Setting Parameters

18.1.1. Parameter Names and Values

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 on , off , true , false , yes , no , 1 , 0 (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 found by referencing pg_settings . unit . For convenience, a different unit can also be specified explicitly. Valid memory units are kB (kilobytes), MB (megabytes), and GB (gigabytes); valid time units are ms (milliseconds), s (seconds), min (minutes), h (hours), and d (days). Note that the multiplier for memory units is 1024, not 1000.

Parameters of type "enum" 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 pg_settings . enumvals . Enum parameter values are case-insensitive.

18.1.2. Setting Parameters via the Configuration File

One 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 there when the database cluster directory is initialized.) 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 ( # ) 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.

The configuration file is reread whenever the main server process receives a SIGHUP signal; this is most easily done 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 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 SIGHUP processing.

18.1.3. Other Ways to Set Parameters

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 might 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 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 ALTER ROLE and ALTER DATABASE , 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 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 be changed without restarting the entire PostgreSQL server. Also, some parameters require superuser permission to change via SET or ALTER .

18.1.4. Examining Parameter Settings

The SHOW command allows inspection of the current values of all parameters.

The virtual table pg_settings also allows displaying and updating session run-time parameters; see Section 47.66 for details and a description of the different variable types and when they can be changed. pg_settings 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. It also contains more information about each parameter than is available from SHOW .

18.1.5. Configuration File Includes

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. 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 for the behavior 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. It is used similarly:

 include_dir 'directory'
 

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

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

You might instead use this configuration directory approach while naming these files more 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).