PostgreSQL基于 Pgpool 实现读写分离-02

1 Pgpool简介

Pgpool 是一个高性能的连接池和负载均衡器，用于 PostgreSQL 数据库。Pgpool 可以作为中间层，位于客户端和 PostgreSQL 服务器之间，来管理连接请求并分配给不同的 PostgreSQL 服务器进行处理，以提高整体的系统性能和可用性。Pgpool 的一些主要功能包括：

• 连接池：Pgpool在应用程序和数据库之间建立一个连接池，使得多个应用程序可以共享一组数据库连接，避免了重复的连接和断开。
• 负载均衡：Pgpool可以将客户端请求均衡地分配到多个PostgreSQL服务器上，以实现负载均衡和更好的性能。
• 高可用性：Pgpool可以检测到PostgreSQL服务器的故障，并自动将客户端请求重新路由到其他可用服务器，从而提高系统的可用性和稳定性。
• 并行查询：Pgpool可以将大型查询分成几个子查询，然后将这些子查询并行发送到多个PostgreSQL服务器上执行，以提高查询性能。

当使用 Postgresql HA 集群时，应用只需连接 pgpool 即可。

• 通过 pgpool 实现读写分离，写入操作由 Master 执行，读取操作由 Slave 执行。
• 由 repmgr 实现流复制，Master 数据自动复制到 Slave。
• 当 Master 遇故障下线时，由 repmgr 自定选择 Slave 为 Master，并继续执行写入操作。
• 当某个节点遇故障下线时，由 pgpool 自动断开故障节点的连接，并切换到可用的节点上。

pgpool官方：pgpool-II 4.5.0 Documentation

pgpoolAdmin官方：Installing pgpool Management Tool

2 Pgpool-II部署

延续上一章节[进阶学习-17]进行

2.1 数据准备

主库执行

pgpool下载地址：Index of /mediawiki/images

#master节点执行
cd /opt/pg/soft/
wget -c "https://www.pgpool.net/mediawiki/images/pgpool-II-4.3.0.tar.gz"

#安装包：pgpool-II-4.3.0.tar.gz

2.2 pgpool单点安装

主库执行

#解压安装包
cd /opt/pg/soft/
tar xf pgpool-II-4.3.0.tar.gz -C /opt
mv /opt/pgpool-II-4.3.0/ /opt/pgpool-II
#编译安装
#安装文档：https://www.pgpool.net/docs/pgpool-II-4.3.0/en/html/install-pgpool.html
cd /opt/pgpool-II/
./configure --prefix=/opt/pgpool-II/ --with-pgsql=/opt/pg/pgsql/12.15/
make -j 4 && make install

#配置环境变量
vi /etc/profile
#增加一条pgpool家目录
export PGPOOL_HOME=/opt/pgpool-II
#修改原来的PATH变量，增加pgpool命令路径
export PATH=$PGHOME/bin:$PATH:$HOME/bin:$PGPOOL_HOME/bin

#生效
source /etc/profile

2.3 配置修改

主库执行,目前部署的pgpool-II单点配置

2.3.1 pool_hba.conf配置

#从配置示例中拷贝出一份配置文件来
cp $PGPOOL_HOME/etc/pool_hba.conf.sample $PGPOOL_HOME/etc/pool_hba.conf
#增加以下内容
vi $PGPOOL_HOME/etc/pool_hba.conf
host    all         all         0.0.0.0/0                md5

2.3.2 pcp.conf和pool_passwd配置

p.conf：此文件中设置的用户，为pcp在线远程管理Pgpool-II集群的密码,可以自定义

pool_passwd：此文件主要用户数据库登录验证和pgpool-II检测数据库用户密码验证，在pgpool.conf配置文件中涉及到的数据库检测用户的密码，建议不配置密码原字符串，这样是不安全的，默认pgpool-II启动后会从这个文件中读取密码。

#从配置示例中拷贝出一份配置文件来
cp $PGPOOL_HOME/etc/pcp.conf.sample $PGPOOL_HOME/etc/pcp.conf

创建pgpool监控管理用户(master节点执行)

#创建数据库用户
CREATE USER pgpool WITH PASSWORD '2lp7VaR9um6g';
#pg_monitor角色用于监控和管理pgpool-II或pgpool_status扩展
GRANT pg_monitor TO pgpool;
#查看系统用户
select rolname,rolpassword from pg_authid;
#or
select usename,passwd from pg_shadow;

#修改pool_passwd,将刚才sql查询的结果配置到文件中
vi $PGPOOL_HOME/etc/pool_passwd
postgres:md5c417d26d7022287f345d0903baa37286
repmgr:md58ea99ab1ec3bd8d8a6162df6c8e1ddcd
pgpool:md5d3b87fa018bc768d044047b4b94cce15

#创建pgpool用户，此系统用户不是必须要创建的用户
useradd pgpool
echo "2lp7VaR9um6g" |passwd --stdin pgpool


#用pg_md5生产postgres密码的md5值，该用户pcp远程管理用户，必须要创建
pg_md5 -u pgpool 2lp7VaR9um6g
cb6af8d1f5fb5d36561db479777b1e7d
#追加到pcp.conf文件中
echo "pgpool:cb6af8d1f5fb5d36561db479777b1e7d" > $PGPOOL_HOME/etc/pcp.conf
#查看文件内容
cat $PGPOOL_HOME/etc/pcp.conf
pgpool:cb6af8d1f5fb5d36561db479777b1e7d
  

2.3.3 pgpool.conf配置

#从配置示例中拷贝出一份配置文件来
cp $PGPOOL_HOME/etc/pgpool.conf.sample $PGPOOL_HOME/etc/pgpool.conf

根据自己的配置进行相应的修改

#CONNECTIONS

listen_addresses = '*'
port = 9999
socket_dir = '/opt/pgpool-II'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/opt/pgpool-II'

# - Backend Connection Settings -
#DISALLOW_TO_FAILOVER:不允许故障切换
#ALLOW_TO_FAILOVER：允许故障切换
backend_hostname0 = '192.168.16.220' #主库hosts
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/opt/pg/pgdata' #数据库位置
backend_flag0 = 'ALLOW_TO_FAILOVER'
#
backend_hostname1 = '192.168.16.221' #从库1hosts
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/opt/pg/pgdata' #数据库位置
backend_flag1 = 'ALLOW_TO_FAILOVER'
#
backend_hostname2 = '192.168.16.222' #从库2hosts
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/opt/pg/pgdata' #数据库位置
backend_flag2 = 'ALLOW_TO_FAILOVER'

# # - Authentication -
#
enable_pool_hba = on
pool_passwd = 'pool_passwd'
#
# # FILE LOCATIONS
#
pid_file_name = '/opt/pgpool-II/pgpool.pid' ##pid文件位置
logdir = '/opt/pg/pglog' ##pool_status文件存放位置
#
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
#
sr_check_period = 5
sr_check_user = 'pgpool' ##主库创建的用户
sr_check_password = '2lp7VaR9um6g' ##密码，生产环境建议不指定明文，默认会从pool_passwd读取
sr_check_database = 'postgres'
#
# # HEALTH CHECK 健康检查
#
health_check_period = 10
health_check_timeout = 20
health_check_user = 'pgpool'  ##主库创建的用户
health_check_password = '2lp7VaR9um6g'  ##密码，生产环境建议不指定明文，默认会从pool_passwd读取
health_check_database = 'postgres'  ##检查的库
#当允许故障切换时，需要此参数
failover_command = '/opt/pg/failover_stream.sh %d %H /tmp/trigger_file0' 

以上故障切换脚本内容参考failover_stream.sh

说明：此脚本的高可用策略是pgpool来检测并且故障切换，这个故障切换是在pgpool节点池中的对主从关系的重新分配，真正的数据库的故障切换是repmgr来执行的。

#!/bin/bash
# Failover command for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
#
# If standby goes down, do nothing. If primary goes down, create a
# trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.
 
failed_node=$1
new_master=$2
trigger_file=$3
 
# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
        exit 0;
fi
 
# Create the trigger file.
/usr/bin/ssh -T $new_master /bin/touch $trigger_file
 
exit 0;

2.4 pgpool启动和停止

#启动 
pgpool -n -D > /opt/pg/pglog/pgpool.log 2>&1 &
#关闭 
pgpool -m fast stop

2.5 查看节点信息

psql -h 192.168.16.220 -p 9999 -U postgres -d postgres

show pool_nodes;

2.6 故障切换验证

2.6.1 模拟主数据库宕机

su - postgres
pg_ctl stop -D $PGDATA

2.6.2 备库查看集群状态

su - postgres -c "/opt/pg/pgsql/12.15/bin/repmgr -f /etc/repmgr.conf cluster show"

2.6.3 pgpool查看日志故障切换

tail -fn500 /opt/pg/pglog/pgpool.log

说明：

1）在pgpool-II检测到192.168.16.220节点数据库宕机

2）开启执行了故障切换脚本，触发pgpool去获取新的主库

3）发现了新的主库，设置了新的主库

4）切换完成，关闭了192.168.16.220数据库状态为down

2024-01-17 16:05:53: pid 10126: DETAIL:  postmaster on DB node 0 was shutdown by administrative command
2024-01-17 16:05:53: pid 10126: LOG:  received degenerate backend request for node_id: 0 from pid [10126]
2024-01-17 16:05:53: pid 10126: LOG:  signal_user1_to_parent_with_reason(0)
2024-01-17 16:05:53: pid 10093: LOG:  Pgpool-II parent process received SIGUSR1
2024-01-17 16:05:53: pid 10093: LOG:  Pgpool-II parent process has received failover request
2024-01-17 16:05:53: pid 10093: LOG:  starting degeneration. shutdown host 192.168.16.220(5432)
2024-01-17 16:05:53: pid 10093: LOG:  Restart all children
2024-01-17 16:05:53: pid 10093: LOG:  execute command: /opt/pg/failover_stream.sh 0 192.168.16.221 /tmp/trigger_file0
2024-01-17 16:05:53: pid 10093: LOG:  find_primary_node_repeatedly: waiting for finding a primary node
2024-01-17 16:05:53: pid 10093: LOG:  find_primary_node: standby node is 1
2024-01-17 16:05:53: pid 10093: LOG:  find_primary_node: standby node is 2
2024-01-17 16:05:54: pid 10093: LOG:  find_primary_node: standby node is 1

...

2024-01-17 16:06:44: pid 10093: LOG:  find_primary_node: standby node is 1
2024-01-17 16:06:44: pid 10093: LOG:  find_primary_node: standby node is 2
2024-01-17 16:06:45: pid 10093: LOG:  find_primary_node: primary node is 1
2024-01-17 16:06:45: pid 10093: LOG:  find_primary_node: standby node is 2
2024-01-17 16:06:45: pid 10093: LOG:  failover: set new primary node: 1
2024-01-17 16:06:45: pid 10093: LOG:  failover: set new main node: 1
failover done. shutdown host 192.168.16.220(5432)2024-01-17 16:06:45: pid 10129: LOG:  worker process received restart request
2024-01-17 16:06:45: pid 10093: LOG:  failover done. shutdown host 192.168.16.220(5432)
2024-01-17 16:06:46: pid 10128: LOG:  restart request received in pcp child process
2024-01-17 16:06:46: pid 10093: LOG:  PCP child 10128 exits with status 0 in failover()
2024-01-17 16:06:46: pid 10093: LOG:  fork a new PCP child pid 10337 in failover()

2.6.4 登录数据库查看节点切换状态

psql -h 192.168.16.220 -p 9999 -U postgres -d postgres

show pool_nodes;

从最新的信息看源主库220已down,已从主库转换角色为备库，备库221已从原先备库切换为主库，故障切换完成

2.7 数据库修复后重新加入集群

2.7.1 master主库修复后重启加入集群

#当master主库修复后，加入到集群中作为备库
#删除pg的数据
rm -rf /opt/pg/pgdata/*
#执行同步主库的数据
su - postgres -c "/opt/pg/pgsql/12.15/bin/repmgr -h 192.168.16.221 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone"
#启动slave2节点数据库
su - postgres
pg_ctl start -w -D $PGDATA -l /opt/pg/pglog/startup.log
#master节点，将master数据库注册到集群，由于之前创建过，需要加--force进行覆盖
su - postgres -c "/opt/pg/pgsql/12.15/bin/repmgr -f /etc/repmgr.conf standby register --force"
#查看最新集群状态
su - postgres -c "/opt/pg/pgsql/12.15/bin/repmgr -f /etc/repmgr.conf cluster show"

2.7.2 登录数据库查看集群状态

psql -h 192.168.16.220 -p 9999 -U postgres -d postgres

show pool_nodes;

2.7.3 重新更新节点状态

#用pcp中的管理命令将恢复数据库加入到pgpool节点中，更新pgpool节点池状态
#注意这个用户pgpool,是pcp.conf定义的用户和密码
pcp_attach_node -d -U pgpool -h 192.168.16.220 -p 9898 -n 0

2.7.4 登录数据库查看集群状态

psql -h 192.168.16.220 -p 9999 -U postgres -d postgres

show pool_nodes;

从图中信息看出：220节点状态在pgpool中已经更新，并且为备库

2.8 读写分离配置

在pgpool.conf中添加参数就可以实现

load_balance_mode = on

pgpool会自动的将select语句分到从库上，将insert等语句分到主库上

psql -h 192.168.16.229 -p 9999 -U postgres -d postgres
#创建库和表
create database test;
create table tb_1 (id int8,create_time timestamp(0) without time zone);
#插入数据
insert into tb_1 values (1,now());
insert into tb_1 values (2,now());
insert into tb_1 values (3,now());
#然后开始查询
select * from tb_1;

通过以下命令查看从库读取数据的负载查询次数

#一定要用监控用户查看
psql -h 192.168.16.229 -p 9999 -U pgpool -d postgres
show pool_nodes;

2.9 PGPOOL 高可用配置

2.9.1 看门狗简介

pgpool自身就可以实现高可用，无需依赖其他插件

看门狗的介绍： watchdog

"看门狗”是一个 pgpool-II 的子进程，用于添加高可用性功能。

看门狗功能

1) pgpool 服务的生命检测

看门狗监控 pgpool 服务的响应而不是进程。它通过被它监控的 pgpool 发送查询到 PostgreSQL，并检查响应情况。

看门狗还监控到从 pgpool 到前端服务器的连接（例如应用服务器）。从 pgpool 到前端服务器的连接作为 pgpool 的服务来监控。

2) 看门狗进程相互监控

看门狗进程交换被监控服务器的信息用来保证信息是最新的，并允许看门狗进程相互监控。

3) 在某些故障检测中交换活跃/备用状态

当一个 pgpool 的故障被检测到，看门狗通知其他的看门狗这个消息。看门狗在旧的活跃 pgpool 发生故障后通过投票确定新的活跃 pgpool 并更新活跃/备用状态。

4)在服务器切换的时候实现自动虚拟 IP 地址分配

当一个备用 pgpool 服务器提升为活跃的，新的活跃服务器启动虚拟 IP 接口。也就是，之前的活跃服务器停用虚拟 IP 接口。这确保活动的 pgpool 使用相同的 IP 地址，即使在发生服务器切换的时候。

5) 在恢复的时候自动注册服务器为备用服务器

当失效的服务器恢复或者新的服务器连接上来，看门狗进程通知其他的看门狗进程关于新服务器的信息， 看门狗进程在活跃服务器和其他服务器上接收这些信息。然后，新连接上的服务器注册为备用节点。

2.9.2 看门狗的启停

看门狗进程由 pgpool-II 自动启动/停止，也就是说，没有单独的命令来启动/停止它。但是，pgpool-II 启动时必须拥有管理员权限（root）， 因为看门狗进程需要控制虚拟 IP 接口。

在等待到所有的 pgpool 启动后，生命监测将启动。所以在启动pgpool时使用root用户启动。

2.9.3 看门狗的配置

以下为上一节内容的部署架构，上一节只实现了流复制和数据库故障自动切换，但是读写分离且高可用部署将延续上一节内容继续部署,这里仅作演示，如果是生产环境建议pgpool-II要部署基数3，5等节点，会更加有利于看门狗的选举，偶数节点会容易导致脑裂的发生。

节点说明	节点配置	节点地址	节点部署
master	CentOS7.9 4c/8G	192.168.16.220	Postgresql 、repmgr、pgpool-II
slave1	CentOS7.9 4c/8G	192.168.16.221	Postgresql 、repmgr、pgpool-II
slave2	CentOS7.9 4c/8G	192.168.16.222	Postgresql 、repmgr
witness	CentOS7.9 4c/8G	192.168.16.223	Postgresql 、repmgr
VIP		192.168.16.229

配置文件名称也是pgpool.conf,只是在此基本配置基础上新增看门狗的配置

官方配置说明：Watchdog

我这里的配置是两个节点高可用配置，强烈建议如果数据库是1主+2从架构，pgpoo-II也采用三节点部署方式。

2.9.3.1 配置详解

#常规配置
listen_addresses = #pgpool监听的地址
port = #pgpool监听端口
socket_dir = #pgpool socket建立连接的文件目录
pcp_listen_addresses = #pcp监听的地址
pcp_port = #pcp监听的端口
pcp_socket_dir = #pcp socket建立连接的文件目录
#后端数据库配置，如果有多个库（这里只给出两个示例配置），需要在key的后面数字递增，比如:backend_hostname0,backend_hostname1,backend_hostname3
backend_hostname0 = #库1的地址配置
backend_port0 = #库1的端口
backend_weight0 = #库1的复载均衡比率
backend_data_directory0 = #库1的数据库位置
backend_flag0 = #控制后端数据库的行为策略，比如数据库1宕机后是否发生切换或者不发生切换等等'ALLOW_TO_FAILOVER'
backend_hostname1 = #库2的地址配置
backend_port1 = #库2的端口
backend_weight1 = #库2的复载均衡比率
backend_data_directory1 = #库2的数据库位置
backend_flag1 = #控制后端数据库的行为策略，比如数据库2宕机后是否发生切换或者不发生切换等等'ALLOW_TO_FAILOVER'
#认证配置
enable_pool_hba = #如为on,Pgpool-II将使用 pool_hba.conf进行客户端身份验证
pool_passwd = #用于身份验证的密码文件的路径和名称，默认在家目录的etc下
#启动文件路径配置
pid_file_name = #pid文件位置
logdir = #存放pgpool_status文件的路径
logging_collector = #启动日志收集器
log_directory = #日志存放路径
log_filename = #日志文件名称
log_rotation_age = #按照时间切割，1d单位为天
log_rotation_size = #按照大小切割
#读写分离模式配置
load_balance_mode = #复制均衡模式如果开启，当查询时会轮询进行分发
ignore_leading_white_space = #忽略执行sql前面的空格
#流复制模式检测
sr_check_period = #每间隔多长时间检测一次
sr_check_user = #检测使用的数据库用户
sr_check_password = #检测使用的用户密码，如果没有填写，优先会从pool_passwd中查找
sr_check_database = #指定检测的库
#pgpool健康检测
health_check_period = #每间隔多长时间检测一次
health_check_timeout = #检测的超时时间
health_check_user = #检测使用的数据库用户
health_check_password = #检测使用的用户密码，如果没有填写，默认会从pool_passwd中查找
health_check_database = #指定检测的库
#当允许故障切换时，需要此参数
failover_command = #配置故障切换的执行的脚本
#基本配置
use_watchdog = #如果为on，启动watchdog
#pgpool节点配置，如果有多个需要全部配置，key(int)做数字递增
hostname0 = #第一个pgpool节点的hosts或ip                                
wd_port0 = #看门狗的端口，默认9000   
pgpool_port0 = #指定pgpool本节点端口，默认为9999   
hostname1 = #第二个pgpool节点的hosts或ip                                
wd_port1 = #看门狗的端口，默认9000   
pgpool_port1 = #指定pgpool本节点端口，默认为9999  
delegate_IP = #设置的vip 
#看门狗成员管理
enable_consensus_with_half_votes = #此参数仅仅限于看门狗集群个数是偶数（2,4,6,8）时开启，默认off
wd_remove_shutdown_nodes = #当节点宕机后自动从集群删除,默认关闭
wd_lost_node_removal_timeout = #当节点宕机后从集群删除的超时时间
wd_no_show_node_removal_timeout = #看门狗集群初始化时候，各节点加入超时时间
#虚拟ip执行命令配置
if_cmd_path = #执行网卡命令父路径                                   
if_up_cmd = #对网卡操作的命令，网卡名根据自己的修改                              
if_down_cmd = #对网卡操作的命令     
#看门狗心跳检测配置
wd_lifecheck_method = #指定看门狗检测方法
wd_monitoring_interfaces_list = #指定看门狗检测的设备名称
wd_interval = #看门狗检测pgpool的间隔时间，默认10s
wd_priority = #看门狗优先级，数据越高，选举为主看门狗的优先级越高
wd_ipc_socket_dir = #指定socket套接字目录
#这里需要说明，如果有多个看门狗节点需要全部都配置，key(int)做数字递增
heartbeat_hostname0 = #第一个看门狗心跳主机信息                    
heartbeat_port0 = #第一个看门狗心跳端口信息 
heartbeat_device0 = #第一个看门狗心跳检测设备
heartbeat_hostname1 = #第二个看门狗心跳主机信息
heartbeat_port1 = #第二个看门狗心跳端口信息 
heartbeat_device1 = #第二个看门狗心跳检测设备
wd_heartbeat_keepalive = #看门狗之间心跳检测间隔时间          
wd_heartbeat_deadtime = #看门狗宕机后心跳信号的最长检测时间    

2.9.3.2 配置实践

pgpool-master配置文件：192.168.16.220

#CONNECTIONS
listen_addresses = '*'
port = 9999
socket_dir = '/opt/pgpool-II'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/opt/pgpool-II'

#pg数据库的节点配置
#DISALLOW_TO_FAILOVER:不允许故障切换
#ALLOW_TO_FAILOVER：允许故障切换
#主库
backend_hostname0 = '192.168.16.220' #主库hosts
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/opt/pg/pgdata' #数据库位置
backend_flag0 = 'ALLOW_TO_FAILOVER'
#备库1
backend_hostname1 = '192.168.16.221' #从库1hosts
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/opt/pg/pgdata' #数据库位置
backend_flag1 = 'ALLOW_TO_FAILOVER'
#备库2
backend_hostname2 = '192.168.16.222' #从库2hosts
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/opt/pg/pgdata' #数据库位置
backend_flag2 = 'ALLOW_TO_FAILOVER'

#认证机制配置
#如为on,Pgpool-II将使用 pool_hba.conf进行客户端身份验证
enable_pool_hba = on
#用于身份验证的密码文件的路径和名称，默认在家目录的etc下
pool_passwd = 'pool_passwd'

#启动文件路径配置
#pid文件位置
pid_file_name = '/opt/pgpool-II/pgpool.pid'
#存放pgpool_status文件的路径
logdir = '/opt/pg/pglog'
#启动日志收集器
logging_collector = on
#日志存放路径
log_directory = '/opt/pg/pglog'
#日志文件名称
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
#按照时间切割，单位天
log_rotation_age = 1d
#按照大小切割
log_rotation_size = 10MB

#读写分离模式配置
#复制均衡模式如果开启，当查询时会轮询进行分发
load_balance_mode = on
#忽略执行sql前面的空格
ignore_leading_white_space = on


sr_check_period = 5
sr_check_user = 'pgpool' ##主库创建的用户
sr_check_password = '2lp7VaR9um6g' ##密码
sr_check_database = 'postgres'
#
# # HEALTH CHECK 健康检查
#
health_check_period = 10
health_check_timeout = 20
health_check_user = 'pgpool'  ##主库创建的用户
health_check_password = '2lp7VaR9um6g'  ##密码
health_check_database = 'postgres'  ##检查的库
#当允许故障切换时，需要此参数
failover_command = '/opt/pg/failover_stream.sh %d %H /tmp/trigger_file0' 
#启动watchdog
use_watchdog = on
#第一个pgpool节点的hosts或ip
hostname0 = '192.168.16.220'
#看门狗的端口，默认9000                                   
wd_port0 = 9000
#指定pgpool本节点端口，默认为9999
pgpool_port0 = 9999

#第二个pgpool节点的hosts或ip
hostname1 = '192.168.16.221'
#第二个pgpool节点watchdog端口
wd_port1 = 9000
#第二个pgpool节点端口
pgpool_port1 = 9999
#设置的vip                                    
delegate_IP = '192.168.16.229'
 
#看门狗成员管理
#此参数仅仅限于看门狗集群个数是偶数时开启，默认off
enable_consensus_with_half_votes = on
#当节点宕机后自动从集群删除,默认关闭
wd_remove_shutdown_nodes = on
#当节点宕机后从集群删除的超时时间
wd_lost_node_removal_timeout = 60
#看门狗集群初始化时候，各节点加入超时时间
wd_no_show_node_removal_timeout = 60

                                  
if_cmd_path = '/sbin'
#对网卡操作的命令，网卡名根据自己的修改                                   
if_up_cmd = '/usr/bin/sudo /sbin/ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
#对网卡操作的命令                                   
if_down_cmd = '/usr/bin/sudo /sbin/ifconfig eth0:0 down'
#指定看门狗检测方法
wd_lifecheck_method = 'heartbeat'
#指定看门狗检测的设备名称
wd_monitoring_interfaces_list = 'eth0'
#看门狗检测pgpool的间隔时间，默认10s
wd_interval = 10
#看门狗优先级，数据越高，选举为主看门狗的优先级越高
wd_priority = 10
#指定socket套接字目录
wd_ipc_socket_dir = '/var/run'

#第一个看门狗主机信息
heartbeat_hostname0 = '192.168.16.220'                          
heartbeat_port0 = 9694     
heartbeat_device0 = 'eth0'
#第二个看门狗主机信息
heartbeat_hostname1 = '192.168.16.221' 
heartbeat_port1 = 9694 
heartbeat_device1 = 'eth0'

#看门狗之间心跳检测间隔时间                                   
wd_heartbeat_keepalive = 2
#看门狗宕机后心跳信号的最长检测时间                                  
wd_heartbeat_deadtime = 30

创建节点id文件

echo "0" > /opt/pgpool-II/etc/pgpool_node_id

pgpool-standby配置文件：192.168.16.221

这里需要说明：由于备节点之前没有安装pgpool需要结合2.2~2.3.2部分安装一下pgpool

listen_addresses = '*'
port = 9999
socket_dir = '/opt/pgpool-II'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/opt/pgpool-II'

# - Backend Connection Settings -
backend_hostname0 = '192.168.16.220' #主库hosts
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/opt/pg/pgdata' #数据库位置
backend_flag0 = 'ALLOW_TO_FAILOVER'
#
backend_hostname1 = '192.168.16.221' #从库1hosts
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/opt/pg/pgdata' #数据库位置
backend_flag1 = 'ALLOW_TO_FAILOVER'
#
backend_hostname2 = '192.168.16.222' #从库2hosts
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/opt/pg/pgdata' #数据库位置
backend_flag2 = 'ALLOW_TO_FAILOVER'

# # - Authentication -

enable_pool_hba = on
pool_passwd = 'pool_passwd'

# # FILE LOCATIONS
pid_file_name = '/opt/pgpool-II/pgpool.pid' ##pid文件位置
logdir = '/opt/pg/pglog'
logging_collector = on
log_directory = '/opt/pg/pglog' ##日志位置
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 10MB
#
load_balance_mode = on
ignore_leading_white_space = on
#
sr_check_period = 5
sr_check_user = 'pgpool' ##主库创建的用户
sr_check_password = '2lp7VaR9um6g' ##密码
sr_check_database = 'postgres'
#
# # HEALTH CHECK 健康检查
#
health_check_period = 10
health_check_timeout = 20
health_check_user = 'pgpool'  ##主库创建的用户
health_check_password = '2lp7VaR9um6g'  ##密码
health_check_database = 'postgres'  ##检查的库
#当允许故障切换时，需要此参数
failover_command = '/opt/pg/failover_stream.sh %d %H /tmp/trigger_file0' 
#启动watchdog
use_watchdog = on
#第一个pgpool节点的hosts或ip
hostname0 = '192.168.16.220'
#看门狗的端口，默认9000                                   
wd_port0 = 9000
#指定pgpool本节点端口，默认为9999
pgpool_port0 = 9999

#第二个pgpool节点的hosts或ip
hostname1 = '192.168.16.221'
#第二个pgpool节点watchdog端口
wd_port1 = 9000
#第二个pgpool节点端口
pgpool_port1 = 9999
#设置的vip                                    
delegate_IP = '192.168.16.229'
 
#看门狗成员管理
enable_consensus_with_half_votes = on
#当节点宕机后自动从集群删除
wd_remove_shutdown_nodes = on
#当节点宕机后,从集群删除的超时时间
wd_lost_node_removal_timeout = 60
#看门狗集群初始化时候，各节点加入超时时间
wd_no_show_node_removal_timeout = 60

#虚拟ip的本地网卡操作配置
if_cmd_path = '/sbin'
#对网卡操作的命令，网卡名根据自己的修改                                   
if_up_cmd = '/usr/bin/sudo /sbin/ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
#对网卡操作的命令                                   
if_down_cmd = '/usr/bin/sudo /sbin/ifconfig eth0:0 down'
#指定看门狗检测方法
wd_lifecheck_method = 'heartbeat'
#指定看门狗检测的设备名称
wd_monitoring_interfaces_list = 'eth0'
#看门狗检测pgpool间隔时间，默认10s
wd_interval = 10
#看门狗优先级，数据越高，选举为主看门狗的优先级越高
wd_priority = 5
#指定socket套接字目录
wd_ipc_socket_dir = '/var/run'

#第一个看门狗主机信息
heartbeat_hostname0 = '192.168.16.220'                          
heartbeat_port0 = 9694     
heartbeat_device0 = 'eth0'
#第二个看门狗主机信息
heartbeat_hostname1 = '192.168.16.221' 
heartbeat_port1 = 9694 
heartbeat_device1 = 'eth0'

#看门狗之间心跳检测间隔时间                                   
wd_heartbeat_keepalive = 2
# 心跳信号的死区时间间隔                                  
wd_heartbeat_deadtime = 30

创建节点id文件

echo "1" > /opt/pgpool-II/etc/pgpool_node_id

说明：如果有多个节点需要依次来填写，不同节点不能相同，这个数字的值要与本节点pgpool.conf中的hostname(int)中的数字相同，比如当前节点时hostname0='192.168.16.220'，那么pgpool_node_id就是0

2.9.3.3 pgpool的启动和关闭

两台pgpool节点配置

#启动
#-n 打印日志
#-d 输出debug日志
#-D 如果pgpool_status文件存在，启动时会忽略这个原先的状态
pgpool -n -d -D > /opt/pg/pglog/pgpool-start.log 2>&1 &

#关闭 
pgpool -m fast stop

2.9.3.4 虚拟IP验证

 ip a

2.9.3.5 通过VIP登录数据库

#用pg库监控用户pgpool登录数据库
psql -h 192.168.16.229 -p 9999 -U pgpool -d postgres
#查看节点信息
show pool_nodes;

通过pcp管理查看成员节点信息

pcp_watchdog_info -p 9898 -h 192.168.16.229 -U pgpool
输入pgpool密码：

2.9.4 看门狗的故障切换

2.9.4.1 模拟主看门狗宕机

#在主库关闭pgpool
pgpool -m fast stop

1)221节点升为主看门狗

2）220节点关闭了虚拟IP

2.9.4.2 VIP漂移

#在备库查询，vip已经漂移完成
ip a

2.10 pgpool-II 启动报错

2.10.1 开启watchdog，启动pgpool报错

报错内容：

2024-01-18 14:37:34: pid 12002: DEBUG:  read pgpool node id file /opt/pgpool-II/etc/pgpool_node_id
2024-01-18 14:37:34: pid 12002: DETAIL:  pgpool node id: 0
2024-01-18 14:37:34: pid 12002: DEBUG:  initializing pool configuration
2024-01-18 14:37:34: pid 12002: DETAIL:  num_backends: 3 total_weight: 3.000000
2024-01-18 14:37:34: pid 12002: DEBUG:  initializing pool configuration
2024-01-18 14:37:34: pid 12002: DETAIL:  backend 0 weight: 715827882.333333 flag: 0000
2024-01-18 14:37:34: pid 12002: DEBUG:  initializing pool configuration
2024-01-18 14:37:34: pid 12002: DETAIL:  backend 1 weight: 715827882.333333 flag: 0000
2024-01-18 14:37:34: pid 12002: DEBUG:  initializing pool configuration
2024-01-18 14:37:34: pid 12002: DETAIL:  backend 2 weight: 715827882.333333 flag: 0000
2024-01-18 14:37:34: pid 12002: WARNING:  checking setuid bit of if_up_cmd
2024-01-18 14:37:34: pid 12002: DETAIL:  ifup[/usr/sbin/ifconfig] doesn't have setuid bit
2024-01-18 14:37:34: pid 12002: WARNING:  checking setuid bit of if_down_cmd
2024-01-18 14:37:34: pid 12002: DETAIL:  ifdown[/usr/sbin/ifconfig] doesn't have setuid bit
2024-01-18 14:37:34: pid 12002: FATAL:  has_setuid_bit: command '/usr/sbin//usr/bin/sudo' not found

报错分析：ifdown[/usr/sbin/ifconfig] doesn't have setuid bit：经过网上查询发现该命令缺少沾滞位的关系

解决方案：

cd /sbin

chmod +s ifup
chmod +s ifdown
chmod +s ifconfig