Subject: wal shipping/pg_standby docs patch

From: Frank Wiles <[email-masked]>
Date: Wed, 25 Mar 2009 14:20:51 -0500

Hello,

It has been awhile since I setup a warm standby server, so I dutifully
followed the documentation.  However, the documentation specifically
suggests using 'cp -i' as the archive command.  Granted, it does say
that it is only a suggestion and not a recommendation, but does
specifically mention it for Linux.

Wasn't three days before I was bitten by transferring a WAL file
before it was fully copied into the archive location.

Attached is a patch that makes the suggestion be 'rsync -avr' in both
backup.sgml and pg_standby.sgml.

Thanks!

-- 
Frank Wiles
Revolution Systems | http://www.revsys.com/
[email-masked]   | (800) 647-6298
diff -ur postgresql-snapshot/doc/src/sgml/backup.sgml my-postgresql/doc/src/sgml/backup.sgml
--- postgresql-snapshot/doc/src/sgml/backup.sgml	2009-03-05 13:50:03.000000000 -0600
+++ my-postgresql/doc/src/sgml/backup.sgml	2009-03-25 13:35:24.000000000 -0500
@@ -564,7 +564,7 @@
 </programlisting>
     which will copy archivable WAL segments to the directory
     <filename>/mnt/server/archivedir</>.  (This is an example, not a
-    recommendation, and might not work on all platforms.)  After the
+    recommendation, and might not work on all platforms. It is highly recommend you use a tool that does atomic copies such as <programlisting>rsync -arv %p...</programlisting>)  After the
     <literal>%p</> and <literal>%f</> parameters have been replaced,
     the actual command executed might look like this:
 <programlisting>
diff -ur postgresql-snapshot/doc/src/sgml/pgstandby.sgml my-postgresql/doc/src/sgml/pgstandby.sgml
--- postgresql-snapshot/doc/src/sgml/pgstandby.sgml	2009-02-27 03:30:21.000000000 -0600
+++ my-postgresql/doc/src/sgml/pgstandby.sgml	2009-03-25 13:27:42.000000000 -0500
@@ -207,7 +207,7 @@
   <para>On Linux or Unix systems, you might use:</para>
 
   <programlisting>
-archive_command = 'cp %p .../archive/%f'
+archive_command = 'rsync -arz %p .../archive/%f'
 
 restore_command = 'pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5442 .../archive %f %p %r 2>>standby.log'
   </programlisting>

---

From: Tom Lane <[email-masked]>
Date: Wed, 25 Mar 2009 22:04:11 -0400

> backup.sgml and pg_standby.sgml.

Seems to me the first question to ask is exactly why you got "bit" by
cp.  I doubt that rsync will provide a magic answer.

			regards, tom lane

---

From: Frank Wiles <[email-masked]>
Date: Thu, 26 Mar 2009 11:08:50 -0500

> cp.  I doubt that rsync will provide a magic answer.

Hi Tom,

Using the example in the docs in both places suggests using cp to copy
to your archive location, which isn't atomic.  This leaves open the
possibility of transferring to your warm standby server part of a WAL
file.  In my case I was using rsync in cron to pull from the "master"
onto the warm standby server.  Ran fine for 3 days, then I transferred
a 14MB and a 8.2MB WAL file.  Basically being unlucky and transferring
it at just the wrong time.

This obviously hosed the warm standby and I had to go through the
whole backup procedure again.  Using rsync, as it is atomic, prevents
this.