Pgpool安装部署（亲测可用）

清疚 2024-02-19 20:24 74阅读 0赞

### **Pgpool安装部署指导** ###

**1、 环境准备**  
**1.1软件**  
 CentOS 7.6  
 PGPool 3.5  
 PostgreSQL11.2  
**1.2节点**  
 node1 172.16.8.7（主库）  
 node2 172.16.8.6（从库）  
 node3 172.16.8.5（从库）  
 vip 172.16.5.9（虚拟ip）  
**1.3配置**  
 PostgreSQL Port:18083  
 复制账号:replica/replica-vcredit  
 管理账号:app1/\*\*\*\*\*\*\*\*

**2、 建立ssh互信**  
参考我的另一篇文章：https://blog.csdn.net/liwangcuihua/article/details/94720057

**3、 PostgreSql一主二从配置**  
参考我的另一篇文章：https://blog.csdn.net/liwangcuihua/article/details/94720459

**4、 Pgpool集群安装部署**  
注意：postgreSql及pgpool相关文件及文件夹需将操作权限配给当前（我的是app1）账号，否则更改文件内容及启动会出现没有权限的问题。  
**4.1 pgpool-II安装**

1.  在三个节点下载并安装pgpool-II和其扩展函数  
    sudo yum install pgpool-II-11.x86\_64  
    sudo yum install pgpool-II-11-extensions.x86\_64
2.  在主服务器上安装扩展函数  
    （a） 安装pgpool-regclass函数，此函数可解决在不同的 schema 中处理相同的表名出现的问题（临时表不会出问题）。  
    psql -h 172.16.8.7 -p 5432 -d gisdb \#连接主库  
    gisdb=\# CREATE EXTENSION pgpool\_regclass;  
    CREATE EXTENSION  
    （b） 安装C语言函数 pgpool-recovery,用于在线恢复节点。  
    gisdb=\# CREATE EXTENSION pgpool\_ recovery;  
    CREATE EXTENSION  
    （c） 建立insert\_lock 表，该表主要用与解决 pgpool-Ⅱ和 VACUUM 表锁的互斥问题。  
    psql -f /usr/pgpool-11/share/pgpool-II/insert\_lock.sql -h 172.16.8.7 -p 18083 -U app1 gisdb  
    **4.2 服务器配置pgpool-II**  
    复制pgpool-Ⅱ 模板文件:  
    cd etc/pgpool-II-11  
    cp pcp.conf.sample pcp.conf  
    cp pgpool.conf.sample pgpool.conf  
    cp pool\_hba.conf.sample pool\_hba.conf  
    4.2.1 配置pgpool.conf文件  
    这里只对目前应用到的参数进行配置讲解。下面是在三个环境上配置pgpool.conf文件。  
    4.2.1.1 连接
    
    listen\_addresses = ‘*’ \#指定pgpool-II 将接受 TCP/IP 连接的主机名或者IP地址  
    port = 18999 \# pgpool-II 监听 TCP/IP 连接的端口号  
    socket\_dir = ‘/var/run’ \# pgpool-II 建立用于建立接受 UNIX 域套接字连接的目录。  
    pcp\_listen\_addresses = '*’ \#指定 pcp 进程接收 TCP/IP 连接的主机名或IP地址。  
    pcp\_port = 18898 \# PCP 进程接受连接的端口号。  
    pcp\_socket\_dir = ‘/var/run’ \#PCP 进程用于建立接受 UNIX 域套接字连接的目录。

4.2.1.2 后端（postgreSql服务）

backend_hostname0 = '172.16.8.7' #指出连接到 PostgreSQL 后台程序的地址。0为主数据库
    backend_port0 = 18083 #指定后台程序的端口号。
    backend_weight0 = 1 #指定后台程序的负载均衡权重。
    backend_data_directory0 = '/data/pgsql11/data' #指定后台的数据库实例的目录。
    backend_flag0 = 'ALLOW_TO_FAILOVER' #允许故障切换或者从后台程序断开。
    
    backend_hostname1 = '172.16.8.6'
    backend_port1 = 18083
    backend_weight1 = 1000
    backend_data_directory1 = '/data/pgsql11/data'
    backend_flag1 = 'ALLOW_TO_FAILOVER'
    
    backend_hostname2 = '172.16.8.5'
    backend_port2 = 18083
    backend_weight2 = 1000
    backend_data_directory2 = '/data/pgsql11/data'
    backend_flag2 = 'ALLOW_TO_FAILOVER'

4.2.1.3 客户端认证

enable_pool_hba = on #如果为 on，则使用 pgpool_hba.conf 来进行客户端认证。
    pool_passwd = 'pool_passwd' #指定用于 md5 认证的文件名。默认值为"pool_passwd"。"" 表示禁用。

4.2.1.4 日志

log_line_prefix = '%t: pid %p: ' #输出到每行日志开头的打印样式字符串，默认值打印时间戳和进程号
    log_connections = off #如果为 on，进入的连接将被打印到日志中。
    log_hostname = off #如果为 on，ps 命令将显示客户端的主机名而不是 IP 地址。
    log_statement = off当设置为 on 时生成 SQL 日志消息。 
    log_per_node_statement = on #针对每个 DB 节点产生日志，要知道一个 SELECT 的结果是不是从查询缓存获得，需要启用它
    pid_file_name = '/var/run/pgpool-II-11/pgpool.pid' #到包含 pgpool-II 进程 ID 的文件的完整路径名。
    logdir = '/data/pgsql11/log' #保存日志文件的目录。

4.2.1.5 主备模式下开启负载均衡

replication_mode = off#设置为 on 以启用复制模式，在主/备模式中， replication_mode 必须被设置为 off，并且 master_slave_mode 为 on
    load_balance_mode = on # 当设置为 on时，SELECT 查询将被分发到每个后台程序上用于负载均衡。
    master_slave_mode = on # 主备模式
    master_slave_sub_mode = 'stream'  # 使用 PostgreSQL 内置的复制系统（基于流复制）时被设置
    
    sr_check_period = 10 #本参数指出基于流复制的延迟检查的间隔，单位为秒
    sr_check_user = 'pgpool1' #执行基于流复制检查的用户名。用户必须存在于所有的 PostgreSQL 后端上，否则，检查将出错。
    sr_check_password = 'pgpool1' #执行流复制检测的用户的密码。
    sr_check_database = 'gisdb' #执行流复制延迟检测的数据库。
    delay_threshold = 10000000 #指定能够容忍的备机上相对于主服务器上的 WAL 的复制延迟，单位为字节。 如果延迟到达了 delay_threshold，pgpool-II 不再发送 SELECT 查询到备机。 所有的东西都被发送到主服务器，即使启用了负载均衡模式，直到备机追赶上来。

4.2.1.6 健康检查

health_check_period = 10 # 本参数指出健康检查的间隔，单位为秒。
    health_check_timeout = 20 # 本参数用于避免健康检查在例如网线断开等情况下等待很长时间。 
    health_check_user = 'pgpool1' #用于执行健康检查的用户。用户必须存在于 PostgreSQL 后台中。
    health_check_password = 'pgpool1' #用于执行健康检查的用户的密码。
    health_check_database = 'gisdb' #执行健康检查的数据库名。

4.2.1.7 配置看门狗

use_watchdog = on #如果为 on，则激活看门狗。
    wd_hostname = '172.16.3.7' #指定 pgpool-II 的主机名或者IP地址，设置本节点的节点名
    wd_port = 19998 #指定看门狗的通信端口。
    wd_priority = 1 #本参数用于设定在主看门狗节点选举时本地看门狗节点的优先权。 在集群启动的时候或者旧的看门狗故障的时候，wd_priority 值较高的节点会被选为主看门狗节点。
    wd_ipc_socket_dir = '/var/run' #建立 pgpool-II 看门狗 IPC 连接的本地域套接字建立的目录。

4.2.1.8 虚拟IP控制

delegate_IP = '172.16.5.9' #指定客户端的服务（例如应用服务等）连接到的 pgpool-II 的虚拟 IP (VIP) 地址。 当一个 pgpool-II 从备节点切换为激活节点时，pgpool-II 接管这个 VIP。
    if_cmd_path = '/sbin' #本参数指定用于切换 IP 地址的命令的所在路径。
    if_up_cmd = 'ifconfig ens192:0 inet $_IP_$ netmask 255.255.255.0' #本参数指定一个命令用以启用虚拟 IP。设置命令和参数，例如 "ip addr add $_IP_$/24 dev eth0 label eth0:0"。 参数 $_IP_$ 会被 delegate_IP 设置的值替换。ens192根据现场机器改掉。
    if_down_cmd = 'ifconfig ens192:0 down' #本参数指定一个命令用以停用虚拟 IP
    注：ifconfig ens192:0 inet $_IP_$ netmask 255.255.255.0'为给网卡配置ip的命令，ens192:0表示虚拟网络接口，给单个网卡配置多个ip时可设置为eth192:x (x是0-255)，netmask 255.255.255.0 配置的是子网掩码，我们可以通过ifconfig命令查看本机网卡，如图所示：

!\[在这里插入图片描述\](https://img-blog.csdnimg.cn/20190705105555151.png![在这里插入图片描述][20191217182143126.png]

4.2.1.9 心跳模式

wd_lifecheck_method = 'heartbeat' #本参数指定存活检查的模式。
    wd_interval = 10 #本参数指定 pgpool-II 进行存活检查的间隔，单位为秒
    
    wd_heartbeat_port = 9694 #本选项指定接收心跳信号的端口号。默认为 9694 。
    wd_heartbeat_keepalive = 2 #本选项指定发送心跳信号的间隔（秒）。默认值为 2。
    wd_heartbeat_deadtime = 30 #如果本选项指定的时间周期内没有收到心跳信号，则看门狗认为远端的 pgpool-II 发生故障。
    heartbeat_destination0 = '172.16.8.6' #本选项指定心跳信号发送的目标，可以是 IP 地址或主机名。设置其它PostgreSQL节点的节点名
    heartbeat_destination_port0 = 9694 #本选项指定由 heartbeat_destinationX 指定的心跳信号目标的端口号。这个值通常等于 wd_heartbeat_port。
    heartbeat_device0 = 'ens192' #本选项指定用于发送心跳信号到由 heartbeat_destinationX指定的目标的设备名。
    heartbeat_destination1 = '172.16.8.5' #设置其它PostgreSQL节点的节点名
    heartbeat_destination_port1 = 9694
    heartbeat_device1 = 'ens192'

4.2.1.10 服务监控

other_pgpool_hostname0 = '172.16.8.6' #指定需要监控的 pgpool-II 服务器主机。这用于发送/接收查询和数据包，同时也是看门狗的标识。 设置其它Pgpool节点的节点名
    other_pgpool_port0 = 18999 # 指定需要监控的 pgpool-II 服务器的 pgpool 服务的端口。
    other_wd_port0 = 19998 # 指定需要监控的 pgpool-II 服务器的看门狗的端口。
    other_pgpool_hostname1 = '172.16.8.5'
    other_pgpool_port1 = 18999
    other_wd_port1 = 19998

4.2.2 配置pool\_hba.config文件（权限配置）  
4.2.2.1 配置pcp命令  
pgpool-II 有一个用于管理功能的接口，用于通过网络获取数据库节点信息、关闭 pgpool-II 等。要使用 PCP 命令，必须进行用户认证。这需要在 pcp.conf 文件中定义一个用户和密码。（pcp具体命令可参考网址http://www.pgpool.net/docs/pgpool-II-3.5.4/doc/pgpool-zh\_cn.html\#memcached\_params）。

1.  使用pg\_md5命令加密  
    ![在这里插入图片描述][20190705105610434.png]
2.  编写pcp.config文件  
    vi /etc/pgpool-II-11/pcp.conf  
    app1: f92ea1839dc16d7396db358365da7066
3.  编写用于md5认证的文件pool\_passwd  
    cd /etc/pgpool-II-11  
    vim pool\_passwd  
    app1: f92ea1839dc16d7396db358365da7066  
    4.2.2.2 配置pool\_hba.conf  
    pgpool可以按照和PostgreSQL的hba.conf类似的方式配置自己的主机认证，所有连接到pgpool上的客户端连接将接受认证，这解决了后端PostgreSQL无法直接对前端主机进行IP地址限制的问题。  
    ![在这里插入图片描述][20190705105625996.png]

4.3 pgpool的启动与测试  
4.3.1 pgpool的启动

1.  启动。注意：所有后端服务（sql、缓存）都必须在启动 pgpool-II 之前启动。  
    sudo pgpool -n \#前端启动命令，退出后会自动关闭  
    sudo pgpool -n > /data/pgpool/logfile/pgpool.log 2>&1 & \#后端启动命令，先启动主服务器，再启动从服务器，启动日志在pgpool.log查看
2.  连接。可以通过命令行或者navicat客户端进行连接测试。  
    psql -h 172.16.8.7 -p 18999 -d gisdb \#连接主pgpool  
    psql -h 172.16.8.6 -p 18999 -d gisdb \#连接从pgpool  
    psql -h 172.16.8.5 -p 18999 -d gisdb \#连接从pgpool  
    在172.16.8.7环境上，连接vip：  
    psql -h 172.16.5.9 -p 18999 -d gisdb \#通过虚拟ip连接pgpool  
    通过 show pool\_nodes;命令查看集群状态：  
    ![在这里插入图片描述][20190705105702961.png]

4.3.2 pgpool的停止  
sudo pgpool stop \#停止命令，如果还有客户端连接着， pgpool-II 等待它们断开连接，然后才结束运行。可用以下命令：  
sudo pgpool -m fast stop \#快速停止命令  
若以上均不能停止pgpool，则需kill掉pgpool的所有进程：  
sudo killall -9 pgpool  
删除所占端口文件  
sudo rm -rf /run/.s.PGSQL.18898  
sudo rm -rf /run/.s.PGSQL.18999  
4.3.3 HA测试

1.  模拟master端pgpool宕机  
    sudo pgpool -m fast stop
2.  psql -h 172.16.5.9 -p 18999 -d gisdb \#通过虚拟ip连接pgpool  
    通过 show pool\_nodes;命令查看集群状态  
    4.4 配置使用memcached  
    4.4.1 安装memcached
3.  安装  
    sudo yum install libevent libevent-devel  
    sudo yum install Memcached
4.  启动  
    /usr/bin/memcached -p 11211 -m 64m -d  
    4.4.2 配置pgpool.config缓存参数
    
    memory\_cache\_enabled = on \#要启用基于内存的查询缓存，设置以下选项为 on  
    memqcache\_method = ‘memcached’ \#你可以选择一个缓存策略：共享内存或者 memcached（不能同时使用）。  
    memqcache\_memcached\_host = ‘172.16.8.7’ \#指定 memcached 工作的主机的主机名或 IP 地址。  
    memqcache\_memcached\_port = 11211 \#指定 memcached 的端口。默认为 11211。  
    memqcache\_total\_size = 67108864 \#指定用于缓存的共享内存的大小，单位为字节。  
    memqcache\_max\_num\_cache = 1000000 \#制定缓存的项目数。这用于定义缓存管理空间的大小。  
    memqcache\_expire = 0 \#查询缓存的生命周期，默认为 0。0 表示没有缓存超时，而且缓存被启用直到表被更新。  
    memqcache\_auto\_cache\_invalidation = on \#如果为 on，则在表被更新的时候自动删除相关的缓存。  
    memqcache\_maxcache = 40960000 \#select结果集的大小最大现在  
    memqcache\_oiddir = ‘/data/pgpool/oiddir’ \#用于 SELECT 的存储表的 OID 的目录的完整路径。  
    white\_memqcache\_table\_list = ‘’ \#指定一个以逗号分隔的表名的列表，用于使 SELECT 的结果被缓存  
    black\_memqcache\_table\_list = ‘’ \#指定一个以逗号分隔的表名的列表，用于使 SELECT 的结果不被缓存

4.4.3 测试memcached  
通过jmeter构建压力测试，查询某条数据，查看日志，日志如下，表示从缓存中查询：  
![在这里插入图片描述][20190705105730722.png]  
4.5 参考资料  
http://www.pgpool.net/docs/pgpool-II-3.5.4/doc/pgpool-zh\_cn.html\#memcached\_params

[20191217182143126.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8c41aa0ed41f4f239ca64d4eeab15154.png
[20190705105610434.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/be795a5d038f4b8f8a0fd5b75def23c4.png
[20190705105625996.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/48141bcc0f004fdd9c3c0be6f73fa445.png
[20190705105702961.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/72deef07148c4d509eb4a9d584e9b48a.png
[20190705105730722.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3036163e0bb24c52b22e0514586a24bf.png