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.

| improve this answer | |

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