Cron syntax and examples

A pg_cron schedule is either a standard five-field cron expression or an interval in seconds. Both are written as text in cron.schedule() or in the Schedule field of the dashboard's Cron tab. All times are UTC on SupaHost.

The five fields

┌───────────── minute        (0-59)
│ ┌─────────── hour          (0-23)
│ │ ┌───────── day of month  (1-31)
│ │ │ ┌─────── month         (1-12)
│ │ │ │ ┌───── day of week   (0-6, Sunday = 0)
│ │ │ │ │
* * * * *

A job runs whenever the current UTC time matches every field. A * means "any value".

Building blocks

When both day of month and day of week are restricted (neither is *), the job runs when either matches. 0 9 1 * 1 runs at 09:00 on the 1st of each month and also every Monday. To run on "the first Monday", schedule it every Monday and check the date in SQL.

Examples

The dashboard's Schedule presets cover the common cases: Every minute, Every 5 minutes, Hourly, Daily at 03:00 UTC and Every 30 seconds.

Sub-minute schedules

Cron expressions can't go below one minute. For more frequent jobs, pg_cron accepts an interval in seconds, from 1 to 59:

select cron.schedule(
  'drain-email-queue',
  '20 seconds',
  $$ select net.http_post(
       url := (select decrypted_secret from vault.decrypted_secrets where name = 'project_url')
              || '/functions/v1/email-worker',
       headers := jsonb_build_object(
         'Authorization', 'Bearer ' || (select decrypted_secret from vault.decrypted_secrets where name = 'service_role_key')
       )
     ); $$
);

Seconds schedules are intervals, not clock times, so use them for polling-style work rather than "at exactly :30". Keep each run short: a job never overlaps with itself, so a slow run delays the next one.

Working with UTC

Schedules are evaluated in UTC, so convert local times before you write them:

Cron doesn't follow daylight saving changes. A job at 0 7 * * * runs at 08:00 Berlin time in winter and 09:00 in summer. If the local time matters exactly, either accept the one-hour shift, or schedule the job hourly and check the local time in SQL:

select cron.schedule(
  'berlin-morning-digest',
  '0 * * * *',
  $$
  do $job$
  begin
    if extract(hour from now() at time zone 'Europe/Berlin') = 8 then
      insert into public.digest_runs (ran_at) values (now());
      -- ...build and send the digest here
    end if;
  end
  $job$;
  $$
);

Note the different dollar-quote tag ($job$) inside the outer $$ ... $$, so the two don't clash.

Checking a schedule

Before relying on a new schedule, confirm it registered the way you expect:

select jobname, schedule, active from cron.job where jobname = 'berlin-morning-digest';

Then watch the first few runs in Dashboard → your project → Cron tab → Recent runs or in cron.job_run_details. See Monitoring and debugging cron jobs.

If an expression isn't valid, cron.schedule raises an error and no job is created.

Spreading the load

Many jobs scheduled at 0 * * * * or 0 0 * * * all start at the same moment, on the same server as your API and database. Offset them by a few minutes (7 * * * *, 23 * * * *) so they don't compete. Also avoid 03:00 to 05:00 UTC for heavy jobs if your instance has automatic upgrades turned on, since that's the maintenance window.

Related

• Scheduled jobs with pg_cron
• Cron job not running
• Timezones and wrong local time in Postgres
• pg_cron on GitHub