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.
PostGIS in Action
About the Authors
Consulting
