Backups

The problem

"Design backups for our orders database." The shop's database holds 2 TB and already has two replicas and a standby in another region, so the first question is why it needs anything more. A restore must bring back data lost to a crash, a bug or an attacker, as it was at any second before the damage. Self-service restores for customers and backups of employees' laptops are separate systems to park.

The numbers

Start with the two numbers from disaster recovery. The business sets an RPO of 1 minute (lose at most a minute of orders) and an RTO of 1 hour (back within an hour). Then check that a restore can meet the RTO, say copying at about 1 GB a second and replaying the log at about 100 MB a second:

step                                    time
copy the 2 TB snapshot (2,000 GB)       2,000 s, about 33 min
replay at most a day of log (100 GB)    1,000 s, about 17 min
total at worst, before checks           3,000 s, 50 min
the RTO                                 3,600 s, 1 hour

That leaves about 10 minutes to start the server and check the data. A database that grows, or a slower copy, breaks the RTO first: the table tells you when restores must get faster.

Replicas copy mistakes

At 14:00:00 a bad deploy runs DROP TABLE orders. Within a second both replicas have dropped it too, and the standby in region B about 2 seconds later: a replica does whatever the database does, mistakes included. Replicas and standby regions protect you from a dead machine or a dead region, never from a mistake. Only a copy from before the mistake, kept apart from the live data, brings the table back: a backup.

Snapshots plus the log

Every change the database makes is also written to its log, about 100 GB a day here. Keep two things: a full snapshot each night, and the log, copied to the backups every few seconds. To restore, load the snapshot and replay the log up to any second you choose: point-in-time recovery. The log's few seconds of delay is the RPO, well inside the minute, and the nightly snapshot caps the replay at a day.

Both alternatives fail a number. A full snapshot every minute can't even be made: each copy of 2 TB takes about 2,000 s, and 1,440 a day would be about 2.9 PB. The log alone, kept from the first day, makes a restore replay every change ever made: a year at 100 GB a day is 36.5 TB, about 4 days of replay.

The restore: up to one second before

1. Start a new server from the 02:00 snapshot, beside the live database.
2. Replay the log on it, from 02:00 up to 13:59:59, one second before the DROP.
3. Check the restored orders: the count, and the last ones before 14:00.
4. Copy the orders table into the live database.

Never restore over the live database: since 14:00 it has taken other changes, like new customers, that a restore to 13:59:59 would wipe out. The log also tells you the exact moment: the DROP is one entry in it, with its time.

Backups an attacker can't delete

Backups in the same cloud account as the database share its weak points. An attacker with an admin's keys often goes for the backups first, so that only a ransom brings the data back, and a cleanup script with a bug can delete them by accident. Copies in three regions of the same account don't help: distance stops a disaster, not a delete. Nor does a rule that only the backup job may delete: stolen admin keys can change the rule first.

So keep the backups in a separate account, in another region, and lock each one against deletes and changes for a set time, say 30 days. Most object stores offer such a lock (write once, read many), and while it runs not even that account's admins can remove a backup.

Restore tests

A backup that has never been restored is a hope. A job can report success every night while saving the wrong thing: a new database it doesn't cover, a gap in the log, files encrypted with a key nobody can find. Restore one every week to a test server, automatically: check the row counts and a few known orders, time the whole restore against the RTO, and alert when a test fails. The real restore is then never the first one.

Same pattern as Multi-Region and Disaster Recovery

The method carries over: agree on an RPO and an RTO first, keep a copy far from the database's region, and practice the recovery, as failover drills practice a failover. What changes is the threat. A standby region guards against losing a region; backups guard against the live copies themselves going wrong, through a mistake, a bug or an attacker.

Summary

• Replicas aren't backups: they copy a DROP TABLE within seconds.
• Numbers: RPO 1 minute, RTO 1 hour; copying 2 TB back takes about 2,000 s.
• What to keep: a nightly snapshot plus the log, copied every few seconds.
• Restores: the snapshot, then the log to one second before the mistake, beside the live database.
• Where: another account and region, locked for 30 days.
• Tests: a restore every week, checked and timed.