Posted on by Julian Markwort

Patroni is a cluster manager used to customize and automate deployment and maintenance of PostgreSQL HA (High Availability) clusters. It uses distributed configuration stores like etcd, Consul, ZooKeeper or Kubernetes for maximum accessibility.

In this tutorial, we will be using a single local etcd instance and two local Patroni instances on a single host instead of a more complex multi-host setup.

A simple Patroni cluster based on etcd, managing two highly-available PostgreSQL instances
A simple Patroni cluster based on etcd, managing two highly-available PostgreSQL instances

 

I assume that you’re comfortable with PostgreSQL Streaming Replication, so let’s proceed with the installation.
If you’re not sure what this all is about, I highly recommend giving this brief introduction a read:
PostgreSQL High-Availability and Patroni – an Introduction.

Install Requirements

A Patroni cluster requires not only executables for Patroni, but also for PostgreSQL of some version (at least 9.5 or above) and the configuration store of your choice (we’ll use etcd).

Cybertec provides .rpm packages of Patroni 1.6.0 for both Fedora 30 and CentOS/RHEL 7 on Github.

Fedora

# install postgresql.org repo
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-30-x86_64/pgdg-fedora-repo-latest.noarch.rpm
dnf install postgresql11 postgresql11-server
dnf install etcd
# get patroni package from github
wget https://github.com/cybertec-postgresql/patroni-packaging/releases/download/1.6.0-1/patroni-1.6.0-1.fc30.x86_64.rpm
dnf install patroni-1.6.0-1.fc30.x86_64.rpm

CentOS/RHEL 7

# install postgresql.org repo
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql11 postgresql11-server
yum install etcd
#install epel, for python36-psycopg2 needed by patroni 
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
# get patroni package from github
wget https://github.com/cybertec-postgresql/patroni-packaging/releases/download/1.6.0-1/patroni-1.6.0-1.rhel7.x86_64.rpm
dnf install patroni-1.6.0-1.rhel7.x86_64.rpm

Ubuntu 19.04/Debian Buster

# Ubuntu 19.04 and Debian Buster already have PostgreSQL 11
apt update
apt install --no-install-recommends postgresql-11 postgresql-client-11
apt install etcd
apt install patroni

Start etcd

Here we start a single member etcd cluster.
This etcd will listen to client requests on port 2379 and stores data in the default directory: ./default.etcd .

etcd > etcd_logfile 2>&1 &

To check the etcd member list, run:

etcdctl member list

Please be aware that this is only a basic setup guide. In a production environment, a single member etcd cluster would be a problematic failure point. If that etcd stops working, Patroni would have to stop the primary and secondary PostgreSQL instances.
There will be another post describing the details of etcd and how to set up clusters containing at least three members, which should be the minimum for a production setup.

Configuring Patroni

Everything that we create and modify from now on should be done by the user postgres, as postgres should own the config files, data directories and the Patroni and PostgreSQL processes.

Each Patroni instance needs its own config file. This config file tells it where to contact the DCS (Distributed Configuration Store – etcd in our case), where and how to start the database and how to configure it.

For simplicity, sample config files are provided for this tutorial for both Debian/Ubuntu and Fedora/RHEL/CentOS.
The REST API port and postgresql port are different in the config file for the second cluster member to avoid conflicts since we are running two members on the same host.

You can find the explanation of the fields in the patroni documentation .

Starting Patroni Cluster

Please note that the provided config files differ in two aspects: the directory that contains binaries for PostgreSQL and the directory that contains your database’s data.
So please, use the files that match your distribution, as shown in the wget calls below.

To start both nodes:

Fedora/RHEL/CentOS:

sudo -iu postgres
wget https://download.cybertec-postgresql.com/patroni_example_configs/rhel_patroni_1.yml
wget https://download.cybertec-postgresql.com/patroni_example_configs/rhel_patroni_2.yml
patroni rhel_patroni_1.yml > patroni_member_1.log 2>&1 &
patroni rhel_patroni_2.yml > patroni_member_2.log 2>&1 &

Debian/Ubuntu:

Before starting the Patroni cluster, make sure the default PostgreSQL cluster has been stopped and disabled.
This can usually be done with a service postgresql stop .

sudo -iu postgres
wget https://download.cybertec-postgresql.com/patroni_example_configs/deb_patroni_1.yml
wget https://download.cybertec-postgresql.com/patroni_example_configs/deb_patroni_2.yml
patroni deb_patroni_1.yml > patroni_member_1.log 2>&1 &
patroni deb_patroni_2.yml > patroni_member_2.log 2>&1 &

If you examine the produced logfiles, you will find that one of the patroni processes has bootstrapped (i.e. ran initdb) and started as a leader.

2019-07-30 13:45:45,168 INFO: Selected new etcd server http://localhost:2379
2019-07-30 13:45:45,193 INFO: Lock owner: None; I am member_1
2019-07-30 13:45:45,198 INFO: trying to bootstrap a new cluster
...
2019-07-30 13:45:47,260 INFO: postmaster pid=25866
...
2019-07-30 13:45:48,328 INFO: establishing a new patroni connection to the postgres cluster
2019-07-30 13:45:48,336 INFO: running post_bootstrap
2019-07-30 13:45:48,361 INFO: initialized a new cluster
2019-07-30 13:45:58,350 INFO: Lock owner: member_1; I am member_1
2019-07-30 13:45:58,360 INFO: Lock owner: member_1; I am member_1
2019-07-30 13:45:58,437 INFO: no action.  i am the leader with the lock
2019-07-30 13:46:08,351 INFO: Lock owner: member_1; I am member_1
2019-07-30 13:46:08,362 INFO: no action.  i am the leader with the lock

The other process noticed that there is already another leader and configured itself to receive streaming replication from it:

2019-07-30 13:45:52,751 INFO: Selected new etcd server http://localhost:2379
2019-07-30 13:45:52,783 INFO: Lock owner: member_1; I am member_2
2019-07-30 13:45:52,784 INFO: Lock owner: member_1; I am member_2
2019-07-30 13:45:52,787 INFO: trying to bootstrap from leader 'member_1'
2019-07-30 13:45:54,616 INFO: replica has been created using basebackup
2019-07-30 13:45:54,618 INFO: bootstrapped from leader 'member_1'
2019-07-30 13:45:54,643 INFO: postmaster pid=25897
...
2019-07-30 13:45:56,725 INFO: Lock owner: member_1; I am member_2
2019-07-30 13:45:56,731 INFO: Lock owner: member_1; I am member_2
2019-07-30 13:45:56,731 INFO: does not have lock
2019-07-30 13:45:56,731 INFO: establishing a new patroni connection to the postgres cluster
2019-07-30 13:45:56,747 INFO: no action.  i am a secondary and i am following a leader
2019-07-30 13:45:58,365 INFO: Lock owner: member_1; I am member_2
2019-07-30 13:45:58,366 INFO: does not have lock
2019-07-30 13:45:58,369 INFO: no action.  i am a secondary and i am following a leader

Verify Patroni Cluster

Check the status of the cluster with patronictl:

patronictl -d etcd://localhost:2379 list patroni_cluster_1

+---------+-------------+----------------+--------+---------+----+-----------+
| Cluster |    Member   |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+-------------+----------------+--------+---------+----+-----------+
|  patroni_cluster_1 | member_1 | 127.0.0.1:5432 | Leader | running |  1 |       0.0 |
|  patroni_cluster_1 | member_2 | 127.0.0.1:5433 |        | running |  1 |       0.0 |
+---------+-------------+----------------+--------+---------+----+-----------+

Now you can simply connect to your new database using the port where your cluster’s leader currently is:

psql -p 5432 -h 127.0.0.1

Conclusion

The steps above can be easily modified to create a cluster that spans several hosts.
Just change the IP addresses for the advertised and initial cluster arguments for etcd and for Patroni and PostgreSQL in the Patroni config files and make sure that all necessary ports are opened, if you’re running systems with a firewall.

Once the key concept of Patroni is well understood, it is easy to deploy and manage bigger clusters.

This post is part of a series.
Besides this post the following articles have already been published:
– PostgreSQL High-Availability and Patroni – an Introduction.
The series will also cover:
– setting up multi-member etcd clusters
– configuration and troubleshooting
– failover, maintenance, and monitoring
– client connection handling and routing
– WAL archiving and database backups using pgBackrest
– PITR a patroni cluster using pgBackrest

Updates to the series will be published regularly. Check back soon for the post on setting up multi-member etcd clusters!

Julian Markwort

Julian Markwort is a junior consultant and developer at CYBERTEC. He began his endeavour into the PostgreSQL community around 2015 during a student job at Münster University, where he continues his quest to acquire a Master of Science, just for good measure. While helping global corporations run Patroni clusters and developing and building database-related things.