I have a simple script running on a Postgres 9.2 hot standby server with wal archiving and streaming replication configured which looks like this:
#!/bin/bash
while true
do
psql -tc "SELECT now(),
pg_last_xlog_receive_location(),
pg_last_xlog_replay_location()" >> pg_rep.log
sleep 10
done
It generates an output like this (I manually added headers):
now | pg_last_xlog_receive_location | pg_last_xlog_replay_location
-------------------------------+-------------------------------+-----------------------------
2014-03-18 11:25:59.173662+01 | 38/FE000000 | 38/FE000000
2014-03-18 11:26:09.187664+01 | 39/0 | 38/FF000000
2014-03-18 11:26:19.202041+01 | 39/0 | 38/FF000000
2014-03-18 11:26:29.216177+01 | 39/0 | 38/FF000000
...
2014-03-18 11:30:49.572774+01 | 39/0 | 38/FF000000
2014-03-18 11:30:59.586294+01 | 39/0 | 38/FF000000
2014-03-18 11:31:09.600025+01 | 39/1000000 | 39/1000000
This Postgres instance has only a few writes on the master node every few hours and it is extremely inactive, and this part of the log is when there is no activity on the master.
As you can see from the logs above, when a new xlog is received 39/0, the replayed valued doesn't change for 5 minutes (this time is probably because archive_timeout = 300s), until 39/1000000 is received. Does anyone know why is that? Why isn't the xlog applied when it was received by the stand by?
Regards,
Strahinja