PostgreSQL流复制之二：pgpool-II实现PostgreSQL数据库集群（转发+整理）
轉發來源：
PostgreSQL的集群技術比較：https://iwin.iteye.com/blog/2108807
參考：https://blog.csdn.net/yaoqiancuo3276/article/details/80983201
參考：https://blog.csdn.net/yaoqiancuo3276/article/details/80805783
參考：https://my.oschina.net/u/3308173/blog/900093?nocache=1494825611692
參考：http://www.pgpool.net/docs/latest/en/html/example-cluster.html
?
pgpool-II實現PostgreSQL數據庫集群
以三臺服務器，部署三個postgreSQl數據，一Master、2個Standby。在三臺服務器上部署pgpool來管理數據庫集群為例。
?一、數據規劃：
PostgreSQL庫的IP/Port規劃：
| 主庫地址/端口 | 10.10.10.1? /? 5432 |
| 備庫2地址/端口 | 10.10.10.2? /? 5432 |
| 備庫3地址/端口 | 10.10.10.3? /? 5432 |
?
Pgpool-II的IP/Port規劃：
| Pgpool-II使用的端口 | 9999 |
| Pgpool-II管理的數據庫浮動IP | 10.10.10.101 |
| Pgpool-II看門狗心跳端口 | 9694 |
| pgpool-II 服務器上的需要被監控的看門狗的端口 | 9000 |
?
注：由于安全原因，我們創建了一個單獨用于復制目的的用戶（u_standby），以及一個用于Pgpool-II流復制延遲檢查和健康檢查的用戶（pgcheck）。
| 1、postgreSQL數據庫用戶名/密碼 2、執行恢復的用戶（恢復：主庫故障后切換，原主庫恢復后變更為備庫）User running online recovery | Postgre? /? 123456 |
| postgreSQL備、主庫流復制用戶名/密碼 | u_standby? /? standby123 |
| Pgpool-II監看健康檢查用戶/密碼（需在數據庫開戶，具備查詢權限） | pgcheck? /? 123456 |
?
二、安裝配置pgpool-II
步驟一：已經搭建好數據庫，并配置流復制
搭建過程見：PostgreSQL流復制之一：原理+環境搭建
?
步驟二：主、備庫安裝pgpool-II
1、 主庫上安裝pgpool-II工具（采用root權限用戶）
下載—>解壓，在解壓目錄下：make編譯—>make install安裝
2、 安裝pgpool-II的擴展函數（使用數據庫postgres用戶）
（1）???? 安裝pgpool_regclass擴展函數
pgpool-II解壓目錄/src/sql 下執行 make—>make install
pgpool-II解壓目錄/src/sql/pgpool-regclass 下執行：
psql -p 5433 -f pgpool-regclass.sql template1
?
?
?（2）???? 建立insert_lock表
pgpool-II解壓目錄/src/sql下執行：
psql -p 5433 -f insert_lock.sql template1
（3）???? 安裝pgpool_recovery擴展函數
pgpool-II解壓目錄/src/sql/pgpool-recovery下執行make install
pgpool-II解壓目錄/src/sql/pgpool-recovery下執行：
psql -p 5433 -f pgpool-recovery.sql template1
3、 在2個備庫上安裝pgpool-II工具。（同主庫操作）
下載—>解壓，在解壓目錄下：make編譯—>make install安裝
4、 檢查2個備庫已具備pgpool-II擴展函數。（注：擴展函數在數據庫中，數據庫的主備流復制已經復制到備庫，不需要重復安裝）
postgres=# select * from pg_extension;
extname???? | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-----------------+----------+--------------+----------------+------------+-----------+--------------
?plpgsql???????? |?????? 10 |?????????? 11 | f????????????? | 1.0??????? |?????????? |
?pgpool_regclass |?????? 10 |???????? 2200 | t????????????? | 1.0?? ?????|?????????? |
?pgpool_recovery |?????? 10 |???????? 2200 | t????????????? | 1.1??????? |?????????? |
(3 rows)
?
步驟三：主庫配置pgpool-II
pgpool有四個主要的配置文件，分別是
- pcp.conf 用于管理查、看節點信息，如加入新節點。該文件主要是存儲用戶名及md5形式的密碼。
- pgpool.conf 用于設置pgpool的模式，主次數據庫的相關信息等。
- pool_hba.conf 用于認證用戶登錄方式，如客戶端IP限制等，類似于postgresql的pg_hba.conf文件。
- pool_passwd 用于保存相應客戶端登錄帳號名及md5密碼。???
1、 配置pgpool.conf
listen_addresses = '*'? # rtm用于pgpool監聽地址，控制哪些地址可以通過pgpool 連接,`*`表示接受所有連接
port = 9999??? # rtm?? pgpool 監聽的端口
pcp_listen_addresses = '*' ?# rtm
pcp_port = 9898???????? # rtm
backend_hostname0 = '10.10.10.1' ?# rtm? 配置后端postgreSQL 數據庫地址，此處為主庫
backend_port0 = 5432????????????? # rtm 后端postgreSQL 數據庫端口
backend_weight0 = 1?????????????? # rtm 權重，用于負載均衡
backend_data_directory0 = '/pgdata/ha/masterdata' # rtm 后端postgreSQL 數據庫實例目錄
backend_flag0 = 'ALLOW_TO_FAILOVER'?? # rtm? 允許故障自動切換
backend_hostname1 = '10.10.10.2'????? # rtm 此處為備庫1數據庫地址
backend_port1 = 5432??????????????????? # rtm
backend_weight1 = 1? ???????????????????# rtm
backend_data_directory1 = '/pgdata/ha/slavedata'??????? # rtm
backend_flag1 = 'ALLOW_TO_FAILOVER'???? # rtm
backend_hostname2 = '10.10.10.3'????? # rtm 此處為備庫2數據庫地址
backend_port2 = 5432??????????????????? # rtm
backend_weight2 = 1???????????????????? # rtm
backend_data_directory2 = '/pgdata/ha/slavedata'??????? # rtm
backend_flag2 = 'ALLOW_TO_FAILOVER'???? # rtm
enable_pool_hba = on??????????? # rtm? 開啟pgpool認證，需要通過 `pool_passwd` 文件對連接到數據庫的用戶進行md5認證
pool_passwd = 'pool_passwd' ????# rtm 認證文件
log_destination = 'stderr,syslog'? # rtm? 日志級別，標注錯誤輸出和系統日志級別
log_line_prefix = '%t: pid %p: '??? # rtm? 日志輸出格式
log_connections = on?????????????? # rtm? 開啟日志
log_hostname = on????????????????? # rtm? 打印主機名稱
#log_statement = all???????????? # rtm??? 取消注釋則打印sql 語句
#log_per_node_statement = on???? # rtm??? 取消注釋則開啟打印sql負載均衡日志，記錄sql負載到每個節點的執行情況
#client_min_messages = log? # rtm????????? 日志
#log_min_messages = info?? # rtm??????????? # 日志級別
pid_file_name = '/opt/pgpool-3/run/pgpool/pgpool.pid'?? # rtm pgpool的運行目錄，若不存在則先創建
logdir = '/opt/pgpool-3/log/pgpool'? # rtm? 指定日志輸出的目錄
replication_mode = off?????????? # rtm?? 關閉pgpool的復制模式
load_balance_mode = on???????????? # rtm? 開啟負載均衡
master_slave_mode = on?????????? # rtm?? 開啟主從模式
master_slave_sub_mode = 'stream'???????? # rtm設置主從為流復制模式
sr_check_period = 10???????????? # rtm??? 流復制的延遲檢測的時間間隔
sr_check_user = 'pgcheck'??????? # rtm??? Specifiy replication delay check user and password，該用戶需要在pg數據庫中存在，且擁有查詢權限
sr_check_password = '123456'?????? # rtm? Pgpool-II 4.0開始，如果這些參數為空，Pgpool-II將首先嘗試從sr_check_password文件中獲取指定用戶的密碼
sr_check_database = 'postgres'?????????? # rtm? 流復制檢查的數據庫名稱
delay_threshold = 10000000???????????? # rtm? 設置允許主備流復制最大延遲字節數,單位為kb
health_check_period = 10????????? # rtm? pg數據庫檢查檢查間隔時間
health_check_timeout = 20??????? # rtm
health_check_user = 'pgcheck'?????????? # rtm?? 健康檢查用戶，需pg數據庫中存在
health_check_password = '123456'??????? # rtm?? 設置方法同sr_check_password
health_check_database = 'postgres'????? # rtm?? 健康檢查的數據庫名稱
health_check_max_retries = 3??????????? # rtm?? 健康檢查最大重試次數
health_check_retry_delay = 3??????????? # rtm? 重試次數間隔
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R'? # rtm 在failover_command參數中指定failover后需要執行的failover.sh腳本
follow_master_command = '/etc/pgpool-II/follow_master.sh %d %h %p %D %m %M %H %P %r %R'??? # rtm如果使用3臺PostgreSQL服務器，需要在主節點切換后指定follow_master_command運行，如果是兩PostgreSQL服務器，則不需要設置 follow_master_command。
fail_over_on_backend_error = off? # rtm??? 如果設置了health_check_max_retries次數，則關閉該參數
use_watchdog = on?????????????????? # rtm? 開啟看門狗，用于監控pgpool 集群健康狀態
wd_hostname = '10.10.10.1'???????????? # rtm? ?本地看門狗地址，配置為當前庫的IP
wd_port = 9000????????????????????????? # rtm
wd_priority = 1???????????????????????? # rtm? 看門狗優先級，用于pgpool 集群中master選舉
delegate_IP = '10.10.10.101'???????????? # rtm?? 在三個庫上指定接受客戶端連接的虛擬IP地址。
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'? # rtm 配置虛擬IP到本地網卡
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'????????? # rtm?
wd_lifecheck_method = 'heartbeat'???????? # rtm? 看門狗健康檢測方法
wd_heartbeat_port = 9694??????????????? # rtm??? 看門狗心跳端口，用于pgpool 集群健康狀態通信
wd_heartbeat_keepalive = 2????????????? # rtm??? 看門狗心跳檢測間隔
wd_heartbeat_deadtime = 30????????????? # rtm
heartbeat_destination0 = '10.10.10.2'? # rtm??? 配置需要監測健康心跳的IP地址，非本地地址，即互相監控，配置對端的IP地址
heartbeat_destination_port0 = 9694????? # rtm 監聽的端口
heartbeat_device0 = 'eth0'????????????? # rtm 監聽的網卡名稱
heartbeat_destination1 = '10.10.10.3'? # rtm??? 配置需要監測健康心跳的IP地址，非本地地址，即互相監控，配置對端的IP地址
heartbeat_destination_port1 = 9694????? # rtm 監聽的端口
heartbeat_device1 = 'eth0'?????? # rtm
wd_life_point = 3?????????????? # rtm?? 生命檢測失敗后重試次數
wd_lifecheck_query = 'SELECT 1' # rtm? 用于檢查 pgpool-II 的查詢語句。默認為“SELECT 1”。
wd_lifecheck_dbname = 'postgres'??????? # rtm 檢查健康狀態的數據庫名稱
wd_lifecheck_user = 'pgcheck'?????????? # rtm 檢查數據庫的用戶，該用戶需要在Postgres數據庫存在，且有查詢權限
wd_lifecheck_password = '123456'??????? # rtm? 看門狗健康檢查用戶密碼
other_pgpool_hostname0 = '10.10.10.2'? # rtm 指定被監控的 pgpool-II 服務器的主機名
other_pgpool_port0 = 9999?????? # rtm 指定被監控的 pgpool-II 服務器的端口號
other_wd_port0 = 9000?????????? # rtm 指定 pgpool-II 服務器上的需要被監控的看門狗的端口號
other_pgpool_hostname1 = '10.10.10.3'? # rtm 指定被監控的 pgpool-II 服務器的主機名
other_pgpool_port0 = 9999?????? # rtm 指定被監控的 pgpool-II 服務器的端口號
other_wd_port0 = 9000?????????? # rtm 指定 pgpool-II 服務器上的需要被監控的看門狗的端口號
2、 配置pool_passwd，該文件用于配置哪些用戶可以訪問pgpool。使用如下命令生成：
pg_md5 -p -m -u postgres ?pool_passwd? //執行后輸入密碼
通過如上命令，設置數據庫的postgre用戶名和密碼，及pgpool進行健康檢查的pgcheck用戶名和密碼。
3、 配置.pgpass。使用pgpool-II進行故障庫自動切換（failover）、或在線恢復（online recovery）（在線恢復：主庫故障后切換，原主庫恢復后變更為備庫。注意是Online?recovery，而不是自動恢復，需要手工執行命令恢復），需要能夠無密碼SSH訪問其他postgreSQL服務器。為了滿足此條件，我們需要在每個postgreSQL服務器上，在postgres用戶的home file下創建了.pgpass文件，并修改器文件權限為600。
[all servers]# su - postgres
[all servers]$ vi /var/lib/pgsql/.pgpass
10.10.10.1:5432:replication:repl:<repl user password>
10.10.10.2:5432:replication:repl:<repl user passowrd>
10.10.10.3:5432:replication:repl:<repl user passowrd>
[all servers]$ chmod 600? /var/lib/pgsql/.pgpass
4、 配置pcp.conf。由于pcp命令需要用戶認證，該在文件中指定配置pcp命令的pgpool用戶的用戶名、密碼。格式：
用戶名：密碼MD5編碼
?????? 注：密碼的MD5編碼看通過“pg_md5” 密碼命令生成
5、 配置pcp的.pcppass。需要follow_master_command腳本情況下，由于此腳本必須在不輸入密碼的情況下執行pcp命令，所以我們在Pgpool-II用戶（root用戶）的home directory下創建.pcppass：
# echo 'localhost:9898:pgpool:pgpool' > ~/.pcppass
# chmod 600 ~/.pcppass
6、 配置pool_hba.conf認證文件，類似于PostgreSQL的pg_hba.conf文件。
?
步驟四：備庫配置pgpool-II
類同主庫配置pgpool-II步驟：
1、 配置pgpool.conf。類同主庫pgpool-II配置。以備庫1配置pgpool.conf為例：修改配置中主庫、備庫2的IP：
use_watchdog = on?????????????????? # rtm? 開啟看門狗，用于監控pgpool 集群健康狀態
wd_hostname = '10.10.10.2'? ? ? # rtm? ?本地看門狗地址，配置為當前庫地址
wd_port = 9000????????????????????????? # rtm
wd_priority = 1???????????????????????? # rtm? 看門狗優先級，用于pgpool 集群中master選舉
?
wd_lifecheck_method = 'heartbeat'???????? # rtm? 看門狗健康檢測方法
wd_heartbeat_port = 9694??????????????? # rtm??? 看門狗心跳端口，用于pgpool 集群健康狀態通信
wd_heartbeat_keepalive = 2????????????? # rtm ???看門狗心跳檢測間隔
wd_heartbeat_deadtime = 30????????????? # rtm
heartbeat_destination0 = '10.10.10.1'? # rtm??? 配置需要監測健康心跳的IP地址，非本地地址，即互相監控，配置對端的IP地址
heartbeat_destination_port0 = 9694????? # rtm 監聽的端口
heartbeat_device0 = 'eth0'????????????? # rtm 監聽的網卡名稱
heartbeat_destination1 = '10.10.10.3'? # rtm??? 配置需要監測健康心跳的IP地址，非本地地址，即互相監控，配置對端的IP地址
?
other_pgpool_hostname0 = '10.10.10.1'? # rtm 指定被監控的 pgpool-II 服務器的主機名
other_pgpool_port0 = 9999?????? # rtm 指定被監控的 pgpool-II 服務器的端口號
other_wd_port0 = 9000?????????? # rtm 指定 pgpool-II 服務器上的需要被監控的看門狗的端口號
other_pgpool_hostname1 = '10.10.10.3'? # rtm 指定被監控的 pgpool-II 服務器的主機名
other_pgpool_port0 = 9999?????? # rtm 指定被監控的 pgpool-II 服務器的端口號
other_wd_port0 = 9000?????????? # rtm 指定 pgpool-II 服務器上的需要被監控的看門狗的端口號
2、 其他部署類同主庫配置。
?
步驟五：啟動pgpool-II
注：
- 啟動pgpool-II前，必須先啟動PostgreSQL；同理，停止PostgreSQL前，必須先停止pgpool-II.
- 上面步驟中，配置的三個庫的優先級一致（backend_weight）。所以Pgpool-II節點角色取決于Pgpool-II啟動的先后順序
分別在主庫、備庫1、備庫2執行如下命令啟動pgpool-II：??pgpool -n -d > pgpool.log 2>&1 &
1、 pgpool-II先啟動的庫，如下打印
顯示角色是主庫。看門狗向另外兩個庫發送心跳，也接收另外另個庫的響應。
DEBUG:? STATE MACHINE INVOKED WITH EVENT = STATE CHANGED Current State = MASTER
DEBUG:? watchdog heartbeat: send heartbeat signal to 10.10.10.2:9694
DEBUG:? watchdog heartbeat: send heartbeat signal to 10.10.10.3:9694
DEBUG:? received heartbeat signal from? 。。。。
2、 pgpool-II后啟動的庫，如下打印
顯示角色是備庫。看門狗向另外兩個庫發送心跳，也接收另外另個庫的響應。
DEBUG:? STATE MACHINE INVOKED WITH EVENT = PACKET RECEIVED Current State = STANDBY
? DEBUG:? watchdog heartbeat: send heartbeat signal to 10.10.10.1:9694
DEBUG:? watchdog heartbeat: send heartbeat signal to 10.10.10.3:9694
DEBUG:? received heartbeat signal from "10.10.10.1(10.10.10.1):9999" node:10.10.10.1:9999 Linux CLWDB3
DEBUG:? received heartbeat signal from "10.10.10.3(10.10.10.3):9999" node:Not_Set
3、 查看VIP
在主庫上執行：#ip addr，看到10.10.10.101虛擬IP綁定在主庫的eth0上。
?
步驟六：查看pgpool集群狀態
1、 通過pgpool命令查看pgpool中庫狀態（IP為pgpool的VIP）
執行：psql -h 10.10.56.87 -p 9999 -U postgres pgpool
?
其他命令：
查看pgpool配置：# show pool_status;
查看pgpool連接池：# show pool_pools;
2、 通過pcp管理pgpool。Pcp是管理pgpool的linux命令。
（1）???? 查看pgpool集群狀態（IP為pgpool的VIP）
# pcp_watchdog_info -h 10.10.10.101 -p 9898 -U pgcheck -v
可以看到集群Node的IP、Port、狀態，虛擬IP綁定的Node等信息。
（2）???? 查看pgpool集群Node數量（IP為pgpool的VIP）
# pcp_node_count -h 10.10.10.101 -p 9898 -U pgcheck -v
（3）???? 查看pgpool集群配置（IP為pgpool的VIP）
# pcp_pool_status -h 10.10.10.101 -p 9898 -U pgcheck -v
（4）???? 查看pgpool processer進程狀態連接池（IP為pgpool的VIP）
#pcp_proc_count -h 10.10.10.101 -p 9898 -U pgcheck -v
?
步驟七：配置主庫故障自動切換腳本
PostgreSQL流復制，支持故障情況下兩種方式切換：
- recovery.conf配置文件中指定trigger_file（trigger_file = '/home/postgres/pg11/trigger'），且trigger_file在備庫中存在，則主庫故障自動切換。
- 在備庫上通過pg_ctl promote命令使備庫升主。
?在pgpool-II工具場景下，pgpool-II的pgpool.conf配置文件中支持指定故障切換腳本。尤其是多庫場景下，此方式可根據用戶需要自定義切換規則，在腳本中通過pg_ctl promote命令方式觸發指定的備庫升主：
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R'? # rtm 在failover_command參數中指定failover后需要執行的failover.sh腳本
? ? failover.sh樣例見附錄，部分邏輯如下：
? ? ? follow_master_command樣例見附錄，部分邏輯如下：
?
切換過程觀察：
1） 切換前狀態
# pcp_watchdog_info -h 10.10.10.101 -p 9898 -U pgcheck –v
Host Name????? : 10.10.10.1
Status Name??? : MASTER
?
Host Name????? : 10.10.10.2
Status Name??? : STANDBY
?
Host Name????? : 10.10.10.3
Status Name??? : STANDBY
2） ?? 停止主庫?？梢詓top Pgpool-II service或shutdown整個主庫
systemctl stop pgpool.service
3） ? 切換后組狀態：
# pcp_watchdog_info -h 10.10.10.101 -p 9898 -U pgcheck –v
Host Name????? : 10.10.10.1
Status Name??? : STANDBY
?
Host Name????? : 10.10.10.2
Status Name??? : MASTER
?
Host Name????? : 10.10.10.3
Status Name??? : STANDBY
?
步驟八：配置故障庫恢復后變更為備庫（online recovery）
我們期望原主庫故障后，恢復后能自動變更為備庫，需要：
- 安裝pgpool_recovery、pgpool_remote_start、pgpool_switch_xlog擴展函數，方法參考安裝步驟。
- 確保原主庫recovery_1st_stage和pgpool_remote_start。
1）?? 配置pgpool.conf文件
在線恢復需要PostgreSQL的超級組權限，所以指定postgres用戶為recovery_user。
recovery_user = 'postgres'??? # Online recovery user
recovery_password = ''??? # Online recovery password
recovery_1st_stage_command = 'recovery_1st_stage'
2）?? 在原主庫服務器 (10.10.10.1) 的數據庫集群目錄下創建recovery_1st_stage和pgpool_remote_start，并添加可執行權限。詳細腳本參考附錄。
# su - postgres
$ vi /var/lib/pgsql/11/data/recovery_1st_stage
$ vi /var/lib/pgsql/11/data/pgpool_remote_start
$ chmod +x /var/lib/pgsql/11/data/{recovery_1st_stage,pgpool_remote_start}
? ? ? ?recovery_1st_stage腳本內容見附件，部分邏輯如下：
3）?? 使用pcp命令執行恢復備庫節點（如下地址為數據庫集群的虛擬地址）
# pcp_recovery_node -h 10.10.10.101 -p 9898 -U pgpool -n 0
Password:
pcp_recovery_node -- Command Successful
?
附1：failover.sh、fialover_master.sh、recovery_1st_stag、pgpool_remote_start件樣例參考：http://www.pgpool.net/docs/latest/en/html/example-cluster.html
轉載于:https://www.cnblogs.com/yickel/p/11161915.html
總結
以上是生活随笔為你收集整理的PostgreSQL流复制之二：pgpool-II实现PostgreSQL数据库集群（转发+整理）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 转：csdn怎么快速转载别人的文章
- 下一篇： pilot