Backups and recovery

Tuesday, 3:00pm. A developer runs a small script to fix the price of one dish. A bug in the script drops the WHERE clause, and this runs on production:

UPDATE menu_items SET price_paise = 0;

Every dish on Tiffin, across 3,000 restaurants, is now free. Within 50 milliseconds, the change has replicated to every replica, including the synchronous standby. Every copy is wrong, perfectly in sync.

Everything this module has built so far protects against machines failing. None of it protects against the data itself being wrong. Replicas faithfully copy mistakes. Failover would just promote a replica with the same free biryani. The only thing that can help now is a copy of the data from before 3:00pm. That's a backup.

What protects against what

Different failures need different defences. It helps to lay them out:

Replicas and backups answer different questions. A replica answers "what does the data look like right now?" A backup answers "what did it look like at some moment in the past?" You need both.

RPO and RTO

Before choosing how to back up, decide what you need. Two numbers capture it:

RPO (recovery point objective) is how much recent data you can afford to lose, measured in time. RTO (recovery time objective) is how long you can afford to be down while recovering.

If you back up once a night at 2am and the database is destroyed at 3pm, you lose 13 hours of orders. Your RPO is effectively 24 hours, because the worst case is a failure just before the next backup. If restoring takes 3 hours, your RTO is 3 hours.

These are business decisions, not technical ones. Ask the business what an hour of lost orders costs, and what an hour of downtime costs. Smaller numbers cost more money and effort:

For Tiffin, the team agrees on an RPO of 1 minute and an RTO of 1 hour for a full restore.

Three kinds of backup

Logical dumps

A tool like pg_dump reads every table and writes out SQL statements, or a compact archive, that can recreate them. It's portable across versions and machines, and you can restore a single table from it.

But it's slow at scale. Dumping a few hundred gigabytes takes hours, and restoring is slower still, because the database has to rebuild every index from scratch. And it only captures one moment: the time the dump started.

Physical backups

A physical backup copies the database's actual data files, using pg_basebackup, a backup tool, or a disk snapshot from your cloud provider. Restoring means copying files back and starting the database, with no rebuilding of indexes. It's much faster for big databases, but it's tied to the same major database version.

Point-in-time recovery

Here's the important one. Remember the write-ahead log from the read replicas lesson: every change, in order. If you save a physical backup, and then also save every piece of WAL generated after it, you can rebuild the database as it was at any moment since:

1. Restore the base backup from, say, 2:00am.
2. Replay the archived WAL, change by change.
3. Stop at exactly 2:59:59pm, one second before the bad UPDATE.

This is called point-in-time recovery, or PITR. WAL is shipped to object storage every few seconds or minutes, so your RPO drops from a day to a minute or less. Tools like pgBackRest and WAL-G handle this for self-managed Postgres, and most managed databases do it automatically: you choose a time within the retention window and click restore.

Most teams use PITR as the main backup, plus an occasional logical dump as a second, independent copy in a different format.

How long will a restore take?

Here's the question that decides your real RTO, and it's usually answered optimistically. Tiffin's database is 800 GB.

download base backup:   800,000 MB / 300 MB/s ≈ 2,700 s ≈ 45 min
WAL since 2am backup:   13 hours x 15 GB/hour ≈ 200 GB to replay
replay at ~100 MB/s:    200,000 MB / 100 MB/s ≈ 2,000 s ≈ 33 min
checks and switching:   ~15 min
total:                  ~1.5 hours

That's over the 1-hour target. Two ways to fix it: take base backups more often (every 6 hours means at most 6 hours of WAL to replay), and use the cloud provider's disk snapshots, which can bring up a large volume much faster than downloading it. The point is that you only find this out by doing the maths, or better, by timing a real restore.

Undoing a mistake without losing the afternoon

Back to 3:00pm. It's now 3:20pm when someone notices. If you rewind the whole production database to 2:59:59pm, you lose 20 minutes of real orders placed after the mistake. Customers who ordered and paid would vanish.

So you usually don't restore over production. Instead:

1. Restore a backup to a new, separate database, as of 2:59:59pm.
2. Copy only the damaged data back into production.

-- restored is the recovered copy, attached via postgres_fdw or imported
UPDATE menu_items m
SET price_paise = r.price_paise
FROM restored.menu_items r
WHERE m.id = r.id;

3. Deal with the side effects: orders placed at a price of zero between 3:00 and 3:20pm need a decision from the business, not from SQL.

A delayed replica

Restoring 800 GB to find the prices from 20 minutes ago takes over an hour. Some teams keep one more replica that deliberately stays behind, say by 1 hour. In Postgres, that's the recovery_min_apply_delay setting. When someone notices a mistake within the hour, the delayed replica still has the old data, ready to read, in seconds. Pause its replay (pg_wal_replay_pause()), copy out what you need, and you're done.

Rules for backups that actually work

Keep copies apart. A common rule is 3-2-1: three copies of the data, on two different kinds of storage, with one in a different place. For Tiffin: the live database, PITR backups in object storage, and a weekly logical dump copied to a different region.

Protect them from yourself. If the same admin account that can drop the database can also delete the backups, so can an attacker who steals that account. Store backups in a separate cloud account, and use storage settings that block deletion for a fixed period (often called object lock or immutability).

Encrypt them. A backup is a full copy of every customer's name, address and phone number.

Decide retention. For example: PITR for the last 14 days, a daily snapshot kept for 35 days, a monthly one kept for a year. Longer isn't always better. Laws on deleting personal data apply to backups too.

Monitor them. Alert if a backup didn't run, if WAL archiving falls behind, or if a backup is suspiciously small.

Test restores. This is the one that matters most. In 2017, GitLab lost several hours of production data after an engineer accidentally deleted a database directory, and then discovered that several of their backup methods had quietly not been working. It's well known because GitLab wrote it up in public, and it's worth remembering because the pattern is so common. A backup you've never restored is a hope, not a backup.

Tiffin runs an automated restore every week: restore last night's backup plus WAL to a scratch database, run checks (row counts, the newest order's timestamp, a few known records), record how long it took, and delete it. That timing is the real RTO.