Patroni Site Switchover: Zero Data Loss Between Two Independent Clusters

The maintenance window opens at 02:00. The DBA confirms that DC2 is streaming, runs patronictl promote-cluster, waits for DC2 to report Leader, and redirects the load balancer. The change record is closed at 02:14.

At 09:30 the application team reports that a batch of orders placed between 02:00 and 02:08 is missing from the database.

What happened is straightforward in retrospect. DC2 had replayed WAL up to 02:03:51. The patronictl promote-cluster command issued at 02:04 activated DC2 on a new PostgreSQL timeline without waiting for the remaining replication lag to close. The eight minutes of missing orders were committed on DC1, had not reached DC2, and were abandoned on DC1’s diverged timeline when DC2 was promoted.

The data was gone.

The answer

A Patroni standby-cluster promotion is not, by itself, a zero-data-loss switchover. The zero-data-loss guarantee comes from a coordinated sequence:

1. Quiesce application writes.
2. Drain active and prepared transactions.
3. Capture the final durable source WAL flush LSN.
4. Wait until the destination has replayed at least that LSN.
5. Stop and verify Patroni on every source-site member.
6. Promote the standby cluster.
7. Validate the new primary.
8. Redirect application traffic.
9. Rejoin the old site as a standby cluster.

Steps 5 and 4 are not optional. They are the two decisive controls. Without both, RPO 0 and split-brain prevention cannot be credibly claimed.

What you will learn

• Why two independent Patroni scopes require explicit fencing before a site promotion
• What the state machine for a controlled switchover looks like
• How to capture and verify the final WAL position before promoting
• A complete, abort-on-failure Bash script for the 3+1 topology
• Failure-scenario handling and the point after which rollback is prohibited

Scope: PostgreSQL 14 or later, Patroni 4.1 or later. The script uses patronictl promote-cluster and patronictl edit-config, which require Patroni 3.0+ and 4.1+ respectively. Test against exact installed versions before production use. Cloud-managed PostgreSQL services (RDS, Cloud SQL, Azure Database for PostgreSQL Flexible Server) use proprietary HA implementations and are not in scope.

1. Architecture

The design uses two independent Patroni scopes and two independent Distributed Configuration Store namespaces.

                          Application write endpoint
                    VIP / HAProxy / load balancer / DNS
                                     |
                                     v
              +------------------------------------------------+
              |                                                |
              |                   DC1                          |
              |                                                |
              |  Patroni scope: postgres-dc1                   |
              |                                                |
              |  pg1 ----+                                     |
              |  pg2 ----+-- one Leader + two local replicas   |
              |  pg3 ----+                                     |
              |                                                |
              +-----------------------+------------------------+
                                      |
                                      | physical WAL streaming
                                      | from the current DC1 leader
                                      v
              +------------------------------------------------+
              |                                                |
              |                   DC2                          |
              |                                                |
              |  Patroni scope: postgres-dc2                   |
              |                                                |
              |  pg4: Standby Leader                           |
              |       - follows the active-site endpoint       |
              |       - remains in PostgreSQL recovery         |
              |                                                |
              +------------------------------------------------+

When DC2 is promoted, the roles reverse:

DC2 pg4: Leader / writable primary
        |
        +---- WAL streaming ----> DC1 standby cluster
                                  pg1/pg2/pg3
                                  one Standby Leader + cascade replicas

A Patroni standby cluster is a separate Patroni cluster. Its standby leader streams from a remote PostgreSQL primary and can cascade WAL to its own replicas. The primary and standby clusters must use different Patroni scopes; they do not coordinate failover decisions with each other.

Relevant Patroni documentation:

• Standby clusters
• Multi-datacenter HA
• patronictl promote-cluster and demote-cluster

2. What “zero data loss” means here

This architecture normally uses asynchronous inter-site replication. It does not guarantee RPO 0 during an unplanned loss of the active datacenter.

It can provide RPO 0 during a controlled planned switchover by enforcing the following invariant:

No additional application commits are possible on the old primary AND DC2 has replayed at least the final durable DC1 WAL position AND DC1 is fenced before DC2 is promoted.

The required order is non-negotiable. Changing it creates either a data-loss window or a split-brain window.

3. Why the source must be fenced

The two Patroni scopes make independent decisions. DC2 cannot prove that DC1 is unavailable merely because replication or network connectivity has stopped.

Promoting DC2 while DC1 still has a running writable primary creates two PostgreSQL timelines and potentially two independently writable databases. Patroni documentation explicitly warns that promoting a standby cluster while the source cluster is still running creates split brain and requires the source to be stopped or fenced first.

Database read-only mode alone is not fencing because:

• default_transaction_read_only affects the initial state of new transactions only
• Existing transactions may already be writable
• Privileged users can override transaction read-only mode
• Application sessions may reconnect through another endpoint
• Jobs and integration services may bypass the normal application path

A defensible switchover requires both:

• Application fencing: load balancer, VIP, proxy, DNS, job scheduler, or application maintenance mode
• Database fencing: read-only setting, connection drain, and eventual Patroni/PostgreSQL shutdown on the old site

4. Required prerequisites

4.1 Patroni requirements

• Patroni 4.1 or later (uses patronictl promote-cluster and patronictl edit-config)
• DC1 and DC2 must have different Patroni scopes
• Member names must be unique across both clusters
• patronictl -f json list must return valid JSON
• Each site must have an accessible Patroni configuration file
• The current active cluster must report one Leader
• The remote cluster must report one Standby Leader

4.2 PostgreSQL requirements

• The destination standby leader must be streaming and replaying WAL
• WAL replay must not be paused
• Both sites must have the same PostgreSQL system identifier
• Replication authentication and pg_hba.conf must already be valid in both directions
• WAL retention or WAL archiving must be sufficient for rewind and rejoin operations

pg_rewind prerequisites must be met:

SHOW wal_log_hints;
SELECT current_setting('data_checksums');

At least one must be true: data checksums enabled at cluster initialization, or wal_log_hints = on.

pg_rewind can still fail for operational reasons, so a tested pg_basebackup rebuild path is mandatory.

4.3 Operating-system requirements

• Script runs as postgres
• Passwordless SSH from the execution node to every PostgreSQL node
• SSH host keys already trusted
• postgres can execute service-control commands through restricted passwordless sudo

Example restricted sudoers entry:

postgres ALL=(root) NOPASSWD: /usr/bin/systemctl start patroni, /usr/bin/systemctl stop patroni, /usr/bin/systemctl is-active patroni

Use exact executable paths and the actual service name from the target servers.

4.4 Application-routing requirements

The organization must provide two tested commands:

• A command that blocks all application writes to the old site
• A command that activates the new site after validation

Examples: disable/enable an HAProxy backend, move a floating VIP, update a load-balancer pool, enable application maintenance mode, stop/start application writer services, change a service-discovery record.

The script exposes these as hooks and refuses to proceed when a quiesce hook is not configured unless the operator explicitly overrides that safety control.

5. Site inventory

DC1 Patroni scope: postgres-dc1
DC2 Patroni scope: postgres-dc2

The standby cluster should follow a stable primary endpoint rather than a single physical host — a VIP, HAProxy address, or DNS name that always routes to the current active-site primary:

postgres-primary.internal.example.com:5432

6. Switchover state machine

State A — Normal operation

• One site is a regular Patroni cluster
• One site is a Patroni standby cluster
• Application connects only to the regular cluster leader

State B — Application quiesced

• Application routing is disabled
• New transactions default to read-only
• Application sessions are terminated
• No active transactions or prepared transactions remain

State C — RPO 0 established

• Final source durable LSN is captured
• Destination replay LSN >= final source LSN

State D — Source fenced

• Patroni is stopped on every old-site member
• PostgreSQL does not accept connections on any old-site member
• The old application endpoint remains disabled

State E — Destination promoted

• Destination reports Leader
• pg_is_in_recovery() is false
• A controlled write validation succeeds

State F — Traffic moved and old site rejoined

• Application writes use the new site
• Old site is converted to a standby cluster
• Old-site members are rewound or rebuilt
• One old-site member is Standby Leader and remaining members are replicas

7. Production script

The following script is intentionally conservative. It aborts on failed safety gates, logs each action, and does not automatically re-enable the old site after the destination has been promoted.

WARNING: Test before production use. Review permissions, assumptions, workload impact, and rollback requirements. No script should be executed in production without change-control approval.

Save as /etc/patroni/site-switchover-rpo0.sh:

#!/usr/bin/env bash
#
# Planned Patroni site switchover with RPO 0 for committed application writes.
# Architecture: DC1 = 3 Patroni members, DC2 = 1 standby-cluster member.
# Direction is detected automatically.
#
# Safety model:
#   1. Quiesce external writes.
#   2. Drain transactions and prepared transactions.
#   3. Capture final durable source WAL LSN.
#   4. Wait until destination has replayed that LSN.
#   5. Stop and verify every source-site Patroni service.
#   6. Promote destination with patronictl promote-cluster.
#   7. Validate write capability.
#   8. Activate application routing.
#   9. Convert old site with patronictl edit-config and restart it.
#
# IMPORTANT:
#   - Run only during an approved maintenance window.
#   - Test the script against the exact Patroni/PostgreSQL versions.
#   - APP_QUIESCE_CMD and APP_ACTIVATE_CMD must be adapted to the environment.
#   - The script must not be used as an automatic disaster-failover mechanism.

set -Eeuo pipefail
IFS=$'\n\t'
umask 077

# -----------------------------------------------------------------------------
# Inventory
# Format: member|host|pg_port|patroni_config|site|cluster_scope
# -----------------------------------------------------------------------------
NODES=(
  'pg1|server1|5432|/etc/patroni/dc1-pg1.yml|dc1|postgres-dc1'
  'pg2|server2|5432|/etc/patroni/dc1-pg2.yml|dc1|postgres-dc1'
  'pg3|server3|5432|/etc/patroni/dc1-pg3.yml|dc1|postgres-dc1'
  'pg4|server4|5432|/etc/patroni/dc2-pg4.yml|dc2|postgres-dc2'
)

SSH_USER='postgres'
SSH_OPTS=(
  -o BatchMode=yes
  -o ConnectTimeout=10
  -o ServerAliveInterval=5
  -o ServerAliveCountMax=3
)

PGUSER='postgres'
PGDATABASE='postgres'

# Use ~/.pgpass or TLS certificate authentication. Do not store a password here.
PSQL_BASE=(psql -X -v ON_ERROR_STOP=1 -tAq -w)

PATRONI_SERVICE='patroni'
REMOTE_SUDO=(sudo -n)

# A stable endpoint that routes to the current active-site primary.
# Written into standby_cluster.host when the old site is demoted.
ACTIVE_PRIMARY_ENDPOINT='postgres-primary.internal.example.com'
ACTIVE_PRIMARY_PORT='5432'
PRIMARY_SLOT_NAME='dr_standby_cluster'

APP_QUIESCE_CMD='/usr/local/sbin/postgres-app-routing quiesce'
APP_ACTIVATE_CMD='/usr/local/sbin/postgres-app-routing activate-new-primary'
APP_ROLLBACK_CMD='/usr/local/sbin/postgres-app-routing restore-old-primary'

# Set to 1 only for a controlled laboratory test where no application can connect.
ALLOW_EMPTY_APP_HOOKS=0

LSN_WAIT_SECONDS=300
PROMOTION_WAIT_SECONDS=180
DEMOTION_WAIT_SECONDS=600
POST_PROMOTION_WRITE_TEST=1

LOG_DIR='/var/log/patroni'
RUN_ID="$(date -u +%Y%m%dT%H%M%SZ)"
LOG_FILE="${LOG_DIR}/site-switchover-${RUN_ID}.log"
LOCK_FILE='/var/run/patroni-site-switchover.lock'

PHASE='initialization'
APP_QUIESCED=0
SOURCE_FENCED=0
DESTINATION_PROMOTED=0

mkdir -p "$LOG_DIR"
exec > >(tee -a "$LOG_FILE") 2>&1

log() {
  printf '%s [%s] %s\n' "$(date -u +'%Y-%m-%dT%H:%M:%SZ')" "$PHASE" "$*"
}

die() {
  log "ERROR: $*"
  exit 1
}

on_error() {
  local rc=$?
  log "FAILED with exit code ${rc}."

  if (( DESTINATION_PROMOTED == 1 )); then
    log 'Destination has already been promoted. Automatic rollback is prohibited.'
    log 'Keep the old source fenced. Validate the new primary and complete recovery manually.'
  elif (( SOURCE_FENCED == 1 )); then
    log 'Source site is already fenced but destination is not confirmed promoted.'
    log 'Do not restart either site blindly. Inspect both Patroni scopes and PostgreSQL roles.'
  elif (( APP_QUIESCED == 1 )); then
    log 'Failure occurred before source fencing. Attempting controlled pre-promotion rollback.'
    set +e
    clear_source_read_only
    run_hook "$APP_ROLLBACK_CMD" 'application rollback'
    set -e
  fi

  exit "$rc"
}

trap on_error ERR

exec 9>"$LOCK_FILE"
flock -n 9 || die "another switchover process holds ${LOCK_FILE}"

require_command() {
  command -v "$1" >/dev/null 2>&1 || die "required command not found: $1"
}

shell_quote_command() {
  local arg quoted='' result=''
  for arg in "$@"; do
    printf -v quoted '%q' "$arg"
    result+=" ${quoted}"
  done
  printf '%s' "${result# }"
}

remote() {
  local host=$1
  shift
  local cmd
  cmd=$(shell_quote_command "$@")
  ssh "${SSH_OPTS[@]}" "${SSH_USER}@${host}" "$cmd"
}

psql_on() {
  local host=$1 port=$2 sql=$3
  printf '%s\n' "$sql" |
    ssh "${SSH_OPTS[@]}" "${SSH_USER}@${host}" \
      "${PSQL_BASE[*]} -h 127.0.0.1 -p $(printf '%q' "$port") -U $(printf '%q' "$PGUSER") -d $(printf '%q' "$PGDATABASE") -f -"
}

run_hook() {
  local command_text=$1 description=$2

  if [[ -z $command_text ]]; then
    if (( ALLOW_EMPTY_APP_HOOKS == 1 )); then
      log "WARNING: no command configured for ${description}; explicit laboratory override is active."
      return 0
    fi
    die "no command configured for ${description}"
  fi

  log "Executing ${description}: ${command_text}"
  bash -o pipefail -c "$command_text"
}

parse_node() {
  local row=$1
  IFS='|' read -r NODE_MEMBER NODE_HOST NODE_PORT NODE_CFG NODE_SITE NODE_SCOPE <<<"$row"
}

lookup_member() {
  local wanted=$1 row
  for row in "${NODES[@]}"; do
    parse_node "$row"
    if [[ $NODE_MEMBER == "$wanted" ]]; then
      MEMBER_HOST=$NODE_HOST
      MEMBER_PORT=$NODE_PORT
      MEMBER_CFG=$NODE_CFG
      MEMBER_SITE=$NODE_SITE
      MEMBER_SCOPE=$NODE_SCOPE
      return 0
    fi
  done
  die "member not found in inventory: ${wanted}"
}

representative_for_site() {
  local wanted=$1 row
  for row in "${NODES[@]}"; do
    parse_node "$row"
    if [[ $NODE_SITE == "$wanted" ]]; then
      REP_HOST=$NODE_HOST
      REP_CFG=$NODE_CFG
      REP_SCOPE=$NODE_SCOPE
      return 0
    fi
  done
  die "site not found in inventory: ${wanted}"
}

nodes_in_site() {
  local wanted=$1 row
  for row in "${NODES[@]}"; do
    parse_node "$row"
    [[ $NODE_SITE == "$wanted" ]] && printf '%s|%s|%s|%s|%s|%s\n' \
      "$NODE_MEMBER" "$NODE_HOST" "$NODE_PORT" "$NODE_CFG" "$NODE_SITE" "$NODE_SCOPE"
  done
}

patroni_json() {
  local host=$1 cfg=$2 scope=$3 json
  json=$(remote "$host" patronictl -c "$cfg" list "$scope" -f json)
  [[ -n $json ]] || die "empty patronictl JSON from ${host}"
  python3 -c 'import json,sys; json.load(sys.stdin)' <<<"$json" >/dev/null || {
    printf '%s\n' "$json" >&2
    die "invalid patronictl JSON from ${host} using ${cfg}"
  }
  printf '%s\n' "$json"
}

cluster_kind_and_leader() {
  local host=$1 cfg=$2 scope=$3 json
  json=$(patroni_json "$host" "$cfg" "$scope")
  python3 -c '
import json, sys
members = json.load(sys.stdin)
leaders = [m for m in members if m.get("Role") in ("Leader", "Standby Leader")]
if len(leaders) != 1:
    print("invalid none")
    raise SystemExit(0)
leader = leaders[0]
kind = "primary" if leader.get("Role") == "Leader" else "standby"
print(kind, leader.get("Member", "none"))
' <<<"$json"
}

service_active() {
  local host=$1
  remote "$host" "${REMOTE_SUDO[@]}" systemctl is-active --quiet "$PATRONI_SERVICE"
}

stop_patroni_site() {
  local site=$1 member host port cfg node_site scope
  while IFS='|' read -r member host port cfg node_site scope; do
    log "Stopping ${PATRONI_SERVICE} on ${member} (${host})."
    remote "$host" "${REMOTE_SUDO[@]}" systemctl stop "$PATRONI_SERVICE"
  done < <(nodes_in_site "$site")
}

start_patroni_site() {
  local site=$1 member host port cfg node_site scope
  while IFS='|' read -r member host port cfg node_site scope; do
    log "Starting ${PATRONI_SERVICE} on ${member} (${host})."
    remote "$host" "${REMOTE_SUDO[@]}" systemctl start "$PATRONI_SERVICE"
  done < <(nodes_in_site "$site")
}

verify_site_fenced() {
  local site=$1 member host port cfg node_site scope status
  while IFS='|' read -r member host port cfg node_site scope; do
    if service_active "$host"; then
      die "Patroni is still active on ${member} (${host})"
    fi
    status=$(remote "$host" pg_isready -h 127.0.0.1 -p "$port" -t 3 || true)
    if [[ $status != *'no response'* ]]; then
      die "PostgreSQL may still be reachable on ${member} (${host}:${port}): ${status}"
    fi
    log "Fenced: ${member} (${host}:${port})"
  done < <(nodes_in_site "$site")
}

clear_source_read_only() {
  if [[ -n ${SRC_HOST:-} && -n ${SRC_PORT:-} ]]; then
    psql_on "$SRC_HOST" "$SRC_PORT" \
      "ALTER SYSTEM RESET default_transaction_read_only; SELECT pg_reload_conf();" >/dev/null
    log 'Source default_transaction_read_only override cleared.'
  fi
}

wait_for_replay_lsn() {
  local host=$1 port=$2 target_lsn=$3
  local deadline=$((SECONDS + LSN_WAIT_SECONDS)) result replay received paused diff

  while (( SECONDS < deadline )); do
    result=$(psql_on "$host" "$port" "
      SELECT
        pg_is_in_recovery()::text || '|' ||
        COALESCE(pg_last_wal_receive_lsn()::text, '') || '|' ||
        COALESCE(pg_last_wal_replay_lsn()::text, '') || '|' ||
        pg_is_wal_replay_paused()::text || '|' ||
        CASE
          WHEN pg_last_wal_replay_lsn() >= '${target_lsn}'::pg_lsn THEN 't'
          ELSE 'f'
        END || '|' ||
        COALESCE(pg_wal_lsn_diff('${target_lsn}'::pg_lsn, pg_last_wal_replay_lsn())::text, '');")

    IFS='|' read -r in_recovery received replay paused reached diff <<<"$result"

    [[ $in_recovery == t ]] || die "destination is no longer in recovery before promotion"
    [[ $paused == f ]] || die "destination WAL replay is paused"

    log "Destination receive=${received:-NULL}, replay=${replay:-NULL}, remaining_bytes=${diff:-unknown}."

    if [[ $reached == t ]]; then
      DEST_REPLAY_LSN=$replay
      return 0
    fi
    sleep 2
  done

  die "destination did not replay target LSN ${target_lsn} within ${LSN_WAIT_SECONDS}s"
}

wait_for_role() {
  local host=$1 port=$2 expected_recovery=$3 timeout=$4 description=$5
  local deadline=$((SECONDS + timeout)) actual
  while (( SECONDS < deadline )); do
    if actual=$(psql_on "$host" "$port" 'SELECT pg_is_in_recovery();' 2>/dev/null); then
      if [[ $actual == "$expected_recovery" ]]; then
        log "Role confirmed: ${description}."
        return 0
      fi
    fi
    sleep 2
  done
  die "timed out waiting for ${description}"
}

# -----------------------------------------------------------------------------
# 0. Local and remote preflight
# -----------------------------------------------------------------------------
PHASE='preflight'
for cmd in ssh python3 flock tee date bash; do
  require_command "$cmd"
done

log "Run ID: ${RUN_ID}"
log "Log file: ${LOG_FILE}"

for row in "${NODES[@]}"; do
  parse_node "$row"
  log "Checking SSH and required commands on ${NODE_MEMBER} (${NODE_HOST})."
  remote "$NODE_HOST" true
  remote "$NODE_HOST" test -r "$NODE_CFG"
  remote "$NODE_HOST" command -v patronictl >/dev/null
  remote "$NODE_HOST" command -v psql >/dev/null
  remote "$NODE_HOST" command -v pg_isready >/dev/null
  remote "$NODE_HOST" command -v systemctl >/dev/null
  remote "$NODE_HOST" "${REMOTE_SUDO[@]}" -v
done

# -----------------------------------------------------------------------------
# 1. Detect the current topology
# -----------------------------------------------------------------------------
PHASE='topology-detection'
representative_for_site dc1
DC1_REP_HOST=$REP_HOST
DC1_REP_CFG=$REP_CFG
DC1_SCOPE=$REP_SCOPE

representative_for_site dc2
DC2_REP_HOST=$REP_HOST
DC2_REP_CFG=$REP_CFG
DC2_SCOPE=$REP_SCOPE

read -r DC1_KIND DC1_LEADER < <(cluster_kind_and_leader "$DC1_REP_HOST" "$DC1_REP_CFG" "$DC1_SCOPE")
read -r DC2_KIND DC2_LEADER < <(cluster_kind_and_leader "$DC2_REP_HOST" "$DC2_REP_CFG" "$DC2_SCOPE")

log "DC1 kind=${DC1_KIND}, leader=${DC1_LEADER}."
log "DC2 kind=${DC2_KIND}, leader=${DC2_LEADER}."

if [[ $DC1_KIND == primary && $DC2_KIND == standby ]]; then
  SRC_SITE=dc1; SRC_LEADER=$DC1_LEADER; SRC_REP_HOST=$DC1_REP_HOST
  SRC_REP_CFG=$DC1_REP_CFG; SRC_SCOPE=$DC1_SCOPE
  DST_SITE=dc2; DST_LEADER=$DC2_LEADER; DST_REP_HOST=$DC2_REP_HOST
  DST_REP_CFG=$DC2_REP_CFG; DST_SCOPE=$DC2_SCOPE
elif [[ $DC2_KIND == primary && $DC1_KIND == standby ]]; then
  SRC_SITE=dc2; SRC_LEADER=$DC2_LEADER; SRC_REP_HOST=$DC2_REP_HOST
  SRC_REP_CFG=$DC2_REP_CFG; SRC_SCOPE=$DC2_SCOPE
  DST_SITE=dc1; DST_LEADER=$DC1_LEADER; DST_REP_HOST=$DC1_REP_HOST
  DST_REP_CFG=$DC1_REP_CFG; DST_SCOPE=$DC1_SCOPE
else
  die "expected one primary and one standby cluster; DC1=${DC1_KIND}, DC2=${DC2_KIND}"
fi

lookup_member "$SRC_LEADER"
SRC_HOST=$MEMBER_HOST; SRC_PORT=$MEMBER_PORT; SRC_CFG=$MEMBER_CFG

lookup_member "$DST_LEADER"
DST_HOST=$MEMBER_HOST; DST_PORT=$MEMBER_PORT; DST_CFG=$MEMBER_CFG

log "Direction: ${SRC_SITE}/${SRC_LEADER} -> ${DST_SITE}/${DST_LEADER}."

# -----------------------------------------------------------------------------
# 2. Validate identity and replication health
# -----------------------------------------------------------------------------
PHASE='replication-validation'
SRC_SYSID=$(psql_on "$SRC_HOST" "$SRC_PORT" \
  "SELECT system_identifier FROM pg_control_system();")
DST_SYSID=$(psql_on "$DST_HOST" "$DST_PORT" \
  "SELECT system_identifier FROM pg_control_system();")
[[ -n $SRC_SYSID && $SRC_SYSID == "$DST_SYSID" ]] || \
  die "system identifier mismatch: source=${SRC_SYSID:-NULL}, destination=${DST_SYSID:-NULL}"

DST_HEALTH=$(psql_on "$DST_HOST" "$DST_PORT" "
  SELECT
    pg_is_in_recovery()::text || '|' ||
    pg_is_wal_replay_paused()::text || '|' ||
    COALESCE((SELECT status FROM pg_stat_wal_receiver LIMIT 1), 'none') || '|' ||
    COALESCE(pg_last_wal_receive_lsn()::text, '') || '|' ||
    COALESCE(pg_last_wal_replay_lsn()::text, '');")
IFS='|' read -r DST_RECOVERY DST_PAUSED DST_RECEIVER DST_RECEIVE_LSN DST_REPLAY_LSN \
  <<<"$DST_HEALTH"

[[ $DST_RECOVERY == t ]] || die 'destination is not in recovery'
[[ $DST_PAUSED == f ]] || die 'destination WAL replay is paused'
[[ $DST_RECEIVER == streaming ]] || \
  die "destination WAL receiver status is ${DST_RECEIVER}, expected streaming"
[[ -n $DST_RECEIVE_LSN && -n $DST_REPLAY_LSN ]] || \
  die 'destination receive/replay LSN is NULL'

WAL_HINTS=$(psql_on "$SRC_HOST" "$SRC_PORT" "SHOW wal_log_hints;")
CHECKSUMS=$(psql_on "$SRC_HOST" "$SRC_PORT" "SELECT current_setting('data_checksums');")
if [[ $WAL_HINTS != on && $CHECKSUMS != on ]]; then
  die 'pg_rewind prerequisite missing: neither wal_log_hints nor data_checksums is enabled'
fi

PREPARED_COUNT=$(psql_on "$SRC_HOST" "$SRC_PORT" \
  'SELECT count(*) FROM pg_prepared_xacts;')
[[ $PREPARED_COUNT == 0 ]] || \
  die "source has ${PREPARED_COUNT} prepared transactions; resolve them before switchover"

log "System identifier=${SRC_SYSID}; receiver=${DST_RECEIVER}; replay=${DST_REPLAY_LSN}."

# -----------------------------------------------------------------------------
# 3. Quiesce application traffic and database writes
# -----------------------------------------------------------------------------
PHASE='application-quiesce'
run_hook "$APP_QUIESCE_CMD" 'application write quiesce'
APP_QUIESCED=1

psql_on "$SRC_HOST" "$SRC_PORT" "
  ALTER SYSTEM SET default_transaction_read_only = on;
  SELECT pg_reload_conf();" >/dev/null

READ_ONLY=$(psql_on "$SRC_HOST" "$SRC_PORT" 'SHOW default_transaction_read_only;')
[[ $READ_ONLY == on ]] || die 'failed to enable default_transaction_read_only on source'

psql_on "$SRC_HOST" "$SRC_PORT" "
  SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE pid <> pg_backend_pid()
    AND backend_type = 'client backend';" >/dev/null

for _ in $(seq 1 30); do
  ACTIVE_XACT=$(psql_on "$SRC_HOST" "$SRC_PORT" "
    SELECT count(*) FROM pg_stat_activity
    WHERE pid <> pg_backend_pid() AND xact_start IS NOT NULL;")
  [[ $ACTIVE_XACT == 0 ]] && break
  sleep 1
done
[[ $ACTIVE_XACT == 0 ]] || \
  die "${ACTIVE_XACT} transactions remain active after quiesce"

PREPARED_COUNT=$(psql_on "$SRC_HOST" "$SRC_PORT" \
  'SELECT count(*) FROM pg_prepared_xacts;')
[[ $PREPARED_COUNT == 0 ]] || \
  die "${PREPARED_COUNT} prepared transactions appeared during quiesce"

sleep 5
CLIENT_COUNT=$(psql_on "$SRC_HOST" "$SRC_PORT" "
  SELECT count(*) FROM pg_stat_activity
  WHERE pid <> pg_backend_pid() AND backend_type = 'client backend';")
[[ $CLIENT_COUNT == 0 ]] || \
  die "${CLIENT_COUNT} client backends reconnected; application fencing is incomplete"

log 'Application writes quiesced; no active or prepared transactions remain.'

# -----------------------------------------------------------------------------
# 4. Establish and verify the final durable WAL boundary
# -----------------------------------------------------------------------------
PHASE='rpo-zero-gate'
FINAL_SOURCE_LSN=$(psql_on "$SRC_HOST" "$SRC_PORT" "
  CHECKPOINT;
  SELECT pg_switch_wal();
  SELECT pg_current_wal_flush_lsn();" | tail -n 1)

[[ $FINAL_SOURCE_LSN =~ ^[0-9A-F]+/[0-9A-F]+$ ]] || \
  die "invalid final source LSN: ${FINAL_SOURCE_LSN:-NULL}"

log "Final durable source LSN=${FINAL_SOURCE_LSN}."
wait_for_replay_lsn "$DST_HOST" "$DST_PORT" "$FINAL_SOURCE_LSN"
log "RPO 0 gate passed: destination replay=${DEST_REPLAY_LSN} >= source flush=${FINAL_SOURCE_LSN}."

ACTIVE_XACT=$(psql_on "$SRC_HOST" "$SRC_PORT" "
  SELECT count(*) FROM pg_stat_activity
  WHERE pid <> pg_backend_pid() AND xact_start IS NOT NULL;")
[[ $ACTIVE_XACT == 0 ]] || die "a transaction appeared after final LSN capture"

# -----------------------------------------------------------------------------
# 5. Fence the entire old primary site
# -----------------------------------------------------------------------------
PHASE='source-fencing'
stop_patroni_site "$SRC_SITE"
verify_site_fenced "$SRC_SITE"
SOURCE_FENCED=1
log "Source site ${SRC_SITE} is fenced."

# -----------------------------------------------------------------------------
# 6. Promote the destination standby cluster
# -----------------------------------------------------------------------------
PHASE='destination-promotion'
remote "$DST_REP_HOST" patronictl -c "$DST_REP_CFG" \
  promote-cluster "$DST_SCOPE" --force
wait_for_role "$DST_HOST" "$DST_PORT" f "$PROMOTION_WAIT_SECONDS" \
  "destination ${DST_LEADER} as writable primary"
DESTINATION_PROMOTED=1

psql_on "$DST_HOST" "$DST_PORT" "
  ALTER SYSTEM RESET default_transaction_read_only;
  SELECT pg_reload_conf();" >/dev/null

NEW_READ_ONLY=$(psql_on "$DST_HOST" "$DST_PORT" 'SHOW default_transaction_read_only;')
[[ $NEW_READ_ONLY == off ]] || die "new primary remains read-only: ${NEW_READ_ONLY}"

NEW_TIMELINE=$(psql_on "$DST_HOST" "$DST_PORT" \
  'SELECT timeline_id FROM pg_control_checkpoint();')
NEW_LSN=$(psql_on "$DST_HOST" "$DST_PORT" 'SELECT pg_current_wal_flush_lsn();')
log "Destination promoted: timeline=${NEW_TIMELINE}, flush_lsn=${NEW_LSN}."

# -----------------------------------------------------------------------------
# 7. Controlled database write validation
# -----------------------------------------------------------------------------
PHASE='new-primary-validation'
if (( POST_PROMOTION_WRITE_TEST == 1 )); then
  psql_on "$DST_HOST" "$DST_PORT" "
    CREATE TABLE IF NOT EXISTS public.patroni_switchover_validation (
      run_id text PRIMARY KEY,
      written_at timestamptz NOT NULL DEFAULT clock_timestamp(),
      server_address inet,
      server_port integer,
      timeline_id bigint NOT NULL
    );
    INSERT INTO public.patroni_switchover_validation
      (run_id, server_address, server_port, timeline_id)
    VALUES
      ('${RUN_ID}', inet_server_addr(), inet_server_port(),
       (SELECT timeline_id FROM pg_control_checkpoint()))
    ON CONFLICT (run_id) DO UPDATE
      SET written_at = clock_timestamp(),
          server_address = excluded.server_address,
          server_port = excluded.server_port,
          timeline_id = excluded.timeline_id;
    SELECT run_id FROM public.patroni_switchover_validation
    WHERE run_id = '${RUN_ID}';" | grep -Fx "$RUN_ID" >/dev/null
  log 'Controlled write validation succeeded.'
fi

remote "$DST_REP_HOST" patronictl -c "$DST_REP_CFG" list "$DST_SCOPE"

# -----------------------------------------------------------------------------
# 8. Activate application routing to the new primary
# -----------------------------------------------------------------------------
PHASE='application-activation'
run_hook "$APP_ACTIVATE_CMD" 'new-primary application activation'
log 'Application routing activation command completed.'

# -----------------------------------------------------------------------------
# 9. Convert the old primary site into a standby cluster
# -----------------------------------------------------------------------------
PHASE='old-site-demotion'
remote "$SRC_REP_HOST" patronictl -c "$SRC_REP_CFG" edit-config "$SRC_SCOPE" --force \
  -s "standby_cluster.host=${ACTIVE_PRIMARY_ENDPOINT}" \
  -s "standby_cluster.port=${ACTIVE_PRIMARY_PORT}" \
  -s "standby_cluster.primary_slot_name=${PRIMARY_SLOT_NAME}" \
  -s 'standby_cluster.create_replica_methods=[basebackup]'

remote "$SRC_REP_HOST" patronictl -c "$SRC_REP_CFG" show-config "$SRC_SCOPE" | \
  grep -q '^standby_cluster:' || \
  die 'standby_cluster configuration was not committed to the old-site DCS'

lookup_member "$SRC_LEADER"
log "Starting former source leader ${SRC_LEADER} first for controlled rewind/rejoin."
remote "$MEMBER_HOST" "${REMOTE_SUDO[@]}" systemctl start "$PATRONI_SERVICE"

wait_for_role "$MEMBER_HOST" "$MEMBER_PORT" t "$DEMOTION_WAIT_SECONDS" \
  "former source leader ${SRC_LEADER} as standby"

while IFS='|' read -r member host port cfg site scope; do
  [[ $member == "$SRC_LEADER" ]] && continue
  log "Starting old-site member ${member} (${host})."
  remote "$host" "${REMOTE_SUDO[@]}" systemctl start "$PATRONI_SERVICE"
done < <(nodes_in_site "$SRC_SITE")

# -----------------------------------------------------------------------------
# 10. Final topology validation
# -----------------------------------------------------------------------------
PHASE='final-validation'
DEADLINE=$((SECONDS + DEMOTION_WAIT_SECONDS))
while (( SECONDS < DEADLINE )); do
  read -r SRC_FINAL_KIND SRC_FINAL_LEADER < <(
    cluster_kind_and_leader "$SRC_REP_HOST" "$SRC_REP_CFG" "$SRC_SCOPE" \
      || printf 'invalid none\n'
  )
  [[ $SRC_FINAL_KIND == standby ]] && break
  sleep 5
done
[[ ${SRC_FINAL_KIND:-invalid} == standby ]] || \
  die "old site did not become a standby cluster within ${DEMOTION_WAIT_SECONDS}s"

read -r DST_FINAL_KIND DST_FINAL_LEADER < <(
  cluster_kind_and_leader "$DST_REP_HOST" "$DST_REP_CFG" "$DST_SCOPE"
)
[[ $DST_FINAL_KIND == primary ]] || die "destination no longer reports a primary cluster"

lookup_member "$SRC_FINAL_LEADER"
OLD_SITE_RECOVERY=$(psql_on "$MEMBER_HOST" "$MEMBER_PORT" \
  'SELECT pg_is_in_recovery();')
[[ $OLD_SITE_RECOVERY == t ]] || die 'old-site standby leader is not in recovery'

lookup_member "$DST_FINAL_LEADER"
NEW_SITE_RECOVERY=$(psql_on "$MEMBER_HOST" "$MEMBER_PORT" \
  'SELECT pg_is_in_recovery();')
[[ $NEW_SITE_RECOVERY == f ]] || die 'new-site leader is unexpectedly in recovery'

remote "$DST_REP_HOST" patronictl -c "$DST_REP_CFG" list "$DST_SCOPE"
remote "$SRC_REP_HOST" patronictl -c "$SRC_REP_CFG" list "$SRC_SCOPE"

PHASE='complete'
log "SUCCESS: ${DST_SITE}/${DST_FINAL_LEADER} is the writable primary cluster."
log "SUCCESS: ${SRC_SITE}/${SRC_FINAL_LEADER} is the standby cluster."
log "Final source LSN before promotion: ${FINAL_SOURCE_LSN}."
log "Destination replay LSN at RPO gate: ${DEST_REPLAY_LSN}."
log "New primary timeline: ${NEW_TIMELINE}."
log "Review application, replication-slot, archive, backup and monitoring status."

Install and validate syntax:

sudo install -o postgres -g postgres -m 0750 \
  site-switchover-rpo0.sh \
  /etc/patroni/site-switchover-rpo0.sh

sudo -u postgres bash -n /etc/patroni/site-switchover-rpo0.sh

Run:

sudo -u postgres /etc/patroni/site-switchover-rpo0.sh

8. Key script design decisions

No plaintext database password

The script uses ~postgres/.pgpass or certificate/peer authentication. Example .pgpass entry:

127.0.0.1:5432:postgres:postgres:REPLACE_WITH_SECRET

chmod 0600 ~postgres/.pgpass

Final LSN is the durable flush position

The script captures pg_current_wal_flush_lsn() after CHECKPOINT and pg_switch_wal(). This identifies the last WAL position known to be durably flushed on the source. The RPO gate is evaluated with PostgreSQL’s native pg_lsn comparison:

pg_last_wal_replay_lsn() >= '<final-source-lsn>'::pg_lsn

pg_last_wal_replay_lsn() represents WAL already applied during recovery — not just received. receive_lsn >= target is insufficient because WAL buffered in the receiver’s walreceiver process has not necessarily been written to disk or replayed.

Prepared transactions are a hard stop

A two-phase transaction can remain in pg_prepared_xacts after its client session has gone. It may later be committed or rolled back — and that decision affects the correct LSN boundary. The script aborts when prepared transactions exist. The operator must commit or roll them back before proceeding.

The source is stopped before promotion

Systemctl stop succeeds on every source-site member and pg_isready reports no response. Only then does patronictl promote-cluster execute. In higher-assurance environments, replace service stopping with hypervisor power-off, cloud instance stop, SAN fencing, or STONITH/watchdog integration.

Patroni-native cluster conversion

The script writes standby_cluster into the old-site DCS with patronictl edit-config, verifies the configuration is committed, and only then starts an old-site member. This preserves the fencing boundary even if the DCS write completes before the first old-site process starts. patronictl demote-cluster is documented but waits for a running standby leader — it is not appropriate when the old site is already stopped and divergent.

Old site follows a stable endpoint

The old site is demoted to follow postgres-primary.internal.example.com, not the physical hostname of the node that was leader during the switchover. This prevents broken inter-site replication after a subsequent local Patroni switchover on the new active site.

Rollback is phase-sensitive

Before source fencing, the script can reset default_transaction_read_only and invoke the application rollback hook.

After source fencing, automatic rollback is unsafe.

After destination promotion, automatic rollback is prohibited. A new PostgreSQL timeline exists. Keep the former source fenced, validate the new primary, and invoke a separately tested reverse-switchover procedure.

9. Application hook example

#!/usr/bin/env bash
set -Eeuo pipefail

ACTION=${1:?usage: $0 quiesce|activate-new-primary|restore-old-primary}

case "$ACTION" in
  quiesce)
    /usr/local/bin/lb-control disable-current-writer
    /usr/local/bin/app-control stop-writers
    ;;
  activate-new-primary)
    /usr/local/bin/lb-control enable-new-writer
    /usr/local/bin/app-control start-writers
    ;;
  restore-old-primary)
    /usr/local/bin/lb-control enable-original-writer
    /usr/local/bin/app-control start-writers
    ;;
  *)
    echo "Unsupported action: ${ACTION}" >&2
    exit 2
    ;;
esac

Install as a root-owned executable and allow only the required subcommands through restricted sudo. The hook must be idempotent.

10. Pre-change checklist

Architecture and configuration

• DC1 and DC2 use different Patroni scopes
• All member names are globally unique
• DC1 contains three healthy Patroni members
• DC2 contains one healthy standby leader
• The application uses a managed write endpoint
• The standby-cluster upstream endpoint resolves to the current primary
• WAL retention and archive capacity are sufficient

Replication health

• pg_stat_wal_receiver.status = 'streaming' on the standby leader
• pg_is_wal_replay_paused() = false
• Receive and replay LSNs are advancing
• System identifiers match
• wal_log_hints = on or data checksums are enabled
• A full rebuild procedure has been tested

Access and automation

• SSH works non-interactively from execution node to all members
• PostgreSQL administrative authentication path tested
• Restricted sudo service control tested
• Application quiesce hook tested
• Application activation hook tested
• Application rollback hook tested
• Stable primary endpoint validated from both sites

Operational governance

• Maintenance approval recorded
• Application, network, and database teams present
• Current backup and restore validation exists
• Reverse-switchover procedure is approved
• Operator understands the point after which automatic rollback is prohibited

11. Execution gates

12. Post-switchover validation

Confirm a single writable primary

On the new active site:

SELECT
    inet_server_addr(),
    inet_server_port(),
    pg_is_in_recovery(),
    pg_current_wal_flush_lsn();

Expected: pg_is_in_recovery = false

On the old site standby leader:

SELECT
    inet_server_addr(),
    inet_server_port(),
    pg_is_in_recovery(),
    pg_last_wal_receive_lsn(),
    pg_last_wal_replay_lsn(),
    pg_last_xact_replay_timestamp();

Expected: pg_is_in_recovery = true

Validate Patroni topology

patronictl -c /etc/patroni/dc2-pg4.yml list postgres-dc2
patronictl -c /etc/patroni/dc1-pg1.yml list postgres-dc1

Expected: new site shows one Leader; old site shows one Standby Leader; no node reports an unexpected primary role.

Validate replication from the new primary

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication
ORDER BY application_name;

Validate the application path

Connect through the application endpoint, not the physical node address:

SELECT
    inet_server_addr(),
    inet_server_port(),
    pg_is_in_recovery(),
    current_timestamp;

Then perform a controlled application transaction and validate it through the application’s normal read path.

Validate operational services

• Connection pools point to the new site
• Scheduled jobs are enabled only on the intended primary
• ETL and integration writers are active
• Backup jobs target the new primary
• WAL archive status is healthy
• Replication slots are active and not retaining excessive WAL
• Monitoring recognizes the new topology
• Application latency and errors are normal

13. Failure scenarios

Before application quiesce

Impact: none. Correct the preflight problem and rerun.

After quiesce but before source fencing

The old primary is still authoritative. Reset the read-only override, restore the old application route, validate writes, and investigate the failed gate.

Destination does not reach the final source LSN

Do not promote. Investigate on both sides:

-- Source
SELECT * FROM pg_stat_replication;

-- Destination
SELECT * FROM pg_stat_wal_receiver;
SELECT
    pg_last_wal_receive_lsn(),
    pg_last_wal_replay_lsn(),
    pg_get_wal_replay_pause_state();

Common causes: network interruption, missing WAL, replay pause, recovery conflict, storage latency, broken replication slot.

After source fencing but before destination promotion

Do not automatically restart DC1 and do not blindly promote DC2. Inspect both Patroni scopes, verify DC1 remains stopped, verify the final replay LSN on DC2, then either complete the promotion or formally abort by restarting only the still-authoritative DC1 cluster before any promotion occurred.

After destination promotion

DC2 is authoritative because a new timeline has been created. Keep DC1 fenced. Do not redirect clients back to DC1. Validate and repair DC2. Rejoin DC1 using pg_rewind or a full rebuild. Use a separately controlled reverse switchover to return service later.

pg_rewind fails

Do not start the divergent data directory as a primary. Reinitialize the affected member:

patronictl -c /etc/patroni/dc1-pg1.yml reinit postgres-dc1 pg1 --force --wait

Repeat for each failed old-site member. A full rebuild can consume significant network, storage, and WAL capacity — size and test it before production adoption.

14. Limitations of the 3+1 design

DC2 has no local PostgreSQL HA. With one PostgreSQL node in DC2, a failure of pg4 after promotion causes an outage. Patroni cannot fail over locally. Maintenance on pg4 requires downtime or a site switchback. For production resilience, DC2 should contain at least two PostgreSQL members with a properly designed DCS quorum.

Planned RPO 0 is not disaster RPO 0. The script achieves zero data loss only when the source is healthy enough to stop writes, flush WAL, and allow the destination to replay the final position before fencing. An abrupt DC1 loss can lose transactions committed locally but not yet received at DC2. Eliminating that risk requires synchronous inter-site commit.

Routing is an external dependency. PostgreSQL and Patroni cannot guarantee that every application, job, or DBA tool uses the intended endpoint. The application fencing hook must cover all write paths.

pg_rewind is not guaranteed. Even with checksums or wal_log_hints, rewind can fail due to missing WAL, damaged files, or configuration errors. Full reinitialization remains the fallback.

15. Recommended improvements for stronger designs

• Add a second PostgreSQL node in DC2 for local HA
• Deploy a properly distributed DCS architecture appropriate to the failure domains
• Use hypervisor or cloud fencing rather than relying only on service stops
• Use a stable primary service endpoint with health checks based on Patroni’s primary REST endpoint
• Manage replication slots permanently through Patroni’s slots configuration
• Archive WAL to storage accessible from both sites
• Test pg_rewind and full reinitialization quarterly
• Store switchover evidence centrally: final source LSN, destination replay LSN, source fencing results, old and new timelines, Patroni topology before and after, application test result
• Implement a separately reviewed reverse-switchover script

The two decisive controls

Without both of these, a Patroni site switchover cannot credibly claim RPO 0 or split-brain prevention:

1. Destination replay LSN >= final source flush LSN — verified with pg_lsn comparison before fencing
2. Old primary site fenced before destination promotion — Patroni stopped and PostgreSQL unreachable on every source-site member

Everything else in the procedure exists to make those two controls reachable, verifiable, and recoverable when they fail.