The Night the Backup Didn’t Work
A colleague of mine worked at a fintech startup. They had a PostgreSQL database backup running nightly via cron — pg_dump, gzip, upload to S3. Clean, simple, automated. Until their primary server crashed.
They went to restore. The backup file was there. They ran the restore command. It failed. Silently corrupt — a disk I/O error mid-write three weeks earlier had quietly mangled the file, and nobody noticed because nobody had ever actually tried to restore it.
That’s not a backup. That’s a false sense of security wearing a backup’s clothes.
Why This Happens More Than You Think
Most teams set up backups and never touch them again. The backup script runs, the file lands somewhere, the monitoring dashboard shows green, and everyone sleeps soundly. But several failure modes lurk under the surface:
- Silent corruption: Disk errors, network drops, or filesystem issues can corrupt backup files without throwing any error in your cron log.
- Partial writes: If your backup process gets killed mid-dump (OOM killer, timeout, server restart), you end up with a truncated file that looks complete at first glance.
- Schema mismatches at restore time: The backup restores fine — but then your app fails because a table or column no longer matches the target environment.
- Credential and permission drift: The restore works technically, but the restored database user doesn’t have the right grants in the new environment.
- Database version incompatibility: A pg_dump from PostgreSQL 14 won’t restore cleanly into PostgreSQL 12 in certain cases.
The root cause is always the same: backups are write operations that nobody reads. You only find out they’re broken when you actually need them.
Three Approaches — and Why Two Fall Short
Approach 1: Manual Periodic Testing
Some teams schedule a quarterly “restore drill” — they pick a backup, spin up a test environment, and try to restore it. This is better than nothing, but it’s manual so it gets skipped when people are busy, and quarterly means up to 90 days of undetected corruption. It also doesn’t catch the 3am backup that silently failed last Tuesday.
Approach 2: Checksum Verification Only
A step up: generate an MD5 or SHA256 hash of the backup file right after creation, then verify the hash before any restore. This catches corruption in transit or at rest. Good — but it doesn’t tell you whether the backup is logically valid. A complete, uncorrupted gzip of a half-written pg_dump will pass a checksum check and still fail to restore.
Approach 3: Automated Restore-and-Verify Pipeline
This is the one that actually works. Every backup gets automatically restored into a throwaway environment, key integrity checks run against the restored data, and you get alerted only if something fails. The rest is silent green. Here’s how to build it.
Building the Pipeline: Step by Step
Step 1 — Structured Backup with a Metadata Sidecar
Make your backup script emit a JSON metadata file alongside the dump. This becomes the contract the verify job reads later:
#!/bin/bash
BACKUP_DIR="/backups/postgres"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/db_$TIMESTAMP.dump"
META_FILE="$BACKUP_DIR/db_$TIMESTAMP.meta.json"
pg_dump -Fc -h localhost -U postgres mydb > "$BACKUP_FILE"
EXIT_CODE=$?
SHA256=$(sha256sum "$BACKUP_FILE" | awk '{print $1}')
SIZE=$(stat -c%s "$BACKUP_FILE")
cat > "$META_FILE" <<EOF
{
"timestamp": "$TIMESTAMP",
"exit_code": $EXIT_CODE,
"file": "$BACKUP_FILE",
"sha256": "$SHA256",
"size_bytes": $SIZE,
"pg_version": "$(pg_dump --version | grep -oP '\d+\.\d+')",
"verified": false
}
EOFThe verified flag starts as false and only gets flipped after a successful restore-and-check cycle. If it stays false, you know something went wrong.
Step 2 — Isolated Restore in a Throwaway Docker Container
The restore should happen in isolation — a fresh container that gets destroyed afterward, leaving no state behind:
#!/bin/bash
# restore_and_verify.sh
BACKUP_FILE="$1"
CONTAINER="pg-verify-$(date +%s)"
docker run -d \
--name "$CONTAINER" \
-e POSTGRES_PASSWORD=verifypass \
-e POSTGRES_DB=verify_db \
postgres:16 > /dev/null
sleep 5 # wait for postgres to be ready
# Restore the backup
docker exec -i "$CONTAINER" \
pg_restore -U postgres -d verify_db < "$BACKUP_FILE"
RESTORE_EXIT=$?
if [ $RESTORE_EXIT -ne 0 ]; then
echo "RESTORE FAILED: $BACKUP_FILE"
docker rm -f "$CONTAINER"
/opt/scripts/send_alert.sh "Restore failed for $BACKUP_FILE"
exit 1
fi
# Hand off to integrity checker
python3 /opt/scripts/verify_integrity.py "$CONTAINER" "$BACKUP_FILE"
VERIFY_EXIT=$?
docker rm -f "$CONTAINER"
exit $VERIFY_EXITStep 3 — SQL Integrity Checks Against the Restored Data
A successful restore isn’t enough. Run actual queries to verify the data makes sense for your application. Tailor these to your schema — the goal is catching logical corruption, not just technical failure:
import subprocess
import json
import sys
def run_check(container, query, description):
result = subprocess.run(
["docker", "exec", container,
"psql", "-U", "postgres", "-d", "verify_db",
"-t", "-c", query],
capture_output=True, text=True
)
value = result.stdout.strip()
print(f"[CHECK] {description}: {value}")
return value
container = sys.argv[1]
backup_file = sys.argv[2]
meta_file = backup_file.replace(".dump", ".meta.json")
checks = [
("SELECT COUNT(*) FROM users", "users table row count"),
("SELECT COUNT(*) FROM orders WHERE created_at > NOW() - INTERVAL '30 days'", "recent orders"),
("SELECT MAX(updated_at) FROM products", "latest product update"),
]
failed = 0
for query, desc in checks:
result = run_check(container, query, desc)
if result in ("", "0", None):
print(f"[WARN] Suspicious result for: {desc}")
failed += 1
with open(meta_file, "r+") as f:
meta = json.load(f)
meta["verified"] = (failed == 0)
meta["check_failures"] = failed
f.seek(0)
json.dump(meta, f, indent=2)
f.truncate()
if failed > 0:
subprocess.run(["/opt/scripts/send_alert.sh",
f"{failed} integrity check(s) failed for {backup_file}"])
sys.exit(0 if failed == 0 else 1)A users table with zero rows is a red flag. So is a “latest updated product” timestamp from three years ago on a table you know changes daily. The checks should reflect what normal looks like for your database.
Step 4 — Wire It Together with Cron
# /etc/cron.d/backup-verify
# Backup at 2am, verify at 3am
0 2 * * * postgres /opt/scripts/backup.sh >> /var/log/backup.log 2>&1
0 3 * * * postgres /opt/scripts/restore_and_verify.sh \
$(ls -t /backups/postgres/*.dump | head -1) >> /var/log/verify.log 2>&1Step 5 — Alert on Failure, Stay Silent on Success
The pipeline should be invisible when things work. A simple Slack webhook handles alerts without adding infrastructure complexity:
#!/bin/bash
# send_alert.sh
MESSAGE="$1"
WEBHOOK_URL="https://hooks.slack.com/services/YOUR/WEBHOOK"
curl -s -X POST -H 'Content-type: application/json' \
--data "{\"text\": \":rotating_light: DB Backup Verify FAILED: $MESSAGE\"}" \
"$WEBHOOK_URL"A Practical Note on Data Format Conversion
One thing that comes up during restore verification: sometimes I need to inspect or cross-check exported table data in different formats — like comparing a CSV export against restored row counts before and after a migration.
When I need to quickly convert CSV to JSON for data imports or spot-checks like this, I use toolcraft.app/en/tools/data/csv-to-json — it runs entirely in the browser, so no data leaves your machine. That matters when you’re handling sensitive database exports and don’t want to pipe them through a random online service.
What the Full Pipeline Looks Like
- 2:00 AM — pg_dump runs; metadata sidecar created with SHA256, size, and exit code
- 3:00 AM — restore script pulls the latest .dump, spins up a Docker container, restores it
- 3:10 AM — Python integrity checker runs SQL queries, updates
verified: true/falsein metadata - 3:11 AM — Slack alert fires if anything failed; otherwise complete silence
- 3:12 AM — Docker container is destroyed, disk space released
The whole cycle takes under 15 minutes for a 5GB database. Metadata files accumulate as an audit trail — on any given day you can look back and see exactly which backup was the last verified one, and when.
Extra Checks Worth Adding Once the Basics Are Running
- Row count delta alerts: Compare today’s restored row counts against yesterday’s. A 90% drop in the users table overnight is almost certainly not expected.
- Restore time benchmarking: Track how long restores take. A sudden 3x spike can indicate the backup is growing unexpectedly, or that your Docker host is under-resourced.
- Application-level smoke test: After restore, point your app’s healthcheck endpoint at the restored database. A database that restores but breaks your app on startup is still a failed recovery.
- Offsite verification: If you upload backups to S3, run a separate job that downloads from S3 and re-verifies. This catches silent upload failures you’d otherwise never see.
The Mental Shift That Actually Matters
The real change here isn’t technical — it’s treating every backup as unverified until proven otherwise. A backup that hasn’t been restored is a hypothesis, not a safety net.
Once this pipeline is running, something shifts in how your team talks about backups. Instead of “we have backups,” you start saying “our last verified backup was 18 hours ago.” That’s a fundamentally different conversation — and the kind of precision that actually matters when something breaks at 2am.
