本方案说明

  • PostgreSQL + repmgr：实现主从自动故障检测与切换（Failover）。
  • PgBouncer：作为连接池，屏蔽后端数据库变动，提供透明连接。
  • 动态配置更新：通过repmgr组件的promote_command阶段触发脚本自动更新 PgBouncer 的 [databases] 配置，指向新主库。
  • 需要开发语言支持连接串写多个IP，来连接多个pgbouncer

节点规划

主机hostname角色组件
10.0.0.41repmgr01LeaderPostgreSQL 15.5、repmgr 5.5.0、pgbouncer-1.24.0
10.0.0.42repmgr02standby1PostgreSQL 15.5、repmgr 5.5.0、pgbouncer-1.24.0
10.0.0.43repmgr03standby2PostgreSQL 15.5、repmgr 5.5.0、pgbouncer-1.24.0

目录文件说明

/data/pgsql/data后端postgresql的数据目录
/data/pgsql/log后端postgresql的日志目录
/data/pgsql/data/postgres.conf后端postgresql的配置文件
/data/pgsql/data/pg_hba.conf后端postgresql的访问控制文件
/data/repmgr高可用组件repmgr的家目录
/data/repmgr/repmgr.conf高可用组件repmgr的配置文件
/data/repmgr/promte_standby_pgbouncer.shrepmgr监测到后端主节点故障后触发的脚本
/data/pgbouncer/pgbouncer.templatepgbouncer配置模板，被触发脚本引用
/data/pgbouncer/pgbouncer.inipgbouncer配置文件

1.集群准备

准备一套1主2从的repmgr集群，部署过程参考我的另一篇文章PostgreSQL高可用架构Repmgr部署流程

1.1 安装依赖

# 集群中所有节点root用户执行安装# 触发脚本之后会将新配置文件同步到所有节点
yum install -y install rsync
# PgBouncer是基于Libevent开发的，所以需要先安装Libevent的开发包
yum install -y install libevent-devel

1.2 安装PgBouncer

# 集群中所有节点root用户执行，安装PgBouncer[root@repmgr01 ~]# cd /opt
[root@repmgr01 opt]# wget http://www.pgbouncer.org/downloads/files/1.24.0/pgbouncer-1.24.0.tar.gz
[root@repmgr01 opt]# tar xvf pgbouncer-1.24.0.tar.gz
[root@repmgr01 opt]# cd pgbouncer-1.24.0
[root@repmgr01 pgbouncer-1.24.0]# ./configure
[root@repmgr01 pgbouncer-1.24.0]# make && make install
# 默认PgBouncer是安装到 /usr/local/bin 目录下的

2.配置pgbouncer

2.1 后端数据库创建业务用户

# 后端主库操作
[postgres@postgres-01 data]$ psql
psql (15.5)
Type "help" for help.postgres=# 
create database erpdb;
CREATE USER erpuser WITH PASSWORD 'Erp@123';
ALTER USER erpuser WITH LOGIN;
GRANT CONNECT ON DATABASE erpdb TO erpuser;
\c erpdb
GRANT USAGE ON SCHEMA public TO erpuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO erpuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO erpuser;# 所有数据库配置pg_hba.conf，追加以下内容
[postgres@repmgr01 ~]$ vim /data/pgsql/data/pg_hba.conf
host    erpdb           erpuser         127.0.0.1/32            scram-sha-256
host    erpdb           erpuser         10.0.0.0/24             scram-sha-256# 所有数据库，重新加载配置
[postgres@postgres-01 data]$ psql
psql (15.5)
Type "help" for help.postgres=# select pg_reload_conf();

2.2 编辑pgbouncer配置文件

用于初次部署集群时，启动pgbouncer服务

# 所有节点postgres用户执行
vim /data/pgbouncer/pgbouncer.ini
[databases]
# "postgres=host=localhost..."中的"postgres"表示外部用户连接PgBouncer时的数据库名称
# 这个数据库名称与后端的实际数据库名称可以不同
# PgBouncer → PostgreSQL：使用此处配置的 user/password
erpdb = host=10.0.0.41 port=5432 dbname=erpdb user=erpuser password=Erp@123
[pgbouncer]
admin_users = admin
logfile = /data/pgbouncer/pgbouncer.log
pidfile = /data/pgbouncer/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /data/pgbouncer/userlist.txt
pool_mode = session
# 每个(数据库+用户)组合的后端连接数
# 例如：用户A访问DB1和用户B访问DB1会有不同的连接池
default_pool_size = 20
# 最多允许用户建多少个连接到PgBouncer，示例计算：
# 有3个应用用户访问2个数据库 → 6个用户数据库组合，设 default_pool_size=20
# max_client_conn ≈ 1.2 × (20 × 6) = 144 (可设为150)
max_client_conn = 150
server_idle_timeout = 600

2.3 编辑pgbouncer配置模板

此模板是为了在故障转移时生成pgbouncer配置文件的[pgbouncer]部分

# 所有节点postgres用户执行
[postgres@repmgr01 ~]$ mkdir /data/pgbouncer
[postgres@repmgr01 ~]$ vim /data/pgbouncer/pgbouncer.template
[pgbouncer]
admin_users = admin
logfile = /data/pgbouncer/pgbouncer.log
pidfile = /data/pgbouncer/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /data/pgbouncer/userlist.txt
pool_mode = session
# 最多允许用户建多少个连接到PgBouncer，示例计算：
# 有3个应用用户访问2个数据库 → 6个用户数据库组合，设 default_pool_size=20
# max_client_conn ≈ 1.2 × (20 × 6) = 144 (可设为150)
max_client_conn = 100
# 每个(数据库+用户)组合的后端连接数
# 例如：用户A访问DB1和用户B访问DB1会有不同的连接池
default_pool_size = 20
server_idle_timeout = 600

2.4 配置pgbouncer认证文件

# 所有节点编辑认证文件，admin用户是pgbouncer的管理用户
vim /data/pgbouncer/userlist.txt
"erpuser" "Erp@123"
"admin" "Admin@123"

2.5 启动PgBouncer

#所有节点
# 启动PgBouncer
# "-d" 表示 "daemon" ，也就是让PgBouncer以后台的方式运行
pgbouncer -d /data/pgbouncer/pgbouncer.ini# 验证是否可以通过pgbouncer登录后端pg数据库
[postgres@repmgr01 ~]$ PGPASSWORD="Erp@123" psql -h 10.0.0.41 -p 6432 -U erpuser -d erpdb
psql (15.5)
Type "help" for help.erpdb=> SELECT inet_server_addr() AS backend_host,inet_server_port() AS backend_port,current_database(),current_user;

在这里插入图片描述

3.编辑repmgr触发脚本

#所有节点
vim /data/repmgr/promte_standby_pgbouncer.sh
#!/usr/bin/env bash
set -u
set -o xtrace
#pgbouncer服务的主机列表
PGBOUNCER_HOSTS="10.0.0.41 10.0.0.42 10.0.0.43"
#pgbouncer服务的配置文件位置
PGBOUNCER_DATABASE_INI="/data/pgbouncer/pgbouncer.ini"
#pgbouncer连接的后端数据库的别名
PGBOUNCER_DATABASE="erpdb"
#pgbouncer服务的管理库
PGBOUNCER_DATABASE_ADMIN_DB="pgbouncer"
#pgbouncer服务的管理用户
PGBOUNCER_DATABASE_USER="admin"
PGBOUNCER_DATABASE_PASSWORD="Admin@123"
#pgbouncer服务端口
PGBOUNCER_PORT=6432
#后端postgresql的端口
PORT=5432
#pgbouncer连接的后端数据库名
DBNAME="erpdb"
PG_HOME=/usr/local/pgsql
HOSTNAME=`hostname -i`
REPMGR_DB="repmgr"
REPMGR_USER="repmgr"
REPMGR_PASSWD="repmgr"
REPMGR_CONF="/data/repmgr/repmgr.conf"
STEP1="Promote ${HOSTNAME} from standby to primary"
STEP2="Recreate the pgbouncer config file on node ${HOSTNAME}"
STEP3="Resync the pgbouncer config file"
STEP4="Reload the pgbouncer config file"
PGBOUNCER_DATABASE_INI_NEW="/tmp/pgbouncer.ini"
PGBOUNCER_DATABASE_INI_TEMPLATE='/data/pgbouncer/pgbouncer.template'# STEP1. Promote this node from standby to primary
${PG_HOME}/bin/repmgr standby promote -f ${REPMGR_CONF} --log-to-file
if [ $? -ne 0 ]; thenecho promte_standby_pgbounce.sh: ${STEP1} on ${HOSTNAME} failed !!!  exit 1
fi
#流复制的标志，f表示主库，t表示从库
standby_flg=`PGPASSWORD=${REPMGR_PASSWD} ${PG_HOME}/bin/psql -p ${PORT} -U ${REPMGR_USER} -h localhost -At -c "SELECT pg_is_in_recovery();"`
if [ ${standby_flg} == 'f' ]; thenecho promte_standby_pgbounce.sh: ${STEP1} on ${HOSTNAME} successfully !!!
elif [ ${standby_flg} == 't' ]; thenecho promte_standby_pgbounce.sh: ${STEP1} on ${HOSTNAME} failed !!!exit 1
fi
# STEP2. Reconfigure pgbouncer instances
for HOST in $PGBOUNCER_HOSTS
do# Recreate the pgbouncer config file# 生成pgbouncer配置文件中关于[databases]标签下的内容echo -e "[databases]\n" > $PGBOUNCER_DATABASE_INI_NEW#生成pgbouncer配置文件中关于后端数据库的conninfoPGPASSWORD=${REPMGR_PASSWD} ${PG_HOME}/bin/psql -p ${PORT} -U ${REPMGR_USER} -h localhost -At -c "SELECT '${PGBOUNCER_DATABASE} = '||  split_part(conninfo,' ',1) ||' port=${PORT}'||' dbname=${DBNAME} ' ||' application_name=pgbouncer_${HOST}' FROM repmgr.nodes WHERE active = TRUE AND type='primary'" >> $PGBOUNCER_DATABASE_INI_NEW# 生成pgbouncer配置文件中[pgbouncer]标签下的内容cat $PGBOUNCER_DATABASE_INI_TEMPLATE >> $PGBOUNCER_DATABASE_INI_NEW echo promte_standby_pgbounce.sh: ${STEP2} on ${HOSTNAME} successfully !!!# STEP3. Resync the pgbouncer config filersync $PGBOUNCER_DATABASE_INI_NEW $HOST:$PGBOUNCER_DATABASE_INIif [ $? -ne 0 ]; thenecho promte_standby_pgbounce.sh: ${STEP3} on ${HOSTNAME} failed !!!elseecho promte_standby_pgbounce.sh: ${STEP3} on ${HOSTNAME} successfully !!!                  fi# STEP4. Reload the pgbouncer config file PGPASSWORD=${PGBOUNCER_DATABASE_PASSWORD} ${PG_HOME}/bin/psql -tc "reload" -h $HOST -p $PGBOUNCER_PORT -d ${PGBOUNCER_DATABASE_ADMIN_DB} -U ${PGBOUNCER_DATABASE_USER}if [ $? -ne 0 ]; thenecho promte_standby_pgbounce.sh: ${STEP4} on ${HOSTNAME} failed !!!  elseecho promte_standby_pgbounce.sh: ${STEP4} on ${HOSTNAME} successfully !!!  fi
done# Clean up generated file
rm -rf $PGBOUNCER_DATABASE_INI_NEW
echo "Reconfiguration of pgbouncer complete"

# 授予脚本可执行权限
chmod +x /data/repmgr/promte_standby_pgbouncer.sh

4.修改repmgr配置文件

# 修改promote_command参数，执行我们新建的脚本
vim /data/repmgr/repmgr.conf
promote_command='/data/repmgr/promte_standby_pgbouncer.sh >> /data/repmgr/repmgrd.log'

5.重新启动repmgrd守护进程

kill $(pgrep -f repmgrd)
repmgrd -f /data/repmgr/repmgr.conf --daemonize

6.验证failover

查看当前集群状态

[postgres@repmgr01 ~]$ repmgr -f  /data/repmgr/repmgr.conf cluster show

在这里插入图片描述

查看pgbouncer指向的后端数据库

[postgres@repmgr01 ~]$ PGPASSWORD="Erp@123" psql -p 6432 -U erpuser -h 127.0.0.1 -d erpdb
psql (15.5)
Type "help" for help.erpdb=> SELECT inet_server_addr() AS backend_host,inet_server_port() AS backend_port,current_database(),current_user;

在这里插入图片描述

手动关闭主节点

[postgres@postgres-01 data]$ pg_ctl stop -D $PGDATA
waiting for server to shut down.... done
server stopped

查看关闭主节点后各节点日志

[postgres@repmgr01 ~]$ tail -f /data/repmgr/repmgrd.log

repmgr01节点失去连接

在这里插入图片描述

repmgr02节点，在该节点上执行了触发脚本，成功被提升为主库，并且node-3节点作为从节点

在这里插入图片描述
在这里插入图片描述

repmgr03节点，作为repmgr02节点的STANDBY成功连接

在这里插入图片描述

再次查看pgbouncer指向的后端数据库

[postgres@postgres-01 data]$ PGPASSWORD="Erp@123" psql -p 6432 -U erpuser -h 127.0.0.1 -d erpdb
psql (15.5)
Type "help" for help.erpdb=> SELECT inet_server_addr() AS backend_host,inet_server_port() AS backend_port,current_database(),current_user;

在这里插入图片描述

所有节点的pgbouncer配置文件已被修改为指向新的主节点

[postgres@repmgr01 data]$ cat /data/pgbouncer/pgbouncer.ini | grep "erpdb"
erpdb = host=10.0.0.42 port=5432 dbname=erpdb  application_name=pgbouncer_10.0.0.41[postgres@repmgr02 data]$ cat /data/pgbouncer/pgbouncer.ini | grep "erpdb"
erpdb = host=10.0.0.42 port=5432 dbname=erpdb  application_name=pgbouncer_10.0.0.42[postgres@repmgr03 data]$ cat /data/pgbouncer/pgbouncer.ini | grep "erpdb"
erpdb = host=10.0.0.42 port=5432 dbname=erpdb  application_name=pgbouncer_10.0.0.43

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.pswp.cn/pingmian/89795.shtml
繁体地址，请注明出处：http://hk.pswp.cn/pingmian/89795.shtml
英文地址，请注明出处：http://en.pswp.cn/pingmian/89795.shtml

如若内容造成侵权/违法违规/事实不符，请联系英文站点网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

查找服务器上存在线程泄露的进程

查找服务器上存在线程泄露的进程

以下是一个改进的命令，可以列出所有线程数大于200的进程及其PID和线程数： find /proc -maxdepth 1 -type d -regex /proc/[0-9] -exec sh -c for pid_dir dopid$(basename "$pid_dir")if [ -f "$pid_dir/status" ]; thenthreads$(aw…
阅读更多...
Facebook 开源多季节性时间序列数据预测工具：Prophet 饱和预测 Saturating Forecasts

Facebook 开源多季节性时间序列数据预测工具：Prophet 饱和预测 Saturating Forecasts

文中内容仅限技术学习与代码实践参考，市场存在不确定性，技术分析需谨慎验证，不构成任何投资建议。 Prophet 是一种基于加法模型的时间序列数据预测程序，在该模型中，非线性趋势与年、周、日季节性以及节假日效应相匹配。…
阅读更多...
从单线程到云原生：Redis 二十年演进全景与内在机理深剖

从单线程到云原生：Redis 二十年演进全景与内在机理深剖

——从 1.0 到 7.2，一窥数据结构、网络模型、持久化、复制、高可用与生态协同的底层脉络（一）序章：为什么是 Redis 1999 年，Salvatore Sanfilippo 在开发一个实时访客分析系统时，发现传统磁盘型数据库无法在…
阅读更多...
得了甲亢军队文职体检能过吗

得了甲亢军队文职体检能过吗

根据军队文职体检现行标准，甲亢患者能否通过体检需分情况判定，核心取决于病情控制状态、治疗结果及稳定时长。结合《军队选拔军官和文职人员体检通用标准》及补充规定，具体分析如下：⚕️ 一、可直接通过体检的情况临床治愈满1年且…
阅读更多...
【编程语言】C、C++、C#深度对比：三种语言的演进历程与应用场景

【编程语言】C、C++、C#深度对比：三种语言的演进历程与应用场景

一、语言概述与历史背景 （一）C语言：系统编程的基石诞生背景 1972年由Dennis Ritchie在贝尔实验室开发为了重写UNIX操作系统而创造从B语言演化而来，增加了数据类型设计目标：简洁、高效、可移植设计哲学 “相信程序员”&…
阅读更多...
《计算机网络》实验报告五 DNS协议分析与测量

《计算机网络》实验报告五 DNS协议分析与测量

目 录 1、实验目的 2、实验环境 3、实验内容 3.1 查看和配置本机的DNS系统 3.2 DNS信息测量 3.3 DNS协议分析 4、实验结果与分析 4.1 查看和配置本机的DNS系统 4.2 DNS信息测量 4.3 DNS协议分析 5、实验小结 5.1 问题与解决办法： 5.2 心得体会&#x…
阅读更多...
Python工厂方法模式详解：从理论到实战

Python工厂方法模式详解：从理论到实战

一、工厂方法模式核心概念 工厂方法模式（Factory Method Pattern）是一种创建型设计模式，属于经典23种设计模式之一。其核心思想是：定义一个创建对象的接口，但将具体对象的实例化过程延迟到子类中实现。这种模式通过引入…
阅读更多...
python爬虫获取PDF

python爬虫获取PDF

【前提：菜鸟学习的记录过程，如果有不足之处，还请各位大佬大神们指教（感谢）】 1.方法一：网站找到目标数据【单篇PDF】 https://bidding.sinopec.com/tpfront/xxgg/004005/ 按F12，----检查------…
阅读更多...
IFN影视官网入口 - 4K影视在线看网站|网页|打不开|下载

IFN影视官网入口 - 4K影视在线看网站|网页|打不开|下载

IFN影视是一个专注于影视内容的网站，提供电影、电视剧、综艺等各类影视资源的在线观看服务。该网站以用户需求为导向，致力于为用户提供高清、流畅的观影体验，并不断更新内容以满足不同用户的观看习惯和偏好。IFN影视的特色在于其内容丰富、分…
阅读更多...
《计算机网络》实验报告四 TCP协议分析

《计算机网络》实验报告四 TCP协议分析

目 录 1、实验目的 2、实验环境 3、实验内容 3.1 利用wget下载新疆大学主页 3.2 使用wireshark分析TCP报文结构 3.3 使用wireshark分析建立连接的三次握手 3.4 使用wireshark分析释放连接的四次挥手 4、实验结果与分析 4.1 利用wget下载新疆大学主页 4.2 使用wiresh…
阅读更多...
知识 IP 的突围：从 “靠感觉” 到 “系统 + AI” 的变现跃迁

知识 IP 的突围：从 “靠感觉” 到 “系统 + AI” 的变现跃迁

越来越多的知识付费从业者陷入 “努力无成果” 的困局：做了内容、上了课程，却没人看、没人买。核心问题不在于能力不足，而在于仍在用 “靠感觉” 的原始方式打造 IP。在流量内卷、节奏加快的当下，“内容情怀” 已撑不起一门生意&a…
阅读更多...
4.Java创建对象有几种方式？

4.Java创建对象有几种方式？

1.使用 new 关键字（最常用）通过调用类的构造函数直接实例化对象Person person new Person(); // 调用无参构造 Person person new Person("Alice", 25); // 调用有参构造2.反射机制（动态创建）利用Java反射 API 在运行…
阅读更多...
【好题】洛谷 P1600 [NOIP 2016 提高组] 天天爱跑步（倍增LCA+桶）

【好题】洛谷 P1600 [NOIP 2016 提高组] 天天爱跑步（倍增LCA+桶）

前言没做出来，看了很多篇题解后AC了，感觉大部分题解讲得不清楚。题目思路结果有两种求法模拟跑步过程，统计每个节点能观察到的人数考虑每条路径会对哪些节点作出贡献（当前路径的玩家能被观察到）尝试第一种求法必须遍历…
阅读更多...
valkey之网络管理架构深度解析

valkey之网络管理架构深度解析

一、连接类型实现体系 valkey通过ConnectionType结构体构建了灵活的网络连接抽象，支持多种连接类型的统一管理。每种连接类型都通过填充该结构体的函数指针来实现特定功能，形成了面向接口的设计模式。1.1 socket连接 Socket连接提供了最基础的TCP/IP通信…
阅读更多...
【解码文本世界的“隐形分界线”：Windows与Linux回车换行之谜】

【解码文本世界的“隐形分界线”：Windows与Linux回车换行之谜】

在计算机的文本世界里，回车（Carriage Return，CR）和换行（Line Feed，LF）是两个看似简单却意义非凡的字符。它们如同文本中的“隐形分界线”，默默地划分着段落与行，影响着文…
阅读更多...
【Project】ELK 7.17.16 日志分析系统部署

【Project】ELK 7.17.16 日志分析系统部署

ELK 日志分析系统集群部署 本文档基于 Rocky Linux 9.4 系统，部署 ELK 7.17.16（长期支持版）集群 案例准备 1. 节点规划IP主机名部署组件角色说明192.168.100.150kafka01Elasticsearch、Kibana主节点（master） 可视化192…
阅读更多...
分布式定时任务系列13：死循环是任务触发的银弹？

分布式定时任务系列13：死循环是任务触发的银弹？

传送门 分布式定时任务系列1：XXL-job安装 分布式定时任务系列2：XXL-job使用 分布式定时任务系列3：任务执行引擎设计 分布式定时任务系列4：任务执行引擎设计续 分布式定时任务系列5：XXL-job中blockingQueue的应用 …
阅读更多...
Flutter基础（前端教程①③-单例）

Flutter基础（前端教程①③-单例）

现实类比：公司打印机假设你们公司有一台共享打印机：非单例（重复创建）：每个员工都自己买一台打印机放在工位上结果：浪费钱，占空间，难维护单例（唯一实例）&#…
阅读更多...
力扣刷题 -- 965.单值二叉树

力扣刷题 -- 965.单值二叉树

题目示例： 思路分析代码实现 bool isUnivalTree(struct TreeNode* root) {if(rootNULL){return true;}if(root->left && root->val ! root->left->val){return false;}if(root->right && root->val ! root->right->val){re…
阅读更多...
uni-api交互反馈组件（showToast）的用法

uni-api交互反馈组件（showToast）的用法

欢迎来到我的UniApp技术专栏！🎉 在这里，我将与大家分享关于UniApp开发的实用技巧、最佳实践和项目经验。 专栏特色： 📱 跨平台开发一站式解决方案 🚀 从入门到精通的完整学习路径 💡 实战项目经…
阅读更多...
最新文章

Copyright @ 2022~2023