Re: Correct query to check streaming replication lag - Mailing list pgsql-general

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

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