The restore worked perfectly and we still lost eleven days

I want to be clear at the start that we did the thing everybody tells you to do. We tested our restores. Monthly. There was a runbook, there was a calendar entry, there was a checkbox in the compliance spreadsheet, and a person — me, mostly — actually did it rather than ticking the box from the pub.

We lost eleven days of data anyway, and the restore test is the reason it took us so long to understand why.

The setup

Mid-size shop, a Postgres cluster that was the centre of everything, and a backup process that had grown the way they all do. Base backup nightly to a local staging directory, WAL archiving continuously, the whole thing synced offsite in the small hours. Written by somebody who’d left, maintained by whoever touched it last.

The monthly test was: pull the most recent backup set, restore it onto a spare box, bring the database up, run a script that counted rows in the twelve biggest tables and compared them against a stored expectation, check the app could connect. Green for nineteen months running.

Then a storage controller took the primary’s array down hard on a Wednesday afternoon, in a way that also ate the replica because — and this is its own story — both were on the same shelf. Fine. This is what backups are for. We were calm. I remember being calm, which in hindsight is the detail that bothers me.

The restore ran clean. Forty minutes. Database came up, WAL replayed, no errors, application connected. Somebody actually said “well, that was painless.”

Then support forwarded a customer asking why an invoice from last week had vanished.

Eleven days

The most recent data in the restored database was eleven days old.

Not corrupt. Not partial. Perfectly consistent, internally coherent, cleanly restored — and eleven days stale. Every write from the previous eleven days was simply not present, in a database that had been happily accepting them the whole time.

The first hour of that afternoon was spent not believing it. We restored again onto a different box in case something in the process was truncating. Same result. We went to the offsite copy. Same result. We started reading WAL segment names by hand.

Eleven days earlier, someone had done a maintenance operation on the primary — a version upgrade, planned, executed properly, with a change ticket and everything. As part of it, the data directory moved. Old path deprecated, new path adopted, the service unit updated, everything cut over correctly.

The backup script had the old path hardcoded.

Why nothing screamed

Here’s the part that still makes me tired.

The old data directory hadn’t been deleted. It sat there, complete, containing a perfectly valid database as of the moment of the cutover. Somebody had left it in place as a rollback safety net for the upgrade, which was a good instinct, and nobody deleted it afterwards, which is a completely normal thing not to do.

So every night the backup job ran, found a real data directory, produced a real base backup, and exited zero. The backup was not failing. It was succeeding, at backing up a frozen copy of the database as it existed eleven days ago.

The size of the backup didn’t change enough to trigger anything, because the database was large and eleven days of deltas were noise against the total. There was a monitor on backup job exit status: green. There was a monitor on backup file age: green, the file was written last night. There was a monitor on backup file size being non-zero: green.

Every check we had was checking the process, and the process was working. Nothing was checking the content, and the content had stopped moving.

And the monthly test?

The monthly restore test was scheduled for the following week.

But the test had also been passing for nineteen months on a design flaw I want to describe carefully, because it’s the actual lesson.

The verification script compared row counts to stored expectations — a set of numbers in a file, updated whenever somebody noticed they’d drifted. It was checking that the restored database looked like a plausible database. It never once compared the restored data to what production contained right now.

So even if we’d run the test on day three of the eleven, it would have passed. The database would have restored, the row counts would have been within tolerance of numbers somebody wrote down in March, and we’d have ticked the box.

We had tested that we could restore a database. We had never tested that we could restore the database.

The bit I got wrong personally

I want to own this rather than write it as though it happened to me.

I had looked at that backup script perhaps a year earlier, noticed the hardcoded path, and thought: that’s ugly, that should come from the config. I made a note. The note went into a backlog. It was never going to get prioritised, because “make a working backup script slightly less ugly” is not a story anyone picks up against feature work, and I never argued for it because I didn’t believe it mattered.

It wasn’t ugly. It was a decoupling between what the database was and what the backup thought it was, waiting for someone to move a directory. I had my hands on it and I filed it under aesthetics.

What we changed

We got most of the eleven days back, eventually, from an assortment of miserable sources — application logs, a reporting warehouse with its own extract on a different schedule, a search index that happened to hold enough fields to reconstruct records, and one team’s ill-advised habit of emailing themselves CSVs. It took a fortnight and it was not complete. There are customers who still had a bad month because of it.

Three changes came out of the postmortem and only one of them is interesting.

The boring ones: the backup script derives the data directory by asking the running database where it is, rather than being told. And the old data directory got deleted, with a policy that rollback copies get an expiry date on creation.

The one that mattered: the restore test now compares against live production, not against a stored expectation. Restore the backup, then for a sample of recent records — the newest row in each of the main tables, plus a hundred random primary keys written in the last 24 hours — go ask production for the same records and diff them. If the restored copy doesn’t contain something production wrote yesterday, the test fails.

That check would have caught this on day one. It catches a whole family of things: wrong path, wrong host, wrong database, replication silently stopped, a filter somebody added to reduce backup size.

The lesson, stated plainly

A backup you’ve never restored is a hypothesis. Everybody knows that one; it’s on posters.

Here’s the one that isn’t: a restore test that doesn’t compare against current production data is also a hypothesis. It proves your restore machinery works. It cannot prove your backup contains your data, and those are different claims — I had spent nineteen months confusing them, with a runbook and a signature and a compliance checkbox to prove I was doing it right.

The job was green every single night. It just wasn’t backing up anything anybody was using.