KingbaseES V8R6集群运维案例之---备节点恢复为单实例库

KingbaseES V8R6集群运维案例之---备节点恢复为单实例库

案例说明：
在生产环境中，手工将集群节点恢复为单实例节点，操作可以分为两步。第一步，先将节点从repmgr管理中注销，脱离集群的管理；第二步，从流复制中拆分节点，成为单实例节点。适用版本： KingbaseES V8R6

集群架构：

ID | Name  | Role    | Status    | Upstream | repmgrd | PID  | Paused? | Upstream last seen
----+-------+---------+-----------+----------+---------+------+---------+--------------------
 1  | node1 | primary | * running |          | running | 4466 | no      | n/a
 2  | node2 | standby |   running | node1    | running | 9380 | no      | 0 second(s) ago
 3  | node3 | standby |   running | node1    | running | 7377 | no      | 1 second(s) ago

一、节点从集群注销
如下所示，在被注销节点执行命令，从repmgr集群管理中注销此节点：

[kingbase@node103 bin]$ ./repmgr standby unregister  --force
----+-------+---------+-----------+----------+----------+----------+----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 20       |         | host=192.168.1.101 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
 2  | node2 | standby |   running | node1    | default  | 100      | 20       | 0 bytes | host=192.168.1.102 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3

 # 从主库节点查看，node3节点已经被注销
 [kingbase@node101 bin]$ ./repmgr cluster show
  ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | LSN_Lag | Connection string                                                                                                                     
----+-------+---------+-----------+----------+----------+----------+----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 20       |         | host=192.168.1.101 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
 2  | node2 | standby |   running | node1    | default  | 100      | 20       | 0 bytes | host=192.168.1.102 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3

二、 节点从流复制拆分1、查看当前流复制状态(如下所示，node3仍然是流复制备库节点)

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
------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 4024 |    16385 | esrep   | node3            | 192.168.1.103 |                 |       25675 | 2023-07-11 09:34:59.201690+08 |              | streaming | 1/C2000DA0 | 1/C2000DA0 | 1/C2000DA0 | 1/C2000DA0 |           |     |            |             0 | async      | 2023-07-11 09:37:50.359201+08
 4026 |    16385 | esrep   | node2            | 192.168.1.102 |                 |       14609 | 2023-07-11 09:35:00.173768+08 |              | streaming | 1/C2000DA0 | 1/C2000DA0 | 1/C2000DA0 | 1/C2000DA0 |           |     |            |             1 | sync       | 2023-07-11 09:37:49.073643+08
(2 rows)

2、复制槽状态信息
如下图所示，复制槽状态正常：

test=# select * from sys_replication_slots;
   slot_name   | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin  | catalog_xmin | restart_lsn | confirmed_flush_lsn
---------------+--------+-----------+--------+----------+-----------+--------+------------+-------+--------------+-------------+---------------------
 repmgr_slot_2 |        | physical  |        |          | f         | t      |       4026 | 60336 | | 1/C2000DA0  |
 repmgr_slot_3 |        | physical  |        |          | f         | t      |       4024 | 60336 | | 1/C2000DA0  |
(2 rows)

3、关闭当前备库集群及数据库服务
如下所示，通过sys_monitor.sh stoplocal可以关闭此节点原有的集群管理和数据库服务：

[kingbase@node103 bin]$ ./sys_monitor.sh stoplocal
Service process "node_export" was killed at process 8204
Service process "postgres_ex" was killed at process 8205
2023-07-11 09:49:39 begin to stop repmgrd on "[localhost]".
2023-07-11 09:49:39 repmgrd on "[localhost]" stop success.
2023-07-11 09:49:39 begin to stop DB on "[localhost]".
waiting for server to shut down...... done
server stopped
2023-07-11 09:49:43 DB on "[localhost]" stop success.

4、删除复制槽
在主库执行，将拆分的备库节点复制槽删除：

test=# select * from sys_replication_slots;
   slot_name   | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin  | catalog_xmin | restart_lsn | confirmed_flush_lsn
---------------+--------+-----------+--------+----------+-----------+--------+------------+-------+--------------+-------------+---------------------
 repmgr_slot_2 |        | physical  |        |          | f         | t      |       4026 | 60337 | | 1/C2000E98  |
 repmgr_slot_3 |        | physical  |        |          | f         | f      |            | 60336 | | 1/C2000DA0  |
(2 rows)

test=# select sys_drop_replication_slot('repmgr_slot_3');
 sys_drop_replication_slot
---------------------------

(1 row)

test=# select * from sys_replication_slots;
   slot_name   | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin  | catalog_xmin | restart_lsn | confirmed_flush_lsn
---------------+--------+-----------+--------+----------+-----------+--------+------------+-------+--------------+-------------+---------------------
 repmgr_slot_2 |        | physical  |        |          | f         | t      |       4026 | 60337 | | 1/C2002560  |
(1 row)

Tips:
删除备库节点复制槽，必须此复制槽active=‘f’：

三、清理集群配置1、关闭此节点kbha进程的启动

[root@node103 ~]# cat /etc/cron.d/KINGBASECRON
#*/1 * * * * kingbase . /etc/profile;/home/kingbase/cluster/R6HA/ha7/kingbase/kingbase/bin/kbha -A daemon -f /home/kingbase/cluster/R6HA/ha7/kingbase/kingbase/bin/../etc/repmgr.conf

---注释或删除。

2、清除备库标识文件(数据库启动为主库)

[kingbase@node103 data]$ ls -lh standby.signal
-rw------- 1 kingbase kingbase 20 Jul 10 15:19 standby.signal
[kingbase@node103 data]$ mv standby.signal standby.signal.bk

3、修改事务commit同步机制

[kingbase@node103 bin]$ cat  /data/kingbase/hac7/data/es_rep.conf|grep -i _commit
synchronous_commit = remote_apply

#修改为以下配置
[kingbase@node103 bin]$ cat  /data/kingbase/hac7/data/es_rep.conf|grep -i _commit
synchronous_commit = local

4、启动此节点数据库服务

[kingbase@node103 bin]$ ./sys_ctl start -D /data/kingbase/hac7/data/
waiting for server to start....2023-07-11 09:49:48.082 CST [12054] LOG:  sepapower extension initialized
.......
server started

# 如下所示，此节点数据库服务启动为主库状态
test=# select sys_is_in_recovery();
 sys_is_in_recovery
--------------------
 f
(1 row)

四、卸载repmgr插件及数据

1、取消repmgr加载
如下图所示，repmgr作为插件加载，可以在配置文件中取消加载：

Tips：
esrep用户和数据库，用于存储repmgr集群元数据，可以在恢复为单实例后，删除相应的库和用户：

2、删除esrep数据库

test=# \l
                               List of databases
   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
 esrep     | system | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 prod      | system | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 security  | system | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | system | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/system        +
           |        |          |             |             | system=CTc/system
 template1 | system | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/system        +
           |        |          |             |             | system=CTc/system
 test      | system | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)

test=# drop database esrep;
DROP DATABASE

3、删除esrep用户

test=# drop user esrep;
DROP ROLE

test=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 sao       | No inheritance                                             | {}
 sso       | No inheritance                                             | {}
 system    | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
           | Password valid until infinity                              |
 tom       | Superuser                                                  | {}

五、总结
对于KingbaseES V8R6集群，将集群备库节点恢复为单实例节点操作相对比较简单，对于主库恢复为单实例库，可以在执行switchover切换后，将主库切换为备库后，从集群恢复为单实例库。