ADR-0001 Phase 3 Cutover — RUNBOOK

Status: Ready to execute. All prerequisites (standby role, pg_hba.conf, architecture validation, dry-run drills) are complete — see companion note "ADR-0001 Dry-Run Debugging Log — Resume Point" for full history.

Scope: Cut production traffic over from the single-instance legacy postgresql container (stack: postgresql, service: postgresql) to the new Patroni/etcd/HAProxy HA stack (postgresqlha), using Patroni's standby_cluster continuous-streaming mode — NOT a one-shot basebackup — so the pre-cutover write gap is near-zero.

Critical constraint: This chat (Open WebUI + LiteLLM, stack ai) is itself a consumer of the database being migrated. The chat will not survive the cutover if anything goes wrong that requires interactive debugging mid-procedure. Every phase from "legacy read-only" onward is scripted with automatic rollback specifically so no human intervention via this chat is required to reach a safe state. cutover.sh and rollback.sh are both directly runnable on docker-2 via SSH/LXC-exec, independent of Woodpecker — do not rely on a CI pipeline run to execute or recover from this, since Woodpecker's own DB (via WOODPECKER_DATABASE_DATASOURCE) is itself a consumer and could be part of what's disrupted.

1. Dependency map (confirmed against live compose files)

All consumers below reach the DB via the Swarm-DNS name postgresql (alias db also exists on the legacy service) on the external overlay network postgresql_db-backend. Confirmed by reading each stack's compose file directly — not assumed.

Stack	Service	How it depends on `postgresql`	Blast radius if broken
`ai`	`open-webui`	`WEBUI_DB_HOST=postgresql`, `DATABASE_URL=${OPEN_WEBUI_DATABASE_URL}`	This chat. Session/user/chat state.
`ai`	`litellm`	`DATABASE_URL=${DATABASE_URL}`	LLM routing layer this chat calls through.
`ai`	`n8n`	attached to network, no explicit `DB_*` vars found in `ai.yaml`	Unconfirmed — likely SQLite. Preflight must verify live, not assume.
`ai`	`mcpo` / `mcpo-critical`	NOT attached to `postgresql_db-backend`	None. Confirmed no dependency — off-limits stack unaffected either way.
`auth`	`authentik-server`, `authentik-worker`	`AUTHENTIK_POSTGRESQL__HOST=postgresql`, DB name `authentik`	SSO — breaks all authenticated services cluster-wide.
`git`	`gitea-server`	attached to network; actual DB config lives in un-committed `git.env`	Preflight must inspect live env, don't assume driver.
`woodpecker`	`woodpecker-server`	explicit DSN `postgres://${DB_USER}:${DB_PASS}@${POSTGRES_HOST}:${POSTGRES_PORT}/woodpecker`	CI/CD pipeline itself. See special-case note above — do not depend on Woodpecker to run/recover this cutover.
`postgresql` (same stack)	`pgadmin`, `databasus`	same-stack, same network	Lower risk, co-located.

Not yet individually confirmed by direct compose-file read (ADR mentions ~13 total consumers): immich, mealie, vaultwarden, guacamole, homeassistant, meshcentral, maintenance (Cronicle / Uptime Kuma may only monitor, not store, in Postgres — verify). Do not trust this table as exhaustive — preflight.sh enumerates consumers authoritatively via docker network inspect postgresql_db-backend (live truth: every container actually attached right now) cross-checked against a repo-wide grep for postgresql_db-backend / POSTGRES / DATABASE_URL patterns (catches anything not currently running). Treat any mismatch between the two as a hard stop, not a warning.

2. Architecture summary

New stack: postgresqlha — 3x etcd (Raft quorum, one per node, hostname-pinned), 2x Patroni/Spilo (patroni-0 primary-labeled, patroni-1 replica-labeled, via node.labels.pg-role constraints), 1x HAProxy (deploy.mode: global, TCP health-routes to whichever Patroni node answers GET /primary with 200).
Bootstrap method: standby_cluster (continuous streaming from live legacy primary) — NOT CLONE_WITH_BASEBACKUP. Validated end-to-end in pgha-test dry run: bootstrap, cascade replication, live write propagation (<3s lag), promotion (zero data loss), and both graceful + hard-crash failover drills post-promotion. See ADR-0001 note for full validation detail.
Auth prerequisite (already live in production): standby role (REPLICATION LOGIN) + matching pg_hba.conf line (host replication standby 10.0.4.0/24 scram-sha-256) — created and network-proven against the live postgresql_postgresql container. Nothing further needed here.
File: postgresql/cutover/postgresql-ha-staging.yaml — the exact, already-rewritten (standby_cluster, no CLONE_*) compose file this cutover deploys. Not yet deployed for real prior to this procedure.

Why the phased order looks the way it does (the actual fix over the failed attempt)

The prior failed attempt scaled legacy to 0 before HAProxy could answer to the postgresql/db aliases — a DNS gap where no backend answered at all for a window. The fix is not simply "add the alias first" in isolation — that alone introduces two worse failure modes:

If HAProxy joins the alias before Patroni is genuinely promoted, HAProxy's own healthcheck (GET /primary, expects 200) correctly reports the standby_leader as unhealthy (it's read-only, not a real primary) — so any connection Swarm's round-robin DNS routes to HAProxy during that window hits zero healthy backends and fails outright. Worse than the original gap, not better.
If legacy is still accepting writes at the exact moment of promotion, any write landing on legacy after the replica's last synced LSN is silently lost the instant Patroni becomes the new source of truth — a split-brain data-loss risk, not just a DNS problem.

The corrected order below closes both gaps: legacy is flipped read-only before promotion (closing gap #2, fully reversible with one command), and the alias is only added after HAProxy has something genuinely healthy to route to (closing gap #1, and incidentally also fixing the original DNS-gap bug since legacy is never removed from DNS until a healthy replacement already answers).

3. Phased cutover procedure

Executed by cutover.sh, run directly on docker-2. Each phase has an explicit, automatic check; any failed check triggers immediate invocation of rollback.sh and the script exits nonzero — no manual intervention required to reach a safe state.

Phase	Action	Verification before proceeding	Rollback if this phase fails
0	Run `preflight.sh` (backup, catalog sanity, disk space, consumer enumeration)	All checks pass	Abort before touching anything live — nothing to roll back
1	Deploy `postgresqlha` stack (`postgresql-ha-staging.yaml`)	All 6 services (`etcd-1/2/3`, `patroni-0`, `patroni-1`, `haproxy`) running; `patroni-1` reaches `standby_leader` via `/cluster` REST	`docker stack rm postgresqlha`, wipe fresh data dirs it created
2	Wait for streaming lag = 0 (`patroni-0` replica of `patroni-1`, both streaming)	`/cluster` REST shows `lag: 0` on both nodes for 3 consecutive polls	Same as Phase 1 — nothing on legacy touched yet
3	Canary write/propagation check (pre-promotion, sanity only) — insert a timestamped row into a disposable scratch table on legacy, confirm it appears on `patroni-0` (far cascade replica) within 5s	Row appears	Same as Phase 1
4	Flip legacy read-only: `ALTER SYSTEM SET default_transaction_read_only = on; SELECT pg_reload_conf();` against legacy	Confirm via `SHOW default_transaction_read_only;` returns `on`; confirm one more streaming-lag poll = 0 (drains any last in-flight write)	Reverse immediately: `ALTER SYSTEM SET default_transaction_read_only = off; SELECT pg_reload_conf();` — legacy resumes normal read-write service, consumers were never touched, no visible outage
5	Promote Patroni: remove `standby_cluster` key from DCS via `etcdctl` (validated method — no dedicated `patronictl` subcommand exists)	`patroni-1` transitions `standby_leader` → `leader` within ~30s (dry run: ~15s); `pg_is_in_recovery()` returns `false` on `patroni-1`	Reverse Phase 4 (read-only off) — legacy is still the only thing anything is talking to, so this is a clean abort
6	Confirm HAProxy backend health	`curl` the HAProxy stats page (`:7000/`, internal-only) or `GET /primary` directly against each Patroni node shows exactly one healthy backend (the new leader)	Reverse Phase 4 — legacy resumes r/w, new HA stack is simply torn down (it never took live traffic)
7	Add `postgresql`/`db` aliases to HAProxy on `postgresql_db-backend` (deploy HAProxy with the alias config now, or `docker network connect --alias` if already running)	`getent hosts postgresql` from a disposable probe container on that network resolves to both legacy's IP and HAProxy's IP	Remove the alias (`docker network disconnect`/redeploy without it) — legacy is still answering `postgresql` alone the whole time, zero consumer impact
8	Canary write/propagation check through the alias — connect to `postgresql:5432` (not directly to a Patroni node), write a timestamped row, confirm visible on both `patroni-0` and `patroni-1`	Round-trip succeeds, row visible on both nodes	Remove alias (Phase 7 rollback) — legacy still primary of record
9	Verify each known consumer reconnects and can write through the alias — for each stack in the dependency map, run its lightest real health check (e.g., Authentik `ak healthcheck`, Gitea `/api/healthz`, Woodpecker `/healthz`, Open WebUI `/health`, LiteLLM `/health`)	All pass	Full rollback via `rollback.sh` (see below) — treat any consumer failure here as cutover failure, not a per-consumer issue to patch around
10	Stop legacy: `docker service scale postgresql_postgresql=0`	HAProxy backend still healthy post-legacy-stop; `getent hosts postgresql` now resolves to HAProxy only; repeat consumer health checks from Phase 9	Restore legacy: `docker service scale postgresql_postgresql=1`, confirm it rejoins DNS; since it was read-only since Phase 4 and never diverged from the new leader's data, no data reconciliation needed at this point — but past this phase, treat rollback as symmetric-but-not-free: HAProxy has been serving real production writes, so rolling back here means picking legacy back up as primary of record and tearing down `postgresqlha`, not simply reversing a flag. Use `rollback.sh`, do not improvise.
11	Final verification pass — all consumers, disk space, `docker stack ps postgresqlha` clean, HAProxy stats page shows steady state	All green	N/A — cutover complete

Design note on why the canary check happens twice (Phase 3 and Phase 8): Phase 3 is a pre-promotion sanity check that streaming genuinely works (matches the dry-run validation methodology). Phase 8 is the actual point of the exercise — proving a write through the real client path (the alias, exactly as every consumer will use it) round-trips correctly, not just that Patroni's internal replication works. Do not skip either on the assumption the other covers it.

4. Rollback procedure

rollback.sh is standalone and callable independently at any point, not just as a cutover.sh failure handler. It detects current state and takes the minimum action to restore the pre-cutover configuration:

If legacy is scaled to 0 → scale back to 1, wait for healthy.
If default_transaction_read_only is on on legacy → set back to off, reload conf.
If HAProxy is holding the postgresql/db aliases → remove them (redeploy HAProxy without the alias, or disconnect/reconnect the network attachment).
Tear down the postgresqlha stack entirely (docker stack rm postgresqlha), wait for task shutdown (verify via docker stack ps — don't assume immediate), then wipe its data dirs so a future attempt starts clean (mirrors the pgha-test teardown precedent from the dry run — data dirs must be wiped, not just the parent dir left behind, since Swarm bind mounts don't auto-create missing host parent dirs; use mkdir -p per-path afterward, no brace expansion in this exec environment).
Re-run the Phase 9-equivalent consumer health checks to confirm everything is back on legacy and functioning.
Print a clear final state summary (which phase it rolled back from, what's now running, what if anything still needs manual follow-up).

If rollback.sh itself fails partway (e.g., legacy won't scale back up): this is the one scenario requiring manual intervention. In that case:

Do not attempt further automation blind — check docker service logs postgresql_postgresql --tail 100 and docker stack ps postgresql --no-trunc first.
The legacy data directory (/volume1/docker/PostgreSQL/data-17) is never deleted by any script in this procedure — only postgresqlha's own dirs are wiped. Worst case, legacy's data is intact and it's a matter of getting the container/service healthy again, not data recovery.
If this chat (Open WebUI/LiteLLM) is unreachable because of this, fall back to direct SSH: ssh root@192.168.4.32, then work from docker service ls, docker stack ps postgresql --no-trunc, docker service logs postgresql_postgresql --tail 100 -f.

5. Known gotchas carried forward from the dry run (still apply)

$(...) vs $$(...) in any command: block — Compose parses $( as attempted ${VAR} interpolation and fails. Already fixed in postgresql-ha-staging.yaml; don't reintroduce if editing.
ETCD3_HOSTS not ETCD_HOSTS — our etcd v3.5.9 has the v2 API disabled.
Docker secrets are immutable — rotating a secret's value does not propagate to running containers or to Woodpecker's provisioning logic until an actual pipeline run re-executes create_or_update_secret. Not expected to matter for this cutover (no secret rotation planned), flagged in case a rollback attempt considers regenerating one.
LXC exec is scoped to docker-2 only and has a ~30s timeout for long-running commands (e.g. docker service create may exceed it even though the operation succeeds) — check docker service ps/docker service logs --raw separately rather than trusting a timeout as failure. To reach containers on docker-1/docker-3, proxy through another container on the same overlay network (e.g. docker exec <legacy> psql -h patroni-1 ...).
Shell globs inside docker exec <container> <cmd> get expanded by the outer shell — wrap in sh -c '...' with the glob inside quotes if one is ever needed.
docker stack rm returns immediately; tasks take several seconds to actually exit — always re-check docker stack ps <name> after a brief wait before wiping any bind-mount data dirs.

6. File manifest

All in postgresql/cutover/ unless noted:

RUNBOOK.md — this file.
preflight.sh — pre-flight checks (backup, catalog sanity, disk space, authoritative consumer enumeration). Run standalone before cutover.sh; cutover.sh also invokes it as Phase 0.
cutover.sh — the phased script described above, Phases 1–11, automatic rollback on any failed check.
rollback.sh — standalone, idempotent, callable independently at any point (also invoked automatically by cutover.sh on failure).
postgresql-ha-staging.yaml — the HA stack compose file this procedure deploys (already written, standby_cluster design, not yet deployed for real prior to this procedure).
post_init_wrapper.sh — Spilo post_init override (superuser placeholder role fix), referenced by postgresql-ha-staging.yaml.
test/pgha-dryrun.yaml — the disposable dry-run design, kept for reference/future re-testing. Not part of the real cutover path.
../haproxy.cfg — HAProxy config (Patroni-aware TCP router), shared between steady-state and this cutover; no changes needed for the cutover itself beyond the alias timing described above.

7. Post-cutover follow-up (manual, not scripted)

Confirm postgresql_postgresql (legacy service) can eventually be fully decommissioned (remove from postgresql.yaml) once the new stack has run stable for a reasonable burn-in period — not part of this procedure, a deliberate later step.
Re-point postgresql.yaml's pgadmin/databasus services at the new topology if they need updating (they connect via the same postgresql alias, so likely no change needed — verify post-cutover).
Revisit the deferred PG18/Spilo major-version upgrade project (see ADR-0001 note) — explicitly out of scope here.

17 KiB Raw Permalink Blame History Unescape Escape