Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 3275F2E3002
	for <pgsql-docs-postgresql.org@postgresql.org>;
	Wed, 28 Nov 2007 09:39:30 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 17931-07 for <pgsql-docs-postgresql.org@postgresql.org>;
	Wed, 28 Nov 2007 09:39:15 -0400 (AST)
Received: from outmail128093.authsmtp.co.uk (outmail128093.authsmtp.co.uk
	[62.13.128.93]) by postgresql.org (Postfix) with ESMTP id 918422E004F
	for <pgsql-docs@postgresql.org>; Wed, 28 Nov 2007 09:39:20 -0400 (AST)
Received: from outmail128185.authsmtp.co.uk (outmail128185.authsmtp.co.uk
	[62.13.128.185])
	by punt4.authsmtp.com (8.14.1/8.14.1/Kp) with ESMTP id lASDdFWt054679;
	Wed, 28 Nov 2007 13:39:15 GMT
Received: from [192.168.0.3] (85-211-24-246.dyn.gotadsl.co.uk [85.211.24.246])
	(authenticated bits=0)
	by mail.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id lASDdDr6083154;
	Wed, 28 Nov 2007 13:39:13 GMT
Subject: Re: PITR Doc clarifications
From: Simon Riggs <simon@2ndquadrant.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-docs@postgresql.org
In-Reply-To: <20071128131231.GI5118@alvh.no-ip.org>
References: <1196255311.4246.1197.camel@ebony.site>
	<20071128131231.GI5118@alvh.no-ip.org>
Content-Type: multipart/mixed; boundary="=-Nk2Y4gOfLIoJ0rZY+JHo"
Date: Wed, 28 Nov 2007 13:39:32 +0000
Message-Id: <1196257173.4246.1226.camel@ebony.site>
Mime-Version: 1.0
X-Mailer: Evolution 2.8.2 
X-Server-Quench: 4ed70b41-9db7-11dc-814e-001185d377ca
X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ
	NksadBtFag1bY1ZH HGQLW1NEUV57Wmp/ ag4fZQBDYEtPQQdu
	TkpNRE1QERpoT0Bw ZWpKJlt0dw1FeXt0 K05kWHAVWBEuckYv
	Rk9JHDlUYnphaTRM TUhZc1ZJcANLfxdA PlN4UyBcLwdSbGoL
	MygYBAV5BRRjEmx5 RQpFJlYbW0cFHzU1 WxEFGy5H
X-Authentic-SMTP: 61633235383639.squirrel.dmpriest.net.uk:752/Kp
X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse
X-Virus-Status: No virus detected - but ensure you scan with your own
	anti-virus system!
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/101
X-Sequence-Number: 4634


--=-Nk2Y4gOfLIoJ0rZY+JHo
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

--=-Nk2Y4gOfLIoJ0rZY+JHo
Content-Disposition: attachment; filename=pitr_docs.v1.patch
Content-Type: text/x-patch; name=pitr_docs.v1.patch; charset=utf-8
Content-Transfer-Encoding: 7bit

Index: doc/src/sgml/backup.sgml
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.107
diff -c -r2.107 backup.sgml
*** doc/src/sgml/backup.sgml	16 Oct 2007 19:44:18 -0000	2.107
--- doc/src/sgml/backup.sgml	28 Nov 2007 12:58:27 -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,660 ----
     <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 off certain SQL operations are optimized
+ 	to avoid writing WAL, as described in <xref linkend="populate-pitr">. If 
+ 	archiving were enabled during execution of one of these statements then it
+ 	would be possible to write data that would not then be part of any 
+ 	concurrent backup because the WAL for those changes had been optimized away. 
+ 	So it is possible to alter <varname>archive_command</> with a reload, but 
+ 	<varname>archive_mode</> cannot be changed except at server start. 
+    </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>
--- 983,1006 ----
     <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 ****
--- 1234,1324 ----
     </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 /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
+ 	  into the archive so that both Base Backup and all required WAL files
+ 	  are part of the same 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 could
+ 	  be written in a popular scripting language such as bash or perl etc..
+ 	  Statements echoed to stderr will appear in the database server log, 
+ 	  allowing complex configurations to be easily diagnosed if they should 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 3 hours, rather than
+ 		 one at a time as they fill.
+         </para>
+        </listitem>
+        <listitem>
+         <para>
+ 		 Interfaces with other Backup and Recovery software.
+         </para>
+        </listitem>
+        <listitem>
+         <para>
+ 		 Interfaces 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())
--- 1540,1546 ----
      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())

--=-Nk2Y4gOfLIoJ0rZY+JHo--