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:

Boolean:
Values can be written as on,
off, true, false,
yes, no,
1, 0 (all
case-insensitive) or any unambiguous prefix of one of
these.

String: 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.

Numeric (integer and
floating point): A decimal point is permitted only
for floating-point parameters. Do not use thousands
separators. Quotes are not required.

Numeric with
Unit: 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
pg_settings.unit. For convenience, settings can be
given with a unit specified explicitly, for example
'120 ms' 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.

Enumerated:
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 pg_settings.enumvals. Enum parameter values are
case-insensitive.

The most fundamental way to set these parameters is to edit
the file 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:

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 (#) 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.

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.

The configuration file is reread whenever the main server
process receives a SIGHUP
signal; this signal is most easily sent by running pg_ctl reload from the command line or by
calling the SQL function pg_reload_conf(). 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 SIGHUP processing.

In addition to postgresql.conf, a
PostgreSQL data directory
contains a file postgresql.auto.conf,
which has the same format as postgresql.conf but should never be edited
manually. This file holds settings provided through the
ALTER SYSTEM command. This
file is automatically read whenever postgresql.conf is, and its settings take
effect in the same way. Settings in postgresql.auto.conf override those in
postgresql.conf.

The system view pg_file_settings can be helpful for
pre-testing changes to the configuration file, or for
diagnosing problems if a SIGHUP
signal did not have the desired effects.

PostgreSQL provides three
SQL commands to establish configuration defaults. The
already-mentioned ALTER
SYSTEM command provides a SQL-accessible means of changing
global defaults; it is functionally equivalent to editing
postgresql.conf. In addition, there
are two commands that allow setting of defaults on a
per-database or per-role basis:

The ALTER DATABASE
command allows global settings to be overridden on a
per-database basis.

The ALTER ROLE command
allows both global and per-database settings to be
overridden with user-specific values.

Values set with ALTER DATABASE and
ALTER ROLE 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).

Once a client is connected to the database, PostgreSQL provides two additional SQL
commands (and equivalent functions) to interact with
session-local configuration settings:

The SHOW command allows
inspection of the current value of all parameters. The
corresponding function is current_setting(setting_name text).

The SET 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 set_config(setting_name, new_value,
is_local).

In addition, the system view pg_settings can be used to view and
change session-local values:

Querying this view is similar to using SHOW ALL but provides more detail. It is
also more flexible, since it's possible to specify filter
conditions or join against other relations.

Using UPDATE on this view,
specifically updating the setting column, is the equivalent of
issuing SET commands. For example,
the equivalent of

SET configuration_parameter TO DEFAULT;

is:

UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';

In addition to setting global defaults or attaching
overrides at the database or role level, you can pass settings
to PostgreSQL via shell
facilities. Both the server and libpq client library accept parameter
values via the shell.

During server startup, parameter settings can be passed
to the postgres command via the
-c command-line parameter. For
example,

postgres -c log_connections=yes -c log_destination='syslog'

Settings provided in this way override those set via
postgresql.conf or ALTER SYSTEM, so they cannot be changed
globally without restarting the server.

When starting a client session via libpq, parameter settings can be
specified using the PGOPTIONS
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 PGOPTIONS is similar to that used
when launching the postgres
command; specifically, the -c flag
must be specified. For example,

env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql

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.

PostgreSQL provides several
features for breaking down complex postgresql.conf files into sub-files. These
features are especially useful when managing multiple servers
with related, but not identical, configurations.

In addition to individual parameter settings, the postgresql.conf file can contain include directives, 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:

include 'filename'

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.

There is also an include_if_exists
directive, which acts the same as the include directive, except when the referenced
file does not exist or cannot be read. A regular include will consider this an error condition,
but include_if_exists merely logs a
message and continues processing the referencing configuration
file.

The postgresql.conf file can also
contain include_dir directives, which
specify an entire directory of configuration files to include.
These look like

include_dir 'directory'

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 .conf will be
included. File names that start with the . 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).

Include files or directories can be used to logically
separate portions of the database configuration, rather than
having a single large postgresql.conf
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 postgresql.conf file to include them:

include 'shared.conf'
include 'memory.conf'
include 'server.conf'

All systems would have the same shared.conf. Each server with a particular
amount of memory could share the same memory.conf; you might have one for all servers
with 8GB of RAM, another for those having 16GB. And finally
server.conf could have truly
server-specific configuration information in it.

Another possibility is to create a configuration file
directory and put this information into files there. For
example, a conf.d directory could be
referenced at the end of postgresql.conf:

include_dir 'conf.d'

Then you could name the files in the conf.d directory like this:

00shared.conf
01memory.conf
02server.conf

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 conf.d/02server.conf would override a value set
in conf.d/01memory.conf.

You might instead use this approach to naming the files
descriptively:

00shared.conf
01memory-8GB.conf
02server-foo.conf

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.)

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.