pg_task

pg_task : execute any sql command at any specific time at background

Overview

Packages

Source

pig build pkg pg_task;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

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

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

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_task';

This extension does not need CREATE EXTENSION DDL command

Usage

Sources: pg_task upstream README, PGXN pg_task, local metadata.

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.