Case 04 Shipped
A highly available PostgreSQL platform
Patroni for failover, HAProxy and Keepalived for one stable address, Ansible for repeatable nodes, and alerts on replication lag and failover events.
Stack: PostgreSQL · Patroni · HAProxy · Keepalived · Ansible · Prometheus · Alertmanager
On this page
Context
A client’s production system could not use a managed database service. It needed self-managed PostgreSQL that survives the loss of a node, and the job had a short deadline. I designed the cluster and built it with Ansible.
Problem
A single primary is a single point of failure. When it dies, someone has to promote a replica and point every client at it, and the system is down until they do.
Constraints
- A managed database service was not an option.
- The deadline was short.
- Clients need one stable address that follows the primary.
- Failover happens without a human.
- Rebuilding a node must be repeatable.
- Backups and access controls are part of the platform, not an afterthought.
Options
A managed service was out from the start. Among the common open-source choices (Patroni, repmgr, and Pacemaker with Corosync), I chose Patroni because it hands leader election to a consensus store and exposes each node’s role over a REST API that HAProxy can health-check.
Engineering decisions
- Patroni manages replication and promotes a replica when the primary fails.
- HAProxy routes connections to the current primary, using Patroni’s health endpoint.
- Keepalived holds a virtual IP across the HAProxy nodes, so the entry point survives the loss of a proxy.
- Ansible builds every node from the same roles.
- Prometheus scrapes the database nodes, and Alertmanager alerts on replication lag and failover events.
- Clients
- Applications one connection address
- Connected through virtual IPEntry
- Keepalived floating IP across proxy nodes
- Proxy
- HAProxy, node A health checks against Patroni
- HAProxy, node B takes over the IP
- Connected through to the current primaryDatabase
- Primary PostgreSQL with Patroni
- Replica PostgreSQL with Patroni
- Replica PostgreSQL with Patroni
- Failover Patroni keeps leader state in a consensus store and promotes a replica when the primary fails.
- Alerts Prometheus scrapes the nodes. Alertmanager alerts on replication lag and on failover events.
- Build Ansible builds every node from the same roles, with backups and access controls.
Implementation
Ansible roles install and configure PostgreSQL, Patroni, HAProxy and Keepalived, so every node starts from the same definition. Backups and access controls ship with the platform. Alerts cover the two signals that matter most for a replicated database: how far replicas lag, and when leadership changes.
Validation
Each part of the cluster reports its own state: Patroni names the leader, HAProxy marks which node passes its health check, and Alertmanager reports replication lag and every change of leader.
Outcome
- ShippedAutomated failover for PostgreSQL behind one stable address.
- ShippedAlerts on replication lag and on failover events.
- ShippedRepeatable node builds with Ansible, with backups and access controls in place.