Обсуждение: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay
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.
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.
Regards.
On 3/11/23 14:23, Atul Kumar wrote:
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.
Hi,Could someone help me in telling the difference between these three parameters1. max_standby_archive_delay2. max_standby_streaming_delay3. recovery_min_apply_delayMy 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.
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.
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