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

prod dbnightly dumprestore test
a backup is only real once it has been restored

key terms

dump

A full copy of the database written to a file you can ship anywhere.

pg_dump mydb > backup.sql

WAL

Write-ahead log: every change is journalled before applied. It enables point-in-time restore.

restore to 09:42, pre-disaster

migration

A versioned script that changes the schema, applied in order, recorded so it never runs twice.

007_add_slug.up.sql

RPO

Recovery point objective: how much data you can afford to lose, measured in time. It sets your backup frequency.

RPO 5 min means frequent WAL

how it works

  1. 1
    Version every change

    Each schema change is a numbered file, committed alongside the code that needs it.

  2. 2
    Review like code

    Migrations go through pull requests. A teammate reading your ALTER TABLE has saved many weekends.

  3. 3
    Apply in order

    A migration tool runs pending files once, in sequence, and records what it did. Never by hand.

  4. 4
    Backup before risky steps

    Any destructive migration runs only after a fresh, verified dump exists.

  5. 5
    Rehearse 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

1pg_dump mydb > backup_monday.sql
the copy you can actually restore
2-- migrations are code: review them
3ALTER TABLE posts ADD COLUMN slug text;
small and additive: easy to roll back
4CREATE UNIQUE INDEX ON posts (slug);
the new rule enforced by the database itself
5-- down: ALTER TABLE posts DROP COLUMN slug;

side by side

backups

  • protect the data itself
  • scheduled: daily dumps or WAL
  • restore must be rehearsed
  • untested means fictional
VS

migrations

  • protect the schema changes
  • versioned, reviewed, ordered
  • small steps, each reversible
  • never hand-edit production
1write migration2review the PR3apply in order4schema v2
a migration travels exactly like code
🪤 gotcha

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

🎤 interview

What is ACID?watch ↗

📚 tutorial

Never hand-edit a prod schemawatch ↗

🔧 useful

Rehearse on a copywatch ↗

💎 deep

The one-liner that saves careerswatch ↗

💎 deep

An untested backup is a hopewatch ↗