Pavlo Golub: Migrate scheduled jobs to pg_timetable from pgAgent

Why migrate to pg_timetable from pgAgent? There are many reasons, actually, why people might want to export pgAgent jobs to pg_timetable. Most of them may be spotted by taking a look at the PostgreSQL schedulers comparison table I introduced in one of my previous posts and the supported platform table from the official readme. In my previous article I highlighted the reasons why and the ways how one might want to move scheduled jobs from pg_cron to pg_timetable. Differences and similarities between pg_timetable and pgAgent architectures Both tools use multi-step job architecture. pg_timetable uses “chain” and “task” terms, while pgAgent uses “job” and “step” terminology. pgAgent is able to execute SQL and BATCH (shell) tasks. pg_timetable‘s task can be one of SQL, PROGRAM, and BUILTIN. Thus to emulate pgAgent‘s BATCH step, pg_timetable will run a PROGRAM task calling shell execution, e.g. bash -c "command". pgAgent allows specifying many schedules for a job. If several schedules are found, we should duplicate the chain definition for each of them. To store scheduling information, pg_timetable uses cron notation, but pgAgent uses boolean arrays as storage. We need to provide a way of transforming the array to cron notation. pgAgent, as well as pg_timetable, allows ignoring task errors. The possible scenarios are: fail the job, mark the step as succeeded and continue, mark as fail but ignore it and proceed. pg_timetable allows only ignoring the error without marking it as succeeded. While pgAgent can limit job execution by the agent’s host, pg_timetable is more flexible in implementing control with the client name. Here we will assume the host name becomes a client name after migration. pgAgent allows setting start and end timestamp for a schedule, as well as if a schedule is enabled. We will migrate only valid schedules: those which are enabled and active right now within the date range. Prerequisites You have installed the pgagent ext[...]