Migrating Oracle Scheduler Jobs (DBMS_SCHEDULER) to PostgreSQL

Where Did the Scheduler Go?

Oracle ships a full job scheduler in the database — DBMS_SCHEDULER — so when you move to PostgreSQL and go looking for its equivalent, the honest answer is: core PostgreSQL doesn’t have one. That sounds like a gap, but in practice most Oracle shops use a small slice of DBMS_SCHEDULER — “run this PL/SQL on this schedule” — and that slice maps cleanly onto the pg_cron extension. This guide covers the mapping, the calendar-to-cron translation, and the handful of differences that actually bite.

Three Ways to Schedule in PostgreSQL

• pg_cron — a widely-available extension that stores cron-syntax schedules in the database and runs SQL from a background worker. The most direct analog to a simple DBMS_SCHEDULER job, and supported on Amazon RDS/Aurora, Google Cloud SQL, and Azure.
• pgAgent — an external daemon (from the pgAdmin project) with multi-step jobs and schedules. Closer to DBMS_SCHEDULER’s richer model when a job has several steps or mixes SQL and shell.
• An external orchestrator — OS cron, systemd timers, or Airflow calling psql. The right choice when scheduling spans more than one system, or you already run an orchestrator.

For a straight migration of in-database jobs, pg_cron is almost always the answer. The rest of this guide leads with it.

The Common Case: A Repeating Job

Here’s a typical Oracle nightly job and its pg_cron equivalent. The shape is the same — a name, a schedule, and an action:

-- Oracle: run a PL/SQL block every day at 02:00
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'NIGHTLY_ROLLUP',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN pkg_reporting.nightly_rollup; END;',
    repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
    enabled         => TRUE);
END;
/

-- PostgreSQL: pg_cron, same schedule in cron syntax
SELECT cron.schedule(
  'nightly-rollup',        -- job name
  '0 2 * * *',             -- min hour dom mon dow
  $$ CALL nightly_rollup(); $$
);

cron.schedule returns a job id and records the job in the cron.job table. Re-running it with the same job name updates the schedule rather than creating a duplicate.

Translating repeat_interval to cron

Oracle’s calendaring syntax is more expressive than cron, but the schedules real jobs use almost always have a direct five-field cron equivalent:

Oracle repeat_interval                         cron
FREQ=HOURLY                                    0 * * * *
FREQ=DAILY;BYHOUR=2;BYMINUTE=0                 0 2 * * *
FREQ=DAILY;BYHOUR=0,12                         0 0,12 * * *
FREQ=WEEKLY;BYDAY=MON;BYHOUR=6                 0 6 * * 1
FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=3             0 3 1 * *
FREQ=MINUTELY;INTERVAL=15                      */15 * * * *

Two honest limits. Cron can’t express “the last day of the month” or “the second Tuesday” directly — for those, schedule a daily job and guard the body with a date check (SELECT ... WHERE date_trunc(’month’, now()) ...), or use pgAgent. And pg_cron’s finest granularity is one minute, the same practical floor as most DBMS_SCHEDULER jobs.

Where the Job Body Goes

In Oracle the job_action is often an anonymous PL/SQL block calling a package. On PostgreSQL, convert that logic into a PL/pgSQL function or procedure and have the scheduled job simply call it. Keep the schedule thin and the logic in a named routine — it’s easier to test, version, and reuse:

CREATE PROCEDURE nightly_rollup()
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO daily_totals (day, amount)
  SELECT current_date - 1, sum(amount)
  FROM   orders
  WHERE  created_at >= current_date - 1
  AND    created_at <  current_date;
END;
$$;

-- the cron job just calls it
SELECT cron.schedule('nightly-rollup', '0 2 * * *', $$ CALL nightly_rollup(); $$);

Multi-Step Jobs and Chains

If you use DBMS_SCHEDULER chains — step A, then B on success, else C —pg_cron alone won’t model the dependency graph. Two good options: put the steps inside a single PL/pgSQL procedure with explicit control flow (the simplest port, and it runs in one transaction context you control), or move to pgAgent, whose jobs have ordered steps and per-step success/failure handling that line up with chains. Oracle windows and resource-manager plans have no PostgreSQL equivalent — those are usually dropped and handled at the application or connection-pool layer.

Five Differences That Bite

• Timezone. pg_cron evaluates schedules in the server’s timezone (set via cron.timezone), while an Oracle job honors its own start_date timezone. Confirm the target’s TZ before you trust “2 AM”.
• Overlap isn’t prevented. If a run overruns its interval, pg_cron will still start the next one. Guard re-entrant jobs with an advisory lock: IF NOT pg_try_advisory_lock(...) THEN RETURN; END IF;
• Privileges. Scheduling is restricted — grant USAGE on the cron schema to the role that owns the jobs; by default only a superuser can schedule.
• Run history lives elsewhere. Oracle’s DBA_SCHEDULER_JOB_RUN_DETAILS becomes cron.job_run_details — same idea, different table. Enable it and prune it, or it grows unbounded.
• Which database? pg_cron runs in one configured database (cron.database_name). On RDS/Aurora set rds.database_name; a job that must touch another database needs dblink or an external scheduler.

Inventory First

Before translating anything, list what you actually have — most databases carry a few live jobs and a pile of disabled ones you don’t need to move:

SELECT job_name, enabled, repeat_interval, job_action
FROM   dba_scheduler_jobs
WHERE  owner NOT IN ('SYS','SYSTEM')
ORDER  BY enabled DESC, job_name;

Translate the enabled ones: map each repeat_interval to a cron expression, move each job_action into a function, and recreate it with cron.schedule. The disabled ones are a conversation with the app owners, not a migration task.