My first restore test reported 0 tables and 0 errors

My first real restore test reported 0 tables and 0 errors. It looked clean. It had restored nothing at all.

Until August 2026, every backup check I ran asked the same question: does the backup exist, and does it look well-formed? I had 49 backup sets, cron jobs refreshing most of them daily, and dashboards showing their age. Nobody had ever taken one and restored it. When I finally did, the first attempt failed silently, the completion check I planned to use gave a false alarm, and a few days later a different job produced two half-sized backups that looked perfectly valid.

This post is about how to test restores cheaply and safely, and about the quiet ways a restore, or a backup, can lie.

Contents · 9 sections

TL;DR #

• 49 backup sets; all checked for existence and freshness for months, and not one had ever been restored.
• 0 tables and 0 errors; that was the first restore. kubectl cp had copied a *-latest symlink as a symlink, so gunzip read nothing and psql loaded nothing, successfully.
• Restore into a scratch database next to production, compare table and row counts, then drop it. It is safe and takes minutes.
• pg_dump 17+ writes a \unrestrict line after the “dump complete” marker, so tail checks report complete dumps as broken. Grep the whole file.
• 2 truncated snapshots; they came from my metrics database, looked like backups and would have evicted good ones from retention. A size floor now makes truncation fail loudly.

The backup fleet before the test #

By early August my backups looked healthy on paper. Under one backup directory: 49 sets, about 35 GB. 36 of them were refreshed within 24 hours by cron: a nightly dump of every database on the shared PostgreSQL, a MySQL dump, a critical-data tarball, an hourly dump of the cluster datastore, twice-daily SSO configuration copies and a few more. The other 13 were dated one-off archives and two superseded paths.

Two things looked like failures and turned out not to be:

• a Git server dump set was 32 days old. It had been superseded by the combined Postgres dump, which now covers 17 databases including the Git server’s;
• an Elasticsearch snapshot directory was empty. It was a leftover mount point; the real snapshots live in Elasticsearch’s own snapshot repository, weekly, three retained, all successful.

Both checks were worth doing. Neither proved anything about restoring.

The method: restore next to production, not over it #

The safe way to test a database restore on a live server is to restore into a scratch database on the same instance, compare it with production, and drop it:

# 1. copy the dump into the database pod (see the symlink trap below)
kubectl -n <namespace> cp "$(readlink -f <backup-dir>/<db>-latest.sql.gz)" <pg-pod>:/tmp/restore.sql.gz

# 2. restore into a scratch database
kubectl -n <namespace> exec -i <pg-pod> -- sh -c '
  createdb -U <admin> restore_test_<date> &&
  gunzip -c /tmp/restore.sql.gz | psql -U <admin> -d restore_test_<date> -v ON_ERROR_STOP=1 -q'

Then compare:

-- run in both databases
SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';
SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY relname;

Finally drop the scratch database, delete the temp file, and re-check that production is untouched.

One guard matters: if a dump contains a \connect <database> line, psql will switch to that database mid-restore and write into production. Check before loading:

zcat <dump>.sql.gz | grep -n '^\\connect' | head

Plain single-database pg_dump output normally has none; pg_dumpall output does.

Trap 1: the restore that restored nothing #

My first attempt reported 0 tables restored and 0 errors. A restore with no errors is easy to accept. A restore with zero tables should not be.

Every *-latest.sql.gz in my backup directory is a symlink to the newest dated dump. kubectl cp does not dereference symlinks: it copied the link itself into the pod, where it pointed at a path that did not exist. gunzip produced empty output, psql received an empty script, executed nothing, and exited successfully.

The fix is to dereference first:

kubectl cp "$(readlink -f <path>/<db>-latest.sql.gz)" <pod>:/tmp/restore.sql.gz

With the real file, the same restore of my Git server’s database produced:

• 112 of 112 tables;
• 9 of 9 repositories;
• 1 of 1 users;
• 0 psql errors.

The only difference from production was the action log: 3008 entries live versus 2975 in the dump, which is Gitea activity accumulated after the midnight dump. That is the result you want: the backup matches production as of its timestamp.

General lesson: assert on content, not on the absence of errors. A restore test should fail if the restored object is empty.

Trap 2: the completion marker that moved #

To check that dumps were complete, I planned to look at the last lines of each file for -- PostgreSQL database dump complete. On a perfectly good dump, it reported the marker missing.

Newer pg_dump versions (17 and later) append a \unrestrict <token> line after the completion marker, part of a security fix that restricts which meta-commands a restore can run. So the last line is no longer the marker. Grep the whole file instead:

zcat <dump>.sql.gz | grep -c 'PostgreSQL database dump complete'

That returned 1 for every database I checked.

Trap 3: backups that are half a backup #

Five days later, the daily snapshot of my Prometheus database to the NAS raised a failed-job alert. It was not a clean failure. Both attempts had created a snapshot and copied it to the NAS, and both copies had been cut short: 6.5 GB and 7.6 GB, where the previous two days were 14 GB and 12 GB.

The usual suspects were ruled out: the NAS had 22 TB free, the admin API was enabled, the NAS was reachable (the log backup job finished normally half an hour later), and Prometheus had not restarted. The job’s pods had already been garbage-collected, so the exact cause of the cut-off was not recoverable.

The dangerous part was what happened next. Retention kept the last 7 snapshot directories. There were 9, including the two truncated ones. As new snapshots landed, the half-sized ones would occupy retention slots and evict good backups. A bad run would quietly destroy good history.

Reading the script showed why truncation had been silent until then: the copy step only marked the backup as failed if creating the target directory failed. A copy that failed or was cut short printed a warning and exited 0. The job failed this time only for an unrelated reason.

The fix, in the job:

1. measure the newest existing snapshot on the NAS before copying (afterwards, the new one is the newest and would be compared with itself);
2. after copying, fail if the new snapshot is under 60 % of the previous one;
3. delete a failed copy immediately, so it cannot hold a retention slot;
4. run retention only after a verified-good copy;
5. treat a failed copy as a failed backup.

In simplified form:

PREV_KB=$(du -sk "$(ls -1d "$NAS"/2* | tail -1)" 2>/dev/null | cut -f1)
cp -a "$SNAP" "$NAS/$STAMP" || NAS_OK=0
NEW_KB=$(du -sk "$NAS/$STAMP" | cut -f1)
if [ "${PREV_KB:-0}" -gt 0 ] && [ $((NEW_KB * 100 / PREV_KB)) -lt 60 ]; then
  echo "TRUNCATED: ${NEW_KB}K vs ${PREV_KB}K"; rm -rf "$NAS/$STAMP"; exit 1
fi

The first-ever snapshot skips the floor. I tested the arithmetic against the real numbers: both bad snapshots counted as truncated, and 12 GB against 14 GB passed.

Removing the two bad snapshots had its own small trap. They were written by a container as root on the NAS share, and my shell user could not delete them: rm -rf failed on every file and removed nothing. I deleted them from a short-lived pod that mounted the same NFS volume, with the size check repeated inside the pod, so it would abort if either one turned out to be a real backup. They measured 56 % and 48 % of the reference, and 7 good snapshots remained.

Checklist #

• Schedule a real restore test, not just existence and age checks.
• Restore into a scratch database, compare counts with production, drop it.
• Check dumps for \connect lines before loading them anywhere near production.
• Dereference symlinks before kubectl cp.
• Make restore tests fail on empty results, not only on errors.
• Verify pg_dump completeness with a whole-file grep.
• Give snapshot jobs a size floor against the previous good backup.
• Run retention only after a verified-good backup, so bad runs cannot evict good ones.
• Delete container-written artifacts as the identity that wrote them, with guards inside that context.

Frequently asked questions #

How do I test a PostgreSQL backup without risking production? #

Restore the dump into a new scratch database on the same server or a disposable one, compare table lists and row counts with production, then drop it. Check first that the dump has no \connect line that would redirect the restore into a live database.

Why did my restore finish with no errors but no data? #

The input was probably empty. A common cause is copying a symlink instead of its target, for example with kubectl cp, which copies links as links. Dereference with readlink -f, and make your test fail when the restored database has no tables.

Why is “PostgreSQL database dump complete” not the last line of my dump? #

Since the 2025 security releases, pg_dump (version 17 and later in my case) adds \restrict and \unrestrict lines around the dump, and the \unrestrict line comes after the completion comment. Search the whole file for the marker instead of the last lines.

How can a failed backup delete good backups? #

If retention keeps the last N copies and a broken run still leaves a directory behind, the broken copy counts as one of the N and pushes out a good one. Validate each new backup (size, checksum or test restore) and run retention only when it passes.

The lesson #

A backup is a claim, and every check I had was a way of re-reading the claim. The first restore test turned it into evidence, and even that tried to fool me with a clean-looking empty result. The pattern behind all three traps is the same: success was defined as “no error”. Define it as “the data is there, and it matches”, and the lies become loud.