Recovery to backup point

Started by MauMauabout 12 years ago14 messages
#1MauMau
maumau307@gmail.com

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'
/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#2Michael Paquier
michael.paquier@gmail.com
In reply to: MauMau (#1)
Re: Recovery to backup point

On Sat, Dec 7, 2013 at 9:06 AM, MauMau <maumau307@gmail.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'
/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#3MauMau
maumau307@gmail.com
In reply to: Michael Paquier (#2)
Re: Recovery to backup point

From: "Michael Paquier" <michael.paquier@gmail.com>

On Sat, Dec 7, 2013 at 9:06 AM, MauMau <maumau307@gmail.com> wrote:

Recovery target 'immediate'
/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#4MauMau
maumau307@gmail.com
In reply to: Michael Paquier (#2)
1 attachment(s)
Re: Recovery to backup point

From: "Michael Paquier" <michael.paquier@gmail.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
/messages/by-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

Attachments:

recover_to_backup.patchapplication/octet-stream; name=recover_to_backup.patchDownload
diff -rpcd a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml
*** a/doc/src/sgml/recovery-config.sgml	2013-12-02 09:17:05.000000000 +0900
--- b/doc/src/sgml/recovery-config.sgml	2013-12-09 16:15:10.000000000 +0900
*************** restore_command = 'copy "C:\\server\\arc
*** 165,171 ****
  
       <varlistentry id="recovery-target-time" xreflabel="recovery_target_time">
        <term><varname>recovery_target_time</varname>
!            (<type>timestamp</type>)
        </term>
        <indexterm>
          <primary><varname>recovery_target_time</> recovery parameter</primary>
--- 165,171 ----
  
       <varlistentry id="recovery-target-time" xreflabel="recovery_target_time">
        <term><varname>recovery_target_time</varname>
!            (<type>string</type>)
        </term>
        <indexterm>
          <primary><varname>recovery_target_time</> recovery parameter</primary>
*************** restore_command = 'copy "C:\\server\\arc
*** 177,182 ****
--- 177,184 ----
          At most one of <varname>recovery_target_time</>,
          <xref linkend="recovery-target-name"> or
          <xref linkend="recovery-target-xid"> can be specified.
+         Setting this to <literal>backup_point</> recovers to the time when
+         the base backup completed.
          The default is to recover to the end of the WAL log.
          The precise stopping point is also influenced by
          <xref linkend="recovery-target-inclusive">.
diff -rpcd a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
*** a/src/backend/access/transam/xlog.c	2013-12-02 09:17:05.000000000 +0900
--- b/src/backend/access/transam/xlog.c	2013-12-09 16:15:27.000000000 +0900
*************** readRecoveryCommandFile(void)
*** 5403,5408 ****
--- 5403,5416 ----
  				continue;
  			recoveryTarget = RECOVERY_TARGET_TIME;
  
+ 			if (strcmp(item->value, "backup_point") == 0)
+ 			{
+ 				recoveryTargetTime = 0;
+ 				ereport(DEBUG2,
+ 					 (errmsg_internal("recovery_target_time = backup_point")));
+ 				continue;
+ 			}
+ 
  			/*
  			 * Convert the time string given by the user to TimestampTz form.
  			 */
*************** exitArchiveRecovery(TimeLineID endTLI, X
*** 5628,5634 ****
  static bool
  recoveryStopsHere(XLogRecord *record, bool *includeThis)
  {
! 	bool		stopsHere;
  	uint8		record_info;
  	TimestampTz recordXtime;
  	char		recordRPName[MAXFNAMELEN];
--- 5636,5642 ----
  static bool
  recoveryStopsHere(XLogRecord *record, bool *includeThis)
  {
! 	bool		stopsHere = false;		/* to keep compiler quiet */
  	uint8		record_info;
  	TimestampTz recordXtime;
  	char		recordRPName[MAXFNAMELEN];
*************** recoveryStopsHere(XLogRecord *record, bo
*** 5710,5716 ****
  		 */
  		*includeThis = false;
  	}
! 	else
  	{
  		/*
  		 * There can be many transactions that share the same commit time, so
--- 5718,5724 ----
  		 */
  		*includeThis = false;
  	}
! 	else if (recoveryTargetTime != 0)
  	{
  		/*
  		 * There can be many transactions that share the same commit time, so
*************** StartupXLOG(void)
*** 6107,6112 ****
--- 6115,6124 ----
  			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",
*************** StartupXLOG(void)
*** 6842,6847 ****
--- 6854,6875 ----
  				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 (!recoveryContinue)
  					break;
*************** StartupXLOG(void)
*** 6978,6983 ****
--- 7006,7014 ----
  					 "%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",
#5Heikki Linnakangas
hlinnakangas@vmware.com
In reply to: MauMau (#4)
Re: Recovery to backup point

On 12/09/2013 02:03 PM, MauMau wrote:

From: "Michael Paquier" <michael.paquier@gmail.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
/messages/by-id/F93E42280A9A4A5EB74FC7350C801A20@maumau

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#6MauMau
maumau307@gmail.com
In reply to: Heikki Linnakangas (#5)
Re: Recovery to backup point

From: "Heikki Linnakangas" <hlinnakangas@vmware.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#7Heikki Linnakangas
hlinnakangas@vmware.com
In reply to: MauMau (#6)
1 attachment(s)
Re: Recovery to backup point

On 12/09/2013 03:05 PM, MauMau wrote:

From: "Heikki Linnakangas" <hlinnakangas@vmware.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

Attachments:

backup_target_immediate-1.patchtext/x-diff; name=backup_target_immediate-1.patchDownload
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index a2361d7..854b5fd 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -1124,7 +1124,7 @@ restore_command = 'cp /mnt/server/archivedir/%f %p'
    <para>
     If you want to recover to some previous point in time (say, right before
     the junior DBA dropped your main transaction table), just specify the
-    required stopping point in <filename>recovery.conf</>.  You can specify
+    required <link linkend="recovery-target-settings">stopping point</link> in <filename>recovery.conf</>.  You can specify
     the stop point, known as the <quote>recovery target</>, either by
     date/time, named restore point or by completion of a specific transaction
     ID.  As of this writing only the date/time and named restore point options
diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml
index 550cdce..a723338 100644
--- a/doc/src/sgml/recovery-config.sgml
+++ b/doc/src/sgml/recovery-config.sgml
@@ -199,8 +199,33 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"'  # Windows
   <sect1 id="recovery-target-settings">
 
     <title>Recovery Target Settings</title>
+     <para>
+      By default, recovery will recover to the end of the WAL log. The
+      following parameters can be used to specify an earlier stopping point.
+      At most one of <varname>recovery_target</>,
+      <varname>recovery_target_name</>, <varname>recovery_target_time</>, or
+      <varname>recovery_target_xid</> can be specified. 
+     </para>
      <variablelist>
 
+     <varlistentry id="recovery-target" xreflabel="recovery_target_name">
+      <term><varname>recovery_target</varname><literal> = 'immediate'</literal></term>
+      <indexterm>
+        <primary><varname>recovery_target</> recovery parameter</primary>
+      </indexterm>
+      <listitem>
+       <para>
+        This parameter specifies that recovery should end as soon as a
+        consistency is reached, ie. as early as possible. When restoring from an
+        online backup, this means the point where taking the backup ended.
+       </para>
+       <para>
+        Technically, this is a string parameter, but <literal>'immediate'</l>
+        is currently the only allowed value.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="recovery-target-name" xreflabel="recovery_target_name">
       <term><varname>recovery_target_name</varname>
            (<type>string</type>)
@@ -212,10 +237,6 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"'  # Windows
        <para>
         This parameter specifies the named restore point, created with
         <function>pg_create_restore_point()</> to which recovery will proceed.
-        At most one of <varname>recovery_target_name</>,
-        <xref linkend="recovery-target-time"> or
-        <xref linkend="recovery-target-xid"> can be specified.  The default is to
-        recover to the end of the WAL log.
        </para>
       </listitem>
      </varlistentry>
@@ -231,10 +252,6 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"'  # Windows
        <para>
         This parameter specifies the time stamp up to which recovery
         will proceed.
-        At most one of <varname>recovery_target_time</>,
-        <xref linkend="recovery-target-name"> or
-        <xref linkend="recovery-target-xid"> can be specified.
-        The default is to recover to the end of the WAL log.
         The precise stopping point is also influenced by
         <xref linkend="recovery-target-inclusive">.
        </para>
@@ -254,15 +271,18 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"'  # Windows
         start, transactions can complete in a different numeric order.
         The transactions that will be recovered are those that committed
         before (and optionally including) the specified one.
-        At most one of <varname>recovery_target_xid</>,
-        <xref linkend="recovery-target-name"> or
-        <xref linkend="recovery-target-time"> can be specified.
-        The default is to recover to the end of the WAL log.
         The precise stopping point is also influenced by
         <xref linkend="recovery-target-inclusive">.
        </para>
       </listitem>
      </varlistentry>
+     </variablelist>
+     <para>
+       The following options further specify the recovery target, and affect
+       what happens when the target is reached:
+     </para>
+
+     <variablelist>
 
      <varlistentry id="recovery-target-inclusive"
                    xreflabel="recovery_target_inclusive">
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index b53ae87..a24586a 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -5434,6 +5434,19 @@ readRecoveryCommandFile(void)
 					(errmsg_internal("recovery_target_name = '%s'",
 									 recoveryTargetName)));
 		}
+		else if (strcmp(item->name, "recovery_target") == 0)
+		{
+			if (strcmp(item->value, "immediate") == 0)
+				recoveryTarget = RECOVERY_TARGET_IMMEDIATE;
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						 errmsg("invalid recovery_target parameter"),
+						 errhint("The only allowed value is 'immediate'")));
+			ereport(DEBUG2,
+					(errmsg_internal("recovery_target = '%s'",
+									 item->value)));
+		}
 		else if (strcmp(item->name, "recovery_target_inclusive") == 0)
 		{
 			/*
@@ -5502,7 +5515,7 @@ readRecoveryCommandFile(void)
 							RECOVERY_COMMAND_FILE),
 					 errhint("The database server will regularly poll the pg_xlog subdirectory to check for files placed there.")));
 	}
-	else
+	else if (recoveryTargetTime != 0)
 	{
 		if (recoveryRestoreCommand == NULL)
 			ereport(FATAL,
@@ -5676,7 +5689,20 @@ recoveryStopsBefore(XLogRecord *record)
 	bool		isCommit;
 	TimestampTz recordXtime = 0;
 
-	/* We only consider stopping before COMMIT or ABORT records. */
+	/* Check if we should stop as soon as reaching consistency */
+	if (recoveryTarget == RECOVERY_TARGET_IMMEDIATE && reachedConsistency)
+	{
+		ereport(LOG,
+				(errmsg("recovery stopping after reaching consistency")));
+
+		recoveryStopAfter = false;
+		recoveryStopXid = InvalidTransactionId;
+		recoveryStopTime = 0;
+		recoveryStopName[0] = '\0';
+		return true;
+	}
+
+	/* Otherwise we only consider stopping before COMMIT or ABORT records. */
 	if (record->xl_rmid != RM_XACT_ID)
 		return false;
 	record_info = record->xl_info & ~XLR_INFO_MASK;
@@ -5825,6 +5851,19 @@ recoveryStopsAfter(XLogRecord *record)
 		}
 	}
 
+	/* Check if we should stop as soon as reaching consistency */
+	if (recoveryTarget == RECOVERY_TARGET_IMMEDIATE && reachedConsistency)
+	{
+		ereport(LOG,
+				(errmsg("recovery stopping after reaching consistency")));
+
+		recoveryStopAfter = true;
+		recoveryStopXid = InvalidTransactionId;
+		recoveryStopTime = 0;
+		recoveryStopName[0] = '\0';
+		return true;
+	}
+
 	return false;
 }
 
@@ -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",
@@ -6246,6 +6289,9 @@ StartupXLOG(void)
 			ereport(LOG,
 					(errmsg("starting point-in-time recovery to \"%s\"",
 							recoveryTargetName)));
+		else if (recoveryTarget == RECOVERY_TARGET_IMMEDIATE)
+			ereport(LOG,
+					(errmsg("starting point-in-time recovery to earliest consistent point")));
 		else
 			ereport(LOG,
 					(errmsg("starting archive recovery")));
@@ -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",
@@ -7125,6 +7190,8 @@ StartupXLOG(void)
 			snprintf(reason, sizeof(reason),
 					 "at restore point \"%s\"",
 					 recoveryStopName);
+		else if (recoveryTarget == RECOVERY_TARGET_IMMEDIATE)
+			snprintf(reason, sizeof(reason), "reached consistency");
 		else
 			snprintf(reason, sizeof(reason), "no recovery target specified");
 
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 017e74d..0a8f54d 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -173,7 +173,8 @@ typedef enum
 	RECOVERY_TARGET_UNSET,
 	RECOVERY_TARGET_XID,
 	RECOVERY_TARGET_TIME,
-	RECOVERY_TARGET_NAME
+	RECOVERY_TARGET_NAME,
+	RECOVERY_TARGET_IMMEDIATE
 } RecoveryTargetType;
 
 extern XLogRecPtr XactLastRecEnd;
#8MauMau
maumau307@gmail.com
In reply to: Heikki Linnakangas (#7)
Re: Recovery to backup point

From: "Heikki Linnakangas" <hlinnakangas@vmware.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#9Michael Paquier
michael.paquier@gmail.com
In reply to: MauMau (#8)
Re: Recovery to backup point

On Fri, Jan 10, 2014 at 12:08 AM, MauMau <maumau307@gmail.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#10Peter Eisentraut
peter_e@gmx.net
In reply to: Heikki Linnakangas (#7)
Re: Recovery to backup point

The documentation doesn't build.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#11MauMau
maumau307@gmail.com
In reply to: Michael Paquier (#9)
Re: Recovery to backup point

From: "Michael Paquier" <michael.paquier@gmail.com>

On Fri, Jan 10, 2014 at 12:08 AM, MauMau <maumau307@gmail.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#12MauMau
maumau307@gmail.com
In reply to: MauMau (#8)
1 attachment(s)
Re: Recovery to backup point

Hi, Heiki-san,

From: "MauMau" <maumau307@gmail.com>

From: "Heikki Linnakangas" <hlinnakangas@vmware.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

Attachments:

recover_to_backup_v2.patchapplication/octet-stream; name=recover_to_backup_v2.patchDownload
diff -rpcd a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
*** a/doc/src/sgml/backup.sgml	2014-01-22 13:17:06.000000000 +0900
--- b/doc/src/sgml/backup.sgml	2014-01-24 15:39:16.000000000 +0900
*************** restore_command = 'cp /mnt/server/archiv
*** 1124,1130 ****
     <para>
      If you want to recover to some previous point in time (say, right before
      the junior DBA dropped your main transaction table), just specify the
!     required stopping point in <filename>recovery.conf</>.  You can specify
      the stop point, known as the <quote>recovery target</>, either by
      date/time, named restore point or by completion of a specific transaction
      ID.  As of this writing only the date/time and named restore point options
--- 1124,1130 ----
     <para>
      If you want to recover to some previous point in time (say, right before
      the junior DBA dropped your main transaction table), just specify the
!     required <link linkend="recovery-target-settings">stopping point</link> in <filename>recovery.conf</>.  You can specify
      the stop point, known as the <quote>recovery target</>, either by
      date/time, named restore point or by completion of a specific transaction
      ID.  As of this writing only the date/time and named restore point options
diff -rpcd a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml
*** a/doc/src/sgml/recovery-config.sgml	2014-01-22 13:17:06.000000000 +0900
--- b/doc/src/sgml/recovery-config.sgml	2014-01-24 15:39:16.000000000 +0900
*************** restore_command = 'copy "C:\\server\\arc
*** 199,206 ****
--- 199,231 ----
    <sect1 id="recovery-target-settings">
  
      <title>Recovery Target Settings</title>
+      <para>
+       By default, recovery will recover to the end of the WAL log. The
+       following parameters can be used to specify an earlier stopping point.
+       At most one of <varname>recovery_target</>,
+       <varname>recovery_target_name</>, <varname>recovery_target_time</>, or
+       <varname>recovery_target_xid</> can be specified. 
+      </para>
       <variablelist>
  
+      <varlistentry id="recovery-target" xreflabel="recovery_target_name">
+       <term><varname>recovery_target</varname><literal> = 'immediate'</literal></term>
+       <indexterm>
+         <primary><varname>recovery_target</> recovery parameter</primary>
+       </indexterm>
+       <listitem>
+        <para>
+         This parameter specifies that recovery should end as soon as a
+         consistency is reached, ie. as early as possible. When restoring from an
+         online backup, this means the point where taking the backup ended.
+        </para>
+        <para>
+         Technically, this is a string parameter, but <literal>'immediate'</l>
+         is currently the only allowed value.
+        </para>
+       </listitem>
+      </varlistentry>
+ 
       <varlistentry id="recovery-target-name" xreflabel="recovery_target_name">
        <term><varname>recovery_target_name</varname>
             (<type>string</type>)
*************** restore_command = 'copy "C:\\server\\arc
*** 212,221 ****
         <para>
          This parameter specifies the named restore point, created with
          <function>pg_create_restore_point()</> to which recovery will proceed.
-         At most one of <varname>recovery_target_name</>,
-         <xref linkend="recovery-target-time"> or
-         <xref linkend="recovery-target-xid"> can be specified.  The default is to
-         recover to the end of the WAL log.
         </para>
        </listitem>
       </varlistentry>
--- 237,242 ----
*************** restore_command = 'copy "C:\\server\\arc
*** 231,240 ****
         <para>
          This parameter specifies the time stamp up to which recovery
          will proceed.
-         At most one of <varname>recovery_target_time</>,
-         <xref linkend="recovery-target-name"> or
-         <xref linkend="recovery-target-xid"> can be specified.
-         The default is to recover to the end of the WAL log.
          The precise stopping point is also influenced by
          <xref linkend="recovery-target-inclusive">.
         </para>
--- 252,257 ----
*************** restore_command = 'copy "C:\\server\\arc
*** 254,268 ****
          start, transactions can complete in a different numeric order.
          The transactions that will be recovered are those that committed
          before (and optionally including) the specified one.
-         At most one of <varname>recovery_target_xid</>,
-         <xref linkend="recovery-target-name"> or
-         <xref linkend="recovery-target-time"> can be specified.
-         The default is to recover to the end of the WAL log.
          The precise stopping point is also influenced by
          <xref linkend="recovery-target-inclusive">.
         </para>
        </listitem>
       </varlistentry>
  
       <varlistentry id="recovery-target-inclusive"
                     xreflabel="recovery_target_inclusive">
--- 271,288 ----
          start, transactions can complete in a different numeric order.
          The transactions that will be recovered are those that committed
          before (and optionally including) the specified one.
          The precise stopping point is also influenced by
          <xref linkend="recovery-target-inclusive">.
         </para>
        </listitem>
       </varlistentry>
+      </variablelist>
+      <para>
+        The following options further specify the recovery target, and affect
+        what happens when the target is reached:
+      </para>
+ 
+      <variablelist>
  
       <varlistentry id="recovery-target-inclusive"
                     xreflabel="recovery_target_inclusive">
diff -rpcd a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
*** a/src/backend/access/transam/xlog.c	2014-01-22 13:17:06.000000000 +0900
--- b/src/backend/access/transam/xlog.c	2014-01-24 16:06:53.000000000 +0900
*************** readRecoveryCommandFile(void)
*** 5434,5439 ****
--- 5434,5452 ----
  					(errmsg_internal("recovery_target_name = '%s'",
  									 recoveryTargetName)));
  		}
+ 		else if (strcmp(item->name, "recovery_target") == 0)
+ 		{
+ 			if (strcmp(item->value, "immediate") == 0)
+ 				recoveryTarget = RECOVERY_TARGET_IMMEDIATE;
+ 			else
+ 				ereport(ERROR,
+ 						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ 						 errmsg("invalid recovery_target parameter"),
+ 						 errhint("The only allowed value is 'immediate'")));
+ 			ereport(DEBUG2,
+ 					(errmsg_internal("recovery_target = '%s'",
+ 									 item->value)));
+ 		}
  		else if (strcmp(item->name, "recovery_target_inclusive") == 0)
  		{
  			/*
*************** recoveryStopsBefore(XLogRecord *record)
*** 5676,5682 ****
  	bool		isCommit;
  	TimestampTz recordXtime = 0;
  
! 	/* We only consider stopping before COMMIT or ABORT records. */
  	if (record->xl_rmid != RM_XACT_ID)
  		return false;
  	record_info = record->xl_info & ~XLR_INFO_MASK;
--- 5689,5708 ----
  	bool		isCommit;
  	TimestampTz recordXtime = 0;
  
! 	/* Check if we should stop as soon as reaching consistency */
! 	if (recoveryTarget == RECOVERY_TARGET_IMMEDIATE && reachedConsistency)
! 	{
! 		ereport(LOG,
! 				(errmsg("recovery stopping after reaching consistency")));
! 
! 		recoveryStopAfter = false;
! 		recoveryStopXid = InvalidTransactionId;
! 		recoveryStopTime = 0;
! 		recoveryStopName[0] = '\0';
! 		return true;
! 	}
! 
! 	/* Otherwise we only consider stopping before COMMIT or ABORT records. */
  	if (record->xl_rmid != RM_XACT_ID)
  		return false;
  	record_info = record->xl_info & ~XLR_INFO_MASK;
*************** recoveryStopsAfter(XLogRecord *record)
*** 5825,5830 ****
--- 5851,5869 ----
  		}
  	}
  
+ 	/* Check if we should stop as soon as reaching consistency */
+ 	if (recoveryTarget == RECOVERY_TARGET_IMMEDIATE && reachedConsistency)
+ 	{
+ 		ereport(LOG,
+ 				(errmsg("recovery stopping after reaching consistency")));
+ 
+ 		recoveryStopAfter = true;
+ 		recoveryStopXid = InvalidTransactionId;
+ 		recoveryStopTime = 0;
+ 		recoveryStopName[0] = '\0';
+ 		return true;
+ 	}
+ 
  	return false;
  }
  
*************** StartupXLOG(void)
*** 6246,6251 ****
--- 6285,6293 ----
  			ereport(LOG,
  					(errmsg("starting point-in-time recovery to \"%s\"",
  							recoveryTargetName)));
+ 		else if (recoveryTarget == RECOVERY_TARGET_IMMEDIATE)
+ 			ereport(LOG,
+ 					(errmsg("starting point-in-time recovery to earliest consistent point")));
  		else
  			ereport(LOG,
  					(errmsg("starting archive recovery")));
*************** StartupXLOG(void)
*** 7125,7130 ****
--- 7167,7174 ----
  			snprintf(reason, sizeof(reason),
  					 "at restore point \"%s\"",
  					 recoveryStopName);
+ 		else if (recoveryTarget == RECOVERY_TARGET_IMMEDIATE)
+ 			snprintf(reason, sizeof(reason), "reached consistency");
  		else
  			snprintf(reason, sizeof(reason), "no recovery target specified");
  
diff -rpcd a/src/include/access/xlog.h b/src/include/access/xlog.h
*** a/src/include/access/xlog.h	2014-01-22 13:17:06.000000000 +0900
--- b/src/include/access/xlog.h	2014-01-24 15:39:16.000000000 +0900
*************** typedef enum
*** 173,179 ****
  	RECOVERY_TARGET_UNSET,
  	RECOVERY_TARGET_XID,
  	RECOVERY_TARGET_TIME,
! 	RECOVERY_TARGET_NAME
  } RecoveryTargetType;
  
  extern XLogRecPtr XactLastRecEnd;
--- 173,180 ----
  	RECOVERY_TARGET_UNSET,
  	RECOVERY_TARGET_XID,
  	RECOVERY_TARGET_TIME,
! 	RECOVERY_TARGET_NAME,
! 	RECOVERY_TARGET_IMMEDIATE
  } RecoveryTargetType;
  
  extern XLogRecPtr XactLastRecEnd;
#13Heikki Linnakangas
hlinnakangas@vmware.com
In reply to: MauMau (#12)
Re: Recovery to backup point

On 01/24/2014 01:37 PM, MauMau wrote:

Hi, Heiki-san,

From: "MauMau" <maumau307@gmail.com>

From: "Heikki Linnakangas" <hlinnakangas@vmware.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#14Michael Paquier
michael.paquier@gmail.com
In reply to: Heikki Linnakangas (#13)
Re: Recovery to backup point

On Sat, Jan 25, 2014 at 5:10 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:

Thanks, committed!

It seems that this patch has not been pushed :)
Regards,
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers