Re: Conflict with recovery on PG version 11.6 - Mailing list pgsql-general

Hi!

What is difference between following error messages?

ERROR:  canceling statement due to conflict with recovery
FATAL:  terminating connection due to conflict with recovery

I tried to correlate process termination on standby and vacuuming on master.

Here is a sample timeline
1) 2020-06-17 22:45:42 - Last vacuuming before session
2) 2020-06-18 01:01:12 - Session was started at standby
3) 2020-06-18 01:27:53 - Session was terminated at standby
4) 2020-06-18 02:59:32 - Next vacuuming

Based on that I would say there is no correlation between vacuuming and process termination.

How can I identify where is the conflict that stops replication and forces to terminate a query on standby host?

BR,
Toomas

> On 17. Jun 2020, at 16:38, Toomas Kristin <toomas.kristin@gmail.com> wrote:
>
> Hi Laurenz,
>
> Thank you!
>
> What are reasons for conflicts? Based on documentation seems that the only reason can be that vacuum removed unused
tuplesthat are in use at standby host and due to that standby host cannot apply modifications while blocking query
eitherfinishes or will be terminated. isnt it? Or there can be some other reasons? 
>
> I just wondering what would be impact when I increase value for autovacuum_vacuum_scale_factor in order force
vacuumingprocess postpone the clean up process. 
>
> BR,
> Toomas
>
>> On 17. Jun 2020, at 12:42, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>>
>> On Wed, 2020-06-17 at 08:42 +0300, Toomas Kristin wrote:
>>> Basically after upgrade to version 11.5 from 10.6 I experience error messages on streaming
>>> replica host “FATAL:  terminating connection due to conflict with recovery” and
>>> “ERROR: canceling statement due to conflict with recovery”. There is no changes for
>>> vacuuming on master nor max_standby_streaming_delay for replica. I tried to correlate
>>> errors with vacuuming process on master but according to logs there is no link between
>>> them. Somehow I have feeling that when query runs longer than value for parameter
>>> max_standby_streaming_delay the query will be terminated regardless vacuuming process on master.
>>>
>>> Is there any changes on version 11.5 what may cause it?
>>>
>>> Is there any good solution without setting max_standby_streaming_delay=-1 or enabling hot_standby_feedback?
>>
>> The basic behavior shouldn't have changed since v10.
>>
>> Check "pg_stat_database_conflicts" to see what kinds of conflicts that are.
>>
>> The only solutions to avoid queries being canceled due to replication conflicts are:
>>
>> 1. avoid that such conflicts happen:
>>  - set "hot_standby_feedback = on" on the standby and/or
>>    "vacuum_defer_cleanup_age" on the primary to avoid VACUUM conflicts
>>  - Don't lock tables in access exclusive mode
>>
>> 2. set "max_standby_streaming_delay" to -1
>>
>> Note that it can be quite hard to completely avoid replication conflicts.
>> Trying to have both no delay in applying changes and no cancelled queries
>> is often not possible without seriously crippling autovacuum.
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
>