Re: Correct query to check streaming replication lag

On Tue, Jan 21, 2014 at 12:41 PM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>wrote:

>
> On Mon, Jan 20, 2014 at 1:53 PM, Granthana Biswas <granthana(at)zedo(dot)com>wrote:
>
>> Yes we already do that. Count the number of ready wal files.
>
>
> I guess a better place to check would be pg_stat_replication
>
>
> Check this discussion:
> http://www.postgresql.org/message-id/4F13ED11.6080001@gmail.com
>
>
> Another way is explained in wiki:
> http://wiki.postgresql.org/wiki/Streaming_Replication
>
> $ psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10 (primary host)
> pg_current_xlog_location
> --------------------------
> 0/2000000
> (1 row)
>
> $ psql -c "select pg_last_xlog_receive_location()" -h192.168.0.20 (standby host)
> pg_last_xlog_receive_location
> -------------------------------
> 0/2000000
> (1 row)
>
> $ psql -c "select pg_last_xlog_replay_location()" -h192.168.0.20 (standby host)
> pg_last_xlog_replay_location
> ------------------------------
> 0/2000000
> (1 row)
>
>
> Mind you, here is a simple suggestion:
SELECT application_name, pg_xlog_location_diff(sent_location,
flush_location) AS replay_delta, sync_priority, sync_state FROM
pg_stat_replication ORDER BY replay_delta ASC, application_name;
Regards
--
Michael