Backups You Can Actually Restore

A backup you have never restored is a rumor

Everyone agrees backups matter. Almost nobody tests them. The uncomfortable truth of that gap: an untested backup is not a backup, it is a hope with a filename.

This post is the build log of my backup vault: a dedicated, minimal server on the most locked-down VLAN in my lab, pulling database dumps from my production host on a timer, with a restore drill that proves the files actually contain my data, and one design flaw my own review caught before it could quietly eat everything.

---

Design first: pull, not push, and the vault lives in the tightest zone

Two decisions shaped everything else.

The vault pulls; production cannot push. If the production host is ever compromised, a push model hands the attacker a road to the backups. In a pull model the vault initiates everything, and production holds no credentials to reach the vault at all. Blast radius contained.

The vault lives in the database VLAN, the most restricted zone I have. It is just data at rest. It does not need internet (packages arrive through a proxy, because that VLAN has no outbound route by design), and only one firewall rule exists for it: vault to production host, SSH, logged. Nothing else in, nothing else out.

The SSH key on production is not a normal key. It is pinned in authorized_keys with a forced command, so the only thing that key can ever execute is the file-transfer command, from one source IP:

from="10.0.30.30",command="/usr/local/bin/rrsync -ro /srv/backups",no-pty,no-port-forwarding ssh-ed25519 AAAA...

I tested the restriction the honest way, by trying to break it. Asked the key to run whoami: refused. Asked it to list the backup directory: worked. A hardened key you have not tried to abuse is the same rumor as an untested backup.

The drill: prove the bytes are real

A dump file on the vault proves transport works. It does not prove the file can become a database again. So: full restore drill, on the vault, into a throwaway PostgreSQL container, then count what came back and compare against production.

# on the vault: restore the pulled dump into a scratch postgres
docker run -d --name restore-test -e POSTGRES_PASSWORD=... postgres:17
docker exec -i restore-test pg_restore -U postgres -d postgres --create < latest.dump

# then interrogate it
SELECT count(*) FROM information_schema.tables WHERE table_schema='public';
SELECT count(*) FROM candles;

Result: 26 tables, 54,451 market candles, 39,048 macro rows. Exact match with production. That number is the moment the backup stops being a rumor.

Two small traps inside the drill, both worth your time:

1. A container's /tmp is ephemeral. I dumped into the database container's /tmp first, and the file vanishes with the container. Dump to a mounted path or stream straight out with docker exec.
2. A fresh PostgreSQL container starts twice. Its first boot initializes the data directory, restarts the server internally, and for a moment pg_isready answers yes while the server is about to go away again. If your script connects at exactly the wrong second, you get a baffling connection error on a "ready" database. Wait for the second readiness, or sleep past the init dance.

The flaw a second pair of eyes caught: backup rot

My first automation was the obvious shape: dump on a timer, keep seven days, delete older. Reviewing it, one question stopped everything:

"Are we sure we're not deleting old backups when the new one didn't actually save?"

That failure mode has a name: backup rot. The dump step starts failing silently (disk full, credential expired, container renamed), while the retention step keeps working perfectly. Retention eats a good backup every day, and the day you finally need one, the shelf holds seven broken files, or nothing.

The hardened order makes rot structurally impossible:

The verify step is humble: does the file exist, is it a sane size, do its first bytes read PGDMP (the PostgreSQL custom-format magic)? That is enough to guarantee retention only ever runs after a real new backup exists. And the script fails loud on purpose: a failed unit in systemctl --failed is a flare; a silent || true is how rot stays invisible for months.

The schedule is a systemd timer, every six hours, seven days of retention:

[Timer]
OnCalendar=00/6:00
Persistent=true

What this bought

• 3-2-1 for the data that matters: production copy, on-host dumps, and an off-host vault in a different failure domain behind a different firewall zone.
• A restore procedure I have actually executed, timed, and written down, so the 3 AM version of me follows steps instead of improvising.
• A prune step that structurally cannot destroy the last good backup.

If you take one thing: schedule the restore drill, not just the backup. The backup script tells you it ran. Only the restore tells you it mattered.

---

Pictures to add later

1. Terminal: the restore drill table/row counts next to production's (the proof moment)
2. systemctl list-timers showing the backup timer with its next run
3. Optional: the refused whoami vs the successful backup listing (hardened key demo)