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 assumed it worked until I started checking the tables and noticed it was still being created on data2. I tried originally with just -f, but that didn't work so I added in -c and that didn't work either. Last night, I had an idea and it'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 add in 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 dump with tablespaces and the other didn't and that I possibly used the one with the tablespace in it...as I was in the directory for the dump and just explicitly wrote out the directory for the restore, and it's the first time I did this and the dump I took explicitly passed in no tablespaces. I couldn't read the file to be sure, so I added -e on the restore and 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 parameter for no tablespaces (and it looks like it didn't add in tablespace info) but on restore, it restored to tablespace data2 instead of the pg_default tablespace of the database....only after modifying the dump file by adding to the top 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 <t...@sss.pgh.pa.us> wrote: > Ian Barwick ian.barw...@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
