Backups and disaster recovery

A backup you have never restored is not a backup. It is a file you are hoping about, and the day you find out whether the hope was justified is the worst possible day to find out.

This lesson uses one real system as its example — this site's own database backup — because a designed backup is more instructive than a described one, and because it has a documented gap worth learning from.

#Two numbers decide everything

Before choosing a mechanism, agree two numbers with whoever owns the business:

• RPO — recovery point objective. How much data may you lose? "Everything since last night" is an RPO of 24 hours. "Nothing" is an RPO of zero, and it costs a great deal.
• RTO — recovery time objective. How long may you be down while you restore? Minutes, hours, a day?

Everything else follows from those. A nightly dump gives an RPO of up to 24 hours — fine for a learning site, unacceptable for a payment ledger. Streaming replication to a standby gives an RPO of seconds and a much lower RTO, and costs a second database running all the time.

The mistake is choosing the mechanism first and discovering the RPO after the incident. "We back up nightly" is a sentence that means we accepted losing up to a day of orders, and somebody should have agreed to that on purpose.

#A backup that is designed, not just scheduled

This site's database is backed up by a small script on a systemd timer. Its design choices are worth reading as a checklist, because each one closes a specific failure:

• Every night, pg_dump --format=custom. The custom format is compressed and restorable selectively — one table, not only the whole database.
• Verified by listing the dump's table of contents before anything else happens. A dump that ran but produced a truncated or empty file is the most common silent failure. Checking the contents is what distinguishes "the job ran" from "the backup is usable".
• Old dumps are removed only after the new one is verified. The obvious ordering — prune, then dump — means a failing dump quietly leaves you with fewer backups each night until there are none.
• The password goes through a temporary PGPASSFILE, never the command line, because a command line is visible to every user on the host through the process list.
• Fourteen days of retention, because the corruption you discover today may have happened last week, and a backup of already-corrupted data is not a recovery.

#The gap: a backup on the same machine

That script writes its dumps to the same host as the database. Its documentation is explicit about the consequence: an off-host copy to S3 happens only when a credentials file exists, and until it does, the journal says off-host copy skipped.

That is worth dwelling on, because it is the most common real gap in small production systems. A backup on the same disk protects you from a bad query and not from losing the machine. Disk failure, a deleted volume, a compromised host, a provider problem — every one of those takes the database and its backups together.

The rule has a name, and it is a good one to be able to recite:

3-2-1: three copies of the data, on two different kinds of storage, with one of them off-site.

The off-host design here is also worth copying for how it limits damage. The upload credentials belong to a dedicated IAM user with permission to put and delete objects and no permission to list the bucket — so a leaked key cannot enumerate what is there, which is also why old copies are pruned by exact name. That is the least-privilege principle from the security course, applied to a backup.

#Restore is the product

Nobody needs backups. Everybody needs restores. So the restore procedure is the thing to design, write down, and rehearse.

This site's documented restore goes into a new database first, never the live one:

createdb -U code10x code10x_restore
pg_restore -U code10x -d code10x_restore --no-owner --no-privileges latest.dump

Then point a copy of the application at it, or check the migration state, before deciding anything about the live database. Restoring straight over production during an incident is how a recoverable situation becomes an unrecoverable one — if the backup turns out to be bad, you have now destroyed the only copy you were not sure about.

Rehearse it on a schedule. A restore drill answers questions you cannot answer any other way:

• Does the backup actually restore?
• How long does it take at today's data size — and is that inside your RTO?
• Does the person on call know the commands, or only the person who wrote them?
• What is missing — a secret, an extension, a sequence value, the files in object storage the rows point at?

That last one catches almost everybody: the database restores perfectly and every uploaded file is gone, because the files lived in a bucket nobody backed up.

#Disaster recovery is more than the database

A full recovery needs everything the service needs to run, and the database is only one entry:

The practical test for an entire DR plan is blunt: could someone who did not build this system restore it from nothing, using only what is written down? If the answer depends on one person remembering something, that person is the single point of failure.