Chapter 30: Backup, Restore, and Point-in-Time Recovery
Backup Types
| Type | Method | Speed | Granularity |
|---|---|---|---|
| Logical (pg_dump) | SQL export | Slow for large DBs | Per-table possible |
| Physical (pg_basebackup) | Copy data files | Fast | Entire cluster |
| Continuous (WAL archiving) | Stream WAL segments | Continuous | Point-in-time recovery |
Logical Backup
# Dump entire database
$ pg_dump -h localhost -U postgres mydb > backup.sql
# Dump specific table
$ pg_dump -t users mydb > users_backup.sql
# Compressed custom format (recommended)
$ pg_dump -Fc mydb > backup.dump
# Restore
$ pg_restore -d mydb backup.dumpPoint-in-Time Recovery (PITR)
Base backup WAL segments archived continuously
(Monday 2am) [wal.001] [wal.002] [wal.003] ... [wal.N]
│ │ │ │ │
└────────────────────┴─────────┴─────────┴────────────┘
↑
Recover to HERE
(Wednesday 3:47pm)
1. Restore base backup (Monday 2am state)
2. Replay WAL segments up to target time
3. Database is now at Wednesday 3:47pm state
The 3-2-1 Rule
- 3 copies of your data
- 2 different storage media
- 1 offsite (different location/cloud region)
⚠️ Test Your Restores
A backup you haven't tested restoring is not a backup. Schedule regular restore tests. Many teams discover their backups are corrupted or incomplete only when they need them.
Key Takeaways
- pg_dump for logical backups (portable, per-table)
- pg_basebackup + WAL archiving for PITR (recover to any point in time)
- Follow 3-2-1 rule. Test restores regularly.
- RPO (Recovery Point Objective): how much data can you lose? WAL archiving → seconds.
- RTO (Recovery Time Objective): how fast must you recover? Depends on DB size.