pg_jobmon

pig build pkg pg_jobmon;		# build deb

pig repo add pgsql -u   # add both repo and update cache

pig install pg_jobmon;		# install via package name, for the active PG version

pig install pg_jobmon -v 18;   # install for PG 18
pig install pg_jobmon -v 17;   # install for PG 17
pig install pg_jobmon -v 16;   # install for PG 16
pig install pg_jobmon -v 15;   # install for PG 15
pig install pg_jobmon -v 14;   # install for PG 14

CREATE EXTENSION pg_jobmon CASCADE; -- requires dblink

CREATE SCHEMA jobmon;
CREATE EXTENSION pg_jobmon SCHEMA jobmon;

INSERT INTO jobmon.dblink_mapping_jobmon (username, pwd) VALUES ('rolename', 'rolepassword');

INSERT INTO jobmon.dblink_mapping_jobmon (host, username, pwd, port)
VALUES ('localhost', 'rolename', 'rolepassword', '5999');

-- 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);

-- 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;