Subject: Re: Re: [COMMITTERS] pgsql: Make standby server
	continuously retry restoring the next WAL
From: Simon Riggs <simon@2ndQuadrant.com>
To: Fujii Masao <masao.fujii@gmail.com>
Cc: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>,
	Dimitri Fontaine <dfontaine@hi-media.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
In-Reply-To: <1265979080.7341.3679.camel@ebony>
References: <20100127152751.3B2047541B9@cvs.postgresql.org>
	<4B73F678.8070109@enterprisedb.com> <1265891248.7341.1346.camel@ebony>
	<4B73FB99.4080403@enterprisedb.com> <1265893599.7341.1454.camel@ebony>
	<877hqjc2kk.fsf@hi-media-techno.com> <1265896250.7341.1627.camel@ebony>
	<4B740C6C.3010607@enterprisedb.com> <1265897834.7341.1714.camel@ebony>
	<4B7412BE.5030605@enterprisedb.com>
	<3f0b79eb1002112138n61a3258fg9986e50751d44ea0@mail.gmail.com>
	<1265979080.7341.3679.camel@ebony>
Content-Type: multipart/mixed; boundary="=-mzCV/7N/YBZ/Kzy28uG9"
Date: Fri, 12 Feb 2010 14:34:29 +0000
Message-Id: <1265985269.7341.4105.camel@ebony>
Mime-Version: 1.0


--=-mzCV/7N/YBZ/Kzy28uG9
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Fri, 2010-02-12 at 12:54 +0000, Simon Riggs wrote:

> So I suggest that you have a new action that gets called after every
> checkpoint to clear down the archive. It will remove all files from the
> archive prior to %r. We can implement that as a sequence of unlink()s
> from within the server, or we can just call a script to do it. I prefer
> the latter approach. However we do it, we need something initiated by
> the server to maintain the archive and stop it from overflowing. 

Attached patch implements pg_standby for use as an
archive_cleanup_command, reusing existing code with new -a option.

e.g.

archive_cleanup_command = 'pg_standby -a -d /mnt/server/archiverdir %r'

Happy to add the archive_cleanup_command into main server as well, if
you like. Won't take long.

-- 
 Simon Riggs           www.2ndQuadrant.com

--=-mzCV/7N/YBZ/Kzy28uG9
Content-Disposition: attachment;
	filename="archive_cleanup_from_pg_standby.patch"
Content-Type: text/x-patch; name="archive_cleanup_from_pg_standby.patch";
	charset="UTF-8"
Content-Transfer-Encoding: 7bit

Index: contrib/pg_standby/pg_standby.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/contrib/pg_standby/pg_standby.c,v
retrieving revision 1.27
diff -c -r1.27 pg_standby.c
*** contrib/pg_standby/pg_standby.c	4 Nov 2009 12:51:30 -0000	1.27
--- contrib/pg_standby/pg_standby.c	12 Feb 2010 14:26:42 -0000
***************
*** 53,58 ****
--- 53,59 ----
  int			keepfiles = 0;		/* number of WAL files to keep, 0 keep all */
  int			maxretries = 3;		/* number of retries on restore command */
  bool		debug = false;		/* are we debugging? */
+ bool		cleanup_only = false;		/* -a option new for 9.0 */
  bool		need_cleanup = false;		/* do we need to remove files from
  										 * archive? */
  
***************
*** 243,249 ****
  	/*
  	 * Work out name of prior file from current filename
  	 */
! 	if (nextWALFileType == XLOG_DATA)
  	{
  		int			rc;
  		DIR		   *xldir;
--- 244,250 ----
  	/*
  	 * Work out name of prior file from current filename
  	 */
! 	if (cleanup_only || nextWALFileType == XLOG_DATA)
  	{
  		int			rc;
  		DIR		   *xldir;
***************
*** 334,340 ****
  		 * these files from archive. This shouldn't happen, but better safe
  		 * than sorry.
  		 */
! 		if (strcmp(restartWALFileName, nextWALFileName) > 0)
  			return false;
  
  		strcpy(exclusiveCleanupFileName, restartWALFileName);
--- 335,341 ----
  		 * these files from archive. This shouldn't happen, but better safe
  		 * than sorry.
  		 */
! 		if (!cleanup_only && strcmp(restartWALFileName, nextWALFileName) > 0)
  			return false;
  
  		strcpy(exclusiveCleanupFileName, restartWALFileName);
***************
*** 512,518 ****
  static void
  usage(void)
  {
! 	printf("%s allows PostgreSQL warm standby servers to be configured.\n\n", progname);
  	printf("Usage:\n");
  	printf("  %s [OPTION]... ARCHIVELOCATION NEXTWALFILE XLOGFILEPATH [RESTARTWALFILE]\n", progname);
  	printf("\n"
--- 513,519 ----
  static void
  usage(void)
  {
! 	printf("%s allows PostgreSQL standby servers to be configured.\n\n", progname);
  	printf("Usage:\n");
  	printf("  %s [OPTION]... ARCHIVELOCATION NEXTWALFILE XLOGFILEPATH [RESTARTWALFILE]\n", progname);
  	printf("\n"
***************
*** 520,526 ****
--- 521,534 ----
  		   "  restore_command = 'pg_standby [OPTION]... ARCHIVELOCATION %%f %%p %%r'\n"
  		   "e.g.\n"
  		   "  restore_command = 'pg_standby -l /mnt/server/archiverdir %%f %%p %%r'\n");
+ 	printf("  %s -a ARCHIVELOCATION RESTARTWALFILE\n", progname);
+ 	printf("\n"
+ 		"with main intended use as an archive_cleanup_command in the recovery.conf:\n"
+ 		   "  archive_cleanup_command = 'pg_standby -a ARCHIVELOCATION %%r'\n"
+ 		   "e.g.\n"
+ 		   "  archive_cleanup_command = 'pg_standby -a -d /mnt/server/archiverdir %%r'\n");
  	printf("\nOptions:\n");
+ 	printf("  -a                 cleans archive only\n");
  	printf("  -c                 copies file from archive (default)\n");
  	printf("  -d                 generate lots of debugging output (testing only)\n");
  	printf("  -k NUMFILESTOKEEP  if RESTARTWALFILE not used, removes files prior to limit\n"
***************
*** 595,604 ****
  	(void) signal(SIGQUIT, sigquit_handler);
  #endif
  
! 	while ((c = getopt(argc, argv, "cdk:lr:s:t:w:")) != -1)
  	{
  		switch (c)
  		{
  			case 'c':			/* Use copy */
  				restoreCommandType = RESTORE_COMMAND_COPY;
  				break;
--- 603,615 ----
  	(void) signal(SIGQUIT, sigquit_handler);
  #endif
  
! 	while ((c = getopt(argc, argv, "acdk:lr:s:t:w:")) != -1)
  	{
  		switch (c)
  		{
+ 			case 'a':			/* Cleanup only */
+ 				cleanup_only = true;
+ 				break;
  			case 'c':			/* Use copy */
  				restoreCommandType = RESTORE_COMMAND_COPY;
  				break;
***************
*** 684,734 ****
  		exit(2);
  	}
  
! 	if (optind < argc)
! 	{
! 		nextWALFileName = argv[optind];
! 		optind++;
! 	}
! 	else
  	{
! 		fprintf(stderr, "%s: use %%f to specify nextWALFileName\n", progname);
! 		fprintf(stderr, "Try \"%s --help\" for more information.\n", progname);
! 		exit(2);
  	}
  
  	if (optind < argc)
  	{
! 		xlogFilePath = argv[optind];
  		optind++;
  	}
! 	else
  	{
! 		fprintf(stderr, "%s: use %%p to specify xlogFilePath\n", progname);
  		fprintf(stderr, "Try \"%s --help\" for more information.\n", progname);
  		exit(2);
  	}
  
- 	if (optind < argc)
- 	{
- 		restartWALFileName = argv[optind];
- 		optind++;
- 	}
- 
  	CustomizableInitialize();
  
  	need_cleanup = SetWALFileNameForCleanup();
  
  	if (debug)
  	{
! 		fprintf(stderr, "Trigger file 		: %s\n", triggerPath ? triggerPath : "<not set>");
! 		fprintf(stderr, "Waiting for WAL file	: %s\n", nextWALFileName);
! 		fprintf(stderr, "WAL file path		: %s\n", WALFilePath);
! 		fprintf(stderr, "Restoring to		: %s\n", xlogFilePath);
! 		fprintf(stderr, "Sleep interval		: %d second%s\n",
! 				sleeptime, (sleeptime > 1 ? "s" : " "));
! 		fprintf(stderr, "Max wait interval	: %d %s\n",
! 				maxwaittime, (maxwaittime > 0 ? "seconds" : "forever"));
! 		fprintf(stderr, "Command for restore	: %s\n", restoreCommand);
  		fprintf(stderr, "Keep archive history	: ");
  		if (need_cleanup)
  			fprintf(stderr, "%s and later\n", exclusiveCleanupFileName);
--- 695,758 ----
  		exit(2);
  	}
  
! 	if (!cleanup_only)
  	{
! 		if (optind < argc)
! 		{
! 			nextWALFileName = argv[optind];
! 			optind++;
! 		}
! 		else
! 		{
! 			fprintf(stderr, "%s: use %%f to specify nextWALFileName\n", progname);
! 			fprintf(stderr, "Try \"%s --help\" for more information.\n", progname);
! 			exit(2);
! 		}
! 
! 		if (optind < argc)
! 		{
! 			xlogFilePath = argv[optind];
! 			optind++;
! 		}
! 		else
! 		{
! 			fprintf(stderr, "%s: use %%p to specify xlogFilePath\n", progname);
! 			fprintf(stderr, "Try \"%s --help\" for more information.\n", progname);
! 			exit(2);
! 		}
  	}
  
  	if (optind < argc)
  	{
! 		restartWALFileName = argv[optind];
  		optind++;
  	}
! 
! 	if (cleanup_only && !restartWALFileName)	
  	{
! 		fprintf(stderr, "%s: must specify restart WAL filename\n", progname);
  		fprintf(stderr, "Try \"%s --help\" for more information.\n", progname);
  		exit(2);
  	}
  
  	CustomizableInitialize();
  
  	need_cleanup = SetWALFileNameForCleanup();
  
  	if (debug)
  	{
! 		if (!cleanup_only)		
! 		{
! 			fprintf(stderr, "Trigger file 		: %s\n", triggerPath ? triggerPath : "<not set>");
! 			fprintf(stderr, "Waiting for WAL file	: %s\n", nextWALFileName);
! 			fprintf(stderr, "WAL file path		: %s\n", WALFilePath);
! 			fprintf(stderr, "Restoring to		: %s\n", xlogFilePath);
! 			fprintf(stderr, "Sleep interval		: %d second%s\n",
! 					sleeptime, (sleeptime > 1 ? "s" : " "));
! 			fprintf(stderr, "Max wait interval	: %d %s\n",
! 					maxwaittime, (maxwaittime > 0 ? "seconds" : "forever"));
! 			fprintf(stderr, "Command for restore	: %s\n", restoreCommand);
! 		}
  		fprintf(stderr, "Keep archive history	: ");
  		if (need_cleanup)
  			fprintf(stderr, "%s and later\n", exclusiveCleanupFileName);
***************
*** 738,743 ****
--- 762,777 ----
  	}
  
  	/*
+ 	 * Cleanup archive immediately if that's all we need to do.
+ 	 */
+ 	if (cleanup_only)		
+ 	{
+ 		if (need_cleanup)
+ 			CustomizableCleanupPriorWALFiles();
+ 		exit(0);
+ 	}
+ 
+ 	/*
  	 * Check for initial history file: always the first file to be requested
  	 * It's OK if the file isn't there - all other files need to wait
  	 */

--=-mzCV/7N/YBZ/Kzy28uG9--