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@gregsmith.com http://www.gregsmith.com Baltimore, MD