3

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.

2

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.

  • That's helpful, but I'm looking for techniques to figure out why replication is slow, not just that it is. – Jeremy Wilson May 9 '14 at 0:50
1

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.

  • 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? – Jeremy Wilson May 8 '14 at 19:20
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.