On Thu, Jun 12, 2014 at 11:32:49AM -0400, David Johnston wrote:
> This addition still needs some work as well as the patch as a whole (see my
> comments above). The attached PDF is the page that results from "make html";
> the original patch has been re-attached as well and -hackers copied given the
> lack of response on -doc.
>
> I am hoping this change would be something that could be included in 9.4; I
> believe the enhancements would be most beneficial to newcomers who are just
> getting started with PostgreSQL and would be using 9.4 as their starting
> point.
[hackers CC removed]
I have reviewed and updated this reworking of our configuration settings
documentation and feel it is ready for application to head and 9.4;
attached.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
new file mode 100644
index 49547ee..156c264
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 11,17 ****
<para>
There are many configuration parameters that affect the behavior of
the database system. In the first section of this chapter, we
! describe how to set configuration parameters. The subsequent sections
discuss each parameter in detail.
</para>
--- 11,17 ----
<para>
There are many configuration parameters that affect the behavior of
the database system. In the first section of this chapter, we
! describe how to interact with configuration parameters. The subsequent sections
discuss each parameter in detail.
</para>
***************
*** 23,69 ****
<para>
All parameter names are case-insensitive. Every parameter takes a
! value of one of five types: Boolean, integer, floating point,
! string or enum. Boolean values can be written as <literal>on</literal>,
! <literal>off</literal>, <literal>true</literal>,
! <literal>false</literal>, <literal>yes</literal>,
! <literal>no</literal>, <literal>1</literal>, <literal>0</literal>
! (all case-insensitive) or any unambiguous prefix of these.
</para>
! <para>
! Some settings specify a memory or time value. Each of these has an
! implicit unit, which is either kilobytes, blocks (typically eight
! kilobytes), milliseconds, seconds, or minutes. Default units can be
! found by referencing <structname>pg_settings</>.<structfield>unit</>.
! For convenience,
! a different unit can also be specified explicitly. Valid memory units
! are <literal>kB</literal> (kilobytes), <literal>MB</literal>
! (megabytes), <literal>GB</literal> (gigabytes), and <literal>TB</literal> (terabytes); valid time units
! are <literal>ms</literal> (milliseconds), <literal>s</literal>
! (seconds), <literal>min</literal> (minutes), <literal>h</literal>
! (hours), and <literal>d</literal> (days). Note that the multiplier
! for memory units is 1024, not 1000.
! </para>
! <para>
! Parameters of type <quote>enum</> are specified in the same way as string
! parameters, but are restricted to a limited set of values. The allowed
! values can be found
! from <structname>pg_settings</>.<structfield>enumvals</>.
! Enum parameter values are case-insensitive.
! </para>
</sect2>
<sect2 id="config-setting-configuration-file">
! <title>Setting Parameters via the Configuration File</title>
<para>
! One way to set these parameters is to edit the file
<filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>,
which is normally kept in the data directory. (A default copy is
! installed there when the database cluster directory is
! initialized.) An example of what this file might look like is:
<programlisting>
# This is a comment
log_connections = yes
--- 23,121 ----
<para>
All parameter names are case-insensitive. Every parameter takes a
! value of one of five types: boolean, integer, floating point,
! string, or enum.
</para>
! <itemizedlist>
! <listitem>
! <para>
! <emphasis>Boolean</emphasis>: Values can be written as
! <literal>on</literal>,
! <literal>off</literal>,
! <literal>true</literal>,
! <literal>false</literal>,
! <literal>yes</literal>,
! <literal>no</literal>,
! <literal>1</literal>,
! <literal>0</literal>
! (all case-insensitive) or any unambiguous prefix of these.
! </para>
! </listitem>
!
! <listitem>
! <para>
! <emphasis>String:</emphasis> Enclose the value in
! single-quotes. Values are case-insensitive. If multiple values
! are allowed, separate them with commas.
! </para>
! </listitem>
!
! <listitem>
! <para>
! <emphasis>Numeric</emphasis> (integer and floating point): Do not use
! single-quotes (unless otherwise required) or thousand separators.
! </para>
! </listitem>
!
! <listitem>
! <para>
! <emphasis>Numeric or String with Unit (Memory &
! Time):</emphasis> These have an implicit unit, which is
! either kilobytes, blocks (typically eight kilobytes),
! milliseconds, seconds, or minutes. An numeric value
! will use the default, which can be found by referencing
! <structname>pg_settings</>.<structfield>unit</>. For convenience,
! a different unit can also be specified explicitly via a string
! value. It is case-sensitive and may include whitespace between
! the value and the unit.
!
! <itemizedlist>
! <listitem>
! <para>
! Valid memory units are <literal>kB</literal> (kilobytes),
! <literal>MB</literal> (megabytes), <literal>GB</literal>
! (gigabytes), and <literal>TB</literal> (terabytes).
! The multiplier for memory units is 1024, not 1000.
! </para>
! </listitem>
!
! <listitem>
! <para>
! Valid time units are <literal>ms</literal> (milliseconds),
! <literal>s</literal> (seconds), <literal>min</literal> (minutes),
! <literal>h</literal> (hours), and <literal>d</literal> (days).
! </para>
! </listitem>
! </itemizedlist>
! </para>
! </listitem>
!
! <listitem>
! <para>
! <emphasis><quote>enum</>:</emphasis> These are specified
! in the same way as string parameters, but are restricted
! to a limited set of values that can be queried from
! <structname>pg_settings</>.<structfield>enumvals</>:
! <programlisting>
! SELECT name, setting, enumvals FROM pg_settings WHERE enumvals IS NOT NULL;
! </programlisting>
! Enum parameter values are case-insensitive.
! </para>
! </listitem>
! </itemizedlist>
</sect2>
<sect2 id="config-setting-configuration-file">
! <title>Parameter Interaction via Configuration File</title>
<para>
! The primary way to set these parameters is to edit the file
<filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>,
which is normally kept in the data directory. (A default copy is
! installed when the database cluster directory is initialized.)
! An example of what this file might look like is:
<programlisting>
# This is a comment
log_connections = yes
*************** log_destination = 'syslog'
*** 71,197 ****
search_path = '"$user", public'
shared_buffers = 128MB
</programlisting>
! One parameter is specified per line. The equal sign between name and
! value is optional. Whitespace is insignificant and blank lines are
! ignored. Hash marks (<literal>#</literal>) designate the remainder of the
! line as a comment. Parameter values that are not simple identifiers or
! numbers must be single-quoted. To embed a single quote in a parameter
! value, write either two quotes (preferred) or backslash-quote.
</para>
<para>
<indexterm>
! <primary>SIGHUP</primary>
</indexterm>
The configuration file is reread whenever the main server process
receives a <systemitem>SIGHUP</> signal; this is most easily done by
running <literal>pg_ctl reload</> from the command-line or by calling
the SQL function <function>pg_reload_conf()</function>. The main
! server process
! also propagates this signal to all currently running server
! processes so that existing sessions also get the new
! value. Alternatively, you can send the signal to a single server
! process directly. Some parameters can only be set at server start;
! any changes to their entries in the configuration file will be ignored
! until the server is restarted. Invalid parameter settings in the
! configuration file are likewise ignored (but logged) during
! <systemitem>SIGHUP</> processing.
</para>
</sect2>
! <sect2 id="config-setting-other-methods">
! <title>Other Ways to Set Parameters</title>
! <para>
! A second way to set these configuration parameters is to give them
! as a command-line option to the <command>postgres</command> command,
! such as:
! <programlisting>
! postgres -c log_connections=yes -c log_destination='syslog'
! </programlisting>
! Command-line options override any conflicting settings in
! <filename>postgresql.conf</filename>. Note that this means you won't
! be able to change the value on-the-fly by editing
! <filename>postgresql.conf</filename>, so while the command-line
! method might be convenient, it can cost you flexibility later.
! </para>
! <para>
! Occasionally it is useful to give a command line option to
! one particular session only. The environment variable
! <envar>PGOPTIONS</envar> can be used for this purpose on the
! client side:
! <programlisting>
! env PGOPTIONS='-c geqo=off' psql
! </programlisting>
! (This works for any <application>libpq</>-based client application, not
! just <application>psql</application>.) Note that this won't work for
! parameters that are fixed when the server is started or that must be
! specified in <filename>postgresql.conf</filename>.
! </para>
! <para>
! Furthermore, it is possible to assign a set of parameter settings to
! a user or a database. Whenever a session is started, the default
! settings for the user and database involved are loaded. The
! commands <xref linkend="sql-alterrole">
! and <xref linkend="sql-alterdatabase">,
! respectively, are used to configure these settings. Per-database
! settings override anything received from the
! <command>postgres</command> command-line or the configuration
! file, and in turn are overridden by per-user settings; both are
! overridden by per-session settings.
</para>
<para>
! Some parameters can be changed in individual <acronym>SQL</acronym>
! sessions with the <xref linkend="SQL-SET">
! command, for example:
! <screen>
! SET ENABLE_SEQSCAN TO OFF;
! </screen>
! If <command>SET</> is allowed, it overrides all other sources of
! values for the parameter. Some parameters cannot be changed via
! <command>SET</command>: for example, if they control behavior that
! cannot be changed without restarting the entire
! <productname>PostgreSQL</productname> server. Also, some parameters
! require superuser permission to change via <command>SET</command> or
! <command>ALTER</>.
</para>
! <para>
! Another way to change configuration parameters persistently is by
! use of <xref linkend="SQL-ALTERSYSTEM">
! command, for example:
! <screen>
! ALTER SYSTEM SET checkpoint_timeout TO 600;
! </screen>
! This command will allow users to change values persistently
! through SQL command. The values will be effective after reload of server configuration
! (<acronym>SIGHUP</>) or server startup. The effect of this command is similar to when
! user manually changes values in <filename>postgresql.conf</filename>.
</para>
</sect2>
! <sect2 id="config-setting-examining">
! <title>Examining Parameter Settings</title>
! <para>
! The <xref linkend="SQL-SHOW">
! command allows inspection of the current values of all parameters.
! </para>
! <para>
! The virtual table <structname>pg_settings</structname> also allows
! displaying and updating session run-time parameters; see <xref
! linkend="view-pg-settings"> for details and a description of the
! different variable types and when they can be changed.
! <structname>pg_settings</structname> is equivalent to <command>SHOW</>
! and <command>SET</>, but can be more convenient
! to use because it can be joined with other tables, or selected from using
! any desired selection condition. It also contains more information about
! each parameter than is available from <command>SHOW</>.
! </para>
</sect2>
--- 123,310 ----
search_path = '"$user", public'
shared_buffers = 128MB
</programlisting>
! One parameter is specified per line. The equal sign between name
! and value is optional. Whitespace is insignificant and blank
! lines are ignored. Hash marks (<literal>#</literal>) designate the
! remainder of lines as comments. Parameter values that are not simple
! identifiers or numbers must be single-quoted. To embed a single
! quote in a parameter value, write either two quotes (preferred)
! or backslash-quote.
! </para>
!
! <para>
! Parameters set in this way provide default values for the cluster.
! The setting seen by active sessions will be this value unless
! it is overridden. The following sections describe ways in which the
! administrator or user can override these defaults.
</para>
<para>
<indexterm>
! <primary>SIGHUP</primary>
</indexterm>
The configuration file is reread whenever the main server process
receives a <systemitem>SIGHUP</> signal; this is most easily done by
running <literal>pg_ctl reload</> from the command-line or by calling
the SQL function <function>pg_reload_conf()</function>. The main
! server process also propagates this signal to all currently running
! server processes so that existing sessions also get the new value
! when they complete their transactions. Alternatively, you can
! send the signal to a single server process directly. Some parameters
! can only be set at server start; any changes to their entries in the
! configuration file will be ignored until the server is restarted.
! Invalid parameter settings in the configuration file are likewise
! ignored (but logged) during <systemitem>SIGHUP</> processing.
</para>
</sect2>
! <sect2 id="config-setting-sql-command-interaction">
! <title>Parameter Interaction via SQL</title>
! <para>
! <productname>PostgreSQL</productname> provides three SQL
! commands to establish configuration defaults that override those
! configured globally. The evaluation of these defaults occurs
! at the beginning of a new session, upon the user issuing <xref
! linkend="SQL-DISCARD">, or if the server forces the session to
! reload its configuration after a <systemitem>SIGHUP</systemitem>
! signal.
! </para>
! <itemizedlist>
! <listitem>
! <para>
! The <xref linkend="SQL-ALTERSYSTEM"> command provides an
! SQL-accessible means of changing global defaults.
! </para>
! </listitem>
! <listitem>
! <para>
! The <xref linkend="sql-alterdatabase"> command allows database
! administrators to override global settings on a per-database basis.
! </para>
! </listitem>
! <listitem>
! <para>
! The <xref linkend="sql-alterrole"> command allows database
! administrators to override both global and per-database settings
! with user-specific values.
! </para>
! </listitem>
! </itemizedlist>
!
! <para>
! Once a client connects to the database PostgreSQL provides
! two additional SQL commands to interact with session-local
! configuration settings. Both of these commands have equivalent
! system administration functions.
</para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ The <xref linkend="SQL-SHOW"> command allows inspection of the
+ current value of all parameters. The corresponding function is
+ <function>current_setting(setting_name text)</function>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <xref linkend="SQL-SET"> command allows modification of the
+ current value of some parameters. The corresponding function is
+ <function>set_config(setting_name, new_value, is_local)</function>.
+ </para>
+ </listitem>
+ </itemizedlist>
+
<para>
! Both <command>SELECT</> and <command>UPDATE</>
! can be issued against the system view <link
! linkend="view-pg-settings"><structname>pg_settings</></> to view
! and change session-local values.
</para>
! <itemizedlist>
! <listitem>
! <para>
! Querying this view is the sames as <command>SHOW</> but provides
! more detail, as well as allowing for joins against other relations
! and the specification of filter criteria.
! </para>
! </listitem>
!
! <listitem>
! <para>
! Using <xref linkend="SQL-UPDATE"> on this relation, specifically
! updating the <structname>setting</> column, is the equivalent
! of issuing SQL <command>SET</>, though all values must be
! single-quoted. Note that the equivalent of
! <programlisting>
! SET configuration_parameter TO DEFAULT;
! </>
! is:
! <programlisting>
! UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';
! </programlisting>
</para>
+ </listitem>
+ </itemizedlist>
+
</sect2>
! <sect2>
! <title>Parameter Interaction via Shell</title>
! <para>
! In addition to setting global defaults or attaching
! overrides at the database or role level, you can pass setting to
! <productname>PostgreSQL</productname> via shell facilities.
! Both the server and <application>libpq</> client library
! accept parameter values via the shell.
! </para>
! <itemizedlist>
! <listitem>
! <para>
! On the <emphasis>server</emphasis>, command-line options can be
! passed to the <command>postgres</command> command directly via the
! <option>-c</> parameter.
! <programlisting>
! postgres -c log_connections=yes -c log_destination='syslog'
! </programlisting>
! Settings provided this way override those resolved globally (via
! <filename>postgresql.conf</> or <command>ALTER SYSTEM</>) but
! are otherwise treated as being global for the purpose of database
! and role overrides.
! </para>
! </listitem>
! <listitem>
! <para>
! On the <emphasis>libpq-client</emphasis>, command-line options can be
! specified using the <envar>PGOPTIONS</envar> environment variable.
! When connecting to the server, the contents of this variable are
! sent to the server as if they were being executed via SQL <xref
! linkend="SQL-SET"> at the beginning of the session.
! </para>
!
! <para>
! However, the format of <envar>PGOPTIONS</envar> is similar to that
! used when launching the <command>postgres</command> command.
! Specifically, the <option>-c</> flag must be specified.
! <programlisting>
! env PGOPTIONS="-c geqo=off -c statement_timeout='5 min'" psql
! </programlisting>
! </para>
!
! <para>
! Other clients and libraries might provide their own mechanisms,
! via the shell or otherwise, that allow the user to alter session
! settings without requiring the user to issue SQL commands.
! </para>
! </listitem>
! </itemizedlist>
</sect2>
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
