1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Chapter17.Server Configuration</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="admin.html" title="PartIII.Server Administration">
<link rel="prev" href="ssh-tunnels.html" title="16.8.Secure TCP/IP Connections with SSH Tunnels">
<link rel="next" href="runtime-config-file-locations.html" title="17.2.File Locations">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="chapter" lang="en" id="runtime-config">
<div class="titlepage"><div><div><h2 class="title">
<a name="runtime-config"></a>Chapter17.Server Configuration</h2></div></div></div>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="sect1"><a href="runtime-config.html#config-setting">17.1. Setting Parameters</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-file-locations.html">17.2. File Locations</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-connection.html">17.3. Connections and Authentication</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-connection.html#runtime-config-connection-settings">17.3.1. Connection Settings</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-connection.html#runtime-config-connection-security">17.3.2. Security and Authentication</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-resource.html">17.4. Resource Consumption</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-resource.html#runtime-config-resource-memory">17.4.1. Memory</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-resource.html#runtime-config-resource-fsm">17.4.2. Free Space Map</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-resource.html#runtime-config-resource-kernel">17.4.3. Kernel Resource Usage</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-resource.html#runtime-config-resource-vacuum-cost">17.4.4. Cost-Based Vacuum Delay
</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-resource.html#runtime-config-resource-background-writer">17.4.5. Background Writer</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-wal.html">17.5. Write Ahead Log</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-wal.html#runtime-config-wal-settings">17.5.1. Settings</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-wal.html#runtime-config-wal-checkpoints">17.5.2. Checkpoints</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-wal.html#runtime-config-wal-archiving">17.5.3. Archiving</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-query.html">17.6. Query Planning</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-query.html#runtime-config-query-enable">17.6.1. Planner Method Configuration</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-query.html#runtime-config-query-constants">17.6.2. Planner Cost Constants
</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-query.html#runtime-config-query-geqo">17.6.3. Genetic Query Optimizer</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-query.html#runtime-config-query-other">17.6.4. Other Planner Options</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-logging.html">17.7. Error Reporting and Logging</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-logging.html#runtime-config-logging-where">17.7.1. Where To Log</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-logging.html#runtime-config-logging-when">17.7.2. When To Log</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-logging.html#runtime-config-logging-what">17.7.3. What To Log</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-statistics.html">17.8. Run-Time Statistics</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-statistics.html#runtime-config-statistics-monitor">17.8.1. Statistics Monitoring</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-statistics.html#runtime-config-statistics-collector">17.8.2. Query and Index Statistics Collector</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-autovacuum.html">17.9. Automatic Vacuuming</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-client.html">17.10. Client Connection Defaults</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-client.html#runtime-config-client-statement">17.10.1. Statement Behavior</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-client.html#runtime-config-client-format">17.10.2. Locale and Formatting</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-client.html#runtime-config-client-other">17.10.3. Other Defaults</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-locks.html">17.11. Lock Management</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-compatible.html">17.12. Version and Platform Compatibility</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-compatible.html#runtime-config-compatible-version">17.12.1. Previous PostgreSQL Versions</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-compatible.html#runtime-config-compatible-clients">17.12.2. Platform and Client Compatibility</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-preset.html">17.13. Preset Options</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-custom.html">17.14. Customized Options</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-developer.html">17.15. Developer Options</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-short.html">17.16. Short Options</a></span></dt>
</dl>
</div>
<a name="id644919"></a><p> 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.
</p>
<div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="config-setting"></a>17.1.Setting Parameters</h2></div></div></div>
<p> 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 <code class="literal">ON</code>,
<code class="literal">OFF</code>, <code class="literal">TRUE</code>,
<code class="literal">FALSE</code>, <code class="literal">YES</code>,
<code class="literal">NO</code>, <code class="literal">1</code>, <code class="literal">0</code>
(all case-insensitive) or any unambiguous prefix of these.
</p>
<p> One way to set these parameters is to edit the file
<code class="filename">postgresql.conf</code><a name="id645008"></a>,
which is normally kept in the data directory. (<code class="command">initdb</code>
installs a default copy there.) An example of what this file might look
like is:
</p>
<pre class="programlisting"># This is a comment
log_connections = yes
log_destination = 'syslog'
search_path = '$user, public'</pre>
<p>
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 (<code class="literal">#</code>) 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.
</p>
<p> <a name="id645048"></a>
The configuration file is reread whenever the
<code class="command">postmaster</code> process receives a
<span class="systemitem">SIGHUP</span> signal (which is most easily sent by means
of <code class="literal">pg_ctl reload</code>). The <code class="command">postmaster</code>
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.
</p>
<p> A second way to set these configuration parameters is to give them
as a command line option to the <code class="command">postmaster</code>, such as:
</p>
<pre class="programlisting">postmaster -c log_connections=yes -c log_destination='syslog'</pre>
<p>
Command-line options override any conflicting settings in
<code class="filename">postgresql.conf</code>. Note that this means you won't
be able to change the value on-the-fly by editing
<code class="filename">postgresql.conf</code>, so while the command-line
method may be convenient, it can cost you flexibility later.
</p>
<p> Occasionally it is useful to give a command line option to
one particular session only. The environment variable
<code class="envar">PGOPTIONS</code> can be used for this purpose on the
client side:
</p>
<pre class="programlisting">env PGOPTIONS='-c geqo=off' psql</pre>
<p>
(This works for any <span class="application">libpq</span>-based client application, not
just <span class="application">psql</span>.) Note that this won't work for
parameters that are fixed when the server is started or that must be
specified in <code class="filename">postgresql.conf</code>.
</p>
<p> 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 <a href="sql-alteruser.html">ALTER USER</a>
and <a href="sql-alterdatabase.html">ALTER DATABASE</a>,
respectively, are used to configure these settings. Per-database
settings override anything received from the
<code class="command">postmaster</code> command-line or the configuration
file, and in turn are overridden by per-user settings; both are
overridden by per-session options.
</p>
<p> Some parameters can be changed in individual <acronym class="acronym">SQL</acronym>
sessions with the <a href="sql-set.html">SET</a>
command, for example:
</p>
<pre class="screen">SET ENABLE_SEQSCAN TO OFF;</pre>
<p>
If <code class="command">SET</code> is allowed, it overrides all other sources of
values for the parameter. Some parameters cannot be changed via
<code class="command">SET</code>: for example, if they control behavior that
cannot reasonably be changed without restarting
<span class="productname">PostgreSQL</span>. Also, some parameters can
be modified via <code class="command">SET</code> or <code class="command">ALTER</code> by superusers,
but not by ordinary users.
</p>
<p> The <a href="sql-show.html">SHOW</a>
command allows inspection of the current values of all parameters.
</p>
<p> The virtual table <code class="structname">pg_settings</code>
(described in <a href="view-pg-settings.html" title="42.41.pg_settings">Section42.41, “<code class="structname">pg_settings</code>”</a>) also allows
displaying and updating session run-time parameters. It is equivalent
to <code class="command">SHOW</code> and <code class="command">SET</code>, but can be more convenient
to use because it can be joined with other tables, or selected from using
any desired selection condition.
</p>
</div>
</div></body>
</html>
|
