I'm running a fairly simple WAL streaming replication setup between two postgreSQL 9.3 servers that are on a private 1Gb LAN, but recently I've noticed that there is an increasing amount of replication delay:
# SELECT extract(epoch from now() - pg_last_xact_replay_timestamp()) AS slave_lag;
slave_lag
-----------
5.50896
The servers aren't particularly busy, so I was wondering what I could do to investigate this problem or possibly fix it.
Have you tried looking at:
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;
The idea here is if both servers are synchronized, then there is no delay. If they are not synchronized, then display the delay.
One potential issue here is if PostgreSQL replication stops, this won't work properly, so you'd need to check periodically by some other mechanism to see if replication is running.
On a non-busy server, that value will not reflect the numbers you are expecting to see. It is literally saying "the last time something was pushed to me was 5.50896 seconds ago". Since your server is not busy, that is most likely the last time a commit happened on your master, not that the slave is delayed.
I believe the difference is likely to be due to a difference in the clocks on each server.
