Restoring a database restores to unexpected tablespace

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Restoring a database restores to unexpected tablespace

Alex Williams
Hi,

Can someone point me in the right direction for this issue we are having -- our goal is to dump a database that is currently on a tablespace named data2 that we want to restore on the same server but on tablespace pg_default -- we tried other 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.

Thanks,

Alex




Sent with ProtonMail Secure Email.

Reply | Threaded
Open this post in threaded view
|

Re: Restoring a database restores to unexpected tablespace

Ian Barwick-3
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 currently on a tablespace named data2 that we want to restore on the same server but on tablespace pg_default -- we tried other 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


Reply | Threaded
Open this post in threaded view
|

Re: Restoring a database restores to unexpected tablespace

Tom Lane-2
Ian Barwick <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Restoring a database restores to unexpected tablespace

Alex Williams
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 <[hidden email]> wrote:

> Ian Barwick [hidden email] 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