Re: PostgreSQL 9.2 high replication lag

‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Wednesday, July 14th, 2021 at 5:30 PM, Laurenz Albe laurenz(dot)albe(at)cybertec(dot)at wrote:

On Tue, 2021-07-13 at 20:14 -0700, David G. Johnston wrote:

> > On Tue, Jul 13, 2021 at 8:01 PM Lucas root(at)sud0(dot)nz wrote:
> >

> > > According to the documentation, max_standby_streaming_delay is a configuration parameter
> > >

> > > determining how long a standby server should wait before canceling queries that conflict
> > >

> > > with pending WAL entries received via streaming replication.
> > >

> > > My question then is: Which queries, if the slave can only receive SELECTs?
> >

> > The select queries...
> >

> > Follow the link in the docs for max_stanbdy_steaming_delay to:
> >

> > https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT
> >

> > for the details.
>

> Perhaps you might find the following article interesting, where I tried to
>

> discuss this topic in some depth:
>

> https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/

Thanks!

I have changed max_standby_streaming_delay from -1 to 120s. However, I can still see the replication being delayed for up to 3 minutes quite frequent.

I will try to see if I can find what kind of query is causing that... Maybe via pg_stat_database_conflicts?