pgpool-II以下简称（pgpool），它是pgsql众多读写分离插件里，比较出色的一个，功能强大，支持功能也比较多，如自动failover、虚拟IP、并行查询、连接池、复制、负载均衡、看门狗等。既满足了读写分离的功能，也拥有了简单的高可用，下面我带着大家来简单使用一下它。
pgpool-II官网地址：https://www.pgpool.net/
环境介绍
| 角色 | IP | 服务 |
|---|---|---|
| leader | 172.22.138.218 | postgresql、pgpool |
| standby1 | 172.22.138.219 | postgresql、pgpool |
| standby2 | 172.22.138.220 | postgresql、pgpool |
pgpool部署前，需要先搭建出一套流复制的集群出来，如果不知道如何搭建流复制的，可以参考我另外一篇文章：https://blog.csdn.net/h_3369/article/details/141358629?spm=1001.2014.3001.5502
环境准备
1.下载pgpool安装包并解压(3节点相同)
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节点相互做)
root # ssh-keygen -t rsa
root # ssh-copy-id postgres@172.22.138.219
root # ssh-copy-id postgres@172.22.138.220
3.创建pgpool的安装目录，编译安装pgpool，并授权给postgres用户，pgpool我们也用postgres用户进行管理。(3节点相同)
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模板结尾的，我们copy一份conf结尾的，再进行编辑。 (3节点相同)
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.创建个pgsql的超级用户，下面和下一篇文章都会用到。（主库leader节点）
postgres # psql
postgres=# create user repmgr with superuser password 'repmgr' connection limit 100;
postgres=# create database repmgr owner repmgr;
3.修改pgpool的配置文件pgpool.conf，确认相关目录和数据库用户是否存在，（三个节点分别根据实际情况修改自己的IP和其余两台的IP）
root # vim pgpool.conf
#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 --
# 除本节点以外，另外两台机器的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文件（三节点一致）
postgres # vim /data/pgpool/etc/pool_hba.conf
...
host all all 0.0.0.0/0 trust
...
5.配置pgpool的管理接口的登录用户和密码，密码需要pg_md5生成，没有这个命令记得配置pgpool的PATH环境变量或者绝对路径使用这个命令，这里就不做赘述了，也就是pcp.conf文件。（三节点一致）
postgres # pg_md5 admin
21232f297a57a5a743894a0e4a801fc3
postgres # vim data/pgpool/etc/pcp.conf
...
# USERID:MD5PASSWD
admin:21232f297a57a5a743894a0e4a801fc3
6.配置每个节点的node-id（分别创建文件并写入1、2、3）
postgres # vim /data/pgsql/pgpool/etc/pgpool_node_id
1
postgres # vim /data/pgsql/pgpool/etc/pgpool_node_id
2
postgres # vim /data/pgsql/pgpool/etc/pgpool_node_id
3
启动pgpool-II
1.启动pgpool，连接测试，三台均可以连接。（按照顺序启动三台节点：Leader>standby1>standby2）
postgres # pgpool -f /data/pgpool/etc/pgpool.conf -n > /data/pgpool/log/pgpool.log 2>&1 &
### 停止命令：pgpool -f /data/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.管理接口查询集群状态
postgres # pcp_watchdog_info -h 10.0.200.218 -p 9898 -U admin
1081
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
