I need to restore a single schema from production postgres to test.

Test database and schema already exists.

The users/roles & sequences are different on prod to test and after schema is restored in test, will need to readjust the users/roles & sequences to what it was originally in test.

Can some one help provide steps & commands on how to perform this task?

dump command as follows:

pg_dump -h hostname -p port -U pgadmin -n schema1 -d db1 > backupfile_20190715.sql -v

Not sure how I restore the test schema. Do I drop schema1 first in test and run the script in? How about user/roles. How do I put back what was there in test after restoring from production backup.


Use a custom format pg_dump with the -Fc option.

Don't restore permissions and ownership by using the -x and -O options of pg_restore. You will then have to write and apply an SQL script that adjusts the ownership and permissions.

There is no need to change the sequences.

Not the answer you're looking for? Browse other questions tagged or ask your own question.