Full Snapshots vs. Point-in-Time Recovery (PITR)
Nightly database dumps (via pg_dump or mysqldump) leave an unacceptable 24-hour data loss window. Modern production databases combine periodic snapshots with continuous Write-Ahead Log (WAL) archiving to achieve Point-in-Time Recovery (PITR).
How PITR Works
The database streams its binary write log segments (WAL in Postgres, binlog in MySQL) continuously to encrypted object storage (S3). To restore to a state right before a catastrophic human error (e.g., an accidental DROP TABLE at 14:02:15):
- Restore the latest baseline snapshot taken before the event (e.g., midnight snapshot).
- Replay the archived WAL logs sequentially up to just before the bad transaction: by timestamp (14:02:14), or more precisely by transaction ID or LSN (
recovery_target_xid/recovery_target_lsnwithrecovery_target_inclusive = false).
Backup methods compared
| Method | Typical RPO | Restore speed | Protects against | Weakness |
|---|---|---|---|---|
Logical dump (pg_dump, mysqldump) | Hours to 24 h | Slow: rebuilds indexes, often hours per TB | Mistakes, version upgrades, restoring a single table | Large RPO, heavy load while it runs |
Physical or volume snapshot (EBS snapshot, pg_basebackup) | Snapshot interval | Fast: copies blocks | Disk loss, mistakes (up to the last snapshot) | Must be crash-consistent, and is tied to the engine version |
| Snapshot + WAL/binlog archive (PITR) | Seconds to about a minute | Snapshot restore + log replay time | Mistakes at any point in the retention window | Replay time grows with the distance from the last snapshot |
| Streaming replica | Near zero | Seconds (promote the replica) | Node or zone failure | Copies deletes and corruption immediately, so it is not a backup |
| Delayed replica (for example 1 h behind) | Up to the delay | Minutes | Mistakes noticed within the delay | Extra cost, and only covers a short window |
Worked example: RTO for a 2 TB PostgreSQL database
- The base backup is taken nightly at 00:00. Someone runs an accidental
DELETEwithout a WHERE clause at 14:02:15. - Restoring 2 TB from object storage at about 500 MB/s takes around 70 minutes.
- WAL generated since midnight is 14 hours at 15 GB/h, or 210 GB. Replaying it at about 100 MB/s takes around 35 minutes.
- Add time for detection, the decision to restore, validation and switching the application over. The realistic RTO is 2 to 3 hours, and RPO is about the
archive_timeout(for example 60 s) for changes outside the damaged table. - To reduce RTO, take snapshots more often (every 4 hours means at most 4 hours of replay), use incremental backups (pgBackRest, WAL-G, or PostgreSQL 17 incremental backup), or restore to a side instance and copy back only the damaged table.
In PostgreSQL, the target is set with recovery_target_time, or more precisely with recovery_target_xid or recovery_target_lsn, which you can find by inspecting the WAL with pg_waldump. Set recovery_target_inclusive = false so that the bad transaction itself is not replayed.
Operational rules
- 3-2-1: three copies, on two kinds of media or services, one off-site. Modern practice adds immutability, for example S3 Object Lock in compliance mode, so that even an administrator cannot delete backups during the retention period.
- Encrypt backups and keep the keys somewhere that survives the loss of the primary account.
- Monitor backups: alert when the last successful backup is too old or much smaller than the previous one. In the 2017 GitLab incident, backups had been failing silently and the failure notifications were never received.
- Managed services: Amazon RDS and Aurora provide automated PITR with up to 35 days of retention. The latest restorable time is usually within about 5 minutes of the present. You still need to test restores and keep a cross-region or cross-account copy.