Sophie: postgresql9.6-docs-9.6.22-1.mga7 noarch

postgresql9.6-docs-9.6.22-1.mga7.noarch.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Setting Parameters</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.6.22 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Server Configuration"
HREF="runtime-config.html"><LINK
REL="PREVIOUS"
TITLE="Server Configuration"
HREF="runtime-config.html"><LINK
REL="NEXT"
TITLE="File Locations"
HREF="runtime-config-file-locations.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2021-05-18T09:16:10"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="4"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.6.22 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Server Configuration"
HREF="runtime-config.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="runtime-config.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 19. Server Configuration</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="File Locations"
HREF="runtime-config-file-locations.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="CONFIG-SETTING"
>19.1. Setting Parameters</A
></H1
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="CONFIG-SETTING-NAMES-VALUES"
>19.1.1. Parameter Names and Values</A
></H2
><P
>     All parameter names are case-insensitive. Every parameter takes a
     value of one of five types: boolean, string, integer, floating point,
     or enumerated (enum).  The type determines the syntax for setting the
     parameter:
    </P
><P
></P
><UL
><LI
><P
>       <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>Boolean:</I
></SPAN
>
       Values can be written as
       <TT
CLASS="LITERAL"
>on</TT
>,
       <TT
CLASS="LITERAL"
>off</TT
>,
       <TT
CLASS="LITERAL"
>true</TT
>,
       <TT
CLASS="LITERAL"
>false</TT
>,
       <TT
CLASS="LITERAL"
>yes</TT
>,
       <TT
CLASS="LITERAL"
>no</TT
>,
       <TT
CLASS="LITERAL"
>1</TT
>,
       <TT
CLASS="LITERAL"
>0</TT
>
       (all case-insensitive) or any unambiguous prefix of one of these.
      </P
></LI
><LI
><P
>       <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>String:</I
></SPAN
>
       In general, enclose the value in single quotes, doubling any single
       quotes within the value.  Quotes can usually be omitted if the value
       is a simple number or identifier, however.
      </P
></LI
><LI
><P
>       <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>Numeric (integer and floating point):</I
></SPAN
>
       A decimal point is permitted only for floating-point parameters.
       Do not use thousands separators.  Quotes are not required.
      </P
></LI
><LI
><P
>       <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>Numeric with Unit:</I
></SPAN
>
       Some numeric parameters have an implicit unit, because they describe
       quantities of memory or time. The unit might be kilobytes, blocks
       (typically eight kilobytes), milliseconds, seconds, or minutes.
       An unadorned numeric value for one of these settings will use the
       setting's default unit, which can be learned from
       <TT
CLASS="STRUCTNAME"
>pg_settings</TT
>.<TT
CLASS="STRUCTFIELD"
>unit</TT
>.
       For convenience, settings can be given with a unit specified explicitly,
       for example <TT
CLASS="LITERAL"
>'120 ms'</TT
> for a time value, and they will be
       converted to whatever the parameter's actual unit is.  Note that the
       value must be written as a string (with quotes) to use this feature.
       The unit name is case-sensitive, and there can be whitespace between
       the numeric value and the unit.

       <P
></P
></P><UL
><LI
><P
>          Valid memory units are <TT
CLASS="LITERAL"
>kB</TT
> (kilobytes),
          <TT
CLASS="LITERAL"
>MB</TT
> (megabytes), <TT
CLASS="LITERAL"
>GB</TT
>
          (gigabytes), and <TT
CLASS="LITERAL"
>TB</TT
> (terabytes).
          The multiplier for memory units is 1024, not 1000.
         </P
></LI
><LI
><P
>          Valid time units are <TT
CLASS="LITERAL"
>ms</TT
> (milliseconds),
          <TT
CLASS="LITERAL"
>s</TT
> (seconds), <TT
CLASS="LITERAL"
>min</TT
> (minutes),
          <TT
CLASS="LITERAL"
>h</TT
> (hours), and <TT
CLASS="LITERAL"
>d</TT
> (days).
         </P
></LI
></UL
><P>
      </P
></LI
><LI
><P
>       <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>Enumerated:</I
></SPAN
>
       Enumerated-type parameters are written in the same way as string
       parameters, but are restricted to have one of a limited set of
       values.  The values allowable for such a parameter can be found from
       <TT
CLASS="STRUCTNAME"
>pg_settings</TT
>.<TT
CLASS="STRUCTFIELD"
>enumvals</TT
>.
       Enum parameter values are case-insensitive.
      </P
></LI
></UL
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="CONFIG-SETTING-CONFIGURATION-FILE"
>19.1.2. Parameter Interaction via the Configuration File</A
></H2
><P
>     The most fundamental way to set these parameters is to edit the file
     <TT
CLASS="FILENAME"
>postgresql.conf</TT
>,
     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:
</P><PRE
CLASS="PROGRAMLISTING"
># This is a comment
log_connections = yes
log_destination = 'syslog'
search_path = '"$user", public'
shared_buffers = 128MB</PRE
><P>
     One parameter is specified per line. The equal sign between name and
     value is optional. Whitespace is insignificant (except within a quoted
     parameter value) and blank lines are
     ignored. Hash marks (<TT
CLASS="LITERAL"
>#</TT
>) 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.
     If the file contains multiple entries for the same parameter,
     all but the last one are ignored.
    </P
><P
>     Parameters set in this way provide default values for the cluster.
     The settings seen by active sessions will be these values unless they
     are overridden.  The following sections describe ways in which the
     administrator or user can override these defaults.
    </P
><P
>     
     The configuration file is reread whenever the main server process
     receives a <SPAN
CLASS="SYSTEMITEM"
>SIGHUP</SPAN
> signal; this signal is most easily
     sent by running <TT
CLASS="LITERAL"
>pg_ctl reload</TT
> from the command line or by
     calling the SQL function <CODE
CLASS="FUNCTION"
>pg_reload_conf()</CODE
>. The main
     server process also propagates this signal to all currently running
     server processes, so that existing sessions also adopt the new values
     (this will happen after they complete any currently-executing client
     command).  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 <SPAN
CLASS="SYSTEMITEM"
>SIGHUP</SPAN
> processing.
    </P
><P
>     In addition to <TT
CLASS="FILENAME"
>postgresql.conf</TT
>,
     a <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> data directory contains a file
     <TT
CLASS="FILENAME"
>postgresql.auto.conf</TT
>,
     which has the same format as <TT
CLASS="FILENAME"
>postgresql.conf</TT
> but
     is intended to be edited automatically not manually.  This file holds
     settings provided through the <A
HREF="sql-altersystem.html"
>ALTER SYSTEM</A
> command.
     This file is read whenever <TT
CLASS="FILENAME"
>postgresql.conf</TT
> is,
     and its settings take effect in the same way.  Settings
     in <TT
CLASS="FILENAME"
>postgresql.auto.conf</TT
> override those
     in <TT
CLASS="FILENAME"
>postgresql.conf</TT
>.
    </P
><P
>     External tools may also
     modify <TT
CLASS="FILENAME"
>postgresql.auto.conf</TT
>.  It is not
     recommended to do this while the server is running, since a
     concurrent <TT
CLASS="COMMAND"
>ALTER SYSTEM</TT
> command could overwrite
     such changes.  Such tools might simply append new settings to the end,
     or they might choose to remove duplicate settings and/or comments
     (as <TT
CLASS="COMMAND"
>ALTER SYSTEM</TT
> will).
    </P
><P
>     The system view
     <A
HREF="view-pg-file-settings.html"
><TT
CLASS="STRUCTNAME"
>pg_file_settings</TT
></A
>
     can be helpful for pre-testing changes to the configuration files, or for
     diagnosing problems if a <SPAN
CLASS="SYSTEMITEM"
>SIGHUP</SPAN
> signal did not have the
     desired effects.
    </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="CONFIG-SETTING-SQL-COMMAND-INTERACTION"
>19.1.3. Parameter Interaction via SQL</A
></H2
><P
>      <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> provides three SQL
      commands to establish configuration defaults.
      The already-mentioned <A
HREF="sql-altersystem.html"
>ALTER SYSTEM</A
> command
      provides a SQL-accessible means of changing global defaults; it is
      functionally equivalent to editing <TT
CLASS="FILENAME"
>postgresql.conf</TT
>.
      In addition, there are two commands that allow setting of defaults
      on a per-database or per-role basis:
     </P
><P
></P
><UL
><LI
><P
>       The <A
HREF="sql-alterdatabase.html"
>ALTER DATABASE</A
> command allows global
       settings to be overridden on a per-database basis.
      </P
></LI
><LI
><P
>       The <A
HREF="sql-alterrole.html"
>ALTER ROLE</A
> command allows both global and
       per-database settings to be overridden with user-specific values.
      </P
></LI
></UL
><P
>      Values set with <TT
CLASS="COMMAND"
>ALTER DATABASE</TT
> and <TT
CLASS="COMMAND"
>ALTER ROLE</TT
>
      are applied only when starting a fresh database session.  They
      override values obtained from the configuration files or server
      command line, and constitute defaults for the rest of the session.
      Note that some settings cannot be changed after server start, and
      so cannot be set with these commands (or the ones listed below).
    </P
><P
>      Once a client is connected to the database, <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
      provides two additional SQL commands (and equivalent functions) to
      interact with session-local configuration settings:
    </P
><P
></P
><UL
><LI
><P
>      The <A
HREF="sql-show.html"
>SHOW</A
> command allows inspection of the
      current value of all parameters.  The corresponding function is
      <CODE
CLASS="FUNCTION"
>current_setting(setting_name text)</CODE
>.
     </P
></LI
><LI
><P
>       The <A
HREF="sql-set.html"
>SET</A
> command allows modification of the
       current value of those parameters that can be set locally to a
       session; it has no effect on other sessions.
       The corresponding function is
       <CODE
CLASS="FUNCTION"
>set_config(setting_name, new_value, is_local)</CODE
>.
      </P
></LI
></UL
><P
>     In addition, the system view <A
HREF="view-pg-settings.html"
><TT
CLASS="STRUCTNAME"
>pg_settings</TT
></A
> can be
     used to view and change session-local values:
    </P
><P
></P
><UL
><LI
><P
>       Querying this view is similar to using <TT
CLASS="COMMAND"
>SHOW ALL</TT
> but
       provides more detail.  It is also more flexible, since it's possible
       to specify filter conditions or join against other relations.
      </P
></LI
><LI
><P
>       Using <A
HREF="sql-update.html"
>UPDATE</A
> on this view, specifically
       updating the <TT
CLASS="STRUCTNAME"
>setting</TT
> column, is the equivalent
       of issuing <TT
CLASS="COMMAND"
>SET</TT
> commands.  For example, the equivalent of
</P><PRE
CLASS="PROGRAMLISTING"
>SET configuration_parameter TO DEFAULT;</PRE
><P>
       is:
</P><PRE
CLASS="PROGRAMLISTING"
>UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';</PRE
><P>
      </P
></LI
></UL
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN32816"
>19.1.4. Parameter Interaction via the Shell</A
></H2
><P
>      In addition to setting global defaults or attaching
      overrides at the database or role level, you can pass settings to
      <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> via shell facilities.
      Both the server and <SPAN
CLASS="APPLICATION"
>libpq</SPAN
> client library
      accept parameter values via the shell.
     </P
><P
></P
><UL
><LI
><P
>       During server startup, parameter settings can be
       passed to the <TT
CLASS="COMMAND"
>postgres</TT
> command via the
       <TT
CLASS="OPTION"
>-c</TT
> command-line parameter.  For example,
</P><PRE
CLASS="PROGRAMLISTING"
>postgres -c log_connections=yes -c log_destination='syslog'</PRE
><P>
       Settings provided in this way override those set via
       <TT
CLASS="FILENAME"
>postgresql.conf</TT
> or <TT
CLASS="COMMAND"
>ALTER SYSTEM</TT
>,
       so they cannot be changed globally without restarting the server.
     </P
></LI
><LI
><P
>      When starting a client session via <SPAN
CLASS="APPLICATION"
>libpq</SPAN
>,
      parameter settings can be
      specified using the <TT
CLASS="ENVAR"
>PGOPTIONS</TT
> environment variable.
      Settings established in this way constitute defaults for the life
      of the session, but do not affect other sessions.
      For historical reasons, the format of <TT
CLASS="ENVAR"
>PGOPTIONS</TT
> is
      similar to that used when launching the <TT
CLASS="COMMAND"
>postgres</TT
>
      command; specifically, the <TT
CLASS="OPTION"
>-c</TT
> flag must be specified.
      For example,
</P><PRE
CLASS="PROGRAMLISTING"
>env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql</PRE
><P>
     </P
><P
>      Other clients and libraries might provide their own mechanisms,
      via the shell or otherwise, that allow the user to alter session
      settings without direct use of SQL commands.
     </P
></LI
></UL
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="CONFIG-INCLUDES"
>19.1.5. Managing Configuration File Contents</A
></H2
><P
>      <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> provides several features for breaking
      down complex <TT
CLASS="FILENAME"
>postgresql.conf</TT
> files into sub-files.
      These features are especially useful when managing multiple servers
      with related, but not identical, configurations.
     </P
><P
>      
      In addition to individual parameter settings,
      the <TT
CLASS="FILENAME"
>postgresql.conf</TT
> file can contain <I
CLASS="FIRSTTERM"
>include
      directives</I
>, which specify another file to read and process as if
      it were inserted into the configuration file at this point.  This
      feature allows a configuration file to be divided into physically
      separate parts.  Include directives simply look like:
</P><PRE
CLASS="PROGRAMLISTING"
>include 'filename'</PRE
><P>
      If the file name is not an absolute path, it is taken as relative to
      the directory containing the referencing configuration file.
      Inclusions can be nested.
     </P
><P
>      
      There is also an <TT
CLASS="LITERAL"
>include_if_exists</TT
> directive, which acts
      the same as the <TT
CLASS="LITERAL"
>include</TT
> directive, except
      when the referenced file does not exist or cannot be read.  A regular
      <TT
CLASS="LITERAL"
>include</TT
> will consider this an error condition, but
      <TT
CLASS="LITERAL"
>include_if_exists</TT
> merely logs a message and continues
      processing the referencing configuration file.
     </P
><P
>      
      The <TT
CLASS="FILENAME"
>postgresql.conf</TT
> file can also contain
      <TT
CLASS="LITERAL"
>include_dir</TT
> directives, which specify an entire
      directory of configuration files to include.  These look like
</P><PRE
CLASS="PROGRAMLISTING"
>include_dir 'directory'</PRE
><P>
      Non-absolute directory names are taken as relative to the directory
      containing the referencing configuration file.  Within the specified
      directory, only non-directory files whose names end with the
      suffix <TT
CLASS="LITERAL"
>.conf</TT
> will be included.  File names that
      start with the <TT
CLASS="LITERAL"
>.</TT
> character are also ignored, to
      prevent mistakes since such files are hidden on some platforms.  Multiple
      files within an include directory are processed in file name order
      (according to C locale rules, i.e., numbers before letters, and
      uppercase letters before lowercase ones).
     </P
><P
>      Include files or directories can be used to logically separate portions
      of the database configuration, rather than having a single large
      <TT
CLASS="FILENAME"
>postgresql.conf</TT
> file.  Consider a company that has two
      database servers, each with a different amount of memory.  There are
      likely elements of the configuration both will share, for things such
      as logging.  But memory-related parameters on the server will vary
      between the two.  And there might be server specific customizations,
      too.  One way to manage this situation is to break the custom
      configuration changes for your site into three files.  You could add
      this to the end of your <TT
CLASS="FILENAME"
>postgresql.conf</TT
> file to include
      them:
</P><PRE
CLASS="PROGRAMLISTING"
>include 'shared.conf'
include 'memory.conf'
include 'server.conf'</PRE
><P>
      All systems would have the same <TT
CLASS="FILENAME"
>shared.conf</TT
>.  Each
      server with a particular amount of memory could share the
      same <TT
CLASS="FILENAME"
>memory.conf</TT
>; you might have one for all servers
      with 8GB of RAM, another for those having 16GB.  And
      finally <TT
CLASS="FILENAME"
>server.conf</TT
> could have truly server-specific
      configuration information in it.
     </P
><P
>      Another possibility is to create a configuration file directory and
      put this information into files there. For example, a <TT
CLASS="FILENAME"
>conf.d</TT
>
      directory could be referenced at the end of <TT
CLASS="FILENAME"
>postgresql.conf</TT
>:
</P><PRE
CLASS="PROGRAMLISTING"
>include_dir 'conf.d'</PRE
><P>
      Then you could name the files in the <TT
CLASS="FILENAME"
>conf.d</TT
> directory
      like this:
</P><PRE
CLASS="PROGRAMLISTING"
>00shared.conf
01memory.conf
02server.conf</PRE
><P>
       This naming convention establishes a clear order in which these
       files will be loaded.  This is important because only the last
       setting encountered for a particular parameter while the server is
       reading configuration files will be used.  In this example,
       something set in <TT
CLASS="FILENAME"
>conf.d/02server.conf</TT
> would override a
       value set in <TT
CLASS="FILENAME"
>conf.d/01memory.conf</TT
>.
     </P
><P
>      You might instead use this approach to naming the files
      descriptively:
</P><PRE
CLASS="PROGRAMLISTING"
>00shared.conf
01memory-8GB.conf
02server-foo.conf</PRE
><P>
      This sort of arrangement gives a unique name for each configuration file
      variation.  This can help eliminate ambiguity when several servers have
      their configurations all stored in one place, such as in a version
      control repository.  (Storing database configuration files under version
      control is another good practice to consider.)
     </P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="runtime-config.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="runtime-config-file-locations.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Server Configuration</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="runtime-config.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>File Locations</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>