Minor improvements in backup and recovery:

- create a separate archive_mode GUC, on which archive_command is dependent

- %r option in recovery.conf sends last restartpoint to recovery command

- %r used in pg_standby, updated README

- minor other code cleanup in pg_standby

- doc on Warm Standby now mentions pg_standby and %r

- log_restartpoints recovery option emits LOG message at each restartpoint

- end of recovery now displays last transaction end time, as requested
  by Warren Little; also shown at each restartpoint

- restart archiver if needed to carry away WAL files at shutdown

Simon Riggs

4 files changed, 92 insertions, 25 deletions

diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index 3040f3187dc..e34fc0f3a67 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.101 2007/09/14 13:26:22 momjian Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.102 2007/09/26 22:36:30 tgl Exp $ -->
 
 <chapter id="backup">
  <title>Backup and Restore</title>
@@ -518,10 +518,13 @@ tar -cf backup.tar /usr/local/pgsql/data
    </para>
 
    <para>
-    The shell command to use is specified by the <xref
-    linkend="guc-archive-command"> configuration parameter, which in practice
-    will always be placed in the <filename>postgresql.conf</filename> file.
-    In this string,
+    To enable WAL archiving, set the <xref
+    linkend="guc-archive-mode"> configuration parameter to <literal>on</>,
+    and specify the shell command to use in the <xref
+    linkend="guc-archive-command"> configuration parameter.  In practice
+    these settings will always be placed in the
+    <filename>postgresql.conf</filename> file.
+    In <varname>archive_command</>,
     any <literal>%p</> is replaced by the path name of the file to
     archive, while any <literal>%f</> is replaced by the file name only.
     (The path name is relative to the working directory of the server,
@@ -1030,8 +1033,15 @@ restore_command = 'cp /mnt/server/archivedir/%f %p'
         it to on the server.
         (The path name is relative to the working directory of the server,
         i.e., the cluster's data directory.)
+        Any <literal>%r</> is replaced by the name of the file containing the
+        last valid restartpoint. That is the earliest file that must be kept
+        to allow a restore to be restartable, so this information can be used
+        to truncate the archive to just the minimum required to support
+        restart of the current restore. <literal>%r</> would only be used in a
+        warm-standby configuration (see <xref
+        linkend="warm-standby-planning">).
         Write <literal>%%</> to embed an actual <literal>%</> character
-        in the command. 
+        in the command.
        </para>
        <para>
         It is important for the command to return a zero exit status if and
@@ -1118,6 +1128,20 @@ restore_command = 'copy /mnt/server/archivedir/%f "%p"'  # Windows
       </listitem>
      </varlistentry>
 
+     <varlistentry id="log-restartpoints" 
+                   xreflabel="log_restartpoints">
+      <term><varname>log_restartpoints</varname> 
+        (<type>boolean</type>)
+      </term>
+      <listitem>
+       <para>
+        Specifies whether to log each restart point as it occurs. This 
+        can be helpful to track the progress of a long recovery.
+        Default is <literal>false</>.
+       </para>
+      </listitem>
+     </varlistentry>
+
    </variablelist>
 
    </sect3>
@@ -1389,7 +1413,8 @@ restore_command = 'copy /mnt/server/archivedir/%f "%p"'  # Windows
     contact between the two database servers is the archive of WAL files
     that both share: primary writing to the archive, standby reading from
     the archive. Care must be taken to ensure that WAL archives for separate
-    primary servers do not become mixed together or confused.
+    primary servers do not become mixed together or confused. The archive
+    need not be large, if it is only required for the standby operation.
    </para>
 
    <para>
@@ -1427,6 +1452,12 @@ if (!triggered)
    </para>
 
    <para>
+    A working example of a waiting <varname>restore_command</> is provided
+    as a contrib module, named <application>pg_standby</>. This can be
+    extended as needed to support specific configurations or environments.
+   </para>
+
+   <para>
     <productname>PostgreSQL</productname> does not provide the system
     software required to identify a failure on the primary and notify
     the standby system and then the standby database server. Many such
@@ -1449,6 +1480,15 @@ if (!triggered)
     as the explicit creation of a trigger file is less error prone, if
     this can be arranged.
    </para>
+
+   <para>
+    The size of the WAL archive can be minimized by using the <literal>%r</>
+    option of the <varname>restore_command</>. This option specifies the
+    last archive filename that needs to be kept to allow the recovery to
+    restart correctly. This can be used to truncate the archive once
+    files are no longer required, if the archive is writable from the
+    standby server.
+   </para>
   </sect2>
 
   <sect2 id="warm-standby-config">
@@ -1468,8 +1508,10 @@ if (!triggered)
      <listitem>
       <para>
        Set up continuous archiving from the primary to a WAL archive located
-       in a directory on the standby server. Ensure that <xref
-       linkend="guc-archive-command"> and <xref linkend="guc-archive-timeout">
+       in a directory on the standby server. Ensure that
+       <xref linkend="guc-archive-mode">,
+       <xref linkend="guc-archive-command"> and
+       <xref linkend="guc-archive-timeout">
        are set appropriately on the primary
        (see <xref linkend="backup-archiving-wal">).
       </para>
@@ -1631,7 +1673,8 @@ if (!triggered)
     far back you need to keep WAL segment files to have a recoverable
     backup.  You can do this by running <application>pg_controldata</>
     on the standby server to inspect the control file and determine the
-    current checkpoint WAL location.
+    current checkpoint WAL location, or by using the 
+    <varname>log_restartpoints</> option to print values to the server log.
    </para>
   </sect2>
  </sect1>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 3cc911b265b..3d7dddc1ace 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.147 2007/09/25 20:03:37 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.148 2007/09/26 22:36:30 tgl Exp $ -->
 
 <chapter Id="runtime-config">
   <title>Server Configuration</title>
@@ -1620,6 +1620,24 @@ SET ENABLE_SEQSCAN TO OFF;
      <title>Archiving</title>
 
     <variablelist>
+     <varlistentry id="guc-archive-mode" xreflabel="archive_mode">
+      <term><varname>archive_mode</varname> (<type>boolean</type>)</term>
+      <indexterm>
+       <primary><varname>archive_mode</> configuration parameter</primary>
+      </indexterm>
+      <listitem>
+       <para>
+        When <varname>archive_mode</> is enabled, completed WAL segments
+        can be sent to archive storage by setting
+        <xref linkend="guc-archive-command">.
+        <varname>archive_mode</> and <varname>archive_command</> are
+        separate variables so that <varname>archive_command</> can be
+        changed without leaving archiving mode.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-archive-command" xreflabel="archive_command">
       <term><varname>archive_command</varname> (<type>string</type>)</term>
       <indexterm>
@@ -1628,8 +1646,7 @@ SET ENABLE_SEQSCAN TO OFF;
       <listitem>
        <para>
         The shell command to execute to archive a completed segment of
-        the WAL file series. If this is an empty string (the default),
-        WAL archiving is disabled. Any <literal>%p</> in the string is
+        the WAL file series.  Any <literal>%p</> in the string is
         replaced by the path name of the file to archive, and any
         <literal>%f</> is replaced by the file name only.
         (The path name is relative to the working directory of the server,
@@ -1638,7 +1655,12 @@ SET ENABLE_SEQSCAN TO OFF;
         command. For more information see <xref
         linkend="backup-archiving-wal">.
         This parameter can only be set in the <filename>postgresql.conf</>
-        file or on the server command line.
+        file or on the server command line.  It is ignored unless
+        <varname>archive_mode</> was enabled at server start.
+        If <varname>archive_command</> is an empty string (the default) while
+        <varname>archive_mode</> is enabled, then WAL archiving is temporarily
+        disabled, but the server continues to accumulate WAL segment files in
+        the expectation that a command will soon be provided.
        </para>
        <para>
         It is important for the command to return a zero exit status if
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index dcd0d1d2d33..df90f37438b 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.64 2007/03/29 00:15:36 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.65 2007/09/26 22:36:30 tgl Exp $ -->
 
  <chapter id="performance-tips">
   <title>Performance Tips</title>
@@ -819,7 +819,7 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
     needs to be written, because in case of an error, the files
     containing the newly loaded data will be removed anyway.
     However, this consideration does not apply when
-    <xref linkend="guc-archive-command"> is set, as all commands
+    <xref linkend="guc-archive-mode"> is set, as all commands
     must write WAL in that case.
    </para>
 
@@ -892,22 +892,24 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
   </sect2>
 
   <sect2 id="populate-pitr">
-   <title>Turn off <varname>archive_command</varname></title>
+   <title>Turn off <varname>archive_mode</varname></title>
 
    <para>
     When loading large amounts of data into an installation that uses
-    WAL archiving, you might want to disable archiving (unset the
-    <xref linkend="guc-archive-command"> configuration variable)
+    WAL archiving, you might want to disable archiving (turn off the
+    <xref linkend="guc-archive-mode"> configuration variable)
     while loading.  It might be
     faster to take a new base backup after the load has completed
     than to process a large amount of incremental WAL data.
+    But note that turning <varname>archive_mode</varname> on or off
+    requires a server restart.
    </para>
 
    <para>
     Aside from avoiding the time for the archiver to process the WAL data,
     doing this will actually make certain commands faster, because they
-    are designed not to write WAL at all if <varname>archive_command</varname>
-    is unset.  (They can guarantee crash safety more cheaply by doing an
+    are designed not to write WAL at all if <varname>archive_mode</varname>
+    is off.  (They can guarantee crash safety more cheaply by doing an
     <function>fsync</> at the end than by writing WAL.)
     This applies to the following commands: 
     <itemizedlist>
@@ -989,8 +991,8 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
      <listitem>
       <para>
        If using WAL archiving, consider disabling it during the restore.
-       To do that, unset <varname>archive_command</varname> before loading the
-       dump script, and afterwards restore <varname>archive_command</varname>
+       To do that, turn off <varname>archive_mode</varname> before loading the
+       dump script, and afterwards turn it back on
        and take a fresh base backup.
       </para>
      </listitem>
diff --git a/doc/src/sgml/ref/show.sgml b/doc/src/sgml/ref/show.sgml
index ab64f859428..14ddd6744f8 100644
--- a/doc/src/sgml/ref/show.sgml
+++ b/doc/src/sgml/ref/show.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/show.sgml,v 1.43 2007/09/11 00:06:41 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/show.sgml,v 1.44 2007/09/26 22:36:30 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -171,7 +171,7 @@ SHOW ALL;
               name              |            setting             |                                         description                                          
 --------------------------------+--------------------------------+----------------------------------------------------------------------------------------------
  add_missing_from               | off                            | Automatically adds missing table references to FROM clauses.
- archive_command                | unset                          | WAL archiving command.
+ allow_system_table_mods        | off                            | Allows modifications of the structure of system tables.
     .
     .
     .