Patroni setup¶

Install Percona Distribution for PostgreSQL and Patroni¶

Run the following commands as root or with sudo privileges on node1, node2 and node3.

On Debian / Ubuntu On RHEL and derivatives

Disable the upstream postgresql-15 package.
Install Percona Distribution for PostgreSQL package
```
$ sudo apt install percona-postgresql-15
```
Install some Python and auxiliary packages to help with Patroni
```
$ sudo apt install python3-pip python3-dev binutils
```
Install Patroni
```
$ sudo apt install percona-patroni
```

Stop and disable all installed services:

$ sudo systemctl stop {patroni,postgresql}
$ sudo systemctl disable {patroni,postgresql}

Even though Patroni can use an existing Postgres installation, our recommendation for a new cluster that has no data is to remove the data directory. This forces Patroni to initialize a new Postgres cluster instance.
```
$ sudo systemctl stop postgresql
$ sudo rm -rf /var/lib/postgresql/15/main
```

Install Percona Distribution for PostgreSQL package

$ sudo yum install percona-postgresql15-server

Check the platform specific notes for Patroni
Install some Python and auxiliary packages to help with Patroni and etcd
```
$ sudo yum install python3-pip python3-devel binutils
```
Install Patroni
```
$ sudo yum install percona-patroni 
```

Stop and disable all installed services:

$ sudo systemctl stop {patroni,postgresql-15}
$ sudo systemctl disable {patroni,postgresql-15}

Important

Don’t initialize the cluster and start the postgresql service. The cluster initialization and setup are handled by Patroni during the bootsrapping stage.

Configure Patroni¶

Run the following commands on all nodes. You can do this in parallel:

Create environment variables¶

Environment variables simplify the config file creation:

Node name:
```
$ export NODE_NAME=`hostname -f`
```
Node IP:
```
$ export NODE_IP=`getent hosts $(hostname -f) | awk '{ print $1 }' | grep -v grep | grep -v '127.0.1.1'`
```
- Check that the correct IP address is defined:
```
$ echo $NODE_IP
```
Sample output node1
```
10.104.0.7
```
If you have multiple IP addresses defined on your server and the environment variable contains the wrong one, you can manually redefine it. For example, run the following command for node1:
```
$ NODE_IP=10.104.0.7
```
Create variables to store the PATH. Check the path to the data and bin folders on your operating system and change it for the variables accordingly:
Debian and Ubuntu RHEL and derivatives
DATA_DIR="/var/lib/postgresql/15/main" PG_BIN_DIR="/usr/lib/postgresql/15/bin"
DATA_DIR="/var/lib/pgsql/data/" PG_BIN_DIR="/usr/pgsql-15/bin"

Patroni information:

NAMESPACE="percona_lab"
SCOPE="cluster_1"

Create the directories required by Patroni¶

Create the directory to store the configuration file and make it owned by the postgres user.

$ sudo mkdir -p /etc/patroni/
$ sudo chown -R  postgres:postgres /etc/patroni/

Patroni configuration file¶

Use the following command to create the /etc/patroni/patroni.yml configuration file and add the following configuration for every node:

echo "
namespace: ${NAMESPACE}
scope: ${SCOPE}
name: ${NODE_NAME}

restapi:
    listen: 0.0.0.0:8008
    connect_address: ${NODE_IP}:8008

etcd3:
    host: ${NODE_IP}:2379

bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  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: replica
              hot_standby: "on"
              wal_keep_segments: 10
              max_wal_senders: 5
              max_replication_slots: 10
              wal_log_hints: "on"
              logging_collector: 'on'
              max_wal_size: '10GB'
              archive_mode: "on"
              archive_timeout: 600s
              archive_command: "cp -f %p /home/postgres/archived/%f"

      pg_hba: # Add following lines to pg_hba.conf after running 'initdb'
      - host replication replicator 127.0.0.1/32 trust
      - host replication replicator 0.0.0.0/0 md5
      - host all all 0.0.0.0/0 md5
      - host all all ::0/0 md5
      recovery_conf:
            restore_command: cp /home/postgres/archived/%f %p

  # some desired options for 'initdb'
  initdb: # Note: It needs to be a list (some options need values, others are switches)
      - encoding: UTF8
      - data-checksums


postgresql:
    cluster_name: cluster_1
    listen: 0.0.0.0:5432
    connect_address: ${NODE_IP}:5432
    data_dir: ${DATA_DIR}
    bin_dir: ${PG_BIN_DIR}
    pgpass: /tmp/pgpass0
    authentication:
        replication:
            username: replicator
            password: replPasswd
        superuser:
            username: postgres
            password: qaz123
    parameters:
        unix_socket_directories: "/var/run/postgresql/"
    create_replica_methods:
        - basebackup
    basebackup:
        checkpoint: 'fast'

    watchdog:
      mode: required # Allowed values: off, automatic, required
      device: /dev/watchdog
      safety_margin: 5

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
" | sudo tee /etc/patroni/patroni.yml

Patroni configuration file

Let’s take a moment to understand the contents of the patroni.yml file.

The first section provides the details of the node and its connection ports. After that, we have the etcd service and its port details.

Following these, there is a bootstrap section that contains the PostgreSQL configurations and the steps to run once

Systemd configuration¶

Check that the systemd unit file percona-patroni.service is created in /etc/systemd/system. If it is created, skip this step.

If it’s not created, create it manually and specify the following contents within:

/etc/systemd/system/percona-patroni.service

[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target 

[Service]
Type=simple 

User=postgres
Group=postgres 

# Start the patroni process
ExecStart=/bin/patroni /etc/patroni/patroni.yml 

# Send HUP to reload from patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID 

# only kill the patroni process, not its children, so it will gracefully stop postgres
KillMode=process 

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=30 

# Do not restart the service if it crashes, we want to manually inspect database on failure
Restart=no 

[Install]
WantedBy=multi-user.target

Make systemd aware of the new service:
```
$ sudo systemctl daemon-reload
```
Make sure you have the configuration file and the systemd unit file created on every node.

Start Patroni¶

Now it’s time to start Patroni. You need the following commands on all nodes but not in parallel.

Start Patroni on node1 first, wait for the service to come to live, and then proceed with the other nodes one-by-one, always waiting for them to sync with the primary node:
```
$ sudo systemctl enable --now percona-patroni
```
When Patroni starts, it initializes PostgreSQL (because the service is not currently running and the data directory is empty) following the directives in the bootstrap section of the configuration file.
Check the service to see if there are errors:
```
$ sudo journalctl -fu percona-patroni
```
See Troubleshooting Patroni startup for guidelines in case of errors.

If Patroni has started properly, you should be able to locally connect to a PostgreSQL node using the following command:
```
$ sudo psql -U postgres

psql (15.18)
Type "help" for help.

postgres=#
```

When all nodes are up and running, you can check the cluster status using the following command:

$ sudo patronictl -c /etc/patroni/patroni.yml list

The output resembles the following:

Sample output node1

+ Cluster: cluster_1 (7440127629342136675) -----+----+-------+
| Member | Host       | Role    | State     | TL | Lag in MB |
+--------+------------+---------+-----------+----+-----------+
| node1  | 10.0.100.1 | Leader  | running   |  1 |           |

Sample output node3

+ Cluster: cluster_1 (7440127629342136675) -----+----+-------+
| Member | Host       | Role    | State     | TL | Lag in MB |
+--------+------------+---------+-----------+----+-----------+
| node1  | 10.0.100.1 | Leader  | running   |  1 |           |
| node2  | 10.0.100.2 | Replica | streaming |  1 |         0 |
| node3  | 10.0.100.3 | Replica | streaming |  1 |         0 |
+--------+------------+---------+-----------+----+-----------+

Troubleshooting Patroni startup¶

A common error is Patroni complaining about the lack of proper entries in the pg_hba.conf file. If you see such errors, you must manually add or fix the entries in that file and then restart the service.

An example of such an error is No pg_hba.conf entry for replication connection from host to <IP>, user replicator, no encryption. This means that Patroni cannot connect to the node you’re adding to the cluster. To resolve this issue, add the IP addresses of the nodes to the pg_hba: section of the Patroni configuration file.

pg_hba: # Add following lines to pg_hba.conf after running 'initdb'
- host replication replicator 127.0.0.1/32 trust
- host replication replicator 0.0.0.0/0 md5
- host replication replicator 10.0.100.2/32 trust
- host replication replicator 10.0.100.3/32 trust
- host all all 0.0.0.0/0 md5
- host all all ::0/0 md5
recovery_conf:
      restore_command: cp /home/postgres/archived/%f %p

For production use, we recommend adding nodes individually as the more secure way. However, if your network is secure and you trust it, you can add the whole network these nodes belong to as the trusted one to bypass passwords use during authentication. Then all nodes from this network can connect to Patroni cluster.

Changing the patroni.yml file and restarting the service will not have any effect here because the bootstrap section specifies the configuration to apply when PostgreSQL is first started in the node. It will not repeat the process even if the Patroni configuration file is modified and the service is restarted.

Next steps¶

pgBackRest setup