Обсуждение: [GENERAL] Understanding pg_last_xlog_receive_location

On Wed, Mar 1, 2017 at 6:51 AM, Zach Walton <zacwalt@gmail.com> wrote:
> I'm following the documentation here (using postgresql 9.4.5):
> https://www.postgresql.org/docs/9.4/static/functions-admin.html

You should really update to a newer version of 9.4.X, you are missing
more than 1 year of bug fixes by staying on 9.4.5.

> I'm not sure I understand how this is possible. According to the docs,
> pg_last_xlog_receive_location can only be NULL when streaming is disabled
> (not the case) or hasn't started yet (doesn't seem possible when
> pg_last_xlog_replay_location is set).
>
> Could someone help shed some light on what state results in
> pg_last_xlog_receive_location being NULL when pg_last_xlog_replay_location
> is set?

That works for me for a streaming node:
=# SELECT pg_is_in_recovery(), pg_last_xlog_receive_location(),
pg_last_xlog_replay_location();
 pg_is_in_recovery | pg_last_xlog_receive_location |
pg_last_xlog_replay_location
-------------------+-------------------------------+------------------------------
 t                 | 0/30008E0                     | 0/30008E0
(1 row)
--
Michael

On Thu, Mar 2, 2017 at 5:53 AM, Zach Walton <zacwalt@gmail.com> wrote:
> I was able to test 9.4.11 and am seeing the same behavior:
>
> postgres=# SELECT pg_is_in_recovery(), pg_last_xlog_receive_location(),
> pg_last_xlog_replay_location();
>  pg_is_in_recovery | pg_last_xlog_receive_location |
> pg_last_xlog_replay_location
> -------------------+-------------------------------+------------------------------
>  t                 |                               | 0/3000198

Okay, you said that you are using here streaming replication, but the
standby you are performing this query on seems just to be a hot
standby recovering WAL from a WAL archive, not via streaming. I would
bet that there is no WAL receiver running.
pg_last_xlog_receive_location() get the last WAL position received
from a streaming node, something that is set to NULL if there is no
streaming happening, while pg_last_xlog_replay_location() is set by
the startup process when replaying WAL records.

Again I see no bugs here, you should check if a WAL receiver is
running on this standby server.
--
Michael

Thanks Michael- That was indeed the issue. We have a very complex wrapper application that walks the server through multiple state transitions, and it turned out that in the state I was running the query from, streaming replication wasn't configured.