Infrastructure at your Service

Mouhamadou Diaw

PostgreSQL High Availability: Patroni, Ectd , HAProxy , Keepalived

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Senior Consultant