Re: Permanent settings

Tom Lane wrote:
> Aidan Van Dyk <aidan(at)highrise(dot)ca> writes:
>
>> * Josh Berkus <josh(at)agliodbs(dot)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