Thread: Re: [HACKERS] Should *.backup files ever be removed from pg_xlog?
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 Index: src/backend/access/transam/xlog.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/access/transam/xlog.c,v retrieving revision 1.197 diff -c -c -r1.197 xlog.c *** src/backend/access/transam/xlog.c 6 Jun 2005 20:22:57 -0000 1.197 --- src/backend/access/transam/xlog.c 9 Jun 2005 01:52:53 -0000 *************** *** 2289,2339 **** * We use the alphanumeric sorting property of the filenames to * decide which ones are earlier than the lastoff segment. */ ! if (strlen(xlde->d_name) == 24 && ! strspn(xlde->d_name, "0123456789ABCDEF") == 24 && strcmp(xlde->d_name + 8, lastoff + 8) <= 0) { ! bool recycle; ! ! if (XLogArchivingActive()) ! recycle = XLogArchiveIsDone(xlde->d_name); ! else ! recycle = true; ! ! if (recycle) { ! snprintf(path, MAXPGPATH, "%s/%s", XLogDir, xlde->d_name); ! ! /* ! * Before deleting the file, see if it can be recycled as ! * a future log segment. ! */ ! if (InstallXLogFileSegment(&endlogId, &endlogSeg, path, ! true, &max_advance, ! true)) { ! ereport(DEBUG2, ! (errmsg("recycled transaction log file \"%s\"", ! xlde->d_name))); ! (*nsegsrecycled)++; ! /* Needn't recheck that slot on future iterations */ ! if (max_advance > 0) { ! NextLogSeg(endlogId, endlogSeg); ! max_advance--; } } ! else { - /* No need for any more future segments... */ ereport(DEBUG2, ! (errmsg("removing transaction log file \"%s\"", xlde->d_name))); unlink(path); ! (*nsegsremoved)++; } - - XLogArchiveCleanup(xlde->d_name); } } errno = 0; --- 2289,2354 ---- * We use the alphanumeric sorting property of the filenames to * decide which ones are earlier than the lastoff segment. */ ! if (strspn(xlde->d_name, "0123456789ABCDEF") == 24 && strcmp(xlde->d_name + 8, lastoff + 8) <= 0) { ! snprintf(path, MAXPGPATH, "%s/%s", XLogDir, xlde->d_name); ! ! if (strlen(xlde->d_name) == 24) { ! bool recycle; ! ! if (XLogArchivingActive()) ! recycle = XLogArchiveIsDone(xlde->d_name); ! else ! recycle = true; ! ! if (recycle) { ! /* ! * Before deleting the file, see if it can be recycled as ! * a future log segment. ! */ ! if (InstallXLogFileSegment(&endlogId, &endlogSeg, path, ! true, &max_advance, ! true)) ! { ! ereport(DEBUG2, ! (errmsg("recycled transaction log file \"%s\"", ! xlde->d_name))); ! (*nsegsrecycled)++; ! /* Needn't recheck that slot on future iterations */ ! if (max_advance > 0) ! { ! NextLogSeg(endlogId, endlogSeg); ! max_advance--; ! } ! } ! else { ! /* No need for any more future segments... */ ! ereport(DEBUG2, ! (errmsg("removing transaction log file \"%s\"", ! xlde->d_name))); ! unlink(path); ! (*nsegsremoved)++; } + XLogArchiveCleanup(xlde->d_name); } ! } ! else if (strlen(xlde->d_name) > 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 status file \"%s\"", xlde->d_name))); unlink(path); ! XLogArchiveCleanup(xlde->d_name); } } } errno = 0;
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 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 */
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