How to Set Up PostgreSQL Cluster using Patroni on Ubuntu 20.04

PostgreSQL is a free, open-source and most advanced database management system around the world. It is a powerful and object-relational database that extends the SQL language with many features that scale the most complicated data workloads.

Patroni is a cluster manager used to automate the deployment and maintenance of PostgreSQL HA clusters. It is written in Python and supports "Distributed Configuration Store" including, Zookeeper, etcd, Consul and Kubernetes. It supports streaming and synchronous replication and provides REST APIs for the dynamic configuration of the PostgreSQL cluster.

In this tutorial, we will explain how to set up PostgreSQL Cluster using Patroni on Ubuntu 20.04

# Prerequisites

• Two servers for Patroni running Ubuntu 20.04.
• One server for ETCD running Ubuntu 20.04.
• One server for HAProxy running Ubuntu 20.04.
• A root password is set on each server.

For the purpose of this tutorial, we will use the following setup:

# Install PostgreSQL

First, you will need to install the PostgreSQL server on both server1 and server2. You can install it on both servers with the following command:

apt-get install postgresql postgresql-contrib -y

Once the installation has been finished, stop the PostgreSQL service on both servers:

systemctl stop postgresql

Next, create a symlink of /usr/lib/postgresql/12/bin/ to /usr/sbin as it contains some tools used for Patroni.

ln -s /usr/lib/postgresql/12/bin/* /usr/sbin/

# Install Patroni

Before installing Patroni, you will need to install Python and other dependencies on both server1 and server2.

apt-get install python3-pip python3-dev libpq-dev -y

Once all the dependencies are installed, upgrade PIP to the latest version with the following command:

pip3 install --upgrade pip

Next, install the Patroni with the following command:

pip install patroni

Next, install other required dependencies with the following command:

pip install python-etcd
pip install psycopg2

Note: Run all of the above commands on both server1 and server2.

# Install Etcd and HAProxy

Next, you will need to install Etcd to store the data across a cluster of machines. You can install the Etcd on the server3 with the following command:

apt-get install etcd -y

Next, you will need to install the HAProxy to forward the connection from Master to Slave or Slave to Master. You can install it on server4 with the following command:

apt-get install haproxy -y

# Configure Etcd

Next, you will need to configure Etcd on server3. You can configure it by editing the file /etc/default/etcd:

nano /etc/default/etcd

Add the following lines:

ETCD_LISTEN_PEER_URLS="http://172.16.0.103:2380,http://127.0.0.1:7001"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379, http://172.16.0.103:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.16.0.103:2380"
ETCD_INITIAL_CLUSTER="etcd0=http://172.16.0.103:2380,"
ETCD_ADVERTISE_CLIENT_URLS="http://172.16.0.103:2379"
ETCD_INITIAL_CLUSTER_TOKEN="cluster1"
ETCD_INITIAL_CLUSTER_STATE="new"

Save and close the file then restart the Etcd service to apply the changes:

systemctl restart etcd

You can also verify the status of the service using the following command:

systemctl status etcd

Output:

● etcd.service - etcd - highly-available key value store
     Loaded: loaded (/lib/systemd/system/etcd.service; enabled; vendor preset: >
     Active: active (running) since Fri 2021-02-19 14:24:05 UTC; 4s ago
       Docs: https://github.com/coreos/etcd
             man:etcd
   Main PID: 1461 (etcd)
      Tasks: 9 (limit: 1145)
     Memory: 20.0M
     CGroup: /system.slice/etcd.service
              └─1461 /usr/bin/etcd

Feb 19 14:24:05 etcd etcd[1461]: 8e9e05c52164694d became candidate at term 3
Feb 19 14:24:05 etcd etcd[1461]: 8e9e05c52164694d received MsgVoteResp from 8e9>
Feb 19 14:24:05 etcd etcd[1461]: 8e9e05c52164694d became leader at term 3
Feb 19 14:24:05 etcd etcd[1461]: raft.node: 8e9e05c52164694d elected leader 8e9>
Feb 19 14:24:05 etcd etcd[1461]: published {Name:etcd ClientURLs:[http://45.58.>
Feb 19 14:24:05 etcd systemd[1]: Started etcd - highly-available key value stor>
Feb 19 14:24:05 etcd etcd[1461]: ready to serve client requests
Feb 19 14:24:05 etcd etcd[1461]: serving insecure client requests on 127.0.0.1:>
Feb 19 14:24:05 etcd etcd[1461]: ready to serve client requests
Feb 19 14:24:05 etcd etcd[1461]: serving insecure client requests on 45.58.43.5>

# Configure Patroni

Patroni uses a YML file to store its configuration. You will need to create a patroni.yml file on both server1 and server2:

You can create it on both servers with the following command:

nano /etc/patroni.yml

Add the following lines:

Note: replace listen, connect_address value with each server IP:

scope: postgres
namespace: /db/
name: postgresql0

restapi:
    listen: 172.16.0.101:8008
    connect_address: 172.16.0.101:8008

etcd:
    host: 172.16.0.103: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 172.16.0.101/0 md5
    - host replication replicator 172.16.0.102/0 md5
    - host all all 0.0.0.0/0 md5

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

 postgresql:
    listen: 172.16.0.101:5432
    connect_address: 172.16.0.101:5432
    data_dir: /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

Save and close the file then create a data directory for Patroni and set proper ownership and permissions on both servers:

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

# Create a Systemd Service File for Patroni

Next, you will need to create a systemd service file to manage the Patroni service on both servers. You can create it with the following command:

nano /etc/systemd/system/patroni.service

Add the following lines:

[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.targ

Save and close the file then reload the systemd daemon with the following command:

systemctl daemon-reload

Next, start the Patroni and PostgreSQL service on both servers:

systemctl start patroni
systemctl start postgresql

You can also check the status of Patroni with the following command:

systemctl status patroni

Output:

● patroni.service - Runners to orchestrate a high-availability PostgreSQL
     Loaded: loaded (/etc/systemd/system/patroni.service; disabled; vendor preset: enabled)
     Active: active (running) since Fri 2021-02-19 14:31:19 UTC; 12s ago
   Main PID: 11883 (patroni)
      Tasks: 13 (limit: 2353)
     Memory: 93.5M
     CGroup: /system.slice/patroni.service
             ├─11883 /usr/bin/python3 /usr/local/bin/patroni /etc/patroni.yml
             ├─11922 postgres -D /data/patroni --config-file=/data/patroni/postgresql.conf --listen_addresses=172.16.0.101 --port=5432 --clu>
             ├─11927 postgres: postgres: checkpointer
             ├─11928 postgres: postgres: background writer
             ├─11929 postgres: postgres: walwriter
             ├─11930 postgres: postgres: autovacuum launcher
             ├─11931 postgres: postgres: stats collector
             ├─11932 postgres: postgres: logical replication launcher
             └─11934 postgres: postgres: postgres postgres 172.16.0.101(45404) idle

Feb 19 14:31:21 patroni1 patroni[11922]: 2021-02-19 14:31:21.717 UTC [11922] LOG:  database system is ready to accept connections
Feb 19 14:31:21 patroni1 patroni[11926]: 172.16.0.101:5432 - accepting connections
Feb 19 14:31:21 patroni1 patroni[11883]: 2021-02-19 14:31:21,729 INFO: establishing a new patroni connection to the postgres cluster
Feb 19 14:31:21 patroni1 patroni[11883]: 2021-02-19 14:31:21,743 INFO: running post_bootstrap
Feb 19 14:31:21 patroni1 patroni[11883]: 2021-02-19 14:31:21,756 WARNING: Could not activate Linux watchdog device: "Can't open watchdog devic>
Feb 19 14:31:21 patroni1 patroni[11883]: 2021-02-19 14:31:21,778 INFO: initialized a new cluster
Feb 19 14:31:31 patroni1 patroni[11883]: 2021-02-19 14:31:31,758 INFO: Lock owner: postgresql0; I am postgresql0
Feb 19 14:31:31 patroni1 patroni[11883]: 2021-02-19 14:31:31,770 INFO: no action.  i am the leader with the lock
Feb 19 14:31:31 patroni1 patroni[11883]: 2021-02-19 14:31:31,794 INFO: Lock owner: postgresql0; I am postgresql0

# Configure HAProxy

Next, you will need to configure HAProxy on server4. You can configure it by editing its main configuration file:

nano /etc/haproxy/haproxy.cfg

Remove all the lines and add the following lines:

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 /

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 postgresql_172.16.0.101_5432 172.16.0.101:5432 maxconn 100 check port 8008
    server postgresql_172.16.0.102_5432 172.16.0.102:5432 maxconn 100 check port 8008

Save and close the file then restart the HAProxy service to apply the changes:

systemctl restart haproxy

You can verify the status of the service using the following command:

systemctl status haproxy

Output:

● haproxy.service - HAProxy Load Balancer
     Loaded: loaded (/lib/systemd/system/haproxy.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2021-02-19 14:46:54 UTC; 17s ago
       Docs: man:haproxy(1)
             file:/usr/share/doc/haproxy/configuration.txt.gz
     Process: 1620 ExecStartPre=/usr/sbin/haproxy -f $CONFIG -c -q $EXTRAOPTS (code=exited, status=0/SUCCESS)
    Main PID: 1631 (haproxy)
      Tasks: 2 (limit: 1145)
     Memory: 2.0M
     CGroup: /system.slice/haproxy.service
             ├─1631 /usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -S /run/haproxy-master.sock
             └─1632 /usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -S /run/haproxy-master.sock

Feb 19 14:46:54 haproxy haproxy[1631]: [WARNING] 049/144654 (1631) : parsing [/etc/haproxy/haproxy.cfg:26] : 'option httplog' not usable with >
Feb 19 14:46:54 haproxy haproxy[1631]: Proxy stats started.
Feb 19 14:46:54 haproxy haproxy[1631]: Proxy stats started.
Feb 19 14:46:54 haproxy haproxy[1631]: Proxy postgres started.

# Verify PostgreSQL Cluster

At this point, the PostgreSQL cluster is ready. Now, open your web browser and use the HAProxy server IP at http://172.16.0.104:7000 to access the HAProxy web interface. You should see the cluster status in the following image:

In the postgresql section, you should see that the first row is highlighted in green that means it is acting as the master, and the other row highlighted in red is acting as a slave.

# Conclusion

In the above guide, you learned how to set up a high availability PostgreSQL cluster with Patroni on Ubuntu 20.04. You can now add more nodes to increase availability.