Message-ID: <3c462aa5a2da6b00f52fa90b69b7a542011cd924.camel@cybertec.at>
Subject: Re: Streaming Replication replay lag
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Nikhil Shetty <nikhil.dba04@gmail.com>, pgsql-admin@postgresql.org
Date: Tue, 24 Nov 2020 17:05:29 +0100
In-Reply-To: 
 <CAFpL5Vzpj7nWvpt-v_2-_DdywQ9vZjh0T5L6gPjcs2fuB7L8NQ@mail.gmail.com>
References: 
	<CAFpL5Vzpj7nWvpt-v_2-_DdywQ9vZjh0T5L6gPjcs2fuB7L8NQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-1.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Tue, 2020-11-24 at 19:54 +0530, Nikhil Shetty wrote:
> We have one Primary and three Standby Postgresql setup. Two standby in DC and one standby in DR.
> 
> We have configured streaming replication.When there are a lot of write transactions
>  in the master, we observed that DR lags behind the master for 5-6 hours. We initially
>  thought this could be because of the slow network between DC and DR and this is normal
>  but when we saw the replay_lsn and receive_lsn , they had a huge difference.
> 
> DR standby was receiving the WAL's in time and there was no delay, the delay was in
>  replaying the lag. Can anybody point me to where I can start with the investigation?
> DC (Master and 2 Standby) and DR(Standby) have the same setup in terms of storage
>  and some database configuration parameters but DR has a little less CPU and RAM.

If there is a delay replaying WAL, the cause is probably a replication conflict.

Set "hot_standby = off" on the standby to avoid the problem totally.

If you want queries on the standby, set "max_standby_streaming_delay" to 0
to keep replication from falling behind.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com