金仓数据库KingbaseES V8R6 集群测试job管理测试

案例说明
本案例参考《Job And Schedule (V8R6C4)》（https://www.cnblogs.com/kingbase/p/15194227.html）单实例环境下的job管理案例。
本案例重点是在测试集群切换后在新的主库下，job任务是否能正常被执行。
测试数据库版本
prod=# 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@node3 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----±--------±--------±----------±---------±---------±---------±---------±------------------
1 | node243 | primary | * running | | default | 100 | 1 | host=192.168.7.243 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node248 | standby | running | node243 | default | 100 | 1 | host=192.168.7.248 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
一、配置job
1、配置kb_schedule extension （所有节点）
[kingbase@node3 data]$ cat kingbase.conf |grep schedule
shared_preload_libraries = ‘repmgr,liboracle_parser, synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, ora_commands,kdb_ora_expr, sepapower, dblink, sys_kwr, sys_ksh, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function,kdb_schedule’

prod=# select name from sys_available_extensions where name like ‘%schedule%’;
name

kdb_schedule
(1 row)
2、所有节点配置job_queue_processes
prod=# show job_queue_processes;
job_queue_processes

0
(1 row)

prod=# alter system set job_queue_processes=5;
ALTER SYSTEM
prod=# select sys_reload_conf();
sys_reload_conf

t
(1 row)
注意： 配置完成后，重启集群。

主备库：

test=# show job_queue_processes;
job_queue_processes

5
(1 row)
3、测试job管理
主库执行：
1）创建测试表
prod=# create table d_test(tid varchar2(64), insdate date);
CREATE TABLE
2）创建测试procedure
prod=# \set SQLTERM /
prod=#
prod=# create or replace procedure p_test() as
prod-# begin
prod-# insert into d_test values(to_char(sysdate, ‘yyyymmddhh24miss’), sysdate);
prod-# commit;
prod-# end;
prod-# /

CREATE PROCEDURE
\set SQLTERM ;
3）创建job
prod=# \set SQLTERM ;
prod=#
prod=# \set SQLTERM /
prod=# DECLARE
prod-# v_jobid NUMBER;
prod-# BEGIN
prod-# dbms_job.submit(v_jobid, ‘call p_test()’, now(), ‘Freq=Minutely;Interval=1’);
prod-# COMMIT;
prod-# END;
prod-# /\set SQLTERM ;ANONYMOUS BLOCK
prod=# \set SQLTERM ;
prod=# call dbms_job.instance(1, ‘user=system dbname=prod port=54321 password=123456ab’);
CALL
4）主备库查询
prod=# select jobid,jobname,jobenabled,joblastrun,jobnextrun,jobrepeattimes from kdb_job;
jobid | jobname | jobenabled | joblastrun | jobnextrun | jobrepeattimes
-------±--------------------------------------------±-----------±-----------±--------------------------
1 | internal_job1:2021-03-01 16:52:12.416076+08 | f | | 2021-03-01 16:52:12.416076+08 | 0
(1 row)
主库执行：
5）启用job
prod=# call dbms_job.broken(1,false);
CALL
主备库查询：
prod=# select jobid,jobname,jobenabled,joblastrun,jobnextrun,jobrepeattimes from kdb_job;
jobid | jobname | jobenabled | joblastrun | jobnextrun | jobrepeattimes
-------±--------------------------------------------±-----------±-----------±-------------------------
1 | internal_job1:2021-03-01 16:52:12.416076+08 | t | | 2021-03-01 16:52:12.416076+08 | 0
(1 row)

prod=# select * from kdb_schedule.kdb_schedule;
scid | scname | scdesc | scenabled | scstart | scend | screpeat_interval
------±-------±-------±----------±---------------------------±------±-------------------------
1 | | | t | 2021-03-01 16:52:12.416076 | | Freq=Minutely;Interval=1
(1 row)

prod=# select * from kdb_schedule.kdb_schedule_job;
sjid | sjscid | sjjobid | sjstatus | sjlasttime | sjnexttime
------±-------±--------±---------±-----------±------------------------------
1 | 1 | 1 | s | | 2021-03-01 16:52:12.416076+08
(1 row)
6）查看job执行情况

主备库查询：

prod=# select * from d_test;
tid | insdate
----------------±--------------------
20210301170529 | 2021-03-01 17:05:29
20210301171623 | 2021-03-01 17:16:23
20210301172126 | 2021-03-01 17:21:26
20210301172216 | 2021-03-01 17:22:16
20210301172316 | 2021-03-01 17:23:16
20210301172416 | 2021-03-01 17:24:16
(6 rows)
二、主备切换测试
1、switchover切换

执行switchover

[kingbase@node1 bin]$ ./repmgr standby switchover -h 192.168.7.243 -U esrep -d esrep --dry-runWARNING: following problems with command line parameters detected:
database connection parameters not required when executing UNKNOWN ACTIONNOTICE: checking switchover on node “node248” (ID: 2) in --dry-run modeINFO: ES connection to host “192.168.7.243” succeededINFO: able to execute “repmgr” on remote host "localhost"INFO: 1 walsenders required, 32 availableINFO: demotion candidate is able to make replication connection to promotion candidateINFO: 0 pending archive filesINFO: replication lag on this standby is 0 secondsINFO: 1 replication slots required, 32 availableINFO: would pause repmgrd on node “node243” (ID 1)INFO: would pause repmgrd on node “node248” (ID 2)NOTICE: local node “node248” (ID: 2) would be promoted to primary; current primary “node243” (ID: 1) would be demoted to standbyINFO: following shutdown command would be run on node “node243”:
"/home/kingbase/cluster/R6C5/R6C5R/kingbase/bin/sys_ctl -D ‘/home/kingbase/cluster/R6C5/R6C5R/kingbase/data’ -l /home/kingbase/cluster/R6C5/R6C5R/kingbase/bin/logfile -W -m fast stop"INFO: prerequisites for executing STANDBY SWITCHOVER are met

[kingbase@node1 bin]$ ./repmgr standby switchover -h 192.168.7.243 -U esrep -d esrep WARNING: following problems with command line parameters detected:
database connection parameters not required when executing UNKNOWN ACTIONNOTICE: executing switchover on node “node248” (ID: 2)INFO: pausing repmgrd on node “node243” (ID 1)INFO: pausing repmgrd on node “node248” (ID 2)NOTICE: local node “node248” (ID: 2) will be promoted to primary; current primary “node243” (ID: 1) will be demoted to standbyNOTICE: stopping current primary node “node243” (ID: 1)NOTICE: issuing CHECKPOINTNOTICE: node (ID: 1) release the virtual ip 192.168.7.241/24 successDETAIL: executing server command "/home/kingbase/cluster/R6C5/R6C5R/kingbase/bin/sys_ctl -D ‘/home/kingbase/cluster/R6C5/R6C5R/kingbase/data’ -l /home/kingbase/cluster/R6C5/R6C5R/kingbase/bin/logfile -W -m fast stop"INFO: checking for primary shutdown; 1 of 60 attempts (“shutdown_check_timeout”)INFO: checking for primary shutdown; 2 of 60 attempts (“shutdown_check_timeout”)INFO: checking for primary shutdown; 3 of 60 attempts (“shutdown_check_timeout”)INFO: checking for primary shutdown; 4 of 60 attempts (“shutdown_check_timeout”)INFO: checking for primary shutdown; 5 of 60 attempts (“shutdown_check_timeout”)NOTICE: current primary has been cleanly shut down at location 0/8000028NOTICE: PING 192.168.7.241 (192.168.7.241) 56(84) bytes of data.

— 192.168.7.241 ping statistics —2 packets transmitted, 0 received, +1 errors, 100% packet loss, time 1003ms

WARNING: ping host"192.168.7.241" failedDETAIL: average RTT value is not greater than zeroINFO: loadvip result: 1, arping result: 1NOTICE: new primary node (ID: 2) acquire the virtual ip 192.168.7.241/24 successNOTICE: promoting standby to primaryDETAIL: promoting server “node248” (ID: 2) using sys_promote()NOTICE: waiting up to 60 seconds (parameter “promote_check_timeout”) for promotion to completeNOTICE: STANDBY PROMOTE successfulDETAIL: server “node248” (ID: 2) was successfully promoted to primaryNOTICE: issuing CHECKPOINTINFO: local node 1 can attach to rejoin target node 2DETAIL: local node’s recovery point: 0/8000028; rejoin target node’s fork point: 0/80000A0NOTICE: setting node 1’s upstream to node 2WARNING: unable to ping "host=192.168.7.243 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3"DETAIL: PQping() returned "PQPING_NO_RESPONSE"NOTICE: begin to start server at 2021-03-01 17:26:43.843581NOTICE: starting server using "/home/kingbase/cluster/R6C5/R6C5R/kingbase/bin/sys_ctl -w -t 90 -D ‘/home/kingbase/cluster/R6C5/R6C5R/kingbase/data’ -l /home/kingbase/cluster/R6C5/R6C5R/kingbase/bin/logfile start"NOTICE: start server finish at 2021-03-01 17:26:44.055041NOTICE: replication slot “repmgr_slot_2” deleted on node 1NOTICE: NODE REJOIN successfulDETAIL: node 1 is now attached to node 2NOTICE: switchover was successfulDETAIL: node “node248” is now primary and node “node243” is attached as standbyINFO: unpausing repmgrd on node “node243” (ID 1)INFO: unpause node “node243” (ID 1) successfullyINFO: unpausing repmgrd on node “node248” (ID 2)INFO: unpause node “node248” (ID 2) successfullyNOTICE: STANDBY SWITCHOVER has completed successfully

切换后集群状态：

[kingbase@node1 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----±--------±--------±----------±---------±---------±---------±---------±--------------------------------------------------------------------------------------------------------------------------------------------------
1 | node243 | standby | running | node248 | default | 100 | 1 | host=192.168.7.243 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node248 | primary | * running | | default | 100 | 2 | host=192.168.7.248 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3INFO: 1 replication slots required, 32 available

job正常被执行：

prod=# select * from d_test;
tid | insdate
----------------±--------------------
20210301170529 | 2021-03-01 17:05:29
20210301171623 | 2021-03-01 17:16:23
20210301172126 | 2021-03-01 17:21:26
20210301172216 | 2021-03-01 17:22:16
20210301172316 | 2021-03-01 17:23:16
20210301172416 | 2021-03-01 17:24:16
20210301172516 | 2021-03-01 17:25:16
20210301172616 | 2021-03-01 17:26:16
20210301172908 | 2021-03-01 17:29:08
20210301172918 | 2021-03-01 17:29:18

2、failover 切换测试

执行failover切换# 停止主库数据库服务

[kingbase@node1 bin]$ ./sys_ctl stop -D …/datawaiting for server to shut down… doneserver stopped

failover切换完成：

[kingbase@node1 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----±--------±--------±----------±---------±---------±---------±---------±-----------------
1 | node243 | primary | * running | | default | 100 | 3 | host=192.168.7.243 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node248 | standby | running | node243 | default | 100 | 2 | host=192.168.7.248 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3

#查看job执行：
prod=# select * from d_test;
tid | insdate
----------------±--------------------
20210301170529 | 2021-03-01 17:05:29
20210301171623 | 2021-03-01 17:16:23
20210301172126 | 2021-03-01 17:21:26
20210301172216 | 2021-03-01 17:22:16
20210301172316 | 2021-03-01 17:23:16
20210301172416 | 2021-03-01 17:24:16
20210301172516 | 2021-03-01 17:25:16
20210301172616 | 2021-03-01 17:26:16
20210301172908 | 2021-03-01 17:29:08
20210301172918 | 2021-03-01 17:29:18
20210301173018 | 2021-03-01 17:30:18
20210301173118 | 2021-03-01 17:31:18
20210301173218 | 2021-03-01 17:32:18
20210301173318 | 2021-03-01 17:33:18
20210301173418 | 2021-03-01 17:34:18
20210301173518 | 2021-03-01 17:35:18
20210301173820 | 2021-03-01 17:38:20
20210301173920 | 2021-03-01 17:39:20
20210301174017 | 2021-03-01 17:40:17
20210301174117 | 2021-03-01 17:41:17
(20 rows)
删除job：
prod=# call dbms_job.remove(1);
CALL

prod=# select * from kdb_schedule.kdb_schedule_job;
sjid | sjscid | sjjobid | sjstatus | sjlasttime | sjnexttime
------±-------±--------±---------±-----------±-----------
(0 rows)
三、总结
通过以上案例说明，在集群环境下job的管理和单实例环境下配置基本一致，集群主备切换后，主库上的job任务亦可以正常被执行。
参考资料
更多金仓数据库KingbaseES信息，详见 KingbaseES产品手册