概述
基于patroni搭建了三台服务器的postgres集群，本文通过haproxy实现postgres的动态负载。
3台机器都需要安装haproxy，三个haproxy上面可以由负载均衡器或者其他工具提供服务。
安装
- haproxy的安装
# 基于CentOS7系统，通过yum只能安装haproxy-1.5或haproxy-1.8，这两个版本都已EOL
yum install haproxy # haproxy-1.5
vim /etc/haproxy/haproxy.cfg
yum install haproxy18 # haproxy-1.8，需要具有epel-release软件包
vim /etc/haproxy18/haproxy.cfg
# 源码安装方式：省略
- /etc/haproxy18/haproxy.conf
defaults
mode tcp
log global
option tcplog
option dontlognull
retries 3
option redispatch
timeout queue 1m
timeout client 30m # 1h
timeout connect 4s # 10s
timeout server 30m # 1h
timeout check 5s
#option clitcpka # haproxy与client端的keepalive配置
#option srvtcpka # haproxy与server端的keepalive配置
option tcpka # haproxy与双方都建立长连接
#clitcpka-cnt 3
#clitcpka-idle 180
#clitcpka-intvl 10
listen primary
bind 0.0.0.0:15432
mode tcp
balance roundrobin
option httpchk HEAD /primary
http-check expect status 200
default-server inter 3s fall 2 rise 1 on-marked-down shutdown-sessions
server DB01 192.168.86.101:5432 maxconn 2000 check port 8008
server DB02 192.168.86.102:5432 maxconn 2000 check port 8008
server DB03 192.168.86.103:5432 maxconn 2000 check port 8008
# acl is_lb_healthcheck src 192.168.0.10 # LB探活机制的源IP
# tcp-request connection reject if is_lb_healthcheck # 阻止探活转发到后端PG，避免造成PG频繁fork
listen replicas
bind 0.0.0.0:15433
mode tcp
balance roundrobin
option httpchk HEAD /replica
# option tcpka
http-check expect status 200
default-server inter 3s fall 2 rise 1 on-marked-down shutdown-sessions
server DB01 192.168.86.101:5432 maxconn 2000 check port 8008
server DB02 192.168.86.102:5432 maxconn 2000 check port 8008
server DB03 192.168.86.103:5432 maxconn 2000 check port 8008
# acl is_lb_healthcheck src 192.168.0.10 # LB探活机制的源IP
# tcp-request connection reject if is_lb_healthcheck # 阻止探活转发到后端PG，避免造成PG频繁fork
listen monitor
bind 0.0.0.0:8888
mode http
option httplog
stats enable
stats uri /haproxy_stats
stats realm Haproxy\ Statistics
# stats auth admin:123456 # haproxy web管理用户名密码，自行设置
stats refresh 30s
stats hide-version
通过15432始终与主节点建立连接，通过15433始终与备节点建立连接，主节点可读可写，备节点只能读，实现读写分离。检测8008端口由patroni提供。
- 启动
systemctl start haproxy18.service