Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a normal problem to anyone that can't trust on applications to do their 'work/ AKA be online' and needs a DB Stored Procedure to run every minute .

Scenario :

  • PostgreSQL 9.2
  • Need To Run A SP To 'Compile' Information That has been inserted into the database.

  1. No I Can't trust the App to be online and call this on the client side.

  2. Job(s) must execute every minute.

  3. Unfortunately I can't use trigger based or any 'EventBased' Model since I don't know when all the data has arrived.

  4. The cluster is made of headless machines.

  5. I could install CRON on every server but that could cause unnecessary chatter inside the Cluster , DeadLocks , Calls at 3 AM , Pain , etc

Problem 1: Now my problem is PGAgent requires ( As far as I know) a GUI , X and so on things I would like to avoid specially for 'just' running jobs.

Problem 2: Also what if the server that runs the Agent ( call it Master ) dies the cluster will self heal but will the agent survive?

So how do you do it?

share|improve this question
Cron. Just let multiple jobs run and compete for a lock. The winner performs the task. – Craig Ringer Mar 11 at 8:51
Craig dont want to place that as an answer so i can press the magic button :) – PythonWolf Mar 11 at 22:09
Not without having thought it out some more. It'd help if you described your cluster. Is it a group of replicas of a single master using PostgreSQL's built-in replication? Using some other replication system? How is failover managed? – Craig Ringer Mar 11 at 22:23
Craig , just a test env. on Xen Server and pgpoolII for Master/Slave Replication ,Query LB. – PythonWolf Mar 12 at 11:47

1 Answer

up vote 1 down vote

pgAgent does not require a graphical user interface at all. It is a process that runs as daemon on unix. pgAdmin is used for configuring the job details. The configuration is stored in database postgres where pgAgent will read it. Once you configured your jobs, you may disconnect pgAdmin safely.

share|improve this answer
Hi eppesuig. But i can only assume if the server dies the pgagent dies with it, no auto start on the new 'master'. If i would install multiple copies of pgagent would end to the same thing to Cron + Locks + NOWAIT with less dependencies – PythonWolf Mar 11 at 22:09
So, the problem is to start pgagent whenever the database cluster become master. Probably, as you have a cluster of machines for dbms, you need a simple cluster of two machines for pgagent. That cluster would start only one instance of pgagent, and make it connect to postgresql. – eppesuig Mar 12 at 8:20
That could also be a solution since we use Xen server that would handle HOST or VM Failure. But it still feels dirty that you have two more servers to manage – PythonWolf Mar 12 at 11:48
I don't think this would be a "dirty" solution: when you have a very important service, you need HA, so a cluster is a viable solution. Moreover, you want to keep separate all your solution components because you need to change/upgrade/fix/whatever them separately. – eppesuig Mar 15 at 9:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.