2025-09-15 10:09:34 - No Comments
This video demonstrates how to set up a highly available PostgreSQL cluster using Patroni, ETCD, and HAProxy on Debian, covering installation, configuration, environment variables, database initialization, user setup, security rules, and Patroni management commands for failover and switchover, while also showcasing read/write splitting with HAProxy for improved scalability.
Patroni installation
First we install all the dependencies for HAProxy, Patroni and PostgreSQL.
sudo apt update
sudo apt install -y postgresql-15 postgresql-contrib-15 patroni python3-pysyncobj haproxy etcd-server etcd-client python3-etcd python3-etcd3Environment variables for creation
Next we setup some environment variables we can later use to create configuration files.
export NODE_NAME=`hostname -f`
export NODE_IP=`hostname -i | awk '{print $1}'`
export PG_DATA_DIR="/var/lib/postgresql/15/main"
export PG_BIN_DIR="/usr/lib/postgresql/15/bin"
export PG_CONFIG_DIR="/etc/postgresql/15/main"
export CLUSTER="cluster_1"
export ETCDCTL_API=3
export ETCD_NAME=`hostname`
export HOST_1=192.168.1.1
export HOST_2=192.168.1.2
export HOST_3=192.168.1.3
export ENDPOINTS=$HOST_1:2379,$HOST_2:2379,$HOST_3:2379ETCD config
Echo a new ETCD configuration file for this particular host. Do that for all the hosts in your ETCD cluster.
echo "
ETCD_NAME="${ETCD_NAME}"
ETCD_DATA_DIR="/var/lib/etcd/mycluster"
ETCD_INITIAL_CLUSTER="per1=http://${HOST_1}:2380,per2=http://${HOST_2}:2380,per3=http://${HOST_3}:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="patroni_cluster_1"
ETCD_ADVERTISE_CLIENT_URLS="http://${NODE_IP}:2379"
ETCD_LISTEN_PEER_URLS="http://${NODE_IP}:2380"
ETCD_LISTEN_CLIENT_URLS="http://${NODE_IP}:2379,http://localhost:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://${NODE_IP}:2380"
" | sudo tee /etc/default/etcdSomewhat quickly start all the services in your cluster so they can find each other and bootstrap the system.
sudo systemctl enable --now etcd
sudo systemctl restart etcd
sudo journalctl -fu etcdNext we need to check that the cluster is available, all nodes are visible and one of them have been chosen as leader.
sudo etcdctl --endpoints=$ENDPOINTS -w table member list
sudo etcdctl --endpoints=$ENDPOINTS -w table endpoint statusPostgreSQL configuration
We need to create a couple of accounts for our server and set passwords so Patroni intern can do important replication and rewinding tasks.
sudo -u postgres psqlALTER SYSTEM SET password_encryption = 'scram-sha-256';
ALTER ROLE postgres WITH LOGIN PASSWORD 'qwerty';
CREATE USER replicator WITH LOGIN PASSWORD 'replPasswd';
ALTER ROLE replicator WITH REPLICATION;
CREATE USER rewind WITH LOGIN PASSWORD 'rewindPasswd';PostgreSQL allowed access
Setting up which networks we allow access from to this server. This is mostly required for servers that we want to keep the database. If we let Patroni bootstrap it the configuration values in the Patroni config file is used.
sudo vi /etc/postgresql/15/main/pg_hba.conflocal all all scram-sha-256
host replication replicator 127.0.0.1/32 trust
host replication replicator 192.168.0.0/16 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256PostgreSQL stopping
Stopping and disabling PostgreSQL service, Patroni vill start PostgreSQL for you so we don't want the systemd service blocking that process.
sudo systemctl stop postgresql
sudo systemctl disable postgresqlPatroni config
This small script will echo a configuration into a file using our previously defined environment variables. This script contains passwords that of course needs changing if used in a production environment.
echo "
scope: ${CLUSTER}
name: ${NODE_NAME}
restapi:
listen: 0.0.0.0:8008
connect_address: ${NODE_IP}:8008
etcd3:
host: ${NODE_IP}:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: on
wal_log_hints: on
logging_collector: on
max_wal_size: "10GB"
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- local all all scram-sha-256
- host replication replicator 127.0.0.1/32 trust
- host replication replicator 192.168.0.0/16 scram-sha-256
- host all all 0.0.0.0/0 scram-sha-256
postgresql:
listen: 0.0.0.0:5432
connect_address: ${NODE_IP}:5432
data_dir: ${PG_DATA_DIR}
bin_dir: ${PG_BIN_DIR}
config_dir: ${PG_CONFIG_DIR}
authentication:
superuser: { username: postgres, password: 'qwerty' }
replication: { username: replicator, password: 'replPasswd' } # change me
rewind: { username: rewind, password: 'rewindPasswd' } # change me
parameters:
unix_socket_directories: '/var/run/postgresql'
password_encryption: 'scram-sha-256'
watchdog:
mode: 'off' # Allowed values: off, automatic, required
device: /dev/watchdog
safety_margin: 5
" | sudo tee /etc/patroni/config.ymlRun a quick validation on your configuration to fix eventual issues that will occur 🙂
sudo -u postgres patroni --validate-config /etc/patroni/config.ymlIf you bootstrap the database
In some cases you don't care about the data or some nodes will replicate others, in that case removing the old database and let Patroni bootstrap a database is the best approach.
sudo rm -rf /var/lib/postgresql/15/main/Bringing up nodes
Now we start Patroni one host at a time. Ensuring that it starts, finds and bootstraps the database correctly without any problem.
sudo systemctl enable --now patroni
sudo systemctl restart patroni
sudo journalctl -fu patroniAfter starting each host we can check the status by listing the members of the cluster.
patronictl -c /etc/patroni/config.yml list $CLUSTERHAProxy
Setting up HAProxy for primary and standby databases so we can send write and read workloads to different ports and always be sure that we are talking to the right databases.
sudo vi /etc/haproxy/haproxy.cfgThis configuration will setup a status page on port 7000 where you can see which databases are in standby mode an which is the primary node that you need to use for writing.
listen stats
mode http
bind *:7000
stats enable
stats uri /
listen primary
bind *:5000
mode tcp
option tcplog
option httpchk /primary
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server per1 per1:5432 maxconn 100 check port 8008
server per2 per2:5432 maxconn 100 check port 8008
server per3 per3:5432 maxconn 100 check port 8008
listen standbys
balance roundrobin
bind *:5001
mode tcp
option tcplog
option httpchk /replica
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server per1 per1:5432 maxconn 100 check port 8008
server per2 per2:5432 maxconn 100 check port 8008
server per3 per3:5432 maxconn 100 check port 8008Check the configuration file for any issues and then reload your configuration.
sudo haproxy -c -f /etc/haproxy/haproxy.cfg
sudo systemctl reload haproxyTesting connection
Now you should be able to connect to your HAProxy on either port 5000 for writing/reading tasks and 5001 for reading tasks.
psql -U postgres -h localhost -p 5000
psql -U postgres -h localhost -p 5001Troubleshooting
In this section you will find some commands that you can use to trouble shoot problems or reset your cluster in order to start over.
If you need to reset you can remove cluster and data directory.
patronictl -c /etc/patroni/config.yml remove $CLUSTER
sudo rm -rf /var/lib/postgresql/15/main/These are commands for PostgreSQL to see if you have a read only database, if it's in recovery mode. Promote it to primary and reloading configuration.
sudo -u postgres psql
SHOW transaction_read_only;
SELECT pg_is_in_recovery();
SELECT pg_promote();
SELECT pg_reload_conf();
Be the first to leave a comment!