Postgresql12 + pgpool-ii 4.1.2 高可用集群[主备切换]配置和脚本编辑

最新推荐文章于 2024-04-30 00:37:35 发布

SoGreater

最新推荐文章于 2024-04-30 00:37:35 发布

阅读量4.2k

点赞数 1

分类专栏： postgresql shell 高可用文章标签： postgresql

本文链接：https://blog.csdn.net/qq_36607860/article/details/123711358

版权

postgresql 同时被 3 个专栏收录

1 篇文章 0 订阅

订阅专栏

shell

1 篇文章 0 订阅

订阅专栏

高可用

1 篇文章 0 订阅

订阅专栏

Postgresql12 + pgpool-ii 4.1.2 高可用集群[主备切换]配置和脚本编辑

高可用(容灾)效果:

先说说这套方案要达到的效果,解决的问题,目的不一致的,没有想了解的可以不用往下看了.解决三种宕机

某一个 postgresql 数据库挂掉 (多台数据库启动后其中一台作为主机,其余作为备机构成一个数据库集群);
- 如果是主机primary,集群检测到挂掉会通过配置的策略重新选一个备机standby切换为主机primary, 整个集群仍旧保证可用, 当原主机恢复服务后, 重新作为一个新备机standby,同步完数据后加入集群
- 如果是备机standby,对整个集群无可见影响, 当备机恢复服务后,从主库同步完数据后,恢复正常状态加入集群;
某一台机器上的pgpool-ii 程序挂掉;
- 监测每个pgpool-ii进程的状态, 监测到挂掉之后,及时"切换"虚拟ip所在的主机以保证可用性(有些人叫IP漂移);
- 整个集群始终对外提供一个唯一的,可用的虚拟IP 来提供访问;
- 监测每个主机postgresql数据库的状态, 以即使切换数据库的主备角色;
某一台主机直接宕机;
- 当pgpool-ii监测主机挂掉之后, 需要进行数据库角色的切换和ip的切换两个操作(如果需要)

方案结构:

基于两台装有postgresql数据库的服务器，通过每台机器上的pgpool-ii程序来维护一个高可用体系, 从而保证能始终提供一个可用的IP地址,用于外界数据操作或者访问.

发行版	ip	hostname	补充说明
Cent OS7	10.242.111.204	master	安装postgresql 12.1 + pgpool-ii 4.1并进行配置
Cent OS7	10.242.111.207	slave	安装postgresql 12.1 + pgpool-ii 4.1并进行配置
/	10.242.111.203	vip	virtual ip, 通过一个虚拟的IP统一对外提供访问

2(n)台主机均安装有postgresql 12 版本的数据库和pgpool-ii 4.1 版本的中间件;
2(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命令来实现

关闭防火墙或者配置防火墙白名单

# 配置防火墙白名单（这里举例firewalld防火墙，可能有iptable）
-bash-4.2$ sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent
-bash-4.2$ sudo firewall-cmd --zone=public --add-port=9999/tcp --permanent
-bash-4.2$ sudo firewall-cmd --zone=public --add-port=9898/tcp --permanent
-bash-4.2$ sudo firewall-cmd --zone=public --add-port=9000/tcp --permanent
-bash-4.2$ sudo firewall-cmd --zone=public --add-port=9694/tcp --permanent
-bash-4.2$ sudo firewall-cmd --reload

# 或者关闭防火墙
-bash-4.2$ systemctl stop firewalld
-bash-4.2$ systemctl disable firewalld

postgresql-12安装(2台机器均安装)

yum 在线安装

# 设置rpm源
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

# 安装(这里是版本为12的postgresql)
yum -y install postgresql12 postgresql12-server  -O

# 如果后续发现连接不上,可以关闭防火墙
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
curl -O https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-server-12.3-1PGDG.rhel7.x86_64.rpm
# contrib 是安装扩展的 没有这个包就没有 ossp-uuid的插件
# server 是数据库的安装文件
# libs 用来客户端进行连接. 
# 注意 如果是centos8 的话,修改为 rhel-8 进行下载就可以了.

# 上传文件到服务器之后, 执行安装命令
rpm -ivh postgresql*.rpm

执行完安装之后(查看状态可跳过, 直接进行数据库初始化):

会帮我们创建一个postgresql-12服务, 此时未进行数据库初始化, 还无法访问.
会帮我们创建一个postgres/postgres 的用户，密码相同.

此时使用systemctl status postgresql-12 查看服务状态

我们可以找到默认配置文件地址: /usr/lib/systemd/system/postgresql-12.service
如果cat命令查看配置文件, 我们可以得到一些基础信息:
数据库数据目录: Environment=PGDATA=/var/lib/pgsql/12/data/
postgresql安装目录: PGHOME=/usr/pgsql-12/

数据库初始化

# 切换到postgres用户
su - postgres
cd /usr/pgsql-12/bin
./initdb -D /var/lib/pgsql/12/data

**配置远程访问 **
修改数据目录下配置文件: pg_hba.conf

vim /var/lib/pgsql/12/data/pg_hba.conf
# 在文件中添加:
host    all             all             192.168.0.0/16          trust
host    all             all             0.0.0.0/0               md5

-- 登陆成功可以执行操作
-- 如修改密码 (引号中为新密码)
postgres=# alter role postgres with password 'postgres';

postgresql-12 流复制(replication)/数据同步配置

流复制原理简述

流复制大约是从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级别的基础上添加了支持逻辑解码所需的信息 |

流复制配置过程

主库(10.242.111.204)

创建一个账户repuser 用户, 提供给备库远程访问, 用来获取流:

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

修改数据目录下配置文件: pg_hba.conf

vim /var/lib/pgsql/12/data/pg_hba.conf
# 在文件中添加(trust设置免密):
host  replication     repuser        192.168.0.0/16             trust
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 = off
# *=all，意思是所有slave都被允许以同步方式连接到master，但同一时间只能有一台slave是同步模式。
# 另外可以指定slave，将值设置为slave的application_name即可。
#synchronous_standby_names = '*'
wal_level = replica
max_wal_senders = 10   		#最多有2个流复制连接
wal_keep_segments = 20  	
wal_sender_timeout = 60s	#流复制超时时间

修改完成重启postgresql-12服务systemctl restart postgresql-12

从库(10.242.111.204)

关闭备库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 master -p 5432 -U repuser -Fp -Xs -Pv -R -D /var/lib/pgsql/12/data -C -S master
# -h 的ip是当前的主库, -U 就是前面个船舰的用来复制的那个用户

额外需要注意的是:

* 如果主库的pg_hba.conf中配置的策略为trust, 这里不需要口令, 如果为md5模式,需要输你创建用户时的那个密码;
* -Fp表示以plain格式数据，-Xs表示以stream方式包含所需的WAL文件，-P表示显示进度，-R表示为replication写配置信息。备份完成，在数据库实例目录下自动生成
* 这里 -R 参数一定要加, 拷贝完在$PGDATA目录下生成standby.signal标志文件(用于表示此库为备库);
* -C 在开始备份之前，允许创建由-S选项命名的复制插槽。
* -S 指定复制插槽名称。这里命令为主机名，方便pgpool判断备机下线后注销
* 使用命令同步完之后, 在data目录下会自动生成postgresql.auto.conf文件中, 优先级是大于postgresql.conf的;
* 这里面的参数请严格参照官网释义;

启动备库postgresql-12 服务

systemctl restart postgresql-12

pgpool-ii4.1.2 高可用集群[主备切换]配置部分

在postgresql12 安装完成之后, 我们需要安装pool-ii中间件, 来完成 HA 自动切换, ip 自动切换的工作

当某一个数据库挂掉之后, 检测其生命状态, 执行相应策略(主库挂掉, 切换某个备库为主库等)
当某一个pgpool节点不可用, 其他节点收到信息进行IP转移(访问入口接管)

服务器SSH免密设置
为后续使用 hostname 和脚本中 SSH 免登录:

# host 文件添加(2台机器均修改)
10.242.111.204   master    
10.242.111.207   slave 
10.242.111.203    vip

# 主机/备机操作
su – postgres
-bash-4.2$ ssh-keygen
# 一直enter
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub master
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub slave

pgpool-ii 4.1 安装(2台机器均安装)

ftp 文件服务器地址中: https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/中间的版本号可以可以更改, 但后续pg版本也需要对应上

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

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

目录/相关命令授权
因为pgpool-ii 配置中会以 postgres 用户执行一些系统权限命令, 需要使用设置普通用户授权:

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

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 相关配置(/etc/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         192.168.0.0/16        trust
host    all         all         0.0.0.0/0             md5

2. pcp.conf / pool_passwd (主备相同)

这个文件是pgpool管理器自己的用户名和密码，用于管理集群的.

-bash-4.2$ pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5
-bash-4.2$ vim pcp.conf
# 编辑内容如下:
# USERID:MD5PASSWD
postgres: e8a48653851e28c69d0506508fb27fc5

# pcp的免密设置需要在.pcppass文件中设置（后面的pcp_attach_node 可能需要用到, pcp_attach_node命令在那个用户下执行，就放在谁的home目录下，~/.pcppass, 为了配合后面的启动脚本，放在postgres用户下的）
[postgres@~]$ vim ~/.pcppass
#hostname:port:username:password
vip:9898:postgres:postgres

# .pcppass文件需要设置权限
[postgres@~]$ chmod 600 ~/.pcppass

# 在pgpool中添加pg数据库的用户名和密码
[postgres@etc~]$ pg_md5 -p -m -u postgres pool_passwd
# 数据库登录用户是postgres,这里输入登录密码，不能出错
# 输入密码后，在/etc/pgpoll-II目录下会生成一个pool_passwd文件

3. failover.sh这个文件可以到 /etc/pgpool-II/ 的目录下复制failover.sh.sample

[postgres@node3 pgpool-II]$ cd /etc/pgpool-II/
[postgres@node3 pgpool-II]$ cp failover.sh.sample failover.sh

# 修改里面的文件目录
vi failover.sh
PGHOME=/usr/pgsql-12

4. 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  (在线故障恢复的脚本示例, 只有2台机器可以不用, 放到postgresql数据目录/var/lib/pgsql/12/data 下)

主库pgpool-ii 节点(10.242.111.204-master)

1. 常用基础配置

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 = 'master'                # 第一台数据库信息
backend_port0 = 5432
backend_weight0 = 1                         # 这个权重和后面负载比例相关
backend_data_directory0 = '/var/lib/pgsql/12/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'slave'                 # 第一台数据库信息
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/12/data'
backend_flag1 = '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'

2. 数据库故障转移(故障后处理)

# 数据库运行状况检查，以便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.sh %H %R '
# follow_master_command = ''                # 2台服务器不配置
# 如果使用3台PostgreSQL服务器，则需要指定follow_master_command在主节点故障转移上的故障转移后运行。如果有两个PostgreSQL服务器，则无需follow_master_command设置。
# 具体脚本文件内容见文末

配置文件中传入的相应参数请参照: config-failover-params

3. 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 ens160 label ens160:0'
# 当前节点指定关闭虚拟IP的命令
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ens160'

# watchdog 健康检查
wd_heartbeat_port = 9694                    # 健康检查端口
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
# 其他机器地址配置(多台请增加配置)
heartbeat_destination0 = 'slave'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'ens160'

# 其他pgpgool节点链接信息(多台请增加配置)
other_pgpool_hostname0 = 'slave'            # 其他节点地址
other_pgpool_port0 = 9999
other_wd_port0 = 9000                       # 其他节点watchdof 端口

# watchdog 发生故障后, 处理的相关配置(宕机, pgpool进程终止)
# 当某个节点故障后, 
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, 出现集群暂时不可访问;

4. 关于在线恢复(master 恢复后自动变为备库)

# 此配置将在多个pgpool-ii 节点时无效
recovery_user = 'postgres'
recovery_password = 'postgres'
recovery_1st_stage_command = 'recovery_1st_stage'   # 这个脚本时放在postgresql数据目录下的
# 如果有多个pgpool-ii 节点共同维护集群状态, 此配置将不可用, 需要手动恢复同步数据>加入集群

5. 自定义自启脚本（start.sh）

首先需要创建一个自启脚本文件（start.sh），shell脚本需要执行权限

vi start.sh
chmod 755 start.sh

脚本内容如下（start.sh）

#!/bin/bash
# This script runs after the database master and standby node is restarted to start the standby node.

set -o xtrace
exec > >(logger -i -p local1.info) 2>&1

DEST_NODE_HOST="$1"
DEST_NODE_PGDATA="$2"
DEST_NODE_INDEX="$3"
VIP_NODE_HOST="$4"

BAK_NODE_PGDATA=/var/lib/pgsql/12/

PGHOME=/usr/pgsql-12
PG_USER="postgres"

## Test passwrodless SSH （测试另一台数据库ssh是否正常）
su - ${PG_USER} -c "ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${DEST_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null"

if [ $? -ne 0 ]; then
    logger -i -p local1.info pgpool_remote_start: passwrodless SSH to postgres@${DEST_NODE_HOST} failed. Please setup passwrodless SSH.
    exit 1
fi

## Check the status of DEST_NODE_HOST (测试另一台数据库是否正常运行)
su - ${PG_USER} -c "ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${DEST_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ${PGHOME}/bin/pg_ctl -w -D ${DEST_NODE_PGDATA} status"

if [ $? -ne 0 ]; then
    logger -i -p local1.info DEST_NODE_HOST: server is down;
    exit 1
fi

## Check the state of DEST_NODE_HOST (查看另一台数据库是否为主库)
HOSTPGSTATE=`su - ${PG_USER} -c "ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${DEST_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ${PGHOME}/bin/pg_controldata -D ${DEST_NODE_PGDATA}" | grep cluster | awk -F : '{print $2}' | sed 's/^[ \t]*\|[ \t]*$//'`

echo `postgres host PGSTATE: ${HOSTPGSTATE}`

if [[ ${HOSTPGSTATE} =~ "production" ]]; then
    # 远程主机为主库
    echo "postgres host is production"

else
    # 远程主机为备机, 需要人工干预，选择一台机器为主库
    echo "postgres host is recovery"
    systemctl stop postgresql-12
    exit 1
fi

systemctl start postgresql-12

PGSTATE=`su - ${PG_USER} -c "${PGHOME}/bin/pg_controldata -D ${DEST_NODE_PGDATA}" | grep cluster | awk -F : '{print $2}' | sed 's/^[ \t]*\|[ \t]*$//'`

systemctl stop postgresql-12

echo "postgres PGSTATE: ${PGSTATE}"

NOW_DATE=`date -d today +"%Y%m%dT%H%M%S"`

if [[ ${PGSTATE} =~ "production" ]]; then
    # 原主机下线
    echo "postgres production to start Standby"

    su - ${PG_USER} -c "mv ${DEST_NODE_PGDATA} ${BAK_NODE_PGDATA}/${NOW_DATE}_data.bak"

    if [ $? -ne 0 ]; then
        echo "postgres Data backup failed"
        exit 1
    fi

    su - ${PG_USER} -c "mkdir -p ${DEST_NODE_PGDATA}/"
    su - ${PG_USER} -c "chmod 750 ${DEST_NODE_PGDATA}"

    su - postgres -c "/usr/pgsql-12/bin/psql -h ${VIP_NODE_HOST} -p 9999 -c \"SELECT pg_drop_replication_slot('${DEST_NODE_HOST}');\""

    su - ${PG_USER} -c "pg_basebackup -h ${DEST_NODE_HOST} -p 5432 -U repuser -Fp -Xs -Pv -R -D /var/lib/pgsql/12/data -C -S ${DEST_NODE_HOST}"

    if [ $? -ne 0 ]; then
        echo "postgres Data pg_basebackup failed"
        exit 1
    fi

    systemctl start postgresql-12

    su - ${PG_USER} -c "pcp_attach_node -d -w -U postgres -h ${VIP_NODE_HOST} -p 9898 -n ${DEST_NODE_INDEX}"

    if [ $? -ne 0 ]; then
        echo "postgres Data pcp_attach_node failed"
        exit 1
    fi

    su - ${PG_USER} -c "pgpool -n -D > /var/log/pgpool/pgpool.log 2>&1 &"

    exit 0
elif [[ ${PGSTATE} =~ "recovery" ]]; then
    # 原备机下线
    echo "postgres recovery to start Standby"

    su - ${PG_USER} -c "mv ${DEST_NODE_PGDATA} ${BAK_NODE_PGDATA}/${NOW_DATE}_data.bak"

    if [ $? -ne 0 ]; then
        echo "postgres Data backup failed"
        exit 1
    fi

    su - ${PG_USER} -c "mkdir -p ${DEST_NODE_PGDATA}/"
    su - ${PG_USER} -c "chmod 750 ${DEST_NODE_PGDATA}"

    su - postgres -c "/usr/pgsql-12/bin/psql -h ${VIP_NODE_HOST} -p 9999 -c \"SELECT pg_drop_replication_slot('${DEST_NODE_HOST}');\""

    su - ${PG_USER} -c "pg_basebackup -h ${DEST_NODE_HOST} -p 5432 -U repuser -Fp -Xs -Pv -R -D /var/lib/pgsql/12/data -C -S ${DEST_NODE_HOST}"

    if [ $? -ne 0 ]; then
        echo "postgres Data pg_basebackup failed"
        exit 1
    fi

    systemctl start postgresql-12

    su - ${PG_USER} -c "pcp_attach_node -d -w -U postgres -h ${VIP_NODE_HOST} -p 9898 -n ${DEST_NODE_INDEX}"

    if [ $? -ne 0 ]; then
        echo "postgres Data pcp_attach_node failed"
        exit 1
    fi

    su - ${PG_USER} -c "pgpool -n -D > /var/log/pgpool/pgpool.log 2>&1 &"

    exit 0
else

    echo 'postgres error PGSTATE'
    exit 1
fi

shell脚本加入自启

vi /etc/rc.local
~/shart.sh master /var/lib/pgsql/12/data 1 vip
# master： 为另一台机器的hosts
# /var/lib/pgsql/12/data： 为数据库文件路径
# 1： 本机数据库在pgpool-ii上的序号
# vip： 为虚拟ip的hosts

自定义自启脚本启动会有日志生成，查看日志/var/log/messages

备库pgpool-ii 节点(10.242.111.207-slave)

# 将主库的配置文件拷贝过来
scp master:/etc/pgpool-II/pool.conf /etc/pgpool-II/pool.conf
# 修改以下几项即可
wd_hostname = 'slave'               # 当前机器
heartbeat_destination0 = 'master'   # 其他pg库机器
other_pgpool_hostname0 = 'master'   # 其他pgpool节点机器

启动验证

1. 启动/终止命令

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
启动完成之后, 可登陆虚拟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       | master   | 5432 | up     | 0.500000  | primary | 0          | true              | 0                 |                   |                        | 2020-06-22 17:48:51
 1       | slave    | 5432 | up     | 0.500000  | standby | 0          | false             | 0                 |                   |                        | 2020-06-22 17:48:51
(2 rows)

lb_weight和前面pool.conf配置中backend_weight0 = 1的比例有关;
role 为postgresql 数据库的主备状态;
up为数据库已加入集群管理;
此时可以通过虚拟IP链接, 执行新增/修改/删除等操作来测试集群正常工作状态的数据同步;

宕机验证

1. pgpool-ii 节点宕机(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 vip -p 9999
# 提示输入密码：
Type "help" for help.

postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
0       | master   | 5432 | up     | 0.500000  | primary | 0             | false  | 0
1       | slave    | 5432 | up     | 0.500000  | standby | 0             |  true  | 0
(2 rows)
#访问成功，在master节点上的pgpool宕机后，由slave节点的pgpool接管vip和集群服务，并未中断应用访问。

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       | master   | 5432 | down   | 0.500000  | standby | 0          | false             | 0
 1       | slave    | 5432 | up     | 0.500000  | primary | 0          | true              | 0
 (2 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 目标机器hosts -p 5432 -U repuser -Fp -Xs -Pv -R -D /var/lib/pgsql/12/data -C -S 目标机器hosts

# 4. 启动服务(切换到root用户)
systemctl restart postgresql-12

# 5. 重新将postgresql数据库加入集群管理(su - postgres)
# -n 节点序号, postgresql 数据库在集群中的节点序号
pcp_attach_node -d -w -U postgres -h vip -p 9898 -n 0

#  -U, --username=NAME     pcp安全认证的用户
#  -h, --host=HOSTNAME     pcp连接ip（localhost）
#  -p, --port=PORT         pcp连接端口号 默认9898
#  -w, --no-password       pcp连接不需要密码认证
#  -W, --password          pcp密码认证 一般在pcp.conf这个文件中
#  -n, --node-id=NODEID    show pool_nodes;查到的
#  -d, --debug             debug模式最好为-d 5
#  -v, --verbose           输出的详细信息

# 6. 此时
node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
 ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | master   | 5432 | up    | 0.500000  | standby | 0          | false             | 0
 1       | slave    | 5432 | up    | 0.500000  | primary | 0          | true              | 0
 (2 rows)

如果只有一个pgpool-ii 节点, 可以使用pgpool.conf中配置的recovery_1st_stage_command在线恢复来操作. 多个pgpool节点此配置不支持.

主机直接宕机

主机直接宕机, 意味着当前机器时区所有链接, 其他pgpool-ii 节点检测不到原"主pgpool-ii"节点存活
首先, 选举出一个pgpool-ii 节点承担责任
接管维护虚拟IP的责任, 启动指定的虚拟ip
此时检测到原主数据库primary不可链接, 执行故障转移脚本, 将当前备库转换为主库为集群提供存储服务;

所有主机同时宕机

1. 人工干预

当所有数据库主机都直接下线，这时需要人工干预，需要人工判断选择一台主机作为主库上线

# 保证所有主机的pgpool都不在线
-bash-4.2$ su - postgres
-bash-4.2$ pgpool -m fast stop

# 启动数据库
-bash-4.2$ systemctl start postgresql-12
# 查看数据，选择一台主机作为主库
# 选择的这台主机本来就是主库则不需要做操作
# 如果选择的这台主机为之前为备机则需要执行命令升为主库
-bash-4.2$ pg_ctl promote -D /var/lib/pgsql/12/data

# 然后启动主库的pgpool-ii 
-bash-4.2$ pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 &     # 有debug日志
-bash-4.2$ pgpool -n -D > /var/log/pgpool/pgpool.log 2>&1 &        # 无debug日志

# 进入集群，查看主机状态
-bash-4.2$ psql -h vip -p 9999

 postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
 ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | master   | 5432 | down   | 0.500000  | standby | 0          | false             | 0
 1       | slave    | 5432 | up     | 0.500000  | primary | 0          | true              | 0
 (2 rows)

2. 重启另一台主机

当主库启动后，就可以重启另一台主机，重启脚本就会自动备份重启为备机，当所有主机都启动完成，进入集群查看所有主机状态，并验证主备机是否之间是否正常。

总结

数据库postgresql 层面的故障转移执行切换脚本, 本质上执行的还是pg_ctl promote命令;
pgpool-ii 层面故障, 后续的检测/切换虚拟IP, 是不影响下层postgresql数据库运行状态的;
在主机直接挂掉的情况下, 上面两个步骤是都要执行的;
数据库宕机恢复的本质依然是同步流复制pg_basebackup命令;
即使是pgpool-ii 提供的在线恢复,本质上也是几个步骤的脚本:清空备机节点数据目录 > 使用pg_basebackup命令同步数据 > 启动数据库服务 > 将数据库重新加入集群

SoGreater

关注

1
点赞
踩
20

收藏

觉得还不错? 一键收藏
5
评论
Postgresql12 + pgpool-ii 4.1.2 高可用集群[主备切换]配置和脚本编辑

Postgresql12 + pgpool-ii 4.1.2 高可用集群[主备切换]配置和脚本编写，来达到集群的主机自动上下线的加入集群和主动备份数据
复制链接

扫一扫