pgsql: Allow time delayed standbys and recovery

--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 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.

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 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.

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 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.

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 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.

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

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 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.

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/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

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

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 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.

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/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

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 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.

Thanks. This doesn't include my self-correction:

s/kept on standby/kept on the standby/

Thom

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

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