PostgreSQL Patroni Cluster — Technical Reference

1. Infrastructure Requirements

Nodes

Node	Role	OS	ZFS
db-node-1	Preferred primary	NixOS	Yes
db-node-2	Replica	NixOS	Yes
db-node-3	Replica	NixOS	Yes

All three nodes join the existing Docker Swarm as worker nodes (not managers — as to not change Quorum calculations).

NixOS Firewall Rules

networking.firewall.allowedTCPPorts = [
  2377   # Docker Swarm management
  7946   # Docker Swarm node discovery
  5432   # PostgreSQL
  5010   # Patroni Raft consensus
  8008   # Patroni REST API
  5000   # HAProxy primary routing
  5001   # HAProxy replica routing
  7000   # HAProxy stats dashboard
];
networking.firewall.allowedUDPPorts = [
  4789   # Docker VXLAN overlay
  7946   # Docker Swarm node discovery
];
# Required ports for Keepalived to communicate inter-node.
extraCommands = ''
         iptables -A INPUT  -p vrrp -j ACCEPT
         iptables -A OUTPUT -p vrrp -j ACCEPT
       '';
       extraStopCommands = ''
         iptables -D INPUT  -p vrrp -j ACCEPT || true
         iptables -D OUTPUT -p vrrp -j ACCEPT || true
       '';

Docker Configuration

# If Docker Hub has IPv6 issues
virtualisation.docker.daemon.settings = {
  dns = ["1.1.1.1" "8.8.8.8"];
};

# If using a private insecure registry
virtualisation.docker.insecureRegistries = [
  "registry-host:port"
];

2. Docker Image

Project Structure

patroni-cluster/
├── image/
│   ├── Dockerfile
│   └── entrypoint.sh
├── config/
│   ├── patroni.yml.tpl
│   └── haproxy.cfg
├── docker-compose.yml
└── create-db.sh

Dockerfile

FROM postgres:16-bookworm AS builder

RUN apt-get update \
    && apt-get install -y --no-install-recommends \
        python3-venv \
    && python3 -m venv /opt/patroni \
    && /opt/patroni/bin/pip install --no-cache-dir \
        patroni[raft] \
        psycopg[binary] \
    && find /opt/patroni -type d -name '__pycache__' -exec rm -rf {} + 2>/dev/null || true \
    && rm -rf /root/.cache

FROM postgres:16-bookworm

LABEL maintainer="homelab"
LABEL description="PostgreSQL 16 + Patroni (Raft consensus)"

RUN apt-get update \
    && apt-get install -y --no-install-recommends \
        python3 \
        gettext-base \
        curl \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*

COPY --from=builder /opt/patroni /opt/patroni
ENV PATH="/opt/patroni/bin:$PATH"

RUN mkdir -p /home/postgres/pgdata /home/postgres/raft /etc/patroni \
    && chown -R postgres:postgres /home/postgres /etc/patroni

COPY --chown=postgres:postgres entrypoint.sh /usr/local/bin/
RUN chmod 755 /usr/local/bin/entrypoint.sh

USER postgres

EXPOSE 5432 8008 5010

ENTRYPOINT ["entrypoint.sh"]

HEALTHCHECK --interval=10s --timeout=5s --start-period=30s --retries=3 \
    CMD curl -sf http://localhost:8008/health || exit 1

Key decisions:

Multi-stage build keeps the final image small (~400MB vs ~1.5GB for Zalando's Spilo)
psycopg[binary] is required — Patroni needs a Python PostgreSQL driver, and the base Postgres image doesn't include one
gettext-base provides envsubst for templating the Patroni config
curl is needed for the HEALTHCHECK

entrypoint.sh

#!/bin/bash
set -euo pipefail

# Fix data directory permissions — PostgreSQL requires 0700
chmod 700 /home/postgres/pgdata 2>/dev/null || true
chmod 700 /home/postgres/raft 2>/dev/null || true

# Read Docker secrets
read_secret() {
    local file="/run/secrets/$1"
    if [ ! -f "$file" ]; then
        printf 'FATAL: secret %s not found at %s\n' "$1" "$file" >&2
        exit 1
    fi
    tr -d '[:space:]' < "$file"
}

export REPLICATION_PASSWORD="$(read_secret pg_replication_pw)"
export SUPERUSER_PASSWORD="$(read_secret pg_superuser_pw)"

# Validate required environment variables
for var in PATRONI_NAME RAFT_SELF RAFT_PARTNERS FAILOVER_PRIORITY; do
    if [ -z "${!var:-}" ]; then
        printf 'FATAL: environment variable %s is not set\n' "$var" >&2
        exit 1
    fi
done

# Generate Patroni config from template
envsubst < /etc/patroni/patroni.yml.tpl > /tmp/patroni.yml
chmod 600 /tmp/patroni.yml

printf '=== Patroni starting ===\n'
printf '  Node:     %s\n' "$PATRONI_NAME"
printf '  Raft:     %s\n' "$RAFT_SELF"
printf '  Partners: %s\n' "$RAFT_PARTNERS"
printf '  Priority: %s\n' "$FAILOVER_PRIORITY"

exec patroni /tmp/patroni.yml

Why chmod 700: Docker volumes may be created with broader permissions than PostgreSQL allows. PG requires its data directory to be 0700 and will refuse to start otherwise. This was discovered during initial deployment when replicas failed with data directory has invalid permissions.

3. Configuration Files

config/patroni.yml.tpl

scope: homelab
name: ${PATRONI_NAME}

raft:
  self_addr: "${RAFT_SELF}"
  data_dir: /home/postgres/raft
  partner_addrs: ${RAFT_PARTNERS}

restapi:
  listen: 0.0.0.0:8008
  connect_address: "${PATRONI_CONNECT}:8008"

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        max_wal_senders: 5
        max_replication_slots: 5
        hot_standby: "on"
        wal_log_hints: "on"
        archive_mode: "off"
        shared_buffers: 256MB
        effective_cache_size: 512MB
        work_mem: 4MB
        maintenance_work_mem: 64MB
        max_connections: 200
        password_encryption: scram-sha-256
        log_connections: "on"
        log_disconnections: "on"
        log_statement: ddl
        log_line_prefix: '%m [%p] %q%u@%d '

  pg_hba:
    - local   all             postgres                          peer
    - local   all             all                               scram-sha-256
    - host    replication     replicator  <LAN_SUBNET>          scram-sha-256
    - host    replication     replicator  10.0.0.0/8            scram-sha-256
    - host    all             all         <LAN_SUBNET>          scram-sha-256
    - host    all             all         10.0.0.0/8            scram-sha-256
    - host    all             all         172.16.0.0/12         scram-sha-256
    - host    all             all         0.0.0.0/0             reject

  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: "${PATRONI_CONNECT}:5432"
  data_dir: /home/postgres/pgdata
  pgpass: /tmp/pgpass0
  authentication:
    replication:
      username: replicator
      password: "${REPLICATION_PASSWORD}"
    superuser:
      username: postgres
      password: "${SUPERUSER_PASSWORD}"

tags:
  failover_priority: ${FAILOVER_PRIORITY}
  nofailover: false
  noloadbalancer: false
  clonefrom: true

Configuration rationale:

Parameter	Value	Why
`ttl: 30`	Leader lock timeout	Lower = faster failover detection but more sensitive to transient network issues
`loop_wait: 10`	Patroni main loop interval	How often Patroni checks cluster state
`maximum_lag_on_failover: 1048576`	1MB	Don't promote a replica that's more than 1MB behind — prevents data loss
`use_pg_rewind: true`	Enable pg_rewind	Allows old primary to rejoin without full base backup — requires `wal_log_hints`
`wal_log_hints: "on"`	Hint bit logging	Required for pg_rewind to work
`data-checksums`	Enabled at initdb	Detects silent data corruption, required for pg_rewind
`archive_mode: "off"`	No WAL archiving	Relying on ZFS snapshots + streaming replication instead
`password_encryption: scram-sha-256`	Modern auth	Never use md5 for new deployments

pg_hba notes:

10.0.0.0/8 covers all Docker internal networks (ingress mesh, overlay, bridge). This is necessary because Docker NATs replication connections through internal networks, and the source IP may be from unexpected ranges like 10.0.4.x
172.16.0.0/12 covers Docker's default bridge network ranges
The final reject rule denies all other connections
Replace <LAN_SUBNET> with your actual network CIDR

config/haproxy.cfg

global
    maxconn 500
    log stdout format raw local0

defaults
    mode tcp
    timeout connect 5s
    timeout client  30m
    timeout server  30m
    timeout check   3s
    log global
    option tcplog

listen pg-primary
    bind *:5000
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server patroni-1 <DB_NODE_1_IP>:5432 maxconn 200 check port 8008
    server patroni-2 <DB_NODE_2_IP>:5432 maxconn 200 check port 8008
    server patroni-3 <DB_NODE_3_IP>:5432 maxconn 200 check port 8008

listen pg-replicas
    bind *:5001
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server patroni-1 <DB_NODE_1_IP>:5432 maxconn 200 check port 8008
    server patroni-2 <DB_NODE_2_IP>:5432 maxconn 200 check port 8008
    server patroni-3 <DB_NODE_3_IP>:5432 maxconn 200 check port 8008

listen stats
    bind *:7000
    mode http
    stats enable
    stats uri /
    stats refresh 5s
    stats show-legends
    acl local src <LAN_SUBNET> 10.0.0.0/8 127.0.0.0/8
    http-request deny unless local

Key HAProxy mechanisms:

option httpchk GET /primary — queries Patroni REST API. Only the current primary returns 200. This is what makes HAProxy "follow" the primary after failover
on-marked-down shutdown-sessions — when a server is marked down, HAProxy sends TCP RST to all connected clients, forcing immediate reconnection to the new primary. Without this, idle connections hang indefinitely
fall 3 rise 2 — mark down after 3 consecutive failures, mark up after 2 consecutive successes. Combined with inter 3s, failure detection takes 9 seconds
Port 5000 for primary (not 5432) because PostgreSQL itself publishes on host port 5432 directly

Why /primary not /leader: The /leader endpoint returns 200 as soon as a node holds the DCS lock, even if PostgreSQL is still performing crash recovery. /primary returns 200 only when the node holds the lock AND PostgreSQL is accepting read-write connections.

4. Docker Stack

docker-compose.yml

networks:
  patroni:
    driver: overlay
    attachable: true

configs:
  patroni_tpl:
    file: ./config/patroni.yml.tpl
  haproxy_cfg:
    file: ./config/haproxy.cfg

secrets:
  pg_replication_pw:
    external: true
  pg_superuser_pw:
    external: true

x-patroni: &patroni-base
  image: <REGISTRY>/patroni:16
  configs:
    - source: patroni_tpl
      target: /etc/patroni/patroni.yml.tpl
      uid: "999"
      gid: "999"
      mode: 0400
  secrets:
    - source: pg_replication_pw
      uid: "999"
      gid: "999"
      mode: 0400
    - source: pg_superuser_pw
      uid: "999"
      gid: "999"
      mode: 0400
  tmpfs:
    - /tmp:size=64M
    - /run:size=16M
    - /var/run/postgresql:size=8M
  deploy: &patroni-deploy
    endpoint_mode: dnsrr
    resources:
      limits:
        memory: 2G
      reservations:
        memory: 512M
    restart_policy:
      condition: on-failure
      delay: 10s
      max_attempts: 5
      window: 60s

services:

  patroni-1:
    <<: *patroni-base
    hostname: patroni-1
    networks:
      patroni:
        aliases:
          - patroni-1
    ports:
      - target: 5432
        published: 5432
        mode: host
      - target: 8008
        published: 8008
        mode: host
      - target: 5010
        published: 5010
        mode: host
    environment:
      PATRONI_NAME: patroni-1
      RAFT_SELF: "<DB_NODE_1_IP>:5010"
      RAFT_PARTNERS: '["<DB_NODE_2_IP>:5010","<DB_NODE_3_IP>:5010"]'
      PATRONI_CONNECT: "<DB_NODE_1_IP>"
      FAILOVER_PRIORITY: "3"
    volumes:
      - patroni-1-data:/home/postgres/pgdata
      - patroni-1-raft:/home/postgres/raft
    deploy:
      <<: *patroni-deploy
      placement:
        constraints:
          - node.labels.pg-node == 1

  patroni-2:
    <<: *patroni-base
    hostname: patroni-2
    networks:
      patroni:
        aliases:
          - patroni-2
    ports:
      - target: 5432
        published: 5432
        mode: host
      - target: 8008
        published: 8008
        mode: host
      - target: 5010
        published: 5010
        mode: host
    environment:
      PATRONI_NAME: patroni-2
      RAFT_SELF: "<DB_NODE_2_IP>:5010"
      RAFT_PARTNERS: '["<DB_NODE_1_IP>:5010","<DB_NODE_3_IP>:5010"]'
      PATRONI_CONNECT: "<DB_NODE_2_IP>"
      FAILOVER_PRIORITY: "1"
    volumes:
      - patroni-2-data:/home/postgres/pgdata
      - patroni-2-raft:/home/postgres/raft
    deploy:
      <<: *patroni-deploy
      placement:
        constraints:
          - node.labels.pg-node == 2

  patroni-3:
    <<: *patroni-base
    hostname: patroni-3
    networks:
      patroni:
        aliases:
          - patroni-3
    ports:
      - target: 5432
        published: 5432
        mode: host
      - target: 8008
        published: 8008
        mode: host
      - target: 5010
        published: 5010
        mode: host
    environment:
      PATRONI_NAME: patroni-3
      RAFT_SELF: "<DB_NODE_3_IP>:5010"
      RAFT_PARTNERS: '["<DB_NODE_1_IP>:5010","<DB_NODE_2_IP>:5010"]'
      PATRONI_CONNECT: "<DB_NODE_3_IP>"
      FAILOVER_PRIORITY: "1"
    volumes:
      - patroni-3-data:/home/postgres/pgdata
      - patroni-3-raft:/home/postgres/raft
    deploy:
      <<: *patroni-deploy
      placement:
        constraints:
          - node.labels.pg-node == 3

  haproxy:
    image: haproxy:3.0-alpine
    read_only: true
    configs:
      - source: haproxy_cfg
        target: /usr/local/etc/haproxy/haproxy.cfg
        mode: 0444
    ports:
      - target: 5000
        published: 5000
        protocol: tcp
        mode: host
      - target: 5001
        published: 5001
        protocol: tcp
        mode: host
      - target: 7000
        published: 7000
        protocol: tcp
        mode: host
    networks:
      patroni:
        aliases:
          - haproxy
    tmpfs:
      - /tmp:size=16M
      - /run:size=8M
    deploy:
      mode: global
      placement:
        constraints:
          - node.labels.pg-ha-node = true
      resources:
        limits:
          memory: 64M
        reservations:
          memory: 16M
      restart_policy:
        condition: on-failure
        delay: 5s

volumes:
  patroni-1-data:
  patroni-1-raft:
  patroni-2-data:
  patroni-2-raft:
  patroni-3-data:
  patroni-3-raft:

Design Decisions in the Stack

endpoint_mode: dnsrr — Docker Swarm's default VIP mode routes through the kernel's IPVS module, which silently drops idle TCP connections after ~900 seconds. Database connections are long-lived. DNSRR returns container IPs directly, bypassing IPVS entirely.

Host mode port publishing — overlay DNS resolution was unreliable (Tailscale DNS conflict). All inter-node communication uses host IPs and host-published ports. This bypasses the overlay for data plane traffic while still using it for service discovery where it works.

HAProxy mode: global — runs one instance on every node labelled pg-node. Any database node IP can serve as an entry point. Combined with keepalived VIP, this eliminates HAProxy as a single point of failure.

YAML anchors (x-patroni, &patroni-base) — x- prefixed keys are ignored by Docker. YAML anchors (&name) and merge keys (<<: *name) allow reusing common configuration across all three Patroni services without duplication.

Secrets with UID/GID 999 — the Postgres user inside the official Postgres image has UID/GID 999. Secrets are mounted with these ownership settings so the non-root container user can read them.

5. Deployment Procedure

Join Swarm

# Get join token from existing manager
docker swarm join-token worker

# On each new DB node
docker swarm join --token SWMTKN-xxx <MANAGER_IP>:2377

Label Nodes

docker node update --label-add pg-node=1 db-node-1   # TrueNAS
docker node update --label-add pg-node=2 db-node-2   # Proxmox
docker node update --label-add pg-node=3 db-node-3   # Proxmox

Create Secrets

printf '%s' "$(openssl rand -base64 32)" | docker secret create pg_replication_pw -
printf '%s' "$(openssl rand -base64 32)" | docker secret create pg_superuser_pw -

Store the generated passwords in a password manager.

Build and Push Image

cd patroni-cluster/image
docker build -t <REGISTRY>/patroni:16 .
docker push <REGISTRY>/patroni:16

Deploy

cd patroni-cluster
docker stack deploy -c docker-compose.yml pg

Verify

# Watch bootstrap
docker service logs -f pg_patroni-1

# Check cluster status
docker exec $(docker ps --filter name=pg_patroni-1.1 -q) \
    patronictl -c /tmp/patroni.yml list

Expected output:

+ Cluster: homelab ------+---------+---------+----+-----------+
| Member    | Host        | Role    | State   | TL | Lag in MB |
+-----------+-------------+---------+---------+----+-----------+
| patroni-1 | <NODE_1_IP> | Leader  | running |  1 |           |
| patroni-2 | <NODE_2_IP> | Replica | running |  1 |         0 |
| patroni-3 | <NODE_3_IP> | Replica | running |  1 |         0 |
+-----------+-------------+---------+---------+----+-----------+

Create Application Databases

Use the helper script:

./create-db.sh <dbname> <username> '<password>'

The script:

Verifies patroni-1 is the primary
Creates the user (or updates password if exists)
Creates the database (skips if exists)
Revokes public access
Uses docker cp to move SQL into the container, avoiding shell escaping issues with special characters in passwords

Connect Services

# In application compose files
services:
  myapp:
    environment:
      DB_HOST: "<VIP_ADDRESS>"
      DB_PORT: "5000"
      DB_NAME: "myapp"
      DB_USER: "myapp"

6. Operations Runbook

Check Cluster Status

docker exec $(docker ps --filter name=pg_patroni-1.1 -q) \
    patronictl -c /tmp/patroni.yml list

Switchover (planned, zero downtime)

docker exec $(docker ps --filter name=pg_patroni-1.1 -q) \
    patronictl -c /tmp/patroni.yml switchover \
    --candidate patroni-1 --force

Power-Off Procedure

# 1. Ensure primary is on TrueNAS
patronictl switchover --candidate patroni-1 --force

# 2. Pause Patroni — CRITICAL
patronictl pause
# Without this, Patroni WILL demote the primary when quorum is lost

# 3. Shut down Proxmox nodes
# PostgreSQL stays writable on 1 chosen node

# 4. When nodes return
patronictl resume
# Replicas resync automatically

Emergency Failover

patronictl failover --candidate patroni-1 --force

Edit Cluster Configuration

patronictl edit-config

View History

patronictl history

7. Troubleshooting

Problem: Overlay DNS Not Resolving Container Names

Symptom: failed to resolve host patroni-2 in Patroni logs

Cause: Docker Swarm overlay DNS conflicts with host DNS resolver (e.g., Tailscale, systemd-resolved). The hostname: directive only sets the container's internal hostname — it does not register DNS entries on the overlay network.

Resolution: Bypass overlay DNS. Use host IPs for all Patroni communication (RAFT_SELF, RAFT_PARTNERS, PATRONI_CONNECT) and publish ports with mode: host. HAProxy also uses host IPs in its backend server definitions. Network aliases are defined but not relied upon.

Problem: Data Directory Permission Denied

Symptom: FATAL: data directory "/home/postgres/pgdata" has invalid permissions

Cause: Docker volumes created with permissions broader than 0700. PostgreSQL enforces strict data directory permissions.

Resolution: chmod 700 on the data and raft directories in the entrypoint, before Patroni starts.

Problem: Replication Rejected by pg_hba

Symptom: FATAL: no pg_hba.conf entry for replication connection from host "10.0.4.x"

Cause: Docker's internal ingress network NATs replication connections through unexpected IP ranges not covered by pg_hba.conf.

Resolution: Add 10.0.0.0/8 and 172.16.0.0/12 to pg_hba rules for both replication and client connections.

Note: pg_hba is set during bootstrap. To change it after the cluster exists, edit it live on the primary and reload:

docker exec <container> sh -c "echo 'host replication replicator 10.0.0.0/8 scram-sha-256' >> /home/postgres/pgdata/pg_hba.conf"
docker exec <container> psql -U postgres -c "SELECT pg_reload_conf();"

Problem: Missing psycopg Driver

Symptom: FATAL: Patroni requires psycopg2>=2.5.4, psycopg2-binary, or psycopg>=3.0.0

Cause: The base Postgres Docker image includes the C libpq library but not the Python PostgreSQL adapter that Patroni needs.

Resolution: Add psycopg[binary] to the pip install in the Dockerfile.

Problem: Containers OOM Killed (Exit Code 137)

Symptom: Containers repeatedly exit with code 137, especially during bootstrap.

Cause: Memory limit too low. During initial pg_basebackup, Patroni + Python + PostgreSQL + the base backup process run simultaneously.

Resolution: Set memory limit to at least 2GB. Monitor steady-state usage with docker stats after bootstrap and adjust downward if desired.

Problem: Patroni Stuck on "failed to acquire initialize lock"

Symptom: All nodes loop on Lock owner: None; I am patroni-X and failed to acquire initialize lock

Cause: Raft cannot form quorum because fewer than 2 of 3 nodes can communicate on port 5010.

Resolution: Verify all three containers are running simultaneously. Check firewall rules allow TCP 5010 between all node IPs. Check that RAFT_SELF and RAFT_PARTNERS use reachable addresses.

Problem: HAProxy REST API Connection Reset Warnings

Symptom: ConnectionResetError: [Errno 104] Connection reset by peer in Patroni logs every 3 seconds.

Cause: HAProxy health checks close the connection after receiving the response. On non-primary nodes that return 503, HAProxy disconnects before Patroni finishes writing. This is cosmetic noise.

Resolution: Ignore. The cluster is healthy. Optionally add log_level: WARNING under the restapi section to reduce noise (though these are already at WARNING level).

Problem: Shell Escaping Issues with create-db Script

Symptom: User/database not created, or password doesn't work. Special characters like !, @, $ in passwords cause failures.

Cause: Passwords pass through multiple shell interpretation layers: local bash → docker exec → container shell → psql. Special characters get mangled.

Resolution: The create-db script writes SQL to a local temp file, copies it into the container with docker cp, then executes it with psql -f. The SQL never passes through shell argument parsing. A chmod 644 (run as root with -u 0) is required after the copy since docker cp sets root ownership.

8. Design Decisions and Rationale

Why Raft Instead of etcd

Raft is embedded in Patroni — no external infrastructure needed. etcd requires a minimum 3-node cluster with its own maintenance, monitoring, backups, and certificate management. For a homelab with UPS-protected nodes, the theoretical risk of Raft's weaker fsync guarantees is acceptable. PostgreSQL's own WAL is properly fsynced regardless — the worst case is Patroni needing manual intervention to re-establish the leader after a total power loss, not data loss.

Why Host IPs Instead of Overlay DNS

Docker Swarm overlay DNS failed to resolve service names across nodes due to conflict with Tailscale's DNS resolver on the host. After debugging confirmed that only the local container's hostname resolved (via the hostname: directive) but no other containers were discoverable, the decision was made to use host IPs for all inter-node communication. Since each Patroni instance is pinned to a specific node anyway, there's no disadvantage.

Why HAProxy Instead of Traefik

Traefik's TCP service health checks can only verify that a port is open. PostgreSQL listens on port 5432 on all nodes — primary and replicas. Traefik cannot query an HTTP endpoint on a different port (8008) and use the result to make TCP routing decisions on another port (5432). HAProxy can — this is its core strength for database load balancing.

Why One PostgreSQL Instance Per Engine Type

Running one shared PostgreSQL instance with multiple databases (one per application) rather than one instance per application. Benefits: lower memory overhead (~50MB baseline per instance avoided), single backup target, single upgrade path. Isolation is logical (separate databases and users with revoked public access) rather than physical, which is appropriate for a homelab.

Why `endpoint_mode: dnsrr`

Docker Swarm's default VIP mode uses IPVS for load balancing, which imposes hardcoded idle timeouts (~900 seconds) on tracked connections. Database connections are long-lived and often idle. When IPVS drops the tracking state, both client and server are unaware — the next query gets Connection reset by peer. DNSRR bypasses IPVS entirely by returning container IPs directly.

Why Port 5000 for HAProxy, Not 5432

PostgreSQL itself publishes on host port 5432 (required for inter-node replication using host IPs). HAProxy uses port 5000 to avoid conflicts. Applications connect to port 5000 and are transparently routed to port 5432 on the current primary.

9. Production Gaps

Things deliberately skipped for the homelab that would be required in a production environment.

TLS Encryption

What's missing: All connections are unencrypted — client-to-HAProxy, HAProxy-to-PostgreSQL, replication between nodes, Patroni REST API, Raft consensus.

Production requirement: TLS everywhere. PostgreSQL ssl = on with certificates. HAProxy TLS termination or passthrough. Patroni REST API with HTTPS. Raft communication encrypted.

etcd Instead of Raft

What's missing: Raft's pysyncobj implementation doesn't guarantee fsync before acknowledging writes. In a simultaneous total power loss, consensus state could be lost.

Production requirement: A 3 or 5 node etcd cluster with its own monitoring, backup, and maintenance procedures. etcd provides mathematically guaranteed synchronous persistence.

WAL Archiving and Off-Site Backups

What's missing: No continuous WAL archiving. No off-site backups. Recovery depends on ZFS snapshots on local disks.

Production requirement: WAL-G or pgBackRest archiving to S3-compatible object storage. Enables Point-in-Time Recovery (PITR) to any second. Regular backup verification by restoring to a test instance. Off-site replication of backups (different datacenter or cloud).

Connection Pooling

What's missing: Each application opens direct connections to PostgreSQL.

Production requirement: PgBouncer in front of PostgreSQL to pool connections. A single PgBouncer instance can serve hundreds of application connections with a much smaller number of actual PostgreSQL backends, reducing memory usage and connection overhead.

Monitoring and Alerting

What's missing: HAProxy stats page and manual patronictl list checks only.

Production requirement: Prometheus exporters for PostgreSQL (postgres_exporter) and Patroni. Grafana dashboards. Alerting on: replication lag exceeding threshold, failover events, connection count approaching max_connections, disk space, OOM kills, backup age, WAL archiving delay.

Automated Backup Verification

What's missing: Backups (ZFS snapshots) are never tested.

Production requirement: Scheduled job that restores the latest backup to a temporary instance, runs consistency checks (pg_checksums --check), verifies key tables have expected row counts, then tears down the temporary instance. Alert if verification fails.

Synchronous Replication

What's missing: Replication is asynchronous. On failover, transactions committed on the primary but not yet received by replicas are lost (typically under 1 second of data).

Production requirement: synchronous_commit = on with synchronous_standby_names configured. The primary waits for at least one replica to confirm receipt before acknowledging a commit to the client. Guarantees zero data loss (RPO = 0) at the cost of higher write latency.

Read/Write Splitting

What's missing: All application traffic goes to the primary via port 5000. Read replicas on port 5001 are available but not used by applications.

Production requirement: Applications or a middleware layer direct read-heavy queries to replicas, reducing load on the primary. Some frameworks support this natively (e.g., Django database routers).

Watchdog / Fencing

What's missing: Patroni logs Could not activate Linux watchdog device. Without a watchdog, if Patroni hangs but doesn't crash, it can't demote itself.

Production requirement: Linux software watchdog (/dev/watchdog) enabled and accessible to the container. If Patroni fails to pat the watchdog within the TTL, the kernel reboots the machine, guaranteeing the old primary is fenced.

Audit Logging

What's missing: Only DDL statements are logged (log_statement: ddl).

Production requirement: pgAudit extension for comprehensive audit logging of data access. Required for compliance frameworks (SOC2, HIPAA, PCI-DSS).

Secrets Management

What's missing: Docker Swarm secrets (plaintext files on the manager nodes). Password in the create-db script passed as a CLI argument (visible in process listings).

Production requirement: Vault or similar secrets manager. Database credentials rotated on a schedule. No passwords in environment variables or CLI arguments.

Automated Failback

What's missing: After an unplanned failover, a manual switchover is needed to return the primary to the preferred node if you want.

Production requirement: Scheduled job or Patroni hook that detects when the preferred node is healthy and at zero lag, then triggers an automatic switchover during a maintenance window.

Network Segmentation

What's missing: Database nodes are on the same flat LAN as all other services.

Production requirement: Database nodes on a dedicated VLAN. Firewall rules restricting which application servers can reach the database ports. No direct internet access from database nodes.