repmgr+pgbouncer實現對業務透明的高可用切換

本方案說明

  • 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