Database Tour Documentation
Manage Cron Jobs in PostgreSQL Database

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

Notes

• Using cron-based job scheduler is available in PostgreSQL since version 10.
• To allow pg_cron to work, you need to add pg_cron to shared_preload_libraries in postgresql.conf file. After doing that, restarting the server is required.
• By default, pg_cron stores its metadata in postgres database. However, you can change this by setting the cron.database_name parameter in postgresql.conf file.

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:
   

   SELECT cron.schedule (
     'Loading Data Warehouse',
     '20 0-23 * * *',
     'select dwh.load_dwh();'
   );
   

   or

   INSERT INTO cron.job (
     schedule,
     command,
     nodename,
     nodeport,
     database,
     username,
     active,
     jobname
   )
   VALUES (
     '20 0-23 * * *',
     'select dwh.load_dwh();',
     'localhost',
     5432,
     'dbmaster',
     'admin',
     True,
     'Loading Data Warehouse'
   );
   

   Here, schedule value 20 0-23 * * * means at 20 minutes, every hour, every day, every month, every day of week.
   Some more examples of schedule values:
   • */10 * * * *
      every 10 minutes, every hour, every day
   • * 0-23 * * *
      every minute, every hour, every day
   • 30 0-23 * * 1-5
      at 30 minutes, every hour, from Monday to Friday
   • */20 3 6-8 * *
      every 20 minutes, at 3 o'clock, summer months only, every day
   • 15-45/5 8-19 * * *
      every 5 minutes in range 15-45, every hour in range 8-19, every day
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.

Stopping/Deleting 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 and choose Delete....

or execute an SQL command like this:

SELECT cron.unschedule (
  'Loading Data Warehouse'
);

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.

Example of editing a cron 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 = '25 0-29 * * *' 
WHERE jobid = <your_job_id>;

See also

Opening PostgreSQL Databases

Database Objects