Thread: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay

On 3/11/23 14:23, Atul Kumar wrote:

Hi,

Could someone help me in telling the difference between these three parameters 

1. max_standby_archive_delay

2. max_standby_streaming_delay

3. recovery_min_apply_delay

My basic motive is to make the standby database server to be delayed to apply the changes on itself,  if any data has been accidentally deleted/updated/ truncated from the primary server.

What you're looking for is that WAL files be "aggregated and optimized" by removing statements which don't have any impact on the final state of instance.

That's great for restoring instances with update-heavy databases, and can only implemented by backup/restore software, or roll-your-own log shipping.  Just as importantly, that aggregation-optimization software has it's own costs (time and disk space), since it takes time to scan through all the recovery logs, keeping track of what must stay, what can be tossed, and the final state.

On Sun, 2023-03-12 at 01:53 +0530, Atul Kumar wrote:
> Could someone help me in telling the difference between these three parameters 
> 1. max_standby_archive_delay
> 2. max_standby_streaming_delay
> 3. recovery_min_apply_delay
>
> My basic motive is to make the standby database server to be delayed to apply the
> changes on itself,  if any data has been accidentally deleted/updated/ truncated
> from the primary server.
>
> Which parameter do I need to configure to serve this purpose ? And 
> When will the remaining two parameters be used ?
>
> It would be great if anyone can explain them with a brief example.

The parameter that does what you describe you want is "recovery_min_apply_delay".

The other parameters only deal with delaying replication in the face of a
replication conflict.

Note that changes are immediately shipped to the standby, what is delayed with
"recovery_min_apply_delay" is only the replay of the WAL information.

So you can recover from a logical problem like DROP TABLE by stopping the
standby, setting "recovery_target_time" to a time before the problem happened
and then restarting the standby.  Then recovery will stop before the problem
is replayed.

Yours,
Laurenz Albe

On 3/12/23 09:01, Laurenz Albe wrote:
> On Sun, 2023-03-12 at 01:53 +0530, Atul Kumar wrote:
>> Could someone help me in telling the difference between these three parameters
>> 1. max_standby_archive_delay
>> 2. max_standby_streaming_delay
>> 3. recovery_min_apply_delay
>>
>> My basic motive is to make the standby database server to be delayed to apply the
>> changes on itself,  if any data has been accidentally deleted/updated/ truncated
>> from the primary server.
>>
>> Which parameter do I need to configure to serve this purpose ? And
>> When will the remaining two parameters be used ?
>>
>> It would be great if anyone can explain them with a brief example.
> The parameter that does what you describe you want is "recovery_min_apply_delay".
>
> The other parameters only deal with delaying replication in the face of a
> replication conflict.
>
> Note that changes are immediately shipped to the standby, what is delayed with
> "recovery_min_apply_delay" is only the replay of the WAL information.
>
> So you can recover from a logical problem like DROP TABLE by stopping the
> standby, setting "recovery_target_time" to a time before the problem happened
> and then restarting the standby.  Then recovery will stop before the problem
> is replayed.

How do you determine what to set recovery_min_apply_delay to? 15 minutes... 
an hour... 8 hours... a week?  (OP's problem is better solved by restoring a 
recent backup to a separate instance, while letting replication do it's 
DR/HA things.)

-- 
Born in Arizona, moved to Babylonia.

On Sun, 2023-03-12 at 12:00 -0500, Ron wrote:
> On 3/12/23 09:01, Laurenz Albe wrote:
> > On Sun, 2023-03-12 at 01:53 +0530, Atul Kumar wrote:
> > > Could someone help me in telling the difference between these three parameters
> > > 1. max_standby_archive_delay
> > > 2. max_standby_streaming_delay
> > > 3. recovery_min_apply_delay
> > >
> > > My basic motive is to make the standby database server to be delayed to apply the
> > > changes on itself,  if any data has been accidentally deleted/updated/ truncated
> > > from the primary server.
> > >
> > > Which parameter do I need to configure to serve this purpose ? And
> > > When will the remaining two parameters be used ?
> > >
> > > It would be great if anyone can explain them with a brief example.
> > The parameter that does what you describe you want is "recovery_min_apply_delay".
> >
> > The other parameters only deal with delaying replication in the face of a
> > replication conflict.
> >
> > Note that changes are immediately shipped to the standby, what is delayed with
> > "recovery_min_apply_delay" is only the replay of the WAL information.
> >
> > So you can recover from a logical problem like DROP TABLE by stopping the
> > standby, setting "recovery_target_time" to a time before the problem happened
> > and then restarting the standby.  Then recovery will stop before the problem
> > is replayed.
>
> How do you determine what to set recovery_min_apply_delay to? 15 minutes...
> an hour... 8 hours... a week?  (OP's problem is better solved by restoring a
> recent backup to a separate instance, while letting replication do it's
> DR/HA things.)

That depends entirely on whether how you want to query the standby.  It is totally
irrelevant for the question of configuring a standby that is deliberately delayed
in order to recover from data loss caused by SQL.

Yours,
Laurenz Albe