postgres patroni - ghdrako/doc_snipets GitHub Wiki
- https://patroni.readthedocs.io/en/latest/
- PostgreSQL High-Availability and Patroni – an Introduction.
- Patroni: Setting up a highly availability PostgreSQL clusters
- https://www.cybertec-postgresql.com/en/introduction-and-how-to-etcd-clusters-for-patroni/
- https://docs.percona.com/postgresql/17/solutions/ha-setup-apt.html#configure-patroni
Patroni is a template for high availability (HA) PostgreSQL solutions using Python. For maximum accessibility, Patroni supports a variety of distributed configuration stores like ZooKeeper, etcd, Consul or Kubernetes.
Patroni is a cluster manager that can customize and automate the deployment and maintenance of PostgreSQL HA (High Availability) clusters. It supports database automatic failover and streaming replication.
HAProxy or a load balancer offers load balancing and proxying for TCP and HTTP-based applications.
Etcd is a strongly consistent, distributed key-value store that provides a reliable way to store data that a distributed system or cluster of machines needs to access. Use Etcd to store the state of the PostgreSQL cluster to keep the Postgres cluster up and running.
To use this architecture, you must specify the HAProxy virtual IP address and port (5000 by default) as the database server host IP address and port.
To connect to the read-only database server, you must specify the HAProxy virtual IP address and the read-only port (6000 by default) as the read-only database server host IP address and port.
+-----------------------------+
| Load Balancer | <-- Optional (e.g., HAProxy/Keepalived)
+-------------+---------------+
|
+-------------+-------------+
| |
+-------v-------+ +---------v-------+
| Patroni Node 1| | Patroni Node 2 |
| PostgreSQL 17 | | PostgreSQL 17 |
+-------+-------+ +--------+--------+
| |
+-----------+-------------+
|
+--------v--------+
| ETCD Cluster |
| (3 Docker nodes)|
+--------+--------+
|
+----------------+----------------+
| ETCD Node 1 (Docker) |
| ETCD Node 2 (Docker) |
| ETCD Node 3 (Docker) |
+--------------------------------+
Components
-
Patroni
- Manages PostgreSQL HA.
- Talks to ETCD for distributed state (leader election).
- Runs on each DB node.
- Controls PostgreSQL startup, promotion, and replication settings.
-
PostgreSQL 17
- One node is primary, others are replicas.
- Patroni handles failover/promote operations.
-
ETCD (3 nodes in Docker)
- Distributed key-value store.
- Holds cluster state and leader election info.
- Minimum 3 nodes recommended for quorum
- ETCD Docker Run Example (on each host) - Do this similarly for etcd2, etcd3
docker run -d \
--name etcd1 \
-p 2379:2379 -p 2380:2380 \
--network host \
quay.io/coreos/etcd \
/usr/local/bin/etcd \
--name etcd1 \
--initial-advertise-peer-urls http://<ip>:2380 \
--listen-peer-urls http://<ip>:2380 \
--listen-client-urls http://<ip>:2379 \
--advertise-client-urls http://<ip>:2379 \
--initial-cluster-token etcd-cluster-1 \
--initial-cluster etcd1=http://<ip1>:2380,etcd2=http://<ip2>:2380,etcd3=http://<ip3>:2380 \
--initial-cluster-state new
- Patroni Config Example (YAML)
scope: pgcluster
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: <node1_ip>:8008
etcd:
host: <etcd1_ip>:2379,<etcd2_ip>:2379,<etcd3_ip>:2379
postgresql:
listen: 0.0.0.0:5432
connect_address: <node1_ip>:5432
data_dir: /var/lib/postgresql/data
bin_dir: /usr/lib/postgresql/17/bin
authentication:
replication:
username: replicator
password: secret
superuser:
username: postgres
password: supersecret
parameters:
wal_level: replica
hot_standby: "on"
max_wal_senders: 10
max_replication_slots: 10
tags:
nofailover: false
noloadbalance: false
clonefrom: false
Repeat for Node 2 with respective IPs and name changes.
- Ensure ETCD cluster reaches quorum (etcdctl endpoint status --cluster).
- Start Patroni on both PostgreSQL nodes.
- Use curl localhost:8008 to view Patroni status.
- Failover test: stop primary node → check if secondary promotes.