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.kris...@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 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? > > 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. > > BR, > Toomas > >> On 17. Jun 2020, at 12:42, Laurenz Albe <laurenz.a...@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 >> >
