I'm following the documentation here (using postgresql 9.4.5):
https://www.postgresql.org/docs/9.4/static/functions-admin.html
I'm attempting to fully understand the interplay between pg_is_in_recovery() + pg_last_xlog_receive_location() + pg_last_xlog_replay_location() so we can devise a reliable health check script.
Here's a database that is configured as a hot standby for streaming replication.
appdb=> 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/70A4C88 (1 row)
Note that the DB is in recovery, but that pg_last_xlog_receive_location is NULL while pg_last_xlog_replay_location is 0/70A4C88.
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?