Re: Does max_standby_streaming_delay delay all WAL commits?

On Fri, Mar 4, 2016 at 2:43 AM, Dave Johansen <davejohansen(at)gmail(dot)com>
wrote:

> We're running into issues with canceled queries on a standby server and
> have be debating between enabling hot_standby_feedback and setting
> max_standby_streaming_delay to something large.
>
> Using max_standby_streaming_delay seems to have the advantage of keeping
> the master independent of the standby, but will a long running query mean
> that all WAL commits are delayed? Or just those that affected by the long
> running query?
>

If you set max_standby_streaming_delay to a large value, WAL commits are
going to continue until there is a conflict with the running queries on
standby. If a WAL commit encounters a conflict, then the WAL update on
standby will wait for "max_standby_streaming_delay" amount of time and
until then all the subsequent WAL commits will be paused. In otherwords -
entire standby recovery will be paused.

Another alternative would be use "pg_xlog_replay_pause()" and
"pg_xlog_replay_resume()" functions if that is possible with your
application. Execute the "pg_xlog_replay_pause()" - then the query/job -
then "pg_xlog_replay_resume()" function.

Regards,
Venkata B N

Fujitsu Australia