Re: 9.2 streaming replication issue and solution strategy

On 11/17/2012 06:08 AM, T. E. Lawrence wrote:
> Hello,
>
> We are running 9.2 w/ streaming replication.
>
> The slave is used for heavy tsearch based data mining.
>
> Apparently depending on the business of the master the slave queries fail with different frequency with the following message —
>
> ERROR: canceling statement due to conflict with recovery
> DETAIL: User query might have needed to see row versions that must be removed.
>
> Initially we resolved the problem by introducing a retry (wait 1 second, try), which works in some cases, and fails on other due to too many retries necessary (10+).
>
> After that we introduced a second slave, and told the try to switch slaves (try here, if not, wait 1 second and try there).
>
> Which works almost every time.
>
> So apparently the issues are not synchronous (probably logical as the streaming replication is asynchronous).
>
> Has anybody else experience such an issue and are there different approaches to it?

Have you looked at the below?:

http://www.postgresql.org/docs/9.2/interactive/hot-standby.html#HOT-STANDBY-CONFLICT

25.5.2. Handling Query Conflicts

>
> Than you,
> T.E.L.
>

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