Subject: Re: New trigger option of pg_standby
From: Simon Riggs <simon@2ndQuadrant.com>
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Fujii Masao <masao.fujii@gmail.com>,
	Guillaume Smet <guillaume.smet@gmail.com>,
	Peter Eisentraut <peter_e@gmx.net>,
	Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
In-Reply-To: <1242247404.3843.503.camel@ebony.2ndQuadrant>
References: <3f0b79eb0903242329j12865d55s348f5c873a956e71@mail.gmail.com>
	<1239381103.16396.30.camel@ebony.2ndQuadrant>
	<3f0b79eb0904122252t1f4c5860v919d199f399ccf72@mail.gmail.com>
	<3f0b79eb0904132241p18084914g792cb8e5ab3eebb3@mail.gmail.com>
	<3f0b79eb0904150101pb1c59a3ubb422867e8e6a944@mail.gmail.com>
	<49EE1E3F.9050307@enterprisedb.com>
	<3f0b79eb0904212209i7fbd8b68k71db1fef09c62751@mail.gmail.com>
	<49F01D79.8020805@enterprisedb.com>
	<3f0b79eb0904230515p751b5248n6ef96880617db055@mail.gmail.com>
	<4A095A39.3030100@enterprisedb.com>
	<3f0b79eb0905130127u3a3275b1w79f4d8cb729ded36@mail.gmail.com>
	<4A0A86D2.9080602@enterprisedb.com> <1985.1242234105@sss.pgh.pa.us>
	<4A0B10C7.2040309@enterprisedb.com>
	<1242247404.3843.503.camel@ebony.2ndQuadrant>
Content-Type: multipart/mixed; boundary="=-Tl0cAz6Hen4an3fyAJTH"
Date: Thu, 14 May 2009 16:36:55 +0100
Message-Id: <1242315415.3843.526.camel@ebony.2ndQuadrant>
Mime-Version: 1.0


--=-Tl0cAz6Hen4an3fyAJTH
Content-Type: text/plain
Content-Transfer-Encoding: 7bit


On Wed, 2009-05-13 at 21:43 +0100, Simon Riggs wrote:
> On Wed, 2009-05-13 at 21:26 +0300, Heikki Linnakangas wrote:
> 
> > This whole thing can be considered to be a new feature. 
> 
> recovery.conf will contain a new optional parameter:
> 
> recovery_end_command (string)

Implemented.

Some possibility of re-factoring in calc of %r, though that has not been
done to ensure code clarity and avoid need for retesting other aspects
of recovery at this stage of beta.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

--=-Tl0cAz6Hen4an3fyAJTH
Content-Disposition: attachment; filename=recovery_end_command.v1.patch
Content-Type: text/x-patch; name=recovery_end_command.v1.patch; charset=utf-8
Content-Transfer-Encoding: 7bit

Index: doc/src/sgml/backup.sgml
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.125
diff -c -r2.125 backup.sgml
*** doc/src/sgml/backup.sgml	27 Apr 2009 16:27:35 -0000	2.125
--- doc/src/sgml/backup.sgml	14 May 2009 15:14:35 -0000
***************
*** 1126,1131 ****
--- 1126,1154 ----
        </listitem>
       </varlistentry>
  
+      <varlistentry id="recovery-end-command" xreflabel="recovery_end_command">
+       <term><varname>recovery_end_command</varname> (<type>string</type>)</term>
+       <listitem>
+        <para>
+ 		This parameter specifies a shell command that will be executed once only
+ 		at the end of recovery. This parameter is optional. The purpose of the
+ 		recovery_end_command is to provide a mechanism for cleanup following
+ 		replication or recovery. 
+ 		Any <literal>%r</> is replaced by the name of the file
+ 		containing the last valid restart point. That is the earliest file that
+ 		must be kept to allow a restore to be restartable, so this information
+ 		can be used to truncate the archive to just the minimum required to
+ 		support restart of the current restore. <literal>%r</> would only be
+ 		used in a warm-standby configuration  (see <xref linkend="warm-standby">). 
+         Write <literal>%%</> to embed an actual <literal>%</> character
+         in the command.
+ 		If the command returns a non-zero exit status then a WARNING log
+ 		message will be written, unless signalled in which case we return
+ 		a FATAL error.
+        </para>
+       </listitem>
+      </varlistentry>
+ 
       <varlistentry id="recovery-target-time" xreflabel="recovery_target_time">
        <term><varname>recovery_target_time</varname>
             (<type>timestamp</type>)
Index: doc/src/sgml/pgstandby.sgml
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/pgstandby.sgml,v
retrieving revision 2.7
diff -c -r2.7 pgstandby.sgml
*** doc/src/sgml/pgstandby.sgml	27 Feb 2009 09:30:21 -0000	2.7
--- doc/src/sgml/pgstandby.sgml	14 May 2009 15:33:18 -0000
***************
*** 210,215 ****
--- 210,216 ----
  archive_command = 'cp %p .../archive/%f'
  
  restore_command = 'pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5442 .../archive %f %p %r 2>>standby.log'
+ recovery_end_command = 'rm /tmp/pgsql.trigger.5442'
    </programlisting>
    <para>
     where the archive directory is physically located on the standby server,
***************
*** 241,246 ****
--- 242,252 ----
     </listitem>
     <listitem>
      <para>
+      remove the trigger file when recovery ends
+     </para>
+    </listitem>
+    <listitem>
+     <para>
       remove no-longer-needed files from the archive directory
      </para>
     </listitem>
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.337
diff -c -r1.337 xlog.c
*** src/backend/access/transam/xlog.c	7 May 2009 11:25:25 -0000	1.337
--- src/backend/access/transam/xlog.c	14 May 2009 15:19:41 -0000
***************
*** 147,152 ****
--- 147,153 ----
  
  /* options taken from recovery.conf */
  static char *recoveryRestoreCommand = NULL;
+ static char *recoveryEndCommand = NULL;
  static bool recoveryTarget = false;
  static bool recoveryTargetExact = false;
  static bool recoveryTargetInclusive = true;
***************
*** 463,468 ****
--- 464,470 ----
  static void XLogFileClose(void);
  static bool RestoreArchivedFile(char *path, const char *xlogfname,
  					const char *recovername, off_t expectedSize);
+ static void ExecuteRecoveryEndCommand(void);
  static void PreallocXlogFiles(XLogRecPtr endptr);
  static void RemoveOldXlogFiles(uint32 log, uint32 seg, XLogRecPtr endptr);
  static void ValidateXLOGDirectoryStructure(void);
***************
*** 2850,2855 ****
--- 2852,2965 ----
  }
  
  /*
+  * Attempt to execute the recovery_end_command.
+  */
+ static void
+ ExecuteRecoveryEndCommand(void)
+ {
+ 	char		xlogRecoveryEndCmd[MAXPGPATH];
+ 	char		lastRestartPointFname[MAXPGPATH];
+ 	char	   *dp;
+ 	char	   *endp;
+ 	const char *sp;
+ 	int			rc;
+ 	bool		signaled;
+ 	uint32		restartLog;
+ 	uint32		restartSeg;
+ 
+ 	Assert(recoveryEndCommand);
+ 
+ 	/*
+ 	 * Calculate the archive file cutoff point for use during log shipping
+ 	 * replication. All files earlier than this point can be deleted
+ 	 * from the archive, though there is no requirement to do so.
+ 	 *
+ 	 * We initialise this with the filename of an InvalidXLogRecPtr, which
+ 	 * will prevent the deletion of any WAL files from the archive
+ 	 * because of the alphabetic sorting property of WAL filenames. 
+ 	 *
+ 	 * Once we have successfully located the redo pointer of the checkpoint
+ 	 * from which we start recovery we never request a file prior to the redo
+ 	 * pointer of the last restartpoint. When redo begins we know that we
+ 	 * have successfully located it, so there is no need for additional
+ 	 * status flags to signify the point when we can begin deleting WAL files
+ 	 * from the archive. 
+ 	 */
+ 	if (InRedo)
+ 	{
+ 		XLByteToSeg(ControlFile->checkPointCopy.redo,
+ 					restartLog, restartSeg);
+ 		XLogFileName(lastRestartPointFname,
+ 					 ControlFile->checkPointCopy.ThisTimeLineID,
+ 					 restartLog, restartSeg);
+ 	}
+ 	else
+ 		XLogFileName(lastRestartPointFname, 0, 0, 0);
+ 
+ 	/*
+ 	 * construct the command to be executed
+ 	 */
+ 	dp = xlogRecoveryEndCmd;
+ 	endp = xlogRecoveryEndCmd + MAXPGPATH - 1;
+ 	*endp = '\0';
+ 
+ 	for (sp = recoveryEndCommand; *sp; sp++)
+ 	{
+ 		if (*sp == '%')
+ 		{
+ 			switch (sp[1])
+ 			{
+ 				case 'r':
+ 					/* %r: filename of last restartpoint */
+ 					sp++;
+ 					StrNCpy(dp, lastRestartPointFname, endp - dp);
+ 					dp += strlen(dp);
+ 					break;
+ 				case '%':
+ 					/* convert %% to a single % */
+ 					sp++;
+ 					if (dp < endp)
+ 						*dp++ = *sp;
+ 					break;
+ 				default:
+ 					/* otherwise treat the % as not special */
+ 					if (dp < endp)
+ 						*dp++ = *sp;
+ 					break;
+ 			}
+ 		}
+ 		else
+ 		{
+ 			if (dp < endp)
+ 				*dp++ = *sp;
+ 		}
+ 	}
+ 	*dp = '\0';
+ 
+ 	ereport(DEBUG3,
+ 			(errmsg_internal("executing recovery end command \"%s\"",
+ 							 xlogRecoveryEndCmd)));
+ 
+ 	/*
+ 	 * Copy xlog from archival storage to XLOGDIR
+ 	 */
+ 	rc = system(xlogRecoveryEndCmd);
+ 	if (rc != 0)
+ 	{
+ 		/*
+ 		 * If the failure was due to any sort of signal, it's best to punt and
+ 		 * abort recovery. See also detailed comments on signals in 
+ 		 * RestoreArchivedFile().
+ 		 */
+ 		signaled = WIFSIGNALED(rc) || WEXITSTATUS(rc) > 125;
+ 
+ 		ereport(signaled ? FATAL : WARNING,
+ 				(errmsg("recovery_end_command \"%s\": return code %d",
+ 								xlogRecoveryEndCmd, rc)));
+ 	}
+ }
+ 
+ /*
   * Preallocate log files beyond the specified log endpoint.
   *
   * XXX this is currently extremely conservative, since it forces only one
***************
*** 4664,4669 ****
--- 4774,4786 ----
  					(errmsg("restore_command = '%s'",
  							recoveryRestoreCommand)));
  		}
+ 		else if (strcmp(tok1, "recovery_end_command") == 0)
+ 		{
+ 			recoveryEndCommand = pstrdup(tok2);
+ 			ereport(LOG,
+ 					(errmsg("recovery_end_command = '%s'",
+ 							recoveryEndCommand)));
+ 		}
  		else if (strcmp(tok1, "recovery_target_timeline") == 0)
  		{
  			rtliGiven = true;
***************
*** 5622,5627 ****
--- 5739,5747 ----
  		 * allows some extra error checking in xlog_redo.
  		 */
  		CreateCheckPoint(CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_IMMEDIATE);
+ 
+ 		if (recoveryEndCommand)
+ 			ExecuteRecoveryEndCommand();
  	}
  
  	/*

--=-Tl0cAz6Hen4an3fyAJTH--