KingbaseES V8R6 集群环境wal日志清理
案例说明：
1、对于集群中的wal日志，除了需要在备库执行recovery外，在集群主备切换（switchover或failover）时，sys_rewind都要读取wal日志，将数据库恢复到一致性状态。
2、对于集群主备库中的wal日志，在清理时，经过测试，理论上在checkpoint所在的wal日志之前的都可以清理，但这是比较理想的状态，在生产环境中，建议保留3天到一个星期的wal日志，避免因为主备延迟，导致在集群切换时，因为缺少wal日志失败。
3、对于KingbaseES V8R6的集群，如果在主备库上通过sys_backup.sh工具建立了备份，归档日志会自动备份，应该也会随着历史备份的清理，自动被清理。如果节点没有建立sys_backup.sh的备份，可以通过 sys_archivecleanup工具清理，原则也是在生产环境中，建议保留3天到一个星期的归档日志。
数据库版本：
test=# select version;
version
------------------------------------------------------------------------------------------------------------------
KingbaseES V008R006C005B0023 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)
集群节点信息：
[kingbase@node1 bin]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.8.200 node1 #集群节点node200
192.168.8.201 node2 #集群节点node201
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+---------+---------+-----------+----------+---------+-------+---------+--------------------
1 | node200 | primary | * running | | running | 29303 | no | n/a
2 | node201 | standby | running | node200 | running | 29748 | no | 1 second(s) ago
一、集群switchover切换测试
1、查看主备库控制文件信息
1）主库控制文件
[kingbase@node1 bin]$ ./sys_controldata -D ../data
sys_control version number: 1201
Catalog version number: 202110271
Database system identifier: 7094057752387829054
Database cluster state: in production
sys_control last modified: Tue 10 May 2022 12:33:09 PM CST
Latest checkpoint location: 1/29001768
Latest checkpoint's REDO location: 1/29001738
Latest checkpoint's REDO WAL file: 000000030000000100000029
Latest checkpoint's TimeLineID: 3
2）备库控制文件
[kingbase@node2 bin]$ ./sys_controldata -D ../data
sys_control version number: 1201
Catalog version number: 202110271
Database system identifier: 7094057752387829054
Database cluster state: in archive recovery
sys_control last modified: Thu 19 May 2022 12:05:06 PM CST
Latest checkpoint location: 1/29001768
Latest checkpoint's REDO location: 1/29001738
Latest checkpoint's REDO WAL file: 000000030000000100000029
Latest checkpoint's TimeLineID: 3
2、清理wal日志（将主备库日志都只保留checkpoint所在的wal日志文件（包括）及以后的）
# 主库保留wal日志
[kingbase@node1 sys_wal]$ ls -lh
total 49M
-rw-------. 1 kingbase kingbase 16M May 10 13:19 000000030000000100000029
-rw-------. 1 kingbase kingbase 16M May 10 13:19 00000003000000010000002A
-rw-------. 1 kingbase kingbase 16M May 10 13:23 00000003000000010000002B
-rw-------. 1 kingbase kingbase 85 May 18 11:28 00000003.history
drwx------. 2 kingbase kingbase 24K May 10 13:19 archive_status
drwxrwxr-x. 2 kingbase kingbase 4.0K May 19 12:58 log_bk
# 备库保留wal日志
[kingbase@node2 sys_wal]$ ls -lh
total 49M
-rw------- 1 kingbase kingbase 16M May 19 12:51 000000030000000100000029
-rw------- 1 kingbase kingbase 16M May 19 12:51 00000003000000010000002A
-rw------- 1 kingbase kingbase 16M May 19 12:55 00000003000000010000002B
-rw------- 1 kingbase kingbase 85 May 18 11:28 00000003.history
drwx------ 2 kingbase kingbase 12K May 19 12:51 archive_status
drwxrwxr-x 2 kingbase kingbase 4.0K May 19 13:00 log_bk
3、执行repmgr standby switchover
1）查看当前集群状态信息
[kingbase@node2 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+----------------
1 | node200 | primary | * running | | default | 100 | 3 | host=192.168.8.200 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node201 | standby | running | node200 | default | 100 | 3 | host=192.168.8.201 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2）执行switchover
[kingbase@node2 bin]$ ./repmgr standby switchover -h 192.168.8.200 -U esrep -d esrep
WARNING: following problems with command line parameters detected:
......
INFO: unpause node "node201" (ID 2) successfully
NOTICE: STANDBY SWITCHOVER has completed successfully
[kingbase@node2 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+----------------
1 | node200 | standby | running | node201 | default | 100 | 3 | host=192.168.8.200 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node201 | primary | * running | | default | 100 | 4 | host=192.168.8.201 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
执行回切测试：
[kingbase@node1 bin]$ ./repmgr standby switchover -h 192.168.8.201 -U esrep -d esrep
WARNING: following problems with command line parameters detected:
INFO: unpause node "node201" (ID 2) successfully
NOTICE: STANDBY SWITCHOVER has completed successfully
[kingbase@node1 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+----------------
1 | node200 | primary | * running | | default | 100 | 5 | host=192.168.8.200 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node201 | standby | running | node200 | default | 100 | 4 | host=192.168.8.201 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
=== 如上所示，switchover切换成功！====
二、集群 failover 切换测试
1、查看当前集群状态信息
[kingbase@node2 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+----------------
1 | node200 | standby | running | node201 | default | 100 | 5 | host=192.168.8.200 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node201 | primary | * running | | default | 100 | 6 | host=192.168.8.201 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2、查看主备库控制文件信息
# 主库：
[kingbase@node2 bin]$ ./sys_controldata -D ../data
sys_control version number: 1201
Catalog version number: 202110271
Database system identifier: 7094057752387829054
Database cluster state: in production
sys_control last modified: Thu 19 May 2022 01:26:08 PM CST
Latest checkpoint location: 1/409BA150
Latest checkpoint's REDO location: 1/3EADD130
Latest checkpoint's REDO WAL file: 00000006000000010000003E
# 备库：
[kingbase@node1 bin]$ ./sys_controldata -D ../data
sys_control version number: 1201
Catalog version number: 202110271
Database system identifier: 7094057752387829054
Database cluster state: in archive recovery
sys_control last modified: Thu 19 May 2022 01:22:19 PM CST
Latest checkpoint location: 1/37000028
Latest checkpoint's REDO location: 1/37000028
Latest checkpoint's REDO WAL file: 000000050000000100000037
3、清理主备库wal日志（将主备库日志都只保留checkpoint所在的wal日志文件（包括）及以后的）
# 主库保留wal日志
[kingbase@node2 sys_wal]$ ls -lh
total 65M
-rw------- 1 kingbase kingbase 16M May 19 13:25 00000006000000010000003E
-rw------- 1 kingbase kingbase 16M May 19 13:26 00000006000000010000003F
-rw------- 1 kingbase kingbase 16M May 19 13:26 000000060000000100000040
-rw------- 1 kingbase kingbase 16M May 19 13:26 000000060000000100000041
-rw------- 1 kingbase kingbase 214 May 19 13:18 00000006.history
drwx------ 2 kingbase kingbase 16K May 19 13:26 archive_status
drwxrwxr-x 2 kingbase kingbase 4.0K May 19 13:30 log_bk
# 备库保留wal日志
[kingbase@node1 sys_wal]$ ls -lh
total 193M
-rw-------. 1 kingbase kingbase 16M May 19 13:17 000000050000000100000037
-rw-------. 1 kingbase kingbase 171 May 19 13:03 00000005.history
-rw-------. 1 kingbase kingbase 16M May 19 13:23 000000060000000100000037
-rw-------. 1 kingbase kingbase 16M May 19 13:24 000000060000000100000038
-rw-------. 1 kingbase kingbase 16M May 19 13:24 000000060000000100000039
-rw-------. 1 kingbase kingbase 16M May 19 13:24 00000006000000010000003A
-rw-------. 1 kingbase kingbase 16M May 19 13:24 00000006000000010000003B
-rw-------. 1 kingbase kingbase 16M May 19 13:25 00000006000000010000003C
-rw-------. 1 kingbase kingbase 16M May 19 13:25 00000006000000010000003D
-rw-------. 1 kingbase kingbase 16M May 19 13:25 00000006000000010000003E
-rw-------. 1 kingbase kingbase 16M May 19 13:25 00000006000000010000003F
-rw-------. 1 kingbase kingbase 16M May 19 13:26 000000060000000100000040
-rw-------. 1 kingbase kingbase 16M May 19 13:26 000000060000000100000041
-rw-------. 1 kingbase kingbase 214 May 19 13:21 00000006.history
drwx------. 2 kingbase kingbase 24K May 19 13:26 archive_status
drwxrwxr-x. 2 kingbase kingbase 4.0K May 19 13:28 log_bk
4、执行failover切换测试
1）关闭主库数据库服务
[kingbase@node2 bin]$ ./sys_ctl stop -D ../data
waiting for server to shut down....... done
server stopped
2）查看切换结果
[kingbase@node1 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------
1 | node200 | primary | * running | | default | 100 | 7 | host=192.168.8.200 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node201 | standby | running | | default | 100 | 6 | host=192.168.8.201 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
=== 如上所示，failover切换成功！====
三、总结
手工清理wal日志，请参考《KingbaseES 单实例环境wal(xlog)日志清理案例》
https://www.cnblogs.com/kingbase/p/16263467.html
KINGBASE研究院
