pg_task

Overview

breaks on many systems

Version

Build

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

pig build pkg pg_task         # build RPM / DEB packages

Install

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

Sources: pg_task upstream README, PGXN pg_task.

pg_task is a background-worker scheduler for running SQL asynchronously at a planned time. Upstream documents PostgreSQL, Greenplum, and Greengage support.

Enable the worker at server start, then create the extension in the database that will own the task table:

shared_preload_libraries = 'pg_task'

CREATE EXTENSION pg_task;

Schedule Tasks

Schedule work by inserting rows into the configured task table, which defaults to public.task in database postgres unless changed with GUCs.

-- 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.
-- max = 1 means one task at a time for this group.
INSERT INTO task ("group", max, input) VALUES ('billing', 1, 'SELECT refresh_billing_cache()');

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

Task Table

The task table is meant to be user-visible. Upstream notes that users may add columns or partition it.

Key columns:

Configuration

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.

The local metadata marks this package as headless, so it needs shared_preload_libraries rather than a user-facing SQL-only install path. Validate background scheduling behavior on the target PostgreSQL version before relying on it for critical jobs.