The restore you have never tested

A backup you have not restored is a hypothesis

That is the whole lesson, and the rest is detail. You do not have backups. You have files you believe are backups, and belief is untested until the day it is tested for you.

What makes a copy a real backup

The usual advice is 3-2-1: three copies, two kinds of media, one off-site. For a small project the part that actually matters is narrower:

At least one copy must live outside the blast radius of the thing it protects. If the same credentials that can drop your database can also delete the bucket holding its backups, then one compromised key, or one confident terraform destroy, ends the company. A different account, ideally a different provider, with write-only or append-only access from the app side.

Two words worth knowing

RPO — how much data you can afford to lose. A nightly dump means your RPO is 24 hours. If the disaster lands at 15:30 and the dump ran at 02:00, thirteen and a half hours are gone.

RTO — how long you can be down. A 40GB dump does not restore in five minutes. It can take one to three hours, plus index rebuilds, plus the time spent finding the file and remembering the command.

Write both numbers down. If your honest RPO is "an hour" and you have nightly dumps, you have not made a decision — you have avoided looking.

Point-in-time recovery is the alternative: the database keeps its write-ahead log, so you can restore to 15:29:58, one second before the bad migration. Managed Postgres usually offers around 7 days of PITR on paid plans and often none on free ones. Go and check today which you have. "The provider handles it" is not an answer; the retention number is.

The drill

Put it on a calendar. Quarterly is plenty for a small project.

1. 1Take the real backup file, from where it really lives.
2. 2Restore into a fresh, empty database.
3. 3Point a copy of the app at it and open a page.
4. 4Check specific rows: your own account, the newest row in the busiest table.
5. 5Time the whole thing and write the number down. That is your RTO, and it is usually two to five times what you guessed.

Every team that does this for the first time finds at least one of these:

• The dump has been zero bytes since a flag changed in March.
• The cron ran fine, the upload failed silently, and nobody checked the exit status.
• pg_dump from version 16 refuses to restore into version 15.
• The backup is encrypted and the key lived on a server that no longer exists.
• Nobody actually knows the restore command.

What is not a backup

• A read replica. It faithfully replicates your DELETE FROM users in under a second.
• Provider redundancy or RAID. Protects against a disk dying. Offers nothing against you.
• Soft deletes. Protects against a user clicking the wrong thing. Offers nothing against a migration.

All three protect against hardware. Almost nothing that destroys a small project's data is hardware.

Enough, and cheap

A nightly pg_dump | gzip to a bucket at a different provider, thirty days of retention, and — this is the part people skip — an alert when today's file is missing or smaller than a plausible threshold. "Backup job succeeded" logs lie; file size does not. Add the quarterly restore drill. Storage costs a few cents to a few dollars a month.

That is a real backup system, and it fits in about fifteen lines of shell.