What Happens to My Existing DBMS_JOB and DBMS_SCHEDULER’s Jobs After Upgrading to EPAS 16?

EDB Postgres Advanced Server (EPAS) 16 has removed the dependency on PgAgent for job scheduling and has added a new extension called edb_job_scheduler, which supports job scheduling through dbms_job and dbms_scheduler. This extension operates via background workers, is easy to configure, and stays under the control of the database server. For more details on the new job scheduler extension, please refer to the blog post "Simplifying Database Job Scheduling in EDB Postgres Advanced Server 16" by Vibhor Kumar.

Users who extensively use dbms_job and/or dbms_scheduler have many questions about the new extension as they transition to EPAS v16, particularly concerning their existing jobs from older versions. They are concerned about how these jobs will be set up again and whether any manual work will be needed to recreate them and so on.

The good news is that users don't have much to do regarding their jobs. They only need to perform two steps after upgrading to the new database cluster:

1. Firstly, set the shared_preload_libraries and edb_job_scheduler.database_list parameters in the postgresql.conf file. Then start the server and run CREATE EXTENSION edb_job_scheduler to create the extension.
2. The second step is to update the dbms_job and/or dbms_scheduler extension for each database where it is installed. The same instructions will be prompted by pg_upgrade after a successful upgrade to update the extension as follows

Checking for extension updates                                notice

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

The update_extensions.sql file includes ALTER EXTENSION ... UPDATE  SQL commands for each database requiring extension updates. This process transfers jobs from the PgAgent catalog to the edb_job_scheduler catalog. After this, dbms_job and dbms_scheduler jobs don't have any dependency on pgAgent.  Users can decide to remove the extension.

Here's a quick demonstration where I'm upgrading from EPAS v14 to EPAS v16. I've created a simple job in v14 that inserts a row into a test table. To keep this post concise, we'll skip executing the job in v14 and instead observe its execution directly after the upgrade to v16.

— Create test table on v14, edb database.

CREATE TABLE public.test(seq INT GENERATED ALWAYS AS IDENTITY, time TIMESTAMP);

– Create a procedure that job will execute.

CREATE OR REPLACE PROCEDURE public.do_job(t timestamp) IS
BEGIN
    INSERT INTO public.test(time) VALUES(t);
    DBMS_OUTPUT.PUT_LINE('Value inserted.');
END;

– Create the job in dbms_job:

DECLARE
    jobno INTEGER;
BEGIN
    DBMS_JOB.SUBMIT (jobno,'public.do_job(now());',SYSDATE,'SYSDATE + 5',FALSE,1,TRUE);
END;

– You can check that, using following SQL:

edb=# SELECT jobid, jobnextrun, jstcode FROM pgagent.pga_job
INNER JOIN pgagent.pga_jobstep ON (jobid = jstjobid);

 jobid |        jobnextrun         |              jstcode               
-------+---------------------------+------------------------------------
     1 | 27-MAY-24 11:48:46 +05:30 | BEGIN dbms_job.run(1, false); END;
(1 row)

– Next, upgrade to v16 using pg_upgrade as:

$NEWBIN/pg_upgrade -b $OLDBIN -B $NEWBIN -d $OLDDATA -D $NEWDATA

– Then, add the following settings to the postgresql.conf file in the new data directory:

shared_preload_libraries = '$libdir/edb_job_scheduler'
edb_job_scheduler.database_list = 'edb'

Note that if you expect many jobs to run concurrently, you need to increase max_worker_processes. Then adjust the edb_job_scheduler.max_workers_per_database settings accordingly.

– Now, start the server and verify that the settings are configured as expected:

edb=# show shared_preload_libraries ;
 shared_preload_libraries  
---------------------------
 $libdir/edb_job_scheduler
(1 row)

edb=# show edb_job_scheduler.database_list;
 edb_job_scheduler.database_list 
---------------------------------
 edb
(1 row)

– Next, create edb_job_scheduler extension:

edb=# create extension edb_job_scheduler;
CREATE EXTENSION

–Finally, execute the update_extensions.sql script to update the extension.

edb=# \i /tmp/RM5444/update_extensions.sql 
You are now connected to database "edb" as user "amul".
ALTER EXTENSION

– Just in case you're wondering what is contained in the update_extensions.sql, here it is:

$ cat /tmp/RM5444/update_extensions.sql 
\connect edb
ALTER EXTENSION "dbms_job" UPDATE;

– In our example, since the job's next run time has already been reached, it will be picked up and executed by the edb_job_scheduler. You can view details about its execution in the sys.job_run_details catalog:

edb=# select jobid, status, scheduletime from sys.job_run_details;
 jobid | status |       scheduletime        
-------+--------+---------------------------
     1 | s      | 27-MAY-24 11:48:46 +05:30
(1 row)

– The job action is expected to result in the insertion of a row into the test table.

edb=# select * from test;
 seq |           time            
-----+---------------------------
   1 | 27-MAY-24 11:56:17.003697
(1 row)

That's it !

Once you update the dbms_job and dbms_scheduler extensions, the pending jobs will execute accordingly. You can check your job list in sys.jobs or use the various views provided by dbms_scheduler to display job information if you use that.