Thread: BUG #8269: the recovery_target_inclusive is always considered as false

BUG #8269: the recovery_target_inclusive is always considered as false

From
eyal.kaspi@delphix.com
Date:
The following bug has been logged on the website:

Bug reference:      8269
Logged by:          PITR Recovery by timestamp ignores recovery_target_inclusive
Email address:      eyal.kaspi@delphix.com
PostgreSQL version: 9.2.4
Operating system:   RHEL
Description:

While doing Point-in-time recovery and specifying a timestamp as the target,
I noticed that the timestamp is always considered as exclusive (as if
recovery_target_inclusive was set to false), i.e the recovery process will
always stop before the timestamp and never after. Example:


$cat recovery.conf:
restore_command = 'cp ../archive/%f %p 2>/dev/null'
recovery_target_time = '2013-06-27 17:40:10-0700'
recovery_target_inclusive = true


after recovery in the postgres log file:
...
LOG:  starting point-in-time recovery to 2013-06-27 17:40:10-07
LOG:  restored log file "000000010000002B000000A7" from archive
...
LOG:  recovery stopping before commit of transaction 1962, time 2013-06-27
17:40:15.849203-07
LOG:  redo done at 2B/B0004098
LOG:  last completed transaction was at log time 2013-06-27
17:39:59.047882-07
LOG:  selected new timeline ID: 2


By looking at the source, it appears that the problem is in xlog.c, function
'recoveryStopsHere':
#########
5969: if (recoveryTargetInclusive)
5970:    stopsHere = (recordXtime > recoveryTargetTime);
5971: else
5972:    stopsHere = (recordXtime >= recoveryTargetTime);
5973:if (stopsHere)
5974:    *includeThis = false;
#########


Even if recoveryTargetInclusive is true, includeThis will bet set to false
when reaching the last record, causing the record not to be applied. The
correct solution would be to do what is done for the RECOVERT_TARGET_XID
case


#########
    if (stopsHere)
        *includeThis = recoveryTargetInclusive;
#########
On Sat, Jun 29, 2013 at 7:12 AM,  <eyal.kaspi@delphix.com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      8269
> Logged by:          PITR Recovery by timestamp ignores recovery_target_inclusive
> Email address:      eyal.kaspi@delphix.com
> PostgreSQL version: 9.2.4
> Operating system:   RHEL
> Description:
>
> While doing Point-in-time recovery and specifying a timestamp as the target,
> I noticed that the timestamp is always considered as exclusive (as if
> recovery_target_inclusive was set to false), i.e the recovery process will
> always stop before the timestamp and never after. Example:
>
>
> $cat recovery.conf:
> restore_command = 'cp ../archive/%f %p 2>/dev/null'
> recovery_target_time = '2013-06-27 17:40:10-0700'
> recovery_target_inclusive = true
>
>
> after recovery in the postgres log file:
> ...
> LOG:  starting point-in-time recovery to 2013-06-27 17:40:10-07
> LOG:  restored log file "000000010000002B000000A7" from archive
> ...
> LOG:  recovery stopping before commit of transaction 1962, time 2013-06-27
> 17:40:15.849203-07
> LOG:  redo done at 2B/B0004098
> LOG:  last completed transaction was at log time 2013-06-27
> 17:39:59.047882-07
> LOG:  selected new timeline ID: 2
>
>
> By looking at the source, it appears that the problem is in xlog.c, function
> 'recoveryStopsHere':
> #########
> 5969: if (recoveryTargetInclusive)
> 5970:   stopsHere = (recordXtime > recoveryTargetTime);
> 5971: else
> 5972:   stopsHere = (recordXtime >= recoveryTargetTime);
> 5973:if (stopsHere)
> 5974:   *includeThis = false;
> #########
>
>
> Even if recoveryTargetInclusive is true, includeThis will bet set to false
> when reaching the last record, causing the record not to be applied.

In that case, since the time of the last record is newer than the
recovery_target_time,
it seems correct not to apply that record.

That is, if recovery_target_inclusive is true, all the records whose
time is older than
or equal to the recovery_target_time should be applied. If
recovery_target_inclusive
is false, all the records whose time is only older than the
recovery_target_time should
be applied. Currently the recovery process works as expected.

Regards,

--
Fujii Masao
You are right. I did not read the doc correctly.

Is there anyway to specify a timestamp and get the database to recover to
the first transaction after that point?


On Mon, Jul 1, 2013 at 10:07 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

> On Sat, Jun 29, 2013 at 7:12 AM,  <eyal.kaspi@delphix.com> wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference:      8269
> > Logged by:          PITR Recovery by timestamp ignores
> recovery_target_inclusive
> > Email address:      eyal.kaspi@delphix.com
> > PostgreSQL version: 9.2.4
> > Operating system:   RHEL
> > Description:
> >
> > While doing Point-in-time recovery and specifying a timestamp as the
> target,
> > I noticed that the timestamp is always considered as exclusive (as if
> > recovery_target_inclusive was set to false), i.e the recovery process
> will
> > always stop before the timestamp and never after. Example:
> >
> >
> > $cat recovery.conf:
> > restore_command = 'cp ../archive/%f %p 2>/dev/null'
> > recovery_target_time = '2013-06-27 17:40:10-0700'
> > recovery_target_inclusive = true
> >
> >
> > after recovery in the postgres log file:
> > ...
> > LOG:  starting point-in-time recovery to 2013-06-27 17:40:10-07
> > LOG:  restored log file "000000010000002B000000A7" from archive
> > ...
> > LOG:  recovery stopping before commit of transaction 1962, time
> 2013-06-27
> > 17:40:15.849203-07
> > LOG:  redo done at 2B/B0004098
> > LOG:  last completed transaction was at log time 2013-06-27
> > 17:39:59.047882-07
> > LOG:  selected new timeline ID: 2
> >
> >
> > By looking at the source, it appears that the problem is in xlog.c,
> function
> > 'recoveryStopsHere':
> > #########
> > 5969: if (recoveryTargetInclusive)
> > 5970:   stopsHere = (recordXtime > recoveryTargetTime);
> > 5971: else
> > 5972:   stopsHere = (recordXtime >= recoveryTargetTime);
> > 5973:if (stopsHere)
> > 5974:   *includeThis = false;
> > #########
> >
> >
> > Even if recoveryTargetInclusive is true, includeThis will bet set to
> false
> > when reaching the last record, causing the record not to be applied.
>
> In that case, since the time of the last record is newer than the
> recovery_target_time,
> it seems correct not to apply that record.
>
> That is, if recovery_target_inclusive is true, all the records whose
> time is older than
> or equal to the recovery_target_time should be applied. If
> recovery_target_inclusive
> is false, all the records whose time is only older than the
> recovery_target_time should
> be applied. Currently the recovery process works as expected.
>
> Regards,
>
> --
> Fujii Masao
>
On Tue, Jul 2, 2013 at 3:05 AM, Eyal Kaspi <eyal.kaspi@delphix.com> wrote:
> You are right. I did not read the doc correctly.
>
> Is there anyway to specify a timestamp and get the database to recover to
> the first transaction after that point?

Currently there is no easy way to do that. Basically you need to find out
the XID or the time of that first transaction, e.g., by using pg_xlogdump,
and specify it in the recovery target.

Regards,

--
Fujii Masao