Two copies, one mistake
A payroll service that pays 40,000 employees for 3,000 small businesses keeps its PostgreSQL primary paired with a standby in a second availability zone. When the primary's host died in the spring, the standby was promoted and pay runs were working again ninety seconds later. Then an engineer ran a cleanup script meant for a test schema against production: UPDATE employees SET bank_account = NULL. The standby received the change within the second, as it was built to. Both copies now agreed, exactly, on the wrong data, and pay day was two days away.
The standby was answering a different question. High availability (HA) keeps serving through component failure via redundancy: multiple instances, a standby replica to promote. Disaster recovery (DR) is for catastrophic loss: a region down, data corrupted or deleted, an attacker inside the account. You restore from backups stored elsewhere and, when a whole region is gone, fail over to another one. A replica replicates corruption just as fast as good data, so relying on replication alone for disaster recovery fails on precisely this kind of day. Replication & read replicas explains how the standby keeps up and what an asynchronous failover loses.
Two numbers, agreed before anything breaks
Two numbers drive the design. The recovery point objective (RPO) is how much data you can lose, measured in time: with an RPO of five minutes, a restore may miss at most the last five minutes of writes. The recovery time objective (RTO) is how fast you must be back, from the moment the disaster strikes to the moment the service works again.
For payroll, an hour of lost writes is a few hundred salary changes customers believe are saved, and a day down before pay day means people are not paid. The team settles on an RPO of five minutes and an RTO of four hours. Without numbers like these, nobody knows how much data loss or downtime is actually acceptable until the incident decides it, and every choice below either meets them or does not.
Full, incremental, snapshot
The oldest answer is a nightly full backup: a complete copy of the database. It is the simplest to restore, and it puts the RPO at up to a day: a disaster just before the next run loses nearly 24 hours of writes.
An incremental backup copies only what changed since the previous backup, so it is cheap enough to run every hour. A restore then needs the whole chain: the last full backup and every incremental after it, applied in order. Lose one link and everything after it is useless, so old backups have to expire as whole chains, never a full backup that later incrementals still depend on.
A snapshot is a point-in-time copy taken by the storage layer, a disk volume or a managed database's own snapshot feature. It is quick to take and usually stored as changed blocks underneath; for a running database spread over several disk volumes, the capture has to leave them in one consistent, recoverable state, through snapshots coordinated across the volumes or the database's own snapshot; volumes captured independently may not recover. None of the three closes the gap between runs. With snapshots alone, RPO is the snapshot interval: hourly snapshots can lose up to an hour, and the payroll team wanted five minutes.
Rewinding to the second before the script
Point-in-time restore needs log archiving between backups. PostgreSQL writes every change to its write-ahead log before it changes the data files (Write-Ahead Log explains why), so a base backup plus every log segment written since it holds every committed change. An archiving process keeps copying each finished segment to backup storage. With that in place, the bad UPDATE can be undone:
- An engineer finds when the script's transaction committed, from the database log and the script's own output.
- The restore job copies the last base backup taken before that moment onto a fresh, isolated server.
- The new server replays the archived log segments in order, applying every committed transaction.
- Replay stops just before the
UPDATE(in PostgreSQL,recovery_target_timeset to its commit time withrecovery_target_inclusive = off; the default also replays a transaction that commits exactly at the target). - The team identifies the rows the script blanked from the restored
employeestable and the audit trail, copies each account number back with anUPDATEthat applies only if the row has not changed since the script, and reconciles by hand any row that has.
The restore goes into an isolated environment, its own network with no route to production (Network topology: VPCs, NAT, VPN & bastions), never over production: rewinding production would throw away every legitimate write since the mistake, and a restored copy wired to production services could send real bank transfers.
Two numbers move. The RPO becomes the archive's lag: whatever log was not yet shipped when disaster struck is lost. PostgreSQL archives a segment when it fills, and archive_timeout forces a switch on a quiet database so a half-full segment cannot sit for hours; at 60 seconds the five-minute RPO holds only while the archive's lag stays under five minutes, so that lag is monitored and pages someone when it grows. The RTO now includes replay, which is not instant: a week of log since the last base backup can take hours to apply, so base backups run often enough that replay fits inside four hours. Tools such as pgBackRest and WAL-G handle the base backups, the archive and the restore.
The standby takes over when the primary's machine fails. The locked backups are what you rewind from, and a scheduled drill restores them away from production to prove they still work.
Out of reach of whoever breaks production
A backup in the same region as production can disappear in the same regional disaster, and one in the same account can disappear to the same attacker. Ransomware operators delete backups first, and production admin credentials let them. Backing up to the same region, account or provider as production means the disaster that takes out production can take out the backup too.
So the payroll backups are off-site, in object storage in another region, and immutable: under a separate account that production credentials cannot administer, with a write-once lock on every object. In S3 Object Lock's compliance mode, no user, the root user included, can delete or overwrite a locked object version until its retention period ends. That cuts both ways: a retention period set too long cannot be shortened, and is paid for until it runs out. Encryption needs the same care, because backups encrypted with a key that a compromised production account can disable or delete are only as safe as that key (Secrets management & encryption covers where keys should live).
A restore can bring back a person whose data was erased since; Deleting a person's data: erasure across every copy covers that.
A backup nobody has restored
An untested backup isn't a backup. The ways it fails are quiet: the archive command has been failing for three weeks since a permissions change, leaving a hole in the chain; a full restore takes eleven hours against an RTO of four; the key that encrypted last year's backups was deleted during a rotation. Each is found by a drill or during the disaster.
So the payroll team restore-tests on a schedule, as a job rather than a good intention. Every night it restores the latest backup to a recent timestamp in the isolated environment, runs a consistency check (the database's own checks, row counts against production, last month's pay-run totals), records how long it took, and pages someone when any step fails. The measured time is the floor of any RTO the team can actually promise: moving traffic and checking the service come on top of it. Rehearsing other failures on purpose, an instance killed or a region evacuated, is Chaos engineering & game days.
The database is not the only state: payslip PDFs in object storage are primary data and need their own backups, while the employee search index can be rebuilt from the restored database, if the rebuild fits inside the RTO.
When the whole region is gone
Cross-region backups survive the loss of a region, with an RTO no shorter than the time it takes to bring up servers there and replay the log, often hours. A replica promoted in the second region gets the RTO down to minutes, but it copies a bad UPDATE as faithfully as the standby next door, so it sits beside the backups, not in place of them. Moving traffic to the surviving copy is Failover, and which data may live in which region is Regional routing & geo-partitioning.
Draw the rewind
Open the Backup & Point-in-Time Restore challenge and draw the payroll team's DR path. Write the RPO and RTO first; then base backups plus a continuous WAL archive into object storage a compromised production account cannot delete, a restore to a chosen timestamp into an isolated environment, and a scheduled job that restores a backup, checks it and alerts when the check fails. Keep the standby replica off that path: it replicates the bad UPDATE within seconds. The HA side, promoting a standby when the primary fails, is what Leader-Follower Replication practises.