From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay
Date: Sun, 12 Mar 2023 12:00:26 -0500
Message-ID: <a1d9191e-49df-741e-2c42-7e1d381fbe7c@gmail.com> (raw)
In-Reply-To: <065aeb58c2593e4087b42556de0a4af2a9ab2819.camel@cybertec.at>
References: <CA+ONtZ62CK2mu75N9A3zzqQ_mffoHaRPiQit11xMTF2OC_zYGA@mail.gmail.com>
<065aeb58c2593e4087b42556de0a4af2a9ab2819.camel@cybertec.at>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay
In-Reply-To: <a1d9191e-49df-741e-2c42-7e1d381fbe7c@gmail.com>
References: <CA+ONtZ62CK2mu75N9A3zzqQ_mffoHaRPiQit11xMTF2OC_zYGA@mail.gmail.com> <065aeb58c2593e4087b42556de0a4af2a9ab2819.camel@cybertec.at> <a1d9191e-49df-741e-2c42-7e1d381fbe7c@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox