安装
下载
https://pgpool.net/mediawiki/index.php/Downloads
编译安装
tar -zxvf pgpool-II-4.4.4.tar.gz
cd pgpool-II-4.4.4.tar.gz
./configure --prefix=/usr/local/pgpool --with-pgsql=/usr/local/postgres/
make && make install
#pgpool-regclass 插件安装
cd /root/pgpool-II-4.4.4/src/sql/pgpool-regclass
make && make install
psql -h /data/pgdata/tmp -p 4567 -U postgres -f pgpool-regclass.sql template1
# 下面的没有执行测试
ln -s /root/pgpool-II-4.4.4//src/sql/pgpool-regclass/* /usr/local/postgres/lib/
# pgpool-recovery插件安装
cd /root/pgpool-II-4.4.4/src/sql/pgpool-recovery
make && make install
psql -h /data/pgdata/tmp -p 4567 -U postgres -f pgpool-recovery.sql template1
cp /usr/local/pgpool/etc/recovery_1st_stage.sample /data/pgdata/recovery_1st_stage
chown -R postgres /usr/local/pgpool
# 链接
ln -s /usr/local/pgpool/bin/* /usr/local/bin
配置
创建账号
# 创建超级用户、pcp管理命令用户、流复制检查用户
CREATE ROLE pgpool WITH LOGIN;
CREATE ROLE u_standby WITH REPLICATION LOGIN;
ALTER USER postgres ENCRYPTED PASSWORD 'Pg_2023zys!';
alter user pgpool encrypted password 'Pg_2023zys!';
alter user u_standby encrypted password 'Pg_2023zys!';
GRANT pg_monitor TO pgpool;
# 在所有postgresql节点上,在postgres的用户目录下创建该文件
cat << EOF > ~/.pgpass
10.10.10.11:4567:*:postgres:Pg_2023zys!
10.10.10.11:4567:*:repuser:Pg_2023zys!
10.10.10.11:4567:*:pgpool:Pg_2023zys!
10.10.10.12:4567:*:postgres:Pg_2023zys!
10.10.10.12:4567:*:pgpool:Pg_2023zys!
10.10.10.12:4567:*:repuser:Pg_2023zys!
localhost:4567:*:postgres:Pg_2023zys!
localhost:4567:*:pgpool:Pg_2023zys!
localhost:4567:*:repuser:Pg_2023zys!
EOF
配置.pcppass
su - postgres
vi .pcppass
10.10.10.12:5432:replication:u_standby:Pg_2023zys!
10.10.10.11:9999:postgres:pgpool:Pg_2023zys!
10.10.10.11:9999:postgres:postgres:Pg_2023zys!
10.10.10.11:5432:postgres:postgres:Pg_2023zys!
10.10.10.12:5432:postgres:postgres:Pg_2023zys!
localhost:9898:pgpool:Pg_2023zys!
echo 'pgpool:'`pg_md5 -u=pgpool Pg_2023zys\!` >> /usr/local/pgpool/etc/pcp.conf
配置 pool_hba.conf文件
cat << EOF > /usr/local/pgpool/etc/pool_hba.conf
host all all 0.0.0.0/0 md5
host all pgpool 0.0.0.0/0 trust
local all all trust
EOF
配置节点号
#10.10.10.11
echo "0" > /usr/local/pgpool/etc/pgpool_node_id
#10.10.10.12
echo "1" > /usr/local/pgpool/etc/pgpool_node_id
配置pgpool.conf
# 后端集群模式:使用流复制
backend_clustering_mode = 'streaming_replication'
# - pgpool 连接设置 -
# 监听地址:监听所有地址
listen_addresses = '*'
# 监听端口:9999
port = 9999
# socket 目录
#socket_dir = '/usr/local/pgpool/tmp'
# - pcp 连接设置 -
# pcp 监听地址:监听所有地址
pcp_listen_addresses = '*'
# pcp 监听端口:9898
pcp_port = 9898
# pcp socket 目录
pcp_socket_dir = '/usr/local/pgpool/tmp'
# 流复制检查
sr_check_user = 'pgpool'
sr_check_password = 'Pg_2023zys!'
sr_check_database = 'postgres'
# - 健康检查设置 -
health_check_period = 5 # 检查健康状态的频率(秒)
health_check_timeout = 30 # 健康检查超时时间(秒)
health_check_user = 'pgpool'
health_check_password = 'Pg_2023zys!'
health_check_max_retries = 3 # 最大重试次数
search_primary_node_timeout = 10min # 查找主节点的超时时间
# - 后端连接设置 -
backend_hostname0 = '10.10.10.11' # 第一个后端数据库的主机名或IP地址
backend_port0 = 4567 # 第一个后端数据库的端口号
backend_weight0 = 1 # 第一个后端数据库的权重
backend_data_directory0 = '/data/pgdata' # 第一个后端数据库的数据目录
backend_flag0 = 'ALLOW_TO_FAILOVER' # 指示允许故障切换
#backend_application_name0 = 'pg1' # (可选)后端应用程序名称
backend_hostname1 = '10.10.10.12' # 第二个后端数据库的主机名或IP地址
backend_port1 = 4567 # 第二个后端数据库的端口号
backend_weight1 = 1 # 第二个后端数据库的权重
backend_data_directory1 = '/data/pgdata' # 第二个后端数据库的数据目录
backend_flag1 = 'ALLOW_TO_FAILOVER' # 指示允许故障切换
#backend_application_name1 = 'pg2' # (可选)后端应用程序名称
# - 故障切换配置 -
failover_command = '/usr/local/pgpool/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S' # 自定义故障切换命令
follow_primary_command = '/usr/local/pgpool/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R' # 自定义切换到主服务器的命令
# - 在线恢复配置 -
recovery_user = 'postgres' # 用于在线恢复的 PostgreSQL 用户
recovery_password = 'Pg_2023zys!'
recovery_1st_stage_command = 'recovery_1st_stage' # 第一阶段恢复命令
# - 客户端认证配置 -
enable_pool_hba = on # 启用客户端认证
pool_passwd = '/usr/local/pgpool/etc/pool_passwd' # 存储用户密码的文件
# - 看门狗配置 -
use_watchdog = on # 启用看门狗
#delegate_IP = '10.221.232.185' # 代理 IP 地址
#if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev bond0 label bond0:1' # 网络接口启动命令
#if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev bond0' # 网络接口关闭命令
#arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I bond0' # 用于发送 ARP 请求的命令
hostname0 = '10.10.10.11' # 看门狗节点 0 的主机名或 IP 地址
wd_port0 = 9000 # 看门狗节点 0 的端口号
pgpool_port0 = 9999 # 看门狗节点 0 上的 pgpool 端口号
hostname1 = '10.10.10.12' # 看门狗节点 1 的主机名或 IP 地址
wd_port1 = 9000 # 看门狗节点 1 的端口号
pgpool_port1 = 9999 # 看门狗节点 1 上的 pgpool 端口号
wd_lifecheck_method = 'heartbeat' # 使用心跳检测进行生存检查
wd_interval = 10 # 看门狗节点间的心跳检测间隔(秒)
heartbeat_hostname0 = '10.10.10.11' # 心跳检测节点 0 的主机名或 IP 地址
heartbeat_port0 = 9694 # 心跳检测节点 0 的端口号
heartbeat_device0 = '' # 心跳检测节点 0 的网络设备(如果需要)
heartbeat_hostname1 = '10.10.10.12' # 心跳检测节点 1 的主机名或 IP 地址
heartbeat_port1 = 9694 # 心跳检测节点 1 的端口号
heartbeat_device1 = '' # 心跳检测节点 1 的网络设备(如果需要)
wd_heartbeat_keepalive = 2 # 心跳检测的保持存活参数
wd_heartbeat_deadtime = 30 # 心跳检测的死亡时间参数
#wd_escalation_command = '/usr/local/pgpool/etc/escalation.sh' # 故障切换升级命令
other_pgpool_hostname0 = '10.10.10.12'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
# - 日志设置 -
log_destination = 'stderr'
logging_collector = on
log_directory = '/usr/local/pgpool/'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d # 日志轮换的时间间隔
log_rotation_size = 10MB # 日志轮换的文件大小上限
pid_file_name = '/usr/local/pgpool/pgp_9999.pid' # 存储 PID 的文件名
# 负载均衡模式:关闭
load_balance_mode = off
配置脚本
cp -p /usr/local/pgpool/etc/failover.sh{.sample,}
cp -p /usr/local/pgpool/etc/follow_primary.sh{.sample,}
chown postgres:postgres /usr/local/pgpool/etc/{failover.sh,follow_primary.sh}
chmod +x /usr/local/pgpool/etc/{failover.sh,follow_primary.sh}
##**把recovery_First_stage和pgpool_remote_start脚本放在$PG_DATA目录上**
cp -p /db/pgpool/etc/recovery_1st_stage.sample $PG_5432/recovery_1st_stage
cp -p /db/pgpool/etc/pgpool_remote_start.sample $PG_5432/pgpool_remote_start
chown postgres:postgres $PG_5432/{recovery_1st_stage,pgpool_remote_start}
chmod +x $PG_5432/{recovery_1st_stage,pgpool_remote_start}
脚本说明
- failover.sh
此脚本由故障转移命令(failover_command)运行,这个脚本的主要功能是在PostgreSQL故障发生时执行故障转移。它通过SSH连接到新主节点,检测是否需要跳过故障转移,测试密码无效的SSH连接,处理备用节点宕机的情况,最后提升备用节点为主节点。
- follow_primary.sh
此脚本在failover_command之后运行,该脚本的主要功能是在PostgreSQL发生故障转移后,将备用节点与新主节点同步。脚本尝试使用pg_rewind,如果失败,则使用pg_basebackup。在同步过程中,还会创建和管理复制插槽,并根据PostgreSQL的版本使用不同的配置文件。最后,脚本将备用节点附加到Pgpool-II中。
- recovery_1st_stage
此脚本由 “recovery_1st_stage” 执行,用于恢复一个备用节点。例如在使用pcp_recovery_node
在线恢复节点时。该脚本的主要功能是通过 pg_basebackup 恢复一个备用节点。在执行过程中,脚本会创建和管理复制插槽,生成与 PostgreSQL 版本相关的恢复配置文件,并执行必要的清理工作。
- pgpool_remote_start
此脚本在recovery_1st_stage后执行,主要用于远程通过pg_ctl启动已经恢复的节点。
:::info
注意:
脚本内的参数基本都是需要修改的,需要安装安装环境进行修改。
:::
配置pool_passwd
pg_md5 --md5auth --username=pgpool "Pg_2023zys!"
pg_md5 --md5auth --username=postgres "Pg_2023zys!"
pg_md5 --md5auth --username=u_standby "Pg_2023zys!"
cat /usr/local/pgpool/etc/pool_passwd
在线恢复
在一个postgresql宕机后,可以通过pcp_recovery_node进行在线恢复,恢复步骤有2个脚本
- recovery_1st_stage:完成节点重新部署和复制
- pgpool_remote_start:启动新节点
##**把recovery_First_stage和pgpool_remote_start脚本放在$PG_DATA目录上**
cp -p /usr/local/pgpool/etc/recovery_1st_stage.sample /data/pgdata/recovery_1st_stage
cp -p /usr/local/pgpool/etc/pgpool_remote_start.sample /data/pgdata/pgpool_remote_start
chown postgres:postgres /data/pgdata/{recovery_1st_stage,pgpool_remote_start}
chmod +x /data/pgdata/{recovery_1st_stage,pgpool_remote_start}
脚本需要修改的地方
- recovery_1st_stage:
- PGHOME=/usr/local/postgres
- ARCHIVEDIR=/var/lib/pgsql/archivedir
- REPLUSER=repuser
- passfile=‘’/home/postgres/.pgpass’‘’
:::danger
⚠️注意:
必须创建/home/postgres/.pgpass文件用于在脚本中psql执行时可以不输入密码。
:::
- pgpool_remote_start
- PGHOME=/usr/local/postgres
- ARCHIVEDIR=/var/lib/pgsql/archivedir
- REPLUSER=repuser
- passfile=‘’/home/postgres/.pgpass’‘’
:::info
注意脚本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。
# 创建pg插件
su - postgres
psql template1 -c "CREATE EXTENSION pgpool_recovery"
psql postgres -c "CREATE EXTENSION pgpool_recovery"
#执行在线恢复
pcp_recovery_node -h 10.10.10.11 -p 9898 -U pgpool -n 0
:::info
[root@node1 pgpool]# pcp_recovery_node --help
pcp_recovery_node - 恢复节点
用法:
pcp_recovery_node [选项…] [节点ID]
选项:
-U, --username=NAME PCP认证的用户名
-h, --host=HOSTNAME pgpool-II主机
-p, --port=PORT PCP端口号
-w, --no-password 永不提示密码
-W, --password 强制提示密码(应该自动发生)
-n, --node-id=NODEID 后端节点的ID
-d, --debug 启用调试消息(可选)
-v, --verbose 输出详细消息
-?, --help 打印此帮助信息
:::
启停pgpool
启动
pg_confi
# 先查看参数是否对
pgpool
# -n:以前台方式启动 Pgpool-II,显示控制台输出。
如果需要指定配置文件等其他参数,可以使用以下命令:
pgpool -c /path/to/pgpool.conf
# -c <配置文件>:指定 Pgpool-II 的配置文件路径
RELOAD
pgpool reload
停止
pgpool -m smart stop
-m :指定关闭模式,可以选择 “smart”、“fast” 或 “immediate”,它们的含义如下:
- “smart”:在所有客户端断开连接后退出。
- “fast”:直接退出,采用适当的关闭方式。
- “immediate”:与 “fast” 模式相同,立即退出。
其他常见参数说明:
- -a <HBA_CONFIG_FILE>:指定 pool_hba.conf 配置文件的路径。
- -f <CONFIG_FILE>:指定 pgpool.conf 配置文件的路径。
- -F <PCP_CONFIG_FILE>:指定 pcp.conf 配置文件的路径。
- -n:以前台方式启动,显示控制台输出。
- -d:以调试模式启动。
- -C:清除查询缓存 oidmaps,当 memqcache_method 设置为 memcached 时使用。
- -x:启用各种断言检查,用于调试。
- -D:丢弃 pgpool_status 文件并不恢复先前的状态。
连接PGPOOL
# socket连接
psql -p9999 -U pgpool -d postgres
# 网络连接
psql -h10.10.10.11 -p9999 -U pgpool -d postgres
keepalived
通过keepalived对pgpool进行监测,替换watchdog,如果一个Pgpool节点失效,则将IP迁移到另一个存活的pgpool
keepalived在双节点时比watchdog好用,更合理,如果大于2个节点,推荐使用watchdog,因为watchdog是选举主节点,而keepalived则是通过权重配比实现的。
主(10.10.10.11)
keepalived.conf
vrrp_script chk_pgpool {
script "/usr/local/bin/chk_pgpool.sh"
interval 2
}
vrrp_instance VI_1 {
state BACKUP
interface bond1
virtual_router_id 84
nopreempt
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass Nmwg_2023
}
virtual_ipaddress {
10.221.232.184/24 dev bond1
}
virtual_ipaddress_excluded {
2409:8010:5af0:1101:10:845:0:79/96 dev bond1
}
track_script {
chk_pgpool
}
}
chk_pgpool.sh
#!/bin/bash
# 定义数据库连接参数
PGPOOL_HOST="10.10.10.11" # PgPool 主机名或IP地址
PGPOOL_PORT="9999" # PgPool 的监听端口
PGPOOL_USER="pgpool" # 连接数据库时使用的用户名
PGPOOL_PASSWORD="Pg_2023zys!" # 连接数据库时使用的密码
# 日志文件路径
LOG_FILE="/usr/local/pgpool/chk_pgpool.log"
# 检查日志文件是否存在,如果不存在则创建它
if [ ! -f "$LOG_FILE" ]; then
touch "$LOG_FILE"
fi
# 获取当前时间并将其写入日志文件
LOG_TIMESTAMP=$(date +'%Y-%m-%d %H:%M:%S')
echo "[$LOG_TIMESTAMP] - 开始进行 PgPool 健康检查..." >> "$LOG_FILE"
# 使用 psql 命令来尝试连接 PgPool 数据库
PGPASSWORD=$PGPOOL_PASSWORD psql -h "$PGPOOL_HOST" -p "$PGPOOL_PORT" -U "$PGPOOL_USER" -d postgres -c "SELECT 1;" >> "$LOG_FILE" 2>&1
# 检查 psql 命令的退出状态
if [ $? -eq 0 ]; then
# 连接成功,检查 PgPool 进程是否存在
pgrep "pgpool" > /dev/null
if [ $? -eq 0 ]; then
# PgPool 进程存在
echo "[$LOG_TIMESTAMP] - PgPool 健康检查成功。" >> "$LOG_FILE"
exit 0
else
# PgPool 进程不存在
echo "[$LOG_TIMESTAMP] - 未找到 PgPool 进程。" >> "$LOG_FILE"
exit 1
fi
else
# 连接失败
echo "[$LOG_TIMESTAMP] - PgPool 健康检查失败。连接错误。" >> "$LOG_FILE"
exit 1
fi
备(10.10.10.12)
keepalived.conf
vrrp_script chk_pgpool {
script "/usr/local/bin/chk_pgpool.sh"
interval 2
}
vrrp_instance VI_1 {
state BACKUP
interface bond1
virtual_router_id 84
priority 99
advert_int 1
authentication {
auth_type PASS
auth_pass Nmwg_2023
}
virtual_ipaddress {
10.10.10.100/24 dev bond1
}
virtual_ipaddress_excluded {
2409:8010:5af0:1101:10:845:0:79/96 dev bond1
}
track_script {
chk_pgpool
}
}
chk_pgpool.sh
#!/bin/bash
# 定义数据库连接参数
PGPOOL_HOST="10.10.10.12" # PgPool 主机名或IP地址
PGPOOL_PORT="9999" # PgPool 的监听端口
PGPOOL_USER="pgpool" # 连接数据库时使用的用户名
PGPOOL_PASSWORD="Pg_2023zys!" # 连接数据库时使用的密码
# 日志文件路径
LOG_FILE="/usr/local/pgpool/chk_pgpool.log"
# 检查日志文件是否存在,如果不存在则创建它
if [ ! -f "$LOG_FILE" ]; then
touch "$LOG_FILE"
fi
# 获取当前时间并将其写入日志文件
LOG_TIMESTAMP=$(date +'%Y-%m-%d %H:%M:%S')
echo "[$LOG_TIMESTAMP] - 开始进行 PgPool 健康检查..." >> "$LOG_FILE"
# 使用 psql 命令来尝试连接 PgPool 数据库
PGPASSWORD=$PGPOOL_PASSWORD psql -h "$PGPOOL_HOST" -p "$PGPOOL_PORT" -U "$PGPOOL_USER" -d postgres -c "SELECT 1;" >> "$LOG_FILE" 2>&1
# 检查 psql 命令的退出状态
if [ $? -eq 0 ]; then
# 连接成功,检查 PgPool 进程是否存在
pgrep "pgpool" > /dev/null
if [ $? -eq 0 ]; then
# PgPool 进程存在
echo "[$LOG_TIMESTAMP] - PgPool 健康检查成功。" >> "$LOG_FILE"
exit 0
else
# PgPool 进程不存在
echo "[$LOG_TIMESTAMP] - 未找到 PgPool 进程。" >> "$LOG_FILE"
exit 1
fi
else
# 连接失败
echo "[$LOG_TIMESTAMP] - PgPool 健康检查失败。连接错误。" >> "$LOG_FILE"
exit 1
fi