WIUNIX ACADEMY
PostgreSQL with Patroni: replication, quorum and safe switchover
Page 3 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
Generate the node and edit secrets privately
This panel is for pg1 ONLY. On pg2 replace the final argument1 with2; on pg3 use3. Do not change the cluster IP list. In the protected editor replace all four REPLACE sentinels with independently generated strong passwords, identical for the corresponding role on all three nodes. The superuser is deliberately privileged for this lab, including rewind; production needs a reviewed least-privilege rewind design. Patroni creates the REPLICATION LOGIN role at bootstrap. It must not be an application superuser. Config contains secrets: never print it, commit it or send it as a support attachment.
Disposable database VM; Bash as administrator
set -eu
sudo -u postgres python3 '/srv/academy-pg18/make-config.py' 1
sudo -u postgres nano '/srv/academy-pg18/patroni.yml'
sudo -u postgres patroni --validate-config '/srv/academy-pg18/patroni.yml'
Only new configs created, all sentinels privately replaced, validation succeeds; no database starts.
LET’S BUILD
Save a dedicated etcd unit
On every VM save the next unit. It is a lab-owned service with a dedicated data path; the distribution etcd service is not used. Starting this fresh cluster initializes the selected DCS data directories. Do not use initial-cluster-state new against a recovered or existing DCS.
Disposable database VM; Bash as administrator
sudo nano /etc/systemd/system/academy-pg-etcd.service
Save the complete unit below.
LET’S BUILD
File: etcd service
Stop/restart affects this voter only. With three healthy voters, one restart retains a majority; two concurrent outages lose quorum.
Disposable database VM; Bash as administrator
[Unit]
Description=Academy isolated PostgreSQL lab DCS
After=network-online.target
Wants=network-online.target
[Service]
Type=simple
User=postgres
Group=postgres
UMask=0077
ExecStart=/opt/academy-etcd-3.7.2/etcd --config-file=/srv/academy-pg18/etcd.yml
Restart=on-failure
RestartSec=5
[Install]
WantedBy=multi-user.target
Unit points to the reviewed binary and dedicated config.
LET’S BUILD
Start only the three DCS voters
Run on all three VMs, then use the admin panel below. No Patroni node starts until endpoint health, membership, TLS and scoped access pass. Service logs can include private addresses; inspect locally.
Disposable database VM; Bash as administrator
sudo systemctl daemon-reload
sudo systemctl start academy-pg-etcd.service
sudo systemctl status academy-pg-etcd.service --no-pager
sudo journalctl -u academy-pg-etcd.service -n 40 --no-pager
Three voters running with TLS; no PostgreSQL process for this lab yet.
LET’S BUILD
Give Patroni only its DCS prefix
Once, from a protected admin shell on pg1. Provision root.crt/root.key in the new admin-only directory using your PKI; never give that key to postgres. The root user and role bootstrap etcd auth; Patroni uses the etcd3 gRPC gateway: certificate CN authentication is unavailable there. Configure a separate patroni username/password in its protected file as well as mTLS. The dedicated gateway client certificate must have an empty subject CN: etcd3.7 rejects a CN-bearing certificate at this gateway even with a password. Root admin and peer certificates keep their own CNs. The prefix includes the trailing slash and is limited to this cluster. The scope may expose cluster metadata, never authentication passwords. Run only on an empty DCS: existing users/roles are a stop condition, not something to overwrite. TLS mTLS is enforced before RBAC is enabled.
Disposable database VM; Bash as administrator
set -eu
sudo install -d -m 0700 '/srv/academy-pg18/admin'
# Provision root.crt and root.key here using the protected PKI channel.
sudo chmod 0600 '/srv/academy-pg18/admin/root.key'
dcs_admin() {
sudo env ETCDCTL_ENDPOINTS='https://10.77.18.11:2379,https://10.77.18.12:2379,https://10.77.18.13:2379' ETCDCTL_CACERT='/srv/academy-pg18/tls/ca.crt' ETCDCTL_CERT='/srv/academy-pg18/admin/root.crt' ETCDCTL_KEY='/srv/academy-pg18/admin/root.key' /opt/academy-etcd-3.7.2/etcdctl "$@"
}
dcs_admin endpoint health
dcs_admin member list --write-out=table
dcs_admin user list
dcs_admin user add root --no-password
dcs_admin role add root
dcs_admin user grant-role root root
dcs_admin role add academy-pg
dcs_admin role grant-permission academy-pg readwrite '/academy/academy_pg18/' --prefix=true
# Interactive: enter the DCS password also saved in the protected Patroni file.
dcs_admin user add patroni
dcs_admin user grant-role patroni academy-pg
dcs_admin auth enable
dcs_admin endpoint health
Three healthy member IDs, auth enabled, Patroni limited to the new cluster prefix.
LET’S BUILD
Check the scoped identity before bootstrap
On pg1, run as the same OS identity as Patroni. An empty prefix is expected before bootstrap. Enter the protected DCS password at each prompt. The second read must fail with permission denied. A successful read outside the prefix is a blocking authorization defect; do not continue. Do not use etcd v2 keys or an etcd v2 client configuration: this tutorial uses etcd3.
Disposable database VM; Bash as administrator
sudo -u postgres env ETCDCTL_ENDPOINTS='https://10.77.18.11:2379' ETCDCTL_CACERT='/srv/academy-pg18/tls/ca.crt' ETCDCTL_CERT='/srv/academy-pg18/tls/patroni.crt' ETCDCTL_KEY='/srv/academy-pg18/tls/patroni.key' /opt/academy-etcd-3.7.2/etcdctl --user=patroni get '/academy/academy_pg18/' --prefix
sudo -u postgres env ETCDCTL_ENDPOINTS='https://10.77.18.11:2379' ETCDCTL_CACERT='/srv/academy-pg18/tls/ca.crt' ETCDCTL_CERT='/srv/academy-pg18/tls/patroni.crt' ETCDCTL_KEY='/srv/academy-pg18/tls/patroni.key' /opt/academy-etcd-3.7.2/etcdctl --user=patroni get /outside-academy-scope
First read succeeds with no keys; second is permission denied.