Way past feature freeze, but this small change allows a powerful new
feature utilising the Restartable Recovery capability. Very useful for
very large database backups...
Includes full documentation.
Perhaps a bit rushed, but inclusion in 8.2 would be great. (Ouch, don't
shout back, read the patch first....)
-----------------------------
Docs copied here as better explanation:
<title>Incrementally Updated Backups</title>
<para>
Restartable Recovery can also be utilised to avoid the need to take
regular complete base backups, thus improving backup performance in
situations where the server is heavily loaded or the database is
very large. This concept is known as incrementally updated backups.
</para>
<para>
If we take a backup of the server files after a recovery is
partially
completed, we will be able to restart the recovery from the last
restartpoint. This backup is now further forward along the timeline
than the original base backup, so we can refer to it as an
incrementally
updated backup. If we need to recover, it will be faster to recover
from
the incrementally updated backup than from the base backup.
</para>
<para>
The <xref linkend="startup-after-recovery"> option in the
recovery.conf
file is provided to allow the recovery to complete up to the current
last
WAL segment, yet without starting the database. This option allows
us
to stop the server and take a backup of the partially recovered
server
files: this is the incrementally updated backup.
</para>
<para>
We can use the incrementally updated backup concept to come up with
a
streamlined backup schedule. For example:
<orderedlist>
<listitem>
<para>
Set up continuous archiving
</para>
</listitem>
<listitem>
<para>
Take weekly base backup
</para>
</listitem>
<listitem>
<para>
After 24 hours, restore base backup to another server, then run a
partial recovery and take a backup of the latest database state to
produce an incrmentally updated backup.
</para>
</listitem>
<listitem>
<para>
After next 24 hours, restore the incrementally updated backup to
the
second server, then run a partial recovery, at the end, take a
backup
of the partially recovered files.
</para>
</listitem>
<listitem>
<para>
Repeat previous step each day, until the end of the week.
</para>
</listitem>
</orderedlist>
</para>
<para>
A weekly backup need only be taken once per week, yet the same level
of
protection is offered as if base backups were taken nightly.
</para>
</sect2>
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Index: doc/src/sgml/backup.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.86
diff -c -r2.86 backup.sgml
*** doc/src/sgml/backup.sgml 16 Sep 2006 00:30:11 -0000 2.86
--- doc/src/sgml/backup.sgml 19 Sep 2006 14:03:32 -0000
***************
*** 1063,1068 ****
--- 1063,1081 ----
</listitem>
</varlistentry>
+ <varlistentry id="startup-after-recovery"
+ xreflabel="startup_after_recovery">
+ <term><varname>startup_after_recovery</varname>
+ (<type>boolean</type>)
+ </term>
+ <listitem>
+ <para>
+ Allows an incrementally updated backup to be taken.
+ See <xref linkend="backup-incremental-updated"> for discussion.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect3>
***************
*** 1137,1142 ****
--- 1150,1229 ----
</para>
</sect2>
+ <sect2 id="backup-incremental-updated">
+ <title>Incrementally Updated Backups</title>
+
+ <indexterm zone="backup">
+ <primary>incrementally updated backups</primary>
+ </indexterm>
+
+ <para>
+ Restartable Recovery can also be utilised to avoid the need to take
+ regular complete base backups, thus improving backup performance in
+ situations where the server is heavily loaded or the database is
+ very large. This concept is known as incrementally updated backups.
+ </para>
+
+ <para>
+ If we take a backup of the server files after a recovery is partially
+ completed, we will be able to restart the recovery from the last
+ restartpoint. This backup is now further forward along the timeline
+ than the original base backup, so we can refer to it as an incrementally
+ updated backup. If we need to recover, it will be faster to recover from
+ the incrementally updated backup than from the base backup.
+ </para>
+
+ <para>
+ The <xref linkend="startup-after-recovery"> option in the recovery.conf
+ file is provided to allow the recovery to complete up to the current last
+ WAL segment, yet without starting the database. This option allows us
+ to stop the server and take a backup of the partially recovered server
+ files: this is the incrementally updated backup.
+ </para>
+
+ <para>
+ We can use the incrementally updated backup concept to come up with a
+ streamlined backup schedule. For example:
+ <orderedlist>
+ <listitem>
+ <para>
+ Set up continuous archiving
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Take weekly base backup
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ After 24 hours, restore base backup to another server, then run a
+ partial recovery and take a backup of the latest database state to
+ produce an incrmentally updated backup.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ After next 24 hours, restore the incrementally updated backup to the
+ second server, then run a partial recovery, at the end, take a backup
+ of the partially recovered files.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Repeat previous step each day, until the end of the week.
+ </para>
+ </listitem>
+ </orderedlist>
+ </para>
+
+ <para>
+ A weekly backup need only be taken once per week, yet the same level of
+ protection is offered as if base backups were taken nightly.
+ </para>
+
+ </sect2>
+
<sect2 id="continuous-archiving-caveats">
<title>Caveats</title>
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.249
diff -c -r1.249 xlog.c
*** src/backend/access/transam/xlog.c 21 Aug 2006 16:16:31 -0000 1.249
--- src/backend/access/transam/xlog.c 19 Sep 2006 14:03:36 -0000
***************
*** 182,187 ****
--- 182,188 ----
static bool recoveryTarget = false;
static bool recoveryTargetExact = false;
static bool recoveryTargetInclusive = true;
+ static bool startupAfterRecovery = true;
static TransactionId recoveryTargetXid;
static time_t recoveryTargetTime;
***************
*** 2506,2514 ****
* or because the administrator has specified the restore program
* incorrectly. We have to assume the former.
*/
! ereport(DEBUG2,
(errmsg("could not restore file \"%s\" from archive: return code %d",
xlogfname, rc)));
/*
* if an archived file is not available, there might still be a version of
--- 2507,2520 ----
* or because the administrator has specified the restore program
* incorrectly. We have to assume the former.
*/
! ereport((startupAfterRecovery ? DEBUG2 : LOG),
(errmsg("could not restore file \"%s\" from archive: return code %d",
xlogfname, rc)));
+
+ if (startupAfterRecovery)
+ ereport(ERROR,
+ (errmsg("recovery ends normally with startup_after_recovery=false")));
+
/*
* if an archived file is not available, there might still be a version of
***************
*** 4343,4348 ****
--- 4349,4366 ----
ereport(LOG,
(errmsg("recovery_target_inclusive = %s", tok2)));
}
+ else if (strcmp(tok1, "startup_after_recovery") == 0)
+ {
+ if (strcmp(tok2, "false") == 0)
+ startupAfterRecovery = false;
+ else
+ {
+ startupAfterRecovery = true;
+ tok2 = "true";
+ }
+ ereport(LOG,
+ (errmsg("startup_after_recovery = %s", tok2)));
+ }
else
ereport(FATAL,
(errmsg("unrecognized recovery parameter \"%s\"",
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
