postgresqlschedulerpgagent

how to schedule a pgagent job through scripts/commandLine

I want to run jobs through PgAgent.

I am able to do that by creating a job in PgAgentJob through PgAdmin UI, as described here https://www.pgadmin.org/docs/pgadmin4/dev/pgagent_jobs.html.

But I want to use a sql script that can create a PgAgent job as we do in Oracle. Please suggest how I can achieve this.

Solution

  • To create a job in pgAgent use something like the following INSERT STATEMENTS (for a Routine Maintenance job) :

    INSERT INTO pgagent.pga_job (jobid, jobjclid, jobname, jobdesc, jobenabled, jobhostagent)
SELECT jcl.jclid, 'MyJob', '', true, ''
FROM pgagent.pga_jobclass jcl WHERE jclname='Routine Maintenance';

    To add a step to this job, which executes a SQL command ('delete from test where user_name=''test'';), use the following command:

    INSERT INTO pgagent.pga_jobstep (jstjobid, jstname, jstdesc, jstenabled, jstkind, jstonerror, jstcode, jstdbname, jstconnstr)
 SELECT (SELECT jobid 
         FROM pgagent.pga_job
         WHERE jobname = 'MyJob'), 'MyStep', '', true, 's', 'f', 'delete from test where user_name=''test'';', 'postgres', '';

    To create a schedule for this job (every day at 08:45), use the following command:

    INSERT INTO pgagent.pga_schedule (jscjobid, jscname, jscdesc, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths, jscenabled, jscstart, jscend)
VALUES((SELECT jobid 
FROM pgagent.pga_job
WHERE jobname = 'MyJob'), 'MySchedule', '', '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,t,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', 
'{f,f,f,f,f,f,f,f,t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', '{t,t,t,t,t,t,t}', '{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}', 
'{t,t,t,t,t,t,t,t,t,t,t,t}', true, '2018-07-16 00:00:00', NULL);

    Here a graphical representation of this schedule:

    enter image description here

    Here you can see a complete summary of these commands inside an anonymous block (generated by pgAdmin):

    DO $$
DECLARE
    jid integer;
    scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
    jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
    1::integer, 'MyJob'::text, ''::text, ''::text, true
) RETURNING jobid INTO jid;

-- Steps
-- Inserting a step (jobid: NULL)
INSERT INTO pgagent.pga_jobstep (
    jstjobid, jstname, jstenabled, jstkind,
    jstconnstr, jstdbname, jstonerror,
    jstcode, jstdesc
) VALUES (
    jid, 'MyStep'::text, true, 's'::character(1),
    ''::text, 'postgres'::name, 'f'::character(1),
    'delete from test where user_name=''test'';'::text, ''::text
) ;

-- Schedules
-- Inserting a schedule
INSERT INTO pgagent.pga_schedule(
    jscjobid, jscname, jscdesc, jscenabled,
    jscstart,     jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
) VALUES (
    jid, 'MySchedule'::text, ''::text, true,
    '2018-07-16 00:00:00+02'::timestamp with time zone, 
    -- Minutes
    ARRAY[false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, true, false, false, false, false, false, false, false, false, false, false, false, false, false, false]::boolean[],
    -- Hours
    ARRAY[false, false, false, false, false, false, false, false, true, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false]::boolean[],
    -- Week days
    ARRAY[true, true, true, true, true, true, true]::boolean[],
    -- Month days
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
    -- Months
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true]::boolean[]
) RETURNING jscid INTO scid;
END
$$;