compose-files/postgresql/cutover/cutover.sh

#!/usr/bin/env bash
# ADR-0001 Phase 3 Cutover — cutover.sh
#
# Run DIRECTLY on docker-2 via SSH (NOT via the LXC-exec MCP tool — several
# phases here run well past its ~30s timeout):
#
#   ssh root@192.168.4.32
#   bash /volume1/docker/compose-files/postgresql/cutover/cutover.sh
#
# Implements RUNBOOK.md's 11 phases. Any failed check triggers immediate,
# automatic invocation of rollback.sh and the script exits nonzero — no
# manual intervention should be required to reach a safe state. Designed
# to be run unattended, but given the blast radius (production DB, this
# chat's own dependency on it, ~13 consumers) watching it live the first
# time is strongly recommended anyway.
#
# ── Design notes on HOW checks are implemented (read before editing) ──────
#
# Node locality: plain `docker exec` only works for containers whose task
# is actually scheduled on the node you run it from — Swarm does NOT proxy
# exec across nodes. patroni-0/1/haproxy/etcd-1/3 may land on docker-1 or
# docker-3, not docker-2. Two techniques are used throughout to work
# around this, both already validated in the ADR-0001 dry run:
#   1. Legacy's own container IS pinned to docker-2 (see postgresql.yaml:
#      node.hostname == docker-2) and shares postgresql_db-backend with
#      every HA-stack container — so `docker exec <legacy_cid> ...` is
#      always available locally and can reach any HA container by its
#      Swarm-DNS hostname (patroni-0, patroni-1, haproxy, etcd-2, etc.).
#      This is the SAME proxy pattern documented in the ADR-0001 note
#      ("docker exec <legacy container> psql -h patroni-1 -p 5432 ...").
#   2. `docker service ps <service>` and `docker service logs <service>`
#      work cluster-wide from any manager regardless of which node the
#      task landed on — used for stability/health signal checks that
#      don't need to reach inside the container.
# Patroni REST calls (port 8008) are made via bash's `/dev/tcp` raw-socket
# trick executed inside the legacy container, NOT via curl — this avoids
# assuming curl is present in the (Debian-slim) postgres:17 legacy image.
#
# ⚠️  DEVIATION FLAGGED FOR OPERATOR REVIEW — Phase 5 (promotion):
# The ADR-0001 dry run promoted by removing the standby_cluster key
# directly via etcdctl (patronictl had an unrelated config-loader quirk
# in that environment). This script instead uses Patroni's own official,
# documented REST API mechanism: `PATCH /config` with `{"standby_cluster":
# null}`, which is Patroni's sanctioned way to remove a dynamic-config
# key (see Patroni docs, REST API section). This avoids needing jq/python
# inside the minimal etcd image to hand-edit YAML, and avoids the
# patronictl quirk entirely — but it was NOT the exact mechanism proven
# in the dry run. A manual fallback (raw etcdctl, matching the dry-run
# method) is documented below and in RUNBOOK.md in case this PATCH
# doesn't behave as expected. Recommend a low-stakes smoke test (e.g. a
# GET /config call, done automatically below before the real PATCH) and
# operator attention specifically during this phase.
#
# Manual fallback for Phase 5 if the PATCH approach ever needs bypassing:
#   docker exec <etcd-2_cid> etcdctl --endpoints=http://etcd-1:2379,http://etcd-2:2379,http://etcd-3:2379 \
#     get /service/postgres-ha/config --print-value-only
#   (hand-edit the returned YAML to remove the standby_cluster block, then)
#   docker exec <etcd-2_cid> etcdctl --endpoints=... put /service/postgres-ha/config -- "<edited YAML>"
# etcd-2 is guaranteed to be on docker-2 (node.hostname == docker-2
# constraint in both staging/final yaml), so this exec always works
# locally if needed.
#
# ── FIX LOG (post-first-real-run) ──────────────────────────────────────
# 2026 run #1 aborted at Phase 1's node-label check with a false negative
# despite labels genuinely being set (docker-2=primary, docker-3=replica,
# confirmed via `docker node inspect --format '{{json .Spec.Labels}}'`).
# Root cause: the original check used
#   docker node inspect {} --format '{{.Spec.Labels.pg-role}}'
# — Go's template engine cannot parse a bare `.pg-role` field reference
# (the hyphen is invalid in that position), so the command errored
# silently (stderr redirected to /dev/null) and always returned empty,
# regardless of actual label state. FIXED below by switching to
# `docker node ls --filter "node.label=pg-role=primary"`, which uses
# Docker's own filter syntax instead of Go template field access and
# sidesteps the hyphen problem entirely.
# Separately, rollback.sh's automatic consumer re-verification (triggered
# by this false-negative rollback) flagged woodpecker's healthz as
# "failed" because it returned HTTP 204 — a legitimate, common
# "healthy, no content" response for that kind of endpoint. The health
# checks below (and the matching ones in rollback.sh) now accept any 2xx
# status code, not just literal 200.
#
# 2026 run #2 hit TWO further real bugs in Phase 1/2, both fixed below:
#   a) Phase 1 hardcoded patroni-1 as the expected standby_leader. The
#      Patroni/etcd bootstrap-race winner is actually nondeterministic —
#      it's a lock race, not something the compose file controls. The
#      ADR-0001 dry run happened to have patroni-1 win it, but the
#      earlier real production attempt (see ADR-0001 note) had patroni-0
#      win instead — proof both outcomes genuinely occur. A check that
#      only ever accepts patroni-1 fails outright half the time even when
#      the cluster is perfectly healthy. FIXED by polling BOTH patroni-0
#      AND patroni-1 every iteration; whichever reports standby_leader
#      first is captured as $LEADER_HOST, the other becomes
#      $REPLICA_HOST. Every phase from here on (2,3,4,5,6,8,10) uses
#      these two variables instead of a hardcoded hostname.
#   b) Phase 1's wait window (240s) and Phase 2's (300s) were both far
#      shorter than the real observed basebackup duration for this
#      cluster's ~42GB dataset — prior polling (both the dry run and the
#      earlier real attempt) showed a full basebackup_fast_xlog copy
#      takes roughly 8-11 minutes. The role only flips to
#      standby_leader/replica AFTER the copy fully completes, so both
#      timeouts could fire (and did) while a completely legitimate
#      basebackup was still in progress, triggering a false-negative
#      auto-rollback mid-copy. FIXED: both windows extended (and later
#      re-tuned further by direct operator edits in git — see commit
#      history for exact current values of PHASE1_MAX_WAIT/
#      PHASE2_MAX_WAIT) with a per-poll `du -sh` (timeout-guarded, since
#      du has been observed to hang on CephFS under heavy concurrent
#      write load) logged on every iteration, so progress is directly
#      observable instead of a silent binary wait.
#
# 2026 run #3 follow-up (operator request, no new failure observed yet):
#   Phase 2's cascade-replica bootstrap adds an EXTRA network/streaming
#   hop on top of the same basebackup workload Phase 1 already does
#   (patroni-0/1's loser bootstraps FROM the winner, not from legacy
#   directly) — plausibly slower than Phase 1's copy, not just equal to
#   it. Phase 2's window was extended further than Phase 1's for this
#   reason (see current PHASE2_MAX_WAIT value below).
#
# 2026 run #4 — Phase 2's lag check was a SILENT NO-OP; found after a
# real run passed Phase 1/2 cleanly (patroni-0 reached standby_leader,
# patroni-1 basebackup completed, role flipped to "replica") but then
# failed Phase 3's canary write with "Canary row did not propagate to
# patroni-1 within 5s" moments later. Root cause: `patroni_lag()` queried
# the REPLICA's OWN `/patroni` endpoint for lag/replication_state — but
# Patroni only exposes per-connection replication lag (derived from
# pg_stat_replication) on the LEADER/standby_leader side; a plain
# replica's own `/patroni` response has no such field at all. So
# $LAG_INFO was ALWAYS empty, and Phase 2's gate:
#   [ "$REPLICA_ROLE" = "replica" ] && { [ -z "$LAG_INFO" ] || ... ; }
# short-circuited permanently true on the `[ -z "$LAG_INFO" ]` branch —
# the lag check never actually ran. Phase 2 degraded to "did the role
# flip to replica 3 times in a row?", which can be true BEFORE the
# replica has actually caught up on WAL backlog from its own basebackup,
# especially right after a fresh cascade connection. Phase 3's real
# canary write then landed in exactly that still-catching-up window.
# FIXED: replaced patroni_lag() with cluster_member_lag_state(), which
# queries the LEADER's `/cluster` endpoint (works from either node,
# reflects live DCS state — same endpoint/shape directly verified in the
# original ADR-0001 dry run: member entries look like
# {"name": "patroni-1", "role": "replica", "state": "streaming", "lag": 0})
# and extracts the SPECIFIC replica's "state"/"lag" fields from its own
# member object (members are flat JSON objects with no nested braces, so
# a bounded grep safely isolates exactly one member's fragment). Phase
# 2's gate now requires literal state=streaming AND (lag=0 or lag field
# absent — some responses omit it when lag is genuinely 0, per the
# ADR-0001 note), not just "field happened to be empty because we asked
# the wrong node." Phase 4's (informational, non-gating) lag log line
# updated to use the same corrected function for consistency.
#
# 2026 run #5 — BOTH nodes stuck forever on "waiting for standby_leader
# to bootstrap", never even attempting to race for the role, on a run
# that started completely fresh (operator confirmed no other changes).
# Root cause: a PRIOR run had been interrupted (operator stopped it
# short) without a clean rollback.sh pass, leaving the etcd data
# directories (etcd-1/2/3-data) non-empty on disk even though the
# postgresqlha STACK itself was already gone. etcd's own persisted raft
# state still had /service/postgres-ha/initialize set (a real system
# identifier from the earlier attempt) plus a /service/postgres-ha/status
# key listing old replication slots — so when the new run's patroni-0/1
# containers (with genuinely fresh, empty PGDATA) came up against this
# NOT-actually-fresh etcd, Patroni correctly concluded "this cluster
# already exists, someone else must already be the leader" and both
# nodes deferred forever waiting for a leader that could never appear
# (nobody actually holds the lock). rollback.sh's own data-dir wipe only
# runs when it detects the postgresqlha stack IS currently present (its
# Case D/E paths) — if the stack was already gone by the time rollback
# logic ran (e.g. an operator-initiated stop before rollback.sh got a
# chance to run), its Case A path does NOT wipe data dirs, leaving
# exactly this trap for the next run. FIXED: added a "Pre-Phase-0" check
# (below, before preflight.sh even runs) that detects a still-present
# postgresqlha stack OR non-empty etcd-*/patroni-*-data directories left
# over from a prior run, and — since this is destructive (wipes data) and
# the operator explicitly asked for this to be an interactive decision,
# not silently automatic — PROMPTS before cleaning up. See that section
# for the exact detection logic, the prompt itself, and the
# AUTO_CLEANUP=yes escape hatch for unattended re-runs where the operator
# has already decided cleanup is always wanted.
#
# 2026 run #6 (addition, no new run yet) — SESSION LOGGING added. Every
# prior investigation into a failed run depended entirely on someone
# having a live terminal open and scrolled back far enough, or on the
# operator's own memory. This is fragile, especially for the one
# genuinely dangerous scenario every phase above is built to avoid ever
# reaching manually: rollback.sh itself failing partway (see
# trigger_rollback() below — this is the one case RUNBOOK.md section 4
# says requires manual intervention). Without a persisted transcript,
# investigating THAT scenario after the fact means reconstructing what
# happened from partial memory of a scrollback buffer that may already be
# gone. FIXED: this script now writes its ENTIRE stdout/stderr transcript
# (all 11 phases, plus Pre-Phase-0 and Phase 0's preflight.sh output, plus
# any triggered rollback.sh output) to a single timestamped file in
# BACKUP_DIR — the SAME directory the pg_dumpall backup itself lands in,
# so the backup and the transcript of the run that produced/needed it are
# always sitting right next to each other. See "SESSION LOGGING" comment
# below for implementation detail. See ADR-0001 note, Session Update 9.
#
# 2026 run #7 — After the underlying Ceph IOPS problem (tracked/fixed
# separately, not a script issue) was resolved, a real run finally got
# CLEANLY past Phase 1 AND Phase 2 (basebackup ~690s, streaming lag=0
# confirmed) but failed Phase 3 within 6 seconds of Phase 2 passing:
# "Canary row did not propagate to patroni-1 within 5s." This is the
# SECOND time this exact Phase 3 failure has been seen with Phase 1/2
# genuinely healthy beforehand (see 2026 run #4 above for the first,
# which had a different — since-fixed — root cause in the lag check
# itself). Investigating this run's session log revealed a diagnostic
# gap: the per-attempt `psql -h ${REPLICA_HOST} ... SELECT 1 FROM
# _cutover_canary ...` check discarded stderr entirely (`2>/dev/null`),
# so when the row failed to appear, the log showed only "row not found"
# with NO indication of WHY — a genuine multi-second replication delay
# on this specific DDL+INSERT and an outright connection/auth/permission
# error to patroni-1 look IDENTICAL in that log, and there was no way to
# tell them apart after the fact. FIXED: each attempt's stderr is now
# captured to /tmp/cutover_phase3_attempt_<N>.stderr (mirrors the
# existing Phase 8 pattern below) AND echoed into the log per-attempt
# alongside the (possibly empty) result, so a future failure's log alone
# should show definitively whether this is a genuine propagation delay
# or a real connection-level error. The final trigger_rollback() message
# on failure also now includes the last non-empty stderr seen, so it's
# visible directly in the FAIL line without needing to scroll back.
#
# 2026 run #8 — The 2026 run #7 stderr-capture fix immediately paid off:
# the very next run's Phase 3 failure log showed, on every single one of
# 5 attempts:
#   Password for user PGadmin:
#   psql: error: connection to server at "patroni-1" (...), port 5432
#   failed: fe_sendauth: no password supplied
# This was NEVER a replication/timing issue at all. ROOT CAUSE: legacy's
# pg_hba.conf requires scram-sha-256 for any non-local connection ("host
# all all all scram-sha-256"), and patroni-0/patroni-1 (per
# postgresql-ha-staging.yaml) use PGUSER_SUPERUSER=PGadmin with the SAME
# postgresql_password Docker secret as legacy's own PGadmin. Every remote
# `psql -h <patroni-node> -U "$POSTGRES_USER" ...` call in this script
# had ALWAYS omitted a password entirely — this bug existed from the
# script's very first version, it simply had never been reached with
# working diagnostics until the run #7 fix exposed it. Checked the WHOLE
# script for this pattern, not just Phase 3 — found the IDENTICAL missing
# password in Phase 5 (the post-promotion `pg_is_in_recovery()` check on
# $LEADER_HOST) and Phase 8 (the alias write + both visibility checks on
# $LEADER_HOST/$REPLICA_HOST) — all three would have failed the exact
# same way if ever reached. FIXED: read the postgresql_password secret
# ONCE (via `docker exec "$LEGACY_CID" cat /run/secrets/postgresql_password`,
# same secret/mechanism legacy itself already uses) into $ADMIN_PGPASSWORD
# early in Phase 3, then pass it to every remote `psql -h <node>` call via
# `docker exec -e PGPASSWORD="$ADMIN_PGPASSWORD" ...` — using docker exec's
# own -e flag rather than splicing the password into the bash -c string,
# specifically to avoid quoting/escaping hazards and to avoid the value
# ever needing to appear inside this script's own string-construction
# logic. NOT yet re-validated by an actual successful run past Phase 3.

set -uo pipefail   # NOTE: deliberately not -e — every phase below checks
                   # its own command's exit status explicitly so we can
                   # invoke rollback with a specific, accurate reason
                   # rather than an opaque `set -e` abort mid-phase.

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
REPO_ROOT="/volume1/docker/compose-files"
CUTOVER_DIR="${REPO_ROOT}/postgresql/cutover"
HA_STACK="postgresqlha"
LEGACY_STACK="postgresql"
LEGACY_SERVICE="postgresql_postgresql"
NETWORK_NAME="postgresql_db-backend"
PATRONI_SCOPE="postgres-ha"
BACKUP_DIR="/volume1/SMB-docker/backup"

# ── SESSION LOGGING (added — see ADR-0001 note, Session Update 9) ────────
# Persists this run's ENTIRE multi-phase stdout/stderr transcript to a
# single timestamped file in BACKUP_DIR — the SAME location preflight.sh
# writes the pg_dumpall backup to, per explicit operator request: the
# backup and the transcript of the run that needed it always end up
# sitting right next to each other. CUTOVER_SESSION_LOG is exported so
# preflight.sh and rollback.sh (both invoked below as plain
# `bash <script>` child processes, which inherit exported env vars)
# automatically detect it's already open and APPEND to this same file
# instead of opening their own — one merged transcript per session,
# covering Pre-Phase-0 through Phase 11 AND any automatically-triggered
# rollback.sh output, not three separate fragments. `tee` mirrors
# everything to the real stdout/stderr too, so live console/SSH-visible
# output is completely unchanged — this is purely additive persistence.
mkdir -p "$BACKUP_DIR"
CUTOVER_SESSION_TS="$(date +%Y%m%d-%H%M%S)"
CUTOVER_SESSION_LOG="${BACKUP_DIR}/cutover-session-${CUTOVER_SESSION_TS}.log"
export CUTOVER_SESSION_LOG
exec > >(tee -a "$CUTOVER_SESSION_LOG") 2>&1

# Always print exactly where the transcript lives and what the final exit
# code was, even on an unexpected/unhandled exit (e.g. a bug in this
# script itself, not one of the deliberate trigger_rollback() paths) —
# the exit code and log location should never be a mystery to whoever
# investigates later.
_cutover_exit_trap() {
  local rc=$?
  echo "" >&2
  echo "[cutover $(date +%H:%M:%S)] Session ending, exit code ${rc}. Full transcript: ${CUTOVER_SESSION_LOG}" >&2
  if [ "$rc" -ne 0 ]; then
    echo "[cutover $(date +%H:%M:%S)] Non-zero exit — if this did NOT go through a clean trigger_rollback() path (check the log above for a matching FAIL line), treat current state as unverified and consult RUNBOOK.md section 4 before assuming it's safe." >&2
  fi
}
trap _cutover_exit_trap EXIT

# Consumer services checked in Phase 9 — (service_name, external health URL
# or empty if internal-only). Derived from the dependency map in
# RUNBOOK.md section 1. Update this list if the dependency map changes.
declare -A CONSUMER_HEALTH_URLS=(
  ["auth_authentik-server"]="https://auth.bryanmail.net/-/health/live/"
  ["git_gitea-server"]="https://git.bryanmail.net/api/healthz"
  ["woodpecker_woodpecker-server"]="https://woodpecker.bryanmail.net/healthz"
)
# Services checked via Swarm task-stability only (no forwardAuth-free
# external endpoint known / internal-only) — open-webui and litellm sit
# behind Authentik forwardAuth on their public routes, so an external curl
# would just hit a login redirect, not real app health.
CONSUMER_STABILITY_ONLY=(
  "ai_open-webui"
  "ai_litellm"
  "auth_authentik-worker"
)

log()  { echo "[cutover $(date +%H:%M:%S)] $*"; }
warn() { echo "[cutover WARN $(date +%H:%M:%S)] $*" >&2; }

# Returns success (0) if $1 looks like a 2xx HTTP status code.
is_2xx() { [[ "$1" =~ ^2[0-9][0-9]$ ]]; }

# Any hard failure calls this — invokes rollback.sh, then exits nonzero.
# No manual step should be required after this runs.
trigger_rollback() {
  local reason="$1"
  echo "" >&2
  echo "[cutover FAIL $(date +%H:%M:%S)] ${reason}" >&2
  echo "[cutover] Invoking rollback.sh automatically — no manual action should be required." >&2
  bash "${CUTOVER_DIR}/rollback.sh" "${reason}"
  local rc=$?
  if [ $rc -ne 0 ]; then
    echo "" >&2
    echo "[cutover] ⚠️  rollback.sh ITSELF returned nonzero (${rc}). This is the one" >&2
    echo "[cutover] scenario requiring manual intervention — see RUNBOOK.md section 4," >&2
    echo "[cutover] 'If rollback.sh itself fails partway'. Do not blindly re-run scripts." >&2
    echo "[cutover] Full transcript of everything up to this point (including rollback.sh's" >&2
    echo "[cutover] own attempted output above) is preserved at: ${CUTOVER_SESSION_LOG}" >&2
  fi
  exit 1
}

# Get the container ID for the (always docker-2-local) legacy container.
legacy_cid() {
  docker ps -q --filter "name=${LEGACY_SERVICE}" | head -1
}

# Maps a Patroni hostname to its bind-mounted data directory on the host,
# used for progress logging during basebackup (a full ~42GB copy has been
# observed to take 8-11 minutes — see Phase 1/2 comments above and below).
patroni_data_dir() {
  case "$1" in
    patroni-0) echo "/volume1/docker/PostgreSQL/patroni-0-data" ;;
    patroni-1) echo "/volume1/docker/PostgreSQL/patroni-1-data" ;;
    *) echo "" ;;
  esac
}

# timeout-guarded `du -sh` — du has been observed to hang on CephFS while
# a basebackup is writing heavily to the same volume. Returns "?" rather
# than blocking the whole polling loop if it doesn't return quickly.
data_dir_size() {
  local dir="$1" out
  out=$(timeout 10 du -sh "$dir" 2>/dev/null | awk '{print $1}')
  echo "${out:-?}"
}

# Raw-socket HTTP GET via /dev/tcp, executed inside the legacy container.
# Avoids assuming curl exists in the legacy postgres:17 image.
# Args: host port path
http_get_via_legacy() {
  local host="$1" port="$2" path="$3" cid
  cid="$(legacy_cid)"
  docker exec "$cid" bash -c "
    exec 3<>/dev/tcp/${host}/${port} || exit 99
    printf 'GET ${path} HTTP/1.0\r\nHost: ${host}\r\nConnection: close\r\n\r\n' >&3
    cat <&3
  " 2>/dev/null
}

# Raw-socket HTTP PATCH with basic auth + JSON body, via /dev/tcp inside
# the legacy container. Args: host port path username password json_body
http_patch_via_legacy() {
  local host="$1" port="$2" path="$3" user="$4" pass="$5" body="$6" cid auth
  cid="$(legacy_cid)"
  auth="$(docker exec "$cid" bash -c "printf '%s' '${user}:${pass}' | base64 -w0")"
  local body_len=${#body}
  docker exec "$cid" bash -c "
    exec 3<>/dev/tcp/${host}/${port} || exit 99
    printf 'PATCH ${path} HTTP/1.0\r\nHost: ${host}\r\nAuthorization: Basic ${auth}\r\nContent-Type: application/json\r\nContent-Length: ${body_len}\r\nConnection: close\r\n\r\n${body}' >&3
    cat <&3
  " 2>/dev/null
}

# Poll a Patroni node's /patroni endpoint for its reported role.
# Args: hostname (e.g. patroni-1) -> prints role string (leader/replica/standby_leader/etc)
patroni_role() {
  local host="$1"
  http_get_via_legacy "$host" 8008 "/patroni" | grep -o '"role"[^,}]*' | sed 's/.*: *"//;s/"$//'
}

# FIXED (see header "FIX LOG", 2026 run #4): a plain replica's OWN
# /patroni endpoint has NO lag/replication_state field — that data only
# exists on the LEADER/standby_leader side (derived from
# pg_stat_replication). Query the LEADER's /cluster endpoint instead and
# extract ONE SPECIFIC member's "state"/"lag" fields from its member
# object. Member objects are flat (no nested braces), so a bounded grep
# safely isolates exactly one member's JSON fragment before parsing.
# Args: query_host (should be the LEADER/standby_leader) member_name
#       (the node whose lag/state you actually want, e.g. the replica)
# Prints: "state=<value> lag=<value>" (either may be <none> if absent)
cluster_member_lag_state() {
  local query_host="$1" member_name="$2" cluster_json member_json state lag
  cluster_json="$(http_get_via_legacy "$query_host" 8008 "/cluster")"
  member_json="$(echo "$cluster_json" | grep -o "{\"name\": *\"${member_name}\"[^}]*}")"
  if [ -z "$member_json" ]; then
    echo "state=<none> lag=<none>"
    return
  fi
  state="$(echo "$member_json" | grep -o '"state": *"[^"]*"' | sed 's/.*"state": *"//;s/"$//')"
  lag="$(echo "$member_json" | grep -o '"lag": *[0-9][0-9]*' | sed 's/.*: *//')"
  echo "state=${state:-<none>} lag=${lag:-<none>}"
}

wait_for_stack_running() {
  local stack="$1" expected_count="$2" max_wait="${3:-120}"
  local waited=0
  while [ "$waited" -lt "$max_wait" ]; do
    local running
    running=$(docker stack ps "$stack" --filter "desired-state=running" --format '{{.CurrentState}}' 2>/dev/null | grep -c '^Running')
    if [ "$running" -ge "$expected_count" ]; then
      return 0
    fi
    sleep 5
    waited=$((waited + 5))
  done
  return 1
}

# ═══════════════════════════════════════════════════════════════════════
log "=== ADR-0001 Phase 3 real cutover starting ==="
log "Full session transcript (this run, all phases + any triggered rollback.sh output): ${CUTOVER_SESSION_LOG}"
if [ -d "${REPO_ROOT}/.git" ]; then
  GIT_HEAD="$(git -C "$REPO_ROOT" rev-parse HEAD 2>/dev/null || echo unknown)"
  log "Repo HEAD at ${REPO_ROOT}: ${GIT_HEAD} (confirm this matches origin/main — see ADR-0001 note's local-checkout-drift lesson, Session Update 8, if unsure)"
fi
log "Chat dependency reminder: this chat (ai_open-webui/litellm) is itself"
log "a consumer of the DB being migrated. If this script exits nonzero"
log "before completing rollback, follow RUNBOOK.md section 4 manually via"
log "direct SSH — do not assume this chat will remain usable to help."

# ── Pre-Phase-0: stale-state detection & optional interactive cleanup ──
# See header "FIX LOG", 2026 run #5, for the full incident this addresses:
# a prior interrupted run can leave etcd-*/patroni-*-data non-empty on
# disk (or the postgresqlha stack itself still deployed) even after the
# stack is gone — and a fresh Patroni bootstrap against non-fresh etcd
# state hangs BOTH nodes forever waiting for a leader that will never
# appear, since etcd already "remembers" the cluster as initialized.
# This check runs BEFORE preflight.sh's ~6+ minute pg_dumpall so a
# doomed run doesn't waste that time before the operator gets a chance
# to intervene. Destructive (wipes data dirs) — per explicit operator
# request, this is an INTERACTIVE prompt, not silent automatic cleanup.
# For unattended re-runs where cleanup should always happen without a
# human present, set AUTO_CLEANUP=yes in the environment beforehand.
log "--- Pre-Phase-0: checking for stale state from a prior interrupted run ---"

STALE_FOUND=0
STALE_DETAILS=()

if docker stack ls --format '{{.Name}}' 2>/dev/null | grep -qx "${HA_STACK}"; then
  STALE_FOUND=1
  STALE_DETAILS+=("the ${HA_STACK} stack is already deployed (a previous run may still be in progress, or was stopped without a clean rollback.sh pass)")
fi

for d in etcd-1-data etcd-2-data etcd-3-data patroni-0-data patroni-1-data; do
  DIR="/volume1/docker/PostgreSQL/${d}"
  if [ -d "$DIR" ] && [ -n "$(ls -A "$DIR" 2>/dev/null)" ]; then
    STALE_FOUND=1
    STALE_DETAILS+=("${DIR} is non-empty (leftover from a prior run — etcd in particular will remember the old cluster as already-initialized and cause both Patroni nodes to hang forever waiting for a leader)")
  fi
done

if [ "$STALE_FOUND" -eq 1 ]; then
  warn "Detected possible stale state from a previous cutover attempt:"
  for detail in "${STALE_DETAILS[@]}"; do
    warn "  - ${detail}"
  done
  warn "Proceeding without cleaning this up is very likely to reproduce the"
  warn "2026-08-05 incident where BOTH patroni-0 and patroni-1 sat forever"
  warn "on 'waiting for standby_leader to bootstrap', never even attempting"
  warn "to race for the role — see the ADR-0001 note, Session Update 9."

  AUTO_CLEANUP="${AUTO_CLEANUP:-}"
  DO_CLEANUP=""
  if [ "$AUTO_CLEANUP" = "yes" ]; then
    log "AUTO_CLEANUP=yes set in environment — cleaning up automatically, no prompt."
    DO_CLEANUP="y"
  elif [ -t 0 ]; then
    read -rp "[cutover] Clean up this stale state now before proceeding? This tears down any existing ${HA_STACK} stack and wipes etcd-*/patroni-*-data. Legacy production data (postgresql_postgresql, data-17) is NEVER touched by this. [y/N] " DO_CLEANUP
  else
    trigger_rollback "Stale state detected from a prior run and this session is non-interactive (no tty to prompt) — refusing to guess. Re-run interactively to be prompted, set AUTO_CLEANUP=yes beforehand to skip the prompt, or clean up manually first (see ADR-0001 note, Session Update 9)."
  fi

  if [[ "$DO_CLEANUP" =~ ^[Yy] ]]; then
    log "Operator confirmed — cleaning up stale state..."
    if docker stack ls --format '{{.Name}}' 2>/dev/null | grep -qx "${HA_STACK}"; then
      log "Tearing down existing ${HA_STACK} stack..."
      docker stack rm "${HA_STACK}"
      log "Waiting for tasks to actually finish shutting down (docker stack rm returns immediately)..."
      for i in $(seq 1 24); do
        REMAINING=$(docker stack ps "${HA_STACK}" 2>/dev/null | grep -c . || true)
        if [ "$REMAINING" -eq 0 ]; then
          break
        fi
        sleep 5
      done
      REMAINING=$(docker stack ps "${HA_STACK}" 2>/dev/null | grep -c . || true)
      if [ "$REMAINING" -gt 0 ]; then
        trigger_rollback "${HA_STACK} still shows ${REMAINING} task(s) after 120s wait during stale-state cleanup — unsafe to wipe data dirs while tasks may still be shutting down."
      fi
      log "Confirmed: ${HA_STACK} stack fully removed."
    fi
    for d in etcd-1-data etcd-2-data etcd-3-data patroni-0-data patroni-1-data; do
      DIR="/volume1/docker/PostgreSQL/${d}"
      if [ -d "$DIR" ]; then
        rm -rf "$DIR"
        mkdir -p "$DIR"
        log "  wiped and recreated: ${DIR}"
      fi
    done
    log "Stale-state cleanup complete — proceeding with a genuinely clean slate."
  else
    warn "Proceeding WITHOUT cleanup, per operator choice."
    warn "If Phase 1 hangs with both nodes stuck on 'waiting for standby_leader"
    warn "to bootstrap' and never attempting to race for the role, this stale"
    warn "state is almost certainly why — stop this run and re-run choosing"
    warn "cleanup, or run manually: docker stack rm ${HA_STACK}; then wipe"
    warn "etcd-*/patroni-*-data under /volume1/docker/PostgreSQL/ before retrying."
  fi
else
  log "No stale state detected from a prior run — proceeding."
fi

# ── Phase 0: preflight ────────────────────────────────────────────────
log "--- Phase 0: preflight ---"
if ! bash "${CUTOVER_DIR}/preflight.sh"; then
  echo "[cutover] preflight.sh failed. Nothing has been touched — safe to stop here, no rollback needed." >&2
  exit 1
fi
log "PASS: Phase 0 preflight"

# ── Phase 1: deploy postgresqlha staging stack ─────────────────────────
log "--- Phase 1: deploy postgresqlha (staging) ---"

# FIXED (see header "FIX LOG"): use docker's own --filter syntax instead
# of Go template field access on a hyphenated label name (which silently
# fails to parse and always returns empty).
PRIMARY_LABELED=$(docker node ls --filter "node.label=pg-role=primary" --format '{{.Hostname}}' | grep -c . || true)
REPLICA_LABELED=$(docker node ls --filter "node.label=pg-role=replica" --format '{{.Hostname}}' | grep -c . || true)
if [ "$PRIMARY_LABELED" -lt 1 ] || [ "$REPLICA_LABELED" -lt 1 ]; then
  trigger_rollback "No node(s) found with pg-role=primary/replica labels — patroni-0/1 placement constraints cannot schedule. Set via: docker node update --label-add pg-role=primary <node> (and =replica on another). Nothing deployed yet, rollback is a no-op safety call."
fi
log "Node labels confirmed: ${PRIMARY_LABELED} node(s) with pg-role=primary, ${REPLICA_LABELED} node(s) with pg-role=replica."

docker stack deploy -c "${CUTOVER_DIR}/postgresql-ha-staging.yaml" "${HA_STACK}"
if [ $? -ne 0 ]; then
  trigger_rollback "docker stack deploy of postgresql-ha-staging.yaml failed."
fi

log "Waiting for postgresqlha services to be running (etcd-1/2/3, patroni-0/1, haproxy = 6)..."
if ! wait_for_stack_running "${HA_STACK}" 6 180; then
  trigger_rollback "postgresqlha stack did not reach 6 running tasks within 180s."
fi

# FIXED (see header "FIX LOG" item a): the standby_leader bootstrap race
# winner is nondeterministic — poll BOTH nodes, whichever wins becomes
# $LEADER_HOST, the other becomes $REPLICA_HOST. Every later phase uses
# these two variables instead of a hardcoded hostname.
# FIXED (see header "FIX LOG" item b): window extended — a full ~42GB
# basebackup has been observed to take 8-11 minutes, and the role only
# flips AFTER it fully completes. Each poll logs both nodes' role AND
# data-dir size so progress is directly observable instead of a silent
# binary wait.
log "Confirming standby_leader bootstrap completes (winner is nondeterministic — polling BOTH patroni-0 and patroni-1; a full ~42GB basebackup has been observed to take 8-11 minutes, so this window is generous)..."
LEADER_HOST=""
REPLICA_HOST=""
PHASE1_MAX_WAIT=3600
PHASE1_POLL_INTERVAL=15
waited=0
ROLE0=""
ROLE1=""
while [ "$waited" -lt "$PHASE1_MAX_WAIT" ]; do
  ROLE0="$(patroni_role patroni-0)"
  ROLE1="$(patroni_role patroni-1)"
  SIZE0="$(data_dir_size "$(patroni_data_dir patroni-0)")"
  SIZE1="$(data_dir_size "$(patroni_data_dir patroni-1)")"
  log "patroni-0 role=${ROLE0:-<none>} data_dir=${SIZE0} | patroni-1 role=${ROLE1:-<none>} data_dir=${SIZE1} (${waited}s/${PHASE1_MAX_WAIT}s elapsed)"
  if [ "$ROLE0" = "standby_leader" ]; then
    LEADER_HOST="patroni-0"; REPLICA_HOST="patroni-1"; break
  fi
  if [ "$ROLE1" = "standby_leader" ]; then
    LEADER_HOST="patroni-1"; REPLICA_HOST="patroni-0"; break
  fi
  sleep "$PHASE1_POLL_INTERVAL"
  waited=$((waited + PHASE1_POLL_INTERVAL))
done
if [ -z "$LEADER_HOST" ]; then
  trigger_rollback "Neither patroni-0 nor patroni-1 reached standby_leader role within ${PHASE1_MAX_WAIT}s. Last observed: patroni-0='${ROLE0}' patroni-1='${ROLE1}'."
fi
log "PASS: Phase 1 — ${LEADER_HOST} is standby_leader (bootstrap winner determined dynamically); ${REPLICA_HOST} will be the cascade replica"

# ── Phase 2: wait for streaming lag = 0 ────────────────────────────────
log "--- Phase 2: confirm streaming lag = 0 (cascade replica: ${REPLICA_HOST}) ---"
# FIXED (see header "FIX LOG" item b): ${REPLICA_HOST} is itself
# bootstrapping via a full basebackup from ${LEADER_HOST} — same
# 8-11-min-for-~42GB precedent as Phase 1.
# FURTHER EXTENDED (see header "FIX LOG", 2026 run #3 follow-up, operator
# request): this cascade-replica bootstrap adds an EXTRA network/streaming
# hop on top of the same basebackup workload, so its window is now longer
# than Phase 1's rather than merely equal to it.
# FIXED (see header "FIX LOG", 2026 run #4): the lag check itself was a
# silent no-op — see cluster_member_lag_state() above for the real fix.
# This gate now queries ${LEADER_HOST}'s /cluster view of ${REPLICA_HOST}
# and requires literal state=streaming AND (lag=0 or lag absent).
log "Note: ${REPLICA_HOST} is bootstrapping via a full basebackup from ${LEADER_HOST} — an extra hop beyond Phase 1's basebackup, so this window is deliberately longer than Phase 1's."
CONSECUTIVE_ZERO=0
PHASE2_MAX_WAIT=2400
PHASE2_POLL_INTERVAL=15
waited=0
REPLICA_ROLE=""
LAG_INFO=""
while [ "$waited" -lt "$PHASE2_MAX_WAIT" ]; do
  REPLICA_ROLE="$(patroni_role "$REPLICA_HOST")"
  LAG_INFO="$(cluster_member_lag_state "$LEADER_HOST" "$REPLICA_HOST")"
  SIZE="$(data_dir_size "$(patroni_data_dir "$REPLICA_HOST")")"
  log "${REPLICA_HOST} role=${REPLICA_ROLE:-<none>} ${LAG_INFO} data_dir=${SIZE} (${waited}s/${PHASE2_MAX_WAIT}s elapsed)"
  if [ "$REPLICA_ROLE" = "replica" ] && echo "$LAG_INFO" | grep -q "state=streaming" && ! echo "$LAG_INFO" | grep -qE "lag=[1-9]"; then
    CONSECUTIVE_ZERO=$((CONSECUTIVE_ZERO + 1))
  else
    CONSECUTIVE_ZERO=0
  fi
  [ "$CONSECUTIVE_ZERO" -ge 3 ] && break
  sleep "$PHASE2_POLL_INTERVAL"
  waited=$((waited + PHASE2_POLL_INTERVAL))
done
if [ "$CONSECUTIVE_ZERO" -lt 3 ]; then
  trigger_rollback "Streaming lag never reached 3 consecutive zero/streaming polls on ${REPLICA_HOST} within ${PHASE2_MAX_WAIT}s. Last state: role=${REPLICA_ROLE} ${LAG_INFO}"
fi
log "PASS: Phase 2 — streaming confirmed, lag=0/streaming for 3 consecutive polls on ${REPLICA_HOST}"

# ── Phase 3: pre-promotion canary write/propagation check ─────────────
log "--- Phase 3: pre-promotion canary (legacy -> ${REPLICA_HOST} cascade replica) ---"
LEGACY_CID="$(legacy_cid)"
if [ -z "$LEGACY_CID" ]; then
  trigger_rollback "Could not find legacy container for canary write."
fi

# FIXED (see header "FIX LOG", 2026 run #8): every remote `psql -h
# <patroni-node>` call in Phases 3/5/8 below needs a password — legacy's
# pg_hba.conf requires scram-sha-256 for any non-local connection, and
# patroni-0/patroni-1 use the SAME PGadmin superuser + SAME
# postgresql_password secret as legacy itself (per
# postgresql-ha-staging.yaml). This was previously never supplied at all,
# so every one of these remote calls had always failed with
# "fe_sendauth: no password supplied" — masked until the run #7
# stderr-capture fix exposed it. Read the secret ONCE here (same
# secret/mechanism legacy already uses) and pass it to every remote call
# below via `docker exec -e PGPASSWORD=...` (NOT spliced into the bash -c
# string, to avoid quoting hazards).
ADMIN_PGPASSWORD=$(docker exec "$LEGACY_CID" cat /run/secrets/postgresql_password 2>/dev/null)
if [ -z "$ADMIN_PGPASSWORD" ]; then
  trigger_rollback "Could not read postgresql_password secret needed for remote psql -h <patroni-node> calls in Phases 3/5/8. Nothing changed yet."
fi

CANARY_VAL="cutover-$(date +%s)"
# FIXED (see header "FIX LOG", 2026 run #7): this write's stderr used to
# be discarded entirely; now captured and logged on failure so a genuine
# write error to LEGACY (as opposed to a downstream propagation issue)
# is visible instead of only surfacing as a generic "failed to write".
CANARY_WRITE_OUT=$(docker exec "$LEGACY_CID" bash -c "psql -U \"\$POSTGRES_USER\" -c \"CREATE TABLE IF NOT EXISTS _cutover_canary (val text, ts timestamptz default now()); INSERT INTO _cutover_canary(val) VALUES ('${CANARY_VAL}');\"" 2>&1)
CANARY_WRITE_RC=$?
if [ "$CANARY_WRITE_RC" -ne 0 ]; then
  log "Canary write to legacy — psql output: ${CANARY_WRITE_OUT}"
  trigger_rollback "Failed to write pre-promotion canary row to legacy. psql output logged above this FAIL line."
fi
# FIXED (see header "FIX LOG", 2026 run #7): the propagation check below
# used to discard stderr entirely (2>/dev/null) — this made a genuine
# replication delay and an outright connection/auth error to
# ${REPLICA_HOST} indistinguishable in the log, and this exact failure
# ("Canary row did not propagate ... within 5s") had already been seen
# TWICE (2026 run #4, root-caused as the lag-check bug; 2026 run #7,
# root-caused as the missing PGPASSWORD below — see run #8) with no way
# to tell which kind of failure either one actually was after the fact.
# Each attempt's stderr is now captured to a numbered file (same pattern
# already used by Phase 8 below) AND echoed into the log per-attempt, so
# a future failure should be self-explanatory from the log alone.
# FIXED (see header "FIX LOG", 2026 run #8): added
# `-e PGPASSWORD="$ADMIN_PGPASSWORD"` to the docker exec — this remote
# psql -h call had NEVER had a password supplied before.
FOUND=0
LAST_CANARY_CHECK_ERR=""
for i in $(seq 1 5); do
  rm -f "/tmp/cutover_phase3_attempt_${i}.stderr"
  RESULT=$(docker exec -e PGPASSWORD="$ADMIN_PGPASSWORD" "$LEGACY_CID" bash -c "psql -h ${REPLICA_HOST} -U \"\$POSTGRES_USER\" -tAc \"SELECT 1 FROM _cutover_canary WHERE val = '${CANARY_VAL}';\"" 2>"/tmp/cutover_phase3_attempt_${i}.stderr")
  CANARY_CHECK_ERR="$(cat "/tmp/cutover_phase3_attempt_${i}.stderr" 2>/dev/null)"
  if [ -n "$CANARY_CHECK_ERR" ]; then
    log "  attempt ${i}/5 on ${REPLICA_HOST}: result='${RESULT:-<empty>}' stderr: ${CANARY_CHECK_ERR}"
    LAST_CANARY_CHECK_ERR="$CANARY_CHECK_ERR"
  else
    log "  attempt ${i}/5 on ${REPLICA_HOST}: result='${RESULT:-<empty>}' (no stderr)"
  fi
  if [ "$RESULT" = "1" ]; then
    FOUND=1
    rm -f /tmp/cutover_phase3_attempt_*.stderr
    break
  fi
  sleep 1
done
if [ "$FOUND" -ne 1 ]; then
  trigger_rollback "Canary row did not propagate to ${REPLICA_HOST} within 5s. standby_cluster streaming is not working as expected. Last psql stderr seen (empty means every attempt connected cleanly and simply found no row yet — a genuine propagation delay, not a connection error): '${LAST_CANARY_CHECK_ERR}'. Full per-attempt detail logged above; raw files also left at /tmp/cutover_phase3_attempt_*.stderr on docker-2 for inspection."
fi
log "PASS: Phase 3 — canary write propagated to ${REPLICA_HOST} within 5s"

# ── Phase 4: flip legacy to read-only ───────────────────────────────────
log "--- Phase 4: flip legacy read-only ---"
docker exec "$LEGACY_CID" bash -c 'psql -U "$POSTGRES_USER" -c "ALTER SYSTEM SET default_transaction_read_only = on; SELECT pg_reload_conf();"' >/dev/null 2>&1
if [ $? -ne 0 ]; then
  trigger_rollback "Failed to set default_transaction_read_only=on on legacy."
fi
RO_STATE=$(docker exec "$LEGACY_CID" bash -c 'psql -U "$POSTGRES_USER" -tAc "SHOW default_transaction_read_only;"' 2>/dev/null | tr -d '[:space:]')
if [ "$RO_STATE" != "on" ]; then
  trigger_rollback "Verified read-only state is '${RO_STATE}', expected 'on'. Reversing immediately."
fi
log "Legacy confirmed read-only. Draining one more lag poll to confirm quiescence..."
sleep 5
LAG_INFO="$(cluster_member_lag_state "$LEADER_HOST" "$REPLICA_HOST")"
log "Post-read-only lag check (${REPLICA_HOST}): ${LAG_INFO}"
log "PASS: Phase 4 — legacy is read-only, no further writes possible there"

# ── Phase 5: promote Patroni ─────────────────────────────────────────
log "--- Phase 5: promote ${LEADER_HOST} (remove standby_cluster from DCS config) ---"
log "⚠️  Highest-scrutiny phase — see script header for the documented deviation from the dry-run's proven method (using Patroni's official REST PATCH /config instead). Manual etcdctl fallback documented above and in RUNBOOK.md if needed."

PATRONI_PASS=$(docker exec "$LEGACY_CID" cat /run/secrets/postgresql_patroni_password 2>/dev/null)
if [ -z "$PATRONI_PASS" ]; then
  # legacy container may not have this secret mounted — fall back to reading via the leader host itself if reachable, else hard-fail before touching anything
  trigger_rollback "Could not read postgresql_patroni_password secret needed for the promotion PATCH call. Nothing promoted yet."
fi

log "Smoke-test: GET /config on ${LEADER_HOST} before attempting the PATCH..."
CONFIG_CHECK=$(http_get_via_legacy "$LEADER_HOST" 8008 "/config")
if ! echo "$CONFIG_CHECK" | grep -qi "standby_cluster"; then
  trigger_rollback "GET /config on ${LEADER_HOST} did not show a standby_cluster key as expected before promotion — refusing to PATCH against an unexpected state. Raw response logged above for review."
fi
log "Smoke-test passed — standby_cluster key confirmed present pre-promotion."

PATCH_RESPONSE=$(http_patch_via_legacy "$LEADER_HOST" 8008 "/config" "patroni" "$PATRONI_PASS" '{"standby_cluster": null}')
log "PATCH /config response: ${PATCH_RESPONSE}"

log "Waiting for ${LEADER_HOST} to transition standby_leader -> leader (expect ~15-30s per dry-run drills)..."
PROMOTED=0
for i in $(seq 1 12); do
  ROLE="$(patroni_role "$LEADER_HOST")"
  log "${LEADER_HOST} role: ${ROLE}"
  if [ "$ROLE" = "leader" ]; then
    PROMOTED=1
    break
  fi
  sleep 5
done
if [ "$PROMOTED" -ne 1 ]; then
  trigger_rollback "${LEADER_HOST} did not reach 'leader' role within 60s of the promotion PATCH. Last observed role: '${ROLE}'. Manual etcdctl fallback is documented in this script's header / RUNBOOK.md if the operator wants to investigate before accepting the automatic rollback."
fi

# FIXED (see header "FIX LOG", 2026 run #8): added
# `-e PGPASSWORD="$ADMIN_PGPASSWORD"` — this remote psql -h call had
# NEVER had a password supplied before (would have failed with
# "fe_sendauth: no password supplied" exactly like Phase 3's did, had it
# ever been reached).
IN_RECOVERY=$(docker exec -e PGPASSWORD="$ADMIN_PGPASSWORD" "$LEGACY_CID" bash -c "psql -h ${LEADER_HOST} -U \"\$POSTGRES_USER\" -tAc \"SELECT pg_is_in_recovery();\"" 2>/dev/null | tr -d '[:space:]')
if [ "$IN_RECOVERY" != "f" ]; then
  trigger_rollback "${LEADER_HOST} reports role=leader but pg_is_in_recovery() = '${IN_RECOVERY}' (expected 'f'). Not trusting this as a genuine promotion."
fi
log "PASS: Phase 5 — ${LEADER_HOST} is genuine writable leader, pg_is_in_recovery=f"

# ── Phase 6: confirm HAProxy would see a healthy backend ──────────────
log "--- Phase 6: confirm healthy backend before touching DNS ---"
PRIMARY_CHECK=$(http_get_via_legacy "$LEADER_HOST" 8008 "/primary")
if ! echo "$PRIMARY_CHECK" | grep -q "200 OK"; then
  trigger_rollback "${LEADER_HOST} does not answer 200 on GET /primary post-promotion. Raw response: ${PRIMARY_CHECK}"
fi
log "PASS: Phase 6 — ${LEADER_HOST} answers 200 on /primary, HAProxy's healthcheck will pass"

# ── Phase 7: deploy final HAProxy with postgresql/db aliases ──────────
log "--- Phase 7: deploy postgresql-ha-final.yaml (adds postgresql/db aliases) ---"
docker stack deploy -c "${CUTOVER_DIR}/postgresql-ha-final.yaml" "${HA_STACK}"
if [ $? -ne 0 ]; then
  trigger_rollback "docker stack deploy of postgresql-ha-final.yaml failed."
fi
log "Waiting for haproxy service update to roll out..."
sleep 15
if ! wait_for_stack_running "${HA_STACK}" 6 90; then
  trigger_rollback "postgresqlha stack did not return to 6 running tasks after haproxy alias update."
fi

log "Confirming DNS resolution of 'postgresql' now shows BOTH legacy and haproxy..."
RESOLVE_OUTPUT=$(docker exec "$LEGACY_CID" bash -c "getent hosts postgresql" 2>/dev/null)
log "Resolution: ${RESOLVE_OUTPUT}"
NUM_IPS=$(echo "$RESOLVE_OUTPUT" | awk '{print $1}' | sort -u | wc -l)
if [ "$NUM_IPS" -lt 2 ]; then
  trigger_rollback "Expected 2 distinct IPs for 'postgresql' (legacy + haproxy) after alias deploy, found ${NUM_IPS}. Resolution output: ${RESOLVE_OUTPUT}"
fi
log "PASS: Phase 7 — 'postgresql' resolves to ${NUM_IPS} IPs (legacy + haproxy), alias handoff live"

# ── Phase 8: canary write through the alias (retry-tolerant of legacy read-only hits) ──
log "--- Phase 8: canary write/propagation THROUGH the shared alias ---"
log "Note: during this window 'postgresql' round-robins between legacy (read-only)"
log "and haproxy (writable). Write attempts landing on legacy will correctly fail"
log "with a read-only error — that is EXPECTED, not a hard failure by itself."
# FIXED (see header "FIX LOG", 2026 run #8): added
# `-e PGPASSWORD="$ADMIN_PGPASSWORD"` to all three docker exec calls in
# this phase — writes/reads that round-robin onto haproxy (i.e. land on
# the promoted leader) are remote, non-local connections and had NEVER
# had a password supplied before. (Writes/reads that happen to land on
# legacy itself go through the "local ... trust" pg_hba.conf rule and
# don't strictly need this, but supplying it is harmless either way.)
CANARY2_VAL="cutover-alias-$(date +%s)"
ALIAS_WRITE_OK=0
for i in $(seq 1 10); do
  docker exec -e PGPASSWORD="$ADMIN_PGPASSWORD" "$LEGACY_CID" bash -c "psql -h postgresql -U \"\$POSTGRES_USER\" -c \"INSERT INTO _cutover_canary(val) VALUES ('${CANARY2_VAL}');\"" >/tmp/cutover_phase8_attempt_$i.log 2>&1
  if [ $? -eq 0 ]; then
    ALIAS_WRITE_OK=1
    break
  fi
  sleep 2
done
if [ "$ALIAS_WRITE_OK" -ne 1 ]; then
  trigger_rollback "Never succeeded writing through the 'postgresql' alias after 10 attempts (~20s). See /tmp/cutover_phase8_attempt_*.log on docker-2 for the read-only/connection errors encountered."
fi
log "Alias write succeeded on attempt ${i}. Confirming visibility on BOTH ${LEADER_HOST} and ${REPLICA_HOST}..."
VLEADER=$(docker exec -e PGPASSWORD="$ADMIN_PGPASSWORD" "$LEGACY_CID" bash -c "psql -h ${LEADER_HOST} -U \"\$POSTGRES_USER\" -tAc \"SELECT 1 FROM _cutover_canary WHERE val = '${CANARY2_VAL}';\"" 2>/dev/null)
VREPLICA=$(docker exec -e PGPASSWORD="$ADMIN_PGPASSWORD" "$LEGACY_CID" bash -c "psql -h ${REPLICA_HOST} -U \"\$POSTGRES_USER\" -tAc \"SELECT 1 FROM _cutover_canary WHERE val = '${CANARY2_VAL}';\"" 2>/dev/null)
if [ "$VLEADER" != "1" ] || [ "$VREPLICA" != "1" ]; then
  trigger_rollback "Alias-written canary row not visible on both nodes (${LEADER_HOST}='${VLEADER}' ${REPLICA_HOST}='${VREPLICA}')."
fi
log "PASS: Phase 8 — real client-path write through the alias succeeded and is visible on both nodes"

# ── Phase 9: verify all known consumers ────────────────────────────────
log "--- Phase 9: verify known consumers reconnect/function ---"
CONSUMER_FAIL=0
for svc in "${!CONSUMER_HEALTH_URLS[@]}"; do
  url="${CONSUMER_HEALTH_URLS[$svc]}"
  CODE=$(curl -sk -o /dev/null -w '%{http_code}' --max-time 10 "$url")
  log "  ${svc} -> ${url} -> HTTP ${CODE}"
  if ! is_2xx "$CODE"; then
    warn "  ${svc} did not return a 2xx status (got ${CODE})"
    CONSUMER_FAIL=1
  fi
done
for svc in "${CONSUMER_STABILITY_ONLY[@]}"; do
  RESTARTS=$(docker service ps "$svc" --filter "desired-state=running" --format '{{.CurrentState}}' 2>/dev/null | grep -c 'seconds ago\|Starting')
  log "  ${svc} -> recent-restart signal count: ${RESTARTS} (0 expected if stable)"
  if [ "$RESTARTS" -gt 0 ]; then
    warn "  ${svc} shows a recent restart — may just be catching up post-cutover, but flagging."
    CONSUMER_FAIL=1
  fi
done
if [ "$CONSUMER_FAIL" -ne 0 ]; then
  trigger_rollback "One or more consumers failed verification in Phase 9. See warnings above. Treating any consumer failure here as a cutover failure, not something to patch around live."
fi
log "PASS: Phase 9 — all known consumers verified"

# ── Phase 10: stop legacy ──────────────────────────────────────────────
log "--- Phase 10: stop legacy service ---"
docker service scale "${LEGACY_SERVICE}=0"
if [ $? -ne 0 ]; then
  trigger_rollback "Failed to scale ${LEGACY_SERVICE} to 0."
fi
sleep 10
log "Confirming HAProxy backend still healthy post-legacy-stop..."
PRIMARY_CHECK2=$(http_get_via_legacy "$LEADER_HOST" 8008 "/primary" 2>/dev/null || true)
# Note: legacy is now stopped, so http_get_via_legacy (which proxies via
# the legacy container) will start failing itself shortly — this is
# EXPECTED and is why this is the last check that uses that proxy method.
log "Last legacy-proxied check (legacy is now down, this method retires after this call): ${PRIMARY_CHECK2}"

log "Confirming 'postgresql' now resolves ONLY to haproxy..."
# Use a disposable probe container instead of the (now-stopping) legacy container.
PROBE_OUT=$(docker run --rm --network "${NETWORK_NAME}" alpine:3 sh -c "getent hosts postgresql" 2>/dev/null)
log "Resolution post-legacy-stop: ${PROBE_OUT}"
NUM_IPS2=$(echo "$PROBE_OUT" | awk '{print $1}' | sort -u | wc -l)
if [ "$NUM_IPS2" -ne 1 ]; then
  warn "Expected exactly 1 IP for 'postgresql' now that legacy is stopped, found ${NUM_IPS2}. This may just be DNS cache settling — re-check manually before treating as fatal, but NOT auto-rolling-back here since legacy has already been cleanly stopped (scale=1 would just resume it)."
fi

log "Re-running Phase 9 consumer checks post-legacy-stop..."
CONSUMER_FAIL2=0
for svc in "${!CONSUMER_HEALTH_URLS[@]}"; do
  url="${CONSUMER_HEALTH_URLS[$svc]}"
  CODE=$(curl -sk -o /dev/null -w '%{http_code}' --max-time 10 "$url")
  log "  ${svc} -> ${url} -> HTTP ${CODE}"
  is_2xx "$CODE" || CONSUMER_FAIL2=1
done
if [ "$CONSUMER_FAIL2" -ne 0 ]; then
  trigger_rollback "Consumer check failed AFTER stopping legacy. Per RUNBOOK.md, rollback past this point is asymmetric (restores legacy as primary of record, not just a flag flip) — invoking rollback.sh now rather than leaving this in a broken state."
fi
log "PASS: Phase 10 — legacy stopped, consumers still healthy"

# ── Phase 11: final verification ────────────────────────────────────────
log "--- Phase 11: final verification pass ---"
docker stack ps "${HA_STACK}" --no-trunc | tail -n +1
AVAIL_GB=$(df --output=avail -BG /volume1/docker/PostgreSQL 2>/dev/null | tail -1 | tr -dc '0-9')
log "Disk space remaining at /volume1/docker/PostgreSQL: ${AVAIL_GB}GB"
log "=== CUTOVER COMPLETE ==="
log "Backup taken this run: $(cat ${BACKUP_DIR}/LATEST 2>/dev/null || echo 'unknown')"
log "Full session transcript: ${CUTOVER_SESSION_LOG}"
log "See RUNBOOK.md section 7 for manual post-cutover follow-up (legacy"
log "decommission, external port 5430 / Traefik SNI handoff, burn-in period)."
exit 0