Thread: 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(-)
--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
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
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
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
On 19 December 2013 19:33, Simon Riggs <simon@2ndquadrant.com> wrote:
Thanks Thom, good point. I will add a note to the docs.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.
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
On 26 February 2014 13:32, Simon Riggs <simon@2ndquadrant.com> wrote:
Please cut a doc patch and I'll apply.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.
Attached.
Thom
Attachment
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