<!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.3.9 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="2015-06-13T20:07:22"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.3.9 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 18. 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"
>18.1. Setting Parameters</A
></H1
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="CONFIG-SETTING-NAMES-VALUES"
>18.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, integer, floating point,
string or enum. Boolean 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 these.
</P
><P
> 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 <TT
CLASS="STRUCTNAME"
>pg_settings</TT
>.<TT
CLASS="STRUCTFIELD"
>unit</TT
>.
For convenience,
a different unit can also be specified explicitly. Valid memory units
are <TT
CLASS="LITERAL"
>kB</TT
> (kilobytes), <TT
CLASS="LITERAL"
>MB</TT
>
(megabytes), and <TT
CLASS="LITERAL"
>GB</TT
> (gigabytes); 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). Note that the multiplier
for memory units is 1024, not 1000.
</P
><P
> Parameters of type <SPAN
CLASS="QUOTE"
>"enum"</SPAN
> 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 <TT
CLASS="STRUCTNAME"
>pg_settings</TT
>.<TT
CLASS="STRUCTFIELD"
>enumvals</TT
>.
Enum parameter values are case-insensitive.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="CONFIG-SETTING-CONFIGURATION-FILE"
>18.1.2. Setting Parameters via the Configuration File</A
></H2
><P
> One 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 there 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 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.
</P
><P
>
The configuration file is reread whenever the main server process
receives a <SPAN
CLASS="SYSTEMITEM"
>SIGHUP</SPAN
> signal; this is most easily done 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 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
<SPAN
CLASS="SYSTEMITEM"
>SIGHUP</SPAN
> processing.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="CONFIG-SETTING-OTHER-METHODS"
>18.1.3. Other Ways to Set Parameters</A
></H2
><P
> A second way to set these configuration parameters is to give them
as a command-line option to the <TT
CLASS="COMMAND"
>postgres</TT
> command,
such as:
</P><PRE
CLASS="PROGRAMLISTING"
>postgres -c log_connections=yes -c log_destination='syslog'</PRE
><P>
Command-line options override any conflicting settings in
<TT
CLASS="FILENAME"
>postgresql.conf</TT
>. Note that this means you won't
be able to change the value on-the-fly by editing
<TT
CLASS="FILENAME"
>postgresql.conf</TT
>, so while the command-line
method might be convenient, it can cost you flexibility later.
</P
><P
> Occasionally it is useful to give a command line option to
one particular session only. The environment variable
<TT
CLASS="ENVAR"
>PGOPTIONS</TT
> can be used for this purpose on the
client side:
</P><PRE
CLASS="PROGRAMLISTING"
>env PGOPTIONS='-c geqo=off' psql</PRE
><P>
(This works for any <SPAN
CLASS="APPLICATION"
>libpq</SPAN
>-based client application, not
just <SPAN
CLASS="APPLICATION"
>psql</SPAN
>.) Note that this won't work for
parameters that are fixed when the server is started or that must be
specified in <TT
CLASS="FILENAME"
>postgresql.conf</TT
>.
</P
><P
> 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 <A
HREF="sql-alterrole.html"
>ALTER ROLE</A
>
and <A
HREF="sql-alterdatabase.html"
>ALTER DATABASE</A
>,
respectively, are used to configure these settings. Per-database
settings override anything received from the
<TT
CLASS="COMMAND"
>postgres</TT
> command-line or the configuration
file, and in turn are overridden by per-user settings; both are
overridden by per-session settings.
</P
><P
> Some parameters can be changed in individual <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
>
sessions with the <A
HREF="sql-set.html"
>SET</A
>
command, for example:
</P><PRE
CLASS="SCREEN"
>SET ENABLE_SEQSCAN TO OFF;</PRE
><P>
If <TT
CLASS="COMMAND"
>SET</TT
> is allowed, it overrides all other sources of
values for the parameter. Some parameters cannot be changed via
<TT
CLASS="COMMAND"
>SET</TT
>: for example, if they control behavior that
cannot be changed without restarting the entire
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> server. Also, some parameters
require superuser permission to change via <TT
CLASS="COMMAND"
>SET</TT
> or
<TT
CLASS="COMMAND"
>ALTER</TT
>.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="CONFIG-SETTING-EXAMINING"
>18.1.4. Examining Parameter Settings</A
></H2
><P
> The <A
HREF="sql-show.html"
>SHOW</A
>
command allows inspection of the current values of all parameters.
</P
><P
> The virtual table <TT
CLASS="STRUCTNAME"
>pg_settings</TT
> also allows
displaying and updating session run-time parameters; see <A
HREF="view-pg-settings.html"
>Section 47.66</A
> for details and a description of the
different variable types and when they can be changed.
<TT
CLASS="STRUCTNAME"
>pg_settings</TT
> is equivalent to <TT
CLASS="COMMAND"
>SHOW</TT
>
and <TT
CLASS="COMMAND"
>SET</TT
>, 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 <TT
CLASS="COMMAND"
>SHOW</TT
>.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="CONFIG-INCLUDES"
>18.1.5. Configuration File Includes</A
></H2
><P
>
In addition to 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 for the behavior
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. It is used similarly:
</P><PRE
CLASS="PROGRAMLISTING"
> include_dir 'directory'
</PRE
><P>
Non-absolute directory names follow the same rules as single file include
directives: they are relative to the directory containing the referencing
configuration file. Within that 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 excluded,
to prevent mistakes as they are hidden on some platforms. Multiple files
within an include directory are processed in file name order. The file names
are ordered by C locale rules, ie. 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="SCREEN"
> 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="SCREEN"
> 00shared.conf
01memory.conf
02server.conf
</PRE
><P>
This shows a clear order in which these files will be loaded. This is
important because only the last setting encountered when the server is
reading its configuration will be used. Something set in
<TT
CLASS="FILENAME"
>conf.d/02server.conf</TT
> in this example would override a value
set in <TT
CLASS="FILENAME"
>conf.d/01memory.conf</TT
>.
</P
><P
> You might instead use this configuration directory approach while naming
these files more descriptively:
</P><PRE
CLASS="SCREEN"
> 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
>
