Re: PITR Doc clarifications - Mailing list pgsql-docs

Simon Riggs wrote:
> On Wed, 2007-11-28 at 10:12 -0300, Alvaro Herrera wrote:
> > Simon Riggs wrote:
> > >
> > > A few docs changes, as mentioned.
> > >
> > > Comments?
> >
> > You forgot the patch :-)
>
> No, just a very short patch. :-)
>
> This patch is slightly longer...

Slightly modified patch attached and applied.  Thanks.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/backup.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.108
diff -c -c -r2.108 backup.sgml
*** doc/src/sgml/backup.sgml    28 Nov 2007 15:42:30 -0000    2.108
--- doc/src/sgml/backup.sgml    28 Nov 2007 22:34:06 -0000
***************
*** 641,650 ****
     <para>
      Also, you can force a segment switch manually with
      <function>pg_switch_xlog</>, if you want to ensure that a
!     just-finished transaction is archived immediately.  Other utility
      functions related to WAL management are listed in <xref
      linkend="functions-admin-backup-table">.
     </para>
    </sect2>

    <sect2 id="backup-base-backup">
--- 641,662 ----
     <para>
      Also, you can force a segment switch manually with
      <function>pg_switch_xlog</>, if you want to ensure that a
!     just-finished transaction is archived as soon as possible.  Other utility
      functions related to WAL management are listed in <xref
      linkend="functions-admin-backup-table">.
     </para>
+
+    <para>
+     When <varname>archive_mode</> is <literal>off</> some SQL commands
+     are optimized to avoid WAL logging, as described in <xref
+     linkend="populate-pitr">. If archiving were turned on during execution
+     of one of these statements, WAL would not contain enough information
+     for archive recovery.  (Crash recovery is unaffected.)  For
+     this reason, <varname>archive_mode</> can only be changed at server
+     start.  (<varname>archive_command</> can be changed with a
+     configuration file reload, and setting it to <literal>''</> does
+     prevent archiving.)
+    </para>
    </sect2>

    <sect2 id="backup-base-backup">
***************
*** 973,986 ****
     <para>
      Normally, recovery will proceed through all available WAL segments,
      thereby restoring the database to the current point in time (or as
!     close as we can get given the available WAL segments).  But if you want
!     to recover to some previous point in time (say, right before 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.
     </para>

     <note>
--- 985,1008 ----
     <para>
      Normally, recovery will proceed through all available WAL segments,
      thereby restoring the database to the current point in time (or as
!     close as we can get given the available WAL segments).  So a normal
!     recovery will end with a "file not found" message, the exact text
!     of the error message depending upon your choice of
!     <varname>restore_command</>.  You may also see an error message
!     at the start of recovery for a file named something like
!     <filename>00000001.history</>.  This is also normal and does not
!     indicate a problem in simple recovery situations. See
!     <xref linkend="backup-timelines"> for discussion.
!    </para>
!
!    <para>
!     If you want to recover to some previous point in time (say, right before
!     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.
     </para>

     <note>
***************
*** 1214,1219 ****
--- 1236,1327 ----
     </para>
    </sect2>

+   <sect2 id="backup-tips">
+    <title>Tips and Examples</title>
+
+    <para>
+     Some examples of configuring Continuous Archiving are given here.
+    </para>
+
+     <sect3 id="backup-standalone">
+      <title>Recovery Settings</title>
+
+      <para>
+       It is possible to use the existing backup facilities to produce
+       standalone hot backups. These are backups that cannot be used for
+       point-in-time recovery, yet are much faster to backup and restore
+       than <application>pg_dump</>.
+      </para>
+
+      <para>
+       To configure standalone backups you should use a switch file. If the
+       file exists then archives are made, otherwise archiving is ignored.
+ <programlisting>
+ archive_command = 'test -f /var/lib/pgsql/backup_in_progress && cp -i %p /var/lib/pgsql/archive/%f
</dev/null'
+ </programlisting>
+       Backup can then be taken using a script like the following:
+ <programlisting>
+ touch /var/lib/pgsql/backup_in_progress
+ psql -c "select pg_start_backup('hot_backup');"
+ tar -cvf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
+ psql -c "select pg_stop_backup();"
+ sleep 20
+ rm /var/lib/pgsql/backup_in_progress
+ tar -rvf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
+ </programlisting>
+       The switch file <filename>/var/lib/pgsql/backup_in_progress</> is
+       created first, allowing archiving to start prior to the backup.
+       After the backup the switch file is removed. Archived WAL files are
+       then added to the backup so that both base backup and all required
+       WAL files are part of the same <application>tar</> file.
+      </para>
+     </sect3>
+
+     <sect3 id="backup-scripts">
+      <title><varname>archive_command</varname> scripts</title>
+
+      <para>
+       Many people choose to use scripts to define their
+       <varname>archive_command</varname>, so that their
+       <filename>postgresql.conf</> looks very simple:
+ <programlisting>
+ archive_command = 'local_backup_script.sh'
+ </programlisting>
+       This allows all complexity to be managed within the script, which
+       can be written in a popular scripting language such as
+       <application>bash</> or <application>perl</>.  Statements echoed to
+       <literal>stderr</> will appear in the database server log, allowing
+       complex configurations to be easily diagnosed if they fail.
+      </para>
+      <para>
+       Example of how scripts might be used include:
+       <itemizedlist>
+        <listitem>
+         <para>
+          Copying data to a secure off-site data storage provider
+         </para>
+        </listitem>
+        <listitem>
+         <para>
+          Batching WAL files so they are transferred every three hours, rather than
+          one at a time as they fill
+         </para>
+        </listitem>
+        <listitem>
+         <para>
+          Interfacing with other backup and recovery software
+         </para>
+        </listitem>
+        <listitem>
+         <para>
+          Interfacing with monitoring software to report errors directly
+         </para>
+        </listitem>
+       </itemizedlist>
+      </para>
+     </sect3>
+   </sect2>
+
    <sect2 id="continuous-archiving-caveats">
     <title>Caveats</title>

***************
*** 1435,1441 ****
      Pseudocode for a suitable <varname>restore_command</> is:
  <programlisting>
  triggered = false;
! while (!NextWALFileReady() && !triggered)
  {
      sleep(100000L);         /* wait for ~0.1 sec */
      if (CheckForExternalTrigger())
--- 1543,1549 ----
      Pseudocode for a suitable <varname>restore_command</> is:
  <programlisting>
  triggered = false;
! while (!NextWALFileReady() && !triggered)
  {
      sleep(100000L);         /* wait for ~0.1 sec */
      if (CheckForExternalTrigger())