Re: Improving postgresql.conf

I have a LOT of opinions about postgresql.conf, and frankly, I think more
comments are not where the problems lie.

If you *really* want to make configuring postgresql easier,
postgresql.conf HAS to live outside the data directory and specify where
everything is. postgresql.conf should do exactly as one would assume it
does, configure postgresql.

Right now it doesn't. Right now it just sets parameters and the "-D" or
PGDATA environment variable *really* configure postgresql. If you do not
know how a machine is setup, you have to look for the install. Hopefuly,
the previous administrator did not have any test directories which would
confuse the search. Sorry, I'm ranting.

In an ideal world, I envision Postgresql having a default location for
postgresql.conf, in this file will be the declarations for where the data
directory is, possible included files, etc. i.e. the stuff I've been
pushing litterally for years. I am not saying that the current behavior
change in any way, what I am saying is that a more world compatible
methodology should be possible.

Once the postgresql.conf file is out of the data directory, you have a new
paradigm from which to work. One could write a setup application, in java
or something, which creates a new postgresql.conf file, right down to
where you want the installed directory to be, and THAT is used by initdb.
The setup application can also provide context sensitive help for each of
the setting. The user may not even *know* that there is such a file as
postgresql.conf.

The application should behave like a control panel of sorts which would
allow you to modify an existing configuration file and optionally restart
the correct postgresql process.

(Obviously, this program can be made to look at other files in other
locations.)

The work flow would be something like this:

run setup application.
Choose volume and directory for database.
set various options
press OK
standard "are you sure" dialog.
run initdb with variables,

In linux, freebsd, or Windows, have the option to start server at system
start.

This is all basically doable right now. Granted we have to hammer out a
few details in my configuration patch, but there are no show stoppers that
I can see. I even have a Windows application which will start and monitor
a cygwin PostgreSQL. It should be easily modified for a mingwin version.

That, IMHO, would *really* make PostgreSQL easy, almost brainless to
install and use.

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> (warning: rehashing of issues ahead)
>
> Sometimes when I talk to technical people about using PostgreSQL,
> I get a strong reaction along the lines of "it's ugly, complex, and
> hard to set up." While we have gotten better than we used to be,
> some of this is still true. I usually press for further details.
> One of the top items mentioned is the configuration files,
> especially postgresql.conf. Specifically, it is non-standard and
> cryptic.
>
> One thing that would go a long way is to remove the confusing
> "default is commented out" behavior. This is ugly and unnecessary:
> simply explicitly list every value.
>
> The second thing that would help is better documentation. It doesn't
> have to be quite as verbose as the httpd.conf file, but a nice
> multi-line explanation of every item in the file, perhaps with a
> URL for further information, would be a very nice addition. Moving
> the comments above each item, rather than trying to squeeze some of
> them next to the parameter, would also make the file more readable
> and more consistent.
>
> For example, this original sample:
>
> #shared_buffers = 1000          # min 16, at least max_connections*2, 8KB
> each
> #work_mem = 1024                # min 64, size in KB
>
> becomes:
>
> ## shared_buffers (kilobytes)
> ## Sets the number of shared memory buffers used by the database server.
> ## Increasing the number of buffers makes it more likely backends will
> find
> ## the information they need in the cache, thus avoiding an expensive
> operating
> ## system request. The default is typically 1000, but may be less if your
> ## kernel settings will not support it. Each buffer is 8192 bytes. The
> minimum
> ## value is 16, or twice the value of the 'max_connections' parameter.
> Settings
> ## significantly higher than the minimum are usually needed for good
> performance.
> ## Values of a few thousand are recommended for production installations.
> ## URL: http://www.postgresql.org/docs/current/static/runtime-config.html
>
> shared_buffers = 1000
>
>
> ## work_mem (kilobytes)
> ## (Previously known as sort_mem)
> ## Sets the amount of memory to be used by internal sort operations and
> hash tables before
> ## switching to temporary disk files. The default value is 1024 kilobytes
> (1 MB).
> ## Note that for a complex query, several sort or hash operations might be
> running in parallel;
> ## each one will be allowed to use up to the value set here before it
> starts to put data
> ## into temporary files. Also, several running sessions could be doing
> sort operations
> ## simultaneously. So the total memory used could be many times the value
> set here.
>
> work_mem = 1024
>
>
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200406090615
>
> -----BEGIN PGP SIGNATURE-----
>
> iD8DBQFAxuPnvJuQZxSWSsgRAgAtAKDReW6WOREYapbWi61yacBi05im6gCeLrvd
> Dj/mlTtUh97C1gHVkJTtLyY=
> =J0CZ
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>