<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>19.1. Setting Parameters</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="runtime-config.html" title="Chapter 19. Server Configuration" /><link rel="next" href="runtime-config-file-locations.html" title="19.2. File Locations" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">19.1. Setting Parameters</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config.html" title="Chapter 19. Server Configuration">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 19. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-file-locations.html" title="19.2. File Locations">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="CONFIG-SETTING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.1. Setting Parameters</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-NAMES-VALUES">19.1.1. Parameter Names and Values</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE">19.1.2. Parameter Interaction via the Configuration File</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION">19.1.3. Parameter Interaction via SQL</a></span></dt><dt><span class="sect2"><a href="config-setting.html#id-1.6.6.4.5">19.1.4. Parameter Interaction via the Shell</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-INCLUDES">19.1.5. Managing Configuration File Contents</a></span></dt></dl></div><div class="sect2" id="CONFIG-SETTING-NAMES-VALUES"><div class="titlepage"><div><div><h3 class="title">19.1.1. Parameter Names and Values</h3></div></div></div><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><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
<span class="emphasis"><em>Boolean:</em></span>
Values can be written as
<code class="literal">on</code>,
<code class="literal">off</code>,
<code class="literal">true</code>,
<code class="literal">false</code>,
<code class="literal">yes</code>,
<code class="literal">no</code>,
<code class="literal">1</code>,
<code class="literal">0</code>
(all case-insensitive) or any unambiguous prefix of one of these.
</p></li><li class="listitem"><p>
<span class="emphasis"><em>String:</em></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 class="listitem"><p>
<span class="emphasis"><em>Numeric (integer and floating point):</em></span>
A decimal point is permitted only for floating-point parameters.
Do not use thousands separators. Quotes are not required.
</p></li><li class="listitem"><p>
<span class="emphasis"><em>Numeric with Unit:</em></span>
Some numeric parameters have an implicit unit, because they describe
quantities of memory or time. The unit might be bytes, 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
<code class="structname">pg_settings</code>.<code class="structfield">unit</code>.
For convenience, settings can be given with a unit specified explicitly,
for example <code class="literal">'120 ms'</code> 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><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
Valid memory units are <code class="literal">B</code> (bytes),
<code class="literal">kB</code> (kilobytes),
<code class="literal">MB</code> (megabytes), <code class="literal">GB</code>
(gigabytes), and <code class="literal">TB</code> (terabytes).
The multiplier for memory units is 1024, not 1000.
</p></li><li class="listitem"><p>
Valid time units are <code class="literal">ms</code> (milliseconds),
<code class="literal">s</code> (seconds), <code class="literal">min</code> (minutes),
<code class="literal">h</code> (hours), and <code class="literal">d</code> (days).
</p></li></ul></div><p>
</p></li><li class="listitem"><p>
<span class="emphasis"><em>Enumerated:</em></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
<code class="structname">pg_settings</code>.<code class="structfield">enumvals</code>.
Enum parameter values are case-insensitive.
</p></li></ul></div></div><div class="sect2" id="CONFIG-SETTING-CONFIGURATION-FILE"><div class="titlepage"><div><div><h3 class="title">19.1.2. Parameter Interaction via the Configuration File</h3></div></div></div><p>
The most fundamental way to set these parameters is to edit the file
<code class="filename">postgresql.conf</code><a id="id-1.6.6.4.3.2.2" class="indexterm"></a>,
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 (<code class="literal">#</code>) 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>
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>
<a id="id-1.6.6.4.3.4.1" class="indexterm"></a>
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 <code class="literal">pg_ctl reload</code> 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 <code class="filename">postgresql.conf</code>,
a <span class="productname">PostgreSQL</span> data directory contains a file
<code class="filename">postgresql.auto.conf</code><a id="id-1.6.6.4.3.5.4" class="indexterm"></a>,
which has the same format as <code class="filename">postgresql.conf</code> but should
never be edited manually. This file holds settings provided through
the <a class="xref" href="sql-altersystem.html" title="ALTER SYSTEM"><span class="refentrytitle">ALTER SYSTEM</span></a> command. This file is automatically
read whenever <code class="filename">postgresql.conf</code> is, and its settings take
effect in the same way. Settings in <code class="filename">postgresql.auto.conf</code>
override those in <code class="filename">postgresql.conf</code>.
</p><p>
The system view
<a class="link" href="view-pg-file-settings.html" title="52.69. pg_file_settings"><code class="structname">pg_file_settings</code></a>
can be helpful for pre-testing changes to the configuration file, or for
diagnosing problems if a <span class="systemitem">SIGHUP</span> signal did not have the
desired effects.
</p></div><div class="sect2" id="CONFIG-SETTING-SQL-COMMAND-INTERACTION"><div class="titlepage"><div><div><h3 class="title">19.1.3. Parameter Interaction via SQL</h3></div></div></div><p>
<span class="productname">PostgreSQL</span> provides three SQL
commands to establish configuration defaults.
The already-mentioned <a class="xref" href="sql-altersystem.html" title="ALTER SYSTEM"><span class="refentrytitle">ALTER SYSTEM</span></a> command
provides a SQL-accessible means of changing global defaults; it is
functionally equivalent to editing <code class="filename">postgresql.conf</code>.
In addition, there are two commands that allow setting of defaults
on a per-database or per-role basis:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
The <a class="xref" href="sql-alterdatabase.html" title="ALTER DATABASE"><span class="refentrytitle">ALTER DATABASE</span></a> command allows global
settings to be overridden on a per-database basis.
</p></li><li class="listitem"><p>
The <a class="xref" href="sql-alterrole.html" title="ALTER ROLE"><span class="refentrytitle">ALTER ROLE</span></a> command allows both global and
per-database settings to be overridden with user-specific values.
</p></li></ul></div><p>
Values set with <code class="command">ALTER DATABASE</code> and <code class="command">ALTER ROLE</code>
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><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
The <a class="xref" href="sql-show.html" title="SHOW"><span class="refentrytitle">SHOW</span></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 class="listitem"><p>
The <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></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></div><p>
In addition, the system view <a class="link" href="view-pg-settings.html" title="52.85. pg_settings"><code class="structname">pg_settings</code></a> can be
used to view and change session-local values:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
Querying this view is similar to using <code class="command">SHOW ALL</code> 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 class="listitem"><p>
Using <a class="xref" href="sql-update.html" title="UPDATE"><span class="refentrytitle">UPDATE</span></a> on this view, specifically
updating the <code class="structname">setting</code> column, is the equivalent
of issuing <code class="command">SET</code> 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><div class="sect2" id="id-1.6.6.4.5"><div class="titlepage"><div><div><h3 class="title">19.1.4. Parameter Interaction via the Shell</h3></div></div></div><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><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
During server startup, parameter settings can be
passed to the <code class="command">postgres</code> command via the
<code class="option">-c</code> 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
<code class="filename">postgresql.conf</code> or <code class="command">ALTER SYSTEM</code>,
so they cannot be changed globally without restarting the server.
</p></li><li class="listitem"><p>
When starting a client session via <span class="application">libpq</span>,
parameter settings can be
specified using the <code class="envar">PGOPTIONS</code> 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 <code class="envar">PGOPTIONS</code> is
similar to that used when launching the <code class="command">postgres</code>
command; specifically, the <code class="option">-c</code> 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><div class="sect2" id="CONFIG-INCLUDES"><div class="titlepage"><div><div><h3 class="title">19.1.5. Managing Configuration File Contents</h3></div></div></div><p>
<span class="productname">PostgreSQL</span> provides several features for breaking
down complex <code class="filename">postgresql.conf</code> files into sub-files.
These features are especially useful when managing multiple servers
with related, but not identical, configurations.
</p><p>
<a id="id-1.6.6.4.6.3.1" class="indexterm"></a>
In addition to individual parameter settings,
the <code class="filename">postgresql.conf</code> file can contain <em class="firstterm">include
directives</em>, 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>
<a id="id-1.6.6.4.6.4.1" class="indexterm"></a>
There is also an <code class="literal">include_if_exists</code> directive, which acts
the same as the <code class="literal">include</code> directive, except
when the referenced file does not exist or cannot be read. A regular
<code class="literal">include</code> will consider this an error condition, but
<code class="literal">include_if_exists</code> merely logs a message and continues
processing the referencing configuration file.
</p><p>
<a id="id-1.6.6.4.6.5.1" class="indexterm"></a>
The <code class="filename">postgresql.conf</code> file can also contain
<code class="literal">include_dir</code> 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 <code class="literal">.conf</code> will be included. File names that
start with the <code class="literal">.</code> 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
<code class="filename">postgresql.conf</code> 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 <code class="filename">postgresql.conf</code> 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 <code class="filename">shared.conf</code>. Each
server with a particular amount of memory could share the
same <code class="filename">memory.conf</code>; you might have one for all servers
with 8GB of RAM, another for those having 16GB. And
finally <code class="filename">server.conf</code> 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 <code class="filename">conf.d</code>
directory could be referenced at the end of <code class="filename">postgresql.conf</code>:
</p><pre class="programlisting">
include_dir 'conf.d'
</pre><p>
Then you could name the files in the <code class="filename">conf.d</code> 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 <code class="filename">conf.d/02server.conf</code> would override a
value set in <code class="filename">conf.d/01memory.conf</code>.
</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 /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="runtime-config.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-file-locations.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 19. Server Configuration </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 19.2. File Locations</td></tr></table></div></body></html>
