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

  1. Download pg_timetable executable

  2. 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;
  3. Create a new job, e.g. run VACUUM each night at 00:30 Postgres server time zone

    my_database=> SELECT timetable.add_job('frequent-vacuum', '30 * * * *', 'VACUUM');
    (1 row)
  4. Run the pg_timetable

    # pg_timetable postgresql://scheduler:somestrong@localhost/my_database --clientname=vacuumer
  5. 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]

  -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]

      --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)

  -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

      --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-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.

1.6. Authors

Implementation: Pavlo Golub

Initial idea and draft design: Hans-Jürgen Schönig