Automated Backup Verification on a VPS: Building Restore Drills That Actually Run

An untested backup is a hypothesis, not a safety net. The failure mode is depressingly common: nightly tar or database dumps run for months, exit code zero, and then the day you need them the archive is truncated, the credentials have rotated, or the dump was of the wrong database. This guide builds automated verification — checksums, restore drills into a scratch area, and alerts — so you learn your backups are broken on a Tuesday afternoon rather than during an incident.

Verification workloads need disk headroom and enough RAM to start a temporary database. If your instance is tight, size it against the VPS configurations on our main site before automating drills.

Prerequisites

• An existing backup routine producing files (dumps, archives, snapshots)
• Free disk space of at least 2x the largest backup for a scratch restore
• sqlite3, psql, or mysql client depending on your stack
• A way to send alerts (mail, webhook, or a log monitored by your uptime check)
• Root or sudo access and the ability to schedule systemd timers or cron

Step 1: Make Every Backup Self-Describing

A backup should carry proof of its own integrity. Write a checksum and a manifest alongside each file.

BACKUP_DIR=/srv/backups
STAMP=$(date +%Y%m%d-%H%M%S)
OUT="$BACKUP_DIR/db-$STAMP.sql.gz"

pg_dump -Fc mydb | gzip -9 > "$OUT"
sha256sum "$OUT" > "$OUT.sha256"

cat > "$OUT.manifest" <<EOF
created=$STAMP
host=$(hostname -f)
source=mydb
size_bytes=$(stat -c %s "$OUT")
pg_version=$(pg_dump --version)
EOF

# Fail loudly if the dump is suspiciously small
SIZE=$(stat -c %s "$OUT")
[ "$SIZE" -lt 1048576 ] && { echo "ABORT: dump under 1MB"; exit 1; }

That size guard alone catches a large fraction of real failures: a dump that suddenly drops from 400MB to 2KB means authentication failed or the schema changed, and it should page you immediately rather than quietly accumulate.

Step 2: Verify Integrity Without a Full Restore

The cheap check runs every night. It confirms the file is intact and structurally readable without touching your production services.

LATEST=$(ls -t /srv/backups/db-*.sql.gz | head -1)

# 1. Checksum
sha256sum -c "$LATEST.sha256" || exit 2

# 2. gzip stream integrity
gzip -t "$LATEST" || exit 3

# 3. Archive table of contents (PostgreSQL custom format)
zcat "$LATEST" | pg_restore -l > /tmp/bk.toc || exit 4
grep -q "TABLE DATA public orders" /tmp/bk.toc || exit 5

# 4. For MySQL dumps, confirm the header and a known table
# zcat "$LATEST" | head -50 | grep -q "CREATE TABLE \`orders\`" || exit 5

echo "integrity OK: $LATEST"

Step 3 is the one people skip. A gzip that decompresses cleanly can still contain a dump of an empty database. Checking the table of contents — and asserting that a known business-critical table is present and non-empty — is what turns a file check into a real verification.

Step 3: Restore Drill Into an Isolated Scratch Instance

Once a week, restore for real — but never over production. Run a throwaway database on a non-standard port with its own data directory.

SCRATCH=/srv/restore-drill
DRILL_PORT=55432
rm -rf "$SCRATCH"; mkdir -p "$SCRATCH"
chown postgres:postgres "$SCRATCH"

sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D "$SCRATCH/data" >/dev/null

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl -D "$SCRATCH/data" \
  -o "-p $DRILL_PORT -k $SCRATCH" -l "$SCRATCH/log" start
sleep 3

sudo -u postgres createdb -p $DRILL_PORT -h "$SCRATCH" drill
zcat "$LATEST" | sudo -u postgres pg_restore -p $DRILL_PORT -h "$SCRATCH" -d drill

echo "rows: $(sudo -u postgres psql -p $DRILL_PORT -h $SCRATCH -d drill \
  -tAc 'select count(*) from orders')"

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl -D "$SCRATCH/data" stop

Assert on row counts, not just exit codes. pg_restore can return non-zero on harmless warnings, and it can return zero after restoring an empty table. Compare the restored count against the live count within a tolerance band and record the result.

Step 4: Schedule It and Alert on Failure

Use a systemd timer so failures are visible in journalctl and the unit’s exit status is recorded.

sudo tee /etc/systemd/system/backup-drill.service > /dev/null <<'EOF'
[Unit]
Description=Weekly backup restore drill
OnFailure=alert@%n.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/backup-drill.sh
Nice=10
IOSchedulingClass=idle
EOF

sudo tee /etc/systemd/system/backup-drill.timer > /dev/null <<'EOF'
[Unit]
Description=Run backup restore drill weekly

[Timer]
OnCalendar=Sun 04:30
Persistent=true
RandomizedDelaySec=900

[Install]
WantedBy=timers.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable --now backup-drill.timer
systemctl list-timers | grep backup-drill

Persistent=true means a drill missed because the VPS was down runs on next boot instead of being silently skipped. RandomizedDelaySec avoids every host in your fleet hammering shared storage at the same second.

Verification Step

Confirm the whole pipeline fails correctly, not just that it succeeds when things go well.

# 1. Run the drill manually and check status
sudo systemctl start backup-drill.service
systemctl status backup-drill.service --no-pager
journalctl -u backup-drill.service --since "5 min ago"

# 2. Negative test: corrupt a copy and confirm detection
cp /srv/backups/db-*.sql.gz /tmp/corrupt.gz
printf 'junk' | dd of=/tmp/corrupt.gz bs=1 seek=5000 conv=notrunc
gzip -t /tmp/corrupt.gz; echo "gzip exit (expect non-zero): $?"

# 3. Confirm the on-failure alert path works
grep -q "ExecStart" /etc/systemd/system/backup-drill.service && echo "unit OK"
systemctl show backup-drill.service -p OnFailure

A drill that has never failed a test is not verified. Deliberately breaking a copy and watching the pipeline catch it is the only way to trust the green result.

Troubleshooting

Symptom	Cause	Fix
Drill passes but restore is empty	No row-count assertion	Compare restored vs live counts
pg_restore exits non-zero	Owner/role missing in scratch instance	Pre-create roles or restore with --no-owner
Scratch instance will not start	Port conflict or missing data dir perms	Use a high port; chown postgres the directory
Timer never fires	OnCalendar syntax invalid	Validate with systemd-analyze calendar "Sun 04:30"
Alerts silent on failure	OnFailure unit not defined	Create the alert template unit
Disk fills during drill	Scratch not cleaned per run	rm -rf the scratch dir at script start

Once this runs for a few weeks you will have a restore time baseline and a failure history — both essential inputs when you plan a real recovery. Backups stop being a ritual and start being evidence, and the OnFailure trail is the audit log that proves it.