Thanks for the feedback (appreciated!!).
I'll spend some time looking at the streaming delay. Generally all
connections to the secondary are restricted (with the exception of my nonce
query, which should be very short lived).
-mike
On Mon, Dec 4, 2017 at 2:16 AM, Laurenz Albe
wrote:
> Mike Howland wrote:
> > I have two machines where the secondary is configured as a backup of the
> primary machine.
> > I used the instructions here: https://wiki.postgresql.org/
> wiki/Streaming_Replication
> > to establish streaming replication...
> > BTW, I'm using the 7.1 path (using backup/rsync/!backup) to prime the
> files on the secondary machine.
> >
> > Most of the time it comes up, connects and is quite happy.
> > For my own sanity I have a bit of monitoring code that occasionally
> injects a change
> > on the primary and watches for it to arrive on the secondary to ensure
> data is flowing.
> > I also have some code that checks to see how close things are using
> pg_current_xlog_location()
> > and pg_last_xlog_receive_location(). When things are working, these
> checks are always fine.
> >
> > Once in a while, this nonce value does not seem to be updated on the
> secondary server
> > (although I can see it change on the primary).
> > During this period of time, my watcher code also detects that
> pg_last_xlog_receive_location()
> > is ahead of pg_current_xlog_location(). From what I can tell, the
> secondary has connected to the primary,
> > it's just data does not seem to be flowing. I'll attach a bit of detal
> below.
>
> The way you initialize the standby looks fine.
>
> One explanation for the delay you observe would be if
> "max_standby_streaming_delay"
> is set greater than zero and there are queries running on the standby that
> block
> application of the changes streamed from the primary.
>
> Yours,
> Laurenz Albe
>