pg_durable

Overview

Requires shared_preload_libraries=pg_durable and a superuser worker role. Upstream README targets PG17; DEB validated PG14-18 on u24a arm64, RPM spec targets PG14-18; pgrx patched to 0.18.1.

Version

Build

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

pig build pkg pg_durable         # build RPM / DEB packages

Install

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

• Install
• pig
• dnf
• apt

pig install pg_durable;          # Install for current active PG version

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

dnf install -y pg_durable_18       # PG 18
dnf install -y pg_durable_17       # PG 17
dnf install -y pg_durable_16       # PG 16
dnf install -y pg_durable_15       # PG 15
dnf install -y pg_durable_14       # PG 14

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

Preload:

shared_preload_libraries = 'pg_durable';

Create Extension:

CREATE EXTENSION pg_durable;

Source: pg_durable v0.2.2 README, User Guide, control file, GUC definitions.

Usage

pg_durable runs durable, fault-tolerant SQL workflows inside PostgreSQL. A workflow is built from SQL strings, functions, and DSL operators, then submitted with df.start(). State is persisted in PostgreSQL so completed steps are not re-executed after crashes or restarts.

pg_durable must be loaded through shared_preload_libraries, followed by a PostgreSQL restart. Its background worker connects to the database named by pg_durable.database and runs under pg_durable.worker_role; upstream defaults are postgres and azuresu, and the worker role must be a superuser.

Enable and Grant Access

CREATE EXTENSION pg_durable;

SELECT df.grant_usage('app_role');

CREATE EXTENSION does not grant usage to PUBLIC. Use df.grant_usage() for application roles, and rerun it after extension upgrades so newly added functions are covered. The background worker initializes asynchronously after extension creation; retry if df.* calls report that the worker is not initialized yet.

Start and Monitor Workflows

SELECT df.start('SELECT ''Hello, durable world!''', 'hello-job');

SELECT *
FROM df.list_instances();

SELECT df.status('a1b2c3d4');
SELECT df.result('a1b2c3d4');
SELECT df.cancel('a1b2c3d4', 'No longer needed');

df.start() returns an instance ID. Use that ID with df.status(), df.result(), df.cancel(), df.signal(), and df.explain().

Compose SQL Steps

-- Run one step, name its result, then substitute it in the next step.
SELECT df.start(
  'SELECT 100 AS amount' |=> 'total'
  ~> 'SELECT $total * 2 AS doubled',
  'double-total'
);

-- Branch on a SQL condition.
SELECT df.start(
  'SELECT count(*) > 10 FROM orders'
    ?> 'SELECT ''high volume'''
    !> 'SELECT ''low volume''',
  'order-volume'
);

-- Run in parallel and wait for both branches.
SELECT df.start(
  'SELECT refresh_accounts()' & 'SELECT refresh_orders()',
  'parallel-refresh'
);

Core operators are ~> for sequence, |=> for naming a result, & for join, | for race, ?> and !> for conditional branches, and @> for loops.

Timers, Schedules, and Signals

SELECT df.start(
  @> (
    df.wait_for_schedule('0 * * * *')
    ~> 'SELECT run_hourly_rollup()'
  ),
  'hourly-rollup'
);

SELECT df.start(
  'SELECT create_invoice()' |=> 'invoice'
  ~> df.wait_for_signal('approval', 86400)
  ~> 'SELECT finalize_invoice($invoice.id)',
  'invoice-approval'
);

Useful DSL functions include df.sleep(seconds), df.wait_for_schedule(cron), df.wait_for_signal(name, timeout), df.signal(id, name, data), df.join(), df.race(), df.if(), df.loop(), and df.explain().

Configuration and Caveats

• Required preload: add pg_durable to shared_preload_libraries and restart PostgreSQL.
• pg_durable.database must name the database where the extension is created; workflows are not processed in a different database.
• pg_durable.worker_role must exist and be a superuser because the worker bypasses RLS to manage all users’ instances.
• Connection-related GUCs include pg_durable.max_management_connections, pg_durable.max_duroxide_connections, pg_durable.max_user_connections, and pg_durable.execution_acquire_timeout.
• df.http() performs outbound HTTP work and is not included in standard grants unless df.grant_usage(..., include_http => true) is used.
• Upstream marks v0.2.2 as early development and not production-ready.