Message-ID: <52D95D7F.70303@pinpointresearch.com>
Date: Fri, 17 Jan 2014 08:42:39 -0800
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.2.0
MIME-Version: 1.0
To: Mario Splivalo <mario@splivalo.hr>, pgsql-admin@postgresql.org
Subject: Re: Checking replication slave state
References: <52D95689.7070101@splivalo.hr>
In-Reply-To: <52D95689.7070101@splivalo.hr>
Content-Type: multipart/alternative;
 boundary="------------030504060704080202060208"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------030504060704080202060208
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------030504060704080202060208
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 01/17/2014 08:12 AM, Mario Splivalo
      wrote:<br>
    </div>
    <blockquote cite="mid:52D95689.7070101@splivalo.hr" type="cite">
      <pre wrap="">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


</pre>
    </blockquote>
    <br>
    If you want to know if the standby is reasonably up-to-date take a
    look at some of the system administration functions:<br>
    <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/current/static/functions-admin.html">http://www.postgresql.org/docs/current/static/functions-admin.html</a><br>
    <br>
    Among my monitoring scripts is one that does a query that uses
    pg_last_xact_replay_timestamp(),&nbsp;pg_last_xlog_receive_location() and
    <br>
    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.<br>
    <br>
    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.<br>
    <br>
    Failure to connect to a standby or excessive lag set off alarms.<br>
    <br>
    Since my standby is not handling load it is virtually always
    up-to-date and rarely more than a few seconds behind the master.<br>
    <br>
    Cheers,<br>
    Steve<br>
    <tt class="LITERAL"><code class="FUNCTION"><br>
      </code></tt>
  </body>
</html>

--------------030504060704080202060208--