简介
Pgpool-II是一个在PostgreSQL服务器和PostgreSQL数据库客户端之间工作的中间件。它是根据BSD许可证授权的。它提供以下功能。
连接池
Pgpool-II保存与PostgreSQL服务器的连接，并在具有相同属性（即用户名，数据库，协议版本）的新连接进入时重用它们。它减少了连接开销，并提高了系统的整体吞吐量。
复制
Pgpool-II可以管理多个PostgreSQL服务器。使用复制功能可以在2个或更多物理磁盘上创建实时备份，以便在磁盘发生故障时服务可以继续运行而不会停止服务器。
负载均衡
如果复制了数据库，则在任何服务器上执行SELECT查询都将返回相同的结果。Pgpool-II利用复制功能，通过在多个服务器之间分配SELECT查询来减少每个PostgreSQL服务器的负载，从而提高系统的整体吞吐量。充其量，性能与PostgreSQL服务器的数量成比例地提高。在许多用户同时执行许多查询的情况下，负载平衡最有效。
限制超出连接
PostgreSQL的最大并发连接数有限制，连接在这么多连接后被拒绝。但是，设置最大连接数会增加资源消耗并影响系统性能。pgpool-II对最大连接数也有限制，但额外连接将排队，而不是立即返回错误。
看家狗
Watchdog可以协调多个Pgpool-II，创建一个强大的集群系统，避免单点故障或脑裂。看门狗可以对其他pgpool-II节点执行生命检查，以检测Pgpoll-II的故障。如果活动Pgpool-II发生故障，则可以将备用Pgpool-II提升为活动状态，并接管虚拟IP。
在内存查询缓存中
在内存中查询缓存允许保存一对SELECT语句及其结果。如果有相同的SELECT，Pgpool-II将从缓存中返回值。由于不涉及SQL解析或访问PostgreSQL，因此在内存缓存中使用速度非常快。另一方面，在某些情况下，它可能比正常路径慢，因为它增加了存储缓存数据的一些开销。
Pgpool-II讲PostgreSQL的后端和前端协议，并在后端和前端之间传递消息。因此，数据库应用程序（前端）认为Pgpool-II是实际的PostgreSQL服务器，服务器（后端）将Pgpool-II视为其客户端之一。因为Pgpool-II对服务器和客户端都是透明的，所以现有的数据库应用程序可以与Pgpool-II一起使用。Pgpool-II讲述PostgreSQL的后端和前端协议，并在它们之间传递连接。因此，数据库应用程序（前端）认为Pgpool-II是实际的PostgreSQL服务器，服务器（后端）将Pgpool-II视为其客户端之一。因为Pgpool-II对服务器和客户端都是透明的，所以现有的数据库应用程序可以与Pgpool-II一起使用，几乎不需要更改其源码。
watchdog是pgpool的核心组件，watchdog在 pgpool方案中扮演非常重要的角色，当启动pgpool时会启动watchdog子进程，主要作用为:
和pgpool后端PostgreSQL数据库节点以及远程pgpool节点进行通信。
对远程pgpool节点是否存活进行检查。
当watchdog子进程启动时，对本地pgpool的配置和远程pgpool的配置参数进行检查，并且输出本地和远程pgpool不一致的参数。
当pgpool主节点宕机时,watchdog集群将选举出新的 watchdog主节点。
当pgpool备节点激活成主节点时，watchdog负责将VIP飘移到新的pgpool节点。
相关文章
之前发布过几篇有关pgpool的文章：
【DB宝60】PG12高可用之1主2从流复制环境搭建及切换测试
【DB宝61】PostgreSQL使用Pgpool-II实现读写分离+负载均衡
PostgreSQL高可用之repmgr（1主2从+1witness）+Pgpool-II实现主从切换+读写分离+负载均衡
【DB宝72】pgpool-II和pgpoolAdmin的使用
PG高可用之主从流复制+keepalived 的高可用
有关pgpool-II的相关文档：
pgpool-II 3.2.5手册
pgpool-II-3.2.5入门教程
架构
本节显示了使用Pgpool-II的流式复制配置示例。在本例中，我们使用3台Pgpool-II服务器来管理PostgreSQL服务器，以创建一个健壮的集群系统，并避免单点故障或脑裂。
本配置示例中使用了PostgreSQL 14。所有脚本都经过PostgreSQL 10及更高版本的测试。
我们使用3台安装了CentOS 7.6的服务器，server1、server2、server3。我们在每台服务器上安装PostgreSQL 14和Pgpool II。
我们假设所有Pgpool-II服务器和PostgreSQL服务器都位于同一子网中。
本文架构图如下所示：
Note:
Active
,Standby
,Primary
,Standby
这些角色不是固定的，在后续的切换中是可以改变的。
如果是2个节点，那么架构可以变为：
Table 1. 主机及IP地址
| Hostname | IP Address | Virtual IP | 别名 |
|---|---|---|---|
| lhrpg30 | 172.72.6.30 | 172.72.6.35 | Server1 |
| lhrpg31 | 172.72.6.31 | 172.72.6.35 | Server2 |
| lhrpg32 | 172.72.6.32 | 172.72.6.35 | Server3 |
Table 2. PostgreSQL版本及配置
| Item | Value | Detail |
|---|---|---|
| PostgreSQL Version | 14.2 | - |
| port | 5432 | - |
| $PGDATA | /var/lib/pgsql/14/data | - |
| Archive mode | on | /var/lib/pgsql/archivedir |
| Replication Slots | Enable | - |
| Start automatically | Enable | - |
Table 3. Pgpool-II版本及配置
| Item | Value | Detail |
|---|---|---|
| Pgpool-II Version | 4.3.0 | - |
| port | 9999 | Pgpool-II accepts connections |
| 9898 | PCP process accepts connections | |
| 9000 | watchdog accepts connections | |
| 9694 | UDP port for receiving Watchdog's heartbeat signal | |
| Config file | /etc/pgpool-II/pgpool.conf | Pgpool-II config file |
| Pgpool-II start user | postgres (Pgpool-II 4.1 or later) | Pgpool-II 4.0 or before, the default startup user is root |
| Running mode | streaming replication mode | - |
| Watchdog | on | Life check method: heartbeat |
| Start automatically | Enable | - |
Table 4. 示例脚本
| Feature | Script | Detail |
|---|---|---|
| Failover | /etc/pgpool-II/failover.sh.sample | Run by failover_command to perform failover |
| /etc/pgpool-II/follow_primary.sh.sample | Run by follow_primary_command to synchronize the Standby with the new Primary after failover. | |
| Online recovery | /etc/pgpool-II/recovery_1st_stage.sample | Run by recovery_1st_stage_command to recovery a Standby node |
| /etc/pgpool-II/pgpool_remote_start.sample | Run after recovery_1st_stage_command to start the Standby node | |
| Watchdog | /etc/pgpool-II/escalation.sh.sample | Run by wd_escalation_command to switch the Active/Standby Pgpool-II safely |
上述脚本包含在RPM包中，可以根据需要进行定制。
1-- 网卡
2docker network create --subnet=172.72.6.0/24 pg-network
3
4-- 申请主机
5docker rm -f lhrpg30
6docker run -d --name lhrpg30 -h lhrpg30 \
7 --net=pg-network --ip 172.72.6.30 \
8 -p 64330:5432 -p 9930:9999 \
9 -v /sys/fs/cgroup:/sys/fs/cgroup \
10 --privileged=true lhrbest/lhrcentos76:8.5 \
11 /usr/sbin/init
12
13
14docker rm -f lhrpg31
15docker run -d --name lhrpg31 -h lhrpg31 \
16 --net=pg-network --ip 172.72.6.31 \
17 -p 64331:5432 -p 9931:9999 \
18 -v /sys/fs/cgroup:/sys/fs/cgroup \
19 --privileged=true lhrbest/lhrcentos76:8.5 \
20 /usr/sbin/init
21
22
23docker rm -f lhrpg32
24docker run -d --name lhrpg32 -h lhrpg32 \
25 --net=pg-network --ip 172.72.6.32 \
26 -p 64332:5432 -p 9932:9999 \
27 -v /sys/fs/cgroup:/sys/fs/cgroup \
28 --privileged=true lhrbest/lhrcentos76:8.5 \
29 /usr/sbin/init
30
31
32[root@docker35 ~]# docker ps
33CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3484113aee6b76 lhrbest/lhrcentos76:8.5 "/usr/sbin/init" 50 seconds ago Up 47 seconds 0.0.0.0:64332->5432/tcp, :::64332->5432/tcp, 0.0.0.0:9932->9999/tcp, :::9932->9999/tcp lhrpg32
35cb9e1aa0798b lhrbest/lhrcentos76:8.5 "/usr/sbin/init" 52 seconds ago Up 50 seconds 0.0.0.0:64331->5432/tcp, :::64331->5432/tcp, 0.0.0.0:9931->9999/tcp, :::9931->9999/tcp lhrpg31
36f2b458ef2e7d lhrbest/lhrcentos76:8.5 "/usr/sbin/init" 55 seconds ago Up 52 seconds 0.0.0.0:64330->5432/tcp, :::64330->5432/tcp, 0.0.0.0:9930->9999/tcp, :::9930->9999/tcp lhrpg30
安装Pgpool-II和PostgreSQL 14
我们使用yum来安装Pgpool-II和PostgreSQL 14。
在3台机器都安装，如下：
yum安装PG 14.2
参考：【DB宝67】使用yum来安装PostgreSQL13.3数据库
1yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2yum install -y postgresql14 postgresql14-server postgresql14-contrib postgresql14-libs
3
4
5/usr/pgsql-14/bin/postgresql-14-setup initdb
6systemctl disable postgresql-14
7systemctl start postgresql-14
8systemctl status postgresql-14
9
10echo "postgres ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers
11echo 'export PS1="[\u@\h \W]$ "' >> /etc/profile
12echo "postgres:lhr" | chpasswd
13
14echo "export PATH=/usr/pgsql-14/bin:$PATH" >> /etc/profile
yum安装pgpool-II
https://www.pgpool.net/yum/rpms/
https://www.pgpool.net/mediawiki/index.php/Yum_Repository
https://www.pgpool.net/docs/latest/en/html/install-rpm.html
1yum install -y https://www.pgpool.net/yum/rpms/4.3/redhat/rhel-7-x86_64/pgpool-II-release-4.3-1.noarch.rpm
2yum install -y pgpool-II pgpool-II-pg14-extensions
3
4-- 会安装如下四个包：
5 Installing : libmemcached-1.0.16-5.el7.x86_64 1/4
6 Installing : pgpool-II-pcp-4.3.1-1.rhel7.x86_64 2/4
7 Installing : pgpool-II-4.3.1-1.rhel7.x86_64 3/4
8 Installing : pgpool-II-pg14-extensions-4.3.1-1.rhel7.x86_64
9
10
11systemctl disable pgpool-II.service
Note: 如果设置Pgpool-II自动启动，则需要将 search_primary_node_timeout
更改为适当的值，以便在服务器启动后启动PostgreSQL。如果Pgpool-II在 search_primary_node_timeout
期间无法连接到后端的PostgreSQL，它将失败。
准备工作
在server1操作即可。
配置归档
1su - postgres
2mkdir -p /var/lib/pgsql/archivedir
3
4
5-- 配置允许PG远程登录，注意版本：
6cat >> /var/lib/pgsql/14/data/postgresql.conf <<"EOF"
7listen_addresses = '*'
8port=5432
9unix_socket_directories='/var/run/postgresql/'
10logging_collector = on
11log_directory = 'pg_log'
12log_filename = 'postgresql-%a.log'
13log_truncate_on_rotation = on
14
15archive_mode = on
16archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'
17max_wal_senders = 10
18max_replication_slots = 10
19wal_level = replica
20hot_standby = on
21wal_log_hints = on
22
23password_encryption = 'md5'
24
25EOF
26
27cat > /var/lib/pgsql/14/data/pg_hba.conf << "EOF"
28# TYPE DATABASE USER ADDRESS METHOD
29local all all trust
30host all all ::1/128 trust
31host all all 127.0.0.1/32 trust
32host all all 172.72.6.0/24 trust
33host all all 0.0.0.0/0 md5
34host replication all 0.0.0.0/0 md5
35EOF
36
37
38sudo systemctl restart postgresql-14
39systemctl status postgresql-14
Server1配置用户
Table 6. Users
| User Name | Password | Detail |
|---|---|---|
| repl | repl | PostgreSQL复制用户 |
| pgpool | pgpool | Pgpool-II health check (health_check_user) and replication delay check (sr_check_user) user |
| postgres | postgres | User running online recovery |
1psql -U postgres -p 5432
2CREATE ROLE pgpool WITH LOGIN;
3CREATE ROLE repl WITH REPLICATION LOGIN;
4alter user postgres with encrypted password 'lhr';
5alter user pgpool with encrypted password 'lhr';
6alter user repl with encrypted password 'lhr';
7GRANT pg_monitor TO pgpool;
配置互信
1chmod +x ./sshUserSetup.sh
2./sshUserSetup.sh -user root -hosts "lhrpg30 lhrpg31 lhrpg32" -advanced exverify -confirm
3./sshUserSetup.sh -user postgres -hosts "lhrpg30 lhrpg31 lhrpg32" -advanced exverify -confirm
4
5
6
7cp /var/lib/pgsql/.ssh/id_rsa /var/lib/pgsql/.ssh/id_rsa_pgpool
8cp /var/lib/pgsql/.ssh/id_rsa.pub /var/lib/pgsql/.ssh/id_rsa_pgpool.pub
9chown -R postgres.postgres /var/lib/pgsql/.ssh/
10
11cp /root/.ssh/id_rsa /root/.ssh/id_rsa_pgpool
12cp /root/.ssh/id_rsa.pub /root/.ssh/id_rsa_pgpool.pub
pgpool配置
创建pgpool的密码文件
注意：/var/lib/pgsql
为postgres用的家目录。
1-- 配置psql的密码
2cat > /var/lib/pgsql/.pgpass <<"EOF"
3172.72.6.30:5432:replication:repl:lhr
4172.72.6.31:5432:replication:repl:lhr
5172.72.6.32:5432:replication:repl:lhr
6172.72.6.35:9999:postgres:pgpool:lhr
7172.72.6.35:9999:postgres:postgres:lhr
8lhrpg30:5432:replication:repl:lhr
9lhrpg31:5432:replication:repl:lhr
10lhrpg32:5432:replication:repl:lhr
11lhrpg30:5432:postgres:postgres:lhr
12lhrpg31:5432:postgres:postgres:lhr
13lhrpg32:5432:postgres:postgres:lhr
14EOF
15
16chown postgres.postgres /var/lib/pgsql/.pgpass
17chmod 600 /var/lib/pgsql/.pgpass
18
19
20
21echo 'localhost:9898:pgpool:lhr' > /var/lib/pgsql/.pcppass
22chmod 600 /var/lib/pgsql/.pcppass
23chown postgres.postgres /var/lib/pgsql/.pcppass
24
25
26echo 'pgpool:'`pg_md5 -u=pgpool lhr` >> /etc/pgpool-II/pcp.conf
创建pgpool_node_id
server11echo "0" > /etc/pgpool-II/pgpool_node_idserver21echo "1" > /etc/pgpool-II/pgpool_node_idserver31echo "2" > /etc/pgpool-II/pgpool_node_id
Pgpool-II参数配置
1cat > /etc/pgpool-II/pgpool.conf <<"EOF"
2
3
4backend_clustering_mode = 'streaming_replication'
5
6
7# - pgpool Connection Settings -
8listen_addresses = '*'
9port=9999
10
11# - Streaming Replication Check
12sr_check_user = 'pgpool'
13sr_check_password = ''
14
15# - Health Check
16health_check_period = 5
17health_check_timeout = 30
18health_check_user = 'pgpool'
19health_check_password = 'lhr'
20health_check_max_retries = 3
21
22
23search_primary_node_timeout = 10min
24
25
26# - Backend Connection Settings -
27backend_hostname0 = '172.72.6.30'
28backend_port0 = 5432
29backend_weight0 = 1
30backend_data_directory0 = '/var/lib/pgsql/14/data'
31backend_flag0 = 'ALLOW_TO_FAILOVER'
32backend_application_name0 = 'server1'
33
34backend_hostname1 = '172.72.6.31'
35backend_port1 = 5432
36backend_weight1 = 1
37backend_data_directory1 = '/var/lib/pgsql/14/data'
38backend_flag1 = 'ALLOW_TO_FAILOVER'
39backend_application_name0 = 'server2'
40
41backend_hostname2 = '172.72.6.32'
42backend_port2 = 5432
43backend_weight2 = 1
44backend_data_directory2 = '/var/lib/pgsql/14/data'
45backend_flag2 = 'ALLOW_TO_FAILOVER'
46backend_application_name0 = 'server3'
47
48
49# - Failover configuration
50failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
51follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
52
53# - Online Recovery Configurations
54recovery_user = 'postgres'
55recovery_password = 'lhr'
56recovery_1st_stage_command = 'recovery_1st_stage'
57
58
59# - Client Authentication Configuration
60enable_pool_hba = on
61pool_passwd = '/etc/pgpool-II/pool_passwd'
62
63# - Watchdog Configuration
64use_watchdog = on
65delegate_IP = '172.72.6.35'
66
67if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:1'
68if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
69arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'
70
71hostname0 = '172.72.6.30'
72wd_port0 = 9000
73pgpool_port0 = 9999
74
75hostname1 = '172.72.6.31'
76wd_port1 = 9000
77pgpool_port1 = 9999
78
79hostname2 = '172.72.6.32'
80wd_port2 = 9000
81pgpool_port2 = 9999
82
83wd_lifecheck_method = 'heartbeat'
84wd_interval = 10
85
86heartbeat_hostname0 = '172.72.6.30'
87heartbeat_port0 = 9694
88heartbeat_device0 = ''
89
90heartbeat_hostname1 = '172.72.6.31'
91heartbeat_port1 = 9694
92heartbeat_device1 = ''
93
94heartbeat_hostname2 = '172.72.6.32'
95heartbeat_port2 = 9694
96heartbeat_device2 = ''
97
98wd_heartbeat_keepalive = 2
99wd_heartbeat_deadtime = 30
100
101wd_escalation_command = '/etc/pgpool-II/escalation.sh'
102
103
104
105# - Where to log -
106log_destination = 'stderr'
107logging_collector = on
108log_directory = '/var/log/pgpool-II'
109log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
110log_truncate_on_rotation = on
111log_rotation_age = 1d
112log_rotation_size = 10MB
113
114
115pid_file_name = '/var/run/postgresql/pgpool.pid'
116
117
118#------------------------------------------------------------------------------
119# LOAD BALANCING MODE
120#------------------------------------------------------------------------------
121load_balance_mode = on
122
123
124EOF
125
126
127
128
129
130cp -p /etc/pgpool-II/failover.sh{.sample,}
131cp -p /etc/pgpool-II/follow_primary.sh{.sample,}
132chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_primary.sh}
133chmod +x /etc/pgpool-II/{failover.sh,follow_primary.sh}
134
135cp -p /etc/pgpool-II/recovery_1st_stage.sample /var/lib/pgsql/14/data/recovery_1st_stage
136cp -p /etc/pgpool-II/pgpool_remote_start.sample /var/lib/pgsql/14/data/pgpool_remote_start
137chown postgres:postgres /var/lib/pgsql/14/data/{recovery_1st_stage,pgpool_remote_start}
138chmod +x /var/lib/pgsql/14/data/{recovery_1st_stage,pgpool_remote_start}
139
140su - postgres
141psql template1 -c "CREATE EXTENSION pgpool_recovery"
142psql postgres -c "CREATE EXTENSION pgpool_recovery"
143
144echo "host all all 0.0.0.0/0 md5" >> /etc/pgpool-II/pool_hba.conf
145
146
147-- cat /etc/pgpool-II/pool_passwd
148pg_md5 --md5auth --username=pgpool "lhr"
149pg_md5 --md5auth --username=postgres "lhr"
150
151
152
153cat > /etc/pgpool-II/escalation.sh <<"EOF"
154#!/bin/bash
155# This script is run by wd_escalation_command to bring down the virtual IP on other pgpool nodes
156# before bringing up the virtual IP on the new active pgpool node.
157
158set -o xtrace
159
160PGPOOLS=(172.72.6.30 172.72.6.31 172.72.6.32)
161VIP=172.72.6.35
162DEVICE=eth0
163
164for pgpool in "${PGPOOLS[@]}"; do
165 [ "$HOSTNAME" = "$pgpool" ] && continue
166
167 ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$pgpool -i ~/.ssh/id_rsa_pgpool "
168 /usr/bin/sudo /sbin/ip addr del $VIP/24 dev $DEVICE
169 "
170done
171exit 0
172
173EOF
174
175
176chown postgres:postgres /etc/pgpool-II/escalation.sh
177chmod +x /etc/pgpool-II/*.sh
178
179
180chown postgres:postgres /etc/pgpool-II/*.conf
181
故障转移配置
在failover_command
参数中执行故障转移后要执行的shell脚本。如果我们使用3台PostgreSQL服务器，我们需要指定follow_primary_command
，以便在主节点故障转移后运行。对于两台PostgreSQL服务器，不需要设置follow_primary_command
。
1failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
2follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
示例脚本failover.sh和follow_primary.sh安装在/etc/pgpool-II/中。可以使用这些示例文件创建故障转移脚本。
1cp -p /etc/pgpool-II/failover.sh{.sample,}
2cp -p /etc/pgpool-II/follow_primary.sh{.sample,}
3chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_primary.sh}
4chmod +x /etc/pgpool-II/{failover.sh,follow_primary.sh}
注意脚本failover.sh
和follow_primary.sh
中的PGHOME是否配置正确。脚本follow_primary.sh
中的PCP_USER为pgpool。
Pgpool-II在线恢复配置
为了使用Pgpool II执行在线恢复，我们指定PostgreSQL用户名和在线恢复命令recovery_1st_stage。因为执行在线恢复需要PostgreSQL中的超级用户权限，所以我们在recovery_user中指定postgres_user。然后，我们在PostgreSQL主服务器（server1）的数据库集群目录中创建recovery_1st_stage和pgpool_remote_start，并添加执行权限。
1recovery_user = 'postgres'
2recovery_password = 'lhr'
3recovery_1st_stage_command = 'recovery_1st_stage'
在线恢复示例脚本recovery_First_stage和pgpool_remote_start安装在/etc/pgpool II/中。将这些文件复制到主服务器（server1）的数据目录。
1cp -p /etc/pgpool-II/recovery_1st_stage.sample /var/lib/pgsql/14/data/recovery_1st_stage
2cp -p /etc/pgpool-II/pgpool_remote_start.sample /var/lib/pgsql/14/data/pgpool_remote_start
3chown postgres:postgres /var/lib/pgsql/14/data/{recovery_1st_stage,pgpool_remote_start}
Basically, it should work if you change PGHOME according to PostgreSQL installation directory.
注意脚本recovery_1st_stage
和pgpool_remote_start
中的PGHOME是否配置正确。脚本follow_primary.sh
中的PCP_USER为pgpool。
为了使用在线恢复功能，需要pgpool_recovery、pgpool_remote_start、pgpool_switch_xlog等功能，因此我们需要在PostgreSQL server server1的template1上安装pgpool_recovery。
1su - postgres
2psql template1 -c "CREATE EXTENSION pgpool_recovery"
3
配置/etc/pgpool-II/pool_hba.conf
1-- cat /etc/pgpool-II/pool_passwd
2pg_md5 --md5auth --username=pgpool "lhr"
3pg_md5 --md5auth --username=postgres "lhr"
Watchdog配置
1cat > /etc/pgpool-II/escalation.sh <<"EOF"
2#!/bin/bash
3# This script is run by wd_escalation_command to bring down the virtual IP on other pgpool nodes
4# before bringing up the virtual IP on the new active pgpool node.
5
6set -o xtrace
7
8PGPOOLS=(172.72.6.30 172.72.6.31 172.72.6.32)
9VIP=172.72.6.35
10DEVICE=eth0
11
12for pgpool in "${PGPOOLS[@]}"; do
13 [ "$HOSTNAME" = "$pgpool" ] && continue
14
15 ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$pgpool -i ~/.ssh/id_rsa_pgpool "
16 /usr/bin/sudo /sbin/ip addr del $VIP/24 dev $DEVICE
17 "
18done
19exit 0
20
21EOF
22
23
24chown postgres:postgres /etc/pgpool-II/escalation.sh
25chmod +x /etc/pgpool-II/escalation.sh
启动Pgpool-II
在启动Pgpool-II之前，请先启动PostgreSQL；在关闭PostgreSQL之前，请先关闭Pgpool-II
1systemctl start pgpool-II.service
2systemctl status pgpool-II.service
过程：
1[root@lhrpg32 data]# systemctl start pgpool-II.service
2[root@lhrpg32 data]# systemctl status pgpool-II.service
3● pgpool-II.service - PGPool-II Middleware Between PostgreSQL Servers And PostgreSQL Database Clients
4 Loaded: loaded (/usr/lib/systemd/system/pgpool-II.service; disabled; vendor preset: disabled)
5 Active: active (running) since Wed 2022-02-23 16:20:55 CST; 373ms ago
6 Main PID: 7279 (pgpool)
7 CGroup: /docker/84113aee6b76f386a21bd9990d098af5a5720410d71245c74998d749926e8385/system.slice/pgpool-II.service
8 ├─7279 /usr/bin/pgpool -f /etc/pgpool-II/pgpool.conf -n -D
9 ├─7280 pgpool: PgpoolLogger
10 └─7282 pgpool: watchdog
11
12Feb 23 16:20:55 lhrpg32 systemd[1]: Started PGPool-II Middleware Between PostgreSQL Servers And PostgreSQL Database Clients.
13Feb 23 16:20:55 lhrpg32 pgpool[7279]: 2022-02-23 16:20:55.552: main pid 7279: LOG: Backend status file /tmp/pgpool_status does not exist
14Feb 23 16:20:55 lhrpg32 pgpool[7279]: 2022-02-23 16:20:55.553: main pid 7279: LOG: redirecting log output to logging collector process
15Feb 23 16:20:55 lhrpg32 pgpool[7279]: 2022-02-23 16:20:55.553: main pid 7279: HINT: Future log output will appear in directory "/var/log/pgpool_log".
16[root@lhrpg32 data]# systemctl status pgpool-II.service
17● pgpool-II.service - PGPool-II Middleware Between PostgreSQL Servers And PostgreSQL Database Clients
18 Loaded: loaded (/usr/lib/systemd/system/pgpool-II.service; disabled; vendor preset: disabled)
19 Active: active (running) since Wed 2022-02-23 16:20:55 CST; 11s ago
20 Main PID: 7279 (pgpool)
21 CGroup: /docker/84113aee6b76f386a21bd9990d098af5a5720410d71245c74998d749926e8385/system.slice/pgpool-II.service
22 ├─7279 /usr/bin/pgpool -f /etc/pgpool-II/pgpool.conf -n -D
23 ├─7280 pgpool: PgpoolLogger
24 ├─7282 pgpool: watchdog
25 ├─7288 pgpool: lifecheck
26 ├─7289 pgpool: heartbeat receiver
27 ├─7290 pgpool: heartbeat sender
28 ├─7291 pgpool: heartbeat receiver
29 ├─7292 pgpool: heartbeat sender
30 ├─7293 pgpool: wait for connection request
31 ├─7294 pgpool: wait for connection request
32 ├─7295 pgpool: wait for connection request
33 ├─7296 pgpool: wait for connection request
34 ├─7297 pgpool: wait for connection request
35 ├─7298 pgpool: wait for connection request
36 ├─7299 pgpool: wait for connection request
37 ├─7300 pgpool: wait for connection request
38 ├─7301 pgpool: wait for connection request
39 ├─7302 pgpool: wait for connection request
40 ├─7303 pgpool: wait for connection request
41 ├─7304 pgpool: wait for connection request
42 ├─7305 pgpool: wait for connection request
43 ├─7306 pgpool: wait for connection request
44 ├─7307 pgpool: wait for connection request
45 ├─7308 pgpool: wait for connection request
46 ├─7309 pgpool: wait for connection request
47 ├─7310 pgpool: wait for connection request
48 ├─7311 pgpool: wait for connection request
49 ├─7312 pgpool: wait for connection request
50 ├─7313 pgpool: wait for connection request
51 ├─7314 pgpool: wait for connection request
52 ├─7315 pgpool: wait for connection request
53 ├─7316 pgpool: wait for connection request
54 ├─7317 pgpool: wait for connection request
55 ├─7318 pgpool: wait for connection request
56 ├─7319 pgpool: wait for connection request
57 ├─7320 pgpool: wait for connection request
58 ├─7321 pgpool: wait for connection request
59 ├─7322 pgpool: wait for connection request
60 ├─7323 pgpool: wait for connection request
61 └─7324 pgpool: wait for connection request
62
63
64Feb 24 09:33:14 lhrpg30 systemd[1]: Started PGPool-II Middleware Between PostgreSQL Servers And PostgreSQL Database Clients.
65Feb 24 09:33:14 lhrpg30 pgpool[3256]: 2022-02-24 09:33:14.596: main pid 3256: LOG: Backend status file /tmp/pgpool_status does not exist
66Feb 24 09:33:14 lhrpg30 pgpool[3256]: 2022-02-24 09:33:14.597: main pid 3256: LOG: redirecting log output to logging collector process
67Feb 24 09:33:14 lhrpg30 pgpool[3256]: 2022-02-24 09:33:14.597: main pid 3256: HINT: Future log output will appear in directory "/var/log/pgpool-II".
68Feb 24 09:33:27 lhrpg30 sudo[3330]: postgres : TTY=unknown ; PWD=/ ; USER=root ; COMMAND=/sbin/ip addr add 172.72.6.35/24 dev eth0 label eth0:1
69Feb 24 09:33:27 lhrpg30 sudo[3332]: postgres : TTY=unknown ; PWD=/ ; USER=root ; COMMAND=/usr/sbin/arping -U 172.72.6.35 -w 1 -I eth0
70
71[root@lhrpg30 pgpool-II]# ifconfig
72eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
73 inet 172.72.6.30 netmask 255.255.255.0 broadcast 172.72.6.255
74 ether 02:42:ac:48:06:1e txqueuelen 0 (Ethernet)
75 RX packets 34373 bytes 39051830 (37.2 MiB)
76 RX errors 0 dropped 0 overruns 0 frame 0
77 TX packets 27310 bytes 1702331 (1.6 MiB)
78 TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
79
80eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
81 inet 172.72.6.35 netmask 255.255.255.0 broadcast 0.0.0.0
82 ether 02:42:ac:48:06:1e txqueuelen 0 (Ethernet)
83
84lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
85 inet 127.0.0.1 netmask 255.0.0.0
86 loop txqueuelen 1000 (Local Loopback)
87 RX packets 1907 bytes 500420 (488.6 KiB)
88 RX errors 0 dropped 0 overruns 0 frame 0
89 TX packets 1907 bytes 500420 (488.6 KiB)
90 TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
节点1已经有VIP了。
配置PostgreSQL主备库
3个节点都启动pgpool:
1systemctl start pgpool-II.service
2systemctl status pgpool-II.service
首先，我们应该使用Pgpool-II在线恢复功能设置PostgreSQL备用服务器。确保pcp_recovery_node命令使用的recovery_1st_stage和pgpool_remote_start脚本位于PostgreSQL主服务器（server1）的数据库群集目录中。
1[postgres@lhrpg30 data]$ pcp_recovery_node -h 172.72.6.30 -p 9898 -U pgpool -n 1
2Password:
3pcp_recovery_node -- Command Successful
4
5[postgres@lhrpg30 data]$ pcp_recovery_node -h 172.72.6.30 -p 9898 -U pgpool -n 2
6Password:
7pcp_recovery_node -- Command Successful
在执行pcp_recovery_node命令后，验证server2和server3是否作为PostgreSQL备用服务器启动。
1[postgres@lhrpg30 data]$ psql -h 172.72.6.35 -p 9999 -U pgpool postgres -c "show pool_nodes"
2 node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
3---------+-------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
4 0 | 172.72.6.30 | 5432 | up | up | 0.333333 | primary | primary | 0 | false | 0 | | | 2022-02-24 10:01:09
5 1 | 172.72.6.31 | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2022-02-24 10:50:54
6 2 | 172.72.6.32 | 5432 | up | up | 0.333333 | standby | standby | 0 | true | 0 | | | 2022-02-24 10:50:54
7(3 rows)
8
切换active/standby watchdog
1[root@lhrpg30 pgpool_log]# pcp_watchdog_info -h 172.72.6.35 -p 9898 -U pgpool
2Password:
33 3 YES 172.72.6.30:9999 Linux lhrpg30 172.72.6.30
4
5172.72.6.30:9999 Linux lhrpg30 172.72.6.30 9999 9000 4 LEADER 0 MEMBER #The Pgpool-II server started first became "LEADER".
6172.72.6.31:9999 Linux lhrpg31 172.72.6.31 9999 9000 7 STANDBY 0 MEMBER #run as standby
7172.72.6.32:9999 Linux lhrpg32 172.72.6.32 9999 9000 7 STANDBY 0 MEMBER #run as standby
停止server1节点或者停止server1上的Pgpool-II服务：
1[postgres@lhrpg30 data]$ ifconfig
2eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
3 inet 172.72.6.30 netmask 255.255.255.0 broadcast 172.72.6.255
4 ether 02:42:ac:48:06:1e txqueuelen 0 (Ethernet)
5 RX packets 13542377 bytes 1195679259 (1.1 GiB)
6 RX errors 0 dropped 0 overruns 0 frame 0
7 TX packets 9497810 bytes 872759308 (832.3 MiB)
8 TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
9
10eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
11 inet 172.72.6.35 netmask 255.255.255.0 broadcast 0.0.0.0
12 ether 02:42:ac:48:06:1e txqueuelen 0 (Ethernet)
13
14lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
15 inet 127.0.0.1 netmask 255.0.0.0
16 loop txqueuelen 1000 (Local Loopback)
17 RX packets 59053 bytes 20664387 (19.7 MiB)
18 RX errors 0 dropped 0 overruns 0 frame 0
19 TX packets 59053 bytes 20664387 (19.7 MiB)
20 TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
21
22[postgres@lhrpg30 data]$
23[postgres@lhrpg30 data]$
24[postgres@lhrpg30 data]$
25[postgres@lhrpg30 data]$ cd
26[postgres@lhrpg30 ~]$
27[postgres@lhrpg30 ~]$
28[postgres@lhrpg30 ~]$ systemctl stop pgpool-II.service
29==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
30Authentication is required to manage system services or units.
31Authenticating as: root
32Password:
33==== AUTHENTICATION COMPLETE ===
34[postgres@lhrpg30 ~]$
35[postgres@lhrpg30 ~]$ ifconfig
36eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
37 inet 172.72.6.30 netmask 255.255.255.0 broadcast 172.72.6.255
38 ether 02:42:ac:48:06:1e txqueuelen 0 (Ethernet)
39 RX packets 13543714 bytes 1195859958 (1.1 GiB)
40 RX errors 0 dropped 0 overruns 0 frame 0
41 TX packets 9499106 bytes 872937513 (832.4 MiB)
42 TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
43
44lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
45 inet 127.0.0.1 netmask 255.0.0.0
46 loop txqueuelen 1000 (Local Loopback)
47 RX packets 60393 bytes 21167594 (20.1 MiB)
48 RX errors 0 dropped 0 overruns 0 frame 0
49 TX packets 60393 bytes 21167594 (20.1 MiB)
50 TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
51
52[postgres@lhrpg30 ~]$ pcp_watchdog_info -h 172.72.6.35 -p 9898 -U pgpool
53Password:
543 3 YES 172.72.6.32:9999 Linux lhrpg32 172.72.6.32
55
56172.72.6.32:9999 Linux lhrpg32 172.72.6.32 9999 9000 4 LEADER 0 MEMBER
57172.72.6.30:9999 Linux lhrpg30 172.72.6.30 9999 9000 10 SHUTDOWN 0 MEMBER
58172.72.6.31:9999 Linux lhrpg31 172.72.6.31 9999 9000 7 STANDBY 0 MEMBER
启动server1上的Pgpool-II服务， 该服务会以standby角色运行：
1[postgres@lhrpg30 ~]$ sudo systemctl start pgpool-II.service
2[postgres@lhrpg30 ~]$ pcp_watchdog_info -h 172.72.6.35 -p 9898 -U pgpool
3Password:
43 3 YES 172.72.6.32:9999 Linux lhrpg32 172.72.6.32
5
6172.72.6.32:9999 Linux lhrpg32 172.72.6.32 9999 9000 4 LEADER 0 MEMBER
7172.72.6.30:9999 Linux lhrpg30 172.72.6.30 9999 9000 7 STANDBY 0 MEMBER
8172.72.6.31:9999 Linux lhrpg31 172.72.6.31 9999 9000 7 STANDBY 0 MEMBER
Failover
1[postgres@lhrpg30 ~]$ psql -h 172.72.6.35 -p 9999 -U pgpool postgres -c "show pool_nodes"
2 node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
3---------+-------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
4 0 | 172.72.6.30 | 5432 | up | up | 0.333333 | primary | primary | 0 | true | 0 | | | 2022-02-24 10:59:29
5 1 | 172.72.6.31 | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2022-02-24 10:59:29
6 2 | 172.72.6.32 | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2022-02-24 10:59:29
7(3 rows)
8
停止节点1的PG数据库，
1[postgres@lhrpg30 ~]$ pg_ctl -D /var/lib/pgsql/14/data -m immediate stop
2waiting for server to shut down.... done
3server stopped
在关闭server1
上的PG后，failover自动发生，节点3成为新的主库：
1 [postgres@lhrpg30 ~]$ psql -h 172.72.6.35 -p 9999 -U pgpool postgres -c "show pool_nodes"
2 node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
3---------+-------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
4 0 | 172.72.6.30 | 5432 | down | down | 0.333333 | standby | unknown | 0 | false | 0 | | | 2022-02-24 11:17:21
5 1 | 172.72.6.31 | 5432 | up | up | 0.333333 | standby | standby | 0 | true | 0 | | | 2022-02-24 10:59:29
6 2 | 172.72.6.32 | 5432 | up | up | 0.333333 | primary | primary | 0 | false | 0 | | | 2022-02-24 10:59:29
7(3 rows)
8
在线恢复
恢复之前宕掉的server1主机：
1[postgres@lhrpg30 ~]$ pcp_recovery_node -h 172.72.6.35 -p 9898 -U pgpool -n 0
2Password:
3pcp_recovery_node -- Command Successful
可以看到，server1
作为一个standby存在：
1[postgres@lhrpg30 ~]$ psql -h 172.72.6.35 -p 9999 -U pgpool postgres -c "show pool_nodes"
2 node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
3---------+-------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
4 0 | 172.72.6.30 | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 280 | | | 2022-02-24 11:30:11
5 1 | 172.72.6.31 | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 280 | | | 2022-02-24 10:59:29
6 2 | 172.72.6.32 | 5432 | up | up | 0.333333 | primary | primary | 0 | true | 0 | | | 2022-02-24 11:30:00
7(3 rows)
8
读写分离+负载均衡
1[postgres@lhrpg30 ~]$ for i in $(seq 1 20); do psql -U pgpool -h 172.72.6.35 -p 9999 -d postgres -c 'SELECT inet_server_addr()'; done | egrep '172.'
2 172.72.6.31
3 172.72.6.32
4 172.72.6.32
5 172.72.6.30
6 172.72.6.30
7 172.72.6.30
8 172.72.6.31
9 172.72.6.31
10 172.72.6.30
11 172.72.6.31
12 172.72.6.30
13 172.72.6.31
14 172.72.6.32
15 172.72.6.31
16 172.72.6.30
17 172.72.6.31
18 172.72.6.31
19 172.72.6.31
20 172.72.6.31
21 172.72.6.32
22[postgres@lhrpg30 ~]$ for i in $(seq 1 20); do psql -U pgpool -h 172.72.6.35 -p 9999 -d postgres -c 'SELECT inet_server_addr()'; done | egrep '172.' | grep 31
23 172.72.6.31
24 172.72.6.31
25 172.72.6.31
26 172.72.6.31
27[postgres@lhrpg30 ~]$ for i in $(seq 1 20); do psql -U pgpool -h 172.72.6.35 -p 9999 -d postgres -c 'SELECT inet_server_addr()'; done | egrep '172.' | grep 32
28 172.72.6.32
29 172.72.6.32
30 172.72.6.32
31 172.72.6.32
32 172.72.6.32
33 172.72.6.32
34[postgres@lhrpg30 ~]$ for i in $(seq 1 20); do psql -U pgpool -h 172.72.6.35 -p 9999 -d postgres -c 'SELECT inet_server_addr()'; done | egrep '172.' | grep 30
35 172.72.6.30
36 172.72.6.30
37 172.72.6.30
38 172.72.6.30
39 172.72.6.30
40 172.72.6.30
41 172.72.6.30
测试之前，可以考虑修改文件pgpool.conf中的如下参数：
1log_statement=all
2log_per_node_statement =on
3client_min_messages =log
4log_min_messages = info
生效：
1pgpool reload
测试完成后，修改回原值：
1log_statement=off
2log_per_node_statement = off
3# client_min_messages =notice
4# log_min_messages = warning
测试过程：
1-- 开3个窗口
2psql -U postgres -h 192.168.66.35 -p 9999 -d sbtest
3
4create table test(id int);
5insert into test values(1);
6select * from test;
过程省略，参考：https://www.xmmup.com/dbbao61postgresqlshiyongpgpool-iishixianduxiefenlifuzaijunheng.html
安装pgpoolAdmin
更多内容参考：【DB宝72】pgpool-II和pgpoolAdmin的使用
pgpool项目还提供了一个使用PHP写的 Web管理工具，称为“pgpoolAdmin "，该Web管理工具可以以 Web界面方式实现pgpool-II 的配置。
pgpoolAdmin下载：https://pgpool.net/mediawiki/index.php/Downloads
pgpoolAdmin官方文档：https://www.pgpool.net/docs/pgpoolAdmin/index_en.html
pgpoolAdmin安装：https://www.pgpool.net/docs/pgpoolAdmin/en/install.html
The pgpool Administration Tool is management tool of pgpool. It is possible to monitor, start, stop pgpool and change setting for pgpool.
PgpoolAdmin是管理pgpool的WEB界面工具，可以对pgpool进行监控，启停服务，更改设置。
使用yum直接安装：
1yum install -y https://www.pgpool.net/yum/rpms/4.3/redhat/rhel-7-x86_64/pgpool-II-release-4.3-1.noarch.rpm
2yum install -y pgpoolAdmin
3ln -s /usr/share/pgpoolAdmin /var/www/html/admin
4
5-- 默认安装的php为5.4版本，需要卸载掉，最低版本为：PHP 5.6.0 and higher，否则浏览器会返回500错误
6rpm -e php-common-5.4.16-48.el7.x86_64 --nodeps
7rpm -e php-process-5.4.16-48.el7.x86_64 --nodeps
8rpm -e php-cli-5.4.16-48.el7.x86_64 --nodeps
9rpm -e php-5.4.16-48.el7.x86_64 --nodeps
10rpm -e php-pdo-5.4.16-48.el7.x86_64 --nodeps
11rpm -e php-pgsql-5.4.16-48.el7.x86_64 --nodeps
12
13
14-- 安装http和php
15rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
16rpm -Uvh https://mirror.webtatic.com/yum/el7/webtatic-release.rpm
17
18yum -y install httpd
19yum install -y php72w php72w-common php72w-cli php72w-gd php72w-xml php72w-mbstring php72w-ldap \
20php72w-pear php72w-xmlrpc php72w-pdo \
21php72w-mbstring php72w-pgsql php72w-process
22
23
24systemctl restart httpd.service
25systemctl status httpd.service
26
27php -v
28
29
30
31chmod 666 /etc/pgpool-II/pgpool.conf
32chmod 666 /etc/pgpool-II/pcp.conf
33
34
35echo "pgpooladmin:3996643de967b80174e48fb45d7227b1" >> /etc/pgpool-II/pcp.conf
36
37echo "*:*:pgpool:lhr" >> /usr/share/httpd/.pcppass
38echo "*:*:pgpooladmin:lhr" >> /usr/share/httpd/.pcppass
39
40chown apache:apache /usr/share/httpd/.pcppass
41chmod 0600 /usr/share/httpd/.pcppass
42
43
44-- 更新pool_passwd：cat etc/pgpool-II/pool_passwd
45pg_md5 --md5auth --username=pgpooladmin "lhr"
46
47
48
49-- 数据库创建用户
50create user pgpooladmin login encrypted password 'lhr' superuser;
51grant postgres to pgpooladmin;
启动：
1[root@lhrpg30 ~]# netstat -tulnp | grep 80
2[root@lhrpg30 ~]# systemctl enable httpd
3Created symlink from /etc/systemd/system/multi-user.target.wants/httpd.service to /usr/lib/systemd/system/httpd.service.
4[root@lhrpg30 ~]# systemctl start httpd
5[root@lhrpg30 ~]# systemctl status httpd
6● httpd.service - The Apache HTTP Server
7 Loaded: loaded (/usr/lib/systemd/system/httpd.service; enabled; vendor preset: disabled)
8 Active: active (running) since Thu 2022-02-24 12:52:59 CST; 3s ago
9 Docs: man:httpd(8)
10 man:apachectl(8)
11 Main PID: 27358 (httpd)
12 Status: "Processing requests..."
13 CGroup: /docker/7d7822b7277ed674bd973fcd9267cd9c0c28d62d21143aa5650c4e0b2d497253/system.slice/httpd.service
14 ├─27358 /usr/sbin/httpd -DFOREGROUND
15 ├─27359 /usr/sbin/httpd -DFOREGROUND
16 ├─27360 /usr/sbin/httpd -DFOREGROUND
17 ├─27361 /usr/sbin/httpd -DFOREGROUND
18 ├─27362 /usr/sbin/httpd -DFOREGROUND
19 └─27363 /usr/sbin/httpd -DFOREGROUND
20
21Feb 24 12:52:59 lhrpg30 systemd[1]: Starting The Apache HTTP Server...
22Feb 24 12:52:59 lhrpg30 httpd[27358]: AH00558: httpd: Could not reliably determine the server's fully qualified domain name, using 172.72.6.30. Set the 'ServerName' directive globally to suppress this message
23Feb 24 12:52:59 lhrpg30 systemd[1]: Started The Apache HTTP Server.
24
25[root@lhrpg30 ~]# netstat -tulnp | grep 80
26tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 27358/httpd
第一次使用，需要进行配置：
第一次使用，需要进行配置：
http://172.72.6.30/admin/install/
配置完成之后，提示会说删除install目录。
rm -rf var/www/html/admin/install最后使用在pcp.conf中的用户名密码登录。
登陆：http://172.72.6.30/admin/login.php ，用户名和密码：pgpooladmin/lhr
可以在界面进行操作进行switchover：
错误解决
可以正常进入登录界面，但是输入用户名和密码后，报错“500 Internal Server Error”，如下：
解决：php版本太低，最低需要PHP 5.6.0，请升级您的php版本。
参考
参考：
https://www.pgpool.net/docs/latest/en/html/example-cluster.html
https://www.jianshu.com/p/dab25c3af916?utm_campaign=haruki
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
