We are building a new streaming replication setup on postgres 9.3 integrated with pgpool for load balancing and failover, and repmgr for replication business. We have seperate daemons running on all postgres nodes and pgpool instances. They basically check status and run remotely called operations (like "go master" or delayed STONITH tasks).
Investigating the scenarios, we have come to a position where we lose the master for good. We need to continue the operation so one of the standbys must be the new master. We are assuming that one of them has the most up-to-date data coming from the master. The question is how to find the most up-to-date node.
Here are some options i can foresee ,
- tool like xlogdump for examining wal
- methods like Josh Berkus mentioned here http://www.databasesoup.com/2012/10/determining-furthest-ahead-replica.html
- rendering the pg_xlogs folder's contents
So how can we find the most up-to-date standby on a master fail?