postgresql 高可用 etcd + patroni 之七 haproxy
os: ubuntu 16.04
postgresql: 9.6.9
etcd: 3.2.18
patroni: 1.4.4
haproxy: 1.6.3
ip规划
192.168.56.101 node1 master
192.168.56.102 node2 slave
192.168.56.103 node3 slave
192.168.56.104 node4 slave
192.168.56.105 node5 slave
192.168.56.201 haproxy1
在 etcd+patroni 前加一层 haproxy，让 haproxy 判断 master/slave。不使用 patroni 的 callback 进行 master vip 的卸载与绑定。
本篇blog介绍的是单点 haproxy，下一篇blog对 haproxy 做 ha。
安装haproxy
# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.3 LTS
Release: 16.04
Codename: xenial
# apt install haproxy
# dpkg -L haproxy
关键的几个文件
/etc/logrotate.d/haproxy
/etc/rsyslog.d/49-haproxy.conf
/etc/init.d/haproxy
/etc/haproxy/haproxy.cfg
/etc/default/haproxy
/lib/systemd/system/haproxy.service
/usr/sbin/haproxy
/usr/bin/halog
/usr/lib/tmpfiles.d/haproxy.conf
配置haproxy
# vi /etc/haproxy/haproxy.cfg
#---------------------------------------------------------------------
# 全局定义
global
# log语法：log [max_level_1]
# 全局的日志配置，使用log关键字，指定使用127.0.0.1上的syslog服务中的local0日志设备，
# 记录日志等级为info的日志
log 127.0.0.1 local0 info
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
# 定义每个haproxy进程的最大连接数 ，由于每个连接包括一个客户端和一个服务器端，
# 所以单个进程的TCP会话最大数目将是该值的两倍。
maxconn 4096
# 用户，组
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 5120
#---------------------------------------------------------------------
# 配置haproxy web监控，查看统计信息
listen status
bind 0.0.0.0:1080
mode http
log global
stats enable
# stats是haproxy的一个统计页面的套接字，该参数设置统计页面的刷新间隔为30s
stats refresh 30s
stats uri /haproxy-stats
# 设置统计页面认证时的提示内容
stats realm Private lands
# 设置统计页面认证的用户和密码，如果要设置多个，另起一行写入即可
stats auth admin:passw0rd
# 隐藏统计页面上的haproxy版本信息
stats hide-version
#---------------------------------------------------------------------
# 监听 postgresql0
listen postgresql0
bind 0.0.0.0:5432
# 配置TCP模式
mode tcp
# 简单的轮询
balance roundrobin
timeout client 1d
timeout server 1d
option httpchk
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server node1 192.168.56.101:5432 cookie postgresql0_node1 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server node2 192.168.56.102:5432 cookie postgresql0_node2 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server node3 192.168.56.103:5432 cookie postgresql0_node3 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server node4 192.168.56.104:5432 cookie postgresql0_node4 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server node5 192.168.56.105:5432 cookie postgresql0_node5 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
启动 haproxy
# systemctl stop haproxy
# systemctl status haproxy
# systemctl start haproxy
# systemctl enable haproxy
平滑重启
# haproxy -f /etc/haproxy/haproxy.cfg -sf `cat /var/run/haproxy.pid`
rsyslog
# apt install rsyslog
# cat /etc/rsyslog.d/49-haproxy.conf
# tail -f /var/log/haproxy.log
Oct 15 15:42:14 haproxy2 haproxy-systemd-wrapper[1343]: haproxy-systemd-wrapper: exit, haproxy RC=0
Oct 15 15:42:14 haproxy2 haproxy-systemd-wrapper[22497]: haproxy-systemd-wrapper: executing /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
Oct 15 15:42:14 haproxy2 haproxy-systemd-wrapper[22497]: [WARNING] 287/154214 (22498) : config : proxy 'postgresql0' : ignoring cookie for server 'node1' as HTTP mode is disabled.
Oct 15 15:42:14 haproxy2 haproxy-systemd-wrapper[22497]: [WARNING] 287/154214 (22498) : config : proxy 'postgresql0' : ignoring cookie for server 'node2' as HTTP mode is disabled.
Oct 15 15:42:14 haproxy2 haproxy-systemd-wrapper[22497]: [WARNING] 287/154214 (22498) : config : proxy 'postgresql0' : ignoring cookie for server 'node3' as HTTP mode is disabled.
Oct 15 15:42:14 haproxy2 haproxy-systemd-wrapper[22497]: [WARNING] 287/154214 (22498) : config : proxy 'postgresql0' : ignoring cookie for server 'node4' as HTTP mode is disabled.
Oct 15 15:42:14 haproxy2 haproxy-systemd-wrapper[22497]: [WARNING] 287/154214 (22498) : config : proxy 'postgresql0' : ignoring cookie for server 'node5' as HTTP mode is disabled.
查看haproxy
查看 etcd + patroni 的 postgresql。
# etcdctl cluster-health
member 1ed680804c6f2330 is healthy: got healthy result from http://192.168.56.101:2379
member 25c24d407ac5f4f6 is healthy: got healthy result from http://192.168.56.102:2379
member 6b09f22d1ec1913b is healthy: got healthy result from http://192.168.56.103:2379
member 7b9bc3861dcccbd8 is healthy: got healthy result from http://192.168.56.104:2379
member b7971532c6e6072a is healthy: got healthy result from http://192.168.56.105:2379
cluster is healthy
# patronictl -c /usr/patroni/conf/patroni_postgresql.yml list
+---------+---------------+----------------+--------+---------+-----------+
| Cluster | Member | Host | Role | State | Lag in MB |
+---------+---------------+----------------+--------+---------+-----------+
| pgsql96 | pgsql96_node1 | 192.168.56.101 | Leader | running | 0.0 |
| pgsql96 | pgsql96_node2 | 192.168.56.102 | | running | 0.0 |
| pgsql96 | pgsql96_node3 | 192.168.56.103 | | running | 0.0 |
| pgsql96 | pgsql96_node4 | 192.168.56.104 | | running | 0.0 |
| pgsql96 | pgsql96_node5 | 192.168.56.105 | | running | 0.0 |
+---------+---------------+----------------+--------+---------+-----------+
查看 haproxy 进程
# ps -ef|grep -i haproxy
root 2388 1 0 14:28 ? 00:00:00 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
haproxy 2389 2388 0 14:28 ? 00:00:00 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
haproxy 2394 2389 0 14:28 ? 00:00:01 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
root 2454 1487 0 15:05 pts/0 00:00:00 grep --color=auto -i haproxy
查看 haproxy 端口
# netstat -antp|grep -i haproxy
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 2394/haproxy
tcp 0 0 0.0.0.0:1080 0.0.0.0:* LISTEN 2394/haproxy
验证 haproxy
打开浏览器，配置文件预先定义,输入
http://192.168.56.201:1080/haproxy-stats
admin
passw0rd
可以观察到 postgresql0 =》Server =》LastChk 对 master/slave 的显示是不一样的。
master L7OK/200 in 3ms
slave L7STS/503 in 3ms
使用 pgadmin 4 连接到 haproxy1 的 5432 端口。执行sql
select inet_server_addr(),inet_server_port();
会一直显示 master 的ip、port。
此时中间件应用服务器连接postgresql数据库时只需要recreate connection 即可。
参考：
https://blog.csdn.net/sj349781478/article/details/78862315
