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
