CODEWITHSUNDEEP
c++cpostgresqllibpq
skyeagle
skyeagle

Reputation: 7325

creating jobs and schedules programmatically with pgagent

Is there a way to create and manage jobs/schedules in pgagent programmatically, i.e. WITHOUT using pgAdmin ?

I suspect there may be a way of doing this by writing a postgres client using libpq (in case pgagent does not support this behavior out of the box) - but I am not sure how to go about it - if I need to go down the rought of writing my OWN API for job/schedule CRUD functionality.

So basically I am asking two questions:

Upvotes: 4

Views: 3054

Answers (2)

Neil McGuigan
Neil McGuigan

Reputation: 48246

The below will create a job that runs every minute, with a step that calls some SQL:

do $$
declare
    job_id int;
begin

    /* add a job and get its id: */
    insert into 
        pgagent.pga_job (jobjclid, jobname) 
    values 
        (1 /*1=Routine Maintenance*/, 'my job name') 
    returning 
        jobid 
    into 
        job_id;


    /* add a step to the job: */
    insert into 
        pgagent.pga_jobstep (jstjobid, jstname, jstkind, jstcode, jstdbname) 
    values 
        (
            job_id, 
            'my step name', 
            's',                    /* sql step */
            'select * from thing',  /* the sql to run */
            'mydb'                  /* the name of the database to run the step against */
        );


    /* add a schedule to the job. This one runs every minute: */
    insert into 
        pgagent.pga_schedule (jscjobid, jscname) 
    values 
        (job_id, 'my schedule name');

end $$;

Upvotes: 3

Frank Heikens
Frank Heikens

Reputation: 127056

pgAdmin just creates some SQL statements, that's it. Any application that can connect to the database "postgres" and has the privileges to use the pgAgent schema and tables, can manage the jobs and schedules for pgAgent. It's just SQL.

Upvotes: 2

Related Questions