pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedconfusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay
5+ messages / 3 participants
[nested] [flat]
* confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay
@ 2023-03-11 20:23 Atul Kumar <akumar14871@gmail.com>
2023-03-11 20:54 ` Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Ron <ronljohnsonjr@gmail.com>
2023-03-12 14:01 ` Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 5+ messages in thread
From: Atul Kumar @ 2023-03-11 20:23 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay
2023-03-11 20:23 confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Atul Kumar <akumar14871@gmail.com>
@ 2023-03-11 20:54 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Ron @ 2023-03-11 20:54 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay
2023-03-11 20:23 confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Atul Kumar <akumar14871@gmail.com>
@ 2023-03-12 14:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 17:00 ` Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2023-03-12 14:01 UTC (permalink / raw)
To: Atul Kumar <akumar14871@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay
2023-03-11 20:23 confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Atul Kumar <akumar14871@gmail.com>
2023-03-12 14:01 ` Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-03-12 17:00 ` Ron <ronljohnsonjr@gmail.com>
2023-03-12 18:04 ` Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Ron @ 2023-03-12 17:00 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay
2023-03-11 20:23 confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Atul Kumar <akumar14871@gmail.com>
2023-03-12 14:01 ` Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 17:00 ` Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Ron <ronljohnsonjr@gmail.com>
@ 2023-03-12 18:04 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2023-03-12 18:04 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-03-12 18:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-11 20:23 confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Atul Kumar <akumar14871@gmail.com>
2023-03-11 20:54 ` Ron <ronljohnsonjr@gmail.com>
2023-03-12 14:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 17:00 ` Ron <ronljohnsonjr@gmail.com>
2023-03-12 18:04 ` Laurenz Albe <laurenz.albe@cybertec.at>
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