pgsql: Allow time delayed standbys and recovery
Allow time delayed standbys and recovery
Set min_recovery_apply_delay to force a delay in recovery apply for commit and
restore point WAL records. Other records are replayed immediately. Delay is
measured between WAL record time and local standby time.
Robert Haas, Fabrízio de Royes Mello and Simon Riggs
Detailed review by Mitsumasa Kondo
Branch
------
master
Details
-------
http://git.postgresql.org/pg/commitdiff/36da3cfb457b77a55582f68208d815f11ac1399e
Modified Files
--------------
doc/src/sgml/recovery-config.sgml | 50 ++++++++++
src/backend/access/transam/recovery.conf.sample | 9 ++
src/backend/access/transam/xlog.c | 119 ++++++++++++++++++++++-
3 files changed, 174 insertions(+), 4 deletions(-)
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
--On 12. Dezember 2013 10:56:36 +0000 Simon Riggs <simon@2ndQuadrant.com>
wrote:
Set min_recovery_apply_delay to force a delay in recovery apply for
commit and restore point WAL records. Other records are replayed
immediately. Delay is measured between WAL record time and local standby
time.
There's a small typo in the first paragraph, "paramater" should be
"parameter":
diff --git a/doc/src/sgml/recovery-config.sgml
b/doc/src/sgml/recovery-config.sgml
new file mode 100644
index ee5dc86..550cdce
*** a/doc/src/sgml/recovery-config.sgml
--- b/doc/src/sgml/recovery-config.sgml
*************** restore_command = 'copy "C:\\server\\arc
*** 152,158 ****
By default, a standby server keeps restoring WAL records from the
primary as soon as possible. It may be useful to have a
time-delayed
copy of the data, offering various options to correct data loss
errors.
! This paramater allows you to delay recovery by a fixed period of
time,
specified in milliseconds if no unit is specified. For example,
if
you set this parameter to <literal>5min</literal>, the standby
will
replay each transaction commit only when the system time on the
standby
--- 152,158 ----
By default, a standby server keeps restoring WAL records from the
primary as soon as possible. It may be useful to have a
time-delayed
copy of the data, offering various options to correct data loss
errors.
! This parameter allows you to delay recovery by a fixed period of
time,
specified in milliseconds if no unit is specified. For example,
if
you set this parameter to <literal>5min</literal>, the standby
will
replay each transaction commit only when the system time on the
standby--
Thanks
Bernd
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
On Thu, Dec 19, 2013 at 7:03 PM, Bernd Helmle <mailings@oopsware.de> wrote:
--On 12. Dezember 2013 10:56:36 +0000 Simon Riggs <simon@2ndQuadrant.com>
wrote:Set min_recovery_apply_delay to force a delay in recovery apply for
commit and restore point WAL records. Other records are replayed
immediately. Delay is measured between WAL record time and local standby
time.There's a small typo in the first paragraph, "paramater" should be
"parameter":
Thanks for the report! Committed.
Regards,
--
Fujii Masao
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
On 12 December 2013 10:56, Simon Riggs <simon@2ndquadrant.com> wrote:
Allow time delayed standbys and recovery
Set min_recovery_apply_delay to force a delay in recovery apply for commit and
restore point WAL records. Other records are replayed immediately. Delay is
measured between WAL record time and local standby time.Robert Haas, Fabrízio de Royes Mello and Simon Riggs
Detailed review by Mitsumasa Kondo
Could a note be added about the additional disk space requirements for
WAL on the standby? If one is delaying replay, WAL files created over
the delayed period will build up in pg_xlog on the standby until
they're consumed upon replay, so considerations about storing those
additional files will need to be made. It may otherwise trip up
anyone who, say, delays replay by 24 hours in a busy production
environment, and finds they have run out of disk space and no longer
have a valid standby.
--
Thom
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
On 19 December 2013 19:19, Thom Brown <thom@linux.com> wrote:
On 12 December 2013 10:56, Simon Riggs <simon@2ndquadrant.com> wrote:
Allow time delayed standbys and recovery
Set min_recovery_apply_delay to force a delay in recovery apply for commit and
restore point WAL records. Other records are replayed immediately. Delay is
measured between WAL record time and local standby time.Robert Haas, Fabrízio de Royes Mello and Simon Riggs
Detailed review by Mitsumasa KondoCould a note be added about the additional disk space requirements for
WAL on the standby? If one is delaying replay, WAL files created over
the delayed period will build up in pg_xlog on the standby until
they're consumed upon replay, so considerations about storing those
additional files will need to be made. It may otherwise trip up
anyone who, say, delays replay by 24 hours in a busy production
environment, and finds they have run out of disk space and no longer
have a valid standby.
Thanks Thom, good point. I will add a note to the docs.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
On 19 December 2013 19:33, Simon Riggs <simon@2ndquadrant.com> wrote:
On 19 December 2013 19:19, Thom Brown <thom@linux.com> wrote:
On 12 December 2013 10:56, Simon Riggs <simon@2ndquadrant.com> wrote:
Allow time delayed standbys and recovery
Set min_recovery_apply_delay to force a delay in recovery apply for
commit and
restore point WAL records. Other records are replayed immediately.
Delay is
measured between WAL record time and local standby time.
Robert Haas, Fabrízio de Royes Mello and Simon Riggs
Detailed review by Mitsumasa KondoCould a note be added about the additional disk space requirements for
WAL on the standby? If one is delaying replay, WAL files created over
the delayed period will build up in pg_xlog on the standby until
they're consumed upon replay, so considerations about storing those
additional files will need to be made. It may otherwise trip up
anyone who, say, delays replay by 24 hours in a busy production
environment, and finds they have run out of disk space and no longer
have a valid standby.Thanks Thom, good point. I will add a note to the docs.
Just a reminder that this note hasn't been added to the docs yet.
--
Thom
On 26 February 2014 13:07, Thom Brown <thom@linux.com> wrote:
On 19 December 2013 19:33, Simon Riggs <simon@2ndquadrant.com> wrote:
On 19 December 2013 19:19, Thom Brown <thom@linux.com> wrote:
On 12 December 2013 10:56, Simon Riggs <simon@2ndquadrant.com> wrote:
Allow time delayed standbys and recovery
Set min_recovery_apply_delay to force a delay in recovery apply for
commit and
restore point WAL records. Other records are replayed immediately.
Delay is
measured between WAL record time and local standby time.Robert Haas, Fabrízio de Royes Mello and Simon Riggs
Detailed review by Mitsumasa KondoCould a note be added about the additional disk space requirements for
WAL on the standby? If one is delaying replay, WAL files created over
the delayed period will build up in pg_xlog on the standby until
they're consumed upon replay, so considerations about storing those
additional files will need to be made. It may otherwise trip up
anyone who, say, delays replay by 24 hours in a busy production
environment, and finds they have run out of disk space and no longer
have a valid standby.Thanks Thom, good point. I will add a note to the docs.
Just a reminder that this note hasn't been added to the docs yet.
Please cut a doc patch and I'll apply.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
On 26 February 2014 13:32, Simon Riggs <simon@2ndquadrant.com> wrote:
On 26 February 2014 13:07, Thom Brown <thom@linux.com> wrote:
On 19 December 2013 19:33, Simon Riggs <simon@2ndquadrant.com> wrote:
On 19 December 2013 19:19, Thom Brown <thom@linux.com> wrote:
On 12 December 2013 10:56, Simon Riggs <simon@2ndquadrant.com> wrote:
Allow time delayed standbys and recovery
Set min_recovery_apply_delay to force a delay in recovery apply for
commit and
restore point WAL records. Other records are replayed immediately.
Delay is
measured between WAL record time and local standby time.Robert Haas, Fabrízio de Royes Mello and Simon Riggs
Detailed review by Mitsumasa KondoCould a note be added about the additional disk space requirements for
WAL on the standby? If one is delaying replay, WAL files created over
the delayed period will build up in pg_xlog on the standby until
they're consumed upon replay, so considerations about storing those
additional files will need to be made. It may otherwise trip up
anyone who, say, delays replay by 24 hours in a busy production
environment, and finds they have run out of disk space and no longer
have a valid standby.Thanks Thom, good point. I will add a note to the docs.
Just a reminder that this note hasn't been added to the docs yet.
Please cut a doc patch and I'll apply.
Attached.
--
Thom
Attachments:
recovery_delay_wal_note.patchtext/x-patch; charset=US-ASCII; name=recovery_delay_wal_note.patchDownload
diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml
index b69ce28..813db5f 100644
--- a/doc/src/sgml/recovery-config.sgml
+++ b/doc/src/sgml/recovery-config.sgml
@@ -182,6 +182,12 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows
the standby will end recovery without further waiting.
</para>
<para>
+ WAL records generated over the delay period need to be stored on the
+ standby until they can be applied. Therefore long delays can result in
+ a build-up of WAL files, increasing disk space requirements for the
+ <filename>pg_xlog</> directory.
+ </para>
+ <para>
This parameter is intended for use with streaming replication deployments,
however, if the parameter is specified it will be honoured in all cases.
Synchronous replication is not affected by this setting because there is
On Wed, 26 Feb 2014 at 13:55, Thom Brown <thom@linux.com> wrote:
On 26 February 2014 13:32, Simon Riggs <simon@2ndquadrant.com> wrote:
On 26 February 2014 13:07, Thom Brown <thom@linux.com> wrote:
On 19 December 2013 19:33, Simon Riggs <simon@2ndquadrant.com> wrote:
On 19 December 2013 19:19, Thom Brown <thom@linux.com> wrote:
On 12 December 2013 10:56, Simon Riggs <simon@2ndquadrant.com> wrote:
Allow time delayed standbys and recovery
Set min_recovery_apply_delay to force a delay in recovery apply for
commit and
restore point WAL records. Other records are replayed immediately.
Delay is
measured between WAL record time and local standby time.Robert Haas, Fabrízio de Royes Mello and Simon Riggs
Detailed review by Mitsumasa KondoCould a note be added about the additional disk space requirements for
WAL on the standby? If one is delaying replay, WAL files created over
the delayed period will build up in pg_xlog on the standby until
they're consumed upon replay, so considerations about storing those
additional files will need to be made. It may otherwise trip up
anyone who, say, delays replay by 24 hours in a busy production
environment, and finds they have run out of disk space and no longer
have a valid standby.Thanks Thom, good point. I will add a note to the docs.
Just a reminder that this note hasn't been added to the docs yet.
Please cut a doc patch and I'll apply.
Attached.
I know it's been 8 years, but I still think it would be a useful note
to add to the docs.
--
Thom
On Tue, Apr 5, 2022 at 8:41 AM Thom Brown <thom@linux.com> wrote:
I know it's been 8 years, but I still think it would be a useful note
to add to the docs.
Makes sense. I will do this soon if nobody objects.
I'm mildly uncomfortable with the phrase "WAL records generated over
the delay period" because it seems a bit imprecise, but I'm not sure
what would be better and I think the meaning is clear.
Also one of us will need to do s/pg_xlog/pg_wal/.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Tue, Apr 5, 2022 at 4:54 PM Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Apr 5, 2022 at 8:41 AM Thom Brown <thom@linux.com> wrote:
I know it's been 8 years, but I still think it would be a useful note
to add to the docs.
Many points for bringing that one back :)
Makes sense. I will do this soon if nobody objects.
I'm mildly uncomfortable with the phrase "WAL records generated over
the delay period" because it seems a bit imprecise, but I'm not sure
what would be better and I think the meaning is clear.
Maybe "during" instead of "over"? But I'm not sure that's the part you're
referring to?
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
On Tue, Apr 5, 2022 at 10:58 AM Magnus Hagander <magnus@hagander.net> wrote:
Makes sense. I will do this soon if nobody objects.
I'm mildly uncomfortable with the phrase "WAL records generated over
the delay period" because it seems a bit imprecise, but I'm not sure
what would be better and I think the meaning is clear.Maybe "during" instead of "over"? But I'm not sure that's the part you're referring to?
Yeah, something like that, maybe.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Tue, 5 Apr 2022 at 16:02, Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Apr 5, 2022 at 10:58 AM Magnus Hagander <magnus@hagander.net> wrote:
Makes sense. I will do this soon if nobody objects.
I'm mildly uncomfortable with the phrase "WAL records generated over
the delay period" because it seems a bit imprecise, but I'm not sure
what would be better and I think the meaning is clear.Maybe "during" instead of "over"? But I'm not sure that's the part you're referring to?
Yeah, something like that, maybe.
I share your discomfort with the wording. How about:
WAL records must be kept on standby until they are ready to be applied.
Therefore, longer delays will result in a greater accumulation of WAL files,
increasing disk space requirements for the standby's <filename>pg_wal</>
directory.
--
Thom
On Wed, 6 Apr 2022 at 01:42, Thom Brown <thom@linux.com> wrote:
On Tue, 5 Apr 2022 at 16:02, Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Apr 5, 2022 at 10:58 AM Magnus Hagander <magnus@hagander.net> wrote:
Makes sense. I will do this soon if nobody objects.
I'm mildly uncomfortable with the phrase "WAL records generated over
the delay period" because it seems a bit imprecise, but I'm not sure
what would be better and I think the meaning is clear.Maybe "during" instead of "over"? But I'm not sure that's the part you're referring to?
Yeah, something like that, maybe.
I share your discomfort with the wording. How about:
WAL records must be kept on standby until they are ready to be applied.
Therefore, longer delays will result in a greater accumulation of WAL files,
increasing disk space requirements for the standby's <filename>pg_wal</>
directory.
*must be kept on the standby
--
Thom
On Tue, Apr 5, 2022 at 8:43 PM Thom Brown <thom@linux.com> wrote:
I share your discomfort with the wording. How about:
WAL records must be kept on standby until they are ready to be applied.
Therefore, longer delays will result in a greater accumulation of WAL files,
increasing disk space requirements for the standby's <filename>pg_wal</>
directory.
Looks awesome.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Wed, Apr 6, 2022 at 8:15 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Apr 5, 2022 at 8:43 PM Thom Brown <thom@linux.com> wrote:
I share your discomfort with the wording. How about:
WAL records must be kept on standby until they are ready to be applied.
Therefore, longer delays will result in a greater accumulation of WAL files,
increasing disk space requirements for the standby's <filename>pg_wal</>
directory.Looks awesome.
Here that is in patch form. I feel that the feature freeze should not
preclude committing this documentation improvement, but if someone
feels otherwise, then I will leave this until the tree reopens.
--
Robert Haas
EDB: http://www.enterprisedb.com
Attachments:
v1-0001-docs-Note-the-recovery_min_apply_delay-bloats-pg_.patchapplication/octet-stream; name=v1-0001-docs-Note-the-recovery_min_apply_delay-bloats-pg_.patchDownload
From 8ad6da893be6873650e08a524578554fd6e273fa Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Fri, 8 Apr 2022 09:34:42 -0400
Subject: [PATCH v1] docs: Note the recovery_min_apply_delay bloats pg_wal.
Those WAL files that we're waiting to apply have to be stored
somewhere.
Thom Brown
Discussion: http://postgr.es/m/CAA-aLv4SkJRK6GGcd0Axt8kt6_eWMEbtG7f8NJpFh+rNshtdNA@mail.gmail.com
---
doc/src/sgml/config.sgml | 6 ++++++
1 file changed, 6 insertions(+)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 6e3e27bed7..9b4bf83bd9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4872,6 +4872,12 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
state, until the standby is promoted or triggered. After that the standby
will end recovery without further waiting.
</para>
+ <para>
+ WAL records must be kept on standby until they are ready to be applied.
+ Therefore, longer delays will result in a greater accumulation of WAL
+ files, increasing disk space requirements for the standby's
+ <filename>pg_wal</filename> directory.
+ </para>
<para>
This parameter is intended for use with streaming replication deployments;
however, if the parameter is specified it will be honored in all cases
--
2.24.3 (Apple Git-128)
On Fri, Apr 8, 2022 at 3:36 PM Robert Haas <robertmhaas@gmail.com> wrote:
On Wed, Apr 6, 2022 at 8:15 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Apr 5, 2022 at 8:43 PM Thom Brown <thom@linux.com> wrote:
I share your discomfort with the wording. How about:
WAL records must be kept on standby until they are ready to be applied.
Therefore, longer delays will result in a greater accumulation of WALfiles,
increasing disk space requirements for the standby's
<filename>pg_wal</>
directory.
Looks awesome.
Here that is in patch form. I feel that the feature freeze should not
preclude committing this documentation improvement, but if someone
feels otherwise, then I will leave this until the tree reopens.
We normally allow documentation and bug fixes after the feature freeze.
(It's only in the "we're about to wrap the release right now"-freeze that
we have to avoid those)
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
On Fri, 8 Apr 2022, 14:36 Robert Haas, <robertmhaas@gmail.com> wrote:
On Wed, Apr 6, 2022 at 8:15 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Apr 5, 2022 at 8:43 PM Thom Brown <thom@linux.com> wrote:
I share your discomfort with the wording. How about:
WAL records must be kept on standby until they are ready to be applied.
Therefore, longer delays will result in a greater accumulation of WALfiles,
increasing disk space requirements for the standby's
<filename>pg_wal</>
directory.
Looks awesome.
Here that is in patch form. I feel that the feature freeze should not
preclude committing this documentation improvement, but if someone
feels otherwise, then I will leave this until the tree reopens.
Thanks. This doesn't include my self-correction:
s/kept on standby/kept on the standby/
Thom
Show quoted text
On Fri, Apr 8, 2022 at 10:45 AM Thom Brown <thom@linux.com> wrote:
Thanks. This doesn't include my self-correction:
s/kept on standby/kept on the standby/
Here is v2, endeavoring to rectify that oversight.
--
Robert Haas
EDB: http://www.enterprisedb.com
Attachments:
v2-0001-docs-Note-the-recovery_min_apply_delay-bloats-pg_.patchapplication/octet-stream; name=v2-0001-docs-Note-the-recovery_min_apply_delay-bloats-pg_.patchDownload
From 04d3d97e401be40cfec50a457d60236118a64db0 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Fri, 8 Apr 2022 11:02:59 -0400
Subject: [PATCH v2] docs: Note the recovery_min_apply_delay bloats pg_wal.
Those WAL files that we're waiting to apply have to be stored
somewhere.
Thom Brown
Discussion: http://postgr.es/m/CAA-aLv4SkJRK6GGcd0Axt8kt6_eWMEbtG7f8NJpFh+rNshtdNA@mail.gmail.com
---
doc/src/sgml/config.sgml | 6 ++++++
1 file changed, 6 insertions(+)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 6e3e27bed7..42d6e85d34 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4872,6 +4872,12 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
state, until the standby is promoted or triggered. After that the standby
will end recovery without further waiting.
</para>
+ <para>
+ WAL records must be kept on the standby until they are ready to be
+ applied. Therefore, longer delays will result in a greater accumulation
+ of WAL files, increasing disk space requirements for the standby's
+ <filename>pg_wal</filename> directory.
+ </para>
<para>
This parameter is intended for use with streaming replication deployments;
however, if the parameter is specified it will be honored in all cases
--
2.24.3 (Apple Git-128)
On Fri, Apr 8, 2022 at 11:10 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Apr 8, 2022 at 10:45 AM Thom Brown <thom@linux.com> wrote:
Thanks. This doesn't include my self-correction:
s/kept on standby/kept on the standby/
Here is v2, endeavoring to rectify that oversight.
Committed.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Mon, 11 Apr 2022, 15:55 Robert Haas, <robertmhaas@gmail.com> wrote:
On Fri, Apr 8, 2022 at 11:10 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Apr 8, 2022 at 10:45 AM Thom Brown <thom@linux.com> wrote:
Thanks. This doesn't include my self-correction:
s/kept on standby/kept on the standby/
Here is v2, endeavoring to rectify that oversight.
Committed.
Much appreciated.
Thom