Today we are going to setup a Postgresql Patroni Cluster with HAProxy. A Patroni cluster is a high availability PostgreSQL cluster managed by Patroni, an open-source tool that automates PostgreSQL replication and failover. Patroni makes a PostgreSQL cluster "smart" by using a distributed key-value store to elect a leader node (primary) and manage replica nodes (standbys). It ensures there's always one writable primary and handles automatic failover if that primary becomes unavailable. For leader election and cluster coordination we will be using ETCD (Distributed Key-Value Store -DCS) . The OS I am using is Ubuntu 22.04 and Postgresql version is 17.04 which is the latest stable version at the moment. In this article, I will be installing ETCD and Patroni on the same nodes.
The infrastructure that I am building today is:
The procedure that I will be following is as follows:
Install Postgresql
Install & Configure and Start ETCD
Install & Configure and Start Patroni
Install & Configure HAProxy and Keepalived
On all Postgres nodes (postgres01,02 and 03):
Update the postgresql repo.
#first switch to user root
sudo su
apt update
apt install -y postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
Install Postgres and additional modules
apt update
apt install -y postgresql postgresql-contrib
We will configure Postgresql later. Stop & disable postgresql service because patroni will handle the lifecycle of postgres and systemctl will not manage the service.
systemctl stop postgresql
systemctl disable postgresql
ETCD Installation:
On all Postgres nodes (postgres01,02 and 03):
Make sure curl is installed on your system
apt install -y wget curl
I am going to install ETCD version 3.5.19.
You can check the current etcd releases from: https://github.com/etcd-io/etcd/releases.
wget https://github.com/etcd-io/etcd/releases/download/v3.5.19/etcd-v3.5.19-linux-amd64.tar.gz
tar xvf etcd-v3.5.19-linux-amd64.tar.gz
mv etcd-v3.5.19-linux-amd64 etcd
In etcd folder I see 3 binary files. Their filename starts with etcd. Move these files to /usr/local/bin
mv etcd/etcd* /usr/local/bin/
Create a service user for etcd service. This user's home directory is /var/lib/etcd and user can not sign in.
useradd --system --home /var/lib/etcd --shell /bin/false etcd
ETCD Configuration:
Create a data directory for etcd. I am also cretaing an environment file for the service. Once the etcd service starts these environmental variables will be applied.
mkdir -p /etc/etcd
chown -R etcd:etcd /etc/etcd
nano /etc/etcd/etcd.env
Cluster state is new for now. We will change it to existing after ETCD cluster up and running.
etcd.env Node1:
ETCD_NAME="postgresql-01"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_INITIAL_CLUSTER="postgresql-01=http://192.168.1.144:2380,postgresql-02=http://192.168.1.145:2380,postgresql-03=http://192.168.1.146:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.144:2380"
ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"
ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.144:2379"
etcd.env Node2:
ETCD_NAME="postgresql-02"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_INITIAL_CLUSTER="postgresql-01=http://192.168.1.144:2380,postgresql-02=http://192.168.1.145:2380,postgresql-03=http://192.168.1.146:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.145:2380"
ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"
ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.145:2379"
etcd.env Node3:
ETCD_NAME="postgresql-03"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_INITIAL_CLUSTER="postgresql-01=http://192.168.1.144:2380,postgresql-02=http://192.168.1.145:2380,postgresql-03=http://192.168.1.146:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.146:2380"
ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"
ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.146:2379"
Now let’s create a service for etcd on all 3 nodes. Configuration is the same on all etcd nodes.
nano /etc/systemd/system/etcd.service[Unit]
Description=etcd key-value store
Documentation=https://github.com/etcd-io/etcd
After=network-online.target
Wants=network-online.target
[Service]
Type=notify
WorkingDirectory=/var/lib/etcd
EnvironmentFile=/etc/etcd/etcd.env
ExecStart=/usr/local/bin/etcd
Restart=always
RestartSec=10s
LimitNOFILE=40000
User=etcd
Group=etcd
[Install]
WantedBy=multi-user.target
We need to create a directory for etcd (ETCD_DATA_DIR) defined in environment file.
mkdir -p /var/lib/etcd
chown -R etcd:etcd /var/lib/etcd
Reload daemon and enable the service all on 3 nodes
systemctl daemon-reload
systemctl enable etcd
Then start service on all 3 nodes (this looks like it’s hanging on the first node but once you add another node it will complete).
Status should be active. You can see etcd log by using journalctl command like below.
systemctl start etcd
systemctl status etcd
journalctl -u etcd -f
etcdctl member list --endpoints=http://localhost:2379
The last command above lists the cluster members
To see the leader node, run
etcdctl --endpoints=http://192.168.1.144:2379,http://192.168.1.145:2379,http://192.168.1.146:2379 endpoint status --write-out=table
To check etcd node health status
etcdctl endpoint health --endpoints=http://192.168.1.144:2379,http://192.168.1.145:2379,http://192.168.1.146:2379 endpoint status --write-out=table
In the next article, We will install and configure Patroni.