Do we need one more Patroni tut?

Let's face it, there are quite enough tutorials published on how to set up the Patroni cluster. This one is covering deployment in a docker swarm with Zookeeper as a DCS. So why zookeeper and why docker swarm?

Why Zookeeper?

Actually, it's something you might want to consider seriously choosing a Patroni setup for your production.

The thing is that Patroni uses third-party services basically to establish and maintain communication among its nodes, the so-called DCS (Dynamic Configuration Storage).

If you have already studied tutorials on Patroni you probably noticed that the most common case is to implement communication through the 'etcd' cluster.

The notable thing about etcd is here (from its faq page):

Since etcd writes data to disk, its performance strongly depends on disk
performance. For this reason, SSD is highly recommended. 

If you don't have SSD on each machine you are planning to run your etcd cluster, it's probably not a good idea to choose it as a DCS for Patroni. In a real production scenario, it is possible that you simply overwhelm your etcd cluster, which might lead to IO errors. Doesn't sound good, right?

So here comes Zookeeper which stores all its data in memory and might actually come in handy if your servers lack SDDs but have got plenty of RAM.

Why docker swarm?

In my situation, I had no other choice as it was one of the business requirements to set it up in a docker swarm. So if by circumstances it's your case as well, you're exactly in the right spot!

But for the rest of the readers with the "testing and trying" purposes, it comes across as quite a distant choice too as you don't need to install/prepare any third-party services (except for docker of course) or place on your machine dozens of dependencies.

Guess it's not far from the truth that we all have Docker engine installed and set up everywhere anyway and it's convenient to keep everything in containers. With one-command tuning, docker is good enough to run your first Patroni cluster locally without virtual machines, Kubernetes, and such.

So if you don't want to dig into other tools and want to accomplish everything neat and clean in a well-known docker environment this tutorial could be the right way to go.

Some extra

In this tutorial, I'm also planning to show various ways to check on the cluster stats (to be concrete will cover all 3 of them) and provide a simple script and strategy for a test run.

Suppose it's enough of talking, let's go ahead and start practicing.

version: '3.7'

services:
  zoo1:
    image: zookeeper:3.4
    hostname: zoo1
    ports:
      - 2191:2181
    networks:
      - patroni
    environment:
      ZOO_MY_ID: 1
      ZOO_SERVERS: server.1=0.0.0.0:2888:3888 server.2=zoo2:2888:3888 server.3=zoo3:2888:3888
    deploy:
      replicas: 1
      placement:
        constraints:
          - node.hostname == floitet
      restart_policy:
        condition: any

  zoo2:
    image: zookeeper:3.4
    hostname: zoo2
    networks:
      - patroni
    ports:
      - 2192:2181
    environment:
      ZOO_MY_ID: 2
      ZOO_SERVERS: server.1=zoo1:2888:3888 server.2=0.0.0.0:2888:3888 server.3=zoo3:2888:3888
    deploy:
      replicas: 1
      placement:
        constraints:
          - node.hostname == floitet
      restart_policy:
        condition: any

  zoo3:
    image: zookeeper:3.4
    hostname: zoo3
    networks:
      - patroni
    ports:
      - 2193:2181
    environment:
      ZOO_MY_ID: 3
      ZOO_SERVERS: server.1=zoo1:2888:3888 server.2=zoo2:2888:3888 server.3=0.0.0.0:2888:3888
    deploy:
      replicas: 1
      placement:
        constraints:
          - node.hostname == floitet
      restart_policy:
        condition: any

networks:
  patroni:
    driver: overlay
    attachable: true

The important thing of course is to give every node its unique service name and published port. The hostname is preferably be set to the same as the service name.

zoo1:
    image: zookeeper:3.4
    hostname: zoo1
    ports:
      - 2191:2181

Notice how we list servers in this line, changing the service we bind depending on the service number. So for the first (zoo1) service server.1 is bound to 0.0.0.0, but for zoo2 it will be the server.2 accordingly.

ZOO_SERVERS: server.1=0.0.0.0:2888:3888 server.2=zoo2:2888:3888 server.3=zoo3:2888:3888

This is how we control the deployment among nodes. As we have only one node, we set a constraint to this node for all services. When you have multiple nodes in your docker swarm cluster and want to spread services among all nodes - just replace node.hostname with the name of the desirable node (use 'docker node ls' command).

      placement:
        constraints:
          - node.hostname == floitet

And the final thing we need to take care of is the network. We're going to deploy Zookeeper and Patroni clusters in one overlay network so that they could communicate with each other in an isolated environment using the service names.

networks:
  patroni:
    driver: overlay
// we need to mark this network as attachable 
// so that to be able to connect patroni services to this network later on 
    attachable: true

scope: patroni
namespace: /service/

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

    postgresql:
      use_pg_rewind: true

    initdb:
    - encoding: UTF8
    - data-checksums

    pg_hba:
    - host replication all all md5
    - host all all all md5

zookeeper:
  hosts: 
      - zoo1:2181
      - zoo2:2181
      - zoo3:2181

postgresql:
    data_dir: /data/patroni
    bin_dir: /usr/lib/postgresql/11/bin
    pgpass: /tmp/pgpass
    parameters:
        unix_socket_directories: '.'

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

What we should be aware of is that we need to specify 'bin_dir' correctly for Patroni to find Postgres binaries. In my use case, I have Postgres 11 so my directory looks like this: '/usr/lib/postgresql/11/bin'. This is the directory Patroni is going to be looking for an inside container. And 'data_dir' is where the data will be stored inside the container. Later on, we'll bind it to the actual volume on our drive so that not to lose all the data if the Patroni cluster for some reason fails.

postgresql:
    data_dir: /data/patroni
    bin_dir: /usr/lib/postgresql/11/bin

I also list all the zookeeper servers here to feed them to patronictl later. Note that, If you don't specify it here we'll end up with a broken patroni command tool (patronictl). Also, I'd like to point out that we don't use IPs to locate zookeeper servers, but we feed Patroni with 'service names' instead. It's a feature of a docker swarm we are taking advantage of.

zookeeper:
  hosts: 
      - zoo1:2181
      - zoo2:2181
      - zoo3:2181

#!/bin/sh

readonly CONTAINER_IP=$(hostname --ip-address)
readonly CONTAINER_API_ADDR="${CONTAINER_IP}:${PATRONI_API_CONNECT_PORT}"
readonly CONTAINER_POSTGRE_ADDR="${CONTAINER_IP}:5432"

export PATRONI_NAME="${PATRONI_NAME:-$(hostname)}"
export PATRONI_RESTAPI_CONNECT_ADDRESS="$CONTAINER_API_ADDR"
export PATRONI_RESTAPI_LISTEN="$CONTAINER_API_ADDR"
export PATRONI_POSTGRESQL_CONNECT_ADDRESS="$CONTAINER_POSTGRE_ADDR"
export PATRONI_POSTGRESQL_LISTEN="$CONTAINER_POSTGRE_ADDR"
export PATRONI_REPLICATION_USERNAME="$REPLICATION_NAME"
export PATRONI_REPLICATION_PASSWORD="$REPLICATION_PASS"
export PATRONI_SUPERUSER_USERNAME="$SU_NAME"
export PATRONI_SUPERUSER_PASSWORD="$SU_PASS"
export PATRONI_approle_PASSWORD="$POSTGRES_APP_ROLE_PASS"
export PATRONI_approle_OPTIONS="${PATRONI_admin_OPTIONS:-createdb, createrole}"

exec /usr/local/bin/patroni /etc/patroni.yml

Actually, the main point of even having this patroni_entrypoint.sh is that Patroni won't simply start without knowing the IP address of its host. And for the host being a docker container we are in a situation where we somehow need to first get to know which IP was granted to the container and only then execute the Patroni start-up command. This indeed crucial task is handled here

readonly CONTAINER_IP=$(hostname --ip-address)
readonly CONTAINER_API_ADDR="${CONTAINER_IP}:${PATRONI_API_CONNECT_PORT}"
readonly CONTAINER_POSTGRE_ADDR="${CONTAINER_IP}:5432"
...
export PATRONI_RESTAPI_CONNECT_ADDRESS="$CONTAINER_API_ADDR"
export PATRONI_RESTAPI_LISTEN="$CONTAINER_API_ADDR"
export PATRONI_POSTGRESQL_CONNECT_ADDRESS="$CONTAINER_POSTGRE_ADDR"

As you can see, in this script, we take advantage of the 'Environment configuration' available for Patroni. It's another way aside from the patroni.yml config file, where we can set the parameters. 'PATRONI_RESTAPI_CONNECTADDRESS', 'PATRONI_RESTAPI_LISTEN', 'PATRONI_POSTGRESQLCONNECT_ADDRESS' are those environment variables Patroni knows of and is applying automatically as setup parameters. And btw, they overwrite the ones set locally in patroni.yml

And here is another thing. Patroni docs do not recommend using superuser to connect your apps to the database. So we are going to this another user for connection which can be created with the lines below. It is also set through special env variables Patroni is aware of. Just replace 'approle' with the name you like to create the user with any name of your preference.

export PATRONI_approle_PASSWORD="$POSTGRES_APP_ROLE_PASS"
export PATRONI_approle_OPTIONS="${PATRONI_admin_OPTIONS:-createdb, createrole}"

And with this last line, where everything is ready for the start we execute Patroni with a link to patroni.yml

exec /usr/local/bin/patroni /etc/patroni.yml

FROM postgres:11 

RUN apt-get update -y\ 
    && apt-get install python3 python3-pip -y    && pip3 install --upgrade setuptools    && pip3 install psycopg2-binary     && pip3 install patroni[zookeeper]     && mkdir /data/patroni -p     && chown postgres:postgres /data/patroni     && chmod 700 /data/patroni 

COPY patroni.yml /etc/patroni.yml
COPY patroni_entrypoint.sh ./entrypoint.sh
USER postgres

ENTRYPOINT ["bin/sh", "/entrypoint.sh"]

The most important thing here is the directory we will be creating inside the container and its owner. Later, when we mount it to a volume on our hard drive, we're gonna need to take care of it the same way we do it here in Dockerfile.

    // the owner should be 'postgres' and the mode is 700

    mkdir /data/patroni -p     chown postgres:postgres /data/patroni     chmod 700 /data/patroni 
    ...
    // we set active user inside container to postgres
    USER postgres

The files we created earlier are copied here:

COPY patroni.yml /etc/patroni.yml
COPY patroni_entrypoint.sh ./entrypoint.sh

And like it was mentioned above at the start we want to execute our entry point script:

ENTRYPOINT ["bin/sh", "/entrypoint.sh"]

That's it for handling the pre-requisites. Now we can finally build our patroni image. Let's give it a sound name 'patroni-test':

docker build -t patroni-test .

When the image is ready we can discuss the last but not least file we're gonna need here and it's the compose file, of course.

version: "3.4"

networks:
    patroni_patroni: 
        external: true
services:
    patroni1:
        image: patroni-test
        networks: [ patroni_patroni ]
        ports:
            - 5441:5432
            - 8091:8091
        hostname: patroni1
        volumes:
          - /patroni1:/data/patroni
        environment:
            PATRONI_API_CONNECT_PORT: 8091
            REPLICATION_NAME: replicator 
            REPLICATION_PASS: replpass
            SU_NAME: postgres
            SU_PASS: supass
            POSTGRES_APP_ROLE_PASS: appass
        deploy:
          replicas: 1
          placement:
            constraints: [node.hostname == floitet]

    patroni2:
        image: patroni-test
        networks: [ patroni_patroni ]
        ports:
            - 5442:5432
            - 8092:8091
        hostname: patroni2
        volumes:
          - /patroni2:/data/patroni
        environment:
            PATRONI_API_CONNECT_PORT: 8091
            REPLICATION_NAME: replicator 
            REPLICATION_PASS: replpass
            SU_NAME: postgres
            SU_PASS: supass
            POSTGRES_APP_ROLE_PASS: appass
        deploy:
          replicas: 1
          placement:
            constraints: [node.hostname == floitet]


    patroni3:
        image: patroni-test
        networks: [ patroni_patroni ]
        ports:
            - 5443:5432
            - 8093:8091
        hostname: patroni3
        volumes:
          - /patroni3:/data/patroni
        environment:
            PATRONI_API_CONNECT_PORT: 8091
            REPLICATION_NAME: replicator 
            REPLICATION_PASS: replpass
            SU_NAME: postgres
            SU_PASS: supass
            POSTGRES_APP_ROLE_PASS: appass
        deploy:
          replicas: 1
          placement:
            constraints: [node.hostname == floitet]

The first detail that pops-up is the network thing we talked about earlier. We want to deploy the Patroni services in the same network as the Zookeeper services. This way 'zoo1', 'zoo2', 'zoo3' names we listed in patroni.yml providing zookeeper servers are going to work out for us.

networks:
    patroni_patroni: 
        external: true

As for the ports, we have a database and API and both of them require its pair of ports.

ports:
    - 5441:5432
    - 8091:8091
...

environment:
    PATRONI_API_CONNECT_PORT: 8091

// we need to make sure that we set Patroni API connect port 
// the same with the one that is set as a target port for docker service    

Of course, we also need to provide all the rest of the environment variables we kind of promised to provide configuring our entry point script for Patroni, but that's not it. There is an issue with a mount directory we need to take care of.

volumes:
   - /patroni3:/data/patroni

As you can see '/data/patroni' we create in Dockerfile is mounted to a local folder we actually need to create. And not only create but also set the proper use and access mode just like in this example:

sudo mkdir /patroni3
sudo chown 999:999 /patroni3
sudo chmod 700 /patroni3

// 999 is the default uid for postgres user 
// repeat these steps for each patroni service mount dir

global
    maxconn 100
    stats socket /run/haproxy/haproxy.sock 
    stats timeout 2m # Wait up to 2 minutes for input

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 patroni1 patroni1:5432 maxconn 100 check port 8091
    server patroni2 patroni2:5432 maxconn 100 check port 8091
    server patroni3 patroni3:5432 maxconn 100 check port 8091

Here we specify ports we want to access the service from:

// one is for stats
listen stats
    mode http
    bind *:7000
// the second one for connection to postgres
listen postgres
    bind *:5000

And simply list all the patroni services we have created earlier:

server patroni1 patroni1:5432 maxconn 100 check port 8091
server patroni2 patroni2:5432 maxconn 100 check port 8091
server patroni3 patroni3:5432 maxconn 100 check port 8091

And the last thing. This line showed below we need if we want to check our Patroni cluster stats from the Haproxy stats tool in the terminal from within a docker container:

stats socket /run/haproxy/haproxy.sock 

FROM haproxy:1.7
COPY haproxy.cfg /usr/local/etc/haproxy/haproxy.cfg
RUN mkdir /run/haproxy &&    apt-get update -y &&    apt-get install -y hatop &&    apt-get clean

version: "3.7"

networks:
  patroni_patroni:
    external: true

services:
 haproxy:
    image: haproxy-patroni
    networks:
      - patroni_patroni
    ports:
      - 5000:5000
      - 7000:7000
    hostname: haproxy
    deploy:
      mode: replicated
      replicas: 1
      placement: 
        constraints: [node.hostname == floitet]

FROM alpine:3.10

RUN apk add --no-cache curl jq bash

CMD ["/bin/sh"]

curl -s patroni1:8091/patroni | jq
{
  "patroni": {
    "scope": "patroni",
    "version": "2.0.1"
  },
  "database_system_identifier": "6893104757524385823",
  "postmaster_start_time": "2020-11-15 19:47:33.917 UTC",
  "timeline": 10,
  "xlog": {
    "received_location": 100904544,
    "replayed_timestamp": null,
    "replayed_location": 100904544,
    "paused": false
  },
  "role": "replica",
  "cluster_unlocked": false,
  "state": "running",
  "server_version": 110009
}

curl -s patroni1:8091/cluster | jq
{
  "members": [
    {
      "port": 5432,
      "host": "10.0.1.5",
      "timeline": 10,
      "lag": 0,
      "role": "replica",
      "name": "patroni1",
      "state": "running",
      "api_url": "http://10.0.1.5:8091/patroni"
    },
    {
      "port": 5432,
      "host": "10.0.1.4",
      "timeline": 10,
      "role": "leader",
      "name": "patroni2",
      "state": "running",
      "api_url": "http://10.0.1.4:8091/patroni"
    },
    {
      "port": 5432,
      "host": "10.0.1.3",
      "lag": "unknown",
      "role": "replica",
      "name": "patroni3",
      "state": "running",
      "api_url": "http://10.0.1.3:8091/patroni"
    }
  ]
}

So for the readers who want to put their hands-on testing the Patroni cluster right away with something pre-made, I created this script. It's super simple and I believe you won't have problems getting a grasp of it. Basically, it just writes the current time in the database through the haproxy gateway each second. Below I'm going to show step by step how to approach it and what was the outcome from the test-run on my local stand.

• Step 1.

Assume you've already downloaded the script from the link and put it somewhere on your machine. If not, do this preparation and follow up. From here we'll move on and create a docker container from an official Microsoft SDK image like so:

docker run --rm -ti --network=patroni_patroni -v /home/floitet/Documents/patroni-test-script:/home mcr.microsoft.com/dotnet/sdk /bin/bash

The important thing is that we get connected to the 'patroni_patroni' network. And another crucial detail is that we want to mount this container to a directory where you've put the script. This way you can easily access it from within a container in the '/home' directory.

• Step2.

Now we need to take care of getting the only dll we are going to need for our script to compile. Standing in the '/home' directory let's create a new folder for the console app. I'm gonna call it 'patroni-test'. Then cd to this directory and run the following command:

dotnet new console

// and the output be like:

Processing post-creation actions...
Running 'dotnet restore' on /home/patroni-test/patroni-test.csproj...
  Determining projects to restore...
  Restored /home/patroni-test/patroni-test.csproj (in 61 ms).
Restore succeeded.

And from here we can add the package we'll be using as a dependency for our script:

dotnet add package npgsql

And after that simply pack the project:

dotnet pack

If everything went as expected you'll get 'Ngsql.dll' sitting here:

'patroni-test/bin/Debug/net5.0/Npgsql.dll'.

It is exactly the path we reference in our script so if yours differs from mine, you're gonna need to change it in the script.

And what we do next is just run the script:

dotnet fsi /home/patroni-test.fsx

// and get the output like this: 

11/18/2020 22:29:32 +00:00
11/18/2020 22:29:33 +00:00
11/18/2020 22:29:34 +00:00

Make sure you keep the terminal with the running script open

• Step 3.

Let's check the Patroni cluster to see where is the leader using patronictl, PatroniAPI, or HaProxy, either way, is fine. In my case the leader status was on 'patroni2':

+ Cluster: patroni (6893104757524385823) --+----+-----------+
| Member   | Host      | Role    | State   | TL | Lag in MB |
+----------+-----------+---------+---------+----+-----------+
| patroni1 | 10.0.1.18 | Replica | running | 21 |         0 |
| patroni2 | 10.0.1.22 | Leader  | running | 21 |           |
| patroni3 | 10.0.1.24 | Replica | running | 21 |         0 |
+----------+-----------+---------+---------+----+-----------+

So what we need to get done at this point is to open another terminal and fail the leader node:

docker service ls | grep patroni
docker service scale $patroni2-id=0

After some time in the terminal with the script we'll see logs throwing error:

// let's memorize the time we got the last successfull insert 

11/18/2020 22:33:06 +00:00
Error
Error
Error

If we check the Patroni cluster stats at this time we might see some delay though and patroni2 still indicating a healthy state running as a leader. But after some time it's going to fail and cluster, through a short stage of the leadership elections, will come to the following state:

+ Cluster: patroni (6893104757524385823) --+----+-----------+
| Member   | Host      | Role    | State   | TL | Lag in MB |
+----------+-----------+---------+---------+----+-----------+
| patroni1 | 10.0.1.18 | Replica | running | 21 |         0 |
| patroni3 | 10.0.1.24 | Leader  | running | 21 |           |
+----------+-----------+---------+---------+----+-----------+

If we go back to our script output we should notice that the connection has finally recovered and the logs are as follows:

Error
Error
Error
11/18/2020 22:33:48 +00:00
11/18/2020 22:33:49 +00:00
11/18/2020 22:33:50 +00:00
11/18/2020 22:33:51 +00:00

• Step 4.

Let's go ahead and check if the database is in a proper state after a failover:

docker run --rm -ti --network=patroni_patroni postgres:11 /bin/bash
psql --host haproxy --port 5000 -U approle -d postgres
postgres=> \c patronitestdb
You are now connected to database "patronitestdb" as user "approle".
// I set the time a little earlier than the crash happened
patronitestdb=> select * from records where time > '22:33:04' limit 15;
time       
-----------------
 22:33:04.171641
 22:33:05.205022
 22:33:06.231735
 // as we can see  in my case it required around 42 seconds 
 // for connection to recover
 22:33:48.345111
 22:33:49.36756
 22:33:50.374771
 22:33:51.383118
 22:33:52.391474
 22:33:53.399774
 22:33:54.408107
 22:33:55.416225
 22:33:56.424595
 22:33:57.432954
 22:33:58.441262
 22:33:59.449541

Summary

From this little experiment, we can conclude that Patroni managed to serve its purpose. After the failure occurred and the leader was re-elected we managed to reconnect and keep working with the database. And the previous data is all present on the leader node which is what we expected. Maybe it could have switched to another node a little faster than in 42 seconds, but at the end of the day, it's not that critical.