Showing results for 
Search instead for 
Did you mean: 

How to run jobs in multiple databases using pgAgent?


The postgres job scheduling tool pgAgent can only run jobs in the database where it is installed. 


If someone wants to run jobs in multiple database in a Postgres cluster, they will need to configure pgAgent in each database in the cluster which is a management nightmare. 


If you are running EDB Postgres in Oracle compatibility, you can use DBMS_SCHEDULER package to schedule and run jobs in other database, as below:



Step 1: Install pgAgent

yum install edb-as-96-pgagent


Step 2: Create pgagent schema and tables in edb database:

$EDB_HOME/bin/psql -p 5444 -d edb -U enterprisedb -f /usr/share/edb-as96-pgagent-3.4.1/pgagent.sql 


Step 3: Create extension dbms_scheduler in edb database:

$EDB_HOME/bin/psql -p 5444 -d edb -U enterprisedb -c "CREATE EXTENSION dbms_scheduler;"


Step 4: Make sure the edb-pgagent-.9.6.service file in /usr/lib/systemd/system is connecting to edb database.


Step 5: Create a .pgpass file in root's home:

# vi .pgpass



Save it and change the mode to 0600.


Step 5: Start the pgAgent service:

systemctl start edb-pgagent-9.6


Step 6: Login to edb database and perform these steps. 

$EDB_HOME/bin/psql -p 5444 edb -U enterprisedb


6.1) In edb database, create Postgres extenson dblink:
6.2) In edb database create a stored procedure that will run a procedure or function in another database:
CREATE OR REPLACE PROCEDURE run_remote_proc ( connstr varchar2, procname varchar2) IS
DECLARE cmdstr varchar2;
         cmdstr := 'exec ' || procname;
         PERFORM dblink_connect ('myconn', connstr);
         PERFORM dblink_exec ('myconn', cmdstr);
         PERFORM dblink_disconnect ('myconn');
6.3) Create the job :
exec dbms_scheduler.create_job (
job_name => 'MY_TESTDB_JOB'
, job_type => 'PLSQL_BLOCK'
, job_action => 'BEGIN run_remote_proc (''port=5544 dbname=testdb'',''test_proc''); END;'
, start_date => 25-OCT-18 17:00:00.000000',
, repeat_interval => 'FREQ=DAILY;BYHOUR=18;BYMINUTE=45'
, end_date => NULL
, enabled => TRUE
, comments => 'This is a test'
In this example, I am executing a procedure called test_proc in the remote database testdb, but one can execute any procedure by supplying it's name as 2nd parameter in run_remote_proc procedure.
One can create separete jobs to execute stored procedures in different databases by supplying the dbname in 1st parameter in run_remote_proc procedure without needing to install pgAgent in each database.
Version history
Revision #:
1 of 1
Last update:
‎10-26-2018 07:54 AM
Updated by:
Labels (3)