From: | Alex Williams <valenceshell(at)protonmail(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Restoring a database restores to unexpected tablespace |
Date: | 2019-07-09 17:56:19 |
Message-ID: | 3YfH9uVTAd1a1GSxv8H2_mH4sPz_2a3NJgufLpjuMnMsIHd0P3uZ7GWFOD_Q9zgbX_ruyFPaRwdLw7Jncd5b7MokLdHYQMyogrhAIqnBR2o=@protonmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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](https://protonmail.com) Secure Email.
From | Date | Subject | |
---|---|---|---|
Next Message | Weatherby,Gerard | 2019-07-09 18:04:04 | execute_values |
Previous Message | Peter Geoghegan | 2019-07-09 17:47:25 | Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR |