PostgreSQL 9.3 streaming replication delay

postgresqlpostgresql-9.3replication

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.

Best Answer

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.

Related Question