19 October 2026, 19:53Виталий Лещенко

A Highly Available PostgreSQL Cluster on Patroni: Setup, Testing, Backups and Pitfalls

A single PostgreSQL server is fine right up to the day the hypervisor underneath it reboots for updates, taking down with it every application that keeps its databases on that server. This article is about building a PostgreSQL cluster out of three machines that survives the loss of any one of them on its own, without an administrator at the console: what parts it consists of, how they are installed on Ubuntu, what happens on failure, how to test it, how to back it up and how to maintain it afterwards. And separately, about the pitfalls I ran into while building such a cluster: some of them cost hours if you don't know where to look.

All names and addresses in the article are made up: the domain example.com, nodes db1, db2, db3 with addresses 192.0.2.11–192.0.2.13, the floating address 192.0.2.10. The operating system is Ubuntu 26.04 LTS, whose repository ships PostgreSQL 18, Patroni 4.1 and etcd 3.5; all commands in the article are run as root or via sudo.

What the cluster consists of

PostgreSQL by itself supports streaming replication: one server, the primary, accepts writes, and the replicas (standbys) receive the write-ahead log, WAL, from it and replay it, staying copies of the primary that lag by milliseconds. The replication protocol is an ordinary PostgreSQL connection over which a stream of WAL records flows instead of SQL queries: the replica connects to the primary with a role that has the REPLICATION privilege, names the position it wants to read from, and then receives records as they appear, periodically reporting what it has written and applied. What PostgreSQL can't do by itself is decide that the primary is dead, appoint a new one and switch clients over to it. That takes three more parts.

                 clients (applications, psql, backups)
                              │
                              ▼  192.0.2.10 (VIP, always on the leader)
      ┌───────────────────────┼──────────────────────┐
      ▼                       ▼                      ▼
┌─────────────┐        ┌─────────────┐        ┌─────────────┐
│    db1      │        │    db2      │        │    db3      │
│ PostgreSQL  │◄──WAL──│ PostgreSQL  │──WAL──►│ PostgreSQL  │
│ replica     │        │ leader      │        │ sync rep.   │
│ Patroni     │        │ Patroni     │        │ Patroni     │
│ vip-manager │        │ vip-manager │        │ vip-manager │
│ pgBackRest  │        │ pgBackRest  │        │ pgBackRest  │
│ etcd ◄──────┼──Raft──┼─► etcd ◄────┼──Raft──┼─► etcd      │
└─────────────┘        └──────┬──────┘        └─────────────┘
                              │ WAL + backups
                              ▼
                     S3-compatible bucket

Patroni is a daemon on each node that manages the local PostgreSQL: it starts it itself, writes postgresql.conf and pg_hba.conf itself, monitors its health and decides who the primary is. It doesn't decide alone: leadership is a key with a time to live in a distributed store, and the primary is whoever manages to keep renewing it. If the leader fails to renew the key, the others hold an election, and the most up-to-date of them promotes itself.

etcd is that store: a small key-value database that three instances keep consistent using the Raft protocol. In Raft the group has a leader; it accepts all writes and sends them to the others; a write is considered committed when a majority has confirmed it (two out of three). If the leader disappears, the rest elect a new one, but only if they still form a majority. Thanks to this the cluster can never have two primaries at once: in a network partition only the side that still has two of the three can renew the leader key, and the node on the other side will see that it has lost the key and demote its PostgreSQL to a replica on its own. etcd clients talk to it over gRPC — remote procedure calls over HTTP/2, and in our case over TLS.

vip-manager is a tiny daemon that watches the leader key in etcd and keeps the floating address on the node whose name is in that key. A node gained leadership — vip-manager brought the address up on the interface; lost it — took it down. Clients only need to know one address.

pgBackRest handles backups: it continuously receives the WAL archive from the primary and once a day takes a backup to S3-compatible storage. Replication protects against a machine dying, but not against a DROP TABLE, which the replicas will repeat after the primary a millisecond later; only an archive you can roll back to any point in time protects against that.

There are other ways to put together such a cluster. repmgr does without an external store, but without one there's no quorum either: in a network partition both halves may decide they are the primary. pg_auto_failover is simpler, but its monitor is a single point of failure, and the third node goes to the monitor rather than to data. Kubernetes operators like CloudNativePG are good if the database lives in a Kubernetes cluster; mine deliberately lives outside. Patroni with etcd is the most widespread option, and its behaviour under failure mattered most to me.

Preparing the nodes

Three virtual machines on three different hypervisors — otherwise the cluster won't survive the very thing it's built for. Each runs Ubuntu Server 26.04 with 4 GB of memory and a 64 GB disk, all in a single network segment: a floating address only works within one L2 domain, because when the address moves, vip-manager sends a gratuitous ARP — a broadcast reply saying "address 192.0.2.10 now belongs to such-and-such MAC", which switches and neighbours use to update their tables. ARP is the protocol nodes in one Ethernet segment use to find a MAC address by IP address; it doesn't pass through a router, so you can't bring a VIP up in another subnet.

The node addresses are static, and that's not a matter of taste. The floating address lives on the same eth0 interface as the node's own address, and any mechanism that registers the node's addresses in DNS by itself will sooner or later attach the VIP to the name of the leader node. There are two such mechanisms: a DHCP server registering the client's name from the lease it handed out, and sssd on a node joined to an Active Directory domain, which by default updates the node's record with all of the interface's addresses. This is the first pitfall, and you don't have to step on it: the name db2.example.com must have exactly one address in DNS, otherwise some clients and the monitoring will reach the leader under a replica's name, and after a switchover — nowhere. So the addresses are set in netplan, the A and PTR records for the nodes are created in DNS by hand as static ones, and dynamic registration in sssd is turned off:

# /etc/netplan/00-static.yaml
network:
  version: 2
  ethernets:
    eth0:
      dhcp4: false
      dhcp6: false
      addresses: [192.0.2.11/24]
      routes:
        - to: default
          via: 192.0.2.1
      nameservers:
        search: [example.com]
        addresses: [192.0.2.2, 192.0.2.3]

# /etc/sssd/sssd.conf, in the domain section
dyndns_update = False

If the segment has DHCP — the protocol by which a node at boot broadcasts a request to a server for an address, mask, gateway and DNS and gets them on lease for a period — the node addresses and the floating address must be excluded from its scope, otherwise the server will one day hand one of them to someone else.

The packages are the same on all three nodes:

apt install postgresql-18 patroni python3-etcd etcd-server etcd-client pgbackrest prometheus-postgres-exporter
systemctl disable --now patroni etcd
pg_dropcluster 18 main --stop

On installation the postgresql-18 package creates and starts the 18-main cluster — it has to go, because PostgreSQL will be started by Patroni, with its own data directory and its own config. The etcd-server package also starts right after installation, and that's the second pitfall, more on it below.

The python3-etcd package is the third pitfall, and the most annoying one. Without it Patroni starts and crashes with the message:

Can not find suitable configuration of distributed configuration store
Available implementations: consul, kubernetes

The patroni package in Ubuntu doesn't pull in the etcd client as a dependency, only recommends it, and a minimal installation doesn't install recommendations. etcd support shows up in the list of implementations only with python3-etcd installed.

etcd with TLS

You can run etcd without encryption: three nodes in a private segment, who's going to get in there. But the same segment often hosts machines that shouldn't be trusted, such as CI runners executing other people's code. An open etcd without authentication lets anyone in the segment rewrite the leader key and trigger a switchover or a shutdown of the cluster. So etcd should be locked down with TLS with client certificate verification on both sides: between members and from clients.

The TLS here is ordinary: the server presents a certificate signed by our certificate authority, the client verifies it against that authority's root certificate and presents its own, which the server verifies the same way. Since the authority is our own, we choose certificate lifetimes ourselves. For an internal cluster it's reasonable to issue the root for ten years and node certificates for five: a scheduled renewal will happen once in the machines' lifetime. etcd rereads its certificate files on change without a restart, while Patroni and vip-manager read the client certificate at startup and must be restarted one at a time after a renewal; restarting Patroni doesn't touch PostgreSQL.

The root:

openssl req -x509 -newkey ec -pkeyopt ec_paramgen_curve:prime256v1 -nodes -days 3650 \
    -subj "/CN=etcd CA example.com" \
    -addext "basicConstraints=critical,CA:TRUE" \
    -addext "keyUsage=critical,keyCertSign,cRLSign" \
    -keyout ca.key -out ca.crt

The two -addext options aren't here for decoration, and this is the fourth pitfall. By default openssl req -x509 doesn't put the keyUsage extension into the certificate. OpenSSL forgives this when verifying the chain, and curl works with such a root. But Python, in which Patroni is written, verifies certificates in strict mode since OpenSSL 3.5 and rejects a root without keyUsage:

certificate verify failed: CA cert does not include key usage extension

Patroni logs only certificate verify failed and waits for etcd forever, while etcdctl and curl with the same files work, which is confusing. It's diagnosed with a direct call from Python:

sudo -u postgres python3 -c "
import urllib3
h = urllib3.PoolManager(cert_reqs='CERT_REQUIRED', ca_certs='/etc/patroni/pki/ca.crt',
    cert_file='/etc/patroni/pki/client.crt', key_file='/etc/patroni/pki/client.key')
print(h.request('GET', 'https://192.0.2.11:2379/version').data)"

Node certificates: one server and one client certificate per node, with the node's name, short name and address in the SAN and with both purposes, serverAuth and clientAuth:

cat > db1.ext <<EOF
subjectAltName=DNS:db1.example.com,DNS:db1,IP:192.0.2.11,IP:127.0.0.1
extendedKeyUsage=serverAuth,clientAuth
keyUsage=digitalSignature
basicConstraints=CA:FALSE
EOF
for kind in server client; do
    openssl req -newkey ec -pkeyopt ec_paramgen_curve:prime256v1 -nodes \
        -subj "/CN=db1.example.com" -keyout db1-$kind.key -out db1-$kind.csr
    openssl x509 -req -in db1-$kind.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
        -days 1825 -extfile db1.ext -out db1-$kind.crt
done

The server certificate and its key go into /etc/etcd/pki/ owned by etcd, the client one into /etc/patroni/pki/ owned by postgres, because Patroni runs as that user; vip-manager runs as root and reads the same client certificate. The root's private key isn't needed on the nodes and must not be stored there.

etcd configuration on Ubuntu is environment variables in /etc/default/etcd, on each node with its own name and address:

ETCD_NAME=db1
ETCD_DATA_DIR=/var/lib/etcd/default
ETCD_LISTEN_PEER_URLS=https://192.0.2.11:2380
ETCD_LISTEN_CLIENT_URLS=https://192.0.2.11:2379,https://127.0.0.1:2379
ETCD_INITIAL_ADVERTISE_PEER_URLS=https://192.0.2.11:2380
ETCD_ADVERTISE_CLIENT_URLS=https://192.0.2.11:2379
ETCD_INITIAL_CLUSTER=db1=https://192.0.2.11:2380,db2=https://192.0.2.12:2380,db3=https://192.0.2.13:2380
ETCD_INITIAL_CLUSTER_STATE=new
ETCD_INITIAL_CLUSTER_TOKEN=db-etcd-example-com
ETCD_CERT_FILE=/etc/etcd/pki/server.crt
ETCD_KEY_FILE=/etc/etcd/pki/server.key
ETCD_TRUSTED_CA_FILE=/etc/etcd/pki/ca.crt
ETCD_CLIENT_CERT_AUTH=true
ETCD_PEER_CERT_FILE=/etc/etcd/pki/server.crt
ETCD_PEER_KEY_FILE=/etc/etcd/pki/server.key
ETCD_PEER_TRUSTED_CA_FILE=/etc/etcd/pki/ca.crt
ETCD_PEER_CLIENT_CERT_AUTH=true
ETCD_AUTO_COMPACTION_RETENTION=1
ETCD_ENABLE_V2=false

Now the second pitfall. On installation the etcd-server package starts with default settings and creates in /var/lib/etcd/default a single-member cluster named default with the address http://localhost:2380. If you then just write the config and restart the service, the ETCD_INITIAL_* parameters are ignored: they are read only when the data directory is created. As a result each node keeps running its own single-member cluster, and with the same member ID, because it's derived from the name and address, which are identical on all three. etcdctl endpoint status across the three addresses will show three "leaders" with the same ID, and Patroni on different nodes will see different stores. The cure is to stop etcd on all nodes, delete the data directory and start again:

systemctl stop etcd && rm -rf /var/lib/etcd/default
systemctl enable --now etcd

When starting the first node, systemctl start may time out: a notify-type service reports readiness only after the cluster has reached quorum, and the second node isn't running yet. That's not an error: as soon as the second one starts, the first completes and becomes active. Verification:

E="--cacert /etc/etcd/pki/ca.crt --cert /etc/patroni/pki/client.crt --key /etc/patroni/pki/client.key"
etcdctl --endpoints=https://192.0.2.11:2379,https://192.0.2.12:2379,https://192.0.2.13:2379 $E endpoint status -w table
etcdctl --endpoints=https://127.0.0.1:2379 $E member list -w table

The table must show three different IDs, one IS LEADER, and member addresses https://192.0.2.1x:2380, not http://localhost:2380. And while you're at it, make sure etcd refuses without a client certificate: the same endpoint health without --cert must return an error.

Patroni

Patroni's configuration is /etc/patroni/config.yml, identical in meaning on all nodes and differing only in the name and addresses. It has three parts: how to find etcd, what to do on first initialization (bootstrap) and how to run the local PostgreSQL.

scope: db
namespace: /service/
name: db1

restapi:
  listen: 192.0.2.11:8008
  connect_address: 192.0.2.11:8008

etcd3:
  hosts:
    - 192.0.2.11:2379
    - 192.0.2.12:2379
    - 192.0.2.13:2379
  protocol: https
  cacert: /etc/patroni/pki/ca.crt
  cert: /etc/patroni/pki/client.crt
  key: /etc/patroni/pki/client.key

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    synchronous_node_count: 1
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        max_connections: 300
        shared_buffers: 1GB
        effective_cache_size: 3GB
        work_mem: 16MB
        maintenance_work_mem: 256MB
        wal_level: replica
        hot_standby: "on"
        wal_log_hints: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 1GB
        password_encryption: scram-sha-256
  initdb:
    - encoding: UTF8
    - locale: en_US.UTF-8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.0.2.11:5432
  data_dir: /var/lib/postgresql/18/patroni
  bin_dir: /usr/lib/postgresql/18/bin
  pgpass: /var/lib/postgresql/.pgpass-patroni
  authentication:
    superuser:
      username: postgres
      password: "..."
    replication:
      username: replicator
      password: "..."
    rewind:
      username: rewind
      password: "..."
  parameters:
    unix_socket_directories: /var/run/postgresql
  pg_hba:
    - local all all peer
    - host all all 127.0.0.1/32 scram-sha-256
    - host replication replicator 192.0.2.0/24 scram-sha-256
    - host all all 0.0.0.0/0 scram-sha-256

A few words on what matters here.

etcd3.hosts lists all three members, not 127.0.0.1: if etcd on this node is stopped, Patroni keeps working through the other two. I tested that — more on it below.

ttl, loop_wait and retry_timeout set the reaction speed. The leader key lives for ttl seconds, and the leader renews it every loop_wait seconds. If the leader died silently, the key expires after ttl, the others notice on their next iteration, and the switchover takes up to ttl + loop_wait, i.e. about 40 seconds with the defaults. You can lower them, but then a brief network hiccup between hypervisors will trigger a switchover out of nowhere. For applications that tolerate a pause of up to a minute, the defaults are the right choice.

synchronous_mode: true with synchronous_node_count: 1 means that one of the two replicas is always synchronous: the primary doesn't confirm a transaction to the client until it has been written on that replica. If the primary fails, Patroni promotes exactly that replica, and not a single confirmed transaction is lost. The failure of the synchronous replica itself doesn't stop writes: Patroni makes the other one synchronous, and for the time when there are no replicas at all it lets the primary work without confirmation (the strict mode synchronous_mode_strict must be off for that, which is the default).

use_pg_rewind and the rewind role are needed so that a former primary, once back, can become a replica of the new one: pg_rewind rolls back in its data directory the records the new primary doesn't have, instead of copying everything over again. That's also what wal_log_hints: on in the parameters is for.

This is the place to explain what a timeline is, because it will appear further on in every patronictl list table and in the discussion of backups. WAL is a continuous sequence of records, and each record is labelled not only with a position, the LSN, but also with a timeline number — the number of a branch of the database's history. While the cluster has one primary, there is one timeline. Each promotion of a replica to primary starts a new one: the number goes up by one, and a small history file like 00000003.history lands in the archive, recording from which timeline and at which position the new one branched off. Why this is needed becomes clear on failure: primary db3 died, db2 was promoted and started writing its own transactions; then db3 came back, and its WAL has records after the point where db2 split off, and they contradict what db2 wrote afterwards. Without timelines nobody could tell whose history is the real one. With them it's unambiguous: db2 writes timeline 3, db3 stayed on timeline 2, and to become a replica again it must roll back its records after the fork point and then read timeline 3. That's exactly what pg_rewind does using the history file. In a healthy cluster the TL column in patronictl list is the same for all nodes; different numbers mean a replica hasn't yet switched to the primary's history or is stuck. When restoring from a backup you also choose the timeline: by default recovery_target_timeline = latest, i.e. the branch the cluster is on now, but you can also ask for an old one abandoned during a switchover.

pg_hba repeats the usual rules: local connections over the socket via peer (the OS user postgres logs in as the postgres role without a password; that's how Patroni itself and psql on the node work), network connections for everyone via SCRAM-SHA-256. SCRAM-SHA-256 is a PostgreSQL password authentication method in which the password is transmitted neither in the clear nor as a hash: both sides prove knowledge of the password by exchanging random values and HMACs, and the catalog stores only a salt and a key derived from the password. The bootstrap section is applied once, when the first node initializes the cluster; after that all these settings live in etcd and must be changed via patronictl edit-config, not in the file.

Start Patroni on one node first:

systemctl enable --now patroni
patronictl -c /etc/patroni/config.yml list

Half a minute later a leader with State: running appears in the table. The config.yml file contains passwords, so it's owned by postgres with mode 600. Then start Patroni on the other two: each will find the existing cluster in etcd, take a pg_basebackup from the leader and join as a replica; a minute later patronictl list shows the leader, a Sync Standby and a Replica, all with the same timeline and zero lag.

If something went wrong on the first attempt and Patroni managed to initialize a cluster you want to recreate from scratch, patronictl remove db deletes the cluster from etcd, after which the data directory on the nodes has to be wiped.

vip-manager

vip-manager isn't installed on Ubuntu from the repository; the deb package comes from the project's releases on GitHub. Its configuration is /etc/default/vip-manager.yml:

interval: 1000
trigger-key: "/service/db/leader"
trigger-value: "db1"
ip: 192.0.2.10
netmask: 24
interface: eth0
hosting-type: basic
dcs-type: etcd
dcs-endpoints:
  - https://192.0.2.11:2379
  - https://192.0.2.12:2379
  - https://192.0.2.13:2379
etcd-ca-file: /etc/patroni/pki/ca.crt
etcd-cert-file: /etc/patroni/pki/client.crt
etcd-key-file: /etc/patroni/pki/client.key
retry-num: 3
retry-after: 250

trigger-key is Patroni's leader key: namespace + scope + /leader. trigger-value is different on each node — the node's name from the Patroni config. Once a second vip-manager reads the key and, if the value matches its name, makes sure the address is up on the interface, otherwise that it's down. After systemctl enable --now vip-manager on all three nodes, the address appears on the leader:

ip -4 -br addr show eth0

A separate note on how applications connect to the cluster. The floating address suits all clients at once and requires no changes in them: the database name in DNS simply points to 192.0.2.10. But clients on libpq and Npgsql also have their own way: list all nodes in the connection string and ask for the primary. For libpq that's host=db1,db2,db3 target_session_attrs=read-write, for Npgsql Host=db1,db2,db3;Target Session Attributes=ReadWrite. The client tries the nodes in turn and stays on the one that reports it is not in recovery mode.

If an application uses LISTEN and NOTIFY, you need to know that they work only on the primary: LISTEN is forbidden on replicas, and notifications are neither persisted nor replicated. On a primary switchover the connection in which the application waits for notifications breaks: Patroni demotes the old node to a replica and closes its connections. The application must reconnect, repeat LISTEN and read from the table whatever a notification may have announced during the switchover window. The usual transactional outbox pattern works exactly this way: a notification only speeds up delivery, and the table remains the source of truth. One more thing: a connection that waits for notifications and sends nothing learns about the silent death of the primary only through TCP keepalive, which on Linux defaults to two hours. In Npgsql, Keepalive=30 in the connection string saves the day; in libpq, keepalives_idle=30.

Testing

Building the cluster is half the job; you need to know how it behaves under failure before failure happens. For measurements I wrote a probe on one of the nodes: every half second it connects to the floating address, inserts a row with the time and the server address and logs whether it succeeded. The log shows every interval when writing was impossible.

#!/bin/bash
while true; do
  t=$(date +%s.%N)
  if out=$(psql "host=192.0.2.10 dbname=probe user=probe connect_timeout=2" -AtXq \
       -c "insert into probe(ts, server) values (now(), inet_server_addr()) returning server" 2>&1)
  then echo "$t ok $out"; else echo "$t fail ${out//$'\n'/ }"; fi
  sleep 0.5
done

Four scenarios and the results:

Scenario	How to trigger	Write pause
Planned switchover	`patronictl switchover db --leader db1 --candidate db3 --force`	1.6 s
Hard death of the primary	power off the leader VM on the hypervisor without a shutdown	33 s
etcd stopped on the leader node	`systemctl stop etcd` for 45 seconds	0
Replica reboot	`systemctl reboot`	0

A planned switchover is almost unnoticeable: Patroni stops the primary in fast mode, waits for the candidate to replay the last WAL records, promotes it, the leader key changes, vip-manager moves the address. A hard death is those same ttl plus an iteration: the synchronous replica became the primary, the address moved, and the old primary, once powered back on, saw the new timeline and became a replica on its own a quarter of a minute after booting. Stopping etcd on the leader caused not a single failure, because the config lists all three members. A replica reboot doesn't exist as far as clients are concerned.

The table shows the main point: if an application survives a minute without its database, this cluster gives you high availability without a single manual action. If you need it faster, you can lower ttl to 20 and loop_wait to 5, but every second here is bought with sensitivity to network delays.

The cost of synchronous replication can be measured too. On the leader:

sudo -u postgres pgbench -i -s 5 probe
sudo -u postgres pgbench -c 4 -j 2 -T 20 probe
sudo -u postgres env PGOPTIONS="-c synchronous_commit=off" pgbench -c 4 -j 2 -T 20 probe

I got 11.5 ms per transaction in synchronous mode versus 0.76 ms without waiting for the replica. That's not the network — the network between hypervisors adds fractions of a millisecond; it's fsync on the replica's disk, which is expensive on virtual machines. For web applications with dozens of writes per second the difference is unnoticeable. If you hit the limit, synchronous_commit = remote_write keeps waiting for the replica but takes its fsync off the critical path: a transaction is considered confirmed when the replica has received the WAL into memory, and is lost only if both machines die at the same time.

Backups

pgBackRest is installed on all three nodes with the same config /etc/pgbackrest/pgbackrest.conf. The repository is in S3-compatible storage: any provider with an S3 API will do, that is, with an HTTP interface where objects are written and read with PUT and GET requests to a bucket/key address, with requests signed by an access key.

[global]
repo1-type=s3
repo1-s3-endpoint=storage.example.com
repo1-s3-region=ru-central1
repo1-s3-bucket=db-cluster-backups
repo1-s3-key=...
repo1-s3-key-secret=...
repo1-path=/pgbackrest
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=...
repo1-retention-full=2
repo1-retention-diff=7
repo1-bundle=y
repo1-block=y
process-max=2
compress-type=zst
log-path=/var/log/pgbackrest
start-fast=y

[db]
pg1-path=/var/lib/postgresql/18/patroni
pg1-port=5432
pg1-socket-path=/var/run/postgresql

The file contains a key and a passphrase, so it's owned by postgres with mode 640. Client-side encryption protects the contents from anyone who gains access to the bucket; the flip side is that without the passphrase nobody can read the backups, including you, so it absolutely must live in a password manager, not only in this file. Retention rules in the bucket itself have no business here: retention is handled by pgBackRest (retention-*), and for that the service account needs permission to delete objects. The only lifecycle rule worth setting up on the bucket is aborting incomplete multipart uploads after a day.

WAL archiving is enabled not in the PostgreSQL config but through Patroni, because Patroni writes that config:

patronictl -c /etc/patroni/config.yml edit-config db --force \
  -s "postgresql.parameters.archive_mode=on" \
  -s "postgresql.parameters.archive_command=pgbackrest --stanza=db archive-push %p" \
  -s "postgresql.parameters.archive_timeout=300" \
  -s "postgresql.recovery_conf.restore_command=pgbackrest --stanza=db archive-get %f \"%p\""
patronictl -c /etc/patroni/config.yml restart db --force

archive_mode requires a restart, and patronictl restart restarts the members one at a time. The restore_command in recovery_conf goes to the replicas: a lagging replica that can no longer get enough WAL from the primary will fetch what's missing from the repository. Only the primary archives; on replicas archive_command isn't called. Next: initializing the repository, a check and the first backup, all as postgres on the primary:

pgbackrest --stanza=db stanza-create
pgbackrest --stanza=db check
pgbackrest --stanza=db --type=full backup
pgbackrest --stanza=db info

check not only verifies access to the bucket but also makes PostgreSQL switch the WAL segment and waits for it to appear in the archive: it's an end-to-end check of archive_command.

The nightly backup is started by the postgres user's cron on all three nodes with the same script, which decides by itself whether it's the leader:

#!/bin/bash
set -euo pipefail
if [[ "$(psql -AtXq -c 'select pg_is_in_recovery()')" != "f" ]]; then
    exit 0
fi
type=diff
[[ "$(date +%u)" == "7" ]] && type=full
exec pgbackrest --stanza=db --type="$type" --log-level-console=warn backup

A backup that has never been restored isn't a backup. A restore test on a replica, into a separate directory and on a separate port, takes a couple of minutes:

D=/var/lib/postgresql/18/restore-test
sudo -u postgres pgbackrest --stanza=db --pg1-path=$D restore
sudo -u postgres /usr/lib/postgresql/18/bin/pg_ctl -D $D -w start \
    -o "-p 5433 -c archive_mode=off -c listen_addresses=127.0.0.1 -c unix_socket_directories=/tmp"
sudo -u postgres psql -h /tmp -p 5433 -c "select datname from pg_database"
sudo -u postgres /usr/lib/postgresql/18/bin/pg_ctl -D $D -m fast stop
sudo rm -rf $D

And here is the fifth pitfall, the most dangerous of all. The restored instance replays WAL from the archive to the end and becomes a standalone primary on a new timeline. Its directory contains a postgresql.conf written by Patroni, with archive_mode=on and an archive_command that writes to the same repository. If you start it without -c archive_mode=off, it will start putting WAL of its own timeline into the archive, and when the live cluster moves to the same timeline number on its next switchover, its records will collide with the foreign ones. Checking that the archive wasn't harmed is simple: after the test, pgbackrest info must show the same wal archive min/max boundaries as before.

Point-in-time recovery of the production cluster is done the same way, only with --type=time --target="2026-10-19 12:00:00+00" and into the main data directory of one node while Patroni is stopped on all nodes; the other two then get a patronictl reinit and are re-copied from it.

Maintenance

The everyday commands fit into a short list.

patronictl list — who is the leader, who is the synchronous replica, timelines and lag.
Node maintenance: a replica can simply be rebooted; the leader is first switched over with patronictl switchover, then rebooted. Upgrading the patroni or vip-manager package is a normal systemctl restart; PostgreSQL keeps running meanwhile.
PostgreSQL parameters are changed via patronictl edit-config, not in files: Patroni will overwrite the file on the next start. Parameters that require a restart are marked by Patroni in patronictl list as pending restart; they are applied with patronictl restart db <node> one at a time, the leader last after a switchover.
A node that has fallen behind or is broken: patronictl reinit db <node> re-copies it from the leader.
Adding a node: the same packages and certificates, etcdctl member add, the new member in ETCD_INITIAL_CLUSTER with ETCD_INITIAL_CLUSTER_STATE=existing, the Patroni config, and it joins as a replica by itself.
etcd certificates: watch the expiry (openssl x509 -enddate -noout -in /etc/etcd/pki/server.crt), reissue with the same script that issued them; etcd picks them up by itself, Patroni and vip-manager are restarted one at a time.

For monitoring, Patroni exposes /metrics on the REST API port with metrics like patroni_primary, patroni_sync_standby, patroni_xlog_replay_location and the like, and prometheus-postgres-exporter on each node provides the usual PostgreSQL metrics. Alerts worth setting up right away: a leader without a synchronous replica for more than a few minutes, replica lag, an unavailable etcd member, the floating address not responding, the last backup older than a day.

The pitfalls in one list

The VIP in DNS under a node's name. The DHCP server and sssd register the node's addresses in DNS by themselves, and the floating address lives on the same interface; the nodes need static addresses, static A and PTR records and dyndns_update = False in sssd.
etcd from the package starts immediately and creates a single-member cluster with the same ID on all nodes; after writing the config the data directory must be deleted, otherwise ETCD_INITIAL_* are ignored.
python3-etcd isn't installed together with patroni; without it Patroni doesn't see the etcd implementation and lists only consul and kubernetes.
A root without keyUsage passes with OpenSSL and curl, but Python on OpenSSL 3.5 rejects it; Patroni logs only certificate verify failed and waits for etcd forever.
A restored instance with archive_mode=on writes its timeline into the shared archive and corrupts it for the live cluster; start it only with archive_mode=off.
A systemctl start etcd timeout on the first node while assembling the cluster isn't an error; the service is waiting for quorum.
pkill -f by script name also kills the SSH session you run it from if the pattern appears in its command line; this isn't about the cluster, but it cost me a few minutes of bewilderment.

Summary

Three virtual machines, five services on each and one config for all. A planned switchover takes two seconds, losing a machine half a minute, and all of it without human involvement; confirmed transactions aren't lost thanks to the synchronous replica, and the WAL archive in the bucket lets you roll back to any point in time. The most valuable part of this build isn't the instructions but the testing: until you power off the primary by hand and look at the probe's log, the cluster is highly available only on paper.