AltSql Restore Proves Your Nightly Backup Restores Before You Need It
Most backups are never restored until the day someone needs them. That’s a bad day to find out the dump stopped halfway through.
AltSql Restore is a new AltSql module that tests the backup every night instead. It’s one program, altsql-restore, open source under the Apache License 2.0, and it ships in the 0.5.0 release on GitHub beside the two shells.
What It Does
You point it at last night’s backup. It restores the backup into a throwaway sandbox, counts the rows in every table, takes a checksum of each, and compares the numbers with the last good run. Then it removes the sandbox.
It takes PostgreSQL dumps (plain, custom or tar format), MySQL and MariaDB dumps, SQLite database files, tar archives and plain folders, gzipped or not. SQLite files and archives need nothing else installed: AltSql reads the SQLite file format, tar and gzip with its own code. For PostgreSQL and MySQL it starts a private server inside the sandbox, from the server programs on the machine if it finds them, or from Docker if not.
The exit code tells your scheduler what happened. 0 means the backup restored and nothing shrank. 1 means the restore failed, or a table lost more rows than you allow (10% by default), or a table disappeared. 3 means it couldn’t verify the backup at all, because no PostgreSQL or MySQL server could run. It says so in plain words and never counts that as a pass. An optional webhook posts a short JSON summary when a run doesn’t pass.
Every run also writes a dated report, signed off with a name you give and sealed with a SHA-256 hash, or an HMAC with a key you keep. That’s the paper trail an auditor asks for: proof that each night’s backup was actually restored, and what was in it. The history of runs lives in an AltSql DB file, the same gateway database the rest of the project uses, so you can query it with SQL.
A Short Example
altsql-restore --job shop-db --signer "ops on call" \
--report-dir /var/log/altsql-restore /backups/shop-latest.sql.gz
Here’s the part of the report that matters, from a night when someone’s clean-up script went too far:
Table rows previous change checksum
public.audit_log 300 300 +0.0% db26aea8b0757b88 same
public.customers 1,200 1,200 +0.0% ec86aa07f03e8f27 same
sales.orders 4,000 5,000 -20.0% 9ff0cccd8d0974b0 changed
Verdict: ALERT
- sales.orders shrank from 5,000 to 4,000 rows (-20.0%), more than the 10% allowed
The alert repeats every night until the rows come back, or until someone accepts the new numbers with --accept after a planned clean-up.
How It Was Tested
The tests aim at four promises. A damaged or cut-short backup always fails. A table that lost rows past the threshold always alerts. A missing engine is reported as not verified. And the sandbox always goes away, even when the run is killed.
In version 0.5.1 the end-to-end tests ran 147 checks, and all passed, with a local PostgreSQL 16.15 and MariaDB 10.11.14. They include SQLite files cut in half, short by one page and short by 100 bytes, files with a page header overwritten, a file copied in the middle of a write, archives cut in the middle or missing their end blocks, PostgreSQL and MySQL dumps cut short or carrying a bad row, and dumps carrying a command for the database client itself, such as a shell command, which the client would run on the machine outside the sandbox. Every one failed, as it should, and none of those commands ran. A run killed with kill -9 while a database server was running in its sandbox left nothing behind: a small watchdog process stopped the server and deleted the folder. The same held for runs killed earlier, while the database was still being set up.
The unit tests ran 39 checks. In one of them, 2,000 gzip files each had one bit flipped, and all 2,000 failed. The whole suite also ran under AddressSanitizer and UBSan without a complaint, and so did a scripted pass of 30,000 damaged SQLite, tar and gzip files through the built-in readers.
On a shared 2-CPU Linux VM, version 0.5.1 checked a SQLite file of a million rows, 45 MB, in 0.57 to 0.64 seconds. The PostgreSQL test dump took 2.0 to 2.2 seconds, most of it starting the server.
The logs behind these numbers are in the repository under engines/restore/results, and the manual lists every option and the known limits. Like the rest of AltSql, it’s an alpha, so the report format may still change.
Case Studies and Use Examples
Three case studies run it on public sample databases with local PostgreSQL and MariaDB servers: five nights of Pagila backups with a cut upload and an archiving job that deleted most of the rentals, dumps a plain restore script called good, and a SQLite file copied during a write, which 0.5.0 passed when it shouldn’t have and 0.5.1 fails. The use examples go through the cron line, the shrink limit, --accept, the history in SQL, keyed seals for an auditor, the webhook and what NOT VERIFIED means. Each was run as written, and the recordings are in the repository.