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