PG 之 REPMGR + VIP 实现应用自动故障转移

原创大表哥 2022-08-16

7489

大家好，今天大表哥要和大家分享的是 PG 之 REPMGR + VIP 实现应用自动故障转移。

在上一篇的文章结尾部分，我们留下一下疑问:

在发生了 auto failover 之后，应用是否能自动感知，是否支持 TAF (Transportation application failover) 应用自动故障转移？如何支持VIP的漂移？

其实目前很多企业版的厂商像是瀚高，经过对REPMGR 的二次开发，提供了 VIP 的配置选项实现了用自动故障转移。

瀚高安装手册截图：

当然网上也有很多文章，采用 keepalived 作为 VIP 的解决方案。

目前周围DBA圈子内同事对 keepalived 的普遍是评价：不稳定， VIP 容易乱飘，基于网络底层协议，DBA的知识面很难做keepalived 网络层面的 trouble shutting.（把问题踢给网络组的人调查，就石沉大海，没有然后了。。。）

大表哥的亲身经历就是，数据库本身好好的，但是由于keepalived 不稳定，经常发生vip 的漂移和相应的数据库的promote 的 fail over 的切换。

从现在流行的容器角度讲数据库是有状态的， keepalived 是否适合为有状态的容器做HA？其实是个大大的问号！

从运维的可维护性来说, 我们摒弃了keepalived 作为HA 的vip 方案。

我们采用repmgr + 最原始的手动挂载/卸载 VIP 的方式实现应用自动故障转移。

核心命令就是：

卸载VIP： /usr/bin/sudo /sbin/ip addr del $VIP/24 dev $DEVICE
挂载VIP： /usr/bin/sudo /sbin/ip addr add $VIP/24 dev $DEVICE
清除 arp cache 缓存： /usr/bin/sudo /sbin/arping -I $DEVICE -s $VIP -b -c 3 $GW

我们需要自己编写一个shell 或者 python 的脚本，具体的逻辑如下：

脚本逻辑实现如下： repmgr_promote_command.sh

#!/bin/bash


set -o xtrace
PGCANDIDATES=(10.67.38.50 10.67.39.149 10.67.39.49)
HOSTNAME=`hostname -i`
VIP=10.67.39.200
GW=10.67.39.254
DEVICE=eth0
PG_HOME=/opt/postgreSQL/pg12
PORT=1998
REPMGR_USER=repmgr
REPMGR_PASSWD=repmgr
STEP1="Remove the VIP on all nodes"
STEP2="Check if vip still online"
STEP3="Promte primary DB"
STEP4="Add vip on new primary DB"
STEP5="Clear ARP Cache"


###step1. Remove the VIP on all nodes
for candidate in "${PGCANDIDATES[@]}"; do
    [ "$HOSTNAME" = "$candidate" ] && continue


    ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$candidate  "
        /usr/bin/sudo /sbin/ip addr del $VIP/24 dev $DEVICE
    "
done
####clear arp cache
/usr/bin/sudo /sbin/arping -I $DEVICE -s $VIP -b -c 5 $GW




###step2.Check if vip still online
/usr/bin/sudo  /usr/bin/ping -c1 -w1 -t5 ${VIP}
if [ $? -eq 0 ]; then
    echo repmgr_promote_command.sh: ${STEP2} : ${VIP} failed !!!  
    exit 1
fi




###step3.Promte primary DB
/opt/postgreSQL/pg12/bin/repmgr standby promote -f /opt/postgreSQL/repmgr.conf --log-to-file
#echo "/opt/postgreSQL/pg12/bin/repmgr standby promote -f /opt/postgreSQL/repmgr.conf --log-to-file"
if [ $? -ne 0 ]; then
    echo repmgr_promote_command.sh: ${STEP3} on ${HOSTNAME} failed !!!  
    exit 1
fi
standby_flg=`${PG_HOME}/bin/psql -p ${PORT} -U ${REPMGR_USER} -w ${REPMGR_PASSWD} -h localhost -At -c "SELECT pg_is_in_recovery();"`
if [ ${standby_flg} == 'f' ]; then
   echo repmgr_promote_command.sh: ${STEP3} on ${HOSTNAME} successfully !!!
elif [ ${standby_flg} == 't' ]; then
   echo repmgr_promote_command.sh: ${STEP3} on ${HOSTNAME} failed !!!
   exit 1
fi
###step4.Add vip on new primary DB
/usr/bin/sudo /sbin/ip addr add $VIP/24 dev $DEVICE
if [ $? -ne 0 ]; then
    echo repmgr_promote_command.sh: ${STEP4} on ${HOSTNAME} failed !!!  
    exit 1
fi
####step5.Clear arp cache
/usr/bin/sudo /sbin/arping -I $DEVICE -s $VIP -b -c 3 $GW
if [ $? -ne 0 ]; then
    echo repmgr_promote_command.sh: ${STEP5} on ${HOSTNAME} failed !!!  
    exit 1
fi

复制

我们现在用 springboot 模拟一个连接池的小程序来测试一下：

我们先查看一下复制集的状态：

INFRA [postgres@wqdcsrv3352 postgreSQL]# repmgr -f /opt/postgreSQL/repmgr.conf cluster show
ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                                      
----+-------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------------------------------------
1  | pg50  | standby |   running | pg49     | default  | 100      | 4        | host=10.67.38.50 port=1998 user=repmgr password=repmgr dbname=repmgr connect_timeout=5
2  | pg49  | primary | * running |          | default  | 80       | 4        | host=10.67.39.49 port=1998 user=repmgr password=repmgr dbname=repmgr connect_timeout=5
3  | pg149 | standby |   running | pg49     | default  | 20       | 4        | host=10.67.39.149 port=1998 user=repmgr password=repmgr dbname=repmgr connect_timeout=5

复制

现在主节点在 pg49 ，我们手动执行命令挂载VIP 10.67.39.200

INFRA [postgres@wqdcsrv3353 postgreSQL]# /usr/bin/sudo /sbin/ip addr add 10.67.39.200/24 dev eth0
INFRA [postgres@wqdcsrv3353 postgreSQL]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:50:56:ae:99:07 brd ff:ff:ff:ff:ff:ff
    inet 10.67.39.49/22 brd 10.67.39.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 10.67.39.200/24 scope global eth0
       valid_lft forever preferred_lft forever

复制

我们需要把repmgrd 的配置文件中的 promote_command 改为我们自定义的shell 文件

promote_command='/opt/postgreSQL/repmgr_promote_command.sh >> /opt/postgreSQL/repmgrd.log'

复制

把VIP的地址加入到 pg_hba.conf


host    repmgr          repmgr      10.67.39.200/32             trust
host    replication     repmgr      10.67.39.200/32             trust
复制

reload 配置文件进行生效：

INFRA [postgres@wqdcsrv3353 data]# /opt/postgreSQL/pg12/bin/pg_ctl -D /data/postgreSQL/1998/data reload
server signaled

复制

暂停repmgrd 的自动failover 功能

INFRA [postgres@wqdcsrv3353 postgreSQL]#  repmgr -f /opt/postgreSQL/repmgr.conf service pause
NOTICE: node 1 (pg50) paused
NOTICE: node 2 (pg49) paused
NOTICE: node 3 (pg149) paused

复制

每个节点逐一重启一下

INFRA [postgres@wqdcsrv3354 postgreSQL]# ps -ef | grep repmgrd
postgres  27960      1  0 Aug10 ?        00:00:57 repmgrd -f /opt/postgreSQL/repmgr.conf --pid-file /tmp/repmgrd.pid
postgres 117464 115485  0 15:00 pts/0    00:00:00 grep --color=auto repmgrd
INFRA [postgres@wqdcsrv3354 postgreSQL]# kill 27960
INFRA [postgres@wqdcsrv3354 postgreSQL]# repmgrd -f /opt/postgreSQL/repmgr.conf --pid-file /tmp/repmgrd.pid
[2022-08-15 15:00:25] [NOTICE] redirecting logging output to "/opt/postgreSQL/repmgrd.log"

复制

打开 repmgrd 的自动failover 功能 :

INFRA [postgres@wqdcsrv3353 postgreSQL]# repmgr -f /opt/postgreSQL/repmgr.conf  service unpause
NOTICE: node 1 (pg50) unpaused
NOTICE: node 2 (pg49) unpaused
NOTICE: node 3 (pg149) unpaused

复制

这次是连接到 VIP 10.67.39.200 上面 ,连接池的配置是最大允许 200 个连接，最小空闲的连接数是10 .

spring.datasource.url=jdbc:postgresql://10.67.39.200:1998/postgres
spring.datasource.username=app_ha_user
spring.datasource.password=app_ha_user
spring.datasource.driverClassName=org.postgresql.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.datasource.maximum-pool-size=200
spring.datasource.min-idle=10

复制

主库创建测试账号：

postgres=# create user app_ha_user with password 'app_ha_user';
CREATE ROLE

复制

测试程序简单用例：打印一下数据库的 IP 地址

public class TestController {


    @Autowired
    JdbcTemplate jdbcTemplate;
    //@Autowired
    //UserMapper usermapper;
    @RequestMapping(value="/", method=RequestMethod.GET)
    public String index() {

          String sql = "select inet_server_addr()";
        // 通过jdbcTemplate查询数据库
        String hostname = (String)jdbcTemplate.queryForObject(
                sql, String.class);
     return "Hello ，you are connecting to " + hostname;
  
    }
}

复制

启动项目后，我们可以看到连接池中的session:

postgres=# select * from pg_stat_activity where usename = 'app_ha_user';
datid | datname  |  pid   | usesysid |   usename   |    application_name    | client_addr | client_hostname | client_port |         backend_start         | xact_start |          query_star
t          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |                      query                      |  backend_type  
-------+----------+--------+----------+-------------+------------------------+-------------+-----------------+-------------+-------------------------------+------------+--------------------
-----------+-------------------------------+-----------------+------------+-------+-------------+--------------+-------------------------------------------------+----------------
13594 | postgres | 105791 |    24649 | app_ha_user | PostgreSQL JDBC Driver | 10.64.44.74 |                 |       55846 | 2022-08-15 15:39:17.152147+08 |            | 2022-08-15 15:39:17
.217994+08 | 2022-08-15 15:39:17.218047+08 | Client          | ClientRead | idle  |             |              | SHOW TRANSACTION ISOLATION LEVEL                | client backend
13594 | postgres | 105794 |    24649 | app_ha_user | PostgreSQL JDBC Driver | 10.64.44.74 |                 |       55849 | 2022-08-15 15:39:17.349778+08 |            | 2022-08-15 15:39:17
.357365+08 | 2022-08-15 15:39:17.357375+08 | Client          | ClientRead | idle  |             |              | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 105805 |    24649 | app_ha_user | PostgreSQL JDBC Driver | 10.64.44.74 |                 |       55856 | 2022-08-15 15:39:18.373328+08 |            | 2022-08-15 15:39:18
.380553+08 | 2022-08-15 15:39:18.380565+08 | Client          | ClientRead | idle  |             |              | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 105806 |    24649 | app_ha_user | PostgreSQL JDBC Driver | 10.64.44.74 |                 |       55857 | 2022-08-15 15:39:18.384277+08 |            | 2022-08-15 15:39:18
.394444+08 | 2022-08-15 15:39:18.394468+08 | Client          | ClientRead | idle  |             |              | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 105807 |    24649 | app_ha_user | PostgreSQL JDBC Driver | 10.64.44.74 |                 |       55858 | 2022-08-15 15:39:18.400829+08 |            | 2022-08-15 15:39:18
.408232+08 | 2022-08-15 15:39:18.408248+08 | Client          | ClientRead | idle  |             |              | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 105808 |    24649 | app_ha_user | PostgreSQL JDBC Driver | 10.64.44.74 |                 |       55859 | 2022-08-15 15:39:18.412017+08 |            | 2022-08-15 15:39:18
.421189+08 | 2022-08-15 15:39:18.421205+08 | Client          | ClientRead | idle  |             |              | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 105809 |    24649 | app_ha_user | PostgreSQL JDBC Driver | 10.64.44.74 |                 |       55860 | 2022-08-15 15:39:18.425258+08 |            | 2022-08-15 15:39:18
.432923+08 | 2022-08-15 15:39:18.43293+08  | Client          | ClientRead | idle  |             |              | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 105810 |    24649 | app_ha_user | PostgreSQL JDBC Driver | 10.64.44.74 |                 |       55861 | 2022-08-15 15:39:18.436881+08 |            | 2022-08-15 15:39:18
.447635+08 | 2022-08-15 15:39:18.447658+08 | Client          | ClientRead | idle  |             |              | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 105811 |    24649 | app_ha_user | PostgreSQL JDBC Driver | 10.64.44.74 |                 |       55862 | 2022-08-15 15:39:18.451929+08 |            | 2022-08-15 15:39:18
.461362+08 | 2022-08-15 15:39:18.461375+08 | Client          | ClientRead | idle  |             |              | SET application_name = 'PostgreSQL JDBC Driver' | client backend
13594 | postgres | 105813 |    24649 | app_ha_user | PostgreSQL JDBC Driver | 10.64.44.74 |                 |       55863 | 2022-08-15 15:39:18.464905+08 |            | 2022-08-15 15:39:18
.473498+08 | 2022-08-15 15:39:18.473509+08 | Client          | ClientRead | idle  |             |              | SET application_name = 'PostgreSQL JDBC Driver' | client backend
(10 rows)

复制

我们访问一下测试的网页：

我们现在测试一下应用故障转移，我们来手动关闭掉主库：

INFRA [postgres@wqdcsrv3353 data]# /opt/postgreSQL/pg12/bin/pg_ctl -D /data/postgreSQL/1998/data/ stop -m fast
waiting for server to shut down.... done
server stopped

复制

我们可以看到 pg50 已经升级到了主库，并且VIP 已经挂载到了 pg50这个节点上：

INFRA [postgres@wqdcsrv3352 data]# repmgr -f /opt/postgreSQL/repmgr.conf cluster show
ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                                      
----+-------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------------------------------------
1  | pg50  | primary | * running |          | default  | 100      | 5        | host=10.67.38.50 port=1998 user=repmgr password=repmgr dbname=repmgr connect_timeout=5
2  | pg49  | primary | - failed  | ?        | default  | 80       |          | host=10.67.39.49 port=1998 user=repmgr password=repmgr dbname=repmgr connect_timeout=5
3  | pg149 | standby |   running | pg50     | default  | 20       | 4        | host=10.67.39.149 port=1998 user=repmgr password=repmgr dbname=repmgr connect_timeout=5


WARNING: following issues were detected
  - unable to connect to node "pg49" (ID: 2)


HINT: execute with --verbose option to see connection error messages
INFRA [postgres@wqdcsrv3352 data]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:50:56:ae:b1:5f brd ff:ff:ff:ff:ff:ff
    inet 10.67.38.50/22 brd 10.67.39.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 10.67.39.200/24 scope global eth0
       valid_lft forever preferred_lft forever

复制

我们再次访问网页：访问还是 VIP 10.67.39.200,

可以看到后台日志，连接池中的连接进行了 reload

2022-08-15 16:33:07,320 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@aacfee6 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-08-15 16:33:07,322 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@7c1e6dee (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-08-15 16:33:07,334 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@11a8f745 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-08-15 16:33:07,339 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@28ece3f3 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-08-15 16:33:07,343 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@73f124ac (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-08-15 16:33:07,345 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@7c0f5621 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-08-15 16:33:07,346 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@2acb943a (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-08-15 16:33:07,347 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@65f709eb (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-08-15 16:33:07,348 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@1f5995bd (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-08-15 16:33:07,348 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@19e17629 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.

复制

结束语的思考：

我们实现了 REPMGR + VIP 的高可用 + 应用故障转移的方案。
优势：
1.配置简单（相对于PGPOOL等等的HA的方案）
2.自己开发脚本可维护，可改造性强

目前这种方案的缺陷
1.脚本的强壮性，是否有覆盖不到的情况？，是否本身存在bug?
2.是否存在脑裂的可能性（主库由于网络问题间歇性失联，又恢复的情况。）

为了规避脑裂问题， EDB 官方给出的方案是 REPMGR + PGBOUNCER 的组合方案。下一篇，我们将分享PG_BOUNCER.

Have a fun 🙂 !