7. Migration from others schedulers
7.1. Migrate jobs from pg_cron to pg_timetable
If you want to quickly export jobs scheduled from pg_cron to pg_timetable, you can use this SQL snippet:
1SELECT timetable.add_job(
2 job_name => COALESCE(jobname, 'job: ' || command),
3 job_schedule => schedule,
4 job_command => command,
5 job_kind => 'SQL',
6 job_live => active
7) FROM cron.job;
The timetable.add_job(), however, has some limitations. First of all, the function will mark the task created as autonomous, specifying scheduler should execute the task out of the chain transaction. It’s not an error, but many autonomous chains may cause some extra connections to be used.
Secondly, database connection parameters are lost for source pg_cron jobs, making all jobs local. To export every information available precisely as possible, use this SQL snippet under the role they were scheduled in pg_cron:
1SET ROLE 'scheduler'; -- set the role used by pg_cron
2
3WITH cron_chain AS (
4 SELECT
5 nextval('timetable.chain_chain_id_seq'::regclass) AS cron_id,
6 jobname,
7 schedule,
8 active,
9 command,
10 CASE WHEN
11 database != current_database()
12 OR nodename != 'localhost'
13 OR username != CURRENT_USER
14 OR nodeport != inet_server_port()
15 THEN
16 format('host=%s port=%s dbname=%s user=%s', nodename, nodeport, database, username)
17 END AS connstr
18 FROM
19 cron.job
20),
21cte_chain AS (
22 INSERT INTO timetable.chain (chain_id, chain_name, run_at, live)
23 SELECT
24 cron_id, COALESCE(jobname, 'cronjob' || cron_id), schedule, active
25 FROM
26 cron_chain
27),
28cte_tasks AS (
29 INSERT INTO timetable.task (chain_id, task_order, kind, command, database_connection)
30 SELECT
31 cron_id, 1, 'SQL', command, connstr
32 FROM
33 cron_chain
34 RETURNING
35 chain_id, task_id
36)
37SELECT * FROM cte_tasks;
7.2. Migrate jobs from pgAgent to pg_timetable
To migrate jobs from pgAgent, please use this script. pgAgent doesn’t have concept of PROGRAM task, thus to emulate BATCH steps, pg_timetable will execute them inside the shell. You may change the shell by editing cte_shell CTE clause.
1CREATE OR REPLACE FUNCTION bool_array_to_cron(bool[], start_with int4 DEFAULT 0) RETURNS TEXT AS
2$$
3WITH u AS (
4 SELECT unnest($1) e, generate_series($2, array_length($1, 1)-1+$2) AS i
5)
6SELECT COALESCE(string_agg(i::text, ','), '*') FROM u WHERE e
7$$
8LANGUAGE sql;
9
10
11WITH
12cte_shell(shell, cmd_param) AS (
13 VALUES ('sh', '-c') -- set the shell you want to use for batch steps, e.g. "pwsh -c", "cmd /C"
14),
15pga_schedule AS (
16 SELECT
17 s.jscjobid,
18 s.jscname,
19 format('%s %s %s %s %s',
20 bool_array_to_cron(s.jscminutes),
21 bool_array_to_cron(s.jschours),
22 bool_array_to_cron(s.jscmonthdays),
23 bool_array_to_cron(s.jscmonths, 1),
24 bool_array_to_cron(s.jscweekdays, 1)) AS schedule
25 FROM
26 pgagent.pga_schedule s
27 WHERE s.jscenabled
28 AND now() < COALESCE(s.jscend, 'infinity'::timestamptz)
29 AND now() > s.jscstart
30),
31pga_chain AS (
32 SELECT
33 nextval('timetable.chain_chain_id_seq'::regclass) AS chain_id,
34 jobid,
35 format('%s @ %s', jobname, jscname) AS jobname,
36 jobhostagent,
37 jobenabled,
38 schedule
39 FROM
40 pgagent.pga_job JOIN pga_schedule ON jobid = jscjobid
41),
42cte_chain AS (
43 INSERT INTO timetable.chain (chain_id, chain_name, client_name, run_at, live)
44 SELECT
45 chain_id, jobname, jobhostagent, schedule, jobenabled
46 FROM
47 pga_chain
48),
49pga_step AS (
50 SELECT
51 c.chain_id,
52 nextval('timetable.task_task_id_seq'::regclass) AS task_id,
53 rank() OVER (ORDER BY jstname) AS jstorder,
54 jstid,
55 jstname,
56 jstenabled,
57 CASE jstkind WHEN 'b' THEN 'PROGRAM' ELSE 'SQL' END AS jstkind,
58 jstcode,
59 COALESCE(
60 NULLIF(jstconnstr, ''),
61 CASE
62 WHEN jstdbname = current_database() THEN NULL
63 WHEN jstdbname > '' THEN 'dbname=' || jstdbname
64 END
65 ) AS jstconnstr,
66 jstonerror != 'f' AS jstignoreerror
67 FROM
68 pga_chain c JOIN pgagent.pga_jobstep js ON c.jobid = js.jstjobid
69),
70cte_tasks AS (
71 INSERT INTO timetable.task(task_id, chain_id, task_name, task_order, kind, command, database_connection)
72 SELECT
73 task_id, chain_id, jstname, jstorder, jstkind::timetable.command_kind,
74 CASE jstkind WHEN 'SQL' THEN jstcode ELSE sh.shell END,
75 jstconnstr
76 FROM
77 pga_step, cte_shell sh
78),
79cte_parameters AS (
80 INSERT INTO timetable.parameter (task_id, order_id, value)
81 SELECT
82 task_id, 1, jsonb_build_array(sh.cmd_param, s.jstcode)
83 FROM
84 pga_step s, cte_shell sh
85 WHERE
86 s.jstkind = 'PROGRAM'
87)
88SELECT * FROM pga_chain;