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. 30 minutes.

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.

Next

• architecture-patterns/01-monorepo-vs-polyrepo