Restore-Tests: Ein Backup, das nie wiederhergestellt wurde, ist nur eine Hoffnung

#!/usr/bin/env bash
# Nightly restore test: latest backup -> fresh database in the isolated twin -> checks
set -euo pipefail
LATEST=$(ls -1 /backups/db/*.dump.gpg | sort | tail -1)   # synced from the off-site store
gpg --batch --decrypt --output /tmp/latest.dump "$LATEST"

START=$(date +%s)
dropdb --if-exists -h restore-twin restore_check
createdb -h restore-twin restore_check
pg_restore -h restore-twin -d restore_check --no-owner --jobs 4 /tmp/latest.dump
echo "restore_seconds $(( $(date +%s) - START ))"            # compare with the RTO

psql -h restore-twin -d restore_check -v ON_ERROR_STOP=1 -f checks.sql
rm -f /tmp/latest.dump

-- checks.sql: run the same queries on a production replica and compare
-- 1. Exact row counts of the key tables
SELECT 'bookings' AS tbl, count(*) FROM bookings
UNION ALL SELECT 'payments', count(*) FROM payments
UNION ALL SELECT 'customers', count(*) FROM customers;

-- 2. Checksum independent of physical row order
SELECT md5(string_agg(b::text, '|' ORDER BY b.id)) AS bookings_checksum
FROM bookings b WHERE b.created_at < date_trunc('day', now());

-- 3. Freshness: the newest row must be inside the RPO
SELECT now() - max(created_at) AS data_age FROM bookings;