Good day, hackers. Here I am to suggest two small improvements to Point In Time Recovery.
First is ability to recover recovery-target-time with timestamp stored in XLOG_RESTORE_POINT. Looks like historically this ability did exist and were removed unintentionally during refactoring at commit [1]
c945af80 "Refactor checking whether we've reached the recovery target."Second is extending XLOG_BACKUP_END record with timestamp, therefore backup will have its own timestamp as well. It is backward compatible change since there were no record length check before.
Both changes slightly helps in mostly idle systems, when between several backups may happens no commits at all, so there's no timestamp to recover to.
Attached sample patches are made in reverse order: - XLOG_BACKUP_END then XLOG_RESTORE_POINT. Second patch made by colleague by my idea. Publishing for both is permitted. If idea is accepted, patches for tests will be applied as well. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=c945af80 --- Yura Sokolov.
From 173cfc3762a97c300b618f863fd23433909cdb81 Mon Sep 17 00:00:00 2001 From: Yura Sokolov <y.soko...@postgrespro.ru> Date: Wed, 3 May 2023 18:48:46 +0300 Subject: [PATCH] PGPRO-8083: record timestamp in XLOG_BACKUP_END for recovery_target_time It is useful for pg_probackup to recover on backup end. Tags: pg_probackup --- src/backend/access/rmgrdesc/xlogdesc.c | 15 +++++++-- src/backend/access/transam/xlog.c | 6 ++-- src/backend/access/transam/xlogrecovery.c | 39 +++++++++++++++++++++++ src/include/access/xlog_internal.h | 7 ++++ 4 files changed, 62 insertions(+), 5 deletions(-) diff --git a/src/backend/access/rmgrdesc/xlogdesc.c b/src/backend/access/rmgrdesc/xlogdesc.c index 3fd7185f217..e1114168239 100644 --- a/src/backend/access/rmgrdesc/xlogdesc.c +++ b/src/backend/access/rmgrdesc/xlogdesc.c @@ -86,10 +86,19 @@ xlog_desc(StringInfo buf, XLogReaderState *record) } else if (info == XLOG_BACKUP_END) { - XLogRecPtr startpoint; + xl_backup_end xlrec = {0, 0}; + size_t rec_len = XLogRecGetDataLen(record); - memcpy(&startpoint, rec, sizeof(XLogRecPtr)); - appendStringInfo(buf, "%X/%X", LSN_FORMAT_ARGS(startpoint)); + if (rec_len == sizeof(XLogRecPtr)) + memcpy(&xlrec.startpoint, rec, sizeof(XLogRecPtr)); + else if (rec_len >= sizeof(xl_backup_end)) + memcpy(&xlrec, rec, sizeof(xl_backup_end)); + + appendStringInfo(buf, "%X/%X", LSN_FORMAT_ARGS(xlrec.startpoint)); + + if (rec_len >= sizeof(xl_backup_end)) + appendStringInfo(buf, " at %s", + timestamptz_to_str(xlrec.end_time)); } else if (info == XLOG_PARAMETER_CHANGE) { diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 5ebb9783e2f..42cd06cd7d8 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -8680,14 +8680,16 @@ do_pg_backup_stop(BackupState *state, bool waitforarchive) } else { + xl_backup_end xlrec; char *history_file; /* * Write the backup-end xlog record */ + xlrec.startpoint = state->startpoint; + xlrec.end_time = GetCurrentTimestamp(); XLogBeginInsert(); - XLogRegisterData((char *) (&state->startpoint), - sizeof(state->startpoint)); + XLogRegisterData((char *) (&xlrec), sizeof(xlrec)); state->stoppoint = XLogInsert(RM_XLOG_ID, XLOG_BACKUP_END); /* diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c index 11747a6ff13..42d5b59ac25 100644 --- a/src/backend/access/transam/xlogrecovery.c +++ b/src/backend/access/transam/xlogrecovery.c @@ -2312,6 +2312,13 @@ getRecordTimestamp(XLogReaderState *record, TimestampTz *recordXtime) *recordXtime = ((xl_restore_point *) XLogRecGetData(record))->rp_time; return true; } + if (rmid == RM_XLOG_ID && info == XLOG_BACKUP_END) + { + if (XLogRecGetDataLen(record) < sizeof(xl_backup_end)) + return false; + *recordXtime = ((xl_backup_end *) XLogRecGetData(record))->end_time; + return true; + } if (rmid == RM_XACT_ID && (xact_info == XLOG_XACT_COMMIT || xact_info == XLOG_XACT_COMMIT_PREPARED)) { @@ -2640,6 +2647,38 @@ recoveryStopsAfter(XLogReaderState *record) } } + if (recoveryTarget == RECOVERY_TARGET_TIME && + rmid == RM_XLOG_ID && info == XLOG_BACKUP_END) + { + bool stopsHere = false; + + if (getRecordTimestamp(record, &recordXtime)) + { + /* + * Use same conditions as in recoveryStopsBefore for transaction + * records to not override transactions time handling. + */ + if (recoveryTargetInclusive) + stopsHere = recordXtime > recoveryTargetTime; + else + stopsHere = recordXtime >= recoveryTargetTime; + } + + if (stopsHere) + { + recoveryStopAfter = true; + recoveryStopXid = InvalidTransactionId; + recoveryStopLSN = InvalidXLogRecPtr; + recoveryStopTime = recordXtime; + recoveryStopName[0] = '\0'; + + ereport(LOG, + (errmsg("recovery stopping at backup end at time %s", + timestamptz_to_str(recoveryStopTime)))); + return true; + } + } + /* Check if the target LSN has been reached */ if (recoveryTarget == RECOVERY_TARGET_LSN && recoveryTargetInclusive && diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h index e5fc66966bc..1bd0daf14a0 100644 --- a/src/include/access/xlog_internal.h +++ b/src/include/access/xlog_internal.h @@ -289,6 +289,13 @@ typedef struct xl_restore_point char rp_name[MAXFNAMELEN]; } xl_restore_point; +/* backup end record */ +typedef struct xl_backup_end +{ + XLogRecPtr startpoint; + TimestampTz end_time; +} xl_backup_end; + /* Overwrite of prior contrecord */ typedef struct xl_overwrite_contrecord { -- GitLab
From 1147eccc124864fcac2967d162522e5bc75708b3 Mon Sep 17 00:00:00 2001 From: Sergey Fukanchik <s.fukanc...@postgrespro.ru> Date: Thu, 4 May 2023 14:45:47 +0300 Subject: [PATCH] PGPRO-8081: Use XLOG_RESTORE_POINT for recovery_target_time It is useful for pg_probackup to recover on restore point. Tags: pg_probackup --- src/backend/access/transam/xlogrecovery.c | 28 +++++++++++++++++++---- 1 file changed, 23 insertions(+), 5 deletions(-) diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c index 42d5b59ac25..4676937d5cb 100644 --- a/src/backend/access/transam/xlogrecovery.c +++ b/src/backend/access/transam/xlogrecovery.c @@ -2648,7 +2648,8 @@ recoveryStopsAfter(XLogReaderState *record) } if (recoveryTarget == RECOVERY_TARGET_TIME && - rmid == RM_XLOG_ID && info == XLOG_BACKUP_END) + rmid == RM_XLOG_ID && (info == XLOG_BACKUP_END || + info == XLOG_RESTORE_POINT)) { bool stopsHere = false; @@ -2670,11 +2671,28 @@ recoveryStopsAfter(XLogReaderState *record) recoveryStopXid = InvalidTransactionId; recoveryStopLSN = InvalidXLogRecPtr; recoveryStopTime = recordXtime; - recoveryStopName[0] = '\0'; - ereport(LOG, - (errmsg("recovery stopping at backup end at time %s", - timestamptz_to_str(recoveryStopTime)))); + if (info == XLOG_BACKUP_END) + { + recoveryStopName[0] = '\0'; + + ereport(LOG, + (errmsg("recovery stopping at backup end at time %s", + timestamptz_to_str(recoveryStopTime)))); + } + else if (info == XLOG_RESTORE_POINT) + { + xl_restore_point *recordRestorePointData; + + recordRestorePointData = (xl_restore_point *) XLogRecGetData(record); + + strlcpy(recoveryStopName, recordRestorePointData->rp_name, MAXFNAMELEN); + + ereport(LOG, + (errmsg("recovery stopping at restore point \"%s\", time %s", + recoveryStopName, + timestamptz_to_str(recoveryStopTime)))); + } return true; } } -- GitLab