This can be achieved by comparing pg_last_xlog_receive_location() and
pg_last_xlog_replay_location() on the slave, and if they are the same it
returns 0, otherwise it runs the above query again:
SELECT
CASE
WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
ELSE EXTRACT (EPOCH FROM now() – pg_last_xact_replay_timestamp())::INTEGER
END
AS replication_lag;
This query is all good, but the problem is that it is not safe. If for some
reason the master stops sending transaction logs, this query will continue
to return 0 and you will think the replication is working, when it is not.
postgres 
