PostgreSQL-pgpool高可用方案

准备

两台虚拟机或者实体机，IP，数据库以及数据库软件版本：

172.16.247.11   master      CentOS Linux release 7.2.1511 (Core)
172.16.247.12   slave       CentOS Linux release 7.2.1511 (Core)
172.16.247.13    vip

PostgreSQL 9.6.14
pgpool-II version 3.7.10

安装postgresql，各个节点均执行

联网安装

• 设置postgresql源

curl -O  https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
rpm -ivh pgdg-redhat-repo-latest.noarch.rpm

• 系统设置

systemctl stop firewalld
systemctl disable firewalld
setenforce 0
sed -i "s@SELINUX=enforcing@SELINUX=disabled@" /etc/sysconfig/selinux

• 安装

yum -y install postgresql96 postgresql96-server

离线安装

• 下载rpm安装包

curl -O https://yum.postgresql.org/9.6/redhat/rhel-6-x86_64/postgresql96-9.6.14-1PGDG.rhel6.x86_64.rpm
curl -O https://yum.postgresql.org/9.6/redhat/rhel-6-x86_64/postgresql96-contrib-9.6.14-1PGDG.rhel6.x86_64.rpm
curl -O https://yum.postgresql.org/9.6/redhat/rhel-6-x86_64/postgresql96-libs-9.6.14-1PGDG.rhel6.x86_64.rpm
curl -O https://yum.postgresql.org/9.6/redhat/rhel-6-x86_64/postgresql96-server-9.6.14-1PGDG.rhel6.x86_64.rpm

• 安装

rpm -ivh postgresql96-*.rpm

初始化

/usr/pgsql-9.6/bin/postgresql96-setup initdb

设置开机自启

systemctl start postgresql-9.6
systemctl enable postgresql-9.6
# 或者
su – postgres; /usr/pgsql-9.6/bin/pg_ctl start –D /var/lig/pgsql/data

测试访问

su - postgres
-bash-4.2$  psql
postgres=#
# postgres用户默认密码是postgres，所以不用输入就可以进入了

创建流复制

主库（172.16.247.11）操作

• 创建账号repuser，用于远程连接复制

su - postgres
-bash-4.2$  psql
postgres=# create role repuser login replication encrypted password 'repuser';
postgres=# \q

• 配置数据目录

数据目录是在/var/lib/pgsql/9.6/data/下，可以通过systemctl status postgresql-9.6查看到service目录是在/usr/lib/systemd/system/postgresql-9.6.service，里面的Environment=PGDATA=/var/lib/pgsql/9.6/data，其中设置的数据目录是/var/lib/pgsql/9.6/data/

• 修改配置文件pg_hba.conf

cd /var/lib/pgsql/9.6/data
vi pg_hba.conf
# 添加如下内容
host     replication     all             0.0.0.0/0               md5
host     all             all             0.0.0.0/0               md5

• 修改配置文件postgresql.conf

vi postgresql.conf
# 内容修改如下
listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_segments = 128
hot_standby = on
hot_standby_feedback = on

• 创建配置文件recovery.done

vi /var/lib/pgsql/9.6/data/recovery.done
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=slave port=5432 user=repuser password=repuser'
trigger_file = '/var/lib/pgsql/data/trigger_file'

• 设置权限

chown postgres.postgres recovery.done

• 重启数据库

systemctl restart postgresql-9.6 
# 或者使用命令启动 
su – postgres; /usr/pgsql-9.6/bin/pg_ctl start –D /var/lig/pgsql/data

备库（172.16.247.12）操作

• 关闭备库服务

systemctl stop postgresql-9.6

• 备份数据

# 进入数据目录
cd /var/lib/pgsql/9.6/
# 备份数据，并创建空的数据存储目录
mv data/ data_back
mkdir data
chmod 0700 data
chown postgres.postgres data

• 把主库整个备份到备库（切换到postgres用户，否则备份的文件属主为root）

pg_basebackup -D /var/lib/pgsql/9.6/data -Fp -Xs -v -P -h master -p 5432 -U repuser

使用root账号同步的数据默认属主是root，需要设置权限执行：chown postgres.postgres -R *

• 修改配置文件：postgresql.conf

该配置文件是从主库备份是自动复制过来的，需要修改如下内容

max_connections = 200

• 修改配置文件

mv recovery.done recovery.conf
vi recovery.conf
# 如下内容
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=master port=5432 user=repuser password=repuser'
trigger_file = '/var/lib/pgsql/data/trigger_file'

修改host为master

• 重启备库

systemctl restart postgresql-9.6
# 或者
su – postgres; /usr/pgsql-9.6/bin/pg_ctl start –D /var/lig/pgsql/data

验证流复制是否成功

• 在主库查看是否有备库连接，并创建一个库test_ry，一个表test_table并添加数据

主库进程

• 备库登录是否能看到创建的库和表

备份进程

PGPool-II + PG流复制实现HA主备切换

pgpool-II 3.7.10

免密设置

• 修改hosts文件，添加如下内容（两台均添加）

172.16.247.11   master    
172.16.247.12   slave 
172.16.247.13    vip

• 设置免密

  • 主库操作

  # 设置密码
  passwd postgrs 
  su – postgres
  -bash-4.2$ ssh-keygen
  ssh-copy-id -i .ssh/id_rsa.pub master
  ssh-copy-id -i .ssh/id_rsa.pub slave

  • 备库操作

  # 设置密码
  passwd postgrs 
  su – postgres
  ssh-keygen
  ssh-copy-id -i .ssh/id_rsa.pub master
  ssh-copy-id -i .ssh/id_rsa.pub slave

安装pgpool-II 3.7.10（两台均操作）

• 联网安装

curl -O http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-release-3.7-1.noarch.rpm
rpm -ivh pgpool-II-release-3.7-1.noarch.rpm
yum -y install pgpool-II-pg96
yum -y install pgpool-II-pg96-debuginfo
yum -y install pgpool-II-pg96-devel
yum -y install pgpool-II-pg96-extensions

• 离线安装（准备好pgpool-II的包）

curl -O http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-pg96-3.7.10-1pgdg.rhel7.x86_64.rpm
curl -O http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-pg96-debuginfo-3.7.10-1pgdg.rhel7.x86_64.rpm
curl -O http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-pg96-devel-3.7.10-1pgdg.rhel7.x86_64.rpm
curl -O http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-pg96-extensions-3.7.10-1pgdg.rhel7.x86_64.rpm
rpm -ivh *.rpm

• 配置系统命令权限

让普通用户也有权限可以执行命令，postgres用户执行切换脚本需要

chmod u+x /usr/sbin/ip
chmod u+s /usr/sbin/arping
chmod u+s /sbin/ip

• 设置pid目录权限和创建日志目录

chown postgres.postgres /var/run/pgpool
mkdir –p /var/log/pgpool/
touch /var/log/pgpool/pgpool_status
chown –R postgres.postgres /var/log/pgpool/

主库（172.16.247.11）操作

• 配置文件pool_hba.conf，添加如下内容

目录为：/etc/pgpool-II/

host    all         all          0.0.0.0/0             md5
#host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

• 配置文件pcp.conf

  目录为：/etc/pgpool-II/

  • 生成配置的用户名密码

  pg_md5 postgres
  # 对密码为postgres，进行pg_md5操作，会产生一个md5字符串，例如：
  fjaifiweqifgwoifjjsafjdhfaklhknbv

  • 主库节点将上面生成的内容复制进pcp.conf，内容如下

  用于管理集群（用于pcp_attach_node命令告知集群节点已经恢复，重新加入集群）

  # USERID:MD5PASSWD
  postgres:fjaifiweqifgwoifjjsafjdhfaklhknbv

• 在pgpool中添加pg数据库的用户名密码

密码用postgres，和上面一致

pg_md5 -p -m -u postgres pool_passwd

会自动生成文件pool_passwd，这里面存着数据库的账号密码

当连接pgpool监听的端口时，pgpool会先检测pool_passwd文件是否存在输入的账号密码，然后再使用该账号密码连接后端服务器

如果数据库中有repuser这个账号但是pgpool没有，或者pgpool有admin账号但是数据库没有都会报错

• 配置pgpool.conf，内容如下

listen_addresses = '*'
port = 9999
socket_dir = '/var/run/postgresql'
listen_backlog_multiplier = 2
serialize_accept = off
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/postgresql'

backend_hostname0 = 'master'
backend_port0 = 5432
backend_weight0 = 5
backend_data_directory0 = '/var/lib/pgsql/9.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'slave'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.6/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60

ssl = off

replication_mode = off
replicate_select = off
insert_lock = on
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'

sr_check_period = 5
sr_check_user = 'repuser'
sr_check_password = 'repuser'
sr_check_database = 'postgres'
delay_threshold = 0

follow_master_command = ''

health_check_period = 10
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'postgres'
health_check_database = 'postgres'
health_check_max_retries = 0
health_check_retry_delay = 1
connect_timeout = 10000

failover_command = ' /var/lib/pgsql/9.6/failover_stream.sh %H'
failback_command = ''
fail_over_on_backend_error = on
search_primary_node_timeout = 300

recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0

use_watchdog = on
trusted_servers = ''
ping_path = '/bin'
wd_hostname = 'master'
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/var/run/postgresql'

delegate_IP = '172.16.247.13'                   # 虚拟IP地址
if_cmd_path = '/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev eno16780032 label eno16780032:0'    # eno16780032是接口名
if_down_cmd = 'ip addr del $_IP_$/24 dev eno16780032:0'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'

wd_monitoring_interfaces_list = ''
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'slave'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'ens33'

wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'nobody'
wd_lifecheck_password = ''

other_pgpool_hostname0 = 'slave'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

创建切换脚本/opt/failover_stream.sh，设置权限
#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.

new_master=$1
trigger_command="/usr/pgsql-9.6/bin/pg_ctl promote -D /var/lib/pgsql/9.6/data"

# Prompte standby database.
/usr/bin/ssh -T $new_master $trigger_command

exit 0;

• 设置脚本权限

chmod 777 /var/lib/pgsql/9.6/failover_stream.sh
chown postgres.postgres /var/lib/pgsql/9.6/failover_stream.sh

备库（172.16.247.12）操作

• 配置文件

从主库复制配置文件pgpool.conf, pcp.conf, pool_passwd, pool_hba.conf 覆盖掉备库的配置文件

scp -r /etc/pgpool-II/* slave:/etc/pgpool-II/

• 修改配置文件pgpool.conf部分内容

wd_hostname = 'slave'
heartbeat_destination0 = 'master'
other_pgpool_hostname0 = 'master'

• 脚本

从主库复制切换脚本/opt/failover_stream.sh到本地对于目录

scp -r /var/lib/pgsql/9.6/failover_stream.sh slave:/var/lib/pgsql/9.6/
chmod 777 /var/lib/pgsql/9.6/failover_stream.sh
chown postgres.postgres /var/lib/pgsql/9.6/failover_stream.sh

启动验证

启动pgpool服务（两台均启动）

su - postgres
pgpool -n -C –D
# -D 表示会清除以前的状态
# -n 前台模式启动  如果后台启动，不加-n

这里使用postgres账号启动，因为当故障切换时，需要执行脚本（脚本中有pg_ctl命令），root用户是无法执行的

验证

登录

使用虚IP登录pgpool监听的tcp 9999端口

psql -h vip -p 9999 -U postgres -d postgres
show pool_nodes; # 查看状态
SELECT pg_is_in_recovery();   # 查看当前库的状态，f表示不是备库，t表示备库

测试切换

• 主库故障

  # 关闭master（172.16.247.11）上的主库
  systemctl stop postgresql-9.6
  

虚IP(虚IP可能会在172.16.247.11，也可能会在172.16.247.12，所以ssh免密登录时，要设置可以免密登录自己)所在的节点pgpool会ssh远程到slave（172.16.247.12）执行切换脚本

slave（172.16.247.12）主机上的备库会把recover.conf重命名为recover.done后重启，变成主库

• 从日志可以看到节点是否有shutdown，是否执行了命令/var/lib/pgsql/9.6/failover_stream.sh 主机IP（备库切换成主库）

• 再次查看状态，master节点（172.16.247.11）变成down状态，同时变成standby；slave节点（172.16.247.12）变成primary

• 主库恢复

  • master（172.16.247.11）主机上

  mv recover.done recover.conf
  # 同时修改postgresql.conf确保max_connections参数的值是大于主库的
  

  • 重启服务

  systemctl restart postgresql-9.6
  

  • 在集群中添加节点状态，将master节点添加至集群中，此时的master节点是备库状态

  pcp_attach_node -d -U postgres -h vip -p 9898 -n 0
  

  • 查看状态，节点master的状态为waiting

  

  • 再次模拟故障（此时要恢复至最初始的状态）

  • slave（172.16.247.12）主库故障

  systemctl stop postgresql-9.6
  

此时主库切回到master节点（172.16.247.11）

• 恢复slave节点至集群

  
  mv recovery.done  recovery.conf
  systemctl start postgresql-9.6
  pcp_attach_node -d -U postgres -h vip -p 9898 -n 1

• 再次查看状态

  show pool_nodes
  

常见问题

• psql登录的时候提示SSL关闭等，都是权限问题，查看pg_hba.conf配置的权限是否正确

• 时间线不一致导致无法同步

  • 增量备份，同步时间线的方式同步数据库

  
  pg_rewind  --target-pgdata=/home/postgres/data --source-server='host=master port=5432 user=postgres dbname=postgres password=nariadmin'

• 参数设置master节点

  wal_log_hints = on

• 可以使用全备份

  • 查看日志提示时间线不匹配cat pg_log/postgresql-Wed.log

# 停止服务,并删除data下的所有文件
systemctl stop postgresql 
# 全部备份
pg_basebackup -D /var/lib/pgsql/data -Fp -Xs -v -P -h slave -p 5432 -U repuser

chown -R postgres.postgres *
mv recovery.done  recovery.conf

# 修改内容master为slave
vi recovery.conf
# 启动服务
systemctl start postgresql