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
onother 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.klaver@gmail.com