Thread: postgresql.conf and postgres options
I've just been looking through the options which can and cannot be set in
postgresql.conf and have a few points to raise.
1. There are some undocumented options which appear to relate to WAL:
Name Variable Default Set by
checkpoint_timeout CheckPointTimeout 300 Startupwal_buffers XLOGbuffers 8
Startupwal_files XLOGfiles 0 Startupwal_debug &XLOG_DEBU 0
Superusercommit_delay &CommitDelay 5 User
Is there any text anywhere to explain what these do? (Point me to that or some commented code, and I'll write a
documentationpatch.)
2. The following command line options to postgres don't have an equivalent in postgresql.conf. Is that intentional?
(I suppose it is in several cases, and I have left out some where it is obviously intentional.) I can't see why
theseitems can't be put in the configuration file:
Option Action
-C Noversion = true [not documented in postgres man page]-D potential_Datadir = arg [set PGDATA]-E
EchoQuery = true [echo queries to log]-e EuroDates = true [use European format for dates]-N
UseNewLine= 0 [newline is not a query separator]-o [set stdout, stderr to file arg]
3. I see the -E is documented as being for stand-alone mode only; in fact it is useful for getting the query into
thebackend log in normal operation.
4. The documentation for -o is confusing:
-o file-name
Sends all debugging and error output to OutputFile. If the backendis running under the postmaster, error messages are
stillsent tothe frontend process as well as to OutputFile, but debugging outputis sent to the controlling tty of the
postmaster(since only one file descriptor can be sent to an actual file).
I think this is saying that, under the postmaster, debugging output does not get sent to OutputFile, and error
messagesare sent both to OutputFile and to the frontend. Is that correct?
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
======================================== "Enter into his gates with thanksgiving, and into his courts with
praise.Be thankful unto him, and bless his name." Psalms 100:4
> 1. There are some undocumented options which appear to relate to WAL: ... > Is there any text anywhere to explain what these do? > (Point me to that or some commented code, and I'll write > a documentation patch.) I'll send description to you soon, thanks. Vadim
Oliver Elphick writes: > 2. The following command line options to postgres don't have an equivalent in > postgresql.conf. Is that intentional? (I suppose it is in several cases, > and I have left out some where it is obviously intentional.) I can't see > why these items can't be put in the configuration file: > -C Noversion = true [not documented in postgres man page] This option doesn't do anything. > -D potential_Datadir = arg [set PGDATA] This option can't be in the config file because it is used to *find* the config file. > -E EchoQuery = true [echo queries to log] Hmm, there's debug_print_query. This will probably be consolidated in the future. > -e EuroDates = true [use European format for dates] This should be a config file option, but Thomas Lockhart couldn't make up his mind what to call it. ;-) > -N UseNewLine = 0 [newline is not a query separator] I don't think this is useful. > -o [set stdout, stderr to file arg] I think this is broken or not well maintained. Will be cleaned up in some later release. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
I think all valid options should appear in the options file. Right now, only a few are in there, I think. > I've just been looking through the options which can and cannot be set in > postgresql.conf and have a few points to raise. > > 1. There are some undocumented options which appear to relate to WAL: > > Name Variable Default Set by > > checkpoint_timeout CheckPointTimeout 300 Startup > wal_buffers XLOGbuffers 8 Startup > wal_files XLOGfiles 0 Startup > wal_debug &XLOG_DEBU 0 Superuser > commit_delay &CommitDelay 5 User > > Is there any text anywhere to explain what these do? (Point me to that or > some commented code, and I'll write a documentation patch.) > > 2. The following command line options to postgres don't have an equivalent in > postgresql.conf. Is that intentional? (I suppose it is in several cases, > and I have left out some where it is obviously intentional.) I can't see > why these items can't be put in the configuration file: > > > Option Action > > -C Noversion = true [not documented in postgres man page] > -D potential_Datadir = arg [set PGDATA] > -E EchoQuery = true [echo queries to log] > -e EuroDates = true [use European format for dates] > -N UseNewLine = 0 [newline is not a query separator] > -o [set stdout, stderr to file arg] > > 3. I see the -E is documented as being for stand-alone mode only; in fact it > is useful for getting the query into the backend log in normal operation. > > 4. The documentation for -o is confusing: > > -o file-name > > Sends all debugging and error output to OutputFile. If the backend > is running under the postmaster, error messages are still sent to > the frontend process as well as to OutputFile, but debugging output > is sent to the controlling tty of the postmaster (since only one > file descriptor can be sent to an actual file). > > I think this is saying that, under the postmaster, debugging output does > not get sent to OutputFile, and error messages are sent both to OutputFile > and to the frontend. Is that correct? > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight http://www.lfix.co.uk/oliver > PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "Enter into his gates with thanksgiving, and into his > courts with praise. Be thankful unto him, and bless > his name." Psalms 100:4 > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
I have added all possible config options to postgresql.conf.sample. I have attached the new version of the file. I think you will be amazed at how GUC gives us such powerful control over PostgreSQL. Thanks, Peter. > Oliver Elphick writes: > > > 2. The following command line options to postgres don't have an equivalent in > > postgresql.conf. Is that intentional? (I suppose it is in several cases, > > and I have left out some where it is obviously intentional.) I can't see > > why these items can't be put in the configuration file: > > > -C Noversion = true [not documented in postgres man page] > > This option doesn't do anything. > > > -D potential_Datadir = arg [set PGDATA] > > This option can't be in the config file because it is used to *find* the > config file. > > > -E EchoQuery = true [echo queries to log] > > Hmm, there's debug_print_query. This will probably be consolidated in the > future. > > > -e EuroDates = true [use European format for dates] > > This should be a config file option, but Thomas Lockhart couldn't make up > his mind what to call it. ;-) > > > -N UseNewLine = 0 [newline is not a query separator] > > I don't think this is useful. > > > -o [set stdout, stderr to file arg] > > I think this is broken or not well maintained. Will be cleaned up in some > later release. > > -- > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026 # # PostgreSQL configuration file # ----------------------------- # # This file consists of lines of the form # # name = value # # (The `=' is optional.) White space is collapsed, comments are # introduced by `#' anywhere on a line. The complete list of option # names and allowed values can be found in the PostgreSQL # documentation. Examples are: #log_connections = on #fsync = off #max_connections = 64 # Any option can also be given as a command line switch to the # postmaster, e.g., 'postmaster -c log_connections=on'. Some options # can be set at run-time with the 'SET' SQL command. #======================================================================== # # Connection Parameters # #tcpip_socket = false #ssl = false #max_connections = 32 # 1-1024 #port = 5432 #hostname_lookup = false #show_source_port = false #unix_socket_directory = "" #unix_socket_group = "" #unix_socket_permissions = 0777 #virtual_host = "" #krb_server_keyfile = "" # # Performance # #sort_mem = 512 #shared_buffers = 2*max_connections # min 16 #fsync = true # # Optimizer Parameters # #enable_seqscan = true #enable_indexscan = true #enable_tidscan = true #enable_sort = true #enable_nestloop = true #enable_mergejoin = true #enable_hashjoin = true #ksqo = false #geqo = true #effective_cache_size = 1000 # default in 8k pages #random_page_cost = 4 #cpu_tuple_cost = 0.01 #cpu_index_tuple_cost = 0.001 #cpu_operator_cost = 0.0025 #geqo_selection_bias = 2.0 # range 1.5-2.0 # # GEQO Optimizer Parameters # #geqo_threshold = 11 #geqo_pool_size = 0 #default based in tables, range 128-1024 #geqo_effort = 1 #geqo_generations = 0 #geqo_random_seed = -1 # auto-compute seed # # Inheritance # #sql_inheritance = true # # Deadlock # #deadlock_timeout = 1000 # # Expression Depth Limitation # #max_expr_depth = 10000 # min 10 # # Write-ahead log (WAL) # #wal_buffers = 8 # min 4 #wal_files = 0 # range 0-64 #wal_debug = 0 # range 0-16 #commit_delay = 5 # range 0-1000 #checkpoint_timeout = 300 # range 30-1800 # # Debug display # #silent_mode = false #log_connections = false #log_timestamp = false #log_pid = false #debug_level = 0 # range 0-16 #debug_print_query = false #debug_print_parse = false #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false #ifdef USE_ASSERT_CHECKING #debug_assertions = true #endif # # Syslog # #ifdef ENABLE_SYSLOG #syslog = 0 # range 0-2 #syslog_facility = "LOCAL0" #syslog_ident = "postgres" #endif # # Statistics # #show_parser_stats = false #show_planner_stats = false #show_executor_stats = false #show_query_stats = false #ifdef BTREE_BUILD_STATS #show_btree_build_stats = false #endif # # Lock Tracing # #trace_notify = false #ifdef LOCK_DEBUG #trace_locks = false #trace_userlocks = false #trace_spinlocks = false #debug_deadlocks = false #trace_lock_oidmin = 16384 #trace_lock_table = 0 #endif
> Bruce Momjian writes:
>
> > I have added all possible config options to postgresql.conf.sample.
>
> It was actually fully intentional that there was *no* list of all possible
> config options in the sample file, because
>
> 1) Who's going to maintain this?
>
> 2) People should read the documentation before messing with options.
>
> (" is not the correct string delimiter either.)
Changed to ''. Thanks.
>
> I have bad experiences with sample config files. The first thing I
> usually do is delete them and dig up the documentation.
>
> Do other people have comments on this issue?
I have marked all places where these defaults are set in the C code,
pointing them to update postgresql.conf.sample.
I found it is nice to see a nice list of all options for quick review.
It makes the file much more useful, I think.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian wrote: > I have added all possible config options to postgresql.conf.sample. > I have attached the new version of the file. I think you will be amazed > at how GUC gives us such powerful control over PostgreSQL. Good. As a sysadmin I _like_ sample configs (which I usually rename to something else, and write my own, of course), as it gives a single concise quick-reference to the syntax. Lots of packages do this -- squid is the biggest one I can think of right now (biggest in terms of config file size and power). Samba is another example. There are a plethora of others, of course. But, Peter's point does hold -- someone will have to maintain this. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
I think the list is great, show what can be configured rather than
guessing/digging to find it, where it belongs, in what order (if any), etc.
etc. etc.
The only addition I could think would be to label (default value).
Needless, I like it.. :)
At 1/24/2001 01:03 PM, Bruce Momjian wrote:
> > Bruce Momjian writes:
> >
> > > I have added all possible config options to postgresql.conf.sample.
> >
> > It was actually fully intentional that there was *no* list of all possible
> > config options in the sample file, because
> >
> > 1) Who's going to maintain this?
> >
> > 2) People should read the documentation before messing with options.
> >
> > (" is not the correct string delimiter either.)
>
>Changed to ''. Thanks.
>
> >
> > I have bad experiences with sample config files. The first thing I
> > usually do is delete them and dig up the documentation.
> >
> > Do other people have comments on this issue?
>
>I have marked all places where these defaults are set in the C code,
>pointing them to update postgresql.conf.sample.
>
>I found it is nice to see a nice list of all options for quick review.
>It makes the file much more useful, I think.
Defaults are listed as the assignment value, and of course, the are all
commented out.
> I think the list is great, show what can be configured rather than
> guessing/digging to find it, where it belongs, in what order (if any), etc.
> etc. etc.
> The only addition I could think would be to label (default value).
>
> Needless, I like it.. :)
>
>
> At 1/24/2001 01:03 PM, Bruce Momjian wrote:
> > > Bruce Momjian writes:
> > >
> > > > I have added all possible config options to postgresql.conf.sample.
> > >
> > > It was actually fully intentional that there was *no* list of all possible
> > > config options in the sample file, because
> > >
> > > 1) Who's going to maintain this?
> > >
> > > 2) People should read the documentation before messing with options.
> > >
> > > (" is not the correct string delimiter either.)
> >
> >Changed to ''. Thanks.
> >
> > >
> > > I have bad experiences with sample config files. The first thing I
> > > usually do is delete them and dig up the documentation.
> > >
> > > Do other people have comments on this issue?
> >
> >I have marked all places where these defaults are set in the C code,
> >pointing them to update postgresql.conf.sample.
> >
> >I found it is nice to see a nice list of all options for quick review.
> >It makes the file much more useful, I think.
>
>
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian writes:
> I have added all possible config options to postgresql.conf.sample.
It was actually fully intentional that there was *no* list of all possible
config options in the sample file, because
1) Who's going to maintain this?
2) People should read the documentation before messing with options.
(" is not the correct string delimiter either.)
I have bad experiences with sample config files. The first thing I
usually do is delete them and dig up the documentation.
Do other people have comments on this issue?
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/