本文简单介绍了在流复制主备环境下如何使用HAProxy搭建负载均衡环境。 **安装** 使用yum安装相关软件，在数据库服务器上安装xinted和telnet ``` yum -y install xinetd telnet ``` **配置xinetd** 编写识别主从库的脚本pgsqlchk ``` [pg12@localhost ~]$ cat pgsqlchk #!/bin/bash # This script checks if a postgres server is healthy running on localhost. It will return: # "HTTP/1.x 200 OK\r" (if postgres is running smoothly) # - OR - # "HTTP/1.x 500 Internal Server Error\r" (else) # The purpose of this script is make haproxy capable of monitoring postgres properly # It is recommended that a low-privileged postgres user is created to be used by this script. # For eg. create user healthchkusr login password 'hc321'; PGBIN=/appdb/pg12/pg12.0/bin PGSQL_HOST="localhost" PGSQL_PORT="5432" PGSQL_DATABASE="testdb" PGSQL_USERNAME="pg12" export PGPASSWORD="root" TMP_FILE="/tmp/pgsqlchk.out" ERR_FILE="/tmp/pgsqlchk.err" # We perform a simple query that should return a few results VALUE=`$PGBIN/psql -qt -h localhost -U pg12 -p 5432 -c "select pg_is_in_recovery()" 2> /dev/null` # Check the output. If it is not empty then everything is fine and we return something. Else, we just do not return anything. if [ $VALUE == "t" ] then /bin/echo -e "HTTP/1.1 206 OK\r\n" /bin/echo -e "Content-Type: Content-Type: text/plain\r\n" /bin/echo -e "\r\n" /bin/echo "Standby" /bin/echo -e "\r\n" elif [ $VALUE == "f" ] then /bin/echo -e "HTTP/1.1 200 OK\r\n" /bin/echo -e "Content-Type: Content-Type: text/plain\r\n" /bin/echo -e "\r\n" /bin/echo "Primary" /bin/echo -e "\r\n" else /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n" /bin/echo -e "Content-Type: Content-Type: text/plain\r\n" /bin/echo -e "\r\n" /bin/echo "DB Down" /bin/echo -e "\r\n" fi [pg12@localhost ~]$ ``` 如为主库，该脚本模拟接收http请求返回http 200响应，如为从库则返回206，如数据库不可用则返回503 ``` #主库 [pg12@localhost ~]$ ifconfig | grep 'inet' inet 192.168.26.28 netmask 255.255.0.0 broadcast 192.168.255.255 ... [pg12@localhost ~]$ ./pgsqlchk HTTP/1.1 200 OK Content-Type: Content-Type: text/plain Primary #从库 [pg12@localhost ~]$ ifconfig | grep 'inet' inet 192.168.26.25 netmask 255.255.0.0 broadcast 192.168.255.255 ... [pg12@localhost ~]$ ./pgsqlchk HTTP/1.1 206 OK Content-Type: Content-Type: text/plain Standby ``` 创建xinetd配置文件，端口使用23267，指向刚才配置的pgsqlchk执行文件 ``` [pg12@localhost ~]$ cat /etc/xinetd.d/pgsqlchk service pgsqlchk { flags = REUSE socket_type = stream port = 23267 wait = no user = nobody server = /home/pg12/pgsqlchk log_on_failure += USERID disable = no only_from = 0.0.0.0/0 per_source = UNLIMITED } ``` 添加服务，并启动xinetd ``` [pg12@localhost ~]$ sudo bash -c 'echo "pgsqlchk 23267/tcp # pgsqlchk" >> /etc/services' [pg12@localhost ~]$ sudo systemctl start xinetd [pg12@localhost ~]$ systemctl status xinetd ● xinetd.service - Xinetd A Powerful Replacement For Inetd Loaded: loaded (/usr/lib/systemd/system/xinetd.service; enabled; vendor preset: enabled) Active: active (running) since Thu 2019-11-14 12:04:37 CST; 23s ago Process: 2847 ExecStart=/usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid $EXTRAOPTIONS (code=exited, status=0/SUCCESS) Main PID: 2848 (xinetd) CGroup: /system.slice/xinetd.service └─2848 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid [pg12@localhost ~]$ ``` 检查服务和监听，（可通过设置flags=IPv4调整为监听IPv4地址） ``` [pg12@localhost ~]$ sudo netstat -antup | grep 23267 tcp6 0 0 :::23267 :::* LISTEN 6837/xinetd [pg12@localhost ~]$ sudo systemctl restart xinetd [pg12@localhost ~]$ sudo systemctl status xinetd -l ● xinetd.service - Xinetd A Powerful Replacement For Inetd Loaded: loaded (/usr/lib/systemd/system/xinetd.service; enabled; vendor preset: enabled) Active: active (running) since Thu 2019-11-14 15:43:49 CST; 6s ago Process: 7461 ExecStart=/usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid $EXTRAOPTIONS (code=exited, status=0/SUCCESS) Main PID: 7462 (xinetd) CGroup: /system.slice/xinetd.service └─7462 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid Nov 14 15:43:49 localhost.localdomain xinetd[7462]: START: pgsqlchk pid=7463 from=192.168.26.26 Nov 14 15:43:49 localhost.localdomain xinetd[7462]: EXIT: pgsqlchk status=0 pid=7463 duration=0(sec) Nov 14 15:43:52 localhost.localdomain xinetd[7462]: START: pgsqlchk pid=7465 from=192.168.26.26 Nov 14 15:43:52 localhost.localdomain xinetd[7462]: EXIT: pgsqlchk status=0 pid=7465 duration=0(sec) Nov 14 15:43:52 localhost.localdomain xinetd[7462]: START: pgsqlchk pid=7466 from=192.168.26.26 Nov 14 15:43:52 localhost.localdomain xinetd[7462]: EXIT: pgsqlchk status=0 pid=7466 duration=0(sec) Nov 14 15:43:55 localhost.localdomain xinetd[7462]: START: pgsqlchk pid=7467 from=192.168.26.26 Nov 14 15:43:55 localhost.localdomain xinetd[7462]: EXIT: pgsqlchk status=0 pid=7467 duration=0(sec) Nov 14 15:43:55 localhost.localdomain xinetd[7462]: START: pgsqlchk pid=7468 from=192.168.26.26 Nov 14 15:43:55 localhost.localdomain xinetd[7462]: EXIT: pgsqlchk status=0 pid=7468 duration=0(sec) [pg12@localhost ~]$ sudo netstat -antup | grep 23267 tcp 0 0 0.0.0.0:23267 0.0.0.0:* LISTEN 7462/xinetd [pg12@localhost ~]$ ``` **配置HAProxy with xinetd** 在代理服务器上安装HAProxy ``` yum -y install haproxy telnet ``` 配置HAProxy（/etc/haproxy/haproxy.cfg） ``` [pg12@localhost ~]$ cat /etc/haproxy/haproxy.cfg #--------------------------------------------------------------------- # Example configuration for a possible web application. See the # full configuration options online. # # http://haproxy.1wt.eu/download/1.4/doc/configuration.txt # #--------------------------------------------------------------------- #--------------------------------------------------------------------- # Global settings #--------------------------------------------------------------------- global # to have these messages end up in /var/log/haproxy.log you will # need to: # # 1) configure syslog to accept network log events. This is done # by adding the '-r' option to the SYSLOGD_OPTIONS in # /etc/sysconfig/syslog # # 2) configure local2 events to go to the /var/log/haproxy.log # file. A line like the following can be added to # /etc/sysconfig/syslog # # local2.* /var/log/haproxy.log # log 127.0.0.1 local2 chroot /var/lib/haproxy pidfile /var/run/haproxy.pid maxconn 4000 user haproxy group haproxy daemon # turn on stats unix socket stats socket /var/lib/haproxy/stats #--------------------------------------------------------------------- # common defaults that all the 'listen' and 'backend' sections will # use if not designated in their block #--------------------------------------------------------------------- defaults mode http log global option httplog option http-server-close option forwardfor except 127.0.0.0/8 retries 3 timeout http-request 10s timeout queue 1m timeout connect 10s timeout client 1m timeout server 1m timeout http-keep-alive 10s timeout check 10s maxconn 3000 listen stats mode http bind *:7000 stats enable stats uri / listen ReadWrite bind *:5000 option httpchk http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg0 pg0:5432 maxconn 100 check port 23267 listen ReadOnly bind *:5001 option httpchk http-check expect status 206 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg0 pg0:5432 maxconn 100 check port 23267 server pg1 pg1:5432 maxconn 100 check port 23267 [pg12@localhost ~]$ ``` 启动HAProxy ``` [pg12@localhost ~]$ sudo systemctl start haproxy [pg12@localhost ~]$ ``` 划重点： 1.HAProxy使用TCP模式而不是默认的http模式 2.HAProxy监听的端口是5000-5001 3.端口5000用于RW而5001用于Read-only 4.使用http-check（端口为23267）判断状态 5.pg0可用于读写，pg1仅用于读 6.基于http-check，确定服务器状态（根据服务响应确定：200/206/503） 验证haproxy服务是否正常启动，如出现bind socket的错误，则需调整内核参数和SELinux策略 ``` 1.设置内核参数： net.ipv4.ip_nonlocal_bind=1 2.设置SELinux策略 setsebool -P haproxy_connect_any=1 vim /etc/sysconfig/selinux SELINUX=enforcing --> SELINUX=permissive ``` 正常启动HAProxy的日志 ``` [root@localhost ~]# systemctl status haproxy -l ● haproxy.service - HAProxy Load Balancer Loaded: loaded (/usr/lib/systemd/system/haproxy.service; disabled; vendor preset: disabled) Active: active (running) since Thu 2019-11-14 14:41:09 CST; 12min ago Main PID: 1963 (haproxy-systemd) CGroup: /system.slice/haproxy.service ├─1963 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid ├─1964 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds └─1965 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds Nov 14 14:41:09 localhost.localdomain systemd[1]: Started HAProxy Load Balancer. Nov 14 14:41:09 localhost.localdomain haproxy-systemd-wrapper[1963]: haproxy-systemd-wrapper: executing /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds [root@localhost ~]# ``` **负载均衡** to be continued **参考资料** [PostgreSQL Application Connection Failover Using HAProxy with xinetd](https://www.percona.com/blog/2019/10/31/postgresql-application-connection-failover-using-haproxy-with-xinetd/)