On Tue, 19 Aug 2008, Rob Adams wrote:
Is it best to give the 'postgres' user network access & archive the WAL files directly to the network drive? Or archive the WAL files to a local folder and then use a scheduled task to move them to the network drive?
I normally write first to a local drive then move to the network, because that has some useful properties:
1) As as long as there's local disk space, the archive_command called always returns quickly. While performance here doesn't really matter it makes me feel better.
2) If there is a problem with the network drive, the slow/blocked network write is not happening where the database is waiting for it.
3) In an emergency where the usual network drive is unavailable, you can temporarily change your archive strategy without touching the database server's configuration.
4) The copying process doesn't need read access to the main database, and the database server process doesn't need permissions on the network drive. I find myself giving it a completely different set of permissions, so that it can't touch the database at all really, but can run alert scripts and take actions I'd never want the database user to have access to.
The main downside of doing it this is way is you're stuck writing and debugging a lot more scripts to make sure everything happens correctly. I've found the alternative--just putting something into archive_command does the transfer directly--isn't quite flexible enough when things go wrong for me to be comfortable with it.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
