pg_task

Overview

breaks on many systems

Version

Build

You can build the DEB packages for pg_task using pig build:

pig build pkg pg_task         # build DEB packages

Install

You can install pg_task 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:

• Install
• pig
• dnf
• apt

pig install pg_task;          # Install for current active PG version

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

dnf install -y pg_task_18       # PG 18
dnf install -y pg_task_17       # PG 17
dnf install -y pg_task_16       # PG 16
dnf install -y pg_task_15       # PG 15
dnf install -y pg_task_14       # PG 14

apt install -y postgresql-18-pg-task   # PG 18
apt install -y postgresql-17-pg-task   # PG 17
apt install -y postgresql-16-pg-task   # PG 16
apt install -y postgresql-15-pg-task   # PG 15
apt install -y postgresql-14-pg-task   # PG 14

Preload:

shared_preload_libraries = 'pg_task';

Usage

pg_task: PostgreSQL job scheduler

pg_task allows executing any SQL command at any specific time in the background asynchronously. It works with PostgreSQL, Greenplum and Greengage.

First, add to postgresql.conf:

shared_preload_libraries = 'pg_task'

Then schedule tasks by inserting into the task table:

-- Run SQL immediately
INSERT INTO task (input) VALUES ('SELECT now()');

-- Run SQL after 5 minutes
INSERT INTO task (plan, input) VALUES (now() + '5 min'::INTERVAL, 'SELECT now()');

-- Run SQL at a specific time
INSERT INTO task (plan, input) VALUES ('2029-07-01 12:51:00', 'SELECT now()');

-- Repeat SQL every 5 minutes
INSERT INTO task (repeat, input) VALUES ('5 min', 'SELECT now()');

-- Exceptions are caught and written to the error column
INSERT INTO task (input) VALUES ('SELECT 1/0');

-- Limit concurrent tasks in a group
INSERT INTO task (group, max, input) VALUES ('group', 1, 'SELECT now()');

-- Run SQL on a remote database
INSERT INTO task (input, remote) VALUES ('SELECT now()', 'user=user host=host');

Task Table Columns

You may add any needed columns and/or make partitions on this table.

Configuration (GUCs)

Key settings:

Multi-Database Configuration

To run tasks on multiple databases, configure via JSON:

pg_task.json = '[{"data":"database1"},{"data":"database2","user":"username2"},{"data":"database3","schema":"schema3"}]'

If the specified database, user, schema or table does not exist, pg_task will create them.