5. Getting started
A variety of examples can be found in the Samples. If you want to migrate from a different scheduler, you can use scripts from Migration from others schedulers chapter.
5.1. Add simple job
In a real world usually it’s enough to use simple jobs. Under this term we understand:
job is a chain with only one task (step) in it;
it doesn’t use complicated logic, but rather simple command;
it doesn’t require complex transaction handling, since one task is implicitely executed as a single transaction.
For such a group of chains we’ve introduced a special function timetable.add_job()
.
- timetable.add_job(job_name, job_schedule, job_command, ...) RETURNS BIGINT
Creates a simple one-task chain
- Parameters:
job_name (text) – The unique name of the chain and command.
job_schedule (timetable.cron) – Time schedule in сron syntax at Postgres server time zone
job_command (text) – The SQL which will be executed.
job_parameters (jsonb) – Arguments for the chain command. Default:
NULL
.job_kind (timetable.command_kind) – Kind of the command: SQL, PROGRAM or BUILTIN. Default:
SQL
.job_client_name (text) – Specifies which client should execute the chain. Set this to NULL to allow any client. Default:
NULL
.job_max_instances (integer) – The amount of instances that this chain may have running at the same time. Default:
NULL
.job_live (boolean) – Control if the chain may be executed once it reaches its schedule. Default:
TRUE
.job_self_destruct (boolean) – Self destruct the chain after execution. Default:
FALSE
.job_ignore_errors (boolean) – Ignore error during execution. Default:
TRUE
.job_exclusive (boolean) – Execute the chain in the exclusive mode. Default:
FALSE
.
- Returns:
the ID of the created chain
- Return type:
integer
5.2. Examples
Run
public.my_func()
at 00:05 every day in August Postgres server time zone:SELECT timetable.add_job('execute-func', '5 0 * 8 *', 'SELECT public.my_func()');
Run VACUUM at minute 23 past every 2nd hour from 0 through 20 every day Postgres server time zone:
SELECT timetable.add_job('run-vacuum', '23 0-20/2 * * *', 'VACUUM');
Refresh materialized view every 2 hours:
SELECT timetable.add_job('refresh-matview', '@every 2 hours', 'REFRESH MATERIALIZED VIEW public.mat_view');
Clear log table after pg_timetable restart:
SELECT timetable.add_job('clear-log', '@reboot', 'TRUNCATE timetable.log');
Reindex at midnight Postgres server time zone on Sundays with reindexdb utility:
using default database under default user (no command line arguments)
SELECT timetable.add_job('reindex', '0 0 * * 7', 'reindexdb', job_kind := 'PROGRAM');
specifying target database and tables, and be verbose
SELECT timetable.add_job('reindex', '0 0 * * 7', 'reindexdb', '["--table=foo", "--dbname=postgres", "--verbose"]'::jsonb, 'PROGRAM');
passing password using environment variable through
bash
shellSELECT timetable.add_job('reindex', '0 0 * * 7', 'bash', '["-c", "PGPASSWORD=5m3R7K4754p4m reindexdb -U postgres -h 192.168.0.221 -v"]'::jsonb, 'PROGRAM');