xxl-job适配postgresql数据库

goxingman

已于 2024-03-01 16:34:22 修改

阅读量597

点赞数 8

分类专栏：定时任务文章标签： postgresql

于 2024-03-01 16:32:16 首次发布

本文链接：https://blog.csdn.net/goxingman/article/details/136400168

版权

定时任务专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1、获取源代码

从github或gitee获取源代码，目前最新版本2.4.1

xxl官网：分布式任务调度平台XXL-JOB

2、创建数据库

DROP TABLE IF EXISTS xxl_job_group;
CREATE TABLE xxl_job_group(
                              id SERIAL NOT NULL,
                              app_name VARCHAR(64) NOT NULL,
                              title VARCHAR(12) NOT NULL,
                              address_type int8 DEFAULT  0,
                              address_list TEXT,
                              update_time TIMESTAMP,
                              PRIMARY KEY (id)
);

COMMENT ON TABLE xxl_job_group IS '';
COMMENT ON COLUMN xxl_job_group.app_name IS '执行器AppName';
COMMENT ON COLUMN xxl_job_group.title IS '执行器名称';
COMMENT ON COLUMN xxl_job_group.address_type IS '执行器地址类型：0=自动注册、1=手动录入';
COMMENT ON COLUMN xxl_job_group.address_list IS '执行器地址列表，多地址逗号分隔';

DROP TABLE IF EXISTS xxl_job_info;
CREATE TABLE xxl_job_info(
                             id SERIAL NOT NULL,
                             job_group INTEGER NOT NULL,
                             job_desc VARCHAR(255) NOT NULL,
                             add_time TIMESTAMP,
                             update_time TIMESTAMP,
                             author VARCHAR(64),
                             alarm_email VARCHAR(255),
                             schedule_type VARCHAR(50) NOT NULL DEFAULT  'NONE',
                             schedule_conf VARCHAR(128),
                             misfire_strategy VARCHAR(50) NOT NULL DEFAULT  'DO_NOTHING',
                             executor_route_strategy VARCHAR(50),
                             executor_handler VARCHAR(255),
                             executor_param VARCHAR(512),
                             executor_block_strategy VARCHAR(50),
                             executor_timeout INTEGER NOT NULL DEFAULT  0,
                             executor_fail_retry_count INTEGER NOT NULL DEFAULT  0,
                             glue_type VARCHAR(50) NOT NULL,
                             glue_source text,
                             glue_remark VARCHAR(128),
                             glue_updatetime TIMESTAMP,
                             child_jobid VARCHAR(255),
                             trigger_status int8 DEFAULT  0,
                             trigger_last_time BIGINT NOT NULL DEFAULT  0,
                             trigger_next_time BIGINT NOT NULL DEFAULT  0,
                             PRIMARY KEY (id)
);

COMMENT ON TABLE xxl_job_info IS '';
COMMENT ON COLUMN xxl_job_info.job_group IS '执行器主键ID';
COMMENT ON COLUMN xxl_job_info.author IS '作者';
COMMENT ON COLUMN xxl_job_info.alarm_email IS '报警邮件';
COMMENT ON COLUMN xxl_job_info.schedule_type IS '调度类型';
COMMENT ON COLUMN xxl_job_info.schedule_conf IS '调度配置，值含义取决于调度类型';
COMMENT ON COLUMN xxl_job_info.misfire_strategy IS '调度过期策略';
COMMENT ON COLUMN xxl_job_info.executor_route_strategy IS '执行器路由策略';
COMMENT ON COLUMN xxl_job_info.executor_handler IS '执行器任务handler';
COMMENT ON COLUMN xxl_job_info.executor_param IS '执行器任务参数';
COMMENT ON COLUMN xxl_job_info.executor_block_strategy IS '阻塞处理策略';
COMMENT ON COLUMN xxl_job_info.executor_timeout IS '任务执行超时时间，单位秒';
COMMENT ON COLUMN xxl_job_info.executor_fail_retry_count IS '失败重试次数';
COMMENT ON COLUMN xxl_job_info.glue_type IS 'GLUE类型';
COMMENT ON COLUMN xxl_job_info.glue_source IS 'GLUE源代码';
COMMENT ON COLUMN xxl_job_info.glue_remark IS 'GLUE备注';
COMMENT ON COLUMN xxl_job_info.glue_updatetime IS 'GLUE更新时间';
COMMENT ON COLUMN xxl_job_info.child_jobid IS '子任务ID，多个逗号分隔';
COMMENT ON COLUMN xxl_job_info.trigger_status IS '调度状态：0-停止，1-运行';
COMMENT ON COLUMN xxl_job_info.trigger_last_time IS '上次调度时间';
COMMENT ON COLUMN xxl_job_info.trigger_next_time IS '下次调度时间';

DROP TABLE IF EXISTS xxl_job_lock;
CREATE TABLE xxl_job_lock(
                             lock_name VARCHAR(50) NOT NULL,
                             PRIMARY KEY (lock_name)
);

COMMENT ON TABLE xxl_job_lock IS '';
COMMENT ON COLUMN xxl_job_lock.lock_name IS '锁名称';

DROP TABLE IF EXISTS xxl_job_log;
CREATE TABLE xxl_job_log(
                            id SERIAL NOT NULL,
                            job_group INTEGER NOT NULL,
                            job_id INTEGER NOT NULL,
                            executor_address VARCHAR(255),
                            executor_handler VARCHAR(255),
                            executor_param VARCHAR(512),
                            executor_sharding_param VARCHAR(20),
                            executor_fail_retry_count INTEGER NOT NULL DEFAULT  0,
                            trigger_time TIMESTAMP,
                            trigger_code INTEGER NOT NULL,
                            trigger_msg TEXT,
                            handle_time TIMESTAMP,
                            handle_code INTEGER NOT NULL,
                            handle_msg TEXT,
                            alarm_status int8 DEFAULT  0,
                            PRIMARY KEY (id)
);

COMMENT ON TABLE xxl_job_log IS '';
COMMENT ON COLUMN xxl_job_log.job_group IS '执行器主键ID';
COMMENT ON COLUMN xxl_job_log.job_id IS '任务，主键ID';
COMMENT ON COLUMN xxl_job_log.executor_address IS '执行器地址，本次执行的地址';
COMMENT ON COLUMN xxl_job_log.executor_handler IS '执行器任务handler';
COMMENT ON COLUMN xxl_job_log.executor_param IS '执行器任务参数';
COMMENT ON COLUMN xxl_job_log.executor_sharding_param IS '执行器任务分片参数，格式如 1/2';
COMMENT ON COLUMN xxl_job_log.executor_fail_retry_count IS '失败重试次数';
COMMENT ON COLUMN xxl_job_log.trigger_time IS '调度-时间';
COMMENT ON COLUMN xxl_job_log.trigger_code IS '调度-结果';
COMMENT ON COLUMN xxl_job_log.trigger_msg IS '调度-日志';
COMMENT ON COLUMN xxl_job_log.handle_time IS '执行-时间';
COMMENT ON COLUMN xxl_job_log.handle_code IS '执行-状态';
COMMENT ON COLUMN xxl_job_log.handle_msg IS '执行-日志';
COMMENT ON COLUMN xxl_job_log.alarm_status IS '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败';


CREATE INDEX I_handle_code ON xxl_job_log(handle_code);
CREATE INDEX I_trigger_time ON xxl_job_log(trigger_time);

DROP TABLE IF EXISTS xxl_job_log_report;
CREATE TABLE xxl_job_log_report(
                                   id SERIAL NOT NULL,
                                   trigger_day TIMESTAMP,
                                   running_count INTEGER NOT NULL DEFAULT  0,
                                   suc_count INTEGER NOT NULL DEFAULT  0,
                                   fail_count INTEGER NOT NULL DEFAULT  0,
                                   update_time TIMESTAMP,
                                   PRIMARY KEY (id)
);

COMMENT ON TABLE xxl_job_log_report IS '';
COMMENT ON COLUMN xxl_job_log_report.trigger_day IS '调度-时间';
COMMENT ON COLUMN xxl_job_log_report.running_count IS '运行中-日志数量';
COMMENT ON COLUMN xxl_job_log_report.suc_count IS '执行成功-日志数量';
COMMENT ON COLUMN xxl_job_log_report.fail_count IS '执行失败-日志数量';


CREATE UNIQUE INDEX i_trigger_day ON xxl_job_log_report(trigger_day);

DROP TABLE IF EXISTS xxl_job_logglue;
CREATE TABLE xxl_job_logglue(
                                id SERIAL NOT NULL,
                                job_id INTEGER NOT NULL,
                                glue_type VARCHAR(50),
                                glue_source text,
                                glue_remark VARCHAR(128) NOT NULL,
                                add_time TIMESTAMP,
                                update_time TIMESTAMP,
                                PRIMARY KEY (id)
);

COMMENT ON TABLE xxl_job_logglue IS '';
COMMENT ON COLUMN xxl_job_logglue.job_id IS '任务，主键ID';
COMMENT ON COLUMN xxl_job_logglue.glue_type IS 'GLUE类型';
COMMENT ON COLUMN xxl_job_logglue.glue_source IS 'GLUE源代码';
COMMENT ON COLUMN xxl_job_logglue.glue_remark IS 'GLUE备注';

DROP TABLE IF EXISTS xxl_job_registry;
CREATE TABLE xxl_job_registry(
                                 id SERIAL NOT NULL,
                                 registry_group VARCHAR(50) NOT NULL,
                                 registry_key VARCHAR(255) NOT NULL,
                                 registry_value VARCHAR(255) NOT NULL,
                                 update_time TIMESTAMP,
                                 PRIMARY KEY (id)
);

COMMENT ON TABLE xxl_job_registry IS '';


CREATE INDEX i_g_k_v ON xxl_job_registry(registry_group,registry_key,registry_value);

DROP TABLE IF EXISTS xxl_job_user;
CREATE TABLE xxl_job_user(
                             id SERIAL NOT NULL,
                             username VARCHAR(50) NOT NULL,
                             password VARCHAR(50) NOT NULL,
                             role int8,
                             permission VARCHAR(255),
                             PRIMARY KEY (id)
);

COMMENT ON TABLE xxl_job_user IS '';
COMMENT ON COLUMN xxl_job_user.username IS '账号';
COMMENT ON COLUMN xxl_job_user.password IS '密码';
COMMENT ON COLUMN xxl_job_user.role IS '角色：0-普通用户、1-管理员';
COMMENT ON COLUMN xxl_job_user.permission IS '权限：执行器ID列表，多个逗号分割';


CREATE UNIQUE INDEX i_username ON xxl_job_user(username);

INSERT INTO xxl_job_user(id, username, password, role, permission) VALUES (1, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL);
INSERT INTO xxl_job_lock ( lock_name) VALUES ( 'schedule_lock');

3、对项目mapper进行修改

3.1 去掉所有字段名的转义符 ` ，直接用空格替换。
3.2 修改 LIMIT #{offset}, #{pagesize} 为 LIMIT #{pagesize} OFFSET #{offset} 。修改LIMIT 0, #{limit}为LIMIT #{limit} OFFSET 0。修改LIMIT 0, #{clearBeforeNum}为LIMIT #{clearBeforeNum} OFFSET 0。LIMIT #{pagesize}保持不变。
3.3 修改DATE_ADD(#{nowTime},INTERVAL - #{timeout} SECOND) 为 (#{nowTime}::timestamp - '${timeout} SECONDS'::interval)。
3.4 修改 WHERE !( 为 WHERE not ( 。