Backups, WAL archiving and point-in-time recovery

conceptThe concept

pg_dump takes a consistent logical snapshot of one database. The custom (-Fc) and directory (-Fd, parallel with -j) formats restore selectively with pg_restore; plain SQL restores with psql. pg_dumpall adds roles and tablespaces. Logical dumps are portable across major versions and architectures but slow to restore for large databases, because every index is rebuilt.

Physical backups copy the data files: pg_basebackup or tools such as pgBackRest, Barman and WAL-G. With continuous WAL archiving (archive_mode, archive_command or archive_library) every WAL segment is shipped to safe storage. Restore = base backup + replay archived WAL up to a recovery_target_time, LSN or named restore point: point-in-time recovery. Since 17, pg_basebackup can take incremental backups using WAL summaries.

RPO (recovery point objective) is how much data you can lose: with archiving, at most the current unarchived segment (or zero with a synchronous standby). RTO (recovery time objective) is how long a restore takes. A backup you have never restored is a hope, not a backup.

whyWhy it matters

Replication protects against hardware failure, not against DELETE FROM orders without a WHERE, which replicates instantly. Only PITR brings back the state from one second before the mistake.

Use caseReal-world scenario

At 14:32 a migration drops a column in production. The team restores last night's base backup to a new server, replays WAL to 14:31:59, copies the column back, and the primary keeps running.

Try itExamples

Run and edit these in the interactive lesson.

# one database, custom format, compressed
pg_dump -h db1 -U postgres -Fc -f shop.dump shop

# directory format, 8 parallel jobs (fastest for big databases)
pg_dump -h db1 -U postgres -Fd -j 8 -f shop_dir shop

# list, then restore only one table into another database
pg_restore -l shop.dump | grep 'TABLE DATA public orders'
pg_restore -h db2 -U postgres -d shop_restore -j 8 --table=orders shop.dump

# roles and tablespaces are cluster-wide, so dump them separately
pg_dumpall -h db1 -U postgres --globals-only > globals.sql

# postgresql.conf on the primary
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=shop archive-push %p'

# full backup, then incremental ones
pgbackrest --stanza=shop --type=full backup
pgbackrest --stanza=shop --type=incr backup

# restore to just before the mistake
pgbackrest --stanza=shop --delta \
  --type=time --target="2025-11-14 14:31:59+00" \
  --target-action=promote restore

SELECT pg_create_restore_point('before_release_2_41') AS lsn;
SELECT archived_count, failed_count, last_archived_wal, last_failed_wal
FROM pg_stat_archiver;

SELECT n.nspname AS schema, c.relkind, count(*) AS objects,
       pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS size
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
GROUP BY 1, 2 ORDER BY 1, 2;

internalsUnder the hood

A base backup taken while the server runs is inconsistent on its own; the WAL generated during the backup makes it consistent. pg_backup_start() and pg_backup_stop() (low-level API) mark the range. Check archiving health in pg_stat_archiver: a growing failed_count means WAL piles up in pg_wal until the disk fills.

Avoid / preferDo this, not that

-- nightly pg_dump is our only backup for a 3 TB database

-- physical base backups plus continuous WAL archiving (pgBackRest, Barman or an operator)
-- and pg_dump only for logical exports

-- backups run every night; nobody has ever restored one

-- scheduled restore test into a scratch environment, with row counts checked

PitfallsCommon mistakes

• Treating a replica as a backup. Mistakes replicate in milliseconds.
• Never testing restores, then discovering missing WAL segments, wrong permissions or a 30-hour restore time during an incident.
• Relying on pg_dump alone for a multi-terabyte database. Restore time (rebuilding every index) usually breaks the RTO.

LimitsWhen not to use it

pg_dump is not a disaster-recovery strategy for large or busy databases. Use it for migrations, per-table exports and long-term logical archives.

SecuritySecurity notes

Backups contain all your data. Encrypt them, restrict the storage bucket, keep an immutable or cross-account copy, and use a dedicated backup role (pg_read_all_data for pg_dump since PostgreSQL 14) instead of superuser.

ChallengeTry it yourself

SELECT current_setting('archive_mode') AS archive_mode,
       archived_count, last_archived_wal, failed_count, last_failed_wal
FROM pg_stat_archiver;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Backup and restore
• Continuous archiving and PITR