pg_timetable: Advanced scheduling for PostgreSQL

pg_timetable is an advanced standalone job scheduler for PostgreSQL, offering many advantages over traditional schedulers such as cron and others. It is completely database driven and provides a couple of advanced concepts. It allows you to schedule PostgreSQL commands, system programs and built-in operations:

-- Run public.my_func() at 00:05 every day in August:
SELECT timetable.add_job('execute-func', '5 0 * 8 *', 'SELECT public.my_func()');

-- Run VACUUM at minute 23 past every 2nd hour from 0 through 20 every day:
SELECT timetable.add_job('run-vacuum', '23 0-20/2 * * *', 'VACUUM');

-- Refresh materialized view every 2 hours:
SELECT timetable.add_job('refresh-matview', '@every 2 hours', 
  'REFRESH MATERIALIZED VIEW public.mat_view');

-- Clear log table after pg_timetable restart:
SELECT timetable.add_job('clear-log', '@reboot', 'TRUNCATE public.log');

-- Reindex at midnight on Sundays with reindexdb utility:

--  using default database under default user (no command line arguments)
SELECT timetable.add_job('reindex-job', '0 0 * * 7', 'reindexdb', job_kind := 'PROGRAM');

--  specifying target database and tables, and be verbose
SELECT timetable.add_job('reindex-job', '0 0 * * 7', 'reindexdb',
          '["--table=foo", "--dbname=postgres", "--verbose"]'::jsonb, 'PROGRAM');

--  passing password using environment variable through bash shell
SELECT timetable.add_job('reindex-job', '0 0 * * 7', 'bash',
    '["-c", "PGPASSWORD=5m3R7K4754p4m reindexdb -U postgres -h 192.168.0.221 -v'::jsonb,
    'PROGRAM');

Documentation

https://pg-timetable.readthedocs.io/

Main features

Tasks can be arranged in chains
Each task executes SQL, built-in or executable command
Parameters can be passed to tasks
Missed chains (possibly due to downtime) can be retried automatically
Support for configurable repetitions
Builtin tasks such as sending emails, downloading, importing files, etc.
Fully database driven configuration
Full support for database driven logging
Enhanced cron-style scheduling
Optional concurrency protection

Installation

Complete installation guide can be found in the documentation.

Possible choices are:

Quick Start

Complete usage guide can be found in the documentation.

Download pg_timetable executable
Make sure your PostgreSQL server is up and running and has a role with CREATE privilege for a target database, e.g.

    my_database=> CREATE ROLE scheduler PASSWORD 'somestrong';
    my_database=> GRANT CREATE ON DATABASE my_database TO scheduler;

Create a new job, e.g. run VACUUM each night at 00:30

    my_database=> SELECT timetable.add_job('frequent-vacuum', '30 0 * * *', 'VACUUM');
    add_job
    ---------
          3
    (1 row)

Run the pg_timetable

    # pg_timetable postgresql://scheduler:somestrong@localhost/my_database --clientname=vacuumer

PROFIT!

Supported Environments

Cloud Service	Supported	PostgreSQL Version	Supported	OS	Supported
Alibaba Cloud	✅	17 (devel)	✅	Linux	✅
Amazon RDS	✅	16 (beta)	✅	Darwin	✅
Amazon Aurora	✅	15 (current)	✅	Windows	✅
Azure	✅	14	✅	FreeBSD*	✅
Citus Cloud	✅	13	✅	NetBSD*	✅
Crunchy Bridge	✅	12	✅	OpenBSD*	✅
DigitalOcean	✅	11	✅	Solaris*	✅
Google Cloud	✅	10	✅
Heroku	✅
Supabase	✅

* - there are no official release binaries made for these OSes. One would need to build them from sources.

** - previous PostgreSQL versions may and should work smoothly. Only officially supported PostgreSQL versions are listed in this table.

Contributing

If you want to contribute to pg_timetable and help make it better, feel free to open an issue or even consider submitting a pull request.

wenlive/pg_timetable