02- Patroni Cluster on Rocky Linux9.5 - Setup Patroni & PostgreSQL

Super User Hits: 92

We will install Postgres on their own nodes (seperate from ETCD).

Run the following on nodes Postgres01, 02 and 03.

#Add PostgreSQL 17 Repository and Install
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#disable the default AppStream module for PostgreSQL
dnf -qy module disable postgresql
dnf install -y postgresql17-server postgresql17-contrib

#Stop and Disable Native PostgreSQL Service
systemctl stop postgresql-17
systemctl disable postgresql-17

#Create PostgreSQL Data Directory for Patroni
mkdir -p /var/lib/pgsql/17/data
chown postgres:postgres /var/lib/pgsql/17/data
chmod 700 /var/lib/pgsql/17/data

#Install Patroni and Dependencies
dnf install -y epel-release
dnf install -y python3-psycopg2 python3-requests python3-pip gcc python3-devel
pip3 install patroni[etcd] etcd3


#add "/usr/local/bin" to your system’s PATH to be able to run patroni or patronictl
echo 'export PATH=$PATH:/usr/local/bin' > /etc/profile.d/patroni.sh
chmod +x /etc/profile.d/patroni.sh
source /etc/profile.d/patroni.sh

Create Patroni Configuration file

mkdir -p /etc/patroni/
nano /etc/patroni/patroni.yml

Node1:

scope: postgresql-cluster
namespace: /service/
name: postgresql-01

etcd3:
  hosts: 192.168.1.151:2379,192.168.1.152:2379,192.168.1.153:2379
  protocol: http

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.144:8008

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      parameters:
      pg_hba:
        - host replication replicator 127.0.0.1/32 md5
        - host replication replicator 192.168.1.144/32 md5
        - host replication replicator 192.168.1.145/32 md5
        - host replication replicator 192.168.1.146/32 md5
        - host all all 0.0.0.0/0 md5
  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.144:5432
  data_dir: /var/lib/pgsql/17/data
  bin_dir: /usr/pgsql-17/bin
  authentication:
    superuser:
      username: postgres
      password: YourPassword123
    replication:
      username: replicator
      password: YourPassword12345
  parameters:
    max_connections: 100
    shared_buffers: 256MB

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

Repeat for postgres02 and postgres03 with adjusted hostnames, IPs, and connect addresses.

Node2:

scope: postgresql-cluster
namespace: /service/
name: postgresql-02

etcd3:
  hosts: 192.168.1.151:2379,192.168.1.152:2379,192.168.1.153:2379
  protocol: http

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.145:8008

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      parameters:
      pg_hba:
        - host replication replicator 127.0.0.1/32 md5
        - host replication replicator 192.168.1.144/32 md5
        - host replication replicator 192.168.1.145/32 md5
        - host replication replicator 192.168.1.146/32 md5
        - host all all 0.0.0.0/0 md5
  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.145:5432
  data_dir: /var/lib/pgsql/17/data
  bin_dir: /usr/pgsql-17/bin
  authentication:
    superuser:
      username: postgres
      password: YourPassword123
    replication:
      username: replicator
      password: YourPassword12345
  parameters:
    max_connections: 100
    shared_buffers: 256MB

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

Node3:

scope: postgresql-cluster
namespace: /service/
name: postgresql-03

etcd3:
  hosts: 192.168.1.151:2379,192.168.1.152:2379,192.168.1.153:2379
  protocol: http

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.146:8008

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      parameters:
      pg_hba:
        - host replication replicator 127.0.0.1/32 md5
        - host replication replicator 192.168.1.144/32 md5
        - host replication replicator 192.168.1.145/32 md5
        - host replication replicator 192.168.1.146/32 md5
        - host all all 0.0.0.0/0 md5
  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.146:5432
  data_dir: /var/lib/pgsql/17/data
  bin_dir: /usr/pgsql-17/bin
  authentication:
    superuser:
      username: postgres
      password: YourPassword123
    replication:
      username: replicator
      password: YourPassword12345
  parameters:
    max_connections: 100
    shared_buffers: 256MB

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

We must create Patroni service file. We installed Patroni manually into /usr/local/bin/ via pip3 install patroni[etcd]. So systemd should know how to start it with this unit file.

nano /etc/systemd/system/patroni.service

#Add the following
[Unit]
Description=Patroni PostgreSQL High Availability
After=network.target

[Service]
Type=simple
User=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target

Enable and Start Patroni

systemctl daemon-reload
systemctl enable --now patroni
journalctl -u patroni -f

patronictl -c /etc/patroni/patroni.yml list

Patroni Cluster is UP and Running. If replica nodes state is "stopped" you can run the following commands to reinitialize a PostgreSQL replica node (deletes ALL PostgreSQL data on this node)

systemctl stop patroni
rm -rf /var/lib/pgsql/17/data/*
systemctl start patroni
patronictl -c /etc/patroni/patroni.yml list

In the next article, we will install and configure HAProxy and KeepAlived.