KingbaseES V8R6集群运维案例之---repmgr standby promote应用案例
案例说明:
在容灾环境中,跨区域部署的异地备节点不会自主提升为主节点,在主节点发生故障或者人为需要切换时需要手动执行切换操作。若主节点已经失效,希望将异地备机提升为主节点。
$bin/repmgr standby promote
适用版本:
KingbaseES V8R6
集群节点信息:
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+---------+---------+-----------+----------+---------+-------+---------+--------------------
1 | node101 | standby | running | node102 | running | 19312 | no | 1 second(s) ago
2 | node102 | primary | * running | | running | 20658 | no | n/a
主备流复制状态信息:
test=# select * from sys_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--
20165 | 10 | system | node101 | 192.168.1.101 | | 10747 | 2022-09-08 11:12:59.798843+08 | | streaming | 4/4C0018A0 | 4/4C0018A0 | 4/4C0018A0 | 4/4C0018A0 | | | | 1 | sync | 2022-09-08 11:16:45.423742+08
(1 row)
关闭failover自动切换:
[kingbase@node102 bin]$ cat ../etc/repmgr.conf|grep failover
#failover='automatic'
failover='manual'
一、模拟主库数据库服务宕机
[kingbase@node102 bin]$ ./sys_ctl stop -D /data/kingbase/r6ha/data
waiting for server to shut down....... done
二、查看备库状态
1、数据库进程状态(仍为备库进程)
kingbase 19132 1 0 11:13 ? 00:00:00 /home/kingbase/cluster/R6HA/kha/kingbase/bin/kingbase -D /data/kingbase/r6ha/data
kingbase 19133 19132 0 11:13 ? 00:00:00 kingbase: logger
kingbase 19134 19132 0 11:13 ? 00:00:00 kingbase: startup recovering 00000018000000040000004D
kingbase 19135 19132 0 11:13 ? 00:00:00 kingbase: checkpointer
kingbase 19136 19132 0 11:13 ? 00:00:00 kingbase: background writer
kingbase 19137 19132 0 11:13 ? 00:00:00 kingbase: stats collector
kingbase 19310 19132 0 11:13 ? 00:00:00 kingbase: system esrep 192.168.1.101(15211) idle
2、查看备库hamgr.log(获取切换过程)
# 备库repmgrd进程监控主库数据库服务状态(PQping()),当主库返回"PQPING_NO_RESPONSE"后,尝试
再次连接主库,超过阈值后,执行切换。
[2022-09-08 11:18:10] [INFO] sleeping 6 seconds until next reconnection attempt
[2022-09-08 11:18:16] [INFO] checking state of node 2, 1 of 10 attempts
[2022-09-08 11:18:16] [DEBUG] is_server_available_params(): ping status for "user=system connect_timeout=10 dbname=esrep host=192.168.1.102 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 fallback_application_name=repmgr" is PQPING_NO_RESPONSE
[2022-09-08 11:18:16] [WARNING] unable to ping "user=system connect_timeout=10 dbname=esrep host=192.168.1.102 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 fallback_application_name=repmgr"
[2022-09-08 11:18:16] [DETAIL] PQping() returned "PQPING_NO_RESPONSE"
[2022-09-08 11:18:16] [INFO] sleeping 6 seconds until next reconnection attempt
........
[2022-09-08 11:19:10] [INFO] checking state of node 2, 10 of 10 attempts
[2022-09-08 11:19:10] [DEBUG] is_server_available_params(): ping status for "user=system connect_timeout=10 dbname=esrep host=192.168.1.102 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 fallback_application_name=repmgr" is PQPING_NO_RESPONSE
.......
# 准备执行切换,但是在repmgr.conf中配置failover=‘manual’,将不会执行自动切换。
[2022-09-08 11:19:10] [DEBUG] do_election(): electoral term is 1
[2022-09-08 11:19:10] [NOTICE] this node is not configured for automatic failover so will not be considered as promotion candidate, and will not follow the new primary
[2022-09-08 11:19:10] [DETAIL] "failover" is set to "manual" in repmgr.conf
[2022-09-08 11:19:10] [HINT] manually execute "repmgr standby follow" to have this node follow the new primary
[2022-09-08 11:19:10] [DEBUG] election result: NOT CANDIDATE
三、在备库执行promote手工切换
1、执行手工切换
[kingbase@node101 bin]$ ./repmgr standby promote
DEBUG: connecting to: "user=system connect_timeout=10 dbname=esrep host=192.168.1.101 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 fallback_application_name=repmgr"
DEBUG: connecting to: "user=system connect_timeout=10 dbname=esrep host=192.168.1.102 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 fallback_application_name=repmgr"
DEBUG: connecting to: "user=system connect_timeout=10 dbname=esrep host=192.168.1.101 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 fallback_application_name=repmgr"
NOTICE: promoting standby to primary
DETAIL: promoting server "node101" (ID: 1) using sys_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
DEBUG: setting node 1 as primary and marking existing primary as failed
NOTICE: STANDBY PROMOTE successful
DETAIL: server "node101" (ID: 1) was successfully promoted to primary
2、查看切换后数据库进程状态(切换为主库状态)
[kingbase@node101 bin]$ ps -ef |grep kingbase
kingbase 19132 1 0 11:13 ? 00:00:00 /home/kingbase/cluster/R6HA/kha/kingbase/bin/kingbase -D /data/kingbase/r6ha/data
kingbase 19133 19132 0 11:13 ? 00:00:00 kingbase: logger
kingbase 19135 19132 0 11:13 ? 00:00:00 kingbase: checkpointer
kingbase 19136 19132 0 11:13 ? 00:00:00 kingbase: background writer
kingbase 19137 19132 0 11:13 ? 00:00:00 kingbase: stats collector
kingbase 19780 19132 0 11:13 ? 00:00:00 kingbase: system test ::1(33354) idle
kingbase 19784 19132 0 11:13 ? 00:00:00 kingbase: system esrep 192.168.1.101(15243) idle
kingbase 20826 19132 0 11:20 ? 00:00:00 kingbase: walwriter
kingbase 20827 19132 0 11:20 ? 00:00:00 kingbase: autovacuum launcher
kingbase 20828 19132 1 11:20 ? 00:00:00 kingbase: archiver archiving 0000001500000002000000FC
四、将原主库恢复为新备库加入集群
1、创建备库标识文件
[kingbase@node102 log]$ touch /data/kingbase/r6ha/data/standby.signal
2、启动数据库服务
[kingbase@node102 bin]$ ./sys_ctl start -D /data/kingbase/r6ha/data
3、注册备库节点
[kingbase@node102 bin]$ ./repmgr standby register --force
INFO: connecting to local node "node102" (ID: 2)
DEBUG: connecting to: "user=system connect_timeout=10 dbname=esrep host=192.168.1.102 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 fallback_application_name=repmgr"
INFO: connecting to primary database
DEBUG: connecting to: "user=system connect_timeout=10 dbname=esrep host=192.168.1.102 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 fallback_application_name=repmgr"
DEBUG: connecting to: "user=system connect_timeout=10 dbname=esrep host=192.168.1.101 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 fallback_application_name=repmgr"
DEBUG: remote_command():
ssh -o Batchmode=yes -q -o ConnectTimeout=10 -o StrictHostKeyChecking=no -o ServerAliveInterval=2 -o ServerAliveCountMax=5 -p 22 192.168.1.101 /home/kingbase/cluster/R6HA/kha/kingbase/bin/kbha -A updateinfo
INFO: standby registration complete
NOTICE: standby node "node102" (ID: 2) successfully registered
4、查看集群节点状态
[kingbase@node102 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------
1 | node101 | primary | * running | | default | 100 | 25 | host=192.168.1.101 user=system dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node102 | standby | running | node101 | default | 100 | 24 | host=192.168.1.102 user=system dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
五、总结
对于KingbaseES V8R6集群在failover切换时,如果备库不能自动切换为主库,或主库宕机后切换失败,都可以使用‘repmgr standby promote’强制手工切换备库为主库,恢复业务访问。
KINGBASE研究院