pgpool-II以下簡稱（pgpool）疗疟，它是pgsql眾多讀寫分離插件里该默，比較出色的一個，功能強大策彤，支持功能也比較多栓袖，如自動failover、虛擬IP店诗、并行查詢裹刮、連接池、復制庞瘸、負載均衡捧弃、看門狗等。既滿足了讀寫分離的功能，也擁有了簡單的高可用违霞，下面我?guī)е蠹襾砗唵问褂靡幌滤?/p>
pgpool-II官網(wǎng)地址：https://www.pgpool.net/
環(huán)境介紹
| 角色 | IP | 服務 |
|---|---|---|
| leader | 172.22.138.218 | postgresql嘴办、pgpool |
| standby1 | 172.22.138.219 | postgresql、pgpool |
| standby2 | 172.22.138.220 | postgresql买鸽、pgpool |
pgpool部署前涧郊，需要先搭建出一套流復制的集群出來，如果不知道如何搭建流復制的眼五，可以參考我另外一篇文章：http://www.reibang.com/p/c297d1729492
環(huán)境準備
1.下載pgpool安裝包并解壓(3節(jié)點相同)
root # cd /opt
root # wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.4.7.tar.gz
root # tar xf pgpool-II-4.4.7.tar.gz
2.主機之間配置root用戶和postgres用戶的ssh互信(3節(jié)點相互做)
root # ssh-keygen -t rsa
root # ssh-copy-id postgres@172.22.138.219
root # ssh-copy-id postgres@172.22.138.220
3.創(chuàng)建pgpool的安裝目錄妆艘，編譯安裝pgpool，并授權(quán)給postgres用戶看幼，pgpool我們也用postgres用戶進行管理双仍。(3節(jié)點相同)
root # mkdir -p /data/pgpool
root # cd /opt/pgpool-II-4.4.7/
root # ./configure --prefix=/data/pgpool --with-pgsql=/data/pgsql/data
root # make && make install
root # chown -R /data/pgpool
root # ls /data/pgpool
...
bin dog etc include lib log share
配置pgpool-II
1.pgpool安裝后，配置文件都是sample模板結(jié)尾的桌吃，我們copy一份conf結(jié)尾的朱沃，再進行編輯。 (3節(jié)點相同)
postgres # cd /data/pgpool/etc
postgres # cp pcp.conf.sample pcp.conf
postgres # cp pgpool.conf.sample pgpool.conf
postgres # cp pool_hba.conf.sample pool_hba.conf
2.創(chuàng)建個pgsql的超級用戶茅诱，下面和下一篇文章都會用到逗物。（主庫leader節(jié)點）
postgres # psql
postgres=# create user repmgr with superuser password 'repmgr' connection limit 100;
postgres=# create database repmgr owner repmgr;
3.修改pgpool的配置文件pgpool.conf，確認相關(guān)目錄和數(shù)據(jù)庫用戶是否存在瑟俭，（三個節(jié)點分別根據(jù)實際情況修改自己的IP和其余兩臺的IP）
#backend_clustering_mode = 'streaming_replication'
#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
# - pgpool Connection Settings -
listen_addresses = '*'
port = 9999
# - pgpool Communication Manager Connection Settings -
pcp_listen_addresses = '*'
pcp_port = 9898
# - Backend Connection Settings -
# hostname0必須為主庫翎卓，不用變動。
backend_hostname0 = '172.22.138.218'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '172.22.138.219'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = '172.22.138.220'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/data/pgsql/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = on
#pool_passwd = 'pool_passwd'
#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------
log_line_prefix = '%m: %a pid %p: ' # printf-style string to output at beginning of each log line.
log_connections = on
log_disconnections =on
log_statement = on
log_per_node_statement = on
log_client_messages = on
log_standby_delay = 'always'
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/data/pgpool/pgpool.pid'
logdir = '/data/pgpool/log'
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on
#------------------------------------------------------------------------------
# STREAMING REPLICATION MODE
#------------------------------------------------------------------------------
sr_check_period = 10
sr_check_user = 'repmgr'
sr_check_password = 'repmgr'
sr_check_database = 'sbtest'
delay_threshold = 10000000
delay_threshold_by_time = 10
#------------------------------------------------------------------------------
# HEALTH CHECK GLOBAL PARAMETERS
#------------------------------------------------------------------------------
health_check_period = 10
health_check_timeout = 20
health_check_user = 'repmgr'
health_check_password = 'repmgr'
health_check_database = 'sbtest'
#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
# - Enabling -
use_watchdog = on
#本機IP
trusted_servers = '172.22.138.218'
trusted_server_command = 'ping -q -c3 %h'
hostname0 = '172.22.138.218'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = '172.22.138.219'
wd_port1 = 9000
pgpool_port1 = 9999
pgpool_port0 = 9999
hostname2 = '172.22.138.220'
wd_port2 = 9000
pgpool_port2 = 9999
wd_priority = 1
wd_ipc_socket_dir = '/tmp'
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
# -- heartbeat mode --
# 除本節(jié)點以外摆寄，另外兩臺機器的IP
heartbeat_hostname0 = '172.22.138.219'
heartbeat_port0 = 9694
heartbeat_device0 = 'eth0'
heartbeat_hostname1 = '172.22.138.220'
heartbeat_port1 = 9694
heartbeat_device1 = 'eth0'
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
4.配置pgpool的hba文件（三節(jié)點一致）
postgres # vim /data/pgpool/etc/pool_hba.conf
...
host all all 0.0.0.0/0 trust
...
5.配置pgpool的管理接口的登錄用戶和密碼失暴，密碼需要pg_md5生成，沒有這個命令記得配置pgpool的PATH環(huán)境變量或者絕對路徑使用這個命令微饥，這里就不做贅述了逗扒，也就是pcp.conf文件。（三節(jié)點一致）
postgres # pg_md5 admin
21232f297a57a5a743894a0e4a801fc3
postgres # vim data/pgpool/etc/pcp.conf
...
# USERID:MD5PASSWD
admin:21232f297a57a5a743894a0e4a801fc3
啟動pgpool-II
1.啟動pgpool欠橘，連接測試矩肩，三臺均可以連接。（按照順序啟動三臺節(jié)點：Leader>standby1>standby2）
postgres # pgpool -f /data/pgsql/pgpool/etc/pgpool.conf -n > /data/pgsql/pgpool/log/pgpool.log 2>&1 &
### 停止命令：pgpool -f /data/pgsql/pgpool/etc/pgpool.conf stop
postgres # psql -U repmgr -p 9999 -h 172.22.138.218 repmgr
postgres # psql -U repmgr -p 9999 -h 172.22.138.219 repmgr
postgres # psql -U repmgr -p 9999 -h 172.22.138.220 repmgr
2.管理接口查詢集群狀態(tài)
postgres # pcp_watchdog_info -h 10.0.200.218 -p 9898 -U admin
