Re: Recovery to backup point

Hello,

It seems that Everyone welcomed the following functionality, and I also want
it to solve some problem. But this doesn't appear to be undertaken.

Recovery target 'immediate'
http://www.postgresql.org/message-id/51703751.2020208@vmware.com

Is there any technical difficulty? May I implement this feature and submit
a patch for the next commitfest if I have time?

Regards
MauMau

On Sat, Dec 7, 2013 at 9:06 AM, MauMau <maumau307(at)gmail(dot)com> wrote:
> It seems that Everyone welcomed the following functionality, and I also want
> it to solve some problem. But this doesn't appear to be undertaken.
Indeed, nobody has really showed up to implement that.

>
> Recovery target 'immediate'
> http://www.postgresql.org/message-id/51703751.2020208@vmware.com
> Is there any technical difficulty?
As far as I recall, I don't think so. The problem and the way to solve
that are clear. The only trick is to be sure that recovery is done
just until a consistent point is reached, and to implement that
cleanly.

> May I implement this feature and submit a patch for the next commitfest if I have time?
Please feel free. I might as well participate in the review.
Regards,
--
Michael

From: "Michael Paquier" <michael(dot)paquier(at)gmail(dot)com>
> On Sat, Dec 7, 2013 at 9:06 AM, MauMau <maumau307(at)gmail(dot)com> wrote:
>> Recovery target 'immediate'
>> http://www.postgresql.org/message-id/51703751.2020208@vmware.com
>>
>> May I implement this feature and submit a patch for the next commitfest
>> if I have time?
> Please feel free. I might as well participate in the review.

Thanks. I'm feeling incliend to make the configuration "recovery_target =
'backup_point'" instead of "recovery_target = 'immediate'", because:

* The meaning of this feature for usrs is to recover the database to the
backup point.
* it doesn't seem to need a new parameter. recovery_target_time sounds
appropriate because users want to restore the database at the "time" of
backup.

Regards
MauMau

From: "Michael Paquier" <michael(dot)paquier(at)gmail(dot)com>
> As far as I recall, I don't think so. The problem and the way to solve
> that are clear. The only trick is to be sure that recovery is done
> just until a consistent point is reached, and to implement that
> cleanly.
>
>> May I implement this feature and submit a patch for the next commitfest
>> if I have time?
> Please feel free. I might as well participate in the review.

I've done with the attached patch. I also confirmed that the problem I
raised in the first mail of the below thread was solved with this patch.

[bug fix] PITR corrupts the database cluster
http://www.postgresql.org/message-id/F93E42280A9A4A5EB74FC7350C801A20@maumau

I'm wondering if I can do this with cleaner and less code. It would be
grateful if you could give me any advice.

Regards
MauMau

On 12/09/2013 02:03 PM, MauMau wrote:
> From: "Michael Paquier" <michael(dot)paquier(at)gmail(dot)com>
>> As far as I recall, I don't think so. The problem and the way to solve
>> that are clear. The only trick is to be sure that recovery is done
>> just until a consistent point is reached, and to implement that
>> cleanly.
>>
>>> May I implement this feature and submit a patch for the next
>>> commitfest if I have time?
>> Please feel free. I might as well participate in the review.
>
> I've done with the attached patch.

Thanks. Looks sane, although I don't much like the proposed interface to
trigger this, setting recovery_target_time='backup_point'. What the code
actually does is to stop recovery as soon as you reach consistency,
which might not have anything to do with a backup. If you set it on a
warm standby server, for example, it will end recovery as soon as it
reaches consistency, but there was probably no backup taken at that point.

> I also confirmed that the problem I
> raised in the first mail of the below thread was solved with this patch.
>
> [bug fix] PITR corrupts the database cluster
> http://www.postgresql.org/message-id/F93E42280A9A4A5EB74FC7350C801A20%40maumau

Hmm. I guess it's a nice work-around to use this option, but it doesn't
really solve the underlying issue. The system might well reach
consistency between deleting database files and the transaction commit,
in which case you still have the same problem.

It would be nice to have a more robust fix for that. Perhaps we could
use the safe_restartpoint machinery we have to not allow recovery to end
until we see the commit record. I was really hoping to get rid of that
machinery in 9.4, though, as it won't be needed for GIN and B-tree after
the patches I have in the current commitfest are committed.

In any case, that's a separate discussion and separate patch.

- Heikki

From: "Heikki Linnakangas" <hlinnakangas(at)vmware(dot)com>
> Thanks. Looks sane, although I don't much like the proposed interface to
> trigger this, setting recovery_target_time='backup_point'. What the code
> actually does is to stop recovery as soon as you reach consistency, which
> might not have anything to do with a backup. If you set it on a warm
> standby server, for example, it will end recovery as soon as it reaches
> consistency, but there was probably no backup taken at that point.

Thank you for reviewing so rapidly. I thought I would check the end of
backup in recoveryStopsHere(), by matching XLOG_BACKUP_END and
ControlFile->backupStartPoint for backups taken on the primary, and
comparing the current redo location with ControlFile->backupEndPoint for
backups taken on the standby. However, that would duplicate much code in
XLOG_BACKUP_END redo processing and checkRecoveryConsistency(). Besides,
the code works only when the user explicitly requests recovery to backup
point, not when he starts the warm standby server. (I wonder I'm answering
correctly.)

> Hmm. I guess it's a nice work-around to use this option, but it doesn't
> really solve the underlying issue. The system might well reach consistency
> between deleting database files and the transaction commit, in which case
> you still have the same problem.

Yes, you're right. But I believe the trouble can be avoided most of the
time.

> It would be nice to have a more robust fix for that. Perhaps we could use
> the safe_restartpoint machinery we have to not allow recovery to end until
> we see the commit record. I was really hoping to get rid of that machinery
> in 9.4, though, as it won't be needed for GIN and B-tree after the patches
> I have in the current commitfest are committed.
>
> In any case, that's a separate discussion and separate patch.

I think so, too. That still seems a bit difficult for what I am now. If
someone starts a discussion in a separate thread, I'd like to join it.

Regards
MauMau

On 12/09/2013 03:05 PM, MauMau wrote:
> From: "Heikki Linnakangas" <hlinnakangas(at)vmware(dot)com>
>> Thanks. Looks sane, although I don't much like the proposed interface
>> to trigger this, setting recovery_target_time='backup_point'. What the
>> code actually does is to stop recovery as soon as you reach
>> consistency, which might not have anything to do with a backup. If you
>> set it on a warm standby server, for example, it will end recovery as
>> soon as it reaches consistency, but there was probably no backup taken
>> at that point.
>
> Thank you for reviewing so rapidly. I thought I would check the end of
> backup in recoveryStopsHere(), by matching XLOG_BACKUP_END and
> ControlFile->backupStartPoint for backups taken on the primary, and
> comparing the current redo location with ControlFile->backupEndPoint for
> backups taken on the standby. However, that would duplicate much code
> in XLOG_BACKUP_END redo processing and checkRecoveryConsistency().
> Besides, the code works only when the user explicitly requests recovery
> to backup point, not when he starts the warm standby server. (I wonder
> I'm answering correctly.)

I was thinking that you have a warm standby server, and you decide to
stop using it as a warm standby, and promote it. You'd do that by
stopping it, modifying recovery.conf to remove standby_mode, and set a
recovery target, and then restart.

After some refactoring and fixing bugs in the existing code, I came up
with the attached patch. I called the option simply "recovery_target",
with the only allowed value of "immediate". IOW, if you want to stop
recovery as early as possible, you add recovery_target='immediate' to
recovery.conf. Now that we have four different options to set the
recovery target with, I rearranged the docs slightly. How does this look
to you?

- Heikki

From: "Heikki Linnakangas" <hlinnakangas(at)vmware(dot)com>
> After some refactoring and fixing bugs in the existing code, I came up
> with the attached patch. I called the option simply "recovery_target",
> with the only allowed value of "immediate". IOW, if you want to stop
> recovery as early as possible, you add recovery_target='immediate' to
> recovery.conf. Now that we have four different options to set the
> recovery target with, I rearranged the docs slightly. How does this look
> to you?

I'm almost comfortable with your patch. There are two comments:

C1. The following parts seem to be mistakenly taken from my patch. These
are not necessary for your patch, aren't they?

@@ -6238,6 +6277,10 @@ StartupXLOG(void)
ereport(LOG,
(errmsg("starting point-in-time recovery to XID %u",
recoveryTargetXid)));
+ else if (recoveryTarget == RECOVERY_TARGET_TIME &&
+ recoveryTargetTime == 0)
+ ereport(LOG,
+ (errmsg("starting point-in-time recovery to backup point")));
else if (recoveryTarget == RECOVERY_TARGET_TIME)
ereport(LOG,
(errmsg("starting point-in-time recovery to %s",
@@ -6971,6 +7017,22 @@ StartupXLOG(void)
if (switchedTLI && AllowCascadeReplication())
WalSndWakeup();

+ /*
+ * If we have reached the end of base backup during recovery
+ * to the backup point, exit redo loop.
+ */
+ if (recoveryTarget == RECOVERY_TARGET_TIME &&
+ recoveryTargetTime == 0 && reachedConsistency)
+ {
+ if (recoveryPauseAtTarget)
+ {
+ SetRecoveryPause(true);
+ recoveryPausesHere();
+ }
+ reachedStopPoint = true;
+ break;
+ }
+
/* Exit loop if we reached inclusive recovery target */
if (recoveryStopsAfter(record))
{
@@ -7116,6 +7178,9 @@ StartupXLOG(void)
"%s transaction %u",
recoveryStopAfter ? "after" : "before",
recoveryStopXid);
+ else if (recoveryTarget == RECOVERY_TARGET_TIME &&
+ recoveryStopTime == 0)
+ snprintf(reason, sizeof(reason), "at backup point");
else if (recoveryTarget == RECOVERY_TARGET_TIME)
snprintf(reason, sizeof(reason),
"%s %s\n",

C2. "recovery_target = 'immediate'" sounds less intuitive than my suggestion
"recovery_target_time = 'backup_point'", at least for those who want to
recover to the backup point.
Although I don't have a good naming sense in English, the value should be a
noun, not an adjective like "immediate", because the value specifies the
"target (point)" of recovery.

Being related to C2, I wonder if users would understand the following part
in the documentation.

+ This parameter specifies that recovery should end as soon as a
+ consistency is reached, ie. as early as possible.

The subsequent sentence clarifies the use case for recovery from an online
backup, but in what use cases do they specify this parameter? For example,
when do the users face the following situation?

> I was thinking that you have a warm standby server, and you decide to
> stop using it as a warm standby, and promote it. You'd do that by
> stopping it, modifying recovery.conf to remove standby_mode, and set a
> recovery target, and then restart.
>

Regards
MauMau

On Fri, Jan 10, 2014 at 12:08 AM, MauMau <maumau307(at)gmail(dot)com> wrote:
> C2. "recovery_target = 'immediate'" sounds less intuitive than my suggestion
> "recovery_target_time = 'backup_point'", at least for those who want to
> recover to the backup point.
> Although I don't have a good naming sense in English, the value should be a
> noun, not an adjective like "immediate", because the value specifies the
> "target (point)" of recovery.
"immediate" is perfectly fine IMO, it fits with what this recovery
target aims at: an immediate consistency point. My 2c on that.
--
Michael

The documentation doesn't build.

From: "Michael Paquier" <michael(dot)paquier(at)gmail(dot)com>
> On Fri, Jan 10, 2014 at 12:08 AM, MauMau <maumau307(at)gmail(dot)com> wrote:
>> C2. "recovery_target = 'immediate'" sounds less intuitive than my
>> suggestion
>> "recovery_target_time = 'backup_point'", at least for those who want to
>> recover to the backup point.
>> Although I don't have a good naming sense in English, the value should be
>> a
>> noun, not an adjective like "immediate", because the value specifies the
>> "target (point)" of recovery.
> "immediate" is perfectly fine IMO, it fits with what this recovery
> target aims at: an immediate consistency point. My 2c on that.

OK, I believe the naming sense of people whose mother tongue is English. I
thought the value should be a noun like "earliest_consistency_point" or
"earliest_consistency" (I don't these are good, though).

Regards
MauMau

Hi, Heiki-san,

From: "MauMau" <maumau307(at)gmail(dot)com>
> From: "Heikki Linnakangas" <hlinnakangas(at)vmware(dot)com>
>> After some refactoring and fixing bugs in the existing code, I came up
>> with the attached patch. I called the option simply "recovery_target",
>> with the only allowed value of "immediate". IOW, if you want to stop
>> recovery as early as possible, you add recovery_target='immediate' to
>> recovery.conf. Now that we have four different options to set the
>> recovery target with, I rearranged the docs slightly. How does this look
>> to you?
>
> I'm almost comfortable with your patch. There are two comments:
>
> C1. The following parts seem to be mistakenly taken from my patch. These
> are not necessary for your patch, aren't they?

I'm going to add the attached new revision of the patch soon, which is
almost based on yours. All what I modified is removal of parts I mentioned
above. I confirmed that the original problem could be solved. Thanks.

Regards
MauMau

On 01/24/2014 01:37 PM, MauMau wrote:
> Hi, Heiki-san,
>
> From: "MauMau" <maumau307(at)gmail(dot)com>
>> From: "Heikki Linnakangas" <hlinnakangas(at)vmware(dot)com>
>>> After some refactoring and fixing bugs in the existing code, I came up
>>> with the attached patch. I called the option simply "recovery_target",
>>> with the only allowed value of "immediate". IOW, if you want to stop
>>> recovery as early as possible, you add recovery_target='immediate' to
>>> recovery.conf. Now that we have four different options to set the
>>> recovery target with, I rearranged the docs slightly. How does this look
>>> to you?
>>
>> I'm almost comfortable with your patch. There are two comments:
>>
>> C1. The following parts seem to be mistakenly taken from my patch. These
>> are not necessary for your patch, aren't they?
>
> I'm going to add the attached new revision of the patch soon, which is
> almost based on yours. All what I modified is removal of parts I mentioned
> above. I confirmed that the original problem could be solved. Thanks.

Thanks, committed!

- Heikki

On Sat, Jan 25, 2014 at 5:10 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> Thanks, committed!
It seems that this patch has not been pushed :)
Regards,
--
Michael