Re: WAL archiving to network drive

Greg Smith wrote:
> On Wed, 20 Aug 2008, Glen Parker wrote:
> The database will continue accumulating WAL segments it can't recycle if
> the archiver keeps failing, which can cause the size of the pg_xlog
> directory (often mounted into a separate, smaller partition or disk) to
> increase dramatically. You do not want to be the guy who caused the
> database to go down because the xlog disk filled after some network
> mount flaked out. I've seen that way too many times in WAN environments
> where the remote location was unreachable for days, due to natural
> disaster for example, and since under normal operation pg_xlog never got
> very big it wasn't sized for that.
>
> It will also slow things down a bit under heavy write loads, as every
> segment change will result in creating a new segment file rather than
> re-using an old one.

So you advocate archiving the WAL files from a small xlog volume, to a
larger local volume. Why not just make the xlog volume large enough to
handle overruns, since you obviously have the space? Copying each WAL
from one place to another on the local machine FAR outweighs the extra
overhead created when WAL files most be created rather than recycled.

Also, you mention days of down time, natural disasters, and a WAN. My
DBMS and archive machines are in the same room. If I had to deal with
different locations, I'd build more safety into the system. In fact, in
a way, I have. My WALs are archived immediately to another machine,
where they are (hours later) sent to tape in batches, which is then
hiked off location; emulating to some extent your decoupled system.

> OK, maybe you're smarter than that and used a separate script. DBAs are
> also not happy changing a script that gets called by the database every
> couple of minutes, and as soon as there's more than one piece involved
> it can be difficult to do an atomic update of said script.

Yes I'm smarter than that, and I'm also the DBA, so I don't mind much ;-)

-Glen