On 01/17/2014 08:12 AM, Mario Splivalo wrote:
Hello.
What would be the preferred/best way to check replication state of a slave?
When I fire up slave, it can either be in 'can not connect to master'
state, it can be in 'catching up' state, or it can be in 'streaming' state.
I can check what pg_is_in_recovery() function returns, but this will
return true regardless of weather server is catching up, failing to
catch up or is in streaming replication with the master.
Is there a way, other then checking the logs, to verify that?
Mario
If you want to know if the standby is reasonably up-to-date take a look at some of the system administration functions:
http://www.postgresql.org/docs/current/static/functions-admin.html Among my monitoring scripts is one that does a query that uses pg_last_xact_replay_timestamp(), pg_last_xlog_receive_location() and
pg_last_xlog_replay_location() to report a lag. Specifically, if receive and replay locations are the same then it returns zero otherwise it reports the time between the current timestamp and the replay_timestamp to get an approximate lag.
As an additional cross-check I have a roll-your-own check consisting of a "sync_status" table with a single row and single timestamptz column which is updated every minute on the master so there will always be some traffic to the master. The standby server(s) verify that the timestamp in that table is (reasonably) recent.
Failure to connect to a standby or excessive lag set off alarms.
Since my standby is not handling load it is virtually always up-to-date and rarely more than a few seconds behind the master.
Cheers,
Steve