PostgreSQL高可用集群之patroni（二）

原创贺晓群 2021-06-26

3330

本文接上篇，上篇使用的是回调脚本实现了主节点VIP漂移，这篇主要介绍使用haproxy+keepalived，haproxy作为服务代理和Patroni配套使用可以很方便地支持failover，读写分离和负载均衡（写访问VIP:5000，读访问VIP:5001），这也是Patroni社区官方方案。缺点是haproxy本身也会占用资源，所有数据流量都经过haproxy，性能上会有一定损耗。如果只配置一个haproxy，本身就有可能成为单点故障，所以可以把haproxy部署在pg_node1和pg_node2 2台主机上，通过keepalived控制VIP（192.168.210.88）在pg_node1和pg_node2上漂移。

服务器信息：

节点名	IP	操作系统	安装软件	备注
pg_node1	192.168.210.15	CentOS 7.6	haproxy 1.5.18/keepalived 1.3.5	VIP：192.168.210.88，keepalived主节点
pg_node2	192.168.210.81	CentOS 7.6	haproxy 1.5.18/keepalived 1.3.5	keepalived备节点

删除回调脚本：

#注释掉所有patroni节点配置文件中的回调脚本
vi /etc/patroni/patroni.yml
#  callbacks: 
#    on_start: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务启动时候的触发的操作
#    on_stop: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务停止时候触发的操作
#    on_role_change: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务角色切换时触发的操作
systemctl restart patroni

pg_node1和pg_node2配置防火墙：

firewall-cmd --add-port=1080/tcp --permanent
firewall-cmd --add-port=5000/tcp --permanent
firewall-cmd --add-port=5001/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-all

pg_node1和pg_node2安装软件包：

yum -y install haproxy keepalived psmisc

配置haproxy和keepalived：

#pg_node1和pg_node2添加haproxy配置

mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.bak
cat >> /etc/haproxy/haproxy.cfg <<EOF
#全局定义
global
    #log语法：log [max_level_1]
    #全局的日志配置，使用log关键字，指定使用127.0.0.1上的syslog服务中的local0日志设备，
    #记录日志等级为info的日志
    #log         127.0.0.1 local0 info
    #log         127.0.0.1 local1 notice
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
     
    #定义每个haproxy进程的最大连接数 ，由于每个连接包括一个客户端和一个服务器端，
    #所以单个进程的TCP会话最大数目将是该值的两倍。
    maxconn     3000
     
    #用户，组
    user        haproxy
    group       haproxy
     
    #以守护进程的方式运行
    daemon
 
    #turn on stats unix socket
    stats socket /var/lib/haproxy/stats
 
#默认部分的定义
defaults    
    #mode语法：mode {http|tcp|health} 。http是七层模式，tcp是四层模式，health是健康检测，返回OK
    mode tcp   
    #使用127.0.0.1上的syslog服务的local3设备记录错误信息
    log 127.0.0.1 local3 err
 
    #if you set mode to http,then you nust change tcplog into httplog
    option     tcplog
     
    #启用该项，日志中将不会记录空连接。所谓空连接就是在上游的负载均衡器或者监控系统为了
    #探测该服务是否存活可用时，需要定期的连接或者获取某一固定的组件或页面，或者探测扫描
    #端口是否在监听或开放等动作被称为空连接；官方文档中标注，如果该服务上游没有其他的负
    #载均衡器的话，建议不要使用该参数，因为互联网上的恶意扫描或其他动作就不会被记录下来
    option     dontlognull
     
    #定义连接后端服务器的失败重连次数，连接失败次数超过此值后将会将对应后端服务器标记为不可用      
    retries    3
     
    #当使用了cookie时，haproxy将会将其请求的后端服务器的serverID插入到cookie中，以保证
    #会话的SESSION持久性；而此时，如果后端的服务器宕掉了，但是客户端的cookie是不会刷新的
    #，如果设置此参数，将会将客户的请求强制定向到另外一个后端server上，以保证服务的正常
    option redispatch
 
    #等待最大时长  When a server's maxconn is reached, connections are left pending in a queue  which may be server-specific or global to the backend.
    timeout queue           1m
     
    #设置成功连接到一台服务器的最长等待时间，默认单位是毫秒
    timeout connect         10s
     
    #客户端非活动状态的超时时长   The inactivity timeout applies when the client is expected to acknowledge or  send data.
    timeout client          1m
     
    #Set the maximum inactivity time on the server side.The inactivity timeout applies when the server is expected to acknowledge or  send data.
    timeout server          1m
    timeout check           5s
    maxconn                 3000

#配置haproxy web监控，查看统计信息
listen status
    bind *:1080
    mode http
    log global
     
    stats enable
    #stats是haproxy的一个统计页面的套接字，该参数设置统计页面的刷新间隔为30s
    stats refresh 30s
    stats uri /
    #设置统计页面认证时的提示内容
    stats realm Private lands
    #设置统计页面认证的用户和密码，如果要设置多个，另起一行写入即可
    stats auth admin:Admin2021
    #隐藏统计页面上的haproxy版本信息
    #stats hide-version

listen master
    bind *:5000
        mode tcp
        option tcplog
        balance roundrobin
    option httpchk OPTIONS /master
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
        server pg_node1 192.168.210.15:5432 maxconn 1000 check port 8008
        server pg_node2 192.168.210.81:5432 maxconn 1000 check port 8008
        server pg_node3 192.168.210.33:5432 maxconn 1000 check port 8008
listen replicas
    bind *:5001
        mode tcp
        option tcplog
        balance roundrobin
    option httpchk OPTIONS /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
        server pg_node1 192.168.210.15:5432 maxconn 1000 check port 8008
        server pg_node2 192.168.210.81:5432 maxconn 1000 check port 8008
        server pg_node3 192.168.210.33:5432 maxconn 1000 check port 8008
EOF

#keepalived主节点配置
[root@pg_node1 keepalived] mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@pg_node1 keepalived] cat >> /etc/keepalived/keepalived.conf <<EOF
global_defs {
    router_id twkp  #虚拟路由名称
    script_user root
    enable_script_security
}
 
#HAProxy健康检查配置
vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"  #使用killall -0检查haproxy实例是否存在，性能高于ps命令
    interval 2   #脚本运行周期
    weight 2     #每次检查的加权权重值
}
 
#虚拟路由配置
vrrp_instance VI_1 {
    state MASTER           #本机实例状态，MASTER/BACKUP，备机配置文件中请写BACKUP
    interface eth0         #本机网卡名称，使用ifconfig命令查看
    virtual_router_id 88   #虚拟路由编号，主备机保持一致
    priority 100           #本机初始权重，备机请填写小于主机的值（例如100）
    advert_int 5           #争抢虚地址的周期，秒
    virtual_ipaddress {
        192.168.210.88     #虚地址IP，主备机保持一致
    }
    track_script {
        chk_haproxy        #对应的健康检查配置
    }
}
EOF

#keepalived备节点配置
[root@pg_node2 keepalived] mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@pg_node2 keepalived] cat >> /etc/keepalived/keepalived.conf <<EOF
global_defs {
    router_id twkp  #虚拟路由名称
    script_user root
    enable_script_security
}

#HAProxy健康检查配置
vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"  #使用killall -0检查haproxy实例是否存在，性能高于ps命令
    interval 2   #脚本运行周期
    weight 2     #每次检查的加权权重值
}

#虚拟路由配置
vrrp_instance VI_1 {
    state BACKUP           #本机实例状态，MASTER/BACKUP，备机配置文件中请写BACKUP
    interface eth0         #本机网卡名称，使用ifconfig命令查看
    virtual_router_id 88   #虚拟路由编号，主备机保持一致
    priority 99            #本机初始权重，备机请填写小于主机的值（例如100）
    advert_int 5           #争抢虚地址的周期，秒
    virtual_ipaddress {
        192.168.210.88     #虚地址IP，主备机保持一致
    }
    track_script {
        chk_haproxy        #对应的健康检查配置
    }
}
EOF

在主备节点依次启动haproxy和keepalived：

systemctl start haproxy
systemctl enable haproxy
systemctl start keepalived
systemctl enable keepalived

运行状态检查：

systemctl status haproxy
systemctl status keepalived

[root@pg_node1 patroni]# patronictl list
+ Cluster: twpg (6976142033405049133) ---+---------+----+-----------+-----------------+
| Member | Host                | Role    | State   | TL | Lag in MB | Pending restart |
+--------+---------------------+---------+---------+----+-----------+-----------------+
| pg1    | 192.168.210.15:5532 | Leader  | running | 18 |           | *               |
| pg2    | 192.168.210.81:5532 | Replica | running | 18 |       0.0 | *               |
| pg3    | 192.168.210.33:5532 | Replica | running | 18 |       0.0 | *               |
+--------+---------------------+---------+---------+----+-----------+-----------------+

#通过haproxy的5001端口访问PG，会轮询连接2个备库
#访问pg_node1上的haproxy
[root@pg_node1 patroni]# psql "host=192.168.210.15 port=5001 user=postgres dbname=postgres password=Test@123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery 
------------------+-------------------
 192.168.210.81   | t
(1 row)

[root@pg_node1 patroni]# psql "host=192.168.210.15 port=5001 user=postgres dbname=postgres password=Test@123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery 
------------------+-------------------
 192.168.210.33   | t
(1 row)

#访问pg_node2上的haproxy
[root@pg_node1 patroni]# psql "host=192.168.210.81 port=5001 user=postgres dbname=postgres password=Test@123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery 
------------------+-------------------
 192.168.210.81   | t
(1 row)

[root@pg_node1 patroni]# psql "host=192.168.210.81 port=5001 user=postgres dbname=postgres password=Test@123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery 
------------------+-------------------
 192.168.210.33   | t
(1 row)

#通过haproxy的5000端口访问PG主库
[root@pg_node1 patroni]# psql "host=192.168.210.15 port=5000 user=postgres dbname=postgres password=Test@123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery 
------------------+-------------------
 192.168.210.15   | f
(1 row)

[root@pg_node1 patroni]# psql "host=192.168.210.81 port=5000 user=postgres dbname=postgres password=Test@123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery 
------------------+-------------------
 192.168.210.15   | f
(1 row)

#通过haproxy的VIP:5001端口访问PG，会轮询连接2个备库
[root@pg_node1 patroni]# psql "host=192.168.210.88 port=5001 user=postgres dbname=postgres password=Test@123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery 
------------------+-------------------
 192.168.210.81   | t
(1 row)

[root@pg_node1 patroni]# psql "host=192.168.210.88 port=5001 user=postgres dbname=postgres password=Test@123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery 
------------------+-------------------
 192.168.210.33   | t
(1 row)

#通过haproxy的VIP:5000端口访问PG主库
[root@pg_node1 patroni]# psql "host=192.168.210.88 port=5000 user=postgres dbname=postgres password=Test@123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery 
------------------+-------------------
 192.168.210.15   | f
(1 row)

可以通过它的web接口http://192.168.210.88:1808/查看统计数据

#模拟pg_node1上的haproxy挂掉

[root@pg_node1 patroni]# ip -o -4 a
1: lo    inet 127.0.0.1/8 scope host lo\       valid_lft forever preferred_lft forever
2: eth0    inet 192.168.210.15/24 brd 192.168.210.255 scope global noprefixroute dynamic eth0\       valid_lft 78520sec preferred_lft 78520sec
2: eth0    inet 192.168.210.88/24 scope global secondary eth0\       valid_lft forever preferred_lft forever

#pg_node1节点kill掉haproxy，检查VIP是否漂移到了pg_node2
[root@pg_node1 patroni]# ps -ef | grep haproxy | grep -v grep
root     13631     1  0 Jun25 ?        00:00:00 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
haproxy  13632 13631  0 Jun25 ?        00:00:00 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
haproxy  13633 13632  0 Jun25 ?        00:00:47 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
[root@pg_node1 patroni]# killall haproxy
[root@pg_node1 patroni]# ps -ef | grep haproxy | grep -v grep
#VIP绑定到了pg_node2
[root@pg_node2 ~]# ip -o -4 a
1: lo    inet 127.0.0.1/8 scope host lo\       valid_lft forever preferred_lft forever
2: eth0    inet 192.168.210.81/24 brd 192.168.210.255 scope global noprefixroute dynamic eth0\       valid_lft 69130sec preferred_lft 69130sec
2: eth0    inet 192.168.210.88/32 scope global eth0\       valid_lft forever preferred_lft forever