The page
In January 2017 GitLab lost a production database after a command ran on the wrong server, then found that several of their backup methods hadn't been working. They wrote it up openly: Postmortem of database outage of January 31.
I was running lab environments across data centres at the time. Reading that, I realised I'd never restored one of our own backups. I'd only seen the jobs report success.
Triage
# does the archive actually contain anything?
ls -la /backups/lab-db/
gzip -t lab-db-2017-05-14.sql.gz && echo "archive ok"
tar -tzf lab-files-2017-05-14.tar.gz | head
# for a pg_dump custom-format file: list the contents without restoring
pg_restore --list lab-db-2017-05-14.dump | head -30
# the real test: restore into a scratch database and query it
createdb restore_test
pg_restore -d restore_test lab-db-2017-05-14.dump
psql -d restore_test -c "SELECT count(*) FROM documents;"
Several dumps were a few kilobytes. The job "succeeded" every night.
Root cause
The backup script ran pg_dump and then compressed whatever came out. When the
database password was rotated months earlier, pg_dump started failing. The
compression step after it succeeded, so the script exited 0.
The fix
set -o pipefail in the script so a failed dump fails the job, a size check
that alerts if a dump is suspiciously small, and a monthly restore into a
scratch VM with a row count checked.
What I learnt
A backup that's never been restored isn't a backup yet. The job's exit code told me the script finished, which isn't the same as telling me the data was safe.
What I do now
- Test restores on a schedule and check the data, not just the exit code.
- Alert on backup size and age, not only on job failure.
- Use
set -euo pipefailin any script whose failure matters.
stage 1 includes the restore test most teams skip → see the engagements