cancel
Showing results for 
Search instead for 
Did you mean: 

How to run jobs in multiple databases using pgAgent?

Symptoms

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

Diagnosis

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. 

Solution

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

localhost:5444:*:enterprisedb:<password>

 

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:
CREATE EXTENSION 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;
BEGIN
         cmdstr := 'exec ' || procname;
         PERFORM dblink_connect ('myconn', connstr);
         PERFORM dblink_exec ('myconn', cmdstr);
         PERFORM dblink_disconnect ('myconn');
END;
 
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:
3 weeks ago
Updated by:
 
Labels (3)
Contributors