Backups and disaster recovery
A backup is only worth something on the day you restore it. This lesson shows you how to set targets for how much data you may lose and how long you may be down, which backups meet those targets, and how to bring a database back to the minute before someone's mistake.
By the end you can
- Explain RPO and RTO in plain words, and ask the business for both
- Choose between a logical dump and a physical backup with WAL archiving
- Restore a database to a chosen moment with point-in-time recovery
- Apply the 3-2-1 rule, encryption and retention to your own setup
- Write a restore-drill checklist and a short DR runbook
The everyday picture
A careful shop owner keeps his ledger safe in two ways. Every Friday he photocopies the whole ledger and keeps the copy at his brother's house in another part of the city. Every day he also writes a small diary: each sale, each delivery, each correction, in order, with the time.
One Tuesday a new helper spills tea over the ledger at 3 pm and tears out the ruined pages. The owner does not panic. He takes Friday's photocopy, then reads the diary from Friday morning and redoes every entry up to 2:59 pm, one minute before the spill. The ledger is complete again, and the spill never happened.
That is Postgres point-in-time recovery. The photocopy is a base backup. The diary is the WAL. And the brother's house matters too: if the shop burns down, a copy kept in the same shop burns with it.
Two numbers to agree on first: RPO and RTO
Before you pick any tool, agree on two targets with the business. They are business decisions, so a PM is the right person to ask for them.
- RPO, the recovery point objective: how much recent data may we lose? "At most 5 minutes of sales" is an RPO of 5 minutes. A nightly backup alone gives an RPO of up to 24 hours: a crash at 11 pm loses the whole day.
- RTO, the recovery time objective: how long may we be down? "POS sync back within 1 hour" is an RTO of 1 hour. It includes noticing the problem, deciding, restoring, checking and switching the app over.
Smaller numbers cost more. An RPO of seconds needs continuous WAL archiving, or a replica. An RTO of minutes needs a replica ready to promote (Replication and read replicas). Restoring a 500 GB backup from cloud storage can take hours on its own. So the honest answer is often different for different failures. "If the server dies: RPO under 1 minute, RTO 15 minutes, by promoting the replica. If someone deletes data: RPO under 5 minutes, RTO 2 hours, by PITR."
Write down which disaster each number covers. A disk failure, a deleted table, a bad migration, a lost cloud account, ransomware and a data centre fire each need a different plan.
Quick check
The business says "If the server dies at 8 pm, we may lose at most 5 minutes of sales, and the tills must sync again by 9 pm." Which targets are those?
RPO is how much recent data may be lost, here 5 minutes of sales. RTO is how long you may be down, here from 8 pm to 9 pm. They are easy to swap: "point" is the point in time you go back to, "time" is the time until you are running again.