(opens in a new tab)

Restore a PostgreSQL Database From a Dump File

September 6, 2026 by Chat2DB Team

Restoring a PostgreSQL dump is simple once you know which of the two restore tools your file needs, and most of the confusion comes from not knowing that there are two. psql restores plain SQL dumps. pg_restore restores the custom, directory and tar formats. Handing a file to the wrong one produces a confusing error and a lot of wasted time.

This guide covers identifying the format, restoring each kind, doing it in parallel, and dealing with the errors that show up on a real restore.

Which format do I have?

pg_dump produces four formats, selected with --format:

Identify an unknown file:

file backup.dump
head -c 100 backup.dump | xxd | head -3

A custom-format dump begins with the magic string PGDMP. A plain dump begins with SQL comments such as -- PostgreSQL database dump. A gzipped plain dump starts with the gzip magic bytes 1f 8b.

# Plain SQL, possibly compressed
head -5 backup.sql
zcat backup.sql.gz | head -5

Restoring a plain SQL dump

A plain dump is just SQL, so psql runs it:

# Create the target database first — a plain dump of a single database
# does not contain CREATE DATABASE unless you used --create.
createdb -U postgres shop
 
psql -U postgres -d shop -f backup.sql

For a compressed dump, stream it rather than expanding it to disk:

gunzip -c backup.sql.gz | psql -U postgres -d shop

Two flags matter here. By default psql prints an error and keeps going, so a broken restore finishes "successfully" with missing objects. Stop on the first error instead:

psql -U postgres -d shop \
     --set ON_ERROR_STOP=on \
     --single-transaction \
     -f backup.sql

--single-transaction wraps the whole restore so a failure leaves you with nothing rather than a half-restored database. It is the right default for a database that fits comfortably in one transaction; for a very large one it holds a transaction open for hours, which blocks vacuum on the target — a real cost if the target is shared.

If the dump was taken with pg_dump --create or came from pg_dumpall, it contains its own CREATE DATABASE, so connect to postgres instead:

psql -U postgres -d postgres -f full_cluster.sql

Restoring a custom or directory dump

pg_restore reads the archive's table of contents, which is what makes selective and parallel restores possible.

# Create the database, then restore into it
createdb -U postgres shop
pg_restore -U postgres -d shop backup.dump
 
# Or let pg_restore create it (only works if the dump was taken with --create)
pg_restore -U postgres -d postgres --create backup.dump
 
# Directory format
pg_restore -U postgres -d shop /backups/shop_dump_dir/

The flags worth knowing:

pg_restore -U postgres -d shop \
  --jobs=8 \            # parallel restore — see below
  --no-owner \          # do not try to SET ROLE to the original owners
  --no-privileges \     # skip GRANT/REVOKE statements
  --exit-on-error \     # stop at the first failure (default is to continue)
  --verbose \
  backup.dump

--no-owner and --no-privileges are what you want when restoring a production dump into a local development database where the production roles do not exist. Everything ends up owned by the connecting user.

Parallel restore

--jobs is the single biggest speedup available, and it only works with custom and directory formats. It restores table data and builds indexes in parallel:

pg_restore -d shop --jobs=8 backup.dump

Start with the number of CPU cores and watch. The parallelism is bounded by disk throughput, not just cores; on a machine with slow storage, --jobs=16 can be slower than --jobs=4 because random I/O from index builds collides.

A dump taken with -Fd --jobs=8 is also produced in parallel, which is usually the bigger win on the backup side.

For a plain SQL dump there is no parallel option — the file is a linear stream of statements. If you regularly restore large databases, that alone is a reason to switch your backups to the directory format.

Tuning the target for a restore

A restore is a write-heavy bulk load. These settings, applied for the duration and reverted afterwards, typically cut the time substantially:

ALTER SYSTEM SET maintenance_work_mem = '2GB';      -- faster index builds
ALTER SYSTEM SET max_wal_size = '32GB';             -- fewer checkpoints
ALTER SYSTEM SET checkpoint_timeout = '30min';
ALTER SYSTEM SET autovacuum = off;                  -- for the restore window only
SELECT pg_reload_conf();

Do not turn fsync off unless the target is genuinely disposable. The advice circulates widely and the risk is under-stated: an unlucky power loss mid-restore leaves a cluster that appears to start and returns corrupt data.

Revert afterwards, and analyze:

ALTER SYSTEM RESET maintenance_work_mem;
ALTER SYSTEM RESET max_wal_size;
ALTER SYSTEM RESET checkpoint_timeout;
ALTER SYSTEM RESET autovacuum;
SELECT pg_reload_conf();

vacuumdb --analyze-in-stages --dbname shop --jobs 4

Skipping the analyze step is the classic post-restore mistake: the data is all there, and every query plan is built on empty statistics.

Restoring only part of a dump

The table of contents in a custom dump lets you restore selectively.

# What is in this dump?
pg_restore --list backup.dump | head -40
 
# One table, data and definition
pg_restore -d shop --table=orders backup.dump
 
# One schema
pg_restore -d shop --schema=reporting backup.dump
 
# Schema only (structure, no rows) or data only
pg_restore -d shop --schema-only backup.dump
pg_restore -d shop --data-only backup.dump

For finer control, edit the list:

pg_restore --list backup.dump > toc.txt
# delete or comment out (with ;) the lines you do not want
pg_restore -d shop --use-list=toc.txt backup.dump

You can also turn any archive back into readable SQL without a server, which is invaluable when you need to check what a dump contains before running it:

pg_restore --schema-only --file=schema.sql backup.dump
less schema.sql

Errors you will actually hit

pg_restore: error: input file appears to be a text format dump. Please use psql. Exactly what it says — use psql -f.

psql: error: ... syntax error at or near "\" when running a custom dump through psql. The reverse mistake: this is a binary archive, use pg_restore.

ERROR: role "app_prod" does not exist The dump references roles that are not in the target cluster. Either create them, or restore with --no-owner --no-privileges. To bring the roles across properly from the source cluster:

pg_dumpall --globals-only -h source.internal -U postgres > globals.sql
psql -U postgres -f globals.sql

ERROR: must be owner of extension postgis or extension errors generally. Extensions must be installed on the target as operating-system packages before restore; the dump only contains CREATE EXTENSION. Install the package, then rerun.

ERROR: database "shop" already exists Drop it first, or restore into a differently named database. Dropping requires no active connections:

SELECT pg_terminate_backend(pid)
FROM   pg_stat_activity
WHERE  datname = 'shop' AND pid <> pg_backend_pid();

dropdb -U postgres shop && createdb -U postgres shop

ERROR: could not create unique index ... Key (email)=(x) is duplicated Almost always means the dump was taken inconsistently — for example by dumping tables individually rather than in one pg_dump run. A single pg_dump invocation is consistent because it uses one snapshot; a shell loop over tables is not.

Version errors: unsupported version (1.16) in file header. The dump was produced by a newer pg_dump than your pg_restore. Use a pg_restore at least as new as the pg_dump that created the file. The general rule for both directions: always use the newer version's tools.

Verifying the restore

Do not trust "the command finished". Check:

-- Table count and row counts
SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';
 
SELECT relname, n_live_tup
FROM   pg_stat_user_tables
ORDER  BY n_live_tup DESC
LIMIT  20;
 
-- Constraints and indexes present
SELECT conrelid::regclass, conname, contype FROM pg_constraint
WHERE  connamespace = 'public'::regnamespace ORDER BY 1;
 
SELECT tablename, indexname FROM pg_indexes
WHERE  schemaname = 'public' ORDER BY 1;
 
-- Sequences positioned correctly (a dump restores these, but verify)
SELECT sequencename, last_value FROM pg_sequences WHERE schemaname = 'public';
 
-- Anything that failed to validate
SELECT conrelid::regclass, conname FROM pg_constraint WHERE NOT convalidated;

Comparing the restored database against the source is much easier with both connections open side by side; Chat2DB (opens in a new tab) will hold both and let you run the same query against each, which beats diffing two terminal windows. You can download it from chat2db.ai/download (opens in a new tab).

A note on what a dump is not

A pg_dump file is a logical export: a snapshot of the data as SQL. It is excellent for moving between major versions, between platforms, and into a development environment. It is a poor primary backup strategy for a large production database, for two reasons: restoring it takes as long as the data takes to reload and re-index, and it gives you exactly one recovery point — the moment the dump started.

For production, pair a physical base backup with WAL archiving, so you can recover to any point in time rather than to last night. Keep logical dumps as well; they are the thing that saves you when someone drops one table and you need it back without rewinding the whole cluster.