Re: WAL archiving to network drive

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD