Should *.backup files ever be removed from pg_xlog?

Bruce Momjian <pgman@candle.pha.pa.us> writes:

When you do a pg_start_backup()/pg_stop_backup(), the *.backup files
created in pg_xlog are never deleted. Is that intended?

Yes. See the documentation. DBAs can delete 'em if they feel like,
but I don't see a strong argument for automatically removing 'em.
They aren't actually large ...

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

When you do a pg_start_backup()/pg_stop_backup(), the *.backup files
created in pg_xlog are never deleted. Is that intended?

Yes. See the documentation. DBAs can delete 'em if they feel like,
but I don't see a strong argument for automatically removing 'em.
They aren't actually large ...

I don't see anywhere in the documentation where we say you can get rid
of them. I see this:

For example, if the starting WAL file is 0000000100001234000055CD the
backup history file will be named something like
0000000100001234000055CD.007C9330.backup. (The second number in the file
name stands for an exact position within the WAL file, and can
ordinarily be ignored.) Once you have safely archived the file system
backup and the WAL segment files used during the backup (as specified in
the backup history file), all archived WAL segments with names
numerically less are no longer needed to recover the file system backup
and may be deleted. However, you should consider keeping several backup
sets to be absolutely certain that you are can recover your data. Keep
in mind that only completed WAL segment files are archived, so there
will be delay between running pg_stop_backup and the archiving of all
WAL segment files needed to make the file system backup consistent.

The "all archived WAL segments with names numerically less are no longer
needed" I assume is talking about files in the archive location, not
pg_xlog. Does this need clarifying?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

When you do a pg_start_backup()/pg_stop_backup(), the *.backup files
created in pg_xlog are never deleted. Is that intended?

Yes. See the documentation. DBAs can delete 'em if they feel like,
but I don't see a strong argument for automatically removing 'em.
They aren't actually large ...

I don't see anywhere in the documentation where we say you can get rid
of them. I see this:

For example, if the starting WAL file is 0000000100001234000055CD the
backup history file will be named something like
0000000100001234000055CD.007C9330.backup. (The second number in the file
name stands for an exact position within the WAL file, and can
ordinarily be ignored.) Once you have safely archived the file system
backup and the WAL segment files used during the backup (as specified in
the backup history file), all archived WAL segments with names
numerically less are no longer needed to recover the file system backup
and may be deleted. However, you should consider keeping several backup
sets to be absolutely certain that you are can recover your data. Keep
in mind that only completed WAL segment files are archived, so there
will be delay between running pg_stop_backup and the archiving of all
WAL segment files needed to make the file system backup consistent.

The "all archived WAL segments with names numerically less are no longer
needed" I assume is talking about files in the archive location, not
pg_xlog. Does this need clarifying?

I have fixed the code to properly remove *.backup files from the
/pg_xlog directory once they are archived. The patch is larger because
of an indenting change --- the 'else if' part is the new part.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I have fixed the code to properly remove *.backup files from the
/pg_xlog directory once they are archived.

I do not actually think that this is a good idea. The .backup files are
not large and they are pretty critical info --- so having multiple
copies of them seems like a good idea. We don't auto-remove timeline
history files either, for the same reason.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I have fixed the code to properly remove *.backup files from the
/pg_xlog directory once they are archived.

I do not actually think that this is a good idea. The .backup files are
not large and they are pretty critical info --- so having multiple
copies of them seems like a good idea. We don't auto-remove timeline
history files either, for the same reason.

I realize the files are small, but the number of entries in the
directory might get pretty large. I am also worried about folks
realizing we are not cleaning out those directories and mucking in there
when they shouldn't --- I already got a private email asking why there
are so many files in there and can they be cleared out. In fact, the
archive_status/ directory also keeps *.done files for the backup files.

We knowe we have archived the *.backup files. Are these files any use
if we can't get the archive files restored?

OK, how about if we do this cleanup when we do pg_stop_backup() so we
delete any previous *.backup files, rather than do it as part of
checkpoint.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I have fixed the code to properly remove *.backup files from the
/pg_xlog directory once they are archived.

I do not actually think that this is a good idea. The .backup files are
not large and they are pretty critical info --- so having multiple
copies of them seems like a good idea. We don't auto-remove timeline
history files either, for the same reason.

I realize the files are small, but the number of entries in the
directory might get pretty large. I am also worried about folks
realizing we are not cleaning out those directories and mucking in there
when they shouldn't --- I already got a private email asking why there
are so many files in there and can they be cleared out. In fact, the
archive_status/ directory also keeps *.done files for the backup files.

We knowe we have archived the *.backup files. Are these files any use
if we can't get the archive files restored?

OK, how about if we do this cleanup when we do pg_stop_backup() so we
delete any previous *.backup files, rather than do it as part of
checkpoint.

OK, new version that removes old backup files only when pg_stop_backup()
is run. This way the current backup file will always be in pg_xlog/.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Applied. (Thanks (to me). :-))

---------------------------------------------------------------------------

Bruce Momjian wrote:

Bruce Momjian wrote:

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I have fixed the code to properly remove *.backup files from the
/pg_xlog directory once they are archived.

I do not actually think that this is a good idea. The .backup files are
not large and they are pretty critical info --- so having multiple
copies of them seems like a good idea. We don't auto-remove timeline
history files either, for the same reason.

I realize the files are small, but the number of entries in the
directory might get pretty large. I am also worried about folks
realizing we are not cleaning out those directories and mucking in there
when they shouldn't --- I already got a private email asking why there
are so many files in there and can they be cleared out. In fact, the
archive_status/ directory also keeps *.done files for the backup files.

We knowe we have archived the *.backup files. Are these files any use
if we can't get the archive files restored?

OK, how about if we do this cleanup when we do pg_stop_backup() so we
delete any previous *.backup files, rather than do it as part of
checkpoint.

OK, new version that removes old backup files only when pg_stop_backup()
is run. This way the current backup file will always be in pg_xlog/.

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.198
diff -c -c -r1.198 xlog.c
*** src/backend/access/transam/xlog.c	8 Jun 2005 15:50:26 -0000	1.198
--- src/backend/access/transam/xlog.c	9 Jun 2005 14:52:11 -0000
***************
*** 450,455 ****
--- 450,456 ----
static int	PreallocXlogFiles(XLogRecPtr endptr);
static void MoveOfflineLogs(uint32 log, uint32 seg, XLogRecPtr endptr,
int *nsegsremoved, int *nsegsrecycled);
+ static void RemoveOldBackupHistory(void);
static XLogRecord *ReadRecord(XLogRecPtr *RecPtr, int emode);
static bool ValidXLOGHeader(XLogPageHeader hdr, int emode);
static XLogRecord *ReadCheckpointRecord(XLogRecPtr RecPtr, int whichChkpt);
***************
*** 2356,2361 ****
--- 2357,2417 ----
}

/*
+  * Remove previous backup history files
+  */
+ static void
+ RemoveOldBackupHistory(void)
+ {
+ 	DIR		   *xldir;
+ 	struct dirent *xlde;
+ 	char		path[MAXPGPATH];
+ 
+ 	xldir = AllocateDir(XLogDir);
+ 	if (xldir == NULL)
+ 		ereport(ERROR,
+ 				(errcode_for_file_access(),
+ 			errmsg("could not open transaction log directory \"%s\": %m",
+ 				   XLogDir)));
+ 
+ 	errno = 0;
+ 	while ((xlde = readdir(xldir)) != NULL)
+ 	{
+ 		if (strlen(xlde->d_name) > 24 &&
+ 			strspn(xlde->d_name, "0123456789ABCDEF") == 24 &&
+ 			strcmp(xlde->d_name + strlen(xlde->d_name) - strlen(".backup"),
+ 				   ".backup") == 0)
+ 		{
+ 			/* Remove any *.backup files that have been archived. */
+ 			if (!XLogArchivingActive() || XLogArchiveIsDone(xlde->d_name))
+ 			{
+ 				ereport(DEBUG2,
+ 					  (errmsg("removing transaction log backup history file \"%s\"",
+ 							  xlde->d_name)));
+ 				snprintf(path, MAXPGPATH, "%s/%s", XLogDir, xlde->d_name);
+ 				unlink(path);
+ 				XLogArchiveCleanup(xlde->d_name);
+ 			}
+ 		}
+ 		errno = 0;
+ 	}
+ #ifdef WIN32
+ 
+ 	/*
+ 	 * This fix is in mingw cvs (runtime/mingwex/dirent.c rev 1.4), but
+ 	 * not in released version
+ 	 */
+ 	if (GetLastError() == ERROR_NO_MORE_FILES)
+ 		errno = 0;
+ #endif
+ 	if (errno)
+ 		ereport(ERROR,
+ 				(errcode_for_file_access(),
+ 			errmsg("could not read transaction log directory \"%s\": %m",
+ 				   XLogDir)));
+ 	FreeDir(xldir);
+ }
+ 
+ /*
* Restore the backup blocks present in an XLOG record, if any.
*
* We assume all of the record has been read into memory at *record.
***************
*** 5737,5742 ****
--- 5793,5800 ----
errmsg("could not remove file \"%s\": %m",
labelfilepath)));

+ RemoveOldBackupHistory();
+
/*
* Notify archiver that history file may be archived immediately
*/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073