Re: Restoring a database restores to unexpected tablespace

Thanks Tom and Ian,

Tom, I wasn't sure if that would work (-c), so I just tried assuming it would throw an error, but it didn't so I
assumedit worked until I started checking the tables and noticed it was still being created on data2. I tried
originallywith just -f, but that didn't work so I added in -c and that didn't work either. Last night, I had an idea
andit's working the way I expect it to now, this is what I did: 

1. Dump Database, this time, no compression so I can search/grep it to see if it has any tablespace references, PLUS
addin my own tablspace reference (See Step 2 for that): 
sudo -u postgres pg_dump --no-owner --no-tablespaces mydatabase > /var/lib/pgsql/dumps/mydatabase.dump

2. Set the default table to pg_default on the first line with sed:
sed  -i '1i SET default_tablespace = pg_default;' /var/lib/pgsql/dumps/mydatabase.dump

3. Restore the database:
cat /var/lib/pgsql/dumps/mydatabase.dump | sudo -H -u postgres psql --quiet -e -c 'SET default_tablespace =
pg_default;'-f - mydatabase_test > /tmp/mydatabase.log 

It's currently still restoring, will take about 2 more hours to complete, but so far so good.

Also, Ian, good idea on that, but there was no issue of the db dump yesterday with path and possibly one path had a
dumpwith tablespaces and the other didn't and that I possibly used the one with the tablespace in it...as I was in the
directoryfor the dump and just explicitly wrote out the directory for the restore, and it's the first time I did this
andthe dump I took explicitly passed in no tablespaces. I couldn't read the file to be sure, so I added -e on the
restoreand checked the logs to see what it was doing and didn't see any mention of tablespaces. On the newer dump in
plaintext,it also doesn't have any tablespace reference other than the one I added as the first line stating: SET
default_tablespace= pg_default; 

Again, the restore to database was set with pg_default as the tablespace to use and the dump explicitly passed in the
parameterfor no tablespaces (and it looks like it didn't add in tablespace info) but on restore, it restored to
tablespacedata2 instead of the pg_default tablespace of the database....only after modifying the dump file by adding to
thetop line this: "SET default_tablespace = pg_default;" did it restore to the pg_default tablespace. 

Thanks again for your help!


Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Wednesday, July 10, 2019 10:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ian Barwick ian.barwick@2ndquadrant.com writes:
>
> > On 7/10/19 2:56 AM, Alex Williams wrote:
> >
> > > 3.  Restore the database with this command:
> > >     zcat /var/backup/db/mydatabase.gz |         sudo -H -u postgres         psql --quiet -e -c 'SET
default_tablespace= pg_default;' -f - mydatabase_test > /tmp/mydatabase_test.log 
> > >
>
> > This should work.
>
> Yeah, on modern PG. But I think psql didn't support combinations of
> -c and -f switches until 9.6. 9.5 would simply have dropped that -c
> switch.
>
> regards, tom lane