Backups and recovery
On 31 January 2017, late at night and after a long incident, a GitLab engineer ran a command to clear a database directory, on what they believed was a broken replica. It was the primary. Then the team went looking for backups and found that several of their backup methods had not been working. They recovered from a copy taken about six hours earlier, lost six hours of data, and published one of the most honest postmortems in the industry.
The lesson they shared is the one this chapter is built on: a backup you have never restored is not a backup, it is a hope. Replication keeps you running when a machine dies. Backups are what save you from people, from bad deploys and from bugs, which are far more common.
You take a full backup every night at 2am. At 4pm a bad migration corrupts the orders table. How much data do you lose if you restore the backup? How could you lose almost none?
Two numbers that define the plan#
RPO, recovery point objective: how much data you can afford to lose, measured in time. "We can lose at most five minutes of orders."
RTO, recovery time objective: how long you can afford to be down while restoring. "Orders must work again within one hour."
Everything else, which tools, how often, where the copies live, is chosen to meet those two numbers. Ask the business for them before designing anything, because the honest answer for payments and the honest answer for an internal wiki are very different.
Nightly backups alone mean losing up to a day. Archiving the write ahead log every minute turns that into about a minute, which is what point in time recovery is. Restore time grows with database size, which is why large databases are restored from disk snapshots or kept as a delayed replica rather than copied file by file.
Logical and physical backups#
Logical backups export the data as SQL statements or a portable dump: pg_dump for Postgres,
mysqldump for MySQL. Readable, portable across versions, and good for copying one table or a
small database. For hundreds of gigabytes they are slow to take and much slower to restore,
because every row is inserted again and every index rebuilt.
Physical backups copy the database's actual files: pg_basebackup for Postgres, Percona
XtraBackup for MySQL, or a snapshot of the disk in the cloud. Much faster to restore at size, tied
to the same database version. Managed services such as RDS do this for you.
Most production setups use physical backups for recovery and occasional logical dumps for migrations and for pulling out a single table.
Point in time recovery#
Here is the answer to the question at the top. A nightly backup alone loses up to a day. But the database already writes every change to its write ahead log. Archive that log continuously to safe storage and you can rebuild the database as it was at any second.
Where the copies live#
A backup stored on the same server, or in the same cloud account, can be lost in the same incident. The old 3-2-1 rule still holds: three copies of the data, on two different kinds of storage, with one copy somewhere else entirely, such as another region and another account with separate credentials. Encrypt backups, and make at least one copy immutable for a retention period, so that an attacker, or a script with the wrong permissions, cannot delete them.
Test restores, automatically#
The GitLab story is not unusual. Backup jobs fail silently, dumps are written empty, credentials expire, formats drift. The only real test is restoring.
- Restore automatically on a schedule, weekly at least, onto a scratch server.
- Check the restored data, not just that the command exited: row counts, the latest timestamp, a few known records.
- Time it. That measured number is your real RTO, not the estimate.
- Alert when a backup or a restore test fails, and when one has not run.
| Choice | What you gain | What you pay | Pick it when |
|---|---|---|---|
| Nightly logical dump | Simple, portable and readable. | Up to a day of data lost, and very slow to restore at size. | Small databases and non critical data. |
| Physical backups with WAL archiving | Recovery to any second, and fast restores of large databases. | More moving parts, and archives to store and monitor. | Any production database you care about. Managed services do this by default. |
| A delayed replica | A copy deliberately an hour behind, ready to promote before a mistake reaches it. | Another server to run, and it only helps if you notice within the delay. | Large databases where a full restore takes too long. |
Checkpoint#
1. Nightly backups at 2am, bad migration at 4pm. How do you lose almost no data?
2. What is RPO?
3. Your backup job has reported success every night for a year. What do you still not know?
- RPO is data you can lose, RTO is time you can be down. Get both from the business.
- Base backup plus archived WAL = point in time recovery, to any second.
- Replicas are not backups. They copy mistakes instantly.
- 3-2-1, one copy immutable and off-account. Test restores automatically.
I start from two numbers: RPO, how much data we can afford to lose, and RTO, how long we can be down. Replication handles a dead machine, but mistakes like a bad migration reach every replica instantly, so recovery comes from backups. I would take physical base backups and archive the write ahead log continuously, which gives point in time recovery: restore the base onto a new server and replay the log to the second before the mistake. Copies follow the 3-2-1 rule, encrypted, with one immutable copy in a separate account. And I would restore automatically every week, verify the data, and time it, because an untested backup is a hope, which is exactly what GitLab learned in 2017.