KingbaseES集群运维案例之-- V8R3与V8R6集群wal函数应用
案例说明：
KingbaseES V8R3和V8R6集群在通过函数获取wal日志的相关信息时，两个版本的函数名称不同，本案例做了函数应用的对比和总结。
适用版本：
KingbaseES V8R3/R6
一、KingbaseES V8R3相关函数
Tips：
在V8R3的版本，事务日志名称为xlog。
1、查询数据库支持的函数
test=# select proname from sys_proc where proname like '%xlog%';
proname
----------------------------------
SYS_CURRENT_XLOG_LOCATION
SYS_CURRENT_XLOG_INSERT_LOCATION
SYS_CURRENT_XLOG_FLUSH_LOCATION
SYS_XLOGFILE_NAME_OFFSET
SYS_XLOGFILE_NAME
SYS_XLOG_LOCATION_DIFF
SYS_LAST_XLOG_RECEIVE_LOCATION
SYS_LAST_XLOG_REPLAY_LOCATION
SYS_IS_XLOG_REPLAY_PAUSED
SYS_XLOG_REPLAY_RESUME
SYS_SWITCH_XLOG
SYS_XLOG_REPLAY_PAUSE
(12 rows)
2、xlog切换并查看对应wal日志文件
test=# select sys_switch_xlog();
sys_switch_xlog
-----------------
0/E80002A8
(1 row)
test=# select SYS_XLOGFILE_NAME('0/E80002A8');
SYS_XLOGFILE_NAME
--------------------------
0000000300000000000000E8
(1 row)
3、获取当前lsn对应的日志文件及offset
# SQL文本
select SYS_CURRENT_XLOG_LOCATION(),SYS_XLOGFILE_NAME(SYS_CURRENT_XLOG_LOCATION()),
SYS_XLOGFILE_NAME_OFFSET(SYS_CURRENT_XLOG_LOCATION());
test=# select SYS_CURRENT_XLOG_LOCATION(),SYS_XLOGFILE_NAME(SYS_CURRENT_XLOG_LOCATION()),
SYS_XLOGFILE_NAME_OFFSET(SYS_CURRENT_XLOG_LOCATION());
SYS_CURRENT_XLOG_LOCATION | SYS_XLOGFILE_NAME | SYS_XLOGFILE_NAME_OFFSET
---------------------------+--------------------------+--------------------------------
0/E9000220 | 0000000300000000000000E9 | (0000000300000000000000E9,544)
(1 row)
4、获取当前lsn及flush_lsn和insert_lsn位置
# SQL文本
select SYS_CURRENT_XLOG_FLUSH_LOCATION(),SYS_CURRENT_XLOG_INSERT_LOCATION(),
SYS_CURRENT_XLOG_LOCATION();
test=# select SYS_CURRENT_XLOG_FLUSH_LOCATION(),SYS_CURRENT_XLOG_INSERT_LOCATION(),
test-# SYS_CURRENT_XLOG_LOCATION();
SYS_CURRENT_XLOG_FLUSH_LOCATION | SYS_CURRENT_XLOG_INSERT_LOCATION | SYS_CURRENT_XLOG_LOCATION
---------------------------------+----------------------------------+---------------------------
0/E9000300 | 0/E9000300 | 0/E9000300
(1 row)
5、对比lsn之间的差异值
test=# select SYS_XLOG_LOCATION_DIFF('0/8BF6C80','0/8BF6C68');
SYS_XLOG_LOCATION_DIFF
------------------------
24
(1 row)
6、获取主备库之间复制延迟
# 主备库之间流复制状态
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 | sy
nc_priority | sync_state
-------+----------+---------+------------------+---------------+-----------------+-------------+-----------------
--------------+--------------+-----------+---------------+----------------+----------------+-----------------+---
------------+------------
32377 | 10 | SYSTEM | node2 | 192.168.1.202 | | 35947 | 2023-10-26 15:03
:45.046059+08 | | streaming | 0/E80001B0 | 0/E80001B0 | 0/E80001B0 | 0/E8000178 |
2 | sync
(1 row)
# SQL文本
select pid,usename,client_addr,state,
SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),write_location) write_delay,
SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),flush_location) flush_delay,
SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),replay_location) replay_dely
from sys_stat_replication;
test=# select pid,usename,client_addr,state,
test-# SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),write_location) write_delay,
test-# SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),flush_location) flush_delay,
test-# SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),replay_location) replay_dely
test-# from sys_stat_replication;
pid | usename | client_addr | state | write_delay | flush_delay | replay_dely
-------+---------+---------------+-----------+-------------+-------------+-------------
32377 | SYSTEM | 192.168.1.202 | streaming | 0 | 0 | 0
(1 row)
7、获取备库lsn信息
test=# select SYS_LAST_XLOG_RECEIVE_LOCATION();
SYS_LAST_XLOG_RECEIVE_LOCATION
--------------------------------
0/E90003E0
(1 row)
test=# select SYS_LAST_XLOG_REPLAY_LOCATION();
SYS_LAST_XLOG_REPLAY_LOCATION
-------------------------------
0/E90003E0
(1 row)
二、KingbaseES V8R6相关函数
Tips：
在V8R6的版本，事务日志名称为wal。
1、查询数据库支持的函数
test=# select proname from sys_proc where proname like '%wal%';
proname
----------------------------
pg_stat_get_wal_senders
pg_stat_get_wal_receiver
pg_current_wal_lsn
pg_current_wal_insert_lsn
pg_current_wal_flush_lsn
pg_walfile_name_offset
pg_walfile_name
pg_wal_lsn_diff
pg_last_wal_receive_lsn
pg_last_wal_replay_lsn
pg_is_wal_replay_paused
pg_switch_wal
pg_wal_replay_pause
pg_wal_replay_resume
pg_ls_waldir
pg_stat_get_wal_buffer
sys_switch_wal
sys_current_wal_lsn
sys_walfile_name_offset
sys_walfile_name
sys_current_wal_insert_lsn
sys_wal_replay_pause
sys_wal_replay_resume
sys_is_wal_replay_paused
sys_stat_get_wal_senders
sys_wal_lsn_diff
sys_stat_get_wal_receiver
sys_current_wal_flush_lsn
sys_ls_waldir
sys_stat_get_wal_buffer
sys_last_wal_receive_lsn
sys_last_wal_replay_lsn
(32 rows)
2、wal切换并查看对应wal日志文件
test=# select sys_switch_wal();
sys_switch_wal
----------------
0/1B0046E8
(1 row)
test=# select pg_walfile_name('0/1B0046E8');
pg_walfile_name
--------------------------
00000003000000000000001B
(1 row)
3、获取当前lsn对应的日志文件及offset
test=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+----------------------------------
5/C0006590 | 0000001B00000005000000C0 | (0000001B00000005000000C0,26000)
(1 row)
4、查看wal日志文件存储目录及修改时间
test=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
------------------------------------------+----------+------------------------
0000001B00000005000000BD | 16777216 | 2021-03-01 12:43:26+08
0000001B00000005000000BE | 16777216 | 2021-03-01 12:45:30+08
0000001A00000005000000B3.partial | 16777216 | 2021-03-01 13:17:33+08
0000001B.history | 834 | 2021-03-01 13:17:45+08
........
0000001B00000005000000BC | 16777216 | 2021-03-01 15:56:16+08
0000001A.history | 790 | 2021-03-01 17:25:02+08
00000018.history | 702 | 2021-03-01 18:31:40+08
(24 rows)
4、获取当前lsn及flush_lsn和insert_lsn位置
test=# select pg_current_wal_flush_lsn(),pg_current_wal_insert_lsn(),
pg_current_wal_lsn();
pg_current_wal_flush_lsn | pg_current_wal_insert_lsn | pg_current_wal_lsn
--------------------------+---------------------------+--------------------
5/C0006590 | 5/C0006590 | 5/C0006590
(1 row)
5、对比lsn之间的差异值
test=# select pg_wal_lsn_diff('0/8BF6C80','0/8BF6C68');
pg_wal_lsn_diff
-----------------
24
(1 row)
6、查询主备库之间复制延迟
test=# select pid,usename,client_addr,state,write_lag,flush_lag,replay_lag from pg_stat_replication;
pid | usename | client_addr | state | write_lag | flush_lag | replay_lag
-------+---------+---------------+-----------+-----------+-----------+------------
12208 | esrep | 192.168.7.243 | streaming | | |
12224 | esrep | 192.168.7.249 | streaming | | |
(2 rows)
test=# select pid,usename,client_addr,state,
test-# pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_delay,
test-# pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,
test-# pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_dely
test-# from pg_stat_replication;
pid | usename | client_addr | state | write_delay | flush_delay | replay_dely
-------+---------+---------------+-----------+-------------+-------------+-------------
12208 | esrep | 192.168.7.243 | streaming | 0 | 0 | 0
12224 | esrep | 192.168.7.249 | streaming | 0 | 0 | 0
(2 rows)
7、查看备库flush_lsn及replay_lsn
prod=# select pg_last_wal_receive_lsn();
pg_last_wal_receive_lsn
-------------------------
5/C0006590
(1 row)
prod=# select pg_last_wal_replay_lsn();
pg_last_wal_replay_lsn
------------------------
5/C0006590
(1 row)
prod=# select pg_last_xact_replay_timestamp();
pg_last_xact_replay_timestamp
-------------------------------
2021-03-01 15:19:28.682064+08
(1 row)
三、总结
由于版本差异，KingbaseES V8R3和R6在wal日志相关查询上，使用的函数名称不同，需要根据版本具体应用。
