How to run jobs on multiple databases in the same cluster in EPAS using DBMS Scheduler/Job/pgAgent.
The pgAgent is a job scheduling agent for Postgres databases, capable of running multi-step batch or shell scripts and SQL tasks on complex schedules.
If using EPAS, DBMS_Scheduler and DBMS_JOB use pgagent for their functionality.
The pgagent is only able to connect to a single database at a time. To run jobs on multiple databases,we need to configure multiple agents and service files to connect to the different databases.
You can find an overview of the steps that should be taken to perform to schedule job for multiple databases with pgAgent:
Step 1 - create database:
pgagent_test=# show port ;
port
5444
(1 row)
edb=# create database pgagent_test;
CREATE DATABASE
Step 2 - create extensions which are required for job scheduling:
The pgagent component is included in the EDB Postgres Advanced Server.The Pgagent package(edb-as14-pgagent) gets installed with database server installation.
-bash-4.2$ ls -lrth /usr/edb/as14/share/extension/pgagent*
-rw-r--r--. 1 root root 18K Apr 9 2021 /usr/edb/as14/share/extension/pgagent--unpackaged--4.2.sql
-rw-r--r--. 1 root root 15K Apr 9 2021 /usr/edb/as14/share/extension/pgagent--4.1--4.2.sql
-rw-r--r--. 1 root root 15K Apr 9 2021 /usr/edb/as14/share/extension/pgagent--4.0--4.2.sql
-rw-r--r--. 1 root root 15K Apr 9 2021 /usr/edb/as14/share/extension/pgagent--3.4--4.2.sql
-rw-r--r--. 1 root root 139 Nov 22 2021 /usr/edb/as14/share/extension/pgagent.control
-rw-r--r--. 1 root root 28K Nov 22 2021 /usr/edb/as14/share/extension/pgagent--4.2.sql
By default, pgAgent is available for installation, we can check it using the pg_available_extensions view.
edb=# select * from pg_available_extensions where name='pgagent';
name | default_version | installed_version | comment
pgagent | 4.2 | | A PostgreSQL job scheduler
(1 row)
However pgagent extension is not created in the database by default.
pgagent_test=# \dx
List of installed extensions
Name | Version | Schema | Description
edb_dblink_libpq | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
edb_dblink_oci | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
edbspl | 1.0 | pg_catalog | EDB-SPL procedural language
pldbgapi | 1.1 | pg_catalog | server-side support for debugging PL/pgSQL functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(5 rows)
The database on which we would like to schedule jobs,the pgagent extension must be present into that particular database.
We need to create pgagent extension manually in the databases where we would like to run enable the pgAgent feature for job scheduling.
Execute the below statements to create extensions.
edb=# \c pgagent_test
psql (14.4.0, server 14.4.0)
You are now connected to database "pgagent_test" as user "enterprisedb".
pgagent_test=# create extension pgagent ;
CREATE EXTENSION
pgagent_test=# create extension dbms_scheduler ;
CREATE EXTENSION
pgagent_test=# create extension dbms_job ;
CREATE EXTENSION
pgagent_test=# \dx
List of installed extensions
Name | Version | Schema | Description
dbms_job | 1.1 | sys | Creates catalog objects for DBMS_JOB package
dbms_scheduler | 1.1 | sys | Creates catalog objects for DBMS_SCHEDULER package
edb_dblink_libpq | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
edb_dblink_oci | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
edbspl | 1.0 | pg_catalog | EDB-SPL procedural language
pgagent | 4.2 | pgagent | A PostgreSQL job scheduler
pldbgapi | 1.1 | pg_catalog | server-side support for debugging PL/pgSQL functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(8 rows)
Step 3 - make a copy of the pgagent service file:
Copy an existing service configuration to create a new service configuration with different settings.
[root@localhost ~]# cp /usr/lib/systemd/system/edb-pgagent-14.service /usr/lib/systemd/system/edb-pgagent-14_pgagent_test.service
Step 4 - make a copy of the pgagent configuration file:
It is not possible to set multiple DATABASEs within one service. For each database, we need to create a separate pgAgent service and need to modify that service file with the respective connection string.(dbname,host,port,username,pgagent configuration file location)
[root@localhost ~]# cp /etc/sysconfig/edb/pgagent14/edb-pgagent-14.conf /etc/sysconfig/edb/pgagent14/edb-pgagent-14_pgagent_test.conf
Step 5 - add database and user details in the new configuration file on which we would like to schedule jobs :
[root@localhost pgagent14]# cat /etc/sysconfig/edb/pgagent14/edb-pgagent-14_pgagent_test.conf
DBNAME=pgagent_test
DBUSER=enterprisedb
DBHOST=127.0.0.1
DBPORT=5444
LOGFILE=/var/log/edb/pgagent14/pgagent_test.log
Step 6 - edit the service file and add the location of the new configuration file(edb-pgagent-14_pgagent_test.conf):
[root@localhost pgagent14]# cat /usr/lib/systemd/system/edb-pgagent-14_pgagent_test.service
[Unit]
Description=EDB Postgres Advanced Server 14 pgAgent Service
After=syslog.target
After=network.target
[Service]
Type=forking
User=enterprisedb
Group=enterprisedb
# Location of the configuration file
EnvironmentFile=/etc/sysconfig/edb/pgagent14/edb-pgagent-14_pgagent_test.conf
ExecStart=/usr/edb/as14/bin/pgagent -s ${LOGFILE} host=${DBHOST} dbname=${DBNAME} user=${DBUSER} port=${DBPORT}
KillMode=mixed
KillSignal=SIGINT
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
[Install]
WantedBy=multi-user.target
Step 7 - reload the new service file to take effect of new values:
[root@localhost pgagent14]# systemctl daemon-reload
Step 8 - start newly created service(edb-pgagent-14_pgagent_test.service) file:
[root@localhost pgagent14]# systemctl start edb-pgagent-14_pgagent_test.service
[root@localhost pgagent14]# systemctl status edb-pgagent-14_pgagent_test.service
● edb-pgagent-14_pgagent_test.service - EDB Postgres Advanced Server 14 pgAgent Service
Loaded: loaded (/usr/lib/systemd/system/edb-pgagent-14_pgagent_test.service; disabled; vendor preset: disabled)
Active: active (running) since Fri 2022-07-29 21:49:02 IST; 1min 7s ago
Process: 28922 ExecStart=/usr/edb/as14/bin/pgagent -s ${LOGFILE} host=${DBHOST} dbname=${DBNAME} user=${DBUSER} port=${DBPORT} (code=exited, status=0/SUCCESS)
Main PID: 28923 (pgagent)
Tasks: 1
CGroup: /system.slice/edb-pgagent-14_pgagent_test.service
└─28923 /usr/edb/as14/bin/pgagent -s /var/log/edb/pgagent14/pgagent_test.log host=127.0.0.1 dbname=pgagent_test user=enterprisedb port=5444
Jul 29 21:49:02 localhost.localdomain systemd[1]: Starting EDB Postgres Advanced Server 14 pgAgent Service...
Jul 29 21:49:02 localhost.localdomain systemd[1]: Started EDB Postgres Advanced Server 14 pgAgent Service.
[root@localhost pgagent14]# ps -ef | grep pgagent
enterpr+ 28923 1 0 21:49 ? 00:00:00 /usr/edb/as14/bin/pgagent -s /var/log/edb/pgagent14/pgagent_test.log host=127.0.0.1 dbname=pgagent_test user=enterprisedb port=5444
enterpr+ 28924 102181 0 21:49 ? 00:00:00 postgres: enterprisedb pgagent_test 127.0.0.1(40228) idle
enterpr+ 29135 102181 0 21:49 ? 00:00:00 postgres: enterprisedb pgagent_test [local] idle
root 29224 20388 0 21:50 pts/0 00:00:00 grep --color=auto pgagent
Step 9 - schedule the job on database "pgagent_test" :
-bash-4.2$ ./psql -p 5444 edb
psql (14.4.0, server 14.4.0)
Type "help" for help.
edb=# \c pgagent_test
psql (14.4.0, server 14.4.0)
You are now connected to database "pgagent_test" as user "enterprisedb".
pgagent_test=# create or replace procedure job_proc as begin
pgagent_test$# insert into test1 values (1 );
pgagent_test$# end;
CREATE PROCEDURE
pgagent_test=# create table test1 ( a int );
CREATE TABLE
pgagent_test=# DECLARE jobid INTEGER;
pgagent_test$# BEGIN
pgagent_test$# DBMS_JOB.SUBMIT(jobid,'job_proc;',SYSDATE, 'SYSDATE + 1/1441');
pgagent_test$# DBMS_OUTPUT.PUT_LINE('jobid: ' || jobid);
pgagent_test$# END;
jobid: 3
EDB-SPL Procedure successfully completed
pgagent_test=# select now;
now
29-JUL-22 21:51:16.463658 +05:30
(1 row)
pgagent_test=#
pgagent_test=# select * from test1 ;
a
1
1
1
(3 rows)
By using the above mentioned steps we have scheduled the job for database pgagent_test.
Use the same steps to schedule job for any other databases which are present in same cluster.
The pgAgent also can be used with pgadmin and PEM tools.You may refer below documentation links for more information on this: