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.
