pg_jobmon
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_jobmon | 1.4.1 | SEC | PostgreSQL | SQL |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 7160 | pg_jobmon | No | Yes | No | Yes | No | No | - |
| Related | dblink pg_cron pg_task pgagent pg_background logerrors bgw_replstatus pgauditlogtofile pg_auth_mon |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | MIXED | 1.4.1 | 1817161514 | pg_jobmon | dblink |
| RPM | PGDG | 1.4.1 | 1817161514 | pg_jobmon_$v | - |
| DEB | PIGSTY | 1.4.1 | 1817161514 | postgresql-$v-pg-jobmon | - |
Build
You can build the DEB packages for pg_jobmon using pig build:
pig build pkg pg_jobmon # build DEB packages
Install
You can install pg_jobmon directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:
pig repo add pgsql -u # Add repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_jobmon; # Install for current active PG version
pig ext install -y pg_jobmon -v 18 # PG 18
pig ext install -y pg_jobmon -v 17 # PG 17
pig ext install -y pg_jobmon -v 16 # PG 16
pig ext install -y pg_jobmon -v 15 # PG 15
pig ext install -y pg_jobmon -v 14 # PG 14
dnf install -y pg_jobmon_18 # PG 18
dnf install -y pg_jobmon_17 # PG 17
dnf install -y pg_jobmon_16 # PG 16
dnf install -y pg_jobmon_15 # PG 15
dnf install -y pg_jobmon_14 # PG 14
apt install -y postgresql-18-pg-jobmon # PG 18
apt install -y postgresql-17-pg-jobmon # PG 17
apt install -y postgresql-16-pg-jobmon # PG 16
apt install -y postgresql-15-pg-jobmon # PG 15
apt install -y postgresql-14-pg-jobmon # PG 14
Create Extension:
CREATE EXTENSION pg_jobmon CASCADE; -- requires: dblink
Usage
pg_jobmon: Autonomous job logging and monitoring for PostgreSQL
pg_jobmon provides autonomous (non-transactional) logging for PostgreSQL transactions and functions. If a function fails, all log information written up to that point is preserved rather than rolled back.
CREATE SCHEMA jobmon;
CREATE EXTENSION pg_jobmon SCHEMA jobmon;
Setup
The extension uses dblink to connect back to the same database (for non-transactional logging). Add credentials:
INSERT INTO jobmon.dblink_mapping_jobmon (username, pwd) VALUES ('rolename', 'rolepassword');
For non-standard ports:
INSERT INTO jobmon.dblink_mapping_jobmon (host, username, pwd, port)
VALUES ('localhost', 'rolename', 'rolepassword', '5999');
Core Logging Functions
-- Start a new job
SELECT jobmon.add_job('My Job Name');
-- Add a step to the job
SELECT jobmon.add_step(job_id, 'Step description');
-- Update step status
SELECT jobmon.update_step(step_id, 'OK', 'Step completed successfully');
SELECT jobmon.update_step(step_id, 'WARNING', 'Something unexpected');
-- Close the job
SELECT jobmon.close_job(job_id);
-- Or fail the job
SELECT jobmon.fail_job(job_id);
Monitoring Functions
-- Check for failed jobs
SELECT * FROM jobmon.check_job_status();
-- View job history
SELECT * FROM jobmon.job_log ORDER BY start_time DESC;
-- View step details
SELECT * FROM jobmon.job_detail WHERE job_id = 123;
The autonomous logging ensures that even if the parent transaction rolls back, the job log entries are preserved for troubleshooting.
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.