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
