Re: Correct query to check streaming replication lag

[Michael Paquier <michael.paquier@xxxxxxxxx>]

On Tue, Jan 21, 2014 at 12:41 PM, Sameer Kumar <sameer.kumar@xxxxxxxxxx> wrote:

On Mon, Jan 20, 2014 at 1:53 PM, Granthana Biswas <granthana@xxxxxxxx> 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@xxxxxxxxx

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