The problem: a script deletes the wrong links
A Snip engineer runs a clean-up script meant to delete expired links for one customer. A bug in its filter deletes 2.3 million live links across 900 customers. The delete is committed, replicated to every replica in seconds, and removed from the cache. Replication did its job perfectly: every copy now faithfully lacks the links.
Replicas copy mistakes. Only a copy taken before the mistake, kept where the mistake couldn't reach, can undo it. That's what backups are for.
Two numbers to decide first
- RPO (recovery point objective): how much recent data you can afford to lose. "No more than 5 minutes of changes."
- RTO (recovery time objective): how long you can afford to be without the data. "Restored within 4 hours."
They drive everything else. Nightly backups give an RPO of up to 24 hours. Restoring a 2 TB database from object storage takes hours, which sets a floor on RTO.
Point-in-time recovery
Postgres's approach (and most databases have an equivalent): take a base backup, a full copy of the data files, regularly, and archive every write-ahead log segment as it fills (04.06 Write-ahead logs and crash recovery). To recover, restore the base backup and replay the archived log up to any moment you choose: 14:31:59, one second before the script ran.
That gives an RPO of minutes (how often log segments are archived) and lets you recover to just before a mistake, not just to last night.
For Snip's accident, a full restore would also roll back every legitimate change since 14:32. So the usual move is to restore to a separate database at 14:31:59 and copy just the deleted links back into production.
Keep backups out of reach
Backups protect only if the thing that destroyed production can't destroy them too:
- Separate account and credentials. A compromised or mistaken production credential shouldn't be able to delete backups.
- Immutable or versioned storage with retention locks, so backups can't be deleted early, even by an admin.
- Another region, against losing a whole region (14.08 Multi-region and disaster recovery).
- Checksums on every backup file, verified (09.06 Checksums and silent corruption).
Test the restore
A backup you've never restored is a hope, not a backup. Restore regularly into a scratch environment, automatically, check row counts and a few known records, and time it: that measured time is your real RTO. Postgres's own documentation tells you to set up and test WAL archiving before taking your first base backup.
Also test the shape of restore you'll need. Restoring a whole database is one thing; restoring 900 customers' links from it, while the other customers keep working, is a different procedure, and it should exist before the day you need it.
Seen in production
Atlassian
2022April 2022 outage
A script deleted 883 sites, belonging to 775 customers, in about 23 minutes. Atlassian could recover a single site, but had not built capabilities and processes for restoring a large batch of sites with their interconnected products. The outage lasted up to 14 days for some customers; no customer lost more than five minutes of data. Atlassian wrote that it met its RPO but missed its RTO, and its lessons included universal soft deletes and automating restoration for large, multi-site deletion events.
What we foundBackups met the recovery point, but restoring hundreds of customers at once had never been built, so recovery took up to 14 days.
Post-Incident Review on the Atlassian April 2022 outageatlassian.com ↗PostgreSQL
DocsContinuous archiving and PITR
Combining a file-system-level backup with archived WAL files lets you restore the backup and replay the WAL, recovering the database to its state at any time since the base backup. It needs a continuous sequence of archived WAL files back to the backup's start, and the documentation advises setting up and testing WAL archiving before taking the first base backup. Logical dumps from pg_dump can't be used for continuous archiving.
What we foundA file-system backup plus archived WAL restores the database to any moment since the backup.
Continuous Archiving and Point-in-Time Recovery (PostgreSQL documentation)postgresql.org ↗Snip runs synchronous replication to a standby in another zone. A manager says 'so we don't need backups'. What's the flaw?
Recap
Interview questions
How would you design backups for a production database? What are RPO and RTO?