This topic describes how to create, manage, and monitor Cron jobs in PostgreSQL database.

Notes

Creating a Cron Job

  1. Open the needed database.
  2. Ensure the pg_cron extension was installed. Switch to the Extensions tab of the Object Browser and find pg_cron in the list of extensions. Gray text of it means the extension was not installed yet. You can install it by two ways:
    • By GUI. Click the extension to select it. Click Utilities button on the General tab and choose Install from the drop-down menu.
    • By SQL. Create a new SQL window and type the following SQL command there:
      CREATE EXTENSION pg_cron;
      Click Execute query button. If the Commit button is active, click it to commit changes.
  3. Switch to the Jobs tab.
  4. Click the New button above the list. A new SQL window will be created and filled with the bone of SQL command for creating a new Cron job. Fill the needed parameters in the VALUES clause. Probably, the most challenging parameter is schedule, but you can find many explanations and examples in the Internet. Example of the SQL command to create a new Cron job:
    INSERT INTO cron.job (
      schedule,
      command,
      nodename,
      nodeport,
      database,
      username,
      active,
      jobname
    )
    VALUES (
      '* 0-23 * * *',
      'select dwh.load_dwh();',
      'localhost',
      5432,
      'dbmaster',
      'admin',
      True,
      'Loading Data Warehouse'
    );
    
  5. Click Execute query button.
  6. If the Commit button is active, click it to commit changes.
  7. Switch to the Jobs tab of the Object Browser and check if the new job is in the list.

Monitoring a Cron Job

  1. Open a database and switch to the Jobs tab.
  2. Select your job in the list of jobs.
  3. Switch to the Statistics tab. Here, you can see the last 100 executions of the job. To refresh the data, click the Refresh button above the table.

Editing a Cron Job

  1. Open a database and switch to the Jobs tab.
  2. Select your job in the list of jobs.
  3. Click Utilities button on the General tab. Here, you can see some functions you can do with the job.

Note: Anyway, you can edit the job by SQL means. All the job parameters are stored in the cron.job table, so you can construct and execute a database query like that:

UPDATE cron.job
SET schedule = '* 0-22 * * *' 
WHERE jobid = <your_job_id>;

See also

 Opening Databases

 Database Objects