Restoring Data with pg_restore

So, you’ve been a responsible database admin and made a backup. Now comes the moment of truth: restoring your data. Enter the star of this episode — the pg_restore command. If pg_dump was your superhero for making backups, then pg_restore is its trusty sidekick, bringing your data back to life from those backups.

pg_restore is used to restore data from files created by pg_dump in binary formats (custom or directory). If your database got corrupted for some reason, or you want to move your data to a new server, this tool is your go-to buddy.

Why you need pg_restore:

• It lets you restore the whole database or just specific parts (schema, tables, data).
• It’s a powerful tool for tricky restore scenarios where you need fine-grained control over the process.

Basic Command Syntax

Working with pg_restore starts with the basic command syntax:

pg_restore -U username -d database_name backup_file.dump

Command parameters:

• -U username: specifies the PostgreSQL user to connect as.
• -d database_name: the name of the database you want to restore data into.
• backup_file.dump: the backup file you made earlier with pg_dump in custom or directory format

For example, if you want to restore the university database from backup.dump as the postgres user, the command would look like this:

pg_restore -U postgres -d university backup.dump

When you run this command, pg_restore will kick off the restore process and bring your data back into the database!

Backup Types and Compatibility

Not all backups are created equal. You might remember that when making a backup with pg_dump, you could pick different formats. Some of them work with pg_restore, and some don’t.

Heads up: for text backups (plain), you can’t use pg_restore. Those files are restored manually with the psql command. For example:

psql -U username -d database_name -f backup.sql

pg_restore Flexibility: Restore Parameters

One of the main reasons pg_restore is so popular is its flexibility. You can use it to restore not just the whole database, but also specific parts.

Restoring the data structure. If you only want to restore the database structure (table schemas, indexes, triggers, etc.), use the --schema-only flag:

pg_restore -U postgres -d university --schema-only backup.dump

This is handy if you want to check out the database structure or make an “empty” copy.

Restoring just the data. If you only care about the data (not the structure), use the --data-only parameter:

pg_restore -U postgres -d university --data-only backup.dump

This approach is useful if the structure already exists, but you need to restore the table contents.

Cleaning before restore. Sometimes tables or schemas already exist in the database. To drop them before restoring, use the --clean flag:

pg_restore -U postgres -d university --clean backup.dump

With this parameter, pg_restore will first drop existing objects, then restore data from the backup.

Filtering objects. pg_restore lets you pick which objects from the backup you want to restore. Use the --table flag for this:

pg_restore -U postgres -d university --table=students backup.dump

This example will restore only the students table.

Outputting SQL to the console

If you want to see what’s going to happen during the restore, but don’t want to actually change the database yet, use the --list flag:

pg_restore -l backup.dump

And to get the actual SQL commands that will be run, add --verbose:

pg_restore -U postgres --verbose --schema-only backup.dump

Restore Examples

1. Full database restore.

The most typical scenario: you want to restore the whole database from a backup:

pg_restore -U postgres -d university backup.dump

2. Restoring a specific table.

For example, if you lost data only from the courses table, restore just that one:

pg_restore -U postgres -d university --table=courses backup.dump

3. Restoring the database schema.

To restore just the table structure (no data):

pg_restore -U postgres -d university --schema-only backup.dump

4. Restoring with object recreation.

If you need to overwrite existing data:

pg_restore -U postgres -d university --clean backup.dump

Common Restore Errors and How to Avoid Them

• Version incompatibility. If you make a backup with one version of PostgreSQL and try to restore it on another (especially an older one), you might hit compatibility errors. Solution: try to use the same PostgreSQL version for both backup and restore.

• "Role does not exist" error. If your backup uses roles (users) that don’t exist on the restore server, you’ll get this error. Solution: create the needed roles on the new server ahead of time.

• "Table already exists" error. This happens if you try to restore a table that’s already there. Use --clean to avoid the conflict.