How to Restore a Backup in PostgreSQL

How do I restore a PostgreSQL backup using pg_restore?

pg_restore recreates a PostgreSQL database or selected objects from a custom-format or directory-format backup file.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problem does pg_restore solve?

pg_restore re-creates schemas, data, indexes, and privileges from a custom or directory-format backup produced by pg_dump, letting you rebuild a database after data loss or migrate it to a new server.

Which backup formats can I restore?

pg_restore handles .backup files created with pg_dump -Fc (custom) and folder dumps made with pg_dump -Fd. Use psql for plain SQL files.

How do I run pg_restore quickly?

Specify the target database with -d, enable parallelism with -j, and turn off ownership checks with --no-owner when needed. These options minimize downtime during a restore.

Can I restore only specific tables?

Yes. Use -t multiple times to pick objects such as Customers or Orders. pg_restore ignores everything else, shortening restore time.

How do I map a restore to a new schema?

Combine --schema, --schema-only, and --prefix/--use-set-session-authorization to direct objects into a different namespace while retaining permissions.

Best practice: restore into an empty DB first

Creating a fresh database avoids conflicts with existing objects and lets you drop and retry without harming production data.

Best practice: validate with --list and --dry-run

Run pg_restore -l backup.dump to inspect contents, then test with pg_restore -f /dev/null -C backup.dump before touching data.

What are common pitfalls?

Restoring into a database that still has active connections or different encodings causes failures. Disconnect users and match encodings beforehand.

Why How to Restore a Backup in PostgreSQL is important

How to Restore a Backup in PostgreSQL Example Usage


# Rebuild staging database from nightly backup
dropdb   shop_staging;
createdb shop_staging --template=template0 --encoding=UTF8;
pg_restore -h localhost -U admin \
          -d shop_staging -j 8 --no-owner --role=app_writer \
          backup_nightly.dump;

How to Restore a Backup in PostgreSQL Syntax


pg_restore [connection_option...] [restore_option...] backup_file

# Common connection options
  -h host               # Server address
  -p port               # Server port
  -U user               # Superuser for restore
  -d dbname             # Target database (omit with -C)

# Key restore options
  -C                    # Create database before restoring
  -j num_jobs           # Parallel workers (directory or custom only)
  -n schema             # Restore only objects in schema
  -t table              # Restore only specified table (repeatable)
  -s | --schema-only    # Restore DDL only
  -a | --data-only      # Restore data only
  --no-owner            # Do not set ownership
  --clean               # Drop objects before recreating them

# Example syntax in ecommerce context
pg_restore -h localhost -U admin -d shop_dev \
           -j 4 --clean --no-owner \
           -t Customers -t Orders backup.dump

Common Mistakes

Restoring over an existing database without --clean causes duplicate-object errors. Fix by passing --clean or dropping and recreating the database first.
Omitting --no-owner when the backup was taken from a different cluster fails with "role does not exist." Add --no-owner or pre-create the roles.

Frequently Asked Questions (FAQs)

Does pg_restore lock the database?

pg_restore acquires object-level locks as it recreates each object. Users can still read unaffected tables, but writes to objects being restored are blocked.

Can I resume an interrupted pg_restore?

If you used a directory-format backup with multiple jobs, you can rerun pg_restore; completed objects are skipped, reducing work.

Is parallel restore always faster?

Yes for large multi-table dumps. Small backups or heavily indexed single tables may not benefit and could even slow down due to coordination overhead.