梧桐数据库（WuTongDB）：管理平台 postgresql 高可用方案 postgres + repmgr +pgpool-II

鲁鲁517

已于 2024-07-28 12:41:45 修改

阅读量1.3k

点赞数

分类专栏：梧桐数据库文章标签：梧桐数据库

于 2024-04-26 22:50:48 首次发布

原文链接：https://www.modb.pro/db/1777899669728858112

版权

梧桐数据库专栏收录该内容

61 篇文章 1 订阅

订阅专栏

个人介绍：艺名王三日，中国移动梧桐数据库研发团队成员，云原生数据库管理平台后端研发，相关技术栈问题都可以一起交流探讨。
欢迎探讨： wtswangjing

背景

为保证梧桐数据库管理平台服务高可用，调研pg高可用方案

数据库架构

本文搭建高可用方案为 pgpool 2 + postgres 1主2从

名称	版本	官网	职责
postgres	14.5	PostgreSQL: The world's most advanced open source database	功能强大的开源关系型数据库管理系统[RDBMS]
repmgr	5.3.3	repmgr - Replication Manager for PostgreSQL clusters	repmgr是一个开源工具套件，用于管理PostgreSQL服务器集群中的复制和故障转移。它使用工具来增强PostgreSQL的内置热备份功能，以设置备用服务器，监控复制以及执行管理任务。
pgpool-II	4.2.2	pgpool Wiki	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