Chapter 17. Server Configuration

Top  Previous  Next

Chapter 17. Server Configuration

Table of Contents

17.1. Setting Parameters

17.2. File Locations

17.3. Connections and Authentication

17.3.1. Connection Settings

17.3.2. Security and Authentication

17.4. Resource Consumption

17.4.1. Memory

17.4.2. Free Space Map

17.4.3. Kernel Resource Usage

17.4.4. Cost-Based Vacuum Delay

17.4.5. Background Writer

17.5. Write Ahead Log

17.5.1. Settings

17.5.2. Checkpoints

17.5.3. Archiving

17.6. Query Planning

17.6.1. Planner Method Configuration

17.6.2. Planner Cost Constants

17.6.3. Genetic Query Optimizer

17.6.4. Other Planner Options

17.7. Error Reporting and Logging

17.7.1. Where To Log

17.7.2. When To Log

17.7.3. What To Log

17.8. Run-Time Statistics

17.8.1. Statistics Monitoring

17.8.2. Query and Index Statistics Collector

17.9. Automatic Vacuuming

17.10. Client Connection Defaults

17.10.1. Statement Behavior

17.10.2. Locale and Formatting

17.10.3. Other Defaults

17.11. Lock Management

17.12. Version and Platform Compatibility

17.12.1. Previous PostgreSQL Versions

17.12.2. Platform and Client Compatibility

17.13. Preset Options

17.14. Customized Options

17.15. Developer Options

17.16. Short Options

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 parameters. The subsequent sections discuss each parameter in detail.

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