Re: tx canceled on standby despite infinite max_standby_streaming_delay

On Sun, May 15, 2016 at 6:15 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> Its customary to bottom-post (or respond inline) on these lists.
>
> On Sun, May 15, 2016 at 7:01 PM, Jay Howard <jhoward(at)alumni(dot)utexas(dot)net>
> wrote:
>
>> Do you have hot_standby_feedback set to "on" ?
>>>
>>
>> It was off. Will research that. Thank you!
>>
>> What is the parameter max_standby_archive_delay configured to ? This
>>> will pause WAL archives from being applied when queries are executed on the
>>> standby database.
>>>
>>
>> It's set to the default, which is 30 seconds. For some reason I thought
>> setting "max_standby_streaming_delay" to -1 would be sufficient.
>>
>>
> ​At minimum I think there is room for improvement in the documentation
> here since I spent probably a good 15-20 minutes trying to find an answer
> related to either vacuum or WAL accumulation and could not discover
> anything that directly permitted your situation to occur.​
>
> At a high level, what's the difference between the "archive_delay" and
>> "streaming_delay"? I will read up on streaming replication in the mean
>> time.
>>
>>
> ​http://www.postgresql.org/docs/9.5/static/hot-standby.html​
>
> ​"""
> ​
> When a conflicting query is short, it's typically desirable to allow it
> to complete by delaying WAL application for a little bit; but a long delay
> in WAL application is usually not desirable. So the cancel mechanism has
> parameters, max_standby_archive_delay and max_standby_streaming_delay, that
> define the maximum allowed delay in WAL application. Conflicting queries
> will be canceled once it has taken longer than the relevant delay setting
> to apply any newly-received WAL data. There are two parameters so that
> different delay values can be specified for the case of reading WAL data
> from an archive (i.e., initial recovery from a base backup or "catching up"
> a standby server that has fallen far behind) versus reading WAL data via
> streaming replication.
> ​"""
>
> ​David J.
>
>
>
W.r.t. improving the documentation, what I didn't realize is that WAL files
can be placed in the archive (making "max_standby_archive_delay" relevant)
in situations other than just "initial recovery from a base backup".

In my case, there was some heavy-duty stuff happening on the master that
caused the standby to get sufficiently far behind that WAL files were
"archived". Then, while they were being applied from the archive, my
pg_dump ran on the standby.