KingbaseES V8R3集群运维案例之---备库状态‘down’修复
案例说明:
Kingbase V8R3集群,集群启动正常,备库数据库服务正常,流复制状态正常。但是备库在show pool_nodes下查看是‘down’状态,通过pcp_attach_node重新注册节点后,仍然是‘down’,通过复制(cp)主库data方式重建备库后,仍然没有解决。
此文档,详细介绍了此问题的分析和解决过程。
适用版本:
KingbaseES V8R3
集群节点信息:
一、问题现象
1、节点状态信息
TEST=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+---------------+-------+--------+-----------+---------+------------+-------------------+-------------------
0 | 192.168.1.101 | 54321 | up | 0.500000 | primary | 0 | true | 0
1 | 192.168.1.102 | 54321 | down | 0.500000 | standby | 0 | false | 0
(2 rows)
### 如上所示,备库节点状态‘down’。
2、流复制状态信息
TEST=# select * from sys_stat_replication;
PID | USESYSID | USENAME | APPLICATION_NAME | CLIENT_ADDR | CLIENT_HOSTNAME | CLIENT_PORT | BACKEND_START |BACKEND_XMIN | STATE | SENT_LOCATION | WRITE_LOCATION | FLUSH_LOCATION | REPLAY_LOCATION | SYNC_PRIORITY | SYNC_STATE
-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
11393 | 10 | SYSTEM | node102 | 192.168.1.102 | | 28251 | 2022-09-21 16:25:46.638372+08 | | streaming | 2/41237A0 | 2/41237A0 | 2/41237A0 | 2/41237A0 | 2 | sync
(1 row)
二、问题分析和解决
Tips:
一般对于以上问题,可以通过pcp_attach_node方式注册节点,恢复到‘up’状态。
1、注册备库节点
[kingbase@node101 bin]$ ./pcp_attach_node -U kingbase 1
Password:
pcp_attach_node -- Command Successful
[kingbase@node101 bin]$ ./pcp_node_info -U kingbase 1
Password:
192.168.1.102 54321 1 0.500000 waiting
[kingbase@node101 bin]$ ./pcp_node_info -U kingbase 1
Password:
192.168.1.102 54321 2 0.500000 up
[kingbase@node101 bin]$ ./pcp_node_info -U kingbase 1
Password:
192.168.1.102 54321 3 0.500000 down
##如上所示,执行备库节点注册后仍然是‘down’状态。
2、查看备库recovery.log
......
primary node/Im node status is changed, primary ip[192.168.1.101], recovery.conf NEED_CHANGE [0] (0 is need ), I,m status is [1] (1 is down), I will be in recovery.
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+---------------+-------+--------+-----------+---------+------------+-------------------+-------------------
0 | 192.168.1.101 | 54321 | up | 0.500000 | primary | 0 | true | 0
1 | 192.168.1.102 | 54321 | down | 0.500000 | standby | 0 | false | 0
(2 rows)
if recover node up, let it down , for rewind
waiting for server to shut down.... done
server stopped
2022-09-21 16:18:36 set /home/kingbase/cluster/R3HA/db/data down now... already down , check again
wait kb stop 5 sec .......
2022-09-21 16:18:37 sys_rewind...
sys_rewind --target-data=/home/kingbase/cluster/R3HA/db/data --source-server="host=192.168.1.101 port=54321 user=SUPERMANAGER_V8ADMIN dbname=TEST"
datadir_source = /home/kingbase/cluster/R3HA/db/data
rewinding from last common checkpoint at 2/125C2A0 on timeline 19
find last common checkpoint start time from 2022-09-21 16:18:37.147787 CST to 2022-09-21 16:18:37.178836 CST, in "0.031049" seconds.
reading source file list
reading target file list
reading WAL in target
Rewind datadir file from source
update the control file: minRecoveryPoint is '2/127FA40', minRecoveryPointTLI is '19', and database state is 'in archive recovery'
rewind start wal location 2/125C268 (file 000000130000000200000001), end wal location 2/127FA40 (file 000000130000000200000001). time from 2022-09-21 16:18:37.147787 CST to 2022-09-21 16:18:38.887227 CST, in "1.739440" seconds.
Done!
server started
ksql "port=54321 user=SUPERMANAGER_V8ADMIN dbname=TEST connect_timeout=10" -c "select 33333;"
SYS_CREATE_PHYSICAL_REPLICATION_SLOT
--------------------------------------
(slot_node101,)
(1 row)
2022-09-21 16:18:43 create the slot [slot_node101] success.
SYS_CREATE_PHYSICAL_REPLICATION_SLOT
--------------------------------------
(slot_node102,)
(1 row)
2022-09-21 16:18:43 create the slot [slot_node102] success.
2022-09-21 16:18:43 start up standby successful!
can not get the replication of myself
---日志显示,在执行pcp_attach_node后,执行sys_rewind做节点的recovery,
sys_rewind执行成功,备库仍然为‘down’状态。
3、查看备库recovery配置文件
1)recovery.conf 配置
[kingbase@node102 data]$ cat recovery.conf
standby_mode='on'
primary_conninfo='port=54321 host=192.168.1.101 user=SYSTEM password=MTIzNDU2 application_name=node101
primary_slot_name ='slot_node101'
2)备库etc/recovery.done
[kingbase@node102 data]$ cat etc/recovery.done
standby_mode='on'
primary_conninfo='port=54321 host=192.168.1.101 user=SYSTEM password=MTIzNDU2 application_name=node101
primary_slot_name ='slot_node101'
3)查看主库recovery.done
[kingbase@node101 bin]$ cat ../data/recovery.done
standby_mode='on'
primary_conninfo='port=54321 host=192.168.1.102 user=SYSTEM password=MTIzNDU2 application_name=node101'
recovery_target_timeline='latest'
primary_slot_name ='slot_node101'
4、查看备库复制槽信息
TEST=# select * from sys_replication_slots;
SLOT_NAME | PLUGIN | SLOT_TYPE | DATOID | DATABASE | ACTIVE | ACTIVE_PID | XMIN | CATALOG_XMIN | RESTART_LSN | CONFIRMED_FL
USH_LSN
--------------+--------+-----------+--------+----------+--------+------------+--------+--------------+-------------+-------------
--------
slot_node101 | | physical | | | t | 6937 | 114712 | | 2/12A1468 |
slot_node102 | | physical | | | f | | 113518 | | 1/FD000878 |
(2 rows)
###由于备库recovery.conf配置问题,导致流复制备库应用了错误的复制槽。
四、问题解决
1、修改备库recovery.conf
[kingbase@node102 data]$ cat recovery.conf
standby_mode='on'
primary_conninfo='port=54321 host=192.168.1.101 user=SYSTEM password=MTIzNDU2 application_name=node102
primary_slot_name ='slot_node102'
2、 备库etc/recovery.done
[kingbase@node102 data]$ cat etc/recovery.done
standby_mode='on'
primary_conninfo='port=54321 host=192.168.1.101 user=SYSTEM password=MTIzNDU2 application_name=node102
primary_slot_name ='slot_node102'
3、关闭备库数据库服务
[kingbase@node102 bin]$ ./sys_ctl stop -D ../data
waiting for server to shut down.... done
server stopped
4、删除复制槽
TEST=# select sys_drop_replication_slot('slot_node101');
SYS_DROP_REPLICATION_SLOT
---------------------------
(1 row)
5、重启集群
[kingbase@node101 bin]$ ./kingbase_monitor.sh restart
6、查看节点状态
TEST=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+---------------+-------+--------+-----------+---------+------------+-------------------+-------------------
0 | 192.168.1.101 | 54321 | up | 0.500000 | primary | 0 | true | 0
1 | 192.168.1.102 | 54321 | up | 0.500000 | standby | 0 | false | 0
(2 rows)
[kingbase@node101 bin]$ ./pcp_node_info -U kingbase 1
Password:
192.168.1.102 54321 2 0.500000 up
[kingbase@node101 bin]$ ./pcp_node_info -U kingbase 0
Password:
192.168.1.101 54321 2 0.500000 up
###如上所示,备库节点已经正常。
7、查看备库recovery.log
.......
2022-09-21 16:37:02 check if the network is ok
ping trust ip 192.168.1.1 success ping times :[3], success times:[2]
determine if i am master or standby
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+---------------+-------+--------+-----------+---------+------------+-------------------+-------------------
0 | 192.168.1.101 | 54321 | up | 0.500000 | primary | 0 | false | 0
1 | 192.168.1.102 | 54321 | up | 0.500000 | standby | 0 | true | 0
(2 rows)
i am standby in cluster,determine if recovery is needed
2022-09-21 16:37:04 now will del vip [192.168.1.200/24]
but no 192.168.1.200/24 on my DEV, nothing to do with del
cluster is sync cluster.
now,there is a synchronous standby.
2022-09-21 16:37:07 ALL NODES ARE UP STATUS!
2022-09-21 16:37:07 ALL NODES ARE UP STATUS!
###如上所示,recovery.log中备库状态信息已经正常。
五、总结
1、 对于集群出现的问题,要从多个方面进行分析、判断,找到问题发生的根本原因。
2、对于备库的clone,尽量不要使用静态copy方式,此种方式备库需修改的文件较多,难免有遗漏,出现集群故障问题。
3、建议使用sys_basebackup执行备库clone,clone后注意检查备库集群配置文件。
KINGBASE研究院