Restoring Individual Tables from Postgresql pg_dump, Using pg_restore Options

Let’s start by making sure we understand how to backup a whole Postgresql database using pg_dump.

-- execute a pg_dump as root
# pg_dump -U postgres -Fc source-db-name > /directory/path/db-dump-name.dump

• pg_dump invokes the utility.
• -U postgres switches the command to be run as the postgres user.
• -Fc puts the resulting file into a format that can be restored from.
• > outputs the command to the singular dump file.
• The synopsis from the official documentation is:
  pg_dump [connection-option...] [option...] [dbname]

Now, Let’s make sure we understand how to create a new blank database instance to restore into.

-- first switch to postgres user
# su - postgres
-- invoke psql interactive terminal
$ psql
-- then create a new blank db
postgres-# CREATE DATABASE new_db;
-- verify the db is there with:
postgres-# \l

From here, we can restore from our dump file the schema (data definitions) only, not the data.

-- run the schema-only restore as root
# pg_restore -U postgres --schema-only -d new_db /directory/path/db-dump-name.dump

At this point we should have all of our tables, definitions, functions, etc, but no rows in any of the tables. Now we’re ready to restore data at a table specific level.

Restore per table data using something like:

# pg_restore -U postgres --data-only -d target-db-name -t table_name /directory/path/dump-name.dump

• The data-only option allows us to restore only the data and not the schema details.
• -d specifies the target database instance that we are restoring into.
• -t option specifies the table from source to the target.
• /directory/path/dump-name.dump is used above as a generic example of a dump file we are using as the source for the table data.

An interesting way that I use this technology as follows:

• Take the pg_dump from the source db.
• Use pg_restore to restore a schema-only version of the dump.
• Use pg_restore again, but this time targeting one table’s data at a time. We can script a set of commands, one for each table we want to restore. Often times with a legacy database schema, the order in which you restore tables selectively in, is very important, due to Foreign Key (FK) constraints and things like that. Often times a set of tables, because of their defined constraints, will throw errors when you attempt to restore their data, until all of the table’s necessary FK records are in place first.
• This is especially useful if you have a set of table contents that should be the same across database shards, but you also may have some user data in your source db that doesn’t need to be copied into your fresh shard.

Hopefully this is helpful to someone. This is something that has become very useful to me. It’s been handy for me in architectural, managerial and administrative roles many times, and I hope this explanation will resonate and make sense.