[PATCH] Timestamp for a XLOG_BACKUP_END WAL-record
Hi,
I prepared a patch which adds a timestamp into a XLOG_BACKUP_END
WAL-record. This functionality is needed in practice when we have to
determine a recovery time of specific backup.
This code developed in compatibility with WAL segments, which do not
have a timestamp in a XLOG_BACKUP_END record.
--
Andrey Lepikhov
Postgres Professional:
https://postgrespro.com
The Russian Postgres Company
Attachments:
0001-BACKUP_END-timestamp-addition.patchtext/x-patch; name=0001-BACKUP_END-timestamp-addition.patchDownload
From 8852a64156e7726bae11c1904b142c9b157cf654 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Date: Mon, 9 Jul 2018 10:57:10 +0500
Subject: [PATCH] BACKUP_END timestamp addition
---
src/backend/access/rmgrdesc/xlogdesc.c | 14 +++++++++++++-
src/backend/access/transam/xlog.c | 7 +++++--
src/include/access/xlog_internal.h | 7 +++++++
3 files changed, 25 insertions(+), 3 deletions(-)
diff --git a/src/backend/access/rmgrdesc/xlogdesc.c b/src/backend/access/rmgrdesc/xlogdesc.c
index 00741c7..5a0d61a 100644
--- a/src/backend/access/rmgrdesc/xlogdesc.c
+++ b/src/backend/access/rmgrdesc/xlogdesc.c
@@ -87,7 +87,19 @@ xlog_desc(StringInfo buf, XLogReaderState *record)
XLogRecPtr startpoint;
memcpy(&startpoint, rec, sizeof(XLogRecPtr));
- appendStringInfo(buf, "%X/%X",
+ /* Check for the format of WAL-record with timestamp */
+ if (XLogRecGetDataLen(record) >= sizeof(xl_backup_end))
+ {
+ TimestampTz timestamp;
+
+ memcpy(×tamp, &((xl_backup_end *)rec)->timestamp, sizeof(TimestampTz));
+ appendStringInfo(buf, "%X/%X; timestamp: %s",
+ (uint32) (startpoint >> 32), (uint32) startpoint,
+ timestamptz_to_str(timestamp));
+ }
+ else
+ /* WAL-record not have a timestamp */
+ appendStringInfo(buf, "%X/%X; <no timestamp>",
(uint32) (startpoint >> 32), (uint32) startpoint);
}
else if (info == XLOG_PARAMETER_CHANGE)
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 44017d3..12a5eb6 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -9950,7 +9950,7 @@ xlog_redo(XLogReaderState *record)
{
XLogRecPtr startpoint;
- memcpy(&startpoint, XLogRecGetData(record), sizeof(startpoint));
+ memcpy(&startpoint, &((xl_backup_end *)XLogRecGetData(record))->startpoint, sizeof(startpoint));
if (ControlFile->backupStartPoint == startpoint)
{
@@ -11069,11 +11069,14 @@ do_pg_stop_backup(char *labelfile, bool waitforarchive, TimeLineID *stoptli_p)
}
else
{
+ xl_backup_end xlrec;
/*
* Write the backup-end xlog record
*/
XLogBeginInsert();
- XLogRegisterData((char *) (&startpoint), sizeof(startpoint));
+ xlrec.startpoint = startpoint;
+ xlrec.timestamp = GetCurrentTimestamp();
+ XLogRegisterData((char *) (&xlrec), sizeof(xl_backup_end));
stoppoint = XLogInsert(RM_XLOG_ID, XLOG_BACKUP_END);
stoptli = ThisTimeLineID;
diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h
index 7c76683..8c5d851 100644
--- a/src/include/access/xlog_internal.h
+++ b/src/include/access/xlog_internal.h
@@ -233,6 +233,13 @@ typedef struct xl_parameter_change
bool track_commit_timestamp;
} xl_parameter_change;
+/* BACKUP_END WAL record main data structure */
+typedef struct xl_backup_end
+{
+ XLogRecPtr startpoint;
+ TimestampTz timestamp;
+} xl_backup_end;
+
/* logs restore point */
typedef struct xl_restore_point
{
--
2.7.4
Hi,
On 2018-07-10 06:41:32 +0500, Andrey V. Lepikhov wrote:
This functionality is needed in practice when we have to determine a
recovery time of specific backup.
What do you mean by "recovery time of specific backup"?
This code developed in compatibility with WAL segments, which do not have a
timestamp in a XLOG_BACKUP_END record.
I don't understand what "compatibility with WAL segments" could mean?
And how are WAL segments related to "XLOG_BACKUP_END record", except as
to how every WAL record is related? Are you thinking about the switch
records?
Greetings,
Andres Freund
On 10.07.2018 06:45, Andres Freund wrote:
Hi,
On 2018-07-10 06:41:32 +0500, Andrey V. Lepikhov wrote:
This functionality is needed in practice when we have to determine a
recovery time of specific backup.What do you mean by "recovery time of specific backup"?
recovery time - is a time point where backup of PostgreSQL database
instance was made.
Performing database recovery, we want to know what point in time the
restored database will correspond to.
This functionality refers to improving the usability of pg_basebackup
and pg_probackup utilities.
This code developed in compatibility with WAL segments, which do not have a
timestamp in a XLOG_BACKUP_END record.I don't understand what "compatibility with WAL segments" could mean?
And how are WAL segments related to "XLOG_BACKUP_END record", except as
to how every WAL record is related? Are you thinking about the switch
records?
In this case 'compatibility' means that patched postgres codes
(pg_basebackup, pg_probackup, pg_waldump etc) will correctly read WAL
segments which not contains a timestamp field in XLOG_BACKUP_END record.
Greetings,
Andres Freund
--
Andrey Lepikhov
Postgres Professional:
https://postgrespro.com
The Russian Postgres Company
On Tue, Jul 10, 2018 at 6:41 PM, Andrey V. Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
On 10.07.2018 06:45, Andres Freund wrote:
Hi,
On 2018-07-10 06:41:32 +0500, Andrey V. Lepikhov wrote:
This functionality is needed in practice when we have to determine a
recovery time of specific backup.What do you mean by "recovery time of specific backup"?
recovery time - is a time point where backup of PostgreSQL database instance
was made.
Performing database recovery, we want to know what point in time the
restored database will correspond to.
This functionality refers to improving the usability of pg_basebackup and
pg_probackup utilities.
Why don't you use a backup history file for that purpose?
Regards,
--
Fujii Masao
On 10.07.2018 22:26, Fujii Masao wrote:
On Tue, Jul 10, 2018 at 6:41 PM, Andrey V. Lepikhov
<a.lepikhov@postgrespro.ru> wrote:On 10.07.2018 06:45, Andres Freund wrote:
Hi,
On 2018-07-10 06:41:32 +0500, Andrey V. Lepikhov wrote:
This functionality is needed in practice when we have to determine a
recovery time of specific backup.What do you mean by "recovery time of specific backup"?
recovery time - is a time point where backup of PostgreSQL database instance
was made.
Performing database recovery, we want to know what point in time the
restored database will correspond to.
This functionality refers to improving the usability of pg_basebackup and
pg_probackup utilities.Why don't you use a backup history file for that purpose?
Timestamp in a backup history file not correspond to any WAL record and
can't be bind with a time of backup exactly.
In my opinion, keeping timestamp in XLOG_BACKUP_END is more reliable,
safe and easy way for recovering a database to a specific time.
Regards,
--
Andrey Lepikhov
Postgres Professional:
https://postgrespro.com
The Russian Postgres Company
On Fri, Jul 13, 2018 at 08:13:39AM +0500, Andrey V. Lepikhov wrote:
Timestamp in a backup history file not correspond to any WAL record and
can't be bind with a time of backup exactly.
In my opinion, keeping timestamp in XLOG_BACKUP_END is more reliable, safe
and easy way for recovering a database to a specific time.
Like Andres and Fujii-san, I don't really see the point of complicating
the code for that. If your goal is to stop WAL replay once consistency
has been reached, then just use recovery_target = 'immediate' if you
fetch the data from a WAL archive and that there are still segments
after the consistency point. Or just use a self-contained backup which
has all the WAL it needs without restore_command.
If your goal is to make sure that the timestamp set in recovery.conf is
not older than the moment the backup has ended, then the backup history
file has what you are looking for. In short, in any case there is no
point in duplicating data which already exists. You can as well use
recovery_target_lsn to point exactly at the time a backup has ended as
returned by pg_stop_backup, and this even saves maths with timestamps.
--
Michael