Re: Enabling archive_mode without restart

On Wed, 12 Nov 2008, Jonah H. Harris wrote:

> In a large-scale OLTP environment, uptime is paramount, and having to
> restart the database to enable PITR is a big PITA.

What I did last time I was stuck with this problem was make the
archive_command point to a script I could toggle on and off outside of the
database itself via a settings file. Then you can leave archive_mode on
all the time, instead adjusting that settings file to determine whether it
ships the logs somewhere or just ignores them when it gets called.

A large benefit to that approach is that you can do other upgrades to said
script without touching the database configuration. The whole idea of
implementing the archive_command as a one-line bit is horrifying to me
anyway. Put it in a script instead, then you can do error checking and
basic sanity checks (disk space and number of active WAL segments are two
good things to monitor) and e-mail or otherwise alert if there's a
problem.

The primary downside to always having archiving on is you lose the fresh
table WAL optimization path, but in real systems I haven't found that to
be so compelling. When doing initial bulk loading, absolutely a help, but
if your database doesn't actually create/truncate tables in normal use it
doesn't buy you anything once you're in production.

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