Re: Command to prune archive at restartpoints

Dimitri Fontaine <dfontaine@hi-media.com> writes:
> Also, should I try to send a patch implementing my proposal (internal
> command exposed as a function at the SQL level, and while at it, maybe
> the internal command "pg_archive_bypass" to mimic /usr/bin/true as an
> archive_command)?

I had to have a try at it, even if quick and dirty. I've not tried to
code the pg_archive_bypass internal command for lack of discussion, but
I still think it would be great to have it.

So here's a "see my idea in code" patch, that put the previous code by
Simon into a backend function. As the goal was not to adapt the existing
code intended as external to use the internal APIs, you'll find it quite
ugly I'm sure.

For example, this #define XLOG_DATA_FNAME_LEN has to go away, but that
won't help having the idea accepted or not, and as I'm only warming up,
I didn't tackle the problem. If you want me to do it, I'd appreciate
some guidance as how to, though.

It goes like this:

dim=# select pg_switch_xlog();
 pg_switch_xlog
----------------
 0/1000098
(1 row)

dim=# select pg_archive_cleanup('0/1000098');
DEBUG:  removing "pg_xlog/000000010000000000000000"
DEBUG:  removing "pg_xlog/000000010000000000000001"
 pg_archive_cleanup
--------------------
 t
(1 row)

I hope you too will find this way of interfacing is easier to deal with
for everybody (from code maintenance to user settings).

Regards,
--
dim

*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***************
*** 599,605 **** static bool read_backup_label(XLogRecPtr *checkPointLoc);
  static void rm_redo_error_callback(void *arg);
  static int    get_sync_bit(int method);

-
  /*
   * Insert an XLOG record having the specified RMID and info bytes,
   * with the body of the record being the data chunk(s) described by
--- 599,604 ----
***************
*** 3056,3062 **** not_available:
  }

  /*
!  * Attempt to execute an external shell command during recovery.
   *
   * 'command' is the shell command to be executed, 'commandName' is a
   * human-readable name describing the command emitted in the logs. If
--- 3055,3065 ----
  }

  /*
!  * Attempt to execute an external shell command during recovery, or an
!  * internal one if given one.
!  *
!  * There's only one supported internal commands today, which is named
!  * "pg_archive_cleanup".
   *
   * 'command' is the shell command to be executed, 'commandName' is a
   * human-readable name describing the command emitted in the logs. If
***************
*** 3094,3145 **** ExecuteRecoveryCommand(char *command, char *commandName, bool failOnSignal)
      LWLockRelease(ControlFileLock);

      /*
!      * construct the command to be executed
       */
!     dp = xlogRecoveryCmd;
!     endp = xlogRecoveryCmd + MAXPGPATH - 1;
!     *endp = '\0';

!     for (sp = command; *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 %s \"%s\"", commandName, command)));

-     /*
-      * execute the constructed command
-      */
-     rc = system(xlogRecoveryCmd);
      if (rc != 0)
      {
          /*
--- 3097,3164 ----
      LWLockRelease(ControlFileLock);

      /*
!      * if the command is internal, call the function
       */
!     if( strcmp(command, "pg_archive_cleanup") == 0 )
!     {
!         text *restart_filename = cstring_to_text(lastRestartPointFname);
!         rc = DatumGetBool(DirectFunctionCall1(pg_archive_cleanup,
!                                               restart_filename)) ? 0 : 1;

!         ereport(DEBUG3,
!                 (errmsg_internal("calling %s \"%s\"", commandName, command)));
!     }
!     else
      {
!         /*
!          * construct the command to be executed
!          */
!         dp = xlogRecoveryCmd;
!         endp = xlogRecoveryCmd + MAXPGPATH - 1;
!         *endp = '\0';
!
!         for (sp = command; *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 %s \"%s\"", commandName, command)));
!
!         /*
!          * execute the constructed command
!          */
!         rc = system(xlogRecoveryCmd);
!     }

      if (rc != 0)
      {
          /*
***************
*** 8916,8921 **** pg_xlogfile_name(PG_FUNCTION_ARGS)
--- 8935,9008 ----
  }

  /*
+  * Cleanup XLOGDIR given a specific WAL file name, which we keep. Typically
+  * used as an archive_cleanup_command.
+  */
+ Datum
+ pg_archive_cleanup(PG_FUNCTION_ARGS)
+ {
+     text       *location = PG_GETARG_TEXT_P(0);
+     char        *exclusiveCleanupFileName = text_to_cstring(location);
+     char        WALFilePath[MAXPGPATH]; /* the file path including archive */
+     int            rc = 0;                 /* nothing to do ain't an error */
+     DIR           *xldir;
+     struct dirent *xlde;
+
+     if ((xldir = opendir(XLOGDIR)) != NULL)
+     {
+         while ((xlde = readdir(xldir)) != NULL)
+         {
+             /*
+              * We ignore the timeline part of the XLOG segment identifiers
+              * in deciding whether a segment is still needed.  This
+              * ensures that we won't prematurely remove a segment from a
+              * parent timeline. We could probably be a little more
+              * proactive about removing segments of non-parent timelines,
+              * but that would be a whole lot more complicated.
+              *
+              * We use the alphanumeric sorting property of the filenames
+              * to decide which ones are earlier than the
+              * exclusiveCleanupFileName file. Note that this means files
+              * are not removed in the order they were originally written,
+              * in case this worries you.
+              */
+             #define XLOG_DATA_FNAME_LEN 24
+             if (strlen(xlde->d_name) == XLOG_DATA_FNAME_LEN &&
+                 strspn(xlde->d_name, "0123456789ABCDEF") == XLOG_DATA_FNAME_LEN &&
+                 strcmp(xlde->d_name + 8, exclusiveCleanupFileName + 8) < 0)
+             {
+ #ifdef WIN32
+                 snprintf(WALFilePath, MAXPGPATH, "%s\\%s", XLOGDIR, xlde->d_name);
+ #else
+                 snprintf(WALFilePath, MAXPGPATH, "%s/%s", XLOGDIR, xlde->d_name);
+ #endif
+
+                 elog(DEBUG1, "removing \"%s\"", WALFilePath);
+
+                 rc = unlink(WALFilePath);
+                 if (rc != 0)
+                 {
+                     elog(ERROR,
+                             "failed to remove \"%s\": %s",
+                             WALFilePath, strerror(errno));
+                     break;
+                 }
+             }
+         }
+     }
+     else
+         /*
+          * How much do we want to manage that? ereport?
+          */
+         elog(ERROR, "Can't open \"%s\"", XLOGDIR);
+
+     closedir(xldir);
+     fflush(stderr);
+
+     PG_RETURN_BOOL(rc == 0);
+ }
+
+ /*
   * read_backup_label: check to see if a backup_label file is present
   *
   * If we see a backup_label during recovery, we assume that we are recovering
*** a/src/include/access/xlog_internal.h
--- b/src/include/access/xlog_internal.h
***************
*** 273,278 **** extern Datum pg_last_xlog_receive_location(PG_FUNCTION_ARGS);
--- 273,279 ----
  extern Datum pg_last_xlog_replay_location(PG_FUNCTION_ARGS);
  extern Datum pg_xlogfile_name_offset(PG_FUNCTION_ARGS);
  extern Datum pg_xlogfile_name(PG_FUNCTION_ARGS);
+ extern Datum pg_archive_cleanup(PG_FUNCTION_ARGS);
  extern Datum pg_is_in_recovery(PG_FUNCTION_ARGS);

  #endif   /* XLOG_INTERNAL_H */
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3314,3319 **** DATA(insert OID = 2850 ( pg_xlogfile_name_offset    PGNSP PGUID 12 1 0 0 f f f t f
--- 3314,3321 ----
  DESCR("xlog filename and byte offset, given an xlog location");
  DATA(insert OID = 2851 ( pg_xlogfile_name            PGNSP PGUID 12 1 0 0 f f f t f i 1 0 25 "25" _null_ _null_
_null__null_ pg_xlogfile_name _null_ _null_ _null_ )); 
  DESCR("xlog filename, given an xlog location");
+ DATA(insert OID = 3822 ( pg_archive_cleanup            PGNSP PGUID 12 1 0 0 f f f t f i 1 0 16 "25" _null_ _null_
_null__null_ pg_archive_cleanup _null_ _null_ _null_ )); 
+ DESCR("cleanup archive up to given xlog location");

  DATA(insert OID = 3810 (  pg_is_in_recovery        PGNSP PGUID 12 1 0 0 f f f t f v 0 0 16 "" _null_ _null_ _null_
_null_pg_is_in_recovery _null_ _null_ _null_ )); 
  DESCR("true if server is in recovery");