Why isn't the xlog applied on a standby server

From: Strahinja Kustudić <strahinjak(at)nordeus(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Why isn't the xlog applied on a standby server
Date: 2014-10-18 13:01:29
Message-ID: CADKbJJULuWmRZeHWxnDVbk_mOKHNhLx-avYvZqMaYoAy6bjF3w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Browse pgsql-admin by date

  From Date Subject
Next Message Shay Gover 2014-10-18 18:06:26 Re: Unable to connect remotely
Previous Message Raghu Ram 2014-10-18 02:23:54 Re: Unable to connect remotely