RPO and RTO explained: an interactive backup and disaster recovery simulator

Every data protection plan answers two questions. How much data can we lose? That is the recovery point objective (RPO). How long can we be down? That is the recovery time objective (RTO). Backups, log archiving and replicas trade these against cost, and they protect against different disasters: a replica saves you from a lost data center but copies a mistaken DELETE within seconds.

The playground runs a day of a database with a backup at midnight. Pick a disaster and a time, and compare what five strategies lose and how long each takes to recover.

Outages: replicas win

If the region holding a 500 GB database fails at 8 p.m., last night’s backup brings back the data as of midnight: 20 hours of writes lost, and about half an hour to provision a server and restore it. Archiving the write-ahead log every minute cuts the loss to about a minute, but recovery now replays 20 hours of log and takes over an hour, and for a 5 TB database the restore alone takes about three hours. A standby in another region is promoted in minutes and loses only the seconds it was behind; a synchronous standby loses nothing, but every commit waits for a cross-region round trip.

Mistakes: only history helps

An accidental DELETE, a bad migration or a bug that corrupts rows is replicated just like a good write, so every standby holds the same damage within seconds. What recovers it is history: a backup plus the archived log lets you restore to one second before the mistake, on a separate server, and copy the lost rows back without losing anything written since. A nightly backup alone loses everything since midnight. That is why serious setups combine both: replicas for availability, a log archive for point-in-time recovery, and a delayed replica or snapshots as a faster path for recent mistakes.

• Test restores. A backup you have never restored is a hope, not a backup. Restore regularly and time it: that is your real RTO.
• Keep copies apart. Store backups in another region and another account, so the same outage, or the same compromised credentials, cannot destroy both.
• Size RTO with data. Restore time grows with database size; at a few TB, restoring from backup takes hours, which pushes large systems towards replicas and smaller shards.

Continuous archiving in PostgreSQL

# postgresql.conf: ship every write-ahead log segment to object storage
archive_mode = on
archive_command = 'wal-g wal-push %p'   # or pgBackRest, Barman
archive_timeout = 60                    # ship at least once a minute: RPO of about a minute

# nightly:   wal-g backup-push $PGDATA
# restore to one second before the mistake:
#   wal-g backup-fetch $PGDATA LATEST
#   recovery_target_time = '2026-10-23 19:59:59'
#   restore_command = 'wal-g wal-fetch %f %p'

In a system design interview

State the objectives and match each failure to a mechanism: “Payments need an RPO of zero and an RTO of minutes, so the ledger database has a synchronous standby in a second zone and an async one in another region. For mistakes and corruption, base backups go to object storage nightly with continuous log archiving, kept 30 days in a separate account, so we can restore to any second. We run a restore drill every month.” Expect the follow-ups: split brain during failover, the latency of synchronous replication, and how backups handle data deletion requests for privacy.

Read the guide to backups and disaster recovery or multi-region design, see database failover and the write-ahead log, or watch a strong candidate design a payment system.

Questions people ask