Tom,
Attached is that patch I talked to you about on hackers. It changes the
description at the top and uncomments most (but not all) of the lines in an
effort to make it clear that commenting is not a way to restore the defaults.
I've tested this on my machine and we end up with the exact same defaults with
this patch as without, so no changes to initdb should be required.
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Enterprise vertical business [EMAIL PROTECTED]
and data analysis solutions (415) 752-2500
and database optimization fax 752-2387
utilizing Open Source technology San Francisco
Index: postgresql.conf.sample
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.124
diff -c -r1.124 postgresql.conf.sample
*** postgresql.conf.sample 8 Aug 2004 19:42:57 -0000 1.124
--- postgresql.conf.sample 9 Aug 2004 22:26:55 -0000
***************
*** 8,23 ****
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
! # allowed values can be found in the PostgreSQL documentation. The
! # commented-out settings shown in this file represent the default values.
#
# 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 changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
! # receives a SIGHUP. If you edit the file on a running system, you have
! # to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".
--- 8,25 ----
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
! # allowed values can be found in the PostgreSQL documentation. Please
! # note that, unless otherwise indicated, commenting out a line
! # will NOT restore the default value. Also, some options are set at initdb
! # time and are not visible in this file; these are marked.
#
# 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 changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
! # receives a SIGHUP. If you edit the file on a running system, you have
! # to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".
***************
*** 25,30 ****
--- 27,35 ----
# CONFIGURATION FILES
#---------------------------------------------------------------------------
+ # Defaults for all config files are to use the $PGDATA directory of the database cluster
+ # being run. Commented below are examples of alternate directory assignments.
+ # Options given on the command line (e.g. -D ) override the below.
# pgdata = '/usr/local/pgsql/data' # use data in another directory
# hba_conf = '/etc/pgsql/pg_hba.conf' # use hba info in another directory
# ident_conf = '/etc/pgsql/pg_ident.conf' # use ident info in another directory
***************
*** 37,62 ****
# - Connection Settings -
! #listen_addresses = 'localhost' # what IP interface(s) to listen on;
# defaults to localhost, '*' = any
! #port = 5432
! #max_connections = 100
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
! #superuser_reserved_connections = 2
! #unix_socket_directory = ''
! #unix_socket_group = ''
! #unix_socket_permissions = 0777 # octal
! #rendezvous_name = '' # defaults to the computer name
# - Security & Authentication -
! #authentication_timeout = 60 # 1-600, in seconds
! #ssl = false
! #password_encryption = true
! #krb_server_keyfile = ''
! #db_user_namespace = false
#---------------------------------------------------------------------------
--- 42,68 ----
# - Connection Settings -
! listen_addresses = 'localhost' # what IP interface(s) to listen on;
# defaults to localhost, '*' = any
! port = 5432
! max_connections = 100
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
! superuser_reserved_connections = 2
! unix_socket_directory = ''
! unix_socket_group = ''
! unix_socket_permissions = 0777 # octal
! rendezvous_name = '' # defaults to the computer name if unset
!
# - Security & Authentication -
! authentication_timeout = 60 # 1-600, in seconds
! ssl = false
! password_encryption = true
! krb_server_keyfile = ''
! db_user_namespace = false
#---------------------------------------------------------------------------
***************
*** 65,99 ****
# - Memory -
! #shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
! #work_mem = 1024 # min 64, size in KB
! #maintenance_work_mem = 16384 # min 1024, size in KB
! #max_stack_depth = 2048 # min 100, size in KB
# - Free Space Map -
! #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
! #max_fsm_relations = 1000 # min 100, ~50 bytes each
!
! # - Kernel Resource Usage -
!
! #max_files_per_process = 1000 # min 25
! #preload_libraries = ''
# - Cost-Based Vacuum Delay -
! #vacuum_cost_delay = 0 # 0-1000 milliseconds
! #vacuum_cost_page_hit = 1 # 0-10000 credits
! #vacuum_cost_page_miss = 10 # 0-10000 credits
! #vacuum_cost_page_dirty = 20 # 0-10000 credits
! #vacuum_cost_limit = 200 # 0-10000 credits
# - Background writer -
! #bgwriter_delay = 200 # 10-5000 milliseconds
! #bgwriter_percent = 1 # 1-100% of dirty buffers
! #bgwriter_maxpages = 100 # 1-1000 buffers max at once
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
--- 71,104 ----
# - Memory -
! shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
! work_mem = 1024 # min 64, size in KB
! maintenance_work_mem = 16384 # min 1024, size in KB
! max_stack_depth = 2048 # min 100, size in KB
# - Free Space Map -
! max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
! max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Cost-Based Vacuum Delay -
! vacuum_cost_delay = 0 # 0-1000 milliseconds
! vacuum_cost_page_hit = 1 # 0-10000 credits
! vacuum_cost_page_miss = 10 # 0-10000 credits
! vacuum_cost_page_dirty = 20 # 0-10000 credits
! vacuum_cost_limit = 200 # 0-10000 credits
# - Background writer -
! bgwriter_delay = 200 # 10-5000 milliseconds
! bgwriter_percent = 1 # 1-100% of dirty buffers
! bgwriter_maxpages = 100 # 1-1000 buffers max at once
+ # - Kernel Resource Usage -
+
+ max_files_per_process = 1000 # min 25
+ preload_libraries = ''
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
***************
*** 101,122 ****
# - Settings -
! #fsync = true # turns forced synchronization on or off
! #wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or open_datasync
! #wal_buffers = 8 # min 4, 8KB each
! #commit_delay = 0 # range 0-100000, in microseconds
! #commit_siblings = 5 # range 1-1000
# - Checkpoints -
! #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
! #checkpoint_timeout = 300 # range 30-3600, in seconds
! #checkpoint_warning = 30 # 0 is off, in seconds
# - Archiving -
! #archive_command = '' # command to use to archive a logfile segment
# If archive_command is '' then archiving is disabled. Otherwise, set it
# to a command to copy a file to the proper place. A simplistic example
--- 106,128 ----
# - Settings -
! fsync = true # turns forced synchronization on or off
! #wal_sync_method = {by platform} # the default varies across platforms:
# fsync, fdatasync, open_sync, or open_datasync
! # will be set at compile/initdb time
! wal_buffers = 8 # min 4, 8KB each
# - Checkpoints -
! checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
! checkpoint_timeout = 300 # range 30-3600, in seconds
! checkpoint_warning = 30 # 0 is off, in seconds
! commit_delay = 0 # range 0-100000, in microseconds
! commit_siblings = 5 # range 1-1000
# - Archiving -
! archive_command = '' # command to use to archive a logfile segment
# If archive_command is '' then archiving is disabled. Otherwise, set it
# to a command to copy a file to the proper place. A simplistic example
***************
*** 131,167 ****
# - Planner Method Configuration -
! #enable_hashagg = true
! #enable_hashjoin = true
! #enable_indexscan = true
! #enable_mergejoin = true
! #enable_nestloop = true
! #enable_seqscan = true
! #enable_sort = true
! #enable_tidscan = true
# - Planner Cost Constants -
! #effective_cache_size = 1000 # typically 8KB each
! #random_page_cost = 4 # units are one sequential page fetch cost
! #cpu_tuple_cost = 0.01 # (same)
! #cpu_index_tuple_cost = 0.001 # (same)
! #cpu_operator_cost = 0.0025 # (same)
# - Genetic Query Optimizer -
! #geqo = true
! #geqo_threshold = 12
! #geqo_effort = 5 # range 1-10
! #geqo_pool_size = 0 # selects default based on effort
! #geqo_generations = 0 # selects default based on effort
! #geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
! #default_statistics_target = 10 # range 1-1000
! #from_collapse_limit = 8
! #join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
--- 137,173 ----
# - Planner Method Configuration -
! enable_hashagg = true
! enable_hashjoin = true
! enable_indexscan = true
! enable_mergejoin = true
! enable_nestloop = true
! enable_seqscan = true
! enable_sort = true
! enable_tidscan = true
# - Planner Cost Constants -
! effective_cache_size = 1000 # typically 8KB each
! random_page_cost = 4 # units are one sequential page fetch cost
! cpu_tuple_cost = 0.01 # (same)
! cpu_index_tuple_cost = 0.001 # (same)
! cpu_operator_cost = 0.0025 # (same)
# - Genetic Query Optimizer -
! geqo = true
! geqo_threshold = 12
! geqo_effort = 5 # range 1-10
! geqo_pool_size = 0 # selects default based on effort
! geqo_generations = 0 # selects default based on effort
! geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
! default_statistics_target = 10 # range 1-1000
! from_collapse_limit = 8
! join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
***************
*** 170,226 ****
# - Where to Log -
! #log_destination = 'stderr' # Valid values are combinations of stderr,
# syslog and eventlog, depending on
# platform.
# This is relevant when logging to stderr:
! #redirect_stderr = false # Enable capturing of stderr into log files.
# These are only relevant if redirect_stderr is true:
! #log_directory = 'pg_log' # Directory where logfiles are written.
# May be specified absolute or relative to PGDATA
! #log_filename_prefix = 'postgresql_' # Prefix for logfile names.
! #log_rotation_age = 1440 # Automatic rotation of logfiles will happen after
# so many minutes. 0 to disable.
! #log_rotation_size = 10240 # Automatic rotation of logfiles will happen after
# so many kilobytes of 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 = notice # 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 = panic # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)
-
- #log_min_duration_statement = -1 # -1 is disabled, in milliseconds.
! #silent_mode = false # DO NOT USE without syslog or redirect_stderr
# - What to Log -
! #debug_print_parse = false
! #debug_print_rewritten = false
! #debug_print_plan = false
! #debug_pretty_print = false
! #log_connections = false
! #log_disconnections = false
! #log_duration = false
! #log_line_prefix = '' # e.g. '<%u%%%d> '
# %u=user name %d=database name
# %r=remote host and port
# %p=PID %t=timestamp %i=command tag
--- 176,232 ----
# - Where to Log -
! log_destination = 'stderr' # Valid values are combinations of stderr,
# syslog and eventlog, depending on
# platform.
# This is relevant when logging to stderr:
! redirect_stderr = false # Enable capturing of stderr into log files.
# These are only relevant if redirect_stderr is true:
! log_directory = 'pg_log' # Directory where logfiles are written.
# May be specified absolute or relative to PGDATA
! log_filename_prefix = 'postgresql_' # Prefix for logfile names.
! log_rotation_age = 1440 # Automatic rotation of logfiles will happen after
# so many minutes. 0 to disable.
! log_rotation_size = 10240 # Automatic rotation of logfiles will happen after
# so many kilobytes of 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 = notice # 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 = panic # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)
! log_min_duration_statement = -1 # -1 is disabled, in milliseconds.
!
! silent_mode = false # DO NOT USE without syslog or redirect_stderr
# - What to Log -
! debug_print_parse = false
! debug_print_rewritten = false
! debug_print_plan = false
! debug_pretty_print = false
! log_connections = false
! log_disconnections = false
! log_duration = false
! log_line_prefix = '' # e.g. '<%u%%%d> '
# %u=user name %d=database name
# %r=remote host and port
# %p=PID %t=timestamp %i=command tag
***************
*** 228,235 ****
# %s=session start timestamp
# %x=stop here in non-session processes
# %%='%'
! #log_statement = 'none' # none, mod, ddl, all
! #log_hostname = false
#---------------------------------------------------------------------------
--- 234,241 ----
# %s=session start timestamp
# %x=stop here in non-session processes
# %%='%'
! log_statement = 'none' # none, mod, ddl, all
! log_hostname = false
#---------------------------------------------------------------------------
***************
*** 238,255 ****
# - Statistics Monitoring -
! #log_parser_stats = false
! #log_planner_stats = false
! #log_executor_stats = false
! #log_statement_stats = false
# - Query/Index Statistics Collector -
! #stats_start_collector = true
! #stats_command_string = false
! #stats_block_level = false
! #stats_row_level = false
! #stats_reset_on_server_start = true
#---------------------------------------------------------------------------
--- 244,261 ----
# - Statistics Monitoring -
! log_parser_stats = false
! log_planner_stats = false
! log_executor_stats = false
! log_statement_stats = false
# - Query/Index Statistics Collector -
! stats_start_collector = true
! stats_command_string = false
! stats_block_level = false
! stats_row_level = false
! stats_reset_on_server_start = true
#---------------------------------------------------------------------------
***************
*** 258,276 ****
# - Statement Behavior -
! #search_path = '$user,public' # schema names
! #check_function_bodies = true
! #default_transaction_isolation = 'read committed'
! #default_transaction_read_only = false
! #statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
! #datestyle = 'iso, mdy'
! #timezone = unknown # actually, defaults to TZ environment setting
! #australian_timezones = false
! #extra_float_digits = 0 # min -15, max 2
! #client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they might be changed
#lc_messages = 'C' # locale for system error message strings
--- 264,284 ----
# - Statement Behavior -
! search_path = '$user,public' # schema names
! check_function_bodies = true
! default_transaction_isolation = 'read committed'
! default_transaction_read_only = false
! statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
! datestyle = 'iso, mdy'
! #timezone = {enviroment} # actually, defaults to TZ environment setting
! # set at initdb time
! australian_timezones = false
! extra_float_digits = 0 # min -15, max 2
! #client_encoding = {db encoding} # actually, defaults to database encoding
! # common: UNICODE, SQL_ASCII
# These settings are initialized by initdb -- they might be changed
#lc_messages = 'C' # locale for system error message strings
***************
*** 280,295 ****
# - Other Defaults -
! #explain_pretty_print = true
! #dynamic_library_path = '$libdir'
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
! #deadlock_timeout = 1000 # in milliseconds
! #max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#---------------------------------------------------------------------------
--- 288,303 ----
# - Other Defaults -
! explain_pretty_print = true
! dynamic_library_path = '$libdir'
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
! deadlock_timeout = 1000 # in milliseconds
! max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#---------------------------------------------------------------------------
***************
*** 298,308 ****
# - Previous Postgres Versions -
! #add_missing_from = true
! #regex_flavor = advanced # advanced, extended, or basic
! #sql_inheritance = true
! #default_with_oids = true
# - Other Platforms & Clients -
! #transform_null_equals = false
--- 306,316 ----
# - Previous Postgres Versions -
! add_missing_from = false
! regex_flavor = advanced # advanced, extended, or basic
! sql_inheritance = true
! default_with_oids = true
# - Other Platforms & Clients -
! transform_null_equals = false
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
