Restore drill checklist

A backup is only proven when it has been restored. This is the checklist for doing that on purpose, before an incident, with the numbers written down. It applies to any Postgres or MySQL backup, whether or not Dumpling took it.

For reference: our own drill on Dumpling's database, from downloading the off-site copy to a running server whose stored secrets decrypted, took 10 minutes 13 seconds and found two problems in the written procedure. Neither was in the code. That is the usual outcome, and it is why the drill is worth doing.

Before you start (5 minutes)

Pick the backup you would reach for in a real incident: the most recent one, not a convenient old one.
Write down the time. The number you get at the end is the point of the exercise.
Have somewhere to restore into that is not production: a local Postgres or MySQL, a Docker container, or a throwaway instance on your host.
Have the tools at the right version. pg_restore must be at least as new as the pg_dump that wrote the file, or it fails with "unsupported version in file header".
If the file is encrypted, have the passphrase from wherever it actually lives (a password manager, not your memory).

The restore (10 to 30 minutes)

Download the file from the bucket. Note how long that takes; on a large database it is most of the time.
Check it is readable before restoring: pg_restore --list file.dump should print a table of contents; gunzip -t file.sql.gz should exit 0; an encrypted file should decrypt with openssl enc -d -aes-256-cbc -pbkdf2 -in file.enc -out file.
Restore into the empty database: createdb restored_db && pg_restore --no-owner --no-acl -d restored_db file.dump, or mysql -e "CREATE DATABASE restored_db" && gunzip < file.sql.gz | mysql restored_db.
Read the errors. A handful of "role does not exist" lines are normal with --no-owner; a missing extension or a failed constraint is not.
Count the rows in the three tables that matter most and compare with production at the time the backup was taken. Check the newest row's timestamp is close to the backup time.
If the application can run against the restored copy, start it and load one real page. A schema that restores but does not serve is not a restore.

Afterwards (5 minutes)

Write down the elapsed time, the file size, and every step that was not in your runbook. Put it where the next person will look during an incident.
Fix the runbook the same day. A drill that finds a problem and does not fix it will find the same problem next quarter.
Delete the restored copy if it holds production data, and the downloaded file if the machine is not where such data should live.
Put the next drill in the calendar. Quarterly is enough for most teams; after any change to the backup setup, do it again.

What drills usually find

The file exists but has no contents. The dump command exited 0 while writing a truncated or empty file. This is why the backup should be verified when it is taken, not when it is needed.
Version mismatch. The client tools on the laptop or the recovery box are older than the ones that wrote the dump. Bump them everywhere at the same time as the server.
Permissions on the target. A copied data directory or volume owned by root, so the database engine cannot write. Our own drill hit this: SQLite reported "attempt to write a readonly database" until the directory was chowned.
A line dropped from the runbook. Someone pasted values over a template and lost a line. In our drill it was the application's own URL, which stopped the server from starting. Diff the key names, not just the values.
The passphrase is in one person's head. The encrypted backup is fine and nobody can open it. The passphrase belongs in the same password manager as the master credentials, tested from a second account.
The bucket credentials only allow writing. Sensible for the backup job, useless for the restore. Keep a separate read-only key somewhere the on-call person can find it.

Record sheet

Date:                    Backup file:
Backup taken at:         File size:
Restore target:          Tool versions:
Download time:           Restore time:
Row counts (3 tables):   Match production?  yes / no
Steps missing from runbook:
Total elapsed:           Next drill:

Make the drill boring

Dumpling checks every backup with pg_restore --list as it is taken, shows the restore command next to every run, and alerts when a run is missed. The drill still matters; it just stops finding surprises in the file.