Skip to main content

PostgreSQL 18 & Patroni — Install & Configure

Run everything on this page on the three database nodes — pg-sv01, pg-sv02, pg-sv03.

Install PostgreSQL 18 and Patroni

Add the official PostgreSQL (PGDG) repository, then install PostgreSQL 18 and Patroni:

sudo install -d -m 0755 /etc/apt/keyrings
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
  | sudo gpg --dearmor -o /etc/apt/keyrings/pgdg.gpg
. /etc/os-release
echo "deb [signed-by=/etc/apt/keyrings/pgdg.gpg] https://apt.postgresql.org/pub/repos/apt ${VERSION_CODENAME}-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt-get update

sudo apt-get install -y postgresql-18 postgresql-client-18 patroni python3-etcd

⚠️ Don't skip python3-etcd. Without it (e.g. if you install Patroni with --no-install-recommends), Patroni cannot load the etcd backend and fails to start with: Can not find suitable configuration of distributed configuration store. Available implementations: consul, kubernetes Installing python3-etcd makes the etcd/etcd3 backends available. (We use the etcd3 backend, since etcd 3.5 disables the legacy v2 API by default.)

Remove the distro's default cluster

The Debian/Ubuntu package automatically creates and starts a main cluster on port 5432. Patroni must own that port and its own data directory, so drop the default cluster:

sudo pg_dropcluster --stop 18 main
sudo systemctl disable --now postgresql

sudo install -d -o postgres -g postgres -m 0700 /var/lib/postgresql/18/patroni
sudo install -d -o postgres -g postgres -m 0755 /etc/patroni

Firewall (UFW)

Allow PostgreSQL and the Patroni REST API from the other DB nodes and the HAProxy node:

for p in 10.100.100.104 10.100.100.105 10.100.100.106 10.100.100.103; do
  sudo ufw allow from $p to any port 5432 proto tcp
  sudo ufw allow from $p to any port 8008 proto tcp
done

Patroni configuration

Create /etc/patroni/patroni.yml on each node. Only name and the two connect_address lines differ per node — set them to that node's hostname and IP.

scope: pg-cluster
namespace: /service/
name: pg-sv01                       # <-- pg-sv02 / pg-sv03 on the other nodes

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.100.100.104:8008    # <-- this node's IP
  authentication:
    username: admin
    password: ChangeMe_PatroniREST        # protects unsafe REST methods only

etcd3:
  hosts:
    - 10.100.100.104:2379
    - 10.100.100.105:2379
    - 10.100.100.106:2379

bootstrap:
  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"
        max_connections: 100
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 128MB
        wal_log_hints: "on"          # required for pg_rewind
  initdb:
    - encoding: UTF8
    - data-checksums
  pg_hba:
    - local all all trust
    - host all all 127.0.0.1/32 scram-sha-256
    - host all all 10.100.100.0/24 scram-sha-256
    - host replication replicator 127.0.0.1/32 scram-sha-256
    - host replication replicator 10.100.100.0/24 scram-sha-256

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.100.100.104:5432    # <-- this node's IP
  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: ChangeMe_Postgres
    replication:
      username: replicator
      password: ChangeMe_Replicator
    rewind:
      username: rewind_user
      password: ChangeMe_Rewind
  parameters:
    password_encryption: scram-sha-256

watchdog:
  mode: "off"                        # enable if you provide /dev/watchdog

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

sudo chown postgres:postgres /etc/patroni/patroni.yml
sudo chmod 0600 /etc/patroni/patroni.yml

A few notes on the important settings:

  • bootstrap.dcs is written to etcd once, on first bootstrap; it is the cluster-wide source of truth thereafter (edit it later with patronictl edit-config, not the file).
  • wal_log_hints: "on" + use_pg_rewind: true let a failed primary rejoin quickly without a full re-clone.
  • ttl: 30 is the leader lease — a dead primary's lock expires after ~30 s, which is the upper bound on failover detection time.
  • watchdog: mode: "off" because our VMs have no /dev/watchdog; on bare metal with a real watchdog, set it to automatic for stronger split-brain protection.

systemd service

Create /etc/systemd/system/patroni.service on each node:

[Unit]
Description=Patroni PostgreSQL HA
After=network-online.target etcd.service
Wants=network-online.target

[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/bin/patroni /etc/patroni/patroni.yml
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
TimeoutSec=30
Restart=on-failure

[Install]
WantedBy=multi-user.target

sudo systemctl daemon-reload
sudo systemctl enable patroni

Don't start it yet — the start order matters, and we cover it on the next page.

No comments to display

Back to top