Setting up and using the pg_cron extension

The pg_cron extension for PostgreSQL is used to execute periodic tasks. You can schedule SQL tasks, such as queries and data imports, using jobs that run at the intervals you set. On a daily, weekly or monthly basis, for example.

The pg_cron extension is available with Scaleway Managed Databases for PostgreSQL. The extension is natively loaded in the shared_preload_libraries of the Database Instances by default.

Before you start

To complete the actions presented below, you must have:

• A Scaleway account logged into the console
• Owner status or IAM permissions allowing you to perform actions in the intended Organization
• A Database Instance running a PostgreSQL engine

Installing pg_cron

Run the following command to install the extension:

rdb=> CREATE EXTENSION pg_cron;
CREATE EXTENSION

Configuring pg_cron

To fully use the extension, you must grant read/write rights to the user who will be running the pg_cron functions to manage jobs on the database.

Scheduling jobs

Jobs allow you to define the SQL command or task you want to run based on a cron schedule.

To schedule jobs, you can run the following command in the SQL client:

SELECT cron.schedule(
  '${JOB_NAME}',
  '${SCHEDULE_SPEC}',
  $$
    ${SQL_COMMAND}
  $$
);

Replace the variables with the corresponding information:

• ${JOB_NAME} - set a name for the job
• ${SCHEDULE_SPEC} - the schedule specification in cron format
• ${SQL_COMMAND} - the SQL command to be executed. Depending on the command, you might need to specify other parameters.

Examples

Deleting old data

You can run the command below to delete old data from the events table every Saturday at 3:30am:

SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

Scheduling a VACUUM job

You can run the command below to execute the VACUUM task every day at 10:00am.

SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');

Listing jobs

To list all scheduled jobs, you can run the following command:

SELECT * FROM cron.job;

Each job is represented by a record. You can see the following information in the response:

• jobid - a unique job ID
• schedule - the schedule specification in cron format
• command - the SQL command
• database, username, nodename, nodeport - connection details
• active - whether the job is active or not
• jobname - the name of the job

-[ RECORD 1 ]-------------------------------------------------------------
jobid | 1
schedule | 30 3 * * 6
command | DELETE FROM events WHERE event_time < now() - interval '1 week'
nodename | /var/run/postgresql
nodeport | 5432
database | rdb
username | myuser
active | t
jobname |
-[ RECORD 2 ]-------------------------------------------------------------
jobid | 2
schedule | 0 10 * * *
command | VACUUM
nodename | /var/run/postgresql
nodeport | 5432
database | rdb
username | myuser
active | t
jobname | nightly-vacuum

Unscheduling jobs

To unschedule a job, you can run the following command:

SELECT cron.unschedule('${JOB_ID}');

Replace ${JOB_ID} with the ID of the job you want to unschedule.

Examples

To unschedule the jobs set in the previous section, you can run:

SELECT cron.unschedule(1);

or

SELECT cron.unschedule('nightly-vacuum');

Scheduling jobs in other databases

To schedule a job in another database, you can use the schedule_in_database function.

In the example below we create a job to insert values into another table.

SELECT cron.schedule_in_database('job-in-another-db', '0 12 * * *', 'INSERT INTO public.another_db_table values (now())', 'mydb');

Editing jobs

To edit a job, you can use the alter_job function.

In the example below we alter an existing job to run in a different database. You must specify the job_id and database.

SELECT cron.alter_job(job_id:=3,database:='anotherdb');

Cron specifications

Schedules in pg_cron use the standard Cron syntax:

┌───────────── min (0 - 59)
│ ┌────────────── hour (0 - 23)
│ │ ┌─────────────── day of month (1 - 31) or last day of the month ($)
│ │ │ ┌──────────────── month (1 - 12)
│ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
│ │ │ │ │ Saturday, or use names; 7 is also Sunday)
│ │ │ │ │
│ │ │ │ │
* * * * *

How to configure your schedule timezone

The time zone of the pg_cron extension can be changed in the advanced settings of the Database Instance. By default, the time zone is set to GMT.

1. Go to the Advanced settings of your Database Instance in the Scaleway console.
2. Click edit icon.
3. Click + Add parameters.
4. Select cron.timezone in the drop-down.
5. Enter the time zone of your choice.
   Tip

   Refer to the official PostgreSQL documentation for more information about timezone conventions.

6. Click validate icon to validate.
   Note

   The configuration takes a few seconds to be applied. During this time the Database Instance connection remains uninterrupted. However, you must wait until the new configuration is applied to edit your advanced settings again.