admin
7a06b040ad
ci/woodpecker/push/deploy Pipeline was successful
Root cause of a real cutover run's failure: the standby_leader's basebackup from legacy completed, but its Postgres process then got permanently stuck in "starting" because legacy had no replication slot reserving WAL — normal WAL recycling (checkpoint_timeout=300s) deleted the segment needed to resume streaming during the 8-11 min basebackup window. This also explained why the cascade replica's own basebackup (which targets the standby_leader) failed with "database system is starting up" — one root cause, not two. Fix: created a physical replication slot (standby_leader_slot) on live production legacy, and added primary_slot_name: standby_leader_slot under bootstrap.dcs.standby_cluster in both patroni-0 and patroni-1's SPILO_CONFIGURATION so Patroni pins the slot automatically. Also bumped wal_keep_size to 4GB on legacy as defense-in-depth. Full incident detail documented in this file's header for future reference.
364 lines
17 KiB
YAML
364 lines
17 KiB
YAML
# ─────────────────────────────────────────────────────────────────────────
|
|
# postgresql/cutover/postgresql-ha-staging.yaml — ADR-0001 Phase 3, Stage 2
|
|
#
|
|
# Deployed by postgres-ha-cutover.sh as a SEPARATE stack:
|
|
# docker stack deploy -c postgresql/cutover/postgresql-ha-staging.yaml postgresqlha
|
|
#
|
|
# ⚠️ NEVER deploy this via stack-deploy.sh or under the "postgresql" stack
|
|
# name. It lives in cutover/ (a subdirectory) deliberately:
|
|
# stack-deploy.sh folder mode merges all *.yml/yaml at -maxdepth 1 of
|
|
# postgresql/, and merging this into the production stack would cause
|
|
# an outage. Subdirectory files are excluded from that merge.
|
|
#
|
|
# What this stage does:
|
|
# - Joins the EXISTING postgresql_db-backend overlay (external) — the
|
|
# live single-instance stack is untouched and keeps the "postgresql"
|
|
# and "db" aliases.
|
|
# - patroni-0/patroni-1 bootstrap a NEW cluster (scope: postgres-ha) as a
|
|
# Patroni STANDBY CLUSTER continuously streaming from the live
|
|
# instance (standby_cluster, host: postgresql — resolves to the OLD
|
|
# instance during staging). This REPLACES the earlier
|
|
# CLONE_WITH_BASEBACKUP design (one-shot snapshot) — see "REVISION"
|
|
# note below for why.
|
|
# - HAProxy runs WITHOUT production aliases and WITHOUT published ports.
|
|
# It only gets those in postgresql-ha-final.yaml at promote time.
|
|
# - Superuser is PGadmin (matches the cloned data's existing superuser,
|
|
# same password secret), NOT Spilo's default "postgres".
|
|
#
|
|
# ── REVISION (2026-08-01): switched from CLONE_WITH_BASEBACKUP to a
|
|
# Patroni "standby cluster" (continuous streaming) ──
|
|
#
|
|
# WHY: CLONE_WITH_BASEBACKUP is a ONE-SHOT snapshot — once pg_basebackup
|
|
# completes, the new cluster has ZERO further connection to the live
|
|
# database. Any write landing on production between the snapshot and the
|
|
# actual traffic cutover would be silently lost — a real risk given ~13
|
|
# active consumers and a 42GB production database. Patroni's standby_cluster
|
|
# mode (https://patroni.readthedocs.io/en/latest/standby_cluster.html)
|
|
# instead makes the new cluster's leader ("standby leader") continuously
|
|
# stream from the remote primary indefinitely, right up until an explicit
|
|
# promotion (done by removing the standby_cluster key from DCS config —
|
|
# there is no dedicated patronictl subcommand for this) — closing the gap
|
|
# to near-zero. Fully validated end-to-end in the pgha-test dry run
|
|
# (2026-08-01): bootstrap, continuous live-write propagation to the far
|
|
# cascade replica in <3s, and a full promote-with-zero-data-loss cycle —
|
|
# see "ADR-0001 Dry-Run Debugging Log" note for the complete play-by-play.
|
|
#
|
|
# ⚠️ NEW PRODUCTION PREREQUISITE THIS INTRODUCES — the dry run's
|
|
# "legacy" stand-in creates a genuine replication role named "standby"
|
|
# (matching PGUSER_STANDBY below) via a fresh-initdb hook script, since
|
|
# standby_cluster streaming authenticates using the cluster's real
|
|
# replication identity (PGUSER_STANDBY/PGPASSWORD_STANDBY), NOT the
|
|
# PGadmin superuser that CLONE_WITH_BASEBACKUP used. The LIVE
|
|
# `postgresql_postgresql` container has NO such hook mechanism available
|
|
# (it's long since initialized, hooks only run on fresh initdb) and does
|
|
# NOT currently have a "standby" role. This must be created manually
|
|
# against the live database before real cutover, analogous to how the
|
|
# pg_hba.conf replication rule was added manually — e.g.:
|
|
# CREATE ROLE standby WITH REPLICATION LOGIN PASSWORD '<same value as
|
|
# the postgresql_replication_password secret>';
|
|
# This is NOT part of this compose file (can't be — it's a one-time SQL
|
|
# statement against already-running production, not something a compose
|
|
# file can express) but MUST be tracked and done as an explicit manual
|
|
# step in the real Phase 3 procedure. STATUS: DONE — see ADR-0001 note,
|
|
# "Production prerequisite" section, for full verification detail
|
|
# (CREATE ROLE + pg_hba.conf line, both proven end-to-end).
|
|
#
|
|
# ── REVISION 2 (2026-08-04): added primary_slot_name to standby_cluster —
|
|
# fixes a real bootstrap failure hit on a live production run ──
|
|
#
|
|
# WHAT HAPPENED: on a real cutover attempt, the standby_leader's OWN
|
|
# basebackup from legacy completed successfully, but the resulting
|
|
# Postgres process then got stuck in "starting"/"rejecting connections"
|
|
# PERMANENTLY (confirmed via the container's own pg_log/*.csv, since
|
|
# docker service logs stops showing Postgres's own LOG lines once the
|
|
# logging collector activates — check
|
|
# <data-dir>/pgroot/pg_log/postgresql-N.csv directly, NOT `docker service
|
|
# logs`, if this needs debugging again). The actual error, repeating
|
|
# every ~5s indefinitely:
|
|
# LOG: started streaming WAL from primary at <LSN> on timeline 1
|
|
# FATAL: could not receive data from WAL stream: ERROR: requested WAL
|
|
# segment <segment> has already been removed
|
|
# LOG: waiting for WAL to become available at <LSN>
|
|
# ROOT CAUSE: legacy had NO replication slot reserving WAL for this
|
|
# connection (confirmed: `select * from pg_replication_slots` = 0 rows,
|
|
# wal_keep_size = 0, max_slot_wal_keep_size = -1/irrelevant with no slot).
|
|
# basebackup_fast_xlog copies data files, then Postgres must replay WAL
|
|
# FORWARD from the backup's start LSN via a normal streaming connection to
|
|
# catch up. With no slot, legacy's normal WAL recycling
|
|
# (checkpoint_timeout=300s) deleted the exact segment needed to resume
|
|
# during the 8-11 minute basebackup window — once gone, gone forever, and
|
|
# the standby_leader can never leave "starting". This ALSO explains why
|
|
# the OTHER node's (the cascade replica's) basebackup then failed with
|
|
# "the database system is starting up" — it targets the standby_leader,
|
|
# which never actually finished starting. Not two bugs — one root cause
|
|
# propagating downstream exactly as it would look if it were two.
|
|
# (Secondary, non-blocking finding from the same run: ~2.2GB of core dump
|
|
# files appeared in the standby_leader's data dir root at the moment of
|
|
# basebackup completion — not yet root-caused, possibly related to
|
|
# bg_mon's repeated port-8080-bind failures inside the container, but NOT
|
|
# confirmed as the same issue and NOT blocking this fix.)
|
|
# THE FIX (this revision): a physical replication slot named
|
|
# "standby_leader_slot" was created on live production legacy:
|
|
# SELECT pg_create_physical_replication_slot('standby_leader_slot');
|
|
# and `primary_slot_name: standby_leader_slot` was added under
|
|
# bootstrap.dcs.standby_cluster in BOTH patroni-0 and patroni-1's
|
|
# SPILO_CONFIGURATION below (Patroni's standby_cluster feature reads this
|
|
# key and uses it to pin the slot on the external primary automatically —
|
|
# no additional Patroni-side config needed). As defense-in-depth,
|
|
# `wal_keep_size` was ALSO bumped from 0 to 4096MB (4GB) directly on live
|
|
# legacy (`ALTER SYSTEND SET wal_keep_size = '4096MB'; SELECT
|
|
# pg_reload_conf();` — NOTE: ALTER SYSTEM cannot share a transaction with
|
|
# other statements, run as two separate -c calls) — belt-and-suspenders
|
|
# in case the slot itself is ever inadvertently dropped mid-cutover.
|
|
# WHY THIS DIDN'T SURFACE EARLIER: the pgha-test dry run used a tiny
|
|
# disposable database with a near-instant basebackup, leaving no real
|
|
# window for WAL to churn before streaming resumed. It's plausible an even
|
|
# earlier real attempt got lucky on timing. With a real ~42GB dataset and
|
|
# multi-minute basebackups, the gap is real and reproducible.
|
|
# STATUS: slot created and verified on live production
|
|
# (`select slot_name, slot_type, active from pg_replication_slots` shows
|
|
# standby_leader_slot/physical/f — inactive is expected/correct until a
|
|
# standby_cluster actually connects to it). wal_keep_size verified at 4GB.
|
|
# This compose file updated to reference the slot. NOT YET RE-RUN against
|
|
# production as of this revision — see ADR-0001 note's "Not yet done"
|
|
# section for current status.
|
|
#
|
|
# NOTE: command: blocks use $$(...) not $(...) — Compose's own variable
|
|
# interpolation parses $( as an attempted ${VAR} reference and fails with
|
|
# "invalid interpolation format" / "you may need to escape any $ with
|
|
# another $". $$ escapes to a literal $ for the shell at runtime. This
|
|
# applies uniformly to EVERY literal $ character anywhere in a command:
|
|
# block, including inside heredocs and nested quoting — Compose scans the
|
|
# raw text before any shell/heredoc logic ever runs, so quoting context
|
|
# doesn't exempt anything from this escaping requirement. (Confirmed the
|
|
# hard way in the pgha-test dry run — this file originally had the
|
|
# unescaped $(...) bug too, before this standby_cluster rewrite.)
|
|
#
|
|
# NOTE: ETCD3_HOSTS not ETCD_HOSTS — confirmed against zalando/spilo
|
|
# configure_spilo.py: PATRONI_DCS includes both "etcd" (legacy v2 API,
|
|
# python-etcd client) and "etcd3" (v3 API, python-etcd3 client) as
|
|
# distinct DCS backends selected by env var prefix. Our etcd containers
|
|
# (v3.5.9) have the v2 API disabled by default, so ETCD_HOSTS causes
|
|
# Patroni to hit /v2 endpoints that 404. ETCD3_HOSTS selects the correct
|
|
# v3-API client.
|
|
#
|
|
# NOTE: patroni-0/patroni-1 override bootstrap.post_init via
|
|
# SPILO_CONFIGURATION (Spilo's documented, supported mechanism for
|
|
# overriding any generated Patroni config — configure_spilo.py deep-merges
|
|
# user-supplied SPILO_CONFIGURATION on top of its own generated config).
|
|
# This is needed because Spilo's own /scripts/post_init.sh hardcodes
|
|
# "ALTER VIEW ... OWNER TO postgres" with no way to parameterize that role
|
|
# name via env vars. Since our superuser is PGUSER_SUPERUSER=PGadmin, there
|
|
# is no role literally named "postgres" in the cloned data, so Spilo's
|
|
# unmodified script fails with 'ERROR: role "postgres" does not exist'.
|
|
# The override points bootstrap.post_init at our own wrapper script
|
|
# instead, which creates a harmless, idempotent "postgres" role (WITH
|
|
# SUPERUSER NOLOGIN — SUPERUSER is required because Spilo's own
|
|
# _zmon_schema.dump does "SET ROLE TO postgres; CREATE EXTENSION
|
|
# plpython3u", which needs real superuser privileges, not just role
|
|
# existence; NOLOGIN means it can never be used to establish a real
|
|
# connection, so this carries no security exposure), then execs Spilo's
|
|
# real, UNMODIFIED post_init.sh with all original arguments passed through
|
|
# — Zalando's script itself is never patched or forked. Both fixes (role
|
|
# existence + SUPERUSER) were found and validated end-to-end in the
|
|
# pgha-test dry run before being ported here — see the dry-run debugging
|
|
# note for full detail. This is CRITICAL for this file specifically since
|
|
# it streams real production data with the real PGadmin superuser.
|
|
# ─────────────────────────────────────────────────────────────────────────
|
|
version: "3.6"
|
|
|
|
services:
|
|
|
|
etcd-1:
|
|
image: quay.io/coreos/etcd:v3.5.9
|
|
hostname: etcd-1
|
|
command:
|
|
- etcd
|
|
- --name=etcd-1
|
|
- --data-dir=/etcd-data
|
|
- --initial-advertise-peer-urls=http://etcd-1:2380
|
|
- --listen-peer-urls=http://0.0.0.0:2380
|
|
- --listen-client-urls=http://0.0.0.0:2379
|
|
- --advertise-client-urls=http://etcd-1:2379
|
|
- --initial-cluster=etcd-1=http://etcd-1:2380,etcd-2=http://etcd-2:2380,etcd-3=http://etcd-3:2380
|
|
- --initial-cluster-state=new
|
|
- --initial-cluster-token=postgresql-ha-etcd
|
|
volumes:
|
|
- /volume1/docker/PostgreSQL/etcd-1-data:/etcd-data
|
|
networks:
|
|
- postgresql_db-backend
|
|
deploy:
|
|
placement:
|
|
constraints:
|
|
- node.hostname == docker-1
|
|
|
|
etcd-2:
|
|
image: quay.io/coreos/etcd:v3.5.9
|
|
hostname: etcd-2
|
|
command:
|
|
- etcd
|
|
- --name=etcd-2
|
|
- --data-dir=/etcd-data
|
|
- --initial-advertise-peer-urls=http://etcd-2:2380
|
|
- --listen-peer-urls=http://0.0.0.0:2380
|
|
- --listen-client-urls=http://0.0.0.0:2379
|
|
- --advertise-client-urls=http://etcd-2:2379
|
|
- --initial-cluster=etcd-1=http://etcd-1:2380,etcd-2=http://etcd-2:2380,etcd-3=http://etcd-3:2380
|
|
- --initial-cluster-state=new
|
|
- --initial-cluster-token=postgresql-ha-etcd
|
|
volumes:
|
|
- /volume1/docker/PostgreSQL/etcd-2-data:/etcd-data
|
|
networks:
|
|
- postgresql_db-backend
|
|
deploy:
|
|
placement:
|
|
constraints:
|
|
- node.hostname == docker-2
|
|
|
|
etcd-3:
|
|
image: quay.io/coreos/etcd:v3.5.9
|
|
hostname: etcd-3
|
|
command:
|
|
- etcd
|
|
- --name=etcd-3
|
|
- --data-dir=/etcd-data
|
|
- --initial-advertise-peer-urls=http://etcd-3:2380
|
|
- --listen-peer-urls=http://0.0.0.0:2380
|
|
- --listen-client-urls=http://0.0.0.0:2379
|
|
- --advertise-client-urls=http://etcd-3:2379
|
|
- --initial-cluster=etcd-1=http://etcd-1:2380,etcd-2=http://etcd-2:2380,etcd-3=http://etcd-3:2380
|
|
- --initial-cluster-state=new
|
|
- --initial-cluster-token=postgresql-ha-etcd
|
|
volumes:
|
|
- /volume1/docker/PostgreSQL/etcd-3-data:/etcd-data
|
|
networks:
|
|
- postgresql_db-backend
|
|
deploy:
|
|
placement:
|
|
constraints:
|
|
- node.hostname == docker-3
|
|
|
|
patroni-0:
|
|
image: ghcr.io/zalando/spilo-17:4.0-p3
|
|
hostname: patroni-0
|
|
command:
|
|
- /bin/sh
|
|
- -c
|
|
- |
|
|
export PGPASSWORD_SUPERUSER="$$(cat /run/secrets/postgresql_password)"
|
|
export PGPASSWORD_STANDBY="$$(cat /run/secrets/postgresql_replication_password)"
|
|
export PATRONI_RESTAPI_PASSWORD="$$(cat /run/secrets/postgresql_patroni_password)"
|
|
mkdir -p /scripts
|
|
cat > /scripts/post_init_wrapper.sh <<'WRAP'
|
|
#!/bin/bash
|
|
set -e
|
|
psql -d "$$2" -v ON_ERROR_STOP=1 -c 'DO $$do$$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = $$x$$postgres$$x$$) THEN CREATE ROLE postgres WITH SUPERUSER NOLOGIN; END IF; END $$do$$;'
|
|
exec /scripts/post_init.sh "$$@"
|
|
WRAP
|
|
chmod +x /scripts/post_init_wrapper.sh
|
|
exec /bin/sh /launch.sh init
|
|
environment:
|
|
SCOPE: postgres-ha
|
|
PATRONI_NAME: patroni-0
|
|
ETCD3_HOSTS: '"etcd-1:2379","etcd-2:2379","etcd-3:2379"'
|
|
PGUSER_SUPERUSER: PGadmin
|
|
PGUSER_STANDBY: standby
|
|
PATRONI_RESTAPI_USERNAME: patroni
|
|
PGROOT: /home/postgres/pgdata/pgroot
|
|
SPILO_CONFIGURATION: |
|
|
bootstrap:
|
|
post_init: /scripts/post_init_wrapper.sh "zalandos"
|
|
dcs:
|
|
standby_cluster:
|
|
host: postgresql
|
|
port: 5432
|
|
primary_slot_name: standby_leader_slot
|
|
create_replica_methods:
|
|
- basebackup_fast_xlog
|
|
secrets:
|
|
- postgresql_password
|
|
- postgresql_replication_password
|
|
- postgresql_patroni_password
|
|
volumes:
|
|
- /volume1/docker/PostgreSQL/patroni-0-data:/home/postgres/pgdata
|
|
networks:
|
|
- postgresql_db-backend
|
|
deploy:
|
|
placement:
|
|
constraints:
|
|
- node.labels.pg-role == primary
|
|
|
|
patroni-1:
|
|
image: ghcr.io/zalando/spilo-17:4.0-p3
|
|
hostname: patroni-1
|
|
command:
|
|
- /bin/sh
|
|
- -c
|
|
- |
|
|
export PGPASSWORD_SUPERUSER="$$(cat /run/secrets/postgresql_password)"
|
|
export PGPASSWORD_STANDBY="$$(cat /run/secrets/postgresql_replication_password)"
|
|
export PATRONI_RESTAPI_PASSWORD="$$(cat /run/secrets/postgresql_patroni_password)"
|
|
mkdir -p /scripts
|
|
cat > /scripts/post_init_wrapper.sh <<'WRAP'
|
|
#!/bin/bash
|
|
set -e
|
|
psql -d "$$2" -v ON_ERROR_STOP=1 -c 'DO $$do$$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = $$x$$postgres$$x$$) THEN CREATE ROLE postgres WITH SUPERUSER NOLOGIN; END IF; END $$do$$;'
|
|
exec /scripts/post_init.sh "$$@"
|
|
WRAP
|
|
chmod +x /scripts/post_init_wrapper.sh
|
|
exec /bin/sh /launch.sh init
|
|
environment:
|
|
SCOPE: postgres-ha
|
|
PATRONI_NAME: patroni-1
|
|
ETCD3_HOSTS: '"etcd-1:2379","etcd-2:2379","etcd-3:2379"'
|
|
PGUSER_SUPERUSER: PGadmin
|
|
PGUSER_STANDBY: standby
|
|
PATRONI_RESTAPI_USERNAME: patroni
|
|
PGROOT: /home/postgres/pgdata/pgroot
|
|
SPILO_CONFIGURATION: |
|
|
bootstrap:
|
|
post_init: /scripts/post_init_wrapper.sh "zalandos"
|
|
dcs:
|
|
standby_cluster:
|
|
host: postgresql
|
|
port: 5432
|
|
primary_slot_name: standby_leader_slot
|
|
create_replica_methods:
|
|
- basebackup_fast_xlog
|
|
secrets:
|
|
- postgresql_password
|
|
- postgresql_replication_password
|
|
- postgresql_patroni_password
|
|
volumes:
|
|
- /volume1/docker/PostgreSQL/patroni-1-data:/home/postgres/pgdata
|
|
networks:
|
|
- postgresql_db-backend
|
|
deploy:
|
|
placement:
|
|
constraints:
|
|
- node.labels.pg-role == replica
|
|
|
|
# Staging HAProxy: NO production aliases, NO published ports. Reachable
|
|
# in-network as "haproxy" for pre-promote testing only.
|
|
haproxy:
|
|
image: haproxy:2.9-alpine
|
|
hostname: haproxy
|
|
volumes:
|
|
- /volume1/docker/compose-files/postgresql/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro
|
|
networks:
|
|
- postgresql_db-backend
|
|
deploy:
|
|
mode: global
|
|
|
|
networks:
|
|
postgresql_db-backend:
|
|
external: true
|
|
|
|
secrets:
|
|
postgresql_password:
|
|
external: true
|
|
postgresql_replication_password:
|
|
external: true
|
|
postgresql_patroni_password:
|
|
external: true
|