一、总体架构
192.168.3.73 主库:repmgr+master+etcd+Patroni+HAProxy
192.168.3.74 从库1 repmgr+standby+etcd+Patroni+HAProxy
192.168.3.75 从库2 repmgr+standby+etcd+Patroni
- 主节点和一个从节点安装 HAProxy
[root@pg73 ~]# cd /postgresql/soft
[root@pg73 soft]# ll
total 3524
-rw-rw-r--. 1 postgres postgres 3594183 Oct 19 09:34 haproxy-2.4.7.tar.gz
drwxr-xr-x. 2 postgres postgres 51 Mar 30 18:01 patroni
drwxrwxr-x. 6 postgres postgres 4096 Nov 6 13:01 postgresql-12.2
drwxrwxr-x. 6 postgres postgres 4096 Mar 1 16:06 repmgr-5.2.0
[root@pg73 soft]# su - postgres
Last login: Wed Mar 30 14:36:26 CST 2022 on pts/0
[postgres@pg73 ~]$ cd /postgresql/soft
[postgres@pg73 soft]$
[postgres@pg73 soft]$
[postgres@pg73 soft]$ tar -zxvf haproxy-2.4.7.tar.gz
haproxy-2.4.7/
haproxy-2.4.7/.cirrus.yml
haproxy-2.4.7/.gitattributes
haproxy-2.4.7/.github/
haproxy-2.4.7/.github/ISSUE_TEMPLATE/
haproxy-2.4.7/.github/ISSUE_TEMPLATE/Bug.md
haproxy-2.4.7/.github/ISSUE_TEMPLATE/Code-Report.md
haproxy-2.4.7/.github/ISSUE_TEMPLATE/Feature.md
haproxy-2.4.7/.github/ISSUE_TEMPLATE/Question.md
haproxy-2.4.7/.github/ISSUE_TEMPLATE/config.yml
haproxy-2.4.7/.github/errorfile
haproxy-2.4.7/.github/h2spec.config
haproxy-2.4.7/.github/workflows/windows.yml
haproxy-2.4.7/.gitignore
haproxy-2.4.7/.travis.yml
haproxy-2.4.7/BRANCHES
haproxy-2.4.7/CHANGELOG
haproxy-2.4.7/CONTRIBUTING
haproxy-2.4.7/INSTALL
haproxy-2.4.7/LICENSE
haproxy-2.4.7/MAINTAINERS
haproxy-2.4.7/Makefile
haproxy-2.4.7/README
haproxy-2.4.7/ROADMAP
haproxy-2.4.7/SUBVERS
haproxy-2.4.7/VERDATE
haproxy-2.4.7/tests/exp/uri_hash.c
haproxy-2.4.7/tests/unit/
haproxy-2.4.7/tests/unit/ist.c
haproxy-2.4.7/tests/unit/test-arg.c
haproxy-2.4.7/tests/unit/test-inherited-fd.py
haproxy-2.4.7/tests/unit/test-list.c
haproxy-2.4.7/tests/unit/test-sockpair.py
[postgres@pg73 soft]$ ll
total 3528
drwxrwxr-x. 13 postgres postgres 4096 Oct 4 20:56 haproxy-2.4.7
-rw-rw-r--. 1 postgres postgres 3594183 Oct 19 09:34 haproxy-2.4.7.tar.gz
drwxr-xr-x. 2 postgres postgres 51 Mar 30 18:01 patroni
drwxrwxr-x. 6 postgres postgres 4096 Nov 6 13:01 postgresql-12.2
drwxrwxr-x. 6 postgres postgres 4096 Mar 1 16:06 repmgr-5.2.0
[postgres@pg73 soft]$ ln -s haproxy-2.4.7 haproxy
[postgres@pg73 haproxy-2.4.7]$ pwd
/postgresql/soft/haproxy-2.4.7
[postgres@pg73 haproxy-2.4.7]$ su - root
Password:
Last login: Wed Mar 30 20:10:24 CST 2022 from 192.168.3.100 on pts/3
[root@pg73 /]# cd /postgresql/soft/haproxy-2.4.7/
[root@pg73 haproxy-2.4.7]# make TARGET=linux-glibc ARCH=x86_64 PREFIX=/postgresql/soft/haproxy USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 USE_SYSTEMD=1 USE_CPU_AFFINITY=1
CC src/haproxy.o
src/haproxy.c:81:31: fatal error: systemd/sd-daemon.h: No such file or directory
#include <systemd/sd-daemon.h>
^
compilation terminated.
make: *** [src/haproxy.o] Error 1
[root@pg73 soft]# systemctl stop keepalived
[root@pg73 soft]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Thu 2022-03-31 07:31:27 CST; 2s ago
Docs: man:keepalived(8)
man:keepalived.conf(5)
man:genhash(1)
https://keepalived.org
Main PID: 2842 (code=exited, status=0/SUCCESS)
Mar 30 14:34:29 pg73 Keepalived_vrrp[2843]: Sending gratuitous ARP on enp0s3 for 192.168.3.220
Mar 30 14:34:30 pg73 Keepalived_vrrp[2843]: Interface virbr0 added
Mar 30 14:34:30 pg73 Keepalived_vrrp[2843]: Interface virbr0-nic added
Mar 30 14:34:30 pg73 Keepalived_vrrp[2843]: (virbr0-nic) MAC Address changed from f2:7d:96:8f:31:77 to 52:54:00:88:a4:65
Mar 30 14:34:30 pg73 Keepalived_vrrp[2843]: (virbr0) MAC Address changed from fa:21:a6:f1:7e:61 to 52:54:00:88:a4:65
Mar 31 07:31:26 pg73 Keepalived[2842]: Stopping
Mar 31 07:31:26 pg73 systemd[1]: Stopping LVS and VRRP High Availability Monitor...
Mar 31 07:31:26 pg73 Keepalived_vrrp[2843]: (VI_1) sent 0 priority
Mar 31 07:31:26 pg73 Keepalived_vrrp[2843]: (VI_1) removing VIPs.
Mar 31 07:31:27 pg73 systemd[1]: Stopped LVS and VRRP High Availability Monitor.
[root@pg73 haproxy]# make TARGET=linux-glibc ARCH=x86_64 PREFIX=/postgresql/soft/haproxy USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 USE_SYSTEMD=1 USE_CPU_AFFINITY=1
CC src/ev_poll.o
CC src/ev_epoll.o
CC src/cpuset.o
CC src/http_htx.o
CC src/check.o
CC src/cache.o
CC src/cfgparse-listen.o
CC src/haproxy.o
src/haproxy.c:81:31: fatal error: systemd/sd-daemon.h: No such file or directory
#include <systemd/sd-daemon.h>
^
compilation terminated.
make: *** [src/haproxy.o] Error 1
[root@pg73 haproxy]#
[root@pg73 haproxy]# yum -y install gcc openssl-devel pcre-devel systemd-devel
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
file:///media/cdrom/repodata/repomd.xml: [Errno 14] curl#37 - "Couldn't open file /media/cdrom/repodata/repomd.xml"
Trying other mirror.
python3 | 951 B 00:00:00
Package gcc-4.8.5-36.el7.x86_64 already installed and latest version
Package 1:openssl-devel-1.0.2k-16.el7.x86_64 already installed and latest version
Package pcre-devel-8.32-17.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package systemd-devel.x86_64 0:219-62.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===============================================================================================================================================================================================================
Package Arch Version Repository Size
===============================================================================================================================================================================================================
Installing:
systemd-devel x86_64 219-62.el7 Server 199 k
Transaction Summary
===============================================================================================================================================================================================================
Install 1 Package
Total download size: 199 k
Installed size: 343 k
Downloading packages:
Error downloading packages:
systemd-devel-219-62.el7.x86_64: [Errno 256] No more mirrors to try.
[root@pg73 haproxy]# mount /dev/cdrom /media/cdrom
mount: /dev/sr0 is write-protected, mounting read-only
[root@pg73 haproxy]# yum -y install gcc openssl-devel pcre-devel systemd-devel
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Server | 4.3 kB 00:00:00
Package gcc-4.8.5-36.el7.x86_64 already installed and latest version
Package 1:openssl-devel-1.0.2k-16.el7.x86_64 already installed and latest version
Package pcre-devel-8.32-17.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package systemd-devel.x86_64 0:219-62.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===============================================================================================================================================================================================================
Package Arch Version Repository Size
===============================================================================================================================================================================================================
Installing:
systemd-devel x86_64 219-62.el7 Server 199 k
Transaction Summary
===============================================================================================================================================================================================================
Install 1 Package
Total download size: 199 k
Installed size: 343 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : systemd-devel-219-62.el7.x86_64 1/1
Verifying : systemd-devel-219-62.el7.x86_64 1/1
Installed:
systemd-devel.x86_64 0:219-62.el7
Complete!
[root@pg73 haproxy]# make TARGET=linux-glibc ARCH=x86_64 PREFIX=/postgresql/soft/haproxy USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 USE_SYSTEMD=1 USE_CPU_AFFINITY=1
CC src/ev_poll.o
CC src/ev_epoll.o
CC src/cpuset.o
CC src/ssl_sample.o
CC src/fix.o
CC src/arg.o
CC src/hash.o
CC src/version.o
LD haproxy
CC dev/flags/flags.o
LD dev/flags/flags
[root@pg73 haproxy]#
[root@pg73 haproxy]# make install PREFIX=/postgresql/soft/haproxy
install: creating directory ‘/postgresql/soft/haproxy/sbin’
‘haproxy’ -> ‘/postgresql/soft/haproxy/sbin/haproxy’
install: creating directory ‘/postgresql/soft/haproxy/share’
install: creating directory ‘/postgresql/soft/haproxy/share/man’
install: creating directory ‘/postgresql/soft/haproxy/share/man/man1’
‘doc/haproxy.1’ -> ‘/postgresql/soft/haproxy/share/man/man1/haproxy.1’
install: creating directory ‘/postgresql/soft/haproxy/doc/haproxy’
‘doc/configuration.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/configuration.txt’
‘doc/management.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/management.txt’
‘doc/seamless_reload.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/seamless_reload.txt’
‘doc/architecture.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/architecture.txt’
‘doc/peers-v2.0.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/peers-v2.0.txt’
‘doc/regression-testing.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/regression-testing.txt’
‘doc/cookie-options.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/cookie-options.txt’
‘doc/lua.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/lua.txt’
‘doc/WURFL-device-detection.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/WURFL-device-detection.txt’
‘doc/proxy-protocol.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/proxy-protocol.txt’
‘doc/linux-syn-cookies.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/linux-syn-cookies.txt’
‘doc/SOCKS4.protocol.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/SOCKS4.protocol.txt’
‘doc/network-namespaces.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/network-namespaces.txt’
‘doc/DeviceAtlas-device-detection.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/DeviceAtlas-device-detection.txt’
‘doc/51Degrees-device-detection.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/51Degrees-device-detection.txt’
‘doc/netscaler-client-ip-insertion-protocol.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/netscaler-client-ip-insertion-protocol.txt’
‘doc/peers.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/peers.txt’
‘doc/close-options.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/close-options.txt’
‘doc/SPOE.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/SPOE.txt’
‘doc/intro.txt’ -> ‘/postgresql/soft/haproxy/doc/haproxy/intro.txt’
[root@pg73 haproxy]#
编译Haproxy时出现
src/haproxy.c:66:31: 致命错误：systemd/sd-daemon.h：没有那个文件或目录
#include <systemd/sd-daemon.h>
原因：没有安装依赖环境
解决：
yum -y install gcc openssl-devel pcre-devel systemd-devel
2.配置环境变量，加入haproxy命令
sed -i "s;:\$PATH:;:/postgresql/soft/haproxy/sbin:\$PATH:;g" /etc/profile
source /etc/profile
3.安装 HAProxy 的所有节点创建配置文件
源码安装的需要新建配置文件 /postgresql/soft/haproxy/haproxy.cfg
global
log 127.0.0.1 local2
chroot /postgresql/soft/haproxy
pidfile /var/run/haproxy.pid
maxconn 5000
user root
group root
daemon
nbproc 2
defaults
mode tcp
log 127.0.0.1 local2 err
option tcplog
option dontlognull
option redispatch
retries 3
maxconn 5000
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 5s
listen status
bind *:1080
mode http
log global
stats enable
stats refresh 30s
stats uri /
stats realm Private lands
stats auth admin:admin
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 pgtest1 192.168.3.73:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgtest2 192.168.3.74:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgtest3 192.168.3.75:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
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 pgtest1 192.168.3.73:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgtest2 192.168.3.74:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgtest3 192.168.3.75:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
4.安装 HAProxy 的所有节点创建服务，启动haproxy
[root@pg73 ~]# vi /usr/lib/systemd/system/haproxy.service
[Unit]
Description=HAProxy Load Balancer
After=syslog.target network.target
[Service]
ExecStartPre=/postgresql/soft/haproxy/sbin/haproxy -f /postgresql/soft/haproxy/haproxy.cfg -c -q
ExecStart=/postgresql/soft/haproxy/sbin/haproxy -Ws -f /postgresql/soft/haproxy/haproxy.cfg -p /var/run/haproxy.pid
ExecReload=/bin/kill -USR2 $MAINPID
[Install]
WantedBy=multi-user.target
# 启动服务
systemctl daemon-reload
systemctl start haproxy
systemctl status haproxy
systemctl enable haproxy
- 浏览器访问 HAProxy
浏览器登录 http://192.168.3.73:1080 输入用户名 admin 密码 admin![]
6.测试5000和5001端口连接数据库
这里我们通过 5000 端口和 5001 端口分别来提供读写服务和只读服务，在没有安装keepalived的情况下，如果需要对数据库写入数据只需要对外提供 192.168.3.73/74 + 5000 端口即可，可以模拟主库故障，即关闭其中的 master 节点来验证是否会进行自动主从切换
[root@pg73 ~]# patronictl list
+--------+--------------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+ Cluster: pg_cluster (7027318900233185278) +----+-----------+
| pg73 | 192.168.3.73 | Leader | running | 2 | |
| pg74 | 192.168.3.74 | Replica | running | 2 | 0 |
| pg75 | 192.168.3.75 | Replica | running | 2 | 0 |
+--------+--------------+---------+---------+----+-----------+
[root@pg73 ~]# psql "host=192.168.3.73 port=5000 user=repmgr dbname=repmgr password=repmgr" -c 'select inet_server_addr(),pg_is_in_recovery()'
inet_server_addr | pg_is_in_recovery
------------------+-------------------
192.168.3.73 | f
(1 row)
[root@pg73 ~]# psql "host=192.168.3.74 port=5000 user=repmgr dbname=repmgr password=repmgr" -c 'select inet_server_addr(),pg_is_in_recovery()'
inet_server_addr | pg_is_in_recovery
------------------+-------------------
192.168.3.73 | f
(1 row)
[root@pg73 ~]# psql "host=192.168.3.74 port=5001 user=repmgr dbname=repmgr password=repmgr" -c 'select inet_server_addr(),pg_is_in_recovery()'
inet_server_addr | pg_is_in_recovery
------------------+-------------------
192.168.3.74 | t
(1 row)
[root@pg73 ~]# psql "host=192.168.3.73 port=5001 user=repmgr dbname=repmgr password=repmgr" -c 'select inet_server_addr(),pg_is_in_recovery()'
inet_server_addr | pg_is_in_recovery
------------------+-------------------
192.168.3.74 | t
(1 row)
最后修改时间：2022-04-24 16:56:27
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
