Restore Databases with pg_restore

You've backed up your PostgreSQL databases, but now disaster strikes — a dropped table, a corrupted row, or a failed migration. If you can't restore, your backup is just dead weight. pg_restore is the PostgreSQL utility that brings your pg_dump custom-format archives back to life, and in this lesson you'll learn not only how to run it, but how to restore with precision, avoid common footguns, and integrate restoration into your disaster recovery workflow. We'll cover the mental model behind dump formats, step-by-step restoration, a hands-on exercise you can run right now, comparisons with other restore tools, and troubleshooting for the errors that trip up even experienced DBAs.

The Problem This Lesson Solves

A backup is only as good as your ability to restore it. Many developers test backups by checking the file size or the exit code of pg_dump, only to discover later that the restoration fails due to schema mismatches, missing permissions, or incorrect flags. Worse, a full database restore can accidentally wipe existing data or grind to a halt when the target database already contains objects.

The real pain: you need a reliable, repeatable way to restore databases with pg_restore — not just for full recoveries, but for selective restores of specific tables, indexes, or functions. Without a solid understanding of pg_restore, you risk downtime, data loss, and a panic-filled recovery process.

This lesson gives you the tools to restore with confidence: you'll learn the core concepts, practice a real restoration, compare pg_restore with alternative methods, and walk away with troubleshooting skills for common edge cases.

Core Concept / Mental Model

Think of pg_restore as a construction crew that rebuilds your database from a blueprint archive. Unlike a simple text SQL file (which is just a list of statements), a pg_dump custom-format archive is a structured catalog that stores each database object (tables, views, functions, data) as a separate entry, along with its dependencies. The archive knows, for example, that a foreign key constraint depends on the referenced table existing first. pg_restore reads that catalog, orders the objects correctly, and executes the rebuild.

Key Definitions

• Custom format (-Fc): A compressed, binary archive produced by pg_dump. Not human-readable, but supports selective restore and parallel jobs.
• Directory format (-Fd): A directory of files, one per object, also supporting parallel restore and selective restore.
• Plain SQL (-Fp): A single .sql file — restored with psql, not pg_restore. Mental model in words: pg_restore is like a translator between the archive's object catalog and the SQL statements needed to recreate the database. It can decide to restore everything or only the objects you ask for, and it can skip errors instead of stopping the whole process.

How It Works Step by Step

Restoring with pg_restore follows a logical sequence. Here's the cause → effect chain:

1. Create the target database (unless you're restoring into an existing one). pg_restore does not create the database by default; it connects to one.
2. Run pg_restore with the archive file, specifying connection details and options.
3. pg_restore connects to the target database, reads the archive catalog, and builds a script of SQL statements.
4. It executes statements in dependency order (unless you use --exit-on-error to stop at the first error).
5. It reports errors/warnings (by default, it continues past non-fatal errors).

The --clean option

If you want to drop existing objects before recreating them (to avoid conflicts), add the --clean (-c) flag. If you want the script to recreate the database itself, use --create (-C) — but that requires a database to connect to first (usually postgres).

Pro tip: Always test your restore in a staging environment before running in production. Restoration is the one operation you don't want to improvise.

Hands-On Walkthrough

Let's put theory into practice. First, create a backup in custom format, then restore it into a fresh database.

Step 1: Create a custom-format backup

Assume you have a database called mydb. Use pg_dump with the custom format:

pg_dump -h localhost -U postgres -Fc mydb > mydb.dump

This produces a compressed archive mydb.dump.

Step 2: Create a target database

createdb -h localhost -U postgres mydb_restore

Step 3: Restore the archive

pg_restore -h localhost -U postgres -d mydb_restore mydb.dump

Expected output (truncated):

Password:
...

No errors means a successful restore. You can verify by listing tables:

psql -h localhost -U postgres -d mydb_restore -c "\dt"

Selective restore (only a table)

To restore just one table public.users:

pg_restore -h localhost -U postgres -d mydb_restore -t users mydb.dump

Restore with clean and create

To drop existing objects and recreate the database (connect to postgres first):

pg_restore -h localhost -U postgres --clean --create -d postgres mydb.dump

Note: Use --list to see the archive contents: pg_restore -l mydb.dump — handy for selective restores.

Compare Options / When to Choose What

When to choose what: - Selective restore: Use custom format with -t or -n. - Speed: Use directory format with -j for parallel restore. - Portability: Use plain SQL dump and psql.

Variations

• --no-owner (-O): Skip ownership statements when restoring as a different user.
• --data-only: Restore only data, not schema.
• --schema-only: Restore only schema definitions.
• --jobs (-j): Run restore with parallel workers (directory format only).

Troubleshooting & Edge Cases

Here are the common errors you'll hit and how to fix them.

Error: "role \"foo\" does not exist"

Cause: The archive contains ownership that refers to a role missing on the target cluster. Fix: Use --no-owner to skip ownership statements, or create the role first.

Error: "database \"mydb\" does not exist"

Cause: You didn't create the target database, or used --create but connected to a non-existent database. Fix: Create the target database (or use -d postgres with --create).

Error: "relation \"public.users\" already exists"

Cause: The target database already has objects and you didn't use --clean. Fix: Add --clean to drop existing objects first.

Restore stops with errors

Cause: By default, pg_restore continues after errors. If you want it to stop on first error, use --exit-on-error. Fix: Review the error log; often the first error causes cascading failures.

Selective restore seems to do nothing

Cause: You forgot to include dependent objects (e.g., a view that depends on the table). Fix: Use --list to check the archive and include the necessary objects.

Pro tip: Always check the exit status: a non-zero exit code from pg_restore indicates errors, even if it finished.

What You Learned & What's Next

You've mastered the core idea behind restoring databases with pg_restore: you can now create a custom-format backup, restore full or partial data, choose between restore options, and troubleshoot common pitfalls. You've also learned to test restores in staging and to use --clean and --no-owner to handle conflicts and permission issues.

Next step: In the next lesson in this PostgreSQL track, you'll likely explore point-in-time recovery or logical replication, which builds on your restore knowledge to keep databases resilient in real time. By now, you're ready to plan a disaster recovery strategy that includes backup and restore as a well-oiled routine.

Keep practicing: set up a dummy database, dump it, break something, and restore it — that hands-on experience is worth more than reading a thousand docs.