WIUNIX ACADEMY
PostgreSQL with Patroni: replication, quorum and safe switchover
Page 2 of 6. Review the earlier steps and prerequisites before continuing.
YOUR SYSTEM. YOUR STEPS.
Where are you working?
Choose your OS and version. The prerequisites, commands and troubleshooting follow your environment.
USE YOUR OWN VALUES
Configure this tutorial
Set the example values for your environment; related explanations and copy panels update together. Never enter passwords, private keys or tokens. Values stay in this tab and are not sent to the server or share links. Check actual DNS, certificates and network policy before running commands.
Different IP addresses, domains or ports? Open the settings below, change the values and select “Apply to tutorial”, then copy the updated commands.
Set values for my environment11 editable values
LET’S BUILD
Install packages without a default cluster
Fresh VMs only. Masks prevent vendor services racing Patroni; disabling automatic main-cluster creation prevents an unintended database on installation. The existing package configuration is inspected first and belongs only to these disposable machines. The PGDG helper configures the matching signed repository. Stop for a missing candidate, PostgreSQL major other than18, or Patroni older than4.1.3 (the etcd authentication fixes are required). Ubuntu26.10 uses its own4.1.4 Patroni candidate, not a substituted suite. Do not remove masks or use pg_ctlcluster later: the dedicated Patroni unit will own PostgreSQL.
Disposable database VM; Bash as administrator
set -eu
. /etc/os-release
test "$VERSION_CODENAME" = 'stonking'
test "$(dpkg --print-architecture)" = amd64
sudo systemctl mask postgresql.service postgresql@18-main.service patroni.service
sudo apt-get update
sudo apt-get install --no-install-recommends postgresql-common curl ca-certificates gnupg nano python3
sudo sed -i -E '/^[# ]*create_main_cluster[ ]*=/d' /etc/postgresql-common/createcluster.conf
printf '%s\n' 'create_main_cluster = false' | sudo tee -a /etc/postgresql-common/createcluster.conf >/dev/null
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo apt-get update
apt-cache policy postgresql-18 patroni python3-etcd python3-psycopg2
sudo apt-get install --no-install-recommends postgresql-18 postgresql-client-18 patroni python3-etcd python3-psycopg2
/usr/lib/postgresql/18/bin/postgres --version
patroni --version
dpkg-query -W postgresql-18 patroni python3 python3-etcd
pg_lsclusters
sudo systemctl is-active postgresql.service patroni.service || true
PostgreSQL18 and Patroni≥4.1.3 installed, no main cluster and no vendor daemon running.
LET’S BUILD
Install the reviewed etcd binaries
Run on every fresh VM. Download the official release and its published checksum file over HTTPS, compare before extracting, then install into a new version-specific directory. A checksum from the same publisher detects mismatches; it is not independent publisher-signature verification. No etcd service starts here. Inspect the release support/upgrade guide before choosing a later version; never replace a running DCS in-place using this fresh-install panel.
Disposable database VM; Bash as administrator
set -eu
test ! -e /opt/academy-etcd-3.7.2
download=$(mktemp -d)
cd "$download"
curl -fLO https://github.com/etcd-io/etcd/releases/download/v3.7.2/etcd-v3.7.2-linux-amd64.tar.gz
curl -fLO https://github.com/etcd-io/etcd/releases/download/v3.7.2/SHA256SUMS
grep ' etcd-v3.7.2-linux-amd64.tar.gz$' SHA256SUMS > selected.sha256
test "$(wc -l < selected.sha256)" -eq 1
sha256sum -c selected.sha256
tar -xzf etcd-v3.7.2-linux-amd64.tar.gz
sudo install -d -m 0755 /opt/academy-etcd-3.7.2
sudo install -m 0755 etcd-v3.7.2-linux-amd64/etcd etcd-v3.7.2-linux-amd64/etcdctl /opt/academy-etcd-3.7.2/
/opt/academy-etcd-3.7.2/etcd --version
Verified archive, etcd3.7.2 binaries only; no daemon yet.
Prepare PKI and network restrictions first
Obtain certificates through your existing private PKI, not a public self-signed bypass. On each node provision ca.crt; a node server.crt/server.key with that node’s IP SAN and serverAuth/clientAuth for etcd peer traffic; a patroni.crt/patroni.key client certificate with an empty subject CN (clientAuth); and api.crt/api.key with that node’s IP SAN and serverAuth/clientAuth. Keep CA signing keys off the nodes. Provision a separate etcd admin certificate with CN root in the administrator’s protected store. Never put private key material in browser settings, shell history or Git.
Allow TCP 5432 only between the three database IPs and 10.77.18.50 for this lab; TCP 8008 only between peers and management; TCP 2379 only for DCS clients/admin; TCP 2380 only between voters. Bind explicitly to private IPs, never world-trust or a public management listener. Retain your firewall owner’s existing SSH/console rules. No firewall mutation panel is supplied because the existing network policy must own it.
LET’S BUILD
Create only the new lab root
Run on each VM after checking parent ownership and symlinks. The example reuses postgres for lab processes; production should separate DCS identity and privileges. Copy the PKI files with your protected transfer method into the new tls directory; use 0600 on private keys and config, never copy a CA private key. The following checks show certificate metadata only.
For etcd peers, SANs must also match the actual peer connection source. On a multi-homed VM inspect routing and source selection before starting.
Disposable database VM; Bash as administrator
set -eu
test ! -e '/srv/academy-pg18'
test ! -L '/srv/academy-pg18'
sudo install -d -o postgres -g postgres -m 0700 '/srv/academy-pg18'
sudo install -d -o postgres -g postgres -m 0700 '/srv/academy-pg18/tls' '/srv/academy-pg18/socket'
# Transfer the reviewed certificates and keys into tls now, then continue.
sudo chown postgres:postgres '/srv/academy-pg18/tls/'*
sudo chmod 0600 '/srv/academy-pg18/tls/'*.key
sudo chmod 0644 '/srv/academy-pg18/tls/'*.crt
sudo openssl x509 -in '/srv/academy-pg18/tls/server.crt' -noout -subject -ext subjectAltName
sudo openssl verify -CAfile '/srv/academy-pg18/tls/ca.crt' '/srv/academy-pg18/tls/server.crt' '/srv/academy-pg18/tls/api.crt' '/srv/academy-pg18/tls/patroni.crt'
Fresh private root and certificates with correct node SANs, trust chain and key ownership.
LET’S BUILD
Save the guarded configuration generator
Use the next file panel as a Python file, not a terminal command. It writes JSON, a valid YAML subset, and refuses to replace existing configs/data. Run it once per VM with node index1,2 or3. Every generated file describes only that VM; all three share one DCS scope. The root must remain under a dedicated /srv child; the browser path type alone cannot enforce safe operational ownership.
Disposable database VM; Bash as administrator
sudo -u postgres nano '/srv/academy-pg18/make-config.py'
Editor opened; save the following complete file.
LET’S BUILD
File: per-node configuration generator
The example deliberately disables the watchdog only for disposable lab VMs. Production automatic promotion must wait for independently tested fencing/watchdog and timing review. Never load a host-reset watchdog on a shared server just to complete this exercise.
Disposable database VM; Bash as administrator
import json, pathlib, sys, ipaddress
root = pathlib.Path('/srv/academy-pg18')
ips = ['10.77.18.11', '10.77.18.12', '10.77.18.13']
network = ipaddress.ip_network('10.77.18.0/24')
assert len(set(ips)) == 3
assert all(ipaddress.ip_address(ip) in network for ip in ips)
assert root.is_absolute() and len(root.parts) >= 3 and not root.is_symlink()
assert str(root).startswith('/srv/') and str(root) != '/srv/wiunix'
index = int(sys.argv[1]) - 1
assert index in range(3)
name, ip = f'pg{index+1}', ips[index]
pg, api, dc, peer = 5432, 8008, 2379, 2380
assert len({pg, api, dc, peer}) == 4
tls = str(root / 'tls')
for filename in ('ca.crt', 'server.crt', 'server.key', 'api.crt', 'api.key', 'patroni.crt', 'patroni.key'):
assert (root / 'tls' / filename).is_file(), filename
for filename in ('patroni.yml', 'etcd.yml', 'data', 'etcd-data'):
assert not (root / filename).exists(), filename
cluster = ','.join(f'pg{i+1}=https://{address}:{peer}' for i,address in enumerate(ips))
etcd = {
'name': name, 'data-dir': str(root/'etcd-data'),
'listen-client-urls': f'https://{ip}:{dc}',
'advertise-client-urls': f'https://{ip}:{dc}',
'listen-peer-urls': f'https://{ip}:{peer}',
'initial-advertise-peer-urls': f'https://{ip}:{peer}',
'initial-cluster': cluster, 'initial-cluster-state': 'new',
'initial-cluster-token': 'academy_pg18-dcs',
'client-transport-security': {'cert-file': tls+'/server.crt', 'key-file': tls+'/server.key', 'trusted-ca-file': tls+'/ca.crt', 'client-cert-auth': True},
'peer-transport-security': {'cert-file': tls+'/server.crt', 'key-file': tls+'/server.key', 'trusted-ca-file': tls+'/ca.crt', 'client-cert-auth': True}}
hba = ['local all postgres peer']
hba += [f'hostssl replication replicator {address}/32 scram-sha-256' for address in ips]
hba += [f'hostssl all postgres {address}/32 scram-sha-256' for address in ips + ['10.77.18.50']]
config = {
'scope': 'academy_pg18', 'namespace': '/academy/', 'name': name,
'restapi': {'listen': f'{ip}:{api}', 'connect_address': f'{ip}:{api}',
'certfile': tls+'/api.crt', 'keyfile': tls+'/api.key', 'cafile': tls+'/ca.crt',
'verify_client': 'required', 'authentication': {'username': 'operator', 'password': 'REPLACE_API_SECRET'}},
'ctl': {'cacert': tls+'/ca.crt', 'certfile': tls+'/api.crt', 'keyfile': tls+'/api.key'},
'etcd3': {'hosts': [f'{address}:{dc}' for address in ips], 'protocol': 'https', 'username': 'patroni', 'password': 'REPLACE_DCS_SECRET',
'cacert': tls+'/ca.crt', 'cert': tls+'/patroni.crt', 'key': tls+'/patroni.key'},
'bootstrap': {
'dcs': {'ttl': 30, 'loop_wait': 10, 'retry_timeout': 10,
'maximum_lag_on_failover': 1048576, 'check_timeline': True,
'synchronous_mode': True, 'synchronous_mode_strict': True, 'synchronous_node_count': 1,
'postgresql': {'use_pg_rewind': True, 'use_slots': True,
'parameters': {'wal_level': 'replica', 'hot_standby': 'on',
'max_wal_senders': 10, 'max_replication_slots': 10,
'wal_keep_size': '256MB', 'max_slot_wal_keep_size': '2GB',
'password_encryption': 'scram-sha-256', 'synchronous_commit': 'on'}}},
'initdb': [{'encoding': 'UTF8'}, {'auth-host': 'scram-sha-256'}, {'auth-local': 'peer'}, 'data-checksums'], 'pg_hba': hba},
'postgresql': {'listen': f'{ip}:{pg}', 'connect_address': f'{ip}:{pg}',
'data_dir': str(root/'data'), 'bin_dir': '/usr/lib/postgresql/18/bin',
'pgpass': str(root/'pgpass'),
'authentication': {
'superuser': {'username': 'postgres', 'password': 'REPLACE_POSTGRES_SECRET', 'sslmode': 'verify-full', 'sslrootcert': tls+'/ca.crt'},
'replication': {'username': 'replicator', 'password': 'REPLACE_REPLICATION_SECRET', 'sslmode': 'verify-full', 'sslrootcert': tls+'/ca.crt'}},
'parameters': {'unix_socket_directories': str(root/'socket'), 'ssl': 'on',
'ssl_cert_file': tls+'/server.crt', 'ssl_key_file': tls+'/server.key', 'ssl_ca_file': tls+'/ca.crt'},
'remove_data_directory_on_rewind_failure': False,
'remove_data_directory_on_diverged_timelines': False},
'watchdog': {'mode': 'off'},
'tags': {'nofailover': False, 'noloadbalance': False, 'nosync': False}}
for filename, data in [('etcd.yml', etcd), ('patroni.yml', config)]:
with (root/filename).open('x') as f:
json.dump(data, f, indent=2)
f.write('\n')
(root/filename).chmod(0o600)
print(name, ip, 'new configuration files created; edit secrets before starting')
File saved intact with public placeholders resolved; secrets are still sentinel values.