> 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. Thanks for your reply. I will look into the pg_standby and rsync. I currently have this in my config file archive_command = 'cp -i "%p" /export/home/user/archivedir/"%f" && scp -i userKey "%p" [EMAIL PROTECTED]:/export/home/user/archivedir/"%f"' userKey is a public-private key generated without the passphrase. This works... scp -i userKey dummy [EMAIL PROTECTED]:/export/home/user/archivedir and it just copies the dummy file from primary to standby without prompting for a password. It doesnt scp the wal files from primary to stand-by when i add it to archive_command as given above. I get this error Warning: Identity file userKey does not exist. ssh_askpass: exec(/usr/lib/ssh/ssh-askpass): No such file or directory Write failed: Broken pipe lost connection LOG: archive command "cp -i "pg_xlog/000000010000000000000039" /export/home/user/archivedir/"000000010000000000000039" && scp -i userKey "pg_xlog/000000010000000000000039" [EMAIL PROTECTED]:/export/home/user/archivedir/"000000010000000000000039"" failed: return code 256 ____________________________________________________________________________________ Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
