Thread: A smaller default postgresql.conf

I seem to recall that there was general support for installing a smaller 
default postgresql.conf file with only, say, a dozen parameters mentioned for 
initial tuning.  The complete file can stay as a sample.  Any objections to 
that?  (Let's not discuss quite yet exactly which parameters are the chosen 
ones.)

Peter Eisentraut wrote:
> I seem to recall that there was general support for installing a smaller 
> default postgresql.conf file with only, say, a dozen parameters mentioned for 
> initial tuning.  The complete file can stay as a sample.  Any objections to 
> that?  (Let's not discuss quite yet exactly which parameters are the chosen 
> ones.)
>
>   

i think this would make sense as long as this small file tells users 
where to find the "full story".
generally i would say that this would be a step into the right direction.

alternatively we could use some sort of "#include" mechanism to split 
"most important" and "not so important".
   hans


-- 
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com

Hans-Juergen Schoenig wrote:

> alternatively we could use some sort of "#include" mechanism to split  
> "most important" and "not so important".

We already have an "include" mechanism.

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

Alvaro Herrera wrote:
> Hans-Juergen Schoenig wrote:
> 
>> alternatively we could use some sort of "#include" mechanism to split  
>> "most important" and "not so important".
> 
> We already have an "include" mechanism.

Using that to include a file that's full of comments anyway (which is
all that's left in postgresql.conf at this time, I'm sure) just seems.
Well. Sub-optimal.


//Magnus

On Tue, 19 Aug 2008 17:11:49 +0200
Magnus Hagander <magnus@hagander.net> wrote:

> Alvaro Herrera wrote:
> > Hans-Juergen Schoenig wrote:
> > 
> >> alternatively we could use some sort of "#include" mechanism to
> >> split "most important" and "not so important".
> > 
> > We already have an "include" mechanism.
> 
> Using that to include a file that's full of comments anyway (which is
> all that's left in postgresql.conf at this time, I'm sure) just seems.
> Well. Sub-optimal.

Yes but part of this idea is valid. The fact is the majority of the
postgresql.conf parameters don't need to be in there by default. It
just makes the file an intimidating mess for newbies and I am not
talking about just n00bs but also people coming from other environments
such as MSSQL.

I believe we could probably break the conf down to a reasonable 2 dozen
or less parameters. The rest should just be documented in our
documentation and call it good. We even have static URLs for this (I
seem to have dejavu with this as I am pretty sure I have had this
discussion already).

Joshua D> Drake


> 
> 
> //Magnus
> 


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua Drake <jd@commandprompt.com> writes:
> Magnus Hagander <magnus@hagander.net> wrote:
>> Using that to include a file that's full of comments anyway (which is
>> all that's left in postgresql.conf at this time, I'm sure) just seems.
>> Well. Sub-optimal.

> Yes but part of this idea is valid. The fact is the majority of the
> postgresql.conf parameters don't need to be in there by default. It
> just makes the file an intimidating mess for newbies and I am not
> talking about just n00bs but also people coming from other environments
> such as MSSQL.

Well, why not just make a one-eighty and say that the default
postgresql.conf is *empty* (except for whatever initdb puts into it)?
I've never thought that the current contents were especially useful
as documentation; the kindest thing you can say about 'em is that they
are duplicative of the SGML documentation.  For novices they aren't
even adequately duplicative.
        regards, tom lane

On Tue, 19 Aug 2008 12:12:16 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Joshua Drake <jd@commandprompt.com> writes:
> > Magnus Hagander <magnus@hagander.net> wrote:

> > Yes but part of this idea is valid. The fact is the majority of the
> > postgresql.conf parameters don't need to be in there by default. It
> > just makes the file an intimidating mess for newbies and I am not
> > talking about just n00bs but also people coming from other
> > environments such as MSSQL.
> 
> Well, why not just make a one-eighty and say that the default
> postgresql.conf is *empty* (except for whatever initdb puts into it)?

I guess it would depend on what initdb puts into it. I don't really
have a problem ripping out all extra stuff as it would help force
people to read the docs but would we still have 150 parameters?. From a
friendly perspective it would make sense to tone it down to the key
parameters such as shared_buffers, listen_address, work_mem etc... 

We don't need (for example) to have autovacuum in there by default as it
is always on and configured reasonably at this point. If they need to
change autovacuum they should be reading the docs about it first;
the same with bgwriter, fsync, async_commit etc...

> I've never thought that the current contents were especially useful
> as documentation; the kindest thing you can say about 'em is that they
> are duplicative of the SGML documentation.  For novices they aren't
> even adequately duplicative.

I can't argue with this. :)

Sincerely,

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua Drake <jd@commandprompt.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, why not just make a one-eighty and say that the default
>> postgresql.conf is *empty* (except for whatever initdb puts into it)?

> I guess it would depend on what initdb puts into it.

Per the code:

max_connections
shared_buffers
max_fsm_pages        (slated to die anyway in 8.4)
lc_messages
lc_monetary
lc_numeric
lc_time
datestyle
default_text_search_config

The first three of those are derived from probing the SHMMAX setting,
and the rest are from the initdb-time locale settings.
        regards, tom lane

Tom,

> Well, why not just make a one-eighty and say that the default
> postgresql.conf is *empty* (except for whatever initdb puts into it)?
> I've never thought that the current contents were especially useful
> as documentation; the kindest thing you can say about 'em is that they
> are duplicative of the SGML documentation.  For novices they aren't
> even adequately duplicative.

Well, that doesn't help unless we either provide a .conf generation tool
(something I favor) or docs somewhere which explain which are the
variables to be the most concerned with instead of making users read
through all 218 of them.

Attached is the postgresql.conf.simple I used in my presentaiton.  It
has an egregious math error in it (see if you can find it) but should
give you the general idea.

--Josh

# ----------------------------------------
# Simple PostgreSQL Configuration File
# ----------------------------------------

# This file provides a simple configuration with the most common options
# which most users need to modify for running PostgreSQL in production,
# including extensive notes on how to set each of these.  If your configuration
# needs are more specific, then use the standard postgresql.conf, or add
# additional configuration options to the bottom of this file.
#
# This file is re-read when you send a SIGHUP to the server, or on a full
# restart.  Note that on a SIGHUP simply recommenting the settings is not
# enough to reset to default value; the last explicit value you set will
# still be in effect.
#
# AvRAM:  Several of the formulas below ask for "AvRAM", which is short for
# "Available RAM".  This refers to the amount of memory which is available for
# running PostgreSQL.  On a dedicated PostgreSQL server, you can use the total
# system RAM, but on shared servers you need to estimate what portion of RAM
# is usually available for PostgreSQL.
#
# Each setting below lists one recommended starting setting, followed by
# several alternate settings which are commented out.  If multiple settings
# are uncommented, the *last* one will take effect.

# listen_addresses
# ------------------------
# listen_addresses takes a list of network interfaces the Postmaster will
# listen on.  The setting below, '*', listens on all interfaces, and is only
# appropriate for development servers and initial setup.  Otherwise, it
# should be restrictively set to only specific addresses. Note that most
# PostgreSQL access control settings are in the pg_hba.conf file.

  listen_addresses = '*' # all interfaces
# listen_addresses = 'localhost'  # unix sockets and loopback only
# listen_addresses = 'localhost,192.168.1.1' # local and one external interface

# max_connections
# ------------------------
# An integer setting a limit on the number of new connection processes which
# PostgreSQL will create.  Should be set to the maximum number of connections
# which you expect to need at peak load.  Note that each connection uses
# shared_buffer memory, as well as additional non-shared memory, so be careful
# not to run the system out of memory.  In general, if you need more than 1000
# connections, you should probably be making more use of connection pooling.
#
# Note that by default 3 connections are reserved for autovacuum and
# administration.

  max_connections = 200  # small server
# max_connections = 700  # web application database
# max_connections = 40   # data warehousing database

# shared_buffers
# ------------------------
# A memory quantity defining PostgreSQL's "dedicated" RAM, which is used
# for connection control, active operations, and more.  However, since
# PostgreSQL also needs free RAM for file system buffers, sorts and
# maintenance operations, it is not advisable to set shared_buffers to a
# majority of RAM.
#
# Note that increasing shared_buffers often requires you to increase some
# system kernel parameters, most notably SHMMAX and SHMALL.  See
# Operating System Environment: Managing Kernel Resources in the PostgreSQL
# documentation for more details.  Also note that shared_buffers over 2GB is
# only supported on 64-bit systems.
#
# The setting below is a formula.  Calculate the resulting value, then
# uncomment it.  Values should be expressed in kB, MB or GB.

# shared_buffers = ( AvRAM / 4 )
# shared_buffers = 512MB   # basic 2GB web server
# shared_buffers = 8GB     # 64-bit server with 32GB RAM

# work_mem
# ------------------------
# This memory quantity sets the limit for the amount of non-shared RAM
# available for each query operation, including sorts and hashes.  This limit
# acts as a primitive resource control, preventing the server from going
# into swap due to overallocation.  Note that this is non-shared RAM per
# *operation*, which means large complex queries can use multple times
# this amount.  Also, work_mem is allocated by powers of two, so round
# to the nearest binary step.

# The setting below is a formula.  Calculate the resulting value, then
                                                            
# uncomment it.  Values should be expressed in kB, MB or GB.  Maximum
# is currently 2GB.

# Most web applications should use the formula below, because their
# queries often require no work_mem.
# work_mem = ( AvRAM / max_connections ) ROUND DOWN to 2^x
# work_mem = 2MB  # for 2GB server with 700 connections

# Formula for most BI/DW applications, or others running many complex
# queries:
# work_mem = ( AvRAM / ( 2 * max_connections ) ) ROUND DOWN to 2^x
# work_mem = 128MB   # DW server with 32GB RAM and 40 connections

# maintenance_work_mem
# -------------------------
# This memory value sets the limit for the amount that autovacuum,
# manual vacuum, bulk index build and other maintenance routines are
# permitted to use.  Setting it to a moderately high value will increase
# the efficiency of vacuum and other operations.

# The setting below is a formula.  Calculate the resulting value, then
                                                                 
# uncomment it.  Values should be expressed in kB, MB or GB.
# Maximum is currently 2GB.
                              

# Formula for most databases
# maintenance_work_mem = ( AvRAM / 8 ) ROUND DOWN to 2^x
# maintenance_work_mem = 256MB  #webserver with 2GB RAM
# maintenance_work_mem = 2GB  #DW server with 32GB RAM

# max_fsm_pages
# --------------------------
# An integer which sets the maximum number of data pages with free space
# which the Postmaster will track.  Setting this too low can lead to
# table bloat and need for VACUUM FULL.  Should be set to the maximum number
# of data pages you expect to be updated between vacuums.
#
# Increasing this setting requires dedicated RAM and like shared_buffers
# may require to to increase system kernel parameters.  Additionally, the
# recommended setting below is based on the default autovacuum settings;
# if you change the autovacuum parameters, then you may need to adjust
# this setting to match.

# The setting below is a formula.  Calculate the resulting value, then
                                                                 
# uncomment it.  DBsize is your estimate of the maximum size of the database;
# if the database is already loaded, you can get his from pg_database_size().
# For large data warehouses, use the volume of data which changes between
# batch loads as your "DBSize"

# For small databases ( less than 10GB )
# max_fsm_pages = ( ( DBsize / 8kB ) / 8 )
# max_fsm_pages = 100000  #6GB web database

# For larger databases ( Many GB to a few TB )
# max_fsm_pages = ( ( DBsize / 8kB ) / 16 )
# max_fsm_pages = 800000  #100GB OLTP database
# max_fsm_pages = 4000000  #DW loading 0.5TB data daily

# synchronous_commit
# -------------------------
# This boolean setting controls whether or not all of your transactions
# are gauranteed to be written to disk when they commit.  If you are
# willing to lose up to 0.4 seconds of data in the event of an unexpected
# shutdown (as many web applications are), then you can gain substantial
# performance benefits by turning off synchronous commit.  For most
# applications, however, this setting is better used on a per-session
# basis.

  synchronous_commit = on   #most applications
# synchronous_commit = off  #if speed is more important than data

# wal_buffers
# -------------------------
# this memory setting defines how much buffer space is available for
# the Write Ahead Log.  Set too low, it can become a bottleneck on
# inserts and updates; there is no benefit to setting it high, however.
# As with some of the other settings above, may require increasing
# some kernel parameters.

wal_buffers = 8MB

# checkpoint_segments
# -------------------------
# This integer defines the maximum number of 8MB transaction log segments
# PostgreSQL will create before forcing a checkpoint.  For most
# high-volume OTLP databases and DW you will want to increase this
# setting significantly.  Alternately, just wait for checkpoint
# warnings in the log before increasing this.
#
# Increasing this setting can make recovery in the event of unexpected
# shutdown take longer.
#
# Maximum disk space required is (checkpoint_segments * 2 + 1) * 16MB,
# so make sure you have that much available before setting it.

checkpoint_segments = 16    #normal small-medium database
# checkpoint_segments = 64  #high-volume OLTP database
# checkpoint_segments = 128 #heavy-ETL large database

# autovacuum
# ---------------------------
# autovacuum turns on a maintenance daemon which runs in the background,
# periodically cleaning up your tables and indexes.  The only reason to turn
# autovacuum off is for large batch loads (ETL).

  autovacuum = on   #most databases
# autovacuum = off  #large DW

# effective_cache_size
# --------------------------
# This memory setting tells the PostgreSQL query planner how much RAM
# is estimated to be available for caching data, in both shared_buffers and
# in the filesystem cache. This setting just helps the planner make good
# cost estimates; it does not actually allocate the memory.

# The setting below is a formula.  Calculate the resulting value, then
                                                                 
# uncomment it.

# effective_cache_size = ( AvRAM * 0.75 )

# default_statistics_target
# --------------------------
# This integer setting determines the histogram sample size for the
# data about table contents kept by the query planner.  The default
# is fine for most databases, but often users need to increase it
# either because they're running data warehouses or because they have
# a lot of poorly planned queries.

default_statistics_target = 10
# default_statistics_target = 200  #have had some bad plans
# default_statistics_target = 400  #data warehouse

# constraint_exclusion
# --------------------------
# This boolean setting should be turned "on" if you plan to use table
# partitioning.  Otherwise, it should be "off".

  constraint_exclusion = off #in general
# constraint_exclusion = on  #if you plan to use partitioning

# log_destination & logging settings
# --------------------------
# This ENUM value determines where PostgreSQL's logs are sent.  What
# setting to use really depends on your server room setup and the
# production status and OS of your server.
#
# Note that there are several dozen settings on what and how often
# to log; these will not be covered in detail in this quick
# configuration file.  Instead, several common combinations are
# given.

# Syslog setup for centralized monitoring
# log_destination = 'syslog'
# syslog_facility = 'LOCAL0'  #local syslog
# syslog_facility = 'log_server_name'  #remote syslog

# Windows
# log_destination = 'eventlog'

# Private PostgreSQL Log
# log_destination = 'stderr'
# log_collector = on
# log_directory = '/path/to/log/dir'

# CSV logging for collecting performance statistics.
# Warning: this much logging will generate many log
# files and affect performance.
# log_destination = 'csvlog'
# log_collector = on
# log_directory = '/path/to/log/dir'
# log_duration = on
# log_temp_files = 256kB
# log_statement = 'all'

On Tue, 19 Aug 2008 12:48:20 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Joshua Drake <jd@commandprompt.com> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Well, why not just make a one-eighty and say that the default
> >> postgresql.conf is *empty* (except for whatever initdb puts into
> >> it)?
> 
> > I guess it would depend on what initdb puts into it.
> 
> Per the code:
> 
> max_connections
> shared_buffers
> max_fsm_pages        (slated to die anyway in 8.4)
> lc_messages
> lc_monetary
> lc_numeric
> lc_time
> datestyle
> default_text_search_config
> 
> The first three of those are derived from probing the SHMMAX setting,
> and the rest are from the initdb-time locale settings.

When I first started to reply I had a list of another dozen or so we
should add but in reality as I think about it; we need only one more
parameter. If we add listen_addresses and a link to the documention for
the rest, I would +1 this.

I was thinking about the apache conf and it is riddled with
documentation, lots and lots of text. I find that either I am irritated
with how much documentation there is (because I already understand the
directive I am working with) or I am frustrated because it doesn't
adequately explain the dependencies.

If we move to the above route, we end up in an environment with a
single source for "official" documentation and we can always point to
that.

Sincerely,

Joshua D. Drake


> 
>             regards, tom lane
> 


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

>>> Josh Berkus <josh@agliodbs.com> wrote: 
> Attached is the postgresql.conf.simple I used in my presentaiton.  It

> has an egregious math error in it (see if you can find it) but should

> give you the general idea.
Well, this sure looks scary:
# maintenance_work_mem = 256MB  #webserver with 2GB RAM
But I'm amazed by this, too:
# max_connections = 700  # web application database
How many CPUs and spindles are you assuming there?
My testing and experience suggest applications should use no more than
4 per CPU plus 2 per spindle, absolute maximum.  Don't you find that a
connection pool with queuing capability is required for best
performance with a large number of users?
-Kevin

Joshua Drake <jd@commandprompt.com> writes:
> If we move to the above route, we end up in an environment with a
> single source for "official" documentation and we can always point to
> that.

Yeah, the fundamental point here is whether or not postgresql.conf
should be trying to serve as part of our system documentation.  I'm
inclined to think that any comments in it should be more about why these
particular values have been set, and not "here are some values you might
like to twiddle".  So initdb might emit

# Set by initdb from probing kernel limits 2008-08-11
max_connections = 100
shared_buffers = 32MB

# Set by initdb from its locale environment: LANG = en_US
lc_messages = en_US
lc_monetary = en_US
[etc]

I'm really not in favor of having comments in the conf file that try to
tell you about stuff you might want to set, much less why.  That task
properly belongs to some kind of introductory chapter in the SGML docs.
Novice DBAs are unlikely even to *find* the config file, let alone look
inside it, if there's not an introductory chapter telling them about
Things They Ought To Do.
        regards, tom lane

On Tue, Aug 19, 2008 at 1:17 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
>> Josh Berkus <josh@agliodbs.com> wrote:
> But I'm amazed by this, too:
>
> # max_connections = 700  # web application database
>
> How many CPUs and spindles are you assuming there?
>
> My testing and experience suggest applications should use no more than
> 4 per CPU plus 2 per spindle, absolute maximum.  Don't you find that a
> connection pool with queuing capability is required for best
> performance with a large number of users?

Agreed, with this many concurrent users, I would expect severe lock
contention on the ProcArrayLock.  Similarly, if this were heavily
updated, WAL-related locks would likely become another significant
bottleneck.

-- 
Jonah H. Harris, Senior DBA
myYearbook.com

On Tue, 19 Aug 2008 12:17:46 -0500
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

> Well, this sure looks scary:
>  
> # maintenance_work_mem = 256MB  #webserver with 2GB RAM

I would agree. 2GB isn't that much memory as it is and that is a fairly
heft amount of maintenance_work_mem. This isn't the days when vacuum
ran via cron at 2am anymore. Autovacuum will fire at any time.

>  
> But I'm amazed by this, too:
>  
> # max_connections = 700  # web application database
>  
> How many CPUs and spindles are you assuming there?
>  
> My testing and experience suggest applications should use no more than
> 4 per CPU plus 2 per spindle, absolute maximum.  Don't you find that a
> connection pool with queuing capability is required for best
> performance with a large number of users?

I just did the math on this and I would say you are correct. I had
never really evaluated in the way you just had but based on some of our
larger installs (32cores, 100 spindles) your math works.

Noting that he actually states it is a webserver connecting there
should absolutely be a pool in front of PostgreSQL.

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Tue, 19 Aug 2008 13:22:34 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm really not in favor of having comments in the conf file that try
> to tell you about stuff you might want to set, much less why.  That
> task properly belongs to some kind of introductory chapter in the
> SGML docs. Novice DBAs are unlikely even to *find* the config file,
> let alone look inside it, if there's not an introductory chapter
> telling them about Things They Ought To Do.

I would be willing to work up a patch that does as you suggest.

Sincerely,

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Tuesday 19 August 2008 19:12:16 Tom Lane wrote:
> Well, why not just make a one-eighty and say that the default
> postgresql.conf is *empty* (except for whatever initdb puts into it)?

Well, my original implementation of GUC had an empty default configuration 
file, which was later craptaculated to its current form based on seemingly 
popular demand.  I am very happy to work back toward the empty state, and 
there appears to be growing support for that.

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


> I'm really not in favor of having comments in the conf file that try to
> tell you about stuff you might want to set, much less why.  That task
> properly belongs to some kind of introductory chapter in the SGML docs.
> Novice DBAs are unlikely even to *find* the config file, let alone look
> inside it, if there's not an introductory chapter telling them about
> Things They Ought To Do.

Ugh, you are heading in the wrong direction. The configuration file
should be well documented: moving the documentation further away
from it is the wrong idea, especially if it means firing up a web
browser to do so. As link is fine, and recommended, but a "bare"
configuration file would be far, far worse than the mess we have today.
I like Josh B's version a lot. It's not perfect (I'd add a URL for
each config for example), but it's a great start. Text space is cheap, and having
a consistent, well-documented, easy-to-read conf file is something
worth shooting for.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200808191511
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkirGyEACgkQvJuQZxSWSsgsvwCdH6Hb4KOj47j/Zceb26FgEQUM
J2gAoKE19rLhMpgP17EdJIuUVoKQ7H3u
=//eH
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg@turnstep.com> writes:
> I like Josh B's version a lot. It's not perfect (I'd add a URL for
> each config for example), but it's a great start.

Josh B's approach is great until people start making changes that are
unrelated to (or perhaps even contradictory to) his comments.  And then
it's just a recipe for confusion.  I would far rather see his text as
part of the SGML docs.
        regards, tom lane

On Tue, 19 Aug 2008 19:12:47 -0000
"Greg Sabino Mullane" <greg@turnstep.com> wrote:

> Ugh, you are heading in the wrong direction. The configuration file
> should be well documented: moving the documentation further away
> from it is the wrong idea, especially if it means firing up a web
> browser to do so.

It is impossible to document the postgresql.conf file in a manner that
is truly useful without firing up the reading material in the first
place.

Even with Josh's improvements there are too many variables and we are
just going to have a bunch of people breaking stuff and then
complaining that, "well it was suggested in the postgresql.conf". 

> a URL
> for each config for example), but it's a great start. Text space is
> cheap, and having a consistent, well-documented, easy-to-read conf
> file is something worth shooting for.

I have yet to find one; anywhere.

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote:
> moving the documentation further away from it is the wrong idea,
> especially if it means firing up a web browser to do so.

I can see that argument, but I think we can quite simply solve it if we 
provide a plain-text version of the configuration chapter of the 
documentation.  You can easily grep that in a second window and don't have to 
be in-your-face to users who just want to edit the settings.

> Text space is cheap,

I'd offer the alternative theory that anything that is longer than one screen 
is overwhelming and unwieldy.

> I'm really not in favor of having comments in the conf file that try to
> tell you about stuff you might want to set, much less why.  That task
> properly belongs to some kind of introductory chapter in the SGML docs.
> Novice DBAs are unlikely even to *find* the config file, let alone look
> inside it, if there's not an introductory chapter telling them about
> Things They Ought To Do.

+1.  When I have a question about something PostgreSQL-related, the
first think I do is Read The Fine Manual.  The PostgreSQL
documentation is excellent, and one of the highlights of the project
IMO.

I've read through the postgresql.conf file occasionally, but that's a
really difficult way to try to understand the subject.  I'd much
rather read through that file in a web browser than a shell window -
but the real advantage of putting it in the documentation is that you
can not only document each specific setting, but also give a broad
overview of relevant topics.  A section on "Performance Tuning" in
"Section III: Server Administration" would be really great.

...Robert

Peter Eisentraut wrote:
> On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote:
> > moving the documentation further away from it is the wrong idea,
> > especially if it means firing up a web browser to do so.
> 
> I can see that argument, but I think we can quite simply solve it if we 
> provide a plain-text version of the configuration chapter of the 
> documentation.  You can easily grep that in a second window and don't have to 
> be in-your-face to users who just want to edit the settings.

Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e.,
you run "man postgresql.conf" and it gives you this manpage documenting
every option.

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

On Tue, 19 Aug 2008 15:43:11 -0400
Alvaro Herrera <alvherre@commandprompt.com> wrote:

> Peter Eisentraut wrote:
> > On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote:
> > > moving the documentation further away from it is the wrong idea,
> > > especially if it means firing up a web browser to do so.
> > 
> > I can see that argument, but I think we can quite simply solve it
> > if we provide a plain-text version of the configuration chapter of
> > the documentation.  You can easily grep that in a second window and
> > don't have to be in-your-face to users who just want to edit the
> > settings.
> 
> Hmm, let me suggest providing it as a manpage for postgresql.conf,
> i.e., you run "man postgresql.conf" and it gives you this manpage
> documenting every option.

and native windows help (egad) but yes that would be good.

Joshua D. Drake 


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Peter Eisentraut wrote:
> On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote:
>> Text space is cheap,
> 
> I'd offer the alternative theory that anything that is longer than one screen 
> is overwhelming and unwieldy.

One more benefit of a small file is that it makes it easier to ask someone
"please attach a copy of your postgresql.conf file"; rather than "please
send the output of "grep -v '^[    ]*#' postgresql.conf  | grep ="" or worse
"Can you recall what you changed?"

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Peter Eisentraut wrote:
>> I can see that argument, but I think we can quite simply solve it if we 
>> provide a plain-text version of the configuration chapter of the 
>> documentation.

> Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e.,
> you run "man postgresql.conf" and it gives you this manpage documenting
> every option.

Seems a bit Unix-centric, but +1 for it on Unix machines anyway.
Is there any near equivalent on Windows?
        regards, tom lane

On Tue, 19 Aug 2008 17:03:48 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Peter Eisentraut wrote:
> >> I can see that argument, but I think we can quite simply solve it
> >> if we provide a plain-text version of the configuration chapter of
> >> the documentation.
> 
> > Hmm, let me suggest providing it as a manpage for postgresql.conf,
> > i.e., you run "man postgresql.conf" and it gives you this manpage
> > documenting every option.
> 
> Seems a bit Unix-centric, but +1 for it on Unix machines anyway.
> Is there any near equivalent on Windows?

Yes there are Windows Help files. I would imagine the installer would
deal with that. Magnus would obviously know better than I.

Sincerely,

Joshua D. Drake

> 
>             regards, tom lane
> 


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Tue, Aug 19, 2008 at 10:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Peter Eisentraut wrote:
>>> I can see that argument, but I think we can quite simply solve it if we
>>> provide a plain-text version of the configuration chapter of the
>>> documentation.
>
>> Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e.,
>> you run "man postgresql.conf" and it gives you this manpage documenting
>> every option.
>
> Seems a bit Unix-centric, but +1 for it on Unix machines anyway.
> Is there any near equivalent on Windows?

No. There are helpfiles (which consist of a navigation tree and a
bunch of pages), but they're what we use for the main docs. There's
nothing akin to a man page.
-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Tue, 19 Aug 2008, Josh Berkus wrote:

> Well, that doesn't help unless we either provide a .conf generation tool 
> (something I favor) or docs somewhere which explain which are the variables 
> to be the most concerned with instead of making users read through all 218 of 
> them.

The design for a pg_generate_conf tool you suggested that's now dumped 
into http://wiki.postgresql.org/wiki/GUCS_Overhaul#pg_generate_conf seemed 
the only reasonable solution I've ever heard here.  The difference of 
opinion between those those want a tiny file and those who want a full one 
cannot be reconciled.  It's not a logical debate, it's a religious one. 
The best you can do is provide something that's switchable to work for the 
most popular positions:

* "The file should be minimal"
* "Every parameter should be there with lots of documentation"
* "Just the important parameters should be listed"

Because no one who is firmly in one of those camps will ever move to 
another just by arguing here.

I'm going to rewrite that Wiki page to make it more obvious how the 
proposed changes actually map to resolving problems in this area.  Much of 
what's come up in this thread is already addressed there but that's 
clearly not obvious to most people.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Dave Page wrote:
> On Tue, Aug 19, 2008 at 10:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Alvaro Herrera <alvherre@commandprompt.com> writes:

> >> Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e.,
> >> you run "man postgresql.conf" and it gives you this manpage documenting
> >> every option.
> >
> > Seems a bit Unix-centric, but +1 for it on Unix machines anyway.
> > Is there any near equivalent on Windows?
> 
> No. There are helpfiles (which consist of a navigation tree and a
> bunch of pages), but they're what we use for the main docs. There's
> nothing akin to a man page.

Well, so we provide a reference to the help file and that's it.  If
there's a way to provide a link in the config file that would
automatically open the appropriate help file on click, that would be
perfect.

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

On Tue, Aug 19, 2008 at 07:12:47PM -0000, Greg Sabino Mullane wrote:
> > I'm really not in favor of having comments in the conf file that
> > try to tell you about stuff you might want to set, much less why.
> > That task properly belongs to some kind of introductory chapter in
> > the SGML docs.  Novice DBAs are unlikely even to *find* the config
> > file, let alone look inside it, if there's not an introductory
> > chapter telling them about Things They Ought To Do.
> 
> Ugh, you are heading in the wrong direction.  The configuration file
> should be well documented: moving the documentation further away
> from it is the wrong idea, especially if it means firing up a web
> browser to do so.  As link is fine, and recommended, but a "bare"
> configuration file would be far, far worse than the mess we have
> today.  I like Josh B's version a lot.  It's not perfect (I'd add a
> URL for each config for example), but it's a great start.  Text
> space is cheap, and having a consistent, well-documented,
> easy-to-read conf file is something worth shooting for.

How about a man page for postgresql.conf?  We already ship very nice
man pages for SQL commands.  While we're at it, we could ship one for
pg_hba.conf, too :)

What do we do about man pages on Windows?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, Aug 19, 2008 at 09:39:39PM +0300, Peter Eisentraut wrote:
> On Tuesday 19 August 2008 19:12:16 Tom Lane wrote:
> > Well, why not just make a one-eighty and say that the default
> > postgresql.conf is *empty* (except for whatever initdb puts into it)?
> 
> Well, my original implementation of GUC had an empty default configuration 
> file, which was later craptaculated to its current form based on seemingly 
> popular demand.  I am very happy to work back toward the empty state, and 
> there appears to be growing support for that.

I like the almost empty file idea.

I often use the include facility to setup postgresql.conf files
that look roughly like this:
 #include postgresql_site_policy.conf #include postgresql_<host_class>.conf listen_port=9999

where the included files set things like the overall sites logging
preferences and buffer_cache and workmen based on installed memory and
disk system (ex: postgresql_32GB.conf) etc.

-dg


-- 
David Gould       daveg@sonic.net      510 536 1443    510 282 0869
If simplicity worked, the world would be overrun with insects.

On Tuesday 19 August 2008 14:39:39 Peter Eisentraut wrote:
> On Tuesday 19 August 2008 19:12:16 Tom Lane wrote:
> > Well, why not just make a one-eighty and say that the default
> > postgresql.conf is *empty* (except for whatever initdb puts into it)?
>
> Well, my original implementation of GUC had an empty default configuration
> file, which was later craptaculated to its current form based on seemingly
> popular demand.  I am very happy to work back toward the empty state, and
> there appears to be growing support for that.

In that case, let me give a big -1 to this idea then. Dealing with a lot of 
newbies, they clearly know how to find the postgresql.conf without reading 
the docs (it's almost obvious), and once they do they need the helpful 
reminders we give for things like which GUC settings require a restart when 
they are changed.  

Additionally I can't imagine that we really don't want to lay out things like 
the logging parameters for people so they can figure out where to look for 
the logs. Sure, you can point someone to the docs, but you can't actually 
*see* what all of the settings are without logging into the database.  Having 
the values in the postgresql.conf at least gives people a chance at that 
(especially on unfamiliar systems when they are having login trouble). 

I'd still like to see us adopt the proposal from some time ago where we stop 
commenting out the parameters at all, but short of that, hiding options seems 
about the worst choice we could make.  If people really don't like large conf 
files, it is far easier to delete entries than it is to add them... Greg 
Mullane had it right, and Greg Smith was not too far off the mark either.  

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

Robert Treat <xzilla@users.sourceforge.net> writes:
> I'd still like to see us adopt the proposal from some time ago where
> we stop commenting out the parameters at all, but short of that,
> hiding options seems about the worst choice we could make.

Well, there seems to be a very substantial body of opinion that says
we *do* need to hide "uninteresting" options.  How many are
"interesting" is certainly open to debate, but as far as I can tell
most people think it's a short list, not "all of them".

In short, you're in the minority.
        regards, tom lane

Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > I'd still like to see us adopt the proposal from some time ago where
> > we stop commenting out the parameters at all, but short of that,
> > hiding options seems about the worst choice we could make.
> 
> Well, there seems to be a very substantial body of opinion that says
> we *do* need to hide "uninteresting" options.  How many are
> "interesting" is certainly open to debate, but as far as I can tell
> most people think it's a short list, not "all of them".
> 
> In short, you're in the minority.

On idea is for postgresql.conf to merely include other files:
include 'sharedmem.conf'include 'compat.conf'...

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:
> On idea is for postgresql.conf to merely include other files:
>     include 'sharedmem.conf'
>     include 'compat.conf'
>     ...

That would definitely add complexity ... what would it buy in return?
        regards, tom lane

On Tue, 19 Aug 2008 23:32:34 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Bruce Momjian <bruce@momjian.us> writes:
> > On idea is for postgresql.conf to merely include other files:
> >     include 'sharedmem.conf'
> >     include 'compat.conf'
> >     ...
> 
> That would definitely add complexity ... what would it buy in return?

I am not arguing for this but if we went down that route it does buy us
the ability to compartmentalize the entire conf.. so you have:

memory_settings.conf
logging.conf
maintenance.conf

It is a bit of misdirection as people will open one conf to find a
bunch of pointers to others. 

Another option would be to break up the conf like the above but do not
include any of them in the main postgresql.conf (which is how I would
argue it should be done). Thus if you want to modify logging, you
"include" it in the postgresql.conf but you make your adjustments in
logging.conf. 

Sincerely,

Joshua D. Drake


> 
>             regards, tom lane
> 


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Wed, 20 Aug 2008 00:10:35 -0400 (EDT)
Bruce Momjian <bruce@momjian.us> wrote:

> > Another option would be to break up the conf like the above but do
> > not include any of them in the main postgresql.conf (which is how I
> > would argue it should be done). Thus if you want to modify logging,
> > you "include" it in the postgresql.conf but you make your
> > adjustments in logging.conf. 
> 
> The problem then is that they modify the logging.conf file and then
> wonder why the change doesn't take effect.

Well we have that problem already :)

Joshua D. Drake 


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua Drake wrote:
> On Tue, 19 Aug 2008 23:32:34 -0400
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Bruce Momjian <bruce@momjian.us> writes:
> > > On idea is for postgresql.conf to merely include other files:
> > >     include 'sharedmem.conf'
> > >     include 'compat.conf'
> > >     ...
> >
> > That would definitely add complexity ... what would it buy in return?
>
> I am not arguing for this but if we went down that route it does buy us
> the ability to compartmentalize the entire conf.. so you have:
>
> memory_settings.conf
> logging.conf
> maintenance.conf
>
> It is a bit of misdirection as people will open one conf to find a
> bunch of pointers to others.

Well, I used to have a huge /etc/profile but now it is short and has
lines like;

    . /etc/profile.tty

    . /etc/profile.env

I find when I then edit the subfiles the contents are more uniform and I
can underestand it better.  As an example, I have attached a suggested
Postgres logging.conf file.

> Another option would be to break up the conf like the above but do not
> include any of them in the main postgresql.conf (which is how I would
> argue it should be done). Thus if you want to modify logging, you
> "include" it in the postgresql.conf but you make your adjustments in
> logging.conf.

The problem then is that they modify the logging.conf file and then
wonder why the change doesn't take effect.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'        # Valid values are combinations of
                    # stderr, csvlog, syslog and eventlog,
                    # depending on platform.  csvlog
                    # requires logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off        # Enable capturing of stderr and csvlog
                    # into log files. Required to be on for
                    # csvlogs.
                    # (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'pg_log'        # directory where log files are written,
                    # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
                    # can include strftime() escapes
#log_truncate_on_rotation = off        # If on, an existing log file of the
                    # same name as the new log file will be
                    # truncated rather than appended to.
                    # But such truncation only occurs on
                    # time-driven rotation, not on restarts
                    # or size-driven rotation.  Default is
                    # off, meaning append to existing files
                    # in all cases.
#log_rotation_age = 1d            # Automatic rotation of logfiles will
                    # happen after that time.  0 to disable.
#log_rotation_size = 10MB        # Automatic rotation of logfiles will
                    # happen after that much log output.
                    # 0 to disable.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'


# - When to Log -

#client_min_messages = notice        # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   log
                    #   notice
                    #   warning
                    #   error

#log_min_messages = warning        # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic

#log_error_verbosity = default        # terse, default, or verbose messages

#log_min_error_statement = error    # values in order of decreasing detail:
                     #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                     #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic (effectively off)

#log_min_duration_statement = -1    # -1 is disabled, 0 logs all statements
                    # and their durations, > 0 logs only
                    # statements running at least this time.

#silent_mode = off            # DO NOT USE without syslog or
                    # logging_collector
                    # (change requires restart)

# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_hostname = off
#log_line_prefix = ''            # special values:
                    #   %u = user name
                    #   %d = database name
                    #   %r = remote host and port
                    #   %h = remote host
                    #   %p = process ID
                    #   %t = timestamp without milliseconds
                    #   %m = timestamp with milliseconds
                    #   %i = command tag
                    #   %c = session ID
                    #   %l = session line number
                    #   %s = session start timestamp
                    #   %v = virtual transaction ID
                    #   %x = transaction ID (0 if none)
                    #   %q = stop here in non-session
                    #        processes
                    #   %% = '%'
                    # e.g. '<%u%%%d> '
#log_lock_waits = off            # log lock waits >= deadlock_timeout
#log_statement = 'none'            # none, ddl, mod, all
#log_temp_files = -1            # log temporary files equal or larger
                    # than specified size;
                    # -1 disables, 0 logs all temp files
#log_timezone = unknown            # actually, defaults to TZ environment

On Tue, Aug 19, 2008 at 9:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
>> I'd still like to see us adopt the proposal from some time ago where
>> we stop commenting out the parameters at all, but short of that,
>> hiding options seems about the worst choice we could make.
>
> Well, there seems to be a very substantial body of opinion that says
> we *do* need to hide "uninteresting" options.
>

more to the point... not just "uninteresting" but "dangerous for the
uninformed" ones...
i have seen to many people turning off fsync in OLTP systems 'cause
someone tolds them that will improve speed...
and work_mem setted at 256Mb because that improves a bad query that
should be rewritten as something more sanely...

--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. (593) 87171157

Alvaro Herrera wrote:
> Dave Page wrote:
>> On Tue, Aug 19, 2008 at 10:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Alvaro Herrera <alvherre@commandprompt.com> writes:
> 
>>>> Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e.,
>>>> you run "man postgresql.conf" and it gives you this manpage documenting
>>>> every option.
>>> Seems a bit Unix-centric, but +1 for it on Unix machines anyway.
>>> Is there any near equivalent on Windows?
>> No. There are helpfiles (which consist of a navigation tree and a
>> bunch of pages), but they're what we use for the main docs. There's
>> nothing akin to a man page.
> 
> Well, so we provide a reference to the help file and that's it.  If
> there's a way to provide a link in the config file that would
> automatically open the appropriate help file on click, that would be
> perfect.

It's a text file. You can't put a clickable link in a textfile. You can
put a regular http link that the user can copy/paste into his browser,
if you want to.

AFAIK, you can't create a link that points into a CHM helpfile - you can
only point to the helpfile itself, not to a specific section in it.

We could just generate a HTML file with the same contents as the
man-page though, and link to that - thus not having to link out onto the
internet which really shouldn't be accessible from your database server :-)

//Magnus

On Wed, Aug 20, 2008 at 4:40 AM, Joshua Drake <jd@commandprompt.com> wrote:
> On Tue, 19 Aug 2008 23:32:34 -0400
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Bruce Momjian <bruce@momjian.us> writes:
>> > On idea is for postgresql.conf to merely include other files:
>> >     include 'sharedmem.conf'
>> >     include 'compat.conf'
>> >     ...
>>
>> That would definitely add complexity ... what would it buy in return?
>
> I am not arguing for this but if we went down that route it does buy us
> the ability to compartmentalize the entire conf.. so you have:
>
> memory_settings.conf
> logging.conf
> maintenance.conf

Would make it damn hard for pgAdmin to figure out how to edit the
config though. That's why we only support single-file configs atm.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

Peter Eisentraut wrote:

> On Tuesday 19 August 2008 19:12:16 Tom Lane wrote:
>> Well, why not just make a one-eighty and say that the default
>> postgresql.conf is *empty* (except for whatever initdb puts into it)?
>
> Well, my original implementation of GUC had an empty default  
> configuration
> file, which was later craptaculated to its current form based on  
> seemingly
> popular demand.  I am very happy to work back toward the empty  
> state, and
> there appears to be growing support for that.

Yeah, +1 from me.

Perhaps we should still add some comments about the parameters changed  
most often, including a link to the documentation of GUC parameters.  
As a kind of starting point for (new) users.

Best Regards
Michael Paesold

Dave Page wrote:
> On Wed, Aug 20, 2008 at 4:40 AM, Joshua Drake <jd@commandprompt.com> wrote:

> > I am not arguing for this but if we went down that route it does buy us
> > the ability to compartmentalize the entire conf.. so you have:
> >
> > memory_settings.conf
> > logging.conf
> > maintenance.conf
> 
> Would make it damn hard for pgAdmin to figure out how to edit the
> config though. That's why we only support single-file configs atm.

That's why we're working on adding a "sourcefile" attribute to GUC, so
that it's trivial to know the path of the config file each settings
comes from.

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

Alvaro Herrera wrote:
> Dave Page wrote:
>> On Wed, Aug 20, 2008 at 4:40 AM, Joshua Drake <jd@commandprompt.com> wrote:
> 
>>> I am not arguing for this but if we went down that route it does buy us
>>> the ability to compartmentalize the entire conf.. so you have:
>>>
>>> memory_settings.conf
>>> logging.conf
>>> maintenance.conf
>> Would make it damn hard for pgAdmin to figure out how to edit the
>> config though. That's why we only support single-file configs atm.
> 
> That's why we're working on adding a "sourcefile" attribute to GUC, so
> that it's trivial to know the path of the config file each settings
> comes from.

Still won't make life very easy for pgadmin, but it will make it
slightly less difficult.

That said, I think splitting the config out like that will mkae it even
more confusing for the newbie user, which is contrary to what we want here.


//Magnus

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


>> Well, there seems to be a very substantial body of opinion that says
>> we *do* need to hide "uninteresting" options.

> more to the point... not just "uninteresting" but "dangerous for the
> uninformed" ones...
> i have seen to many people turning off fsync in OLTP systems 'cause
> someone tolds them that will improve speed...
> and work_mem setted at 256Mb because that improves a bad query that
> should be rewritten as something more sanely...

It's a conf file - we shouldn't be hiding anything. If someone does
not understand a setting, they should not be changing it. It's not
our responsibility to "child-proof" the conf file, and there are so
many potential foot guns and "dangerous" settings it would be futile
to try anyway.


To clarify my earlier point, I'm strongly in favor of explanations and links
for each setting, but will concede the 'recommended settings' per Josh's
example, as much as I think it is a good idea. So above each (uncommented!)
setting, we'd have:

# foobar: Adjusts the foobariness of the database
#
# This uses units of baz from 1-10, with 10 being the strongest
#
# Changing this setting requires a reload
# This setting may also be changed per session
# The default value is 5
#
# For more information, please see:
# http://postgres.org/doc/foobar.html

foobar: 7




- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200808200923
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkisHpIACgkQvJuQZxSWSsjVKgCeOIICw6YLAAMbnE+TCo9NXVwg
YSwAn3imeIz1A25T2nib/rM6C8+dwzdX
=wdUL
-----END PGP SIGNATURE-----

On Wednesday 20 August 2008 02:22:26 Jaime Casanova wrote:
> On Tue, Aug 19, 2008 at 9:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Robert Treat <xzilla@users.sourceforge.net> writes:
> >> I'd still like to see us adopt the proposal from some time ago where
> >> we stop commenting out the parameters at all, but short of that,
> >> hiding options seems about the worst choice we could make.
> >
> > Well, there seems to be a very substantial body of opinion that says
> > we *do* need to hide "uninteresting" options.
>
> more to the point... not just "uninteresting" but "dangerous for the
> uninformed" ones...
> i have seen to many people turning off fsync in OLTP systems 'cause
> someone tolds them that will improve speed...
> and work_mem setted at 256Mb because that improves a bad query that
> should be rewritten as something more sanely...

This is a false argument, because people can just as easily be told "you 
should add fsync=off into you blank postgresql.conf" and then they are in the 
same boat. 

Most of the newbie confusion comes from a lack of understanding of how and 
what to tune. Adding a full on tuning guide into the docs would help with 
that.  Untill then, we should point them to 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

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

"Greg Sabino Mullane" <greg@turnstep.com> writes:
> # foobar: Adjusts the foobariness of the database
> #
> # This uses units of baz from 1-10, with 10 being the strongest
> #
> # Changing this setting requires a reload
> # This setting may also be changed per session
> # The default value is 5
> #
> # For more information, please see:
> # http://postgres.org/doc/foobar.html

> foobar: 7

So your plan is that postgresql.conf will be approximately two thousand
lines long, before the user has ever touched it at all?  (Two hundred
or so GUC variables and ten lines of comments for each one)

This seems entirely nuts.  Duplicating the whole contents of config.sgml
in another place is pointless as well as maintenance-intensive.  And it
*still* wouldn't be enough information for people to know how to twiddle
many of the variables; there are other parts of the SGML docs that
contain relevant info as well.

I could see having *one* comment at the top of the file giving pointers
to where to read the documentation.
        regards, tom lane

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


> So your plan is that postgresql.conf will be approximately two thousand
> lines long, before the user has ever touched it at all?  (Two hundred
> or so GUC variables and ten lines of comments for each one)

Sure, why not? Clarity should always trump brevity. The only people who
gain from a comment-less file are the ones who are already expert in it.
Besides, the file is already long enough to require use of an editor's
find function.

> This seems entirely nuts.  Duplicating the whole contents of config.sgml
> in another place is pointless as well as maintenance-intensive.  And it
> *still* wouldn't be enough information for people to know how to twiddle
> many of the variables; there are other parts of the SGML docs that
> contain relevant info as well.

No, not the entire contents - these would be shorter hints. Right now we already
partially 'duplicate' due to the mishmash of having some vars explained.
Some are explained, some are not, and some have poorly-placed end-of-line
comments. A short explanation should be enough to tell people if they need to
bother with it or not, or remind them of what it is. For full details, they
can go to the provided URL.

To add some more fuel to the fire, are those in the no-comments, bare-bone
camp going to argue for cleaning up pg_hba.conf as well?


> I could see having *one* comment at the top of the file giving pointers
> to where to read the documentation.

I think this is the one thing everyone agrees on so far. :)


- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200808201143
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkisPOcACgkQvJuQZxSWSsjoFACfezftBPQHZ63B0BgTVhDcePZb
I0oAoKZhuL+oRJguXQCGsER9P52WmTiJ
=aIpg
-----END PGP SIGNATURE-----

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


> One more benefit of a small file is that it makes it easier to ask someone
> "please attach a copy of your postgresql.conf file"; rather than "please
> send the output of "grep -v '^[    ]*#' postgresql.conf  | grep ="" or worse
> "Can you recall what you changed?"

Er, what's so hard about asking them to attach the file, no matter the size?
Seems safer to ask the average Joe to just attach the entire file rather
than worrying about if they know how to use grep or getting the syntax of that
command string correct.

> "Can you recall what you changed?"

Introduce them to 'ci -l' and you've made a friend for life. :)

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200808201151
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkisPe8ACgkQvJuQZxSWSshcRgCg73XeQhsUsASiqv6FKQSYO9ms
OboAoND1f5dvpLasLsJdqknx9g9mIUkL
=djcQ
-----END PGP SIGNATURE-----

On Wed, 20 Aug 2008 15:49:39 -0000
"Greg Sabino Mullane" <greg@turnstep.com> wrote:

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

> Sure, why not? Clarity should always trump brevity. The only people
> who gain from a comment-less file are the ones who are already expert
> in it.

You are right, Clarity always trumps brevity but then again, some
things are only clear if they are brief. Point being, the
documentation in the postgresql.conf isn't really useful and to make it
useful; would make the configuration file itself not useful. A succinct
file with directed links that are explicitly telling you, "Don't be
stupid read the docs" is a very good idea.

> 
> To add some more fuel to the fire, are those in the no-comments,
> bare-bone camp going to argue for cleaning up pg_hba.conf as well?
> 

Well I would. I find the pg_hba.conf obnoxious. Especially if we pushed
the documentation to a man page and windows help. 

Sincerely,

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

"Greg Sabino Mullane" <greg@turnstep.com> writes:
>> So your plan is that postgresql.conf will be approximately two thousand
>> lines long, before the user has ever touched it at all?  (Two hundred
>> or so GUC variables and ten lines of comments for each one)

> Sure, why not? Clarity should always trump brevity. The only people who
> gain from a comment-less file are the ones who are already expert in it.

I don't think that having to guess which parts of a 2000-line file
represent local changes, and which are just defaults, is much of an
improvement in "clarity".

> Besides, the file is already long enough to require use of an editor's
> find function.

This entire discussion is about fixing that ;-)
        regards, tom lane

On Wed, Aug 20, 2008 at 09:08:02AM -0700, Joshua D. Drake wrote:
> On Wed, 20 Aug 2008 15:49:39 -0000
> "Greg Sabino Mullane" <greg@turnstep.com> wrote:
> > Sure, why not?  Clarity should always trump brevity.  The only
> > people who gain from a comment-less file are the ones who are
> > already expert in it.
> 
> You are right, Clarity always trumps brevity but then again, some
> things are only clear if they are brief.  Point being, the
> documentation in the postgresql.conf isn't really useful and to make
> it useful; would make the configuration file itself not useful.  A
> succinct file with directed links that are explicitly telling you,
> "Don't be stupid read the docs" is a very good idea.

And we're back to man pages and CHM files.

How big a project would that latter be?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:
> And we're back to man pages and CHM files.
>
> How big a project would that latter be?

CHM files already exist.  (At least I think that CHM == HTML Help == Windows 
help system.)

David Fetter wrote:

> And we're back to man pages and CHM files.

So, did anyone else try to generate man pages?  I did "make man" and ran
into several issues.

The first is that D2MDIR needs to be specified manually.  I assume this
is how everyone does it, so I did that.

The second is that the Perl script as packaged by Debian doesn't work --
it is missing a handful of HTML entities, and it errors out before
generating anything.  I had to patch it thusly:

*** docbook2man-spec.pl.orig    2008-08-21 08:56:20.000000000 -0400
--- docbook2man-spec.pl    2008-08-19 18:13:22.000000000 -0400
***************
*** 1177,1182 ****
--- 1177,1192 ---- man_sgml('|[copy  ]|', '(C)'); man_sgml('|[nbsp  ]|', '\~'); man_sgml('|[thinsp]|', '\~');
+ man_sgml('|[mdash ]|', '--');
+ man_sgml('|[pi    ]|', 'pi');
+ man_sgml('|[ntilde]|', 'ñ');
+ man_sgml('|[aacute]|', 'á');
+ man_sgml('|[oacute]|', 'ó');
+ man_sgml('|[bull  ]|', 'X');
+ man_sgml('|[quot  ]|', '"');
+ man_sgml('|[scaron]|', 'š');
+ man_sgml('|[oslash]|', 'ø');
+ man_sgml('|[ouml  ]|', 'ö');  # # Default handlers (uncomment these if needed).  Right now, these are set


The third problem I ran into is that manpages that are supposed to go
into the "l" section do not; the filename ends in a dot:

$ ls | tail -10
SPI_saveplan.
SPI_scroll_cursor_fetch.
SPI_scroll_cursor_move.
START TRANSACTION.
TRUNCATE.
UNLISTEN.
UPDATE.
VACUUM.
VALUES.
version.sgml

The makefile is passing "--section l" so I'm not sure what's going wrong
here.

And lastly, I couldn't figure out what decides which manpages are
generated, to make it pick the text we want to publish.

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

Alvaro Herrera wrote:
> So, did anyone else try to generate man pages?

Before we get too excited here:  Man pages are only built/buildable from 
<refentry> elements.  You can't just go and convert some arbitrary section or 
chapter into a man page.  So there is a bit of work and invention necessary 
here.  Offhand I'd try writing an XSLT script that can convert the chapter to 
the required refentry structure and go from there.

> I did "make man" and ran into several issues.
>
> The first is that D2MDIR needs to be specified manually.  I assume this
> is how everyone does it, so I did that.

True, because there is no standard place for it.  And throughout most of 
history, you needed a locally built version anyway.

> The second is that the Perl script as packaged by Debian doesn't work --
> it is missing a handful of HTML entities, and it errors out before
> generating anything.

The packaging situation on Debian is quite weird.  (See also 
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=275715)  I suggest you 
download docbook2man-sgmlspl-1.0.tar.gz from upstream (docbook2x.sf.net) and 
install it (configure, make, make install).  Then with

make man D2MDIR=/usr/local/bin

I get plausible output.  There are indeed a few entities missing, but I think 
that is not really important because they appear in the areas that are not 
converted to the man pages.

If there is interest in sorting out this tools mess, then I suggest also 
exploring converting the entire thing to use the new XML-based docbook2x 
tools.

Kevin,

> # maintenance_work_mem = 256MB  #webserver with 2GB RAM

Well, that was before multiple autovacuum workers. Now, you'd want it 
lower.  But ... it's better for vacuum to finish quickly than to drag 
on.  Vacuum uses more I/O than it does RAM.

> But I'm amazed by this, too:
>  
> # max_connections = 700  # web application database
>  
> How many CPUs and spindles are you assuming there?

I'm assuming that the user isn't using connection pooling, and 7/8 of 
those connections will be idle at any given time.

--Josh

Peter,

> Well, my original implementation of GUC had an empty default configuration 
> file, which was later craptaculated to its current form based on seemingly 
> popular demand.  I am very happy to work back toward the empty state, and 
> there appears to be growing support for that.
> 

I'd prefer a hybrid: that is, something like the parameters set in 
postgresql.conf.simple (without the commentary) and the rest left out.

There are 12-20 things which people really need to set.

--Josh