lt_cron is a simple cron-based job scheduler for LightDB that runs inside the database as an extension. It allows you to schedule LightDB commands directly from the database:
-- Vacuum on Saturday at 3:30am (East eight time zone)
SELECT cron.schedule('30 3 * * 6', 'VACUUM');
schedule
----------
42
-- Vacuum every day at 10:00am (East eight time zone)
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');
schedule
----------
43
-- Change to vacuum at 3:00am (East eight time zone)
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
schedule
----------
43
-- Stop scheduling jobs
SELECT cron.unschedule('nightly-vacuum' );
unschedule
------------
t
(1 row)
SELECT cron.unschedule(42);
unschedule
------------
t
lt_cron can support second-level precision:
-- Vacuum every day at 10:00:30am (East eight time zone)
SELECT cron.schedule('30 0 10 * * *', 'VACUUM');
schedule
----------
45
-- Vacuum every second
SELECT cron.schedule('dayly-vacuum', '* * * * * *', 'VACUUM');
schedule
----------
46
-- Change to Vacuum every 10 seconds
SELECT cron.schedule('dayly-vacuum', '*/10 * * * * *', 'VACUUM');
schedule
----------
46
lt_cron can support four task modes, include one-time tasks, asap takes, next interval tasks and fixed interval tasks. You can pass in the task mode in the fourth parameter and there are four parameters to choose from (If you want to configure the task mode, the first parameter task name must be passed in):
'single' represents a one-time task, this means that when the task is executed for the first time,
the task will not be executed again.
-- Change to Vacuum only once immediately
SELECT cron.schedule('dayly-vacuum', '* * * * * *', 'VACUUM', 'single');
schedule
----------
46
-- Change to Vacuum every 30 seconds
SELECT cron.schedule('dayly-vacuum', '*/30 * * * * *', 'VACUUM', 'next');
schedule
----------
46
-- Change to Vacuum only once at the next 10:00:30am (East eight time zone)
SELECT cron.schedule('dayly-vacuum', '30 0 10 * * *', 'VACUUM', 'single');
schedule
----------
46
-- Change to Vacuum every day at 10:00:30am (East eight time zone)
SELECT cron.schedule('dayly-vacuum', '30 0 10 * * *', 'VACUUM', 'next');
schedule
----------
46
'asap' represents a asap scheduled task, for the same task it runs at most one instance of a job at a time.
If a second run is supposed to start before the first one finishes, then the second run is queued and
started as soon as the first run completes.
-- Change to Vacuum every 30 seconds
SELECT cron.schedule('dayly-vacuum', '*/30 * * * * *', 'VACUUM', 'asap');
schedule
----------
46
-- Change to Vacuum every day at 10:00:30am (East eight time zone)
SELECT cron.schedule('dayly-vacuum', '30 0 10 * * *', 'VACUUM', 'asap');
schedule
----------
46
'next' represents a next interval scheduled task, for the same task it runs at most one instance of a job at a time.
If a second run is supposed to start before the first one finishes, then the second run is queued and
started at the time point of the next timing cycle.
Compatible with the previous version, the mode parameter input 'timing' is the same as 'next'.
-- Change to Vacuum every 30 seconds
SELECT cron.schedule('dayly-vacuum', '*/30 * * * * *', 'VACUUM', 'next');
schedule
----------
46
-- Change to Vacuum every day at 10:00:30am (East eight time zone)
SELECT cron.schedule('dayly-vacuum', '30 0 10 * * *', 'VACUUM', 'next');
schedule
----------
46
'fixed' represents a fixed interval scheduled task, for the same task it runs at most four instances of a job at a time by default.
If a second run is supposed to start before the first one finishes, then the second run will not wait, and will start at the time point of the timing cycle,
then will be executed in parallel with the first unfinished task.
You can modify the maximum number of concurrent executions for the same task when it expires by configuring the 'cron.max_connections_per_task'
GUC parameters in the lightdb.conf and restarting the database to take effect. The maximum upper limit is 16.
-- Change to Vacuum every 30 seconds
SELECT cron.schedule('dayly-vacuum', '*/30 * * * * *', 'VACUUM', 'fixed');
schedule
----------
46
-- Change to Vacuum every day at 10:00:30am (East eight time zone)
SELECT cron.schedule('dayly-vacuum', '30 0 10 * * *', 'VACUUM', 'fixed');
schedule
----------
46
lt_cron can support time zone configuration. You can pass the timezone value in the fifth parameter. If you want to configure the time zone, the first parameter task name and the fourth parameter task mode must be passed in. If no time zone is configured, the default is East eight time zone:
-- Change to Vacuum every day at 10:00am (GMT)
SELECT cron.schedule('dayly-vacuum', '0 10 * * *', 'VACUUM', 'next', '0');
schedule
----------
46
-- Change to vacuum every day at 10:00am (West ten time zone)
SELECT cron.schedule('dayly-vacuum', '0 10 * * *', 'VACUUM', 'next', '-10');
schedule
----------
46
-- Change to vacuum only once at the next 10:00am (East six time zone)
SELECT cron.schedule('dayly-vacuum', '0 10 * * *', 'VACUUM', 'single', '6');
schedule
----------
46
lt_cron can support the execution of linux os commands. You can pass the command type value in the sixth parameter. pass parameter 'sql' represents that the second parameter is the sql command, and pass parameter 'linux' represents that the second parameter is the sql command. If you want to configure the command type, the first parameter task name, the fourth parameter task mode and the fifth parameter time zone must be passed in. If no command type is configured, the default is sql command. Note that you must be a super user to execute linux commands:
-- Change to Vacuum every day at 10:00am (East eight time zone)
SELECT cron.schedule('dayly-vacuum', '0 10 * * *', 'VACUUM', 'next', '8', 'sql');
schedule
----------
46
-- delete log file every day at 23:59 (East eight time zone)
SELECT cron.schedule('dayly-touch', '59 23 * * *', 'rm -rf $LTDATA/log/*', 'next', '8', 'linux');
schedule
----------
46
lt_cron can run multiple jobs in parallel, and by default it uses next interval mode, i.e. it runs at most one instance of a job at a time. If a second run is supposed to start before the first one finishes, then the second run is queued and started at the time point of the next timing cycle.
lt_cron supports a default timeout of 3 minutes for scheduled tasks for SQL commands, which is valid for all types of tasks.
If the task execution times out, it will log the error in cron.job_run_details and return,
waiting for the next execution. You can modify the timing task timeout time by setting the guc parameter
cron.task_running_timeout in lightdb.conf and restarting the database to take effect.
The maximum value is 60 minutes; if it is set to 0, it means there is no timeout limit.
Note that there is no timeout mechanism for linux command timing tasks.
The schedule uses the standard cron syntax, in which * means "run every time period", and a specific number means "but only at this time":
┌───────────── min (0 - 59)
│ ┌────────────── hour (0 - 23)
│ │ ┌─────────────── day of month (1 - 31)
│ │ │ ┌──────────────── month (1 - 12)
│ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
│ │ │ │ │ Saturday, or use names; 7 is also Sunday)
│ │ │ │ │
│ │ │ │ │
* * * * *
An easy way to create a cron schedule is: crontab.guru.
It has been enhanced on the basis of standard cron syntax to supports second-level tasks:
┌─────────────second (0 - 59)
│ ┌───────────── minute (0 - 59)
│ │ ┌────────────── hour (0 - 23)
│ │ │ ┌─────────────── day of month (1 - 31)
│ │ │ │ ┌──────────────── month (1 - 12)
│ │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
│ │ │ │ │ │ Saturday, or use names; 7 is also Sunday)
│ │ │ │ │ │
│ │ │ │ │ │
* * * * * *
For security, jobs are executed in the database in which the cron.schedule function is called
with the same permissions as the current user. In addition, users are only able to see their
own jobs in the cron.job table and cron.lt_job view.
-- View active jobs
select * from cron.job;
You can view the status of running and recently completed job runs in the cron.job_run_details:
select * from cron.job_run_details order by start_time desc limit 5;
┌───────┬───────┬─────────┬──────────┬──────────┬───────────────────┬───────────┬──────────────────┬───────────────────────────────┬───────────────────────────────┐
│ jobid │ runid │ job_pid │ database │ username │ command │ status │ return_message │ start_time │ end_time │
├───────┼───────┼─────────┼──────────┼──────────┼───────────────────┼───────────┼──────────────────┼───────────────────────────────┼───────────────────────────────┤
│ 10 │ 4328 │ 2610 │ postgres │ marco │ select process() │ succeeded │ SELECT 1 │ 2023-02-07 09:30:00.098164+01 │ 2023-02-07 09:30:00.130729+01 │
│ 10 │ 4327 │ 2609 │ postgres │ marco │ select process() │ succeeded │ SELECT 1 │ 2023-02-07 09:29:00.015168+01 │ 2023-02-07 09:29:00.832308+01 │
│ 10 │ 4321 │ 2603 │ postgres │ marco │ select process() │ succeeded │ SELECT 1 │ 2023-02-07 09:28:00.011965+01 │ 2023-02-07 09:28:01.420901+01 │
│ 10 │ 4320 │ 2602 │ postgres │ marco │ select process() │ failed │ server restarted │ 2023-02-07 09:27:00.011833+01 │ 2023-02-07 09:27:00.72121+01 │
│ 9 │ 4320 │ 2602 │ postgres │ marco │ select do_stuff() │ failed │ job canceled │ 2023-02-07 09:26:00.011833+01 │ 2023-02-07 09:26:00.22121+01 │
└───────┴───────┴─────────┴──────────┴──────────┴───────────────────┴───────────┴──────────────────┴───────────────────────────────┴───────────────────────────────┘
(10 rows)
In cron.job_run_details the maximum number of scheduled task records that are closest to the current time is 100000, and any excess early records will be automatically deleted.
If you do not want to use cron.job_run_details at all, then you can add cron.log_run = off to lightdb.conf.