目录
一、高可用方案效果
二、配置方案
三、postgresql-12安装(3台机器均安装)
（一）yum 在线安装
（二）rpm离线安装
（三）postgresql-12 流复制配置
四、pgpool-ii4.1.2集群配置
（一）服务器SSH免密设置
（二）pgpool-ii 安装(三台机器均安装)
1.yum 在线安装
2.rpm离线安装
3.授权
（三）pgpool-ii 配置
1.pool_hba.conf
2.pcp.conf
3.pgpool.conf
（四）验证
（五）宕机验证
（1）pgpool-ii 节点宕机
（2）postgresql 数据库宕机
（3）服务器宕机
五、总结
一、高可用方案效果
- 某一个 postgresql 数据库挂掉 (多台数据库启动后 其中一台作为主机,其余作为备机 构成一个数据库集群);
- 如果是主机primary,集群检测到挂掉会通过配置的策略重新选一个备机standby切换为主机primary, 整个集群仍旧保证可用, 当原主机恢复服务后, 重新作为一个新备机standby,同步完数据后加入集群
- 如果是备机standby,对整个集群无可见影响, 当备机恢复服务后,从主库同步完数据后,恢复正常状态加入集群;
- 某一台机器上的pgpool-ii 程序挂掉;
- 监测每个pgpool-ii进程的状态, 监测到挂掉之后,及时"切换"虚拟ip所在的主机以保证可用性(有些人叫IP漂移);
- 整个集群始终对外提供一个唯一的,可用的虚拟IP 来提供访问;
- 监测每个主机postgresql数据库的状态, 以即使切换数据库的主备角色;
- 某一台主机直接宕机;
- 当pgpool-ii监测主机挂掉之后, 需要进行数据库角色的切换和ip的切换两个操作(如果需要)
二、配置方案
基于两台装有postgresql数据库的服务器，通过每台机器上的pgpool-ii程序来维护一个高可用体系, 从而保证能始终提供一个可用的IP地址,用于外界数据操作或者访问.
发行版 | ip | 补充说明 |
Cent OS7 | 10.242.111.204 | 安装postgresql 12.1 + pgpool-ii 4.1并进行配置 |
Cent OS7 | 10.242.111.205 | 安装postgresql 12.1 + pgpool-ii 4.1并进行配置 |
Cent OS7 | 10.242.111.206 | 安装postgresql 12.1 + pgpool-ii 4.1并进行配置 |
/ | 10.242.111.203 | virtual ip, 通过一个虚拟的IP统一对外提供访问 |
3(n)台主机均安装有postgresql 12 版本的数据库和pgpool-ii 4.1 版本的中间件;
3(n)个数据库之间可以做到数据同步以(通过流复制来实现, 但同一时刻主机primary只有一台,其余作为备机standby)及身份切换;
pgpool-ii 是一个介于postgresql 服务器和postgresql数据库之间的中间件, 提供了链接池(Connection Pooling),看门狗(WatchDog),复制,负载均衡,缓存等功能(具体的可以查看官方文档);
通过pgpool-ii 维护的虚拟ip, 向外界提供一个始终可用的访问地址, 屏蔽掉具体的主机数据库地址概念;
通过pgpool-ii 程序来自动处理宕机后相关方案(后面有讲)
数据库down之后需要通过pcp_attach_node将节点加入集群
流复制数据同步: 通过postgresql数据库配置来实现
虚拟ip自动切换: 通过pgpool-ii 配置实现
数据库主备角色切换: 通过pgpool-ii 监测机 + 执行 postgresql 中的promote命令来实现
三、postgresql-12安装(3台机器均安装)
（一）yum 在线安装
#设置rpm源
#安装(这里是版本为12的postgresql)
sudo yum install -y postgresql12-server
systemctl stop firewalld
systemctl disable firewalld
（二）rpm离线安装
# ftp下载rpm离线包
curl -O https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-12.3-1PGDG.rhel7.x86_64.rpm
curl -O https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-contrib-12.3-1PGDG.rhel7.x86_64.rpm
curl -O https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-libs-12.3-1PGDG.rhel7.x86_64.rpm
# 上传文件到服务器之后, 执行安装命令
rpm -ivh postgresql*.rpm
执行完安装之后(查看状态可跳过, 直接进行数据库初始化):
- 会帮我们创建一个postgresql-12服务, 此时未进行数据库初始化, 还无法访问.
- 会帮我们创建一个postgres/postgres 的用户，密码相同.
此时使用systemctl status postgresql-12 查看服务状态:
● postgresql-12.service - PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; disabled; vendor preset: disabled)
Active: inactive (dead)
Docs: https://www.postgresql.org/docs/12/static/
我们可以找到默认配置文件地址: /usr/lib/systemd/system/postgresql-12.service
如果cat命令查看配置文件, 我们可以得到一些基础信息:
数据库数据目录: Environment=PGDATA=/var/lib/pgsql/12/data/
postgresql安装目录: PGHOME=/usr/pgsql-12/
数据库初始化
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
#设置开机自启
systemctl enable postgresql-12 && systemctl restart postgresql-12
**本机测试访问 **
su - postgres
-bash-4.2$ psql
postgres=# (这里就可以开始写查询语句了)
**配置远程访问 **
修改数据目录下配置文件: pg_hba.conf
vim /var/lib/pgsql/12/data/pg_hba.conf# 在文件中添加:
host all all 0.0.0.0/0 md5
修改数据目录下配置文件: postgresql.conf
vim /var/lib/pgsql/12/data/postgresql.conf# 在文件中修改(此配置仅用于远程访问, 流复制后续还有额外配置):
listen_addresses = '*'
port = 5432
max_connections = 100
修改完成重启服务, 使用远程命令或者远程客户端测试访问即可
-- 使用远程命令访问
psql -h 10.242.111.204 -p 5432 -U postgres-- 登陆成功可以执行操作-- 如修改密码 (引号中为新密码)
postgres=# alter role postgres with password 'postgres';
（三）postgresql-12 流复制配置
1.流复制原理简述
流复制大约是从pg9版本之后使用, 流复制其原理为：备库不断的从主库同步相应的数据，并在备库apply每个WAL record，这里的流复制每次传输单位是WAL日志的record。(关于预写式日志WAL,是一种事务日志的实现)
图中可以看到流复制中日志提交的大致流程为:
- 事务commit后，日志在主库写入wal日志，还需要根据配置的日志同步级别，等待从库反馈的接收结果。
- 主库通过日志传输进程将日志块传给从库，从库接收进程收到日志开始回放，最终保证主从数据一致性。
- 流复制同步级别
通过在postgresql.conf配置synchronous_commit参数来设置同步级别
synchronous_commit = off # synchronization level; # off, local, remote_write, or on
remote_apply：事务commit或rollback时，等待其redo在primary、以及同步standby(s)已持久化，并且其redo在同步standby*(s)已apply。
on：事务commit或rollback时，等待其redo在primary、以及同步standby(s)已持久化。
remote_write：事务commit或rollback时，等待其redo在primary已持久化; 其redo在同步standby(s)已调用write接口(写到 OS, 但是还没有调用持久化接口如fsync)。
local：事务commit或rollback时，等待其redo在primary已持久化;
off：事务commit或rollback时，等待其redo在primary已写入wal buffer，不需要等待其持久化;
配置时需要注意的点:
- postgresql-12版本不再支持通过recovery.conf的方式进行主备切换，如果数据目录中存在recovery.conf，则数据库无法启动;
- 新增 recovery.signal 标识文件，表示数据库处于 recovery 模式;
- 新增加 standby.signal 标识文件，表示数据库处于 standby 模式(这个需要重点关注一下);
- 以前版本中 standby_mode 参数不再支持;
- recovery.conf文件取消, 合并到了postgresql.conf文件中;
- 配置中war_level存储级别, postgresql-9.6以后有改变:
等级 | 说明 |
minimal | 不能通过基础备份和wal日志恢复数据库 |
replica | 9.6新增,将之前版本的 archive 和 hot_standby合并, 该级别支持wal归档和复制 |
logical | 在replica级别的基础上添加了支持逻辑解码所需的信息 |
2．流复制配置过程
（1）主库、从库都要配置
创建一个账户repuser 用户, 提供给备库远程访问, 用来获取流:
su - postgres
-bash-4.2$ psql
postgres=# create role repuser login replication encrypted password 'repuser';
CREATE ROLE
postgres=# \q
修改数据目录下配置文件: pg_hba.conf
vim /var/lib/pgsql/12/data/pg_hba.conf# 在文件中添加:
host replication repuser 0.0.0.0/0 md5
修改数据目录下配置文件: postgresql.conf
vim /var/lib/pgsql/12/data/postgresql.conf# 在文件中修改(此配置仅用于远程访问, 流复制后续还有额外配置):
listen_addresses = '*'
port = 5432
max_connections = 100 # 最大连接数，据说从机需要大于或等于该值
# 控制是否等待wal日志buffer写入磁盘再返回用户事物状态信息。同步流复制模式需要打开。
synchronous_commit = on# *=all，意思是所有slave都被允许以同步方式连接到master，但同一时间只能有一台slave是同步模式。# 另外可以指定slave，将值设置为slave的application_name即可。
synchronous_standby_names = '*'
wal_level = replica
max_wal_senders = 2 #最多有2个流复制连接
wal_keep_segments = 16
wal_sender_timeout = 60s #流复制超时时间
修改完成重启postgresql-12服务
systemctl restart postgresql-12
（2）从库(所有从库服务器都要操作)
关闭备库postgresql数据库
systemctl stop postgresql-12
权限配置
如果备库机器上没有 PGDATA(/var/lib/pgsql/12/data)目录(恢复出故障数据，目录消失同样操作)
mkdir /var/lib/pgsql/12/data
chmod 0700 data
chown postgres.postgres data
把主库整个备份到从库
其实后续的pgpool的主库挂了, 从库升级主库之后, 主库恢复为从库的过程就是: 备份data目录,然后重复这里的第2,3步骤
# 切换到postgres用户，否则备份的文件属主为root, 导致启动出错
su – postgres
pg_basebackup -h 10.242.111.204 -p 5432 -U repuser -Fp -Xs -Pv -R -D /var/lib/pgsql/12/data# -h 的ip是当前的主库, -U 就是前面个船舰的用来复制的那个用户
额外需要注意的是:
如果主库的pg_hba.conf中配置的策略为trust, 这里不需要口令, 如果为md5模式,需要输你创建用户时的那个密码;
这里 -R 参数一定要加, 拷贝完在$PGDATA目录下生成standby.signal标志文件(用于表示此库为备库);
使用命令同步完之后, 在data目录下会自动生成postgresql.auto.conf文件中, 优先级是大于postgresql.conf的;
启动备库postgresql-12 服务
systemctl restart postgresql-12
3.流复制验证
使用命令或者远程客户端工具登入postgresql主(primary)数据库
# 切换垂直显示
postgres=# \x
Expanded display is on.# 查询备机连接
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 27132
usesysid | 16384
usename | repuser
application_name | walreceiver
client_addr | 10.242.111.205
client_hostname |
client_port | 34726
backend_start | 2020-06-19 16:23:01.309172+08
backend_xmin |
state | streaming
sent_lsn | 0/11000148
write_lsn | 0/11000148
flush_lsn | 0/11000148
replay_lsn | 0/11000148
write_lag |
flush_lag |
replay_lag |
sync_priority | 1
sync_state | sync
reply_time | 2020-06-19 18:39:43.346062+08# 可以发现207的那台备机已经连接上, 并且使用的是 sync 同步模式(async 为异步)# 此时可以在主库上创建数据库/表/添加数据, 然后去备库上查询 以显示效果, 如:
postgres=# create database test_204;
postgres=# \c test_204
postgres=# create table test_table(name text);
postgres=# insert into test_table(name) values ('china');# 补充基础查询命令# \list 列出可用数据库# \db_name 选择数据库# \dt 查询关系表
至此, 流复制(数据同步)策略完成.
postgresql 主从切换(primary->standby)
- 主数据库是读写的，备数据库是只读的。
- 使用/usr/pgsql-12/bin/pg_controldata /var/lib/pgsql/12/data/ 可以查看数据库运行状态
- Database cluster state: in production (主库为运行中)
- Database cluster state: in archive recovery (从库为正在归档回复)
- 当主库primary 出现故障, 我们需要将从库standby提升为主库primary;
- pg12版本以前的切换方式有两种:
- pg_ctl 方式: 在备库主机执行 pg_ctl promote shell 脚本
- 触发器文件方式: 备库配置 recovery.conf 文件的 trigger_file 参数，之后在备库主机上创建触发器文件
- pg12开始新增了一个pg_promote()函数, 支持知用promote指令进行切换
- pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)
- wait: 表示是否等待备库的 promotion 完成或者 wait_seconds 秒之后返回成功，默认值为 true
- wait_seconds: 等待时间，单位秒，默认 6
- 切换举例
# 关闭主库primary
systemctl stop postgresql-12# 从库上执行切换# 第一种
/usr/pgsql-12/bin/pg_ctl promote -D /var/lib/pgsql/12/data# 第三种
su postgres
psql
postgres=# select pg_promote(true,60);# 此时从库会提升为主库接管集群中的读写操作pgpool-ii 中默认的 failover_command 切换数据库主备角色脚本, 是通过pg_ctl promote 来实现的
四、pgpool-ii4.1.2集群配置
概述:
在postgresql12 安装完成之后, 我们需要安装pool-ii中间件, 来完成 HA 自动切换, ip 自动切换的工作。当某一个数据库挂掉之后, 检测其生命状态, 执行相应策略(主库挂掉, 切换某个备库为主库等)；当某一个pgpool节点不可用, 其他节点收到信息进行IP转移(访问入口接管)。
（一）服务器SSH免密设置
为后续使用 hostname 和脚本中 SSH 免登录:
#临时关闭selinux
setenforce 0
#永久关闭selinux
修改 /etc/selinux/config 文件中的 SELINUX=enforcing 修改为 SELINUX=disabled
注意：需要关闭selinux，否则免登陆设置后还是要求输入密码
# 主机/备机操作
su – postgres
-bash-4.2$ ssh-keygen
# 一直enter首先修改postgres用户密码，以root用户操作：
passwd postgres
输入密码如postgres后即可修改。
公钥拷贝到其他服务器（每台服务器执行相同指令）
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub 10.242.111.204
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub 10.242.111.205
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub 10.242.111.206
注意：
- 提示输入密码，请输入第二步修改的密码，如postgres
- 如提示未找到/var/lib/pgsql/.ssh目录，在root用户创建目录:
创建目录、赋予权限
mkdir /var/lib/pgsql/.ssh
chown postgres /var/lib/pgsql/.ssh
注意本服务器也需要执行ssh免登陆，因切换主节点时候，需要使用ip登录
（二）pgpool-ii 安装(三台机器均安装)
1.yum 在线安装
#设置rpm源
curl -O https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-release-4.1-2.noarch.rpm
rpm -ivh pgpool-II-release-4.1-2.noarch.rpm
#安装(关于对应的 postgresql 版本,体现在文件名中的 pgXX 这里)
yum -y install pgpool-II-pg12
yum -y install pgpool-II-pg12-debuginfo
yum -y install pgpool-II-pg12-devel
yum -y install pgpool-II-pg12-extensions
2.rpm离线安装
# ftp下载rpm离线包
curl -O https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-pg12-4.1.2-1pgdg.rhel7.x86_64.rpm
curl -O https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-pg12-debuginfo-4.1.2-1pgdg.rhel7.x86_64.rpm
curl -O https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-pg12-devel-4.1.2-1pgdg.rhel7.x86_64.rpm
curl -O https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-pg12-extensions-4.1.2-1pgdg.rhel7.x86_64.rpm
# 上传文件到服务器之后, 执行安装命令
rpm -ivh pgpool*.rpm
补充: 源码编译安装参照官方指定文档：Installation of Pgpool-II
3.授权
因为pgpool-ii 配置中会以 postgres 用户执行一些系统权限命令, 需要使用设置普通用户授权:
使用服务器root账号配置
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/
（三）pgpool-ii 配置
1.pool_hba.conf
pool_hba.conf 是配置用户链接时的验证策略, 和postgresql的pg_hba.conf保持一致，要么都是trust，要么都是md5验证方式，这里采用了md5验证方式如下设置:
[root@localhost ~] su - postgres
-bash-4.2$ cd /etc/pgpool-II/
-bash-4.2$ vim pool_hba.conf#编辑内容如下(这里和postgressql设置一样, trust/md5保持一致)# IPv4 local connections:
host all all 0.0.0.0/0 md5
host all all 0/0 md52.pcp.conf
这个文件是pgpool管理器自己的用户名和密码，用于管理集群的.
-bash-4.2$ pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5
-bash-4.2$ vim pcp.conf#编辑内容如下:# USERID:MD5PASSWD
postgres: e8a48653851e28c69d0506508fb27fc5#在pgpool中添加pg数据库的用户名和密码
[postgres@etc~]$ pg_md5 -p -m -u postgres pool_passwd#数据库登录用户是postgres,这里输入登录密码，不能出错#输入密码后，在/etc/pgpoll-II目录下会生成一个pool_passwd文件3.pgpool.conf
这个文件中会配置我们pgpool-ii 节点的关键参数, pgpool-ii 自带提供几种不同模式的配置文件:
# 执行完前面步骤 /etc/pgpool-II/目录下的文件, 我们需要用到的
failover.sh (数据库故障切换脚本)
pcp.conf (用户访问验证策略trust/md5)
pgpool.conf (pgpool-ii 主配置文件)
pool_hba.conf (集群节点密码管理)
pool_passwd (数据库密码管理文件)
recovery_1st_stage.sample (在线故障恢复的脚本示例, 放到postgresql数据目录/var/lib/pgsql/12/data 下)
主库pgpool-ii 节点(10.242.111.204-master)
#常用基础配置
pid_file_name = '/var/run/pgpool/pgpool.pid'# pid 文件位置， 如果不配置有默认的
logdir = '/var/run/pgpool' # status 文件存储位置# 通用
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
# 后台数据库链接信息配置
backend_hostname0 = '10.242.111.204' # 第一台数据库信息
backend_port0 = 5432
backend_weight0 = 1 # 这个权重和后面负载比例相关
backend_data_directory0 = '/var/lib/pgsql/12/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '10.242.111.205' # 第二台数据库信息
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/12/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = '10.242.111.206' # 第三台数据库信息
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/12/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
# 流复制相关配置
replication_mode = off # pgpool-ii 中复制制模式关闭
load_balance_mode = on # 负载均衡打开
master_slave_mode = on # 主从打开
master_slave_sub_mode = 'stream' # 主从之间模式为流传输stream
sr_check_period = 5 # 流复制检查相关配置
sr_check_user = 'repuser'
sr_check_password = 'repuser'
sr_check_database = 'postgres'
- 数据库故障转移(故障后处理)
# 数据库运行状况检查，以便Pgpool-II执行故障转移: 数据库的主备切换
health_check_period = 10 # Health check period, Disabled (0) by default
health_check_timeout = 20 # 健康检查的超时时间,0 永不超时
health_check_user = 'postgres' # 健康检查的用户
health_check_password = 'postgres' # 健康检查的用户密码
health_check_database = 'postgres' # 健康检查的数据库
# 故障后处理, 为了当postgresql数据库挂掉之后执行相应的策略# 这个脚本时放在pgpool的目录下, 确切的说是由pgpool执行脚本来维护集群中数据库的状态
failover_command = '/etc/pgpool-II/failover_stream.sh %d %P %H %R' # follow_master_command = '' # 2台服务器不配置# 如果使用3台PostgreSQL服务器，则需要指定follow_master_command在主节点故障转移上的故障转移后运行。如果有两个PostgreSQL服务器，则无需follow_master_command设置。
具体配置脚本详见pgpool.conf文件
配置文件中传入的相应参数请参照: config-failover-params
- watchdog(看门狗)配置(用于检测pgpool-ii 节点状态, 为后续pgpool故障处理提供依据)
use_watchdog = on # 激活看门狗配置
wd_hostname = 'master' # 当前主机(也可使用IP)
wd_port = 9000 # 工作端口
# 虚拟IP指定
delegate_IP = '10.242.111.203'
if_cmd_path = '/sbin' # 如果if_up_cmd, if_down_cmd 以/开头, 忽略此配置# 命令中的`ens160` 请根据自己机器上ip addr 实际的网卡名称进行修改# 当前节点启动指定虚拟IP的命令
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ifcfg-ens03 label ifcfg-ens03:0'# 当前节点指定关闭虚拟IP的命令
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ifcfg-ens03'
# watchdog 健康检查
wd_heartbeat_port = 9694 # 健康检查端口
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30# 其他机器地址配置(多台请增加配置)
heartbeat_destination0 = '10.242.111.205'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'ifcfg-ens03'
heartbeat_destination0 = '10.242.111.206'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'ifcfg-ens03'
# 其他pgpgool节点链接信息(多台请增加配置)
other_pgpool_hostname0 = '10.242.111.205' # 其他节点地址
other_pgpool_port0 = 9999
other_wd_port0 = 9000 # 其他节点watchdof 端口
# watchdog 发生故障后, 处理的相关配置(宕机, pgpool进程终止)# 当某个节点故障后,
other_pgpool_hostname1 = '10.242.111.206' # 其他节点地址
other_pgpool_port1 = 9999
other_wd_port1 = 9000
failover_when_quorum_exists = on
failover_require_consensus = on
allow_multiple_failover_requests_from_node = on
enable_consensus_with_half_votes = on
- 关于watchdog本身(pgpool-ii)发生故障后相关的处理策略, 请务必阅读官方文档: CONFIG-WATCHDOG-FAILOVER-BEHAVIOR
- watchdog本身(pgpool-ii节点)本身故障后, 如果配置打开, 其他节点会执行仲裁, 如仲裁从节点中那一个成为主节点, 那一台阶段虚拟IP等, 这个仲裁本身有投票机制, 和无视仲裁结果等配置;
- 如果不配置, 主pgpool-i 节点关闭后, 可能不会转移虚拟IP, 出现集群暂时不可访问;
- 注意网卡配置，需要根据ip addr命令结果的配置
- 关于在线恢复(master 恢复后自动变为备库)
# 此配置将在多个pgpool-ii 节点时无效
recovery_user = 'postgres'
recovery_password = 'postgres'
recovery_1st_stage_command = 'recovery_1st_stage' # 这个脚本时放在postgresql数据目录下的
如果有多个pgpool-ii 节点共同维护集群状态, 此配置将不可用, 需要手动恢复同步数据>加入集群
备库pgpool-ii 节点(10.242.111.205和10.242.111.206-slave)
# 将主库的配置文件拷贝过来
在备份节点服务器上的/etc/pgpool-II/目录下操作以下命令：
scp 10.242.111.205:/etc/pgpool-II/\{pool_hba.conf,pcp.conf,pool_passwd,pgpool.conf,failover.sh, follow_matser.sh\} .
#备份节点服务器迁移recovery_1st_stage文件
cp recovery_1st_stage.sample recovery_1st_stage
mv recovery_1st_stage /var/lib/pgsql/12/data
# 修改以下几项即可
wd_hostname = '10.242.111.205' # 当前机器ip
heartbeat_destination0 = '10.242.111.204' # 其他pg库机器（如10.242.111.204）
heartbeat_destination1 = '10.242.111.206' # 其他pg库机器（如10.242.111.206）
other_pgpool_hostname0 = '10.242.111.204' # 其他pgpool节点机器
other_pgpool_hostname1 = '10.242.111.206' # 其他pgpool节点机器
（四）验证
启动命令
su - postgres# 启动命令(日志位置可在命令中指定)
pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 & # 有debug日志
pgpool -n -D > /var/log/pgpool/pgpool.log 2>&1 & # 无debug日志
# 终止命令
pgpool -m fast stop
配置相关脚本权限
chown postgres.postgres /etc/pgpool-II
chmod +x /etc/pgpool-II/follow_matser.sh
Chmod +x /etc/pgpool-II/failover.sh
启动完成之后, 可登陆虚拟IP, 进行集群状态查询
psql -h vip -p9999 -Upostgres -d postgres#或
psql -h 10.242.111.203 -p9999 -Upostgres -d postgres
查询结果可见:
[root@slave ~]# psql -h 10.242.111.203 -p9999 -Upostgres -d postgres
Password for user postgres:
psql (12.3)
Type "help" for help.
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 10.242.111.204 | 5432 | up | 0.500000 | primary | 0 | true | 0 | | | 2020-06-22 17:48:51
1 | 10.242.111.205 | 5432 | up | 0.500000 | standby | 0 | false | 0 | | | 2020-06-22 17:48:51
2 | 10.242.111.206 | 5432 | up | 0.500000 | standby | 0 | false | 0 | | | 2020-06-22 17:48:51
(3 rows)
- lb_weight和前面pool.conf配置中backend_weight0 = 1的比例有关;
- role 为postgresql 数据库的主备状态;
- up为数据库已加入集群管理;
- 此时可以通过虚拟IP链接, 执行新增/修改/删除等操作来测试集群正常工作状态的数据同步;
（五）宕机验证
（1）pgpool-ii 节点宕机
处理关键步骤:
- 如果时正常关闭: 释放虚拟IP > 节点正常关闭 > 其他节点检测到时区链接 > 仲裁选出新的"主节点" > 接管虚拟IP > 正常提供服务
- 如果异常关闭: 其他节点检测到时区链接且IP不可用> 仲裁选出新的"主节点" > 接管虚拟IP > 正常提供服务
# 在master节点上停止pgpool服务
-bash-4.2$ pgpool -m fast stop
# 原从节点切换完之后，访问集群# 可查看/var/log/pgpool/pgpool.log 日志, 可看到被关闭的释放虚拟IP, 其他节点接管虚拟IP
-bash-4.2$ psql -h 10.242.111.203 -p 9999
# 提示输入密码：
Type "help" for help.
postgres=# show pool_nodes;
（2）postgresql 数据库宕机
# 模拟数据库宕机
systemctl stop postgresql-12
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | 10.242.111.204 | 5432 | down | 0.500000 | standby | 0 | false | 0
1 | 10.242.111.205 | 5432 | up | 0.500000 | primary | 0 | true
2 | 10.242.111.206 | 5432 | up | 0.500000 | standby | 0 | true | 0
(3 rows)#slave已经被切换成primary，且master节点状态是down
- master的数据库挂(primary)掉了，同时变成了standby，pgpool执行数据库故障切换脚本failover.sh,slave正在作为新的主数据库(primary)提供服务
- 这里当master 重启之后, 需要恢复同步数据, 重新加入集群
节点重新加入集群步骤：
1. 恢复同步数据
# 2. 清除当前"备库"上的data目录
mkdir /var/lib/pgsql/12/data
chmod 0700 data
chown postgres.postgres data
# 3. 执行复制命令
su – postgres
pg_basebackup -h 目标机器IP -p 5432 -U repuser -Fp -Xs -Pv -R -D /var/lib/pgsql/12/data
# 4. 启动服务(切换到root用户)
systemctl restart postgresql-12
# 5. 重新将postgresql数据库加入集群管理(su - postgres)# -n 节点序号, postgresql 数据库在集群中的节点序号
pcp_attach_node -d -U postgres -h vip -p 9898 -n 0
# 6. 此时
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | 10.242.111.204 | 5432 | up | 0.500000 | standby | 0 | false | 0
1 | 10.242.111.205 | 5432 | up | 0.500000 | primary | 0 | true | 0
2 | 10.242.111.206 | 5432 | up | 0.500000 | standby | 0 | false | 0
(3 rows)如果只有一个pgpool-ii 节点, 可以使用pgpool.conf中配置的recovery_1st_stage_command在线恢复来操作. 多个pgpool节点此配置不支持.
（3）服务器宕机
- 主机直接宕机, 意味着当前机器时区所有链接, 其他pgpool-ii 节点检测不到原"主pgpool-ii"节点存活
- 首先, 选举出一个pgpool-ii 节点承担责任
- 接管维护虚拟IP的责任, 启动指定的虚拟ip
- 此时检测到原主数据库primary不可链接, 执行故障转移脚本, 将当前备库转换为主库为集群提供存储服务;
五、总结
- 数据库postgresql 层面的故障转移执行切换脚本, 本质上执行的还是 pg_ctl promote命令;
- pgpool-ii 层面故障, 后续的检测/切换虚拟IP, 是不影响下层postgresql数据库运行状态的;
- 在主机直接挂掉的情况下, 上面两个步骤是都要执行的;
- 数据库宕机恢复的本质依然是同步流复制pg_basebackup命令;
- 即使是pgpool-ii 提供的在线恢复,本质上也是几个步骤的脚本:
清空备机节点数据目录 > 使用pg_basebackup命令同步数据 > 启动数据库服务 > 将数据库重新加入集群 - 如pgpool-ii故障切换出现问题，请查看/var/log/pgpool/pgpool.log日志和/var/log/messages日志，结合一起综合分析异常信息
注意：本文参考（参考原文不能直接使用，会出现各种环境问题）。在此基础上优化，可以直接使用到正式环境。
