Job Scheduling with Postgres: Using pgAgent and Other Tools

Efficient job scheduling plays a crucial role in automating tasks and streamlining processes. Teams that fail to implement automated scheduling will waste countless hours and effort on tedious jobs that could have been spent on strategic tasks. 

Good news is there are countless tools for scheduling jobs, including solutions specifically designed for use with popular technologies, databases, and operating systems. For teams using Postgres, or PostgreSQL as their application’s backend database, pgAgent and ActiveBatch are among the available solutions for creating and scheduling new jobs.

What is PostgreSQL? 

PostgreSQL, also known as Postgres, is an open source object-relational database management system (DBMS). Postgres is a robust, reliable, and scalable solution for storing and managing large amounts of structured data.

PostgreSQL can be used with a wide range of data types and offers advanced features like transactions, concurrency control, and data integrity enforcement. It also supports multiple programming languages.

While PostgreSQL lacks built-in job scheduling functionality, there are ways to schedule jobs using Postgres in combination with other tools.

One option for scheduling jobs with Postgres is an operating system job scheduler, like cron jobs on Unix-like systems including Linux, or Task Scheduler on Windows. Another popular tool for scheduling and running jobs with PostgreSQL is pgAgent. 

Using pgAgent to Schedule Jobs with Postgres

pgAgent is a job scheduling agent designed for PostgreSQL. This open source tool enables job scheduling within the PostgreSQL database server.  With pgAgent, jobs can be defined as SQL scripts or external shell scripts, and new jobs can be scheduled to run at specified times or intervals.

To use pgAgent for scheduling jobs with Postgres, take the following steps: 

1. Install pgAgent.
2. Create a pgAgent Postgres database where job information can be stored and configured. To create the database, use the following command: createdb in the CLI. 
3. Set up pgAgent scheme and tables by running the pgagent.sql script provided with the pgAgent installation to create the schema and tables within the pgAgent database.  Execute the script using psql in the command line, or use a GUI tool like pgAdmin. 
4. Configure pgAgent. Open the pgagent.conf config file located in the pgAgent install directory. Specify the connection details for the created pgAgent Postgres database. Ensure file permissions are set to restrict access to the configuration. 
5. Define pgAgent jobs using pgAdmin. New jobs can be defined as SQL scripts or external scripts to run at specified times or intervals. 
6. Launch the pgAgent daemon or service to run as a background worker to execute scheduled jobs.

Other commands that may be included in the script include DBname or database_name, JobID, and JobName. 

Schedule a Cron Job with Postgres

To schedule a cron job using Postgres, create a script to perform the desired database operations using SQL queries or other PostgreSQL client tools (psql). Then schedule the execution of the script using cron.

Here’s an example of a shell script containing commands to execute. The following command will run a simple SQL query to update a table in the Postgres database. 

#!/bin/bash

# Connect to PostgreSQL and execute the SQL query

psql -h localhost -d X_database -U X_username -c "UPDATE Xr_table SET column = value WHERE condition;"

Replace localhost, X_database (cron.database), X_username, X_table, column, value, and condition with the connection details, table name, column name, values, and condition for the update query.

Ensure that the script has the necessary compute or execute permissions so that it can be executed by the cron job. Use the chmod command to set permissions, and the crontab command to edit the cron table and define the job schedule with a select cron.schedule command. The cron table is specific to each user (usr) and contains cron job entries. Each line in the crontab file represents a separate job and specifies the schedule and command to be executed.

In the crontab file, use the appropriate syntax and pg_cron expression to specify the schedule for the job. This typically includes the minute, hour, day of the month, month, and day of week for the job to run. After defining the cron job entries, save and exit the crontab file. The cron daemon will automatically pick up the changes and schedule the jobs accordingly.

ActiveBatch Job Scheduler for Postgres

ActiveBatch offers cross-platform job scheduling software to automate and orchestrate complex processes. Teams can build end-to-end workflows across their entire IT environment with a comprehensive library of integrations and extensions. 

Seamless integrations with data warehousing tools like Microsoft SQL Server, Oracle Databases, IBM, Red Hat, and open source data management tools makes ActiveBatch the perfect solution for teams using Postgres for job scheduling. Infrastructure automation includes support for on-prem data centers and cloud vendors like Amazon EC2, VMWare, GitHub, and more. With ActiveBatch, users can monitor scheduled jobs in real time and initiate notifications to keep IT informed when things need their attention. Teams can optimize their security framework and control access to sensitive information and environments with ActiveBatch’s authentication features.

Frequently Asked Questions