Database Reliability: The SRE Approach to Keeping Data Safe

#!/bin/bash
# weekly-restore-test.sh — runs every Sunday at 3am

TEST_DB="restore_test_$(date +%Y%m%d)"

# Step 1: Download latest backup
aws s3 cp s3://backups/prod-db/latest.sql.gz /tmp/restore-test.sql.gz
if [ $? -ne 0 ]; then
  alert "CRITICAL: Cannot download backup from S3"
  exit 1
fi

# Step 2: Restore to test database
gunzip -c /tmp/restore-test.sql.gz | psql -h test-db-host -U admin -d $TEST_DB
if [ $? -ne 0 ]; then
  alert "CRITICAL: Backup restore failed"
  exit 1
fi

# Step 3: Validate data
PROD_COUNT=$(psql -h prod-db -t -c "SELECT count(*) FROM users")
TEST_COUNT=$(psql -h test-db-host -d $TEST_DB -t -c "SELECT count(*) FROM users")

DIFF=$(( PROD_COUNT - TEST_COUNT ))
if [ $DIFF -gt 100 ]; then
  alert "WARNING: Restored DB has $DIFF fewer rows than production"
fi

# Step 4: Cleanup
psql -h test-db-host -c "DROP DATABASE $TEST_DB"
rm /tmp/restore-test.sql.gz

notify "Backup restore test PASSED. Rows: prod=$PROD_COUNT test=$TEST_COUNT"

-- PostgreSQL replication lag
SELECT 
  client_addr,
  state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag_bytes,
  pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag_bytes,
  pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag_bytes,
  pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

# pgbouncer.ini
[pgbouncer]
pool_mode = transaction     # Not session!
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600

# Monitoring
stats_period = 60

-- Find the worst queries
SELECT 
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER())::numeric, 2) AS pct,
  left(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

# Dangerous: Locks table for entire ALTER
ALTER TABLE orders ADD COLUMN tracking_id VARCHAR(100);

# Safe: Use CREATE INDEX CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_orders_tracking ON orders(tracking_id);

# Safe migration pattern:
# 1. Add nullable column (instant, no lock)
ALTER TABLE orders ADD COLUMN tracking_id VARCHAR(100);

# 2. Backfill in batches (no lock)
UPDATE orders SET tracking_id = generate_tracking_id(id) 
WHERE id BETWEEN 1 AND 10000;

# 3. Add NOT NULL constraint when ready (requires ACCESS EXCLUSIVE briefly)
ALTER TABLE orders ALTER COLUMN tracking_id SET NOT NULL;

Panel 1: Active connections vs max_connections (saturation)
Panel 2: Query latency p50/p95/p99
Panel 3: Replication lag (seconds)
Panel 4: Transactions per second
Panel 5: Cache hit ratio (should be > 99%)
Panel 6: Disk usage + growth rate + days until full