(6 MIN READ)

Job Scheduling with Postgres: Using pgAgent and Other Tools

Learn about job scheduling with Postgres, or PostgreSQL, using tools like pgAgent, cron jobs, and ActiveBatch. Automate jobs within your team’s data management system.

Written by Editorial Staff. Last Updated:

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

What is the difference between Postgres and MySQL?

Postgres and MySQL are both popular relational database management systems, but they have some significant differences, including feature set, licensing, and more.

PostgreSQL has a strong emphasis on data integrity, offering features for enforcing complex business rules, constraints, and referential integrity. It supports advanced SQL statements and features like window functions, common table expressions, unschedule, and stored procedures. MySQL focuses on speed and performance and offers a sudo subset of SQL features compared to PostgreSQL.

Learn how teams use ActiveBatch for job scheduling on Windows and other platforms.

What is the difference between a trigger and a job in Postgres?

Triggers are associated with specific tables and automatically execute in response to data manipulation events on those tables. Jobs are external scripts or commands scheduled to run at specific intervals.

Triggers are most often used for enforcing data integrity, implementing business rules, and performing actions within the database. Jobs are used for executing tasks external to the database, like data processing, to create extensions, or other system operations.

While jobs are usually managed and scheduled with external tools, triggers are defined as part of the Postgres database scheme and are stored within the database. 

See why teams prefer ActiveBatch for job scheduling over cron jobs for Unix operating systems.

How do you make a job more efficient in Postgres? 

There are a few strategies for increasing the efficiency of jobs in Postgres. These include optimizing SQL queries, using batch job processing, improving management of job transactions, table partitioning, and asynchronous processing.

To ensure the PostgreSQL server is appropriately configured and tuned for the job’s requirements, adjust settings like memory allocation, disk I/O, and parallelism parameters based on the available hardware resources and workload characteristics. Regularly monitor server performance and identify any bottlenecks or areas for optimization. 

Make job scheduling more efficient with ActiveBatch’s batch scheduling feature.