|PostgreSQL Reference Manual - Volume 3 - Server Administration Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 204 pages
RRP £13.95 ($24.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
4.1 Setting Parameters
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
(all case-insensitive) or any unambiguous prefix of these.
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
queried by referencing
pg_settings.unit. For convenience,
a different unit can also be specified explicitly. Valid memory units
GB (gigabytes); valid time units
d (days). Note that the multiplier
for memory units is 1024, not 1000.
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' shared_buffers = 128MB
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.
In addition to 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. Include directives simply look like
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.
The configuration file is reread whenever the main server process receives a
SIGHUP signal (which is most easily sent by means
pg_ctl reload). 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.
A second way to set these configuration parameters is to give them
as a command-line option to the
postgres command, such as:
postgres -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
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 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
respectively, are used to configure these settings. Per-database
settings override anything received from the
postgres command-line or the configuration
file, and in turn are overridden by per-user settings; both are
overridden by per-session settings.
Some parameters can be changed in individual SQL
sessions with the
command, for example:
SET ENABLE_SEQSCAN TO OFF;
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 be changed without restarting the entire
PostgreSQL server. Also, some parameters can
be modified via
ALTER by superusers,
but not by ordinary users.
command allows inspection of the current values of all parameters.
The virtual table
pg_settings) also allows
displaying and updating session run-time parameters. It is equivalent
SET, but can be more convenient
to use because it can be joined with other tables, or selected from using
any desired selection condition.
|ISBN 0954612043||PostgreSQL Reference Manual - Volume 3 - Server Administration Guide||See the print edition|