WIUNIX ACADEMY
PostgreSQL with Patroni: replication, quorum and safe switchover
Page 5 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
Rehearse a planned switchover
Quiesce lab writers, verify all replicas are healthy on the same timeline and pg2 is an eligible synchronized candidate. If roles differ, stop and reconcile rather than blindly using these names. The interactive command asks for confirmation; inspect it. It changes the leader, disconnects sessions and is disruptive. A planned switchover is not a power-loss, network-partition or fencing test. Never use forced failover to make an ineligible replica writable.
Wait until patronictl itself reports pg2 as Sync Standby; a transient SQL sync state during bootstrap is not enough to establish candidate eligibility.
Disposable database VM; Bash as administrator
sudo -u postgres patronictl -c '/srv/academy-pg18/patroni.yml' list
sudo -u postgres patronictl -c '/srv/academy-pg18/patroni.yml' switchover 'academy_pg18' --leader pg1 --candidate pg2
sudo -u postgres patronictl -c '/srv/academy-pg18/patroni.yml' list
pg2 becomes leader, pg1 follows; inspect all timelines/roles before resuming writes.
LET’S BUILD
Check the marker through the new leader
On pg2 after confirming it is leader. Reconnect explicitly: this guide did not create a stable client endpoint. Applications need transaction-aware retries and idempotency for uncertain commit outcomes. Compare the marker and recovery state, then insert a second marker. Confirm both rows on pg1 using the earlier read panel.
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'
SET statement_timeout = '15s';
SELECT pg_is_in_recovery();
TABLE probe;
INSERT INTO probe VALUES (2, 'after-switchover');
SELECT pg_current_wal_lsn();
SQL
Recovery false on pg2; both markers readable on the rejoined pg1.
Budget WAL retention, do not confuse it with backup
Slots retain WAL for disconnected replicas and can fill storage; the2GB cap bounds this example but can invalidate a lagging slot. wal_keep_size is a minimum retention allowance, not an archive. Monitor slot wal_status/restart_lsn, disk space, replay lag and archive failures. If needed WAL has been removed, the replica may need a new basebackup into a new empty destination. Do not delete slot state or overwrite PGDATA to conceal lag. Physical replication also copies accidental deletes and corruption. A separate base backup plus continuous archived WAL, retention, encryption and an isolated restore/PITR rehearsal are required before real data; none is established by this tutorial.
LET’S BUILD
Diagnose without promoting an unsafe replica
Run locally on the affected VM. Permission denied from etcd means inspect the gateway certificate (empty CN), protected username/password, auth role and exact prefix; connection errors mean check routes, SANs, expiry and clocks. No leader with lost DCS majority means restore quorum, not initialize a replacement DCS or force-promote a lagging node. For blocked writes inspect the synchronous partner and disk; strict mode may be behaving correctly. A replica requiring removed WAL needs a reviewed rebuild, not trust authentication or disabled TLS. bootstrap.dcs is applied only at first cluster initialization; later changes belong to patronictl edit-config and an audited dynamic-config review.
Disposable database VM; Bash as administrator
sudo systemctl status academy-pg-patroni.service academy-pg-etcd.service --no-pager
sudo journalctl -u academy-pg-patroni.service -u academy-pg-etcd.service -n 80 --no-pager
sudo -u postgres patronictl -c '/srv/academy-pg18/patroni.yml' list
sudo -u postgres patronictl -c '/srv/academy-pg18/patroni.yml' show-config
sudo -u postgres psql -h '/srv/academy-pg18/socket' -p 5432 -d postgres -X -c "SELECT pg_is_in_recovery();"
sudo -u postgres psql -h '/srv/academy-pg18/socket' -p 5432 -d postgres -X -c "SELECT name,setting,pending_restart FROM pg_settings WHERE name IN ('synchronous_commit','synchronous_standby_names','max_slot_wal_keep_size');"
sudo df -h '/srv/academy-pg18'
sudo openssl x509 -in '/srv/academy-pg18/tls/api.crt' -noout -dates -ext subjectAltName
Evidence identifies role, timeline, quorum, TLS or retention cause; no unsafe promotion performed.
Failover needs fencing outside this lab
Lease expiry alone cannot stop a paused VM or stuck old primary. Before production, prove the old writer is isolated/reset before a new writer accepts transactions. Patroni required watchdog mode refuses leadership if the device cannot activate, but a software watchdog on the same overloaded kernel is not equivalent to independent power fencing. Review ttl/loop_wait/retry_timeout, pause behavior and watchdog margin together. Do not kill Patroni alone as a fencing test: PostgreSQL may survive it. Record actual failure-domain, partition, stall, data-loss, client reconnection and recovery measurements. This lab uses watchdog off and carries no production automatic-failover or zero-data-loss acceptance.
Keep divergent data before attempting rejoin
For a former primary, first isolate client writes and retain a private snapshot/copy of its stopped data and required WAL. Identify the authoritative timeline using healthy cluster evidence and the business recovery decision. pg_rewind modifies the target and may discard its divergent transactions; it needs checksums or wal_log_hints plus required WAL and full_page_writes. This lab initializes checksums. The two remove_data_directory flags remain false so failure does not silently delete data. Do not run reinit, delete the DCS prefix, rerun initdb, or overwrite a nonempty target. If rewind cannot safely work, keep the original and restore/clone into a separately approved empty destination. Rejoin as standby, check both markers/LSN, then consider a later planned switchover; failback is not an automatic overwrite of the old primary.