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.

closed as too broad by LowlyDBA, Marcello Miorelli, kevinsky, hot2use, Colin 't Hart Aug 6 at 11:33

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.


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.