WIUNIX ACADEMY
PostgreSQL with Patroni: replication, quorum and safe switchover
Page 4 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
Give PostgreSQL exactly one process owner
Save the next unit on all three VMs. Patroni starts/stops PostgreSQL under its own child process tree. KillMode process lets Patroni shut its child down gracefully rather than having systemd kill the whole group first. Do not run the distribution PostgreSQL or Patroni units in parallel. The root/data directory must still be absent before the first bootstrap; basebackup initializes standbys and must never target useful data.
Disposable database VM; Bash as administrator
sudo nano /etc/systemd/system/academy-pg-patroni.service
Save the complete unit below with the correct lab root.
LET’S BUILD
File: Patroni owns the database
This unit is for the lab. Watchdog is off and automatic restart does not prove fencing. A production rollout requires a reviewed watchdog/fencing device, fail-closed required mode, resource limits and actual partition/stall tests with console recovery.
Disposable database VM; Bash as administrator
[Unit]
Description=Academy PostgreSQL18 Patroni lab
After=network-online.target academy-pg-etcd.service
Wants=network-online.target
[Service]
Type=simple
User=postgres
Group=postgres
UMask=0077
ExecStart=/usr/bin/patroni /srv/academy-pg18/patroni.yml
KillMode=process
TimeoutStopSec=120
Restart=on-failure
RestartSec=5
[Install]
WantedBy=multi-user.target
One dedicated lifecycle owner, no vendor database unit enabled.
LET’S BUILD
Bootstrap pg1, then clone pg2 and pg3
Run first on pg1, then repeat on pg2 and pg3. Refuse any REPLACE sentinel, unexpected data directory or active vendor service. Initializing pg1 and cloning standbys creates data; it is not an import of an existing database. Strict synchronous mode intentionally blocks writes until a synchronous replica is available. Do not enable boot persistence yet: first inspect roles and the recovery procedure.
Disposable database VM; Bash as administrator
set -eu
sudo -u postgres python3 - <<'PY'
import json, pathlib
p=pathlib.Path('/srv/academy-pg18')
config=json.loads((p/'patroni.yml').read_text())
assert 'REPLACE_' not in json.dumps(config), 'Edit secrets privately first'
assert not (p/'data').exists(), 'Do not overwrite existing data'
PY
if systemctl is-active --quiet postgresql.service || systemctl is-active --quiet patroni.service; then exit 1; fi
sudo -u postgres patroni --validate-config '/srv/academy-pg18/patroni.yml'
sudo systemctl daemon-reload
sudo systemctl start academy-pg-patroni.service
sudo journalctl -u academy-pg-patroni.service -n 40 --no-pager
pg1 initializes; pg2/pg3 basebackup into fresh paths and join as replicas.
LET’S BUILD
Check roles, API trust and WAL replay
On pg1. patronictl reads the protected config and uses mTLS plus the configured API authentication. Do not print its config. The local SQL connection uses peer authentication on the private Unix socket; network replication uses the dedicated SCRAM role and TLS verify-full. The API must reject a request without the client certificate; a certificate error is not a reason to use insecure curl flags.
The output below is a simplified illustrative role summary; record your actual system ID, LSN, timeline and lag.
Disposable database VM; Bash as administrator
sudo -u postgres patronictl -c '/srv/academy-pg18/patroni.yml' list
sudo -u postgres psql -h '/srv/academy-pg18/socket' -p 5432 -d postgres -X -v ON_ERROR_STOP=1 -c "SELECT application_name,state,sync_state,write_lsn,flush_lsn,replay_lsn FROM pg_stat_replication;"
sudo -u postgres psql -h '/srv/academy-pg18/socket' -p 5432 -d postgres -X -c "SELECT slot_name,active,wal_status,restart_lsn FROM pg_replication_slots;"
sudo -u postgres curl --fail --cacert '/srv/academy-pg18/tls/ca.crt' --cert '/srv/academy-pg18/tls/api.crt' --key '/srv/academy-pg18/tls/api.key' 'https://10.77.18.11:8008/cluster'
sudo -u postgres curl --cacert '/srv/academy-pg18/tls/ca.crt' 'https://10.77.18.11:8008/health'
pg1 Leader running
pg2 Sync Standby streaming
pg3 Replica streaming
One leader, one sync standby, another replica; streaming WAL and denied unauthenticated TLS client.
LET’S BUILD
Write a lab marker and read it on a standby
On the current leader create only the uniquely named lab database; do not reuse an existing database. The second panel is read-only and runs on pg2 after streaming catches up. Matching rows establish this marker’s replication, not durability under every failure. If pg2 is the leader already, choose the actual standby and keep the same local-socket command.
Disposable database VM; Bash as administrator
sudo -u postgres createdb -h '/srv/academy-pg18/socket' -p 5432 academy_replication_probe
sudo -u postgres psql -h '/srv/academy-pg18/socket' -p 5432 -d academy_replication_probe -X -v ON_ERROR_STOP=1 <<'SQL'
SET statement_timeout = '15s';
CREATE TABLE probe(id integer PRIMARY KEY, marker text NOT NULL);
INSERT INTO probe VALUES (1, 'before-switchover');
SELECT pg_current_wal_lsn();
SQL
A committed marker and recorded leader LSN; a timeout needs diagnosis, not forced promotion.
LET’S BUILD
Read recovery state and the same marker
Run on standby pg2. Recovery must be true and the row must arrive. Timestamp lag is not a useful zero-lag guarantee on an idle database; compare LSNs and application markers too.
Disposable database VM; Bash as administrator
sudo -u postgres psql -h '/srv/academy-pg18/socket' -p 5432 -d academy_replication_probe -X -v ON_ERROR_STOP=1 <<'SQL'
SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn();
TABLE probe;
SELECT status,sender_host,sender_port FROM pg_stat_wal_receiver;
SQL
Recovery true, marker1 present and WAL receiver streaming from the actual leader.