Re: Conflict with recovery on PG version 11.6

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