Welcome to Part two of our series about building a High Availability Postgres cluster using Patroni! Part one focused entirely on establishing the DCS using etcd, providing the critical layer that Patroni uses to store metadata and guarantee its leadership token uniqueness across the cluster.

With this solid foundation, it's now time to build the next layer in our stack: Patroni itself. Patroni does the job of managing the Postgres service and provides a command interface for node administration and monitoring. Technically the Patroni cluster is complete at the end of this article, but stick around for part three where we add the routing layer that brings everything together.

Hopefully you still have the three VMs where you installed etcd. Those will be the same place where everything else happens, so if you haven’t already gone through the steps in part one, come back when you’re ready.

Otherwise, let’s get started!

Installing Postgres

The Postgres community site has an incredibly thorough page dedicated to installation on various platforms. For the sake of convenience, this guide includes a simplified version of the Debian instructions. Perform these steps on all three servers.

Start by setting up the PGDG repository:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Then install your favorite version of Postgres. For the purposes of this guide, we’re also going to stop Postgres and drop the initial cluster the Postgres package creates. Patroni will recreate all of this anyway, and it should be in control.

sudo apt install -y postgresql-18
sudo systemctl stop postgresql@18-main
sudo pg_dropcluster 18 main

It’s also important to completely disable the default Postgres service since Patroni will be in charge:

sudo systemctl disable postgresql

Finally, install the version of Patroni included in the PGDG repositories. This should be available on supported platforms like Debian and RedHat variants, but if it isn’t, you may have to resort to the official installation instructions.

sudo apt install -y patroni

Once that command completes, we should have three fresh VMs ready for configuration.

Configuring Patroni the easy way

The Debian Patroni package provides a tool called pg_createconfig_patroni that transforms a Patroni template into a configuration file customized specifically for Debian systems. Before using it, it’s necessary to modify part of that template to use etcd, as ZooKeeper is the default. Perform these steps on all three servers.

cat<<EOF|sudo tee /etc/patroni/dcs.yml
etcd3:
  host: 127.0.0.1:2379
EOF

Note that the YAML header shows “etcd3” rather than simply “etcd”. Patroni uses etcd2 by default for backward compatibility purposes, and version 3 requires a much different communication protocol.

Then create the rest of the config with a single command:

sudo pg_createconfig_patroni 18 demo

This creates a file named 18-demo.yml in the /etc/patroni configuration directory, which systemd uses when managing this specific cluster. We’ll also need this for invoking patronictl.

Understanding Patroni configuration

Despite the fact that the configuration file is already complete, it’s important to actually understand the purpose of each section and what it does. This will enable users of other platforms to manually configure Patroni if necessary.

Let’s start with the topmost section dedicated to the DCS:

scope: "18-demo"
namespace: "/postgresql-common/"
name: patroni-demo-1

etcd3:
  host: 127.0.0.1:2379

When Patroni writes to the DCS, all keys start at the path specified by the namespace parameter. Similarly as one DCS may host multiple clusters, keys for this cluster must include scope in the key path. The name indicates how Patroni should refer to this individual node. The configuration tool actually uses the DCS to see which names are already reserved so each VM will be uniquely identified. Go ahead and check all three to make sure they’re correct.

The next section, labeled bootstrap, determines how Patroni should create the initial Postgres cluster, the parameters to use, and other important information. It’s also pretty long, so let’s look at each individual portion:

bootstrap:
  method: pg_createcluster
  pg_createcluster:
    command: /usr/share/patroni/pg_createcluster_patroni

Normally Patroni uses pg_init when creating a new cluster, but for full compatibility with Debian organization quirks regarding Postgres, the configuration specifies an alternative command. This short section will likely only appear on a Debian system.

Next comes the dcs section under bootstrap. All of these parameters should be covered in the Dynamic Configuration Settings documentation, but we’ll explain the important ones. It’s important to note that any settings defined here actually persist in the DCS layer and apply to Patroni on all nodes. After initialization, the only way to change these parameters is through the patronictl utility. It’s a good idea to make sure all of these are set properly, as changing them later is somewhat inconvenient.

dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    check_timeline: true
    primary_start_timeout: 300
    synchronous_mode: false

These parameters define how Patroni interacts with the DCS layer and how it should manage certain Postgres features. Remember that the leadership token determines which node is the primary, so ttl defines how long that lease should last, loop_wait controls how long to wait between lease renewals, and retry_timeout says how long to wait for a response from the DCS.

We’ve included primary_start_timeout in this output because the leader race isn’t quite absolute. If Patroni promotes a node to primary, or determines Postgres has failed, it has up to this timeout before it forces a failover. The provides a grace period for crash recovery to complete, but you may find the default of five minutes is much too long. 

Another important parameter here is synchronous_mode, which tells Patroni it should manage the synchronous_standby_names Postgres configuration setting by automatically using names of other nodes in the cluster. This is how you would enable synchronous replication in Patroni.

Next is the postgresql section under dcs: 

postgresql:
      use_pg_rewind: true
      remove_data_directory_on_rewind_failure: true
      remove_data_directory_on_diverged_timelines: true
      use_slots: true
      parameters:
        wal_level: hot_standby

This section defines how Patroni should operate the Postgres service. The first few parameters control how Patroni recycles old primary nodes, such as using the pg_rewind utility when possible, and whether it should erase the data directory as a last resort. Patroni also uses replication slots for replicas by default to prevent unnecessary replica rebuilds in failure scenarios.

You can also pass GUC settings directly to Postgres on all nodes through the parameters section. This is useful for providing important cluster-wide settings that may not be hardware dependent, such as wal_level, max_connections, or hot_standby_feedback.

The final section under the dcs.postgresql heading is pg_hba: 

pg_hba:
      - local   all             all                                     peer
      - host    all             all             127.0.0.1/32            md5
      - host    all             all             ::1/128                 md5
#      - host    all             all             192.168.6.10/16         md5
      - local   replication     all                                     peer
      - host    replication     all             127.0.0.1/32            md5
      - host    replication     all             ::1/128                 md5
      - host    replication     all             192.168.6.10/16         md5

You’ll want to customize this section before starting Patroni; it uses this to build the pg_hba.conf file that controls incoming connection access. The default will allow connections on the server’s subnet if you uncomment the disabled line, otherwise it’s local access only.

Next is another postgresql section, but this is a top-level header meant to tell Patroni how it should handle Postgres on this specific server. These sections are explained in more detail in the Patroni YAML Configuration Settings documentation.

This example starts with some Debian-specific content:

postgresql:
  create_replica_method:
    - pg_clonecluster
  pg_clonecluster:
    command: /usr/share/patroni/pg_clonecluster_patroni

As before, this is so Debian can integrate with the other packaged Postgres tooling, so it’s safe to skip on other platforms. After that comes a few pertinent parameters for handling connections: 

listen: "*:5432"
  connect_address: 192.168.6.10:5432
  use_unix_socket: true

This sample effectively tells Patroni how it should connect to the local Postgres service for administrative actions. Patroni uses unix sockets when possible using these settings, which makes sense as Patroni runs as the postgres OS user and has direct socket access.

Then comes a fun section that defines several paths: 

data_dir: /var/lib/postgresql/18/demo
  bin_dir: /usr/lib/postgresql/18/bin
  config_dir: /etc/postgresql/18/demo
  pgpass: /var/lib/postgresql/18-demo.pgpass

Patroni knows it will be installed in several different environments where Postgres and configuration directories may be in completely arbitrary locations. These are the defaults for Postgres 18 running on a Debian system.

Lastly there’s a second parameters section, meant for parameters that should only apply to this specific Postgres server: 

parameters:
    unix_socket_directories: '/var/run/postgresql/'
    logging_collector: 'on'
    log_directory: '/var/log/postgresql'
    log_filename: 'postgresql-18-demo.log'

Nothing here should be surprising; it’s mostly just log storage for the local instance and where the unix socket directory is located. These are likely to be universal across the cluster, but it’s safer to leave them out of the DCS section. If there is ever any variance caused by a hardware or OS distribution migration, you’ll want to have the ability to change these locally.

In any case, take some time to examine the /etc/patroni/18-demo.yml file on each node to spot-check it for any mistakes.

Starting and validating Patroni

The Patroni package provides a standard systemd service file; simply enable and start the service on all VMs.

sudo systemctl enable patroni@18-demo
sudo systemctl start patroni@18-demo

One of the three nodes will “win” the leader race and become the primary for the cluster. Patroni then invokes the pg_createcluster_patroni command on that system to create the data and configuration directories before starting Postgres. On the other nodes, Patroni calls pg_clonecluster_patroni instead to create new streaming replicas. If you want a specific node to start as the primary, simply start Patroni on that node and wait for it to establish a cluster before starting the service on the other two.

The end result on all three systems should be a new “demo” database visible to pg_lsclusters: 

pg_lsclusters

Ver Cluster Port Status         Owner    Data directory              Log file
18  demo    5432 online,patroni postgres /var/lib/postgresql/18/demo /var/log/postgresql/postgresql-18-demo.log

The next step is to check the status of the Patroni cluster itself. You should be able to run this command from any node as the postgres OS user. It will also work as root, but now that Patroni is installed and managing the cluster, it’s best to avoid relying on the root user.

patronictl -c /etc/patroni/18-demo.yml list

+ Cluster: 18-demo (7606465692216410488) -+-----------+----+-------------+-----+------------+-----+
| Member         | Host         | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+----------------+--------------+---------+-----------+----+-------------+-----+------------+-----+
| patroni-demo-1 | 192.168.6.10 | Leader  | running   |  2 |             |     |            |     |
| patroni-demo-2 | 192.168.6.11 | Replica | streaming |  2 |   0/4000060 |   0 |  0/4000060 |   0 |
| patroni-demo-3 | 192.168.6.12 | Replica | streaming |  2 |   0/4000060 |   0 |  0/4000060 |   0 |
+----------------+--------------+---------+-----------+----+-------------+-----+------------+-----+

This output tells us the cluster is healthy and operational, node 1 is the current primary, both replicas are streaming, and there’s no replication lag. Success!

Editing the cluster configuration

The last step that might be necessary is to modify the cluster configuration stored in the DCS layer. These are the Postgres parameters and pg_hba.conf entries used to bootstrap the initial state of the cluster, and it’s easy to make mistakes early on.

Once again, patronictl comes to the rescue: 

patronictl -c /etc/patroni/18-demo.yml edit-config

Patroni loads the current DCS config into the current default editor, and in our case it looks like this:

check_timeline: true
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters: null
  pg_hba:
  - local   all             all                                     peer
  - host    all             all             127.0.0.1/32            md5
  - host    all             all             ::1/128                 md5
  - host    all             all             192.168.6.10/16         md5
  - local   replication     all                                     peer
  - host    replication     all             127.0.0.1/32            md5
  - host    replication     all             ::1/128                 md5
  - host    replication     all             192.168.6.10/16         md5
  remove_data_directory_on_diverged_timelines: true
  remove_data_directory_on_rewind_failure: true
  use_pg_rewind: true
  use_slots: true
retry_timeout: 10
ttl: 30

Use this as an opportunity to fix any missing HBA lines, or add any Postgres parameters that should apply to all nodes. For example, add wal_level under postgresql.parameters to enable logical replication:

check_timeline: true
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    wal_level: logical
  pg_hba:
  - local   all             all                                     peer
  - host    all             all             127.0.0.1/32            md5
  - host    all             all             ::1/128                 md5
  - host    all             all             192.168.6.10/16         md5
  - local   replication     all                                     peer
  - host    replication     all             127.0.0.1/32            md5
  - host    replication     all             ::1/128                 md5
  - host    replication     all             192.168.6.10/16         md5
  remove_data_directory_on_diverged_timelines: true
  remove_data_directory_on_rewind_failure: true
  use_pg_rewind: true
  use_slots: true
retry_timeout: 10
ttl: 30

Since changing the wal_level parameter requires a Postgres restart, use patronictl to restart the nodes in the cluster: 

patronictl -c /etc/patroni/18-demo.yml restart 18-demo --force

Then check with Postgres to verify that the setting changed as expected. This is the output from node 3, even though I modified the DCS and restarted the cluster from node 1:

SHOW wal_level;

wal_level 
-----------
 logical

Finishing up

Now you know why this series was broken into three parts! Setting up Patroni isn’t too difficult by itself, but getting the configuration right, knowing how and why each section works the way it does, and continuing to modify the cluster after deployment, is a complex process. But if you followed along, you should have a fully operational Patroni cluster at this very moment.

Technically you can even stop here and skip the third and final installment of this series. Postgres supports multi-host connection strings, and specifying read-write for the target_session_attr restricts connections to the primary node. Connecting with psql might look like this:

psql -d "user=postgres host=pg1,pg2,pg3 target_session_attrs=read-write"

But what if, in some distant future, we change server names, or add more nodes to the cluster, or want other connection restrictions? That’s where the routing layer comes in, and what fully completes a Patroni deployment.

So come back next week to learn about HAProxy and how it provides that critical and final component!