Table of Contents
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 chapters. The subsequent sections discuss each parameter in detail.
All parameter names are case-insensitive. Every parameter takes a
value of one of four types: Boolean, integer, floating point,
or string. Boolean values may be written as ON
,
OFF
, TRUE
,
FALSE
, YES
,
NO
, 1
, 0
(all case-insensitive) or any unambiguous prefix of these.
One way to set these parameters is to edit the file
postgresql.conf
,
which is normally kept in the data directory. (initdb
installs a default copy there.) An example of what this file might look
like is:
# This is a comment log_connections = yes log_destination = 'syslog' search_path = '$user, public'
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 (#
) introduce comments
anywhere. 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.
The configuration file is reread whenever the
postmaster
process receives a
SIGHUP signal (which is most easily sent by means
of pg_ctl reload
). The postmaster
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.
A second way to set these configuration parameters is to give them
as a command line option to the postmaster
, such as:
postmaster -c log_connections=yes -c log_destination='syslog'
Command-line options override any conflicting settings in
postgresql.conf
. Note that this means you won't
be able to change the value on-the-fly by editing
postgresql.conf
, so while the command-line
method may be convenient, it can cost you flexibility later.
Occasionally it is useful to give a command line option to
one particular session only. The environment variable
PGOPTIONS
can be used for this purpose on the
client side:
env PGOPTIONS='-c geqo=off' psql
(This works for any libpq-based client application, not
just psql.) Note that this won't work for
parameters that are fixed when the server is started or that must be
specified in postgresql.conf
.
Furthermore, it is possible to assign a set of option 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 ALTER USER
and ALTER DATABASE,
respectively, are used to configure these settings. Per-database
settings override anything received from the
postmaster
command-line or the configuration
file, and in turn are overridden by per-user settings; both are
overridden by per-session options.
Some parameters can be changed in individual SQL sessions with the SET command, for example:
SET ENABLE_SEQSCAN TO OFF;
If SET
is allowed, it overrides all other sources of
values for the parameter. Some parameters cannot be changed via
SET
: for example, if they control behavior that
cannot reasonably be changed without restarting
PostgreSQL. Also, some parameters can
be modified via SET
or ALTER
by superusers,
but not by ordinary users.
The SHOW command allows inspection of the current values of all parameters.
The virtual table pg_settings
(described in Section 42.41, “pg_settings
”) also allows
displaying and updating session run-time parameters. It is equivalent
to SHOW
and SET
, but can be more convenient
to use because it can be joined with other tables, or selected from using
any desired selection condition.