Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 8AAC82E01B1
	for <pgsql-docs-postgresql.org@postgresql.org>;
	Wed, 28 Nov 2007 18:37:49 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 38184-03 for <pgsql-docs-postgresql.org@postgresql.org>;
	Wed, 28 Nov 2007 18:36:35 -0400 (AST)
Received: from momjian.us (momjian.us [70.90.9.53])
	by postgresql.org (Postfix) with ESMTP id D77452E2E39
	for <pgsql-docs@postgresql.org>; Wed, 28 Nov 2007 18:36:34 -0400 (AST)
Received: (from bruce@localhost) by momjian.us (8.11.6/8.11.6) id
 lASMaLJ09459;
	Wed, 28 Nov 2007 17:36:21 -0500 (EST)
From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200711282236.lASMaLJ09459@momjian.us>
Subject: Re: PITR Doc clarifications
In-Reply-To: <1196257173.4246.1226.camel@ebony.site>
To: Simon Riggs <simon@2ndquadrant.com>
Date: Wed, 28 Nov 2007 17:36:21 -0500 (EST)
CC: Alvaro Herrera <alvherre@alvh.no-ip.org>, pgsql-docs@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL124 (25)]
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ELM1196289381-12213-5_"
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/115
X-Sequence-Number: 4648


--ELM1196289381-12213-5_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

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

--ELM1196289381-12213-5_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/rtmp/diff"

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 &amp;&amp; cp -i %p /var/lib/pgsql/archive/%f &lt;/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() &amp;&amp; !triggered)
  {
      sleep(100000L);         /* wait for ~0.1 sec */
      if (CheckForExternalTrigger())

--ELM1196289381-12213-5_--