KingbaseES V8R6集群运维系列 -- 集群环境job and schedule管理

案例说明：

本案例参考《Job And Schedule (V8R6C4)》（https://www.cnblogs.com/kingbase/p/15194227.html）单实例环境下的job and schedule管理案例，本案例重点是在集群切换后在新的主库下，job任务是否能正常被执行。

适用版本：

   KingbaseES V8R6

集群架构：

[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 （all nodes）

[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           | j
obrepeattimes 
-------+---------------------------------------------+------------+------------+---------------------------
     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           | j
obrepeattimes 
-------+---------------------------------------------+------------+------------+--------------------------
     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-run
WARNING: following problems with command line parameters detected:
......
DETAIL: node "node248" is now primary and node "node243" is attached as standby
INFO: unpausing repmgrd on node "node243" (ID 1)
INFO: unpause node "node243" (ID 1) successfully
INFO: unpausing repmgrd on node "node248" (ID 2)
INFO: unpause node "node248" (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  | 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=3
INFO: 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 ../data
waiting for server to shut down........ done
server 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任务亦可以正常被执行。