Thread: Restoring a database restores to unexpected tablespace

On 7/10/19 2:56 AM, Alex Williams wrote:
> Hi,
> 
> Can someone point me in the right direction for this issue we are having -- our goal is to dump a database that is
currentlyon a tablespace named data2 that we want to restore on the same server but on tablespace pg_default -- we
triedother ways like:
 
> ALTER DATABASE "[database_name]" SET default_tablespace = [new_tablespace];
> alter table all in tablespace data2 set tablespace pg_default;
> 
> But we want to try it with a pgdump/psql.
> 
> To reproduce on our end (Server 9.5):
> 
> 1. create new database for the restore with the tablespace as pg_default
> 
> 2. Dump the source database (currently on data2 tablespace) with the following command:
> sudo -u postgres pg_dump mydatabase --no-owner --no-tablespaces | gzip  > mydatabase.gz
> 
> 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
 
> 
> What happens during the restore is that all tables are created on data2, not pg_default.
> 
> Any help would be greatly appreciated.

This should work.

Double-check each step to make sure nothing has been missed out somewhere, e.g.
in step 2 you create mydatabase.gz in the current working directory but in step 3 restore it
from an absolute filepath, which is a common cause of errors.

Also maybe try dumping an individual table definition (pg_dump --schema-only --table=sometablename ...) and check
exactly what's being dumped and how it gets restored.

Regards


Ian Barwick

-- 
  Ian Barwick                   https://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

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

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