Hi,
Here is a patch that implements "named restore points".
It allows DBAs to specify an exact point to which they can recover
but that point will have a name, so they have a better control of when
they want to stop recovery (ie: DBA's won't depend of remember
specific times, dates and such).
This adds a new function: pg_create_restore_point(text) (i'm not
wedded with the name so if someone wants to suggest something better,
that's fine with me), a new xlog record and a new recovery_target
parameter in recovery.conf
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index db7c834..e12720c 100644
*** a/doc/src/sgml/backup.sgml
--- b/doc/src/sgml/backup.sgml
*************** restore_command = 'cp /mnt/server/archiv
*** 1075,1083 ****
the junior DBA dropped your main transaction table), just specify the
required stopping point in <filename>recovery.conf</>. You can specify
the stop point, known as the <quote>recovery target</>, either by
! date/time or by completion of a specific transaction ID. As of this
! writing only the date/time option is very usable, since there are no tools
! to help you identify with any accuracy which transaction ID to use.
</para>
<note>
--- 1075,1084 ----
the junior DBA dropped your main transaction table), just specify the
required stopping point in <filename>recovery.conf</>. You can specify
the stop point, known as the <quote>recovery target</>, either by
! date/time, a named restore point or by completion of a specific transaction ID.
! As of this writing, there are no tools to help you identify with any accuracy
! which transaction ID to use so only date/time and named restore points are
! useful.
</para>
<note>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 04769f1..d335104 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT set_config('log_statement_stats',
*** 13930,13935 ****
--- 13930,13938 ----
<primary>pg_switch_xlog</primary>
</indexterm>
<indexterm>
+ <primary>pg_create_restore_point</primary>
+ </indexterm>
+ <indexterm>
<primary>pg_xlogfile_name</primary>
</indexterm>
<indexterm>
*************** SELECT set_config('log_statement_stats',
*** 13988,13993 ****
--- 13991,14003 ----
</row>
<row>
<entry>
+ <literal><function>pg_create_restore_point(<parameter>name</> <type>text</> )</function></literal>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>Establish a named point for restore (restricted to superusers)</entry>
+ </row>
+ <row>
+ <entry>
<literal><function>pg_xlogfile_name(<parameter>location</> <type>text</>)</function></literal>
</entry>
<entry><type>text</type></entry>
diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml
index bd9dfd1..b3fc001 100644
*** a/doc/src/sgml/recovery-config.sgml
--- b/doc/src/sgml/recovery-config.sgml
*************** restore_command = 'copy "C:\\server\\arc
*** 144,149 ****
--- 144,171 ----
<title>Recovery target settings</title>
<variablelist>
+ <varlistentry id="recovery-target-name" xreflabel="recovery_target_name">
+ <term><varname>recovery_target_name</varname>
+ (<type>string</type>)
+ </term>
+ <indexterm>
+ <primary><varname>recovery_target_name</> recovery parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ This parameter specifies a named restore point, created with
+ <function>pg_create_restore_point()</>, to which recovery
+ will proceed.
+ At most one of <varname>recovery_target_name</>,
+ <xref linkend="recovery-target-time"> or
+ <xref linkend="recovery-target-xid"> can be specified.
+ The default is to recover to the end of the WAL log.
+ The precise stopping point is also influenced by
+ <xref linkend="recovery-target-inclusive">.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="recovery-target-time" xreflabel="recovery_target_time">
<term><varname>recovery_target_time</varname>
(<type>timestamp</type>)
*************** restore_command = 'copy "C:\\server\\arc
*** 155,161 ****
<para>
This parameter specifies the time stamp up to which recovery
will proceed.
! At most one of <varname>recovery_target_time</> and
<xref linkend="recovery-target-xid"> can be specified.
The default is to recover to the end of the WAL log.
The precise stopping point is also influenced by
--- 177,184 ----
<para>
This parameter specifies the time stamp up to which recovery
will proceed.
! At most one of <varname>recovery_target_time</>,
! <xref linkend="recovery-target-name"> or
<xref linkend="recovery-target-xid"> can be specified.
The default is to recover to the end of the WAL log.
The precise stopping point is also influenced by
*************** restore_command = 'copy "C:\\server\\arc
*** 177,183 ****
start, transactions can complete in a different numeric order.
The transactions that will be recovered are those that committed
before (and optionally including) the specified one.
! At most one of <varname>recovery_target_xid</> and
<xref linkend="recovery-target-time"> can be specified.
The default is to recover to the end of the WAL log.
The precise stopping point is also influenced by
--- 200,207 ----
start, transactions can complete in a different numeric order.
The transactions that will be recovered are those that committed
before (and optionally including) the specified one.
! At most one of <varname>recovery_target_xid</>,
! <xref linkend="recovery-target-name"> or
<xref linkend="recovery-target-time"> can be specified.
The default is to recover to the end of the WAL log.
The precise stopping point is also influenced by
diff --git a/src/backend/access/transam/recovery.conf.sample b/src/backend/access/transam/recovery.conf.sample
index f8f6f9b..552aae1 100644
*** a/src/backend/access/transam/recovery.conf.sample
--- b/src/backend/access/transam/recovery.conf.sample
***************
*** 66,76 ****
# If you want to stop rollforward at a specific point, you
# must set a recovery target.
#
! # You may set a recovery target either by transactionId, or
! # by timestamp. Recovery may either include or exclude the
# transaction(s) with the recovery target value (ie, stop either
# just after or just before the given target, respectively).
#
#recovery_target_time = '' # e.g. '2004-07-14 22:39:00 EST'
#
#recovery_target_xid = ''
--- 66,78 ----
# If you want to stop rollforward at a specific point, you
# must set a recovery target.
#
! # You may set a recovery target either by transactionId, a named point
! # or by timestamp. Recovery may either include or exclude the
# transaction(s) with the recovery target value (ie, stop either
# just after or just before the given target, respectively).
#
+ #recovery_target_name = ''
+ #
#recovery_target_time = '' # e.g. '2004-07-14 22:39:00 EST'
#
#recovery_target_xid = ''
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 5b6a230..a84e3f4 100644
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
*************** static RecoveryTargetType recoveryTarget
*** 186,191 ****
--- 186,192 ----
static bool recoveryTargetInclusive = true;
static TransactionId recoveryTargetXid;
static TimestampTz recoveryTargetTime;
+ static char *recoveryTargetName;
/* options taken from recovery.conf for XLOG streaming */
static bool StandbyMode = false;
*************** static char *TriggerFile = NULL;
*** 195,200 ****
--- 196,202 ----
/* if recoveryStopsHere returns true, it saves actual stop xid/time here */
static TransactionId recoveryStopXid;
static TimestampTz recoveryStopTime;
+ static char recoveryStopNamedRestorePoint[MAXFNAMELEN];
static bool recoveryStopAfter;
/*
*************** typedef struct xl_parameter_change
*** 541,546 ****
--- 543,557 ----
int wal_level;
} xl_parameter_change;
+ /*
+ * named restore points
+ */
+ typedef struct xl_named_restore_points
+ {
+ TimestampTz xtime;
+ char name[MAXFNAMELEN];
+ } xl_named_restore_points;
+
/*
* Flags set by interrupt handlers for later service in the redo loop.
*/
*************** writeTimeLineHistory(TimeLineID newTLI,
*** 4378,4383 ****
--- 4389,4402 ----
xlogfname,
recoveryStopAfter ? "after" : "before",
timestamptz_to_str(recoveryStopTime));
+ else if (recoveryTarget == RECOVERY_TARGET_NAME)
+ snprintf(buffer, sizeof(buffer),
+ "%s%u\t%s\t%s named restore point %s\n",
+ (srcfd < 0) ? "" : "\n",
+ parentTLI,
+ xlogfname,
+ recoveryStopAfter ? "after" : "before",
+ recoveryStopNamedRestorePoint);
else
snprintf(buffer, sizeof(buffer),
"%s%u\t%s\tno recovery target specified\n",
*************** readRecoveryCommandFile(void)
*** 5101,5108 ****
--- 5120,5142 ----
ereport(DEBUG2,
(errmsg("recovery_target_timeline = latest")));
}
+ else if (strcmp(item->name, "recovery_target_name") == 0)
+ {
+ recoveryTargetName = pstrdup(item->value);
+ ereport(DEBUG2,
+ (errmsg("recovery_target_name = '%s'",
+ recoveryTargetName)));
+ recoveryTarget = RECOVERY_TARGET_NAME;
+ }
else if (strcmp(item->name, "recovery_target_xid") == 0)
{
+ /*
+ * if recovery_target_name specified, then this overrides
+ * recovery_target_xid
+ */
+ if (recoveryTarget == RECOVERY_TARGET_NAME)
+ continue;
+
errno = 0;
recoveryTargetXid = (TransactionId) strtoul(item->value, NULL, 0);
if (errno == EINVAL || errno == ERANGE)
*************** readRecoveryCommandFile(void)
*** 5117,5126 ****
else if (strcmp(item->name, "recovery_target_time") == 0)
{
/*
! * if recovery_target_xid specified, then this overrides
! * recovery_target_time
*/
! if (recoveryTarget == RECOVERY_TARGET_XID)
continue;
recoveryTarget = RECOVERY_TARGET_TIME;
--- 5151,5160 ----
else if (strcmp(item->name, "recovery_target_time") == 0)
{
/*
! * if recovery_target_xid or recovery_target_name specified,
! * then this overrides recovery_target_time
*/
! if (recoveryTarget == RECOVERY_TARGET_XID || recoveryTarget == RECOVERY_TARGET_NAME)
continue;
recoveryTarget = RECOVERY_TARGET_TIME;
*************** exitArchiveRecovery(TimeLineID endTLI, u
*** 5358,5371 ****
static bool
recoveryStopsHere(XLogRecord *record, bool *includeThis)
{
bool stopsHere;
uint8 record_info;
TimestampTz recordXtime;
! /* We only consider stopping at COMMIT or ABORT records */
if (record->xl_rmid != RM_XACT_ID)
! return false;
record_info = record->xl_info & ~XLR_INFO_MASK;
if (record_info == XLOG_XACT_COMMIT)
{
xl_xact_commit *recordXactCommitData;
--- 5392,5418 ----
static bool
recoveryStopsHere(XLogRecord *record, bool *includeThis)
{
+ bool couldStop;
bool stopsHere;
uint8 record_info;
TimestampTz recordXtime;
! /*
! * We could stop at COMMIT or ABORT records
! */
! couldStop = true;
if (record->xl_rmid != RM_XACT_ID)
! couldStop = false;
! /*
! * Or when we found a named restore point
! */
record_info = record->xl_info & ~XLR_INFO_MASK;
+ if ((record->xl_rmid == RM_XLOG_ID) && (record_info == XLOG_RESTORE_POINT))
+ couldStop = true;
+
+ if (!couldStop)
+ return false;
+
if (record_info == XLOG_XACT_COMMIT)
{
xl_xact_commit *recordXactCommitData;
*************** recoveryStopsHere(XLogRecord *record, bo
*** 5380,5385 ****
--- 5427,5439 ----
recordXactAbortData = (xl_xact_abort *) XLogRecGetData(record);
recordXtime = recordXactAbortData->xact_time;
}
+ else if (record_info == XLOG_RESTORE_POINT)
+ {
+ xl_named_restore_points *recordNamedRestorePoint;
+
+ recordNamedRestorePoint = (xl_named_restore_points *) XLogRecGetData(record);
+ recordXtime = recordNamedRestorePoint->xtime;
+ }
else
return false;
*************** recoveryStopsHere(XLogRecord *record, bo
*** 5405,5411 ****
if (stopsHere)
*includeThis = recoveryTargetInclusive;
}
! else
{
/*
* there can be many transactions that share the same commit time, so
--- 5459,5465 ----
if (stopsHere)
*includeThis = recoveryTargetInclusive;
}
! else if (recoveryTarget == RECOVERY_TARGET_TIME)
{
/*
* there can be many transactions that share the same commit time, so
*************** recoveryStopsHere(XLogRecord *record, bo
*** 5419,5424 ****
--- 5473,5492 ----
if (stopsHere)
*includeThis = false;
}
+ else
+ {
+ char name[MAXFNAMELEN];
+
+ strncpy(name, ((xl_named_restore_points *) XLogRecGetData(record))->name, MAXFNAMELEN);
+ stopsHere = (strncmp(name, recoveryTargetName, MAXFNAMELEN) == 0);
+
+ /*
+ * Because this record does nothing but hold the restore point name
+ * applying or not this record is just waste so recoveryTargetInclusive
+ * is meaningless in this case
+ */
+ *includeThis = false;
+ }
if (stopsHere)
{
*************** recoveryStopsHere(XLogRecord *record, bo
*** 5426,5431 ****
--- 5494,5502 ----
recoveryStopTime = recordXtime;
recoveryStopAfter = *includeThis;
+ if (recoveryTarget == RECOVERY_TARGET_NAME)
+ strncpy(recoveryStopNamedRestorePoint, recoveryTargetName, MAXFNAMELEN);
+
if (record_info == XLOG_XACT_COMMIT)
{
if (recoveryStopAfter)
*************** recoveryStopsHere(XLogRecord *record, bo
*** 5439,5445 ****
recoveryStopXid,
timestamptz_to_str(recoveryStopTime))));
}
! else
{
if (recoveryStopAfter)
ereport(LOG,
--- 5510,5516 ----
recoveryStopXid,
timestamptz_to_str(recoveryStopTime))));
}
! else if (record_info == XLOG_XACT_ABORT)
{
if (recoveryStopAfter)
ereport(LOG,
*************** recoveryStopsHere(XLogRecord *record, bo
*** 5452,5457 ****
--- 5523,5533 ----
recoveryStopXid,
timestamptz_to_str(recoveryStopTime))));
}
+ else
+ ereport(LOG,
+ (errmsg("recovery stopping at named point %s, time %s",
+ recoveryStopNamedRestorePoint,
+ timestamptz_to_str(recoveryStopTime))));
if (recoveryStopAfter)
SetLatestXTime(recordXtime);
*************** XLogPutNextOid(Oid nextOid)
*** 7774,7779 ****
--- 7850,7877 ----
}
/*
+ * Write a restore point record
+ */
+ XLogRecPtr
+ XLogRestorePointName(char *name)
+ {
+ XLogRecPtr RecPtr;
+ XLogRecData rdata;
+ xl_named_restore_points xlrec;
+
+ xlrec.xtime = GetCurrentTimestamp();
+ strncpy(xlrec.name, name, MAXFNAMELEN);
+
+ rdata.data = (char *) &xlrec;
+ rdata.len = sizeof(xl_named_restore_points);
+ rdata.buffer = InvalidBuffer;
+ rdata.next = NULL;
+ RecPtr = XLogInsert(RM_XLOG_ID, XLOG_RESTORE_POINT, &rdata);
+
+ return RecPtr;
+ }
+
+ /*
* Write an XLOG SWITCH record.
*
* Here we just blindly issue an XLogInsert request for the record.
*************** xlog_redo(XLogRecPtr lsn, XLogRecord *re
*** 7988,7993 ****
--- 8086,8095 ----
{
/* nothing to do here */
}
+ else if (info == XLOG_RESTORE_POINT)
+ {
+ /* nothing to do here */
+ }
else if (info == XLOG_SWITCH)
{
/* nothing to do here */
*************** xlog_desc(StringInfo buf, uint8 xl_info,
*** 8127,8132 ****
--- 8229,8244 ----
xlrec.max_locks_per_xact,
wal_level_str);
}
+ else if (info == XLOG_RESTORE_POINT)
+ {
+ xl_named_restore_points xlrec;
+ char name[MAXFNAMELEN];
+
+ memcpy(&xlrec, rec, sizeof(xl_named_restore_points));
+ strncpy(name, xlrec.name, MAXFNAMELEN);
+
+ appendStringInfo(buf, "named restore point: '%s'", name);
+ }
else
appendStringInfo(buf, "UNKNOWN");
}
*************** do_pg_abort_backup(void)
*** 8755,8760 ****
--- 8867,8909 ----
}
/*
+ * pg_create_restore_point: a named point for restore
+ */
+ Datum
+ pg_create_restore_point(PG_FUNCTION_ARGS)
+ {
+ text *restore_name = PG_GETARG_TEXT_P(0);
+ char *restore_name_str;
+ XLogRecPtr restorepoint;
+ char location[MAXFNAMELEN];
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser to create a restore point"))));
+
+ if (RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("recovery is in progress"),
+ errhint("WAL control functions cannot be executed during recovery.")));
+
+ if (!XLogArchivingActive())
+ ereport(NOTICE,
+ (errmsg("WAL archiving is not enabled; you must ensure that WAL segments are copied through other means for restore points to be usefull for you")));
+
+ restore_name_str = text_to_cstring(restore_name);
+ restorepoint = XLogRestorePointName(restore_name_str);
+
+ /*
+ * As a convenience, return the WAL location of the restore point record
+ */
+ snprintf(location, sizeof(location), "%X/%X",
+ restorepoint.xlogid, restorepoint.xrecoff);
+ PG_RETURN_TEXT_P(cstring_to_text(location));
+ }
+
+ /*
* pg_switch_xlog: switch to next xlog file
*/
Datum
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 74d3427..3394152 100644
*** a/src/include/access/xlog.h
--- b/src/include/access/xlog.h
*************** typedef enum
*** 184,190 ****
{
RECOVERY_TARGET_UNSET,
RECOVERY_TARGET_XID,
! RECOVERY_TARGET_TIME
} RecoveryTargetType;
extern XLogRecPtr XactLastRecEnd;
--- 184,191 ----
{
RECOVERY_TARGET_UNSET,
RECOVERY_TARGET_XID,
! RECOVERY_TARGET_TIME,
! RECOVERY_TARGET_NAME
} RecoveryTargetType;
extern XLogRecPtr XactLastRecEnd;
*************** extern void InitXLOGAccess(void);
*** 302,307 ****
--- 303,309 ----
extern void CreateCheckPoint(int flags);
extern bool CreateRestartPoint(int flags);
extern void XLogPutNextOid(Oid nextOid);
+ extern XLogRecPtr XLogRestorePointName(char *name);
extern XLogRecPtr GetRedoRecPtr(void);
extern XLogRecPtr GetInsertRecPtr(void);
extern XLogRecPtr GetFlushRecPtr(void);
diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h
index ba64035..dd5a252 100644
*** a/src/include/access/xlog_internal.h
--- b/src/include/access/xlog_internal.h
*************** extern XLogRecPtr RequestXLogSwitch(void
*** 266,271 ****
--- 266,272 ----
*/
extern Datum pg_start_backup(PG_FUNCTION_ARGS);
extern Datum pg_stop_backup(PG_FUNCTION_ARGS);
+ extern Datum pg_create_restore_point(PG_FUNCTION_ARGS);
extern Datum pg_switch_xlog(PG_FUNCTION_ARGS);
extern Datum pg_current_xlog_location(PG_FUNCTION_ARGS);
extern Datum pg_current_xlog_insert_location(PG_FUNCTION_ARGS);
diff --git a/src/include/catalog/pg_control.h b/src/include/catalog/pg_control.h
index ddf1398..43c1abe 100644
*** a/src/include/catalog/pg_control.h
--- b/src/include/catalog/pg_control.h
*************** typedef struct CheckPoint
*** 59,64 ****
--- 59,65 ----
#define XLOG_SWITCH 0x40
#define XLOG_BACKUP_END 0x50
#define XLOG_PARAMETER_CHANGE 0x60
+ #define XLOG_RESTORE_POINT 0x70
/*
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f8b5d4d..b037109 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 2172 ( pg_start_backup
*** 3395,3400 ****
--- 3395,3402 ----
DESCR("prepare for taking an online backup");
DATA(insert OID = 2173 ( pg_stop_backup PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_stop_backup _null_ _null_ _null_ ));
DESCR("finish taking an online backup");
+ DATA(insert OID = 2847 ( pg_create_restore_point PGNSP PGUID 12 1 0 0 f f f t f v 1 0 25 "25" _null_ _null_ _null_ _null_ pg_create_restore_point _null_ _null_ _null_ ));
+ DESCR("create a named restore point");
DATA(insert OID = 2848 ( pg_switch_xlog PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_switch_xlog _null_ _null_ _null_ ));
DESCR("switch to new xlog file");
DATA(insert OID = 2849 ( pg_current_xlog_location PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_current_xlog_location _null_ _null_ _null_ ));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
