Patroni and ETCD provide a great combination for a cluster solution for a PostgreSQL. In a nutshell: Patroni managed the cluster while the ETCD allows configuration data to be stored. The diagram of how they interact with PostgreSQL you can see below:

So let's get started with the configuration of ETCD first. To configure ETCD, we need to:

1. Install the package
2. Configure the cluster

Both tasks are fairly simple:

Let's assume we have three nodes (as the picture above)

• node00 (10.100.10.200)
• node01 (10.100.10.201)
• node02 (10.100.10.202)

I like starting from “0” I know To install the package, simply use the package provider: yum, apt-get, etc:

node02:$ yum -y install etcd
node02: --> Running transaction check
node02: ---> Package etcd.x86_64 0:3.3.11-2.el7.centos will be installed
node02: --> Finished Dependency Resolution
node02:
node02: Dependencies Resolved
node02:
node02: ================================================================================
node02:  Package      Arch           Version                       Repository      Size
node02: ================================================================================
node02: Installing:
node02:  etcd         x86_64         3.3.11-2.el7.centos           extras          10 M
node02:
node02: Transaction Summary
node02: ================================================================================
node02: Install  1 Package
node02:
node02: Total download size: 10 M
node02: Installed size: 45 M
node02: Downloading packages:
node02: Running transaction check
node02: Running transaction test
node02: Transaction test succeeded
node02: Running transaction
node02:   Installing : etcd-3.3.11-2.el7.centos.x86_64                              1/1
node02:   Verifying  : etcd-3.3.11-2.el7.centos.x86_64                              1/1
node02:
node02: Installed:
node02:   etcd.x86_64 0:3.3.11-2.el7.centos
node02:
node02: Complete!

You need to do that all nodes

To configure the cluster, use the following script:

mkdir -p /db/etcd
host=`hostname`
cat > /etc/etcd/etcd.conf <<EOF
name: ${host}
data-dir: /db/etcd 
initial-advertise-peer-urls: http://${host}:2380 
listen-peer-urls: http://0.0.0.0:2380 
listen-client-urls: http://0.0.0.0:2379 
advertise-client-urls: http://${host}:2379 
initial-cluster: "node00=http://node00:2380,node01=http://node01:2380,node02=http://node02:2380"
EOF

mkdir -p /var/log/postgresql

Again, you need to run that on all 3 nodes, after that we can start the ETCD

[root@node00 ~]# etcd --config-file /etc/etcd/etcd.conf &>/var/log/postgresql/etcd.log &
[1] 5536
[root@node00 ~]#

Again, on all nodes. We can after that check the status of the cluster:

[root@node00 postgresql]# etcdctl cluster-health
member 4fd4358d80b0e45e is healthy: got healthy result from http://node00:2379
member 825e916a3f48ca3f is healthy: got healthy result from http://node01:2379
member ce1dafd3ad7d9971 is healthy: got healthy result from http://node02:2379
cluster is healthy
[root@node00 postgresql]#

You can also “TEST” the server as publishing messages (key-values)

#On 2nd node:
[root@node01 ~]# etcdctl put ha-cookbook-2 "Hello World"
OK
[root@node01 ~]#

#On 1st node:
[root@node00 etcd]# etcdctl get ha-cookbook-2
ha-cookbook-2
Hello World
[root@node00 etcd]#

So we have our key-value propagated among the cluster, nice….

The Patroni is really the brain behind the cluster management and the coordination with the distributed key store (in our case ETCD, although other solutions are possible as well: Consul, K8S, etc) Patroni is a fork of Governor and its job is to manage the cluster. With patroni you can also reinitialize a node, as we will see below. To sum it up, the configuration of Patroni, you can use the following script:

yum -y install centos-release-scl-rh
yum -y install llvm-toolset-7-clang gcc python3-devel postgresql15-devel.x86_64
pip3 install patroni
pip3 install python-etcd
mkdir /etc/patroni
mkdir -p /db/pgdata 
mkdir -p /var/log/postgresql/


chown -R postgres:postgres /etc/patroni
chown postgres:postgres /db/pgdata
chmod 600 /etc/patroni/stampede.yml
chown postgres:postgres /var/log/postgresql/
chown postgres:postgres /db

host=`hostname`
binexec=`find / -name "pg_config"`
bindir=`${binexec} --bindir`
ipaddr=`cat /etc/hosts | grep "${host}" | cut -d' ' -f1`
subnet=`echo ${ipaddr} | cut -d'.' -f1-3`

cat > /etc/patroni/stampede.yml <<EOF
scope: stampede 
name: ${host} 
 
restapi: 
  listen: ${host}:8008 
  connect_address: ${host}:8008 
 
etcd: 
  host: ${host}:2379 
 
bootstrap: 
  dcs: 
    ttl: 30 
    loop_wait: 10 
    retry_timeout: 10 
    maximum_lag_on_failover: 1048576 
    postgresql: 
      use_pg_rewind: true 
      use_slots: true 
      parameters: 
        wal_level: logical 
        wal_log_hints: "on" 
 
  initdb: 
  - encoding: UTF8 
  - data-checksums 
 
  pg_hba: 
  - host replication rep_user ${ipaddr}/24 md5 
  - host all all ${subnet}.0/24 md5 
 
  users: 
    admin: 
      password: adminpass 
      options: 
        - createrole 
        - createdb 
 
postgresql: 
  listen: ${host}:5432 
  connect_address: ${host}:5432 
  data_dir: /db/pgdata 
  bin_dir: ${bindir}
  pgpass: /tmp/pgpass0 
  authentication: 
    replication: 
      username: rep_user 
      password: newpass 
    superuser: 
      username: postgres 
      password: newpass 
  parameters: 
    unix_socket_directories: '/var/run/postgresql' 
    external_pid_file: '/var/run/postgresql/15-main.pid' 
    logging_collector: "on" 
    log_directory: "/var/log/postgresql" 
    log_filename: "postgresql-15-main.log" 
EOF

chmod 750 /db/pgdata
chown postgres:postgres /var/log/postgresql/patroni.log
patroni /etc/patroni/stampede.yml &> /var/log/postgresql/patroni.log &

Execute that command on all servers in the cluster. You don't have to substitute anything. Everything is self generated (I like twerking with bash). On the first server, you just need to create the replication user:

postgres=# create role rep_user with replication login password 'newpass';
CREATE ROLE
postgres=#

After that, you should have all 3 nodes working together:

-bash-4.2$ patronictl -c /etc/patroni/stampede.yml list stampede
+ Cluster: stampede --------+---------+----+-----------+
| Member | Host   | Role    | State   | TL | Lag in MB |
+--------+--------+---------+---------+----+-----------+
| node00 | node00 | Leader  | running |  2 |           |
| node01 | node01 | Replica | running |  2 |         0 |
| node02 | node02 | Replica | running |  2 |         0 |
+--------+--------+---------+---------+----+-----------+
-bash-4.2$

Now, we can see that the node: “node00” is the leader and the rest are Replicas.

Let's test a failover. We will halt the first node:

[root@node00 wal]# halt

Don't forget, that Patroni is checking if there is a Leader node every 5 seconds or so. So it should see that we are Leaderless and promote a replica:

-bash-4.2$ patronictl -c /etc/patroni/stampede.yml list stampede
+ Cluster: stampede --------+---------+----+-----------+
| Member | Host   | Role    | State   | TL | Lag in MB |
+--------+--------+---------+---------+----+-----------+
| node00 | node00 | Leader  | stopped |    |           |
| node01 | node01 | Replica | running |  2 |         0 |
| node02 | node02 | Replica | running |  2 |         0 |
+--------+--------+---------+---------+----+-----------+

We see that we have stopped server, so let's see again:

-bash-4.2$ patronictl -c /etc/patroni/stampede.yml list stampede
+ Cluster: stampede --------+---------+----+-----------+
| Member | Host   | Role    | State   | TL | Lag in MB |
+--------+--------+---------+---------+----+-----------+
| node00 | node00 | Replica | stopped |    |   unknown |
| node01 | node01 | Replica | running |  2 |         0 |
| node02 | node02 | Leader  | running |  2 |           |
+--------+--------+---------+---------+----+-----------+

Yeyyy, after some time, we have a new Leader. Off we go.

Of course, with every software, we have to manage it. So let's what issues I had. Well, I had many issues, but one of the most important ones was that Node02 was ahead of the Node00 for some reason, so I had to reinitialize Node02:

Re-initializing is quite simple. It will pretty much do a pg_basebackup and restore it on the node:

-bash-4.2$ patronictl -c /etc/patroni/stampede.yml reinit stampede node02
+ Cluster: stampede --------+---------+----+-----------+
| Member | Host   | Role    | State   | TL | Lag in MB |
+--------+--------+---------+---------+----+-----------+
| node00 | node00 | Leader  | running |  2 |           |
| node01 | node01 | Replica | running |  2 |         0 |
| node02 | node02 | Replica | running |  2 |         0 |
+--------+--------+---------+---------+----+-----------+
Are you sure you want to reinitialize members node02? [y/N]: y
Success: reinitialize for member node02
-bash-4.2$

That was necessary because, even though, node02 was “running” it wasn't in sync with the leader. After the re-initialization, all is fixed.