使用Pgpool搭建Postgresql集群

操作系统：CentOS7

Postgresql版本：10.10
Pgpool-II版本：4.0.6

端口
  9999：对外提供服务
  9000：watch_dog
  9898：pcp
  9694：watch_dog心跳检查

备注：下文的安装步骤中，【】包围的部分需根据自己的环境来配置

背景

  项目上有实现数据库高可用的需求，Postgresql本身支持数据同步和手动failover。即，一个双机热备的Postgresql集群，主节点宕机时，整个集群只能对外提供读服务，而无法提供写服务，只有手动执行promote命令晋升备节点为主节点，才能恢复数据库服务的正常。以下是Postgresql官方文档的引文:

PostgreSQL does not provide the system software required to identify a
failure on the primary and notify the standby database server.

  Pgpool通过其WatchDog的虚拟IP和健康检查功能实现了自动故障切换。本文使用master-slave模式搭建集群，不使用replication_mode的原因是该模式下的online recovery存在限制，Pgpool官网引文如下：

There is a restriction in the online recovery in replication_mode. If
Pgpool-II itself is installed on multiple hosts, online recovery does
not work correctly, because Pgpool-II has to stop all the clients
during the 2nd stage of online recovery. If there are several
Pgpool-II hosts, only one of them will have received the online
recovery command and will block the connections from clients.

  为实现分区容错，本文使用3个节点搭建集群，每个节点安装Pgpool-II和Postgresql数据库，数据库为1主2备。
  注意：Pgpool-II和Postgresql集群对于主节点使用的术语不同，Pgpool-II的主节点称为Master，Postgresql的主节点称为Primary；备节点使用的术语相同，都为standby。

步骤

1 安装并配置PostgreSQL
  在所有节点安装PostgreSQL，但仅需要在节点1配置并启动PostgreSQL，然后使用pcp_recovery_node功能初始化并启动节点2、3上的Postgresql服务，注意不要让PostgreSQL开机自启。参考PG数据库安装。

2 配置/etc/hosts
  增加DNS配置，供recovery_1st_stage使用

【节点1的IP】 【节点1的主机名】
【节点2的IP】 【节点2的主机名】
【节点3的IP】 【节点3的主机名】

3 配置ssh免密通信
  Pgpool的failover配置中使用的failover.sh、follow_master.sh，以及online recovery配置中使用的recovery_1st_stage、pgpool_remote_start脚本中，都涉及通过ssh进行远程操作。
  需要配置root用户到postgres用户（包括本机的postgres用户）的单向，以及postgres用户之间的双向免密通信。
3.1 设置postgres用户的密码

su
passwd postgres

3.2 在当前节点执行以下语句，生成密钥
  注意不要设置passphrase

su 【当前用户】
ssh-keygen -t rsa

3.3 将公钥发送到其他节点

ssh-copy-id -i ~/.ssh/id_rsa.pub 【用户名】@【IP】

3.4 测试ssh

ssh 【用户名】@【IP】

4 修改ssh配置（可选，默认10个一般够）

vi /etc/ssh/sshd_config

修改

MaxSessions 100

重启sshd

systemctl restart sshd

5 配置系统日志
  创建日志文件，应创建在/var/log路径下，否则syslog无法写入文件（除非关闭或配置SELinux）。

mkdir /var/log/pgpool
touch /var/log/pgpool/pgpool_run.log

修改syslog配置

vi /etc/rsyslog.conf

修改

*.info;mail.none;authpriv.none;cron.none;LOCAL1.none  /var/log/messages
LOCAL1.*                                        /var/log/pgpool/pgpool_run.log

重启

systemctl restart rsyslog

6 配置pgpass
  为了允许数据库的postgres用户无需为流复制和在线恢复指定密码，对于每个节点，在操作系统的postgres用户的主目录中创建.pgpass文件，权限改为600。

su postgres
vi ~/.pgpass

编辑

# hostname:port:database:username:password
# In a standby server, a database field of replication matches streaming replication connections made to the master server.
【节点1的IP】:5432:postgres:postgres:123456
【节点2的IP】:5432:postgres:postgres:123456
【节点3的IP】:5432:postgres:postgres:123456

授权

chmod 600 ~/.pgpass

7 安装Pgpool
  root用户执行，在所有节点安装和配置Pgpool；为了使用在线恢复功能，需要pgpool_recovery、pgpool_remote_start、pgpool_switch_xlog等函数，因此需要安装pgpool_recovery。
  注意：安装pgpool_recovery需要先安装postgresql-devel。

mkdir /usr/local/pgpool
cd /usr/local/pgpool
tar -zxvf pgpool-II-4.0.6.tar.gz
cd /usr/local/pgpool/pgpool-II-4.0.6
./configure --with-pgsql=/usr/local/pgsql
make && make install
cd /usr/local/pgpool/pgpool-II-4.0.6/src/sql/pgpool-recovery
chown -R postgres /usr/local/pgsql
make && make install
cp /usr/local/etc/pgpool.conf.sample-stream /usr/local/etc/pgpool.conf

8 在节点1的Postgresql上安装pgpool_recovery扩展
  使用PG创建数据库时，实际上是通过复制一个现有的数据库来工作的。默认情况下，它复制名为template1的标准系统数据库。

psql template1 -c "CREATE EXTENSION pgpool_recovery"
psql postgres -c "CREATE EXTENSION pgpool_recovery"

9 编写failover脚本
在所有节点上新建配置文件，并赋予执行权限

vi /usr/local/pgpool/failover_stream.sh

编辑脚本（注意和pgpool.conf中failover_command参数的联系），如果primary宕机，晋升standby；如果standby宕机，仅记录日志。

#!/bin/bash
# This script is run by failover_command.

set -o xtrace
logger -i -p local1.info failover_command begin

# 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

FAILED_NODE_ID="$1"
FAILED_NODE_HOST="$2"
FAILED_NODE_PORT="$3"
FAILED_NODE_PGDATA="$4"
NEW_MASTER_NODE_ID="$5"
NEW_MASTER_NODE_HOST="$6"
OLD_MASTER_NODE_ID="$7"
OLD_PRIMARY_NODE_ID="$8"
NEW_MASTER_NODE_PORT="$9"
NEW_MASTER_NODE_PGDATA="${10}"

PGHOME=/usr/local/pgsql

logger -i -p local1.info failover.sh: start: failed_node_id=${FAILED_NODE_ID} old_primary_node_id=${OLD_PRIMARY_NODE_ID} \
    failed_host=${FAILED_NODE_HOST} new_master_host=${NEW_MASTER_NODE_HOST}

## Test passwrodless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa ls /tmp > /dev/null

if [ $? -ne 0 ]; then
    logger -i -p local1.