postgreSQL 9.3 streaming replication delay

Question

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.

Answer 1

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.

• Jeremy Wilson: That's helpful, but I'm looking for techniques to figure out why replication is slow, not just that it is.

Answer 2

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.

• Jeremy Wilson: It's busy enough to get an update per second so I'd like to figure out what's causing the delay. Are there any diagnostics I can do to investigate replication speed?

Answer 3

I believe the difference is likely to be due to a difference in the clocks on each server.