Patroni is one the most famous tool that can be used to setup a high avalaibilty for PostgreSQL. In this blog I am describing a test I did.
Below the environment we will use
The etcd cluster will contains 3 servers
etcd-server1: 192.168.56.100
etcd-server2: 192.168.56.101
etcd-server3: 192.168.56.106
HAProxy and Keepalived will be configured in this 2 servers.
haproxy-server1: 192.168.56.102
haproxy-server2: 192.168.56.103
A VIP will be configured
haproxyvip 192.168.56.108
We have 2 databases servers
pgserver1: 192.168.56.104 (initial primary server)
pgserver2: 192.168.56.105
Below a resume of all IP used in this blog
etcd-server1: 192.168.56.100 etcd-server2: 192.168.56.101 etcd-server3: 192.168.56.106 haproxy-server1: 192.168.56.102 haproxy-server2: 192.168.56.103 pgserver1: 192.168.56.104 pgserver2: 192.168.56.105 haproxyvip 192.168.56.108
All servers are ppc64le architecture. But the steps will not change if you are using X64 architecture
Etcd Install
The first steps is to install etcd on all etcd servers
etcd-server1
etcd-server2
etcd-server3
For the installation of etcd, the latest archive is downloaded here
The following steps were done on all 3 etc servers
Unpack the etcd downloaded archive
[[email protected] ~]# tar xvzf etcd-v3.4.14-linux-ppc64le.tar.gz
And then copy following executables to /usr/local/bin
[[email protected] etcd-v3.4.14-linux-ppc64le]# pwd /root/etcd-v3.4.14-linux-ppc64le [[email protected] etcd-v3.4.14-linux-ppc64le]# cp etcd etcdctl /usr/local/bin/
For the startup a etcd service is created for both servers. This service will contain the configuration values of the etcd cluster.
Below the contents of each service
-etcd.service etcd-server1
[[email protected] ~]# cat /etc/systemd/system/etcd.service [Unit] Description=etcd service Documentation=https://github.com/coreos/etcd [Service] Type=notify ExecStart=/usr/local/bin/etcd --name=etcd0 --data-dir=/var/lib/etcd/default.etcd --initial-advertise-peer-urls=http://192.168.56.100:2380 --listen-peer-urls=http://192.168.56.100:2380 --listen-client-urls=http://192.168.56.100:2379,http://127.0.0.1:2379 --advertise-client-urls=http://192.168.56.100:2379 --initial-cluster-token=etcd-cluster-1 --initial-cluster=etcd0=http://192.168.56.100:2380,etcd1=http://192.168.56.101:2380,etcd2=http://192.168.56.106:2380 --initial-cluster-state=new Restart=on-failure RestartSec=5 [Install] WantedBy=multi-user.target [[email protected] ~]#
-etcd.service etcd-server2
[[email protected] ~]# cat /etc/systemd/system/etcd.service Description=etcd service Documentation=https://github.com/coreos/etcd [Service] Type=notify ExecStart=/usr/local/bin/etcd --name=etcd1 --data-dir=/var/lib/etcd/default.etcd --initial-advertise-peer-urls=http://192.168.56.101:2380 --listen-peer-urls=http://192.168.56.101:2380 --listen-client-urls=http://192.168.56.101:2379,http://127.0.0.1:2379 --advertise-client-urls=http://192.168.56.101:2379 --initial-cluster-token=etcd-cluster-1 --initial-cluster=etcd0=http://192.168.56.100:2380,etcd1=http://192.168.56.101:2380,etcd2=http://192.168.56.106:2380 --initial-cluster-state=new Restart=on-failure RestartSec=5 [Install] WantedBy=multi-user.target [[email protected] ~]#
-etcd.service etcd-server3
[[email protected] ~]# cat /etc/systemd/system/etcd.service [Unit] Description=etcd service Documentation=https://github.com/coreos/etcd [Service] Type=notify ExecStart=/usr/local/bin/etcd --name=etcd2 --data-dir=/var/lib/etcd/default.etcd --initial-advertise-peer-urls=http://192.168.56.106:2380 --listen-peer-urls=http://192.168.56.106:2380 --listen-client-urls=http://192.168.56.106:2379,http://127.0.0.1:2379 --advertise-client-urls=http://192.168.56.106:2379 --initial-cluster-token=etcd-cluster-1 --initial-cluster=etcd0=http://192.168.56.100:2380,etcd1=http://192.168.56.101:2380,etcd2=http://192.168.56.106:2380 --initial-cluster-state=new Restart=on-failure RestartSec=5 [Install] WantedBy=multi-user.target [[email protected] ~]#
Now let’s start etcd services on both servers (stop your firewall or do requested rules)
[[email protected] ~]# systemctl start etcd.service [[email protected] ~]# systemctl start etcd.service [[email protected] ~]# systemctl start etcd.service [[email protected] ~]# systemctl enable etcd.service [[email protected] ~]# systemctl enable etcd.service [[email protected] ~]# systemctl enable etcd.service
If everything is OK, you can query the state of the etcd cluster
[[email protected] ~]# etcdctl endpoint status --cluster -w table +---------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+ | ENDPOINT | ID | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS | +---------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+ | http://192.168.56.101:2379 | 56fde5fc4136bc8b | 3.4.14 | 328 kB | false | false | 18 | 14 | 14 | | | http://192.168.56.100:2379 | fa26cebd7281c24 | 3.4.14 | 328 kB | false | false | 18 | 14 | 14 | | | http://192.168.56.106:2379 | b75652c4850bb9d2 | 3.4.14 | 328 kB | true | false | 18 | 14 | 14 | | +---------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
Keepalived and HAProxy Install
These components are installed on following servers
haproxy-server1
haproxy-server2
To install Keepalived download the latest archive here
You will need maybe to install some linux required packages.
Unpack the archive and do the configure
[[email protected] haproxy-server1 ~]# tar xvzf keepalived-2.2.0.tar.gz [[email protected] haproxy-server1 ~]# cd keepalived-2.2.0/ [[email protected] haproxy-server1 keepalived-2.2.0]# ./configure
If there is no error then run make and make install
[[email protected] haproxy-server1 keepalived-2.2.0]# make [[email protected] haproxy-server1 keepalived-2.2.0]# make install
For Keepalived startup the same service is created on both servers.
[[email protected] haproxy-server1 ~]# cat /etc/systemd/system/keepalived.service [Unit] Description=LVS and VRRP High Availability Monitor After=network-online.target syslog.target Wants=network-online.target [Service] Type=forking PIDFile=/run/keepalived.pid KillMode=process EnvironmentFile=-/usr/local/etc/sysconfig/keepalived ExecStart=/usr/local/sbin/keepalived $KEEPALIVED_OPTIONS ExecReload=/bin/kill -HUP $MAINPID [Install] WantedBy=multi-user.target [[email protected] haproxy-server1 ~]#
Before starting keepalived, we have to configure a configuration file on both servers.
-haproxy-server1
[[email protected] ]# cat /etc/keepalived/keepalived.conf global_defs { } vrrp_script chk_haproxy { # Requires keepalived-1.1.13 script "killall -0 haproxy" # widely used idiom interval 2 # check every 2 seconds weight 2 # add 2 points of prio if OK } vrrp_instance VI_1 { interface eth0 state MASTER # or "BACKUP" on backup priority 101 # 101 on master, 100 on backup virtual_router_id 51 authentication { auth_type PASS auth_pass 1234 } virtual_ipaddress { 192.168.56.108 } track_script { chk_haproxy } } [[email protected] ]#
-haproxy-server2
[[email protected] ]# cat /etc/keepalived/keepalived.conf global_defs { } vrrp_script chk_haproxy { # Requires keepalived-1.1.13 script "killall -0 haproxy" # widely used idiom interval 2 # check every 2 seconds weight 2 # add 2 points of prio if OK } vrrp_instance VI_1 { interface eth0 state BACKUP # or "BACKUP" on backup priority 100 # 101 on master, 100 on backup virtual_router_id 51 authentication { auth_type PASS auth_pass 1234 } virtual_ipaddress { 192.168.56.108 } track_script { chk_haproxy } } [[email protected] ]#
Now it’s time to start Keepalived on both servers can be started on both servers
[[email protected] keepalived]# systemctl start keepalived [[email protected] keepalived]# systemctl status keepalived [[email protected] keepalived]# systemctl enable keepalived [[email protected] keepalived]# systemctl start keepalived [[email protected] keepalived]# systemctl status keepalived [[email protected] keepalived]# systemctl enable keepalived
If everything is fine, the VIP 192.168.56.108 should be run on one server and will automatically failover to the the second server if there is any issue with the server.
Now let’s install the HAProxy on both servers haproxy-server1 and haproxy-server2. We have downloaded the latest HAProxy archive here
Then unpack the archive and run the make and make install commands (Maybe you will have to install some linux packages)
[[email protected] ~]# tar xzzf haproxy-2.3.4.tar.gz [[email protected] ~]# cd haproxy-2.3.4/ [[email protected] haproxy-2.1.5]# make TARGET=linux-glibc [[email protected] haproxy-2.1.5]# make install
Following directories are created in my case
[[email protected] haproxy-2.1.5]# mkdir -p /etc/haproxy [[email protected] haproxy-2.1.5]# mkdir -p /var/lib/haproxy [[email protected] haproxy-2.1.5]# touch /var/lib/haproxy/stats
To startup HAProxy a service is created on both HAProxy servers
[[email protected] system]# cat haproxy.service [Unit] Description=HAProxy Load Balancer Documentation=man:haproxy(1) Documentation=file:/usr/share/doc/haproxy/configuration.txt.gz # allows us to do millisecond level restarts without triggering alert in Systemd #StartLimitInterval=0 #StartLimitBurst=0 After=network.target syslog.service Wants=syslog.service [Service] Environment="CONFIG=/etc/haproxy/haproxy.cfg" "PIDFILE=/run/haproxy.pid" # EXTRAOPTS and RELOADOPS come from this default file # EnvironmentFile=-/etc/default/haproxy ExecStartPre=/usr/local/sbin/haproxy -f $CONFIG -c -q ExecStart=/usr/local/sbin/haproxy -W -f $CONFIG -p $PIDFILE $EXTRAOPTS ExecReload=/usr/local/sbin/haproxy -f $CONFIG -c -q $EXTRAOPTS $RELOADOPTS ExecReload=/bin/kill -USR2 $MAINPID KillMode=mixed #Restart=always #Type=forking Restart=on-failure RestartSec=5 [Install] WantedBy=multi-user.target [[email protected] system]#
The same haproxy.cfg file is configured on both HAProxy servers like
[[email protected] ]# cat /etc/haproxy/haproxy.cfg global maxconn 100 defaults log global mode tcp retries 2 timeout client 30m timeout connect 4s timeout server 30m timeout check 5s listen stats mode http bind *:7000 stats enable stats uri / listen production bind 192.168.56.108:5000 option httpchk OPTIONS/master http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server postgresql_192.168.56.104_5432 192.168.56.104:5432 maxconn 100 check port 8008 server postgresql_192.168.56.105_5432 192.168.56.105:5432 maxconn 100 check port 8008 listen standby bind 192.168.56.108:5001 option httpchk OPTIONS/replica http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server postgresql_192.168.56.104_5432 192.168.56.104:5432 maxconn 100 check port 8008 server postgresql_192.168.56.105_5432 192.168.56.105:5432 maxconn 100 check port 8008 [[email protected] ]#
Let’s start HAProxy
[[email protected] haproxy-2.1.5]# setsebool -P haproxy_connect_any=1 [[email protected] system]# systemctl enable haproxy.service [[email protected] system]# systemctl start haproxy.service [[email protected] system]# systemctl status haproxy.service [[email protected] system]# setsebool -P haproxy_connect_any=1 [[email protected] system]# systemctl start haproxy.service [[email protected] system]# systemctl status haproxy.service [[email protected] system]# systemctl enable haproxy.service
Patroni Install
Patroni will be installed on both PostgreQL servers pgserver1 and pgserver2. Some components will be installed with root and some components with the user postgres
In my case I had to install some linux packages
[[email protected] ~]# yum install python3-pip.noarch [[email protected] ~]# yum install python36-devel.x86_64
With the user root install following component
[[email protected] ~]# pip3 install --upgrade setuptools
With the user postgres install these components
[[email protected] ~]$ pip3 install --user psycopg2-binary [[email protected] ~]$ pip3 install --user python-etcd [[email protected] ~]$ pip3 install --user wheel [[email protected] ~]$ pip3 install --user patroni
If all components are installed without error, we can now configure a configuration file for each patroni
-pgserver1
[[email protected] cat /etc/patroni.yaml scope: postgres_poc name: pg-patroni1 restapi: listen: 192.168.56.104:8008 connect_address: 192.168.56.104:8008 etcd3: hosts: 192.168.56.100:2379,192.168.56.101:2379,192.168.56.106:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true postgresql: listen: 192.168.56.104:5432 bin_dir: /u01/app/postgres/product/13/db_1/bin connect_address: 192.168.56.104:5432 data_dir: /u02/pgdata pgpass: /tmp/pgpass authentication: replication: username: postgres password: postgres superuser: username: postgres password: postgres parameters: unix_socket_directories: '.' tags: nofailover: false noloadbalance: false clonefrom: false nosync: false [[email protected] ~]$
-pgserver1
[[email protected] ~]$ cat /etc/patroni.yaml scope: postgres_poc name: pg-patroni2 restapi: listen: 192.168.56.105:8008 connect_address: 192.168.56.105:8008 etcd3: hosts: 192.168.56.100:2379,192.168.56.101:2379,192.168.56.106:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true postgresql: listen: 192.168.56.105:5432 bin_dir: /u01/app/postgres/product/13/db_1/bin connect_address: 192.168.56.105:5432 data_dir: /u02/pgdata pgpass: /tmp/pgpass authentication: replication: username: postgres password: postgres superuser: username: postgres password: postgres parameters: unix_socket_directories: '.' tags: nofailover: false noloadbalance: false clonefrom: false nosync: false [[email protected] ~]$
The pg_hba.conf file should be configured on both databse servers to allow connections from the different servers. Then patroni can be started on both servers.
On pgserver1 which is actullaly the primary, we will see outputs like
[[email protected] PG1]$ patroni /etc/patroni.yaml … … 2020-01-15 20:31:45,898 INFO: acquired session lock as a leader 2020-01-15 20:31:55,862 INFO: Lock owner: pg-patroni1; I am pg-patroni1 2020-01-15 20:31:55,871 INFO: Lock owner: pg-patroni1; I am pg-patroni1 2020-01-15 20:31:55,905 INFO: no action. i am the leader with the lock
On pgserver2 which is the standby server we will have outputs like
[[email protected] PG1]$ patroni /etc/patroni.yaml .. 2020-01-15 20:41:25,941 INFO: Lock owner: pg-patroni1; I am pg-patroni2 2020-01-15 20:41:25,941 INFO: does not have lock 2020-01-15 20:41:25,952 INFO: no action. i am a secondary and i am following a leader
For the automatic startup of Patroni a service can be created on both databases servers
[[email protected] ~]# cat /etc/systemd/system/patroni.service [Service] Type=simple User=postgres Group=postgres #ExecStart=/usr/local/bin/patroni /opt/patroni/patroni.yml ExecStart=/home/postgres/.local/bin/patroni /etc/patroni.yaml KillMode=process TimeoutSec=30 Restart=no [Install] WantedBy=multi-user.target [[email protected] ~]#
The command patronictl will be used to manage Patroni
-List the members of the configuration
[[email protected] ~]$ patronictl -c /etc/patroni.yaml list +----------+-------------+----------------+--------+---------+----+-----------+ | Cluster | Member | Host | Role | State | TL | Lag in MB | +----------+-------------+----------------+--------+---------+----+-----------+ | postgres | pg-patroni1 | 192.168.56.104 | Leader | running | 1 | | | postgres | pg-patroni2 | 192.168.56.105 | Replica| running | 1 | 0 | +----------+-------------+----------------+--------+---------+----+-----------+
Perform a switchover
[[email protected] ~]$ patronictl -c /etc/patroni.yaml switchover Master [pg-patroni1]: Candidate ['pg-patroni2'] []: When should the switchover take place (e.g. 2021-01-15T11:16 ) [now]: Current cluster topology + Cluster: postgres_poc (6917537380726361322) ----+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-------------+---------------+---------+---------+----+-----------+ | pg-patroni1 | 192.168.56.104| Leader | running | 1 | | | pg-patroni2 | 192.168.56.105| Replica | running | 1 | 0 | +-------------+---------------+---------+---------+----+-----------+ Are you sure you want to switchover cluster postgres_poc, demoting current master pg-patroni1? [y/N]: y 2021-01-15 10:16:35.39948 Successfully switched over to "pg-patroni2" + Cluster: postgres_poc (6917537380726361322) ----+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-------------+---------------+---------+---------+----+-----------+ | pg-patroni1 | 192.168.56.104| Replica | stopped | | unknown | | pg-patroni2 | 192.168.56.105| Leader | running | 1 | | +-------------+---------------+---------+---------+----+-----------+ [[email protected] ~]$ patronictl -c /etc/patroni.yaml list + Cluster: postgres_poc (6917537380726361322) ----+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-------------+---------------+---------+---------+----+-----------+ | pg-patroni1 | 192.168.56.104| Replica | running | 2 | 0 | | pg-patroni2 | 192.168.56.105| Leader | running | 2 | | +-------------+---------------+---------+---------+----+-----------+
If the primary crash, the replica will be automatically promoted.
For the connection to the databases, client can use the VIP configured via HAProxy
-To connect to the standby the port 5001 is used
[[email protected] [~]> psql -h 192.168.56.108 -p 5001 -U postgres Password for user postgres: psql (13.1) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row)
-To connect to the primary the port 5000 is used
[[email protected] [~]> psql -h 192.168.56.108 -p 5000 -U postgres Password for user postgres: psql (13.1) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row)
Conclusion
Hope this may help
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/04/Slide56.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/STH_web-min-scaled.jpg)