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.

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.

</para>

<note>

<indexterm>

<primary>backup</primary>

</indexterm>

<indexterm>

<primary>pg_create_restore_point</primary>

</indexterm>

<indexterm>

<primary>pg_current_xlog_insert_location</primary>

</indexterm>

</thead>

<tbody>

<row>

<entry>

<literal><function>pg_create_restore_point(<parameter>name</> <type>text</>)</function></literal>

</entry>

<entry><type>text</type></entry>

<entry>Create a named point for performing restore (restricted to superusers)</entry>

</row>

<row>

<entry>

<literal><function>pg_current_xlog_insert_location()</function></literal>

<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 the 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.

</para>

</listitem>

</varlistentry>

<varlistentry id="recovery-target-time" xreflabel="recovery_target_time">

<term><varname>recovery_target_time</varname>

(<type>timestamp</type>)

<para>

This parameter specifies the time stamp up to which recovery

will proceed.

At most one of <varname>recovery_target_time</> and

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

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

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

# 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

# You may set a recovery target either by transactionId,by name,

#orby 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. 'daily backup 2011-01-26'

#

#recovery_target_time = ''# e.g. '2004-07-14 22:39:00 EST'

#

#recovery_target_xid = ''

staticboolrecoveryPauseAtTarget= true;

staticTransactionIdrecoveryTargetXid;

staticTimestampTzrecoveryTargetTime;

staticchar*recoveryTargetName;

staticboolStandbyMode= false;

staticchar*PrimaryConnInfo=NULL;

staticchar*TriggerFile=NULL;

staticTransactionIdrecoveryStopXid;

staticTimestampTzrecoveryStopTime;

staticcharrecoveryStopName[MAXFNAMELEN];

staticboolrecoveryStopAfter;

intwal_level;

}xl_parameter_change;

{

TimestampTzrp_time;

charrp_name[MAXFNAMELEN];

}xl_restore_point;

xlogfname,

recoveryStopAfter ?"after" :"before",

timestamptz_to_str(recoveryStopTime));

elseif (recoveryTarget==RECOVERY_TARGET_NAME)

snprintf(buffer,sizeof(buffer),

"%s%u\t%s\tat restore point \"%s\"\n",

(srcfd<0) ?"" :"\n",

parentTLI,

xlogfname,

recoveryStopName);

snprintf(buffer,sizeof(buffer),

"%s%u\t%s\tno recovery target specified\n",

elseif (strcmp(item->name,"recovery_target_time")==0)

{

if (recoveryTarget==RECOVERY_TARGET_XID)

if (recoveryTarget==RECOVERY_TARGET_XID||

recoveryTarget==RECOVERY_TARGET_NAME)

continue;

recoveryTarget=RECOVERY_TARGET_TIME;

(errmsg("recovery_target_time = '%s'",

timestamptz_to_str(recoveryTargetTime))));

}

elseif (strcmp(item->name,"recovery_target_name")==0)

{

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)));

}

elseif (strcmp(item->name,"recovery_target_inclusive")==0)

{

boolstopsHere;

uint8record_info;

TimestampTzrecordXtime;

charrecordRPName[MAXFNAMELEN];

if (record->xl_rmid!=RM_XACT_ID)

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)

recordXactAbortData= (xl_xact_abort*)XLogRecGetData(record);

recordXtime=recordXactAbortData->xact_time;

}

elseif (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);

}

return false;

if (stopsHere)

*includeThis=recoveryTargetInclusive;

}

elseif (recoveryTarget==RECOVERY_TARGET_NAME)

{

stopsHere= (strcmp(recordRPName,recoveryTargetName)==0);

*includeThis= false;

}

{

recoveryStopXid,

timestamptz_to_str(recoveryStopTime))));

}

{

if (recoveryStopAfter)

ereport(LOG,

recoveryStopXid,

timestamptz_to_str(recoveryStopTime))));

}

{

strncpy(recoveryStopName,recordRPName,MAXFNAMELEN);

ereport(LOG,

(errmsg("recovery stopping at restore point \"%s\", time %s",

recoveryStopName,

timestamptz_to_str(recoveryStopTime))));

}

if (recoveryStopAfter)

SetLatestXTime(recordXtime);

ereport(LOG,

(errmsg("starting point-in-time recovery to %s",

timestamptz_to_str(recoveryTargetTime))));

elseif (recoveryTarget==RECOVERY_TARGET_NAME)

ereport(LOG,

(errmsg("starting point-in-time recovery to \"%s\"",

recoveryTargetName)));

ereport(LOG,

(errmsg("starting archive recovery")));

returnRecPtr;

}

XLogRestorePoint(constchar*rpName)

{

XLogRecPtrRecPtr;

XLogRecDatardata;

xl_restore_pointxlrec;

xlrec.rp_time=GetCurrentTimestamp();

strncpy(xlrec.rp_name,rpName,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);

returnRecPtr;

}

{

}

elseif (info==XLOG_RESTORE_POINT)

{

}

elseif (info==XLOG_BACKUP_END)

{

XLogRecPtrstartpoint;

{

appendStringInfo(buf,"xlog switch");

}

elseif (info==XLOG_RESTORE_POINT)

{

xl_restore_point*xlrec= (xl_restore_point*)rec;

appendStringInfo(buf,"restore point: %s",xlrec->rp_name);

}

elseif (info==XLOG_BACKUP_END)

{

XLogRecPtrstartpoint;

PG_RETURN_TEXT_P(cstring_to_text(location));

}

pg_create_restore_point(PG_FUNCTION_ARGS)

{

text*restore_name=PG_GETARG_TEXT_P(0);

char*restore_name_str;

XLogRecPtrrestorepoint;

charlocation[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 (!XLogIsNeeded())

ereport(ERROR,

(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),

errmsg("WAL level not sufficient for creating a restore point"),

errhint("wal_level must be set to \"archive\" or \"hot_standby\" at server start.")));

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);

snprintf(location,sizeof(location),"%X/%X",

restorepoint.xlogid,restorepoint.xrecoff);

PG_RETURN_TEXT_P(cstring_to_text(location));

}