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;