Re: WAL shipping question

On Tue, 4 Dec 2007, SHARMILA JOTHIRAJAH wrote:

> This basically archives the data in the primary server itself...right!!!
> But how can I set up continuous archiving from primary to a directory
> (WAL archive directory) on the stand-by server ?

The closest thing to a worked out example of how to do this I'm aware of
is at http://archives.postgresql.org/sydpug/2006-10/msg00001.php

That uses rsync as the transport mechanism for reasons it explains (the
'atomic copy' feature). You can certainly replicate that using ssh, but
you may have to use a secondary directory to hold files while they're
being transferred so the stand-by doesn't try to do something with the
partial copies. Mounting filesystems and copying the files over
Samba/NFS/etc. is another approach with its own issues. It's been my
experience that remote filesystems will hang in odd ways when there's a
connectivity problem, while copying with ssh/scp gives you a more
predictable copied/failed return code without retrying too hard.
PostgreSQL can tolerate the archive_command spitting back an error just
fine and will retry automatically, I prefer not to expose the server to a
situation where the archive_command might not return quickly.

The main thing that's improved in 8.3 is the integration of pg_standby as
a more rugged restore_command than most people were coding on their own:

http://www.postgresql.org/docs/8.3/static/pgstandby.html

You should use it instead of the example restore.sh included in the
message I referenced above.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD