1. Introduction
pg_timetable is an advanced 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.
1.1. Main features
Tasks can be arranged in chains
A chain can consist of built-int commands, SQL and executables
Parameters can be passed to chains
Missed tasks (possibly due to downtime) can be retried automatically
Support for configurable repetitions
Built-in tasks such as sending emails, etc.
Fully database driven configuration
Full support for database driven logging
Cron-style scheduling at the PostgreSQL server time zone
Optional concurrency protection
Task and chain can have execution timeout settings
1.2. Quick Start
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 Postgres server time zonemy_database=> SELECT timetable.add_job('frequent-vacuum', '30 * * * *', 'VACUUM'); add_job --------- 3 (1 row)
Run the pg_timetable
# pg_timetable postgresql://scheduler:somestrong@localhost/my_database --clientname=vacuumer
PROFIT!
1.3. Command line options
# ./pg_timetable
Application Options:
-c, --clientname= Unique name for application instance [$PGTT_CLIENTNAME]
--config= YAML configuration file
--no-program-tasks Disable executing of PROGRAM tasks [$PGTT_NOPROGRAMTASKS]
Connection:
-h, --host= PostgreSQL host (default: localhost) [$PGTT_PGHOST]
-p, --port= PostgreSQL port (default: 5432) [$PGTT_PGPORT]
-d, --dbname= PostgreSQL database name (default: timetable) [$PGTT_PGDATABASE]
-u, --user= PostgreSQL user (default: scheduler) [$PGTT_PGUSER]
--password= PostgreSQL user password [$PGTT_PGPASSWORD]
--sslmode=[disable|require] What SSL priority use for connection (default: disable)
--pgurl= PostgreSQL connection URL [$PGTT_URL]
--timeout= PostgreSQL connection timeout in seconds (default: 90) [$PGTT_TIMEOUT]
Logging:
--log-level=[debug|info|error] Verbosity level for stdout and log file (default: info)
--log-database-level=[debug|info|error] Verbosity level for database storing (default: info)
--log-file= File name to store logs
--log-file-format=[json|text] Format of file logs (default: json)
Start:
-f, --file= SQL script file to execute during startup
--init Initialize database schema to the latest version and exit. Can be used
with --upgrade
--upgrade Upgrade database to the latest version
--debug Run in debug mode. Only asynchronous chains will be executed
Resource:
--cron-workers= Number of parallel workers for scheduled chains (default: 16)
--interval-workers= Number of parallel workers for interval chains (default: 16)
--chain-timeout= Abort any chain that takes more than the specified number of milliseconds
--task-timeout= Abort any task within a chain that takes more than the specified number
of milliseconds
REST:
--rest-port: REST API port (default: 0) [%PGTT_RESTPORT%]
1.4. 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. You also can give a star to pg_timetable project, and to tell the world about it.
1.5. Support
For professional support, please contact Cybertec.