How do I rewind to seconds before a disaster with WAL archiving and PITR?

Short answer: nightly dumps mean an 18-hour RPO (acceptable data-loss window) — unacceptable in production. PITR (Point-in-Time Recovery) closes that gap and takes you back to seconds before the bad statement.

Short answer

The logic: with a base backup plus an ordered record of every change since (the WAL), you can rebuild history by “fast-forwarding” up to any exact moment you want. I covered how I set RPO and RTO targets, together with the active-passive scenario, in the disaster recovery record; PITR is the database leg of those targets.

Why

1. The Write-Ahead Log records every change in the DB in order. As long as that ordered record is archived off-box, every transaction between the base backup and now stays replayable — the loss window drops from your backup interval to your archiving lag.

2. recovery_target_time lands you at exactly the moment you want. Postgres stops at the point you set — just before the destructive statement. That’s how you land at “the moment before disaster,” not last night.

3. An unverified backup is Schrödinger’s backup. You don’t know if it works until you open it; if recovery has never been rehearsed, what you hold isn’t a backup but an assumption.

What to do

1. Take a periodic base backup. Take a consistent full backup (base backup) at regular intervals. This is the starting point of recovery; WAL replay is applied on top of it.

2. Continuously archive the WAL to S3. With archive_command (or pgBackRest/WAL-G), ship the WAL segments to S3 as they’re produced. That way the record of every transaction between the base backup and now sits safely off-box.

3. Replay up to the exact moment with recovery_target_time. On recovery, restore the base backup, then replay the WAL up to the target you set: recovery_target_time = '...3 seconds before the bad DELETE'.

4. Always rehearse the restore. WAL archiving must be on and tested, and base backups must be on a schedule. Most critically: rehearse recovery periodically. Don’t hand-roll this; use pgBackRest or WAL-G.

Bottom line: base backups + continuous WAL archiving (to S3) + tested PITR — turns 18 hours of loss into seconds. On a production DB, trusting only a nightly dump will one day leave you stranded with hours of lost data on a bad DELETE; set up PITR today and rehearse the restore.