Backups & Migrations
Friends don’t let friends edit production tables raw.
▶ the lesson · narrated sketch video · tap for sound
the intro
Two disciplines keep databases safe over time. Backups: copies of your data you can actually restore, tested regularly. Migrations: versioned changes to the schema that are reviewed and applied in order. Together they make change safe.
fun fact ✦
The backup rule: an untested backup is not a backup, it is a hope.
the docs
the deep dive
the ideas behind the video — concepts, diagrams, a walkthrough and the traps to avoid
Two quiet disciplines separate hobby projects from systems people trust. Backups answer "what if we lose it"; migrations answer "what if we need to change it". Neither is glamorous, both are why senior engineers sleep at night, and both are mostly habits rather than tools.
habits, not heroics
key terms
dump
A full copy of the database written to a file you can ship anywhere.
pg_dump mydb > backup.sqlWAL
Write-ahead log: every change is journalled before applied. It enables point-in-time restore.
restore to 09:42, pre-disastermigration
A versioned script that changes the schema, applied in order, recorded so it never runs twice.
007_add_slug.up.sqlRPO
Recovery point objective: how much data you can afford to lose, measured in time. It sets your backup frequency.
RPO 5 min means frequent WALhow it works
- 1Version every change
Each schema change is a numbered file, committed alongside the code that needs it.
- 2Review like code
Migrations go through pull requests. A teammate reading your ALTER TABLE has saved many weekends.
- 3Apply in order
A migration tool runs pending files once, in sequence, and records what it did. Never by hand.
- 4Backup before risky steps
Any destructive migration runs only after a fresh, verified dump exists.
- 5Rehearse the restore
Quarterly, restore a backup into a scratch database. The drill is the deliverable.
the code, explained
a dump and a small, reversible migration
side by side
backups
- protect the data itself
- scheduled: daily dumps or WAL
- restore must be rehearsed
- untested means fictional
migrations
- protect the schema changes
- versioned, reviewed, ordered
- small steps, each reversible
- never hand-edit production
A backup you have never restored is a hope, not a backup. Dump files rot, credentials expire, and half-remembered restore steps fail at 3am. Schedule a rehearsed restore — the drill is the product.
quick answers
How often should I back up?
Your RPO decides. A blog can lose a day; a payments app cannot lose five minutes — that is what WAL archiving is for.
pg_dump or filesystem snapshots?
pg_dump is portable and version-proof. Snapshots restore faster for huge databases but drag the whole instance along.
Can I edit production by hand?
Look, yes; change, no. If it is not a reviewed migration, it did not happen — and it cannot be rolled back.
quick hits about Backups & Migrations
short answers · interviews, tips, trivia & hidden gems