ery routing in Streaming Replication cluster

本章節將結合Pgpool-II，達成使用者端read write query的自動rounting。換句話說，read query(SELECT)自動在cluster中load balance； write query(INSERT, UPDATE...)自動指向master，架構如下圖所示：

此架構所帶來好處如下列：

• read query負載平衡
• connection pool
• 使用者端不需要因為master, standby角色互換，而需要更改指向master的IP或主機位置
• 可稱的上某種程度獨寫分離操作，且不需要實作在AP端
• 無單點故障問題，任一節點掛掉皆可獨立運作

缺點：

• 發生單點故障時，除非前端Loadbalance有自動偵測機制，否則仍需手動修改...

架構說明：

由於是由pgpool在前端實際面對使用者，建議使其listen postgres default post, 5432；讓後端postgres10listen其他port，本文讓postgres listen在5431上，對於使用者、管理者來說使用者也會比較友善。

[on master、slave]

安裝Pgpool-II

~$ sudo apt-get install pgpool2

設定pgpool.conf

先備份pgpool.conf設定檔

~$ sudo mv /etc/pgpool2/pgpool.conf /etc/pgpool2/pgpool.conf_old

由於Pgpool-II設定項目眾多，且不同模式間也有許多設定項目互斥的情況，請到/usr/share/doc/pgpool2/examples目錄下複製streaming replication設定範例

~$ sudo cp /usr/share/doc/pgpool2/examples/pgpool.conf.sample-stream.gz /etc/pgpool2/ && sudo gzip -d /etc/pgpool2/pgpool.conf.sample-stream.gz
~$ sudo mv /etc/pgpool2/pgpool.conf.sample-stream /etc/pgpool2/pgpool.conf

編輯pgpool.conf並確認下列設定項目是否設定正確

~$ sudo vim /etc/pgpool2/pgpool.conf
# Connection
listen_addresses = '*'
port = 5432

# Backends
backend_hostname0 = 'node1'  # hostname or IP
backend_port0 = 5431
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/10/main'
backend_flag0 = 'DISALLOW_TO_FAILOVER'

# Authentication
enable_pool_hba = off

# pgpool mode
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'

# Do not use pgpool to do failover
fail_over_on_backend_error = off

# Log setting
log_destination = 'syslog'
log_connections = on
log_per_node_statement = on

# Health check
sr_check_user = 'postgres'
health_check_user = 'postgres'

Note : fail_over_on_backend_error若不設為off，只要任一節點有問題就會因為failover失敗導致pgpool停擺

將pgpool加入syslog facility

~$ sudo vim  /etc/rsyslog.conf
#pgpool
local0.* /var/log/pgpool.log

設定pgpool logrotate

~$ sudo vim /etc/logrotate.d/pgpool
/var/log/pgpool.log {
       daily
       rotate 10
       copytruncate
       delaycompress
       compress
       notifempt
       missingok
       su root root
}

pool_hba.conf連線管理

若希望在前端pgpool管理驗證連線，除了透過pgpool的pool_hba.conf必須搭配後端的pg_hba.conf來完成，設定對照如下表：

整理自：https://www.pgpool.net/mediawiki/index.php/FAQ

一般實務情況下，前後端都應該開啟md5驗證模式，以避免資料庫匿名存取；但上列表格不適用於replication mode以及master/slave mode，若是使用以上模式，僅支援trust, clear text passwordu以及pam方法管理連線。但明文密碼可能會造成管理上的漏洞，故比較推薦透過來源IP、使用者以及存取資料庫來嚴格限制DB存取。

啟動與測試pgpool

於master, slave上啟動pgpool服務

~$ sudo service pgpool2 start

觀察是否能正常接受查詢

~$ psql -h pgdbnode1 -U postgres -c "show pool_nodes;"
 node_id | hostname  | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | pgdbnode1 | 5431 | up     | 0.500000  | primary | 2          | true              | 0
 1       | pgdbnode2 | 5431 | unused | 0.500000  | standby | 0          | false             | 0
(2 筆資料列)

~$ psql -h pgdbnode2 -U postgres -c "show pool_nodes;"
 node_id | hostname  | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | pgdbnode1 | 5431 | up     | 0.500000  | primary | 0          | true              | 0
 1       | pgdbnode2 | 5431 | unused | 0.500000  | standby | 0          | false             | 0
(2 筆資料列)

Note: psql指令預設會使用5432 port來連線，我們的設定是將postgresql service listen在5431port上，pgpool listen在5432 port上

在show pool_nodes返回結果中，standby node會處於unused狀態，且pgpool.log中也可以觀察到，select query只會被送到node0(master)，並沒有送到node1(slave)，Load balance功能實際上是失效的。

~$ tail -f /var/log/pgpool.log
Nov  8 14:02:28 pgdbnode1 pgpool[48322]: [106-1] 2017-11-08 14:02:28: pid 48322: LOG:  pool_send_and_wait: Error or notice message from backend: : DB node id: 0...
Nov  8 14:02:28 pgdbnode1 pgpool[48322]: [106-1] 2017-11-08 14:02:28: pid 48322: LOG:  pool_send_and_wait: Error or notice message from backend: : DB node id: 0...
Nov  8 14:02:28 pgdbnode1 pgpool[48322]: [106-1] 2017-11-08 14:02:28: pid 48322: LOG:  pool_send_and_wait: Error or notice message from backend: : DB node id: 0...

這是因為pgpool雖然在設定檔中知道standby的存在，但在管理pgpool的meta中並不知道它的存在，此時會需要透過PCP-pgpool Communication Manager工具來節點加入到pgpool中來管理。

使用pcp_attach_node加入standby node

使用pg_md5產生密碼

~$ pg_md5 <password>
81dc9bdb52d04dc20036dbd8313ed055

將hash後密碼加到pcp.conf中，並讓admin使用者可以透過該組密碼進行驗證

~$ sudo vim /etc/pgpool2/pcp.conf
admin:81dc9bdb52d04dc20036dbd8313ed055

預設PCP服務listen 9898 port上，確認該服務有正確listen後，使用pcp_node_info並帶上驗證資訊測試

~$ pcp_node_info -U admin -h pgdbnode1 -n 0
Password:(輸入剛剛用pg_md5來hash的密碼)
pgdbnode1 5431 2 0.500000 up

~$ pcp_node_info -U admin -h pgdbnode1 -n 1
Password:
pgdbnode2 5431 0 0.500000 unused

確認PCP運作正常後，請透過將node1(standby)加入pgpool管理

~$ pcp_attach_node -h pgdbnode1 -U admin -n 1
Password: 
pcp_attach_node -- Command Successful

~$ pcp_node_info -h pgdbnode2 -U admin -n 1
pgdbnode2 5431 2 0.500000 up

此時show pool_nodes standby的狀態就會被更新成up了，也可以觀察到/var/log/pgpool.log會有分配到slave的select語法

 ~$ psql -h pgdbnode1 -U postgres -c "show pool_nodes;"
 node_id | hostname  | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | pgdbnode1 | 5431 | up     | 0.500000  | primary | 2          | true              | 0
 1       | pgdbnode2 | 5431 | up     | 0.500000  | standby | 0          | false             | 0
(2 筆資料列)