Backup Verification and Integrity Checks

In this lesson, you will learn how to automate the restoration and integrity validation of your PostgreSQL backups to ensure your data is truly recoverable when disaster strikes.

The "Backup Fallacy"

Many administrators treat a successful "dump" or "base backup" as a guarantee of safety. However, a backup file is useless if it is corrupted, missing files, or contains inconsistent data. Manual testing is often skipped due to time constraints, which is why automating the restore-and-verify cycle is critical.

Automating the Pipeline

To automate verification, you need an isolated environment where your backup pipeline can automatically trigger a restoration process. A robust workflow typically follows these three steps:

1. Automated Restore: Use a script triggered by your backup tool to spin up a transient database instance (or use a dedicated "verification" container).
2. Consistency Check: Run pg_checksums (on stopped physical backups) or perform logical validation queries against the restored data.
3. Smoke Tests: Execute a suite of SQL queries that verify the row counts and data integrity of your most critical tables.

For example, a simple verification script might perform a basic row-count check after a restore:

# 1. Restore the backup to a temporary instance
pg_restore -d verify_db backup_file.dump

# 2. Run a smoke test to confirm critical data is accessible
psql verify_db -c "SELECT count(*) FROM orders WHERE created_at > NOW() - INTERVAL '1 day';" > result.txt

# 3. Fail the pipeline if the result is unexpected
if [ $(cat result.txt | tail -n 1) -lt 100 ]; then
  echo "Integrity check failed: Data count below threshold."
  exit 1
fi

Why Logic Matters

While physical integrity checks (like checking the block checksums) ensure the files aren't corrupted at the disk level, logical verification ensures the application-level data is intact. By running specific analytical queries—similar to those you defined in your performance baselines—you ensure that the restored data remains valid for the query planner and that your business logic remains sound. This process should be treated as a CI/CD task for your data.

Key Takeaway: A backup is not truly a backup until it has been successfully restored and verified through automated integrity checks.