Failover PostgreSQL multi-région, testé

# postgresql.conf sur le primaire
wal_level = replica
max_wal_senders = 10
wal_keep_size = 2GB
# Plafonne le WAL qu'un slot déconnecté peut retenir, pour qu'un standby mort ne remplisse pas le disque
max_slot_wal_keep_size = 100GB
 
archive_mode = on
archive_command = 'wal-g wal-push %p'
# Force un changement de segment WAL au moins toutes les 60 s, pour que l'archive (et le RPO
# des sauvegardes) n'ait jamais plus d'une minute environ de retard, même sur une base calme
archive_timeout = 60
 
ssl = on
ssl_cert_file = '/etc/postgresql/tls/server.crt'
ssl_key_file  = '/etc/postgresql/tls/server.key'
 
# Un commit attend N'IMPORTE LAQUELLE des deux répliques LOCALES. Nommer un seul standby ici
# bloque les écritures du primaire dès que ce standby tombe.
# Le standby inter-région n'est pas listé : il reste asynchrone et les écritures n'attendent jamais le WAN.
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

# Sur l'hôte de la région B, en tant qu'utilisateur postgres, avec un répertoire de données VIDE.
# Le mot de passe vient de ~/.pgpass (chmod 600), jamais de la ligne de commande :
#   primary.internal:5432:replication:replicator:<mot de passe>
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 peut recopier le mot de passe utilisé dans postgresql.auto.conf.
# Vérifiez, et supprimez-le s'il est présent, pour qu'il ne vive que dans .pgpass :
grep primary_conninfo /var/lib/postgresql/data/postgresql.auto.conf

# postgresql.conf sur le standby inter-région
restore_command = 'wal-g wal-fetch %f %p'
recovery_target_timeline = 'latest'
hot_standby = on

# 1. Isolez d'abord l'ancien primaire (arrêtez-le, ou coupez son réseau/security group).
#    Deux primaires accessibles en écriture, c'est pire que quelques minutes d'interruption.
 
# 2. Sur le standby de la région B, vérifiez ce qu'il a reçu et rejoué
psql -Atc "SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), now() - pg_last_xact_replay_timestamp() AS replay_delay;"
 
# 3. Promotion (PostgreSQL 12+ ; attend jusqu'à 60 s la fin de la promotion)
psql -Atc "SELECT pg_promote(wait => true, wait_seconds => 60);"
 
# 4. Vérifiez qu'il accepte maintenant les écritures : doit afficher 'f'
psql -Atc "SELECT pg_is_in_recovery();"

#!/usr/bin/env bash
# Tourne en tant qu'utilisateur postgres, avec le même environnement WAL-G (bucket, préfixe) que la production.
# Donnez à cet hôte des identifiants en LECTURE SEULE sur le bucket, comme seconde ligne de défense.
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
# Connexion en lecture seule à une réplique de production, utilisée comme référence
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   # arrête et efface toujours l'instance de test, même si un contrôle échoue
 
rm -rf "$RESTORE_TARGET"
mkdir -p "$RESTORE_TARGET" && chmod 700 "$RESTORE_TARGET"
 
echo "[1/5] Récupération de la dernière sauvegarde complète via wal-g..."
wal-g backup-fetch "$RESTORE_TARGET" LATEST
 
echo "[2/5] Configuration d'une recovery isolée..."
# Les paquets Debian/Ubuntu gardent la configuration dans /etc, la sauvegarde peut donc ne pas la contenir
[ -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 est lu en dernier : ces valeurs écrasent celles venues de la 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
# Sans ce fichier, PostgreSQL n'entre pas en archive recovery et ne rejoue rien
touch "$RESTORE_TARGET/recovery.signal"
 
echo "[3/5] Démarrage de PostgreSQL et rejeu du WAL jusqu'à la fin de l'archive..."
pg_ctl -D "$RESTORE_TARGET" -l "$RESTORE_LOG" start -w -t 600
for _ in $(seq 1 720); do   # jusqu'à 1 h de rejeu du WAL
  [ "$(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 "ÉCHEC : toujours en recovery après 1 h, voir $RESTORE_LOG" >&2
  exit 1
fi
 
echo "[4/5] Exécution des contrôles d'intégrité..."
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;")
 
# Échantillon déterministe et borné : les SAMPLE_ROWS derniers ids inférieurs ou égaux au max restauré.
# ORDER BY + LIMIT sont dans la sous-requête, le hash ne couvre donc vraiment que cet échantillon.
# Ne hachez que des colonnes qui ne changent jamais après l'insertion.
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 "ÉCHEC : la dernière transaction rejouée date de ${REPLAY_DELAY} s, l'archive WAL semble en retard" >&2
  exit 1
fi
if [ "$ROW_COUNT" -lt "$EXPECTED_MIN_ROWS" ]; then
  echo "ÉCHEC : nombre de lignes restaurées ($ROW_COUNT) sous le seuil attendu ($EXPECTED_MIN_ROWS)" >&2
  exit 1
fi
if [ "$RESTORED_SUM" != "$PROD_SUM" ]; then
  echo "ÉCHEC : checksum d'échantillon différent (restauré $RESTORED_SUM, production $PROD_SUM)" >&2
  exit 1
fi
 
echo "[5/5] Restauration vérifiée : $ROW_COUNT lignes, id max $MAX_ID, checksum d'échantillon identique à la 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
  }
}