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?

  • Cron. Just let multiple jobs run and compete for a lock. The winner performs the task. – Craig Ringer Mar 11 '13 at 8:51
  • Craig dont want to place that as an answer so i can press the magic button :) – PythonWolf Mar 11 '13 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 '13 at 22:23
  • Craig , just a test env. on Xen Server and pgpoolII for Master/Slave Replication ,Query LB. – PythonWolf Mar 12 '13 at 11:47

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.

  • 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 '13 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 '13 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 '13 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 '13 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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