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:
$ [[email protected] ~]# firewall-cmd --add-rich-rule='rule protocol value="vrrp" accept' --permanent $ success $ [[email protected] ~]# firewall-cmd --reload $ success $ [[email protected] ~]#
Next part will be the configuration of keepalived:
$ [[email protected] /]# 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 $ } $ } $ [[email protected] /]#
Priority defines the default role, in my case 255 for the master role.
The keepalived.conf for the backup role on patroni-02:
$ [[email protected] /]# 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 $ } $ } [[email protected] /]#
The keepalived.conf for the backup role on patroni-03:
$ [[email protected] /]# 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 $ } $ } $ [[email protected] /]#
Checking status on all three nodes.:
patroni-01 as MASTER:
$ [[email protected] /]# 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:
$ [[email protected] /]#setsebool -P haproxy_connect_any=1
haproxy.cfg is the same on all three servers:
$ [[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 / $ # 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 $ [[email protected] /]#
Starting and enabling haproxy:
$ [[email protected] /]# systemctl start haproxy $ [[email protected] /]# 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:
$ [[email protected] 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.
$ [[email protected] 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 $ } $ [[email protected] 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:
$ [[email protected] 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 - %[email protected]%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 $ [[email protected] 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.
$ [email protected]: 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 | $ +-------------------------+-----------------+---------+---------+----+-----------+