Pgpool安装部署（亲测可用）

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'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4.2.1.3 客户端认证

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

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' #保存日志文件的目录。
1
2
3
4
5
6
7

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 查询到备机。 所有的东西都被发送到主服务器，即使启用了负载均衡模式，直到备机追赶上来。
1
2
3
4
5
6
7
8
9
10

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' #执行健康检查的数据库名。
1
2
3
4
5

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 连接的本地域套接字建立的目录。
1
2
3
4
5

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命令查看本机网卡，如图所示：
1
2
3
4
5

![在这里插入图片描述](https://img-blog.csdnimg.cn/20190705105555151.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'
1
2
3
4
5
6
7
8
9
10
11
12

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
1
2
3
4
5
6

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命令加密
   
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地址限制的问题。
   

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;命令查看集群状态：
   

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构建压力测试，查询某条数据，查看日志，日志如下，表示从缓存中查询：

4.5 参考资料
http://www.pgpool.net/docs/pgpool-II-3.5.4/doc/pgpool-zh_cn.html#memcached_params