Re: Permanent settings

Currently, pgAdmin supports editing postgresql.conf remotely using the
adminpack to open the file, change it locally in memory, and using the
adminpack again to write it back. This means that in theory pgAdmin needs a
full postgresql.conf parser. Right now it doesn't have this - it just
exposes the config file itself. Which sucks for usability, and it's
something I've heard a lot of people complain about. Other databases (in my
personal experience MSSQL, but IIRC I've had people say the same about
other ones as well) support configuring the database remotely (and using a
GUI for the most common options), and this is a feature that a lot of users
are lacking in PostgreSQL. I'd like to do something about that.

What I'd really like to see is something like a new keyword on the SET
command, so you could to SET PERMANENT foo=bar, which would write the
configuration back into postgresql.conf.

I don't have a complete solution for how to actually implement it, so I'm
just throwing out some ideas for comment.

I don't think we need to be able to parse and deal with "very complex
configuration files", as long as we're not likely to corrupt them badly.

The task got a bit harder with the support of include files, but I'm sure
it's doable.

One way might be to simply have the config file reader store the location
for each setting where it was found, and when you do a SET PERMANENT (if
that's what we'd call it) it'll go back to that place and make the
modification there. If a setting hasn't previously been set, we could just
append it to the end of the main configuration file.

One thing that can be hard to deal with is comments. It would be good if
there was some way to support reading/writing simple comments (say a # at
the end of the line) through this API, but I think it's OK not to deal with
complex multi-line comments. I think it's fairly safe to say that the vast
majority of users will *either* change their configuration through the
config file *or* through the API. Or those that use both aren't likely to
use really complex combinations of config files and comments and such.

(before someone complains about the "argh, editing config files remote is
insecure" - we can always have a config option to turn it off. And it can
still be protected by not giving the server write permissions on the file,
or selinux, or whatever)

Thoughts? More ranting?

//Magnus

* Magnus Hagander <magnus(at)hagander(dot)net> [080219 09:37]:

> One way might be to simply have the config file reader store the location
> for each setting where it was found, and when you do a SET PERMANENT (if
> that's what we'd call it) it'll go back to that place and make the
> modification there. If a setting hasn't previously been set, we could just
> append it to the end of the main configuration file.
>
> One thing that can be hard to deal with is comments. It would be good if
> there was some way to support reading/writing simple comments (say a # at
> the end of the line) through this API, but I think it's OK not to deal with
> complex multi-line comments. I think it's fairly safe to say that the vast
> majority of users will *either* change their configuration through the
> config file *or* through the API. Or those that use both aren't likely to
> use really complex combinations of config files and comments and such.

Speaking as one who favours the unix admin style (i.e. editing the
config file), take the following with a grain of salt.

But if you *need* a way to "set permanent", couldn't you do with with
the following KISS idea?

Any "set permanent" settings should be *appended* to the main config
file, preferably with a comment line, like:
# Set by user <USER> from client <CLIENT> on <TIMESTAMP>
some_guc option = some_value

This does 2 things:

1) Eliminates a need for a fancy config parser/editor/rewriter
2) Makes it very easy to implement
3) Even allows adminpack to have a "set_permanent" function that could do it
all internally, and not actually need support in the backend core.

This relies on the fact that a "later" setting over-rides an earlier
one. This also means that your postgres user actually has write access
to the config files (is this something people normally allow?) I guess
this "write" problem could be overcome with yet-another-guc to specify
the "permanent write file" path..

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Magnus Hagander
> Sent: Tuesday, February 19, 2008 8:36 AM
> To: pgsql-hackers
> Subject: [HACKERS] Permanent settings
>
> What I'd really like to see is something like a new keyword on the SET
> command, so you could to SET PERMANENT foo=bar, which would write the
> configuration back into postgresql.conf.

How about putting an indicator in the postgresql.conf file dynamic=1 and
then the db could manage the file else the dynamic change wouldn't stick
on a restart? You wouldn't need to add a new keyword this way and less
likely for a DBA to mess up the syntax.

Jon

On Tue, Feb 19, 2008 at 09:53:30AM -0500, Aidan Van Dyk wrote:
> * Magnus Hagander <magnus(at)hagander(dot)net> [080219 09:37]:
>
> > One way might be to simply have the config file reader store the location
> > for each setting where it was found, and when you do a SET PERMANENT (if
> > that's what we'd call it) it'll go back to that place and make the
> > modification there. If a setting hasn't previously been set, we could just
> > append it to the end of the main configuration file.
> >
> > One thing that can be hard to deal with is comments. It would be good if
> > there was some way to support reading/writing simple comments (say a # at
> > the end of the line) through this API, but I think it's OK not to deal with
> > complex multi-line comments. I think it's fairly safe to say that the vast
> > majority of users will *either* change their configuration through the
> > config file *or* through the API. Or those that use both aren't likely to
> > use really complex combinations of config files and comments and such.
>
> Speaking as one who favours the unix admin style (i.e. editing the
> config file), take the following with a grain of salt.
>
> But if you *need* a way to "set permanent", couldn't you do with with
> the following KISS idea?
>
> Any "set permanent" settings should be *appended* to the main config
> file, preferably with a comment line, like:
> # Set by user <USER> from client <CLIENT> on <TIMESTAMP>
> some_guc option = some_value

Are you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?

> This does 2 things:
>
> 1) Eliminates a need for a fancy config parser/editor/rewriter
> 2) Makes it very easy to implement
> 3) Even allows adminpack to have a "set_permanent" function that could do it
> all internally, and not actually need support in the backend core.

I should warn you that it's on my radar to try to find the compromises
necessary to merge the required functionality away from adminpack and into
core for 8.4. Not sure if I'll manage, but I'm certainly going to try.
Having to install contrib modules to be able to read your logfiles (just
one of several examples) is another thing that most users I've come in
contact with hate.

//Magnus

* Magnus Hagander <magnus(at)hagander(dot)net> [080219 10:28]:

> > But if you *need* a way to "set permanent", couldn't you do with with
> > the following KISS idea?
> >
> > Any "set permanent" settings should be *appended* to the main config
> > file, preferably with a comment line, like:
> > # Set by user <USER> from client <CLIENT> on <TIMESTAMP>
> > some_guc option = some_value
>
> Are you suggesting we keep appending? So if I set the same parameter 100
> times, it would show up on 100 rows?

In my opinion, absolutely. It's easy, safe, and the "overhead"
associated with it is minimal, and not in any critical path "work" path.
Add to that the fact that the admin can easily clean up the file any
time he wants too.

> > This does 2 things:
> >
> > 1) Eliminates a need for a fancy config parser/editor/rewriter
> > 2) Makes it very easy to implement
> > 3) Even allows adminpack to have a "set_permanent" function that could do it
> > all internally, and not actually need support in the backend core.
>
> I should warn you that it's on my radar to try to find the compromises
> necessary to merge the required functionality away from adminpack and into
> core for 8.4. Not sure if I'll manage, but I'm certainly going to try.
> Having to install contrib modules to be able to read your logfiles (just
> one of several examples) is another thing that most users I've come in
> contact with hate.

Sure, but the "append via an adminpack function" has the added benifit
that it can easily be "backported" as a contrib module to the previous
versions that pgadmin supports as well.

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

> Are you suggesting we keep appending? So if I set the same parameter 100
> times, it would show up on 100 rows?

What about not touching the config file at all, but write to a separate
file which is completely under the control of postgres and include that
at the end of the config file ? You just said includes are a new feature
which could complicate things, so why not use it actually in your
advantage ;-)

That way disabling the overrides would be as simple as commenting out
the inclusion of the postgres controlled config file. And it would
separate the user writable and machine writable configuration...

Cheers,
Csaba.

On Tue, Feb 19, 2008 at 10:34:26AM -0500, Aidan Van Dyk wrote:
> * Magnus Hagander <magnus(at)hagander(dot)net> [080219 10:28]:
>
> > > But if you *need* a way to "set permanent", couldn't you do with with
> > > the following KISS idea?
> > >
> > > Any "set permanent" settings should be *appended* to the main config
> > > file, preferably with a comment line, like:
> > > # Set by user <USER> from client <CLIENT> on <TIMESTAMP>
> > > some_guc option = some_value
> >
> > Are you suggesting we keep appending? So if I set the same parameter 100
> > times, it would show up on 100 rows?
>
> In my opinion, absolutely. It's easy, safe, and the "overhead"
> associated with it is minimal, and not in any critical path "work" path.
> Add to that the fact that the admin can easily clean up the file any
> time he wants too.

I think that's entirely unworkable. While I absolutelyi don't want to break
things for people who use the config file as the primary interface (heck,
*I* am one of those people), it has to be usable for the case it's trying
to fix. And this really wouldn't be.

> > > This does 2 things:
> > >
> > > 1) Eliminates a need for a fancy config parser/editor/rewriter
> > > 2) Makes it very easy to implement
> > > 3) Even allows adminpack to have a "set_permanent" function that could do it
> > > all internally, and not actually need support in the backend core.
> >
> > I should warn you that it's on my radar to try to find the compromises
> > necessary to merge the required functionality away from adminpack and into
> > core for 8.4. Not sure if I'll manage, but I'm certainly going to try.
> > Having to install contrib modules to be able to read your logfiles (just
> > one of several examples) is another thing that most users I've come in
> > contact with hate.
>
> Sure, but the "append via an adminpack function" has the added benifit
> that it can easily be "backported" as a contrib module to the previous
> versions that pgadmin supports as well.

I should clearify that I'm not at all against implementing it as a
function - that may well be a better way. And it would be backportable. I'm
only against the "stick it in adminpack because some people don't want it"
part. It should be made good enough that it's not disruptive for those that
don't want it, and good enough to be in core.

//Magnus

On Tue, Feb 19, 2008 at 04:38:16PM +0100, Csaba Nagy wrote:
> > Are you suggesting we keep appending? So if I set the same parameter 100
> > times, it would show up on 100 rows?
>
> What about not touching the config file at all, but write to a separate
> file which is completely under the control of postgres and include that
> at the end of the config file ? You just said includes are a new feature
> which could complicate things, so why not use it actually in your
> advantage ;-)
>
> That way disabling the overrides would be as simple as commenting out
> the inclusion of the postgres controlled config file. And it would
> separate the user writable and machine writable configuration...

Yeah, that may actually be a very good way to implement it. I don't like
the idea of continously appending to an existing file, but if we did have a
separate file with a tightly controlled format that would be doable.

The end result wouldn't be "as clean" as some would expect, but it would
certainly be easier code-wise. For example, I'm sure someone would get the
suggestion to go edit postgresql.conf to change a config value, and be
surprised when it didn't show up as a changed setting because it was
overridden from another file..

//Magnus

Magnus Hagander wrote:
> What I'd really like to see is something like a new keyword on the SET
> command, so you could to SET PERMANENT foo=bar, which would write the
> configuration back into postgresql.conf.
>
> I don't have a complete solution for how to actually implement it, so I'm
> just throwing out some ideas for comment.

Not sure if it's of interest, but you might want to look at the postfix
mailserver configuration setup and see if that translates to an API.

postconf
lists the configuration settings (in alphabetical order)
postconf -n
list non-default settings
postconf <setting>
display "setting = value"
postconf -e <setting> = <value>
edit the configuration file, changing that setting

The editing option replaces any existing version of that setting and
adds the new value at the end of the file.

Having all the values at the end of the file works well, because for a
simple setup you don't need to change many settings and they don't
depend on order.

--
Richard Huxton
Archonet Ltd

On Tue, 2008-02-19 at 16:41 +0100, Magnus Hagander wrote:
> The end result wouldn't be "as clean" as some would expect, but it would
> certainly be easier code-wise. For example, I'm sure someone would get the
> suggestion to go edit postgresql.conf to change a config value, and be
> surprised when it didn't show up as a changed setting because it was
> overridden from another file..

Yes, but at least the override part would be nicely separated in a file,
and could suggestively be named as something like
postgresql.conf.override, and hopefully will stick out sufficiently for
those who edit the config file directly to wonder about it's purpose...
and of course always editable directly too, so you can easily manually
fix foot-shooting mistakes made from the admin interface. It would be
just simply rewritten each time you change something without regard to
the manual changes, and possibly ignored altogether if your manual
changes violate it's expected layout.

Cheers,
Csaba.

On Tue, Feb 19, 2008 at 03:53:11PM +0000, Richard Huxton wrote:
> Magnus Hagander wrote:
> >What I'd really like to see is something like a new keyword on the SET
> >command, so you could to SET PERMANENT foo=bar, which would write the
> >configuration back into postgresql.conf.
> >
> >I don't have a complete solution for how to actually implement it, so I'm
> >just throwing out some ideas for comment.
>
> Not sure if it's of interest, but you might want to look at the postfix
> mailserver configuration setup and see if that translates to an API.
>
> postconf
> lists the configuration settings (in alphabetical order)

SELECT * FROM pg_settings

> postconf -n
> list non-default settings

SELECT * FROM pg_settings WHERE NOT source='default'

> postconf <setting>
> display "setting = value"

SHOW log_destination

> postconf -e <setting> = <value>
> edit the configuration file, changing that setting

That's the one remaining :-)

> The editing option replaces any existing version of that setting and
> adds the new value at the end of the file.

Eh, it cannot both replace it, and add it at the end of the file, can it?
Does it replace it in-line, or does it remove the in-line entry and put the
new one at the end? Or are you saying it edits in-line entries and appends
new ones at the end?

> Having all the values at the end of the file works well, because for a
> simple setup you don't need to change many settings and they don't
> depend on order.

Right. I don't think we have any settings that depend on order, do we?

//Magnus

Magnus Hagander wrote:
> On Tue, Feb 19, 2008 at 03:53:11PM +0000, Richard Huxton wrote:
>> Magnus Hagander wrote:
>>> What I'd really like to see is something like a new keyword on the SET
>>> command, so you could to SET PERMANENT foo=bar, which would write the
>>> configuration back into postgresql.conf.
>>>
>>> I don't have a complete solution for how to actually implement it, so I'm
>>> just throwing out some ideas for comment.
>> Not sure if it's of interest, but you might want to look at the postfix
>> mailserver configuration setup and see if that translates to an API.
>>
>> postconf
>> lists the configuration settings (in alphabetical order)
>
> SELECT * FROM pg_settings
>
>> postconf -n
>> list non-default settings
>
> SELECT * FROM pg_settings WHERE NOT source='default'
>
>> postconf <setting>
>> display "setting = value"
>
> SHOW log_destination
>
>> postconf -e <setting> = <value>
>> edit the configuration file, changing that setting
>
> That's the one remaining :-)
>
>
>> The editing option replaces any existing version of that setting and
>> adds the new value at the end of the file.
>
> Eh, it cannot both replace it, and add it at the end of the file, can it?
> Does it replace it in-line, or does it remove the in-line entry and put the
> new one at the end? Or are you saying it edits in-line entries and appends
> new ones at the end?

Sorry,
- Edits existing lines.
- Adds new ones to end of file.
- Leaves blank lines, comments etc. alone

>> Having all the values at the end of the file works well, because for a
>> simple setup you don't need to change many settings and they don't
>> depend on order.
>
> Right. I don't think we have any settings that depend on order, do we?

That's what I was trying to think of - nothing came to mind.

--
Richard Huxton
Archonet Ltd

* Magnus Hagander <magnus(at)hagander(dot)net> [080219 10:39]:
> On Tue, Feb 19, 2008 at 10:34:26AM -0500, Aidan Van Dyk wrote:
> > > Are you suggesting we keep appending? So if I set the same parameter 100
> > > times, it would show up on 100 rows?
> >
> > In my opinion, absolutely. It's easy, safe, and the "overhead"
> > associated with it is minimal, and not in any critical path "work" path.
> > Add to that the fact that the admin can easily clean up the file any
> > time he wants too.
>
> I think that's entirely unworkable. While I absolutelyi don't want to break
> things for people who use the config file as the primary interface (heck,
> *I* am one of those people), it has to be usable for the case it's trying
> to fix. And this really wouldn't be.

Can you explain why this wouldn't be usable?

I see the following propeties:

*) KISS
*) Easily "function-able"
*) 0 cost on the server writing "new/changed" GUC settings (open/seek/write/close)
*) 0 cost on setting "permanent" settings via commands
*) 0 cost on PostgreSQL config code infrastructure
*) 0 cost on "running" database
*) minimal cost on "reading" config file (a few more lines)

This seems to be usable for everything the case it's trying to fix
wants:
*) simple, and guarenteed to work, not loosing any existing config file syntax
*) not hard to maintain/backport
*) not expensive to a running database cluster
*) "permanent" settings are saved/reloaded correctly

I don't see anything that would make this unusable for the purpose of
having the server be able to "permanently" save GUC settings.

A user using this interface isn't going to care if a file is 1 line,
or 100 lines, and whether the config file parsing (on startup or reload)
takes 13.34ms or 13.69ms.

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

* Csaba Nagy <nagy(at)ecircle-ag(dot)com> [080219 10:59]:
> > Are you suggesting we keep appending? So if I set the same parameter 100
> > times, it would show up on 100 rows?
>
> What about not touching the config file at all, but write to a separate
> file which is completely under the control of postgres and include that
> at the end of the config file ? You just said includes are a new feature
> which could complicate things, so why not use it actually in your
> advantage ;-)
>
> That way disabling the overrides would be as simple as commenting out
> the inclusion of the postgres controlled config file. And it would
> separate the user writable and machine writable configuration...

Yes, I think that would be necessary (like I said), because in most
installations, I don't even thing the postgres user even has write
access to the main config file.

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Aidan Van Dyk wrote:
>>> Any "set permanent" settings should be *appended* to the main config
>>> file, preferably with a comment line, like:
>>> # Set by user <USER> from client <CLIENT> on <TIMESTAMP>
>>> some_guc option = some_value
>>>
>> Are you suggesting we keep appending? So if I set the same parameter 100
>> times, it would show up on 100 rows?
>>
>
> In my opinion, absolutely. It's easy, safe, and the "overhead"
> associated with it is minimal, and not in any critical path "work" path.
> Add to that the fact that the admin can easily clean up the file any
> time he wants too.
>
>

I think this is quite unacceptable and ugly. Creating an ever-growing
file that the admin would have to clean up by hand is horrid.

ISTM that this whole area is likely to be difficult unless we move to a
more structured config file (JSON, anyone?)

cheers

andrew

Richard Huxton wrote:
> Magnus Hagander wrote:
>>
>> Right. I don't think we have any settings that depend on order, do we?
>
> That's what I was trying to think of - nothing came to mind.
>

custom_variable_classes and dependents?

cheers

andrew

On Tue, Feb 19, 2008 at 11:11:05AM -0500, Aidan Van Dyk wrote:
> * Csaba Nagy <nagy(at)ecircle-ag(dot)com> [080219 10:59]:
> > > Are you suggesting we keep appending? So if I set the same parameter 100
> > > times, it would show up on 100 rows?
> >
> > What about not touching the config file at all, but write to a separate
> > file which is completely under the control of postgres and include that
> > at the end of the config file ? You just said includes are a new feature
> > which could complicate things, so why not use it actually in your
> > advantage ;-)
> >
> > That way disabling the overrides would be as simple as commenting out
> > the inclusion of the postgres controlled config file. And it would
> > separate the user writable and machine writable configuration...
>
> Yes, I think that would be necessary (like I said), because in most
> installations, I don't even thing the postgres user even has write
> access to the main config file.

The postgres user gets it by default whenever you run a standard initdb,
AFAIK.

//Magnus

On Tue, Feb 19, 2008 at 11:09:43AM -0500, Aidan Van Dyk wrote:
> * Magnus Hagander <magnus(at)hagander(dot)net> [080219 10:39]:
> > On Tue, Feb 19, 2008 at 10:34:26AM -0500, Aidan Van Dyk wrote:
> > > > Are you suggesting we keep appending? So if I set the same parameter 100
> > > > times, it would show up on 100 rows?
> > >
> > > In my opinion, absolutely. It's easy, safe, and the "overhead"
> > > associated with it is minimal, and not in any critical path "work" path.
> > > Add to that the fact that the admin can easily clean up the file any
> > > time he wants too.
> >
> > I think that's entirely unworkable. While I absolutelyi don't want to break
> > things for people who use the config file as the primary interface (heck,
> > *I* am one of those people), it has to be usable for the case it's trying
> > to fix. And this really wouldn't be.
>
> Can you explain why this wouldn't be usable?

Because you will end up with an ever-growing file, that will be a PITA to
deal with. Consider it after 10k+ changes. (yes, I can see that happening.
You know how some people use GUIs) Or 100k. The problem does not happen at
100 lines...

I can see the solution with a single file with them all in, but it needs to
be able to overwrite them IMHO.

//Magnus

On Tue, Feb 19, 2008 at 11:14:59AM -0500, Andrew Dunstan wrote:
>
>
> Richard Huxton wrote:
> >Magnus Hagander wrote:
> >>
> >>Right. I don't think we have any settings that depend on order, do we?
> >
> >That's what I was trying to think of - nothing came to mind.
> >
>
> custom_variable_classes and dependents?

I think we could easily get away with saying that you can't change
custom_variable_classes remotely through this interface. That's not
something the user generally changes, that's something that's set when you
install a new module.

//Magnus

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> What I'd really like to see is something like a new keyword on the SET
> command, so you could to SET PERMANENT foo=bar, which would write the
> configuration back into postgresql.conf.

FWIW, I made a Pl/PerlU function that did this at one point. At first, I
parsed the postgresql.conf file and rewrote it, but after a while
I switched to the include a writeable file idea. The downside to that was
trying to follow the path of files to figure out what a particular setting
was (e.g. "grep 'effective' postgresql.conf" no longer provided a canonical
answer), so at the end of the day I simply appended a big comment to the bottom
of the postgresql.conf file and added the settings there. Rather than
adding 100 lines for 100 changes to the same variable, the function checked
the postgresql.conf into version control[1] after every change. That might be
ambitious for the SET command to handle, but it would sure be a slick feature :)
If not that, it might be nice to provide a switch to allow 100 lines, with
timestamp, if desired. An optional comment from the command line would be
another nice touch:

SET PERMANENT effective_cache_size='4GB' COMMENT='Added more RAM to box'

[1] As long as your version control was cvs, subversion, git, or rcs.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200802191128
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAke7BGAACgkQvJuQZxSWSsg4GQCg3nnXaRBvZqJRnFIkq+Y8sXRr
hZ4AoPVQnJEnk3lJFpNJmikuDwaqz88c
=5BwE
-----END PGP SIGNATURE-----

"Magnus Hagander" <magnus(at)hagander(dot)net> writes:

> Yeah, that may actually be a very good way to implement it. I don't like
> the idea of continously appending to an existing file, but if we did have a
> separate file with a tightly controlled format that would be doable.

+1

Separating the automatically written configuration and the explicit user
configuration is definitely the right approach. My experience comes from
Debian where packages editing their own configuration files is verboten.
Otherwise you run into problems reconciling user-made changes and automatic
changes.

The include file method is workable but isn't perfect. What happens if a user
connects with pgadmin and changes a parameter but that parameter is overridden
by a variable in the config file?

The alternative is to have two files and read them both. Then if you change a
variable which is overridden by the other source you can warn that the change
is ineffective.

I think on balance the include file method is so much simpler that I prefer it.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Gregory Stark wrote:

> The alternative is to have two files and read them both. Then if you change a
> variable which is overridden by the other source you can warn that the change
> is ineffective.
>
> I think on balance the include file method is so much simpler that I prefer it.

I think this is a good idea. I would suggest being able to query
exactly which config file a setting came from -- so you can see whether
it's the stock postgresql.conf, or the locally-modified
postgresql.local.conf.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

>
> Gregory Stark wrote:
>
> > The alternative is to have two files and read them both. Then if you
> change a
> > variable which is overridden by the other source you can warn that
the
> change
> > is ineffective.
> >
> > I think on balance the include file method is so much simpler that I
> prefer it.
>
> I think this is a good idea. I would suggest being able to query
> exactly which config file a setting came from -- so you can see
whether
> it's the stock postgresql.conf, or the locally-modified
> postgresql.local.conf.
>

So a junior DBA goes to manage the db. Makes a change the
postgresql.conf file and bounces the db. The change doesn't stick.
That doesn't sound like fun and it also sounds like Oracle's spfile and
pfile.

Jon

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, 19 Feb 2008 15:36:26 +0100
Magnus Hagander <magnus(at)hagander(dot)net> wrote:

> Currently, pgAdmin supports editing postgresql.conf remotely using the
> adminpack to open the file, change it locally in memory, and using the
> adminpack again to write it back. This means that in theory pgAdmin
> needs a full postgresql.conf parser. Right now it doesn't have this -
> it just exposes the config file itself. Which sucks for usability,
> and it's something I've heard a lot of people complain about. Other
> databases (in my personal experience MSSQL, but IIRC I've had people
> say the same about other ones as well) support configuring the
> database remotely (and using a GUI for the most common options), and
> this is a feature that a lot of users are lacking in PostgreSQL. I'd
> like to do something about that.
>
>
> What I'd really like to see is something like a new keyword on the SET
> command, so you could to SET PERMANENT foo=bar, which would write the
> configuration back into postgresql.conf.

IMO this should all be in the database and that's it. The idea that our
global settings are in a file seems unusual consider we have a
perfectly good storage engine available.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHuxvEATb/zqfZUUQRAhoFAJ4jDcM0WyUuu0VgN9EZL7qRmMPPSACfb7Gh
dyY7w3KDaCO1xQMdhtF50x0=
=6/GJ
-----END PGP SIGNATURE-----

Joshua D. Drake wrote:

> IMO this should all be in the database and that's it. The idea that our
> global settings are in a file seems unusual consider we have a
> perfectly good storage engine available.

That doesn't work, because many settings must be loaded before the
database is fully operational.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> IMO this should all be in the database and that's it. The idea that our
> global settings are in a file seems unusual consider we have a
> perfectly good storage engine available.

The sufficient reason why not is that many of these settings must be
available before we can read the database.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, 19 Feb 2008 15:22:42 -0300
Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:

> Joshua D. Drake wrote:
>
> > IMO this should all be in the database and that's it. The idea that
> > our global settings are in a file seems unusual consider we have a
> > perfectly good storage engine available.
>
> That doesn't work, because many settings must be loaded before the
> database is fully operational.

Right but couldn't that be changed or if not, why not only have the
settings that "must" be loaded before the database is fully operation
in the postgresql.conf file.

I can hear the wails of we don't want multiple configuration sources
but we already have multiple configuration sources and having 90% of
the configuration in the database should would make it easier.

Sincerely,

Joshua D. Drake
- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHux+nATb/zqfZUUQRAjJtAKCCaH8Ubud/OZ8Gv7oOAJf8jjO9nACfQFf8
WMzBcyZcNvWGgoPv6b1fR/w=
=IEBA
-----END PGP SIGNATURE-----

Alvaro Herrera wrote:
> Joshua D. Drake wrote:
>
>
>> IMO this should all be in the database and that's it. The idea that our
>> global settings are in a file seems unusual consider we have a
>> perfectly good storage engine available.
>>
>
> That doesn't work, because many settings must be loaded before the
> database is fully operational.
>
>

Not to mention what would happen if the database had a problem so we
couldn't read the config.

Knowing when not to use a database is as important as knowing when to
use one.

cheers

andrew

On Tue, Feb 19, 2008 at 02:19:16PM -0300, Alvaro Herrera wrote:
> Gregory Stark wrote:
>
> > The alternative is to have two files and read them both. Then if you change a
> > variable which is overridden by the other source you can warn that the change
> > is ineffective.
> >
> > I think on balance the include file method is so much simpler that I prefer it.
>
> I think this is a good idea. I would suggest being able to query
> exactly which config file a setting came from -- so you can see whether
> it's the stock postgresql.conf, or the locally-modified
> postgresql.local.conf.

Yeah, that's something I've been thinking about quite apart from this one.
If you have a bunch of include files, it can be interesting to know exactly
where the current setting was picked up. It's not like you can't find it
elsewhere, but it's quicker if we could add that to pg_settings. I'll add
that to my TODO list for things to look at when I work on this.

//Magnus

On Tue, Feb 19, 2008 at 04:58:21PM +0000, Gregory Stark wrote:
> "Magnus Hagander" <magnus(at)hagander(dot)net> writes:
>
> > Yeah, that may actually be a very good way to implement it. I don't like
> > the idea of continously appending to an existing file, but if we did have a
> > separate file with a tightly controlled format that would be doable.
>
> +1
>
> Separating the automatically written configuration and the explicit user
> configuration is definitely the right approach. My experience comes from
> Debian where packages editing their own configuration files is verboten.
> Otherwise you run into problems reconciling user-made changes and automatic
> changes.
>
> The include file method is workable but isn't perfect. What happens if a user
> connects with pgadmin and changes a parameter but that parameter is overridden
> by a variable in the config file?

Um, if you put the include statement at the bottom, isn't that the one that
will override?

> The alternative is to have two files and read them both. Then if you change a
> variable which is overridden by the other source you can warn that the change
> is ineffective.

Ok, now I don't follow. If we use an include, we do have two files, and we
read them both, no?

> I think on balance the include file method is so much simpler that I prefer it.

Yeah, that is one very clear argument for that method.

Since there have been no major protests, I assume that if I can come up
with reasonably pretty code without opening up any horrible holes, going
by the include method is the way to go?

//Magnus

Andrew Dunstan wrote:
>
>
> Alvaro Herrera wrote:
>> Joshua D. Drake wrote:
>>
>>
>>> IMO this should all be in the database and that's it. The idea that our
>>> global settings are in a file seems unusual consider we have a
>>> perfectly good storage engine available.
>>>
>>
>> That doesn't work, because many settings must be loaded before the
>> database is fully operational.
>>
>>
>
> Not to mention what would happen if the database had a problem so we
> couldn't read the config.
>
>

Although, on further reflection, we could probably meet both of these
objections by having the database maintain a text version of the config
which it would load on startup.

One other possible objection is that it would allow any superuser to
set things that currently require direct access to the config files, so
that would be a major change in security arrangements.

cheers

andrew

"Magnus Hagander" <magnus(at)hagander(dot)net> writes:

> On Tue, Feb 19, 2008 at 04:58:21PM +0000, Gregory Stark wrote:
>
>> The include file method is workable but isn't perfect. What happens if a user
>> connects with pgadmin and changes a parameter but that parameter is overridden
>> by a variable in the config file?
>
> Um, if you put the include statement at the bottom, isn't that the one that
> will override?

I was picturing putting it on top on the general principle that manual changes
should override automatic ones. I see I'm in the minority though.

It doesn't really matter though, this is all in the manually edited file --
the admin can always move it around or add other configuration settings below
it.

>> The alternative is to have two files and read them both. Then if you change a
>> variable which is overridden by the other source you can warn that the change
>> is ineffective.
>
> Ok, now I don't follow. If we use an include, we do have two files, and we
> read them both, no?

Not from the point of view of the guc processing. It's all one source. Even if
it remembered which file various settings came from it's not going to remember
what order they arrived or what might hypothetically override a new setting.

I was describing have two independent files read separately and kept track of
separately. That would hard code one having preference over the other and mean
that pgadmin could look at the guc source to see if there's a command-line
variable, environment variable, or popstgresql.conf setting which overrides
the postgresql.auto (or whatever) settings.

>> I think on balance the include file method is so much simpler that I prefer it.
>
> Yeah, that is one very clear argument for that method.

Still my feeling. We can put comments in the default config warning about the
consequences to pgadmin of overriding variables after the include.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

On Tue, 2008-02-19 at 19:38 +0000, Gregory Stark wrote:
> "Magnus Hagander" <magnus(at)hagander(dot)net> writes:
>
> > On Tue, Feb 19, 2008 at 04:58:21PM +0000, Gregory Stark wrote:
> >
> >> The include file method is workable but isn't perfect. What happens if a user
> >> connects with pgadmin and changes a parameter but that parameter is overridden
> >> by a variable in the config file?
> >
> > Um, if you put the include statement at the bottom, isn't that the one that
> > will override?
>
> I was picturing putting it on top on the general principle that manual changes
> should override automatic ones. I see I'm in the minority though.

Oh.

> It doesn't really matter though, this is all in the manually edited file --
> the admin can always move it around or add other configuration settings below
> it.

Yeah - or take it away completely if needed.

> >> The alternative is to have two files and read them both. Then if you change a
> >> variable which is overridden by the other source you can warn that the change
> >> is ineffective.
> >
> > Ok, now I don't follow. If we use an include, we do have two files, and we
> > read them both, no?
>
> Not from the point of view of the guc processing. It's all one source. Even if
> it remembered which file various settings came from it's not going to remember
> what order they arrived or what might hypothetically override a new setting.
>
> I was describing have two independent files read separately and kept track of
> separately. That would hard code one having preference over the other and mean
> that pgadmin could look at the guc source to see if there's a command-line
> variable, environment variable, or popstgresql.conf setting which overrides
> the postgresql.auto (or whatever) settings.

Oh, ok. That does seem a bit more complicated than needed, though.

> >> I think on balance the include file method is so much simpler that I prefer it.
> >
> > Yeah, that is one very clear argument for that method.
>
> Still my feeling. We can put comments in the default config warning about the
> consequences to pgadmin of overriding variables after the include.

Yeah.

Another thing I thought about - would people prefer a function or a
parameter to the SET statement. Since I would imagine they're more or
less only to be used from frontend programs like pgadmin, it could just
as well be a function...

//Magnus

Magnus, All,

This is something I've been thinking about too, just because my efforts to
write auto-config scripts have gotten bogged down in the need to parse and
write .conf files in a paltform-agnostic way and preserve comments.  I
agree with Magnus that it's something we need to address.  Having the
ability to update .conf through an api other than reading & writing a file
one line will make developing future autotuning tools significanly easier.

I think that the idea of just appending extra lines to the bottom of the
file
in chronoligical (or random) order is so messy and hackish that it's simply
not worthy of consideration for the PostgreSQL project.

Instead, here's my proposal:

1) add to the top of postgresql.conf another file switch, like this:

# auto_config_file = 'ConfigDir/postgresql.auto.conf'
# if set, the auto config file will be read by the system and override the
settings in the rest of this postgresql.conf file, which will be ignored.
# to disable automated and SQL command-line-based configuration
# comment the above or set it to an empty string

2) split the "category" column in pg_settings into two columns, and add a
categories lookup table, so it can be sorted properly

3) have command line config write to postgresql.auto.conf, dumping the
whole of pg_settings organized with headings in categories order.

I think an arrangement like that will work well with pg_settings based
config, autotuning, while still allowing backwards-compatible manual
control via postgresql.conf.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

On Tue, 2008-02-19 at 13:31 -0800, Josh Berkus wrote:
> Magnus, All,
>
> This is something I've been thinking about too, just because my efforts to
> write auto-config scripts have gotten bogged down in the need to parse and
> write .conf files in a paltform-agnostic way and preserve comments. I
> agree with Magnus that it's something we need to address. Having the
> ability to update .conf through an api other than reading & writing a file
> one line will make developing future autotuning tools significanly easier.
>
> I think that the idea of just appending extra lines to the bottom of the
> file
> in chronoligical (or random) order is so messy and hackish that it's simply
> not worthy of consideration for the PostgreSQL project.
>
> Instead, here's my proposal:
>
> 1) add to the top of postgresql.conf another file switch, like this:
>
> # auto_config_file = 'ConfigDir/postgresql.auto.conf'
> # if set, the auto config file will be read by the system and override the
> settings in the rest of this postgresql.conf file, which will be ignored.
> # to disable automated and SQL command-line-based configuration
> # comment the above or set it to an empty string

That's basically "include" but with a different name, no?

> 2) split the "category" column in pg_settings into two columns, and add a
> categories lookup table, so it can be sorted properly

Why do you need to split it in two columns, and what would go in what
column?

> 3) have command line config write to postgresql.auto.conf, dumping the
> whole of pg_settings organized with headings in categories order.

Don't get what you mean here. You mean you want a commandline tool to
generate a config file from pg_settings?

Another question completely, but related, is if it's actually the right
thing to use postgresql.conf to write documentation. The way it is now
we basically add all new config options to postgresql.conf.sample along
with a comment that is the documentation. A different approach would be
to only include the very most common settings, or possibly even only
those that initdb sets to something non-default, in
postgresql.conf.sample, and have the rest only added when they're
actually used. Documentation really belongs in the documentation, after
all...

But again, that's a different question - it's equally valid with or
without an API way for modifying the configuration.

//Magnus

Magnus,

> That's basically "include" but with a different name, no?

Yes. FWIW, I seem to be lagged about 3 hours on -hackers.

> Why do you need to split it in two columns, and what would go in what
> column?

Current data:

postgres=# select name, category from pg_settings;
name | category
-------------------------+-------------------------------------------------------------------
allow_system_table_mods | Developer Options
archive_command | Write-Ahead Log / Settings
archive_mode | Write-Ahead Log / Settings
archive_timeout | Write-Ahead Log / Settings

How it should be:

postgres=# select name, category, subcategory from pg_settings;
name | category | subcategory
-------------------------+------------------------------------------------
allow_system_table_mods | Developer Options |
archive_command | Write-Ahead Log | Settings
archive_mode | Write-Ahead Log | Settings
archive_timeout | Write-Ahead Log | Settings

this would then allow us to do this:

select * from pg_settings_categories
name order
Developer Options 37
Write-Ahead Log 11

select * from pg_settings_subcategories
name category
allow_system_table_mods Developer Options
archive_command Write-Ahead Log
archive_mode Write-Ahead Log

and then generate a file which looks like this:

# == Write-Ahead Log ==
# Settings

archive_command = '/bin/rsync'
archive_mode = 'on'

# Fsync

fsync = on
wal_buffers = 8mb

... etc.

This would allow the automatically generated version to be readable and
searchable, if not quite as narrative as the present postgresql.conf.

>
> > 3) have command line config write to postgresql.auto.conf, dumping the
> > whole of pg_settings organized with headings in categories order.
>
> Don't get what you mean here. You mean you want a commandline tool to
> generate a config file from pg_settings?

I meant from the SQL command line.

> Another question completely, but related, is if it's actually the right
> thing to use postgresql.conf to write documentation. The way it is now
> we basically add all new config options to postgresql.conf.sample along
> with a comment that is the documentation. A different approach would be
> to only include the very most common settings, or possibly even only
> those that initdb sets to something non-default, in
> postgresql.conf.sample, and have the rest only added when they're
> actually used. Documentation really belongs in the documentation, after
> all...

Yeah, we've taken an Apache-like approach of including heavy comments on
the settings in the settings file itself. Unfortunately, I think changing
that practice at this point would alienate a bunch of users.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

One idea would be to remove duplicate postgresql.conf appended entries
on server start.

---------------------------------------------------------------------------

Josh Berkus wrote:
> Magnus,
>
> > That's basically "include" but with a different name, no?
>
> Yes. FWIW, I seem to be lagged about 3 hours on -hackers.
>
> > Why do you need to split it in two columns, and what would go in what
> > column?
>
> Current data:
>
> postgres=# select name, category from pg_settings;
> name | category
> -------------------------+-------------------------------------------------------------------
> allow_system_table_mods | Developer Options
> archive_command | Write-Ahead Log / Settings
> archive_mode | Write-Ahead Log / Settings
> archive_timeout | Write-Ahead Log / Settings
>
> How it should be:
>
> postgres=# select name, category, subcategory from pg_settings;
> name | category | subcategory
> -------------------------+------------------------------------------------
> allow_system_table_mods | Developer Options |
> archive_command | Write-Ahead Log | Settings
> archive_mode | Write-Ahead Log | Settings
> archive_timeout | Write-Ahead Log | Settings
>
> this would then allow us to do this:
>
> select * from pg_settings_categories
> name order
> Developer Options 37
> Write-Ahead Log 11
>
> select * from pg_settings_subcategories
> name category
> allow_system_table_mods Developer Options
> archive_command Write-Ahead Log
> archive_mode Write-Ahead Log
>
> and then generate a file which looks like this:
>
> # == Write-Ahead Log ==
> # Settings
>
> archive_command = '/bin/rsync'
> archive_mode = 'on'
>
> # Fsync
>
> fsync = on
> wal_buffers = 8mb
>
> ... etc.
>
> This would allow the automatically generated version to be readable and
> searchable, if not quite as narrative as the present postgresql.conf.
>
>
> >
> > > 3) have command line config write to postgresql.auto.conf, dumping the
> > > whole of pg_settings organized with headings in categories order.
> >
> > Don't get what you mean here. You mean you want a commandline tool to
> > generate a config file from pg_settings?
>
> I meant from the SQL command line.
>
> > Another question completely, but related, is if it's actually the right
> > thing to use postgresql.conf to write documentation. The way it is now
> > we basically add all new config options to postgresql.conf.sample along
> > with a comment that is the documentation. A different approach would be
> > to only include the very most common settings, or possibly even only
> > those that initdb sets to something non-default, in
> > postgresql.conf.sample, and have the rest only added when they're
> > actually used. Documentation really belongs in the documentation, after
> > all...
>
> Yeah, we've taken an Apache-like approach of including heavy comments on
> the settings in the settings file itself. Unfortunately, I think changing
> that practice at this point would alienate a bunch of users.
>
> --
> --Josh
>
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Tuesday 19 February 2008 15:05, Bruce Momjian wrote:
> One idea would be to remove duplicate postgresql.conf appended entries
> on server start.

I think anything which has us appending extra settings to the end of the
file is a non-starter. We'd get "I changed the setting, but nothing's
happening" error reports 8x hour on #postgresql.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> On Tuesday 19 February 2008 15:05, Bruce Momjian wrote:
>> One idea would be to remove duplicate postgresql.conf appended entries
>> on server start.

> I think anything which has us appending extra settings to the end of the
> file is a non-starter. We'd get "I changed the setting, but nothing's
> happening" error reports 8x hour on #postgresql.

Yeah, I agree. Any proposal that makes it materially harder for people
to maintain the config files with an editor is going to suffer so much
push-back that it will ultimately fail. And adding extra copies of
settings to an existing file does make it harder.

What I would suggest is to write a function in contrib/adminpack that
updates the config file by replacing the variable assignment in-place.
(Yes, it will have to be smart enough to parse the config file, but
that hardly requires a great deal of smarts.) If that implementation
sees sufficient usage then we can migrate the functionality into core.

It was complained up-thread that some installations make the config
files read-only to the postgres user, but I see no conflict there.
Anyone who does that is saying that they don't *want* automatic changes
to the configuration settings. Such folk will not consider it a
feature for the database to make an end-run around that policy.

regards, tom lane

On Tuesday 19 February 2008 14:32, Andrew Dunstan wrote:
> Andrew Dunstan wrote:
> > Alvaro Herrera wrote:
> >> Joshua D. Drake wrote:
> >>> IMO this should all be in the database and that's it. The idea that our
> >>> global settings are in a file seems unusual consider we have a
> >>> perfectly good storage engine available.
> >>
> >> That doesn't work, because many settings must be loaded before the
> >> database is fully operational.
> >

this is a valid objection, though I think it could be worked around.

> > Not to mention what would happen if the database had a problem so we
> > couldn't read the config.
>

people bring this objection for moving pg_hba settings into the db, but I
think the same answer applies; having a command line flag for the postmaster
to read options from a file probably gets you around this.

> Although, on further reflection, we could probably meet both of these
> objections by having the database maintain a text version of the config
> which it would load on startup.
>

yes, that would probably work.

> One other possible objection is that it would allow any superuser to
> set things that currently require direct access to the config files, so
> that would be a major change in security arrangements.
>

If you are superuser, you can write a C function (or just install adminpacks
functions) and do this anyway. (there might be a way to prevent this, but I'm
not quite sure how you would do it)

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Tuesday 19 February 2008 20:08, Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > On Tuesday 19 February 2008 15:05, Bruce Momjian wrote:
> >> One idea would be to remove duplicate postgresql.conf appended entries
> >> on server start.
> >
> > I think anything which has us appending extra settings to the end of the
> > file is a non-starter. We'd get "I changed the setting, but nothing's
> > happening" error reports 8x hour on #postgresql.
>
> Yeah, I agree. Any proposal that makes it materially harder for people
> to maintain the config files with an editor is going to suffer so much
> push-back that it will ultimately fail. And adding extra copies of
> settings to an existing file does make it harder.
>

+1

> What I would suggest is to write a function in contrib/adminpack that
> updates the config file by replacing the variable assignment in-place.
> (Yes, it will have to be smart enough to parse the config file, but
> that hardly requires a great deal of smarts.) If that implementation
> sees sufficient usage then we can migrate the functionality into core.
>

phppgadmin would certainly use said function if it existed in core, so I'd
suggest if we go that route put it in 8.4 straight away. My guess is it
would also be easier to maintain if it was built-in.

> It was complained up-thread that some installations make the config
> files read-only to the postgres user, but I see no conflict there.
> Anyone who does that is saying that they don't *want* automatic changes
> to the configuration settings. Such folk will not consider it a
> feature for the database to make an end-run around that policy.
>

Hmm.... I don't think I've ever seen one like this, but thinking about it I
suppose I could see the argument and way to do it... but yes, I think you'd
get an error that the file was read-only, so the behavior would be similar to
trying to edit it on the box as postgres user.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, Feb 19, 2008 at 04:38:16PM +0100, Csaba Nagy wrote:
> > Are you suggesting we keep appending? So if I set the same parameter 100
> > times, it would show up on 100 rows?
>
> What about not touching the config file at all, but write to a separate
> file which is completely under the control of postgres and include that
> at the end of the config file ?

+2

Not only that, but including "in the middle" would allow to flexibly
state what may be overridden by the "gui" and what not. And it is a
clean separation of mechanism (general include mechanism) and policy.

More complex schemes (a top-level postgresql.conf and a directory of
includable module-specific files postgresql.conf.d) would be imaginable
(mimicking a bit Debian's way to deal with such things).

On syntax: anything (current is fine by me :) but please not XML :-/

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFHu97FBcgs9XrR2kYRAul8AJ0dbPgVUjMCroIfUB9k4p6n6NU1vwCdFkLK
HIdGsrX+lWOFBAJKSEDW2Ms=
=lxkZ
-----END PGP SIGNATURE-----

On Tue, Feb 19, 2008 at 02:59:44PM -0800, Josh Berkus wrote:
> Magnus,
>
> > That's basically "include" but with a different name, no?
>
> Yes. FWIW, I seem to be lagged about 3 hours on -hackers.
>
> > Why do you need to split it in two columns, and what would go in what
> > column?
>
> Current data:
>
> postgres=# select name, category from pg_settings;
> name | category
> -------------------------+-------------------------------------------------------------------
> allow_system_table_mods | Developer Options
> archive_command | Write-Ahead Log / Settings
> archive_mode | Write-Ahead Log / Settings
> archive_timeout | Write-Ahead Log / Settings
>
> How it should be:
>
> postgres=# select name, category, subcategory from pg_settings;
> name | category | subcategory
> -------------------------+------------------------------------------------
> allow_system_table_mods | Developer Options |
> archive_command | Write-Ahead Log | Settings
> archive_mode | Write-Ahead Log | Settings
> archive_timeout | Write-Ahead Log | Settings
>
> this would then allow us to do this:
>
> select * from pg_settings_categories
> name order
> Developer Options 37
> Write-Ahead Log 11
>
> select * from pg_settings_subcategories
> name category
> allow_system_table_mods Developer Options
> archive_command Write-Ahead Log
> archive_mode Write-Ahead Log
>
> and then generate a file which looks like this:
> # == Write-Ahead Log ==
> # Settings
>
> archive_command = '/bin/rsync'
> archive_mode = 'on'
>
> # Fsync
>
> fsync = on
> wal_buffers = 8mb
>
> ... etc.
>
> This would allow the automatically generated version to be readable and
> searchable, if not quite as narrative as the present postgresql.conf.

Ok, now I see the point. But does this really work in a scenario when the
user edits the config file himself? The order will likely be broken pretty
quickly anyway in that case...

> > > 3) have command line config write to postgresql.auto.conf, dumping the
> > > whole of pg_settings organized with headings in categories order.
> >
> > Don't get what you mean here. You mean you want a commandline tool to
> > generate a config file from pg_settings?
>
> I meant from the SQL command line.

Oh, ok. Then I'm in agreement.

> > Another question completely, but related, is if it's actually the right
> > thing to use postgresql.conf to write documentation. The way it is now
> > we basically add all new config options to postgresql.conf.sample along
> > with a comment that is the documentation. A different approach would be
> > to only include the very most common settings, or possibly even only
> > those that initdb sets to something non-default, in
> > postgresql.conf.sample, and have the rest only added when they're
> > actually used. Documentation really belongs in the documentation, after
> > all...
>
> Yeah, we've taken an Apache-like approach of including heavy comments on
> the settings in the settings file itself. Unfortunately, I think changing
> that practice at this point would alienate a bunch of users.

AFAIK, Apache doesn't document all it's parameters there. Or maybe it does
and the distributions generaelly cut it down? ;-)

//Magnus

On Tue, Feb 19, 2008 at 11:27:47PM -0500, Robert Treat wrote:
> On Tuesday 19 February 2008 20:08, Tom Lane wrote:
> > Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > > On Tuesday 19 February 2008 15:05, Bruce Momjian wrote:
> > >> One idea would be to remove duplicate postgresql.conf appended entries
> > >> on server start.
> > >
> > > I think anything which has us appending extra settings to the end of the
> > > file is a non-starter. We'd get "I changed the setting, but nothing's
> > > happening" error reports 8x hour on #postgresql.
> >
> > Yeah, I agree. Any proposal that makes it materially harder for people
> > to maintain the config files with an editor is going to suffer so much
> > push-back that it will ultimately fail. And adding extra copies of
> > settings to an existing file does make it harder.
> >
>
> +1

That is indeed what I'd prefer, but the other way would "have less impact"
on those that prefer config files. As in they could easily get rid of it.

> > What I would suggest is to write a function in contrib/adminpack that
> > updates the config file by replacing the variable assignment in-place.
> > (Yes, it will have to be smart enough to parse the config file, but
> > that hardly requires a great deal of smarts.) If that implementation
> > sees sufficient usage then we can migrate the functionality into core.

Wel,l it would take some logic to deal with:
log_destination = 'stderr' # This is where we're sending the log

But more to deal wtih
#
# This is a long comment about why we moved to syslog because of
# blah blah blah blah lah
#
log_destination = 'syslog' # Yeah, we really did go to syslog!

Now, if you change log_destination, you really should change both the
comments as well. An easy way would be to just say "don't use combinations
of the two ways" and have the user deal with it, though.

> phppgadmin would certainly use said function if it existed in core, so I'd
> suggest if we go that route put it in 8.4 straight away. My guess is it
> would also be easier to maintain if it was built-in.

As I said before, my goal is to get rid of the adminpack and merge the
functionality into core. Let's not add new stuff there.

I've seen several cases of people choosing the windows version over the
unix one simply because the functionality provided by the adminpack is not
available on Unix by default. Sure, a little research would show it's
fairly easy to get it in there, but nevertheless it's *seen* as a
deficiency.

> > It was complained up-thread that some installations make the config
> > files read-only to the postgres user, but I see no conflict there.
> > Anyone who does that is saying that they don't *want* automatic changes
> > to the configuration settings. Such folk will not consider it a
> > feature for the database to make an end-run around that policy.
> >
>
> Hmm.... I don't think I've ever seen one like this, but thinking about it I
> suppose I could see the argument and way to do it... but yes, I think you'd
> get an error that the file was read-only, so the behavior would be similar to
> trying to edit it on the box as postgres user.

Right, I don't see any problem at all with this. The default as set by
initdb is that you can edit it. If you want to lock it down by permissions,
go right ahead, but don't expect the GUIs for configuration to work after
that. Seems very straightforward.

//Magnus

On Feb 19, 2008 10:31 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Magnus, All,
>
> This is something I've been thinking about too, just because my efforts to
> write auto-config scripts have gotten bogged down in the need to parse and
> write .conf files in a paltform-agnostic way and preserve comments. I
> agree with Magnus that it's something we need to address. Having the
> ability to update .conf through an api other than reading & writing a file
> one line will make developing future autotuning tools significanly easier.
>
> I think that the idea of just appending extra lines to the bottom of the
> file
> in chronoligical (or random) order is so messy and hackish that it's simply
> not worthy of consideration for the PostgreSQL project.

I don't like it either. I think there is a place of chronological
list of changes
made to the configuration -- it is the log file. When configuration is changed
remotely it must be logged, and an extra comment message might be nice.
>
> Instead, here's my proposal:
>
> 1) add to the top of postgresql.conf another file switch, like this:
>
> # auto_config_file = 'ConfigDir/postgresql.auto.conf'
> # if set, the auto config file will be read by the system and override the
> settings in the rest of this postgresql.conf file, which will be ignored.
> # to disable automated and SQL command-line-based configuration
> # comment the above or set it to an empty string
>
> 2) split the "category" column in pg_settings into two columns, and add a
> categories lookup table, so it can be sorted properly
>
> 3) have command line config write to postgresql.auto.conf, dumping the
> whole of pg_settings organized with headings in categories order.
>
> I think an arrangement like that will work well with pg_settings based
> config, autotuning, while still allowing backwards-compatible manual
> control via postgresql.conf.

I kind of like the idea of having two files -- one user-managed and one
database-managed. But let me first write few issues of general matter.

1) changes that cannot be done to live server:

SET PERMANENT shared_buffers = '1GB';

Now, this is a setting that cannot be changed "live", but it should be
changeable. And we need a command to query what's permanent
and what's current.

2) '1GB' -- If we are modifying postgres.conf I _think_ the format should
be preserved, so not changed into number of pages but written 'as-is'.

3) If we do have two configuration files (+1), I think PostgreSQL should
issue a BIG FAT WARNING saying that its overriding user-managed
postgres.conf wih postgres.auto, on a per-setting basis. This way nobody
would be surprised why their setting is not working.

And the rollback of all remote changes would be one unlink away.

4) Saving actual file. Sometimes it could be nice to be able set work_mem
globally (as if by postgres.conf) but not permanent (so you don't see these
settings on next start), though I am not convinced the feature is worth the
risks of people mixing up things.

5) if we have a file that is 100% PostgreSQL controlled, we could some
day use it
as an alternative to pg_hba.conf and pg_ident.conf.

Regards,
Dawid

Le mardi 19 février 2008, Gregory Stark a écrit :
> "Magnus Hagander" <magnus(at)hagander(dot)net> writes:
> > Yeah, that may actually be a very good way to implement it. I don't like
> > the idea of continously appending to an existing file, but if we did have
> > a separate file with a tightly controlled format that would be doable.
>
> +1
>
> Separating the automatically written configuration and the explicit user
> configuration is definitely the right approach. My experience comes from
> Debian where packages editing their own configuration files is verboten.
> Otherwise you run into problems reconciling user-made changes and automatic
> changes.
>
> The include file method is workable but isn't perfect. What happens if a
> user connects with pgadmin and changes a parameter but that parameter is
> overridden by a variable in the config file?
>
> The alternative is to have two files and read them both. Then if you change
> a variable which is overridden by the other source you can warn that the
> change is ineffective.

Ok, here's another idea, which only merits could well be to be different :)

What about having a postgresql.conf.d directory containing a file per setting,
maybe with a subdir per section. If I take Josh Berkus example, we'd have
either:
$PGDATA/postgresql.conf.d/allow_system_table_mods
$PGDATA/postgresql.conf.d/archive_command
$PGDATA/postgresql.conf.d/archive_mode
$PGDATA/postgresql.conf.d/archive_timeout

or:
$PGDATA/postgresql.conf.d/developer_options/allow_system_table_mods
$PGDATA/postgresql.conf.d/wal/settings/archive_command
$PGDATA/postgresql.conf.d/wal/settings/archive_mode
$PGDATA/postgresql.conf.d/wal/settings/archive_timeout
$PGDATA/postgresql.conf.d/wal/fsync/fsync
$PGDATA/postgresql.conf.d/wal/fsync/wal_buffers

Each file would then only contains the parameter value, with or without
comments in it, e.g.:
cat $PGDATA/postgresql.conf.d/log/where/log_destination
'syslog'
#
# This is a long comment about why we moved to syslog because of
# blah blah blah blah lah
#

This would solve a part of the configuration file parsing issues and I think
would ease much of the 'make it all automatic and writable by backends, and
still editable by user without too much confusion in the file(s)', but does
nothing about Magnus remarks about comments (mis-)organisation in the file.
Or we could force the files format to have the value in the first line, no
comments and values on the same line allowed, then anything on following
lines.

Of course you end up with a forest of files, and that would be a pain to
manually edit, but provided the categorization in pg_settings, it seems easy
enough to automatically transform postgresql.conf to the 'forest', so there
could be some way for the DBA to say he wont ever want resort to automatic
configuration handling. Then postgresql.conf is the edited file and reload
will have PostgreSQL generate the forest before to use it.

Hope this helps,
--
dim

On Wed, Feb 20, 2008 at 11:20:29AM +0100, Dimitri Fontaine wrote:
> Le mardi 19 février 2008, Gregory Stark a écrit :
> > "Magnus Hagander" <magnus(at)hagander(dot)net> writes:
> > > Yeah, that may actually be a very good way to implement it. I don't like
> > > the idea of continously appending to an existing file, but if we did have
> > > a separate file with a tightly controlled format that would be doable.
> >
> > +1
> >
> > Separating the automatically written configuration and the explicit user
> > configuration is definitely the right approach. My experience comes from
> > Debian where packages editing their own configuration files is verboten.
> > Otherwise you run into problems reconciling user-made changes and automatic
> > changes.
> >
> > The include file method is workable but isn't perfect. What happens if a
> > user connects with pgadmin and changes a parameter but that parameter is
> > overridden by a variable in the config file?
> >
> > The alternative is to have two files and read them both. Then if you change
> > a variable which is overridden by the other source you can warn that the
> > change is ineffective.
>
> Ok, here's another idea, which only merits could well be to be different :)
>
> What about having a postgresql.conf.d directory containing a file per setting,
> maybe with a subdir per section. If I take Josh Berkus example, we'd have

<snip>
IMHO, if we do that it really sucks for those who use manual configuration
files, to the point of being completely unusable. It could be valid if we
want to support config only through the API, but that's not what people are
asking for.

We need something that's low-impact for existing users, and this certainly
isn't.

//Magnus

Dawid Kuroczko escribió:

> 1) changes that cannot be done to live server:
>
> SET PERMANENT shared_buffers = '1GB';
>
> Now, this is a setting that cannot be changed "live", but it should be
> changeable. And we need a command to query what's permanent
> and what's current.

IMO restart-only settings should not be changeable via the new SQL
command. It's just too messy to deal with that.

Also, this SQL command should reject being used in a transaction block
(BEGIN/COMMIT), because rolling it back seems fragile.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Wed, Feb 20, 2008 at 09:36:43AM -0300, Alvaro Herrera wrote:
> Dawid Kuroczko escribió:
>
> > 1) changes that cannot be done to live server:
> >
> > SET PERMANENT shared_buffers = '1GB';
> >
> > Now, this is a setting that cannot be changed "live", but it should be
> > changeable. And we need a command to query what's permanent
> > and what's current.
>
> IMO restart-only settings should not be changeable via the new SQL
> command. It's just too messy to deal with that.

I respectfully disagree. It should be settable. You need a restart, sure,
and the GUI app should tell you that. But you shuld be able to change them.
(for example, pgadmin can restart the server just fine for you if you're on
Windows)

> Also, this SQL command should reject being used in a transaction block
> (BEGIN/COMMIT), because rolling it back seems fragile.

That I can agree with.

//Magnus

Magnus Hagander escribió:
> On Wed, Feb 20, 2008 at 09:36:43AM -0300, Alvaro Herrera wrote:

> > IMO restart-only settings should not be changeable via the new SQL
> > command. It's just too messy to deal with that.
>
> I respectfully disagree. It should be settable. You need a restart, sure,
> and the GUI app should tell you that. But you shuld be able to change them.
> (for example, pgadmin can restart the server just fine for you if you're on
> Windows)

What if the server doesn't come back up? Say, because you increased
shared_buffers and now it doesn't fit on the kernel limits. If you
haven't made arrangements to be able to edit the postgresql.conf file
beforehand, you're hosed.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, Feb 20, 2008 at 10:20:55AM -0300, Alvaro Herrera wrote:
> Magnus Hagander escribió:
> > On Wed, Feb 20, 2008 at 09:36:43AM -0300, Alvaro Herrera wrote:
>
> > > IMO restart-only settings should not be changeable via the new SQL
> > > command. It's just too messy to deal with that.
> >
> > I respectfully disagree. It should be settable. You need a restart, sure,
> > and the GUI app should tell you that. But you shuld be able to change them.
> > (for example, pgadmin can restart the server just fine for you if you're on
> > Windows)
>
> What if the server doesn't come back up? Say, because you increased
> shared_buffers and now it doesn't fit on the kernel limits. If you
> haven't made arrangements to be able to edit the postgresql.conf file
> beforehand, you're hosed.

Right. So a warning in the GUI program is important, but that's no reason
to restrict the API.

Doing DROP TABLE can also be very dangerous. Or DROP TYPE CASCADE. Yet we
do support them, and rely on the user to think first, or the GUI pogram to
show warnings.

//Magnus

Le mercredi 20 février 2008, Magnus Hagander a écrit :
> > What about having a postgresql.conf.d directory containing a file per
> > setting, maybe with a subdir per section. If I take Josh Berkus example,
> > we'd have
>
> <snip>
> IMHO, if we do that it really sucks for those who use manual configuration
> files, to the point of being completely unusable. It could be valid if we
> want to support config only through the API, but that's not what people are
> asking for.
>
> We need something that's low-impact for existing users, and this certainly
> isn't.

What about having PG still able to load postgresql.conf or the tree of config
files, automatically, erroring when both mechanisms are in use at the same
time. This would allow for manual config editing installations and SQL
embedded configuration setting, just not in the same cluster at the same
time.

I see how the proposal fails to answer to people wanting to edit the same
configuration both with a file editor and SQL commands, but maybe having
either postgresql.conf or SQL interface for configuration could be a first
step?

--
dim

Dimitri Fontaine wrote:
> Le mercredi 20 février 2008, Magnus Hagander a écrit :
>
>>> What about having a postgresql.conf.d directory containing a file per
>>> setting, maybe with a subdir per section. If I take Josh Berkus example,
>>> we'd have
>>>
>> <snip>
>> IMHO, if we do that it really sucks for those who use manual configuration
>> files, to the point of being completely unusable. It could be valid if we
>> want to support config only through the API, but that's not what people are
>> asking for.
>>
>> We need something that's low-impact for existing users, and this certainly
>> isn't.
>>
>
> What about having PG still able to load postgresql.conf or the tree of config
> files, automatically, erroring when both mechanisms are in use at the same
> time. This would allow for manual config editing installations and SQL
> embedded configuration setting, just not in the same cluster at the same
> time.
>
> I see how the proposal fails to answer to people wanting to edit the same
> configuration both with a file editor and SQL commands, but maybe having
> either postgresql.conf or SQL interface for configuration could be a first
> step?
>
>

No. Seriously. We need to have reasonable manual editability preserved
for all cases. The tree of files proposal just strikes me as a basic
non-starter, and, frankly, a piece of bad design. If you need structure,
then using the file system to provider it is just a bad move.

All this discussion seems to me to be going off into the clouds, where
every objection is met with some still more elaborate scheme. I think we
need to look at simple, incremental, and if possible backwards
compatible changes.

cheers

andrew

Le mercredi 20 février 2008, Andrew Dunstan a écrit :
> No. Seriously. We need to have reasonable manual editability preserved
> for all cases. The tree of files proposal just strikes me as a basic
> non-starter, and, frankly, a piece of bad design. If you need structure,
> then using the file system to provider it is just a bad move.

Ok. Just wanted to have the idea exposed, nothing more.
The aim was not to structure the file (that was just a bonus), but to be able
to very easily edit the settings from C-code... comments included.

> All this discussion seems to me to be going off into the clouds, where
> every objection is met with some still more elaborate scheme. I think we
> need to look at simple, incremental, and if possible backwards
> compatible changes.

ISTM backward compatible could mean including automatic migration code, where
PostgreSQL 8.4 (e.g.) would convert old postgresql.conf to new format all by
itself, with either a add-on command line tool or at first start maybe...
--
dim

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> All this discussion seems to me to be going off into the clouds, where
> every objection is met with some still more elaborate scheme. I think we
> need to look at simple, incremental, and if possible backwards
> compatible changes.

+1. Let me propose the simplest possible scheme, namely

The SQL-exposed function knows how to find and replace the definition
of a variable (commented or otherwise) in the primary configuration
file. It does not chase INCLUDEs. If it doesn't find the target
variable anyplace in the primary file, it errors out.

What this would mean is that for people using configuration include
files (which is surely a tiny minority), anything tucked away in an
include file is not editable from a SQL session. This gives them the
flexibility to decide which things are editable and which aren't, and by
removing items from the base config file and/or rearranging the ordering
of includes, they can control which things can be overridden from SQL.

In particular, this works conveniently for the case where the base
config file is in $PGDATA and is postgres-writable, whereas the include
file(s) are shared across database clusters and are not writable.
I think that's the most obvious use-case for having an include file.

regards, tom lane

Tom,

> The SQL-exposed function knows how to find and replace the definition
> of a variable (commented or otherwise) in the primary configuration
> file. It does not chase INCLUDEs. If it doesn't find the target
> variable anyplace in the primary file, it errors out.

Hmmm. I guess I'm just not good enough with conf file parsing.

The problem I've constantly run into with parsing and modifying settings
in a user-edited postgresql.conf file is that sometimes users do their
own chronological documentation:

#work_mem = 1mb #original setting
#work_mem = 4mb #2008-01-05 not high enough
#work_mem = 32mb #2008-01-11 too high, OOM killer
work_mem = 16mb #current setting

If the user then chooses to use the API to update work_mem to 12mb, how
do we process the file? Does it become this?

work_mem = 12mb
work_mem = 12mb
work_mem = 12mb
work_mem = 12mb

The above wouldn't seem such a problem, except that sometimes those
individual setting lines could be widely separated in the file,
depending the application's history of DBAs.

Further, sometimes comments can look like this:

# warning! never, ever, ever, set
# work_mem to be more than 16mb, it will
# cause OOM!

Which then gets transformed to:

# warning! never, ever, ever, set
work_mem = 12mb
# cause OOM!

Obviously, these individual cases can be worked around, but as long as
we're trying to preserve our historical human-readable-and-documented
.conf format *and* allow DBAs to hand-edit and machine-edit the same
file, I think we're going to end up writing more "corner case" code than
core implementation. I think an include approach would be a lot cleaner
and less prone to issues.

--Josh

Magnus, all:

Other thoughts:

1) fix category display for pg_settings. 'nuff said.

2) allow *commenting* of pg_settings / SET PERMANENT. Thus:

SET PERMANENT work_mem = '12mb' COMMENT '16mb too high; OOM';

SET

SELECT name, comment FROM pg_settings WHERE name = 'work_mem';

work_mem | 16mb too high; OOM

3) We should also discuss potentially having "changed_on", "changedby" data:

SELECT name, changedon, changedat, changedby FROM pg_settings;

work_mem | 2008-01-22 14:35:11 | postgres
shared_buffers | 2008-01-20 13:11:11 | postgresql.conf file

(for "postgresql.conf file" the changedon would always be the date of
the last reboot)

4) We'll need a log setting for "log SET", since I can see people
wanting to log this kind of activity without necessarily logging all
statements.

--Josh Berkus

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> The problem I've constantly run into with parsing and modifying settings
> in a user-edited postgresql.conf file is that sometimes users do their
> own chronological documentation:
> [snip]

Yeah, those are good examples. It would be fairly easy to deal with a
postgresql.conf file that's in a pristine state, but I can see that
distinguishing "commented-out values" from actual comments is likely
to be AI-complete :-(

> Obviously, these individual cases can be worked around, but as long as
> we're trying to preserve our historical human-readable-and-documented
> .conf format *and* allow DBAs to hand-edit and machine-edit the same
> file, I think we're going to end up writing more "corner case" code than
> core implementation. I think an include approach would be a lot cleaner
> and less prone to issues.

I'm starting to wonder why any of this proposal is a good idea at all.
We already have sufficient support for someone to suck out the
postgresql.conf file, edit it remotely, and put it back, so the argument
that this will enable remote administration that you can't do now is
entirely bogus. I don't see what it will buy us that is worth the
problems it will create.

For the point-and-drool crowd that can't cope with editing a text file,
perhaps the best avenue to having a GUI is to build it atop the
just-mentioned facility, namely

1. suck out the current settings.
2. provide a GUI that manipulates the values.
3. write back an entirely new postgresql.conf that doesn't take any
trouble to preserve what was there before.

regards, tom lane

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> 2) allow *commenting* of pg_settings / SET PERMANENT. Thus:

> SET PERMANENT work_mem = '12mb' COMMENT '16mb too high; OOM';

Ugh :-(

I think that putting this into SET is a pretty bad idea in any case.
SET is, and always has been, a session-local operation. Providing a
secondary option that transforms it into something completely different
doesn't seem to me to be good design. If we do anything along this line
it should be some other syntax --- and really a specialized function
will serve the purpose just fine.

The other stuff you suggest is even more lily-gilding, not to mention
completely pointless unless we were to make this function the *only* way
that the settings could be changed.

regards, tom lane

On Wed, Feb 20, 2008 at 01:27:25PM -0500, Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > The problem I've constantly run into with parsing and modifying settings
> > in a user-edited postgresql.conf file is that sometimes users do their
> > own chronological documentation:
> > [snip]
>
> Yeah, those are good examples. It would be fairly easy to deal with a
> postgresql.conf file that's in a pristine state, but I can see that
> distinguishing "commented-out values" from actual comments is likely
> to be AI-complete :-(

Right, this is one of the reasons for the suggestion to use a separate
include file where the user isn't supposed to be editing it manually at
all.

> > Obviously, these individual cases can be worked around, but as long as
> > we're trying to preserve our historical human-readable-and-documented
> > .conf format *and* allow DBAs to hand-edit and machine-edit the same
> > file, I think we're going to end up writing more "corner case" code than
> > core implementation. I think an include approach would be a lot cleaner
> > and less prone to issues.
>
> I'm starting to wonder why any of this proposal is a good idea at all.
> We already have sufficient support for someone to suck out the
> postgresql.conf file, edit it remotely, and put it back, so the argument
> that this will enable remote administration that you can't do now is
> entirely bogus. I don't see what it will buy us that is worth the
> problems it will create.
>
> For the point-and-drool crowd that can't cope with editing a text file,
> perhaps the best avenue to having a GUI is to build it atop the
> just-mentioned facility, namely
>
> 1. suck out the current settings.
> 2. provide a GUI that manipulates the values.
> 3. write back an entirely new postgresql.conf that doesn't take any
> trouble to preserve what was there before.

That's what we have now, and it basically forces each frontend to do the
implementatino themselevs. E.g. pgadmin has one implementation, phppgadmin
has another implementation, apparantly Greg has one implementation, there
may be third party ones out there with their own implementation.

The point is we need one implementatino that's in the server, because that
takes away redundancy and it makes it easier to maintain.

//Magnus

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Wed, Feb 20, 2008 at 01:27:25PM -0500, Tom Lane wrote:
>> For the point-and-drool crowd that can't cope with editing a text file,
>> perhaps the best avenue to having a GUI is to build it atop the
>> just-mentioned facility, namely
>>
>> 1. suck out the current settings.
>> 2. provide a GUI that manipulates the values.
>> 3. write back an entirely new postgresql.conf that doesn't take any
>> trouble to preserve what was there before.

> That's what we have now, and it basically forces each frontend to do the
> implementatino themselevs. E.g. pgadmin has one implementation, phppgadmin
> has another implementation, apparantly Greg has one implementation, there
> may be third party ones out there with their own implementation.

> The point is we need one implementatino that's in the server, because that
> takes away redundancy and it makes it easier to maintain.

The main part of that is the GUI, which is certainly not going to be in
the server, so I fail to see exactly what you think you're really
gaining.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 20 Feb 2008 13:27:25 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> For the point-and-drool crowd that can't cope with editing a text

*ahem*

I am far form a point and drool person and I am telling you:

SET PERMANENTLY work_mem TO 65MB ;

Is a heck of a lot more sane than editing a text file.

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHvHUcATb/zqfZUUQRAmbYAJ9oZ6BZrAHNVdzk/Jf8feiB5NDdBQCdEXWs
iJqE5FqoQuZ5NJdVpTT6a94=
=pnaR
-----END PGP SIGNATURE-----

* Joshua D. Drake <jd(at)commandprompt(dot)com> [080220 13:43]:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Wed, 20 Feb 2008 13:27:25 -0500
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > For the point-and-drool crowd that can't cope with editing a text
>
> *ahem*
>
> I am far form a point and drool person and I am telling you:
>
> SET PERMANENTLY work_mem TO 65MB ;
>
> Is a heck of a lot more sane than editing a text file.

I think the first step is really for some people to show code that
"rewrites" the config file changing a setting reliably and correctly.

Once we have people comfortable with it rewriting the file, the
bikeshedding can start as to how to "use" it through the SQL interface.

But, until there's code out there...
<bikeshedding>
But as Tom said, that's *really* changing what SET has tradionally
been.

Why is a function something like this not sufficient:
pg_save_setting('work_mem', '65MB', 'comment so I remeber')
or,
pg_save_setting('work_mem', '65MB')
or even
pg_save_setting('work_mem')

Since it's a function:
1) It's "implementable" by anybody, in any fashion
2) It's implemtation is easily replacable by anyone, in any fashion
3) It's easily backportable to adminpack/8.3/8.2/8.1 for those who want
it
</bikeshedding>

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Wed, Feb 20, 2008 at 01:43:46PM -0500, Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > On Wed, Feb 20, 2008 at 01:27:25PM -0500, Tom Lane wrote:
> >> For the point-and-drool crowd that can't cope with editing a text file,
> >> perhaps the best avenue to having a GUI is to build it atop the
> >> just-mentioned facility, namely
> >>
> >> 1. suck out the current settings.
> >> 2. provide a GUI that manipulates the values.
> >> 3. write back an entirely new postgresql.conf that doesn't take any
> >> trouble to preserve what was there before.
>
> > That's what we have now, and it basically forces each frontend to do the
> > implementatino themselevs. E.g. pgadmin has one implementation, phppgadmin
> > has another implementation, apparantly Greg has one implementation, there
> > may be third party ones out there with their own implementation.
>
> > The point is we need one implementatino that's in the server, because that
> > takes away redundancy and it makes it easier to maintain.
>
> The main part of that is the GUI, which is certainly not going to be in
> the server, so I fail to see exactly what you think you're really
> gaining.

The way things are now, writing the GUI is *simple* compared to the fact
that you have to write a config file parser. One for each tool.

The gain is exactly what I said above: we only need one implementation, not
one for each potential tool using it, and the maintenance is easier should
we ever decide to change how the config files are handled.

//Magnus

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 20 Feb 2008 13:55:05 -0500
Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:

> Once we have people comfortable with it rewriting the file, the
> bikeshedding can start as to how to "use" it through the SQL
> interface.
>
> But, until there's code out there...
> <bikeshedding>
> But as Tom said, that's *really* changing what SET has
> tradionally been.
>
> Why is a function something like this not sufficient:
> pg_save_setting('work_mem', '65MB', 'comment so I
> remeber') or,
> pg_save_setting('work_mem', '65MB')
> or even
> pg_save_setting('work_mem')
>
> Since it's a function:
> 1) It's "implementable" by anybody, in any fashion
> 2) It's implemtation is easily replacable by anyone, in any
> fashion 3) It's easily backportable to adminpack/8.3/8.2/8.1 for
> those who want it
> </bikeshedding>

I am not opposed to the above. I was trying to make a point about the
ignorance of the point and drool crowd statement.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHvHkKATb/zqfZUUQRAo2TAJsEGcTKCSaM7klq4KFYtuFT035nAwCfeAIr
KfaC5FrE3jSZ0V10eX8LOME=
=BUPQ
-----END PGP SIGNATURE-----

On Wed, Feb 20, 2008 at 01:55:05PM -0500, Aidan Van Dyk wrote:
> * Joshua D. Drake <jd(at)commandprompt(dot)com> [080220 13:43]:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > On Wed, 20 Feb 2008 13:27:25 -0500
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > > For the point-and-drool crowd that can't cope with editing a text
> >
> > *ahem*
> >
> > I am far form a point and drool person and I am telling you:
> >
> > SET PERMANENTLY work_mem TO 65MB ;
> >
> > Is a heck of a lot more sane than editing a text file.
>
> I think the first step is really for some people to show code that
> "rewrites" the config file changing a setting reliably and correctly.

But what we're donig now is discussing *how to do that*, no?

> Once we have people comfortable with it rewriting the file, the
> bikeshedding can start as to how to "use" it through the SQL interface.
>
> But, until there's code out there...
> <bikeshedding>
> But as Tom said, that's *really* changing what SET has tradionally
> been.
>
> Why is a function something like this not sufficient:
> pg_save_setting('work_mem', '65MB', 'comment so I remeber')
> or,
> pg_save_setting('work_mem', '65MB')
> or even
> pg_save_setting('work_mem')
>
> Since it's a function:
> 1) It's "implementable" by anybody, in any fashion
> 2) It's implemtation is easily replacable by anyone, in any fashion
> 3) It's easily backportable to adminpack/8.3/8.2/8.1 for those who want
> it
> </bikeshedding>

I for one am perfectly fine with a function instead of a parameter to SET.
Because it's less invasive, and because of your argumen 3 above.

//Magnus

* Magnus Hagander <magnus(at)hagander(dot)net> [080220 14:03]:

> > I think the first step is really for some people to show code that
> > "rewrites" the config file changing a setting reliably and correctly.
>
> But what we're donig now is discussing *how to do that*, no?

Sort of, but of course, we're getting caught up in extra syntactic
stuff..

If someone *is* writing this config-rewriter now, these are the types of
quesitons I think they need to be asking, some of which have been
touched on, some not. But I think a first cut could pick any answer for
them, and still be easily adaptable...

1) What file to we "rewrite"? Main one, or some other specified one?
2) Do we follow includes to find our setting?
3) Which setting do we change, the 1st, or last found in the config
file?
4) What do we do about comments *on the same line* as the setting we're
changing (I'm assuming all other lines won't be touched)
5) How do we want to handle errors like "ENOSPC", or EPERM (I'm assuming
of course that the file rewrite will be a tmp+rename, not a trunc+write)
6) Do we want to distinguish between "restart only" settings, and
reloadable settings, and if so, how?

a.
--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Wed, Feb 20, 2008 at 01:43:46PM -0500, Tom Lane wrote:
>> The main part of that is the GUI, which is certainly not going to be in
>> the server, so I fail to see exactly what you think you're really
>> gaining.

> The way things are now, writing the GUI is *simple* compared to the fact
> that you have to write a config file parser. One for each tool.

No you don't. All you need is the output of the pg_settings view.
Or at least, if that's insufficient, let's discuss exactly how.

regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> On Wed, Feb 20, 2008 at 01:43:46PM -0500, Tom Lane wrote:
>>> The main part of that is the GUI, which is certainly not going to be in
>>> the server, so I fail to see exactly what you think you're really
>>> gaining.
>
>> The way things are now, writing the GUI is *simple* compared to the fact
>> that you have to write a config file parser. One for each tool.
>
> No you don't. All you need is the output of the pg_settings view.
> Or at least, if that's insufficient, let's discuss exactly how.

I can read the settings. How do I write them, if the only interface to
write them is to deal with the file as a complete unit?

I was certainly planning to use the output of the pg_settings view to
read the data. pgadmin today uses the config file, which is one reason
it sucks :-) (because it basically presents the entire config file as a
remote text-file editor to the user, and that's not what the user wants)

//Magnus

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> Tom Lane wrote:
>> No you don't. All you need is the output of the pg_settings view.
>> Or at least, if that's insufficient, let's discuss exactly how.

> I can read the settings. How do I write them, if the only interface to
> write them is to deal with the file as a complete unit?

You write the file as a unit --- what's the problem? We already agreed
that the GUIs would not be trying to preserve comments in the file.

regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Tom Lane wrote:
>>> No you don't. All you need is the output of the pg_settings view.
>>> Or at least, if that's insufficient, let's discuss exactly how.
>
>> I can read the settings. How do I write them, if the only interface to
>> write them is to deal with the file as a complete unit?
>
> You write the file as a unit --- what's the problem? We already agreed
> that the GUIs would not be trying to preserve comments in the file.

Well, I have to parse the file, and figure out where to have the
setting. And if there are multiple configuration files, I have to parse
multiple configuration files.

And phppgadmin has to implement the exact same parser. As will <insert
third party app here>.

And people like JD who want such a feature *even though they may not be
using the GUI* are left with nothing. (no, he's not the only one)

The point was exactly to move that parsing to the backend.

If we're fine with GUIs messing up the comments, then we can just have
those functions in the backend and be fine with them messing up the
comments.

//Magnus

On Wed, Feb 20, 2008 at 7:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>
> > 2) allow *commenting* of pg_settings / SET PERMANENT. Thus:
>
> > SET PERMANENT work_mem = '12mb' COMMENT '16mb too high; OOM';
>
> Ugh :-(
>
> I think that putting this into SET is a pretty bad idea in any case.
> SET is, and always has been, a session-local operation. Providing a
> secondary option that transforms it into something completely different

I think that's valid argument.

We already have ALTER USER foo SET bar = baz, so why not something like:

ALTER CLUSTER SET shared_buffers TO '2GB';

...perhaps with some other word than CLUSTER?

Regards,
Dawid

All,

I think we're failing to discuss the primary use-case for this, which
is one reason why the solutions aren't obvious.

And that use case is: multi-server management.

PostgreSQL is *easy* to manage on one server. For a single server, the
existing text file editor GUIs are clunky but good enough.

However, imagine you're adminning 250 PostgreSQL servers backing a
social networking application. You decide the application needs a
higher default sort_mem for all new connections, on all 250 servers.
How, exactly, do you deploy that?

Worse, imagine you're an ISP and you have 250 *differently configured*
PostgreSQL servers on vhosts, and you need to roll out a change in
logging destination to all machines while leaving other settings
untouched.

We need a server-based tool for the manipulating postgresql.conf, and
one which is network-accessable, allows updating individual settings,
and can be plugged into 3rd-party server management tools. This goes
for pg_hba.conf as well, for the same reasons.

If we want to move PostgreSQL into larger enterprises (and I certainly
do) we need to make it more manageable.

Now, none of this requires managing the settings via the SQL command
line. Since we need to make it network-accessable, though, that seems
the easiest route. Otherwise, we'd have to set up a daemon running on
a 2nd port.

P.S. I don't care what the syntax is.

Josh Berkus
PostgreSQL @ Sun
San Francisco 415-752-2500

* Josh Berkus <josh(at)agliodbs(dot)com> [080220 18:00]:
> All,
>
> I think we're failing to discuss the primary use-case for this, which
> is one reason why the solutions aren't obvious.

> However, imagine you're adminning 250 PostgreSQL servers backing a
> social networking application. You decide the application needs a
> higher default sort_mem for all new connections, on all 250 servers.
> How, exactly, do you deploy that?
>
> Worse, imagine you're an ISP and you have 250 *differently configured*
> PostgreSQL servers on vhosts, and you need to roll out a change in
> logging destination to all machines while leaving other settings
> untouched.

But, from my experience, those are "pretty much" solved, with things
like rsync, SCM (pick your favourite) and tools like "clusterssh,
multixterm", rancid, wish, expect, etc.

I would have thought that any "larger enterprise" was familiar with
these approaches, and are probably using them already to
manage/configure there general unix environments

> We need a server-based tool for the manipulating postgresql.conf, and
> one which is network-accessable, allows updating individual settings,
> and can be plugged into 3rd-party server management tools. This goes
> for pg_hba.conf as well, for the same reasons.
>
> If we want to move PostgreSQL into larger enterprises (and I certainly
> do) we need to make it more manageable.

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?

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Aidan Van Dyk wrote:
-- Start of PGP signed section.
> * Josh Berkus <josh(at)agliodbs(dot)com> [080220 18:00]:
> > All,
> >
> > I think we're failing to discuss the primary use-case for this, which
> > is one reason why the solutions aren't obvious.
>
> > However, imagine you're adminning 250 PostgreSQL servers backing a
> > social networking application. You decide the application needs a
> > higher default sort_mem for all new connections, on all 250 servers.
> > How, exactly, do you deploy that?
> >
> > Worse, imagine you're an ISP and you have 250 *differently configured*
> > PostgreSQL servers on vhosts, and you need to roll out a change in
> > logging destination to all machines while leaving other settings
> > untouched.
>
> But, from my experience, those are "pretty much" solved, with things
> like rsync, SCM (pick your favourite) and tools like "clusterssh,
> multixterm", rancid, wish, expect, etc.

Agreed. Put postgresql.conf on an NFS server and restart the servers.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 20 Feb 2008 18:38:10 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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?

I believe the more correct argument would be to look at how our
competition is doing this, and perhaps learn from them. How does
Oracle, MSSQL, and DB2 handle this? Yes I purposely left out the
dolphin tamers.

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHvLzsATb/zqfZUUQRAr0WAJ4gkYww0pBzC7ZzwdZZI0E6oLEaqgCfc1gm
MOpFjuKHJ9sX20rJLfrXNOQ=
=hjk0
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 20 Feb 2008 18:38:09 -0500 (EST)
Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> Agreed. Put postgresql.conf on an NFS server and restart the servers.
>
Bruce, that is insane.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHvL0AATb/zqfZUUQRAvlIAKCXvceixsK18qN1xNDEzMuvFjVjggCfXZMd
R7mCktmpp/RcNg4XPxOaPi4=
=zVm4
-----END PGP SIGNATURE-----

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> All this discussion seems to me to be going off into the clouds, where
>> every objection is met with some still more elaborate scheme. I think we
>> need to look at simple, incremental, and if possible backwards
>> compatible changes.
>
> +1. Let me propose the simplest possible scheme, namely
>
> The SQL-exposed function knows how to find and replace the definition
> of a variable (commented or otherwise) in the primary configuration
> file. It does not chase INCLUDEs. If it doesn't find the target
> variable anyplace in the primary file, it errors out.

I think there are a few problems with having the function edit the primary
config file:

1) It requires parsing and dealing with arbitrary user data. There could be
comments on the same line, the order or white-space might be important to the
user, etc.

2) How would this interact with config files outside of the data directory? If
you have multiple postgres clusters using the same config fie or if your
config file is in read-only media (as /etc often is) or if you're a packager
where editing user-maintained /etc files is a forbidden and an awful idea this
all leads to problems.

I think it's much cleaner to have a postgresql.conf.auto file in the data
directory which has a limited syntax. No comments, no extra white-space, and
no includes. The user is not expected to edit it, though he can. The functions
edit it using simple algorithms which add and remove single lines.

The default config file then includes this postgresql.conf.auto and the
sysadmin can decide whether to keep or remove that include, change
configuration options before or after the include, etc.

Actually this is very similar to how a lot of other packages manage their
automatically maintained data. Apache used to be done like this on Debian (now
it's a bit more complex using a directory, but the same idea). Emacs's
custom.el package can be set up in a similar way where custom.el edits a
separate file which you include from your .emacs.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

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

On Thu, Feb 21, 2008 at 12:02 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> All,
>
> I think we're failing to discuss the primary use-case for this, which
> is one reason why the solutions aren't obvious.
>
> And that use case is: multi-server management.

...and third-party management solutions.

> PostgreSQL is *easy* to manage on one server. For a single server, the
> existing text file editor GUIs are clunky but good enough.
[...]

I tried to ask myself -- what other similar systems do I know and what
do they give me.

Well, I know Oracle does have a concept of database managed
configuration (a SPFILE),
and it is preferred set up. If you are using SPFILE, you can issue:

ALTER SYSTEM SET foo = 'bar' [ COMMENT = 'comment' ] SCOPE=SPFILE (or
MEMORY or BOTH).

...SPFILE means changes take place upon next restart, MEMORY -- they
are temporary (though
global for the system).

At any moment you can switch from one form to the other (CREATE PFILE
FROM SPFILE)
or vice versa. The idea is that human can edit PFILE, and that SPFILE
is database-only
(and database can store some extra hints there, if it wishes).

OK, so what does it give Oracle? The management solutions use it a
lot. You can easily change
parameters from them. Combined with monitoring this gives "full
service" solutions, say a PostgreSQL
could diisplay a bgwriter statistics, suggest changes to the current
settings, and a "one click away"
solution to try them out.

Would I like PostgreSQL to have such an option? Yes, having used it
on Oracle, I think
such an ability is nothing but beneficial (if done right).

Regards,
Dawid

PS: And I think postgres.conf as it is today is one of the nicest
application-provided configuration files. :)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 20 Feb 2008 09:42:02 -0500
Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

> All this discussion seems to me to be going off into the clouds,
> where every objection is met with some still more elaborate scheme. I
> think we need to look at simple, incremental, and if possible
> backwards compatible changes.

The simplest solution I can think of is:

Have a table pg_configuration (pg_settings?).

Allow that table to be inserted into but not updated or deleted from.

Provide functions to manipulate the table perhaps:

select update_settings('shared_memory','64M');

That table is used as the definitive source for "building" the
postgresql.conf.

The postgresql.conf is pushed to disk each time the system is reloaded
via:

refresh_settings();

Refresh_settings would be called as an initial startup function as
well. So if you did:

pg_ctl -D data start

It would actually do:

pg_ctl -D data start; select update_settings(); pg_ctl -D data
restart;

The reason we only insert is that the function refresh_settings() calls
the max(created) for the setting. That way we can know what previous
settings for the GUC.

Other things could be added such as:

select update_settings('shared_memory','64M','Used to be 16 but we
got more ram');

The one thing this does is make the postgresql.conf basically a
placeholder. It is not definitive anymore, in the sense that settings
will be overwritten on restart. That really isn't that uncommon anyway
in other applications.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHvN6FATb/zqfZUUQRAqlKAJ0ZHMGSfOBBUVqFGDtsNAw9b04JUgCgiRa4
T4e2P3+NqtVtiFpwPYArdBA=
=Zto3
-----END PGP SIGNATURE-----

Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>
>> The problem I've constantly run into with parsing and modifying settings
>> in a user-edited postgresql.conf file is that sometimes users do their
>> own chronological documentation:
>> [snip]
>>
>
> Yeah, those are good examples. It would be fairly easy to deal with a
> postgresql.conf file that's in a pristine state, but I can see that
> distinguishing "commented-out values" from actual comments is likely
> to be AI-complete :-(
>
>

How about if we provide for a magic value of 'default' for every
setting? So the model config file would move from lines like this:

#port = 5432 # (change requires restart)

to lines like this:

port = default # 5432 (change requires restart)

So we'd never uncomment a commented out line.

That way we could preserve comments, which would be a Good Thing (tm)

Then I think Tom's original proposal suitably modified would make sense.

cheers

andrew

* Joshua D. Drake <jd(at)commandprompt(dot)com> [080220 21:15]:

> The one thing this does is make the postgresql.conf basically a
> placeholder. It is not definitive anymore, in the sense that settings
> will be overwritten on restart. That really isn't that uncommon anyway
> in other applications.

Man, I'ld screem *bloody murder* if the config file we just finished,
after spending days (or weeks) of careful analisys and implementation
discussion was "overwritten" by postmaster "automatically" on server
startup...

Of course, I'm not quite that dumb - the config file would be checked
out of SCM, so it wouldn't be lost, but I certainly wouldn't be happy to
have to puzzle why the config file I *just wrote* seems not to be
affecting things the way I intended, only to find that the database
"overwrote" it with the old settings it had been using (that were
obviously the reason we needed to change the config)...

But part of that might just be user education... I personally just
can't imagine that education could be enough to let *all* users know
that as of version S, postgresql.conf is blatantly ignored, no, more
exactly *purposely overwritten* with the "old" settings...

If postgresql.conf is *ever* going to be deprecated as a "config file"
that in *controls* PostgreSQL, then absolutely do *not* leave it around,
and screem loudly if postmaster notices that it exists...

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 20 Feb 2008 23:02:34 -0500
Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:

> * Joshua D. Drake <jd(at)commandprompt(dot)com> [080220 21:15]:
>
> > The one thing this does is make the postgresql.conf basically a
> > placeholder. It is not definitive anymore, in the sense that
> > settings will be overwritten on restart. That really isn't that
> > uncommon anyway in other applications.
>
> Man, I'ld screem *bloody murder* if the config file we just finished,
> after spending days (or weeks) of careful analisys and implementation
> discussion was "overwritten" by postmaster "automatically" on server
> startup...

And I of course would respond, read the docs :P

>
> But part of that might just be user education... I personally just
> can't imagine that education could be enough to let *all* users know
> that as of version S, postgresql.conf is blatantly ignored, no, more
> exactly *purposely overwritten* with the "old" settings...

We could also make it optional.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHvPkJATb/zqfZUUQRAqNlAJ972s1p0RvfWabRXOQKkzJvACkEYQCfXrsc
IZ18stRvr6NONj0T3wUBpXE=
=nWtF
-----END PGP SIGNATURE-----

On 21/02/2008, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> On Wed, 20 Feb 2008 23:02:34 -0500
> Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
>
> > * Joshua D. Drake <jd(at)commandprompt(dot)com> [080220 21:15]:
> >
> > > The one thing this does is make the postgresql.conf basically a
> > > placeholder. It is not definitive anymore, in the sense that
> > > settings will be overwritten on restart. That really isn't that
> > > uncommon anyway in other applications.
> >
> > Man, I'ld screem *bloody murder* if the config file we just finished,
> > after spending days (or weeks) of careful analisys and implementation
> > discussion was "overwritten" by postmaster "automatically" on server
> > startup...
>
>
> And I of course would respond, read the docs :P
>
>
> >
> > But part of that might just be user education... I personally just
> > can't imagine that education could be enough to let *all* users know
> > that as of version S, postgresql.conf is blatantly ignored, no, more
> > exactly *purposely overwritten* with the "old" settings...
>
>
> We could also make it optional.
>
>
Silly point postgresql.conf has a bunch of settings that are needed by the
server before it can actually read the database, Sure move out settings that
are not needed early in startup but your going to get problems with others.

I quite like the function based method its flexible. Allowing pg_settings to
be update able does not seam to be a bad idea but then you could do that
with triggers and rules that called the functions surly?

set should be for temporary transaction and session based variables, not for
change permanent things thats what the SQL constructs insert, update, alter,
create, delete, drop etc are for.

Regards

Peter

On Wed, Feb 20, 2008 at 06:38:09PM -0500, Bruce Momjian wrote:
> Aidan Van Dyk wrote:
> > * Josh Berkus <josh(at)agliodbs(dot)com> [080220 18:00]:
> > > All,
> > >
> > > I think we're failing to discuss the primary use-case for this, which
> > > is one reason why the solutions aren't obvious.
> >
> > > However, imagine you're adminning 250 PostgreSQL servers backing a
> > > social networking application. You decide the application needs a
> > > higher default sort_mem for all new connections, on all 250 servers.
> > > How, exactly, do you deploy that?
> > >
> > > Worse, imagine you're an ISP and you have 250 *differently configured*
> > > PostgreSQL servers on vhosts, and you need to roll out a change in
> > > logging destination to all machines while leaving other settings
> > > untouched.
> >
> > But, from my experience, those are "pretty much" solved, with things
> > like rsync, SCM (pick your favourite) and tools like "clusterssh,
> > multixterm", rancid, wish, expect, etc.
>
> Agreed. Put postgresql.conf on an NFS server and restart the servers.

You've never actually administered machines in this scenario in production,
have you?

NFS mounting things thruogh firewalls will have a *really* hard time
getting past any firewall config person worthy of his name, for example.
And there are countless of other scenarios where it can't be done.

//Magnus

On Wed, Feb 20, 2008 at 06:38:10PM -0500, 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?

In my experience, most that *can*, do that. Pretty much every new system
I've installed in the past 5+ years store their entire configuration *in
the database*. The only thing that goes in the config file is the bare
minimum to reach the database.

If they don't have an actual database, it's fairly common to use SQLite or
similar just to get proper database storage for it.

> 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.

Which is why we need to keep the old way of doing things *as well*, for
people who want to use that method. But that part only realliy argues
against the option to try to store everything in the database and generate
the file from there (like we do with pg_database etc).

//Magnus

On Wed, Feb 20, 2008 at 03:56:38PM -0800, paul rivers wrote:
> 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.

It used to be that Oracle didn't have this, IIRC. And it's often quoted as
one of the reasons why people choose MSSQL over it - simply because it made
things easier.

> 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.

Yeah, as long as you have that level of control, that method will work. In
a "typical environment" in many enterprises you simply don't have that
level of control. Your machines may come pre-installed by the vendor, but
you are still expected to manage and maintain them. That means you need
some interface that deals with the configuration on a per-setting basis,
not per-complete-configuration basis.

//Magnus

On Wed, Feb 20, 2008 at 11:53:47PM +0000, Gregory Stark wrote:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> >> All this discussion seems to me to be going off into the clouds, where
> >> every objection is met with some still more elaborate scheme. I think we
> >> need to look at simple, incremental, and if possible backwards
> >> compatible changes.
> >
> > +1. Let me propose the simplest possible scheme, namely
> >
> > The SQL-exposed function knows how to find and replace the definition
> > of a variable (commented or otherwise) in the primary configuration
> > file. It does not chase INCLUDEs. If it doesn't find the target
> > variable anyplace in the primary file, it errors out.
>
> I think there are a few problems with having the function edit the primary
> config file:
>
> 1) It requires parsing and dealing with arbitrary user data. There could be
> comments on the same line, the order or white-space might be important to the
> user, etc.

That is the big one.

> 2) How would this interact with config files outside of the data directory? If
> you have multiple postgres clusters using the same config fie or if your
> config file is in read-only media (as /etc often is) or if you're a packager
> where editing user-maintained /etc files is a forbidden and an awful idea this
> all leads to problems.

If it doesn't have permissions, it fails.

> I think it's much cleaner to have a postgresql.conf.auto file in the data
> directory which has a limited syntax. No comments, no extra white-space, and
> no includes. The user is not expected to edit it, though he can. The functions
> edit it using simple algorithms which add and remove single lines.

Yes, that's certainly going to be the easiest implementation. And I think
that can be a good starting-point. We can always change the implementation
later as long as we have a stable API for it. But we need to start the
implementatino with something that's reasonably low-impact for others.
Incremental development is supposed to be what OSS rocks at :)

//Magnus

On Wed, Feb 20, 2008 at 06:14:27PM -0800, Joshua D. Drake wrote:
> On Wed, 20 Feb 2008 09:42:02 -0500
> Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> > All this discussion seems to me to be going off into the clouds,
> > where every objection is met with some still more elaborate scheme. I
> > think we need to look at simple, incremental, and if possible
> > backwards compatible changes.
>
> The simplest solution I can think of is:
>
> Have a table pg_configuration (pg_settings?).
>
> Allow that table to be inserted into but not updated or deleted from.

<snip>

This pretty much kills the ability for people who *want* to edit things
from the config file only, right? I don't think that's acceptable. At least
not if it's at all avoidable, which I think it is.

//Magnus

On Wed, Feb 20, 2008 at 06:17:37PM -0500, Aidan Van Dyk wrote:
> * Josh Berkus <josh(at)agliodbs(dot)com> [080220 18:00]:
> > All,
> >
> > I think we're failing to discuss the primary use-case for this, which
> > is one reason why the solutions aren't obvious.
>
> > However, imagine you're adminning 250 PostgreSQL servers backing a
> > social networking application. You decide the application needs a
> > higher default sort_mem for all new connections, on all 250 servers.
> > How, exactly, do you deploy that?
> >
> > Worse, imagine you're an ISP and you have 250 *differently configured*
> > PostgreSQL servers on vhosts, and you need to roll out a change in
> > logging destination to all machines while leaving other settings
> > untouched.
>
> But, from my experience, those are "pretty much" solved, with things
> like rsync, SCM (pick your favourite) and tools like "clusterssh,
> multixterm", rancid, wish, expect, etc.
>
> I would have thought that any "larger enterprise" was familiar with
> these approaches, and are probably using them already to
> manage/configure there general unix environments

What makes you think that all environments are unix environments? MOst
large enterprises have multiple operating systems to manage.

> > We need a server-based tool for the manipulating postgresql.conf, and
> > one which is network-accessable, allows updating individual settings,
> > and can be plugged into 3rd-party server management tools. This goes
> > for pg_hba.conf as well, for the same reasons.
> >
> > If we want to move PostgreSQL into larger enterprises (and I certainly
> > do) we need to make it more manageable.
>
> 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?

Do you know of any cross-platform tool that is capable of dealing with the
PostgreSQL configuration file in a context sensitive manner? Meaning that
it doesn't just treat it as a big file, but you can actually do "for all
these 32 servers, change work_mem to 2Mb"? If so, I'd like to know which
one beause I could *raelly* use that one right now.

//Magnus

On Wed, Feb 20, 2008 at 03:02:49PM -0800, Josh Berkus wrote:
> All,
>
> I think we're failing to discuss the primary use-case for this, which
> is one reason why the solutions aren't obvious.
>
> And that use case is: multi-server management.

I don't agree that this is the primary use case. But I do agree that it's
one.

> PostgreSQL is *easy* to manage on one server. For a single server, the
> existing text file editor GUIs are clunky but good enough.

Agreed.

> Now, none of this requires managing the settings via the SQL command
> line. Since we need to make it network-accessable, though, that seems
> the easiest route. Otherwise, we'd have to set up a daemon running on
> a 2nd port.

Not just a 2nd port. A second security and authenticatino system.
Supporting all the authentication methods the backend does (we can't just
say "you can use gssapi/kerberos to increase your security, and TLS to
prevent sniffing. Oh, but to make configuration changes, it's plaintext
passwords over unencrypted connection")

A second daemon and a second protocol is just plain stupid. We have a
perfectly good framework to build it on inside the current protocol.

> P.S. I don't care what the syntax is.

+1. Probably the best way is a function, because that's least invasive, and
easiest to change.

//Magnus

Aidan Van Dyk wrote:
> * Magnus Hagander <magnus(at)hagander(dot)net> [080220 14:03]:
>
> > > I think the first step is really for some people to show code that
> > > "rewrites" the config file changing a setting reliably and correctly.
> >
> > But what we're donig now is discussing *how to do that*, no?
>
> Sort of, but of course, we're getting caught up in extra syntactic
> stuff..
>
> If someone *is* writing this config-rewriter now, these are the types of
> quesitons I think they need to be asking, some of which have been
> touched on, some not. But I think a first cut could pick any answer for
> them, and still be easily adaptable...
>
> 1) What file to we "rewrite"? Main one, or some other specified one?

I think the file that is the source for the option that we write.
If a parser can determine what is the last occurence of the option in
the configuration files - this can be done as well for SET PERMANENT.

> 2) Do we follow includes to find our setting?

Yes and no. We have to follow includes, otherwise the effect of remote
changes to the option won't be the same as the effect of manual changes
via the text editor. However, following the previous proposals we can
store a file that is the source of the option, i.e. the file that sets
the value for the option that is active for the time of SET PERMANENT.

> 3) Which setting do we change, the 1st, or last found in the config
> file?

I think that is active, presumably last.

> 4) What do we do about comments *on the same line* as the setting we're
> changing (I'm assuming all other lines won't be touched)

Just drop them. Another idea is to comment the old value, preserving
the old inline comments, and put a new value to the next line, but
I don't really like it since eventually it would make the config file
unreadable with lots of 'dead' commented values.
Anyway, we can't parse that comments unless we teach the backend to speak
several hundreds of available world languages, that is not what the
database development about :).

> 5) How do we want to handle errors like "ENOSPC", or EPERM (I'm assuming
> of course that the file rewrite will be a tmp+rename, not a trunc+write)

don't change anything, return ERROR.

> 6) Do we want to distinguish between "restart only" settings, and
> reloadable settings, and if so, how?

I think now, since we don't digstinguish between them when writing the
config file manually.

--
Alexey Klyukin http://www.commandprompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Magnus Hagander wrote:
> On Wed, Feb 20, 2008 at 06:14:27PM -0800, Joshua D. Drake wrote:
>> On Wed, 20 Feb 2008 09:42:02 -0500
>> Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>
>>> All this discussion seems to me to be going off into the clouds,
>>> where every objection is met with some still more elaborate scheme. I
>>> think we need to look at simple, incremental, and if possible
>>> backwards compatible changes.
>> The simplest solution I can think of is:
>>
>> Have a table pg_configuration (pg_settings?).
>>
>> Allow that table to be inserted into but not updated or deleted from.
>
> <snip>
>
> This pretty much kills the ability for people who *want* to edit things
> from the config file only, right? I don't think that's acceptable. At least
> not if it's at all avoidable, which I think it is.

I did mention in a reply that it could be optional :).

Joshua D. Drake

* Magnus Hagander <magnus(at)hagander(dot)net> [080221 04:30]:

> > I would have thought that any "larger enterprise" was familiar with
> > these approaches, and are probably using them already to
> > manage/configure there general unix environments

> What makes you think that all environments are unix environments? MOst
> large enterprises have multiple operating systems to manage.

Well, I guess I've been lucky so far ;-)

> Do you know of any cross-platform tool that is capable of dealing with the
> PostgreSQL configuration file in a context sensitive manner? Meaning that
> it doesn't just treat it as a big file, but you can actually do "for all
> these 32 servers, change work_mem to 2Mb"? If so, I'd like to know which
> one beause I could *raelly* use that one right now.

perl (cpp at one point), git (recently, I fought with CVS previously),
and ssh have worked for me.

Again, I guess I've been lucky so far.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Thu, 21 Feb 2008 10:30:00 +0100
Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> Do you know of any cross-platform tool that is capable of dealing with the
> PostgreSQL configuration file in a context sensitive manner? Meaning that
> it doesn't just treat it as a big file, but you can actually do "for all
> these 32 servers, change work_mem to 2Mb"? If so, I'd like to know which
> one beause I could *raelly* use that one right now.

If it was me I would create a database that served variables and a
portable script - Python for me but Perl would probably work too - that
read variables from the database and plugged them into a basic
template. That way you can make localized changes to specific servers
by changing the database or global changes by changing the template.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> Do you know of any cross-platform tool that is capable of dealing with the
> PostgreSQL configuration file in a context sensitive manner? Meaning that
> it doesn't just treat it as a big file, but you can actually do "for all
> these 32 servers, change work_mem to 2Mb"? If so, I'd like to know which
> one beause I could *raelly* use that one right now.

I've got my aforementioned plperlu function, if you want to take a look at
that. It even keeps whitespace and comments intact. The one catch: you
need to have plperlu installed on all the servers. :)

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200802211042
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAke9m94ACgkQvJuQZxSWSsiG0QCgq8MXBzF/XBtMTroyzQwXKSDq
uQgAn0Xni/1gDgnC/71mUNuxNlDdKei1
=6koE
-----END PGP SIGNATURE-----

>>> On Wed, Feb 20, 2008 at 5:02 PM, in message
<web-15430532(at)davinci(dot)ethosmedia(dot)com>, "Josh Berkus" <josh(at)agliodbs(dot)com> wrote:

> imagine you're adminning 250 PostgreSQL servers backing a
> social networking application. You decide the application needs a
> higher default sort_mem for all new connections, on all 250 servers.
> How, exactly, do you deploy that?
>
> Worse, imagine you're an ISP and you have 250 *differently configured*
> PostgreSQL servers on vhosts, and you need to roll out a change in
> logging destination to all machines while leaving other settings
> untouched.

We handle this by having a common postgresql.conf file, with an
include at the end for an override file. We can push out a new
default everywhere using scp and ssh. (No, we don't run production
databases on Windows.) For central machines (those that we don't
need to go through the WAN to reach), I've occasionally wished for
the ability to reconfigure through a database connection; but the
total time saved by such a feature probably would not amount to the
time required to read through the syntax definition.

Regarding other database products, I know that Sybase reads a
configuration file at startup or when a RECONFIGURE command is
issued. There is a function called sp_configure to allow changes
through a database configuration, or it can be edited directly.
When using the function, the old file is renamed using a numeric
suffix, to keep a history of configurations. Lines are not
commented out, but DEFAULT is used for values without an override.
sp_configure without a value specified shows you the existing values
with columns for default value, current configuration file value,
value currently in effect (since you might not have issued the
reconfigure or it might be a startup-only setting), and the RAM
required to support the configured value.

-Kevin

Joshua D. Drake napsal(a):
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Tue, 19 Feb 2008 15:22:42 -0300
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>
>> Joshua D. Drake wrote:
>>
>>> IMO this should all be in the database and that's it. The idea that
>>> our global settings are in a file seems unusual consider we have a
>>> perfectly good storage engine available.
>> That doesn't work, because many settings must be loaded before the
>> database is fully operational.
>
> Right but couldn't that be changed or if not, why not only have the
> settings that "must" be loaded before the database is fully operation
> in the postgresql.conf file.
>
> I can hear the wails of we don't want multiple configuration sources
> but we already have multiple configuration sources and having 90% of
> the configuration in the database should would make it easier.

I like this idea. By my opinion only GUC variable with PGC_POSTMASTER context
must be in postgresql.conf. Most of them are related to memory and networking
configuration. Other can be only store in database. And for startup, default
value will work pretty well.

By the way, why postgresql.conf is required for startup when I'm able setup all
parameter on command line?

Zdenek

Zdenek Kotala wrote:

> I like this idea. By my opinion only GUC variable with PGC_POSTMASTER
> context must be in postgresql.conf. Most of them are related to memory
> and networking configuration. Other can be only store in database. And
> for startup, default value will work pretty well.
>
> By the way, why postgresql.conf is required for startup when I'm able
> setup all parameter on command line?
>

You aren't able to.

postgres=# set shared_buffers = '32768';
ERROR: parameter "shared_buffers" cannot be changed after server start
STATEMENT: set shared_buffers = '32768';
ERROR: parameter "shared_buffers" cannot be changed after server start
postgres=#

Joshua D. Drake

>
> Zdenek
>

Joshua D. Drake napsal(a):
> Zdenek Kotala wrote:
>
>> I like this idea. By my opinion only GUC variable with PGC_POSTMASTER
>> context must be in postgresql.conf. Most of them are related to memory
>> and networking configuration. Other can be only store in database. And
>> for startup, default value will work pretty well.
>>
>> By the way, why postgresql.conf is required for startup when I'm able
>> setup all parameter on command line?
>>
>
> You aren't able to.
>
> postgres=# set shared_buffers = '32768';
> ERROR: parameter "shared_buffers" cannot be changed after server start
> STATEMENT: set shared_buffers = '32768';
> ERROR: parameter "shared_buffers" cannot be changed after server start
> postgres=#

I meant following notation:

/usr/bin/postgres -shared_buffers=32768

Zdenek

Magnus Hagander wrote:
> If they don't have an actual database, it's fairly common to use SQLite or
> similar just to get proper database storage for it.

With all the concern about parsing in this thread, perhaps it's best
if this config-overrides file not be of the same syntax as postgresql.conf
at all.

If the interactive form of these overrides will be
"SET PERMANENTLY work_mem TO 65MB;", why not make the override
config file use the same syntax; since a parser for it'll have
to exist anyway?

Maybe some XML bloat. Or, since you mentioned it, perhaps SQLite
itself, since some people on the thread seem to want sql-like
syntaxes to maintain it?

[Personally, we maintain perl scripts that apply patches to
the default postgresql.conf; and check those in to source
control. I don't think I'd use this override file feature.]

Hi,

Magnus Hagander wrote:
...
>> Can you explain why this wouldn't be usable?
>
> Because you will end up with an ever-growing file, that will be a PITA to
> deal with. Consider it after 10k+ changes. (yes, I can see that happening.
> You know how some people use GUIs) Or 100k. The problem does not happen at
> 100 lines...

I don't think even 100k lines would be a problem. And there should be a
penalty if someone would have automated settings to be written
permanently.

> I can see the solution with a single file with them all in, but it needs to
> be able to overwrite them IMHO.

Why? If you want to strip down the file you can just postprocess it.
Either a tool or even a little sed-script could do. And you would
save the records for reference.

+1 for Aidans idea.

Regards
Tino