pgpool-II搭建集群，实现高可用与读写分离
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">pgpool-II简介</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">pgpool-II架构图</a></li><li><a href="#_lab2_0_1">pgpool-II进程简介</a></li><li><a href="#_lab2_0_2">看门狗进程详细介绍</a></li><li><a href="#_lab2_0_3">pgpool-II的工作模式</a></li></ul><li><a href="#_label1">1.环境规划</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_4">1.1 节点信息</a></li><li><a href="#_lab2_1_5">1.2PostgreSQL版本和配置</a></li><li><a href="#_lab2_1_6">1.3 Pgpool-II版本与配置</a></li><li><a href="#_lab2_1_7">1.4 相关脚本</a></li></ul><li><a href="#_label2">2.系统准备</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_8">2.1 安装依赖</a></li><li><a href="#_lab2_2_9">2.2 配置域名解析</a></li><li><a href="#_lab2_2_10">2.3 创建用户与目录</a></li><li><a href="#_lab2_2_11">2.4 配置互信</a></li><li><a href="#_lab2_2_12">2.5 系统优化</a></li></ul><li><a href="#_label3">3.软件安装</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_13">3.1 安装postgresql</a></li><li><a href="#_lab2_3_14">3.2 安装pgpool</a></li></ul><li><a href="#_label4">4.数据库主节点配置</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_15">4.1 初始化主节点</a></li><li><a href="#_lab2_4_16">4.2 编辑配置文件</a></li><li><a href="#_lab2_4_17">4.3 启动主节点</a></li><li><a href="#_lab2_4_18">4.4 创建用户</a></li><li><a href="#_lab2_4_19">4.5 配置访问控制文件</a></li></ul><li><a href="#_label5">5.pgpool配置</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_20">5.1 相关脚本配置</a></li><ul class="third_class_ul"><li><a href="#_label3_5_20_0">配置failover.sh</a></li><li><a href="#_label3_5_20_1">配置follow_primary.sh</a></li><li><a href="#_label3_5_20_2">配置recovery_1st_stage</a></li><li><a href="#_label3_5_20_3">配置pgpool_remote_start</a></li><li><a href="#_label3_5_20_4">配置escalation.sh</a></li></ul><li><a href="#_lab2_5_21">5.2 访问控制文件与密码验证文件</a></li><ul class="third_class_ul"><li><a href="#_label3_5_21_5">配置pool_hba.conf</a></li><li><a href="#_label3_5_21_6">配置pool_passwd文件</a></li><li><a href="#_label3_5_21_7">配置pcp.conf文件</a></li><li><a href="#_label3_5_21_8">配置pgpool_node_id文件</a></li><li><a href="#_label3_5_21_9">配置.pgpass文件</a></li><li><a href="#_label3_5_21_10">配置.pcppass 文件</a></li></ul><li><a href="#_lab2_5_22">5.3 编辑主配置文件</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label6">6. 集群启动</a></li><ul class="second_class_ul"><li><a href="#_lab2_6_23">6.1 启动pgpool</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_6_24">6.2 启动后端数据库从节点</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_6_25">6.3 查看集群状态</a></li><ul class="third_class_ul"></ul></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>pgpool-II简介</h2><p>https://www.pgpool.net/docs/45/en/html/ 官方文档地址</p>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>pgpool-II架构图</h3>
<p style="text-align:center"><img alt="pgpool-II实现对业务透明的高可用与读写分离_pgpool-II" src="https://img.jbzj.com/file_images/article/202504/202504190840311.png" width="570" /></p>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>pgpool-II进程简介</h3>
<ol start="1"><li>pcp进程：pcp是一个命令行的管理工具，用户可以使用此管理工具向pgpool-II发送管 理命令。 </li><li>pgpool-II父进程：pgpool-II父进程负责检查各个底层数据库的健康状态。 </li><li>pgpool-II子进程：负责接收用户发过来的SQL请求，然后再根据规则将SQL请求发送 到底层的数据库上。 </li><li>worker进程：pgpool-II 3.X版本之后才增加的进程，负责检查底层数据库之间的复制 延迟。 </li><li>watchdog进程：可以把多个pgpool-II组成一个高可用集群，解决pgpool-II自身的高可用问题，提供了vip的管理功能。 </li></ol>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>看门狗进程详细介绍</h3>
<p>pgpool-II在3.2版本之后把健康检查的功能从pgpool-II父进程中剥离出来，放到了一个 叫“看门狗”（watchdog）的模块中，该模块添加的功能如下。</p>
<ol><li>pgpool服务是否正常工作的检测：看门狗模块监控pgpool服务的响应是否正常，而不 是简单监控进程是否存活。它通过向被它监控的pgpool发送查询，并检查响应情况来判断 pgpool是否正常工作。看门狗进程还监控从pgpool到前端服务器的连接（如应用服务器） 。从pgpool到前端服务器的连接作为pgpool的服务来监控。 </li><li>看门狗进程相互监控：看门狗进程交换被监控服务器的信息用来保证信息是最新的 ，并允许看门狗进程相互监控。 ·在某些故障检测中交换各自pgpool的主/备状态：当一个pgpool的故障被检测到，看门狗进程会把故障信息通知到其他的看门狗进程，同时看门狗进程会通过投票来确定哪一 个pgpool是主pgpool，哪一个是备pgpool。 </li><li>在pgpool进行主备切换的时候自动进行虚拟IP地址的漂移：当一个备用pgpool服务器 提升为主pgpool时，相应的虚拟IP也会漂移过来。这样应用程序不需要修改配置就可以连 接到新的主pgpool上。 </li><li>当原先发生故障的pgpool服务器恢复时，会自动注册为为备用pgpool服务器：当发生 故障的服务器恢复后，或原服务器彻底损坏通过增加新的服务器来替换原先的服务器时， 新的服务器连接上来后，新服务器上的看门狗进程通知其他的看门狗进程，新的备pgpool 服务器加进来了，让整个环境恢复成高可用状态。</li></ol>
<p class="maodian"><a name="_lab2_0_3"></a></p><h3>pgpool-II的工作模式</h3>
<p>pgpool-II有连接池、复制、负载均衡等功能，使用这些功能需要把pgpool-II配置在不同的工作模式下，pgpool-II有以下几种工作模式。</p>
<ol start="1"><li>原始模式：只实现一个故障切换的功能，可以配置多个后端数据库，当第一个后端 数据库不能工作时，pgpool-II会切换到第二个后端数据库，如果第二个后端数据库也不能 工作，再切换到第三个后端数据库，依次类推。 </li><li>内置复制（Native Replication）的模式：实际上就是把修改数据库的操作同时发送到 后端所有的数据库上进行处理，只读查询发送给任意一台数据库。此模式下可以实现负载 均衡的功能。 </li><li>主/备模式：此模式下，使用其他软件（非pgpool自身）完成实际的数据复制，如使 用Slony-I或流复制，中间件层使用pgpool-II。此时，pgpool-II主要提供高可用和连接池的 功能。在主/备模式中，DDL和DML操作在主节点上执行，SELECT语句可以在主备节点 上执行，当然也可以强制SELECT语句在主节点上执行，但需要在SELECT语句前添加“/* NO LOAD BALANCE*/”注释。</li></ol>
<p>pgpool-II 3.0版本之后支持配合使用流复制+Standby的主/备模式，这基本是现在的主流模式。所以此文我们是基于这种模式部署的。操作步骤很多，请注意哪些是在所有节点执行，哪些是只在主节点执行</p>
<p class="maodian"><a name="_label1"></a></p><h2>1.环境规划</h2>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>1.1 节点信息</h3>
<table><tbody><tr><td colspan="1" rowspan="1"><p>主机</p></td><td colspan="1" rowspan="1"><p>hostname</p></td><td colspan="1" rowspan="1"><p>节点配置</p></td><td colspan="1" rowspan="1"><p>角色</p></td><td colspan="1" rowspan="1"><p>组件</p></td><td colspan="1" rowspan="1"><p>vip</p></td></tr><tr><td colspan="1" rowspan="1"><p>10.0.0.41</p></td><td colspan="1" rowspan="1"><p>postgres-01</p></td><td colspan="1" rowspan="1"><p>Centos7.9 4c/8GB</p></td><td colspan="1" rowspan="1"><p>Leader</p></td><td colspan="1" rowspan="1"><p>PostgreSQL 15.5、pgpool-II-4.5.6</p></td><td colspan="1" rowspan="3"><p>10.0.0.44</p></td></tr><tr><td colspan="1" rowspan="1"><p>10.0.0.42</p></td><td colspan="1" rowspan="1"><p>postgres-02</p></td><td colspan="1" rowspan="1"><p>Centos7.9 4c/8GB</p></td><td colspan="1" rowspan="1"><p>standby1</p></td><td colspan="1" rowspan="1"><p>PostgreSQL 15.5、pgpool-II-4.5.6</p></td></tr><tr><td colspan="1" rowspan="1"><p>10.0.0.43</p></td><td colspan="1" rowspan="1"><p>postgres-03</p></td><td colspan="1" rowspan="1"><p>Centos7.9 4c/8GB</p></td><td colspan="1" rowspan="1"><p>standby2</p></td><td colspan="1" rowspan="1"><p>PostgreSQL 15.5、pgpool-II-4.5.6</p></td></tr></tbody></table>
<p> <p class="maodian"><a name="_lab2_1_5"></a></p><h3>1.2PostgreSQL版本和配置</h3></p>
<table><tbody><tr><td colspan="1" rowspan="1"><p>Item</p></td><td colspan="1" rowspan="1"><p>Value</p></td><td colspan="1" rowspan="1"><p>Detail</p></td></tr><tr><td colspan="1" rowspan="1"><p>PostgreSQL Version</p></td><td colspan="1" rowspan="1"><p>15.5</p></td><td colspan="1" rowspan="1"><p>-</p></td></tr><tr><td colspan="1" rowspan="1"><p>port</p></td><td colspan="1" rowspan="1"><p>5432</p></td><td colspan="1" rowspan="1"><p>-</p></td></tr><tr><td colspan="1" rowspan="1"><p>$PGDATA</p></td><td colspan="1" rowspan="1"><p>/data/pgsql/data</p></td><td colspan="1" rowspan="1"><p>-</p></td></tr><tr><td colspan="1" rowspan="1"><p>Archive mode</p></td><td colspan="1" rowspan="1"><p>on</p></td><td colspan="1" rowspan="1"><p>/data/pgsql/archive</p></td></tr><tr><td colspan="1" rowspan="1"><p>Replication Slots</p></td><td colspan="1" rowspan="1"><p>Enabled</p></td><td colspan="1" rowspan="1"></td></tr><tr><td colspan="1" rowspan="1"><p>Async/Sync Replication</p></td><td colspan="1" rowspan="1"><p>Async</p></td><td colspan="1" rowspan="1"><p>-</p></td></tr></tbody></table>
<p> <p class="maodian"><a name="_lab2_1_6"></a></p><h3>1.3 Pgpool-II版本与配置</h3></p>
<table><tbody><tr><td colspan="1" rowspan="1"><p>Item</p></td><td colspan="1" rowspan="1"><p>Value</p></td><td colspan="1" rowspan="1"><p>Detail</p></td></tr><tr><td colspan="1" rowspan="1"><p>Pgpool-II Version</p></td><td colspan="1" rowspan="1"><p>4.4.5</p></td><td colspan="1" rowspan="1"><p>-</p></td></tr><tr><td colspan="1" rowspan="4"><p>port</p></td><td colspan="1" rowspan="1"><p>9999</p></td><td colspan="1" rowspan="1"><p>Pgpool-II接收连接</p></td></tr><tr><td colspan="1" rowspan="1"><p>9898</p></td><td colspan="1" rowspan="1"><p>PCP 进程接收连接</p></td></tr><tr><td colspan="1" rowspan="1"><p>9000</p></td><td colspan="1" rowspan="1"><p>看门狗接收连接</p></td></tr><tr><td colspan="1" rowspan="1"><p>9694</p></td><td colspan="1" rowspan="1"><p>UDP port接收看门狗心跳信号</p></td></tr><tr><td colspan="1" rowspan="1"><p>Config file</p></td><td colspan="1" rowspan="1"><p>/usr/local/pgpool/etc/pgpool.conf</p></td><td colspan="1" rowspan="1"><p>-</p></td></tr><tr><td colspan="1" rowspan="1"><p>User running Pgpool-II</p></td><td colspan="1" rowspan="1"><p>postgres (Pgpool-II 4.1 or later)</p></td><td colspan="1" rowspan="1"><p>Pgpool-II 4.0 or before, the default user running Pgpool-II is root</p></td></tr><tr><td colspan="1" rowspan="1"><p>Running mode</p>
<p></p></td><td colspan="1" rowspan="1"><p>streaming replication mode</p></td><td colspan="1" rowspan="1"><p>-</p></td></tr><tr><td colspan="1" rowspan="1"><p>Watchdog</p></td><td colspan="1" rowspan="1"><p>on</p></td><td colspan="1" rowspan="1"><p>Life check method: heartbeat</p></td></tr></tbody></table>
<p> <p class="maodian"><a name="_lab2_1_7"></a></p><h3>1.4 相关脚本</h3></p>
<table><tbody><tr><td colspan="1" rowspan="1"><p>模块</p></td><td colspan="1" rowspan="1"><p>脚本名</p></td><td colspan="1" rowspan="1"><p>说明</p></td></tr><tr><td colspan="1" rowspan="2"><p>Failover</p></td><td colspan="1" rowspan="1"><p>failover.sh</p></td><td colspan="1" rowspan="1"><p>用于启动failover</p></td></tr><tr><td colspan="1" rowspan="1"><p>follow_primary.sh</p></td><td colspan="1" rowspan="1"><p>failover之后从节点与新主节点的同步</p></td></tr><tr><td colspan="1" rowspan="2"><p>Online recovery</p></td><td colspan="1" rowspan="1"><p>recovery_1st_stage</p></td><td colspan="1" rowspan="1"><p>用于恢复从节点的数据</p></td></tr><tr><td colspan="1" rowspan="1"><p>pgpool_remote_start</p></td><td colspan="1" rowspan="1"><p>用于启动从节点</p></td></tr><tr><td colspan="1" rowspan="1"><p>Watchdog</p></td><td colspan="1" rowspan="1"><p>escalation.sh</p></td><td colspan="1" rowspan="1"><p>用于安全的切换主从</p></td></tr></tbody></table>
<p> <p class="maodian"><a name="_label2"></a></p><h2>2.系统准备</h2></p>
<p>注意：以下操作在所有节点进行</p>
<p class="maodian"><a name="_lab2_2_8"></a></p><h3>2.1 安装依赖</h3>
<div class="jb51code"><pre class="brush:bash;">yum install -y yum-utils openjade docbook-dtds docbook-style-dsssl docbook-style-xsl
yum install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib
yum install -y yum-builddep flex libselinux-devel libxml2-devel libxslt-devel openssl-devel pam-devel readline-devel libcurl-devel json-c-devel</pre></div>
<p class="maodian"><a name="_lab2_2_9"></a></p><h3>2.2 配置域名解析</h3>
<div class="jb51code"><pre class="brush:bash;">#所有节点
cat >> /etc/hosts << EOF
10.0.0.41 postgres-01
10.0.0.42 postgres-02
10.0.0.43 postgres-03
EOF</pre></div>
<p class="maodian"><a name="_lab2_2_10"></a></p><h3>2.3 创建用户与目录</h3>
<div class="jb51code"><pre class="brush:bash;">#所有节点
groupadd -g 701 postgres
useradd -g 701 -u 701 -s /bin/bash -m postgres
passwd postgres
mkdir /data/pgsql/{data,log,archive} -p
mkdir /data/pgpool/log -p
chown -R postgres.postgres /data
# Pgpool-II运行的进程通常是在后台运行，没有关联的终端，所以如果不做处理，在执行 if_up_cmd、if_down_cmd 和 arping_cmd 这些带有 sudo 的命令时就会失败
visudo
Defaults:postgres !requiretty
postgres ALL=(ALL) NOPASSWD: /sbin/ip,/usr/sbin/arping</pre></div>
<p class="maodian"><a name="_lab2_2_11"></a></p><h3>2.4 配置互信</h3>
<div class="jb51code"><pre class="brush:bash;">#所有节点都要与其它节点互信，需要特殊配置私钥文件得名称为id_rsa_pgpool，因为pgpool的很多配置文件里面的变量都已经写好了这个名字
（1）生成公钥
su - postgres
mkdir /home/postgres/.ssh
cd /home/postgres/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
s
（2）将公钥分发到所有节点
for i in {1..3};do ssh-copy-id -i id_rsa_pgpool.pub postgres@postgres-0$i;done
（3）修改权限
chmod 600 ~/.ssh/*
chmod 644 ~/.ssh/*.pub
chmod 700 ~/.ssh
（4）互信验证
for i in {1..3};do ssh postgres@postgres-0$i -i id_rsa_pgpool hostname;done
for i in {1..3};do ssh postgres@postgres-0$i -i id_rsa_pgpool.pub hostname;done</pre></div>
<p style="text-align:center"><img alt="pgpool-II实现对业务透明的高可用与读写分离_pgpool-II_02" src="https://img.jbzj.com/file_images/article/202504/202504190840312.png" width="570" /></p>
<p class="maodian"><a name="_lab2_2_12"></a></p><h3>2.5 系统优化</h3>
<div class="jb51code"><pre class="brush:bash;">> /etc/sysctl.conf
cat >> /etc/sysctl.conf << EOF
vm.swappiness = 0
vm.overcommit_memory=2
vm.overcommit_ratio=90
vm.dirty_background_ratio = 10
vm.dirty_ratio = 20
#测试环境物理内存8G，数据节点shared_buffer=2G，最大连接数500为例，进行如下设置
# SEMMSL SEMMNS SEMOPM SEMMNI
kernel.sem=30060000100 200
vm.nr_hugepages=1500
EOF
sysctl -p
echo "never" > /sys/kernel/mm/transparent_hugepage/enabled
echo "never" > /sys/kernel/mm/transparent_hugepage/defrag
chmod +x /etc/rc.d/rc.local
cat >> /etc/rc.local << EOF
#修改为数据库系统盘符
blockdev --setra 4096 /dev/sdc
echo deadline > /sys/block/sdc/queue/scheduler
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
EOF
cat /sys/kernel/mm/transparent_hugepage/enabled
cat /sys/kernel/mm/transparent_hugepage/defrag
> /etc/security/limits.conf
cat >> /etc/security/limits.conf << EOF
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
* soft memlock -1
* hard memlock -1
EOF
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>3.软件安装</h2>
<p>注意：以下操作在所有节点进行</p>
<p class="maodian"><a name="_lab2_3_13"></a></p><h3>3.1 安装postgresql</h3>
<div class="jb51code"><pre class="brush:bash;">#所有节点
（1）编译安装
cd /opt
wget https://ftp.postgresql.org/pub/source/v15.5/postgresql-15.5.tar.gz
tar xf postgresql-15.5.tar.gz
cd postgresql-15.5
./configure --prefix=/usr/local/postgresql-15.5 --with-perl --with-python
make && make install
（2）创建软连接，方便日后升级
ln -sf /usr/local/postgresql-15.5 /usr/local/pgsql
（3）安装contrib目录下的工具
cd contrib/
make && make install
（4）加入环境变量
# vi /etc/profile
export PATH=/usr/local/pgsql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH
export PGDATA=/data/pgsql/data
export PGHOST=/tmp
# source /etc/profile
（5）验证安装
# psql -V
psql (PostgreSQL) 15.5</pre></div>
<p class="maodian"><a name="_lab2_3_14"></a></p><h3>3.2 安装pgpool</h3>
<div class="jb51code"><pre class="brush:bash;">cd /opt
wget https://www.pgpool.net/mediawiki/download.php"export PATH=/usr/local/pgpool/bin/:$PATH">> /etc/profile
$ source /etc/profile
# 安装pgpool_recovery
cd ./src/sql/pgpool-recovery/
make && make install</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>4.数据库主节点配置</h2>
<p>注意：以下操作只在主节点进行</p>
<p class="maodian"><a name="_lab2_4_15"></a></p><h3>4.1 初始化主节点</h3>
<div class="jb51code"><pre class="brush:bash;"># su - postgres
$ initdb -D $PGDATA</pre></div>
<p class="maodian"><a name="_lab2_4_16"></a></p><h3>4.2 编辑配置文件</h3>
<div class="jb51code"><pre class="brush:bash;">$ cd $PGDATA
> postgresql.conf
cat >> postgresql.conf << EOF
############# Connection #############
listen_addresses = '*'
port = 5432
max_connections = 500
superuser_reserved_connections = 3
############# Buffer #############
shared_buffers = 2GB
temp_buffers = 8MB
work_mem = 4MB
huge_pages = on
############# Log #############
logging_collector = on
log_min_messages = warning
log_statement = ddl
log_directory = '/data/pgsql/log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
############# Wal #############
wal_level = replica
wal_compression = on
max_wal_senders = 10
wal_log_hints = on
fsync = on
archive_mode = on
archive_command = 'cp "%p" "/data/pgsql/archive"'
############# VACUUM #############
autovacuum = on
autovacuum_max_workers = 10
############ 流复制 ##############
hot_standby=on
hot_standby_feedback=on
max_replication_slots = 10
EOF</pre></div>
<p class="maodian"><a name="_lab2_4_17"></a></p><h3>4.3 启动主节点</h3>
<div class="jb51code"><pre class="brush:bash;">$ pg_ctl start -D $PGDATA
# 安装pgpool_recovery扩展
# 如果一个节点损坏，要在线把失败的节点再加回集群，需要使用到函数"pgpool_recovery"、"pgpool_remote_start"、"pgpool_switch_xlog"。另外，附带工具pgpoolAdmin控制pgpoolII启停和重新连接后端的PostgreSQL节点，需要使用函数"pgpool_pgctl"。这些函数都在pgpool提供的扩展插件pgpool_recovery中
$ psql template1
psql (15.5)
Type "help" for help.
template1=# CREATE EXTENSION pgpool_recovery;</pre></div>
<p class="maodian"><a name="_lab2_4_18"></a></p><h3>4.4 创建用户</h3>
<table><tbody><tr><td colspan="1" rowspan="1"><p>用户名</p></td><td colspan="1" rowspan="1"><p>Password</p></td><td colspan="1" rowspan="1"><p>说明</p></td></tr><tr><td colspan="1" rowspan="1"><p>repl</p></td><td colspan="1" rowspan="1"><p>Repl@123</p></td><td colspan="1" rowspan="1"><p>PostgreSQL流复制用户</p></td></tr><tr><td colspan="1" rowspan="1"><p>pgpool</p></td><td colspan="1" rowspan="1"><p>Pgpool@123</p></td><td colspan="1" rowspan="1"><p>Pgpool-II (<a href="https://www.pgpool.net/docs/45/en/html/runtime-config-health-check.html" rel="nofollow" target="_blank"> health_check_user</a>) and (<a href="https://www.pgpool.net/docs/45/en/html/runtime-streaming-replication-check.html" rel="nofollow" target="_blank"> sr_check_user</a>) </p></td></tr><tr><td colspan="1" rowspan="1"><p>postgres</p></td><td colspan="1" rowspan="1"><p>Postgres@123</p></td><td colspan="1" rowspan="1"><p>User running online recovery</p></td></tr><tr><td colspan="1" rowspan="1"><p>appuser </p></td><td colspan="1" rowspan="1"><p>Appuser@123</p></td><td colspan="1" rowspan="1"><p>业务用户</p></td></tr></tbody></table>
<p> <div class="jb51code"><pre class="brush:bash;">$ psql
set password_encryption = md5;
CREATE user pgpoolpassword 'Pgpool@123';
GRANT pg_monitor TO pgpool;
CREATE user repl replicationpassword 'Repl@123';
alter user postgres password 'Postgres@123';
CREATE USER appuser WITH PASSWORD 'Appuser@123' login;
create database appdb;
\c appdb
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;</pre></div></p>
<p class="maodian"><a name="_lab2_4_19"></a></p><h3>4.5 配置访问控制文件</h3>
<div class="jb51code"><pre class="brush:bash;">$ vim pg_hba.conf
host replication repl 10.0.0.0/24 md5
host all pgpool 10.0.0.0/24 md5
host all postgres 10.0.0.0/24 md5
host all postgres 127.0.0.1/32 md5
local all postgres md5
host appdb appuser 10.0.0.0/24 md5
# 重新加载配置
$ psql
postgres=# select pg_reload_conf();</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>5.pgpool配置</h2>
<p class="maodian"><a name="_lab2_5_20"></a></p><h3>5.1 相关脚本配置</h3>
<div class="jb51code"><pre class="brush:bash;">（1）所有节点做的操作
# chown -R postgres.postgres /usr/local/pgpool
cd /usr/local/pgpool/etc
cp pool_hba.conf.sample pool_hba.conf
cp pcp.conf.sample pcp.conf
cp pgpool.conf.sample pgpool.conf
cp follow_primary.sh.sample follow_primary.sh
cp failover.sh.sample failover.sh
cp escalation.sh.sample escalation.sh
cp recovery_1st_stage.sample recovery_1st_stage
cp pgpool_remote_start.sample pgpool_remote_start
chmod +x follow_primary.sh
chmod +x failover.sh
chmod +x escalation.sh
chmod +x recovery_1st_stage
chmod +x pgpool_remote_start
mkdir sample
mv *.sample sample
（2）主节点操作
mv recovery_1st_stage /data/pgsql/data
mv pgpool_remote_start /data/pgsql/data</pre></div>
<p class="maodian"><a name="_label3_5_20_0"></a></p><h4>配置failover.sh</h4>
<p>注意：以下操作在所有节点进行</p>
<p>在检测到 PostgreSQL 后端节点出现故障时，自动执行一系列操作以实现故障转移，从而保障数据库服务的高可用性</p>
<div class="jb51code"><pre class="brush:bash;"># 所有节点
# 修改failover.sh文件中的如下配置
# PGHOME=/usr/local/pgsql
cd /usr/local/pgpool/etc
sed -i 's/^PGHOME=.*/PGHOME=\/usr\/local\/pgsql/' /usr/local/pgpool/etc/failover.sh</pre></div>
<p class="maodian"><a name="_label3_5_20_1"></a></p><h4>配置follow_primary.sh</h4>
<p>注意：以下操作在所有节点进行</p>
<div class="jb51code"><pre class="brush:bash;"># 修改/usr/local/pgpool/etc/follow_primary.sh文件中的如下配置
# PGHOME=/usr/local/pgsql/
# ARCHIVEDIR=/data/pgsql/archive
# PGPOOL_PATH=/usr/local/pgpool/bin
# 95行左右的.pgpass文件路径由/var/lib/pgsql/.pgpass更改为/home/postgres/.pgpass
# failover后，新主节点的连接信息，写入到什么文件，RECOVERYCONF=${NODE_PGDATA}/postgresql.auto.conf
cd /usr/local/pgpool/etc
sed -i 's/^PGHOME=.*/PGHOME=\/usr\/local\/pgsql/' follow_primary.sh
sed -i 's/^ARCHIVEDIR=.*/ARCHIVEDIR=\/data\/pgsql\/archive/' follow_primary.sh
sed -i 's/^PGPOOL_PATH.*/PGPOOL_PATH=\/usr\/local\/pgpool\/bin/' follow_primary.sh
sed -i 's/\/var\/lib\/pgsql/\/home\/postgres/' follow_primary.sh
sed -i 's/myrecovery.conf/postgresql.auto.conf/' follow_primary.sh</pre></div>
<p class="maodian"><a name="_label3_5_20_2"></a></p><h4>配置recovery_1st_stage</h4>
<p>注意：以下操作在主节点执行</p>
<p># 官方原文：出于安全原因，该命令文件必须放置在数据库数据目录中。例如，如果recovery_1st_stage_command = 'sync-command'，则Pgpool-II将在$PGDATA目录中查找命令脚本，并尝试执行$PGDATA/sync-command</p>
<div class="jb51code"><pre class="brush:bash;"># 修改/data/pgsql/data/recovery_1st_stage文件中的如下配置：
# PGHOME=/usr/local/pgsql
# ARCHIVEDIR=/data/pgsql/archive
# REPLUSER=repl
# 在56行左右的pg_basebackup命令的后面加上-R参数
# 59行左右的.pgpass文件路径由/var/lib/pgsql/.pgpass更改为/home/postgres/.pgpass
cd /usr/local/pgpool/etc
sed -i 's/^PGHOME=.*/PGHOME=\/usr\/local\/pgsql/' recovery_1st_stage
sed -i 's/^ARCHIVEDIR=.*/ARCHIVEDIR=\/data\/pgsql\/archive/' recovery_1st_stage
sed -i 's/^REPLUSER=.*/REPLUSER=repl/' recovery_1st_stage
sed -i 's/-X stream/-X stream -R/' recovery_1st_stage
sed -i 's/\/var\/lib\/pgsql/\/home\/postgres/' recovery_1st_stage</pre></div>
<p class="maodian"><a name="_label3_5_20_3"></a></p><h4>配置pgpool_remote_start</h4>
<p>注意：以下操作在主节点执行</p>
<p>官方原文：</p>
<p>注意：脚本路径和文件名是硬编码的，$PGDATA/pgpool_remote_start在主节点上执行。</p>
<div class="jb51code"><pre class="brush:bash;"># 修改/data/pgsql/data/pgpool_remote_start文件中的如下配置
# PGHOME=/usr/local/pgsql
cd /usr/local/pgpool/etc
sed -i 's/^PGHOME=.*/PGHOME=\/usr\/local\/pgsql/' pgpool_remote_start</pre></div>
<p class="maodian"><a name="_label3_5_20_4"></a></p><h4>配置escalation.sh</h4>
<p>注意：以下操作在所有节点进行</p>
<p>主要用于当前 Pgpool 节点被选举为 Leader 时，执行 VIP切换和资源接管</p>
<div class="jb51code"><pre class="brush:bash;"># 修改escalation.sh文件中的如下配置
# PGPOOLS=(10.0.0.41 10.0.0.42 10.0.0.43)
# VIP=10.0.0.44
# DEVICE=ens33
cd /usr/local/pgpool/etc
sed -i 's/^PGPOOLS.*/PGPOOLS=(10.0.0.41 10.0.0.42 10.0.0.43)/' escalation.sh
sed -i 's/^VIP.*/VIP=10.0.0.44/' escalation.sh
sed -i 's/^DEVICE.*/DEVICE=ens33/' escalation.sh</pre></div>
<p class="maodian"><a name="_lab2_5_21"></a></p><h3>5.2 访问控制文件与密码验证文件</h3>
<p>注意：以下操作在所有节点进行</p>
<p class="maodian"><a name="_label3_5_21_5"></a></p><h4>配置pool_hba.conf</h4>
<p>用于客户端到pgpool的访问控制</p>
<div class="jb51code"><pre class="brush:bash;">$ cd /usr/local/pgpool/etc
$ > pool_hba.conf
cat >> pool_hba.conf << EOF
host all pgpool 10.0.0.0/24 md5
host all pgpool 127.0.0.1/32 md5
host all postgres 10.0.0.0/24 md5
host all postgres 127.0.0.1/32 md5
local all postgres md5
host appdb appuser 10.0.0.0/24 md5
EOF</pre></div>
<p class="maodian"><a name="_label3_5_21_6"></a></p><h4>配置pool_passwd文件</h4>
<p>用于客户端到pgpool的用户密码验证</p>
<div class="jb51code"><pre class="brush:bash;">$ cd /usr/local/pgpool/etc
pg_md5 -p -m -u postgres pool_passwd
password: (输入密码Postgres@123)
pg_md5 -p -m -u pgpool pool_passwd
password: (输入密码Pgpool@123)
pg_md5 -p -m -u appuser pool_passwd
password: (输入密码Appuser@123)
$ cat pool_passwd
postgres:md5dd27d33705155fd675e498384ad3d2ea
pgpool:md52a23dd6e92c7f1d2a42fafbb757ef026
appuser:md5af0df380d022abbae08aeff4d162a25b</pre></div>
<p class="maodian"><a name="_label3_5_21_7"></a></p><h4>配置pcp.conf文件</h4>
<p>对 PCP（Pgpool-II Command Prompt）工具的认证信息进行管理，独立哈希算法（非标准 MD5） ，纯 32 位哈希字符串（无md5前缀）</p>
<div class="jb51code"><pre class="brush:bash;">echo 'pgpool:'`pg_md5 Pgpool@123` > /usr/local/pgpool/etc/pcp.conf
echo 'postgres:'`pg_md5 Postgres@123` >> /usr/local/pgpool/etc/pcp.conf
$ cat /usr/local/pgpool/etc/pcp.conf
pgpool:a6270f3fee9602c6d2754b7515e85ac3
postgres:c3178349c6ad5ddaecf81e15616be142</pre></div>
<p class="maodian"><a name="_label3_5_21_8"></a></p><h4>配置pgpool_node_id文件</h4>
<p>从 Pgpool-II 4.2 开始，所有主机的所有配置参数都相同。如果启用了监视器功能，以消除对哪个主机是哪个主机的干扰，则需要pgpool_node_id文件</p>
<div class="jb51code"><pre class="brush:bash;">$ echo "0" >> /usr/local/pgpool/etc/pgpool_node_id
$ echo "1" >> /usr/local/pgpool/etc/pgpool_node_id
$ echo "2" >> /usr/local/pgpool/etc/pgpool_node_id</pre></div>
<p class="maodian"><a name="_label3_5_21_9"></a></p><h4>配置.pgpass文件</h4>
<p>当 Pgpool 需要连接后端 PostgreSQL 节点进行（健康检查、故障转移、负载均衡）时，提供用户名和密码</p>
<div class="jb51code"><pre class="brush:bash;">cat >> /home/postgres/.pgpass << EOF
10.0.0.41:5432:replication:repl:Repl@123
10.0.0.42:5432:replication:repl:Repl@123
10.0.0.43:5432:replication:repl:Repl@123
10.0.0.41:5432:postgres:postgres:Postgres@123
10.0.0.42:5432:postgres:postgres:Postgres@123
10.0.0.43:5432:postgres:postgres:Postgres@123
EOF
chmod 0600 /home/postgres/.pgpass</pre></div>
<p class="maodian"><a name="_label3_5_21_10"></a></p><h4>配置.pcppass 文件</h4>
<p>.pcppass 文件可让用户在使用 PCP 工具时无需每次都手动输入用户名和密码，从而实现自动认证。</p>
<div class="jb51code"><pre class="brush:bash;">cat >> /home/postgres/.pcppass << EOF
localhost:9898:pgpool:Pgpool@123
10.0.0.41:9898:pgpool:Pgpool@123
10.0.0.42:9898:pgpool:Pgpool@123
10.0.0.43:9898:pgpool:Pgpool@123
EOF
chmod 600 /home/postgres/.pcppass</pre></div>
<p class="maodian"><a name="_lab2_5_22"></a></p><h3>5.3 编辑主配置文件</h3>
<p>注意：以下操作在所有节点进行</p>
<div class="jb51code"><pre class="brush:bash;">$ cd /usr/local/pgpool/etc/
$ vim pgpool.conf
###################
### CONNECTIONS ###
###################
listen_addresses = '*'
port = 9999
socket_dir = '/data/pgpool'
pid_file_name = '/data/pgpool/pgpool.pid'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/data/pgpool'
num_init_children = 30
#####################
### Backend Nodes ###
#####################
backend_clustering_mode = 'streaming_replication'
backend_hostname0 = '10.0.0.41'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'backend_node01'
backend_hostname1 = '10.0.0.42'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'backend_node02'
backend_hostname2 = '10.0.0.43'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/data/pgsql/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'backend_node03'
######################
### Authentication ###
######################
enable_pool_hba = on
pool_passwd = '/usr/local/pgpool/etc/pool_passwd'
############
### LOGS ###
############
logging_collector = on
log_directory = '/data/pgpool/log'
log_filename = 'pgpool-%Y-%m-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
l
####################
### HEALTH CHECK ###
####################
health_check_period = 5
health_check_timeout = 20
health_check_user = 'pgpool'
health_check_password = 'Pgpool@123'
health_check_max_retries = 3
sr_check_user = 'pgpool'
sr_check_password = 'Pgpool@123'
follow_primary_command = '/usr/local/pgpool/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
################
### FAILOVER ###
################
failover_command = '/usr/local/pgpool/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
#######################
### ONLINE RECOVERY ###
#######################
recovery_user = 'postgres'
recovery_password = 'Postgres@123'
recovery_1st_stage_command = 'recovery_1st_stage'
# 官方原文：只有原生复制模式和快照隔离模式才需要第二阶段。其他模式（包括流复制模式）不会执行第二阶段
recovery_2nd_stage_command = ''
################
### WATCHDOG ###
################
# 注意：如果您有偶数个看门狗节点，则需要打开enable_consensus_with_half_votes参数。
use_watchdog = on
wd_ipc_socket_dir = '/data/pgpool'
hostname0 = '10.0.0.41'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = '10.0.0.42'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = '10.0.0.43'
wd_port2 = 9000
pgpool_port2 = 9999
###################
### VIP Setting ###
delegate_ip = '10.0.0.44'
# "$_IP_$" 在硬编码中代表delegate_ip的值
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ens33 label ens33:vip'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ens33'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens33'
##########################
### escalation Setting ###
##########################
wd_escalation_command = '/usr/local/pgpool/etc/escalation.sh'
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 10
#########################
### Lifecheck Setting ###
#########################
wd_lifecheck_method = 'heartbeat'
heartbeat_hostname0 = '10.0.0.41'
heartbeat_port0 = 9694
heartbeat_device0 = 'ens33'
heartbeat_hostname1 = '10.0.0.42'
heartbeat_port1 = 9694
heartbeat_device1 = 'ens33'
heartbeat_hostname2 = '10.0.0.43'
heartbeat_port2 = 9694
heartbeat_device2 = 'ens33'</pre></div>
<p class="maodian"><a name="_label6"></a></p><h2>6. 集群启动</h2>
<p class="maodian"><a name="_lab2_6_23"></a></p><h3>6.1 启动pgpool</h3>
<div class="jb51code"><pre class="brush:bash;"># 所有节点启动
# 注意第一个启动的pgpool并不会直接挂载vip，要在第二个pgpool启动之后才会选举出leader，在leader上挂载vip
pgpool -f /usr/local/pgpool/etc/pgpool.conf -D
# 启动完之后会在主节点的日志中出现以下内容，证明成功的将 VIP 添加到网络接口
2025-04-17 16:45:44.385: watchdog_utility pid 1676: LOG:watchdog escalation successful
2025-04-17 16:45:48.419: watchdog_utility pid 1676: LOG:successfully acquired the delegate IP:"10.0.0.44"
2025-04-17 16:45:48.419: watchdog_utility pid 1676: DETAIL:'if_up_cmd' returned with success</pre></div>
<p style="text-align:center"><img alt="pgpool-II实现对业务透明的高可用与读写分离_高可用_03" src="https://img.jbzj.com/file_images/article/202504/202504190840313.png" width="570" /></p>
<p class="maodian"><a name="_lab2_6_24"></a></p><h3>6.2 启动后端数据库从节点</h3>
<p>会在主节点触发recovery_1st_stage、pgpool_remote_start两个脚本，从主节点克隆数据，并直接启动从节点</p>
<div class="jb51code"><pre class="brush:bash;">$ pcp_recovery_node -h 10.0.0.41 -p 9898 -U pgpool --node-id=1 -v -w
pcp_recovery_node -- Command Successful
$ pcp_recovery_node -h 10.0.0.41 -p 9898 -U pgpool --node-id=2 -v -w
pcp_recovery_node -- Command Successful</pre></div>
<p class="maodian"><a name="_lab2_6_25"></a></p><h3>6.3 查看集群状态</h3>
<p>使用vip连接pgpool 9999端口，查看后端数据库</p>
<div class="jb51code"><pre class="brush:bash;">$ PGPASSWORD="Postgres@123" psql -h10.0.0.44 -p 9999
psql (15.5)
Type "help" for help.
postgres=# SELECT inet_server_addr() AS backend_host,inet_server_port() AS backend_port,current_database(),current_user;</pre></div>
<p style="text-align:center"><img alt="pgpool-II实现对业务透明的高可用与读写分离_数据库_04" src="https://img.jbzj.com/file_images/article/202504/202504190840314.png" width="570" /></p>
<p>查看流复制</p>
<div class="jb51code"><pre class="brush:bash;">postgres=# select usename,client_addr,sync_state from pg_stat_replication;</pre></div>
<p style="text-align:center"><img alt="pgpool-II实现对业务透明的高可用与读写分离_数据库_05" src="https://img.jbzj.com/file_images/article/202504/202504190840315.png" width="570" /></p>
<p>查看pool_node节点信息</p>
<div class="jb51code"><pre class="brush:bash;">postgres=# show pool_nodes;</pre></div>
<p style="text-align:center"><img alt="pgpool-II实现对业务透明的高可用与读写分离_数据库_06" src="https://img.jbzj.com/file_images/article/202504/202504190840316.png" width="570" /></p>
<p>到此这篇关于pgpool-II搭建集群，实现高可用与读写分离的文章就介绍到这了,更多相关pgpool-II实现高可用与读写分离内容请搜索琼殿技术社区以前的文章或继续浏览下面的相关文章希望大家以后多多支持琼殿技术社区！</p>
頁:
[1]