Backups and Restore Runbook
Nobody has ever needed a backup. People need restores. A backup is a file you hope contains your data; a restore is the proof. Plenty of businesses have discovered, on the worst day of their year, that they owned three years of nightly backups and zero working restores: the dump was empty, the credentials had rotated, the one person who knew the procedure had left. The backup job ran green every night. Green meant "a file was written," not "your data is safe," and nobody had ever checked which.
That is why this template is a runbook and not a script. The script is the easy part, and your agent will write it in five minutes. The runbook is the part that makes it real: what gets backed up, on what schedule, kept how long, stored where, and, as a first-class scheduled step and not a someday, the drill where you restore a backup onto a scratch database and verify the data is actually in it. If the drill isn't on a calendar, it doesn't exist. You will not spontaneously test restores on a quiet Tuesday. Nobody does.
Two numbers anchor everything, and you should pick them before any tooling: how much data you can afford to lose (if the answer is "a day," daily backups are fine; if it's "an hour," you need more than dumps), and how long you can afford to be down while restoring. Everything else in the runbook is machinery serving those two numbers. Fill it in with your agent, then put the drill dates on your actual calendar before you close the tab.
Prerequisites
- A database you can reach with admin credentials (managed Postgres, Supabase, SQLite file, or self-hosted).
- A storage destination that is not the same provider or machine as the database: S3, Backblaze B2, or similar.
# Backup and restore runbook: [project name]
Last drill passed: [date; if this is blank or older than the drill
schedule, this runbook is expired and the backups are unverified]
## The two numbers
- Acceptable data loss (RPO): [e.g. 24 hours. This sets backup frequency.]
- Acceptable downtime to restore (RTO): [e.g. 4 hours. The drill must
prove restore fits inside this, or the number is fiction.]
## What gets backed up
| Data | Where it lives | Method | Covered? |
|---|---|---|---|
| [main database] | [provider/host] | [pg_dump nightly + provider PITR] | yes |
| [uploaded files] | [S3 bucket / disk] | [versioning + replication] | [..] |
| [env vars / secrets] | [Vercel, host] | [encrypted copy in password manager] | [..] |
| [code] | GitHub | [it IS the backup, plus releases] | yes |
Anything not in this table is not backed up. Write that sentence somewhere
you will believe it. Databases are what people remember; uploaded files and
secrets are what people lose.
## Schedule and retention
- Nightly [02:00 UTC]: full `pg_dump`, compressed, encrypted, uploaded to
[bucket]. Filename: `[project]-YYYY-MM-DD.dump.gz.age`
- Retention: keep 7 daily, 4 weekly, 12 monthly. Prune automatically.
(Corruption discovered late needs an old-enough backup to predate it.)
- Storage rules: different provider than the database, so one account
compromise or billing failure cannot take both. Encrypted at rest;
a backup bucket is your whole database in one downloadable file.
- The backup job pings a healthcheck on success (dead-man's switch).
Silent stoppage is the most common backup failure. Alert on absence.
## Restore drill: every [month], scheduled on a real calendar
Do this by hand, from this document, without asking the agent to remember
the steps for you. The drill tests the document as much as the backup.
1. Pick yesterday's backup file from [bucket]. Note its size; a sudden
drop vs. last month is itself a finding.
2. Download and decrypt it using ONLY credentials from [password manager].
(This step catches rotated keys while it's a drill, not an outage.)
3. Create a scratch database: [command / provider console steps].
4. Restore into scratch: [pg_restore command with flags spelled out].
5. Verify contents, not just exit codes:
- [ ] Row counts within expectations: [table]: [~N], [table]: [~N]
- [ ] Newest record in [critical table] is dated within the last [24h]
- [ ] Spot-check one real record you know: [which one]
6. Point a local copy of the app at scratch; log in; click the money path.
7. Record below: date, backup file, time-to-restore, pass/fail, surprises.
8. Destroy the scratch database. It contains production data (§6.5); it
does not get to linger.
Time-to-restore vs. RTO: [drill result] vs. [target]. If restore took
longer than RTO, that is a failed drill even though the data was fine.
## Drill log
| Date | Backup file | Time to restore | Result | Notes |
|---|---|---|---|---|
| [..] | [..] | [..] | [..] | [..] |
## Real-incident procedure
1. Stop writes (maintenance mode / pause the app). Restoring under
incoming writes turns one incident into two.
2. Identify the last good backup: newest is not automatically best if the
incident is corruption or a bad migration rather than loss.
3. Restore to a NEW database, verify with the drill's step 5, then switch
the app's connection string. Never restore over the only copy you have.
4. Afterward, write down what the drill hadn't prepared you for, and
update this runbook the same day.Adaptation notes:
- Managed Postgres (Supabase, Neon, RDS) gives you point-in-time recovery, which is excellent and not sufficient: it lives in the same account as the database. Your own dumps to a second provider cover the account-level failures (billing lapse, compromise, provider exit). Run both.
- SQLite: replace pg_dump with a proper
.backupor Litestream replication; copying the raw file mid-write can capture a corrupt state. The drill is unchanged: restore the copy elsewhere and open it. - Solo on a hobby project, monthly drills may be more ceremony than the data warrants. Quarterly is defensible. "Never" is a decision to lose the data; make it on purpose or not at all.
- The mistake: verifying the restore by checking that the command exited zero. Empty databases restore flawlessly. Step 5 exists because the only question that matters is whether the rows are in there.
- The agent writes the backup script and the pruning; the drill stays human. On the day you need this runbook, you will be following it by hand under stress, and that is the condition it has to be tested in.