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.

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.

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. The Write-Ahead Log records every change in the DB in order. With archive_command (or pgBackRest/WAL-G), ship those 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'. Postgres stops at exactly that point — just before the destructive statement. That’s how you land at “the moment before disaster,” not last night.
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. An unverified backup is Schrödinger’s backup — you don’t know if it works until you open it. 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.