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
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>20.1. Setting Parameters</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="runtime-config.html" title="Chapter 20. Server Configuration" /><link rel="next" href="runtime-config-file-locations.html" title="20.2. File Locations" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">20.1. Setting Parameters</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config.html" title="Chapter 20. Server Configuration">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 20. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-file-locations.html" title="20.2. File Locations">Next</a></td></tr></table><hr /></div><div class="sect1" id="CONFIG-SETTING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">20.1. Setting Parameters</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-NAMES-VALUES">20.1.1. Parameter Names and Values</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE">20.1.2. Parameter Interaction via the Configuration File</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION">20.1.3. Parameter Interaction via SQL</a></span></dt><dt><span class="sect2"><a href="config-setting.html#id-1.6.7.4.5">20.1.4. Parameter Interaction via the Shell</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-INCLUDES">20.1.5. Managing Configuration File Contents</a></span></dt></dl></div><div class="sect2" id="CONFIG-SETTING-NAMES-VALUES"><div class="titlepage"><div><div><h3 class="title">20.1.1. Parameter Names and Values</h3></div></div></div><p>
All parameter names are case-insensitive. Every parameter takes a
value of one of five types: boolean, string, integer, floating point,
or enumerated (enum). The type determines the syntax for setting the
parameter:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
<span class="emphasis"><em>Boolean:</em></span>
Values can 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 one of these.
</p></li><li class="listitem"><p>
<span class="emphasis"><em>String:</em></span>
In general, enclose the value in single quotes, doubling any single
quotes within the value. Quotes can usually be omitted if the value
is a simple number or identifier, however.
(Values that match an SQL keyword require quoting in some contexts.)
</p></li><li class="listitem"><p>
<span class="emphasis"><em>Numeric (integer and floating point):</em></span>
Numeric parameters can be specified in the customary integer and
floating-point formats; fractional values are rounded to the nearest
integer if the parameter is of integer type. Integer parameters
additionally accept hexadecimal input (beginning
with <code class="literal">0x</code>) and octal input (beginning
with <code class="literal">0</code>), but these formats cannot have a fraction.
Do not use thousands separators.
Quotes are not required, except for hexadecimal input.
</p></li><li class="listitem"><p>
<span class="emphasis"><em>Numeric with Unit:</em></span>
Some numeric parameters have an implicit unit, because they describe
quantities of memory or time. The unit might be bytes, kilobytes, blocks
(typically eight kilobytes), milliseconds, seconds, or minutes.
An unadorned numeric value for one of these settings will use the
setting's default unit, which can be learned from
<code class="structname">pg_settings</code>.<code class="structfield">unit</code>.
For convenience, settings can be given with a unit specified explicitly,
for example <code class="literal">'120 ms'</code> for a time value, and they will be
converted to whatever the parameter's actual unit is. Note that the
value must be written as a string (with quotes) to use this feature.
The unit name is case-sensitive, and there can be whitespace between
the numeric value and the unit.
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
Valid memory units are <code class="literal">B</code> (bytes),
<code class="literal">kB</code> (kilobytes),
<code class="literal">MB</code> (megabytes), <code class="literal">GB</code>
(gigabytes), and <code class="literal">TB</code> (terabytes).
The multiplier for memory units is 1024, not 1000.
</p></li><li class="listitem"><p>
Valid time units are
<code class="literal">us</code> (microseconds),
<code class="literal">ms</code> (milliseconds),
<code class="literal">s</code> (seconds), <code class="literal">min</code> (minutes),
<code class="literal">h</code> (hours), and <code class="literal">d</code> (days).
</p></li></ul></div><p>
If a fractional value is specified with a unit, it will be rounded
to a multiple of the next smaller unit if there is one.
For example, <code class="literal">30.1 GB</code> will be converted
to <code class="literal">30822 MB</code> not <code class="literal">32319628902 B</code>.
If the parameter is of integer type, a final rounding to integer
occurs after any unit conversion.
</p></li><li class="listitem"><p>
<span class="emphasis"><em>Enumerated:</em></span>
Enumerated-type parameters are written in the same way as string
parameters, but are restricted to have one of a limited set of
values. The values allowable for such a parameter can be found from
<code class="structname">pg_settings</code>.<code class="structfield">enumvals</code>.
Enum parameter values are case-insensitive.
</p></li></ul></div></div><div class="sect2" id="CONFIG-SETTING-CONFIGURATION-FILE"><div class="titlepage"><div><div><h3 class="title">20.1.2. Parameter Interaction via the Configuration File</h3></div></div></div><p>
The most fundamental way to set these parameters is to edit the file
<code class="filename">postgresql.conf</code><a id="id-1.6.7.4.3.2.2" class="indexterm"></a>,
which is normally kept in the data directory. A default copy is
installed when the database cluster directory is initialized.
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'
shared_buffers = 128MB
</pre><p>
One parameter is specified per line. The equal sign between name and
value is optional. Whitespace is insignificant (except within a quoted
parameter value) and blank lines are
ignored. Hash marks (<code class="literal">#</code>) 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.
If the file contains multiple entries for the same parameter,
all but the last one are ignored.
</p><p>
Parameters set in this way provide default values for the cluster.
The settings seen by active sessions will be these values unless they
are overridden. The following sections describe ways in which the
administrator or user can override these defaults.
</p><p>
<a id="id-1.6.7.4.3.4.1" class="indexterm"></a>
The configuration file is reread whenever the main server process
receives a <span class="systemitem">SIGHUP</span> signal; this signal is most easily
sent by running <code class="literal">pg_ctl reload</code> from the command line or by
calling the SQL function <code class="function">pg_reload_conf()</code>. The main
server process also propagates this signal to all currently running
server processes, so that existing sessions also adopt the new values
(this will happen after they complete any currently-executing client
command). 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 <span class="systemitem">SIGHUP</span> processing.
</p><p>
In addition to <code class="filename">postgresql.conf</code>,
a <span class="productname">PostgreSQL</span> data directory contains a file
<code class="filename">postgresql.auto.conf</code><a id="id-1.6.7.4.3.5.4" class="indexterm"></a>,
which has the same format as <code class="filename">postgresql.conf</code> but
is intended to be edited automatically, not manually. This file holds
settings provided through the <a class="link" href="sql-altersystem.html" title="ALTER SYSTEM"><code class="command">ALTER SYSTEM</code></a> command.
This file is read whenever <code class="filename">postgresql.conf</code> is,
and its settings take effect in the same way. Settings
in <code class="filename">postgresql.auto.conf</code> override those
in <code class="filename">postgresql.conf</code>.
</p><p>
External tools may also
modify <code class="filename">postgresql.auto.conf</code>. It is not
recommended to do this while the server is running, since a
concurrent <code class="command">ALTER SYSTEM</code> command could overwrite
such changes. Such tools might simply append new settings to the end,
or they might choose to remove duplicate settings and/or comments
(as <code class="command">ALTER SYSTEM</code> will).
</p><p>
The system view
<a class="link" href="view-pg-file-settings.html" title="54.7. pg_file_settings"><code class="structname">pg_file_settings</code></a>
can be helpful for pre-testing changes to the configuration files, or for
diagnosing problems if a <span class="systemitem">SIGHUP</span> signal did not have the
desired effects.
</p></div><div class="sect2" id="CONFIG-SETTING-SQL-COMMAND-INTERACTION"><div class="titlepage"><div><div><h3 class="title">20.1.3. Parameter Interaction via SQL</h3></div></div></div><p>
<span class="productname">PostgreSQL</span> provides three SQL
commands to establish configuration defaults.
The already-mentioned <code class="command">ALTER SYSTEM</code> command
provides an SQL-accessible means of changing global defaults; it is
functionally equivalent to editing <code class="filename">postgresql.conf</code>.
In addition, there are two commands that allow setting of defaults
on a per-database or per-role basis:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
The <a class="link" href="sql-alterdatabase.html" title="ALTER DATABASE"><code class="command">ALTER DATABASE</code></a> command allows global
settings to be overridden on a per-database basis.
</p></li><li class="listitem"><p>
The <a class="link" href="sql-alterrole.html" title="ALTER ROLE"><code class="command">ALTER ROLE</code></a> command allows both global and
per-database settings to be overridden with user-specific values.
</p></li></ul></div><p>
Values set with <code class="command">ALTER DATABASE</code> and <code class="command">ALTER ROLE</code>
are applied only when starting a fresh database session. They
override values obtained from the configuration files or server
command line, and constitute defaults for the rest of the session.
Note that some settings cannot be changed after server start, and
so cannot be set with these commands (or the ones listed below).
</p><p>
Once a client is connected to the database, <span class="productname">PostgreSQL</span>
provides two additional SQL commands (and equivalent functions) to
interact with session-local configuration settings:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
The <a class="link" href="sql-show.html" title="SHOW"><code class="command">SHOW</code></a> command allows inspection of the
current value of any parameter. The corresponding SQL function is
<code class="function">current_setting(setting_name text)</code>
(see <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SET" title="9.27.1. Configuration Settings Functions">Section 9.27.1</a>).
</p></li><li class="listitem"><p>
The <a class="link" href="sql-set.html" title="SET"><code class="command">SET</code></a> command allows modification of the
current value of those parameters that can be set locally to a
session; it has no effect on other sessions.
Many parameters can be set this way by any user, but some can
only be set by superusers and users who have been
granted <code class="literal">SET</code> privilege on that parameter.
The corresponding SQL function is
<code class="function">set_config(setting_name, new_value, is_local)</code>
(see <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SET" title="9.27.1. Configuration Settings Functions">Section 9.27.1</a>).
</p></li></ul></div><p>
In addition, the system view <a class="link" href="view-pg-settings.html" title="54.24. pg_settings"><code class="structname">pg_settings</code></a> can be
used to view and change session-local values:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
Querying this view is similar to using <code class="command">SHOW ALL</code> but
provides more detail. It is also more flexible, since it's possible
to specify filter conditions or join against other relations.
</p></li><li class="listitem"><p>
Using <code class="command">UPDATE</code> on this view, specifically
updating the <code class="structname">setting</code> column, is the equivalent
of issuing <code class="command">SET</code> commands. For example, the equivalent of
</p><pre class="programlisting">
SET configuration_parameter TO DEFAULT;
</pre><p>
is:
</p><pre class="programlisting">
UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';
</pre><p>
</p></li></ul></div></div><div class="sect2" id="id-1.6.7.4.5"><div class="titlepage"><div><div><h3 class="title">20.1.4. Parameter Interaction via the Shell</h3></div></div></div><p>
In addition to setting global defaults or attaching
overrides at the database or role level, you can pass settings to
<span class="productname">PostgreSQL</span> via shell facilities.
Both the server and <span class="application">libpq</span> client library
accept parameter values via the shell.
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
During server startup, parameter settings can be
passed to the <code class="command">postgres</code> command via the
<code class="option">-c</code> command-line parameter. For example,
</p><pre class="programlisting">
postgres -c log_connections=yes -c log_destination='syslog'
</pre><p>
Settings provided in this way override those set via
<code class="filename">postgresql.conf</code> or <code class="command">ALTER SYSTEM</code>,
so they cannot be changed globally without restarting the server.
</p></li><li class="listitem"><p>
When starting a client session via <span class="application">libpq</span>,
parameter settings can be
specified using the <code class="envar">PGOPTIONS</code> environment variable.
Settings established in this way constitute defaults for the life
of the session, but do not affect other sessions.
For historical reasons, the format of <code class="envar">PGOPTIONS</code> is
similar to that used when launching the <code class="command">postgres</code>
command; specifically, the <code class="option">-c</code> flag must be specified.
For example,
</p><pre class="programlisting">
env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql
</pre><p>
</p><p>
Other clients and libraries might provide their own mechanisms,
via the shell or otherwise, that allow the user to alter session
settings without direct use of SQL commands.
</p></li></ul></div></div><div class="sect2" id="CONFIG-INCLUDES"><div class="titlepage"><div><div><h3 class="title">20.1.5. Managing Configuration File Contents</h3></div></div></div><p>
<span class="productname">PostgreSQL</span> provides several features for breaking
down complex <code class="filename">postgresql.conf</code> files into sub-files.
These features are especially useful when managing multiple servers
with related, but not identical, configurations.
</p><p>
<a id="id-1.6.7.4.6.3.1" class="indexterm"></a>
In addition to individual parameter settings,
the <code class="filename">postgresql.conf</code> file can contain <em class="firstterm">include
directives</em>, 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:
</p><pre class="programlisting">
include 'filename'
</pre><p>
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.
</p><p>
<a id="id-1.6.7.4.6.4.1" class="indexterm"></a>
There is also an <code class="literal">include_if_exists</code> directive, which acts
the same as the <code class="literal">include</code> directive, except
when the referenced file does not exist or cannot be read. A regular
<code class="literal">include</code> will consider this an error condition, but
<code class="literal">include_if_exists</code> merely logs a message and continues
processing the referencing configuration file.
</p><p>
<a id="id-1.6.7.4.6.5.1" class="indexterm"></a>
The <code class="filename">postgresql.conf</code> file can also contain
<code class="literal">include_dir</code> directives, which specify an entire
directory of configuration files to include. These look like
</p><pre class="programlisting">
include_dir 'directory'
</pre><p>
Non-absolute directory names are taken as relative to the directory
containing the referencing configuration file. Within the specified
directory, only non-directory files whose names end with the
suffix <code class="literal">.conf</code> will be included. File names that
start with the <code class="literal">.</code> character are also ignored, to
prevent mistakes since such files are hidden on some platforms. Multiple
files within an include directory are processed in file name order
(according to C locale rules, i.e., numbers before letters, and
uppercase letters before lowercase ones).
</p><p>
Include files or directories can be used to logically separate portions
of the database configuration, rather than having a single large
<code class="filename">postgresql.conf</code> 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 <code class="filename">postgresql.conf</code> file to include
them:
</p><pre class="programlisting">
include 'shared.conf'
include 'memory.conf'
include 'server.conf'
</pre><p>
All systems would have the same <code class="filename">shared.conf</code>. Each
server with a particular amount of memory could share the
same <code class="filename">memory.conf</code>; you might have one for all servers
with 8GB of RAM, another for those having 16GB. And
finally <code class="filename">server.conf</code> could have truly server-specific
configuration information in it.
</p><p>
Another possibility is to create a configuration file directory and
put this information into files there. For example, a <code class="filename">conf.d</code>
directory could be referenced at the end of <code class="filename">postgresql.conf</code>:
</p><pre class="programlisting">
include_dir 'conf.d'
</pre><p>
Then you could name the files in the <code class="filename">conf.d</code> directory
like this:
</p><pre class="programlisting">
00shared.conf
01memory.conf
02server.conf
</pre><p>
This naming convention establishes a clear order in which these
files will be loaded. This is important because only the last
setting encountered for a particular parameter while the server is
reading configuration files will be used. In this example,
something set in <code class="filename">conf.d/02server.conf</code> would override a
value set in <code class="filename">conf.d/01memory.conf</code>.
</p><p>
You might instead use this approach to naming the files
descriptively:
</p><pre class="programlisting">
00shared.conf
01memory-8GB.conf
02server-foo.conf
</pre><p>
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.)
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="runtime-config.html" title="Chapter 20. Server Configuration">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-file-locations.html" title="20.2. File Locations">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 20. Server Configuration </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.2. File Locations</td></tr></table></div></body></html>
|
