Job scheduling with Postgres: Improve database management with automation

Written by: Redwood Software
5 Min Read
Rmjpostgres

Efficient job scheduling is essential for automating repetitive tasks and ensuring the smooth, uninterrupted operation of a PostgreSQL database. From routine backups to executing stored procedures and SQL scripts, automation reduces manual intervention, minimizes human error and improves data consistency across critical use cases.

You have several options for scheduling tasks in a PostgreSQL environment:

  • pg_cron: An extension that runs inside PostgreSQL
  • pgAgent: A separate service that stores jobs in PostgreSQL and is managed via pgAdmin
  • Linux cron: An OS-level scheduler that runs shell/psql scripts outside PostgreSQL
  • Enterprise schedulers: For cross-platform, event-driven orchestration

Here, we’ll look at how each works and when to use them.

What is a database management system? 

Before exploring scheduling options, it’s important to understand what a database management system (DBMS) is. A DBMS is software that provides an interface for creating, organizing, accessing and modifying data stored in a database. It simplifies data manipulation through structured commands like SQL statements and supports various administrative functions such as access control, performance tuning and job scheduling.

PostgreSQL, or Postgres, is an advanced open source DBMS with a strong reputation for standards compliance, flexibility and high availability. It supports custom data types, JSON/XML, concurrency control, complex joins and full-text search.

With native support for triggers, background workers and extensions like pg_cron, PostgreSQL is a favorite for developers building scalable applications.

A closer look at PostgreSQL

pgAgent is a dedicated job scheduler for PostgreSQL databases. It integrates with pgAdmin and enables users to run automated jobs using SQL commands, stored procedures or shell scripts. It’s a mature tool for managing jobs like backups, index rebuilds and data processing tasks in on-premises or hybrid environments.

The job scheduler runs as a separate service (daemon) outside the PostgreSQL server. It connects to your database to read job definitions and write logs, while execution and monitoring appear in pgAdmin.

The process for scheduling jobs with Postgres and pgAgent involves several steps, including installing pgAgent on the machine where the DBMS is running. After pgAgent is installed, it needs to be connected to the Postgres database using a client like psql or pgAdmin. 

pgAgent is installed separately from PostgreSQL. Install the pgAgent package/binaries for your OS, then run the schema script to create the required tables and functions.

After installing the pgAgent binaries, initialize the pgAgent schema by running the provided SQL file (location can vary by OS/package; examples include /usr/share/pgagent/pgagent.sql or <postgres_share_dir>/pgagent.sql).

\i /path/to/pgagent.sql

Next, start the pgAgent service (daemon) so it can run jobs. For example, on Linux you might run a command like:

pgagent host=<db_host>

dbname=<database_name> user=<db_user>

or use your OS service manager to start pgagent. On Windows, install pgAgent as a service via the installer, then start the service.

After pgAgent has been set up, the most reliable way to create jobs is through pgAdmin:

  1. In pgAdmin, expand your server —> the database —> pgAgent —> Jobs.
  2. Right-click Jobs —> Create —> Job. Give the job a Name and set Enabled = Yes.
  3. Open the Steps tab —> Add a step. Choose Kind = SQL (for database tasks) or Batch/Shell (for OS scripts). Enter your SQL (e.g., SELECT COUNT(*) FROM my_table;).
  4. Open the Schedule tab —> Add a schedule. Set frequency (e.g., every 15 minutes) and time zone.
  5. Save. The pgAgent service will execute the job on schedule and write logs you can view under pgAgent —> Jobs —> [Your Job] —> Steps/Logs.

(Direct inserts into pgAgent tables are version-specific and error-prone; pgAdmin enforces the correct structure for jobs, steps and schedules.)

Users can monitor job execution and view job logs with pgAdmin. pgAgent provides a set of tables to store job-related metadata and logs like pga_jobsteplog and pga_schedule.

How to schedule jobs with Postgres and cron jobs 

Another solution for scheduling and running jobs in Postgres is using the cron job functionality available in Unix operating systems like Linux. Cron is a time-based job scheduler that allows users to automate tasks on a recurring basis. By combining the power of cron with Postgres, jobs can be scheduled that interact with the database.

Cron is a daemon, or a background process that executes non-interactive jobs. A cron file is a simple text file containing commands to run periodically at specific times. The default system cron table, or crontab, config file is /etc/crontab.

Cron jobs are scheduled by creating a shell script or command-line executable function that performs the desired database operations. This can be done using SQL statements, psql commands or other means of interacting with the database within the script. Once the script is created, cron is configured to execute the new job at specified intervals.To schedule jobs with Postgres using Linux cron, create a shell script that runs your SQL via psql, then register it with crontab. For example, your script might call:

psql "dbname=<database_name> user=<db_user>" -c "SELECT my_function();"
Add an entry with crontab -e like
*/5 * * * * /path/to/script.sh

This approach runs outside PostgreSQL and is managed by the OS.

(Note: The pg_cron extension is a different method that runs inside PostgreSQL. Its usage is covered in the pg_cron section.)

Enterprise-grade scheduling with PostgreSQL 

For advanced orchestration needs, especially in hybrid cloud and multi-application environments, RunMyJobs by Redwood offers a fully hosted, cloud-native job scheduler that integrates easily with PostgreSQL.

RunMyJobs supports:

  • Event-driven workflows and API-triggered jobs
  • Cross-platform scheduling for Linux, Windows, and cloud systems
  • Native support for PostgreSQL, MySQL, SQL Server, Oracle, and more
  • SLA tracking with real-time alerts via email, SMS, or webhook
  • Visual job templates and drag-and-drop design tools
  • Seamless automation across SAP, Microsoft and custom applications

With agentless architecture and robust monitoring features, RunMyJobs simplifies enterprise-wide scheduling without the overhead of managing on-premises infrastructure or background workers.

PostgreSQL scheduled job FAQs

Does Postgres have a job scheduler?

PostgreSQL does not include a built-in job scheduler, but it supports scheduling through extensions and third-party tools. A popular option is pg_cron, an extension that runs inside the PostgreSQL server process and schedules SQL using cron-style expressions.

Another widely used tool is pgAgent, which works with pgAdmin and enables automation of tasks like database backups, reporting and shell script execution. These tools help streamline database operations and reduce the need for manual intervention.

For enterprise workloads, platforms like RunMyJobs by Redwood offer a more scalable approach to PostgreSQL job scheduling, with features like real-time monitoring, cross-platform support, API integration and workload orchestration across hybrid environments.

RunMyJobs offers batch scheduling software for hybrid IT processes and reliable results.

How do I create a scheduled job in PostgreSQL?

To create a scheduled job in PostgreSQL, you typically use tools like pg_cron or pgAgent. These job schedulers allow you to define tasks that run automatically at specified intervals.

Using pg_cron, you can schedule SQL queries or stored procedures to run at regular times, making it ideal for automated maintenance or reporting. It requires superuser access and is commonly used in on-premises or cloud-hosted environments like AWS RDS.

With pgAgent, you can schedule jobs using either the pgAdmin GUI or command-line tools like psql. Jobs can include SQL statements or shell scripts, and schedules can be configured with fine-grained control over frequency and time zone.

Enterprise teams may prefer using RunMyJobs by Redwood, which simplifies job creation using drag-and-drop templates, job dependencies, built-in notifications and high-availability orchestration across business systems.

See why enterprise teams use RunMyJobs to manage task scheduling and workload automation.

What is a cron job in PostgreSQL?

A cron job in PostgreSQL refers to a scheduled task created using the pg_cron extension. It behaves similarly to a traditional cron job in Linux but runs directly inside the PostgreSQL database engine.

With pg_cron, users can automate routine SQL operations such as data cleanup, report generation or refreshing materialized views. It offers native integration with PostgreSQL, enabling real-time execution without relying on external scripts or system cron tables.

To schedule a job with pg_cron, use a cron expression along with the SQL you want to run. For example:

SELECT cron.schedule('* * * * *', 'SELECT my_function();');

This creates a job that runs every minute. The function returns a jobid, which you can later use to unschedule it:

SELECT cron.unschedule();

pg_cron is especially useful in environments that need lightweight automation inside the database itself, with support for background processing and minimal configuration. It's a popular choice for developers and database administrators managing automation on Linux, cloud services like RDS or self-hosted PostgreSQL servers.

Note: pg_cron requires superuser privileges, and the extension must be loaded in the server’s shared_preload_libraries setting.

Learn how to easily coordinate SAP and non-SAP jobs for optimal results using RunMyJobs.

What is a job scheduling tool for PostgreSQL?

A job scheduling tool for PostgreSQL is software or an extension that helps users automate database-related tasks. These tools allow scheduling of SQL queries, maintenance routines and shell scripts based on specific times, events, or conditions.

Common options include:

  • pg_cron, a lightweight extension for scheduling tasks natively inside the PostgreSQL server
  • pgAgent, a GUI-friendly scheduler that supports SQL and shell scripts with detailed job logging
  • Linux cron, for external task scheduling using shell commands and psql scripts
  • RunMyJobs by Redwood, a modern workload automation solution for orchestrating PostgreSQL jobs alongside file transfers, SAP tasks and other enterprise workflows

These tools improve operational efficiency, enforce consistency and reduce human error across PostgreSQL environments, whether hosted on premises or in the cloud.

Discover how to orchestrate automation across your entire hybrid cloud environment with Redwood job scheduling software.

Solutions

Company

ISO 27001

© 2025 All Rights Reserved

close