diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index a461b0a..4a554de 100644 *** a/doc/src/sgml/backup.sgml --- b/doc/src/sgml/backup.sgml *************** restore_command = 'cp /mnt/server/archiv *** 1086,1094 **** the junior DBA dropped your main transaction table), just specify the required stopping point in recovery.conf. You can specify the stop point, known as the 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. --- 1086,1095 ---- the junior DBA dropped your main transaction table), just specify the required stopping point in recovery.conf. You can specify the stop point, known as the recovery target, either by ! date/time, named restore point or by completion of a specific transaction ! ID. As of this writing only the date/time and named restore point options ! are very usable, since there are no tools to help you identify with any ! accuracy which transaction ID to use. diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index ed2039c..07495c3 100644 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** SELECT set_config('log_statement_stats', *** 13915,13920 **** --- 13915,13923 ---- backup + pg_create_restore_point + + pg_current_xlog_insert_location *************** SELECT set_config('log_statement_stats', *** 13953,13958 **** --- 13956,13968 ---- + pg_create_restore_point(name text) + + text + Create a named point for performing restore (restricted to superusers) + + + pg_current_xlog_insert_location() text diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml index 454ec84..7021656 100644 *** a/doc/src/sgml/recovery-config.sgml --- b/doc/src/sgml/recovery-config.sgml *************** restore_command = 'copy "C:\\server\\arc *** 143,148 **** --- 143,167 ---- Recovery Target Settings + + recovery_target_name + (string) + + + recovery_target_name recovery parameter + + + + This parameter specifies the named restore point, created with + pg_create_restore_point() to which recovery will proceed. + At most one of recovery_target_name, + or + can be specified. The default is to + recover to the end of the WAL log. + + + + recovery_target_time (timestamp) *************** restore_command = 'copy "C:\\server\\arc *** 154,160 **** This parameter specifies the time stamp up to which recovery will proceed. ! At most one of recovery_target_time and can be specified. The default is to recover to the end of the WAL log. The precise stopping point is also influenced by --- 173,180 ---- This parameter specifies the time stamp up to which recovery will proceed. ! At most one of recovery_target_time, ! or 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 *** 176,182 **** 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 recovery_target_xid and can be specified. The default is to recover to the end of the WAL log. The precise stopping point is also influenced by --- 196,203 ---- 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 recovery_target_xid, ! or 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..314bc6f 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,79 ---- # 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, by name, ! # 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 = '' # e.g. 'new app 2011-01-26' + # #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 66cc004..d00a80f 100644 *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *************** static RecoveryTargetType recoveryTarget *** 184,198 **** static bool recoveryTargetInclusive = true; static TransactionId recoveryTargetXid; static TimestampTz recoveryTargetTime; /* options taken from recovery.conf for XLOG streaming */ static bool StandbyMode = false; static char *PrimaryConnInfo = NULL; static char *TriggerFile = NULL; ! /* if recoveryStopsHere returns true, it saves actual stop xid/time here */ static TransactionId recoveryStopXid; static TimestampTz recoveryStopTime; static bool recoveryStopAfter; /* --- 184,200 ---- 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 *PrimaryConnInfo = NULL; static char *TriggerFile = NULL; ! /* if recoveryStopsHere returns true, it saves actual stop xid/time/name here */ static TransactionId recoveryStopXid; static TimestampTz recoveryStopTime; + static char recoveryStopName[MAXFNAMELEN]; static bool recoveryStopAfter; /* *************** typedef struct xl_parameter_change *** 548,553 **** --- 550,562 ---- int wal_level; } xl_parameter_change; + /* logs restore point */ + typedef struct xl_restore_point + { + TimestampTz rp_time; + char rp_name[MAXFNAMELEN]; + } xl_restore_point; + /* * Flags set by interrupt handlers for later service in the redo loop. */ *************** writeTimeLineHistory(TimeLineID newTLI, *** 4385,4390 **** --- 4394,4406 ---- xlogfname, recoveryStopAfter ? "after" : "before", timestamptz_to_str(recoveryStopTime)); + else if (recoveryTarget == RECOVERY_TARGET_NAME) + snprintf(buffer, sizeof(buffer), + "%s%u\t%s\tat restore point \"%s\"\n", + (srcfd < 0) ? "" : "\n", + parentTLI, + xlogfname, + recoveryStopName); else snprintf(buffer, sizeof(buffer), "%s%u\t%s\tno recovery target specified\n", *************** readRecoveryCommandFile(void) *** 5163,5172 **** 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; --- 5179,5189 ---- 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; *************** readRecoveryCommandFile(void) *** 5182,5187 **** --- 5199,5224 ---- (errmsg("recovery_target_time = '%s'", timestamptz_to_str(recoveryTargetTime)))); } + else if (strcmp(item->name, "recovery_target_name") == 0) + { + /* + * if recovery_target_xid specified, then this overrides + * recovery_target_name + */ + if (recoveryTarget == RECOVERY_TARGET_XID) + continue; + recoveryTarget = RECOVERY_TARGET_NAME; + + recoveryTargetName = pstrdup(item->value); + if (strlen(recoveryTargetName) >= MAXFNAMELEN) + ereport(FATAL, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("recovery_target_name is too long"))); + + ereport(DEBUG2, + (errmsg("recovery_target_name = '%s'", + recoveryTargetName))); + } else if (strcmp(item->name, "recovery_target_inclusive") == 0) { /* *************** exitArchiveRecovery(TimeLineID endTLI, u *** 5396,5403 **** * Returns TRUE if we are stopping, FALSE otherwise. On TRUE return, * *includeThis is set TRUE if we should apply this record before stopping. * ! * We also track the timestamp of the latest applied COMMIT/ABORT record ! * in XLogCtl->recoveryLastXTime, for logging purposes. * Also, some information is saved in recoveryStopXid et al for use in * annotating the new timeline's history file. */ --- 5433,5440 ---- * Returns TRUE if we are stopping, FALSE otherwise. On TRUE return, * *includeThis is set TRUE if we should apply this record before stopping. * ! * We also track the timestamp of the latest applied COMMIT/ABORT/RESTORE POINT ! * record in XLogCtl->recoveryLastXTime, for logging purposes. * Also, some information is saved in recoveryStopXid et al for use in * annotating the new timeline's history file. */ *************** recoveryStopsHere(XLogRecord *record, bo *** 5407,5415 **** 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) --- 5444,5453 ---- bool stopsHere; uint8 record_info; TimestampTz recordXtime; + char recordRPName[MAXFNAMELEN]; ! /* We only consider stopping at COMMIT, ABORT or RESTORE POINT records */ ! if (record->xl_rmid != RM_XACT_ID && record->xl_rmid != RM_XLOG_ID) return false; record_info = record->xl_info & ~XLR_INFO_MASK; if (record_info == XLOG_XACT_COMMIT) *************** recoveryStopsHere(XLogRecord *record, bo *** 5426,5431 **** --- 5464,5477 ---- recordXactAbortData = (xl_xact_abort *) XLogRecGetData(record); recordXtime = recordXactAbortData->xact_time; } + else if (record_info == XLOG_RESTORE_POINT) + { + xl_restore_point *recordRestorePointData; + + recordRestorePointData = (xl_restore_point *) XLogRecGetData(record); + recordXtime = recordRestorePointData->rp_time; + strncpy(recordRPName, recordRestorePointData->rp_name, MAXFNAMELEN); + } else return false; *************** recoveryStopsHere(XLogRecord *record, bo *** 5451,5456 **** --- 5497,5516 ---- if (stopsHere) *includeThis = recoveryTargetInclusive; } + else if (recoveryTarget == RECOVERY_TARGET_NAME) + { + /* + * there can be many restore points that share the same name, so we stop + * at the first one + */ + stopsHere = (strcmp(recordRPName, recoveryTargetName) == 0); + + /* + * ignore recoveryTargetInclusive because this is not a transaction + * record + */ + *includeThis = false; + } else { /* *************** recoveryStopsHere(XLogRecord *record, bo *** 5485,5491 **** recoveryStopXid, timestamptz_to_str(recoveryStopTime)))); } ! else { if (recoveryStopAfter) ereport(LOG, --- 5545,5551 ---- recoveryStopXid, timestamptz_to_str(recoveryStopTime)))); } ! else if (record_info == XLOG_XACT_ABORT) { if (recoveryStopAfter) ereport(LOG, *************** recoveryStopsHere(XLogRecord *record, bo *** 5498,5503 **** --- 5558,5572 ---- recoveryStopXid, timestamptz_to_str(recoveryStopTime)))); } + else + { + strncpy(recoveryStopName, recordRPName, MAXFNAMELEN); + + ereport(LOG, + (errmsg("recovery stopping at restore point \"%s\", time %s", + recoveryStopName, + timestamptz_to_str(recoveryStopTime)))); + } if (recoveryStopAfter) SetLatestXTime(recordXtime); *************** StartupXLOG(void) *** 5781,5786 **** --- 5850,5859 ---- ereport(LOG, (errmsg("starting point-in-time recovery to %s", timestamptz_to_str(recoveryTargetTime)))); + else if (recoveryTarget == RECOVERY_TARGET_NAME) + ereport(LOG, + (errmsg("starting point-in-time recovery to \"%s\"", + recoveryTargetName))); else ereport(LOG, (errmsg("starting archive recovery"))); *************** RequestXLogSwitch(void) *** 7847,7852 **** --- 7920,7949 ---- } /* + * Write a RESTORE POINT record + */ + XLogRecPtr + XLogRestorePoint(const char *rp) + { + XLogRecPtr RecPtr; + XLogRecData rdata; + xl_restore_point xlrec; + + xlrec.rp_time = GetCurrentTimestamp(); + strncpy(xlrec.rp_name, rp, MAXFNAMELEN); + + rdata.buffer = InvalidBuffer; + rdata.data = (char *) &xlrec; + rdata.len = sizeof(xl_restore_point); + rdata.next = NULL; + + RecPtr = XLogInsert(RM_XLOG_ID, XLOG_RESTORE_POINT, &rdata); + + return RecPtr; + } + + + /* * Check if any of the GUC parameters that are critical for hot standby * have changed, and update the value in pg_control file if necessary. */ *************** xlog_redo(XLogRecPtr lsn, XLogRecord *re *** 8038,8043 **** --- 8135,8144 ---- { /* nothing to do here */ } + else if (info == XLOG_RESTORE_POINT) + { + /* nothing to do here */ + } else if (info == XLOG_BACKUP_END) { XLogRecPtr startpoint; *************** xlog_desc(StringInfo buf, uint8 xl_info, *** 8140,8145 **** --- 8241,8253 ---- { appendStringInfo(buf, "xlog switch"); } + else if (info == XLOG_RESTORE_POINT) + { + xl_restore_point *xlrec = (xl_restore_point *) rec; + + appendStringInfo(buf, "restore point: %s", xlrec->rp_name); + + } else if (info == XLOG_BACKUP_END) { XLogRecPtr startpoint; *************** pg_switch_xlog(PG_FUNCTION_ARGS) *** 8937,8942 **** --- 9045,9093 ---- PG_RETURN_TEXT_P(cstring_to_text(location)); } + /* + * 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 preogress"), + 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 to recover up to named restore point"))); + + restore_name_str = text_to_cstring(restore_name); + + if (strlen(restore_name_str) >= MAXFNAMELEN) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("value too long for restore point"))); + + restorepoint = XLogRestorePoint(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)); + } + /* * Report the current WAL write location (same format as pg_start_backup etc) * diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h index 122e96b..3e59b1a 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 XLogRestorePoint(const char *rp); 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..a530f87 100644 *** a/src/include/access/xlog_internal.h --- b/src/include/access/xlog_internal.h *************** extern XLogRecPtr RequestXLogSwitch(void *** 267,272 **** --- 267,273 ---- extern Datum pg_start_backup(PG_FUNCTION_ARGS); extern Datum pg_stop_backup(PG_FUNCTION_ARGS); extern Datum pg_switch_xlog(PG_FUNCTION_ARGS); + extern Datum pg_create_restore_point(PG_FUNCTION_ARGS); extern Datum pg_current_xlog_location(PG_FUNCTION_ARGS); extern Datum pg_current_xlog_insert_location(PG_FUNCTION_ARGS); extern Datum pg_last_xlog_receive_location(PG_FUNCTION_ARGS); diff --git a/src/include/catalog/pg_control.h b/src/include/catalog/pg_control.h index ddf1398..fb458ac 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..e37a88e 100644 *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** DATA(insert OID = 2173 ( pg_stop_backup *** 3397,3402 **** --- 3397,3403 ---- DESCR("finish taking an online backup"); 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 = 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_ )); 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_ )); DESCR("current xlog write location"); DATA(insert OID = 2852 ( pg_current_xlog_insert_location PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_current_xlog_insert_location _null_ _null_ _null_ ));