Skip to content

Commit c016ce7

Browse files
simonat2ndQuadrantsimonat2ndQuadrant
committed
Named restore points in recovery. Users can record named points, then
new recovery.conf parameter recovery_target_name allows PITR to specify named points as recovery targets. Jaime Casanova, reviewed by Euler Taveira de Oliveira, plus minor edits
1 parent 8c6e3ad commit c016ce7

File tree

9 files changed

+209
-17
lines changed

9 files changed

+209
-17
lines changed

‎doc/src/sgml/backup.sgml

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1086,9 +1086,10 @@ restore_command = 'cp /mnt/server/archivedir/%f %p'
10861086
the junior DBA dropped your main transaction table), just specify the
10871087
required stopping point in <filename>recovery.conf</>. You can specify
10881088
the stop point, known as the <quote>recovery target</>, either by
1089-
date/time or by completion of a specific transaction ID. As of this
1090-
writing only the date/time option is very usable, since there are no tools
1091-
to help you identify with any accuracy which transaction ID to use.
1089+
date/time, named restore point or by completion of a specific transaction
1090+
ID. As of this writing only the date/time and named restore point options
1091+
are very usable, since there are no tools to help you identify with any
1092+
accuracy which transaction ID to use.
10921093
</para>
10931094

10941095
<note>

‎doc/src/sgml/func.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13914,6 +13914,9 @@ SELECT set_config('log_statement_stats', 'off', false);
1391413914
<indexterm>
1391513915
<primary>backup</primary>
1391613916
</indexterm>
13917+
<indexterm>
13918+
<primary>pg_create_restore_point</primary>
13919+
</indexterm>
1391713920
<indexterm>
1391813921
<primary>pg_current_xlog_insert_location</primary>
1391913922
</indexterm>
@@ -13951,6 +13954,13 @@ SELECT set_config('log_statement_stats', 'off', false);
1395113954
</thead>
1395213955

1395313956
<tbody>
13957+
<row>
13958+
<entry>
13959+
<literal><function>pg_create_restore_point(<parameter>name</> <type>text</>)</function></literal>
13960+
</entry>
13961+
<entry><type>text</type></entry>
13962+
<entry>Create a named point for performing restore (restricted to superusers)</entry>
13963+
</row>
1395413964
<row>
1395513965
<entry>
1395613966
<literal><function>pg_current_xlog_insert_location()</function></literal>

‎doc/src/sgml/recovery-config.sgml

Lines changed: 23 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -143,6 +143,25 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows
143143
<title>Recovery Target Settings</title>
144144
<variablelist>
145145

146+
<varlistentry id="recovery-target-name" xreflabel="recovery_target_name">
147+
<term><varname>recovery_target_name</varname>
148+
(<type>string</type>)
149+
</term>
150+
<indexterm>
151+
<primary><varname>recovery_target_name</> recovery parameter</primary>
152+
</indexterm>
153+
<listitem>
154+
<para>
155+
This parameter specifies the named restore point, created with
156+
<function>pg_create_restore_point()</> to which recovery will proceed.
157+
At most one of <varname>recovery_target_name</>,
158+
<xref linkend="recovery-target-time"> or
159+
<xref linkend="recovery-target-xid"> can be specified. The default is to
160+
recover to the end of the WAL log.
161+
</para>
162+
</listitem>
163+
</varlistentry>
164+
146165
<varlistentry id="recovery-target-time" xreflabel="recovery_target_time">
147166
<term><varname>recovery_target_time</varname>
148167
(<type>timestamp</type>)
@@ -154,7 +173,8 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows
154173
<para>
155174
This parameter specifies the time stamp up to which recovery
156175
will proceed.
157-
At most one of <varname>recovery_target_time</> and
176+
At most one of <varname>recovery_target_time</>,
177+
<xref linkend="recovery-target-name"> or
158178
<xref linkend="recovery-target-xid"> can be specified.
159179
The default is to recover to the end of the WAL log.
160180
The precise stopping point is also influenced by
@@ -176,7 +196,8 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows
176196
start, transactions can complete in a different numeric order.
177197
The transactions that will be recovered are those that committed
178198
before (and optionally including) the specified one.
179-
At most one of <varname>recovery_target_xid</> and
199+
At most one of <varname>recovery_target_xid</>,
200+
<xref linkend="recovery-target-name"> or
180201
<xref linkend="recovery-target-time"> can be specified.
181202
The default is to recover to the end of the WAL log.
182203
The precise stopping point is also influenced by

‎src/backend/access/transam/recovery.conf.sample

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -66,11 +66,14 @@
6666
# If you want to stop rollforward at a specific point, you
6767
# must set a recovery target.
6868
#
69-
# You may set a recovery target either by transactionId, or
70-
# by timestamp. Recovery may either include or exclude the
69+
# You may set a recovery target either by transactionId, by name,
70+
# or by timestamp. Recovery may either include or exclude the
7171
# transaction(s) with the recovery target value (ie, stop either
7272
# just after or just before the given target, respectively).
7373
#
74+
#
75+
#recovery_target_name = '' # e.g. 'daily backup 2011-01-26'
76+
#
7477
#recovery_target_time = '' # e.g. '2004-07-14 22:39:00 EST'
7578
#
7679
#recovery_target_xid = ''

‎src/backend/access/transam/xlog.c

Lines changed: 161 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -185,15 +185,17 @@ static bool recoveryTargetInclusive = true;
185185
static bool recoveryPauseAtTarget = true;
186186
static TransactionId recoveryTargetXid;
187187
static TimestampTz recoveryTargetTime;
188+
static char *recoveryTargetName;
188189

189190
/* options taken from recovery.conf for XLOG streaming */
190191
static bool StandbyMode = false;
191192
static char *PrimaryConnInfo = NULL;
192193
static char *TriggerFile = NULL;
193194

194-
/* if recoveryStopsHere returns true, it saves actual stop xid/time here */
195+
/* if recoveryStopsHere returns true, it saves actual stop xid/time/name here */
195196
static TransactionId recoveryStopXid;
196197
static TimestampTz recoveryStopTime;
198+
static char recoveryStopName[MAXFNAMELEN];
197199
static bool recoveryStopAfter;
198200

199201
/*
@@ -551,6 +553,13 @@ typedef struct xl_parameter_change
551553
int wal_level;
552554
} xl_parameter_change;
553555

556+
/* logs restore point */
557+
typedef struct xl_restore_point
558+
{
559+
TimestampTz rp_time;
560+
char rp_name[MAXFNAMELEN];
561+
} xl_restore_point;
562+
554563
/*
555564
* Flags set by interrupt handlers for later service in the redo loop.
556565
*/
@@ -4391,6 +4400,13 @@ writeTimeLineHistory(TimeLineID newTLI, TimeLineID parentTLI,
43914400
xlogfname,
43924401
recoveryStopAfter ? "after" : "before",
43934402
timestamptz_to_str(recoveryStopTime));
4403+
else if (recoveryTarget == RECOVERY_TARGET_NAME)
4404+
snprintf(buffer, sizeof(buffer),
4405+
"%s%u\t%s\tat restore point \"%s\"\n",
4406+
(srcfd < 0) ? "" : "\n",
4407+
parentTLI,
4408+
xlogfname,
4409+
recoveryStopName);
43944410
else
43954411
snprintf(buffer, sizeof(buffer),
43964412
"%s%u\t%s\tno recovery target specified\n",
@@ -5178,10 +5194,11 @@ readRecoveryCommandFile(void)
51785194
else if (strcmp(item->name, "recovery_target_time") == 0)
51795195
{
51805196
/*
5181-
* if recovery_target_xid specified, then this overrides
5182-
* recovery_target_time
5197+
* if recovery_target_xid or recovery_target_name specified, then
5198+
* this overrides recovery_target_time
51835199
*/
5184-
if (recoveryTarget == RECOVERY_TARGET_XID)
5200+
if (recoveryTarget == RECOVERY_TARGET_XID ||
5201+
recoveryTarget == RECOVERY_TARGET_NAME)
51855202
continue;
51865203
recoveryTarget = RECOVERY_TARGET_TIME;
51875204

@@ -5197,6 +5214,26 @@ readRecoveryCommandFile(void)
51975214
(errmsg("recovery_target_time = '%s'",
51985215
timestamptz_to_str(recoveryTargetTime))));
51995216
}
5217+
else if (strcmp(item->name, "recovery_target_name") == 0)
5218+
{
5219+
/*
5220+
* if recovery_target_xid specified, then this overrides
5221+
* recovery_target_name
5222+
*/
5223+
if (recoveryTarget == RECOVERY_TARGET_XID)
5224+
continue;
5225+
recoveryTarget = RECOVERY_TARGET_NAME;
5226+
5227+
recoveryTargetName = pstrdup(item->value);
5228+
if (strlen(recoveryTargetName) >= MAXFNAMELEN)
5229+
ereport(FATAL,
5230+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5231+
errmsg("recovery_target_name is too long")));
5232+
5233+
ereport(DEBUG2,
5234+
(errmsg("recovery_target_name = '%s'",
5235+
recoveryTargetName)));
5236+
}
52005237
else if (strcmp(item->name, "recovery_target_inclusive") == 0)
52015238
{
52025239
/*
@@ -5411,8 +5448,8 @@ exitArchiveRecovery(TimeLineID endTLI, uint32 endLogId, uint32 endLogSeg)
54115448
* Returns TRUE if we are stopping, FALSE otherwise. On TRUE return,
54125449
* *includeThis is set TRUE if we should apply this record before stopping.
54135450
*
5414-
* We also track the timestamp of the latest applied COMMIT/ABORT record
5415-
* in XLogCtl->recoveryLastXTime, for logging purposes.
5451+
* We also track the timestamp of the latest applied COMMIT/ABORT/RESTORE POINT
5452+
* record in XLogCtl->recoveryLastXTime, for logging purposes.
54165453
* Also, some information is saved in recoveryStopXid et al for use in
54175454
* annotating the new timeline's history file.
54185455
*/
@@ -5422,9 +5459,10 @@ recoveryStopsHere(XLogRecord *record, bool *includeThis)
54225459
bool stopsHere;
54235460
uint8 record_info;
54245461
TimestampTz recordXtime;
5462+
char recordRPName[MAXFNAMELEN];
54255463

5426-
/* We only consider stopping at COMMIT or ABORT records */
5427-
if (record->xl_rmid != RM_XACT_ID)
5464+
/* We only consider stopping at COMMIT, ABORT or RESTORE POINT records */
5465+
if (record->xl_rmid != RM_XACT_ID && record->xl_rmid != RM_XLOG_ID)
54285466
return false;
54295467
record_info = record->xl_info & ~XLR_INFO_MASK;
54305468
if (record_info == XLOG_XACT_COMMIT)
@@ -5441,6 +5479,14 @@ recoveryStopsHere(XLogRecord *record, bool *includeThis)
54415479
recordXactAbortData = (xl_xact_abort *) XLogRecGetData(record);
54425480
recordXtime = recordXactAbortData->xact_time;
54435481
}
5482+
else if (record_info == XLOG_RESTORE_POINT)
5483+
{
5484+
xl_restore_point *recordRestorePointData;
5485+
5486+
recordRestorePointData = (xl_restore_point *) XLogRecGetData(record);
5487+
recordXtime = recordRestorePointData->rp_time;
5488+
strncpy(recordRPName, recordRestorePointData->rp_name, MAXFNAMELEN);
5489+
}
54445490
else
54455491
return false;
54465492

@@ -5466,6 +5512,20 @@ recoveryStopsHere(XLogRecord *record, bool *includeThis)
54665512
if (stopsHere)
54675513
*includeThis = recoveryTargetInclusive;
54685514
}
5515+
else if (recoveryTarget == RECOVERY_TARGET_NAME)
5516+
{
5517+
/*
5518+
* there can be many restore points that share the same name, so we stop
5519+
* at the first one
5520+
*/
5521+
stopsHere = (strcmp(recordRPName, recoveryTargetName) == 0);
5522+
5523+
/*
5524+
* ignore recoveryTargetInclusive because this is not a transaction
5525+
* record
5526+
*/
5527+
*includeThis = false;
5528+
}
54695529
else
54705530
{
54715531
/*
@@ -5500,7 +5560,7 @@ recoveryStopsHere(XLogRecord *record, bool *includeThis)
55005560
recoveryStopXid,
55015561
timestamptz_to_str(recoveryStopTime))));
55025562
}
5503-
else
5563+
else if (record_info == XLOG_XACT_ABORT)
55045564
{
55055565
if (recoveryStopAfter)
55065566
ereport(LOG,
@@ -5513,6 +5573,15 @@ recoveryStopsHere(XLogRecord *record, bool *includeThis)
55135573
recoveryStopXid,
55145574
timestamptz_to_str(recoveryStopTime))));
55155575
}
5576+
else
5577+
{
5578+
strncpy(recoveryStopName, recordRPName, MAXFNAMELEN);
5579+
5580+
ereport(LOG,
5581+
(errmsg("recovery stopping at restore point \"%s\", time %s",
5582+
recoveryStopName,
5583+
timestamptz_to_str(recoveryStopTime))));
5584+
}
55165585

55175586
if (recoveryStopAfter)
55185587
SetLatestXTime(recordXtime);
@@ -5900,6 +5969,10 @@ StartupXLOG(void)
59005969
ereport(LOG,
59015970
(errmsg("starting point-in-time recovery to %s",
59025971
timestamptz_to_str(recoveryTargetTime))));
5972+
else if (recoveryTarget == RECOVERY_TARGET_NAME)
5973+
ereport(LOG,
5974+
(errmsg("starting point-in-time recovery to \"%s\"",
5975+
recoveryTargetName)));
59035976
else
59045977
ereport(LOG,
59055978
(errmsg("starting archive recovery")));
@@ -7989,6 +8062,29 @@ RequestXLogSwitch(void)
79898062
return RecPtr;
79908063
}
79918064

8065+
/*
8066+
* Write a RESTORE POINT record
8067+
*/
8068+
XLogRecPtr
8069+
XLogRestorePoint(const char *rpName)
8070+
{
8071+
XLogRecPtr RecPtr;
8072+
XLogRecData rdata;
8073+
xl_restore_point xlrec;
8074+
8075+
xlrec.rp_time = GetCurrentTimestamp();
8076+
strncpy(xlrec.rp_name, rpName, MAXFNAMELEN);
8077+
8078+
rdata.buffer = InvalidBuffer;
8079+
rdata.data = (char *) &xlrec;
8080+
rdata.len = sizeof(xl_restore_point);
8081+
rdata.next = NULL;
8082+
8083+
RecPtr = XLogInsert(RM_XLOG_ID, XLOG_RESTORE_POINT, &rdata);
8084+
8085+
return RecPtr;
8086+
}
8087+
79928088
/*
79938089
* Check if any of the GUC parameters that are critical for hot standby
79948090
* have changed, and update the value in pg_control file if necessary.
@@ -8181,6 +8277,10 @@ xlog_redo(XLogRecPtr lsn, XLogRecord *record)
81818277
{
81828278
/* nothing to do here */
81838279
}
8280+
else if (info == XLOG_RESTORE_POINT)
8281+
{
8282+
/* nothing to do here */
8283+
}
81848284
else if (info == XLOG_BACKUP_END)
81858285
{
81868286
XLogRecPtr startpoint;
@@ -8283,6 +8383,13 @@ xlog_desc(StringInfo buf, uint8 xl_info, char *rec)
82838383
{
82848384
appendStringInfo(buf, "xlog switch");
82858385
}
8386+
else if (info == XLOG_RESTORE_POINT)
8387+
{
8388+
xl_restore_point *xlrec = (xl_restore_point *) rec;
8389+
8390+
appendStringInfo(buf, "restore point: %s", xlrec->rp_name);
8391+
8392+
}
82868393
else if (info == XLOG_BACKUP_END)
82878394
{
82888395
XLogRecPtr startpoint;
@@ -9080,6 +9187,51 @@ pg_switch_xlog(PG_FUNCTION_ARGS)
90809187
PG_RETURN_TEXT_P(cstring_to_text(location));
90819188
}
90829189

9190+
/*
9191+
* pg_create_restore_point: a named point for restore
9192+
*/
9193+
Datum
9194+
pg_create_restore_point(PG_FUNCTION_ARGS)
9195+
{
9196+
text *restore_name = PG_GETARG_TEXT_P(0);
9197+
char *restore_name_str;
9198+
XLogRecPtr restorepoint;
9199+
char location[MAXFNAMELEN];
9200+
9201+
if (!superuser())
9202+
ereport(ERROR,
9203+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
9204+
(errmsg("must be superuser to create a restore point"))));
9205+
9206+
if (RecoveryInProgress())
9207+
ereport(ERROR,
9208+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
9209+
(errmsg("recovery is in progress"),
9210+
errhint("WAL control functions cannot be executed during recovery."))));
9211+
9212+
if (!XLogIsNeeded())
9213+
ereport(ERROR,
9214+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
9215+
errmsg("WAL level not sufficient for creating a restore point"),
9216+
errhint("wal_level must be set to \"archive\" or \"hot_standby\" at server start.")));
9217+
9218+
restore_name_str = text_to_cstring(restore_name);
9219+
9220+
if (strlen(restore_name_str) >= MAXFNAMELEN)
9221+
ereport(ERROR,
9222+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
9223+
errmsg("value too long for restore point")));
9224+
9225+
restorepoint = XLogRestorePoint(restore_name_str);
9226+
9227+
/*
9228+
* As a convenience, return the WAL location of the restore point record
9229+
*/
9230+
snprintf(location, sizeof(location), "%X/%X",
9231+
restorepoint.xlogid, restorepoint.xrecoff);
9232+
PG_RETURN_TEXT_P(cstring_to_text(location));
9233+
}
9234+
90839235
/*
90849236
* Report the current WAL write location (same format as pg_start_backup etc)
90859237
*

0 commit comments

Comments
 (0)