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.

Context
Self-managed PostgreSQL, a DataArt project, 2022–2023
My part
I designed and built the cluster alone, from architecture to provisioning, backups, access controls and monitoring.
Team
Solo
Status
Delivered to production

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

  1. Patroni manages replication and promotes a replica when the primary fails.
  2. HAProxy routes connections to the current primary, using Patroni’s health endpoint.
  3. Keepalived holds a virtual IP across the HAProxy nodes, so the entry point survives the loss of a proxy.
  4. Ansible builds every node from the same roles.
  5. Prometheus scrapes the database nodes, and Alertmanager alerts on replication lag and failover events.
  1. Clients
    • Applications one connection address
  2. Entry
    • Keepalived floating IP across proxy nodes
  3. Proxy
    • HAProxy, node A health checks against Patroni
    • HAProxy, node B takes over the IP
  4. Database
    • 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.
Clients use one address. Keepalived moves it between proxy nodes, and HAProxy sends connections to the node Patroni reports as primary.Abstracted view. Names and counts are illustrative, not the real topology.

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.