Zero-Downtime Multi-Region PostgreSQL Failover

This setup uses cross-region streaming replication for fast failover and adds a nightly restore check to catch problems replication will not protect against, like a bad migration, a corrupted table, or an accidental DELETE without a WHERE clause.

Target architecture

The standby region runs a warm app tier (scaled down, not zero) so failover is a traffic and promotion event, not a cold start.

Replication: sync vs. async, and what it costs you

Synchronous replication gives you an RPO of zero (no committed transaction is ever lost), but every write waits on a round trip to the standby. Across regions with 40-80ms of latency, that's a non-starter. So replication ends up doing two different jobs depending on distance:

• Synchronous replication to a same-region/same-AZ replica for instant, zero-data-loss local failover.
• Asynchronous streaming replication to the cross-region standby for disaster-level failover, accepting a small (seconds-scale) RPO in exchange for acceptable write latency.

# postgresql.conf on the primary
wal_level = replica
max_wal_senders = 6
wal_keep_size = 2GB
archive_mode = on
archive_command = 'wal-g wal-push %p'
 
# synchronous_standby_names targets the LOCAL replica only,
# the cross-region standby stays async so writes aren't blocked on WAN latency.
synchronous_standby_names = 'local_replica'

# postgresql.conf on the cross-region standby
primary_conninfo = 'host=primary.internal port=5432 user=replicator application_name=region_b_standby'
restore_command = 'wal-g wal-fetch %f %p'
recovery_target_timeline = 'latest'

Tools like Patroni (backed by etcd/Consul for leader election) can automate that promotion instead of leaving it to a human. But automated failover across regions is a decision to make deliberately, not a default. A false-positive failover across a WAN link is its own incident.

Backup validation: the step almost everyone skips

The check runs nightly against the latest archived base backup + WAL, on a throwaway instance, fully automated:

#!/usr/bin/env bash
set -euo pipefail
 
RESTORE_TARGET="/var/lib/postgresql/restore-verify"
EXPECTED_MIN_ROWS=1000000
 
echo "[1/4] Fetching latest base backup via wal-g..."
wal-g backup-fetch "$RESTORE_TARGET" LATEST
 
echo "[2/4] Starting PostgreSQL in recovery mode against restored data..."
pg_ctl -D "$RESTORE_TARGET" -o "-p 5433" start -w
 
echo "[3/4] Running integrity checks..."
ROW_COUNT=$(psql -p 5433 -Atc "SELECT count(*) FROM orders;")
CHECKSUM=$(psql -p 5433 -Atc "SELECT md5(string_agg(id::text, ',' ORDER BY id)) FROM orders LIMIT 100000;")
 
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
 
echo "[4/4] Restore verified, $ROW_COUNT rows, checksum $CHECKSUM"
pg_ctl -D "$RESTORE_TARGET" stop

Wire the exit code into your alerting (a failed nightly restore is a page, not a ticket). Of every control in this playbook, this nightly check pays for itself fastest: it converts "we think our backups work" into "we proved it 20 minutes ago."

DNS-level failover

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
  }
}

The standby's matching SECONDARY failover record takes over automatically once health checks fail; no manual DNS change is needed during an actual incident.

RTO/RPO targets we design against

| Scenario | RPO | RTO | |---|---|---| | Single-AZ failure (local sync replica) | 0 (zero data loss) | < 60s (automated) | | Full-region failure (async standby promotion) | < 30s of writes | < 5 min (automated) | | Corrupted data / bad migration (restore from backup) | Up to last WAL segment (~5 min) | 15-30 min (manual, deliberate) |

The result

Streaming replication and Route53 failover are mature, well-understood building blocks. What decides the outcome during a real incident is whether the failover path has actually been exercised, and whether "the backup works" is a tested fact rather than an assumption.