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 extension in your database.
• You've scheduled pgagent jobs to export.
• You have created the pg_timetable schema by running the pg_timetable against this database at least once. The schema will be created automatically. It doesn't matter if you have already added some chains or not.

The pgAgent to pg_timetable migration script - full explanation

To export every piece of information available as precisely as possible, use this SQL snippet. I will explain it step by step.

bool_array_to_cron()

As I said earlier, we need to convert boolean arrays storing execution information for minutes, hours, days, and months to proper cron notation.

The first thing to notice is that arrays are of different lengths, e.g., 60 for minutes, 12 for months, etc. Second, the cron clause starts from 0 for hours and minutes but with 1 for other units.

The trick is simple: unnest the input array, join it with order numbers, throw out rows with false values, and combine numbers into a cron-acceptable comma-separated string. If all values in the array are false, then just use asterisk cron notation.

Which shell to use

pg_timetable is a cross-platform tool that may run on most of the known platforms. Because of this, we want to know exactly which shell will be used for batch pgAgent tasks. On Unix-like platforms the obvious choice would be sh -c "command". On Windows, it might be either pwsh -c "command" or cmd /C "command".

Thecte_shell clause serves precisely that purpose. You can easily specify the default shell to use:

Please note the naming convention I'm using here. If the CTE clause starts with 'cte', it somehow produces the final result. Meanwhile, CTE clauses named 'pga_*' prepare pgAgent-specific result sets for transformation.

pga_schedule

In this CTE clause, we want to return all active pgAgent schedules with cron notation. The schedule name will later be used in the chain name.

pga_chain and cte_chain

This clause will return the result set for all pgAgent jobs scheduled. Because on the target database there might already be some chains, we cannot use jobid directly. Instead, we want to generate the next chain_id from the sequence to use later.

Given that pgAgent might have several schedules for the same job, we are specifying jobname as a concatenation of the job name and the schedule name. cte_chain will insert all selected rows into timetable.chain table.

pga_step

This clause is the most complicated one here. So let me throw some light on it. We want to generate the task_id in a similar way as we did for chain_id.

pgAgent steps are performed in alphanumeric order by name. pg_timetable uses the numeric value to specify the task order. To generate these numeric values, we are using the rank() window function here.

The hardest part is with the connection string. pg_timetable and pgAgent allow remote execution for SQL tasks. Within the pgAgent settings, you may use either the database name to execute a task on the same cluster, or specify the connection string for a custom remote cluster. pg_timetable uses connection string notation in both cases. The logic is:

1. 1. Use connection string if specified. Ignore empty string with NULLIF().
   2. Then check if the database name is equal to the current database. If so, do nothing.
   3. Otherwise, generate the proper connection string with the database name specified.

cte_tasks and cte_parameters

These clauses are pretty straightforward. The only thing to explain is how PROGRAM tasks are specified in pg_timetable. The command column should contain the program name (possibly with full path) only. In our case, it's the name of the shell we specified in the first step. All command-line parameters for executing the shell go into the timetable.parameter table. Pay attention; we don't do any quote escaping here. pg_timetable handles all parameter values automatically.

Finally...

Finally, when you execute the migration script, you might see something like this:

From this particular example, one may conclude pgAgent has:

1. One scheduled job with the name "Download locations and aggregate".
2. That job has 3 enabled schedules: "every min", "test schedule", "run download".
3. In order to execute the migrated chain, pg_timetable should be started with the command-line option --clientname=node1.

If you want to contribute to pg_timetable and help to make it better:

• ⭐give a star to the project,
• feel free to open an 🤚issue and ask a 🎓question
• or even consider submitting a 📜pull request.

In conclusion, I wish you all the best! ♥️

One more thing. I want to thank everyone here in the Cybertec and the PostgreSQL community for the support you provided to Ukrainians and me personally! #StandWithUkraine 💙💛

I hope we can meet in person at one of the conferences, meetups, or training sessions!

Please leave your comments below. In order to receive regular updates on important changes in PostgreSQL, subscribe to the CYBERTEC newsletter, or follow us on Twitter, Facebook, or LinkedIn