原 【DB宝72】pgpool-II和pgpoolAdmin的使用
Tags： 原创PGLinux高可用监控Pgpool-IIpgpoolAdmin
👉 本文共约6022个字，系统预计阅读时间或需23分钟。
目录
四、部署和使用pgpool-II和pgpoolAdmin工具
4.1、快速部署pgpool-II和pgpoolAdmin工具
由于安装和配置比较繁琐，我们本文主要着重使用，所以，这里省略安装和配置过程，具体可以参考：【DB宝61】PostgreSQL使用Pgpool-II实现读写分离+负载均衡
我们可以直接使用麦老师配置好的镜像，该镜像集成了pgpool-II和pgpoolAdmin工具，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | docker rm -f lhrpgpool docker run -d --name lhrpgpool -h lhrpgpool \ --net=pg-network --ip 172.72.6.66 \ -p 19999:9999 -p 19898:9898 -p 180:80 -p 13389:3389 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrpgpool:4.2.2 \ /usr/sbin/init docker network connect bridge lhrpgpool docker restart lhrpgpool docker exec -it lhrpgpool bash -- lhrbest/lhrpgpool:4.2.2 只安装了pgpool -- lhrbest/lhrpgpool:4.2.2_01 安装了pgpool并做了配置 -- lhrbest/lhrpgpool:4.2.2_02 安装了pgpool和pgpoolAdmin并做了配置 |
启动容器后，我们就拥有了pgpool-II和pgpoolAdmin工具。
4.2、使用pgpool-II实现读写分离+负载均衡
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | [root@lhrpgpool /]# systemctl status pgpool ● pgpool.service - Pgpool-II Loaded: loaded (/usr/lib/systemd/system/pgpool.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2021-06-24 15:06:22 CST; 9min ago Main PID: 181 (pgpool) CGroup: /docker/d45b808bb68bd3fa21db1881b6fc82a1ae194abc88fcbb6b8667875d9257ac54/system.slice/pgpool.service ├─181 /postgresql/pgpool/bin/pgpool -f /postgresql/pgpool/etc/pgpool.conf -n ├─218 pgpool: wait for connection request ├─219 pgpool: wait for connection request ├─220 pgpool: wait for connection request ├─221 pgpool: wait for connection request ├─222 pgpool: wait for connection request ├─223 pgpool: wait for connection request ├─224 pgpool: wait for connection request ├─225 pgpool: wait for connection request ├─226 pgpool: wait for connection request ├─227 pgpool: wait for connection request ├─229 pgpool: wait for connection request ├─230 pgpool: wait for connection request ├─231 pgpool: wait for connection request ├─235 pgpool: wait for connection request ├─236 pgpool: wait for connection request ├─237 pgpool: wait for connection request ├─239 pgpool: wait for connection request ├─240 pgpool: wait for connection request ├─241 pgpool: wait for connection request ├─243 pgpool: wait for connection request ├─245 pgpool: wait for connection request ├─246 pgpool: wait for connection request ├─249 pgpool: wait for connection request ├─251 pgpool: PCP: wait for connection request ├─252 pgpool: worker process ├─767 pgpool: wait for connection request ├─784 pgpool: wait for connection request ├─829 pgpool: wait for connection request ├─840 pgpool: wait for connection request ├─841 pgpool: wait for connection request ├─845 pgpool: wait for connection request ├─846 pgpool: wait for connection request ├─915 pgpool: wait for connection request └─916 pgpool: wait for connection request Jun 24 15:15:16 lhrpgpool pgpool[251]: [150-1] 2021-06-24 15:15:16: pid 251: LOG: PCP process with pid: 911 exits with status 0 Jun 24 15:15:16 lhrpgpool pgpool[251]: [151-1] 2021-06-24 15:15:16: pid 251: LOG: forked new pcp worker, pid=913 socket=8 Jun 24 15:15:16 lhrpgpool pgpool[251]: [152-1] 2021-06-24 15:15:16: pid 251: LOG: PCP process with pid: 913 exit with SUCCESS. Jun 24 15:15:16 lhrpgpool pgpool[251]: [153-1] 2021-06-24 15:15:16: pid 251: LOG: PCP process with pid: 913 exits with status 0 Jun 24 15:15:16 lhrpgpool pgpool[774]: [20-1] 2021-06-24 15:15:16: pid 774: FATAL: pgpool is not accepting any new connections Jun 24 15:15:16 lhrpgpool pgpool[181]: [49-1] 2021-06-24 15:15:16: pid 181: LOG: child process with pid: 774 exits with status 256 Jun 24 15:15:16 lhrpgpool pgpool[181]: [50-1] 2021-06-24 15:15:16: pid 181: LOG: fork a new child process with pid: 915 Jun 24 15:15:16 lhrpgpool pgpool[773]: [18-1] 2021-06-24 15:15:16: pid 773: FATAL: pgpool is not accepting any new connections Jun 24 15:15:16 lhrpgpool pgpool[181]: [51-1] 2021-06-24 15:15:16: pid 181: LOG: child process with pid: 773 exits with status 256 Jun 24 15:15:16 lhrpgpool pgpool[181]: [52-1] 2021-06-24 15:15:16: pid 181: LOG: fork a new child process with pid: 916 |
4.2.1、添加被监控主机
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | vi /postgresql/pgpool/etc/pgpool.conf # - Backend Connection Settings - backend_hostname0 = '172.72.6.2' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/postgresql/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_application_name0 = 'lhrpg64302' backend_hostname1 = '172.72.6.3' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/postgresql/data' backend_flag1 = 'ALLOW_TO_FAILOVER' backend_application_name1 = 'lhrpg64303' backend_hostname2 = '172.72.6.4' backend_port2 = 5432 backend_weight2 = 1 backend_data_directory2 = '/var/lib/postgresql/data' backend_flag2 = 'ALLOW_TO_FAILOVER' backend_application_name2 = 'lhrpg64304' |
? 注意：
写只会发送到172.72.6.2，而读会均衡的发送到172.72.6.2、172.72.6.3和172.72.6.4上。
如果想读只发送到172.72.6.3和172.72.6.4上，那么需要修改172.72.6.2的权重backend_weight0 = 0
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
不错不错哈