320 lines
13 KiB
YAML
320 lines
13 KiB
YAML
# ─────────────────────────────────────────────────────────────────────────
|
|
# postgresql/postgresql.yaml — Patroni + etcd + HAProxy HA stack (ADR-0001)
|
|
# BOOTSTRAP TIER — manual deploy only via:
|
|
# bash /volume1/docker/compose-files/deploy/stack-deploy.sh postgresql
|
|
#
|
|
# Env var names for Spilo/Patroni verified against upstream source
|
|
# (zalando/spilo ENVIRONMENT.rst, configure_spilo.py, launch.sh) and the
|
|
# Patroni ENVIRONMENT docs — NOT guessed. Spilo has no native Docker-secret
|
|
# _FILE suffix support, so patroni-0/patroni-1 use a command wrapper to
|
|
# read secrets from /run/secrets and export them as the plain env vars
|
|
# Spilo's configure_spilo.py actually expects, before invoking the image's
|
|
# real entrypoint chain (/launch.sh init).
|
|
#
|
|
# 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 — see "ADR-0001 Dry-Run Debugging Log"
|
|
# note — this file originally had the unescaped $(...) bug.)
|
|
#
|
|
# 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. (Also originally wrong in this file — fixed after dry run.)
|
|
#
|
|
# 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 (to
|
|
# match production), 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.
|
|
# ─────────────────────────────────────────────────────────────────────────
|
|
version: "3.6"
|
|
|
|
services:
|
|
|
|
# ── etcd (3-node Raft quorum — NOT data replicas, ~150MB RAM total) ──────
|
|
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 / Spilo data replicas (2 total, per user constraint) ────────
|
|
# Image tag pinned to 4.0-p3 (Zalando postgres-operator's own referenced
|
|
# default at time of writing) — re-verify against current releases
|
|
# before actual deploy, as patch tags move. Decision (2026-07-30): staying
|
|
# on PostgreSQL 17 / this Spilo line for now; PG18 deferred as a separate
|
|
# future project pending app-compatibility checks. See ADR-0001 notes.
|
|
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_STANDBY: standby
|
|
PATRONI_RESTAPI_USERNAME: patroni
|
|
PGROOT: /home/postgres/pgdata/pgroot
|
|
SPILO_CONFIGURATION: |
|
|
bootstrap:
|
|
post_init: /scripts/post_init_wrapper.sh "zalandos"
|
|
# standby_cluster is NOT configured in this steady-state bootstrap file —
|
|
# it only applies during Phase 3 cutover staging
|
|
# (postgresql/cutover/postgresql-ha-staging.yaml), which streams
|
|
# continuously from the live single instance until promotion. See
|
|
# ADR-0001 Dry-Run Debugging Log note for the standby_cluster design.
|
|
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_STANDBY: standby
|
|
PATRONI_RESTAPI_USERNAME: patroni
|
|
PGROOT: /home/postgres/pgdata/pgroot
|
|
SPILO_CONFIGURATION: |
|
|
bootstrap:
|
|
post_init: /scripts/post_init_wrapper.sh "zalandos"
|
|
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
|
|
|
|
# ── HAProxy — TCP routing via Patroni REST /primary health check only ───
|
|
# No placement constraint: all 3 nodes are managers (node.role == worker
|
|
# matches nothing in this cluster). mode: global + no constraint = one
|
|
# replica per node (docker-1/2/3), matching ADR's "HAProxy global, 3x".
|
|
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:
|
|
aliases:
|
|
- postgresql
|
|
- db
|
|
authentik_backend: {}
|
|
ports:
|
|
- 5430:5432/tcp
|
|
deploy:
|
|
mode: global
|
|
labels:
|
|
- traefik.enable=true
|
|
- traefik.tcp.routers.postgres.entrypoints=postgresql
|
|
- traefik.tcp.routers.postgres.rule=HostSNI(`*`)
|
|
- traefik.tcp.services.postgres.loadbalancer.server.port=5432
|
|
- traefik.tcp.routers.postgres.service=postgres
|
|
- traefik.swarm.network=traefik_backend
|
|
|
|
# ── Unchanged from current flat postgresql.yaml ──────────────────────────
|
|
databasus:
|
|
hostname: databasus
|
|
image: databasus/databasus:latest
|
|
networks:
|
|
- traefik_backend
|
|
- postgresql_db-backend
|
|
volumes:
|
|
- /volume1/docker/databasus:/databasus-data
|
|
deploy:
|
|
labels:
|
|
- traefik.enable=true
|
|
- traefik.http.routers.databasus.rule=Host(`${DATABASUS_HOST}`)
|
|
- traefik.http.routers.databasus.tls=true
|
|
- traefik.http.routers.databasus.tls.certresolver=letsencrypt
|
|
- traefik.http.routers.databasus.entrypoints=websecure
|
|
- traefik.http.services.databasus.loadbalancer.server.port=4005
|
|
- traefik.swarm.network=traefik_backend
|
|
|
|
pgadmin:
|
|
image: dpage/pgadmin4:latest
|
|
environment:
|
|
PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL}
|
|
PGADMIN_DEFAULT_PASSWORD_FILE: /run/secrets/postgresql_pgadmin_password
|
|
PGADMIN_LISTEN_PORT: 80
|
|
secrets:
|
|
- postgresql_pgadmin_password
|
|
volumes:
|
|
- "/volume1/docker/PostgreSQL Admin:/var/lib/pgadmin"
|
|
ports:
|
|
- 3030:80
|
|
networks:
|
|
- postgresql_db-backend
|
|
- traefik_backend
|
|
deploy:
|
|
labels:
|
|
- traefik.enable=true
|
|
- traefik.http.routers.pgadmin.rule=Host(`${PGADMIN_HOST}`)
|
|
- traefik.http.routers.pgadmin.tls=true
|
|
- traefik.http.routers.pgadmin.tls.certresolver=letsencrypt
|
|
- traefik.http.routers.pgadmin.entrypoints=websecure
|
|
- traefik.http.services.pgadmin.loadbalancer.server.port=80
|
|
- traefik.swarm.network=traefik_backend
|
|
resources:
|
|
limits:
|
|
cpus: '1'
|
|
memory: 512M
|
|
|
|
networks:
|
|
postgresql_db-backend:
|
|
name: postgresql_db-backend
|
|
attachable: true
|
|
driver: overlay
|
|
driver_opts:
|
|
com.docker.network.driver.mtu: "8950" # preserve — MTU mismatch previously broke Vaultwarden/Immich
|
|
traefik_backend:
|
|
external: true
|
|
authentik_backend:
|
|
external: true
|
|
|
|
secrets:
|
|
postgresql_password:
|
|
external: true
|
|
postgresql_pgadmin_password:
|
|
external: true
|
|
postgresql_replication_password:
|
|
external: true
|
|
postgresql_patroni_password:
|
|
external: true
|