AltSql Restore Fails Three PostgreSQL Dumps That psql Restored With Exit Status 0
Plenty of teams already test their backups with a one-line script. It loads last night’s dump into a spare database with psql, and if the exit status is 0, the backup counts as good. The trouble is that psql reading a file keeps going after an error. It prints the error, moves on to the next statement, and exits with 0 at the end, however many statements failed.
We took three PostgreSQL dumps with real faults and gave each one to that script and to altsql-restore 0.5.0. Then we did the same with a MariaDB dump. Everything ran on 31 October 2026 against local PostgreSQL 16.15 and MariaDB 10.11.14 servers.
| Backup | Plain script exit status | What the database held afterwards | AltSql Restore |
|---|---|---|---|
| Chinook dump saved as Latin-1 | 0, with 8 errors | 5 of 11 tables empty | FAILED |
| Pagila as published upstream | 0, with 323 errors | 11 tables; customers, films, rentals and payments missing | FAILED |
Chinook dump cut short, through gunzip | psql |
0, with 1 error | The track table empty | FAILED |
| Chinook MariaDB dump saved as Latin-1 | 1 | 9 tables, the last ones missing | FAILED |
A Dump Saved in the Wrong Encoding
Chinook is the public sample database of a digital music store, with artists, albums, tracks, customers and invoices. Its names come from many languages, so the text has many letters outside plain ASCII. We dumped it with pg_dump and then converted the file to Latin-1 with iconv. An editor or a file transfer tool set to a Windows code page does the same thing quietly, and the file still looks right in most viewers.
The dump says SET client_encoding = 'UTF8' at the top, so PostgreSQL rejected the first byte that isn’t valid UTF-8. Here’s what the plain script left:
$ psql -X -q -d plaincheck -f backups/chinook-latin1.sql
exit status: 0
error lines: 8
psql:backups/chinook-latin1.sql:551: ERROR: invalid byte sequence for encoding "UTF8": 0xfa
...
tables afterwards (rows):
public.album 0
public.artist 0
public.customer 0
public.invoice 0
public.invoice_line 2240
public.track 0
Albums, artists, customers, invoices and tracks are empty. Each table’s whole COPY failed on one bad byte. The invoice lines loaded anyway, since the foreign keys that would have refused them are added at the end of the dump, and those failed too.
AltSql Restore runs psql with stop-on-first-error and the whole plain dump in one transaction. It stopped at the first bad byte and said where (the sandbox path shortened here):
Verdict: FAILED
- the restore failed: psql stopped with an error (exit 3): psql:/tmp/altsql-restore-.../dump:551:
ERROR: invalid byte sequence for encoding "UTF8": 0xfa | CONTEXT: COPY album, line 26
The same dump before the conversion passed, with all 11 tables counted.
A Dump From a Newer Server
The second dump is Pagila, the DVD rental sample database, exactly as its project publishes it: a schema file and a data file, which together make one plain dump. They come from a newer PostgreSQL than 16. The schema calls uuidv7() from PostgreSQL 18 and uses the pgvector extension, and the data file sets transaction_timeout from PostgreSQL 17.
That’s a common way to meet this problem in real life: production moves to a new major version, and the restore test server stays where it was. On PostgreSQL 16 the plain script reported success with 323 errors behind it. The tables that did load were actors, addresses, categories, cities, countries, the film links, inventory, languages, staff and stores. There were no customers, films, rentals or payments at all.
Restore stopped on line 284, the first uuidv7():
Verdict: FAILED
- the restore failed: psql stopped with an error (exit 3): ...dump:284:
ERROR: function uuidv7() does not exist
The fix is a newer server for the test, and the report says which server was used. When the local programs are older than the dump, --pg-image picks a newer PostgreSQL image for the Docker sandbox. The Pagila case study used a copy of Pagila adapted for 16.
A Dump Cut Short in a Pipe
The third dump is Chinook again, gzipped, with the copy cut at 60%: 88,668 of 147,780 bytes. The plain script was gunzip -c backup.sql.gz | psql. gunzip prints a warning and exits with 1 when the data ends early, but a shell pipeline reports the exit status of its last command, which is psql. The result was exit status 0, one error line, missing data for column "unit_price", and a track table with no rows. The other ten tables loaded, so a quick look at the database wouldn’t show anything wrong.
Restore read the gzip file with its own reader, which checks the length and CRC-32 at the end, and failed it in under a second: the gzip data ends early (truncated).
The Same Latin-1 Fault on MariaDB
MariaDB’s mariadb client stops at the first error by default and exits with 1, so a script that checks the exit status does catch this one. It still leaves a half-restored database behind: nine tables, the playlists empty, and the track and playlist-track tables never created. Restore failed it with the same message from the client, ERROR at line 3657: Unknown command '\'', and threw the sandbox away. The good MariaDB dump of Chinook passed with all 11 tables, the server started from the local MariaDB programs.
What a Plain Script Is Missing
A plain script can be fixed: psql -v ON_ERROR_STOP=1 --single-transaction, set -o pipefail in the shell, a check on every exit status. Those fixes are easy to leave out, because the script prints success every night either way. Restore has them built in, starts its own throwaway server so the script doesn’t need a spare database, and then counts the rows, which no exit status does. A dump that restores cleanly with most of its rows gone gets past every exit status check. The Pagila case study has one.
The commands, outputs and reports are in the repository under engines/restore/results/case-studies/2-restore-scripts, with the run.sh that makes them again. Each plain-*.txt file holds the plain script’s exit status, its error lines and the row counts it left behind.