Point-in-time recovery (PITR)
Restoring a database to any exact moment in the past by replaying its write log on top of a base backup.
See it
What it is
Point-in-time recovery restores a database to any exact moment inside its retention window, not just to whenever the last nightly dump ran. It works by pairing a base backup with a continuous archive of the write-ahead log: the restore replays that log on top of the snapshot and stops at the timestamp you name, say 14:31:50 yesterday, one second before the DELETE without a WHERE clause.
This is the button you reach for after human error or a bad migration: a dropped table, a script that overwrote every email address, a compromised account that wiped records. Managed Postgres (RDS, Cloud SQL, Neon, Supabase) gives you a retention window, commonly 7 to 35 days, and restores into a new instance rather than overwriting the live one. That is a feature: you get to diff old against current and copy back only the damaged table, instead of throwing away every good write that happened since.
Gotchas: recovery is not instant. Time to restore scales with how much log has to be replayed, so a 2TB database is an hours-long outage, not a click. Your retention window is also a hard ceiling: damage discovered five weeks later is out of reach, which is why PITR complements long-term snapshots rather than replacing them. And a backup you have never restored is a hypothesis, so run a real drill and time it, because that number is your actual RTO.
Ask AI for it
Set up and document point-in-time recovery for this Postgres database. Enable continuous WAL archiving to object storage with a base backup schedule, set the retention window to at least 14 days, and confirm the earliest restorable time is reported somewhere visible. Then write a runbook for the 'someone deleted production rows' case: restore to a new instance at a chosen timestamp, verify the damaged table there, copy the recovered rows back into the live database, and only then resume traffic. Include the commands, expected restore duration, and a quarterly restore drill checklist with stated RPO and RTO targets.