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:

Servers	Applications	IP Address
server1	Patroni, PostgreSQL	172.16.0.101
server2	Patroni, PostgreSQL	172.16.0.102
server3	ETCD	172.16.0.103
server4	HAProxy	172.16.0.104

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

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.target

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

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

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.

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.