How to restore select tables, select objects, and schemas from Pg Backup

One of the nice things about the PostgreSQL command-line restore tool is the ease with which you can restore select objects from a backup. We tend to use schemas for logical groupings which are partitioned by context, time, geography etc. Often times when we are testing things, we just want to restore one schema or set of tables from our backup because restoring a 100 gigabyte database takes a lot of space, takes more time and is unnecessary for our needs. In order to be able to accomplish such a feat, you need to create tar or compressed (PG custom format) backups. We usually maintain PG custom backups of each of our databases.

Restoring a whole schema

Below is a snippet of how you would restore a schema including all its objects to a dev database or some other database.


psql -d devdbgoeshere -U usernamegoeshere -c "CREATE SCHEMA someschema"
pg_restore -d devdbgoeshere --format=c -U usernamegoeshere --schema="someschema" --verbose "/path/to/somecustomcompressed.backup"

Restoring a select set of objects

Now restoring a single table or set of objects is doable, but surprisingly more annoying than restoring a whole schema of objects. It seems if you try to restore a table, it doesn't restore the related stuff, so what we do is first create a table of contents of stuff we want to restore and then use that to restore.

To create a table of contents of stuff to restore do this:

pg_restore --list "/path/to/somecustomcompressed.backup" --file="mytoc.list"

Then simply open up the text file created from above and cut out all the stuff you don't want to restore. Then feed this into the below restore command.

pg_restore -v --username=usernamegoeshere --dbname=devdbgoeshere --use-list="mytoc.list" "/path/to/somecustomcompressed.backup"

Sorry for the mix and match - note -U --username=, -d --dbname= etc. are interchangeable. For more details on how to use these various switches, check out our PostgreSQL Pg_dump Pg_Restore Cheatsheet.