Before the Patroni installation, let's make sure our certificates are ready.
Create the ssl folder and move the corresponding certificates to ssl folder (on Postgres Nodes)
Each PostgreSQL node has its own certificate and key, but the file names and paths will be the same on all nodes (server.crt / server.key). This is important because Patroni stores these paths as cluster-wide settings in etcd.
mkdir -p /var/lib/postgresql/ssl
#we created these certs in article1
#on node postgres01
mv /var/tmp/postgres01.crt /var/lib/postgresql/ssl/server.crt
mv /var/tmp/postgres01.key /var/lib/postgresql/ssl/server.key
#on node postgres02
mv /var/tmp/postgres02.crt /var/lib/postgresql/ssl/server.crt
mv /var/tmp/postgres02.key /var/lib/postgresql/ssl/server.key
#on node postgres03
mv /var/tmp/postgres03.crt /var/lib/postgresql/ssl/server.crt
mv /var/tmp/postgres03.key /var/lib/postgresql/ssl/server.key
#run the below commands on each Postgres nodes
sh -c 'cat /var/lib/postgresql/ssl/server.crt /var/lib/postgresql/ssl/server.key > /var/lib/postgresql/ssl/postgres.pem'
chown postgres:postgres /var/lib/postgresql/ssl/server.crt \
/var/lib/postgresql/ssl/server.key \
/var/lib/postgresql/ssl/postgres.pem
chmod 600 /var/lib/postgresql/ssl/server.key
chmod 600 /var/lib/postgresql/ssl/postgres.pem
chmod 600 /var/lib/postgresql/ssl/server.crt # 600 or 644 is fine; key MUST be 600
Copy ETCD certificates to PostgreSQL nodes
When Patroni communicates (TLS handshake) with ETCD, it should use the local copies of ETCD certificates that reside on PostgreSQL nodes. Therefore we need to copy ETCD certificates to PostgreSQL nodes. If ETCD certificates are renewed in the future, we need to copy those new certs to PostgreSQL nodes as well.
Run the following commands on each ETCD node to copy ETCD certs to PostgreSQL nodes:
cd /etc/etcd/ssl/
scp ca.crt etcd*.crt etcd*.key This email address is being protected from spambots. You need JavaScript enabled to view it. :/var/tmp/
scp ca.crt etcd*.crt etcd*.key This email address is being protected from spambots. You need JavaScript enabled to view it. :/var/tmp/
scp ca.crt etcd*.crt etcd*.key This email address is being protected from spambots. You need JavaScript enabled to view it. :/var/tmp/
Then run the following commands on postgres nodes
mkdir -p /etc/etcd/ssl/
mv /var/tmp/*.crt /etc/etcd/ssl/
mv /var/tmp/*.key /etc/etcd/ssl/
# give permission to postgres user for these local copies
chown postgres:postgres /etc/etcd/ssl/etcd*.crt /etc/etcd/ssl/etcd*.key
chmod 600 /etc/etcd/ssl/etcd*.key
chmod 644 /etc/etcd/ssl/etcd*.crt /etc/etcd/ssl/ca.crt
Patroni Installation (on Postgres Nodes):
apt install -y patroni
mkdir -p /etc/patroni/
mkdir -p /var/lib/postgresql/data
chown postgres:postgres /var/lib/postgresql/data
chmod 700 /var/lib/postgresql/data
nano /etc/patroni/config.yml
postgres01 (192.168.204.16) /etc/patroni/config.yml
scope: postgresql-cluster
namespace: /service/
name: postgres01 # node1
etcd3:
hosts: 192.168.204.13:2379,192.168.204.14:2379,192.168.204.15:2379 # etcd cluster nodes
protocol: https
cacert: /etc/etcd/ssl/ca.crt
cert: /etc/etcd/ssl/etcd01.crt # postgres01's ETCD client certificate
key: /etc/etcd/ssl/etcd01.key # postgres01's ETCD client key
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.204.16:8008 # node1 REST API
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
parameters:
ssl: 'on'
ssl_cert_file: /var/lib/postgresql/ssl/server.crt # same path on all nodes
ssl_key_file: /var/lib/postgresql/ssl/server.key # same path on all nodes
pg_hba:
# for replication
- hostssl replication replicator 127.0.0.1/32 md5
- hostssl replication replicator 192.168.204.16/32 md5
- hostssl replication replicator 192.168.204.17/32 md5
- hostssl replication replicator 192.168.204.18/32 md5
- hostssl replication replicator 192.168.204.19/32 md5 # Barman server (for streaming replication)
# normal client access
- hostssl all all 127.0.0.1/32 md5
- hostssl all all 0.0.0.0/0 md5
initdb:
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.204.16:5432
data_dir: /var/lib/postgresql/data
bin_dir: /usr/lib/postgresql/18/bin
authentication:
superuser:
username: postgres
password: Pass2010! # use your own password
replication:
username: replicator
password: Pass2020! # use your own password
parameters:
max_connections: 100
shared_buffers: 256MB
tags:
nofailover: false
noloadbalance: false
clonefrom: false
postgres02 (192.168.204.17) /etc/patroni/config.yml
scope: postgresql-cluster
namespace: /service/
name: postgres02 # node2
etcd3:
hosts: 192.168.204.13:2379,192.168.204.14:2379,192.168.204.15:2379
protocol: https
cacert: /etc/etcd/ssl/ca.crt
cert: /etc/etcd/ssl/etcd02.crt
key: /etc/etcd/ssl/etcd02.key
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.204.17:8008
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
parameters:
ssl: 'on'
ssl_cert_file: /var/lib/postgresql/ssl/server.crt
ssl_key_file: /var/lib/postgresql/ssl/server.key
pg_hba:
- hostssl replication replicator 127.0.0.1/32 md5
- hostssl replication replicator 192.168.204.16/32 md5
- hostssl replication replicator 192.168.204.17/32 md5
- hostssl replication replicator 192.168.204.18/32 md5
- hostssl replication replicator 192.168.204.19/32 md5 # Barman
- hostssl all all 127.0.0.1/32 md5
- hostssl all all 0.0.0.0/0 md5
initdb:
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.204.17:5432
data_dir: /var/lib/postgresql/data
bin_dir: /usr/lib/postgresql/18/bin
authentication:
superuser:
username: postgres
password: Pass2010!
replication:
username: replicator
password: Pass2020!
parameters:
max_connections: 100
shared_buffers: 256MB
tags:
nofailover: false
noloadbalance: false
clonefrom: false
postgres03 (192.168.204.18) /etc/patroni/config.yml
scope: postgresql-cluster
namespace: /service/
name: postgres03 # node3
etcd3:
hosts: 192.168.204.13:2379,192.168.204.14:2379,192.168.204.15:2379
protocol: https
cacert: /etc/etcd/ssl/ca.crt
cert: /etc/etcd/ssl/etcd03.crt
key: /etc/etcd/ssl/etcd03.key
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.204.18:8008
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
parameters:
ssl: 'on'
ssl_cert_file: /var/lib/postgresql/ssl/server.crt
ssl_key_file: /var/lib/postgresql/ssl/server.key
pg_hba:
- hostssl replication replicator 127.0.0.1/32 md5
- hostssl replication replicator 192.168.204.16/32 md5
- hostssl replication replicator 192.168.204.17/32 md5
- hostssl replication replicator 192.168.204.18/32 md5
- hostssl replication replicator 192.168.204.19/32 md5 # Barman
- hostssl all all 127.0.0.1/32 md5
- hostssl all all 0.0.0.0/0 md5
initdb:
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.204.18:5432
data_dir: /var/lib/postgresql/data
bin_dir: /usr/lib/postgresql/18/bin
authentication:
superuser:
username: postgres
password: Pass2010!
replication:
username: replicator
password: Pass2020!
parameters:
max_connections: 100
shared_buffers: 256MB
tags:
nofailover: false
noloadbalance: false
clonefrom: false
Run the following commands only on postgres01
systemctl enable patroni
systemctl restart patroni
journalctl -u patroni -f
If you see initialized a new cluster and I am (postgres01), the leader with the lock, then the first node is fine.
Then go ahead and run the following commands on the other PostgreSQL nodes:
Go ahead and run the following commands on the other postgres nodes
systemctl enable patroni
systemctl restart patroni
journalctl -u patroni -f
patronictl -c /etc/patroni/config.yml list
Our Patroni Cluster is working fine. At this point we should go to our etcd nodes and modify the etcd.env parameter, ETCD_INITIAL_CLUSTER_STATE="new" to "existing"
nano /etc/etcd/etcd.env
systemctl restart etcd
systemctl status etcd
#etcd node1
export ETCDCTL_API=3
export ETCDCTL_ENDPOINTS="https://192.168.204.13:2379"
export ETCDCTL_CACERT=/etc/etcd/ssl/ca.crt
export ETCDCTL_CERT=/etc/etcd/ssl/etcd01.crt
export ETCDCTL_KEY=/etc/etcd/ssl/etcd01.key
etcdctl member list --write-out=table
#etcd node2
export ETCDCTL_API=3
export ETCDCTL_ENDPOINTS="https://192.168.204.14:2379"
export ETCDCTL_CACERT=/etc/etcd/ssl/ca.crt
export ETCDCTL_CERT=/etc/etcd/ssl/etcd02.crt
export ETCDCTL_KEY=/etc/etcd/ssl/etcd02.key
etcdctl member list --write-out=table
#etcd node3
export ETCDCTL_API=3
export ETCDCTL_ENDPOINTS="https://192.168.204.15:2379"
export ETCDCTL_CACERT=/etc/etcd/ssl/ca.crt
export ETCDCTL_CERT=/etc/etcd/ssl/etcd03.crt
export ETCDCTL_KEY=/etc/etcd/ssl/etcd03.key
etcdctl member list --write-out=table
etcdctl endpoint health --cluster
If you need to switchover to another node, use the command below. First it will ask you the current leader then what node you want to failover.
patronictl -c /etc/patroni/config.yml switchover