AltSql Restore Catches a Cut Upload and a 72 Percent Rental Loss in Five Nights of Pagila Backups
Pagila is the public sample database of a made-up DVD rental chain, a PostgreSQL port of MySQL’s Sakila. It has customers, films, stores, 51,805 rentals and 51,061 payments, and the payments sit in one partition per month. That makes 69 tables in all, which is close enough to a small shop’s real database to be worth a test.
We loaded it into a local PostgreSQL 16.15 server and backed it up five times with pg_dump | gzip, the way many teams do it every night. After each backup, the released altsql-restore 0.5.0 checked the file with the same job name and the same history. The five runs were made one after another on 31 October 2026, and each one stands for a night.
The Nightly Check
Every night ran the same command on the backup host:
altsql-restore --job pagila --history history.db --signer "dba on call" \
backups/pagila-night1.sql.gz
| Night | What happened before the check | Verdict | Exit status |
|---|---|---|---|
| 1 | A normal night | PASSED, the baseline | 0 |
| 2 | 250 new rentals, then the copy to the backup host stopped at 60% | FAILED | 1 |
| 3 | An archiving job ran with the wrong cutoff year | ALERT | 1 |
| 4 | Nobody had fixed it yet | ALERT | 1 |
| 5 | The deleted rows were put back | PASSED | 0 |
Night 1 restored the 4.4 MB dump into a throwaway PostgreSQL server in 3.7 seconds, counted and checksummed all 69 tables, and kept the numbers as the baseline.
Night 2: The Upload That Stopped Short
The dump that night was 4,425,071 bytes. The copy that reached the backup host was 2,655,042. A file of 2.6 MB with the right name and a fresh date looks fine in a directory listing, and that’s how cut uploads go unnoticed for weeks.
Restore didn’t get as far as starting a server:
Verdict: FAILED
- the restore failed: pagila-night2.sql.gz: the gzip data ends early (truncated)
It took 0.2 seconds. The built-in gzip reader checks the length and CRC-32 at the end of the file, and a file cut in the middle has no end. A failed run never becomes the baseline, so the next night is still compared with night 1.
Night 3: The Archiving Job
The shop’s archiving job was meant to remove rentals and payments from before 2023. Someone typed 2025. In one transaction it deleted 37,420 rentals and 36,864 payments, and then the nightly backup ran, as it should. The dump was smaller, 1.6 MB against 4.4 MB the night before, and it restored without a single error. Every row in it was valid. Most of them were simply gone.
Here’s the report, shortened to the lines that matter:
Compared with: run 1 of 2026-10-31 11:17:14 UTC (PASSED)
Table rows previous change checksum
public.payment_p2022_01 0 723 -100.0% f1f78b968204538f changed
public.payment_p2025_01 747 747 +0.0% 6dcb81eccb295e5d same
public.rental 14,635 51,805 -71.7% f7b9f6f4d1422b4d changed
Verdict: ALERT
- public.payment_p2022_01 shrank from 723 to 0 rows (-100.0%), more than the 10% allowed
- public.payment_p2022_02 shrank from 2,401 to 0 rows (-100.0%), more than the 10% allowed
...
- public.rental shrank from 51,805 to 14,635 rows (-71.7%), more than the 10% allowed
The verdict lists 37 tables: the 36 monthly payment partitions from January 2022 to December 2024, each emptied, and the rental table. Note the “previous” column for rentals. It says 51,805, the night 1 count, because night 2 failed and never became the baseline.
Night 4: The Alert Comes Back
Nothing had changed by the next night, so the report said the same thing, again compared with night 1. An alert doesn’t move the baseline. It repeats every night until the rows come back, or until someone runs --accept because the deletion was planned. An alert that fires once is easy to miss on a busy morning. One that fires every night isn’t.
Night 5: Back to Normal
The deleted rows were put back. The script had saved them just before the delete, which stands in for copying them out of the night 1 backup. Then the night 5 backup passed. Rentals stood at 52,055, up 0.5% on night 1 thanks to night 2’s 250 new rentals. All 36 payment partitions that had been emptied came back with the same checksums they had on night 1, so the rows put back were the rows that left.
What We Used
PostgreSQL 16.15 from Ubuntu, local server programs, no Docker. Upstream Pagila now needs PostgreSQL 18 and the pgvector extension, so a small script made it load on 16: it drops the one table with vector embeddings and swaps two PostgreSQL 18 features for older ones, and leaves every other line alone. What happens when you restore the upstream files as they are is the subject of the case study on plain restore scripts.
Every command, report and count above comes from the recorded runs in the repository under engines/restore/results/case-studies/1-pagila-postgres. Its run.sh runs all five nights again, after fetch.sh one folder up has downloaded the release and Pagila and checked both against a SHA-256. Dates, seals and timings will differ on a rerun. Counts, verdicts and exit codes won’t.