Backup & Restore

VACUUM keeps a running database healthy, but it does nothing for the failure modes that matter most: a dropped table, a bad migration, a corrupted disk, a whole machine gone. Backups are the only defense against those — and Postgres gives you two genuinely different kinds, suited to different recovery goals.

Logical backups: pg_dump and pg_dumpall

pg_dump exports a database's schema and data as SQL (or a portable binary format) — a logical snapshot, independent of the exact files Postgres happens to store it in on disk.

# single database, custom compressed format (recommended default)
docker exec -it postgres pg_dump -U admin -d learning -Fc -f /tmp/learning.dump

# copy it out of the container
docker cp postgres:/tmp/learning.dump ./learning.dump

# plain SQL instead, if you want something human-readable/diffable
docker exec -it postgres pg_dump -U admin -d learning > learning.sql

# every database in the cluster, plus roles/users — pg_dump alone won't do this
docker exec -it postgres pg_dumpall -U admin > cluster_full.sql

pg_dump operates on one database at a time and never touches roles or tablespaces — that's what pg_dumpall is for, usually paired with per-database pg_dump -Fc dumps in the same backup routine: pg_dumpall --globals-only for roles, individual pg_dumps for data.

Restoring: pg_restore

The custom format (-Fc) isn't plain SQL — it's restored with pg_restore, which (unlike replaying a giant .sql file) can run in parallel and lets you select individual tables:

# create a fresh target database
docker exec -it postgres psql -U admin -d learning -c "CREATE DATABASE learning_restore;"

# restore into it, 4 jobs in parallel
docker exec -it postgres pg_restore -U admin -d learning_restore -j 4 /tmp/learning.dump

# restore just one table from the same dump
docker exec -it postgres pg_restore -U admin -d learning_restore -t payments /tmp/learning.dump

Physical (base) backups: pg_basebackup

A logical dump reconstructs data by re-running INSERTs — it's slow to restore on a large database and, on its own, only ever gets you back to the exact moment the dump was taken. A physical backup copies the actual data directory — heap files, indexes, everything — byte for byte:

docker exec -it postgres pg_basebackup \
-U admin -D /tmp/base_backup -Fp -Xs -P

-Xs streams the WAL generated during the backup alongside it, which matters: copying files while the database is live and being written to produces an internally inconsistent snapshot on its own — it's only made consistent by replaying that WAL on startup, the same crash-recovery machinery from Write-Ahead Log.

Point-in-time recovery (PITR)

A base backup plus a continuous WAL archive (archive_command, also covered in Write-Ahead Log) together let you restore to any moment since the base backup was taken — not just the moment it happened to be taken. That's PITR, and it's the only tool that answers "someone ran a bad DELETE at 2:14pm, get us back to 2:13pm" without losing every other commit up to that second.

The recipe, at a high level:

# 1. restore the base backup's files into a fresh data directory
cp -r /mnt/backups/base_backup/* /var/lib/postgresql/data/

# 2. tell Postgres where to find archived WAL, and when to stop replaying
cat > /var/lib/postgresql/data/postgresql.auto.conf <<EOF
restore_command = 'cp /mnt/wal-archive/%f %p'
recovery_target_time = '2026-08-17 14:13:00'
EOF
touch /var/lib/postgresql/data/recovery.signal

# 3. start Postgres — it replays archived WAL up to the target and stops there
docker compose up -d

On startup, Postgres notices recovery.signal, pulls WAL segments from the archive via restore_command, and replays them forward from the base backup — the exact same replay mechanism as ordinary crash recovery, just fed a longer history and told where to stop instead of running to the end.

Choosing between them

• pg_dump/pg_dumpall — portable across Postgres versions and even hardware architectures, easy to inspect, good for migrating a single database or seeding a dev environment. Slow to restore on a large database; only restores to the exact dump moment.
• pg_basebackup + WAL archiving — restores an entire cluster fast (it's a file copy, not replayed INSERTs) and, combined with PITR, recovers to any second in the archived window. Tied to the same Postgres major version and, generally, the same architecture.

Most production setups run both: periodic pg_dumps for portability/dev-seeding, and continuous base backups + WAL archiving as the real disaster-recovery path.

Next: instead of restoring from a backup after the fact, keeping a second server continuously up to date in the first place, in Replication.