pgagent

Overview

Version

Install

You can install pgagent directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pgagent;          # Install for current active PG version

pig ext install -y pgagent -v 18  # PG 18
pig ext install -y pgagent -v 17  # PG 17
pig ext install -y pgagent -v 16  # PG 16
pig ext install -y pgagent -v 15  # PG 15
pig ext install -y pgagent -v 14  # PG 14

dnf install -y pgagent_18       # PG 18
dnf install -y pgagent_17       # PG 17
dnf install -y pgagent_16       # PG 16
dnf install -y pgagent_15       # PG 15
dnf install -y pgagent_14       # PG 14

apt install -y pgagent   # PG 18
apt install -y pgagent   # PG 17
apt install -y pgagent   # PG 16
apt install -y pgagent   # PG 15
apt install -y pgagent   # PG 14

Create Extension:

CREATE EXTENSION pgagent;

Usage

pgagent: A PostgreSQL job scheduler

pgAgent is a job scheduling agent for PostgreSQL, capable of running multi-step batch/shell scripts and SQL tasks on complex schedules. It runs as a daemon and stores job definitions in the database.

Core Concepts

• Job: A named schedulable unit containing one or more steps and schedules
• Step: An individual action (SQL script or OS batch/shell command) within a job
• Schedule: Defines when a job runs, with cron-like flexibility

Job Management via SQL

pgAgent stores its configuration in the pgagent schema. Jobs can be managed through pgAdmin or directly via SQL.

-- View all jobs
SELECT jobid, jobname, jobenabled, jobdesc
FROM pgagent.pga_job;

-- View job steps
SELECT jstid, jstjobid, jstname, jstenabled, jstkind, jstcode
FROM pgagent.pga_jobstep;

-- View job schedules
SELECT jscid, jscjobid, jscname, jscenabled,
       jscstart, jscend, jscminutes, jschours,
       jscweekdays, jscmonthdays, jscmonths
FROM pgagent.pga_schedule;

-- View job execution log
SELECT * FROM pgagent.pga_joblog
WHERE jlgjobid = 1 ORDER BY jlgstart DESC;

-- View step execution log
SELECT * FROM pgagent.pga_jobsteplog
WHERE jsljlgid IN (SELECT jlgid FROM pgagent.pga_joblog WHERE jlgjobid = 1)
ORDER BY jslstart DESC;

Step Types

Schedule Fields

Security

The pgAgent daemon connects to the database using a stored connection string. Only database superusers or users granted appropriate privileges on the pgagent schema tables should manage jobs.