Horton

Thread

Commits

  1. docs: Note the recovery_min_apply_delay bloats pg_wal.

  2. Allow time delayed standbys and recovery

  1. pgsql: Allow time delayed standbys and recovery

    Simon Riggs <simon@2ndquadrant.com> — 2013-12-12T10:56:36Z

    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(-)

  2. Re: pgsql: Allow time delayed standbys and recovery

    Bernd Helmle <mailings@oopsware.de> — 2013-12-19T10:03:06Z

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

  3. Re: pgsql: Allow time delayed standbys and recovery

    Fujii Masao <masao.fujii@gmail.com> — 2013-12-19T10:26:47Z

    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

  4. Re: pgsql: Allow time delayed standbys and recovery

    Thom Brown <thom@linux.com> — 2013-12-19T19:19:21Z

    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

  5. Re: pgsql: Allow time delayed standbys and recovery

    Simon Riggs <simon@2ndquadrant.com> — 2013-12-19T19:33:28Z

    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

  6. Re: pgsql: Allow time delayed standbys and recovery

    Thom Brown <thom@linux.com> — 2014-02-26T13:07:51Z

    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

  7. Re: pgsql: Allow time delayed standbys and recovery

    Simon Riggs <simon@2ndquadrant.com> — 2014-02-26T13:32:25Z

    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

  8. Re: pgsql: Allow time delayed standbys and recovery

    Thom Brown <thom@linux.com> — 2014-02-26T13:55:39Z

    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

  9. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Thom Brown <thom@linux.com> — 2022-04-05T12:40:47Z

    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

  10. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Robert Haas <robertmhaas@gmail.com> — 2022-04-05T14:54:30Z

    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

  11. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Magnus Hagander <magnus@hagander.net> — 2022-04-05T14:58:43Z

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

  12. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Robert Haas <robertmhaas@gmail.com> — 2022-04-05T15:01:56Z

    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

  13. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Thom Brown <thom@linux.com> — 2022-04-06T00:42:48Z

    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

  14. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Thom Brown <thom@linux.com> — 2022-04-06T01:04:48Z

    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

  15. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Robert Haas <robertmhaas@gmail.com> — 2022-04-06T12:15:19Z

    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

  16. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Robert Haas <robertmhaas@gmail.com> — 2022-04-08T13:36:27Z

    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

  17. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Magnus Hagander <magnus@hagander.net> — 2022-04-08T13:45:18Z

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

  18. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Thom Brown <thom@linux.com> — 2022-04-08T14:44:51Z

    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

>

  19. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Robert Haas <robertmhaas@gmail.com> — 2022-04-08T15:10:09Z

    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

  20. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Robert Haas <robertmhaas@gmail.com> — 2022-04-11T14:55:00Z

    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

  21. Re: [COMMITTERS] pgsql: Allow time delayed standbys and recovery

    Thom Brown <thom@linux.com> — 2022-04-11T18:51:03Z

    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

>