PosgresSQL

SET UP PATRONI POSTGRESQL 16 HA CLUSTER

By dem-linux #cluster, #linux, #patroni, #postgres

How to set up patorni high available PostgreSQL on Linux with etcd + ha proxy.

Node1 – PatroniNode2 – PatroniETCD1ETCD2HA Proxy 1HA Proxy 2
Debian
10.10.0.181		Debian
10.10.0.182		Debian
10.10.0.220		Debian
10.10.0.221		Debian
10.10.0.216
Keepalived
10.10.0.218		Debian
10.10.0.217
Keepalived
10.10.0.218
Keepalived will move the IP upon the status of the server, if the master/primary server dies it will automatically assign the 10.10.0.218 IP to the secondary HA proxy.

To add the latest Postgres to your server check PosgresSQL website. In my case I am adding this for Debian if you are using debian as well you can copy paste most of my commands.

Bash
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

On the Node 1 and Node 2 install postgres:

Bash
apt-get install postgresql-16 postgresql-contrib -y

Stop Postgres when it’s done:

Bash
systemctl stop postgresql

Create Symlink on both nodes: (change 16 to the latest version)

Bash
ln -s /usr/lib/postgresql/16/bin/* /usr/sbin/

Install python and patroni on Node 1 and 2.

Bash
apt install python3-pip python3-dev libpq-dev -y 
pip3 install --upgrade pip 
pip install patroni 
pip install python-etcd 
pip install psycopg2

Create Configuration File for Patroni:

Bash
nano /etc/patroni.yml

Things to change:

  • name: node1 < set name for your node
  • listen: listen: NODE_IP_GOES_HERE:8008 set to your NODE IP!
  • connect: connect_address: NODE_IP_GOES_HERE:8008 same as above set to your NODE IP!
  • etcd: host: 10.10.0.184:2379 < If you are using one etcd then change the IP if you are using more etcd like i show in this guide then change it to “hosts: 10.10.0.184:2379, 10.10.0.185:2379
  • Add your IP in pg_hba replication.
  • Change under postgres listen and connect set same IP as the NODE IP! (your servers IP)
  • Change data dir to whatever you prefer.
  • Set strong password, in this case we are using md5 but you can check my other post how to change that to much stronger.

Node1 configuration:

YAML
scope: postgres
namespace: /db/
name: node1

restapi:
    listen: 10.10.0.181:8008 
    connect_address: 10.10.0.181:8008
etcd:
    hosts: 10.10.0.220:2379, 10.10.0.221:2379

bootstrap:
    dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
            use_pg_rewind: true

    initdb:
    - encoding: UTF8
    - data-checksums

    pg_hba:
    - host replication replicator 127.0.0.1/32 md5
    - host replication replicator 10.10.0.181/0 md5
    - host replication replicator 10.10.0.182/0 md5
    - host all all 0.0.0.0/0 md5 

    users:
        admin:
            password: admin
            options:
                - createrole
                - createdb

postgresql:
    listen: 10.10.0.181:5432
    connect_address: 10.10.0.181:5432
    data_dir: /dev/data/patroni/
    pgpass: /tmp/pgpass
    authentication:
        replication:
            username: replicator
            password: password
        superuser:
            username: postgres
            password: password
    parameters:
        unix_socket_directories: '.'

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

Node 2 Configuration

YAML
scope: postgres
namespace: /db/
name: node2

restapi:
    listen: 10.10.0.182:8008 
    connect_address: 10.10.0.182:8008
etcd:
    hosts: 10.10.0.184:2379, 10.10.0.185:2379

bootstrap:
    dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
            use_pg_rewind: true

    initdb:
    - encoding: UTF8
    - data-checksums

    pg_hba:
    - host replication replicator 127.0.0.1/32 md5
    - host replication replicator 10.10.0.181/0 md5
    - host replication replicator 10.10.0.182/0 md5
    - host all all 0.0.0.0/0 md5

    users:
        admin:
            password: admin
            options:
                - createrole
                - createdb

postgresql:
    listen: 10.10.0.182:5432
    connect_address: 10.10.0.182:5432
    data_dir: /dev/data/patroni/
    pgpass: /tmp/pgpass
    authentication:
        replication:
            username: replicator
            password: password
        superuser:
            username: postgres
            password: password
    parameters:
        unix_socket_directories: '.'

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

Now create DataDir on both nodes and set file permission:

Bash
mkdir -p /dev/data/patroni/ 
chown postgres:postgres /dev/data/patroni/
chmod 700 /dev/data/patroni/

Create the service on both nodes so we can later start the patroni service:

Bash
nano /etc/systemd/system/patroni.service
Bash
[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
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.target

Setup ETCD on your etcd hosts, in my case I will use 2 etcd servers!

Bash
apt install etcd -y
Bash
nano /etc/default/etcd
ETCD_NAME="CHANGE ME"
ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"
ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://CHANGE-ME:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://CHANGE-ME:2380,etcd2=http://CHANGE-ME:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="node1"
ETCD_ADVERTISE_CLIENT_URLS="http://CHANGE-ME:2379"
ETCD_ENABLE_V2="true"

Set the IP to your etcd host, in the row ETCD_INITIAL_CLUSTER this is where we tell the etcd that it will work / talk in a cluster, add both hosts there for me my etcd1 has IP of 10.10.0.220:2380 for you this could be something else
ETCD_INITIAL_CLUSTER="etcd1=http://ETCD-SERVER-IP-1:2380,etcd2=http://ETCD-SERVER-IP-2:2380"

etcd server 1 configuration and server 2

This is how my configuration looks like on both etcd servers:

ETCD_NAME="etcd1"
ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"
ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.10.0.220:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://10.10.0.220:2380,etcd2=http://10.10.0.221:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="node1"
ETCD_ADVERTISE_CLIENT_URLS="http://10.10.0.220:2379"
ETCD_ENABLE_V2="true"

Server 2:

ETCD_NAME="etcd2"
ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"
ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.10.0.221:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://10.10.0.220:2380,etcd2=http://10.10.0.221:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="node1"
ETCD_ADVERTISE_CLIENT_URLS="http://10.10.0.221:2379"
ETCD_ENABLE_V2="true"

Setup HA-PROXY:

Bash
apt install haproxy -y
nano /etc/haproxy/haproxy.cfg
global
    maxconn 100

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 /

frontend patroni-prod
        mode tcp
        maxconn 5000
        bind *:5432
        default_backend patroni_servers


backend patroni_servers
        mode tcp
        option httpchk OPTIONS /leader
        http-check expect status 200
        default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

        server node1 10.10.0.181:5432 maxconn 100 check port 8008
        server node2 10.10.0.182:5432 maxconn 100 check port 8008

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 10.10.0.181:5432 maxconn 100 check port 8008
    server node2 10.10.0.182:5432 maxconn 100 check port 8008

To setup HA on HAProxy you need to have 2 HA proxy servers that points to same backend

Use same configuration file on both servers:

global
    maxconn 100

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 /

frontend patroni-prod
        mode tcp
        maxconn 5000
        bind *:5432
        default_backend patroni_servers


backend patroni_servers
        mode tcp
        option httpchk OPTIONS /leader
        http-check expect status 200
        default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

        server node1 10.10.0.181:5432 maxconn 100 check port 8008
        server node2 10.10.0.182:5432 maxconn 100 check port 8008

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 10.10.0.181:5432 maxconn 100 check port 8008
    server node2 10.10.0.182:5432 maxconn 100 check port 8008

Now install Keepalived on both servers (proxys)

Bash
apt install keepalived
nano /etc/keepalived/keepalived.conf

Add the following to the keepalived.conf on the ha proxy server 1 and on the other server change the STATE to SLAVE Make sure you pick a free IP for the virtual IP!

Make sure to exclude this IP from the dhcp pool, dhcp will still think that this is a free IP and could easily assign it to a device. Virtual IP or as some cloud providers calls it floating IP is supposed to float between those two HA proxys!

vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    interface eth0
    virtual_router_id 51


    state MASTER
    priority 102

    virtual_ipaddress {
      10.10.0.218
  }
  track_script {
    chk_haproxy
  }
}

HA PROXY NODE 2

vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    interface eth0
    virtual_router_id 51


    state SLAVE
    priority 101

    virtual_ipaddress {
      10.10.0.218
  }
  track_script {
    chk_haproxy
  }
}

After adding the configuration to both severs start the keepalived service and check the status.

Bash
systemctl start keepalived
systemctl status keepalived

The “MASTER” server HA proxy 1 should get 1 extra IP, the his the virtual IP that keepalived will use, to check that run “ip a” on the master ha proxy sevrer. The result should be somthing like this:

Bash

2: eth0:  mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 0e:fd:d7:5e:26:da brd ff:ff:ff:ff:ff:ff
    altname enp0s18
    altname ens18
    inet 10.10.0.217/24 brd 10.10.0.255 scope global dynamic eth0
       valid_lft 84885sec preferred_lft 84885sec
    inet 10.10.0.218/32 scope global eth0   < THIS IS MY VIRTUAL IP I ADDED IN THE CONFIG!
       valid_lft forever preferred_lft forever
    inet6 fe80::cfd:d7ff:fe5e:26da/64 scope link
       valid_lft forever preferred_lft forever

Now when you try to access the HA proxy you will use the Virtual IP that IP will always point to the master HA proxy and manage the failover in case master dies the slave will take over as a master role. In my case I would use 10.10.0.218 to access the Patroni cluster!

Bash
psql -h 10.10.0.218 -U postgres

Enable Patroni service if everything is OK

Run on both nodes to make sure Patroni service starts if the server reboots.

Bash
systemctl enable patroni.service

Troubleshooting:

If you receive error that your patroni can’t talk to etcd this is ussaly because of the dns, add dns records or add the hosts manually in /etc/hosts

On node 1 and node 2

Bash
nano /etc/hosts
Bash
10.10.0.220 etcd1
10.10.0.221 etcd2

You can now test ping etcd1 and the name should resolve to the IP. You can also change in the patroni.yml from IP to dns name in the etcd hosts:

From: hosts: 10.10.0.220:2379, 10.10.0.221:2379
To: hosts: etcd1:2379, etcd2:2379

Restart the patroni service and check the status again.

Useful commands for Patroni:

patronictl -c /etc/patroni.yml list

Bash
Cluster: postgres (7294370029723403248) -----+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+----------+-------------+---------+-----------+----+-----------+
| node1 | 10.10.0.181 | Leader | running | 2 | |
| node2 | 10.10.0.182 | Replica | streaming | 2 | 0 |
+----------+-------------+---------+-----------+----+-----------+

This shows the status of the cluster and who is the leader.

To force switchover run:

Bash
patronictl -c /etc/patroni.yml switchover

To create exact same setup you could use Digital Ocean for that, signing up with my referral link will give you 200$ for free to try out the platform!
Click on the box below to use my referral link or click here

DigitalOcean Referral Badge
Post Views: 18,177

By dem-linux

Related Post

One thought on “SET UP PATRONI POSTGRESQL 16 HA CLUSTER”

Leave a Reply

Your email address will not be published. Required fields are marked *


You Missed

Cassandra

Setup multi node Cassandra Cluster Debian

WordPress Mysql

Install WordPress on Debian 12/13

VPN

Bypass Netflix sharing policy with WireGuard

PosgresSQL

SET UP PATRONI POSTGRESQL 16 HA CLUSTER