Re: Checking replication slave state

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
|
|