PostgreSQL Backup and Restore: pg_dump and PITR
On this page
Logical vs physical backups
Someone runs DELETE FROM orders without a WHERE clause at 14:35. By the end of this lesson you'll know how to get those orders back, and how to make sure your backups would really work when that day comes.
There are two kinds of backup, and production systems usually need both:
| Logical: `pg_dump` | Physical: `pg_basebackup` + WAL archive | |
|---|---|---|
| What it copies | SQL definitions and data | The data files themselves |
| Restore one table | Yes | No, whole cluster only |
| Across major versions | Yes, that's how many upgrades work | No, same major version only |
| Recover to any moment | No, only to when the dump ran | Yes, with point-in-time recovery |
| Speed on very large databases | Slow to restore, since indexes are rebuilt | Fast |
Remember one more thing: replication (Day 27) isn't a backup. A replica applies your accidental DELETE a fraction of a second later.
Step 1: Take a logical backup with pg_dump
pg_dump backs up one database while it's in use, as a consistent snapshot. Use the custom format (-Fc), which is compressed and lets you restore selectively:
pg_dump -d appdb -Fc -f appdb.dumpFor big databases, the directory format (-Fd) can dump several tables in parallel:
pg_dump -d appdb -Fd -j 4 -f appdb_dirA plain SQL dump (-Fp) is human-readable and restores with psql, but it can't be restored selectively or in parallel. Keep it for small databases and for reading.
pg_dump doesn't include roles or tablespaces, because those belong to the whole cluster, not one database. Back them up separately:
pg_dumpall --globals-only -f globals.sqlStep 2: Restore it with pg_restore
Restore into a new, empty database. Use -j to run in parallel, which helps most with index builds:
createdb appdb_restored
pg_restore -d appdb_restored -j 4 appdb.dumpThe custom format pays off when you only need part of the backup. List what's inside, then restore a single table's data:
pg_restore -l appdb.dump | head
pg_restore -d appdb_restored -t orders --data-only appdb.dumpThat's often the fastest way to recover from "someone deleted the orders": restore yesterday's dump into a scratch database and copy back the missing rows. The catch is that you lose everything written since the dump ran. To do better, you need the next step.
Step 3: Set up base backups and WAL archiving
Every change is written to the WAL before it reaches the table files (Day 2). If you keep every WAL segment, you can take a copy of the data files from last night and replay the WAL forward to any moment since.
First, tell PostgreSQL to archive each finished WAL segment. This needs a restart:
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'%p is the segment's path and %f its file name. The test ! -f guard refuses to overwrite a segment that already exists. In production, archive to separate storage such as S3 using a dedicated tool. pgBackRest and WAL-G handle compression, encryption, retention and parallelism for you.
Then take a base backup, a consistent copy of the whole data directory, taken while the server runs:
pg_basebackup -h db-primary -U replicator -D /backups/base_2026_10_08 -Fp -Xs -P -c fast-Xs streams the WAL generated during the backup, so the copy is consistent by itself. PostgreSQL 17 added incremental base backups (pg_basebackup --incremental, combined later with pg_combinebackup), which copy only the blocks that changed since the last backup.
Step 4: Point-in-time recovery (PITR) to 14:34
This is point-in-time recovery (PITR). The orders were deleted at 14:35, so you recover to 14:34:59, on a separate server so production keeps running while you check the result:
- Copy the latest base backup taken before 14:35 into an empty data directory.
- Tell PostgreSQL where to find archived WAL and where to stop:
# postgresql.conf on the recovery server
restore_command = 'cp /mnt/wal_archive/%f %p'
recovery_target_time = '2026-10-08 14:34:59+00'
recovery_target_action = 'pause'- Create an empty file called
recovery.signalin the data directory, then start the server:
touch /var/lib/postgresql/18/recovery/recovery.signal
pg_ctl -D /var/lib/postgresql/18/recovery startPostgreSQL replays the WAL up to 14:34:59 and pauses in read-only mode. Check that the orders are there. If you need a different point, change the target and restart. When you're happy, run SELECT pg_wal_replay_resume(); to finish recovery and open the server for writes, then copy the rows back to production or switch over to this server.
Note the file name. recovery.signal means "recover to the target, then become a normal server". standby.signal means "keep following WAL forever as a replica" (Day 27), which isn't what you want here.
Before a risky change such as a large migration, you can save yourself guessing a timestamp by creating a named restore point:
SELECT pg_create_restore_point('before_migration_2026_10');Later, recovery_target_name = 'before_migration_2026_10' recovers to exactly that point.
Step 5: Test your backups, with RPO and RTO
A backup you've never restored isn't a backup yet. Backup jobs fail quietly: a disk fills, a credential expires, and nobody notices until the day it matters. Automate the checks:
- Alert on archiving failures at once.
pg_stat_archivershowsfailed_countand the last failure time. A gap in the WAL archive means you can't recover past it. - Restore last night's dump into a scratch database on a schedule, and check row counts on key tables.
- Run a full PITR drill every quarter, on separate infrastructure, and time it.
- Alert on backup age. Page someone if the newest good backup is older than you can afford to lose.
Two numbers decide how far you need to go. RPO (recovery point objective) is how much recent data you can afford to lose. RTO (recovery time objective) is how long you can afford to be down. Nightly dumps give an RPO of up to 24 hours. WAL archiving brings it down to minutes or less. A replica (Day 27) cuts RTO for hardware failures but does nothing for mistakes, which is why serious setups combine all three.
Check yourself
Restore last night's dump into a scratch database and copy invoices back with pg_restore -t invoices. Every invoice change made between the dump and 09:12 is lost, because a dump only captures the moment it ran.
There's a gap in the WAL archive, so recovery can't go past it. Monitor pg_stat_archiver and alert on archiving failures so you find gaps long before you need them.
The replica copies every change, including the mistakes, almost at once. Backups with PITR are what let you go back to the moment before a bad DELETE or DROP.
RPO, recovery point objective, is how much data loss is acceptable, such as five minutes. RTO, recovery time objective, is how long recovery may take. Nightly dumps give an RPO of up to a day; WAL archiving with PITR can bring it to seconds. Tested restore times tell you your real RTO.
Backups fail silently: missing WAL segments, a wrong archive path, corrupted files or a restore procedure nobody remembers. Only a regular, timed restore into a separate environment proves the backup works and measures how long recovery really takes.