I have tried to document step by step How to Setup PostgreSQL High Availability using Patroni & Etcd & HAProxy & Keepalived for Debian (Ubuntu). I hope it will be useful for those who need it!
High Level Installation and Configuration Steps
1-Setup Environment Details
2-Set Hostnames on Each Server
3-Update /etc/hosts Files on All Servers
4-Disable Firewall on All Servers
5-Install PostgreSQL
6-Install Etcd
7-Configure Etcd
8-Install Patroni
9-Configure Patroni
10-Enable & Start Patroni
11-Reconfigure Etcd Cluster
12-Install & Configure & Start HAProxy
13-Install & Configure & Start Keepalived
14-Create Table and Insert Some Data for Test Purpose on Leader Node
15-Check Newly Created Table & Data on Replica Nodes
16-Shutdown HAProxy-01 and Ensure DB Connections Work as Expected Using KeepAliveD Virtual IP Address and Ports (RW & RO)
17-Shutdown HAProxy-02 and Ensure DB Connections Work as Expected Using KeepAliveD Virtual IP Address and Ports (RW & RO)
18-KeepAliveD Test Script Using Virtual IP Address and Ports (RW & RO)
1-Setup Environment Details
Ubuntu 24.04 – PostgreSQL 16.9 – Patroni 4.0.5 – Etcd 3.6.0 – HAProxy 2.8.5 – Keepalived 2.2.8
🔹 PostgreSQL 🔹
- What it does: PostgreSQL is a powerful, open-source relational database management system.
- Purpose: It stores and manages your data reliably, supporting complex queries, transactions, and data integrity.
🔹 Patroni 🔹
- What it does: Patroni is a high-availability (HA) solution for PostgreSQL using distributed consensus.
- Purpose: It manages PostgreSQL replication and failover automatically, ensuring that one node is always the primary and the others are replicas.
🔹 Etcd 🔹
- What it does: Etcd is a distributed key-value store used for service discovery and coordination.
- Purpose: In this setup, it helps Patroni coordinate cluster state and perform leader election across nodes.
🔹 HAProxy 🔹
- What it does: HAProxy is a high-performance TCP/HTTP load balancer.
- Purpose: It routes traffic to the correct PostgreSQL node (usually the primary), ensuring continuous database access for applications.
🔹 Keepalived 🔹
- What it does: Keepalived provides IP failover via the Virtual Router Redundancy Protocol (VRRP).
- Purpose: It ensures that the HAProxy IP address is always available, even if one HAProxy node fails, by switching the virtual IP to the healthy node.
2-Set Hostnames on Each Server
192.168.1.151 -> sudo hostnamectl set-hostname postgres-patroni-etcd-01
192.168.1.152 -> sudo hostnamectl set-hostname postgres-patroni-etcd-02
192.168.1.153 -> sudo hostnamectl set-hostname postgres-patroni-etcd-03
192.168.1.154 -> sudo hostnamectl set-hostname haproxy-keepalived-01
192.168.1.155 -> sudo hostnamectl set-hostname haproxy-keepalived-023-Update /etc/hosts Files on All Servers
This step should be repeated on below servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
- haproxy-keepalived-01
- haproxy-keepalived-02
vi /etc/hosts
192.168.1.151 postgres-patroni-etcd-01
192.168.1.152 postgres-patroni-etcd-02
192.168.1.153 postgres-patroni-etcd-03
192.168.1.154 haproxy-keepalived-01
192.168.1.155 haproxy-keepalived-024-Disable Firewall on All Servers
This step should be repeated on below servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
- haproxy-keepalived-01
- haproxy-keepalived-02
systemctl status ufw
systemctl stop ufw
sudo systemctl disable ufw
sudo systemctl mask ufw
systemctl status ufw5-Install PostgreSQL
These steps should be repeated on each of the 3 PostgreSQL Servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
Install PostgreSQL Package Tools
This package provides tools required to manage PostgreSQL versions and to install the official PostgreSQL APT repository.
sudo apt update
sudo apt install -y postgresql-commonAdd the Official PostgreSQL APT Repository
This script automatically adds the PostgreSQL Global Development Group (PGDG) APT repository to your system, enabling access to the latest PostgreSQL versions — including PostgreSQL 16.
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.shUpdate Package List and Install PostgreSQL 16 with Additional Modules
- postgresql-16: The core PostgreSQL 16 server package.
- postgresql-contrib-16: Adds useful extensions like pg_stat_statements, uuid-ossp, and more.
sudo apt update
sudo apt install -y postgresql-16 postgresql-contrib-16Stop and Disable PostgreSQL Service
Patroni will take control of the PostgreSQL process — including startup, shutdown, and restart. Therefore, the default systemd-managed PostgreSQL service must be stopped and disabled.
sudo systemctl stop postgresql
sudo systemctl disable postgresql6-Install Etcd
These steps should be repeated on each of the 3 Etcd Servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
Install Prerequisites
sudo apt update
sudo apt-get install -y wget curlDownload Latest Etcd Release
Go to https://github.com/etcd-io/etcd/releases and copy the latest Linux tarball link (adjust the version if needed):
cd /tmp
wget https://github.com/etcd-io/etcd/releases/download/v3.6.0/etcd-v3.6.0-linux-amd64.tar.gzExtract and Rename Directory
tar xvf etcd-v3.6.0-linux-amd64.tar.gz
mv etcd-v3.6.0-linux-amd64 etcdMove Binaries to /usr/local/bin
sudo mv etcd/etcd* /usr/local/bin/Verify Installation
etcd --version
etcdctl version
Create System User for Etcd
sudo useradd --system --home /var/lib/etcd --shell /bin/false etcdPrepare Etcd Configuration Directory
sudo mkdir -p /etc/etcd
chown -R etcd:etcd /etc/etcd7-Configure Etcd
Create Etcd Config File
Etcd Node1 (postgres-patroni-etcd-01) (192.168.1.151)
sudo vi /etc/etcd/etcd.env
ETCD_NAME="postgres-patroni-etcd-01"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_INITIAL_CLUSTER="postgres-patroni-etcd-01=http://192.168.1.151:2380,postgres-patroni-etcd-02=http://192.168.1.152:2380,postgres-patroni-etcd-03=http://192.168.1.153:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.151:2380"
ETCD_LISTEN_PEER_URLS="http://192.168.1.151:2380,http://localhost:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.1.151:2379,http://localhost:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.151:2379"Etcd Node2 (postgres-patroni-etcd-02) (192.168.1.152)
sudo vi /etc/etcd/etcd.env
ETCD_NAME="postgres-patroni-etcd-02"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_INITIAL_CLUSTER="postgres-patroni-etcd-01=http://192.168.1.151:2380,postgres-patroni-etcd-02=http://192.168.1.152:2380,postgres-patroni-etcd-03=http://192.168.1.153:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.152:2380"
ETCD_LISTEN_PEER_URLS="http://192.168.1.152:2380,http://localhost:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.1.152:2379,http://localhost:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.152:2379"Etcd Node3 (postgres-patroni-etcd-03) (192.168.1.153)
sudo vi /etc/etcd/etcd.env
ETCD_NAME="postgres-patroni-etcd-03"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_INITIAL_CLUSTER="postgres-patroni-etcd-01=http://192.168.1.151:2380,postgres-patroni-etcd-02=http://192.168.1.152:2380,postgres-patroni-etcd-03=http://192.168.1.153:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.153:2380"
ETCD_LISTEN_PEER_URLS="http://192.168.1.153:2380,http://localhost:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.1.153:2379,http://localhost:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.153:2379"Create Etcd Service
These steps should be repeated on each of the 3 Etcd Servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
sudo mkdir -p /var/lib/etcd
sudo chown -R etcd:etcd /var/lib/etcd
sudo vi /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.targetStart Etcd Service
These steps should be repeated on each of the 3 Etcd Servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
# 1. Reload Systemd Manager Configuration
# This reloads unit files to recognize any changes made to the etcd.service definition.
sudo systemctl daemon-reload
# 2. Enable etcd to Start at Boot
sudo systemctl enable etcd
# 3. Start the Etcd Service
# On the first node, this may appear to hang. That’s expected — etcd waits for quorum (a majority of configured members in the cluster) before fully initializing.The behavior changes once another node joins and the cluster forms quorum.
sudo systemctl start etcd
# 4. Check Etcd Service Status
sudo systemctl status etcd
# 5. Check Etcd Logs for Troubleshooting or Confirmation
journalctl -xeu etcd.serviceCheck Etcd Cluster
These steps can be perfomed on one of the 3 Etcd Servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
Check Cluster Members
etcdctl member listExpected Output
Check Endpoint Health
etcdctl endpoint health --endpoints=http://192.168.1.151:2379,http://192.168.1.152:2379,http://192.168.1.153:2379Expected Output
Check Cluster Leader
etcdctl --endpoints=http://192.168.1.151:2379,http://192.168.1.152:2379,http://192.168.1.153:2379 endpoint status --write-out=table
OR
etcdctl endpoint status --cluster --write-out=tableExpected Output
8-Install Patroni
These steps should be repeated on each of the 3 Patroni Servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
# Create Needed Directories
mkdir -p /data
mkdir -p /data/pg/data/16
mkdir -p /data/pg/archive
mkdir -p /data/pg/logs
mkdir -p /data/pg/logs/postgres
mkdir -p /data/pg/logs/patroni
mkdir -p /data/pg/wal
mkdir -p /data/pg/patroni
chown -R postgres:postgres /data
chmod -R 700 /data/pg/data/16/
# Install Patroni
sudo apt install -y patroni9-Configure Patroni
Create Patroni Config File
Patroni Node1 (postgres-patroni-etcd-01) (192.168.1.151)
sudo vi /data/pg/patroni/patroni.yml
scope: postgresql-cluster
namespace: /service/
name: postgres-patroni-etcd-01 # node1
etcd3:
hosts: 192.168.1.151:2379,192.168.1.152:2379,192.168.1.153:2379 # etcd cluster nodes
log:
dir: /data/pg/logs/patroni
level: INFO
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.1.151:8008 # IP for node1's REST API
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576 # Failover parameters
postgresql:
pg_hba: # Access rules
- host replication replicator 127.0.0.1/32 md5
- host replication replicator 192.168.1.151/32 md5
- host replication replicator 192.168.1.152/32 md5
- host replication replicator 192.168.1.153/32 md5
- host all all 127.0.0.1/32 md5
- host all all 0.0.0.0/0 md5
- local all all md5
initdb:
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.1.151:5432 # IP for node1's PostgreSQL
data_dir: /data/pg/data/16
bin_dir: /usr/lib/postgresql/16/bin # Binary directory for PostgreSQL 16
authentication:
superuser:
username: postgres
password: "postgres2025!" # Superuser password - be sure to change
replication:
username: replicator
password: "replicator2025!" # Replication password - be sure to change
parameters:
max_connections: 100
shared_buffers: 256MB
tags:
nofailover: false
noloadbalance: false
clonefrom: falsePatroni Node2(postgres-patroni-etcd-02) (192.168.1.152)
sudo vi /data/pg/patroni/patroni.yml
scope: postgresql-cluster
namespace: /service/
name: postgres-patroni-etcd-02 # node2
etcd3:
hosts: 192.168.1.151:2379,192.168.1.152:2379,192.168.1.153:2379 # etcd cluster nodes
log:
dir: /data/pg/logs/patroni
level: INFO
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.1.152:8008 # IP for node2's REST API
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576 # Failover parameters
postgresql:
pg_hba: # Access rules
- host replication replicator 127.0.0.1/32 md5
- host replication replicator 192.168.1.151/32 md5
- host replication replicator 192.168.1.152/32 md5
- host replication replicator 192.168.1.153/32 md5
- host all all 127.0.0.1/32 md5
- host all all 0.0.0.0/0 md5
- local all all md5
initdb:
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.1.152:5432 # IP for node2's PostgreSQL
data_dir: /data/pg/data/16
bin_dir: /usr/lib/postgresql/16/bin # Binary directory for PostgreSQL 16
authentication:
superuser:
username: postgres
password: "postgres2025!" # Superuser password - be sure to change
replication:
username: replicator
password: "replicator2025!" # Replication password - be sure to change
parameters:
max_connections: 100
shared_buffers: 256MB
tags:
nofailover: false
noloadbalance: false
clonefrom: falsePatroni Node3(postgres-patroni-etcd-03) (192.168.1.153)
sudo vi /data/pg/patroni/patroni.yml
scope: postgresql-cluster
namespace: /service/
name: postgres-patroni-etcd-03 # node3
etcd3:
hosts: 192.168.1.151:2379,192.168.1.152:2379,192.168.1.153:2379 # etcd cluster nodes
log:
dir: /data/pg/logs/patroni
level: INFO
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.1.153:8008 # IP for node3's REST API
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576 # Failover parameters
postgresql:
pg_hba: # Access rules
- host replication replicator 127.0.0.1/32 md5
- host replication replicator 192.168.1.151/32 md5
- host replication replicator 192.168.1.152/32 md5
- host replication replicator 192.168.1.153/32 md5
- host all all 127.0.0.1/32 md5
- host all all 0.0.0.0/0 md5
- local all all md5
initdb:
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.1.153:5432 # IP for node3's PostgreSQL
data_dir: /data/pg/data/16
bin_dir: /usr/lib/postgresql/16/bin # Binary directory for PostgreSQL 16
authentication:
superuser:
username: postgres
password: "postgres2025!" # Superuser password - be sure to change
replication:
username: replicator
password: "replicator2025!" # Replication password - be sure to change
parameters:
max_connections: 100
shared_buffers: 256MB
tags:
nofailover: false
noloadbalance: false
clonefrom: falseEdit Patroni Service File
These steps should be repeated on each of the 3 Patroni Servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
mv /usr/lib/systemd/system/patroni.service /usr/lib/systemd/system/patroni.service_original
vi /usr/lib/systemd/system/patroni.service
[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target
[Service]
Type=simple
User=postgres
Group=postgres
# Read in configuration file if it exists, otherwise proceed
EnvironmentFile=-/etc/patroni_env.conf
# The default is the user's home directory, and if you want to change it, you must provide an absolute path.
# WorkingDirectory=/home/sameuser
# Where to send early-startup messages from the server
# This is normally controlled by the global default set by systemd
#StandardOutput=syslog
# Pre-commands to start watchdog device
# Uncomment if watchdog is part of your patroni setup
#ExecStartPre=-/usr/bin/sudo /sbin/modprobe softdog
#ExecStartPre=-/usr/bin/sudo /bin/chown postgres /dev/watchdog
# Start the patroni process
ExecStart=/usr/bin/patroni /data/pg/patroni/patroni.yml
# Send HUP to reload from patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
# Only kill the patroni process, not it's children, so it will gracefully stop postgres
KillMode=process
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=30
# Restart the service if it crashed
Restart=on-failure
[Install]
WantedBy=multi-user.target10-Enable & Start Patroni
These steps should be repeated on each of the 3 Patroni Servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
chown -R postgres:postgres /data
chmod -R 700 /data/pg/data/16/
systemctl daemon-reload
systemctl enable patroni
systemctl start patroni
systemctl status patroniCheck Patroni Leader & Replica
sudo patronictl -c /data/pg/patroni/patroni.yml list
tail -444f /data/pg/logs/patroni/patroni.log
curl -k http://192.168.1.151:8008/primary
curl -k http://192.168.1.152:8008/primary
curl -k http://192.168.1.153:8008/primary
11-Reconfigure Etcd Cluster
These steps should be repeated on each of the 3 Etcd Servers.
- postgres-patroni-etcd-01
- postgres-patroni-etcd-02
- postgres-patroni-etcd-03
sudo vi /etc/etcd/etcd.env
Change ETCD_INITIAL_CLUSTER_STATE parameter;
FROM
ETCD_INITIAL_CLUSTER_STATE="new"
TO
ETCD_INITIAL_CLUSTER_STATE="existing"12-Install & Configure & Start HAProxy
These steps should be repeated on each of the 2 HAProxy Servers.
- haproxy-keepalived-01
- haproxy-keepalived-02
Install HAProxy
sudo apt -y update
sudo apt -y install haproxyConfigure HAProxy
mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg_original
sudo vi /etc/haproxy/haproxy.cfg
frontend postgres_rw_frontend
bind *:5432
mode tcp
default_backend postgres_rw_backend
frontend postgres_ro_frontend
bind *:5433
mode tcp
default_backend postgres_ro_backend
backend postgres_rw_backend
mode tcp
option httpchk GET /primary
http-check expect status 200
timeout connect 5s
timeout client 30s
timeout server 30s
server postgresql-01 192.168.1.151:5432 check port 8008
server postgresql-02 192.168.1.152:5432 check port 8008
server postgresql-03 192.168.1.153:5432 check port 8008
backend postgres_ro_backend
mode tcp
option httpchk GET /replica
http-check expect status 200
balance roundrobin
timeout connect 5s
timeout client 30s
timeout server 30s
server postgresql-01 192.168.1.151:5432 check port 8008
server postgresql-02 192.168.1.152:5432 check port 8008
server postgresql-03 192.168.1.153:5432 check port 8008🔎 Descriptions
- /primary → This is the HTTP endpoint provided by Patroni that returns HTTP 200 OK only if the node is the leader (read-write).
- /replica → This is the HTTP endpoint that returns HTTP 200 OK only if the node is a replica (read-only).
- 5432 → Used for read-write connections; applications should connect here to perform write operations.
- 5433 → Used for read-only connections; applications should connect here for read queries.
- balance roundrobin → Distributes read-only connections evenly across available replica nodes to achieve load balancing.
Start HAProxy
sudo systemctl start haproxy
sudo systemctl status haproxy
sudo tail -f /var/log/syslog | grep haproxy
13-Install & Configure & Start Keepalived
These steps should be repeated on each of the 2 Keepalived Servers.
- haproxy-keepalived-01
- haproxy-keepalived-02
Install Keepalived
sudo apt update
sudo apt install keepalived -yConfigure Keepalived
Create Check Script on Each Node
sudo vi /etc/keepalived/check_haproxy.sh
#!/bin/bash
# Define the port to check (e.g., HAProxy frontend port)
PORT=5432
# Check if HAProxy is running
if ! pidof haproxy > /dev/null; then
echo "HAProxy is not running"
exit 1
fi
# Check if HAProxy is listening on the expected port
if ! ss -ltn | grep -q ":${PORT}"; then
echo "HAProxy is not listening on port ${PORT}"
exit 2
fi
# All checks passed
exit 0Create User to Execute Script on Each Node
sudo useradd -r -s /bin/false keepalived_script
sudo chmod +x /etc/keepalived/check_haproxy.sh
sudo chown keepalived_script:keepalived_script /etc/keepalived/check_haproxy.sh
sudo chmod 700 /etc/keepalived/check_haproxy.shKeepalived Node1 (haproxy-keepalived-01) (192.168.1.154)
sudo vi /etc/keepalived/keepalived.conf
global_defs {
enable_script_security
script_user keepalived_script
}
vrrp_script check_haproxy {
script "/etc/keepalived/check_haproxy.sh"
interval 2
fall 3
rise 2
}
vrrp_instance VI_1 {
state MASTER
interface enp0s3 # update with your nic
virtual_router_id 51
priority 100
advert_int 1
virtual_ipaddress {
192.168.1.100
}
track_script {
check_haproxy
}
}Keepalived Node2 (haproxy-keepalived-02) (192.168.1.155)
sudo vi /etc/keepalived/keepalived.conf
global_defs {
enable_script_security
script_user keepalived_script
}
vrrp_script check_haproxy {
script "/etc/keepalived/check_haproxy.sh"
interval 2
fall 3
rise 2
}
vrrp_instance VI_1 {
state BACKUP
interface enp0s3 # update with your nic
virtual_router_id 51
priority 90
advert_int 1
virtual_ipaddress {
192.168.1.100
}
track_script {
check_haproxy
}
}Start Keepalived
sudo systemctl restart keepalived
sudo systemctl status keepalivedCheck Keepalived Master & Backup Node
sudo journalctl -u keepalived -f
ip a
14-Create Table and Insert Some Data for Test Purpose on Leader Node
patronictl -c /data/pg/patroni/patroni.yml list
psql -U postgres
CREATE DATABASE testdb;
\c testdb
CREATE TABLE replication_test_table (
id SERIAL PRIMARY KEY,
replication_test VARCHAR(50)
);
INSERT INTO replication_test_table (replication_test) VALUES
('Test01 was inserted by Master-192.168.1.151'),
('Test02 was inserted by Master-192.168.1.151'),
('Test03 was inserted by Master-192.168.1.151'),
('Test04 was inserted by Master-192.168.1.151'),
('Test05 was inserted by Master-192.168.1.151'),
('Test06 was inserted by Master-192.168.1.151');
SELECT * FROM replication_test_table;
15-Check Newly Created Table & Data on Replica Nodes
patronictl -c /data/pg/patroni/patroni.yml list
psql -U postgres
\c testdb
SELECT * FROM replication_test_table;
16-Shutdown HAProxy-01 and Ensure DB Connections Work as Expected Using KeepAliveD Virtual IP Address and Ports (RW & RO)
Check Install & Configure & Start HAProxy Section
- Read/Write Connections:5432
- Read/Only Connections:5433-- Read/Write Connection Test
PGPASSWORD='postgres2025!' psql -h 192.168.1.100 -p 5432 -U postgres -d postgres -c "SELECT inet_server_addr();"
-- Read/Only Connection Test
PGPASSWORD='postgres2025!' psql -h 192.168.1.100 -p 5433 -U postgres -d postgres -c "SELECT inet_server_addr();"
17-Shutdown HAProxy-02 and Ensure DB Connections Work as Expected Using KeepAliveD Virtual IP Address and Ports (RW & RO)
Check Install & Configure & Start HAProxy Section
- Read/Write Connections:5432
- Read/Only Connections:5433-- Read/Write Connection Test
PGPASSWORD='postgres2025!' psql -h 192.168.1.100 -p 5432 -U postgres -d postgres -c "SELECT inet_server_addr();"
-- Read/Only Connection Test
PGPASSWORD='postgres2025!' psql -h 192.168.1.100 -p 5433 -U postgres -d postgres -c "SELECT inet_server_addr();"
18-KeepAliveD Test Script Using Virtual IP Address and Ports (RW & RO)
# RW Test
while [ 1 ]
do
sleep 1
PGPASSWORD='postgres2025!' psql -h 192.168.1.100 -p 5432 -U postgres -d postgres -c "SELECT inet_server_addr();"
done
# RO Test
while [ 1 ]
do
sleep 1
PGPASSWORD='postgres2025!' psql -h 192.168.1.100 -p 5433 -U postgres -d postgres -c "SELECT inet_server_addr();"
done
Some Useful Commands
ETCD Commands
Check Cluster Members
etcdctl member list
Check Endpoint Health
etcdctl endpoint health --endpoints=http://192.168.1.151:2379,http://192.168.1.152:2379,http://192.168.1.153:2379
Check Cluster Leader
etcdctl --endpoints=http://192.168.1.151:2379,http://192.168.1.152:2379,http://192.168.1.153:2379 endpoint status --write-out=table
OR
etcdctl endpoint status --cluster --write-out=table
PATRONI Commands
Check Patroni Leader & Replica Status
sudo patronictl -c /data/pg/patroni/patroni.yml list
tail -444f /data/pg/logs/patroni/patroni.log
curl -k http://192.168.1.151:8008/primary
curl -k http://192.168.1.152:8008/primary
curl -k http://192.168.1.153:8008/primary
Check & Edit & Reload Patroni Config
# Check Config
sudo patronictl -c /data/pg/patroni/patroni.yml show-config
# Edit Config
# After saving these will be replicated to all nodes
sudo patronictl -c /data/pg/patroni/patroni.yml edit-config
# Reload Config
# postgresql-cluster is name of your cluster
patronictl -c /data/pg/patroni/patroni.yml reload postgresql-clusterPatroni Switchover
# 1. Check the Cluster Status
patronictl -c /data/pg/patroni/patroni.yml list
# 2. Perform the Switchover
patronictl -c /data/pg/patroni/patroni.yml switchover
Primary [postgres-patroni-etcd-02]: ENTER
Candidate ['postgres-patroni-etcd-01', 'postgres-patroni-etcd-03'] []: postgres-patroni-etcd-01
When should the switchover take place (e.g. 2025-05-29T17:08 ) [now]: ENTER
Are you sure you want to switchover cluster postgresql-cluster, demoting current leader postgres-patroni-etcd-03? [y/N]: y
2025-05-29 16:08:44.22239 Successfully switched over to "postgres-patroni-etcd-01"
Reinitialize Replica
# Syntax
patronictl --config-file=<patroni_yml_file_path> reinit <cluster_name> <member_name>
# Example
patronictl --config-file=/data/pg/patroni/patroni.yml reinit postgresql-cluster postgres-patroni-etcd-01
Restart PostgreSQL Nodes
# Syntax
patronictl restart <cluster_name> [<member_name>] [--role <role>] [--force]# Restart All Nodes
patronictl --config-file=/data/pg/patroni/patroni.yml restart postgresql-cluster
# Restart Specific Node
patronictl --config-file=/data/pg/patroni/patroni.yml restart postgresql-cluster postgres-patroni-etcd-01
KEEPALIVED Commands
Check Keepalived Master & Backup Node
sudo journalctl -u keepalived -f
ip a
SERHAT CELIK DATABASE BLOG 