Day 88: Replication, PITR & WAL archiving, failover
Keeping a second copy, and being able to rewind time
Streaming replication ships the write-ahead log (WAL — tomorrow's topic) continuously from a primary to one or more replicas, which replay it to stay near-real-time in sync — this is single-leader replication (Phase 7, Day 45) in its most common real form.
PITR (Point-In-Time Recovery) goes further: by continuously archiving WAL segments (not just replaying them live), you can restore a base backup and replay WAL up to any specific moment — recovering to "right before the accidental DROP TABLE" rather than only to the last full backup.
archive_mode = on
archive_command = 'cp %p /mnt/wal_archive/%f'Failover is promoting a replica to primary when the original primary fails — Postgres itself doesn't automate this decision; tools like Patroni handle leader election (Phase 7, Day 43's Raft, again) and promote a replica automatically.
Key terms
- Streaming replication
- Continuously shipping WAL from a primary to replicas for near-real-time sync.
- PITR
- Restoring a database to any specific point in time using a base backup plus archived WAL.
- Failover
- Promoting a replica to primary after the original primary fails.
A teammate accidentally runs DROP TABLE orders in production 20 minutes ago. Your last full backup is from last night. What lets you recover to just before the accident?
