Postgressql高可用（pgpool+异步流复制）

文档结构：

由于博主之前是Oracle dba,oracle 高可用一般是rac,以及搭建ADG,一个是基于实例的高可用，一个是基于数据的容灾，甚至也有rac+adg的方式。Postgres有同步和异步两种同步方式，我发现，mysql,oracle,postgresql 这两种同步模式原理都是一个德行，可能都是绕不开关系型数据库吧。

异步方式主库上的事务不会等待备库接受日志流发出确认信息后主库才向客户端返回成功，同步方式事务会主库等待至少一个备库接受日志流发出确认信息后便返回成功；很明显，异步时间会有延迟，同步会增加相应时间，但是保证了数据的一致性，在资源允许的情况下，可以一主多从且采取一个备库同步，多个备库异步方式。

   除了pgpool异步高可用，还可以采用keepalived方式，本次就简单实验pgpool异步方式。

什么是pgpool-Ⅱ

Pgpool-II是一个在PostgreSQL服务器和PostgreSQL数据库客户端之间工作的中间件。它是根据BSD许可证授权的。它提供以下功能。

连接池

Pgpool-II保存与PostgreSQL服务器的连接，并在具有相同属性（即用户名，数据库，协议版本）的新连接进入时重用它们。它减少了连接开销，并提高了系统的整体吞吐量。

复制

Pgpool-II可以管理多个PostgreSQL服务器。使用复制功能可以在2个或更多物理磁盘上创建实时备份，以便在磁盘发生故障时服务可以继续运行而不会停止服务器。

负载均衡

如果复制了数据库，则在任何服务器上执行SELECT查询都将返回相同的结果。Pgpool-II利用复制功能，通过在多个服务器之间分配SELECT查询来减少每个PostgreSQL服务器的负载，从而提高系统的整体吞吐量。充其量，性能与PostgreSQL服务器的数量成比例地提高。在许多用户同时执行许多查询的情况下，负载平衡最有效。

限制超出连接

PostgreSQL的最大并发连接数有限制，连接在这么多连接后被拒绝。但是，设置最大连接数会增加资源消耗并影响系统性能。pgpool-II对最大连接数也有限制，但额外连接将排队，而不是立即返回错误。

看家狗

Watchdog可以协调多个Pgpool-II，创建一个强大的集群系统，避免单点故障或脑裂。看门狗可以对其他pgpool-II节点执行生命检查，以检测Pgpoll-II的故障。如果活动Pgpool-II发生故障，则可以将备用Pgpool-II提升为活动状态，并接管虚拟IP。

在内存查询缓存中

在内存中查询缓存允许保存一对SELECT语句及其结果。如果有相同的SELECT，Pgpool-II将从缓存中返回值。由于不涉及SQL解析或访问PostgreSQL，因此在内存缓存中使用速度非常快。另一方面，在某些情况下，它可能比正常路径慢，因为它增加了存储缓存数据的一些开销。

Pgpool-II讲PostgreSQL的后端和前端协议，并在后端和前端之间传递消息。因此，数据库应用程序（前端）认为Pgpool-II是实际的PostgreSQL服务器，服务器（后端）将Pgpool-II视为其客户端之一。因为Pgpool-II对服务器和客户端都是透明的，所以现有的数据库应用程序可以与Pgpool-II一起使用。Pgpool-II讲述PostgreSQL的后端和前端协议，并在它们之间传递连接。因此，数据库应用程序（前端）认为Pgpool-II是实际的PostgreSQL服务器，服务器（后端）将Pgpool-II视为其客户端之一。因为Pgpool-II对服务器和客户端都是透明的，所以现有的数据库应用程序可以与Pgpool-II一起使用，几乎不需要更改其源码。

以下测试主要来自谭峰（francs）老师的《postgres 实战》

一．Pgpool-Ⅱ的安装

主机名	说明	IP地址	端口	版本
10pg1	主库	192.168.10.41	5432	Postgres10.8
	pgpool主		5555
VIP	192.168.10.61
10pg2	备库	192.168.10.51	5433	Postgres10.8
	pgpool备		5555

其中，应用连接vip,防火墙建议关闭，同时，生产上肯定不是postgres这库了，肯定时业务库了，这个按照实际情况配置。

1.   下载地址

https://www.pgpool.net/mediawiki/index.php/Downloads

我是采用rpm的最新版本4.0.6。

2.   部署（主备）

yum -y install libmemcached

export PATH=$PATH:/usr/local/pgsql/bin/

mkdir /pgpool

./configure --prefix=/pgpool --with-pgsql=/pgpool/

make

make install

安装生成的配置文件在/pgpool/ 目录下。

注意如果提示：

configure: error: libpq is not installed or libpq is old

如果配置了环境变量（PATH,PGHOME,PGDATA）还是出现以上错误，建议使用

./configure --prefix=/pgpool

make

make install

并把pgpool配置到环境变量中（root,postgres用户下）

1.   配置pgpool主备服务器互信（非root设置）

我用postgres 执行故障转移脚本，所以进行互信设置把

vi /etc/hosts

192.168.10.41  10pg1

192.168.10.52  10pg2

ssh-keygen 

ssh-copy-id postgres@10pg2 

 

同理在备库上执行

ssh-keygen

ssh-copy-id postgres@10pg1

测试：

 ssh postgres@10pg2

ssh postgres@10pg1

1.   配置pool_hba.conf连接配置文件

Postgres针对连接策略需要配置pg_hba.conf文件，而pgpool连接中由于应用程序是线连接pgpool,然后通过pgpool再连接到后端数据库，因此也需要在pgpool层面进行连接配置。

Pgpool的配置跟pg_hba.conf一样的。

cd /pgpool/etc/

cp pool_hba.conf.sample pool_hba.conf

vi pool_hba.conf

加上192.168.10.0 这个网段的ip 都可以的访问

host    all         all         0.0.0.0/0      md5

1.   配置密码配置文件

默认文件是不存在的，生成pool_passwd配置文件

pg_md5 命令生成

pg_md5 -u postgres -m 密码

或者 手工建一个文件，怕密码泄露

select rolpassword from pg_authid where rolname='postgres';

vi pool_passwd

postgres:md53175bce1d3201d16594cebf9d7eb3f9d

rep:md5df2c887bcb2c49b903aa33bdbc5c2984

1.   配置pgpool.conf配置文件

cp /pgpool/etc/pgpool.conf.sample-stream /pgpool/etc/pgpool.conf

vi /pgpool/etc/pgpool.conf

需要修改的（由于参数太多直接写结果吧）：

主库参数配置：

 

port = 5555

listen_addresses = '*'          #表示监听所有地址连接（跟postgres参数一样的意思）

backend_hostname0 = '192.168.10.41'   #配置节点0的hostname

backend_port0 = 5432          #主库端口

backend_weight0 = 1           #没有开启模式参数配不配之都可以

backend_data_directory0 = '/pgsql/pg_data'  #节点0数据目录

backend_flag0 = 'ALLOW_TO_FAILOVER'   #主库是否允许故障转移

backend_hostname1 = '192.168.10.51'

backend_port1 = 5433

backend_weight1 = 1

backend_data_directory1 = '/pgsql/pg_data'

backend_flag1 = 'ALLOW_TO_FAILOVER'

enable_pool_hba = on  #表示启用pool_hba.conf

pool_passwd = 'pool_passwd'   #设置MD5认证的密码文件

log_destination = 'syslog'  #日志

pid_file_name = '/pgpool/pgpool.pid'

load_balance_mode = off  #关闭负载均衡（如果开启，select语句会在备库执行）

master_slave_mode = on

master_slave_sub_mode = 'stream'

sr_check_period = 10  #流复制检查10s

sr_check_user = 'rep' #我同步采用的rep 用户

sr_check_password = 'rep'

sr_check_database = 'postgres'  #流复制检查连接的数据库

delay_threshold = 10000000 这个是备库延迟wal/xlog日志大于10000000字节，将不会select语句分发到备库

health_check_period= 5

health_check_timeout = 20

health_check_user = 'rep'

health_check_password = 'rep'

health_check_database = 'postgres'

health_check_max_retries = 3

health_check_retry_delay = 3

failover_command = '/pgpool/etc/failover_stream.sh %d %P %H %R'

use_watchdog = on #启用watchdog

wd_hostname = '192.168.10.41'

wd_port = 9000

wd_priority = 1 ##表示watchdog的优先级，级别越高则被选为主节点，一主一从设置一样

delegate_IP = '192.168.10.61' #设置的VIP

if_cmd_path = '/sbin'

if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' 我的环境是eth0

if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'

heartbeat_destination0 = '192.168.10.51'

heartbeat_destination_port0 = 9694

heartbeat_device0 = 'eth0'

wd_life_point = 3 #当探测pgpool节点失败后设置重试次数

wd_lifecheck_query = 'SELECT 1'

wd_lifecheck_dbname = 'postgres'

wd_lifecheck_user = 'rep'

wd_lifecheck_password = 'rep'

other_pgpool_hostname0 = '10pg2'  #设置远程pgpool节点主机

other_pgpool_port0 = 5555  #设置远程pgpool端口号

other_wd_port0 = 9000     #设置远程pgpool节点watchdog端口号

从库参数配置：

 

port = 5555

listen_addresses = '*'          #表示监听所有地址连接（跟postgres参数一样的意思）

backend_hostname0 = '192.168.10.41'   #配置节点0的hostname

backend_port0 = 5432        #端口

backend_weight0 = 1           #没有开启模式参数配不配之都可以

backend_data_directory0 = '/pgsql/data'  #节点0数据目录

backend_flag0 = 'ALLOW_TO_FAILOVER'   #节点0是否允许故障转移

backend_hostname1 = '192.168.10.51'     #节点1的主机名

backend_port1 = 5433      #节点1的端口

backend_weight1 = 1           #没有开启模式参数配不配之都可以

backend_data_directory1 = '/pgsql/data'  #节点1数据目录

backend_flag1= 'ALLOW_TO_FAILOVER'   #节点1是否允许故障转移

enable_pool_hba = on  #表示启用pool_hba.conf

pool_passwd = 'pool_passwd'   #设置MD5认证的密码文件

log_destination = 'syslog'  #日志

pid_file_name = '/pgpool/pgpool.pid'

load_balance_mode = off  #关闭负载均衡（如果开启，select语句会在备库执行）

master_slave_mode = on

master_slave_sub_mode = 'stream'

sr_check_period = 10  #流复制检查10s

sr_check_user = 'rep' #我同步采用的rep 用户

sr_check_password = 'rep'

sr_check_database = 'postgres'  #流复制检查连接的数据库

delay_threshold = 10000000 这个是备库延迟wal/xlog日志大于10000000字节，将不会select语句分发到备库

health_check_period= 5

health_check_timeout = 20

health_check_user = 'rep'

health_check_password = 'rep'

health_check_database = 'postgres'

health_check_max_retries = 3

health_check_retry_delay = 3

failover_command = '/pgpool/etc/failover_stream.sh'

use_watchdog = on #启用watchdog

wd_hostname = '10pg2'

wd_port = 9000

wd_priority = 1  ##表示watchdog的优先级，级别越高则被选为主节点，一主一从设置一样

delegate_IP = '192.168.10.61' #设置的VIP

if_cmd_path = '/sbin'

if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' 我的环境是eth0

if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'

heartbeat_destination0 = '192.168.10.41'

heartbeat_destination_port0 = 9694

heartbeat_device0 = 'eth0'

wd_life_point = 3  #当探测pgpool节点失败后设置重试次数

wd_lifecheck_query = 'SELECT 1'

wd_lifecheck_dbname = 'postgres'

wd_lifecheck_user = 'rep'

wd_lifecheck_password = 'rep'

other_pgpool_hostname0 = '192.168.10.41'  #设置远程pgpool节点主机

other_pgpool_port0 = 5555  #设置远程pgpool端口号

other_wd_port0 = 9000     #设置远程pgpool节点watchdog端口号

1.  配置failover_stream.sh脚本

cat /pgpool/etc/failover_stream.sh

#! /bin/bash

# Executes this command after master failover

# Special values:

#   %d = node id

#   %h = host name

#! /bin/bash

# Executes this command after master failover

# Special values:

#   %d = node id

#   %h = host name

#   %p = port number

#   %D = database cluster path

#   %m = new master node id

#   %H = hostname of the new master node

#   %M = old master node id

#   %P = old primary node id

#   %r = new master port number

#   %R = new master database cluster path

#   %% = '%' character

falling_node=$1

old_primary=$2

new_primary=$3

pgdata=$4

pghome=/usr/local/pgsql/

log=/pgpool/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;

脚本主要是通过有没有recovery.conf文件来判断为主备，会调用ip addr添加或者删除IP地址，使用root维护pgpool程序会方便些。需要给root 添加环境变量：

export PGPOOL_HOME=/pgpool

export PATH=$PATH:$HOME/bin:/pgpool/bin/:$PGPOOL_HOME/bin

在主库上启动pgpool(root用户)

pgpool

查看主库启动的vip

查看日志：

查看p'g'pool参数的使用：

个人感觉跟pg_ctl 类似，尤其是加载参数：

pgpool reload

或者关闭pgpool -m fast stop

Shutdown modes are:

  smart       quit after all clients have disconnected

  fast        quit directly, with proper shutdown

  immediate   the same mode as fast

关闭是这三种状态

日志查看是：

tail -100f /var/log/messages

后面启动从库pgpool：

主库日志：

 

从库日志：

如果从库起不来 或者报错，那肯定是参数设置错了，根据日志去修改参数，值得注意的是，这里面参数很多是本库的，也有很多是远端库的，一定要配置对。

通过vip 查看pgpool 状态：

psql -h 192.168.10.61 -p5555 postgres

show pool_nodes;

悲剧的是主库居然pgpool 状态为down,也就是日志说的node 0 0,正常应该node 0 2;

 

反复检查参数配置没有错，于是执行（一般主备库重启后，状态异常可以执行以下语句）：

pcp_attach_node -h 192.168.10.61 -U pgpool 0

注意，0为节点1，如果是备库有问题，就是pcp_attach_node -h 192.168.10.61 -U pgpool 1，然后就正常了

如下图：

如果主备库关闭过，也需要重新连接pgpool

pcp_attach_node -h 192.168.10.61 -U pgpool 0或者1

8.  PCP管理接口配置

pgpool提供一个用于管理pgpool 的系统层命令工具，pcp用户属于pgpool 层面，和数据库中的用户没有关系，例如查看pgpool 节点信息，增加pgpool 节点，断开pgpool 节点等。

例如，我设置PCP层面密码为pgpool

 

[root@10pg1 ~]# pg_md5 pgpool

ba777e4c2f15c11ea8ac3be7e0440aa0

cd /pgpool/etc/

cp pcp.conf.sample pcp.conf

添加到

vi pcp.conf

二．高可用测试

1.  查看pgpool状态

pcp命令查看pcp节点，watchdog信息等。

pcp_node_info --verbose -h 192.168.10.61 -U pgpool 0

 

Status 字段值意思：

0为初始化

1为以启动没有连接

2为以启动有链接

3节点关闭

查看watchdog集群信息：

pcp_watchdog_info --verbose -h 192.168.10.61 -U pgpool 

2.  高可用测试

a. 关闭pgpool程序

停掉主节点的pgpool 程序

 pgpool -m stop fast

 

查看主库IP：

主库已经删除了VIP 61

查看系统日志：

主库：

备库：

备库接管了VIP

登录进去查看：

psql -h 192.168.10.61 -p5555 postgres postgres

虽然备库接管了VIP，但是没有发生failover切换，单独的pgpool程序故障并不会发生数据库主备切换（主机数据库宕机除外，待会测试）。

b. 只关闭postgres 流复制主库

把主库上的pgpool开启，主库服务器没有接管VIP，重启一下备库pgpool,让主库接管vip,再启动备库pgpool做关闭数据库测试。

pg_ctl stop -D /pgsql/pg_data  -m fast

查看备库日志：

查看VIP

可以查看的是VIP 虽然仍然再10pg1上，但是其实现在连接的时新主库：

测试：

已经发生failover.

查看新主库:

Failover 成功。查看关闭的库failover的日志：

这个日志时再原主库上生成的failover日志

c. 关闭postgres 主库主机

重新搭建主从同步。

关闭主库主机相当于关闭数据库和pgpool了，个人猜想时vip 会飘过来，也会执行备库提升为主库。

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

show pool_nodes;

关闭主库服务器。

经过几秒钟VIP漂移过来了，而且备库提升为主库了。

查看新主库pgpool日志：

查看新主库发生failover的日志：

查看新主库信息

 

d. 总结

按照步骤来安装以及测试，如果参数没有配置错误，实验还是会比较成功。值得注意的时要配置pool_passwd和pcp密码，以及pgpool参数文件配置的时候，不要把主从参数配错了，最后更值得注意的时failover脚本，如果写错了不会发生故障转移，再重启过数据库或者pgpool程序时，如果pgpool节点状态不正常，可以利用pcp_attach_node把pgpool节点加进去,感觉备库切换主库的时候，不是立马，有点延迟，应该是跟时间参数配置有关。