Patroni

Patroni is a High Availability solution for PostgreSQL using asynchronous streaming replication, Etcd, and HAProxy. For more details, you can read the Patroni documentation which is really good and covers many details. In this post, I will explain:

• Patroni architecture
• Ansible for patroni cluster setup
• verification of patroni/etcd cluster
• Switchover
• Fixing out-of-sync issue

Architecture

The Patroni agent is responsible for running PostgreSQL on each node and provides an API used to check a particular node’s current health. HAProxy also runs on each node, consumes this API and provides a single endpoint for the end-user to access the cluster’s leader, which is a node for managing all read/write operations for the whole cluster, often called a master node.

This endpoint can then be used for both reads and writes and is available on each node in the cluster on port `5000`. Patroni cluster may also provide an additional HAproxy endpoint that will accept only reads available on port `6000`. The Patroni REST API is available directly on each node on port `8008`.

Here is an example playbook containing two plays. The first ansible task will set up three compute instances on the Google Cloud Platform (GCP) that will be used to deploy the PostgreSQL databases. The second ansible task will configure the three nodes into a Patroni cluster.

# de-test-patroni-cluster.yml 

- name:         Launch VM(s)
  hosts:        localhost
  gather_facts: false
  roles:
    - role: gcp_vm
      gcp_vm_instances:
        - name:          de-gcp-patroni-example-01
        - name:          de-gcp-patroni-example-02
        - name:          de-gcp-patroni-example-03

      gcp_vm_options:
        defaults:
          state:         present
          os_image:      ubuntu1804
          type:          n1-standard-1
          ansible_group: patroni

    - role: gcp_lb
      gcp_lb_name: de-gcp-patroni-example
      gcp_lb_draining_timeout_secs: 60
      gcp_lb_healthcheck:
        tcp_port: 5000
      gcp_lb_instances: patroni   # This is the name of the Ansible host group containing the instances we want to use with this load balancer.
      gcp_lb_dns_name: de-gcp-patroni-example

- name:         Configure VM(s)
  hosts:        patroni
  become:       true

  roles:
    - role: patroni
      patroni_etcd_cluster_name:      my_etcd_cluster
      patroni_etcd_master_group_name: patroni                       
      patroni_master_node:            de-gcp-patroni-example-01     
      patroni_replication_nodes:      ""
      patroni_dbbackup:               true
      patroni_users:

        - name: myuser
          pass: mypass                    

      patroni_databases:                  
        - name:  mydatabase
          owner: myuser

        - name:  myseconddatabase
          owner: myuser

      patroni_database_schemas:           

        - database: mydatabae
          schema:   myschema
          owner:    myuser

      patroni_user_privileges:            

        - name:            myuser
          db:              mydatabase
          priv:            ALL
          role_attr_flags: NOCREATEDB

Ansible playbook can be run like this ( the above ansible code assumes you are using ansible version > 2.1, and installation notes can be found here )

ansible-playbook de-test-patroni-cluster.yml

After running this playbook, end users could access the PostgreSQL cluster via the GCP load balancer `de-gcp-patroni-example.de.gcp.cloud` on port `5000` for reading and writes or port `6000` for just reads.

Verify Patroni cluster status

Patroni needs at least two of the three nodes to be operational for the cluster to be available. One additional benefit is that Patroni completely manages your PostgreSQL databases. In terms of bringing it up and down, you can verify the status of a Patroni cluster and database in the following ways:

HTTP API

Make an HTTP request to one of the nodes on port `8008`. You may run it on any terminal with ssh connectivity to your Patroni cluster (below example used iterm terminal on mac). You should get a JSON document in the response showing the node’s role, details about the other replication nodes, and their status. As an example, using curl against the first node in the cluster created with the code above, will show the following output:

$ curl de-gcp-patroni-example-01:8008

{
  "database_system_identifier": "6820776744425517415",
  "postmaster_start_time": "2022-04-28 14:57:20.932 UTC",
  "timeline": 1,
  "cluster_unlocked": false,
  "patroni": {
    "scope": "patroniha",
    "version": "1.6.1"
  },

  "replication": [

    {

      "sync_state": "async",
      "sync_priority": 0,
      "client_addr": "10.80.129.111",
      "state": "streaming",
      "application_name": "de_gcp_patroni_example_03",
      "usename": "replicator"
    },

    {
      "sync_state": "async",
      "sync_priority": 0,
      "client_addr": "10.80.129.113",
      "state": "streaming",
      "application_name": "de_gcp_patroni_example_02",
      "usename": "replicator"
    }
  ],

  "state": "running",
  "role": "master",
  "xlog": {
    "location": 67125840
  },
  "server_version": 110007

}

Patronictl

Patroni comes with a CLI utility called patronictl. One can perform any admin operation related to the Postgres database or cluster using this command line utility. You can use the command line interface to describe the current cluster status by SSHing to one of the nodes and running the following command:

$ patronictl -d etcd://127.0.0.1:2379 list patroniha

+-----------+---------------------------+--------------------+--------+---------+----+-----------+
|  Cluster  |           Member          |        Host        |  Role  |  State  | TL | Lag in MB |
+-----------+---------------------------+--------------------+--------+---------+----+-----------+
| patroniha | de_gcp_patroni_example_01 | 10.80.131.214:5433 | Leader | running |  1 |           |
| patroniha | de_gcp_patroni_example_02 | 10.80.129.113:5433 |        | running |  1 |       0.0 |
| patroniha | de_gcp_patroni_example_03 | 10.80.129.111:5433 |        | running |  1 |       0.0 |
+-----------+---------------------------+--------------------+--------+---------+----+-----------+

Checking Logs

Patroni is managed via SystemD, and as such, you can get error logs for a particular server by SSHing to it, and then querying with `journalctl`. For example, on a master:

$ sudo journalctl -f -u patroni

Apr 28 16:06:21 de-gcp-patroni-example-01 patroni[20782]: 2022-04-28 16:06:21,983 INFO: Lock owner: de_gcp_patroni_example_01; I am de_gcp_patroni_example_01
Apr 28 16:06:22 de-gcp-patroni-example-01 patroni[20782]: 2022-04-28 16:06:22,017 INFO: no action.  i am the leader with the lock

On a secondary, you will see something slightly different:

$ sudo journalctl -f -u patroni

Apr 28 16:08:11 de-gcp-patroni-example-02 patroni[20552]: 2022-04-28 16:08:11,990 INFO: does not have lock
Apr 28 16:08:11 de-gcp-patroni-example-02 patroni[20552]: 2022-04-28 16:08:11,997 INFO: no action.  i am a secondary and i am following a leader

Verifying Etcd Cluster status

Etcd is used for Patroni to distribute configuration and state between nodes, so it must also be healthy for proper cluster operation.

You can check the status of the cluster by SSHing to one of the nodes and running the following:

$ etcdctl cluster-health patroniha

member 13a17454e5cb0797 is healthy: got healthy result from http://10.80.129.113:2379
member 4ba8a9618e42f288 is healthy: got healthy result from http://10.80.129.111:2379
member 6a453ce7684a6c8d is healthy: got healthy result from http://10.80.131.214:2379

cluster is healthy

Perform a Manual Switchover

Using the command line interface, you can force Patroni to switch to a new leader. The switchover will occur immediately and require small downtime ( cluster unavailability ).

1. First, you should SSH to one of the nodes in the cluster and verify the current cluster status.

$ patronictl -d etcd://127.0.0.1:2379 list patroniha

   +-----------+---------------------------+--------------------+--------+---------+----+-----------+
   |  Cluster  |           Member          |        Host        |  Role  |  State  | TL | Lag in MB |
   +-----------+---------------------------+--------------------+--------+---------+----+-----------+
   | patroniha | de_gcp_patroni_example_01 | 10.80.131.214:5433 | Leader | running |  1 |           |
   | patroniha | de_gcp_patroni_example_02 | 10.80.129.113:5433 |        | running |  1 |       0.0 |
   | patroniha | de_gcp_patroni_example_03 | 10.80.129.111:5433 |        | running |  1 |       0.0 |
   +-----------+---------------------------+--------------------+--------+---------+----+-----------+

2. Next, you can trigger the switchover and specify which node you want to make the new leader. You can also choose to schedule the switchover in the future or perform it immediately:

$ patronictl -d etcd://127.0.0.1:2379 switchover patroniha

Master [de_gcp_patroni_example_01]: <return>
Candidate ['de_gcp_patroni_example_02', 'de_gcp_patroni_example_03'] []: de_gcp_patroni_example_02
When should the switchover take place (e.g. 2020-04-28T17:15 )  [now]: <return>
Current cluster topology

+-----------+---------------------------+--------------------+--------+---------+----+-----------+
|  Cluster  |           Member          |        Host        |  Role  |  State  | TL | Lag in MB |
+-----------+--------------------------+--------------------+--------+---------+----+-----------+
| patroniha | de_gcp_patroni_example_01 | 10.80.131.214:5433 | Leader | running |  1 |           |
| patroniha | de_gcp_patroni_example_02 | 10.80.129.113:5433 |        | running |  1 |       0.0 |
| patroniha | de_gcp_patroni_example_03 | 10.80.129.111:5433 |        | running |  1 |       0.0 |
+-----------+---------------------------+--------------------+--------+---------+----+-----------+

Are you sure you want to switchover cluster patroniha, demoting current master de_gcp_patroni_example_01? [y/N]:

You can verify the operation is correct and then enter `y` to proceed, after which you’ll see the following:

2020-04-28 16:17:09.50350 Successfully switched over to "de_gcp_patroni_example_02"

+-----------+---------------------------+--------------------+--------+---------+----+-----------+
|  Cluster  |           Member          |        Host        |  Role  |  State  | TL | Lag in MB |
+-----------+---------------------------+--------------------+--------+---------+----+-----------+
| patroniha | de_gcp_patroni_example_01 | 10.80.131.214:5433 |        | stopped |    |   unknown |
| patroniha | de_gcp_patroni_example_02 | 10.80.129.113:5433 | Leader | running |  1 |           |
| patroniha | de_gcp_patroni_example_03 | 10.80.129.111:5433 |        | running |  1 |       0.0 |
+-----------+---------------------------+--------------------+--------+---------+----+-----------+

3. Finally, you can verify that the switchover is now completed by checking the status again:

$ patronictl -d etcd://127.0.0.1:2379 list patroniha

+-----------+---------------------------+--------------------+--------+---------+----+-----------+
|  Cluster  |           Member          |        Host        |  Role  |  State  | TL | Lag in MB |
+-----------+---------------------------+--------------------+--------+---------+----+-----------+
| patroniha | de_gcp_patroni_example_01 | 10.80.131.214:5433 |        | running |  2 |       0.0 |
| patroniha | de_gcp_patroni_example_02 | 10.80.129.113:5433 | Leader | running |  2 |           |
| patroniha | de_gcp_patroni_example_03 | 10.80.129.111:5433 |        | running |  2 |       0.0 |
+-----------+---------------------------+--------------------+--------+---------+----+-----------+

Fixing Patroni out of sync issue

1. First, you should SSH to the primary node in the cluster and verify the current cluster status; you can execute the below command on any cluster node to find out which is the primary node.

$ patronictl -d etcd://127.0.0.1:2379 list patroniha

root@de-gcp-patroni-example-01:~# patronictl -d etcd://127.0.0.1:2379 list  patroniha
+ Cluster: patroniha (6830699472358332339) ------+--------+---------+----+-----------+
|          Member           |        Host        |  Role  |  State  | TL | Lag in MB |
+---------------------------+--------------------+--------+---------+----+-----------+
| de_gcp_patroni_example_01 | 10.80.128.61:5433  | Leader | running |  2 |           |
| de_gcp_patroni_example_02 | 10.80.128.160:5433 |        | running |  2 |        75 |
| de_gcp_patroni_example_03 | 10.80.128.236:5433 |        | running |  2 |         0 |
+---------------------------+--------------------+--------+---------+----+-----------+

    If you find `Lag in MB` is greater than zero for any secondary, it means there is a problem with its sync, and the command reinit can be used in the node to fix the issue.

2. Log in to the primary node of the cluster, and run the below command. Patroni has a built-in function reinit to make that standby in sync again, saving you from the manual work. This command will force the cluster node to sync data from the primary node.

patronictl -d etcd://127.0.0.1:2379 reinit cluster_name node_name

    For example:

root@de-gcp-patroni-example-01:~# patronictl -d etcd://127.0.0.1:2379 reinit patroniha de_gcp_patroni_example_02

   + Cluster: patroniha (6830699472358332339) ------+--------+---------+----+------------+
   |          Member           |        Host        |  Role  |  State  | TL | Lag in MB  |
   +---------------------------+--------------------+--------+---------+----+------------+
   | de_gcp_patroni_example_01 | 10.80.128.61:5433  | Leader | running |  2 |            |
   | de_gcp_patroni_example_02 | 10.80.128.160:5433 |        | running |  2 |         75 |
   | de_gcp_patroni_example_03 | 10.80.128.236:5433 |        | running |  2 |          0 |
   +---------------------------+--------------------+--------+---------+----+------------+

   Are you sure you want to reinitialize members de_gcp_patroni_example_02? [y/N]: y
   Success: reinitialize for member de_gcp_patroni_example_02

   root@de-gcp-patroni-example-01:~#

    You can log in to node de_gcp_patroni_example_02 and verify in Patroni service log too.

      1. ssh to de-gcp-patroni-example-02

      2. As root, run

root@de-gcp-patroni-example-02:~# journalctl -f -u patroni

      You will see some information in the log:

2022-05-26 08:54:41.748 UTC [2846] LOG:  database system is shut down
2022-05-26 08:54:41,760 INFO: Removing data directory: /data/postgresql/db
2022-05-26 0854:46,627 INFO: replica has been created using basebackup
2022-05-26 08:54:46,628 INFO: bootstrapped from leader 'de_gcp_patroni_example_01'
2022-05-26 08:54:46,633 INFO: closed patroni connection to the postgresql cluster
2022-05-26 08:54:47,442 INFO: postmaster pid=4426
localhost:5433 - no response

2022-05-26 08:54:47.713 UTC [4426] LOG:  starting PostgreSQL 12.3 (Ubuntu 12.3-1.pgdg18.04+1) on x86_64-pc

And, normal logging agai
    
2022-05-26 08:55:00,441 INFO: no action.  i am a secondary and i am following a leader
2022-05-26 08:55:10,433 INFO: Lock owner: de_gcp_patroni_example_01; I am de_gcp_patroni_example_02
2022-05-26 08:55:10,433 INFO: does not have lock
2022-05-26 08:55:10,440 INFO: no action.  i am a secondary and i am following a leader

3. After some time, depending on database size, “`Lag in MB “ should be zero again. You can verify as

$ patronictl -d etcd://127.0.0.1:2379 list patroniha

root@de-gcp-patroni-example-01:~# patronictl -d etcd://127.0.0.1:2379 list patroniha

+ Cluster: patroniha (6830699472358332339) ----+--------+---------+----+-----------+
|          Member           |        Host        |  Role  |  State  | TL | Lag in MB |
+---------------------------+--------------------+--------+---------+----+-----------+
| de_gcp_patroni_example_01 | 10.80.128.61:5433  | Leader | running |  2 |           |
| de_gcp_patroni_example_02 | 10.80.128.160:5433 |        | running |  2 |         0 |
| de_gcp_patroni_example_03 | 10.80.128.236:5433 |        | running |  2 |         0 |
+---------------------------+--------------------+--------+---------+----+-----------+

Conclusion