Postgressql高可用（pgpool+非同步流複製）

  • 2019 年 10 月 7 日
  • 筆記

文檔結構：

 

 

 

由於部落客之前是Oracle dba,oracle 高可用一般是rac,以及搭建ADG,一個是基於實例的高可用，一個是基於數據的容災，甚至也有rac+adg的方式。Postgres有同步和非同步兩種同步方式，我發現，mysql,oracle,postgresql 這兩種同步模式原理都是一個德行，可能都是繞不開關係型資料庫吧。

非同步方式主庫上的事務不會等待備庫接受日誌流發出確認資訊後主庫才向客戶端返回成功，同步方式事務會主庫等待至少一個備庫接受日誌流發出確認資訊後便返回成功；很明顯，非同步時間會有延遲，同步會增加相應時間，但是保證了數據的一致性，在資源允許的情況下，可以一主多從且採取一個備庫同步，多個備庫非同步方式。

   除了pgpool非同步高可用，還可以採用keepalived方式，本次就簡單實驗pgpool非同步方式。

什麼是pgpool-Ⅱ

Pgpool-II是一個在PostgreSQL伺服器和PostgreSQL資料庫客戶端之間工作的中間件。它是根據BSD許可證授權的。它提供以下功能。

 

連接池

Pgpool-II保存與PostgreSQL伺服器的連接，並在具有相同屬性（即用戶名，資料庫，協議版本）的新連接進入時重用它們。它減少了連接開銷，並提高了系統的整體吞吐量。

複製

Pgpool-II可以管理多個PostgreSQL伺服器。使用複製功能可以在2個或更多物理磁碟上創建實時備份，以便在磁碟發生故障時服務可以繼續運行而不會停止伺服器。

負載均衡

如果複製了資料庫，則在任何伺服器上執行SELECT查詢都將返回相同的結果。Pgpool-II利用複製功能，通過在多個伺服器之間分配SELECT查詢來減少每個PostgreSQL伺服器的負載，從而提高系統的整體吞吐量。充其量，性能與PostgreSQL伺服器的數量成比例地提高。在許多用戶同時執行許多查詢的情況下，負載平衡最有效。

限制超出連接

PostgreSQL的最大並發連接數有限制，連接在這麼多連接後被拒絕。但是，設置最大連接數會增加資源消耗並影響系統性能。pgpool-II對最大連接數也有限制，但額外連接將排隊，而不是立即返回錯誤。

看家狗

Watchdog可以協調多個Pgpool-II，創建一個強大的集群系統，避免單點故障或腦裂。看門狗可以對其他pgpool-II節點執行生命檢查，以檢測Pgpoll-II的故障。如果活動Pgpool-II發生故障，則可以將備用Pgpool-II提升為活動狀態，並接管虛擬IP。

在記憶體查詢快取中

在記憶體中查詢快取允許保存一對SELECT語句及其結果。如果有相同的SELECT，Pgpool-II將從快取中返回值。由於不涉及SQL解析或訪問PostgreSQL，因此在記憶體快取中使用速度非常快。另一方面，在某些情況下，它可能比正常路徑慢，因為它增加了存儲快取數據的一些開銷。

Pgpool-II講PostgreSQL的後端和前端協議，並在後端和前端之間傳遞消息。因此，資料庫應用程式（前端）認為Pgpool-II是實際的PostgreSQL伺服器，伺服器（後端）將Pgpool-II視為其客戶端之一。因為Pgpool-II對伺服器和客戶端都是透明的，所以現有的資料庫應用程式可以與Pgpool-II一起使用。Pgpool-II講述PostgreSQL的後端和前端協議，並在它們之間傳遞連接。因此，資料庫應用程式（前端）認為Pgpool-II是實際的PostgreSQL伺服器，伺服器（後端）將Pgpool-II視為其客戶端之一。因為Pgpool-II對伺服器和客戶端都是透明的，所以現有的資料庫應用程式可以與Pgpool-II一起使用，幾乎不需要更改其源碼。

以下測試主要來自譚峰（francs）老師的《postgres 實戰》

一．Pgpool-Ⅱ的安裝

主機名

說明

IP地址

埠

版本

10pg1

主庫

192.168.10.41

5432

Postgres10.8

pgpool主

5555

 

VIP

192.168.10.61

10pg2

備庫

192.168.10.51

5433

Postgres10.8

pgpool備

5555

 

 

其中，應用連接vip,防火牆建議關閉，同時，生產上肯定不是postgres這庫了，肯定時業務庫了，這個按照實際情況配置。

 

1.   下載地址

https://www.pgpool.net/mediawiki/index.php/Downloads

我是採用rpm的最新版本4.0.6。

 

2.   部署（主備）

yum -y install libmemcached

export PATH=$PATH:/usr/local/pgsql/bin/

mkdir /pgpool

./configure –prefix=/pgpool –with-pgsql=/pgpool/

make

make install

 

安裝生成的配置文件在/pgpool/ 目錄下。

 

 

 

注意如果提示：

configure: error: libpq is not installed or libpq is old

如果配置了環境變數（PATH,PGHOME,PGDATA）還是出現以上錯誤，建議使用

./configure –prefix=/pgpool

make

make install

並把pgpool配置到環境變數中（root,postgres用戶下）

 

1.   配置pgpool主備伺服器互信（非root設置）

我用postgres 執行故障轉移腳本，所以進行互信設置把

 

vi /etc/hosts

 

192.168.10.41  10pg1

192.168.10.52  10pg2

 

ssh-keygen 

 

 

 

ssh-copy-id postgres@10pg2 

 

 

 

 

 

 

同理在備庫上執行

ssh-keygen

ssh-copy-id postgres@10pg1

測試：

 ssh postgres@10pg2

 

 

 

ssh postgres@10pg1

 

 

 

 

1.   配置pool_hba.conf連接配置文件

 

Postgres針對連接策略需要配置pg_hba.conf文件，而pgpool連接中由於應用程式是線連接pgpool,然後通過pgpool再連接到後端資料庫，因此也需要在pgpool層面進行連接配置。

Pgpool的配置跟pg_hba.conf一樣的。

 

cd /pgpool/etc/

cp pool_hba.conf.sample pool_hba.conf

vi pool_hba.conf

加上192.168.10.0 這個網段的ip 都可以的訪問

host    all         all         0.0.0.0/0      md5

1.   配置密碼配置文件

默認文件是不存在的，生成pool_passwd配置文件

pg_md5 命令生成

 

 

 

pg_md5 -u postgres -m 密碼

或者 手工建一個文件，怕密碼泄露

select rolpassword from pg_authid where rolname=’postgres’;

 

 

 

vi pool_passwd

 

postgres:md53175bce1d3201d16594cebf9d7eb3f9d

rep:md5df2c887bcb2c49b903aa33bdbc5c2984

 

 

 

 

1.   配置pgpool.conf配置文件

cp /pgpool/etc/pgpool.conf.sample-stream /pgpool/etc/pgpool.conf

vi /pgpool/etc/pgpool.conf

需要修改的（由於參數太多直接寫結果吧）：

主庫參數配置：

 

port = 5555

listen_addresses = ‘*’          #表示監聽所有地址連接（跟postgres參數一樣的意思）

backend_hostname0 = ‘192.168.10.41’   #配置節點0的hostname

backend_port0 = 5432          #主庫埠

backend_weight0 = 1           #沒有開啟模式參數配不配之都可以

backend_data_directory0 = ‘/pgsql/pg_data’  #節點0數據目錄

backend_flag0 = ‘ALLOW_TO_FAILOVER’   #主庫是否允許故障轉移

backend_hostname1 = ‘192.168.10.51’

backend_port1 = 5433

backend_weight1 = 1

backend_data_directory1 = ‘/pgsql/pg_data’

backend_flag1 = ‘ALLOW_TO_FAILOVER’

 

enable_pool_hba = on  #表示啟用pool_hba.conf

pool_passwd = ‘pool_passwd’   #設置MD5認證的密碼文件

 

log_destination = ‘syslog’  #日誌

pid_file_name = ‘/pgpool/pgpool.pid’

 

 

load_balance_mode = off  #關閉負載均衡（如果開啟，select語句會在備庫執行）

 

master_slave_mode = on

master_slave_sub_mode = ‘stream’

sr_check_period = 10  #流複製檢查10s

sr_check_user = ‘rep’ #我同步採用的rep 用戶

sr_check_password = ‘rep’

sr_check_database = ‘postgres’  #流複製檢查連接的資料庫

delay_threshold = 10000000 這個是備庫延遲wal/xlog日誌大於10000000位元組，將不會select語句分發到備庫

 

health_check_period= 5

health_check_timeout = 20

health_check_user = ‘rep’

health_check_password = ‘rep’

health_check_database = ‘postgres’

health_check_max_retries = 3

health_check_retry_delay = 3

 

failover_command = ‘/pgpool/etc/failover_stream.sh %d %P %H %R’

 

use_watchdog = on #啟用watchdog

wd_hostname = ‘192.168.10.41’

wd_port = 9000

wd_priority = 1 ##表示watchdog的優先順序，級別越高則被選為主節點，一主一從設置一樣

 

delegate_IP = ‘192.168.10.61’ #設置的VIP

if_cmd_path = ‘/sbin’

if_up_cmd = ‘ip addr add $_IP_$/24 dev eth0 label eth0:0’ 我的環境是eth0

if_down_cmd = ‘ip addr del $_IP_$/24 dev eth0’

 

heartbeat_destination0 = ‘192.168.10.51’

heartbeat_destination_port0 = 9694

heartbeat_device0 = ‘eth0’

 

wd_life_point = 3 #當探測pgpool節點失敗後設置重試次數

wd_lifecheck_query = ‘SELECT 1’

wd_lifecheck_dbname = ‘postgres’

wd_lifecheck_user = ‘rep’

wd_lifecheck_password = ‘rep’

 

other_pgpool_hostname0 = ’10pg2′  #設置遠程pgpool節點主機

other_pgpool_port0 = 5555  #設置遠程pgpool埠號

other_wd_port0 = 9000     #設置遠程pgpool節點watchdog埠號

 

從庫參數配置：

 

port = 5555

listen_addresses = ‘*’          #表示監聽所有地址連接（跟postgres參數一樣的意思）

backend_hostname0 = ‘192.168.10.41’   #配置節點0的hostname

backend_port0 = 5432        #埠

backend_weight0 = 1           #沒有開啟模式參數配不配之都可以

backend_data_directory0 = ‘/pgsql/data’  #節點0數據目錄

backend_flag0 = ‘ALLOW_TO_FAILOVER’   #節點0是否允許故障轉移

backend_hostname1 = ‘192.168.10.51’     #節點1的主機名

backend_port1 = 5433      #節點1的埠

backend_weight1 = 1           #沒有開啟模式參數配不配之都可以

backend_data_directory1 = ‘/pgsql/data’  #節點1數據目錄

backend_flag1= ‘ALLOW_TO_FAILOVER’   #節點1是否允許故障轉移

 

enable_pool_hba = on  #表示啟用pool_hba.conf

pool_passwd = ‘pool_passwd’   #設置MD5認證的密碼文件

 

log_destination = ‘syslog’  #日誌

pid_file_name = ‘/pgpool/pgpool.pid’

 

 

load_balance_mode = off  #關閉負載均衡（如果開啟，select語句會在備庫執行）

 

master_slave_mode = on

master_slave_sub_mode = ‘stream’

sr_check_period = 10  #流複製檢查10s

sr_check_user = ‘rep’ #我同步採用的rep 用戶

sr_check_password = ‘rep’

sr_check_database = ‘postgres’  #流複製檢查連接的資料庫

delay_threshold = 10000000 這個是備庫延遲wal/xlog日誌大於10000000位元組，將不會select語句分發到備庫

 

health_check_period= 5

health_check_timeout = 20

health_check_user = ‘rep’

health_check_password = ‘rep’

health_check_database = ‘postgres’

health_check_max_retries = 3

health_check_retry_delay = 3

 

failover_command = ‘/pgpool/etc/failover_stream.sh’

 

use_watchdog = on #啟用watchdog

wd_hostname = ’10pg2′

wd_port = 9000

wd_priority = 1  ##表示watchdog的優先順序，級別越高則被選為主節點，一主一從設置一樣

 

delegate_IP = ‘192.168.10.61’ #設置的VIP

if_cmd_path = ‘/sbin’

if_up_cmd = ‘ip addr add $_IP_$/24 dev eth0 label eth0:0’ 我的環境是eth0

if_down_cmd = ‘ip addr del $_IP_$/24 dev eth0’

 

heartbeat_destination0 = ‘192.168.10.41’

heartbeat_destination_port0 = 9694

heartbeat_device0 = ‘eth0’

 

wd_life_point = 3  #當探測pgpool節點失敗後設置重試次數

wd_lifecheck_query = ‘SELECT 1’

wd_lifecheck_dbname = ‘postgres’

wd_lifecheck_user = ‘rep’

wd_lifecheck_password = ‘rep’

 

other_pgpool_hostname0 = ‘192.168.10.41’  #設置遠程pgpool節點主機

other_pgpool_port0 = 5555  #設置遠程pgpool埠號

other_wd_port0 = 9000     #設置遠程pgpool節點watchdog埠號

 

 

1.  配置failover_stream.sh腳本

cat /pgpool/etc/failover_stream.sh

 

#! /bin/bash

# Executes this command after master failover

# Special values:

#   %d = node id

#   %h = host name

#! /bin/bash

# Executes this command after master failover

# Special values:

#   %d = node id

#   %h = host name

#   %p = port number

#   %D = database cluster path

#   %m = new master node id

#   %H = hostname of the new master node

#   %M = old master node id

#   %P = old primary node id

#   %r = new master port number

#   %R = new master database cluster path

#   %% = ‘%’ character

falling_node=$1

old_primary=$2

new_primary=$3

pgdata=$4

pghome=/usr/local/pgsql/

log=/pgpool/failover.log

date >> $log

 

#輸出變數到日誌，方便此腳本出現異常時調試

echo “falling_node=$falling_node” >> $log

echo “old_primary=$old_primary” >> $log

echo “new_primary=$new_primary” >> $log

echo “pgdata=$pgdata” >> $log

##如果故障的資料庫為主庫並且執行腳本的作業系統用戶為root

if [ $falling_node = $old_primary ] && [ $UID -eq 0 ];then

    if [ -f $pgdata/recovery.conf ];then

        su  postgres -c “$pghome/bin/pg_ctl promote -D $pgdata”

        echo “Local promote” >> $log

    else

        su postgres -c “ssh -T postgres@$new_primary $pghome/bin/pg_ctl promote -D $pgdata”

    echo “Remote promote” >> $log

    fi 

fi

exit 0;

 

 

腳本主要是通過有沒有recovery.conf文件來判斷為主備，會調用ip addr添加或者刪除IP地址，使用root維護pgpool程式會方便些。需要給root 添加環境變數：

export PGPOOL_HOME=/pgpool

export PATH=$PATH:$HOME/bin:/pgpool/bin/:$PGPOOL_HOME/bin

 

在主庫上啟動pgpool(root用戶)

pgpool

 

 

 

查看主庫啟動的vip

 

 

 

查看日誌：

 

 

 

查看p’g’pool參數的使用：

 

個人感覺跟pg_ctl 類似，尤其是載入參數：

pgpool reload

 

或者關閉pgpool -m fast stop

Shutdown modes are:

  smart       quit after all clients have disconnected

  fast        quit directly, with proper shutdown

  immediate   the same mode as fast

 

關閉是這三種狀態

日誌查看是：

tail -100f /var/log/messages

 

後面啟動從庫pgpool：

主庫日誌：

 

 

 

 

從庫日誌：

 

 

 

 

如果從庫起不來 或者報錯，那肯定是參數設置錯了，根據日誌去修改參數，值得注意的是，這裡面參數很多是本庫的，也有很多是遠端庫的，一定要配置對。

 

通過vip 查看pgpool 狀態：

psql -h 192.168.10.61 -p5555 postgres

show pool_nodes;

 

 

 

悲劇的是主庫居然pgpool 狀態為down,也就是日誌說的node 0 0,正常應該node 0 2;

 

 

 

 

反覆檢查參數配置沒有錯，於是執行（一般主備庫重啟後，狀態異常可以執行以下語句）：

pcp_attach_node -h 192.168.10.61 -U pgpool 0

 

 

 

注意，0為節點1，如果是備庫有問題，就是pcp_attach_node -h 192.168.10.61 -U pgpool 1，然後就正常了

 

如下圖：

 

 

 

如果主備庫關閉過，也需要重新連接pgpool

pcp_attach_node -h 192.168.10.61 -U pgpool 0或者1

 

8.  PCP管理介面配置

pgpool提供一個用於管理pgpool 的系統層命令工具，pcp用戶屬於pgpool 層面，和資料庫中的用戶沒有關係，例如查看pgpool 節點資訊，增加pgpool 節點，斷開pgpool 節點等。

例如，我設置PCP層面密碼為pgpool

 

 

 

 

[root@10pg1 ~]# pg_md5 pgpool

ba777e4c2f15c11ea8ac3be7e0440aa0

cd /pgpool/etc/

cp pcp.conf.sample pcp.conf

添加到

vi pcp.conf

 

 

 

 

二．高可用測試

 

1.  查看pgpool狀態

pcp命令查看pcp節點，watchdog資訊等。

pcp_node_info –verbose -h 192.168.10.61 -U pgpool 0

 

 

 

 

Status 欄位值意思：

0為初始化

1為以啟動沒有連接

2為以啟動有鏈接

3節點關閉

 

查看watchdog集群資訊：

pcp_watchdog_info –verbose -h 192.168.10.61 -U pgpool 

 

 

 

2.  高可用測試

a. 關閉pgpool程式

停掉主節點的pgpool 程式

 pgpool -m stop fast

 

 

 

 

查看主庫IP：

 

 

 

主庫已經刪除了VIP 61

查看系統日誌：

主庫：

 

 

 

備庫：

 

 

 

備庫接管了VIP

登錄進去查看：

psql -h 192.168.10.61 -p5555 postgres postgres

 

 

 

雖然備庫接管了VIP，但是沒有發生failover切換，單獨的pgpool程式故障並不會發生資料庫主備切換（主機資料庫宕機除外，待會測試）。

b. 只關閉postgres 流複製主庫

把主庫上的pgpool開啟，主庫伺服器沒有接管VIP，重啟一下備庫pgpool,讓主庫接管vip,再啟動備庫pgpool做關閉資料庫測試。

 

 

 

pg_ctl stop -D /pgsql/pg_data  -m fast

 

 

 

查看備庫日誌：

 

 

 

查看VIP

 

 

 

可以查看的是VIP 雖然仍然再10pg1上，但是其實現在連接的時新主庫：

 

 

 

測試：

 

 

 

已經發生failover.

 

查看新主庫:

 

 

 

Failover 成功。查看關閉的庫failover的日誌：

這個日誌時再原主庫上生成的failover日誌

 

 

 

c. 關閉postgres 主庫主機

重新搭建主從同步。

 

關閉主庫主機相當於關閉資料庫和pgpool了，個人猜想時vip 會飄過來，也會執行備庫提升為主庫。

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

show pool_nodes;

 

 

關閉主庫伺服器。

經過幾秒鐘VIP漂移過來了，而且備庫提升為主庫了。

 

 

查看新主庫pgpool日誌：

 

 

查看新主庫發生failover的日誌：

 

 

查看新主庫資訊

 

 

 

d. 總結

按照步驟來安裝以及測試，如果參數沒有配置錯誤，實驗還是會比較成功。值得注意的時要配置pool_passwd和pcp密碼，以及pgpool參數文件配置的時候，不要把主從參數配錯了，最後更值得注意的時failover腳本，如果寫錯了不會發生故障轉移，再重啟過資料庫或者pgpool程式時，如果pgpool節點狀態不正常，可以利用pcp_attach_node把pgpool節點加進去,感覺備庫切換主庫的時候，不是立馬，有點延遲，應該是跟時間參數配置有關。

 

VirMach 便宜 VPS

QNews

QNews