Subject: Re: Streaming Replication replay lag
To: Nikhil Shetty <nikhil.dba04@gmail.com>
Cc: pgsql-admin@postgresql.org
References: 
 <CAFpL5Vzpj7nWvpt-v_2-_DdywQ9vZjh0T5L6gPjcs2fuB7L8NQ@mail.gmail.com>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Message-ID: <3df84e65-5bef-53eb-fe76-248677ed37b6@sqlexec.com>
Date: Tue, 24 Nov 2020 09:38:09 -0500
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/7.0.38
MIME-Version: 1.0
In-Reply-To: 
 <CAFpL5Vzpj7nWvpt-v_2-_DdywQ9vZjh0T5L6gPjcs2fuB7L8NQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

Check replication settings:
max_standby_archive_delay
max_standby_streaming_delay
hot_standby_feedback

These can delay wals being applied to the replica while queries are 
still running.

Regards,
Michael Vitale

Nikhil Shetty wrote on 11/24/2020 9:24 AM:
> Hi,
>
> 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.
>
> Attaching replay and receive wal difference.
>