Step 8
Backup · restore drills
25 min
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_dumpmajor 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
🎉 You finished PostgreSQL in depth + Redis · Kafka
What's next? Pick another course below.