Re: hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?

Steve Kehlet wrote:
> Our queries on our Standby are getting cancelled and so we're investigating
> how to prevent this. The standby is for running periodic reporting queries
> only, we don't care if it falls behind a little bit, we just set this guy
> up to reduce the load on the Primary.
>
> While researching there appear to be several different parameters that can
> help solve this. It sounds like we don't need hot_standby_feedback or
> vacuum_defer_cleanup_age, we really just want to pause things on the
> standby to let it run its queries. So we're going to try
> applying max_standby_archive_delay and max_standby_streaming_delay to 1h or
> so. We're also looking at pg_xlog_replay_pause(), although this is less
> desirable because we don't want to have to ask the people running reports
> to remember to pause and resume things.

Note that not all standby cancellations are the same. For instance if
you're grabbing access exclusive locks on the master and the salve is,
say, taking pg_dump for a long time, no amount of standby feedback or
vacuum deferring will save you.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services