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.
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