The one thing to understand first
Replication protects against hardware failure, but it faithfully copies mistakes — a DROP TABLE or bad UPDATE replicates instantly to every standby. Point-in-Time Recovery (PITR) protects against logical errors by letting you restore the database to any moment in the past: the instant before the mistake. It combines a base backup with the continuous archive of WAL.
PITR is just “restore the base backup, then replay the saved WAL up to a chosen instant.” A backup without its WAL archive can only return you to backup time; the WAL stream is what lets you stop one second before the disaster.
The two ingredients
- Base backup — a physical snapshot of the data directory taken while the database runs (
pg_basebackup or a snapshot tool). It is the starting point.
- Archived WAL — every WAL segment, copied off to safe storage as it fills. Replaying these on top of the base backup rolls the database forward, <a class="sev1-termlink" href="https://thesev1database.com/glossary/tuple/" title="Tuple">record by record, to any chosen point.
Continuous archiving
Enable WAL archiving so each completed segment is shipped to durable storage: