pgAutomator

The goal for pgAutomator is to provide the most feature rich, stable, and comprehensive job scheduling and automation platform.

It will run on Postgres, but will not be limited to only working with Postgres servers. It will be possible to use as an enterprise wide platform for centrally managing all scheduled jobs within your organization.

Components

1. Postgres extension which will set up it's own schema, and tables, functions, views, etc within it inside a target database.
   • installed on server
2. Job agent which connects to the database the extension is installed on, and does all the actual work of running the jobs, steps, etc.
   • installed on server
3. Management UI to view/configure agents, set up jobs and steps, view current running jobs and history, etc. Must also connect to the database the extension is installed on.
   • installed on client machine, or available in a hosted fashion.

Planned features & design decisions

• An extensive API for managing all facets of pgAutomator defined in database functions.
  • For every single interaction with the extension's tables and all, every single action should be embeded in a function. No arbitrary SQL should be necessary in the Agent, or the management UI.
  • This allows easier extension or automation if someone wants to integrate their workflow with pgAutomator.
• Schedules can be set up independant of jobs, and a single schedule can be used for multiple jobs.
• Jobs can have multiple schedules.
• Agent's should be able to have smtp info set up to allow them to send mail.
• Jobs and Steps should have an "email on" configuration, and allow user defined subjects, messages, to, and from.
• Jobs should be able to be killed from the management UI.
• Jobs should be able to be kicked off from the management UI.
• Steps should be able to run a batch script on the host machine of the agent, or connect to any database using JDBC and execute SQL / functions / stored procedures.
• User created categories for categorizing jobs, to group like-jobs together.
• Detailed logging of the Job and Step.
  • Know which Agent was executing a Job.
  • Know which Schedule kicked off a Job.
  • Show start time, end time, and how long the Job / Step ran for.
  • Show the Job / Step State.
  • Show any messages generated by running a Step.
  • Show the number of retries for a Step.
• Schedules should allow a bunch of temporal scheduling options.
  • Schedule Type - when to run up to the day resolution.
    • Once - Defined as a single date.
    • Daily - Defined as every [x] days.
    • Weekly - Defined as a list of day's of the week to run on.
    • Monthly - Defined as a single day within the month to run on, every [x] months.
    • Monthly Relative - Defined as [first, second, third, forth, last] day of week [smtwtfs] every [x] months.
  • Schedule Sub - when to run at the time resolution.
    • Once - Defined as a single time.
    • Every - Defined as every [x] [min, hour] starting at [x] time and ending at [x] time.
• There should be info on what agents are currently up, and what they are doing.

Requires

pgAutomator-agent requires Java 9+

Platforms targeted

• Linux
• Windows
• macOS