Re: Permanent settings

Tom Lane wrote:
> Aidan Van Dyk <aidan@highrise.ca> writes:
>   
>> * Josh Berkus <josh@agliodbs.com> [080220 18:00]:
>>     
>>> We need a server-based tool for the manipulating postgresql.conf, and
>>> one which is network-accessable, allows updating individual settings,
>>>       
>
>   
>> Do we need to develop our own set of "remote management" tools/systems,
>> or possibly document some best practices using already available "multi-
>> server managment" tools?
>>     
>
> Indeed.  If Josh's argument were correct, why isn't every other daemon
> on the planet moving away from textual configuration files?
>
> IIRC, one of the arguments for the config include-file facility was to
> simplify management of multiple servers by letting them share part or
> all of their configuration data.  One of the things that bothers me
> considerably about all the proposals made so far in this thread
> (including mine) is that they don't play very nicely with such a
> scenario.  Putting a setting into one file that contradicts one made in
> some other file is a recipe for confusion and less admin-friendliness,
> not more.
>   

If you're interested in comments from the peanut gallery, we run 
hundreds of instances of nearly equal numbers of oracle, sql server, 
postgres, mysql.

IMHO oracle has the most polish here, with its pfile/spfile business 
(excluding listener management, which is still pretty primitive, esp 
compared to the equivalent of what pg_hba.conf offers).  SQL Server is 
close, with the internal table sysconfigures, but some things do get 
stashed in the registry.  You can programatically edit this across the 
network, so it's not so bad.

For postgres and mysql, we make mass changes by being able to mass 
distribute a postgresql.conf or my.cnf patch script, which is usually 
sed/awk/perl-ish in nature, and then running this en masse from a host 
setup with a trusted ssh key that can look through the servers list and 
call the patch script on each host (and for each instance that might be 
on that host).   The config files get auto-checked into a SCM from 
there.   So it's very much as Aidan described.

To date, this approach has worked without any problems.  In our case, 
there is a very uniform layout for everything, which is what makes this 
work.  postgresql.conf/my.cnf start from general templates, there are 
standard locations for everything, there are shell functions to fetch 
details about any instance from a master list, etc.  So while some team 
members would be happy if Pg were more Oracle-esque, it's not a *major* 
issue for us. 

I can't imagine, though, a so-called "enterprise" setup where I would be 
willing to literally share the same config file across instances (via 
NFS or whatever).  Seriously, that's just not done.  Someone may do that 
for a few or even a dozen, but not on the scale Josh is talking about.  
Further, while every daemon may not be moving away from text config 
files, most every database is?

Certainly if our environment grew out faster than we were able to 
consolidate it and develop a sensible, structured approach, I could 
imagine we would be in a world of hurt.  The oracle approach seems to me 
close to the dba's ideal.  I can generate a text representation from the 
running instance or binary representation, modify the running instance 
and/or binary representation from the text version, find the locations 
of these programmatically, and do all this via sqlplus or whatever I 
want, across the network.  If we mass deploy a pile of servers, we're 
not likely to comment different settings for each instance, but rather 
document in our install docs why we are now setting A to x instead of y 
across the board. 

Regards,
Paul