Software Development 12 min read

PostgreSQL High Availability with Patroni, Etcd, and HAProxy

Building a PostgreSQL High Availability solution with Patroni, Etcd, and HAProxy to eliminate single points of failure and enable automatic failover.

PostgreSQL High Availability with Patroni, Etcd, and HAProxy

Does your application currently use only a single database server? What happens if your database server suddenly goes down during peak traffic? Your application will face total downtime until the database server is restored. In this scenario, the database becomes a Single Point of Failure.

The most common solution is creating a database replica (Master-Replica), a built-in PostgreSQL feature. However, standard PostgreSQL replication lacks auto-failover, a critical aspect of High Availability (HA) architecture. When the master database dies, we must manually promote a replica to master, update database IPs in our application, and restart the application.

To build a truly High Availability (HA) and self-healing database system, we need three additional components: Patroni, Etcd, and HAProxy.

Patroni, Etcd, and HAProxy

Before diving deeper, let’s get to know these three essential components.

Patroni

Patroni is an open-source tool that acts as a process supervisor to automatically manage the PostgreSQL lifecycle. Patroni runs alongside PostgreSQL on every server node. Its primary functions include:

  • Managing initialization, configuration, and database replication.
  • Performing periodic database health checks.
  • Coordinating with a Distributed Configuration Store (DCS) to elect which node acts as master.
  • Providing a REST API to monitor node status (200 for primary, 503 for standby).

Etcd

Etcd is a distributed key-value store acting as a Distributed Configuration Store (DCS). It uses the Raft consensus algorithm, which requires a majority (Quorum) to operate.

  • Stores cluster state and metadata (identifying the current Primary and registered Replicas).
  • Manages leader locks with a Time-To-Live (TTL) system. The Primary node must regularly renew its lease. If the Primary dies, its key expires, and Patroni on other nodes triggers a leader election.
  • Prevents split-brain scenarios (where two nodes believe they are the Primary simultaneously).

We need at least 3 Etcd nodes because the Raft algorithm requires a majority (Q = [N/2] + 1). In a 2-node cluster, if 1 node dies, the remaining node (50%) does not meet the majority, locking the cluster. With 3 nodes, losing 1 node leaves 2 (66.7%), allowing safe consensus.

HAProxy

HAProxy acts as a high-performance Load Balancer and TCP Proxy sitting in front of the database cluster.

  • Provides a single endpoint for the application, so your code doesn’t need to track individual PostgreSQL server IPs.
  • Acts as the single source of truth for both reads and writes: all traffic enters via Port 5432, and HAProxy forwards it to the current Primary node.
  • Checks the Patroni REST API (port 8008). When the Primary dies and a Replica node is promoted, HAProxy automatically detects the new HTTP 200 OK response and redirects traffic in seconds.

Multi-Server Architecture

To build a truly High Availability cluster and avoid a Single Point of Failure, we will use a 3-database node and 1-gateway topology.

graph TD
    subgraph "App Server (Gateway)"
        App[Aplikasi]
        HAP[HAProxy]
    end

    subgraph "Server 1 (Node 1)"
        PG1[PostgreSQL Primary]
        Patroni1[Patroni]
        Etcd1[Etcd Node 1]
    end

    subgraph "Server 2 (Node 2)"
        PG2[PostgreSQL Replica]
        Patroni2[Patroni]
        Etcd2[Etcd Node 2]
    end

    subgraph "Server 3 (Node 3 / Witness)"
        PG3[PostgreSQL Replica / Witness]
        Patroni3[Patroni]
        Etcd3[Etcd Node 3]
    end

    %% App Traffic
    App -- "Read & Write 5432" --> HAP
    HAP -- "Health Check 8008" --> Patroni1
    HAP -- "Health Check 8008" --> Patroni2
    HAP -- "Health Check 8008" --> Patroni3

    %% DCS Cluster Consensus
    Etcd1 <--> Etcd2
    Etcd2 <--> Etcd3
    Etcd3 <--> Etcd1

    %% Patroni Monitoring DCS
    Patroni1 -. "Leader Key / Heartbeat" .-> Etcd1
    Patroni2 -. "Leader Key / Heartbeat" .-> Etcd2
    Patroni3 -. "Leader Key / Heartbeat" .-> Etcd3

    %% Native PG Streaming Replication
    PG1 == "Streaming Replication" ==> PG2
    PG1 == "Streaming Replication" ==> PG3

Role Distribution

  1. App Server (Gateway)
    • Contains the main application and HAProxy.
    • HAProxy acts as the gateway distributing database connections from the application to the correct server.
  2. Server 1 & 2 (Database Nodes 1 & 2)
    • Each runs PostgreSQL, Patroni, and Etcd.
    • By default, one is elected Primary (Master) for writes, while others act as Replicas (Standby) receiving automatic data replication.
  3. Server 3 (Database Node 3 / Quorum Member)
    • A full node running PostgreSQL, Patroni, and Etcd.
    • Special Role: While capable of becoming a Replica or Primary, its vital function is acting as the tie-breaker (quorum member).
    • Etcd needs an odd number of nodes for Quorum ($N/2 + 1$). Server 3 ensures the cluster has 3 Etcd nodes, so if one database server dies, the remaining 2 form a majority to elect a Master automatically.

Hands-on

To simulate a multi-server environment, we use 4 separate Docker compose files connected via a single Docker network.

1docker network create pg-ha-net

Database Node

Node server 1

 1services:
 2  etcd1:
 3    image: quay.io/coreos/etcd:v3.5.11
 4    container_name: etcd1
 5    environment:
 6      - ETCD_NAME=etcd1
 7      - ETCD_INITIAL_ADVERTISE_PEER_URLS=http://etcd1:2380
 8      - ETCD_LISTEN_PEER_URLS=http://0.0.0.0:2380
 9      - ETCD_LISTEN_CLIENT_URLS=http://0.0.0.0:2379
10      - ETCD_ADVERTISE_CLIENT_URLS=http://etcd1:2379
11      - ETCD_INITIAL_CLUSTER=etcd1=http://etcd1:2380,etcd2=http://etcd2:2380,etcd3=http://etcd3:2380
12      - ETCD_INITIAL_CLUSTER_STATE=new
13      - ETCD_INITIAL_CLUSTER_TOKEN=etcd-pg-cluster
14    networks:
15      - pg-ha-net
16
17  pg-node1:
18    image: ongres/patroni:latest
19    container_name: pg-node1
20    command: ["patroni", "/tmp/patroni.yml"]
21    volumes:
22      - ../patroni-bootstrap.yml:/tmp/patroni.yml:ro
23    environment:
24      - PATRONI_NAME=node1
25      - PATRONI_RESTAPI_LISTEN=0.0.0.0:8008
26      - PATRONI_RESTAPI_CONNECT_ADDRESS=pg-node1:8008
27      - PATRONI_ETCD3_HOSTS=etcd1:2379,etcd2:2379,etcd3:2379
28      - PATRONI_SCOPE=pg-cluster
29      - PATRONI_POSTGRESQL_LISTEN=0.0.0.0:5432
30      - PATRONI_POSTGRESQL_CONNECT_ADDRESS=pg-node1:5432
31      - PATRONI_POSTGRESQL_DATA_DIR=/var/lib/postgresql/data
32      - PATRONI_POSTGRESQL_PGPASS=/tmp/pgpass
33      - PATRONI_REPLICATION_USERNAME=replicator
34      - PATRONI_REPLICATION_PASSWORD=rep_password
35      - PATRONI_SUPERUSER_USERNAME=postgres
36      - PATRONI_SUPERUSER_PASSWORD=postgres_password
37    depends_on:
38      - etcd1
39    networks:
40      - pg-ha-net
41
42networks:
43  pg-ha-net:
44    external: true

Node server 2

 1services:
 2  etcd2:
 3    image: quay.io/coreos/etcd:v3.5.11
 4    container_name: etcd2
 5    environment:
 6      - ETCD_NAME=etcd2
 7      - ETCD_INITIAL_ADVERTISE_PEER_URLS=http://etcd2:2380
 8      - ETCD_LISTEN_PEER_URLS=http://0.0.0.0:2380
 9      - ETCD_LISTEN_CLIENT_URLS=http://0.0.0.0:2379
10      - ETCD_ADVERTISE_CLIENT_URLS=http://etcd2:2379
11      - ETCD_INITIAL_CLUSTER=etcd1=http://etcd1:2380,etcd2=http://etcd2:2380,etcd3=http://etcd3:2380
12      - ETCD_INITIAL_CLUSTER_STATE=new
13      - ETCD_INITIAL_CLUSTER_TOKEN=etcd-pg-cluster
14    networks:
15      - pg-ha-net
16
17  pg-node2:
18    image: ongres/patroni:latest
19    container_name: pg-node2
20    command: ["patroni", "/tmp/patroni.yml"]
21    volumes:
22      - ../patroni-bootstrap.yml:/tmp/patroni.yml:ro
23    environment:
24      - PATRONI_NAME=node2
25      - PATRONI_RESTAPI_LISTEN=0.0.0.0:8008
26      - PATRONI_RESTAPI_CONNECT_ADDRESS=pg-node2:8008
27      - PATRONI_ETCD3_HOSTS=etcd1:2379,etcd2:2379,etcd3:2379
28      - PATRONI_SCOPE=pg-cluster
29      - PATRONI_POSTGRESQL_LISTEN=0.0.0.0:5432
30      - PATRONI_POSTGRESQL_CONNECT_ADDRESS=pg-node2:5432
31      - PATRONI_POSTGRESQL_DATA_DIR=/var/lib/postgresql/data
32      - PATRONI_POSTGRESQL_PGPASS=/tmp/pgpass
33      - PATRONI_REPLICATION_USERNAME=replicator
34      - PATRONI_REPLICATION_PASSWORD=rep_password
35      - PATRONI_SUPERUSER_USERNAME=postgres
36      - PATRONI_SUPERUSER_PASSWORD=postgres_password
37    depends_on:
38      - etcd2
39    networks:
40      - pg-ha-net
41
42networks:
43  pg-ha-net:
44    external: true

Node server 3

 1services:
 2  etcd3:
 3    image: quay.io/coreos/etcd:v3.5.11
 4    container_name: etcd3
 5    environment:
 6      - ETCD_NAME=etcd3
 7      - ETCD_INITIAL_ADVERTISE_PEER_URLS=http://etcd3:2380
 8      - ETCD_LISTEN_PEER_URLS=http://0.0.0.0:2380
 9      - ETCD_LISTEN_CLIENT_URLS=http://0.0.0.0:2379
10      - ETCD_ADVERTISE_CLIENT_URLS=http://etcd3:2379
11      - ETCD_INITIAL_CLUSTER=etcd1=http://etcd1:2380,etcd2=http://etcd2:2380,etcd3=http://etcd3:2380
12      - ETCD_INITIAL_CLUSTER_STATE=new
13      - ETCD_INITIAL_CLUSTER_TOKEN=etcd-pg-cluster
14    networks:
15      - pg-ha-net
16
17  pg-node3:
18    image: ongres/patroni:latest
19    container_name: pg-node3
20    command: ["patroni", "/tmp/patroni.yml"]
21    volumes:
22      - ../patroni-bootstrap.yml:/tmp/patroni.yml:ro
23    environment:
24      - PATRONI_NAME=node3
25      - PATRONI_RESTAPI_LISTEN=0.0.0.0:8008
26      - PATRONI_RESTAPI_CONNECT_ADDRESS=pg-node3:8008
27      - PATRONI_ETCD3_HOSTS=etcd1:2379,etcd2:2379,etcd3:2379
28      - PATRONI_SCOPE=pg-cluster
29      - PATRONI_POSTGRESQL_LISTEN=0.0.0.0:5432
30      - PATRONI_POSTGRESQL_CONNECT_ADDRESS=pg-node3:5432
31      - PATRONI_POSTGRESQL_DATA_DIR=/var/lib/postgresql/data
32      - PATRONI_POSTGRESQL_PGPASS=/tmp/pgpass
33      - PATRONI_REPLICATION_USERNAME=replicator
34      - PATRONI_REPLICATION_PASSWORD=rep_password
35      - PATRONI_SUPERUSER_USERNAME=postgres
36      - PATRONI_SUPERUSER_PASSWORD=postgres_password
37    depends_on:
38      - etcd3
39    networks:
40      - pg-ha-net
41
42networks:
43  pg-ha-net:
44    external: true

Look at the variable PATRONI_ETCD3_HOSTS. In the all nodes, this variable is set to the address of all Etcd nodes. Its purpose is to ensure Patroni can coordinate with Etcd even if one of the database servers goes down.

Patroni Bootstrap Configuration

See that the third node runs command: ["patroni", "/tmp/patroni.yml"] and mounts a file patroni-bootstrap.yml with the same content to /tmp/patroni.yml. This file is required because the bootstrap section (options initdb, pg_hba, and DCS timing) can only be read by Patroni from the configuration file, not available as environment variable PATRONI_BOOTSTRAP_*.

 1bootstrap:
 2  dcs:
 3    ttl: 10
 4    loop_wait: 2
 5    retry_timeout: 5
 6    maximum_lag_on_failover: 1048576
 7    postgresql:
 8      use_pg_rewind: true
 9  initdb:
10    - encoding: UTF8
11    - data-checksums
12  pg_hba:
13    - host replication replicator 0.0.0.0/0 md5
14    - host all all 0.0.0.0/0 md5
15
16postgresql:
17  parameters:
18    unix_socket_directories: /tmp
  • ttl determines how long a lease key in Etcd will remain valid before being considered expired, which is the most important factor in failover duration.
  • loop_wait determines how often HA loops on each node, which slows down the process of a replica detecting the empty leader key and taking over.
  • unix_socket_directories is set to /tmp because the ongres/patroni image does not create the default /run/postgresql directory.

HAProxy

Next, we will set up a HAProxy container that reads the Patroni REST API (port 8008).

Configuration File (haproxy.cfg)

global
    log stdout format raw local0

defaults
    mode tcp
    timeout connect 5s
    timeout client 50s
    timeout server 50s

# Front-end & Back-end: single source of truth for read & write (Primary Only)
frontend pg_front
    bind *:5432
    default_backend pg_back

backend pg_back
    mode tcp
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 pg-node1:5432 maxconn 100 check port 8008
    server pg2 pg-node2:5432 maxconn 100 check port 8008
    server pg3 pg-node3:5432 maxconn 100 check port 8008

# Dashboard HAProxy Stats
listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
    stats refresh 5s
    stats auth admin:admin # (Optional) Username & Password for login
  • The HAProxy calls the /primary endpoint of Patroni. A node that is currently holding the primary will return HTTP 200 OK, while a replica will return HTTP 503 Service Unavailable.
  • Because there is only one backend (pg_back), all read or write connections automatically follow the primary node.
  • The HAProxy stats dashboard can be accessed through the browser to see which node is currently up in the pg_back backend. It is the current primary node.

Docker compose

 1services:
 2  haproxy:
 3    image: haproxy:3.3-alpine
 4    container_name: haproxy-gateway
 5    ports:
 6      - "5432:5432" # Read/Write Port (on the node currently holding the primary)
 7      - "7001:7000" # HAProxy Stats Page — (I am using this because host 7000 is used by the AirPlay Receiver on macOS)
 8    volumes:
 9      - ./haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro
10    networks:
11      - pg-ha-net
12
13networks:
14  pg-ha-net:
15    external: true

Application

After setting up the infrastructure, we will create a simple application using Bun to test the High Availability infrastructure we have built.

You can check the repository here for the full source code.

 1import { Pool } from "pg";
 2
 3const db = new Pool({
 4  host: "localhost",
 5  port: 5432,
 6  user: "postgres",
 7  password: "postgres_password",
 8  database: "postgres",
 9  connectionTimeoutMillis: 3000,
10  idleTimeoutMillis: 10000,
11});
12
13const boot = async () => {
14  console.log("Booting application...");
15  try {
16    await db.query(`
17            CREATE TABLE IF NOT EXISTS logs (
18                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
19                message TEXT NOT NULL,
20                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
21            );
22        `);
23
24    console.log("Application booted successfully.");
25  } catch (error) {
26    console.error("Error booting application:", error);
27  }
28};
29
30const cleanup = async () => {
31  console.log("Cleaning up application...");
32  try {
33    await db.query("DROP TABLE IF EXISTS logs");
34    console.log("Application cleaned up successfully.");
35  } catch (error) {
36    console.error("Error cleaning up application:", error);
37  } finally {
38    process.exit(0);
39  }
40};
41
42const insert = async (message: string) => {
43  try {
44    const result = await db.query(
45      "INSERT INTO logs (message) VALUES ($1) RETURNING *;",
46      [message],
47    );
48
49    return result.rows[0];
50  } catch (error: any) {
51    throw new Error(error.message);
52  }
53};
54
55const count = async () => {
56  try {
57    const result = await db.query("SELECT COUNT(id) FROM logs;");
58    return result.rows[0].count;
59  } catch (error: any) {
60    throw new Error(error.message);
61  }
62};
63
64const simulate = async () => {
65  console.log("Simulating data insertion and reading flip-flop...");
66
67  setInterval(async () => {
68    try {
69      const message = `Log at ${new Date().toISOString()}`;
70
71      const insertResult = await insert(message);
72      console.log(
73        `Inserted log with ID ${insertResult.id} and message "${insertResult.message}".`,
74      );
75
76      const countResult = await count();
77      console.log(`Current total logs: ${countResult}.`);
78    } catch (error: any) {
79      console.error(
80        "Error simulating data insertion and reading flip-flop:",
81        error.message,
82      );
83    } finally {
84      console.log(
85        "--------------------------------------------------------------------------------",
86      );
87    }
88  }, 1000);
89};
90
91process.on("SIGINT", cleanup);
92process.on("SIGTERM", cleanup);
93
94await boot();
95await simulate();

Failover Simulation

The infrastructure is ready, and the application is created; let’s simulate a failover.

Running 3 Node DB Servers and 1 Node HAProxy

We use make up-all to run all nodes. Check the project repository for the source code!

Running all nodes

HAProxy Stats Page

We can monitor the nodes via the HAProxy statistics page, specifically focusing on the pg_back section.

HAProxy Stats Page

Nodes green in the pg_back table are active primary databases, while red nodes are active replicas.

Why? It’s due to option httpchk. The pg_back backend calls Patroni’s /primary endpoint. The node currently serving as primary returns HTTP 200 OK, marked green (UP), and receives all traffic. Replica nodes return 503, and HAProxy marks them red (DOWN).

If you stop the primary container (e.g., docker stop pg-node2), after a few seconds, it turns red, and a promoted replica turns green. Application traffic shifts automatically without restarts or configuration changes.

Running the Application

Ensure Bun is installed, then clone the repository: https://github.com/kykurniawan/pg-ha-poc.

1cd application && bun start

Application running

When we stop the primary database (node 2), node 3 is automatically promoted to primary.

Node 2 stopped

Automatically promote node 3 to primary.

Node 3 promoted to primary

The log from node 3, showing the election process.

Node 3 log

During the promotion process, the application might fail to log for a few seconds. This is normal, as promotion takes time; however, it’s better than total connectivity loss.

Error duration can be shortened by reducing ttl and loop_wait in patroni-bootstrap.yml. Beware: smaller values risk false positives (unnecessary failovers caused by minor network hiccups).

Database connection restored

Conclusion

We demonstrated how Patroni automatically promotes a replica when the primary goes down, HAProxy reroutes traffic, and the application recovers automatically without manual intervention.

However, those “few seconds” aren’t free. 3 Etcd nodes for a safe quorum triple infrastructure costs, and aggressive ttl/loop_wait values risk unnecessary failovers. High Availability requires balancing these trade-offs according to application needs.

Note: We simulated this on a single server (Docker). In production, these nodes typically reside on physically separate servers or different locations, communicating via secure private networks.

Extra: Why are Read and Write not separated?

Currently, pg_back is the only HAProxy backend, so all traffic is routed to the Primary. Patroni provides a /replica endpoint (opposite of /primary), which we could use for a separate read backend, but we omitted it here to focus on HA failover.


Originally posted at: https://blog.dot.co.id/articles/postgres-high-availability-menggunakan-patroni-etcd-dan-haproxy-1790129940833

Share X Facebook LinkedIn