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. > > What are reasons for conflicts? Based on documentation seems that the only reason can be that vacuum removed > unused tuples that are in use at standby host and due to that standby host cannot apply > modifications while blocking query either finishes or will be terminated. isnt it? Or there can be some other reasons? There can be other reasons: - replicated ACCESS EXCLUSIVE locks that conflict with queries - replicated ACCESS EXCLUSIVE locks that cause deadlocks - buffer pins that are needed for replication but held by a query - dropped tablespaces that hold temporary files on the standby > I just wondering what would be impact when I increase value for autovacuum_vacuum_scale_factor > in order force vacuuming process postpone the clean up process. That won't help, it will just get your primary bloated. I told you the remedies above, why don't you like them? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
