Within the first part I have described the setup of Consul as replacement for ETCD.
Here now the setup ob keepalived, haproxy and patroni.
The needed packages I have installed within the first part, so let’s start with the configuration of keepalived.
At first we need to open firewalld for the VRRP Protocol:
$ [root@patroni-01 ~]# firewall-cmd --add-rich-rule='rule protocol value="vrrp" accept' --permanent $ success $ [root@patroni-01 ~]# firewall-cmd --reload $ success $ [root@patroni-01 ~]#
Next part will be the configuration of keepalived:
$ [root@patroni-01 /]# cat /etc/keepalived/keepalived.conf
$ vrrp_script haproxy {
$ script "killall -0 haproxy"
$ interval 2
$ weight 2
$ }
$ vrrp_instance VI_1 {
$ state MASTER
$ interface ens160
$ virtual_router_id 51
$ priority 255
$ advert_int 1
$ authentication {
$ auth_type PASS
$ auth_pass new_password
$ }
$ virtual_ipaddress {
$ 192.168.198.200/24
$ }
$ track_script {
$ haproxy
$ }
$ }
$ [root@patroni-01 /]#
Priority defines the default role, in my case 255 for the master role.
The keepalived.conf for the backup role on patroni-02:
$ [root@patroni-02 /]# cat /etc/keepalived/keepalived.conf
$ vrrp_script haproxy {
$ script "killall -0 haproxy"
$ interval 2
$ weight 2
$ }
$ vrrp_instance VI_1 {
$ state BACKUP
$ interface ens160
$ virtual_router_id 51
$ priority 254
$ advert_int 1
$ authentication {
$ auth_type PASS
$ auth_pass new_password
$ }
$ virtual_ipaddress {
$ 192.168.198.200/24
$ }
$ track_script {
$ haproxy
$ }
$ }
[root@patroni-02 /]#
The keepalived.conf for the backup role on patroni-03:
$ [root@patroni-03 /]# cat /etc/keepalived/keepalived.conf
$ vrrp_script haproxy {
$ script "killall -0 haproxy"
$ interval 2
$ weight 2
$ }
$ vrrp_instance VI_1 {
$ state BACKUP
$ interface ens160
$ virtual_router_id 51
$ priority 254
$ advert_int 1
$ authentication {
$ auth_type PASS
$ auth_pass new_password
$ }
$ virtual_ipaddress {
$ 192.168.198.200/24
$ }
$ track_script {
$ haproxy
$ }
$ }
$ [root@patroni-03 /]#
Checking status on all three nodes.:
patroni-01 as MASTER:
$ [root@patroni-01 /]# journalctl -u keepalived $ Mar 25 13:04:45 patroni-01.patroni.test Keepalived_vrrp[11468]: (VI_1) Entering MASTER STATE
patroni-02 as BACKUP:
$ journalctl -u keepalived $ Mar 25 14:20:18 patroni-02.patroni.test Keepalived_vrrp[1484]: (VI_1) Entering BACKUP STATE
patroni-03 as BACKUP:
$ journalctl -u keepalived $ Mar 25 14:21:56 patroni-03.patroni.test Keepalived_vrrp[1465]: (VI_1) Entering BACKUP STATE
Next step haproxy.
At first we need to adapt SE Linux for haproxy or switch it off:
$ [root@patroni-01 /]#setsebool -P haproxy_connect_any=1
haproxy.cfg is the same on all three servers:
$ [root@patroni-01 /]# 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 / $ # stats auth haproxy:haproxy $ # stats refresh 10s $ $ listen PG1 $ bind *:5000 $ option httpchk $ http-check expect status 200 $ default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions $ server postgresql_192.168.198.132_5432 192.168.198.132:5432 maxconn 100 check port 8008 $ server postgresql_192.168.198.133_5432 192.168.198.133:5432 maxconn 100 check port 8008 $ server postgresql_192.168.198.134_5432 192.168.198.134:5432 maxconn 100 check port 8008 $ [root@patroni-01 /]#
Starting and enabling haproxy:
$ [root@patroni-01 /]# systemctl start haproxy $ [root@patroni-01 /]# systemctl enable haproxy
Now the interesting part, Patroni.
At first, there is a missing dependancy by installing Patroni out of RPM Pachages, python3-urllib3 is missing:
$ [root@patroni-01 pgdata]# dnf install python3-urllib3 $ Last metadata expiration check: 5:18:38 ago on Mon 11 Apr 2022 11:06:33 AM CEST. $ Dependencies resolved. $ ========================================================================================================================================================================================================================================================================================== $ Package Architecture Version Repository Size $ ========================================================================================================================================================================================================================================================================================== $ Installing: $ python3-urllib3 noarch 1.24.2-5.el8 baseos 176 k $ Installing dependencies: $ python3-pysocks noarch 1.6.8-3.el8 baseos 33 k $ $ Transaction Summary $ ========================================================================================================================================================================================================================================================================================== $ Install 2 Packages $ $ Total download size: 209 k $ Installed size: 681 k $ Is this ok [y/N]: y $ Downloading Packages: $ (1/2): python3-pysocks-1.6.8-3.el8.noarch.rpm 274 kB/s | 33 kB 00:00 $ (2/2): python3-urllib3-1.24.2-5.el8.noarch.rpm 1.0 MB/s | 176 kB 00:00 $ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ $ Total 469 kB/s | 209 kB 00:00 $ Running transaction check $ Transaction check succeeded. $ Running transaction test $ Transaction test succeeded. $ Running transaction $ Preparing : 1/1 $ Installing : python3-pysocks-1.6.8-3.el8.noarch 1/2 $ Installing : python3-urllib3-1.24.2-5.el8.noarch 2/2 $ Running scriptlet: python3-urllib3-1.24.2-5.el8.noarch 2/2 $ Verifying : python3-pysocks-1.6.8-3.el8.noarch 1/2 $ Verifying : python3-urllib3-1.24.2-5.el8.noarch 2/2 $ $ Installed: $ python3-pysocks-1.6.8-3.el8.noarch python3-urllib3-1.24.2-5.el8.noarch $ $ Complete!
Patroni need a information which one of the consul nodes is master at start.
This information comes out of the parameter “bootstrap”: true only on the master node at start.
$ [root@patroni-01 consul.d]# cat consul.json-dist.hcl
$ {
$ "bootstrap": true,
$ "server": true,
$ "data_dir": "/pgdata/consul",
$ "log_level": "INFO"
$ "disable_update_check": true,
$ "disable_anonymous_signature": true,
$ "advertise_addr": "192.168.198.132",
$ "bind_addr": "192.168.198.132",
$ "bootstrap_expect": 3,
$ "client_addr": "0.0.0.0",
$ "domain": "patroni.test",
$ "enable_script_checks": true,
$ "dns_config": {
$ "enable_truncate": true,
$ "only_passing": true
$ },
$ "enable_syslog": true,
$ "encrypt": "ueX3vI8HI63FR/VE+Yv1T4+x7mrrNIU7F2bDNfPVR9g=",
$ "leave_on_terminate": true,
$ "log_level": "INFO",
$ "rejoin_after_leave": true,
$ "retry_join": [
$ "patroni-01",
$ "patroni-02",
$ "patroni-03"
$ ],
$ "server": true,
$ "start_join": [
$ "patroni-01",
$ "patroni-02",
$ "patroni-03"
$ ],
$ "ui_config.enabled": true
$ }
$ [root@patroni-01 consul.d]#
Now Patroni, this is similar to Patroni using etcd.
By using etcd there is a part etcd within the patroni.yml file, this is replaced with a part consul:
$ [root@patroni-01 patroni]# cat patroni.yml $ name: "patroni-01.patroni.test" $ scope: PG1 $ namespace: /patroni.test/ $ consul: $ url: http://127.0.0.1:8500 $ register_service: true $ postgresql: $ connect_address: "patroni-01.patroni.test:5432" $ bin_dir: /usr/pgsql-14/bin $ data_dir: /pgdata/14/data $ authentication: $ replication: $ username: replicator $ password: replicator $ superuser: $ username: postgres $ password: postgres $ listen: 192.168.198.132:5432 $ restapi: $ connect_address: "patroni-01.patroni.test:8008" $ listen: "patroni-01.patroni.test:8008" $ bootstrap: $ dcs: $ postgresql: $ use_pg_rewind: true $ use_slots: true $ parameters: $ wal_level: 'hot_standby' $ hot_standby: "on" $ wal_keep_segments: 8 $ max_replication_slots: 10 $ wal_log_hints: "on" $ listen_addresses: '*' $ port: 5432 $ logging_collector: 'on' $ log_truncate_on_rotation: 'on' $ log_filename: 'postgresql-%a.log' $ log_rotation_age: '1440' $ log_line_prefix: '%m - %l - %p - %h - %u@%d - %x' $ log_directory: 'pg_log' $ log_min_messages: 'WARNING' $ log_autovacuum_min_duration: '60s' $ log_min_error_statement: 'NOTICE' $ log_min_duration_statement: '30s' $ log_checkpoints: 'on' $ log_statement: 'ddl' $ log_lock_waits: 'on' $ log_temp_files: '0' $ log_timezone: 'Europe/Zurich' $ log_connections: 'on' $ log_disconnections: 'on' $ log_duration: 'on' $ client_min_messages: 'WARNING' $ wal_level: 'replica' $ hot_standby_feedback: 'on' $ max_wal_senders: '10' $ shared_buffers: '1024MB' $ work_mem: '8MB' $ effective_cache_size: '3072MB' $ maintenance_work_mem: '64MB' $ wal_compression: 'off' $ max_wal_senders: '20' $ shared_preload_libraries: 'pg_stat_statements' $ autovacuum_max_workers: '6' $ autovacuum_vacuum_scale_factor: '0.1' $ autovacuum_vacuum_threshold: '50' $ archive_mode: 'on' $ archive_command: '/bin/true' $ wal_log_hints: 'on' $ ssl: "on" $ ssl_ciphers: "TLSv1.2:!aNULL:!eNULL" $ ssl_cert_file: /pgdata/certs/server.crt $ ssl_key_file: /pgdata/certs/server.key $ users: $ app_user: $ password: "aZ5QrESZ" $ pg_hba: $ - local all all scram-sha-256 $ - hostssl all all 127.0.0.1/32 scram-sha-256 $ - hostssl all all ::1/128 scram-sha-256 $ - hostssl all all ::1/128 scram-sha-256 $ - hostssl all all 0.0.0.0/0 scram-sha-256 $ - hostssl replication replicator patroni-01.patroni.test scram-sha-256 $ - hostssl replication replicator patroni-01.patroni.test scram-sha-256 $ - hostssl replication replicator patroni-01.patroni.test scram-sha-256 $ initdb: $ - encoding: UTF8 $ [root@patroni-01 patroni]#
The only difference within patroni.yml on the three nodes within this example setup is:
name: “patroni-01.patroni.test” needs to be adapted to “patroni-02.patroni.test” or “patroni-03.patroni.test”
Under postgresql:
connect_address: “patroni-01.patroni.test:5432” needs to be adapted to “patroni-02.patroni.test:5432” or “patroni-03.patroni.test:5432”.
listen: 192.168.198.132:5432 needs to be adpated to the corosponding IPs 192.168.198.133:5432 or 192.168.198.134:5432.
Under reatapi:
connect_address: “patroni-01.patroni.test:8008” to “patroni-02.patroni.test:8008” or “patroni-02.patroni.test:8008”.
listen: “patroni-01.patroni.test:8008” to “patroni-02.patroni.test:8008” or “patroni-02.patroni.test:8008”.
In my exapmle patroni-01 is the consul leader, so here we need to start patroni first to be leader within the patroni cluster.
Means the consul leader will be the patroni leader in any case, also in case of failover.
$ postgres@patroni-01: patronictl list $ + Cluster: PG1 (7358967191570897068) -----------------+---------+----+-----------+ $ | Member | Host | Role | State | TL | Lag in MB | $ +-------------------------+-----------------+---------+---------+----+-----------+ $ | patroni-01.patroni.test | 192.168.198.132 | Leader | running | 2 | | $ | patroni-02.patroni.test | 192.168.198.133 | Replica | running | 2 | 0 | $ | patroni-03.patroni.test | 192.168.198.134 | Replica | running | 2 | 0 | $ +-------------------------+-----------------+---------+---------+----+-----------+
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/05/open-source-author.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/09/SNA_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/STH_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2024/01/HME_web.jpg)