Multi-Region PostgreSQL Failover, Tested End to End

# postgresql.conf on the primary
wal_level = replica
max_wal_senders = 10
wal_keep_size = 2GB
# Cap how much WAL a disconnected slot can pin, so a dead standby can't fill the disk
max_slot_wal_keep_size = 100GB
 
archive_mode = on
archive_command = 'wal-g wal-push %p'
# Force a WAL segment switch at least every 60s, so the archive (and the backup RPO)
# is never more than about a minute behind, even on a quiet database
archive_timeout = 60
 
ssl = on
ssl_cert_file = '/etc/postgresql/tls/server.crt'
ssl_key_file  = '/etc/postgresql/tls/server.key'
 
# A commit waits for ANY ONE of the two LOCAL replicas. Naming a single standby here
# means primary writes hang the moment that one standby goes down.
# The cross-region standby is not listed, so it stays async and writes never wait on WAN latency.
synchronous_standby_names = 'ANY 1 (local_replica_a, local_replica_b)'

# TYPE   DATABASE     USER        ADDRESS        METHOD
hostssl  replication  replicator  10.20.0.0/16   scram-sha-256

# On the region B host, as the postgres user, with an EMPTY data directory.
# The password comes from ~/.pgpass (chmod 600), never from the command line:
#   primary.internal:5432:replication:replicator:<password>
pg_basebackup \
  -d "host=primary.internal port=5432 user=replicator sslmode=verify-full sslrootcert=/etc/postgresql/tls/ca.crt application_name=region_b_standby" \
  -D /var/lib/postgresql/data \
  -R -X stream -C -S region_b_standby -P
 
# -R can copy the password it used into postgresql.auto.conf.
# Check, and remove it if present, so it only lives in .pgpass:
grep primary_conninfo /var/lib/postgresql/data/postgresql.auto.conf

# postgresql.conf on the cross-region standby
restore_command = 'wal-g wal-fetch %f %p'
recovery_target_timeline = 'latest'
hot_standby = on

# 1. Fence the old primary first (stop it, or cut its network/security group).
#    Two writable primaries are worse than a few minutes of downtime.
 
# 2. On the region B standby, check how much it has received and replayed
psql -Atc "SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), now() - pg_last_xact_replay_timestamp() AS replay_delay;"
 
# 3. Promote (PostgreSQL 12+; waits up to 60s for promotion to complete)
psql -Atc "SELECT pg_promote(wait => true, wait_seconds => 60);"
 
# 4. Confirm it now accepts writes: must print 'f'
psql -Atc "SELECT pg_is_in_recovery();"

#!/usr/bin/env bash
# Runs as the postgres user, with the same WAL-G environment (bucket, prefix) as production.
# Give this host READ-ONLY credentials on the bucket as a second line of defence.
set -Eeuo pipefail
 
RESTORE_TARGET="/var/lib/postgresql/restore-verify"
RESTORE_LOG="/var/log/postgresql/restore-verify.log"
PORT=5433
DB="app"
EXPECTED_MIN_ROWS=1000000
SAMPLE_ROWS=100000
MAX_REPLAY_DELAY_SECONDS=900
# Read-only connection to a production replica, used as the reference
PROD_DSN="host=replica.internal dbname=app user=restore_check sslmode=verify-full"
 
cleanup() {
  pg_ctl -D "$RESTORE_TARGET" stop -m immediate >/dev/null 2>&1 || true
  rm -rf "$RESTORE_TARGET"
}
trap cleanup EXIT   # always stop and wipe the test instance, even when a check fails
 
rm -rf "$RESTORE_TARGET"
mkdir -p "$RESTORE_TARGET" && chmod 700 "$RESTORE_TARGET"
 
echo "[1/5] Fetching latest base backup via wal-g..."
wal-g backup-fetch "$RESTORE_TARGET" LATEST
 
echo "[2/5] Configuring an isolated recovery..."
# Debian/Ubuntu packages keep config files in /etc, so the backup may not contain them
[ -f "$RESTORE_TARGET/postgresql.conf" ] || : > "$RESTORE_TARGET/postgresql.conf"
[ -f "$RESTORE_TARGET/pg_hba.conf" ] || echo "local all all peer" > "$RESTORE_TARGET/pg_hba.conf"
# postgresql.auto.conf is read last, so these override whatever came from production
cat >> "$RESTORE_TARGET/postgresql.auto.conf" <<CONF
port = $PORT
listen_addresses = 'localhost'
archive_mode = off
archive_command = ''
synchronous_standby_names = ''
restore_command = 'wal-g wal-fetch %f %p'
recovery_target_action = 'promote'
hot_standby = on
CONF
# Without this file PostgreSQL does not enter archive recovery and replays nothing
touch "$RESTORE_TARGET/recovery.signal"
 
echo "[3/5] Starting PostgreSQL and replaying WAL to the end of the archive..."
pg_ctl -D "$RESTORE_TARGET" -l "$RESTORE_LOG" start -w -t 600
for _ in $(seq 1 720); do   # allow up to 1h of WAL replay
  [ "$(psql -p "$PORT" -d postgres -Atc 'SELECT pg_is_in_recovery()')" = "f" ] && break
  sleep 5
done
if [ "$(psql -p "$PORT" -d postgres -Atc 'SELECT pg_is_in_recovery()')" != "f" ]; then
  echo "FAIL: still in recovery after 1h, see $RESTORE_LOG" >&2
  exit 1
fi
 
echo "[4/5] Running integrity checks..."
REPLAY_DELAY=$(psql -p "$PORT" -d "$DB" -Atc \
  "SELECT coalesce(extract(epoch FROM now() - pg_last_xact_replay_timestamp())::int, 999999);")
ROW_COUNT=$(psql -p "$PORT" -d "$DB" -Atc "SELECT count(*) FROM orders;")
MAX_ID=$(psql -p "$PORT" -d "$DB" -Atc "SELECT max(id) FROM orders;")
 
# Deterministic, bounded sample: the last SAMPLE_ROWS ids at or below the restored max id.
# ORDER BY + LIMIT live in the subquery, so the hash really covers only that sample.
# Hash only columns that never change after insert.
SAMPLE_SQL="SELECT md5(string_agg(id::text, ',' ORDER BY id)) FROM (SELECT id FROM orders WHERE id <= $MAX_ID ORDER BY id DESC LIMIT $SAMPLE_ROWS) s;"
RESTORED_SUM=$(psql -p "$PORT" -d "$DB" -Atc "$SAMPLE_SQL")
PROD_SUM=$(psql "$PROD_DSN" -Atc "$SAMPLE_SQL")
 
if [ "$REPLAY_DELAY" -gt "$MAX_REPLAY_DELAY_SECONDS" ]; then
  echo "FAIL: last replayed transaction is ${REPLAY_DELAY}s old, WAL archive looks stale" >&2
  exit 1
fi
if [ "$ROW_COUNT" -lt "$EXPECTED_MIN_ROWS" ]; then
  echo "FAIL: restored row count ($ROW_COUNT) below expected floor ($EXPECTED_MIN_ROWS)" >&2
  exit 1
fi
if [ "$RESTORED_SUM" != "$PROD_SUM" ]; then
  echo "FAIL: sample checksum mismatch (restored $RESTORED_SUM, production $PROD_SUM)" >&2
  exit 1
fi
 
echo "[5/5] Restore verified: $ROW_COUNT rows, max id $MAX_ID, sample checksum matches production"

resource "aws_route53_health_check" "primary_region" {
  fqdn              = "app-primary.example.com"
  port              = 443
  type              = "HTTPS"
  resource_path     = "/healthz"
  failure_threshold = 3
  request_interval  = 10
}
 
resource "aws_route53_record" "app_failover_primary" {
  zone_id = var.zone_id
  name    = "app.example.com"
  type    = "A"
 
  failover_routing_policy {
    type = "PRIMARY"
  }
 
  set_identifier  = "primary"
  health_check_id = aws_route53_health_check.primary_region.id
  alias {
    name                   = aws_lb.region_a.dns_name
    zone_id                = aws_lb.region_a.zone_id
    evaluate_target_health = true
  }
}