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
