postgresql 14 高可用 pgsql高可用方案

本文建立在数据库主从复制(Master-Slave)的基础上。

Master：10.19.100.2 port：5432 数据库版本PostgreSQL-10.3

Slave：10.19.100.3 port：5432 数据库版本PostgreSQL-10.3

pgpool-II使用虚拟IP：10.19.100.4 软件版本 pgpool-II 3.7.2

一 pgpool中间件及HA简介

在没有中间件的情况下，在Master数据库宕机，需要手工提升Standby为Master，还需要应用主动把连接切换到新Master库上才能保证整个系统能继续对外提供服务。为了实现自动切换，增加系统可靠性，这里可以考虑pgpool中间件。pgpool可以检测数据库集群中的Master节点是否存活，在Master节点失效时可以自动提升Standby库，并且pgpool本身可以做高可用部署，搭建多个pgpool，使用vip在多个pgpool上漂移。

pgpool-II 3.7对原来旧版本的几个影响用户使用的问题进行了fix，增加了新的pgpool主从模式和参数设置，通过pgpool主动切换主备后，备库如果没有问题，也可以直接加入集群了(实测pg 9.6.8也可用)。而之前版本的pgpool，脱离集群的节点无法重新加入进去。

注意1：如果需要输出pgpool日志，需要设置服务器的selinux为disable。且做以下修改

# vi /etc/rsyslog.conf
local0.*    /PostgreSQL/pgpool/pgpool.log

# /etc/init.d/rsyslog restart

注意2：通过pgpool登录数据库目前仅支持数据库白名单pg_hba.conf中的password验证方式。

二 pgpool安装

2.1 编译安装软

# tar zxvf pgpool-II-3.7.2.tar.gz
# cd pgpool-II-3.7.2
# ./configure --prefix=/PostgreSQL/pgpool
# make
# make install

2.2 配置pgpool管理用户和密码

Pgpool管理员用户密码位于其配置文件pcp.conf中，手工编辑即可。其中密码需要使用pg_md5加密。

# /PostgreSQL/pgpool/bin/pg_md5 123456
e10adc3949ba59abbe56e057f20f883e

# vi /PostgreSQL/pgpool/etc/pcp.conf
postgres:e10adc3949ba59abbe56e057f20f883e

2.3 创建Master/Standby切换脚本

# vi /PostgreSQL/pgpool/failover.sh
 
#! /bin/sh
new_master=$1
pathdir=$2
/usr/bin/ssh -T $new_master /PostgreSQL/10/bin/pg_ctl promote -D $pathdir
exit 0;

切换脚本权限

# chmod 755 /PostgreSQL/pgpool/failover.sh

2.4 软件权限

# chown -r postgres:postgres /PostgreSQL/pgpool

三 配置pgpool

3.1配置master节点的pgpool

pgpool没有必要和数据库部署在同一台服务器上，我这里为了配置方便pgpool master节点和master库放在同一台服务器上。

在Master节点上安装pgpool

安装方法见第一节。

修改配置文件pgpool.conf

Pgpool的etc目录下自带多种配置文件范例，我这里使用最基础的版本pgpool.conf.sample进行修改，仅为数据库高可用考虑，两套pgpool间为主从关系。如果需要负载均衡等其他功能，请详细阅读pgpool.conf.sample内的说明后进行配置。

$ cd /PostgreSQL/pgpool/etc
$ cp pgpool.conf.sample pgpool.conf
$ vi pgpool.conf

这里单独列出要修改的内容：

listen_addresses = '*'
backend_hostname0 = '10.19.100.2'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/PostgreSQL/10/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '10.19.100.3'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/PostgreSQL/10/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
log_destination = 'syslog'
pid_file_name = '/PostgreSQL/pgpool/pgpool.pid'
logdir = '/PostgreSQL/pgpool'
master_slave_mode = on
sr_check_user = 'postgres'
sr_check_password = '123456'
sr_check_database = 'postgres'
health_check_user = 'postgres'
health_check_password = '123456'
health_check_database = 'postgres'
failover_command = '/PostgreSQL/pgpool/failover.sh %H %R'
use_watchdog = on
wd_hostname = '10.19.100.2'
delegate_IP = '10.19.100.4'
heartbeat_destination0 = '10.19.100.3'
wd_lifecheck_dbname = 'postgres'
wd_lifecheck_user = 'postgres'
wd_lifecheck_password = '123456'
other_pgpool_hostname0 = '10.19.100.3'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

ifconfig权限

如果需要非root用户启动Pgpool时能启动虚拟IP，需要配置该权限

$ su -
Password:

# chmod u+s /sbin/ip

ssh信任

$ su - postgres
$ ssh-keygen
$ ssh-copy-id postgres@10.19.100.3
$ ssh-copy-id postgres@10.19.100.2

3.2配置Standby节点的pgpool

在Standby节点上安装pgpool

安装方法见第一节。

修改配置文件pgpool.conf

修改内容和Master节点基本相同，这里仅列出不同的部分

wd_hostname = '10.19.100.3'
heartbeat_destination0 = '10.19.100.2'
other_pgpool_hostname0 = '10.19.100.2'

ifconfig权限

如果需要非root用户启动pgpool时能启动虚拟IP，需要配置该权限

$ su -
Password:

# chmod u+s /sbin/ip

ssh信任

$ su - postgres
$ ssh-keygen
$ ssh-copy-id postgres@10.19.100.2
$ ssh-copy-id postgres@10.19.100.3

四 启动pgpool

启动Master节点的pgpool

$ /PostgreSQL/pgpool/bin/pgpool -f /PostgreSQL/pgpool/etc/pgpool.conf

启动后可以观察到虚拟IP生效

$ ifconfig eth0:0

eth0:0    Link encap:Ethernet  HWaddr 08:00:27:56:B5:91 
          inet addr:10.19.100.4  Bcast:0.0.0.0  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

启动Standby节点的pgpool

$ /PostgreSQL/pgpool/bin/pgpool -f /PostgreSQL/pgpool/etc/pgpool.conf

五 使用pgpool连接数据库

通过pgpool的虚拟IP和端口即可连接数据库，整个过程对用户透明，不需要更改任何连接工具：

$ psql -U postgres -d postgres -h 10.19.100.2 -p 9999
Password for user postgres:

psql.bin (10.3)
Type "help" for help.

postgres=#

可以通过命令看到数据库各个节点状态

postgres=# show pool_nodes;

 node_id |  hostname   | port | status | lb_weight |  role   | select_cnt | load_balance_node | replic
ation_delay

---------+-------------+------+--------+-----------+---------+------------+-------------------+-------
------------

 0       | 10.19.100.2 | 5432 | up     | 0.500000  | primary | 0          |false             | 0

 1       | 10.19.100.3 | 5432 | up     | 0.500000  | standby | 0          | true             | 0

(2 rows)

六 pgpool切换

这里把10.19.100.2拔电，模拟Master节点宕机，数据库Master节点和pgpool Master节点全部失去的情况。

Master节点下电后在Standby节点服务器上可以看到：

虚拟IP发生漂移

 

$ ifconfig eth0:0

eth0:0    Link encap:Ethernet  HWaddr 08:00:27:56:B5:91 
          inet addr:10.19.100.4  Bcast:0.0.0.0  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

Standby库变为Master库

postgres=# show pool_nodes;

 node_id |  hostname   | port | status | lb_weight |  role   | select_cnt | load_balance_node | replic
ation_delay

---------+-------------+------+--------+-----------+---------+------------+-------------------+-------
------------

 0       | 10.19.100.2 | 5432 | down   | 0.500000  | standby| 0          | true              | 0

 1       | 10.19.100.3 | 5432 | up     | 0.500000  | primary | 0          | false             | 0

(2 rows)

我这里因为只是模拟故障，因此重启10.19.100.2，将主库的recovery.done文件重命名为recovery.conf后，重新启动主库即可重新恢复流复制。流复制恢复后使用下面的命令把节点重新加入pgpool集群。

$ /PostgreSQL/pgpool/bin/pcp_attach_node -h 10.19.100.4 -p 9898 -U postgres -n 1
Password:

pcp_attach_node -- Command Successful

附： 主从节点上的pgpool详细配置文件