In modern database infrastructures, high availability (HA) is critical for ensuring that systems remain operational during node failures, maintenance, or other disruptions. PostgreSQL, while powerful, lacks built-in tools for easily achieving HA. This is where Patroni comes in as a PostgreSQL HA solution that manages replication and failover. Combined with ETCD as the distributed key-value store and HA Proxy as the load balancer, you can build a resilient, highly available PostgreSQL cluster.

This blog will walk through setting up a highly available PostgreSQL cluster using:

• 3 ETCD nodes (for distributed consensus)
• 2 Patroni nodes (for PostgreSQL replication and failover)
• HAProxy nodes (for load balancing)
• Keepalived (for VIP failover)

What is Patroni?

Patroni is an open-source tool designed to manage PostgreSQL high-availability (HA) clusters. It provides an automated and reliable solution for database failover and replication, ensuring that your PostgreSQL setup remains resilient and available in case of node failures. Patroni helps coordinate leader election, monitor cluster health, and promote standby nodes to the primary role when necessary. Its ease of configuration and integration with existing infrastructure makes it a popular choice for managing high-availability PostgreSQL environments.

What is ETCD?

ETCD is a distributed key-value store that plays a crucial role in achieving high availability (HA) for PostgreSQL clusters. As a reliable coordination service, ETCD is often used to store configuration data and coordinate cluster nodes in systems like Patroni. In a high-availability PostgreSQL setup, ETCD helps manage leader elections, monitor cluster health, and ensure data consistency across multiple database instances.

By providing a consensus-based mechanism, ETCD ensures that only one PostgreSQL node acts as the primary (leader) at any given time, while others function as replicas. In the event of a primary node failure, ETCD facilitates the automatic promotion of a replica to the primary role, minimizing downtime and ensuring continuous availability. Its simplicity, speed, and strong consistency make it a popular choice for managing distributed PostgreSQL clusters.

What is HA PROXY?

HA Proxy is a high-performance, open-source load balancer and proxy server that plays a key role in managing high availability (HA) in PostgreSQL clusters. In a high-availability setup, HAProxy is often used to route client connections to the correct PostgreSQL node, ensuring that read and write requests are directed appropriately.

HA Proxy helps manage traffic between the application and the database cluster by automatically directing connections to the primary node for write operations and to replica nodes for read operations. If the primary PostgreSQL instance fails, HA Proxy, in combination with tools like Patroni, can seamlessly reroute traffic to a newly promoted primary, ensuring minimal downtime.

With its fast and reliable performance, HA Proxy is widely used in PostgreSQL HA setups to balance load, handle failover, and provide a single-entry point for clients, thus improving both availability and scalability.

What is Keepalived?

Keepalived is an open-source routing software that enhances the availability of services by implementing high-availability features for systems like HAProxy, Keepalived enables multiple HAProxy instances to share a virtual IP address, ensuring that clients can always reach the load balancer, even if one instance fails.

In a typical setup, Keepalived monitors the health of the HAProxy nodes. If the primary HAProxy instance becomes unresponsive, Keepalived automatically transfers the virtual IP address to a standby instance, allowing continuous access to the backend services without manual intervention. This seamless failover capability enhances the resilience and uptime of applications, making Keepalived an essential component in high-availability architectures.

Architecture Overview

The architecture includes:

• 3 ETCD nodes for distributed configuration management and failover handling.
• Patroni nodes running PostgreSQL. Patroni ensures that one node acts as the leader while the other is a follower (replica).
• HAProxy nodes that will forward database traffic to the active Patroni leader, providing load balancing and failover.
• Keepalived runs on the HAProxy nodes to manage the VIP. If one HAProxy node fails, Keepalived automatically shifts the VIP to the surviving node.

Step by Step Demonstration for HA Setup:

Step 1: Setting Up a 3-node ETCD Cluster

The ETCD cluster will store the state of the Patroni cluster and help with leader election.

Install ETCD on all 3 ETCD nodes:

Configure ETCD on all three nodes:

On each node, modify the /etc/default/ETCD or /etc/ETCD/ETCD.conf file (depending on your OS).

On ETCD1, the configuration looks like this:

ETCD_NAME=node1

ETCD_DATA_DIR="/var/lib/ETCD"

ETCD_LISTEN_PEER_URLS="http://<node1-ip>:2380"

ETCD_LISTEN_CLIENT_URLS="http://<node1-ip>:2379,http://127.0.0.1:2379"

ETCD_INITIAL_ADVERTISE_PEER_URLS="http://<node1-ip>:2380"

ETCD_ADVERTISE_CLIENT_URLS="http://<node1-ip>:2379"

ETCD_INITIAL_CLUSTER="node1=http://<node1-ip>:2380,node2=http://<node2-ip>:2380,node3=http://<node3-ip>:2380"

ETCD_INITIAL_CLUSTER_STATE="new"

ETCD_INITIAL_CLUSTER_TOKEN="ETCD-cluster-1"

Repeat the configuration on ETCD2 and ETCD3 by changing the ETCD_NAME and IPs accordingly.

Start and enable ETCD:

Step 2: Installing and Configuring Patroni

Install patroni on both servers

Configure Patroni (2 Nodes)

Each PostgreSQL node running Patroni needs a configuration file. Below is an example configuration for the first Patroni node. On both Patroni nodes, create a Patroni configuration file /etc/patroni.yml:

scope: pg_cluster

namespace: /service/

name: patroni1

restapi:

listen: 0.0.0.0:8008

connect_address: <patroni1_name>:8008

ETCD:

host: <ETCD_cluster_address>:2379

bootstrap:

dcs:

ttl: 30

loop_wait: 10

retry_timeout: 10

maximum_lag_on_failover: 1048576

postgresql:

use_pg_rewind:true

parameters:

wal_level: replica

hot_standby: "on"

wal_keep_segments: 8

max_wal_senders: 5

max_replication_slots: 5

synchronous_commit: "off"

initdb:

- encoding: UTF8

- data-checksums

pg_hba:

- host replication replicator 0.0.0.0/0 md5

- host all all 0.0.0.0/0 md5

users:

admin:

password: admin_pass

options:

- createrole

- createdb

postgresql:

listen: 0.0.0.0:5432

connect_address: <patroni1_name>:5432

data_dir: /var/lib/postgresql/12/main

bin_dir: /usr/lib/postgresql/12/bin

authentication:

replication:

username: replicator

password: repl_pass

superuser:

username: postgres

password: postgres_pass

parameters:

unix_socket_directories: '/var/run/postgresql'

On the second node, adjust name, connect_address, and any other node-specific details.

Start Patroni on both nodes:

sudo systemctl start patroni

Step 3 Setup HA Proxy

Install HA Proxy

Configure HAProxy (2 Nodes)
On both HAProxy nodes, edit /etc/haproxy/haproxy.cfg:

global

log /dev/log local0

log /dev/log local1 notice

maxconn 2000

user haproxy

group haproxy

defaults

log global

option redispatch

option tcplog

retries 3

timeout connect 10s

timeout client 1m

timeout server 1m

frontend postgresql

bind *:5432

default_backend postgresql-backend

backend postgresql-backend

balance roundrobin

option tcp-check

server postgresql1<patroni-node1-ip>:5432 check port 8008

server postgresql2 <patroni-node2-ip>:5432 check port 8008

Start HA PROXY

Step 4: Setup Keepalived (2 Nodes)

Install Keepalived

Step 4: Configure Keepalived

On both HAProxy nodes, edit /etc/keepalived/keepalived.conf to define a virtual IP (VIP) for failover.
Node1:

vrrp_script chk_haproxy {

script "pidof haproxy"

interval 2

weight 2

}

vrrp_instance VI_1 {

interface eth0

state MASTER

virtual_router_id 51

priority 101

advert_int 1

authentication {

auth_type PASS

auth_pass password123

}

virtual_ipaddress {

192.168.1.100

}

track_script {

chk_haproxy

}

}

Node 2:

vrrp_instance VI_1 {

interface eth0

state BACKUP

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass password123

}

virtual_ipaddress {

192.168.1.100

}

track_script {

chk_haproxy

}

}

Start and enable Keepalived:

Check Cluster with patronictl

patronictl list

+ Cluster: postgres-cluster (6958757123490168240) --------+----+-----------+

| Member | Host | Role | State | TL | Lag in MB |

+-----------+-------------+---------+---------+----+-----------+

| patroni1 | <patroni1_ip> | Leader | running | 3 | |

| patroni2 | <patroni2_ip> | Replica | running | 3 | 0 |

+-----------+-------------+---------+---------+----+-----------+

This confirms that the PostgreSQL cluster is running with one leader and one replica.

Conclusion

Setting up a highly available PostgreSQL cluster with ETCD, Patroni, HAProxy, and Keepalived ensures that your database remains online, even in the event of multiple node failures. This HA setup distributes responsibilities across various layers:

• ETCD ensures distributed consensus for Patroni.
• Patroni manages PostgreSQL replication and failover.
• HAProxy distributes traffic between the database nodes.
• Keepalived handles failover at the load-balancing layer with a floating VIP.

By following this guide, you can create a robust and resilient PostgreSQL environment capable of handling node failures with minimal downtime.