背景
为保证梧桐数据库管理平台服务高可用，调研pg高可用方案
数据库架构
本文搭建高可用方案为 pgpool 2 + postgres 1主2从
相关组件介绍
名称版本官网职责postgres14.5https://www.postgresql.org/功能强大的开源关系型数据库管理系统[RDBMS]repmgr5.3.3https://www.repmgr.org/repmgr是一个开源工具套件，用于管理PostgreSQL服务器集群中的复制和故障转移。它使用工具来增强PostgreSQL的内置热备份功能，以设置备用服务器，监控复制以及执行管理任务。pgpool-II4.2.2https://www.pgpool.net/Pgpool 是一个高性能的连接池和负载均衡器，用于 PostgreSQL 数据库。Pgpool 可以作为中间层，位于客户端和 PostgreSQL 服务器之间，来管理连接请求并分配给不同的 PostgreSQL 服务器进行处理，以提高整体的系统性能和可用性。Pgpool 的一些主要功能包括： 连接池、负载均衡、高可用性、并行查询
环境
centos7 x86 3台
三台机器建立ssh 免密
postgers用户
ssh-keygen -t rsa
ssh-copy-id postgres@20240314-1
ssh-copy-id postgres@20240314-2
ssh-copy-id postgres@20240314-3
正式搭建
搭建pg master节点
安装postgres
yum install -y perl-ExtUtils-Embed python-devel bison flex readline-devel zlib-devel gcc gcc-c++ wget uuid uuid-devel
yum -y install pgdg-srpm-macros lz4-devel libicu-devel openldap-devel openssl-devel libuuid-devel libxml2-devel libxslt-devel
wget --no-check-certificate https://ftp.postgresql.org/pub/source/v14.5/postgresql-14.5.tar.gz
https://ftp.postgresql.org/pub/source/v14.9/postgresql-14.9.tar.gz
tar -zxvf postgresql-14.5.tar.gz
cd postgresql-14.5
./configure --prefix=/usr/pgsql-14 --with-perl --with-python --enable-thread-safety --with-uuid=ossp
make && make install
cd postgresql-14.5/contrib
添加环境变量
mkdir /usr/pgdata
vi ~/.bash_profile
PGDATA=/usr/pgdata
export PGDATA
export PATH
source ~/.bash_profile
添加postgres用户
useradd postgres
chown -R postgres:postgres /usr/local/pgsql-14
chown -R postgres:postgres /data/repmgr-5.3.3
chown -R postgres:postgres /data/postgresql-14.5
mkdir /usr/pgdata
chown -R postgres:postgres /usr/pgdata
切换至postgres用户 修改环境变量
sudo su postgres
vi /home/postgres/.bashrc
export PGDATA=/usr/pgdata
export PATH=/usr/pgsql-14/bin:$PATH
export LD_LIBRARY_PATH=/usr/pgsql-14/lib
source /home/postgres/.bashrc
初始化pg,修改postgresql.conf配置文件
initdb -D $PGDATA -k
mkdir $PGDATA/arch_log
vi $PGDATA/archive.sh
cp --preserve=timestamps $1 $PGDATA/arch_log/$2 ; find $PGDATA/arch_log -type f -mtime +30 | xargs rm -fr;
修改pg_hba.conf
vi $PGDATA/pg_hba.conf
local all postgres peer
local replication repmgr trust
host replication repmgr 127.0.0.1/32 trust
host replication repmgr 0.0.0.0/0 trust
local repmgr repmgr trust
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 0.0.0.0/0 trust
至此pg修改完成
主节点安装repmgr
安装repmgr
wget --no-check-certificate https://www.repmgr.org/download/repmgr-5.3.3.tar.gz
tar -zxvf repmgr-5.3.3.tar.gz
cd repmgr-5.3.3
./configure
make install
编辑repmgr配置文件
vi /data/repmgr-5.3.3/repmgr.conf
node_id=1
node_name='20240314-1'
conninfo='host=20240314-1 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/usr/pgdata'
failover=automatic
promote_command='/usr/local/pgsql-14/bin/repmgr standby promote -f /data/repmgr-5.3.3/repmgr.conf --log-to-file'
follow_command='/usr/local/pgsql-14/bin/repmgr standby follow -f /data/repmgr-5.3.3/repmgr.conf --log-to-file --upstream-node-id=%n'
service_start_command = '/usr/local/pgsql-14/bin/pg_ctl start -D /usr/pgdata'
service_stop_command = '/usr/local/pgsql-14/bin/pg_ctl stop -D /usr/pgdata'
service_restart_command = '/usr/local/pgsql-14/bin/pg_ctl restart -D /usr/pgdata'
service_reload_command = '/usr/local/pgsql-14/bin/pg_ctl reload -D /usr/pgdata'
repmgrd_pid_file='/tmp/repmgrd.pid'
log_file='/tmp/repmgrd.log'
priority=100
monitor_interval_secs = 2
connection_check_type ='ping'
reconnect_attempts = 4
reconnect_interval = 5
注释
monitor_interval_secs 参数告诉repmgr守护程序应该多久（以秒为单位）检查上游节点的可用性。默认2s检查一次
connection_check_type 用什么方式联系上游节点。此参数可以采用三个值：默认为ping
- ping
- connection
- query
reconnect_attempts = 4
reconnect_interval = 8
当主节点不可用时，备用节点中的repmgr守护程序将尝试重新连接到主节点达reconnect_attempts次。此参数的默认值为6。在每次重新连接尝试之间，它将等待reconnect_interval秒，默认值为10。
此时repmgr安装完毕
完善元数据信息
psql
postgres@[local:/tmp]:1998=#83348 create user repmgr superuser password 'repmgr';
CREATE ROLE
postgres@[local:/tmp]:1998=#83348 ALTER USER repmgr SET search_path TO repmgr, "$user", public;
ALTER ROLE
postgres@[local:/tmp]:1998=#83348 create database repmgr owner repmgr;
CREATE DATABASE
启动pg,启动repmgr
pg_ctl start
repmgr -f /data/repmgr-5.3.3/repmgr.conf primary register
repmgrd -f /data/repmgr-5.3.3/repmgr.conf
执行
repmgr -f /data/repmgr-5.3.3/repmgr.conf service status 即可查询目前的pg集群状态
pg主节点搭建完成
搭建pg stangby 节点
安装postgres 与 repmgr 完全一样，装好之后执行
#将🐷节点pgdata拉取
执行
repmgr -f /data/repmgr-5.3.3/repmgr.conf service status 即可查询目前的pg集群状态
多个stangby节点 操作均一致，不做赘述
机器搭建pgpool-II（在pg所在节点自行安装）
安装pgpool
mkdir -p /data/pgpool
wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.2.2.tar.gz
tar -zxvf pgpool-II-4.2.2.tar.gz
cd pgpool-II-4.2.2/
./configure --prefix=/data/pgpool --with-pgsql=/usr/local/pgsql-14
make -j 8 && make install
添加pgpool用户
psql -U postgres
create role nobody login encrypted password 'passwd';
create role pgpool login encrypted password 'passwd';
grant postgres to nobody,pgpool;
修改pgpool.conf配置文件
backend_hostname0 = '20240314-1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data/postgres'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '20240414-2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/postgres'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = '20240314-3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/data/postgres'
backend_flag3 = 'ALLOW_TO_FAILOVER'
postgres用户需要一些命令权限
echo "postgres ALL=NOPASSWD: ALL" >> /etc/sudoers
修改pool_hba.conf配置文件
echo "host all all 0.0.0.0/0 md5" >> /etc/pool_hba.conf
pgpool-II 4.2版本之后 pgpool集群所有节点配置文件一致，其他不需要变更，但是需要一个nodeid文件，nodeid从0开始，各节点累加
echo $node_id>>/data/pgpool/etc/pgpool_node_id
pgpool启停命令
/data/pgpool/bin/pgpool -f /data/pgpool/etc/pgpool.conf -m fast stop
/data/pgpool/bin/pgpool -f /data/pgpool/etc/pgpool.conf -n > /tmp/pgpool.log 2>&1
至此 安装完毕
高可用运维相关命令：
repmgr命令
主节点解除关联
/usr/pgsql-14/bin/repmgr -f /data/repmgr-5.3.3/repmgr.conf primary unregister --node-id 1
将2号从节点从集群中删除
repmgr standby unregister -f /data/repmgr-5.3.3/repmgr.conf --node-id=2
查看集群状态 这里会显示 节点id hostname 关系
/usr/pgsql-14/bin/repmgr -f /data/repmgr-5.3.3/repmgr.conf service status
从节点接触关联
/usr/pgsql-14/bin/repmgr -f /data/repmgr-5.3.3/repmgr.conf standby unregister
从节点加入集群时，拉取主节点数据(端口 不是默认端口时 -p 端口号)
/usr/pgsql-14/bin/repmgr -F -h 20240319dev501478m1-13830-13916-4（主节点hostname） -U repmgr -d repmgr -f /data/repmgr-5.3.3/repmgr.conf standby clone
从节点加入集群
/usr/pgsql-14/bin/repmgr -f /data/repmgr-5.3.3/repmgr.conf standby register
主节点加入集群
/usr/pgsql-14/bin/repmgr -f /data/repmgr-5.3.3/repmgr.conf primary register
暂停repmgrd，可以在任何一个节点上执行，可以用于例行维护，避免主库正常关闭，集群被切换
repmgr -f /data/repmgr-5.3.3/repmgr.conf service pause
解除暂停为
repmgr -f /data/repmgr-5.3.3/repmgr.conf service unpause
pg节点监控 新增节点或者节点挂了重新启动之后需要监控
repmgrd -f /data/repmgr-5.3.3/repmgr.conf
pgpool命令
查看 pgpool 集群信息 密码为passwd 用户名nobody不行的话 就换pgpool，密码一样
pcp_watchdog_info -h 10.0.200.239 -p 9898 -U nobody
更新pg节点状态 当pg出现掉线时 状态为down ，需要手动将pgpool中的pg节点状态更新
pcp_attach_node -U nobody -h 10.0.200.239 -p 9898 -n 0
听说…关注下面公众号的人都变牛了，纯技术，纯干货 !