Module 5 · Lesson 5 of 5
Backups and point-in-time recovery
The backup you haven't restored isn't a backup.
On a Tuesday afternoon, a developer runs a cleanup script against what they believe is staging. It was production. By 3:14 pm, the orders table is empty, and the replicas, faithful as always, emptied themselves a few milliseconds later. Failover can't help and neither can replicas. The only thing between Gatepass and a very bad week is the backup, if one exists and if it works.
What replicas and failover don't protect against
Everything so far protects against a machine dying. None of it protects against the data being wrong:
- a bad
DELETEorUPDATE, or a migration that drops a column; - a bug that quietly corrupts rows for three days;
- ransomware or a compromised credential;
- an entire region or cloud account being lost;
- someone deleting the database instance.
Replication copies the mistake. A backup is a separate, older copy that mistakes can't reach. They solve different problems, and you need both.
Two kinds of backup
Snapshots (full backups). A copy of the whole database as of one moment. A nightly snapshot of a 200 GB database is easy to store and easy to restore. The weakness is the gap: restore last night's snapshot and every order since then is lost.
Continuous log archiving. The database already writes every change to a log (the write-ahead log in Postgres). Ship those log segments to storage as they're produced, typically every minute or so. Now you hold a base snapshot plus an unbroken stream of changes after it.
Together they give you point-in-time recovery (PITR): restore the snapshot, then replay the log up to any chosen moment, such as 3:13:59 pm, one second before the script ran.
RPO and RTO
Two numbers define what you need. Agree on them with the business before choosing tools.
- RPO (recovery point objective): how much data you can afford to lose, measured in time. A nightly snapshot has an RPO of up to 24 hours. Log archiving every minute gives about 1 minute. Synchronous replication to another region gives close to zero.
- RTO (recovery time objective): how long you can be down. Restoring 200 GB and replaying a day of logs may take 30 to 90 minutes. A warm standby takes seconds.
Tighter targets cost more. For Gatepass, orders and payments might need RPO under a minute and RTO under an hour. The marketing database can take RPO of a day and RTO of a day.
The backup you haven't restored isn't a backup. It's a hope.
The 3-2-1 idea
A classic rule: keep 3 copies of the data, on 2 different kinds of storage, with 1 offsite. For a cloud database translate it as:
- the live database plus its replicas (copy one);
- automated snapshots and log archive in object storage (copy two);
- a copy in another region and, ideally, another account, with delete protection or object lock so a compromised credential cannot erase it (copy three).
Also encrypt backups and control who can read them. A backup holds all the data the database does, and it often has weaker access controls.
Testing restores
Backups fail silently. Real incidents involve backups that were never configured on the new database, that ran but were empty, that were encrypted with a lost key, or that took 14 hours to restore when the business had four.
Make restores routine:
- Restore automatically every week into a scratch instance, run a few sanity queries (row counts, the latest order timestamp) and alert if they fail.
- Time it. The measured restore time is your real RTO, whatever the plan says.
- Practise the human parts. Who decides? Where are the runbook and the credentials? Run a game day once or twice a year where the team restores to a chosen point without help.
- Monitor the backup itself. Alert if the newest snapshot or log segment is older than your RPO.
Recovering from the bad script
For the 3:14 pm incident, the plan reads:
- Stop the damage: revoke the script's access, put the app in read-only or maintenance mode.
- Pick the target time, 3:13:59 pm, from the audit log.
- Restore the last snapshot and replay logs to that time on a fresh instance. About 40 minutes.
- Compare: export the lost
ordersrows from the restored instance and load them into production, then reconcile against payment-provider records for the orders placed in the gap. - Afterwards, fix the cause: separate credentials for staging and production, a confirmation on destructive scripts, and read-only access by default.
Check yourself
Gatepass takes a snapshot at 02:00 and archives its change log every minute. At 15:14 a script deletes the orders table. What's the best recovery?
Your turn
Write the backup plan for Gatepass's orders database: the RPO and RTO you'd choose, what you'd run, where copies live, and how you'd know it works.
Key takeaways
- Replicas and failover protect against hardware failure. Only backups protect against bad data, bugs and attackers.
- Snapshots plus archived logs give point-in-time recovery to any moment, such as just before a bad script.
- Choose RPO (data you can lose) and RTO (time you can be down) first. Tighter targets cost more.
- Keep copies in another region and account, encrypted and protected from deletion.
- Restore regularly and time it. An untested backup is only a hope.
Finished reading?
Mark it complete to tick it off in the course.