pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedChecking replication slave state
2+ messages / 2 participants
[nested] [flat]
* Checking replication slave state
@ 2014-01-17 16:12 Mario Splivalo <mario@splivalo.hr>
2014-01-17 16:42 ` Re: Checking replication slave state Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 2+ messages in thread
From: Mario Splivalo @ 2014-01-17 16:12 UTC (permalink / raw)
To: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Checking replication slave state
2014-01-17 16:12 Checking replication slave state Mario Splivalo <mario@splivalo.hr>
@ 2014-01-17 16:42 ` Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 2+ messages in thread
From: Steve Crawford @ 2014-01-17 16:42 UTC (permalink / raw)
To: Mario Splivalo <mario@splivalo.hr>; pgsql-admin
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
|
|
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2014-01-17 16:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-01-17 16:12 Checking replication slave state Mario Splivalo <mario@splivalo.hr>
2014-01-17 16:42 ` Steve Crawford <scrawford@pinpointresearch.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox