Migrating Oracle Scheduler jobs to PostgreSQL

Do not migrate the schedule in isolation

A daily job may depend on an upstream import, month-end processing or the availability of another system. The migration therefore starts with trigger, effect and ownership—not with translating a calendar expression.

Determine whether a job runs SQL, PL/SQL or an external program. Include disabled jobs as well: some may still be required for year-end processing or rare recovery procedures.

Build an initial Oracle inventory

The following read-only query lists Scheduler jobs visible to the logged-in Oracle user. It is not a complete system-wide inventory and does not include separate legacy DBMS_JOB entries.

SELECT job_name,
       job_type,
       job_action,
       repeat_interval,
       enabled,
       state
FROM user_scheduler_jobs
ORDER BY job_name;

• Add runtime, recent failures, target system and business owner to each job definition.
• Capture job chains, calendars, programs and credentials separately.
• Review additional schemas and DBMS_JOB entries with the appropriate privileges.
• Include runbooks and application teams: not every dependency is visible in the database catalog.

Choose the target mechanism by responsibility

For recurring SQL tasks, pg_cron can be an option where the extension is supported and configured. More complex dependencies, external processes and central retry control may belong in an orchestration platform. Business workflows can also move into the application.

Evaluate operations at the same time: Who notices a failed run? Who is allowed to restart it? How long are execution logs retained? Where do credentials live and how are they rotated?

A deliberately small pg_cron example

On a system where pg_cron is already installed and correctly configured, a harmless demonstration job can be scheduled. It runs a read-only query. The timezone follows the pg_cron configuration; the expression alone does not define it.

SELECT cron.schedule(
  'gcon-demo-heartbeat',
  '15 2 * * *',
  $$SELECT 1$$
);

-- Remove the demonstration job after the test:
SELECT cron.unschedule('gcon-demo-heartbeat');

The example does not provide monitoring or a complete retry strategy. Whether a business job may be repeated depends primarily on what results or side effects it may already have produced.

Test failure modes before cutover

• The target system is unavailable at the scheduled start time.
• A run takes longer than the configured interval.
• Processing fails after a partial result.
• A retry must not process the same business data twice.
• Timezone changes, maintenance windows and database failover affect execution timing.

For every production job, document trigger, target mechanism, success evidence, restart procedure and owner. That list becomes part of operational acceptance.