PostgreSQL中间件：pgpool简单安装

概述

Pgpool 是一个基于pg的强大的中间件，可以提供很多功能，如连接池，负载均衡，高可用，复制等，下面就来简单描述下它的安装步骤
参考技术书籍《PostgreSQL实战》完成

安装准备

下载地址

http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/

选择合适的安装

yum localinstall pgpool-II-pg10-4.0.3-1pgdg.rhel7.x86_64.rpm

配置/etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.6.27 clup-27
192.168.6.26 clup-26

两个节点配置pool_hba.conf文件
添加

host all all 0.0.0.0/0 trust

配置pgpool文件(两台机器都需配置)

pcp_listen_addresses = '*'

节点配置
backend_hostname0 = 'clup-27'
backend_port0 = 1921
backend_weight0 = 1
backend_data_directory0 = '/pgdata'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'clup26'
backend_port1 = 1921
backend_weight1 = 1
backend_data_directory1 = '/pgdata'
backend_flag1 = 'ALLOW_TO_FAILOVER'

检查机制设置
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = 'postgres'
sr_check_database = 'postgres'
delay_threshold = 0


高可用迁移脚本设置
follow_master_command = '/etc/pgpool-II/failover_stream.sh %d %P %H %R'

健康机制设置
health_check_period = 5
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'postgres'
health_check_database = 'postgres'
health_check_max_retries = 3
health_check_retry_delay = 3

看门狗设置
use_watchdog = on
wd_hostname = 'clup-27'
wd_port = 9000
wd_priority = 1

vip的设置
delegate_IP = '192.168.6.31'
if_cmd_path = '/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev ens3 label ens3:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev ens3'

心跳检测
heartbeat_destination0 = 'clup-26'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'ens3'

存活检查
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'postgres'
wd_lifecheck_user = 'postgres'
wd_lifecheck_password = 'postgres'

other_pgpool_hostname0 = 'clup-26'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

高可用切换脚本

一旦触发高可用，就会启用该脚本（该脚本是通过pgpool官方提供的脚本修改而成）

[root@clup-26 pgpool-II]# cat failover_stream.sh 
#!/bin/bash

falling_node=$1
old_primary=$2
new_primary=$3
pgdata=$4
pghome=/usr/pgsql
log=/tmp/failover.log
date >> $log


#输出变量到日志，方便脚本出现异常调试
echo "falling_node=$falling_node" >> $log
echo "old_primary=$old_primary" >> $log
echo "new_primary=$new_primary" >> $log
echo "pgdata=$pgdata" >> $log


#如果故障的数据库并且执行脚本的为root

if [ $falling_node = $old_primary ] && [ $UID -eq 0]; then
	if [ -f $pgdata/recovery.conf ]; then
		su - postgres -c "$pghome/bin/pg_ctl promote -D $pgdata"
		echo "local promote " >> $log
	else
		su - postgres -c "ssh -T postgres@$new_primary $pghome/bin/pg_ctl promote -D $pgdata"
		echo "remote promote" >> $log
	fi
fi;
exit 0;

启动pgpool

直接使用pgpool命令即可启动，无报错之后

[root@clup-27 pgpool-II]# psql -h 192.168.6.31 -p 9999 postgres postgres
psql.bin (10.1)
Type "help" for help.

postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change  
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
 0 | clup-27 | 5432 | up | 0.500000 | primary | 0 | true | 0 | 2019-03-07 21:50:58
 1 | clup26 | 5432 | down | 0.500000 | standby | 0 | false | 0 | 2019-03-07 21:37:02
(2 rows)

pcp管理接口装置

添加用户

[root@clup-27 pgpool-II]# pg_md5 pgpool
ba777e4c2f15c11ea8ac3be7e0440aa0

把信息添加到pcp.conf中

可看到相关信息

[root@clup-27 pgpool-II]# pcp_node_info --verbose -h192.168.6.31 -U pgpool 0
Password: 
Hostname : clup-27
Port : 5432
Status : 1
Weight : 0.500000
Status Name : waiting
Role : primary
Replication Delay : 0
Last Status Change: 1970-01-01 08:00:00

查看集群信息

[root@clup-27 pgpool-II]# pcp_watchdog_info --verbose -h192.168.6.31 -U pgpool
Password: 
Watchdog Cluster Information 
Total Nodes : 2
Remote Nodes : 1
Quorum state : QUORUM IS ON THE EDGE
Alive Remote Nodes : 0
VIP up on local node : YES
Master Node Name : clup-27:9999 Linux clup-27
Master Host Name : clup-27

Watchdog Node Information 
Node Name : clup-27:9999 Linux clup-27
Host Name : clup-27
Delegate IP : 192.168.6.31
Pgpool port : 9999
Watchdog port : 9000
Node priority : 1
Status : 4
Status Name : MASTER

Node Name : Not_Set
Host Name : clup-26
Delegate IP : Not_Set
Pgpool port : 9999
Watchdog port : 9000
Node priority : 0
Status : 0
Status Name : DEAD