Migrating SQL Server Agent jobs to PostgreSQL

The schema converts. The data loads. Then someone asks what happens to the 40 jobs that ran overnight, and the migration stalls for three weeks.

SQL Server Agent has no equivalent in PostgreSQL. Not a weaker version — no equivalent. It is a scheduler, a retry engine, a credential store, an execution history database and an alerting system in one service, and PostgreSQL ships none of that. Every one of those five functions has to be rebuilt somewhere else, and the usual mistake is to replace only the first one.

This continues our guide on what breaks in a SQL Server to PostgreSQL migration. Here we deal with the part that lives outside the database.

What SQL Agent actually gives you

Before choosing a replacement, list what you are replacing. A typical SQL Agent job carries more than a schedule:

• •A schedule, often with several attached to one job.
• •Ordered steps, with on-success and on-failure flow between them.
• •Step types beyond T-SQL: CmdExec, PowerShell, SSIS packages, replication agents.
• •A security context — proxies and credentials, so a step can run as someone other than the service account.
• •Retry counts and retry intervals, per step.
• •Execution history retained in msdb, queryable after the fact.
• •Operators and alerts on failure, usually by email.

Nothing on that list survives the move by itself.

The candidate replacements

pg_cron is the default suggestion and the most frequently wrong one. It runs SQL on a schedule and stops there. If the job had two steps, a retry policy and an email on failure, pg_cron replaces the schedule and silently drops the other three.

Mapping the pieces

Schedules

The straightforward part. SQL Agent schedules translate to cron expressions, with two things to watch. First, a job with multiple schedules becomes multiple entries. Second, time zones: SQL Agent runs in the server's local time, and pg_cron by default schedules in the database time zone. If the old server ran local time with daylight saving and the new one runs UTC, jobs move by an hour twice a year.

-- pg_cron: nightly aggregate at 02:15
SELECT cron.schedule(
  'nightly-rollup',
  '15 2 * * *',
  $$ CALL reporting.build_daily_rollup() $$
);

-- check what is scheduled and in which time zone
SELECT jobid, schedule, command, nodename FROM cron.job;
SHOW timezone;

Multi-step jobs

SQL Agent lets a job run step 1, and on failure jump to step 3. That branching has no direct equivalent. Two workable approaches: wrap the whole sequence in a single procedure with explicit exception handling, or move the job to a real orchestrator where steps and dependencies are first-class.

The wrapper approach is fine for two or three steps and gets ugly beyond that. If a job has six steps with conditional flow, that is a signal it was always a pipeline wearing a job's clothing, and it belongs in Airflow or your CI runner rather than in the database.

Retries

SQL Agent retries a failed step n times with a delay. pg_cron does not retry at all — a failed run is simply a failed run. Either build the retry into the procedure, or put the job somewhere that retries natively.

# systemd timer with retry, as a replacement for a CmdExec step
# /etc/systemd/system/nightly-export.service
[Service]
Type=oneshot
ExecStart=/usr/local/bin/nightly-export.sh
Restart=on-failure
RestartSec=300
# systemd retries; journalctl -u nightly-export keeps the history

Credentials and proxies

This one is a security decision, not a technical one. A SQL Agent proxy let a step run under a different Windows account without anyone knowing that account's password. When the job moves to a shell script, the credential moves with it — and the lazy answer is a password in the script.

Decide deliberately: a secret manager, a systemd unit running as a dedicated service user, .pgpass with the right file mode, or client certificates. Whatever is chosen, it should be chosen, not inherited from whoever wrote the first script.

History

msdb keeps job history and people query it more than they admit — to prove a job ran, to find when something started failing, to show an auditor. pg_cron writes to cron.job_run_details, which is thinner and, importantly, is not cleaned up automatically. It grows until someone notices.

-- what actually ran, and what failed
SELECT jobid, status, return_message, start_time, end_time
FROM cron.job_run_details
WHERE start_time > now() - interval '7 days'
  AND status <> 'succeeded'
ORDER BY start_time DESC;

-- keep it from growing forever
DELETE FROM cron.job_run_details WHERE end_time < now() - interval '30 days';

Alerting

SQL Agent could email an operator on failure. Nothing in PostgreSQL does this. The failure has to reach your existing monitoring — a Prometheus alert on the last successful run timestamp is usually the least effort, because you probably already run Prometheus. The check that matters is not "did the job error" but "has the job succeeded recently enough", which also catches the job that stopped being scheduled at all.

The steps that are not jobs

Two step types deserve their own decision, because they usually turn out to be projects:

• •SSIS packages. These do not port. They get rebuilt in whatever ETL tooling you land on, and the effort is measured in the number of packages, not the size of the database.
• •Replication agents. If the job list includes replication, the migration question is not scheduling but what replaces the replication topology entirely.

How we sequence it

• •Export the full job inventory from msdb before touching anything — name, schedule, steps, step type, proxy, retry policy, notification, and when each last ran.
• •Sort by whether the job still matters. In every migration we have done, part of the list is jobs nobody has looked at in years. Retiring one is cheaper than porting it.
• •Map each surviving job to a target: pg_cron, pgAgent, systemd, orchestrator.
• •Rebuild, then run old and new in parallel with the new one writing to a staging target or with its writes disabled.
• •Compare outputs across a full cycle, including month-end. Quarterly jobs are the ones that surface three months after cutover, when everyone has moved on.
• •Switch off the SQL Agent job only after its replacement has run clean through a full period.

-- run this on SQL Server first: the inventory nobody has
SELECT j.name,
       j.enabled,
       s.step_id,
       s.step_name,
       s.subsystem,          -- TSQL, CmdExec, PowerShell, SSIS...
       s.retry_attempts,
       s.retry_interval,
       s.proxy_id,
       j.notify_level_email
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps s ON s.job_id = j.job_id
ORDER BY j.name, s.step_id;

Common questions

Is pgAgent still a reasonable choice?

Yes, if the goal is a like-for-like move and the team is used to the SQL Agent model. It gives multi-step jobs and a history table without new infrastructure. It is less actively developed than the alternatives and it needs its own daemon running somewhere, which becomes one more thing to monitor. For a handful of jobs it is a pragmatic answer; for thirty, an orchestrator ages better.

Can jobs stay on the SQL Server box after the database moves?

Sometimes, and it is a legitimate transition step — point the T-SQL steps at PostgreSQL over a linked connection and leave the scheduling where it is. It buys time. It also means keeping a Windows licence alive for a scheduler, so treat it as a bridge with an end date rather than the destination.

Where this fits

Job migration is rarely quoted separately, which is why it inflates the estimate later. In our assessments it is a line item of its own: inventory, decide, rebuild, run parallel. We do MSSQL and PostgreSQL migrations at €65/hour, with the inventory step scoped up front so that the number of jobs is known before anyone commits to a date.