pgAgent is a tool used for scheduling jobs for PostgreSQL databases. Each job consists of steps and schedules.
Creating a pgAgent job
To create or manage a job, use the pgAdmin object explorer to browse to the server on which the pgAgent database objects were created. The object explorer will display a pgAgent Jobs node, under which currently defined jobs are displayed. To add a new job, right click on the pgAgent Jobs node, and select Create pgAgent Job… from the context menu.
Once you click the option the pgAgent dialog opens; use the tabs on pgAgent job dialog to define steps and schedules.
Inside the general tab provides basic information like job name, class etc.
Defining steps
Use the steps tab to define and manage the steps that the job will perform. Click on the add icon (+) to add a new step, then click on the edit icon (located on the left side) to open the step definition dialog.
- Fill in all the details and click on the code tab. If you have selected kind as SQL, provide one or more SQL queries that need to be executed. If it is a batch step then provide a batch script that will be executed during the step. If you have selected batch then you can not provide connection type and database.
- Use the On error drop-down to specify the behaviour of pgAgent if it encounters an error while executing the step. Select from:
- Fail - Stop the job if you encounter an error while processing this step.
- Success - Mark the step as completing successfully, and continue.
- Ignore - Ignore the error, and continue.
In the code tab provide the query/batch script as per your requirement (in this example we are executing a query which copies data from a table to a CSV file).
Defining schedule
Click on the schedule tab to define the schedule for running the job.
- Provide a name for the schedule.
- Provide start and end time for the schedule.
Select the Repeat tab to define days on which schedule will execute.
Use the fields on the Repeat tab to specify the details about the schedule in a cron-style format. The job will execute on each date or time element selected on the Repeat tab.
If there are days or times you don’t want to execute a job, you can create a more granular time schedule, or you can set this by navigating to the Exceptions tab.
You can add more schedules by clicking on the add (+) icon for doing more complex things. e.g. one schedule might run on every Monday, whilst a second schedule might run on the last day of every month, with the combination of the schedules being the result.
When you’ve finished defining the schedule, you can use the SQL tab to review the code that will create or modify your job.
Click the Save button to save the job definition.
After saving a job, the job will be listed under the pgAgent Jobs node of the pgAdmin object explorer of the server on which it was defined. The Properties tab will show a high level detail about the job.
Conclusion
One can use pgAgent to schedule a job. It is capable of running multi step batch or shell scripts and SQL tasks on a complex schedule.