• Pgpool介紹
• 環(huán)境準(zhǔn)備
• 搭建配置
  • 服務(wù)器配置
  • pgpool安裝
  • PostgreSQL數(shù)據(jù)庫(kù)配置
  • pgpool配置
  • 相關(guān)腳本
• 啟動(dòng)
• 停止
• 測(cè)試
• 負(fù)載均衡測(cè)試
• 自動(dòng)故障轉(zhuǎn)移測(cè)試
• 寫在最后
• 關(guān)于作者
• 相關(guān)鏈接
  本文由 @小劉先森 原創(chuàng)，轉(zhuǎn)載請(qǐng)注明出處。

該篇文章主要是介紹基于pgpool4.1以及PostgreSQL的流復(fù)制實(shí)現(xiàn)PostgreSQL的讀寫分離以及高可用，配置參考Pgpool-II官方文檔。使用版本為Pgpool4.1、PostgreSQL11.6。

文章略長(zhǎng)。。。。。。。。。。。雖然我也不想

Pgpool介紹

pgpool相關(guān)介紹不在該文章中，需要者可以參考我的另一篇文章。傳送門：pgpool介紹

環(huán)境準(zhǔn)備

本次使用三臺(tái)CentOS7.3的服務(wù)器來搭建集群，首先在三臺(tái)服務(wù)器上部署PostgreSQL11.6版本的數(shù)據(jù)庫(kù)，部署教程大家可以網(wǎng)上自行查找。具體規(guī)劃如下：

架構(gòu)圖參考官網(wǎng)：

pgpool架構(gòu)

搭建配置

服務(wù)器配置

故障轉(zhuǎn)移、在線恢復(fù)時(shí)需要ssh到其它服務(wù)器執(zhí)行命令，故需要配置服務(wù)器之間無密碼ssh登錄（如服務(wù)器之間已經(jīng)配置ssh可以跳過該節(jié)，但是需要修改failover.sh等腳本）

     [all servers]# cd ~/.ssh
     [all servers]# ssh-keygen -t rsa -f id_rsa_pgpool
     [all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
     [all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
     [all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server3
     
     [all servers]# su  postgres
     [all servers]$ cd ~/.ssh
     [all servers]$ ssh-keygen -t rsa -f id_rsa_pgpool
     [all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
     [all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
     [all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server3

pgpool安裝

pgpool安裝不在該文章中介紹，需要者可以參考我另一篇文章。傳送門：pgpool安裝。

PostgreSQL數(shù)據(jù)庫(kù)配置

• WAL歸檔
  需要WAL歸檔的可自行配置，該示例暫時(shí)未使用WAL歸檔

    [all servers]# su - postgres
    [all servers]$ mkdir /var/lib/pgsql/archivedir
    ## postgres.conf 配置
    archive_mode = on
    archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'

這兒有一個(gè)歸檔的腳本，使用該腳本可以自行修改保留多少天歸檔 pg_archive.sh

• postgres.conf配置
  該配置僅在主節(jié)點(diǎn)配置，從節(jié)點(diǎn)使用pgpool的在線恢復(fù)功能配置

     listen_addresses = '*'
     port = 5432
     max_wal_senders = 10
     max_replication_slots = 10
     wal_level = replica
     hot_standby = on
     wal_log_hints = on

• 數(shù)據(jù)庫(kù)用戶以及密碼配置

     [server1]# psql -U postgres -p 5432
     postgres=# CREATE ROLE pgpool WITH LOGIN PASSWORD 'pgpool';
     postgres=# CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'repl';
     ## 用于SHOW POOL_NODES 展示 "replication_state" and "replication_sync_state" 
     postgres=# GRANT pg_monitor TO pgpool;

• pg_hba.con配置
  添加用戶驗(yàn)證配置，將repl復(fù)制用戶添加到pg_hba中。此文檔中所有密碼使用md5驗(yàn)證。

    host    all             all             0.0.0.0/0               md5
    host    replication     repl            0.0.0.0/0               md5

• 配置.pgpass文件用于無密碼操作

由于在故障轉(zhuǎn)移、在線恢復(fù)時(shí)使用腳本進(jìn)行操作，腳本中使用pg_basebakup、pg_rewind等命令，所以需要配置無密碼操作

在postgres用戶的home目錄下創(chuàng)建.pgpass文件，并且文件權(quán)限為600。

     [all servers]# su - postgres
     [all servers]$ vi ~/.pgpass
     ## 格式為：hostname:port:database:username:password
     server1:5432:replication:repl:<repl user password>
     server2:5432:replication:repl:<repl user passowrd>
     server3:5432:replication:repl:<repl user passowrd>
     server1:5432:postgres:postgres:<postgres user passowrd>
     server2:5432:postgres:postgres:<postgres user passowrd>
     server3:5432:postgres:postgres:<postgres user passowrd>
     [all servers]$ chmod 600  ~/.pgpass

pgpool配置

1. # cp -p /usr/local/pgpool/etc/pgpool.conf.sample-stream /usr/local/pgpool/etc/pgpool.conf
2. 配置PostgreSQL數(shù)據(jù)庫(kù)信息

    # - Backend Connection Settings -
    
    # 有幾臺(tái)PostgreSQL數(shù)據(jù)庫(kù)，配置幾個(gè)后端信息，使用后綴名0、1、2……
    
    backend_hostname0 = 'server1' # Host name or IP address to connect to for backend 0
    backend_port0 = 5432 # Port number for backend 0
    backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode)
    backend_data_directory0 = '/data/pgsql/sport/' # Data directory for backend 0
    # Controls various backend behavior
    # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
    backend_flag0 = 'ALLOW_TO_FAILOVER' 
    
    backend_hostname1 = 'server2'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/var/lib/pgsql/11/data'
    backend_flag1 = 'ALLOW_TO_FAILOVER'

    backend_hostname2 = 'server3'
    backend_port2 = 5432
    backend_weight2 = 1
    backend_data_directory2 = '/var/lib/pgsql/11/data'
    backend_flag2 = 'ALLOW_TO_FAILOVER'

3. 基礎(chǔ)配置

    listen_addresses = '*'
    pid_file_name = '/var/run/pgpool/pgpool.pid'
                                   # PID file name
                                   # Can be specified as relative to the"
                                   # location of pgpool.conf file or
                                   # as an absolute path
                                   # (change requires restart)
    logdir = '/var/run/pgpool'
                                   # Directory of pgPool status file
                                   # (change requires restart)

創(chuàng)建pgpool運(yùn)行需要目錄mkdir -p /var/run/pgpool

4. 配置復(fù)制延遲檢查

    sr_check_user = 'pgpool'
    ## 自從4.0版本后，如果密碼設(shè)置為的話，pgpool會(huì)首先從`pool_passwd`文件中獲取密碼，然后在使用空密碼
    sr_check_password = 'pgpool'

5. 配置健康檢查

   # Health check period
   # Disabled (0) by default
   health_check_period = 5
   
   # Health check timeout
   # 0 means no timeout
   health_check_timeout = 30

   health_check_user = 'pgpool'
   health_check_password = 'pgpool'

   health_check_max_retries = 3

6. 配置故障轉(zhuǎn)移

   failover_command = '/usr/locla/pgpool/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
   follow_master_command = '/usr/locla/pgpool/etc/follow_master.sh %d %h %p %D %m %H %M %P %r %R'

關(guān)于腳本會(huì)在后面給出下載地址以及腳本使用說明

# 給腳本設(shè)置執(zhí)行權(quán)限，注意：該腳本需要在三臺(tái)pgpool服務(wù)器中都需要?jiǎng)?chuàng)建
chmod +x /usr/local/pgpoll/etc/{failover.sh,follow_master.sh}

7. 配置在線恢復(fù)
   為了使用Pgpool-II執(zhí)行在線恢復(fù)，我們需要配置PostgreSQL用戶名和在線恢復(fù)命令recovery_1st_stage。由于執(zhí)行在線恢復(fù)需要PostgreSQL中的超級(jí)用戶特權(quán)，因此我們?cè)趓ecovery_user中指定postgres用戶。然后，我們?cè)赑ostgreSQL主服務(wù)器（server1）的數(shù)據(jù)庫(kù)目錄中創(chuàng)建recovery_1st_stage和pgpool_remote_start，并添加執(zhí)行權(quán)限。

   recovery_user = 'postgres'
   # Online recovery user
   recovery_password = 'postgres'
   # Online recovery password

   recovery_1st_stage_command = 'recovery_1st_stage'

## 該腳本只需要在數(shù)據(jù)庫(kù)主節(jié)點(diǎn)創(chuàng)建，后續(xù)使用在線恢復(fù)時(shí)會(huì)復(fù)制過去
   [server1]# su - postgres
   [server1]$ vi /data/pgsql/sport/recovery_1st_stage
   [server1]$ vi /data/pgsql/sport/pgpool_remote_start
   [server1]$ chmod +x /data/pgsql/sport/{recovery_1st_stage,pgpool_remote_start}

數(shù)據(jù)庫(kù)創(chuàng)建擴(kuò)展，該擴(kuò)展是為了能夠執(zhí)行在線恢復(fù)（如果在安裝時(shí)已經(jīng)創(chuàng)建該擴(kuò)展則可以跳過該步驟）

    [server1]# su - postgres
    [server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"

6. 配置客戶端身份驗(yàn)證

## pgpool.conf中
enable_pool_hba = on

身份驗(yàn)證文件為/usr/local/pgpool/etc/pool_hba.conf，配置方式與PostgreSQL基本一樣。(scram-sha-256方式可參考pgpool官網(wǎng))

    host    all         pgpool           0.0.0.0/0          md5
    host    all         postgres         0.0.0.0/0          md5

cd /usr/local/pgpool/etc
../bin/pg_md5 -p -m -u postgres pool_passwd
../bin/pg_md5 -p -m -u pgpool pool_passwd
cat /etc/pgpool-II/pool_passwd 
    # pgpool:AESheq2ZMZjynddMWk5sKP/Rw==
    # postgres:AESHs/pWL5rtXy2IwuzroHfqg==

9. 看門狗配置

use_watchdog = on
delegate_IP = '192.168.111.6' ##vip配置
## 網(wǎng)卡名字需要正確配置
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 label enp0s8:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s8'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp0s8'

配置其它pgpool信息，注意：該配置在三臺(tái)服務(wù)器不一樣，只需要配置另外幾臺(tái)即可
[server1配置如下]
      # - Other pgpool Connection Settings -
      
      other_pgpool_hostname0 = 'server2'
      other_pgpool_port0 = 9999
      other_wd_port0 = 9000
      
      other_pgpool_hostname1 = 'server3'
      other_pgpool_port1 = 9999
      other_wd_port1 = 9000

      heartbeat_destination0 = 'server2'
      heartbeat_destination_port0 = 9694
      heartbeat_device0 = ''

      heartbeat_destination1 = 'server3'
      heartbeat_destination_port1 = 9694
      heartbeat_device1 = ''
[server2配置如下]
      # - Other pgpool Connection Settings -

      other_pgpool_hostname0 = 'server1'
      other_pgpool_port0 = 9999
      other_wd_port0 = 9000
      
      other_pgpool_hostname1 = 'server3'
      other_pgpool_port1 = 9999
      other_wd_port1 = 9000

      heartbeat_destination0 = 'server1'
      heartbeat_destination_port0 = 9694
      heartbeat_device0 = ''

      heartbeat_destination1 = 'server3'
      heartbeat_destination_port1 = 9694
      heartbeat_device1 = ''
  [server3配置如下]
      # - Other pgpool Connection Settings -

      other_pgpool_hostname0 = 'server1'
      other_pgpool_port0 = 9999
      other_wd_port0 = 9000
      
      other_pgpool_hostname1 = 'server2'
      other_pgpool_port1 = 9999
      other_wd_port1 = 9000

      heartbeat_destination0 = 'server1'
      heartbeat_destination_port0 = 9694
      heartbeat_device0 = ''

      heartbeat_destination1 = 'server2'
      heartbeat_destination_port1 = 9694
      heartbeat_device1 = ''

10. pcp命令配置

cd /usr/local/pgpool/bin
echo 'pgpool:'`pg_md5 pgpool` >> /usr/local/pgpool/etc/pcp.conf

以上pgpool相關(guān)配置都配置完成，最終會(huì)生成pgpool.conf、pool_hba.conf、pool_passwd、pcp.conf，可以配置完一臺(tái)后，在其它服務(wù)器copy配置文件即可。注意：pgpool.conf中看門狗配置需要在其它服務(wù)器修改一下。

相關(guān)腳本

此處一共需要四個(gè)腳本分別為:

• failover.sh 目錄為: /usr/local/pgpool/etc/下
• follow_master.sh 目錄為: /usr/local/pgpool/etc/下
• recovery_1st_stage 目錄為：PostgreSQL數(shù)據(jù)庫(kù)數(shù)據(jù)目錄下
• pgpool_remote_start目錄為：PostgreSQL數(shù)據(jù)庫(kù)數(shù)據(jù)目錄下

腳本地址：https://github.com/MrSmallLiu/pgpool
腳本參考于官網(wǎng)，但是稍有改動(dòng)：

• 創(chuàng)建與刪除復(fù)制槽時(shí)，如果使用ip地址，則會(huì)報(bào)錯(cuò)，因?yàn)槊植辉试S有.，修改：${FAILED_NODE_HOST//./_}
• follow_master.sh腳本中一處bug

# drop replication slot
            ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
                ${PGHOME}/bin/psql -p ${NEW_MASTER_NODE_PORT} -c \"SELECT pg_drop_replication_slot('${FAILED_NODE_HOST//./_}')\"
            "

注意：

• 注意所有腳本中 PGHOME變量為PostgreSQL安裝路徑
• 注意所有腳本中ARCHIVEDIR，如果未使用，需要將其注釋，并且修改相應(yīng)使用地方
• 注意所有腳本中PGPOOL_PATH變量為pgpool的bin路徑
• 注意recovery_1st_stage腳本中PRIMARY_NODE_HOST變量，該變量取hostname值，可以先在自己服務(wù)器測(cè)試一下該命令是否符合預(yù)期。不符合者使用hostnamectl set-hostname server1

啟動(dòng)

• 后臺(tái)運(yùn)行
  /usr/local/pgpool/bin/pgpool
• debug運(yùn)行
  /usr/local/pgpool/bin/pgpool -n -d
/usr/local/pgpool/bin/pgpool -n
• standby 數(shù)據(jù)庫(kù)啟動(dòng)

-n 表示在pgpool.conf配置文件中PostgreSQL配置的后面的序號(hào)
    /usr/local/pgpool/bin/pcp_recovery_node -h 192.168.111.6 -p 9898 -U pgpool -n 1
    Password: 
    pcp_recovery_node -- Command Successful
    
    /usr/local/pgpool/bin/pcp_recovery_node -h 192.168.111.6 -p 9898 -U pgpool -n 2
    Password: 
    pcp_recovery_node -- Command Successful

停止

/usr/local/pgpool/bin/pgpool -m fast stop

測(cè)試

• 使用客戶端連接（navicat、pgAdmin等）
  psql -h 192.168.111.6 -p 9999 -U passwd：postgres
• 執(zhí)行show pool_nodes
  
  pool_nodes

負(fù)載均衡測(cè)試

可以配置PostgreSQL日志打印sql語句，使用vip地址連接上后測(cè)試輸出即可看到查詢會(huì)負(fù)載到三臺(tái)服務(wù)器（默認(rèn)根據(jù)連接使用負(fù)載均衡，需要幾次新建查詢來測(cè)試）

自動(dòng)故障轉(zhuǎn)移測(cè)試

可以使用PostgreSQL命令停掉主節(jié)點(diǎn)數(shù)據(jù)庫(kù)來測(cè)試故障轉(zhuǎn)移

寫在最后

• 以上配置經(jīng)過我們部署測(cè)試是可以正常運(yùn)行的，但是是后續(xù)整理才寫的該篇文章，可能存在遺漏不足的地方。小伙伴在配置過程中有疑惑、配置后未能正常啟動(dòng)、有文檔意見都可以與我聯(lián)系。隨時(shí)歡迎提給我，然后我進(jìn)行修改文檔，以幫助更多人。
• 后面抽時(shí)間整理一個(gè)pgpool的部署流程圖，方便大家理解部署流程。
• 關(guān)于配置文件的具體講解以及優(yōu)化會(huì)在后面再寫相關(guān)文檔，歡迎持續(xù)關(guān)注

關(guān)于該文章，歡迎各位批評(píng)指正

關(guān)于作者

GISer
小劉先森
QQ: 1016817543
郵箱：1016817543@qq.com
github：https://github.com/MrSmallLiu (歡迎star)

相關(guān)鏈接

以下為本人參與開發(fā)的一些庫(kù)，歡迎各位Star、Issues、PR

• 基于PostgreSQL和PostGIS的坐標(biāo)轉(zhuǎn)換函數(shù)，支持點(diǎn)、線、面的WGS84和CGCS2000與GCJ02和BD09坐標(biāo)系與之間互轉(zhuǎn)
• 統(tǒng)一社會(huì)信用代碼相關(guān)的PostgreSQL函數(shù)，包括驗(yàn)證、隨機(jī)生成等