DEV Community

Mo Rizal
Mo Rizal

Posted on

Building Highly Available PostgreSQL Cluster With Patroni, ETCD, HAProxy, And Keepalived

A database is one of the most critical components in an application.

When the database is running normally, the application can connect to it and continue serving requests. But when the database server becomes unavailable, the impact can quickly spread to the services that depend on it.

For a system that needs to remain available, relying on a single database server creates an obvious risk.

If that server fails, the application may lose its database connection until the server is recovered or another database server is manually brought into service.

This raises an important question:

What happens when the database server that our application depends on suddenly fails?

A common approach is to run multiple PostgreSQL servers. However, having multiple servers does not automatically make the database highly available.

A highly available database needs to continue serving applications even when part of the infrastructure fails. This means the system needs to handle failures without requiring the application to be manually reconfigured every time something goes wrong.

In this article, we will build a highly available database cluster using PostgreSQL, Patroni, etcd, HAProxy, and Keepalived.

What We Need

Before building the system, we first need to define what we expect from a highly available database.

The goal is not simply to have multiple PostgreSQL servers running at the same time. The system should remain available and usable even when individual components fail.

For this project, we define four main requirements:

Multiple PostgreSQL Nodes

We need multiple PostgreSQL nodes so that the database service has another node available when one of the servers becomes unavailable.

The nodes should maintain the same database state closely enough that one of them can take over when necessary.

This gives us redundancy at the database layer.

Automatic Failover

When the current primary PostgreSQL nodes becomes unavailable, the cluster should be able to detect the failure and recover by promoting an eligible node without requiring to manually reconfigure the entire database cluster.

Stable Connection Endpoint

We do not want application configuration to contain the address of a specific PostgreSQL server and require that address to be changed after every failover.

Instead, the application should connect to a stable endpoint while the infrastructure handles which PostgreSQL node currently receives the connection.

No Single Point of Failure

If database traffic passes through another component, that component can become a new single point of failure.

For example, having three PostgreSQL nodes does not provide end to end availability if all application traffic depends on one proxy server.

The connection layer therefore also needs redundancy so that the failure of a single proxy does not make the database unreachable.

Why Replication Is Not Enough

PostgreSQL provides replication mechanisms that allow data to be replicated from one instance to another.

This gives us an important foundation for high availability.

But having a replicated database does not automatically mean that the application can continue operating.

Consider a simple setup:

Under normal conditions, the application connects to the primary while the replica receives replicated data.

Now imagine that the primary suddenly fails:

The replica may still contain the database data, but it does not automatically solve the entire failure.

We still need a mechanism to determine that the primary is no longer available and decide what should happen next.

A Replica Is Not Automatically the New Primary

Replication gives us another copy of the data, but the cluster still needs to coordinate the transition from the failed primary to another node.

Without an automated mechanism, we need to:

  1. Detect the failure

  2. Determine whether the primary is really unavailable

  3. Promote a suitable replica

  4. Reconfigure clients to use the new primary

  5. Re establish replication for the remaining nodes

The data may still be available, but the database service itself is not automatically recovered.

The Connection Problem

There is another problem after a failover.

Suppose the application connects directly to the primary's address:

After the primary fails and another node becomes primary:

The new primary may be ready to accept writes activity, but the application is still trying to connect to the old address.

This means that database failover and application connectivity are two different problems.

We need the database cluster to recover and provide a consistent way for clients to reach whichever node is currently serving as primary.

From Replication to High Availability

This shows why a highly available PostgreSQL system requires more than PostgreSQL replication alone.

We need additional components to coordinate the database nodes, manage the transition when a primary fails, and provide a reliable connection path for applications.

The architecture therefore needs to solve three different concerns:

Instead of treating the cluster as a collection of PostgreSQL servers, we separate it into two main layers:

  • Database layer, responsible for storing data and managing PostgreSQL availability

  • Connection layer, responsible for providing a stable path from applications to the active PostgreSQL node

The architecture consists of five components:

  • PostgreSQL

  • Patroni

  • etcd

  • HAProxy

  • Keepalived

Each component has a specific responsibility.

PostgreSQL

PostgreSQL is the database engine and the component that stores the application data.

We will run PostgreSQL on three separate virtual machines:

The three PostgreSQL instances form the database cluster.

One node acts as the current primary, while the other nodes act as replicas.

The primary handles write operations, while the replicas maintain copies of the database that can be used during failover.

Patroni

Patroni runs alongside PostgreSQL on each database node and manages the PostgreSQL instances as a high availability cluster.

Its responsibilities include:

  • Monitoring PostgreSQL health

  • Managing the PostgreSQL cluster state

  • Coordinating leader election

  • Promoting a replica when the primary fails

  • Managing PostgreSQL configuration required for the HA setup

etcd

etcd act as distributed system where cluster state can be stored and coordinated between the nodes.

Each database VM runs an etcd member:

The etcd members form a distributed consensus cluster.

Patroni uses etcd to store and coordinate information about the PostgreSQL cluster, including which node currently holds the leader role.

HAProxy

HAProxy monitors the PostgreSQL nodes and routes client connections to the node that is currently eligible to receive database traffic.

This means the application does not need to know which PostgreSQL VM is currently the primary.

In this project we will deploy two HAProxy instances:

Keepalived

Using two HAProxy instances introduces another problem.

If the application connects directly to one HAProxy server and that server fails, the PostgreSQL cluster may still be healthy but the application can no longer reach it.

We therefore need a way to make the HAProxy layer highly available as well.

Keepalived provides this through a Virtual IP (VIP).

Under normal conditions, one HAProxy node owns the VIP and handles client connections.

If that node fails, Keepalived can move the VIP to the other HAProxy node.

The application therefore continues using the same database endpoint.

Putting the Architecture Together

With each component assigned to a specific responsibility, the complete architecture for this project looks like this:

Each layer addresses a different failure scenario.

  1. If a PostgreSQL primary fails, Patroni manages the database failover.

  2. If the active HAProxy node fails, Keepalived moves the VIP to the other proxy.

The code snippets in this article focus on the important parts of the implementation. For the complete configuration you can find the source code in the repository below.

Github Repository: https://github.com/muhammadyulasfipahrizal/postgres-ha.git

Building the PostgreSQL HA Cluster

The database layer consists of three VM

Each VM has its own PostgreSQL instance, its own Patroni process, and one member of the etcd cluster. The three nodes then work together to provide a PostgreSQL cluster with automatic failover.

PostgreSQL Nodes

At any given time, one node is the primary and the other nodes are replicas.

The primary handles write operations, while the replicas continuously receive changes from the primary.

For example, the initial state might look like:

VM1 -> Primary
VM2 -> Replica
VM3 -> Replica
Enter fullscreen mode Exit fullscreen mode

After a failure, the cluster may transition to:

VM1 -> Failed
VM2 -> Primary
VM3 -> Replica
Enter fullscreen mode Exit fullscreen mode

The node roles are therefore part of the cluster's dynamic state rather than something we manually configure once and leave unchanged.

PostgreSQL Replication

The PostgreSQL instances use streaming replication to maintain copies of the database across the nodes.

This gives the cluster multiple copies of the database while allowing the primary to continue serving write traffic.

The replication settings are defined through Patroni's configuration. For example:

postgresql:
  listen: 0.0.0.0:5432
  connect_address: ${SERVER_IP}:5432

  parameters:
    hba_file: /etc/patroni/pg_hba.conf
    unix_socket_directories: '/var/run/postgresql'
Enter fullscreen mode Exit fullscreen mode

The cluster is also configured with parameters required for streaming replication:

postgresql:
  parameters:
    wal_level: replica
    hot_standby: "on"
    wal_keep_size: 256MB
    max_wal_senders: 10
    max_replication_slots: 10
Enter fullscreen mode Exit fullscreen mode

These settings are part of the Patroni configuration rather than being managed independently on each PostgreSQL instance.

etcd Cluster

Alongside PostgreSQL, each database VM runs an etcd member.

The three etcd members form a distributed consensus cluster.

Using three members is important because the cluster can tolerate the loss of one member while still maintaining a quorum.

Patroni uses this cluster to coordinate the state of the PostgreSQL nodes.

The etcd endpoints are configured in patroni.yml:

etcd3:
  hosts:
    - 192.168.122.1:2379
    - 192.168.122.2:2379
    - 192.168.122.3:2379
Enter fullscreen mode Exit fullscreen mode

Patroni also defines the cluster scope and namespace used to store its distributed state:

scope: postgres-ha
namespace: /service/
name: postgres1
Enter fullscreen mode Exit fullscreen mode

The name value is node specific, while the cluster scope and namespace identify the Patroni cluster. On the other nodes, the node name and server address change accordingly.

Patroni on Each Node

Each PostgreSQL node also runs a Patroni instance. Patroni connects the PostgreSQL instances with the distributed cluster state provided by etcd.

Each Patroni instance monitors its local PostgreSQL server and participates in managing the cluster.

An important part of the configuration is the timing used for leader management:

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
Enter fullscreen mode Exit fullscreen mode

These values control how Patroni interacts with the distributed configuration store and how frequently it checks the cluster state.

Cluster Configuration

The repository keeps the database configuration inside the db-server folder.

The Docker Compose configuration mounts the PostgreSQL data directory and the Patroni configuration into the database container:

services:
  db:
    build:
      context: .
    restart: unless-stopped

    ports:
      - "5432:5432"
      - "8008:8008"

    volumes:
      - ./data:/var/lib/postgresql/data
      - ./config/patroni.yml:/etc/patroni/patroni.yml:ro
      - ./config/pg_hba.conf:/etc/patroni/pg_hba.conf:ro
Enter fullscreen mode Exit fullscreen mode

This keeps the database configuration version controlled while allowing the PostgreSQL data itself to remain in its persistent volume.

Starting the Cluster

Once the three database nodes are configured, we can start the services across the VMs. We can verify the resulting cluster state through Patroni before moving to the next layer.

The cluster should expose a state similar to:

The HAProxy Layer

The PostgreSQL cluster can now handle database level failover, but there is still a connection problem.

HAProxy receives PostgreSQL connections on port 5432 and forwards them to the PostgreSQL node that is currently serving as the primary.

This allows the application to use a single database endpoint while the cluster manages which PostgreSQL node is active.

HAProxy in TCP Mode

PostgreSQL uses its own protocol, so HAProxy is configured in TCP mode rather than HTTP mode.

The frontend listens for incoming PostgreSQL connections:

frontend postgres
    bind *:5432

    default_backend postgres_primary
Enter fullscreen mode Exit fullscreen mode

The application therefore only needs to connect to the HAProxy endpoint using the normal PostgreSQL port.

Detecting the Current Primary

We cannot simply configure HAProxy to always send traffic to one server because that server may become unavailable.

Instead, HAProxy uses Patroni API to check the role of each PostgreSQL node.

backend postgres_primary
    mode tcp

    option httpchk GET /primary
    http-check expect status 200

    server postgres1 192.168.122.1:5432 check port 8008
    server postgres2 192.168.122.2:5432 check port 8008
    server postgres3 192.168.122.3:5432 check port 8008
Enter fullscreen mode Exit fullscreen mode

The HTTP request is only used as a health check to ask Patroni whether the node is currently the primary.

Patroni /primary endpoint returns HTTP 200 when the node is the current primary. HAProxy uses that response to determine which backend server should receive write traffic.

The Routing Flow

HAProxy periodically checks the Patroni endpoint on each database node.

Only the node reporting itself as the current primary passes the /primary health check.

The result is that HAProxy can automatically direct new connections to the current primary without requiring configuration change.

Adding Keepalived

Keepalived uses VRRP (Virtual Router Redundancy Protocol) to manage VIP (virtual ip) between the two proxy servers.

For example:

Under normal conditions, HAProxy 1 owns the VIP.

The application therefore connects to:

192.168.122.200:5432
Enter fullscreen mode Exit fullscreen mode

rather than:

192.168.122.10:5432
Enter fullscreen mode Exit fullscreen mode

Choosing the Active Proxy

Keepalived assigns a priority to each node to determine which node should normally own the VIP.

The node with the higher priority becomes the active VRRP node and owns the VIP. If the active node becomes unavailable, the other node can take ownership of the VIP.

This allows the connection endpoint to remain unchanged even though the physical server handling the traffic changes.

Monitoring HAProxy

Simply checking whether the server itself is reachable is not enough.The proxy node could still be running while HAProxy itself has stopped.

In this situation, Keepalived should not continue advertising the VIP from that node.

The project therefore uses a health check script:

#!/bin/bash

if docker ps --format '{{.Names}}' | grep -qx haproxy; then
    exit 0
else
    exit 1
fi
Enter fullscreen mode Exit fullscreen mode

The script checks whether the HAProxy container is running and returns a success or failure status accordingly.

Keepalived then tracks this script:

vrrp_script check_haproxy {
    script "/etc/keepalived/check_haproxy.sh"
    interval 2
    timeout 2
    fall 2
    rise 2
    weight -50
}

vrrp_instance VI_1 {
    state MASTER
    interface ens3

    virtual_router_id 51
    priority 110
    advert_int 1

    virtual_ipaddress {
        192.168.122.200/24
    }

    track_script {
        check_haproxy
    }
}
Enter fullscreen mode Exit fullscreen mode

If the HAProxy check fails repeatedly, the configured negative weight reduces the node's VRRP priority.

This allows the other Keepalived node to become the owner of the VIP.

HAProxy Failure

Suppose the initial state is:

When HAProxy 1 fails, Keepalived detects the HAProxy failure through the tracking script.

The VIP can then move to HAProxy 2:

The application does not need to change its database configuration, It continues connecting to the same address

The physical proxy handling the connection has changed, but the application's endpoint has not.

Testing the Setup

With the complete architecture connected, we can now test whether the system actually behaves as expected.

We will test five scenarios:

  1. Normal cluster state

  2. PostgreSQL primary failure

  3. HAProxy failure

  4. VIP failover

Normal Cluster

Before introducing any failures, we first verify that all components are operating normally.

We can check the Patroni cluster state with:

patronictl -c /etc/patroni/patroni.yml list
Enter fullscreen mode Exit fullscreen mode

We should see one node identified as the Leader and the remaining nodes as replicas, in my setup VM2 is currently the Leader.

We can also verify that HAProxy recognizes the current primary through its statistics page or by checking the backend state.

Finally, we verify that the VIP is currently owned by the active HAProxy node.

ip addr show <network-interface>
Enter fullscreen mode Exit fullscreen mode

The expected result is that the VIP is present on the active proxy.

PostgreSQL Failure

Next, we intentionally stop the current PostgreSQL primary.

For example, if VM2 is currently the primary, we can stop the PostgreSQL container in VM2:

docker stop <postgres-container>
Enter fullscreen mode Exit fullscreen mode

After the failure, VM2 should no longer be available as the primary:

Before:
VM1 -> REPLICA
VM2 -> PRIMARY
VM3 -> REPLICA


After:
VM1 -> PRIMARY
VM3 -> REPLICA
Enter fullscreen mode Exit fullscreen mode

We can then check the Patroni cluster again:

patronictl -c /etc/patroni/patroni.yml list
Enter fullscreen mode Exit fullscreen mode

At this point, the cluster should detect that the previous primary is unavailable and begin the leader transition.

Patroni should promote an eligible replica to become the new primary, here VM1 becomes the new primary.

We can also verify the Patroni REST API directly:

curl -i http://192.168.122.28:8008/primary
Enter fullscreen mode Exit fullscreen mode

A successful primary check should return:

HTTP/1.1 200 OK
Enter fullscreen mode Exit fullscreen mode

This confirms that the database layer has completed the failover.

HAProxy Detects the New Primary

Once Patroni has promoted the new primary, HAProxy should discover the role change through its health checks.

HAProxy Failure

First, we identify which HAProxy node currently owns the VIP.

ip addr show <network-interface>
Enter fullscreen mode Exit fullscreen mode

In my setup VM4 currently owns the VIP:

We then stop HAProxy on VM4:

docker stop haproxy
Enter fullscreen mode Exit fullscreen mode

Because the current Keepalived health check monitors the HAProxy container, the check should fail after the configured number of failed checks.

Keepalived should then reduce the active node's VRRP priority and allow the other proxy to take ownership of the VIP.

VIP Failover

After HAProxy on VM4 fails, the VIP is now on VM5.

The application still uses the same endpoint:

<VIP>:5432
Enter fullscreen mode Exit fullscreen mode

This demonstrates that the proxy layer can fail independently from the PostgreSQL layer.

Conclusion

Building a highly available PostgreSQL environment is not about simply running multiple database servers. It is about designing the system so that individual failures do not automatically become application outages.

In the setup, we combined several components, each responsible for a different part of the availability problem:

  • PostgreSQL provides the database layer and streaming replication

  • Patroni manages cluster membership, leader election, and automatic failover

  • etcd provides the distributed state required by Patroni

  • HAProxy provides a stable database endpoint and routes connections to the current primary

  • Keepalived provides a virtual IP so that the HAProxy layer does not depend on a single server

The important lesson is that high availability is not achieved by adding redundancy alone. Every component needs to have a clear role in detecting failures, making decisions, and redirecting traffic when something goes wrong.

This project is intentionally built as a lab environment, but the concepts can be applied to more complex production deployments.

You can reproduce the entire setup and experiment with the failure scenarios yourself by cloning my GitHub repository below:

https://github.com/muhammadyulasfipahrizal/postgres-ha.git

Top comments (0)