安装目录：cd /usr/
mkdir pgpool
下载地址： https://pgpool.net/mediawiki/index.php/Downloads
解压
tar -zxvf pgpool-II-3.7.17.tar.gz
cd pgpool-II-3.7.17
./configure --prefix=/usr/pgpool
如果异常：configure: error: no acceptable C compiler found in $PATH，执行 yum install -y gcc
如果再次异常：configure: error: libpq is not installed or libpq is old， 执行 yum install -y postgresql-devel
make && make install
进入sql目录
cd src/sql/
安装recovery
进入目录pgpool-recovery
cd pgpool-recovery/
make && make install
安装regclass
进入目录pgpool-regclass
cd pgpool-regclass/
make && make install
安装 pgpool_adm
进入pgpool-adm
cd pgpool-adm/
make && make install
创建insert_lock表
进入sql目录,执行命令
执行
psql -U postgres -f insert_lock.sql
去数据库查询 是否新建了一个新模式
新建 systemctl
vi /lib/systemd/system/pgpool.service
[Unit]
Description=Pgpool-II
After=syslog.target network.target
[Service]
ExecStart=/usr/pgpool/bin/pgpool -f /usr/pgpool/etc/pgpool.conf -F /usr/pgpool/etc/pcp.conf -a /usr/pgpool/etc/pool_hba.conf -n
ExecStop=/usr/pgpool/bin/pgpool -f /usr/pgpool/etc/pgpool.conf -F /usr/pgpool/etc/pcp.conf -a /usr/pgpool/etc/pool_hba.conf -m fast stop
ExecReload=/usr/pgpool/bin/pgpool -f /usr/pgpool/etc/pgpool.conf -F /usr/pgpool/etc/pcp.conf -a /usr/pgpool/etc/pool_hba.conf reload
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=30s
TimeoutSec=0
[Install]
WantedBy=multi-user.target
保存
启动测试
systemctl start pgpool
查询运行状态
systemctl status pgpool
如果存在异常：pid 68583: FATAL: could not open pid file as /var/run/pgpool/pgpool.pid. reason: No such file or directory
直接解决方法： 新建一个目录
mkdir /var/run/pgpool
复制配置文件 目录 /usr/pgpool/etc
cp pcp.conf.sample pcp.conf
cp pgpool.conf.sample pgpool.conf
执行命令，启用配置文件pool_passwd
pg_md5 -m -p -u postgres pool passwd
查询密码是否生成
cat pool_passwd
配置pgpool.conf
# - pgpool Communication Manager Connection Settings -
listen_addresses = '*'
---默认0是主库，其它是从库，backend_weight可以控制数据库读在两台机器上的分配比例
# - Backend Connection Settings -
backend_hostname0 = '192.168.85.130'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.5/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.85.131'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.5/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = '192.168.85.132'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/9.5/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
# - Authentication -
---开启pgpool的hba认证
enable_pool_hba = on
#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
sr_check_user = 'postgres'
sr_check_password = 'postgres'
#------------------------------------------------------------------------------
# HEALTH CHECK
#------------------------------------------------------------------------------
health_check_period = 1
health_check_user = 'postgres'
health_check_password = 'postgres'
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
---用来在主库失败时，把只读的从库切为主库
failover_command = '/usr/pgpool/failover_stream.sh %d %H /tmp/trigger_file0'
修改配置文件pool_hba.conf
---添加一行：
host all all all md5 //根据实际情况自己定
---删除一行
host all all 127.0.0.1/32 trust
添加主备脚本切换脚本failover_stream.sh
vi /usr/pgpool/failover_stream.sh
#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.
new_master=$1
trigger_command="$PGHOME/bin/pg_ctl promote -D $PGDATA"
# Prompte standby database.
/usr/bin/ssh -T $new_master $trigger_command
执行命令
chmod 700 failover_stream.sh
问题：
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+----------------+------+---------+-----------+--------+------------+-------------------+-------------------
0 | 192.168.85.130 | 5432 | up | 0.333333 | master | 0 | true | 0
1 | 192.168.85.131 | 5432 | waiting | 0.333333 | slave | 0 | false | 0
2 | 192.168.85.132 | 5432 | waiting | 0.333333 | slave | 0 | false | 0
停掉192.168.85.130 130服务器执行systemctl stop postgresql-9.5
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+----------------+------+--------+-----------+--------+------------+-------------------+-------------------
0 | 192.168.85.130 | 5432 | down | 0.333333 | slave | 0 | false | 0
1 | 192.168.85.131 | 5432 | up | 0.333333 | master | 0 | true | 0
2 | 192.168.85.132 | 5432 | up | 0.333333 | slave | 0 | false | 0
(3 rows)
启动130服务器 130服务器执行systemctl start postgresql-9.5
状态没变，pgpool服务器执行pgpool -C -D
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+----------------+------+---------+-----------+--------+------------+-------------------+-------------------
0 | 192.168.85.130 | 5432 | up | 0.333333 | master | 0 | true | 0
1 | 192.168.85.131 | 5432 | waiting | 0.333333 | slave | 0 | false | 0
2 | 192.168.85.132 | 5432 | waiting | 0.333333 | slave | 0 | false | 0
(3 rows)
状态对了！
-C, --clear-oidmaps Clear query cache oidmaps when memqcache_method is memcached
-D, --discard-status Discard pgpool_status file and do not restore previous status
