Backing up a database small enough to fit on a laptop, and still getting it wrong

The database was small. A few hundred megabytes, easily small enough to fit on a laptop, small enough that “set up a proper backup strategy” felt like a disproportionate amount of ceremony for the actual size of the thing. That feeling turned out to have nothing to do with whether losing it would matter.

What “backups” actually meant

The setup was a cron job, once a day, running pg_dump and writing the output to a file on the same VPS the database lived on. It had been running for a long time without incident, which felt like proof it worked. Nobody had actually tried to restore from one of those files, not once, in all that time.

#!/bin/sh
pg_dump -Fc mydb > /var/backups/mydb-$(date +\%Y-\%m-\%d).dump
find /var/backups -name '*.dump' -mtime +14 -delete

This is a completely reasonable-looking script. It’s also two separate mistakes stacked on top of each other, and neither one announces itself.

Mistake one: the backup lived next to the thing it was backing up

Every dump file sat on the same disk as the live database. That means a single disk failure, or anything that took the VPS itself down, would take out the database and every single backup at the same moment. The backup existed to protect against exactly the kind of failure it was equally vulnerable to. It protected against a much narrower thing, accidentally dropping a table or a bad migration, and looked, from a glance at the cron log, like it protected against everything.

Mistake two: nobody had checked that a restore actually works

This is the one that actually bit. Real data got corrupted, a bad migration that ran further than intended, months after this setup had been running. Restoring should have been routine at that point: grab the latest dump, run pg_restore, done. Instead, the most recent usable dump file was three weeks old. The restore script, a separate piece of automation meant to periodically verify a dump could actually be loaded, had been silently failing, quietly, for weeks, because a Postgres version bump on the VPS had changed a flag pg_restore expected, and nothing was watching that job’s exit code. The daily dump job kept running and kept “succeeding,” because writing the file out is a different operation from the file being valid and restorable, and only one of those two things was actually being checked.

The fix: monitor the restore, not just the dump

The actual fix wasn’t a fancier backup tool, it was closing the gap between “a file got written” and “the file is known to be good”:

• Ship the dump somewhere other than the same disk, object storage or a second machine, so a single disk or VPS failure doesn’t take out the database and its backups together.
• Alert on the backup job’s exit code and file size, not just its existence, a silently truncated or empty dump file passes a “does the file exist” check just as easily as a good one.
• Periodically actually restore a dump into a scratch database and run a basic sanity check against it, on a schedule, as its own monitored job, not as a manual step someone remembers to do “sometime.”

That last one is the part that would have caught the real incident weeks before it mattered, because the corrupted-restore-script problem would have shown up as a failing scheduled job with an alert attached, instead of showing up as “the data is gone and today, for the first time, we’re finding out whether the backups actually work.”

Why the size of the database was never the point

It’s easy to reason “this database is small, backing it up properly can wait,” and that reasoning isn’t even wrong about the size. It’s wrong about what actually determines whether a backup strategy matters: not how big the data is, but how bad it is to lose it and how long you’d be down finding that out. A tiny database that’s genuinely load-bearing for a real product deserves exactly the same restore discipline as a huge one, because the pain of losing it doesn’t scale down just because the file does.

A backup you haven’t restored from isn’t a backup, it’s a belief about a backup, and the only way to know the difference is to actually try the restore before you need it, not after.