Re: Hot Standby - ERROR: canceling statement due to conflict with recovery

On Sunday, February 27, 2011 11:57:35 am Sean Laurent wrote:
> On Sun, Feb 27, 2011 at 1:04 PM, Jens Wilke <jens(at)wilke(dot)org> wrote:
> > On Sonntag, 27. Februar 2011, Sean Laurent wrote:
> > > Unfortunately, most queries against the hot standby fail. Worse
> >
> > > yet, pg_dump fails:
> > ...
> >
> > > I'm not entirely certain I understand why I'm seeing this. Nor do
> > > I understand how to fix or work around this. Any advice or
> > > suggestions would be greatly appreciated.
> >
> > Long running queries on the standby are a bit tricky, because they
> > might need to see row versions that are already removed on the
> > master.
> > It's well documented:
> > http://www.postgresql.org/docs/9.0/static/hot-standby.html
>
> Right. I read all of that. I guess I just assumed it was possible to create
> a snapshot on the standby so that a longer running on the standby could
> complete. In particular, I was really hoping to run database dumps against
> the standby, not the master.
>
> Thanks.
>
> Sean

From the above link:

"The most common reason for conflict between standby queries and WAL replay is
"early cleanup". Normally, PostgreSQL allows cleanup of old row versions when
there are no transactions that need to see them to ensure correct visibility of
data according to MVCC rules. However, this rule can only be applied for
transactions executing on the master. So it is possible that cleanup on the
master will remove row versions that are still visible to a transaction on the
standby. "

Below that it goes into some possible solutions, the easiest of which to test
would seem to be:

"Another option is to increase vacuum_defer_cleanup_age on the primary server,
so that dead rows will not be cleaned up as quickly as they normally would be.
This will allow more time for queries to execute before they are cancelled on
the standby, without having to set a high max_standby_streaming_delay. However
it is difficult to guarantee any specific execution-time window with this
approach, since vacuum_defer_cleanup_age is measured in transactions executed on
the primary server. "

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com