Thread: pgAgent and WAL streaming
I am relatively new to PostgreSQL. I recently got PostgreSQL 9.2 up in a redundant WAL streaming configuration. The replication is working well. Failing over to the "standby" server is also working well using the trigger file, for when the "production" server fails.
The question I have is we are looking on using pgAgent to schedule running an SQL script periodically. How does that work in this scenario? I am assuming that the "production" server will just execute the script based on the configured schedule. But the standby user can not execute the same script as that would be duplicated. Does pgAgent know not to run when the database is in "archive" mode automatically? In the event of a "production" failure, the "archive" (now promoted to "production") would have to continue the schedule.
Is there a way to accomplish what we are attempting to do?
The SQL script updates a remote database.
Thanks,
Keith
--As of February 14, 2013 3:10:07 AM +0000, Keith Ouellette is alleged to have said: > The question I have is we are looking on using pgAgent to schedule > running an SQL script periodically. How does that work in this scenario? > I am assuming that the "production" server will just execute the script > based on the configured schedule. But the standby user can not execute > the same script as that would be duplicated. Does pgAgent know not to run > when the database is in "archive" mode automatically? In the event of a > "production" failure, the "archive" (now promoted to "production") would > have to continue the schedule. > > Is there a way to accomplish what we are attempting to do? --As for the rest, it is mine. My solution would be to have the script write a timestamp someplace into the database (probably in a table created just for it), and schedule the 'archive' script to run a minute or two after the production script should complete. If the script sees a recent timestamp, it quits without running. (I would have the same version script on both boxes: The production one wouldn't see the timestamp, so it would run, and if there's ever a case where you need to switch the boxes around, you can do so with little fuss.) Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
Daniel, That is a good idea. I wonder if I am able to start the script to check if it is "production" or "archive", then decideto run or exit out of the script is possible. I will look into that. Thank you. Keith ________________________________________ From: pgsql-novice-owner@postgresql.org [pgsql-novice-owner@postgresql.org] on behalf of Daniel Staal [DStaal@usa.net] Sent: Wednesday, February 13, 2013 10:23 PM To: pgsql-novice@postgresql.org Subject: Re: [NOVICE] pgAgent and WAL streaming --As of February 14, 2013 3:10:07 AM +0000, Keith Ouellette is alleged to have said: > The question I have is we are looking on using pgAgent to schedule > running an SQL script periodically. How does that work in this scenario? > I am assuming that the "production" server will just execute the script > based on the configured schedule. But the standby user can not execute > the same script as that would be duplicated. Does pgAgent know not to run > when the database is in "archive" mode automatically? In the event of a > "production" failure, the "archive" (now promoted to "production") would > have to continue the schedule. > > Is there a way to accomplish what we are attempting to do? --As for the rest, it is mine. My solution would be to have the script write a timestamp someplace into the database (probably in a table created just for it), and schedule the 'archive' script to run a minute or two after the production script should complete. If the script sees a recent timestamp, it quits without running. (I would have the same version script on both boxes: The production one wouldn't see the timestamp, so it would run, and if there's ever a case where you need to switch the boxes around, you can do so with little fuss.) Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. --------------------------------------------------------------- -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice