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 vacuuming
processpostpone 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
>