pg_jobmon

pig build pkg pg_jobmon         # build DEB packages

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

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;