Architecture:
OS: Ubuntu 22.04
Postgres version: 14
Machine Details:
Machine: node1 IP: <node1_ip> Role: Postgresql, Patroni
Machine: node2 IP: <node2_ip> Role: Postgresql, Patroni
Machine: node3 IP: <node3_ip> Role: Postgresql, Patroni
Machine: etcdnode IP: <etcdnode_ip> Role: etcd
Machine: haproxynode IP: <haproxynode_ip> Role: HA Proxy
In my case:
Machine: node1 IP: 192.168.32.130 Role: Postgresql, Patroni
Machine: node2 IP: 192.168.32.131 Role: Postgresql, Patroni
Machine: node3 IP: 192.168.32.xxx Role: Postgresql, Patroni
Machine: etcdnode IP: 192.168.32.140 Role: etcd
Machine: haproxynode IP: 192.168.32.135 Role: HA Proxy
Step-by-step instructions guide
Step 1 – Setup node1, node2, node3:
--Setup node1--
sudo apt update
sudo hostnamectl set-hostname node1
sudo apt install net-tools
sudo apt install postgresql postgresql-server-dev-14
sudo systemctl stop postgresql
sudo ln -s /usr/lib/postgresql/14/bin/* /usr/sbin/
sudo apt -y install python3 python3-pip
sudo apt install python3-testresources
sudo pip3 install --upgrade setuptools
sudo pip3 install psycopg2
sudo pip3 install patroni
sudo pip3 install python-etcd
--Setup node2--
sudo apt update
sudo hostnamectl set-hostname node2
sudo apt install net-tools
sudo apt install postgresql postgresql-server-dev-14
sudo systemctl stop postgresql
sudo ln -s /usr/lib/postgresql/14/bin/* /usr/sbin/
sudo apt -y install python3 python3-pip
sudo apt install python3-testresources
sudo pip3 install --upgrade setuptools
sudo pip3 install psycopg2
sudo pip3 install patroni
sudo pip3 install python-etcd
--Setup node3--
sudo apt update
sudo hostnamectl set-hostname node3
sudo apt install net-tools
sudo apt install postgresql postgresql-server-dev-14
sudo systemctl stop postgresql
sudo ln -s /usr/lib/postgresql/14/bin/* /usr/sbin/
sudo apt -y install python3 python3-pip
sudo apt install python3-testresources
sudo pip3 install --upgrade setuptools
sudo pip3 install psycopg2
sudo pip3 install patroni
sudo pip3 install python-etcd
Step 2 – Setup etcdnode:
sudo apt update
sudo hostnamectl set-hostname etcdnode
sudo apt install net-tools
sudo apt -y install etcd
Step 3 – Setup haproxynode:
sudo apt update
sudo hostnamectl set-hostname haproxynode
sudo apt install net-tools
sudo apt -y install haproxy
Step 4 – Configure etcd on the etcdnode:
sudo vi /etc/default/etcd
ETCD_LISTEN_PEER_URLS="http://192.168.32.140:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://192.168.32.140:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.32.140:2380"
ETCD_INITIAL_CLUSTER="default=http://192.168.32.140:2380,"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.32.140:2379"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
sudo systemctl restart etcd
sudo systemctl status etcd
OUTPUT:--
curl http://192.168.32.140:2380/members
OUTPUT:--
Step 5-Part1 – Configure Patroni on the node1:
sudo vi /etc/patroni.yml
scope: postgres
namespace: /db/
name: node1
restapi:
listen: 192.168.32.130:8008
connect_address: 192.168.32.130:8008
etcd:
host: 192.168.32.140:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication replicator 127.0.0.1/32 md5
- host replication replicator 192.168.32.130/0 md5
- host replication replicator 192.168.32.131/0 md5
- host replication replicator 192.168.32.xxx/0 md5
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: 192.168.32.130:5432
connect_address: 192.168.32.130:5432
data_dir: /data/patroni
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: admin@123
superuser:
username: postgres
password: admin@123
parameters:
unix_socket_directories: '.'
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
sudo mkdir -p /data/patroni
sudo chown postgres:postgres /data/patroni
sudo chmod 700 /data/patroni
sudo vi /etc/systemd/system/patroni.service
[Unit]
Description=High availability PostgreSQL Cluster
After=syslog.target network.target
[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni.yml
KillMode=process
TimeoutSec=30
Restart=no
[Install]
WantedBy=multi-user.targ
Step 5-Part2 – Configure Patroni on the node2:
sudo vi /etc/patroni.yml
scope: postgres
namespace: /db/
name: node2
restapi:
listen: 192.168.32.131:8008
connect_address: 192.168.32.131:8008
etcd:
host: 192.168.32.140:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication replicator 127.0.0.1/32 md5
- host replication replicator 192.168.32.130/0 md5
- host replication replicator 192.168.32.131/0 md5
- host replication replicator 192.168.32.xxx/0 md5
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: 192.168.32.131:5432
connect_address: 192.168.32.131:5432
data_dir: /data/patroni
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: admin@123
superuser:
username: postgres
password: admin@123
parameters:
unix_socket_directories: '.'
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
sudo mkdir -p /data/patroni
sudo chown postgres:postgres /data/patroni
sudo chmod 700 /data/patroni
sudo vi /etc/systemd/system/patroni.service
[Unit]
Description=High availability PostgreSQL Cluster
After=syslog.target network.target
[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni.yml
KillMode=process
TimeoutSec=30
Restart=no
[Install]
WantedBy=multi-user.targ
Step 5-Part3 – Configure Patroni on the node3:
sudo vi /etc/patroni.yml
scope: postgres
namespace: /db/
name: node2
restapi:
listen: 192.168.32.xxx:8008
connect_address: 192.168.32.xxx:8008
etcd:
host: 192.168.32.140:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication replicator 127.0.0.1/32 md5
- host replication replicator 192.168.32.130/0 md5
- host replication replicator 192.168.32.131/0 md5
- host replication replicator 192.168.32.xxx/0 md5
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: 192.168.32.xxx:5432
connect_address: 192.168.32.xxx:5432
data_dir: /data/patroni
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: admin@123
superuser:
username: postgres
password: admin@123
parameters:
unix_socket_directories: '.'
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
sudo mkdir -p /data/patroni
sudo chown postgres:postgres /data/patroni
sudo chmod 700 /data/patroni
sudo vi /etc/systemd/system/patroni.service
[Unit]
Description=High availability PostgreSQL Cluster
After=syslog.target network.target
[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni.yml
KillMode=process
TimeoutSec=30
Restart=no
[Install]
WantedBy=multi-user.targ
Step 6 – Start Patroni service on the node1, on the node2 and on the node3:
sudo systemctl start patroni
sudo systemctl status patroni
OUTPUT:-
Step 7 – Configuring HA Proxy on the node haproxynode:
sudo vi /etc/haproxy/haproxy.cfg
Replace its context with this:
global
maxconn 100
log 127.0.0.1 local2
defaults
log global
mode tcp
retries 2
timeout client 30m
timeout connect 4s
timeout server 30m
timeout check 5s
listen stats
mode http
bind *:7000
stats enable
stats uri /
listen postgres
bind *:5000
option httpchk
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server node1 192.168.32.130:5432 maxconn 100 check port 8008
server node2 192.168.32.131:5432 maxconn 100 check port 8008
server node2 192.168.32.xxx:5432 maxconn 100 check port 8008
sudo systemctl restart haproxy
sudo systemctl status haproxy
OUTPUT:--
Step 8 – Testing High Availability Cluster Setup of PostgreSQL:
http://<haproxynode_ip>:7000/>
Check the output
sudo systemctl stop patroni
In this case, the second Postgres server is promoted to master.
Step 9 – Connect Postgres clients to the HAProxy IP address:
psql -h <haproxynode_ip> -p 5000 -U postgres
psql -h 192.168.32.135 -p 5000 -U postgres
Password for user postgres:
postgres=#
root@node1:/home/node1# patronictl -c /etc/patroni.yml list
+ Cluster: postgres (7366967472395659584) ------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+--------+----------------+---------+-----------+----+-----------+
| node1 | 192.168.32.130 | Replica | streaming | 2 | 0 |
| node2 | 192.168.32.131 | Leader | running | 2 | |
+--------+----------------+---------+-----------+----+-----------+
root@node1:/home/node1#
Step 10 – Failover test:
On one of the nodes run:
sudo systemctl stop patroni
Now check in haproxy server.
