数据库postgresql安装简便，在此基础上把postgresql16的高可用搭起来
一 基础环境准备（所有节点）
pgsql：版本16
pgpool2：版本4.5
操作系统：centos7.6
虚拟IP : 192.168.131.210
hostname | IP | 配置 | 用途 |
pgmaster | 192.168.131.202 | 2c/4g/20g | pgsql主服务器 |
pgslave01 | 192.168.131.203 | 2c/4g/20g | pgsql从服务器 |
端口规划
5432 | PostgreSQL 监听端口 |
9999 | pgpool-II 监听端口 |
9898 | pcp 管理端口 |
9000 | watchdog 监听端口 |
1.按照表里修改主机名（root用户）
hostnamectl set-hostname XXXX2.配置sudo权限（root用户）
vi /etc/sudoers
postgres ALL=(ALL) NOPASSWD: ALL
3.修改host文件（root用户）
echo "192.168.131.202 pgmaster" | sudo tee -a /etc/hosts
echo "192.168.131.203 pgslave01" | sudo tee -a /etc/hostssudo tee -a /etc/hosts：tee 命令用于将数据写入到文件中。-a 参数表示以追加模式写入文件，即不会覆盖文件的现有内容，而是将新内容添加到文件末尾。
3.关闭防火墙以及selinux（root用户）
systemctl stop firewalld
systemctl disable firewalld
sudo sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config && sudo reboot4.修改ssh配置，服务端打开公钥权限（root用户）
sudo sed -i '/^#PubkeyAuthentication/s/^#//' /etc/ssh/sshd_config && sudo systemctl restart sshd5.设置服务器间免密登陆（一定要用postgres的系统账户执行）
passwd postgres
su - postgres
mkdir ~/.ssh
cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool将位于 /home/postgres/.ssh/id_rsa_pgpool.pub 的 SSH 公钥复制到 postgres 用户在 IP 地址为 192.168.131.203 的远程主机上的 ~/.ssh/authorized_keys 文件中。这样，postgres 用户就可以在本地机器上通过 SSH 免密码登录到 192.168.131.203 这台远程主机
ssh-copy-id -i /home/postgres/.ssh/id_rsa_pgpool.pub postgres@192.168.131.203登录对方机器，授权
ssh postgres@192.168.131.202
chmod 700 ~/.ssh
chmod 600 ~/.ssh/authorized_keys
exit回到本地主机，互相间测试免密登陆命令
ssh -i /home/postgres/.ssh/id_rsa_pgpool postgres@192.168.131.203二 安装pgpool2（所有节点）
1.包安装pgpool2
yum install -y https://www.pgpool.net/yum/rpms/4.5/redhat/rhel-9-x86_64/pgpool-II-release-4.5-1.noarch.rpm
下载并安装 pgdg-redhat-repo 包，该包会自动将 pgdg-redhat-all.repo 文件添加到 /etc/yum.repos.d/ 目录下
yum install -y pgpool-II-pg16-*
2.编译安装pgpool2
root用户执行
wget -c https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.5.1.tar.gz
tar -xvf download.php?f=pgpool-II-4.5.1.tar.gz
cd pgpool-II-4.5.1
mkdir -p /usr/local/pgpool-II-4.5.1
./configure --prefix=/usr/local/pgpool-II-4.5.1 --with-openssl --with-pgsql=/opt/pgsql/postgresql
make && make install
cd /usr/local
ln -s pgpool-II-4.5.1 pgpool3.设置环境变量
vi /etc/profile
export PATH=$PATH:/usr/local/pgpool/bin/
source /etc/profile4.生成配置文件
因为 pgpool 主要用于 pgsql 数据库的管理，推荐使用 postgres 用户管理
chown -R postgres:postgres /usr/local/pgpool/etc/
su - postgres
cd /usr/local/pgpool/etc
cp pcp.conf.sample pcp.conf
cp pgpool.conf.sample pgpool.conf
cp pool_hba.conf.sample pool_hba.conf5创建日志目录
su - root
mkdir /var/log/pgpool_log
chown postgres:postgres /var/log/pgpool_log/三 配置pgpool2（所有节点）
1.编辑 pcp.conf 文件
pcp.conf 文件用于定义可以访问 Pgpool-II 的 PCP（Pgpool-II Communication Protocol）接口的用户名和密码。只有知道这些凭据的用户才能执行 PCP 命令。通过 pcp.conf 文件，Pgpool-II 能够限制对敏感操作的访问，增加一层安全控制。具体配置如下：
生成pcp需要的加密的密码
pg_md5 "your密码"
echo 'pgpool:加密后的密码' >> /usr/local/pgpool/etc/pcp.conf
2.编辑.pcppass
.pcppass 文件是 Pgpool-II 的一个配置文件，它用于存储 PCP（Pgpool-II Communication Protocol）工具的用户名和密码。PCP 工具允许用户远程管理和监控 Pgpool-II 集群，而 .pcppass 文件则包含了这些工具用于认证的凭据。文件内容格式如下：
hostname:port:database:username:password每一行代表一个认证记录，包含主机名、端口、数据库名、用户名和密码。这些信息用于 PCP 工具连接到指定的 Pgpool-II 节点。具体编辑内容如下
su - postgres -c "
echo 'pgmaster:5432:pgpool:your-pcp-password
pgslave:5432:pgpool:your-pcp-password' > ~/.pcppass
chmod 600 ~/.pcppass
"注意：这里pcp用户名用的是pgpool，your-pcp-password是pgpool的密码
3.编辑 pgpool.conf
cat << EOF > /usr/local/pgpool-II-4.5.1/etc/pgpool.conf
unix_socket_directories = '/run/pgpool'
pid_file_name = '/run/pgpool/pgpool.pid'
backend_status_file = '/run/pgpool/pgpool_status'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_enable = true
pcp_user = 'pgpool'
pcp_password = 'ba777e4c2f15c11ea8ac3be7e0440aa0' #这里注意要填写你上面pcp用户的加密的密文密码
backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
port = 9999
sr_check_user = 'pgpool'
sr_check_password = ''
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3
enable_pool_hba = on
pool_passwd = 'pool_passwd'
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'
# - Backend Connection Settings -
backend_hostname0 = '192.168.131.202'
backend_application_name0 = '192.168.131.202'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/opt/pgsql/postgresql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.131.203'
backend_application_name1 = '192.168.131.203'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/opt/pgsql/postgresql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
# 看门狗基本设置
use_watchdog = on
# VIP设置
delegate_IP = '192.168.131.210' # 虚拟IP地址
if_cmd_path = '/usr/sbin'
if_up_cmd = 'ip addr add 192.168.131.210 dev ens192'
if_down_cmd = 'ip addr del 192.168.131.210 dev ens192'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U 192.168.131.210 -w 1'
#看门狗节点设置
hostname0 = '192.168.131.202' #master node
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = '192.168.131.203'
wd_port1 = 9000
pgpool_port1 = 9999
# 心跳设置
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_hostname0 = '192.168.131.202'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = '192.168.131.203'
heartbeat_port1 = 9694
heartbeat_device1 = ''
wd_escalation_command = '/usr/local/pgpool-II-4.5.1/etc/escalation.sh'
#log设置
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool_log'
log_filename = 'pgpool-%Y-%m-%d.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
# 优化的连接池大小配置
num_init_children = 500 # 修改：初始化子进程数为200，以支持更高的并发连接数
max_pool = 10 # 修改：每个子进程的连接池大小为10
child_life_time = 300 # 修改：子进程生命周期为300秒
connection_life_time = 0 # 修改：连接生命周期为0（无限制）
client_idle_limit = 0 # 修改：客户端空闲时间限制为0（无限制）
child_max_connections = 0 # 修改：每个子进程最大连接数为0（无限制）
EOF4.配置pgpool_node_id
pgpool_node_id 是 Pgpool-II 配置中的一个文件，用于标识集群中每个 Pgpool-II 实例的唯一节点 ID。在 Pgpool-II 的高可用（HA）配置中，特别是当使用 Watchdog 进行故障转移时，这个文件起着至关重要的作用。
主节点配置
cat << EOF > /usr/local/pgpool/etc/pgpool_node_id
0
EOF从节点配置
cat << EOF > /usr/local/pgpool/etc/pgpool_node_id
1
EOF5.配置pool_hba.conf 客户端认证配置文件
cat << EOF > /usr/local/pgpool-II-4.5.1/etc/pool_hba.conf
host all all 192.168.131.202/32 trust
host all all 192.168.131.203/32 trust
host all all 192.168.131.210/32 trust
EOF修改 pg_hba.conf 文件后，需要重新加载 PostgreSQL 的配置，以便更改生效
pg_ctl reload6.配置pool_passwd文件
执行命令生成repl、postgresql、pgpool这三个数据库用户的md5密码
pg_md5 -p -m -u postgres pool_passwd
pg_md5 -p -m -u repl pool_passwd
pg_md5 -p -m -u pgpool pool_passwd7. 配置故障转移
cp -p /usr/local/pgpool/etc/failover.sh.sample /usr/local/pgpool/etc/failover.sh
cp -p /usr/local/pgpool/etc/follow_primary.sh.sample /usr/local/pgpool/etc/follow_primary.sh
chown postgres:postgres /usr/local/pgpool/etc/{failover.sh,follow_primary.sh}8.配置流复制和故障恢复
建立从节点建立和主节点的关联，保证wal日志自动同步到从节点的脚本
cp -p /usr/local/pgpool/etc/recovery_1st_stage.sample /opt/pgsql/postgresql/data/recovery_1st_stage
cp -p /usr/local/pgpool/etc/pgpool_remote_start.sample /opt/pgsql/postgresql/data/pgpool_remote_start
chown postgres:postgres /opt/pgsql/postgresql/data/{recovery_1st_stage,pgpool_remote_start}
chmod 755 /opt/pgsql/postgresql/data/{recovery_1st_stage,pgpool_remote_start}9.配置看门狗脚本
看门狗是一个独立的软件组件，用于监控和管理集群中的节点健康状态，并在主节点发生故障时协调故障转移过程，Pgpool-II 的看门狗组件负责监控，故障检测，故障转移和集群协调，避免脑裂。是作为 Pgpool-II 高可用解决方案的一部分来安装和部署的。
cp -p /usr/local/pgpool/etc/escalation.sh.sample /usr/local/pgpool/etc/escalation.sh
chown postgres:postgres /usr/local/pgpool/etc/escalation.sh
此文件中要修改的内容如下
PGPOOLS=(server1 server2 server3)
VIP=192.168.100.50
DEVICE=enp0s8实际采用脚本修改，具体脚本如下
sed -i 's/PGPOOLS=(server1 server2 server3)/PGPOOLS=(pgmaster pgslave)/; s/VIP=192.168.100.50/VIP=192.168.131.210/; s/DEVICE=enp0s8/DEVICE=ens192/' /usr/local/pgpool/etc/escalation.sh授权使用脚本
sudo chmod u+s /usr/sbin/ip
sudo chmod u+s /usr/sbin/arpingip是一个用于管理网络接口和路由的工具。在某些网络配置脚本中，可能需要以 root 用户的权限执行ip命令。通过设置 SUID 位，即使普通用户也可以执行这些命令，而无需每次都输入 root 密码。arping是一个用于发送 ARP 请求的工具，通常用于网络诊断。设置 SUID 位允许普通用户以 root 用户的权限运行arping，这对于网络管理员在没有 root 权限的情况下进行网络故障排查非常有用。
10.初始化 Pgpool-II 的状态文件
创建一个名为 pgpool_status 的空文件。这个文件通常用于 Pgpool-II 来存储当前的运行状态信息
sudo mkdir -p /run/pgpool/
sudo chown postgres:postgres /run/pgpool/
sudo chmod 755 /run/pgpool/
sudo touch /run/pgpool/pgpool_status
sudo chown -R postgres:postgres /run/pgpool/pgpool_status
sudo chmod 664 /run/pgpool/pgpool_status11.启动pgpool
因为 pgpool 主要用于 PostgreSQL 数据库的管理，推荐使用 postgres 用户管理，且如果存在 gpool_status 文件，则 gpoolⅡ将从 gpool_state 文件读取后端状态（up/down）。
/usr/local/pgpool/bin/pgpool
--停止 pgpool
pgpool stop
--强制关闭 pgpool-II
pgpool -m fast stop四 主备库基本配置
1.创建归档目录（所有节点）
先用root用户授权pgsql安装目录
chown -R postgres:postgres /opt/pgsql/再用postgres用户创建需要的目录
mkdir /opt/pgsql/archivedir2.主数据库参数配置postgresql.conf（主节点）
echo "listen_addresses = '*'
port = 5432
archive_mode = on
archive_command = 'cp %p /opt/pgsql/archivedir/%f'
archive_cleanup_command = 'pg_archivecleanup /opt/pgsql/archivedir %r'
archive_timeout = '30min'
max_wal_senders = 5
max_wal_size = '1GB'
min_wal_size = '80MB'
checkpoint_timeout = '2min'
checkpoint_completion_target = 0.7
max_replication_slots = 2
wal_level = replica
hot_standby = on
wal_log_hints = on
shared_buffers = '1GB'
work_mem = '64MB'
maintenance_work_mem = '512MB'
effective_cache_size = '12GB'
wal_buffers = '16MB'
max_wal_size = '1GB'
min_wal_size = '80MB'
max_connections = '500'
superuser_reserved_connections = '3'
max_parallel_workers_per_gather = '2'
max_parallel_workers = '1'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = '500ms'
autovacuum = on" | tee /opt/pgsql/postgresql/data/postgresql.conf3.配置数据库用户（主节点）
用户名 | 密码 | 用途 |
repl | repl | 用于 PostgreSQL 流复制 |
pgpool | pgpool | 用于 pgpool-ll 心跳检测以及复制延迟检测 |
postgres | postgres | 用于在线恢复 |
重启数据库
sudo service postgresql stop
sudo service postgresql start
#进入数据库
psql -U postgres -p 5432
#设置全局密码加密方式
alter system set password_encryption='md5';
#重新加载配置
SELECT pg_reload_conf();
#创建pgpool用户
CREATE ROLE pgpool WITH LOGIN;
#创建内建流复制用户
CREATE ROLE repl WITH REPLICATION LOGIN;
#创建了一个名为 repl 的角色，它具有复制权限，可以用于流复制，并且也有一个密码。
#设置pgpool用户密码
\password pgpool
#设置repl用户密码
\password repl
#设置postgres用户密码
\password postgres
#给pgpool账户权限
GRANT pg_monitor TO pgpool;4.配置用户密码.pgass（所有节点）
为了允许repl用户无需指定密码即可进行流式复制和在线恢复，并 使用postgres执行pg_rewind，我们在postgres用户的主目录中创建.pgpass文件，并将 每个PostgreSQL服务器上的 权限更改为600。该文件允许repl用户和postgres 用户无需提供密码即可进行流式复制和故障转移。
记得替换密码
echo -e "192.168.131.202:5432:replication:repl:<repl user password>\n192.168.131.203:5432:replication:repl:<repl user password>\n192.168.131.202:5432:postgres:postgres:<postgres user password>\n192.168.131.203:5432:postgres:postgres:<postgres user password>" | sudo tee /opt/pgsql/postgresql/.pgpass > /dev/null
chown -R postgres:postgres /opt/pgsql/postgresql/.pgpass
chmod 600 /opt/pgsql/postgresql/.pgpass5. 配置远程访问pg_hba.conf（主节点）
echo "host replication repl 192.168.131.202/32 trust" >> /opt/pgsql/postgresql/data/pg_hba.conf
echo "host replication repl 192.168.131.203/32 trust" >> /opt/pgsql/postgresql/data/pg_hba.conf
echo "host replication repl 192.168.131.210/32 trust" >> /opt/pgsql/postgresql/data/pg_hba.conf
echo "host all pgpool 192.168.131.202/32 trust" >> /opt/pgsql/postgresql/data/pg_hba.conf
echo "host all pgpool 192.168.131.203/32 trust" >> /opt/pgsql/postgresql/data/pg_hba.conf
echo "host all pgpool 192.168.131.210/32 trust" >> /opt/pgsql/postgresql/data/pg_hba.conf
echo "host all postgres 192.168.131.202/32 trust" >> /opt/pgsql/postgresql/data/pg_hba.conf
echo "host all postgres 192.168.131.203/32 trust" >> /opt/pgsql/postgresql/data/pg_hba.conf
echo "host all postgres 192.168.131.210/32 trust" >> /opt/pgsql/postgresql/data/pg_hba.conf五 主库配置
主数据库中创建 pgpool_recovery 扩展
安装pgpool_recovery扩展
您需要从 Pgpool-II 的源代码中安装 pgpool_recovery 扩展
cd /opt/tools/pgpool-II-4.5.1/src/sql/pgpool-recovery
make
make install数据库中创建pgpool_recovery扩展
su - postgres
psql template1 -c "CREATE EXTENSION pgpool_recovery"查看扩展
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \dt
Did not find any relations.
template1=# \df
List of functions
Schema | Name | Result data type | Argument data types
| Type
--------+---------------------+------------------+------------------------------------------------------------------------------------------------
---------------------------------------------+------
public | pgpool_pgctl | boolean | action text, stop_mode text
| func
public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text
| func
public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text, primary_port text
| func
public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text, primary_port text, remote_node
integer | func
public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text, primary_port text, remote_node
integer, remote_port text | func
public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text, primary_port text, remote_node
integer, remote_port text, primary_host text | func
public | pgpool_remote_start | boolean | remote_host text, remote_data_directory text
| func
public | pgpool_switch_xlog | text | archive_dir text
| func
(8 rows)六 从库配置
1.从主库创建基础备份
停止从库服务
service postgresql stop
#清空从库的现有数据目录
sudo rm -rf /opt/pgsql/postgresql/data/*在从库上使用pg_basebackup从主库创建基础备份
sudo -u postgres pg_basebackup -h 192.168.131.202 -D /opt/pgsql/postgresql/data -U repl -P -R2.设置备库为流复制模式
查看节点对应的node_id号
psql -h 192.168.131.210 -p 9999 -U pgpool postgres -c "show pool_nodes"设置从节点接收主库wal日志
pcp_recovery_node -h 192.168.131.210 -p 9898 -U pgpool -n 1 -W命令首先尝试使用提供的主机名、端口和用户名连接到 Pgpool-II 服务器，认证成功后，pcp_recovery_node 命令会在指定的从节点确保该节点可以开始或恢复与主节点的流复制。这个命令是 PostgreSQL 与 Pgpool-II 高可用集群配置中的关键步骤，确保WAL数据可以从主节点同步到从节点，并且在主节点发生故障时，从节点可以接管服务。
3.查看看门狗状态
这里用的是pcp的账户
pcp_watchdog_info -h 192.168.131.210 -p 9898 -U pgpool -W七 故障恢复后主变从配置
pcp_recovery_node -h 192.168.131.210 -p 9898 -U pgpool -n your-node-id -W八 日志清理配置
归档日志自动清理脚本
#!/bin/bash
# 定义归档日志目录
ARCHIVE_DIR="/var/lib/pgsql/archivedir"
# 找到超过一天前的最后一个 WAL 文件
LAST_FILE_TO_KEEP=$(find $ARCHIVE_DIR -type f -name '????????????????????????' -mtime +1 | sort | tail -n 1)
# 检查是否找到文件
if [[ -n "$LAST_FILE_TO_KEEP" ]]; then
# 获取文件名
FILE_NAME=$(basename $LAST_FILE_TO_KEEP)
# 运行 pg_archivecleanup 清理
/usr/pgsql-16/bin/pg_archivecleanup $ARCHIVE_DIR $FILE_NAME
echo "Cleaned up archive logs older than $FILE_NAME."
else
echo "No archive logs older than 1 day found."
fi问题总结
现象：数据库无法启动
解决：/tmp/.s.PGSQL.5432被占用
原因：Unix 域套接字文件（如 /tmp/.s.PGSQL.5432）主要用于本地机器上的进程间通信。对于远程连接，Unix 域套接字不直接使用，因为它们是基于文件系统路径的，不能跨越网络。远程连接通常使用 TCP/IP 协议，通过服务器的 IP 地址和端口号（默认是 5432）来建立连接。
问题：日志查看位置
pgpool日志位置：/var/log/pgpool_log/
postgresql日志位置：/opt/pgsql/postgresql/data/pg_log/
主库和备库日志位置一致
