pgAgent for multiple databases in same instance
Hi,
We are using EDB postgres 12 ,and using pgAgent to schedule db jobs , now
we have two databases in the instance but job run only on one of the
databases.
I created a pgAgent extension on both db's , I can submit a job on both
db's too , just it doesnt run on one db until i change dbname in
edb-pgagent.conf.
I tried giving dbname as * but then it stopped working on both the
databases.
I check pgagent.pga_joblog to see if job is running or not , so it gives me
clear idea of jobs running.
We are using EDB EPAS 12.
Do we need multiple instances of pgAgent daemon for multiple db as we tried
running a separate instance of pgAgen by passing 2nd db credentials and
then jobs were running on both db so is that the way to go ?
Regards,
Sandeep
So my experience isn't with pgagent directly, because I have been using my
re-written version of it for ~5 years (but at least at one point I had a
pretty darn good understanding from doing that rewrite)...please take this
with a grain of salt if I am incorrect on anything.
So the agent is only able to connect to a single "db" at a time, so you are
correct you'd need to run multiple agents (connected to the different db's)
to have things work as you intended.
What is the reason for creating the pgagent extensions in multiple
databases? The extension itself is the storage / logs / job definition, so
each extra one is totally detached from the rest. You can still use a
single pgagent install to connect to many databases to do work, so I am
just not sure why you'd need multiple.
Hi Adam ,
Thank you for the such a good explaination , actually in same instance
dba's are looking to create multiple QA databases and we had jobs in oracle
for archiving and some business functionality so I created pgagent
extension in multiple databases ,in our application end users can submit
job from front end so we have procedures to do that submit job operation so
if i understand your explanation correctly.
I should install pgAgent only on one db like edb or postgres db and create
db link in other db's to connect to it to submit jobs in it as those jobs
will be for different db's.
In this case it will be only one pgAgent instance ,so from architecture
point of view does it make sense to you.
Regards,
Sandeep
On Wed, Mar 10, 2021 at 3:16 PM Adam Brusselback <adambrusselback@gmail.com>
wrote:
Show quoted text
So my experience isn't with pgagent directly, because I have been using my
re-written version of it for ~5 years (but at least at one point I had a
pretty darn good understanding from doing that rewrite)...please take this
with a grain of salt if I am incorrect on anything.So the agent is only able to connect to a single "db" at a time, so you
are correct you'd need to run multiple agents (connected to the different
db's) to have things work as you intended.What is the reason for creating the pgagent extensions in multiple
databases? The extension itself is the storage / logs / job definition, so
each extra one is totally detached from the rest. You can still use a
single pgagent install to connect to many databases to do work, so I am
just not sure why you'd need multiple.