An Eight Mile project · open source

PostgreSQL primary routing.

An Ansible role that deploys Spilo/PostgreSQL members, an etcd quorum and a global HAProxy service on Docker Swarm. HAProxy probes Patroni on port 8008 and routes traffic from haproxy:5000 only to the member returning 200 for /primary.

View on GitHub View configuration

Frontend

haproxy:5000

Health

Patroni /primary

Runtime

HAProxy 3.0

Published ports

none

Primary route

live topology

Primary selection

Patroni’s REST state controls the HAProxy backend.

HAProxy performs an HTTP check against port 8008 on every PostgreSQL member. The /primary endpoint returns 200 only on the current Patroni leader, so the TCP frontend on port 5000 has exactly one writable backend in the UP state.

Schematic — the primary shown is illustrative; Patroni can promote any eligible member.

Patroni health-check interval

fall 3

Failed checks before marked down

rise 2

Healthy checks before restored

30m

Client and server TCP timeout

Component architecture

Control plane, database members and TCP routing.

etcd stores the distributed leader state, Patroni manages PostgreSQL promotion and replication, HAProxy converts Patroni state into backend availability, and Swarm places each stateful service on the host containing its bind mounts.

HAProxy

data plane

TCP frontend with Patroni-aware backend selection.

TCP mode on :5000

HTTP health checks on :8008

Global Swarm service

Spilo + Patroni

database

PostgreSQL 17 managed by Patroni inside Spilo.

Leader election and promotion

Replication slots + pg_rewind

Asynchronous replication

etcd

consensus

Distributed configuration store used for leader election.

One member per declared node

Leader key and cluster state

Majority quorum required

Docker Swarm

placement

Service placement and private overlay networking.

DNSRR service discovery

Host-pinned stateful members

Immutable Swarm configs

Configuration reference

HAProxy, Patroni and Swarm configuration.

These excerpts define the primary health check, backend transition thresholds, global service placement, Patroni failover parameters and checksum-versioned Swarm config.

ansible-role-postgres

cfg · jinja2

TCP frontend with HTTP health checks against Patroni /primary.

listen postgres
    bind *:{{ postgres_haproxy_port }}
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

{% for node in postgres_replica_members %}
    server postgres{{ loop.index }} postgres{{ loop.index }}:{{ postgres_port }} check port 8008
{% endfor %}

Failure handling

Quorum loss removes write authority.

Primary failure triggers Patroni promotion and HAProxy backend convergence. If etcd loses majority quorum, Patroni demotes the database to read-only rather than allowing multiple members to accept writes.

Event

Cluster response

Current PostgreSQL primary fails

Patroni promotes an eligible standby; HAProxy follows its /primary response.

One etcd member is lost

The surviving quorum keeps serving. The lost member must be re-added manually.

Two of three etcd members are lost

Quorum is gone; Patroni demotes the database to read-only until etcd returns.

Network exposure

PostgreSQL, etcd, the SQL frontend and stats page publish no host ports. Applications join db-network and use haproxy:5000.

Primary-routed logical backups

A manager-only pg_dumpall runs through HAProxy, captures databases and roles, compresses the dump, and prunes local copies after seven days by default.

etcd member recovery

A lost etcd member cannot rejoin with initial state set to new. Remove it, add it back, then start only that member with state existing.

on-marked-down shutdown-sessions drops sessions to the failed primary instead of letting them linger.

Deployment and inspection

Required inputs and runtime state.

Pre-deployment assertions reject missing or placeholder database credentials. After deployment, patronictl reports member roles and replication state, while the overlay-only HAProxy stats endpoint reports backend health.

Required deployment inputs

postgres_replica_members

Hostnames and per-member memory budgets.

postgres_password

Required superuser secret, supplied through Vault.

postgres_standby_password

Required replication secret in HA mode.

db-network

External overlay created before this role deploys.

Inspect the live route

docker exec $(docker ps -qf name=postgres_postgres1) patronictl list

docker run --rm --network db-network curlimages/curl -s http://haproxy:7000/

The stats page remains overlay-only. Tunnel it for browser access instead of publishing port 7000.

Clone the repository Open an issue

Behind the project

Built by Eight Mile in London, as part of our infrastructure and cloud work — the same engineers who build and run systems like it for clients.

Read the case study

What our infrastructure and cloud work covers