Thread: 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@hagander.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 supportin 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@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
> -----Original Message----- > From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers- > owner@postgresql.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@hagander.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@hagander.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@highrise.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@hagander.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@hagander.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@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
* Csaba Nagy <nagy@ecircle-ag.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@highrise.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
On Tue, Feb 19, 2008 at 11:11:05AM -0500, Aidan Van Dyk wrote: > * Csaba Nagy <nagy@ecircle-ag.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
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:09:43AM -0500, Aidan Van Dyk wrote: > * Magnus Hagander <magnus@hagander.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@turnstep.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@hagander.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'sPostgreSQL 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@hagander.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@commandprompt.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@commandprompt.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@hagander.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@hagander.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@hagander.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 overridethe settings in the rest of this postgresql.conf file, which will be ignored.# to disable automated and SQL command-line-basedconfiguration# 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 | DeveloperOptionsarchive_command | Write-Ahead Log / Settingsarchive_mode | Write-Ahead Log / Settingsarchive_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 | Settingsarchive_mode | Write-Ahead Log | Settingsarchive_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' # Fsyncfsync = onwal_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@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian <bruce@momjian.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@agliodbs.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@agliodbs.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@agliodbs.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@agliodbs.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@hagander.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 whywe 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@hagander.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@dunslane.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@agliodbs.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@agliodbs.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@agliodbs.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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Wed, 20 Feb 2008 13:27:25 -0500 Tom Lane <tgl@sss.pgh.pa.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-----
Magnus Hagander <magnus@hagander.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
* Joshua D. Drake <jd@commandprompt.com> [080220 13:43]:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Wed, 20 Feb 2008 13:27:25 -0500
> Tom Lane <tgl@sss.pgh.pa.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 tradionallybeen.
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 fashion2) It's implemtation is easily replacable by
anyone,in any fashion3) It's easily backportable to adminpack/8.3/8.2/8.1 for those who wantit
</bikeshedding>
a.
--
Aidan Van Dyk Create like a god,
aidan@highrise.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@hagander.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@highrise.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@commandprompt.com> [080220 13:43]:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > On Wed, 20 Feb 2008 13:27:25 -0500
> > Tom Lane <tgl@sss.pgh.pa.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@hagander.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 betouched) 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@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
Magnus Hagander <magnus@hagander.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@hagander.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@hagander.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@hagander.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@sss.pgh.pa.us> wrote: > Josh Berkus <josh@agliodbs.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@agliodbs.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@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
Aidan Van Dyk <aidan@highrise.ca> writes:
> * Josh Berkus <josh@agliodbs.com> [080220 18:00]:
>> We need a server-based tool for the manipulating postgresql.conf, and
>> one which is network-accessable, allows updating individual settings,
> Do we need to develop our own set of "remote management" tools/systems,
> or possibly document some best practices using already available "multi-
> server managment" tools?
Indeed. If Josh's argument were correct, why isn't every other daemon
on the planet moving away from textual configuration files?
IIRC, one of the arguments for the config include-file facility was to
simplify management of multiple servers by letting them share part or
all of their configuration data. One of the things that bothers me
considerably about all the proposals made so far in this thread
(including mine) is that they don't play very nicely with such a
scenario. Putting a setting into one file that contradicts one made in
some other file is a recipe for confusion and less admin-friendliness,
not more.
regards, tom lane
Aidan Van Dyk wrote: -- Start of PGP signed section. > * Josh Berkus <josh@agliodbs.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@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Wed, 20 Feb 2008 18:38:10 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Aidan Van Dyk <aidan@highrise.ca> writes: > > * Josh Berkus <josh@agliodbs.com> [080220 18:00]: > >> We need a server-based tool for the manipulating postgresql.conf, > >> and one which is network-accessable, allows updating individual > >> settings, > > > Do we need to develop our own set of "remote management" > > tools/systems, or possibly document some best practices using > > already available "multi- server managment" tools? > > Indeed. If Josh's argument were correct, why isn't every other daemon > on the planet moving away from textual configuration files? 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@momjian.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@sss.pgh.pa.us> writes: > Andrew Dunstan <andrew@dunslane.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@highrise.ca> writes: > >> * Josh Berkus <josh@agliodbs.com> [080220 18:00]: >> >>> We need a server-based tool for the manipulating postgresql.conf, and >>> one which is network-accessable, allows updating individual settings, >>> > > >> Do we need to develop our own set of "remote management" tools/systems, >> or possibly document some best practices using already available "multi- >> server managment" tools? >> > > Indeed. If Josh's argument were correct, why isn't every other daemon > on the planet moving away from textual configuration files? > > IIRC, one of the arguments for the config include-file facility was to > simplify management of multiple servers by letting them share part or > all of their configuration data. One of the things that bothers me > considerably about all the proposals made so far in this thread > (including mine) is that they don't play very nicely with such a > scenario. Putting a setting into one file that contradicts one made in > some other file is a recipe for confusion and less admin-friendliness, > not more. > If you're interested in comments from the peanut gallery, we run hundreds of instances of nearly equal numbers of oracle, sql server, postgres, mysql. IMHO oracle has the most polish here, with its pfile/spfile business (excluding listener management, which is still pretty primitive, esp compared to the equivalent of what pg_hba.conf offers). SQL Server is close, with the internal table sysconfigures, but some things do get stashed in the registry. You can programatically edit this across the network, so it's not so bad. For postgres and mysql, we make mass changes by being able to mass distribute a postgresql.conf or my.cnf patch script, which is usually sed/awk/perl-ish in nature, and then running this en masse from a host setup with a trusted ssh key that can look through the servers list and call the patch script on each host (and for each instance that might be on that host). The config files get auto-checked into a SCM from there. So it's very much as Aidan described. To date, this approach has worked without any problems. In our case, there is a very uniform layout for everything, which is what makes this work. postgresql.conf/my.cnf start from general templates, there are standard locations for everything, there are shell functions to fetch details about any instance from a master list, etc. So while some team members would be happy if Pg were more Oracle-esque, it's not a *major* issue for us. I can't imagine, though, a so-called "enterprise" setup where I would be willing to literally share the same config file across instances (via NFS or whatever). Seriously, that's just not done. Someone may do that for a few or even a dozen, but not on the scale Josh is talking about. Further, while every daemon may not be moving away from text config files, most every database is? Certainly if our environment grew out faster than we were able to consolidate it and develop a sensible, structured approach, I could imagine we would be in a world of hurt. The oracle approach seems to me close to the dba's ideal. I can generate a text representation from the running instance or binary representation, modify the running instance and/or binary representation from the text version, find the locations of these programmatically, and do all this via sqlplus or whatever I want, across the network. If we mass deploy a pile of servers, we're not likely to comment different settings for each instance, but rather document in our install docs why we are now setting A to x instead of y across the board. Regards, Paul
On Thu, Feb 21, 2008 at 12:02 AM, Josh Berkus <josh@agliodbs.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@dunslane.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@agliodbs.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@commandprompt.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@highrise.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@highrise.ca> wrote: > * Joshua D. Drake <jd@commandprompt.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@commandprompt.com> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wed, 20 Feb 2008 23:02:34 -0500
Aidan Van Dyk <aidan@highrise.ca> wrote:
> * Joshua D. Drake <jd@commandprompt.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@agliodbs.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@highrise.ca> writes: > > * Josh Berkus <josh@agliodbs.com> [080220 18:00]: > >> We need a server-based tool for the manipulating postgresql.conf, and > >> one which is network-accessable, allows updating individual settings, > > > Do we need to develop our own set of "remote management" tools/systems, > > or possibly document some best practices using already available "multi- > > server managment" tools? > > Indeed. If Josh's argument were correct, why isn't every other daemon > on the planet moving away from textual configuration files? 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@highrise.ca> writes: > > > >>* Josh Berkus <josh@agliodbs.com> [080220 18:00]: > >> > >>>We need a server-based tool for the manipulating postgresql.conf, and > >>>one which is network-accessable, allows updating individual settings, > >>> > > > > > >>Do we need to develop our own set of "remote management" tools/systems, > >>or possibly document some best practices using already available "multi- > >>server managment" tools? > >> > > > >Indeed. If Josh's argument were correct, why isn't every other daemon > >on the planet moving away from textual configuration files? > > > >IIRC, one of the arguments for the config include-file facility was to > >simplify management of multiple servers by letting them share part or > >all of their configuration data. One of the things that bothers me > >considerably about all the proposals made so far in this thread > >(including mine) is that they don't play very nicely with such a > >scenario. Putting a setting into one file that contradicts one made in > >some other file is a recipe for confusion and less admin-friendliness, > >not more. > > > > If you're interested in comments from the peanut gallery, we run > hundreds of instances of nearly equal numbers of oracle, sql server, > postgres, mysql. > > IMHO oracle has the most polish here, with its pfile/spfile business > (excluding listener management, which is still pretty primitive, esp > compared to the equivalent of what pg_hba.conf offers). SQL Server is > close, with the internal table sysconfigures, but some things do get > stashed in the registry. You can programatically edit this across the > network, so it's not so bad. 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@sss.pgh.pa.us> writes: > > > Andrew Dunstan <andrew@dunslane.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@dunslane.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@agliodbs.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@hagander.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@dunslane.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@hagander.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@highrise.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@hagander.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@druid.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@turnstep.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@davinci.ethosmedia.com>, "Josh Berkus" <josh@agliodbs.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@commandprompt.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.]
I have been looking at this thread for a bit and want to interject an idea. A couple years ago, I offered a patch to the GUC system that added a number of abilities, two left out were: (1) Specify a configuration file on the command line. (2) Allow the inclusion of a configuration file from within the configuration file. If the include functionality were re-instated, then a default file, something like, $DATADIR/defaults.conf could be included by default on start up. This file could be written by the program. It offers the benefit of being readable and editable, requiring very little extra work to implement, and working in a consistent way with existing functionality. P.S. I'd like the ability to load a config file with a command line option as well. :-)
Mark Woodward wrote: > I have been looking at this thread for a bit and want to interject an idea. > > A couple years ago, I offered a patch to the GUC system that added a > number of abilities, two left out were: > > (1) Specify a configuration file on the command line. > (2) Allow the inclusion of a configuration file from within the > configuration file. > eh? We have both of these capabilities. cheers andrew
> > > Mark Woodward wrote: >> I have been looking at this thread for a bit and want to interject an >> idea. >> >> A couple years ago, I offered a patch to the GUC system that added a >> number of abilities, two left out were: >> >> (1) Specify a configuration file on the command line. >> (2) Allow the inclusion of a configuration file from within the >> configuration file. >> > > eh? We have both of these capabilities. Really? Maybe I'm just missing it in the "--help" message and the web docs, (I didn't see it in the code either) how do you specify a config file on postmaster startup? Also I didn't see any way to include a file from within postgresql.conf.
Mark Woodward wrote: >> Mark Woodward wrote: >> >>> I have been looking at this thread for a bit and want to interject an >>> idea. >>> >>> A couple years ago, I offered a patch to the GUC system that added a >>> number of abilities, two left out were: >>> >>> (1) Specify a configuration file on the command line. >>> (2) Allow the inclusion of a configuration file from within the >>> configuration file. >>> >>> >> eh? We have both of these capabilities. >> > > Really? Maybe I'm just missing it in the "--help" message and the web > docs, (I didn't see it in the code either) how do you specify a config > file on postmaster startup? > > Also I didn't see any way to include a file from within postgresql.conf. > > See http://www.postgresql.org/docs/current/static/config-setting.html and http://www.postgresql.org/docs/current/static/runtime-config-file-locations.html cheers andrew
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