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

> Take a periodic base backup, archive WAL to S3 continuously, replay to a recovery_target_time seconds before the bad statement, and rehearse the restore.

- Asked: 2026-06-09
- Answered: 2026-06-12
- Asked by: Yusuf
- Tags: veritabani, postgresql, dayaniklilik
- Source: https://www.muhammetsafak.com.tr/en/just-ask/point-in-time-recovery-with-wal-archiving/
- Language: en-US
- Author: Muhammet Şafak

---
**Question:** Our production DB was corrupted by a cyberattack or bad code (e.g. a `DELETE` with a forgotten `WHERE` clause). If we restore the nightly SQL backup, we lose 18 hours of data.

In the DB architecture, how exactly do continuous WAL log archiving to S3 and a PITR setup let us go back to 3 seconds before the moment of disaster? Explain the process architecturally.


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.

## Related Reading

- [Is PostgreSQL Enough for Everything?](https://sade.dev/en/journal/is-postgresql-enough-for-everything) — sade.dev
- [What rules should I follow to prevent database deadlocks?](https://www.muhammetsafak.com.tr/en/just-ask/detecting-and-preventing-database-deadlocks/) — Just Ask
- [How do I prevent PostgreSQL split-brain with quorum and consensus?](https://www.muhammetsafak.com.tr/en/just-ask/avoiding-postgresql-split-brain-with-quorum-and-consensus/) — Just Ask
- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://www.muhammetsafak.com.tr/en/just-ask/should-i-use-a-partial-index-on-a-queue-table-where/) — Just Ask