How to Restore a PostgreSQL Database from a Backup

There’s a specific kind of stress that comes with restoring a database — usually because something has already gone wrong, and now the pressure is on to get it back exactly right. I’ve been on the other end of that phone call more than once, and the thing that’s saved me every time wasn’t luck, it was actually understanding the restore process ahead of time rather than trying to learn it under pressure. In this guide, I’ll walk through the different restore methods depending on how your backup was taken, so you’re prepared before you ever need to be.

If you haven’t taken a backup yet, my companion article, “How to Back Up a PostgreSQL Database,” covers pg_dump, pg_dumpall, and physical backup methods in detail — this guide assumes you already have one of those in hand.

Know Your Backup Type First

The restore method depends entirely on how the backup was created:

• Plain SQL dump (pg_dump -F p or pg_dump default in older versions) — a plain text file of SQL statements, restored with psql.
• Custom format dump (pg_dump -F c) — a compressed, PostgreSQL-specific binary format, restored with pg_restore.
• Directory format dump (pg_dump -F d) — similar to custom format but split into multiple files, also restored with pg_restore.
• pg_dumpall output — a plain SQL dump of the entire cluster, including roles and tablespaces, restored with psql.
• Physical/base backup (pg_basebackup or filesystem-level backup) — a full copy of the data directory, “restored” by placing the files back and starting PostgreSQL, often combined with WAL replay for point-in-time recovery.

Restoring a Plain SQL Dump

If your backup was created with:

pg_dump -U postgres -d mydb -F p -f mydb_backup.sql

Restore it into a fresh, empty database:

createdb -U postgres mydb_restored
psql -U postgres -d mydb_restored -f mydb_backup.sql

Since it’s just a sequence of SQL statements, you can also pipe it directly:

psql -U postgres -d mydb_restored < mydb_backup.sql

Plain SQL dumps restore sequentially and will print errors to the terminal as they occur rather than stopping outright (unless you add ON_ERROR_STOP=1):

psql -U postgres -d mydb_restored -v ON_ERROR_STOP=1 -f mydb_backup.sql

I’d recommend always using ON_ERROR_STOP=1 for restores — silently continuing past errors can leave you with a database that looks restored but is actually missing objects or data.

Restoring a Custom Format Dump

If your backup was created with:

pg_dump -U postgres -d mydb -F c -f mydb_backup.dump

Restore it with pg_restore:

createdb -U postgres mydb_restored
pg_restore -U postgres -d mydb_restored mydb_backup.dump

Custom format gives you a lot more flexibility than plain SQL. A few useful options:

pg_restore -U postgres -d mydb_restored -j 4 mydb_backup.dump

-j 4 parallelizes the restore across 4 jobs, which can dramatically speed up large restores — though it only helps with the data-loading phase, not schema creation, which is inherently sequential.

To restore only specific tables:

pg_restore -U postgres -d mydb_restored -t employees mydb_backup.dump

To see what’s inside a dump file before restoring anything:

pg_restore -l mydb_backup.dump

This lists every object in the archive with an ID, which you can use to selectively restore just parts of it by passing a filtered list back in with -L.

Restoring a Directory Format Dump

pg_dump -U postgres -d mydb -F d -f mydb_backup_dir -j 4

Restoring is nearly identical to custom format:

pg_restore -U postgres -d mydb_restored -j 4 mydb_backup_dir

Directory format is particularly useful for very large databases since the dump itself can be created in parallel (-j), not just the restore.

Restoring a Full Cluster from pg_dumpall

If you backed up the entire cluster (all databases, roles, and tablespaces) with:

pg_dumpall -U postgres -f full_cluster_backup.sql

Restore it against a fresh PostgreSQL instance:

psql -U postgres -f full_cluster_backup.sql postgres

This recreates roles, databases, and all their content. Since pg_dumpall produces a plain SQL file, the same ON_ERROR_STOP=1 advice applies here too.

Restoring a Physical Backup (Base Backup)

If you took a physical backup with pg_basebackup, restoring means putting the files back in place and letting PostgreSQL start up and recover:

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres tar -xzf base_backup.tar.gz -C /var/lib/postgresql/16/main
sudo systemctl start postgresql

If you also archived WAL files separately (for point-in-time recovery), you’ll need a restore_command pointing to your WAL archive and a recovery_target_time if you want to stop recovery at a specific moment rather than replaying everything:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-08-14 10:00:00'

Place these in postgresql.conf (or postgresql.auto.conf), create a recovery.signal file in the data directory (for versions 12+), and start PostgreSQL. It will replay WAL up to the target time and then stop, ready to be promoted:

touch /var/lib/postgresql/16/main/recovery.signal
sudo systemctl start postgresql

Once recovery completes and you’re satisfied with the state of the database, promote it to normal operation:

SELECT pg_promote();

Restoring into an Existing Database (Overwriting)

If you need to restore over an existing database rather than into a fresh one, the safest pattern is:

psql -U postgres -c "DROP DATABASE IF EXISTS mydb;"
psql -U postgres -c "CREATE DATABASE mydb;"
pg_restore -U postgres -d mydb mydb_backup.dump

Dropping and recreating the database guarantees you’re not left with a mix of old and restored objects. If you can’t drop the database (active connections, replication, etc.), terminate existing connections first:

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

Verifying the Restore

After any restore, I go through a quick checklist:

\dt                                    -- confirm tables exist
SELECT COUNT(*) FROM employees;        -- spot-check row counts
SELECT MAX(hire_date) FROM employees;  -- sanity check on recent data

Compare row counts and key values against what you expect from the source system, if you have any way to cross-reference them. Don’t just trust that “no errors” means “fully correct.”

Common Use Cases

• Recovering from accidental data loss (a bad DELETE or DROP TABLE).
• Standing up a staging or development environment with a copy of production data.
• Migrating a database to a new server or a new PostgreSQL version.
• Disaster recovery after a hardware failure or corrupted data directory.
• Point-in-time recovery to a moment just before a specific incident occurred.

Troubleshooting Common Issues

“role does not exist” errors during restore — the dump references roles that don’t exist on the target server. Either restore with pg_dumpall --roles-only first, or create the missing roles manually before restoring.

“database already exists” — either drop and recreate the target database first, or restore into a differently named database.

Restore hangs or is extremely slow — for custom or directory format dumps, try increasing parallelism with -j. Also check whether indexes are being rebuilt during restore (normal, but can be slow for large tables) — this is expected behavior since pg_dump typically creates indexes after loading data, not before.

“out of shared memory” during restore — often caused by restoring many objects with a low max_locks_per_transaction. Increase it in postgresql.conf and restart before retrying.

Point-in-time recovery doesn’t stop where expected — double-check recovery_target_time is in the correct timezone, and confirm your WAL archive actually contains files covering that time range.

Best Practices

• Always test your restore process before you need it in an emergency — a backup you’ve never restored is an assumption, not a guarantee.
• Use ON_ERROR_STOP=1 with psql restores so failures are surfaces immediately rather than silently skipped.
• Prefer custom or directory format dumps over plain SQL for anything beyond small databases — they support parallel restore and selective table restoration.
• Keep a copy of your role and permission definitions separately (pg_dumpall --roles-only) so a database-only restore doesn’t leave you missing users and grants.
• Document your recovery time objective (RTO) and recovery point objective (RPO), and make sure your backup and restore strategy actually meets them — measure how long a real restore takes, don’t estimate it.
• For critical systems, combine periodic base backups with continuous WAL archiving so you’re not limited to whatever your last full backup happened to capture.

Wrapping Up

Restoring a PostgreSQL database isn’t inherently hard, but it does require knowing which tool matches your backup format, and having a clear head about the steps rather than improvising during an outage. Whether it’s a quick psql restore from a plain SQL dump, a parallelized pg_restore from a custom format archive, or a full point-in-time recovery from a physical backup, the principle is the same: know your backup type, verify as you go, and practice the process before you’re depending on it under pressure.