Test Your Postgres Backups with a Real Restore Drill
Summary
Pixel’s announcement walks through testing a scheduled Postgres backup end to end with Levelrail: back up a database, restore it to a fresh database, and compare customer and order counts plus totals. It matters because a successful backup job only confirms a file was written—not that data can actually be recovered.
DEV Community
My nightly backup job had been green for months. I never restored it once.
My nightly backup job had been green for months. I never restored it once. Last week I looked at a cron job of mine that had printed "backup ok" every night for months, and I asked myself a plain question: when did I last load one of those files into a database? I could not remember a date. A green job tells you a file was written. It does not tell you the file works. I built a small test and ran it end to end. I created a Postgres database, backed it up on a schedule, changed the live data afterwards, restored the backup into a separate database, and compared row counts. I used Levelrail, the self-hosted deploy platform I work on, built from the v1.0.1 release branch (commit d510e8488). Everything below ran on my Mac with Docker, and I mark the parts I only read in the docs. TL;DR Step Command What it proves Schedule backups schedule set A backup runs without you Restore as new backups restore-as-new The backup loads into a fresh database Compare databases query Counts match the moment of the backup Drill backups drill run Checksum, restore and content check pass Copy backups destinations copy set A second bucket holds the same object (docs only) The test setup I made a Postgres 16 database with two tables: 500 customers and 2000 orders. Then I recorded three numbers I could check later. levelrail-cli databases create --name main --engine postgres --version 16
levelrail-cli databases query main --sql "select (select count() from customers) as customers, (select count() from orders) as orders, (select sum(total_cents) from orders) as total_cents"
customers orders total_cents
500 2000 8805000
I pick a sum on top of the counts because two tables with the right row count can still hold the wrong values. Schedule it and take one now A backup target is the place the files go. Levelrail has a local type that needs no bucket, which is fine for a first run but lives on the same disk as the database, so it is no protection against losing the machine. I used it first, then switched to an S3-compatible target, which I get to below. levelrail-cli backup-targets create --name nightly --provider local --path nightly
levelrail-cli backups schedule set main --target TARGET_ID --cron "0 3 * * *" --retain 7 --retain-days 30
levelrail-cli backups trigger main --target TARGET_ID
levelrail-cli backups list main
The list printed a succeeded row of about 11.8 KB, finished in the same second it started. Retention combines two limits: keep at most the newest 7 backups, and nothing older than 30 days. A backup goes when either rule would remove it (docs/guides/self-host-postgres-with-backups.md). Break the live data, then restore the old state This is the step that makes the test mean something. After the backup, I deleted 500 orders from the live database, the way a bad migration or a wrong DELETE would. levelrail-cli databases query main --write --confirm main --sql "delete from orders where id > 1500"
levelrail-cli backups restore-as-new main --backup BACKUP_ID --new-name main-restore
Restoring as new builds a separate database next to the original and never touches it. The new container was up in under a minute. Then I ran the same count query against both. live
customers orders total_cents
500 1500 6597750
restored
customers orders total_cents
500 2000 8805000
The restored database has the numbers from before the delete, to the cent, and the live one has the damage. That is the output I wanted from a backup, and I had never produced it from my cron job. An in-place restore also exists. It overwrites the live database and asks you to type the database name, so I would use restore-as-new first and only then decide. What a restore drill adds A manual rehearsal is good once. A drill is the same idea on a timer. For Postgres, MySQL and MariaDB, Levelrail downloads the stored backup, compares its SHA-256 with the checksum recorded at backup time, replays it into a throwaway container with no network, check...

Powered by Readybot.io