"Boosting Productivity with pgAgent: A Comprehensive Overview of PostgreSQL Job Scheduling"

pgAgent is like your database's personal assistant that helps automate and manage tasks.

Ex: Just like you might set a reminder on your phone to wake up early, pgAgent helps your PostgreSQL database perform tasks on a schedule without you needing to manually intervene

Creating a Plan:

With pgAgent, you can create plans or schedules for various tasks. These tasks can include anything from regular data backups to executing specific SQL queries. Each plan outlines what needs to be done and when.

Task Management

Within each plan, you can break down tasks into smaller steps. For instance, if you want to automate a daily data backup, you can create steps like "Connect to Database," "Create Backup," and "Save Backup File." pgAgent ensures that these steps are executed in the right order and at the right time.

Reliable Execution

pgAgent ensures that tasks are executed reliably and consistently. It handles the timing and sequencing of tasks, freeing you from the need to manually trigger each action.

"Let's put this into practice!"

Follow the steps:-

create table Student(id numeric, time timestamp default Now());        

Create one Stored Procedure Query

CREATE OR REPLACE PROCEDURE student()
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
id_ integer;
BEGIN
	begin
		select coalesce(max(id),0)+2 into id_ from student;
	exception when others then?
		id_ := 0;
	end;
Insert into student(id) values(id_);
END;
$BODY$;        

Call the Procedure query:-"When we execute the stored procedure, it will create one record in the 'students' table."

"Scheduling the JOB: This query will be automatically generated by pgAdmin."

"The JOB is scheduled to run every 1 minute. You can observe the boolean values for minutes."

DO $
DECLARE
? ? jid integer;
? ? scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
? ? jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
? ? 1::integer, 'job1'::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, 'step'::text, true, 's'::character(1),
? ? ''::text, 'postgres'::name, 'f'::character(1),
? ? 'call public.testing_job_proc();'::text, ''::text
) ;
-- Schedules
-- Inserting a schedule
INSERT INTO pgagent.pga_schedule(
? ? jscjobid, jscname, jscdesc, jscenabled,
? ? jscstart,? ? ?jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
) VALUES (
? ? jid, 'step2'::text, ''::text, false,
? ? '2023-08-21 19:24:00+05:30'::timestamp with time zone,?
? ? -- Minutes
? ? ARRAY[true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false,true,false]::boolean[],
? ? -- Hours
? ? 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]::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
$$;$        

"Note: We will discuss Stored Procedures in the next article."