I have a database with two schemas, say development and production. Now I dumped a few tables from schema development and wanted to restore the dumped tables into schema production. I thought it should be quite simple (may actually is), but I haven't been able to do so just with pg_dump and pg_restore. I thought this could be done in one of two ways: either 1, to dump the tables from development but have the schema changed to production in the dump file or 2, to restore the tables from dump file but have the schema changed to production (so the tables will be restored in production). I've read the man pages of pg_dump and pg_restore, but seems pg_dump doesn't allow me to do 1 and pg_restore doesn't allow me to do 2. I still suspect that I may have missed something. I'm using Postgres 15. Any help's appreciated. Thanks.

I have a database with two schemas, say development and production. Now I dumped a few tables from schema development and wanted to restore the dumped tables into schema production. I thought it should be quite simple (may actually is), but I haven't been able to do so just with pg_dump and pg_restore. I thought this could be done in one of two ways: either 1, to dump the tables from development but have the schema changed to production in the dump file or 2, to restore the tables from dump file but have the schema changed to production (so the tables will be restored in production). I've read the man pages of pg_dump and pg_restore, but seems pg_dump doesn't allow me to do 1 and pg_restore doesn't allow me to do 2. I still suspect that I may have missed something. I'm using Postgres 15. Any help's appreciated. Thanks.

• postgresql
• pg-dump
• pg-restore

• 1) pg_restore ... -f some_file. This will create a plain text file SQL file where you can search and replace the schema name. Then do psql ... -f some_file to restore to database. Or just pg_dump ... to plain text in the first place. In other words don't use -Fc. Again you will need to restore with psql. 2) In source database copy/move tables to production schema and then do dump. – Adrian Klaver Commented Nov 18, 2024 at 22:26
• For 2 to move see ALTER TABLE : SET SCHEMA This form moves the table into another schema. Associated indexes, constraints, and sequences owned by table columns are moved as well. . To copy see CREATE TABLE AS. Also CREATE TABLE using LIKE. Generally it is easier to set up separate dev and production databases and use the same naming in each. – Adrian Klaver Commented Nov 18, 2024 at 22:44

1 Answer 1

There is no way to rename a schema during dump/restore.

I suggest the following workaround:

# dump the schema
pg_dump -F c -U postgres -n myschema -f dumpfile dbname

createdb -U postgres scratch

# restore the schema to a scratch database
pg_restore -d scratch -U postgres dumpfile

# rename the schema in the scratch database
psql -d scratch -U postgres -c 'ALTER SCHEMA myschema RENAME TO newschema'

# dump the renamed schema from the scratch database
pg_dump -F c -U postgres -n newschema -f dumpfile scratch

# restore the renamed schema to the original database
pg_restore -d dmname -U postgres dumpfile

dropdb -U postgres scratch