Backup · restore drills

A backup you've never restored is not a backup. Monthly drills are the real safety net.

1. pg_dump

pg_dump --host=localhost --username=postgres --dbname=mydb \
  --no-owner --no-privileges --format=custom \
  --file=mydb-2026-05-06.dump

custom enables compressed + parallel restore.

2. gzip pipeline

pg_dump --format=plain mydb | gzip > mydb.sql.gz

Readable, no parallel restore.

3. Select tables

pg_dump -t users -t posts -t comments mydb > user-data.sql

Different cadences for different tables.

4. Cron

# /etc/cron.d/pg-backup
0 2 * * * postgres /usr/local/bin/backup.sh

#!/bin/bash
DATE=$(date +%Y-%m-%d)
pg_dump --format=custom --file=/backups/db-$DATE.dump mydb
find /backups -name "db-*.dump" -mtime +7 -delete

5. Restore

pg_restore --dbname=mydb --clean --if-exists mydb.dump

--clean drops then creates. Never run against production.

6. PITR

Two-days-ago-at-3pm needs WAL archiving.

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal/%f'

pg_basebackup + WAL archiving + recovery.conf.

7. Streaming replication

Standby replays the master's WAL live.

primary_conninfo = 'host=master port=5432 user=replicator'

8. Monthly drill

docker run -d --name pg-restore-test -e POSTGRES_PASSWORD=x -p 5433:5432 postgres:15
docker cp mydb.dump pg-restore-test:/tmp/
docker exec pg-restore-test pg_restore --dbname=postgres --create /tmp/mydb.dump

Verify row counts, sample rows, integrity — that single cycle is the definition of "backup success".

9. Encrypted, offsite

pg_dump ... | gzip | gpg --symmetric --passphrase-file /etc/backup.key | \
  aws s3 cp - s3://my-backups/db-$(date +%Y-%m-%d).sql.gz.gpg

10. Gotchas

pg_dump major version drift
Disk full at restore time
Never tested restores
Missed important tables
Lost encryption key

11. Checklist

Daily cron backups
7+ day retention
Offsite copy (S3 or other region)
Encryption at rest and in transit
Monthly restore drill on another host
WAL archiving if PITR is required

Closing

"Set and forget" is the trap. A 30-minute monthly drill is the best investment in durability.

architecture-patterns/01-monorepo-vs-polyrepo