runtime.sgml 175 KB
Newer Older
1
<!--
2
$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.280 2004/08/31 04:53:43 tgl Exp $
3 4
-->

5
<Chapter Id="runtime">
6
 <Title>Server Run-time Environment</Title>
7 8 9 10 11 12 13

 <Para>
  This chapter discusses how to set up and run the database server
  and the interactions with the operating system.
 </para>

 <sect1 id="postgres-user">
14
  <title>The <productname>PostgreSQL</productname> User Account</title>
15

16 17 18 19
  <indexterm>
   <primary>postgres user</primary>
  </indexterm>

20
  <para>
21 22 23 24
   As with any other server daemon that is connected to outside world,
   it is advisable to run <productname>PostgreSQL</productname> under a
   separate user account. This user account should only own the data
   that is managed by the server, and should not be shared with other
25
   daemons. (For example, using the user <literal>nobody</literal> is a bad
26 27
   idea.) It is not advisable to install executables owned by this 
   user because compromised systems could then modify their own 
28
   binaries.
29
  </para>
30

31
  <para>
32
   To add a Unix user account to your system, look for a command
33
   <command>useradd</command> or <command>adduser</command>. The user
34 35
   name <systemitem>postgres</systemitem> is often used but is by no
   means required.
36 37 38 39
  </para>
 </sect1>

 <sect1 id="creating-cluster">
40
  <title>Creating a Database Cluster</title>
41

42 43 44 45 46 47 48 49 50
  <indexterm>
   <primary>database cluster</primary>
  </indexterm>

  <indexterm>
   <primary>data area</primary>
   <see>database cluster</see>
  </indexterm>

51 52
  <para>
   Before you can do anything, you must initialize a database storage
53
   area on disk. We call this a <firstterm>database cluster</firstterm>.
54 55 56 57
   (<acronym>SQL</acronym> uses the term catalog cluster instead.) A
   database cluster is a collection of databases is accessible by a
   single instance of a running database server. After initialization, a
   database cluster will contain a database named
58
   <literal>template1</literal>. As the name suggests, this will be used
59
   as a template for subsequently created databases; it should not be
60 61
   used for actual work.  (See <xref linkend="managing-databases"> for information
   about creating databases.)
62 63
  </para>

64 65
  <para>
   In file system terms, a database cluster will be a single directory
66 67
   under which all data will be stored. We call this the <firstterm>data
   directory</firstterm> or <firstterm>data area</firstterm>. It is
68
   completely up to you where you choose to store your data.  There is no
69
   default, although locations such as
70
   <filename>/usr/local/pgsql/data</filename> or
71
   <filename>/var/lib/pgsql/data</filename> are popular. To initialize a
Peter Eisentraut's avatar
Peter Eisentraut committed
72
   database cluster, use the command <command>initdb</command>,<indexterm><primary>initdb</></> which is
73 74 75
   installed with <productname>PostgreSQL</productname>. The desired
   file system location of your database system is indicated by the
   <option>-D</option> option, for example
76
<screen>
Peter Eisentraut's avatar
Peter Eisentraut committed
77
<prompt>$</> <userinput>initdb -D /usr/local/pgsql/data</userinput>
78
</screen>
79 80 81
   Note that you must execute this command while logged into the
   <productname>PostgreSQL</productname> user account, which is
   described in the previous section.
82
  </para>
83

84
  <tip>
85
   <para>
86 87
    As an alternative to the <option>-D</option> option, you can set
    the environment variable <envar>PGDATA</envar>.
88
    <indexterm><primary><envar>PGDATA</envar></primary></indexterm>
89
   </para>
90 91 92 93
  </tip>

  <para>
   <command>initdb</command> will attempt to create the directory you
94 95 96 97 98 99
   specify if it does not already exist. It is likely that it will not
   have the permission to do so (if you followed our advice and created
   an unprivileged account). In that case you should create the
   directory yourself (as root) and change the owner to be the
   <productname>PostgreSQL</productname> user. Here is how this might
   be done:
100 101 102 103
<screen>
root# <userinput>mkdir /usr/local/pgsql/data</userinput>
root# <userinput>chown postgres /usr/local/pgsql/data</userinput>
root# <userinput>su postgres</userinput>
Peter Eisentraut's avatar
Peter Eisentraut committed
104
postgres$ <userinput>initdb -D /usr/local/pgsql/data</userinput>
105 106
</screen>
  </para>
107

108 109
  <para>
   <command>initdb</command> will refuse to run if the data directory
110
   looks like it it has already been initialized.</para>
111 112 113

  <para>
   Because the data directory contains all the data stored in the
114
   database, it is essential that it be secured from unauthorized
115
   access. <command>initdb</command> therefore revokes access
116 117
   permissions from everyone but the
   <productname>PostgreSQL</productname> user.
118
  </para>
119

120 121
  <para>
   However, while the directory contents are secure, the default
122
   client authentication setup allows any local user to connect to the
Peter Eisentraut's avatar
Peter Eisentraut committed
123
   database and even become the database superuser. If you do not
124 125 126
   trust other local users, we recommend you use one of
   <command>initdb</command>'s <option>-W</option>, <option>--pwprompt</option>
   or <option>--pwfile</option> option to assign a password to the
Peter Eisentraut's avatar
Peter Eisentraut committed
127 128 129
   database superuser.<indexterm><primary>password</><secondary>of the
   superuser</></indexterm> After <command>initdb</command>, modify
   the <filename>pg_hba.conf</filename> file to use <literal>md5</> or
130
   <literal>password</> instead of <literal>trust</> authentication
131
   <emphasis>before</> you start the server for the first time. (Other
132 133
   approaches include using <literal>ident</literal> authentication or
   file system permissions to restrict connections. See <xref
134
   linkend="client-authentication"> for more information.)
135 136
  </para>

137
  <para>
138 139 140 141 142 143 144
   <command>initdb</command> also initializes the default
   locale<indexterm><primary>locale</></> for the database cluster.
   Normally, it will just take the locale settings in the environment
   and apply them to the initialized database.  It is possible to
   specify a different locale for the database; more information about
   that can be found in <xref linkend="locale">.  The sort order used
   within a particular database cluster is set by
145 146 147 148
   <command>initdb</command> and cannot be changed later, short of
   dumping all data, rerunning <command>initdb</command>, and
   reloading the data. So it's important to make this choice correctly
   the first time.
149
  </para>
150 151 152
 </sect1>

 <sect1 id="postmaster-start">
153
  <title>Starting the Database Server</title>
154 155

  <para>
156
   Before anyone can access the database, you must start the database
157 158 159
   server. The database server program is called
   <command>postmaster</command>.<indexterm><primary>postmaster</></>
   The <command>postmaster</command> must know where to
160 161 162
   find the data it is supposed to use. This is done with the
   <option>-D</option> option. Thus, the simplest way to start the
   server is:
163
<screen>
Peter Eisentraut's avatar
Peter Eisentraut committed
164
$ <userinput>postmaster -D /usr/local/pgsql/data</userinput>
165
</screen>
166 167 168
   which will leave the server running in the foreground. This must be
   done while logged into the <productname>PostgreSQL</productname> user
   account. Without <option>-D</option>, the server will try to use
Tom Lane's avatar
Tom Lane committed
169 170
   the data directory named by the environment variable <envar>PGDATA</envar>.
   If that variable is not provided either, it will fail.
171 172 173
  </para>

  <para>
Tom Lane's avatar
Tom Lane committed
174 175
   Normally it is better to start the <command>postmaster</command> in the
   background.  For this, use the usual shell syntax:
176
<screen>
Peter Eisentraut's avatar
Peter Eisentraut committed
177
$ <userinput>postmaster -D /usr/local/pgsql/data &gt;logfile 2&gt;&amp;1 &amp;</userinput>
178
</screen>
Tom Lane's avatar
Tom Lane committed
179
   It is important to store the server's <systemitem>stdout</> and
180 181 182 183
   <systemitem>stderr</> output somewhere, as shown above. It will help
   for auditing purposes and to diagnose problems. (See <xref
   linkend="logfile-maintenance"> for a more thorough discussion of log
   file handling.)
184 185 186
  </para>

  <para>
187 188
   The <command>postmaster</command> also takes a number of other
   command line options. For more information, see the reference page
189
   and <xref linkend="runtime-config"> below.
190 191
  </para>

192
  <para>
193
   This shell syntax can get tedious quickly.  Therefore the shell
194 195 196
   script wrapper
   <command>pg_ctl</command><indexterm><primary>pg_ctl</primary></indexterm>
   is provided to simplify some tasks.  For example:
197 198 199 200
<programlisting>
pg_ctl start -l logfile
</programlisting>
   will start the server in the background and put the output into the
201
   named log file. The <option>-D</option> option has the same meaning
202
   here as in the <command>postmaster</command>. <command>pg_ctl</command> is also
203
   capable of stopping the server.
204 205
  </para>

206 207
  <para>
   Normally, you will want to start the database server when the
Peter Eisentraut's avatar
Peter Eisentraut committed
208
   computer boots.<indexterm><primary>booting</><secondary>starting
Tom Lane's avatar
Tom Lane committed
209 210
   the server during</></> Autostart scripts are operating-system-specific.
   There are a few distributed with
211
   <productname>PostgreSQL</productname> in the
Tom Lane's avatar
Tom Lane committed
212 213
   <filename>contrib/start-scripts</> directory. Installing one will require
   root privileges.
214 215 216
  </para>

  <para>
217 218
   Different systems have different conventions for starting up daemons
   at boot time. Many systems have a file
219
   <filename>/etc/rc.local</filename> or
220 221 222 223 224 225
   <filename>/etc/rc.d/rc.local</filename>. Others use
   <filename>rc.d</> directories. Whatever you do, the server must be
   run by the <productname>PostgreSQL</productname> user account
   <emphasis>and not by root</emphasis> or any other user. Therefore you
   probably should form your commands using <literal>su -c '...'
   postgres</literal>.  For example:
226
<programlisting>
227
su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres
228 229 230 231
</programlisting>
  </para>

  <para>
Tom Lane's avatar
Tom Lane committed
232 233 234
   Here are a few more operating-system-specific suggestions. (In each
   case be sure to use the proper installation directory and user
   name where we show generic values.)
235 236 237 238

   <itemizedlist>
    <listitem>
     <para>
239
      For <productname>FreeBSD</productname>, look at the file
240 241
      <filename>contrib/start-scripts/freebsd</filename> in the
      <productname>PostgreSQL</productname> source distribution.
Peter Eisentraut's avatar
Peter Eisentraut committed
242
      <indexterm><primary>FreeBSD</><secondary>start script</secondary></>
243 244 245 246 247 248 249
     </para>
    </listitem>

    <listitem>
     <para>
      On <productname>OpenBSD</productname>, add the following lines
      to the file <filename>/etc/rc.local</filename>:
Peter Eisentraut's avatar
Peter Eisentraut committed
250
      <indexterm><primary>OpenBSD</><secondary>start script</secondary></>
251
<programlisting>
252 253 254 255
if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postmaster ]; then
    su - -c '/usr/local/pgsql/bin/pg_ctl start -l /var/postgresql/log -s' postgres
    echo -n ' postgresql'
fi
256 257 258 259 260 261
</programlisting>
     </para>
    </listitem>

    <listitem>
     <para>
262
      On <productname>Linux</productname> systems either add
Peter Eisentraut's avatar
Peter Eisentraut committed
263
      <indexterm><primary>Linux</><secondary>start script</secondary></>
264
<programlisting>
265
/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
266
</programlisting>
267
      to <filename>/etc/rc.d/rc.local</filename> or look at the file
268
      <filename>contrib/start-scripts/linux</filename> in the
269
      <productname>PostgreSQL</productname> source distribution.
270 271 272 273 274
     </para>
    </listitem>

    <listitem>
     <para>
275 276 277
      On <productname>NetBSD</productname>, either use the
      <productname>FreeBSD</productname> or
      <productname>Linux</productname> start scripts, depending on
Peter Eisentraut's avatar
Peter Eisentraut committed
278
      preference. <indexterm><primary>NetBSD</><secondary>start script</secondary></>
279 280 281 282 283
     </para>
    </listitem>

    <listitem>
     <para>
284
      On <productname>Solaris</productname>, create a file called
285
      <filename>/etc/init.d/postgresql</filename> that contains
286
      the following line:
Peter Eisentraut's avatar
Peter Eisentraut committed
287
      <indexterm><primary>Solaris</><secondary>start script</secondary></>
288 289 290
<programlisting>
su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
</programlisting>
291
      Then, create a symbolic link to it in <filename>/etc/rc3.d</> as
292
      <filename>S99postgresql</>.
293 294 295 296 297
     </para>
    </listitem>
   </itemizedlist>

  </para>
298

299
   <para>
300 301
    While the <command>postmaster</command> is running, its
    <acronym>PID</acronym> is stored in the file
302
    <filename>postmaster.pid</filename> in the data directory. This is
303 304 305
    used to prevent multiple <command>postmaster</command> processes
    running in the same data directory and can also be used for
    shutting down the <command>postmaster</command> process.
306 307
   </para>

308
   <sect2 id="postmaster-start-failures">
309
    <title>Server Start-up Failures</title>
310 311

    <para>
312 313 314 315 316
     There are several common reasons the server might fail to
     start. Check the server's log file, or start it by hand (without
     redirecting standard output or standard error) and see what error
     messages appear. Below we explain some of the most common error
     messages in more detail.
317 318 319 320
    </para>

    <para>
<screen>
321 322 323
LOG:  could not bind IPv4 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
FATAL:  could not create TCP/IP listen socket
324
</screen>
325
     This usually means just what it suggests: you tried to start
326
     another <command>postmaster</command> on the same port where one is already running.
327 328
     However, if the kernel error message is not <computeroutput>Address
     already in use</computeroutput> or some variant of that, there may
329
     be a different problem. For example, trying to start a <command>postmaster</command>
330
     on a reserved port number may draw something like:
331
<screen>
332
$ <userinput>postmaster -p 666</userinput>
333 334 335
LOG:  could not bind IPv4 socket: Permission denied
HINT:  Is another postmaster already running on port 666? If not, wait a few seconds and retry.
FATAL:  could not create TCP/IP listen socket
336 337 338 339
</screen>
    </para>

    <para>
340
     A message like
341
<screen>
342
FATAL:  could not create shared memory segment: Invalid argument
Peter Eisentraut's avatar
Peter Eisentraut committed
343
DETAIL:  Failed system call was shmget(key=5440001, size=4011376640, 03600).
344
</screen>
345
     probably means your kernel's limit on the size of shared memory is
346 347
     smaller than the work area <productname>PostgreSQL</productname>
     is trying to create (4011376640 bytes in this example). Or it could
348
     mean that you do not have System-V-style shared memory support
349
     configured into your kernel at all. As a temporary workaround, you
350
     can try starting the server with a smaller-than-normal number
351 352
     of buffers (<option>-B</option> switch). You will eventually want
     to reconfigure your kernel to increase the allowed shared memory
353
     size. You may also see this message when trying to start multiple
354
     servers on the same machine, if their total space requested
355
     exceeds the kernel limit.
356 357 358
    </para>

    <para>
359
     An error like
360
<screen>
361
FATAL:  could not create semaphores: No space left on device
Peter Eisentraut's avatar
Peter Eisentraut committed
362
DETAIL:  Failed system call was semget(5440126, 17, 03600).
363
</screen>
364 365 366 367 368
     does <emphasis>not</emphasis> mean you've run out of disk
     space. It means your kernel's limit on the number of <systemitem
     class="osname">System V</> semaphores is smaller than the number
     <productname>PostgreSQL</productname> wants to create. As above,
     you may be able to work around the problem by starting the
369
     server with a reduced number of allowed connections
370 371 372
     (<option>-N</option> switch), but you'll eventually want to
     increase the kernel limit.
    </para>
373

Peter Eisentraut's avatar
Peter Eisentraut committed
374
    <para>
375
     If you get an <quote>illegal system call</> error, it is likely that
376 377 378
     shared memory or semaphores are not supported in your kernel at
     all. In that case your only option is to reconfigure the kernel to
     enable these features.
Peter Eisentraut's avatar
Peter Eisentraut committed
379 380
    </para>

381
    <para>
382 383
     Details about configuring <systemitem class="osname">System V</>
     <acronym>IPC</> facilities are given in <xref linkend="sysvipc">.
384
    </para>
385 386 387 388 389 390
   </sect2>

   <sect2 id="client-connection-problems">
    <title>Client Connection Problems</title>

    <para>
391 392 393 394 395
     Although the error conditions possible on the client side are quite
     varied and application-dependent, a few of them might be directly
     related to how the server was started up. Conditions other than
     those shown below should be documented with the respective client
     application.
396 397 398 399
    </para>

    <para>
<screen>
400
psql: could not connect to server: Connection refused
401
        Is the server running on host "server.joe.com" and accepting
402
        TCP/IP connections on port 5432?
403 404 405
</screen>
     This is the generic <quote>I couldn't find a server to talk
     to</quote> failure. It looks like the above when TCP/IP
406 407
     communication is attempted. A common mistake is to forget to
     configure the server to allow TCP/IP connections.
408 409 410
    </para>

    <para>
411 412
     Alternatively, you'll get this when attempting Unix-domain socket
     communication to a local server:
413
<screen>
414
psql: could not connect to server: No such file or directory
415 416
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
417 418 419 420 421
</screen>
    </para>

    <para>
     The last line is useful in verifying that the client is trying to
422
     connect to the right place. If there is in fact no server
423 424
     running there, the kernel error message will typically be either
     <computeroutput>Connection refused</computeroutput> or
425
     <computeroutput>No such file or directory</computeroutput>, as
426 427
     illustrated. (It is important to realize that
     <computeroutput>Connection refused</computeroutput> in this context
428 429
     does <emphasis>not</emphasis> mean that the server got your
     connection request and rejected it. That case will produce a
430
     different message, as shown in <xref
431 432 433 434 435 436
     linkend="client-authentication-problems">.) Other error messages
     such as <computeroutput>Connection timed out</computeroutput> may
     indicate more fundamental problems, like lack of network
     connectivity.
    </para>
   </sect2>
437 438
  </sect1>

439
  <sect1 id="runtime-config">
440
   <Title>Run-time Configuration</Title>
441

442 443
   <indexterm>
    <primary>configuration</primary>
Peter Eisentraut's avatar
Peter Eisentraut committed
444
    <secondary>of the server</secondary>
445 446
   </indexterm>

447
   <para>
448 449 450 451
    There are a lot of configuration parameters that affect the
    behavior of the database system. In this subsection, we describe
    how to set configuration parameters; the following subsections
    discuss each parameter in detail.
452 453 454
   </para>

   <para>
455
    All parameter names are case-insensitive. Every parameter takes a
456
    value of one of the four types: boolean, integer, floating point,
457 458 459 460 461
    and string. Boolean values are <literal>ON</literal>,
    <literal>OFF</literal>, <literal>TRUE</literal>,
    <literal>FALSE</literal>, <literal>YES</literal>,
    <literal>NO</literal>, <literal>1</literal>, <literal>0</literal>
    (case-insensitive) or any non-ambiguous prefix of these.
462 463
   </para>

464
   <para>
465
    One way to set these parameters is to edit the file
466 467 468
    <filename>postgresql.conf</filename><indexterm><primary>postgresql.conf</></>
    in the data directory. (A default file is installed there.) An
    example of what this file might look like is:
469 470 471
<programlisting>
# This is a comment
log_connections = yes
472
log_destination = 'syslog'
473
search_path = '$user, public'
474
</programlisting>
475
    One parameter is specified per line. The equal sign between name and
476 477
    value is optional. Whitespace is insignificant and blank lines are
    ignored. Hash marks (<literal>#</literal>) introduce comments
478 479
    anywhere.  Parameter values that are not simple identifiers or
    numbers should be single-quoted.
480 481 482
   </para>

   <para>
483 484 485
    <indexterm>
     <primary>SIGHUP</primary>
    </indexterm>
486 487 488 489 490 491 492
    The configuration file is reread whenever the
    <command>postmaster</command> process receives a
    <systemitem>SIGHUP</> signal (which is most easily sent by means
    of <literal>pg_ctl reload</>). The <command>postmaster</command>
    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
Bruce Momjian's avatar
Bruce Momjian committed
493 494 495
    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.
496 497 498
   </para>

   <para>
499
    A second way to set these configuration parameters is to give them
500
    as a command line option to the <command>postmaster</command>, such as:
501
<programlisting>
502
postmaster -c log_connections=yes -c log_destination='syslog'
503
</programlisting>
504 505
    Command-line options override any conflicting settings in
    <filename>postgresql.conf</filename>.
506 507
   </para>

508 509
   <para>
    Occasionally it is also useful to give a command line option to
510
    one particular session only. The environment variable
511 512 513
    <envar>PGOPTIONS</envar> can be used for this purpose on the
    client side:
<programlisting>
514
env PGOPTIONS='-c geqo=off' psql
515
</programlisting>
516 517 518 519 520
    (This works for any <application>libpq</>-based client application, not
    just <application>psql</application>.) Note that this won't work for
    parameters that are fixed when the server is started, nor for
    parameters that require superuser permissions to change (not even
    if you are logging in as superuser).
521
   </para>
522

523
   <para>
524 525 526
    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
527 528 529 530 531 532 533 534
    commands <xref linkend="sql-alterdatabase"
    endterm="sql-alterdatabase-title"> and <xref
    linkend="sql-alteruser" endterm="sql-alteruser-title">,
    respectively, are used to configure these settings.  Per-database
    settings override anything received from the
    <command>postmaster</command> command-line or the configuration
    file, and in turn are overridden by per-user settings; both are
    overridden by per-session options.
535
   </para>
536

537
   <para>
538 539 540 541 542 543 544
    Some parameters can be changed in individual <acronym>SQL</acronym>
    sessions with the <xref linkend="SQL-SET" endterm="SQL-SET-title">
    command, for example:
<screen>
SET ENABLE_SEQSCAN TO OFF;
</screen>
    If <command>SET</> is allowed, it overrides all other sources of
545 546 547 548 549 550
    values for the parameter. Some parameters cannot be changed via
    <command>SET</command>: for example, if they control behavior that
    cannot reasonably be changed without restarting
    <productname>PostgreSQL</productname>.  Also, some parameters can
    be modified via <command>SET</command> by superusers, but not by
    ordinary users.
551 552 553 554 555 556 557 558 559 560 561 562 563 564
   </para>

   <para>
    The <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title">
    command allows inspection of the current values of all parameters.
   </para>

   <para>
    The virtual table <structname>pg_settings</structname>
    (described in <xref linkend="view-pg-settings">) also allows
    displaying and updating session run-time parameters.  It is equivalent
    to <command>SHOW</> and <command>SET</>, but can be more convenient
    to use because it can be joined with other tables, or selected from using
    any desired selection condition.
565
   </para>
566
    
567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614
   <sect2 id="runtime-config-configuration-files">
    <title>Configuration Files</title>

     <variablelist>

     <varlistentry id="guc-pgdata" xreflabel="pgdata">
      <term><varname>pgdata</varname> (<type>string</type>)</term>
      <listitem>
       <para>
         Specifies the directory to use for data storage (everything except
         configuration files).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-hba-conf" xreflabel="hba-conf">
      <term><varname>hba_conf</varname> (<type>string</type>)</term>
      <listitem>
       <para>
         Specifies the file name to use for configuration of host-based 
         authentication (HBA).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-ident-conf" xreflabel="ident-conf">
      <term><varname>ident_conf</varname> (<type>string</type>)</term>
      <listitem>
       <para>
         Specifies the file name to use for configuration of 
         <application>ident</> authentication.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="external-pidfile" xreflabel="external-pidfile">
      <term><varname>external_pidfile</varname> (<type>string</type>)</term>
      <listitem>
       <para>
         Specifies the location of an additional <application>postmaster</>
         process-id (PID) file for use by server administration programs.
       </para>
      </listitem>
     </varlistentry>

     </variablelist>
   </sect2>

615 616
   <sect2 id="runtime-config-connection">
    <title>Connections and Authentication</title>
617

618 619
    <sect3 id="runtime-config-connection-settings">
     <title>Connection Settings</title>
620

621
     <variablelist>
622 623 624

     <varlistentry id="guc-listen-addresses" xreflabel="listen_addresses">
      <term><varname>listen_addresses</varname> (<type>string</type>)</term>
625 626
      <listitem>
       <para>
627 628
         Specifies the TCP/IP address(es) on which the server is
         to listen for connections from client applications.  
629
         The value takes the form of a comma-separated list of host names
630 631 632 633 634 635 636 637
         and/or numeric IP addresses.  The special entry <literal>*</>
         corresponds to all available IP interfaces.
         If the list is empty, the server does not listen on any IP interface
         at all, in which case only Unix-domain sockets can be used to connect
         to it.
         The default value is <systemitem class="systemname">localhost</>,
         which allows only local <quote>loopback</> connections to be made.
         This parameter can only be set at server start.
638
       </para>
639 640
      </listitem>
     </varlistentry>
641 642 643 644 645 646 647 648 649 650 651 652 653

     <varlistentry id="guc-port" xreflabel="port">
      <term><varname>port</varname> (<type>integer</type>)</term>
      <indexterm><primary>port</></>
      <listitem>
       <para>
        The TCP port the server listens on; 5432 by default.  Note that the
        same port number is used for all IP addresses the server listens on.
        This parameter can only be set at server start.
       </para>
      </listitem>
     </varlistentry>

654
     <varlistentry id="guc-max-connections" xreflabel="max_connections">
655
      <term><varname>max_connections</varname> (<type>integer</type>)</term>
656 657
      <listitem>
       <para>
658
        Determines the maximum number of concurrent connections to the
659
        database server. The default is typically 100, but may be less
660 661 662
        if your kernel settings will not support it (as determined
        during <application>initdb</>).  This parameter can only be
        set at server start.
663 664 665 666 667 668 669
       </para>

       <para>
        Increasing this parameter may cause <productname>PostgreSQL</>
        to request more <systemitem class="osname">System V</> shared
        memory or semaphores than your operating system's default configuration
        allows. See <xref linkend="sysvipc"> for information on how to
670
        adjust those parameters, if necessary.
671
       </para>
672 673
      </listitem>
     </varlistentry>
674
     
675 676
     <varlistentry id="guc-superuser-reserved-connections"
     xreflabel="superuser_reserved_connections">
677
      <term><varname>superuser_reserved_connections</varname>
678
      (<type>integer</type>)</term>
679 680
      <listitem>
       <para>
681 682
        Determines the number of <quote>connection slots</quote> that
        are reserved for connections by <productname>PostgreSQL</>
683 684 685 686 687 688
        superusers.  At most <xref linkend="guc-max-connections">
        connections can ever be active simultaneously.  Whenever the
        number of active concurrent connections is at least
        <varname>max_connections</> minus
        <varname>superuser_reserved_connections</varname>, new
        connections will be accepted only for superusers.
689 690 691 692
       </para>

       <para>
        The default value is 2. The value must be less than the value of
693
        <varname>max_connections</varname>. This parameter can only be
694
        set at server start.
695 696 697
       </para>
      </listitem>
     </varlistentry>
698 699


700
     <varlistentry id="guc-unix-socket-directory" xreflabel="unix_socket_directory">
701
      <term><varname>unix_socket_directory</varname> (<type>string</type>)</term>
702 703
      <listitem>
       <para>
704 705 706 707
        Specifies the directory of the Unix-domain socket on which the
        server is to listen for
        connections from client applications.  The default is normally
        <filename>/tmp</filename>, but can be changed at build time.
708
        This parameter can only be set at server start.
709 710 711
       </para>
      </listitem>
     </varlistentry>
712

713
     <varlistentry id="guc-unix-socket-group" xreflabel="unix_socket_group">
714
      <term><varname>unix_socket_group</varname> (<type>string</type>)</term>
715 716
      <listitem>
       <para>
717
        Sets the owning group of the Unix-domain socket.  (The owning
718 719
        user of the socket is always the user that starts the
        server.)  In combination with the option
720
        <varname>unix_socket_permissions</varname> this can be used as
721
        an additional access control mechanism for Unix-domain connections.
722 723 724
        By default this is the empty string, which uses the default
        group for the current user.  This option can only be set at
        server start.
725 726 727 728
       </para>
      </listitem>
     </varlistentry>

729
     <varlistentry id="guc-unix-socket-permissions" xreflabel="unix_socket_permissions">
730
      <term><varname>unix_socket_permissions</varname> (<type>integer</type>)</term>
731 732
      <listitem>
       <para>
733
        Sets the access permissions of the Unix-domain socket.  Unix
734
        domain sockets use the usual Unix file system permission set.
735
        The option value is expected to be a numeric mode
736 737 738 739
        specification in the form accepted by the
        <function>chmod</function> and <function>umask</function>
        system calls.  (To use the customary octal format the number
        must start with a <literal>0</literal> (zero).)
740
       </para>
741

742 743 744
       <para>
        The default permissions are <literal>0777</literal>, meaning
        anyone can connect. Reasonable alternatives are
745
        <literal>0770</literal> (only user and group, see also
746
        <varname>unix_socket_group</varname>) and <literal>0700</literal>
747 748 749 750
        (only user). (Note that actually for a Unix domain socket, only write
        permission matters and there is no point in setting or revoking
        read or execute permissions.)
       </para>
751

752
       <para>
753 754
        This access control mechanism is independent of the one
        described in <xref linkend="client-authentication">.
755 756 757
       </para>

       <para>
758
        This option can only be set at server start.
759 760 761
       </para>
      </listitem>
     </varlistentry>
762
     
763
     <varlistentry id="guc-rendezvous-name" xreflabel="rendezvous_name">
764
      <term><varname>rendezvous_name</varname> (<type>string</type>)</term>
765 766
      <listitem>
       <para>
767 768 769 770 771
        Specifies the <productname>Rendezvous</productname> broadcast
        name.  By default, the computer name is used, specified as an
        empty string ''.  This option is only meaningful on platforms
        that support <productname>Rendezvous</productname>.  This
        option can only be set at server start.
772 773 774 775
       </para>
      </listitem>
     </varlistentry>
     
776 777 778 779 780 781
     </variablelist>
     </sect3>
     <sect3 id="runtime-config-connection-security">
     <title>Security and Authentication</title>
     
     <variablelist>
782
     <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
783
      <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
Peter Eisentraut's avatar
Peter Eisentraut committed
784 785
      <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
      <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
786 787
      <listitem>
       <para>
788 789 790 791 792
        Maximum time to complete client authentication, in seconds. If a
        would-be client has not completed the authentication protocol in
        this much time, the server breaks the connection. This prevents
        hung clients from occupying a connection indefinitely. This
        option can only be set at server start or in the
793
        <filename>postgresql.conf</filename> file. The default is 60.
794 795 796
       </para>
      </listitem>
     </varlistentry>
797
     
798
     <varlistentry id="guc-ssl" xreflabel="ssl">
799
      <indexterm>
800
       <primary>SSL</primary>
801
      </indexterm>
802
      <term><varname>ssl</varname> (<type>boolean</type>)</term>
803 804
      <listitem>
       <para>
805 806 807
        Enables <acronym>SSL</> connections. Please read
        <xref linkend="ssl-tcp"> before using this. The default
        is off.
808 809 810 811
       </para>
      </listitem>
     </varlistentry>

812
     <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
813
      <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
814 815
      <listitem>
       <para>
816 817 818 819 820 821 822
        When a password is specified in <xref
        linkend="sql-createuser" endterm="sql-createuser-title"> or
        <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
        without writing either <literal>ENCRYPTED</> or
        <literal>UNENCRYPTED</>, this option determines whether the
        password is to be encrypted. The default is on (encrypt the
        password).
823 824 825 826
       </para>
      </listitem>
     </varlistentry>

827
     <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
828
      <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
829 830
      <listitem>
       <para>
831 832
        Sets the location of the Kerberos server key file. See
        <xref linkend="kerberos-auth"> for details.
833 834 835 836
       </para>
      </listitem>
     </varlistentry>

837
     <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
838
      <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
839 840
      <listitem>
       <para>
841
        This allows per-database user names.  It is off by default.
842 843
       </para>

844 845 846 847 848 849 850 851 852
       <para>
        If this is on, you should create users as <literal>username@dbname</>.
        When <literal>username</> is passed by a connecting client,
        <literal>@</> and the database name is appended to the user
        name and that database-specific user name is looked up by the
        server. Note that when you create users with names containing
        <literal>@</> within the SQL environment, you will need to
        quote the user name.
       </para>
853

854
       <para>
855 856 857 858
        With this option enabled, you can still create ordinary global
        users.  Simply append <literal>@</> when specifying the user
        name in the client.  The <literal>@</> will be stripped off
        before the user name is looked up by the server.
859
       </para>
860 861 862 863 864 865 866 867

       <note>
        <para>
         This feature is intended as a temporary measure until a
         complete solution is found.  At that time, this option will
         be removed.
        </para>
       </note>
868 869 870
      </listitem>
     </varlistentry>

871 872 873 874 875
    </variablelist>
    </sect3>
   </sect2>

   <sect2 id="runtime-config-resource">
876
    <title>Resource Consumption</title>
877 878 879 880 881

    <sect3 id="runtime-config-resource-memory">
     <title>Memory</title>

     <variablelist>
882
     <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
883
      <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
884 885
      <listitem>
       <para>
886
        Sets the number of shared memory buffers used by the database
887 888 889
        server. The default is typically 1000, but may be less if your
        kernel settings will not support it (as determined during
        <application>initdb</>).  Each buffer is 8192 bytes, unless a
Peter Eisentraut's avatar
Peter Eisentraut committed
890
        different value of <symbol>BLCKSZ</symbol> was chosen when building
891
        the server.  This setting must be at least 16, as well as at
892
        least twice the value of <xref linkend="guc-max-connections">;
893 894 895 896
        however, settings significantly higher than the minimum are
        usually needed for good performance.  Values of a few thousand
        are recommended for production installations.  This option can
        only be set at server start.
897 898 899 900 901 902 903
       </para>

       <para>
        Increasing this parameter may cause <productname>PostgreSQL</>
        to request more <systemitem class="osname">System V</> shared
        memory than your operating system's default configuration
        allows. See <xref linkend="sysvipc"> for information on how to
904
        adjust those parameters, if necessary.
905 906 907 908
       </para>
      </listitem>
     </varlistentry>

909
     <varlistentry id="guc-work-mem" xreflabel="work_mem">
910
      <term><varname>work_mem</varname> (<type>integer</type>)</term>
911 912
      <listitem>
       <para>
913 914
        Specifies the amount of memory to be used by internal sort operations
        and hash tables before switching to temporary disk files. The value is
915 916 917 918
        specified in kilobytes, and defaults to 1024 kilobytes (1 MB).
        Note that for a complex query, several sort or hash operations might be
        running in parallel; each one will be allowed to use as much memory
        as this value specifies before it starts to put data into temporary
919 920 921 922 923 924
        files. Also, several running sessions could be doing such operations
        concurrently.  So the total memory used could be many
        times the value of <varname>work_mem</varname>; it is necessary to
        keep this fact in mind when choosing the value. Sort operations are
        used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
        merge joins.
925
        Hash tables are used in hash joins, hash-based aggregation, and
926
        hash-based processing of <literal>IN</> subqueries.
927 928 929
       </para>
      </listitem>
     </varlistentry>
930
     
931
     <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
932
      <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
933 934
      <listitem>
       <para>
935 936 937 938 939 940 941 942 943 944
        Specifies the maximum amount of memory to be used in maintenance
        operations, such as <command>VACUUM</command>, <command>CREATE
        INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>.
        The value is specified in kilobytes, and defaults to 16384 kilobytes
        (16 MB).  Since only one of these operations can be executed at 
        a time by a database session, and an installation normally doesn't
        have very many of them happening concurrently, it's safe to set this
        value significantly larger than <varname>work_mem</varname>.  Larger
        settings may improve performance for vacuuming and for restoring
        database dumps.
945 946 947 948
       </para>
      </listitem>
     </varlistentry>

949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968
     <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
      <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Specifies the maximum safe depth of the server's execution stack.
        The ideal setting for this parameter is the actual stack size limit
        enforced by the kernel (as set by <literal>ulimit -s</> or local
        equivalent), less a safety margin of a megabyte or so.  The safety
        margin is needed because the stack depth is not checked in every
        routine in the server, but only in key potentially-recursive routines
        such as expression evaluation.  Setting the parameter higher than
        the actual kernel limit will mean that a runaway recursive function
        can crash an individual backend process.  The default setting is
        2048 KB (two megabytes), which is conservatively small and unlikely
        to risk crashes.  However, it may be too small to allow execution
        of complex functions.
       </para>
      </listitem>
     </varlistentry>

969 970 971 972 973 974
     </variablelist>
     </sect3>
     <sect3 id="runtime-config-resource-fsm">
     <title>Free Space Map</title>

     <variablelist>
975
     <varlistentry id="guc-max-fsm-pages" xreflabel="max_fsm_pages">
976
      <term><varname>max_fsm_pages</varname> (<type>integer</type>)</term>
977 978
      <listitem>
       <para>
979 980 981
        Sets the maximum number of disk pages for which free space will
        be tracked in the shared free-space map.  Six bytes of shared memory
        are consumed for each page slot.  This setting must be more than
982
        16 * <varname>max_fsm_relations</varname>.  The default is 20000.
983
        This option can only be set at server start.
984 985 986
       </para>
      </listitem>
     </varlistentry>
987

988
     <varlistentry id="guc-max-fsm-relations" xreflabel="max_fsm_relations">
989
      <term><varname>max_fsm_relations</varname> (<type>integer</type>)</term>
990 991 992 993 994 995 996 997 998 999 1000 1001 1002
      <listitem>
       <para>
        Sets the maximum number of relations (tables and indexes) for which
        free space will be tracked in the shared free-space map.  Roughly
        fifty bytes of shared memory are consumed for each slot.
        The default is 1000.
        This option can only be set at server start.
       </para>
      </listitem>
     </varlistentry>
     
     </variablelist>
     </sect3>
1003 1004
     <sect3 id="runtime-config-resource-kernel">
     <title>Kernel Resource Usage</title>
1005 1006
     <variablelist>

1007
     <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
1008
      <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
1009 1010 1011
      <listitem>
       <para>
        Sets the maximum number of simultaneously open files allowed to each
1012 1013 1014 1015 1016 1017 1018 1019
        server subprocess. The default is 1000. If the kernel is enforcing
        a safe per-process limit, you don't need to worry about this setting.
        But on some platforms (notably, most BSD systems), the kernel will
        allow individual processes to open many more files than the system
        can really support when a large number of processes all try to open
        that many files. If you find yourself seeing <quote>Too many open
        files</> failures, try reducing this setting.
        This option can only be set at server start.
1020 1021 1022 1023
       </para>
      </listitem>
     </varlistentry>
     
1024
     <varlistentry id="guc-preload-libraries" xreflabel="preload_libraries">
1025
      <term><varname>preload_libraries</varname> (<type>string</type>)</term>
1026 1027 1028 1029
      <indexterm><primary>preload_libraries</></>
      <listitem>
       <para>
        This variable specifies one or more shared libraries that are
1030 1031 1032 1033
        to be preloaded at server start. A parameterless
        initialization function can optionally be called for each
        library.  To specify that, add a colon and the name of the
        initialization function after the library name. For example
1034 1035
        <literal>'$libdir/mylib:mylib_init'</literal> would cause
        <literal>mylib</> to be preloaded and <literal>mylib_init</>
1036 1037
        to be executed. If more than one library is to be loaded,
        separate their names with commas.
1038
       </para>
1039

1040
       <para>
1041 1042 1043 1044 1045
        If <literal>mylib</> or <literal>mylib_init</> are not found, the
        server will fail to start.
       </para>

       <para>
1046 1047 1048
        <productname>PostgreSQL</productname> procedural language
        libraries may be preloaded in this way, typically by using the
        syntax <literal>'$libdir/plXXX:plXXX_init'</literal> where
1049 1050
        <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
        <literal>tcl</>, or <literal>python</>.
1051 1052 1053 1054 1055
       </para>

       <para>
        By preloading a shared library (and initializing it if
        applicable), the library startup time is avoided when the
1056
        library is first used.  However, the time to start each new
1057 1058
        server process may increase, even if that process never
        uses the library.
1059 1060
       </para>
      </listitem>
1061 1062 1063 1064 1065 1066
     </varlistentry>

     </variablelist>
    </sect3>

    <sect3 id="runtime-config-resource-vacuum-cost">
1067 1068 1069
     <title>Cost-Based Vacuum Delay</title>

     <para>
1070 1071 1072 1073 1074 1075 1076 1077
      During the execution of <xref linkend="sql-vacuum"
      endterm="sql-vacuum-title"> and <xref linkend="sql-analyze"
      endterm="sql-analyze-title"> commands, the system maintains an
      internal counter that keeps track of the estimated cost of the
      various I/O operations that are performed.  When the accumulated
      cost reaches a limit (specified by
      <varname>vacuum_cost_limit</varname>), the process performing
      the operation will sleep for a while (specified by
1078
      <varname>vacuum_cost_delay</varname>). Then it will reset the
1079 1080 1081 1082
      counter and continue execution.
     </para>

     <para>
1083
      The intent of this feature is to allow administrators to reduce
1084
      the I/O impact of these commands on concurrent database
1085
      activity. There are many situations in which it is not very
1086
      important that maintenance commands like
1087
      <command>VACUUM</command> and <command>ANALYZE</command> finish
1088
      quickly; however, it is usually very important that these
1089 1090 1091 1092 1093 1094 1095
      commands do not significantly interfere with the ability of the
      system to perform other database operations. Cost-based vacuum
      delay provides a way for administrators to achieve this.
     </para>

     <para>
      This feature is disabled by default. To enable it, set the
1096
      <varname>vacuum_cost_delay</varname> variable to a nonzero
1097 1098
      value.
     </para>
1099 1100

     <variablelist>
1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117
      <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
       <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)</term>
       <listitem>
        <para>
         The length of time, in milliseconds, that the process will sleep
         when the cost limit has been exceeded.
         The default value is 0, which disables the cost-based vacuum
         delay feature.  Positive values enable cost-based vacuuming.
         Note that on many systems, the effective resolution
         of sleep delays is 10 milliseconds; setting
         <varname>vacuum_cost_delay</varname> to a value that is
         not a multiple of 10 may have the same results as setting it
         to the next higher multiple of 10.
        </para>
       </listitem>
      </varlistentry>

1118
      <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1119 1120 1121
       <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
       <listitem>
        <para>
1122
         The estimated cost for vacuuming a buffer found in the shared buffer
1123 1124 1125 1126 1127 1128
         cache. It represents the cost to lock the buffer pool, lookup
         the shared hash table and scan the content of the page. The
         default value is 1.
        </para>
       </listitem>
      </varlistentry>
1129

1130
      <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1131 1132 1133
       <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
       <listitem>
        <para>
1134
         The estimated cost for vacuuming a buffer that has to be read from
1135 1136 1137 1138 1139 1140
         disk.  This represents the effort to lock the buffer pool,
         lookup the shared hash table, read the desired block in from
         the disk and scan its content. The default value is 10.
        </para>
       </listitem>
      </varlistentry>
1141

1142
      <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1143 1144 1145
       <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
       <listitem>
        <para>
1146
         The estimated cost charged when vacuum modifies a block that was
1147 1148 1149 1150 1151 1152
         previously clean. It represents the extra I/O required to
         flush the dirty block out to disk again. The default value is
         20.
        </para>
       </listitem>
      </varlistentry>
1153

1154
      <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1155 1156 1157
       <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
       <listitem>
        <para>
1158 1159
         The accumulated cost that will cause the vacuuming process to sleep.
         The default value is 200.
1160 1161 1162
        </para>
       </listitem>
      </varlistentry>
1163
     </variablelist>
1164 1165 1166

     <note>
      <para>
1167
       There are certain operations that hold critical locks and should
1168 1169 1170 1171
       therefore complete as quickly as possible.  Cost-based vacuum
       delays do not occur during such operations.  Therefore it is
       possible that the cost accumulates far higher than the specified
       limit.  To avoid uselessly long delays in such cases, the actual
1172
       delay is calculated as <varname>vacuum_cost_delay</varname> *
1173 1174
       <varname>accumulated_balance</varname> /
       <varname>vacuum_cost_limit</varname> with a maximum of
1175
       <varname>vacuum_cost_delay</varname> * 4.
1176 1177 1178
      </para>
     </note>

1179
    </sect3>
1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208

    <sect3 id="runtime-config-resource-background-writer">
     <title>Background Writer</title>

     <para>
      Beginning in <productname>PostgreSQL</> 8.0, there is a separate server
      process called the <firstterm>background writer</>, whose sole function
      is to issue writes of <quote>dirty</> shared buffers.  The intent is
      that server processes handling user queries should seldom or never have
      to wait for a write to occur, because the background writer will do it.
      This arrangement also reduces the performance penalty associated with
      checkpoints.  The background writer will continuously trickle out dirty
      pages to disk, so that only a few pages will need to be forced out when
      checkpoint time arrives, instead of the storm of dirty-buffer writes that
      formerly occurred at each checkpoint.  However there is a net overall
      increase in I/O load, because where a repeatedly-dirtied page might
      before have been written only once per checkpoint interval, the
      background writer might write it several times in the same interval.
      In most situations a continuous low load is preferable to periodic
      spikes, but the parameters discussed in this section can be used to tune
      the behavior for local needs.
     </para>

     <variablelist>
      <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
       <term><varname>bgwriter_delay</varname> (<type>integer</type>)</term>
       <listitem>
        <para>
         Specifies the delay between activity rounds for the
1209 1210 1211 1212 1213 1214 1215 1216 1217 1218
         background writer.  In each round the writer issues writes
         for some number of dirty buffers (controllable by the
         following parameters).  The selected buffers will always be
         the least recently used ones among the currently dirty
         buffers.  It then sleeps for <varname>bgwriter_delay</>
         milliseconds, and repeats.  The default value is 200. Note
         that on many systems, the effective resolution of sleep
         delays is 10 milliseconds; setting <varname>bgwriter_delay</>
         to a value that is not a multiple of 10 may have the same
         results as setting it to the next higher multiple of 10.
1219 1220 1221 1222 1223 1224 1225 1226 1227 1228
         This option can only be set at server start or in the
         <filename>postgresql.conf</filename> file.
        </para>
       </listitem>
      </varlistentry>

      <varlistentry id="guc-bgwriter-percent" xreflabel="bgwriter_percent">
       <term><varname>bgwriter_percent</varname> (<type>integer</type>)</term>
       <listitem>
        <para>
1229 1230 1231 1232
         In each round, no more than this percentage of the currently
         dirty buffers will be written (rounding up any fraction to
         the next whole number of buffers).  The default value is
         1. This option can only be set at server start or in the
1233 1234 1235 1236 1237 1238 1239 1240 1241
         <filename>postgresql.conf</filename> file.
        </para>
       </listitem>
      </varlistentry>

      <varlistentry id="guc-bgwriter-maxpages" xreflabel="bgwriter_maxpages">
       <term><varname>bgwriter_maxpages</varname> (<type>integer</type>)</term>
       <listitem>
        <para>
1242 1243 1244
         In each round, no more than this many dirty buffers will be
         written. The default value is 100. This option can only be
         set at server start or in the
1245 1246 1247 1248 1249 1250 1251 1252
         <filename>postgresql.conf</filename> file.
        </para>
       </listitem>
      </varlistentry>
     </variablelist>

     <para>
      Smaller values of <varname>bgwriter_percent</varname> and
1253 1254 1255 1256
      <varname>bgwriter_maxpages</varname> reduce the extra I/O load
      caused by the background writer, but leave more work to be done
      at checkpoint time.  To reduce load spikes at checkpoints,
      increase the values.
1257 1258 1259
     </para>
    </sect3>

1260
   </sect2>
1261

1262
   <sect2 id="runtime-config-wal">
1263
    <title>Write Ahead Log</title>
1264

1265 1266 1267 1268 1269 1270 1271 1272 1273
   <para>
    See also <xref linkend="wal-configuration"> for details on WAL
    tuning.
   </para>

    <sect3 id="runtime-config-wal-settings">
     <title>Settings</title>
     <variablelist>
     
1274
     <varlistentry id="guc-fsync" xreflabel="fsync">
1275 1276 1277 1278
      <indexterm>
       <primary>fsync</primary>
      </indexterm>

1279
      <term><varname>fsync</varname> (<type>boolean</type>)</term>
1280 1281
      <listitem>
       <para>
1282 1283 1284 1285 1286
        If this option is on, the <productname>PostgreSQL</> server
        will use the <function>fsync()</> system call in several places
        to make sure that updates are physically written to disk. This
        insures that a database cluster will recover to a
        consistent state after an operating system or hardware crash.
1287
       </para>
1288

1289
       <para>
1290 1291 1292 1293
        However, using <function>fsync()</function> results in a
        performance penalty: when a transaction is committed,
        <productname>PostgreSQL</productname> must wait for the
        operating system to flush the write-ahead log to disk.  When
1294
        <varname>fsync</varname> is disabled, the operating system is
1295 1296 1297 1298 1299
        allowed to do its best in buffering, ordering, and delaying
        writes. This can result in significantly improved performance.
        However, if the system crashes, the results of the last few
        committed transactions may be lost in part or whole. In the
        worst case, unrecoverable data corruption may occur.
1300 1301 1302
        (Crashes of the database server itself are <emphasis>not</>
        a risk factor here.  Only an operating-system-level crash
        creates a risk of corruption.)
1303
       </para>
1304

1305
       <para>
1306
        Due to the risks involved, there is no universally correct
1307 1308
        setting for <varname>fsync</varname>. Some administrators
        always disable <varname>fsync</varname>, while others only
1309 1310
        turn it off for bulk loads, where there is a clear restart
        point if something goes wrong, whereas some administrators
1311 1312
        always leave <varname>fsync</varname> enabled. The default is
        to enable <varname>fsync</varname>, for maximum reliability.
1313 1314
        If you trust your operating system, your hardware, and your
        utility company (or your battery backup), you can consider
1315
        disabling <varname>fsync</varname>.
1316
       </para>
1317

1318 1319 1320 1321 1322 1323 1324
       <para>
        This option can only be set at server start or in the
        <filename>postgresql.conf</filename> file.
       </para>
      </listitem>
     </varlistentry>
     
1325
     <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1326
      <term><varname>wal_sync_method</varname> (<type>string</type>)</term>
1327 1328 1329 1330
      <listitem>
       <para>
        Method used for forcing WAL updates out to disk.  Possible
        values are
1331 1332 1333 1334
        <literal>fsync</> (call <function>fsync()</> at each commit),
        <literal>fdatasync</> (call <function>fdatasync()</> at each commit),
        <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>), and
        <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>).
1335
        Not all of these choices are available on all platforms.
1336
        If <varname>fsync</varname> is off then this setting is irrelevant.
1337 1338 1339 1340 1341 1342
        This option can only be set at server start or in the
        <filename>postgresql.conf</filename> file.
       </para>
      </listitem>
     </varlistentry>
     
1343
     <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1344
      <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1345 1346
      <listitem>
       <para>
1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382
        Number of disk-page buffers allocated in shared memory for WAL data.
        The default is 8.  The setting need only be large enough to hold
        the amount of WAL data generated by one typical transaction.
        This option can only be set at server start.
       </para>
      </listitem>
     </varlistentry>
                
     <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
      <term><varname>commit_delay</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Time delay between writing a commit record to the WAL buffer
        and flushing the buffer out to disk, in microseconds. A
        nonzero delay can allow multiple transactions to be committed
        with only one <function>fsync()</function> system call, if
        system load is high enough that additional transactions become
        ready to commit within the given interval. But the delay is
        just wasted if no other transactions become ready to
        commit. Therefore, the delay is only performed if at least
        <varname>commit_siblings</varname> other transactions are
        active at the instant that a server process has written its
        commit record. The default is zero (no delay).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
      <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Minimum number of concurrent open transactions to require
        before performing the <varname>commit_delay</> delay. A larger
        value makes it more probable that at least one other
        transaction will become ready to commit during the delay
        interval. The default is five.
1383 1384 1385
       </para>
      </listitem>
     </varlistentry>
1386

1387 1388 1389 1390
     </variablelist>
     </sect3>
     <sect3 id="runtime-config-wal-checkpoints">
     <title>Checkpoints</title>
1391

1392
    <variablelist>
1393
     <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1394
      <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1395 1396
      <listitem>
       <para>
1397 1398 1399 1400
        Maximum distance between automatic WAL checkpoints, in log
        file segments (each segment is normally 16 megabytes). The
        default is three.  This option can only be set at server start
        or in the <filename>postgresql.conf</filename> file.
1401 1402 1403
       </para>
      </listitem>
     </varlistentry>
1404

1405
     <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1406
      <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1407 1408
      <listitem>
       <para>
1409 1410 1411 1412
        Maximum time between automatic WAL checkpoints, in
        seconds. The default is 300 seconds.  This option can only be
        set at server start or in the <filename>postgresql.conf</>
        file.
1413 1414 1415
       </para>
      </listitem>
     </varlistentry>
1416

1417
     <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1418
      <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1419 1420
      <listitem>
       <para>
1421
        Write a message to the server logs if checkpoints caused by
1422 1423 1424
        the filling of checkpoint segment files happen closer together
        than this many seconds.  The default is 30 seconds.
        Zero turns off the warning.
1425 1426 1427
       </para>
      </listitem>
     </varlistentry>
1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438

     </variablelist>
     </sect3>
     <sect3 id="runtime-config-wal-archiving">
     <title>Archiving</title>

    <variablelist>
     <varlistentry id="guc-archive-command" xreflabel="archive_command">
      <term><varname>archive_command</varname> (<type>string</type>)</term>
      <listitem>
       <para>
1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451
        The shell command to execute to archive a completed segment of
        the WAL file series. If this is an empty string (the default),
        WAL archiving is disabled. Any <literal>%p</> in the string is
        replaced by the absolute path of the file to archive, and any
        <literal>%f</> is replaced by the file name only. Use
        <literal>%%</> to embed an actual <literal>%</> character in the
        command. For more information see <xref
        linkend="backup-archiving-wal">. This option can only be set at
        server start or in the <filename>postgresql.conf</filename>
        file.
       </para>
       <para>
        It is important for the command to return a zero exit status only if
1452
        it succeeds.  Examples:
1453 1454 1455 1456
<programlisting>
archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
archive_command = 'copy "%p" /mnt/server/archivedir/"%f"'  # Win32
</programlisting>
1457 1458 1459
       </para>
      </listitem>
     </varlistentry>
1460 1461 1462 1463 1464 1465
     
     </variablelist>
    </sect3>
   </sect2>

   <sect2 id="runtime-config-query">
1466
    <title>Query Planning</title>
1467 1468

    <sect3 id="runtime-config-query-enable">
1469
     <title>Planner Method Configuration</title>
1470

1471 1472 1473
      <para>
       These configuration parameters provide a crude method for
       influencing the query plans chosen by the query optimizer. If
1474 1475 1476 1477 1478
       the default plan chosen by the optimizer for a particular query
       is not optimal, a temporary solution may be found by using one
       of these configuration parameters to force the optimizer to
       choose a better plan. Other ways to improve the quality of the
       plans chosen by the optimizer include configuring the <xref
1479
       linkend="runtime-config-query-constants"
1480 1481 1482 1483 1484 1485 1486
       endterm="runtime-config-query-constants-title">, running <xref
       linkend="sql-analyze" endterm="sql-analyze-title"> more
       frequently, increasing the value of the <xref
       linkend="guc-default-statistics-target"> configuration parameter,
       and increasing the amount of statistics collected for a
       particular column using <command>ALTER TABLE SET
       STATISTICS</command>.
1487
      </para>
1488

1489
     <variablelist>
1490
     <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
1491
      <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
1492 1493
      <listitem>
       <para>
1494 1495 1496
        Enables or disables the query planner's use of hashed
        aggregation plan types. The default is on. This is used for
        debugging the query planner.
1497 1498 1499
       </para>
      </listitem>
     </varlistentry>
1500

1501
     <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
1502
      <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
1503 1504
      <listitem>
       <para>
1505
        Enables or disables the query planner's use of hash-join plan
1506 1507
        types. The default is on. This is used for debugging the query
        planner.
1508 1509 1510 1511
       </para>
      </listitem>
     </varlistentry>

1512
     <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
1513 1514 1515 1516
      <indexterm>
       <primary>index scan</primary>
      </indexterm>

1517
      <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
1518 1519
      <listitem>
       <para>
1520
        Enables or disables the query planner's use of index-scan plan
1521 1522
        types. The default is on. This is used for debugging the query
        planner.
1523 1524 1525 1526
       </para>
      </listitem>
     </varlistentry>

1527
     <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
1528
      <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
1529 1530
      <listitem>
       <para>
1531
        Enables or disables the query planner's use of merge-join plan
1532 1533
        types. The default is on. This is used for debugging the query
        planner.
1534 1535 1536 1537
       </para>
      </listitem>
     </varlistentry>

1538
     <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
1539
      <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
1540 1541
      <listitem>
       <para>
1542 1543 1544 1545 1546
        Enables or disables the query planner's use of nested-loop join
        plans. It's not possible to suppress nested-loop joins entirely,
        but turning this variable off discourages the planner from using
        one if there are other methods available. The default is
        on. This is used for debugging the query planner.
1547 1548 1549 1550
       </para>
      </listitem>
     </varlistentry>

1551
     <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
1552 1553 1554 1555
      <indexterm>
       <primary>sequential scan</primary>
      </indexterm>

1556
      <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
1557 1558
      <listitem>
       <para>
1559 1560 1561 1562 1563
        Enables or disables the query planner's use of sequential scan
        plan types. It's not possible to suppress sequential scans
        entirely, but turning this variable off discourages the planner
        from using one if there are other methods available. The
        default is on. This is used for debugging the query planner.
1564 1565 1566 1567
       </para>
      </listitem>
     </varlistentry>

1568
     <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
1569
      <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
1570 1571
      <listitem>
       <para>
1572 1573 1574 1575 1576
        Enables or disables the query planner's use of explicit sort
        steps. It's not possible to suppress explicit sorts entirely,
        but turning this variable off discourages the planner from
        using one if there are other methods available. The default
        is on. This is used for debugging the query planner.
1577
       </para>
1578 1579
      </listitem>
     </varlistentry>
1580

1581
     <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
1582
      <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
1583
      <listitem>
1584
       <para>
1585 1586 1587
        Enables or disables the query planner's use of <acronym>TID</>
        scan plan types. The default is on. This is used for debugging
        the query planner.
1588 1589 1590
       </para>
      </listitem>
     </varlistentry>
1591 1592 1593 1594
     
     </variablelist>
     </sect3>
     <sect3 id="runtime-config-query-constants">
1595 1596 1597
     <title id="runtime-config-query-constants-title">
      Planner Cost Constants
     </title>
1598

1599 1600 1601 1602
   <note>
    <para>
     Unfortunately, there is no well-defined method for determining
     ideal values for the family of <quote>cost</quote> variables that
1603
     appear below. You are encouraged to experiment and share
1604 1605 1606 1607 1608 1609
     your findings.
    </para>
   </note>

     <variablelist>
     
1610
     <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
1611
      <term><varname>effective_cache_size</varname> (<type>floating point</type>)</term>
1612 1613
      <listitem>
       <para>
1614
        Sets the planner's assumption about the effective size of the
1615 1616 1617
        disk cache (that is, the portion of the kernel's disk cache
        that will be used for <productname>PostgreSQL</productname>
        data files). This is measured in disk pages, which are
1618
        normally 8192 bytes each. The default is 1000.
1619
       </para>
1620 1621
      </listitem>
     </varlistentry>
1622

1623
     <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
1624
      <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
1625 1626 1627 1628 1629
      <listitem>
       <para>
        Sets the query planner's estimate of the cost of a
        nonsequentially fetched disk page. This is measured as a
        multiple of the cost of a sequential page fetch. A higher
1630 1631 1632
        value makes it more likely a sequential scan will be used, a
        lower value makes it more likely an index scan will be
        used. The default is four.
1633
       </para>
1634 1635 1636
      </listitem>
     </varlistentry>

1637
     <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
1638
      <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
1639 1640 1641
      <listitem>
       <para>
        Sets the query planner's estimate of the cost of processing
1642
        each row during a query. This is measured as a fraction of
1643
        the cost of a sequential page fetch. The default is 0.01.
1644 1645 1646 1647
       </para>
      </listitem>
     </varlistentry>

1648
     <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
1649
      <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
1650 1651
      <listitem>
       <para>
1652
        Sets the query planner's estimate of the cost of processing
1653
        each index row during an index scan. This is measured as a
1654 1655
        fraction of the cost of a sequential page fetch. The default
        is 0.001.
1656 1657 1658 1659
       </para>
      </listitem>
     </varlistentry>
    
1660
     <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
1661
      <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
1662 1663 1664 1665
      <listitem>
       <para>
        Sets the planner's estimate of the cost of processing each
        operator in a <literal>WHERE</> clause. This is measured as a fraction of
1666
        the cost of a sequential page fetch. The default is 0.0025.
1667 1668 1669 1670
       </para>
      </listitem>
     </varlistentry>

1671
     </variablelist>
1672

1673 1674
    </sect3>
     <sect3 id="runtime-config-query-geqo">
1675
     <title>Genetic Query Optimizer</title>
1676 1677 1678

     <variablelist>

1679
     <varlistentry id="guc-geqo" xreflabel="geqo">
1680 1681 1682 1683 1684 1685 1686
      <indexterm>
       <primary>genetic query optimization</primary>
      </indexterm>
      <indexterm>
       <primary>GEQO</primary>
       <see>genetic query optimization</see>
      </indexterm>
1687
      <term><varname>geqo</varname> (<type>boolean</type>)</term>
1688 1689
      <listitem>
       <para>
1690
        Enables or disables genetic query optimization, which is an
1691 1692 1693 1694
        algorithm that attempts to do query planning without
        exhaustive searching. This is on by default. The
        <varname>geqo_threshold</varname> variable provides a more
        granular way to disable GEQO for certain classes of queries.
1695 1696 1697 1698
       </para>
      </listitem>
     </varlistentry>

1699
     <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
1700
      <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
1701 1702
      <listitem>
       <para>
1703 1704 1705
        Use genetic query optimization to plan queries with at least
        this many <literal>FROM</> items involved. (Note that an outer
        <literal>JOIN</> construct counts as only one <literal>FROM</>
1706
        item.) The default is 12. For simpler queries it is usually best
1707 1708
        to use the deterministic, exhaustive planner, but for queries with
        many tables the deterministic planner takes too long.
1709 1710 1711 1712
       </para>
      </listitem>
     </varlistentry>

1713 1714 1715 1716 1717 1718
     <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
      <term><varname>geqo_effort</varname>
      (<type>integer</type>)</term>
      <listitem>
       <para>
        Controls the tradeoff between planning time and query plan
1719 1720 1721 1722 1723
        efficiency in GEQO. This variable must be an integer in the
        range from 1 to 10. The default value is 5. Larger values
        increase the time spent doing query planning, but also
        increase the likelyhood that an efficient query plan will be
        chosen.
1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736
       </para>

       <para>
        <varname>geqo_effort</varname> doesn't actually do anything
        directly; it is only used to compute the default values for
        the other variables that influence GEQO behavior (described
        below). If you prefer, you can set the other parameters by
        hand instead.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
1737
      <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750
      <listitem>
       <para>
        Controls the pool size used by GEQO. The pool size is the
        number of individuals in the genetic population.  It must be
        at least two, and useful values are typically 100 to 1000.  If
        it is set to zero (the default setting) then a suitable
        default is chosen based on <varname>geqo_effort</varname> and
        the number of tables in the query.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
1751
      <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764
      <listitem>
       <para>
        Controls the number of generations used by GEQO.  Generations
        specifies the number of iterations of the algorithm.  It must
        be at least one, and useful values are in the same range as
        the pool size.  If it is set to zero (the default setting)
        then a suitable default is chosen based on
        <varname>geqo_pool_size</varname>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
1765
      <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
1766 1767
      <listitem>
       <para>
1768 1769 1770
        Controls the selection bias used by GEQO. The selection bias
        is the selective pressure within the population. Values can be
        from 1.50 to 2.00; the latter is the default.
1771 1772 1773
       </para>
      </listitem>
     </varlistentry>
1774 1775 1776 1777
     
     </variablelist>
    </sect3>
     <sect3 id="runtime-config-query-other">
1778
     <title>Other Planner Options</title>
1779 1780

     <variablelist>
1781

1782
     <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
1783
      <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
1784 1785
      <listitem>
       <para>
1786 1787 1788 1789 1790 1791 1792
        Sets the default statistics target for table columns that have
        not had a column-specific target set via <command>ALTER TABLE
        SET STATISTICS</>.  Larger values increase the time needed to
        do <command>ANALYZE</>, but may improve the quality of the
        planner's estimates. The default is 10. For more information
        on the use of statistics by the <productname>PostgreSQL</>
        query planner, refer to <xref linkend="planner-stats">.
1793 1794 1795
       </para>
      </listitem>
     </varlistentry>
1796

1797
     <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
1798
      <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
1799 1800
      <listitem>
       <para>
1801 1802 1803 1804
        The planner will merge sub-queries into upper queries if the
        resulting <literal>FROM</literal> list would have no more than
        this many items.  Smaller values reduce planning time but may
        yield inferior query plans.  The default is 8.  It is usually
1805
        wise to keep this less than <xref linkend="guc-geqo-threshold">.
1806 1807 1808 1809
       </para>
      </listitem>
     </varlistentry>

1810
     <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
1811
      <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
1812 1813
      <listitem>
       <para>
1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835
        The planner will rewrite explicit inner <literal>JOIN</>
        constructs into lists of <literal>FROM</> items whenever a
        list of no more than this many items in total would
        result. Prior to <productname>PostgreSQL</> 7.4, joins
        specified via the <literal>JOIN</literal> construct would
        never be reordered by the query planner. The query planner has
        subsequently been improved so that inner joins written in this
        form can be reordered; this configuration variable controls
        the extent to which this reordering is performed.
        <note>
         <para>
          At present, the order of outer joins specified via the
          <literal>JOIN</> construct is never adjusted by the query
          planner; therefore, <varname>join_collapse_limit</> has no
          effect on this behavior. The planner may be improved to
          reorder some classes of outer joins in a future release of
          <productname>PostgreSQL</productname>.
         </para>
        </note>
       </para>

       <para>
1836
        By default, this variable is set the same as
1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855
        <varname>from_collapse_limit</varname>, which is appropriate
        for most uses. Setting it to 1 prevents any reordering of
        inner <literal>JOIN</>s. Thus, the explicit join order
        specified in the query will be the actual order in which the
        relations are joined. The query planner does not always choose
        the optimal join order; advanced users may elect to
        temporarily set this variable to 1, and then specify the join
        order they desire explicitly. Another consequence of setting
        this variable to 1 is that the query planner will behave more
        like the <productname>PostgreSQL</productname> 7.3 query
        planner, which some users might find useful for backward
        compatibility reasons.
       </para>

       <para>
        Setting this variable to a value between 1 and
        <varname>from_collapse_limit</varname> might be useful to
        trade off planning time against the quality of the chosen plan
        (higher values produce better plans).
1856 1857 1858
       </para>
      </listitem>
     </varlistentry>
1859 1860 1861 1862 1863 1864
     
     </variablelist>
    </sect3>
   </sect2>

   <sect2 id="runtime-config-logging">
1865
    <title>Error Reporting and Logging</title>
1866

Peter Eisentraut's avatar
Peter Eisentraut committed
1867 1868 1869 1870
    <indexterm zone="runtime-config-logging">
     <primary>server log</primary>
    </indexterm>

1871 1872
    <sect3 id="runtime-config-logging-where">
     <title>Where to log</title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1873

1874 1875
     <indexterm zone="runtime-config-logging-where">
      <primary>where to log</primary>
Peter Eisentraut's avatar
Peter Eisentraut committed
1876 1877
     </indexterm>

1878
     <variablelist>
1879

1880 1881
     <varlistentry id="guc-log-destination" xreflabel="log_destination">
      <term><varname>log_destination</varname> (<type>string</type>)</term>
1882 1883
      <listitem>
       <para>
1884 1885 1886 1887 1888 1889 1890 1891
        <productname>PostgreSQL</productname> supports several methods
         for logging server messages, including
         <systemitem>stderr</systemitem> and
         <systemitem>syslog</systemitem>. On Windows, 
         <systemitem>eventlog</systemitem> is also supported. Set this
         option to a list of desired log destinations separated by
         commas. The default is to log to <systemitem>stderr</systemitem> 
         only.
1892 1893
         This option can only be set at server start or in the
         <filename>postgresql.conf</filename> configuration file.
1894
       </para>
1895 1896 1897
      </listitem>
     </varlistentry>

1898 1899 1900 1901
     <varlistentry id="guc-redirect-stderr" xreflabel="redirect_stderr">
      <term><varname>redirect_stderr</varname> (<type>boolean</type>)</term>
       <listitem>
        <para>
1902 1903 1904 1905 1906 1907 1908 1909
          This option allows messages sent to <application>stderr</> to be
          captured and redirected into log files.
          This option, in combination with logging to <application>stderr</>,
          is often more useful than
          logging to <application>syslog</>, since some types of messages
          may not appear in <application>syslog</> output (a common example
          is dynamic-linker failure messages).
          This option can only be set at server start.
1910 1911 1912 1913 1914 1915 1916 1917
        </para>
       </listitem>
     </varlistentry>

     <varlistentry id="guc-log-directory" xreflabel="log_directory">
      <term><varname>log_directory</varname> (<type>string</type>)</term>
       <listitem>
        <para>
1918
          When <varname>redirect_stderr</> is enabled, this option
1919
          determines the directory in which log files will be created.
1920 1921
          It may be specified as an absolute path, or relative to the
          cluster data directory.
1922 1923 1924 1925 1926 1927
         This option can only be set at server start or in the
         <filename>postgresql.conf</filename> configuration file.
        </para>
       </listitem>
     </varlistentry>

1928 1929
     <varlistentry id="guc-log-filename" xreflabel="log_filename">
      <term><varname>log_filename</varname> (<type>string</type>)</term>
1930 1931
       <listitem>
        <para>
1932
          When <varname>redirect_stderr</> is enabled, this option
1933 1934 1935 1936 1937 1938 1939 1940 1941 1942
          sets the file names of the created log files.  The value
          is treated as a <systemitem>strftime</> pattern,
          so <literal>%</>-escapes
          can be used to specify time-varying file names.
          If no <literal>%</>-escapes are present,
          <productname>PostgreSQL</productname> will
          append the epoch of the new log file's open time.  For example,
          if <varname>log_filename</> were <literal>server_log</>, then the
          chosen file name would be <literal>server_log.1093827753</>
          for a log starting at Sun Aug 29 19:02:33 2004 MST.
1943 1944 1945 1946 1947 1948 1949 1950 1951 1952
         This option can only be set at server start or in the
         <filename>postgresql.conf</filename> configuration file.
        </para>
       </listitem>
     </varlistentry>

     <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
      <term><varname>log_rotation_age</varname> (<type>integer</type>)</term>
       <listitem>
        <para>
1953 1954 1955 1956 1957
          When <varname>redirect_stderr</> is enabled, this option
          determines the maximum lifetime of an individual log file.
          After this many minutes have elapsed, a new log file will
          be created.  Set to zero to disable time-based creation of
          new log files.
1958 1959 1960 1961 1962 1963 1964 1965 1966 1967
         This option can only be set at server start or in the
         <filename>postgresql.conf</filename> configuration file.
        </para>
       </listitem>
     </varlistentry>

     <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
      <term><varname>log_rotation_size</varname> (<type>integer</type>)</term>
       <listitem>
        <para>
1968 1969 1970 1971 1972
          When <varname>redirect_stderr</> is enabled, this option
          determines the maximum size of an individual log file.
          After this many kilobytes have been emitted into a log file,
          a new log file will be created.  Set to zero to disable size-based
          creation of new log files.
1973 1974 1975 1976 1977 1978
         This option can only be set at server start or in the
         <filename>postgresql.conf</filename> configuration file.
        </para>
       </listitem>
     </varlistentry>

1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998
     <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
      <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)</term>
       <listitem>
        <para>
          When <varname>redirect_stderr</> is enabled, this option will cause
          <productname>PostgreSQL</productname> to truncate (overwrite),
          rather than append to, any existing log file of the same name.
          However, truncation will occur only when a new file is being opened
          due to time-based rotation, not during server startup or size-based
          rotation.  When false, pre-existing files will be appended to in
          all cases.  For example, using this option in combination with
          a <varname>log_filename</> like <literal>postgresql-%H.log</>
          would result in generating twenty-four hourly log files and then
          cyclically overwriting them.
         This option can only be set at server start or in the
         <filename>postgresql.conf</filename> configuration file.
        </para>
       </listitem>
     </varlistentry>

1999
     <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
2000
      <term><varname>syslog_facility</varname> (<type>string</type>)</term>
2001 2002
       <listitem>
        <para>
2003
          When logging to <application>syslog</> is enabled, this option
2004 2005
          determines the <application>syslog</application>
          <quote>facility</quote> to be used.  You may choose
2006 2007 2008 2009 2010
          from <literal>LOCAL0</>, <literal>LOCAL1</>,
          <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
          <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
          the default is <literal>LOCAL0</>. See also the
          documentation of your system's
2011
          <application>syslog</application> daemon.
2012
          This option can only be set at server start.
2013 2014 2015 2016
        </para>
       </listitem>
     </varlistentry>
     
2017
     <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
2018
      <term><varname>syslog_ident</varname> (<type>string</type>)</term>
2019 2020
       <listitem>
        <para>
2021
         When logging to <application>syslog</> is enabled, this option
2022
         determines the program name used to identify
2023
         <productname>PostgreSQL</productname> messages in
2024
         <application>syslog</application> logs. The default is
2025
         <literal>postgres</literal>.
2026
          This option can only be set at server start.
2027 2028 2029
        </para>
       </listitem>
      </varlistentry>
2030 2031 2032 2033 2034 2035 2036 2037
      
      </variablelist>
    </sect3>
     <sect3 id="runtime-config-logging-when">
     <title>When To Log</title>

     <variablelist>

2038
     <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
2039
      <term><varname>client_min_messages</varname> (<type>string</type>)</term>
2040 2041 2042 2043 2044 2045 2046 2047 2048 2049
      <listitem>
       <para>
        Controls which message levels are sent to the client.
        Valid values are <literal>DEBUG5</>,
        <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
        <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
        <literal>WARNING</>, and <literal>ERROR</>.  Each level
        includes all the levels that follow it.  The later the level,
        the fewer messages are sent.  The default is
        <literal>NOTICE</>.  Note that <literal>LOG</> has a different
2050
        rank here than in <varname>log_min_messages</>.
2051 2052 2053 2054
       </para>
      </listitem>
     </varlistentry>

2055
     <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
2056
      <term><varname>log_min_messages</varname> (<type>string</type>)</term>
2057 2058
      <listitem>
       <para>
2059
        Controls which message levels are written to the server log.
Peter Eisentraut's avatar
Peter Eisentraut committed
2060
        Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
2061 2062 2063 2064 2065 2066 2067
        <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
        <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
        <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
        <literal>PANIC</>.  Each level includes all the levels that
        follow it.  The later the level, the fewer messages are sent
        to the log.  The default is <literal>NOTICE</>.  Note that
        <literal>LOG</> has a different rank here than in
2068
        <varname>client_min_messages</>.
2069
        Only superusers can increase this option.
2070 2071 2072 2073
       </para>
      </listitem>
     </varlistentry>

2074
     <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
2075
      <term><varname>log_error_verbosity</varname> (<type>string</type>)</term>
2076 2077
      <listitem>
       <para>
2078
        Controls the amount of detail written in the server log for each
Peter Eisentraut's avatar
Peter Eisentraut committed
2079 2080 2081
        message that is logged.  Valid values are <literal>TERSE</>,
        <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
        fields to displayed messages.
2082 2083 2084 2085
       </para>
      </listitem>
     </varlistentry>

2086
     <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
2087
      <term><varname>log_min_error_statement</varname> (<type>string</type>)</term>
2088 2089 2090 2091
      <listitem>
       <para>
        Controls whether or not the SQL statement that causes an error
        condition will also be recorded in the server log. All SQL
2092 2093
        statements that cause an error of the specified level or
        higher are logged.  The default is
2094
        <literal>PANIC</literal> (effectively turning this feature
2095
        off for normal use). Valid values are <literal>DEBUG5</literal>,
2096 2097 2098 2099 2100 2101 2102 2103 2104
        <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
        <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
        <literal>INFO</literal>, <literal>NOTICE</literal>,
        <literal>WARNING</literal>, <literal>ERROR</literal>,
        <literal>FATAL</literal>, and <literal>PANIC</literal>.  For
        example, if you set this to <literal>ERROR</literal> then all
        SQL statements causing errors, fatal errors, or panics will be
        logged. Enabling this option can be helpful in tracking down
        the source of any errors that appear in the server log.
2105
        Only superusers can increase this option.
2106 2107 2108 2109
       </para>
      </listitem>
     </varlistentry>
     
2110
     <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
2111
      <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
2112 2113 2114
       <listitem>
        <para>
         Sets a minimum statement execution time (in milliseconds)
2115 2116
         that causes a statement to be logged.  All SQL statements
         that run for the time specified or longer will be logged with
2117 2118
         their duration.  Setting this to zero will print
         all queries and their durations.  Minus-one (the default)
2119
         disables the feature.  For example, if you set it to 
2120 2121 2122 2123 2124
         <literal>250</literal> then all SQL statements that run 250ms 
         or longer will be logged.  Enabling this option can be
         useful in tracking down unoptimized queries in your applications.
         Only superusers can increase this or set it to minus-one if this 
         option is set by the administrator.
2125 2126 2127
        </para>
       </listitem>
      </varlistentry>
2128

2129
     <varlistentry id="guc-silent-mode" xreflabel="silent_mode">
2130
      <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
2131 2132
      <listitem>
       <para>
2133
        Runs the server silently. If this option is set, the server
2134
        will automatically run in background and any controlling
2135 2136 2137
        terminals are disassociated (same effect as
        <command>postmaster</>'s <option>-S</option> option).
        The server's standard output and standard error are redirected
2138 2139 2140
        to <literal>/dev/null</>, so any messages sent to them will be lost.
        Unless <application>syslog</> logging is selected or
        <varname>redirect_stderr</> is enabled, using this option
2141
        is discouraged because it makes it impossible to see error messages.
2142 2143 2144 2145 2146
       </para>
      </listitem>
     </varlistentry>

     </variablelist>
2147 2148 2149

       <para>
        Here is a list of the various message severity levels used in
Peter Eisentraut's avatar
Peter Eisentraut committed
2150
        these settings:
2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 2225 2226 2227 2228 2229 2230
        <variablelist>
         <varlistentry>
          <term><literal>DEBUG[1-5]</literal></term>
          <listitem>
           <para>
            Provides information for use by developers.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>INFO</literal></term>
          <listitem>
           <para>
            Provides information implicitly requested by the user,
            e.g., during <command>VACUUM VERBOSE</>.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>NOTICE</literal></term>
          <listitem>
           <para>
            Provides information that may be helpful to users, e.g.,
            truncation of long identifiers and the creation of indexes as part
            of primary keys.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>WARNING</literal></term>
          <listitem>
           <para>
            Provides warnings to the user, e.g., <command>COMMIT</>
            outside a transaction block.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>ERROR</literal></term>
          <listitem>
           <para>
            Reports an error that caused the current transaction to abort.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>LOG</literal></term>
          <listitem>
           <para>
            Reports information of interest to administrators, e.g.,
            checkpoint activity.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>FATAL</literal></term>
          <listitem>
           <para>
            Reports an error that caused the current session to abort.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>PANIC</literal></term>
          <listitem>
           <para>
            Reports an error that caused all sessions to abort.
           </para>
          </listitem>
         </varlistentry>
        </variablelist>
       </para>

2231 2232 2233
    </sect3>
     <sect3 id="runtime-config-logging-what">
     <title>What To Log</title>
2234

2235
     <variablelist>
2236

2237
     <varlistentry>
2238 2239 2240 2241
      <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
      <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
      <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
      <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
2242 2243
      <listitem>
       <para>
2244 2245 2246 2247 2248 2249 2250 2251 2252
        These options enable various debugging output to be sent to
        the client or server log. For each executed query, they print
        the resulting parse tree, the query rewriter output, or the
        execution plan.  <varname>debug_pretty_print</varname> indents
        these displays to produce a more readable but much longer
        output format.  <varname>client_min_messages</varname> or
        <varname>log_min_messages</varname> must be
        <literal>DEBUG1</literal> or lower to send output to the
        client or server logs. These options are off by default.
2253
       </para>
2254 2255
      </listitem>
     </varlistentry>
2256

2257
     <varlistentry id="guc-log-connections" xreflabel="log_connections">
2258
      <term><varname>log_connections</varname> (<type>boolean</type>)</term>
2259 2260 2261 2262 2263 2264 2265 2266 2267 2268
      <listitem>
       <para>
        This outputs a line to the server logs detailing each successful
        connection. This is off by default, although it is probably very
        useful. This option can only be set at server start or in the
        <filename>postgresql.conf</filename> configuration file.
       </para>
      </listitem>
     </varlistentry>

2269
     <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
2270 2271 2272
      <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
2273 2274 2275 2276 2277
        This outputs a line in the server logs similar to
        <varname>log_connections</varname> but at session termination,
        and includes the duration of the session.  This is off by
        default. This option can only be set at server start or in the
        <filename>postgresql.conf</filename> configuration file.
2278 2279 2280 2281 2282
       </para>
      </listitem>
     </varlistentry>


2283
     <varlistentry id="guc-log-duration" xreflabel="log_duration">
2284
      <term><varname>log_duration</varname> (<type>boolean</type>)</term>
2285 2286 2287
      <listitem>
       <para>
        Causes the duration of every completed statement to be logged.
2288
        To use this option, it is recommended that you also enable
2289
        <varname>log_statement</> and if not using <application>syslog</>
2290
        log the PID using <varname>log_line_prefix</> so that you
2291 2292 2293
        can link the statement to the duration using the process
        ID. The default is off.  Only superusers can turn off this
        option if it is enabled by the administrator.
2294 2295 2296 2297
       </para>
      </listitem>
     </varlistentry>
     
2298
     <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
Bruce Momjian's avatar
Add:  
Bruce Momjian committed
2299 2300 2301
      <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
      <listitem>
       <para>
2302 2303 2304 2305 2306 2307 2308
         This is a <function>printf</>-style string that is output at the
         beginning of each log line. The default is an empty string.
         Each recognized escape is replaced as outlined 
         below - anything else that looks like an escape is ignored. Other
         characters are copied straight to the log line. Some escapes are
         only recognised by session processes, and do not apply to
         background processes such as the postmaster. <application>Syslog</>
2309
         produces its own 
2310 2311 2312
         timestamp and process ID information, so you probably do not want to
         use those escapes if you are using <application>syslog</>.
         This option can only be set at server start or in the
2313 2314
         <filename>postgresql.conf</filename> configuration file.

2315 2316 2317 2318 2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359
         <informaltable>
          <tgroup cols="3">
           <thead>
            <row>
             <entry>Escape</entry>
             <entry>Effect</entry>
             <entry>Session only</entry>
             </row>
            </thead>
           <tbody>
            <row>
             <entry><literal>%u</literal></entry>
             <entry>User Name</entry>
             <entry>Yes</entry>
            </row>
            <row>
             <entry><literal>%d</literal></entry>
             <entry>Database Name</entry>
             <entry>Yes</entry>
            </row>
            <row>
             <entry><literal>%r</literal></entry>
             <entry>Remote Hostname or IP address, and Remote Port</entry>
             <entry>Yes</entry>
            </row>
            <row>
             <entry><literal>%p</literal></entry>
             <entry>Process ID</entry>
             <entry>No</entry>
            </row>
            <row>
             <entry><literal>%t</literal></entry>
             <entry>Timestamp</entry>
             <entry>No</entry>
            </row>
            <row>
             <entry><literal>%i</literal></entry>
             <entry>Command Tag. This is the command which generated the log
              line.</entry>
             <entry>Yes</entry>
            </row>
            <row>
             <entry><literal>%c</literal></entry>
             <entry>Session ID. A unique identifier for each session.
             It is 2 4-byte hexadecimal numbers (without leading zeros) 
2360
             separated by a dot. The numbers
2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390
             are the Session Start Time and the Process ID, so this can also
             be used as a space saving way of printing these items.</entry>
             <entry>Yes</entry>
            </row>
            <row>
             <entry><literal>%l</literal></entry>
             <entry>Number of the log line for each process,
              starting at 1</entry>
             <entry>No</entry>
            </row>
            <row>
             <entry><literal>%s</literal></entry>
             <entry>Session Start Timestamp</entry>
             <entry>Yes</entry>
            </row>
            <row>
             <entry><literal>%x</literal></entry>
             <entry>Does not produce any output, but tells non-session
             processes to stop at this point in the string. Ignored by
             session processes.</entry>
             <entry>No</entry>
            </row>
            <row>
             <entry><literal>%%</literal></entry>
             <entry>Literal <literal>%</></entry>
             <entry>No</entry>
            </row>
           </tbody>
          </tgroup>
         </informaltable>
Bruce Momjian's avatar
Add:  
Bruce Momjian committed
2391 2392 2393 2394
       </para>
      </listitem>
     </varlistentry>

2395
     <varlistentry id="guc-log-statement" xreflabel="log_statement">
2396
      <term><varname>log_statement</varname> (<type>string</type>)</term>
2397 2398
      <listitem>
       <para>
2399
        Controls which SQL statements are logged. Valid values are
2400 2401 2402 2403 2404 2405 2406 2407
        <literal>all</>, <literal>ddl</>, <literal>mod</>, and
        <literal>none</>. <literal>ddl</> logs all data definition
        commands like <literal>CREATE</>, <literal>ALTER</>, and
        <literal>DROP</> commands. <literal>mod</> logs all
        <literal>ddl</> statements, plus <literal>INSERT</>,
        <literal>UPDATE</>, <literal>DELETE</>, <literal>TRUNCATE</>,
        and <literal>COPY FROM</>. <literal>PREPARE</> and
        <literal>EXPLAIN ANALYZE</> statements are also considered for
2408 2409 2410 2411 2412
        appropriate commands. 
       </para>
       <para>
        The default is <literal>none</>. Only superusers can reduce 
        the detail of this option if it has been set by an administrator.
2413
       </para>
2414 2415 2416

       <note>
        <para>
2417 2418 2419 2420
         The <command>EXECUTE</command> statement not considered a
         <literal>ddl</> or <literal>mod</> statement.  When it is logged, 
         only the name of the prepared statement is reported, not the
         actual prepared statement.
2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431
        </para>

        <para>
         When a function is defined in a server-side language like
         <application>PL/pgSQL</application>, any queries executed by
         the function will only be logged the first time that the
         function is invoked in a particular session. This is because
         the <application>PL/pgSQL</application> keeps a cache of the
         query plans produced for the SQL statements in the function.
        </para>
       </note>
2432 2433 2434
      </listitem>
     </varlistentry>

2435
     <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
2436
      <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
2437 2438
      <listitem>
       <para>
2439 2440 2441 2442 2443
        By default, connection logs only show the IP address of the
        connecting host. If you want it to show the host name you can
        turn this on, but depending on your host name resolution setup
        it might impose a non-negligible performance penalty. This
        option can only be set at server start.
2444 2445 2446 2447
       </para>
      </listitem>
     </varlistentry>

2448 2449 2450 2451
     </variablelist>
    </sect3>
   </sect2>

2452 2453 2454 2455 2456 2457 2458 2459
   <sect2 id="runtime-config-statistics">
    <title>Runtime Statistics</title>

    <sect3 id="runtime-config-statistics-monitor">
     <title>Statistics Monitoring</title>
     <variablelist>

     <varlistentry>
2460 2461 2462 2463
      <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
      <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
      <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
      <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
2464 2465 2466 2467
      <listitem>
       <para>
        For each query, write performance statistics of the respective
        module to the server log. This is a crude profiling
2468 2469
        instrument.  <varname>log_statement_stats</varname> reports total
        statement statistics, while the others report per-state statistics.
2470 2471
        <varname>log_statement_stats</varname> can not be enabled with 
        the other options.  All of these options are disabled by default.  
2472 2473
        Only superusers can turn off any of these options if they have
        been enabled by the administrator.
2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484
       </para>
      </listitem>
     </varlistentry>

     </variablelist>

    </sect3>
    <sect3 id="runtime-config-statistics-collector">
     <title>Query and Index Statistics Collector</title>
     <variablelist>

2485
     <varlistentry id="guc-stats-start-collector" xreflabel="stats_start_collector">
2486
      <term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>
2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497
      <listitem>
       <para>
        Controls whether the server should start the
        statistics-collection subprocess.  This is on by default, but
        may be turned off if you know you have no interest in
        collecting statistics.  This option can only be set at server
        start.
       </para>
      </listitem>
     </varlistentry>

2498
     <varlistentry id="guc-stats-command-string" xreflabel="stats_command_string">
2499
      <term><varname>stats_command_string</varname> (<type>boolean</type>)</term>
2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514
      <listitem>
       <para>
        Enables the collection of statistics on the currently
        executing command of each session, along with the time at
        which that command began execution. This option is off by
        default. Note that even when enabled, this information is not
        visible to all users, only to superusers and the user owning
        the session being reported on; so it should not represent a
        security risk. This data can be accessed via the
        <structname>pg_stat_activity</structname> system view; refer
        to <xref linkend="monitoring"> for more information.
       </para>
      </listitem>
     </varlistentry>

2515
     <varlistentry id="guc-stats-block-level" xreflabel="guc_stats_block_level">
2516
      <term><varname>stats_block_level</varname> (<type>boolean</type>)</term>
2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529
      <listitem>
       <para>
        Enables the collection of block-level statistics on database
        activity. This option is disabled by default. If this option
        is enabled, the data that is produced can be accessed via the
        <structname>pg_stat</structname> and
        <structname>pg_statio</structname> family of system views;
        refer to <xref linkend="monitoring"> for more information.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-stats-row-level" xreflabel="guc_stats_row_level">
2530
      <term><varname>stats_row_level</varname> (<type>boolean</type>)</term>
2531 2532
      <listitem>
       <para>
2533 2534 2535
        Enables the collection of row-level statistics on database
        activity. This option is disabled by default. If this option
        is enabled, the data that is produced can be accessed via the
2536 2537 2538 2539 2540 2541 2542
        <structname>pg_stat</structname> and
        <structname>pg_statio</structname> family of system views;
        refer to <xref linkend="monitoring"> for more information.
       </para>
      </listitem>
     </varlistentry>

2543
     <varlistentry id="guc-stats-reset-on-server-start" xreflabel="stats_reset_on_server_start">
2544
      <term><varname>stats_reset_on_server_start</varname> (<type>boolean</type>)</term>
2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558
      <listitem>
       <para>
        If on, collected statistics are zeroed out whenever the server
        is restarted. If off, statistics are accumulated across server
        restarts. The default is on. This option can only be set at
        server start.
       </para>
      </listitem>
     </varlistentry>

     </variablelist>
    </sect3>
   </sect2>

2559 2560 2561 2562 2563 2564
   <sect2 id="runtime-config-client">
    <title>Client Connection Defaults</title>

    <sect3 id="runtime-config-client-statement">
     <title>Statement Behavior</title>
     <variablelist>
2565

2566
     <varlistentry id="guc-search-path" xreflabel="search_path">
2567
      <term><varname>search_path</varname> (<type>string</type>)</term>
2568 2569
      <indexterm><primary>search_path</></>
      <indexterm><primary>path</><secondary>for schemas</></>
2570 2571
      <listitem>
       <para>
2572 2573 2574 2575 2576 2577 2578
        This variable specifies the order in which schemas are searched
        when an object (table, data type, function, etc.) is referenced by a
        simple name with no schema component.  When there are objects of
        identical names in different schemas, the one found first
        in the search path is used.  An object that is not in any of the
        schemas in the search path can only be referenced by specifying
        its containing schema with a qualified (dotted) name.
2579 2580 2581
       </para>

       <para>
2582
        The value for <varname>search_path</varname> has to be a comma-separated
2583 2584 2585 2586
        list of schema names.  If one of the list items is
        the special value <literal>$user</literal>, then the schema
        having the name returned by <function>SESSION_USER</> is substituted, if there
        is such a schema.  (If not, <literal>$user</literal> is ignored.)
2587
       </para>
2588

2589
       <para>
2590 2591 2592 2593 2594 2595 2596 2597
        The system catalog schema, <literal>pg_catalog</>, is always
        searched, whether it is mentioned in the path or not.  If it is
        mentioned in the path then it will be searched in the specified
        order.  If <literal>pg_catalog</> is not in the path then it will
        be searched <emphasis>before</> searching any of the path items.
        It should also be noted that the temporary-table schema,
        <literal>pg_temp_<replaceable>nnn</></>, is implicitly searched before any of
        these.
2598
       </para>
2599

2600 2601 2602 2603 2604 2605
       <para>
        When objects are created without specifying a particular target
        schema, they will be placed in the first schema listed
        in the search path.  An error is reported if the search path is
        empty.
       </para>
2606

2607 2608 2609 2610 2611 2612 2613 2614 2615 2616
       <para>
        The default value for this parameter is
        <literal>'$user, public'</literal> (where the second part will be
        ignored if there is no schema named <literal>public</>).
        This supports shared use of a database (where no users
        have private schemas, and all share use of <literal>public</>),
        private per-user schemas, and combinations of these.  Other
        effects can be obtained by altering the default search path
        setting, either globally or per-user.
       </para>
2617

2618
       <para>
2619
        The current effective value of the search path can be examined
2620 2621
        via the <acronym>SQL</acronym> function
        <function>current_schemas()</>.  This is not quite the same as
2622
        examining the value of <varname>search_path</varname>, since
2623
        <function>current_schemas()</> shows how the requests
2624
        appearing in <varname>search_path</varname> were resolved.
2625 2626 2627 2628
       </para>

       <para>
        For more information on schema handling, see <xref linkend="ddl-schemas">.
2629 2630 2631 2632
       </para>
      </listitem>
     </varlistentry>

2633
     <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
2634 2635 2636
      <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
2637 2638 2639 2640 2641 2642
        This parameter is normally true.  When set to false, it disables
        validation of the function body string in <xref
        linkend="sql-createfunction"
        endterm="sql-createfunction-title">.  Disabling validation is
        occasionally useful to avoid problems such as forward
        references when restoring function definitions from a dump.
2643 2644 2645
       </para>
      </listitem>
     </varlistentry>
2646

2647
     <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
2648 2649 2650 2651
      <indexterm>
       <primary>transaction isolation level</primary>
      </indexterm>

2652
      <term><varname>default_transaction_isolation</varname> (<type>string</type>)</term>
2653 2654
      <listitem>
       <para>
2655 2656 2657 2658 2659 2660
        Each SQL transaction has an isolation level, which can be
        either <quote>read uncommitted</quote>, <quote>read
        committed</quote>, <quote>repeatable read</quote>, or
        <quote>serializable</quote>.  This parameter controls the
        default isolation level of each new transaction. The default
        is <quote>read committed</quote>.
2661 2662 2663
       </para>

       <para>
2664
        Consult <xref linkend="mvcc"> and <xref linkend="sql-set-transaction"> for more
2665 2666 2667 2668 2669
        information.
       </para>
      </listitem>
     </varlistentry>

2670
     <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
2671 2672 2673 2674
      <indexterm>
       <primary>read-only transaction</primary>
      </indexterm>

2675
      <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 2686 2687
      <listitem>
       <para>
        A read-only SQL transaction cannot alter non-temporary tables.
        This parameter controls the default read-only status of each new
        transaction. The default is false (read/write).
       </para>

       <para>
        Consult <xref linkend="sql-set-transaction"> for more information.
       </para>
      </listitem>
     </varlistentry>
2688
     
2689
     <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
2690
      <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
2691 2692
      <listitem>
       <para>
2693
        Aborts any statement that takes over the specified number of
2694
        milliseconds.  A value of zero (the default) turns off the limitation.
2695 2696 2697
       </para>
      </listitem>
     </varlistentry>
2698 2699 2700 2701 2702
     
     </variablelist>
    </sect3>
     <sect3 id="runtime-config-client-format">
     <title>Locale and Formatting</title>
2703

2704
     <variablelist>
2705

2706 2707
     <varlistentry id="guc-datestyle" xreflabel="DateStyle">
      <term><varname>DateStyle</varname> (<type>string</type>)</term>
2708
      <indexterm><primary>date style</></>
2709 2710
      <listitem>
       <para>
2711 2712 2713
        Sets the display format for date and time values, as well as the
        rules for interpreting ambiguous date input values. For
        historical reasons, this variable contains two independent
Peter Eisentraut's avatar
Peter Eisentraut committed
2714
        components: the output format specification (<literal>ISO</>,
2715 2716 2717 2718 2719 2720 2721 2722
        <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
        and the input/output specification for year/month/day ordering
        (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
        can be set separately or together. The keywords <literal>Euro</>
        and <literal>European</> are synonyms for <literal>DMY</>; the
        keywords <literal>US</>, <literal>NonEuro</>, and
        <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
        <xref linkend="datatype-datetime"> for more information. The
2723
        default is <literal>ISO, MDY</>.
2724 2725 2726 2727
       </para>
      </listitem>
     </varlistentry>

2728
     <varlistentry id="guc-timezone" xreflabel="timezone">
2729
      <term><varname>timezone</varname> (<type>string</type>)</term>
2730
      <indexterm><primary>time zone</></>
2731 2732
      <listitem>
       <para>
2733 2734 2735 2736
        Sets the time zone for displaying and interpreting time
        stamps.  The default is to use whatever the system environment
        specifies as the time zone.  See <xref
        linkend="datatype-datetime"> for more information.
2737
       </para>
2738 2739 2740
      </listitem>
     </varlistentry>

2741
     <varlistentry id="guc-australian-timezones" xreflabel="australian_timezones">
2742
      <term><varname>australian_timezones</varname> (<type>boolean</type>)</term>
Peter Eisentraut's avatar
Peter Eisentraut committed
2743
      <indexterm><primary>time zone</><secondary>Australian</></>
2744 2745
      <listitem>
       <para>
2746 2747 2748 2749 2750
        If set to true, <literal>ACST</literal>,
        <literal>CST</literal>, <literal>EST</literal>, and
        <literal>SAT</literal> are interpreted as Australian time
        zones rather than as North/South American time zones and
        Saturday. The default is false.
2751
       </para>
2752 2753
      </listitem>
     </varlistentry>
Tom Lane's avatar
Tom Lane committed
2754

2755
     <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
2756 2757 2758 2759
      <indexterm>
       <primary>significant digits</primary>
      </indexterm>
      <indexterm>
Peter Eisentraut's avatar
Peter Eisentraut committed
2760 2761
       <primary>floating-point</primary>
       <secondary>display</secondary>
2762 2763
      </indexterm>

2764
      <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
2765
      <listitem>
Tom Lane's avatar
Tom Lane committed
2766
       <para>
2767 2768 2769 2770 2771 2772 2773 2774
        This parameter adjusts the number of digits displayed for
        floating-point values, including <type>float4</>, <type>float8</>,
        and geometric data types.  The parameter value is added to the
        standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
        as appropriate).  The value can be set as high as 2, to include
        partially-significant digits; this is especially useful for dumping
        float data that needs to be restored exactly.  Or it can be set
        negative to suppress unwanted digits.
Tom Lane's avatar
Tom Lane committed
2775
       </para>
2776 2777
      </listitem>
     </varlistentry>
2778

2779
     <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
2780
      <term><varname>client_encoding</varname> (<type>string</type>)</term>
Peter Eisentraut's avatar
Peter Eisentraut committed
2781
      <indexterm><primary>character set</></>
2782 2783 2784 2785 2786 2787 2788
      <listitem>
       <para>
        Sets the client-side encoding (character set).
        The default is to use the database encoding.
       </para>
      </listitem>
     </varlistentry>
2789

2790
     <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
2791
      <term><varname>lc_messages</varname> (<type>string</type>)</term>
2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810
      <listitem>
       <para>
        Sets the language in which messages are displayed.  Acceptable
        values are system-dependent; see <xref linkend="locale"> for
        more information.  If this variable is set to the empty string
        (which is the default) then the value is inherited from the
        execution environment of the server in a system-dependent way.
       </para>

       <para>
        On some systems, this locale category does not exist.  Setting
        this variable will still work, but there will be no effect.
        Also, there is a chance that no translated messages for the
        desired language exist.  In that case you will continue to see
        the English messages.
       </para>
      </listitem>
     </varlistentry>

2811
     <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
2812
      <term><varname>lc_monetary</varname> (<type>string</type>)</term>
2813 2814
      <listitem>
       <para>
2815
        Sets the locale to use for formatting monetary amounts, for
2816
        example with the <function>to_char</function> family of
2817
        functions.  Acceptable values are system-dependent; see <xref
2818 2819 2820 2821 2822 2823 2824 2825
        linkend="locale"> for more information.  If this variable is
        set to the empty string (which is the default) then the value
        is inherited from the execution environment of the server in a
        system-dependent way.
       </para>
      </listitem>
     </varlistentry>

2826
     <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
2827
      <term><varname>lc_numeric</varname> (<type>string</type>)</term>
2828 2829 2830
      <listitem>
       <para>
        Sets the locale to use for formatting numbers, for example
2831
        with the <function>to_char</function> family of
2832 2833 2834 2835 2836 2837 2838 2839 2840
        functions. Acceptable values are system-dependent; see <xref
        linkend="locale"> for more information.  If this variable is
        set to the empty string (which is the default) then the value
        is inherited from the execution environment of the server in a
        system-dependent way.
       </para>
      </listitem>
     </varlistentry>

2841
     <varlistentry id="guc-lc-time" xreflabel="lc_time">
2842
      <term><varname>lc_time</varname> (<type>string</type>)</term>
2843 2844
      <listitem>
       <para>
2845 2846 2847
        Sets the locale to use for formatting date and time values.
        (Currently, this setting does nothing, but it may in the
        future.)  Acceptable values are system-dependent; see <xref
2848 2849 2850 2851 2852 2853 2854 2855
        linkend="locale"> for more information.  If this variable is
        set to the empty string (which is the default) then the value
        is inherited from the execution environment of the server in a
        system-dependent way.
       </para>
      </listitem>
     </varlistentry>

2856
     </variablelist>
2857

2858 2859 2860
    </sect3>
     <sect3 id="runtime-config-client-other">
     <title>Other Defaults</title>
2861

2862
     <variablelist>
2863

2864
     <varlistentry id="guc-explain-pretty-print" xreflabel="explain_pretty_print">
2865
      <term><varname>explain_pretty_print</varname> (<type>boolean</type>)</term>
2866 2867
      <listitem>
       <para>
2868 2869 2870
        Determines whether <command>EXPLAIN VERBOSE</> uses the
        indented or non-indented format for displaying detailed
        query-tree dumps. The default is on.
2871 2872 2873 2874
       </para>
      </listitem>
     </varlistentry>

2875
     <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
2876
      <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
2877 2878
      <indexterm><primary>dynamic_library_path</></>
      <indexterm><primary>dynamic loading</></>
2879 2880
      <listitem>
       <para>
2881
        If a dynamically loadable module needs to be opened and the
2882 2883 2884
        file name specified in the <command>CREATE FUNCTION</command> or
        <command>LOAD</command> command
        does not have a directory component (i.e. the
2885
        name does not contain a slash), the system will search this
2886
        path for the required file.
2887 2888
       </para>

2889
       <para>
2890
        The value for <varname>dynamic_library_path</varname> has to be a
2891
        list of absolute directory paths separated by colons (or semi-colons
2892
        on Windows).  If a list element starts
2893
        with the special string <literal>$libdir</literal>, the
2894
        compiled-in <productname>PostgreSQL</productname> package
2895 2896 2897 2898 2899
        library directory is substituted for <literal>$libdir</literal>. This
        is where the modules provided by the standard
        <productname>PostgreSQL</productname> distribution are installed.
        (Use <literal>pg_config --pkglibdir</literal> to find out the name of
        this directory.) For example:
2900 2901
<programlisting>
dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
2902
</programlisting>
2903
        or, in a Windows environment:
2904 2905
<programlisting>
dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
2906
</programlisting>
2907 2908 2909
       </para>

       <para>
2910 2911 2912
        The default value for this parameter is
        <literal>'$libdir'</literal>. If the value is set to an empty
        string, the automatic path search is turned off.
2913 2914 2915
       </para>

       <para>
2916 2917 2918 2919 2920 2921
        This parameter can be changed at run time by superusers, but a
        setting done that way will only persist until the end of the
        client connection, so this method should be reserved for
        development purposes. The recommended way to set this parameter
        is in the <filename>postgresql.conf</filename> configuration
        file.
2922 2923 2924 2925
       </para>
      </listitem>
     </varlistentry>

2926 2927 2928 2929 2930 2931 2932 2933 2934
     </variablelist>
    </sect3>
   </sect2>

   <sect2 id="runtime-config-locks">
    <title>Lock Management</title>

     <variablelist>

2935
     <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
2936
      <indexterm>
2937
       <primary>deadlock</primary>
Peter Eisentraut's avatar
Peter Eisentraut committed
2938
       <secondary>timeout during</secondary>
2939 2940 2941 2942
      </indexterm>
      <indexterm>
       <primary>timeout</primary>
       <secondary>deadlock</secondary>
2943 2944
      </indexterm>

2945
      <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
2946 2947
      <listitem>
       <para>
2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961
        This is the amount of time, in milliseconds, to wait on a lock
        before checking to see if there is a deadlock condition. The
        check for deadlock is relatively slow, so the server doesn't run
        it every time it waits for a lock. We (optimistically?) assume
        that deadlocks are not common in production applications and
        just wait on the lock for a while before starting the check for a
        deadlock. Increasing this value reduces the amount of time
        wasted in needless deadlock checks, but slows down reporting of
        real deadlock errors. The default is 1000 (i.e., one second),
        which is probably about the smallest value you would want in
        practice. On a heavily loaded server you might want to raise it.
        Ideally the setting should exceed your typical transaction time,
        so as to improve the odds that a lock will be released before
        the waiter decides to check for deadlock.
2962 2963 2964 2965
       </para>
      </listitem>
     </varlistentry>

2966
     <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
2967
      <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
2968 2969
      <listitem>
       <para>
2970
        The shared lock table is sized on the assumption that at most
2971
        <varname>max_locks_per_transaction</varname> *
2972
        <varname>max_connections</varname> distinct objects will need to
2973 2974 2975 2976
        be locked at any one time. The default, 64, has historically
        proven sufficient, but you might need to raise this value if you
        have clients that touch many different tables in a single
        transaction. This option can only be set at server start.
2977 2978 2979 2980
       </para>
      </listitem>
     </varlistentry>

2981 2982 2983 2984 2985 2986 2987
     </variablelist>
   </sect2>

   <sect2 id="runtime-config-compatible">
    <title>Version and Platform Compatibility</title>

    <sect3 id="runtime-config-compatible-version">
Peter Eisentraut's avatar
Peter Eisentraut committed
2988
     <title>Previous PostgreSQL Versions</title>
2989 2990
     <variablelist>

2991
     <varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
2992
      <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
Peter Eisentraut's avatar
Peter Eisentraut committed
2993
      <indexterm><primary>FROM</><secondary>missing</></>
2994 2995
      <listitem>
       <para>
2996
        When <literal>true</>, tables that are referenced by a query will be
Peter Eisentraut's avatar
Peter Eisentraut committed
2997 2998 2999 3000 3001 3002 3003
        automatically added to the <literal>FROM</> clause if not already
        present.  The default is <literal>true</> for compatibility with
        previous releases of <productname>PostgreSQL</>.  However, this
        behavior is not SQL-standard, and many people dislike it because it
        can mask mistakes.  Set to <literal>false</> for the SQL-standard
        behavior of rejecting references to tables that are not listed in
        <literal>FROM</>.
3004
       </para>
3005 3006
      </listitem>
     </varlistentry>
3007

3008
     <varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
3009
      <term><varname>regex_flavor</varname> (<type>string</type>)</term>
3010 3011
      <indexterm><primary>regular expressions</></>
      <listitem>
3012
       <para>
3013 3014
        The regular expression <quote>flavor</> can be set to
        <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
3015
        The default is <literal>advanced</>.  The <literal>extended</>
3016 3017
        setting may be useful for exact backwards compatibility with
        pre-7.4 releases of <productname>PostgreSQL</>.
3018 3019 3020 3021
       </para>
      </listitem>
     </varlistentry>

3022
     <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
3023
      <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
3024
      <indexterm><primary>inheritance</></>
3025 3026
      <listitem>
       <para>
3027 3028 3029 3030 3031 3032
        This controls the inheritance semantics, in particular whether
        subtables are included by various commands by default. They were
        not included in versions prior to 7.1. If you need the old
        behavior you can set this variable to off, but in the long run
        you are encouraged to change your applications to use the
        <literal>ONLY</literal> key word to exclude subtables. See
3033
        <xref linkend="ddl-inherit"> for more information about inheritance.
3034 3035 3036
       </para>
      </listitem>
     </varlistentry>
3037

3038
     <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
3039 3040 3041
      <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
3042 3043 3044
        This controls whether <command>CREATE TABLE</command>
        and <command>CREATE TABLE AS</command> will include OIDs in
        newly-created tables, if neither <literal>WITH OIDS</literal>
3045 3046
        nor <literal>WITHOUT OIDS</literal> have been specified. It
        also determines whether OIDs will be included in tables
3047
        created by <command>SELECT INTO</command>. In
3048
        <productname>PostgreSQL</productname> &version;
3049 3050 3051 3052
        <varname>default_with_oids</varname> defaults to
        true. This is also the behavior of previous versions
        of <productname>PostgreSQL</productname>. However, assuming
        that tables will contain OIDs by default is not
3053
        encouraged. This option will probably default to false in a
3054 3055 3056 3057 3058 3059 3060 3061
        future release of <productname>PostgreSQL</productname>.
       </para>

       <para>
        To ease compatibility with applications that make use of OIDs,
        this option should left enabled. To ease compatibility with
        future versions of <productname>PostgreSQL</productname>, this
        option should be disabled, and applications that require OIDs
3062
        on certain tables should explicitly specify <literal>WITH
3063 3064 3065 3066 3067 3068 3069
        OIDS</literal> when issuing the <command>CREATE
        TABLE</command> statements for the tables in question.
       </para>
      </listitem>
     </varlistentry>

     </variablelist>
3070 3071 3072 3073 3074
    </sect3>
    <sect3 id="runtime-config-compatible-clients">
     <title>Platform and Client Compatibility</title>
     <variablelist>

3075
     <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
3076
      <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
3077
      <indexterm><primary>IS NULL</></>
3078 3079 3080
      <listitem>
       <para>
        When turned on, expressions of the form
3081 3082
        <literal><replaceable>expr</> = NULL</literal> (or <literal>NULL
        = <replaceable>expr</></literal>) are treated as
3083
        <literal><replaceable>expr</> IS NULL</literal>, that is, they
3084
        return true if <replaceable>expr</> evaluates to the null value,
3085
        and false otherwise. The correct SQL-spec-compliant behavior of
3086
        <literal><replaceable>expr</> = NULL</literal> is to always
3087
        return null (unknown). Therefore this option defaults to off.
3088 3089 3090 3091 3092 3093
       </para>

       <para>
        However, filtered forms in <productname>Microsoft
        Access</productname> generate queries that appear to use
        <literal><replaceable>expr</> = NULL</literal> to test for
3094
        null values, so if you use that interface to access the database you
3095 3096
        might want to turn this option on.  Since expressions of the
        form <literal><replaceable>expr</> = NULL</literal> always
3097
        return the null value (using the correct interpretation) they are not
3098 3099 3100
        very useful and do not appear often in normal applications, so
        this option does little harm in practice.  But new users are
        frequently confused about the semantics of expressions
3101
        involving null values, so this option is not on by default.
3102 3103 3104
       </para>

       <para>
3105 3106
        Note that this option only affects the exact form <literal>= NULL</>,
        not other comparison operators or other expressions
3107 3108 3109 3110 3111 3112
        that are computationally equivalent to some expression
        involving the equals operator (such as <literal>IN</literal>).
        Thus, this option is not a general fix for bad programming.
       </para>

       <para>
3113
        Refer to <xref linkend="functions-comparison"> for related information.
3114 3115 3116 3117
       </para>
      </listitem>
     </varlistentry>

3118 3119
     </variablelist>
    </sect3>
3120
   </sect2>
3121

3122 3123
   <sect2 id="runtime-config-preset">
    <title>Preset Options</title>
3124 3125

    <para>
3126
     The following <quote>parameters</> are read-only, and are determined
3127 3128
     when <productname>PostgreSQL</productname> is compiled or when it is
     installed. As such, they have been excluded from the sample
3129 3130 3131 3132
     <filename>postgresql.conf</> file.  These options determine
     various aspects of <productname>PostgreSQL</productname> behavior
     that may be of interest to certain applications, particularly
     administrative front-ends.
3133 3134 3135 3136
    </para>

    <variablelist>

3137
     <varlistentry id="guc-block-size" xreflabel="block_size">
3138 3139 3140 3141 3142
      <term><varname>block_size</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Shows the size of a disk block.  It is determined by the value
        of <literal>BLCKSZ</> when building the server. The default
3143 3144 3145 3146
        value is 8192 bytes.  The meaning of some configuration
        variables (such as <xref linkend="guc-shared-buffers">) is
        influenced by <varname>block_size</varname>. See <xref
        linkend="runtime-config-resource"> for information.
3147 3148 3149 3150
       </para>
      </listitem>
     </varlistentry>

3151
     <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
3152 3153 3154
      <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
3155
        Shows whether <productname>PostgreSQL</productname> was built
3156
        with support for 64-bit-integer dates and times.  It is set by
3157 3158 3159
        configuring with <literal>--enable-integer-datetimes</literal>
        when building <productname>PostgreSQL</productname>.  The
        default value is <literal>off</literal>.
3160 3161 3162 3163
       </para>
      </listitem>
     </varlistentry>

3164 3165 3166 3167 3168
     <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
      <term><varname>lc_collate</varname> (<type>string</type>)</term>
      <listitem>
       <para>
        Shows the locale in which sorting of textual data is done.
3169 3170
        See <xref linkend="locale"> for more information.
        The value is determined when the database cluster is initialized.
3171 3172 3173 3174 3175 3176 3177 3178 3179
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
      <term><varname>lc_ctype</varname> (<type>string</type>)</term>
      <listitem>
       <para>
        Shows the locale that determines character classifications.
3180 3181 3182 3183
        See <xref linkend="locale"> for more information.
        The value is determined when the database cluster is initialized.
        Ordinarily this will be the same as <varname>lc_collate</varname>,
        but for special applications it might be set differently.
3184 3185 3186 3187
       </para>
      </listitem>
     </varlistentry>

3188
     <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
3189 3190 3191 3192 3193 3194 3195 3196 3197 3198
      <term><varname>max_function_args</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Shows the maximum number of function arguments. It is determined by
        the value of <literal>FUNC_MAX_ARGS</> when building the server. The
        default value is 32.
       </para>
      </listitem>
     </varlistentry>

3199
     <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
3200 3201 3202
      <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
3203 3204 3205 3206
        Shows the maximum identifier length. It is determined as one
        less than the value of <literal>NAMEDATALEN</> when building
        the server. The default value of <literal>NAMEDATALEN</> is
        64; therefore the default
3207 3208 3209 3210 3211
        <varname>max_identifier_length</varname> is 63.
       </para>
      </listitem>
     </varlistentry>

3212
     <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
3213 3214 3215 3216 3217 3218 3219 3220 3221 3222
      <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Shows the maximum number of index keys. It is determined by
        the value of <literal>INDEX_MAX_KEYS</> when building the server. The
        default value is 32.
       </para>
      </listitem>
     </varlistentry>

3223 3224 3225 3226 3227 3228 3229 3230 3231 3232 3233 3234 3235 3236 3237 3238 3239 3240 3241 3242 3243 3244 3245
     <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
      <term><varname>server_encoding</varname> (<type>string</type>)</term>
      <indexterm><primary>character set</></>
      <listitem>
       <para>
        Shows the database encoding (character set).
        It is determined when the database is created.  Ordinarily,
        clients need only be concerned with the value of <xref
        linkend="guc-client-encoding">.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-server-version" xreflabel="server_version">
      <term><varname>server_version</varname> (<type>string</type>)</term>
      <listitem>
       <para>
        Shows the version number of the server. It is determined by the
        value of <literal>PG_VERSION</> when building the server.
       </para>
      </listitem>
     </varlistentry>

3246 3247 3248
    </variablelist>
   </sect2>

3249 3250 3251 3252
   <sect2 id="runtime-config-custom">
    <title>Customized Options</title>

    <para>
3253 3254 3255 3256
     This feature was designed to allow options not normally known to
     <productname>PostgreSQL</productname> to be added by add-on modules
     (such as procedural languages).  This allows add-on modules to be
     configured in the standard ways.
3257 3258 3259 3260 3261 3262 3263 3264 3265
    </para>

    <variablelist>

     <varlistentry id="guc-custom-variable-classes" xreflabel="custom-variable-classes">
      <term><varname>custom_variable_classes</varname> (<type>string</type>)</term>
      <indexterm><primary>custom_variable_classes</></>
      <listitem>
       <para>
3266 3267 3268 3269 3270
        This variable specifies one or several class names to be used for
        custom variables, in the form of a comma-separated list. A custom
        variable is a variable not normally known
        to <productname>PostgreSQL</productname> proper but used by some
        add-on module.  Such variables must have names consisting of a class
3271
        name, a dot, and a variable name.  <varname>custom_variable_classes</>
3272
        specifies all the class names in use in a particular installation.
3273 3274 3275 3276 3277 3278 3279
        This option can only be set at server start or in the
        <filename>postgresql.conf</filename> configuration file.
       </para>

      </listitem>
     </varlistentry>
    </variablelist>
3280 3281 3282 3283 3284 3285 3286 3287 3288 3289 3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307

    <para>
     The difficulty with setting custom variables in
     <filename>postgresql.conf</> is that the file must be read before add-on
     modules have been loaded, and so custom variables would ordinarily be
     rejected as unknown.  When <varname>custom_variable_classes</> is set,
     the server will accept definitions of arbitrary variables within each
     specified class.  These variables will be treated as placeholders and
     will have no function until the module that defines them is loaded. When a
     module for a specific class is loaded, it will add the proper variable
     definitions for its class name, convert any placeholder
     values according to those definitions, and issue warnings for any
     placeholders of its class that remain (which presumably would be
     misspelled configuration variables).
    </para>

    <para>
     Here is an example of what <filename>postgresql.conf</> might contain
     when using custom variables:

<programlisting>
custom_variable_classes = 'plr,pljava'
plr.path = '/usr/lib/R'
pljava.foo = 1
plruby.bar = true        # generates error, unknown class name
</programlisting>
    </para>
   </sect2>
3308

3309
   <sect2 id="runtime-config-developer">
3310
    <title>Developer Options</title>
3311

3312
    <para>
3313 3314 3315 3316 3317 3318 3319
     The following options are intended for work on the
     <productname>PostgreSQL</productname> source, and in some cases
     to assist with recovery of severely damaged databases.  There
     should be no reason to use them in a production database setup.
     As such, they have been excluded from the sample
     <filename>postgresql.conf</> file.  Note that many of these
     options require special source compilation flags to work at all.
3320
    </para>
3321 3322

    <variablelist>
3323
     <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
3324
      <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
3325 3326
      <listitem>
       <para>
3327 3328 3329
        Turns on various assertion checks. This is a debugging aid. If
        you are experiencing strange problems or crashes you might want
        to turn this on, as it might expose programming mistakes. To use
Peter Eisentraut's avatar
Peter Eisentraut committed
3330
        this option, the macro <symbol>USE_ASSERT_CHECKING</symbol>
3331 3332 3333
        must be defined when <productname>PostgreSQL</productname> is
        built (accomplished by the <command>configure</command> option
        <option>--enable-cassert</option>). Note that
Peter Eisentraut's avatar
Peter Eisentraut committed
3334
        <varname>debug_assertions</varname> defaults to on if
3335 3336
        <productname>PostgreSQL</productname> has been built with
        assertions enabled.
3337 3338 3339 3340
       </para>
      </listitem>
     </varlistentry>

3341 3342 3343 3344 3345 3346 3347 3348 3349 3350 3351
     <varlistentry id="guc-debug-shared-buffers" xreflabel="debug_shared_buffers">
      <term><varname>debug_shared_buffers</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Number of seconds between ARC reports.
        If set greater than zero, emit ARC statistics to the log every so many
        seconds.  Zero (the default) disables reporting.
       </para>
      </listitem>
     </varlistentry>

3352
     <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
3353
      <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
3354 3355 3356
      <listitem>
       <para>
        If nonzero, a delay of this many seconds occurs just after a new
Peter Eisentraut's avatar
Peter Eisentraut committed
3357 3358 3359 3360
        server process is forked, before it conducts the authentication
        process.  This is intended to give an opportunity to attach to the
        server process with a debugger to trace down misbehavior in
        authentication.
3361 3362 3363 3364
       </para>
      </listitem>
     </varlistentry>

3365
     <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
3366
      <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
3367 3368
      <listitem>
       <para>
3369 3370
        Generates a great amount of debugging output for the
        <command>LISTEN</command> and <command>NOTIFY</command>
3371 3372
        commands.  <xref linkend="guc-client-min-messages"> or
        <xref linkend="guc-log-min-messages"> must be
3373
        <literal>DEBUG1</literal> or lower to send this output to the
3374
        client or server log, respectively.
3375 3376 3377 3378
       </para>
      </listitem>
     </varlistentry>

3379
     <varlistentry>
3380 3381 3382 3383 3384 3385 3386
      <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
      <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
      <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
      <term><varname>trace_lock_oidmin</varname> (<type>boolean</type>)</term>
      <term><varname>trace_lock_table</varname> (<type>boolean</type>)</term>
      <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
      <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
3387 3388
      <listitem>
       <para>
3389
        Various other code tracing and debugging options.
3390 3391 3392 3393
       </para>
      </listitem>
     </varlistentry>

3394
     <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
3395
      <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
3396 3397
      <listitem>
       <para>
3398 3399 3400 3401
        If true, emit WAL-related debugging output. This option is
        only available if the <symbol>WAL_DEBUG</symbol> macro was
        defined when <productname>PostgreSQL</productname> was
        compiled.
3402 3403 3404 3405
       </para>
      </listitem>
     </varlistentry>

3406
    <varlistentry id="guc-zero-damaged-pages" xreflabel="zero-damaged-pages">
3407
      <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
3408 3409
      <listitem>
       <para>
3410 3411
        Detection of a damaged page header normally causes
        <productname>PostgreSQL</> to report an error, aborting the current
3412
        transaction.  Setting <varname>zero_damaged_pages</> to true causes
3413 3414 3415 3416 3417 3418 3419 3420 3421
        the system to instead report a warning, zero out the damaged page,
        and continue processing.  This behavior <emphasis>will destroy data</>,
        namely all the rows on the damaged page.  But it allows you to get
        past the error and retrieve rows from any undamaged pages that may
        be present in the table.  So it is useful for recovering data if
        corruption has occurred due to hardware or software error.  You should
        generally not set this true until you have given up hope of recovering
        data from the damaged page(s) of a table.  The
        default setting is off, and it can only be changed by a superuser.
3422 3423 3424
       </para>
      </listitem>
     </varlistentry>
3425 3426
   </variablelist>
  </sect2>
3427 3428 3429
  <sect2 id="runtime-config-short">
   <title>Short Options</title>

3430
   <para>
3431 3432
    For convenience there are also single letter command-line option switches
    available for some parameters. They are described in <xref
3433 3434
    linkend="runtime-config-short-table">.
   </para>
3435

3436
    <table id="runtime-config-short-table">
3437
     <title>Short option key</title>
3438
     <tgroup cols="2">
3439 3440 3441 3442 3443 3444
      <thead>
       <row>
        <entry>Short option</entry>
        <entry>Equivalent</entry>
       </row>
      </thead>
3445

3446 3447
      <tbody>
       <row>
3448 3449
        <entry><option>-B <replaceable>x</replaceable></option></entry>
        <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
3450 3451
       </row>
       <row>
3452
        <entry><option>-d <replaceable>x</replaceable></option></entry>
3453
        <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
3454 3455
       </row>
       <row>
3456 3457
        <entry><option>-F</option></entry>
        <entry><literal>fsync = off</></entry>
3458
       </row>
3459
       <row>
3460
        <entry><option>-h <replaceable>x</replaceable></option></entry>
3461
        <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
3462
       </row>
3463
       <row>
3464
        <entry><option>-i</option></entry>
3465
        <entry><literal>listen_addresses = '*'</></entry>
3466 3467
       </row>
       <row>
3468 3469
        <entry><option>-k <replaceable>x</replaceable></option></entry>
        <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
3470
       </row>
3471
       <row>
3472 3473
        <entry><option>-l</option></entry>
        <entry><literal>ssl = on</></entry>
3474
       </row>
3475
       <row>
3476 3477
        <entry><option>-N <replaceable>x</replaceable></option></entry>
        <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
3478 3479
       </row>
       <row>
3480 3481
        <entry><option>-p <replaceable>x</replaceable></option></entry>
        <entry><literal>port = <replaceable>x</replaceable></></entry>
3482 3483 3484
       </row>

       <row>
3485 3486 3487 3488 3489 3490 3491
        <entry>
          <option>-fi</option>, <option>-fh</option>,
          <option>-fm</option>, <option>-fn</option>,
          <option>-fs</option>, <option>-ft</option><footnote
          id="fn.runtime-config-short">
           <para>
            For historical reasons, these options must be passed to
3492 3493
            the individual server process via the <option>-o</option>
            <command>postmaster</command> option, for example,
3494 3495 3496 3497 3498 3499 3500 3501 3502
<screen>
$ <userinput>postmaster -o '-S 1024 -s'</userinput>
</screen>
            or via <envar>PGOPTIONS</envar> from the client side, as
            explained above.
           </para>
          </footnote>
         </entry>
         <entry>
3503 3504 3505 3506 3507 3508
          <literal>enable_indexscan = off</>,
          <literal>enable_hashjoin = off</>,
          <literal>enable_mergejoin = off</>,
          <literal>enable_nestloop = off</>,
          <literal>enable_seqscan = off</>,
          <literal>enable_tidscan = off</>
3509
         </entry>
3510
       </row>
3511

3512
       <row>
3513
        <entry><option>-s</option><footnoteref linkend="fn.runtime-config-short"></entry>
3514
        <entry><literal>log_statement_stats = on</></entry>
3515
       </row>
3516

3517
       <row>
3518 3519
        <entry><option>-S <replaceable>x</replaceable></option><footnoteref linkend="fn.runtime-config-short">
        </entry>
3520
        <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
3521
       </row>
3522

3523
       <row>
3524
        <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option><footnoteref linkend="fn.runtime-config-short"></entry>
3525 3526 3527
        <entry><literal>log_parser_stats = on</>,
        <literal>log_planner_stats = on</>, 
        <literal>log_executor_stats = on</></entry>
3528 3529 3530 3531 3532
       </row>
      </tbody>
     </tgroup>
    </table>

3533
  </sect2>
3534 3535
 </sect1>

3536

3537 3538 3539 3540
 <sect1 id="kernel-resources">
  <title>Managing Kernel Resources</title>

  <para>
3541
   A large <productname>PostgreSQL</> installation can quickly exhaust
3542 3543 3544
   various operating system resource limits. (On some systems, the
   factory defaults are so low that you don't even need a really
   <quote>large</> installation.) If you have encountered this kind of
3545
   problem, keep reading.
3546 3547 3548 3549 3550
  </para>

  <sect2 id="sysvipc">
   <title>Shared Memory and Semaphores</title>

3551 3552 3553 3554 3555 3556 3557 3558
   <indexterm zone="sysvipc">
    <primary>shared memory</primary>
   </indexterm>

   <indexterm zone="sysvipc">
    <primary>semaphores</primary>
   </indexterm>

3559 3560
   <para>
    Shared memory and semaphores are collectively referred to as
3561 3562 3563
    <quote><systemitem class="osname">System V</>
    <acronym>IPC</></quote> (together with message queues, which are not
    relevant for <productname>PostgreSQL</>). Almost all modern
3564
    operating systems provide these features, but not all of them have
3565 3566 3567 3568
    them turned on or sufficiently sized by default, especially systems
    with BSD heritage. (For the <systemitem class="osname">QNX</> and
    <systemitem class="osname">BeOS</> ports, <productname>PostgreSQL</>
    provides its own replacement implementation of these facilities.)
3569 3570 3571 3572
   </para>

   <para>
    The complete lack of these facilities is usually manifested by an
3573
    <errorname>Illegal system call</> error upon server start. In
3574
    that case there's nothing left to do but to reconfigure your
3575
    kernel.  <productname>PostgreSQL</> won't work without them.
3576 3577 3578
   </para>

   <para>
3579
    When <productname>PostgreSQL</> exceeds one of the various hard
3580
    <acronym>IPC</> limits, the server will refuse to start and
3581 3582 3583 3584 3585 3586 3587 3588
    should leave an instructive error message describing the problem
    encountered and what to do about it. (See also <xref
    linkend="postmaster-start-failures">.) The relevant kernel
    parameters are named consistently across different systems; <xref
    linkend="sysvipc-parameters"> gives an overview. The methods to set
    them, however, vary. Suggestions for some platforms are given below.
    Be warned that it is often necessary to reboot your machine, and
    possibly even recompile the kernel, to change these settings.
3589 3590 3591 3592
   </para>


   <table id="sysvipc-parameters">
3593
    <title><systemitem class="osname">System V</> <acronym>IPC</> parameters</>
3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 3605 3606 3607

    <tgroup cols="3">
     <thead>
      <row>
       <entry>Name</>
       <entry>Description</>
       <entry>Reasonable values</>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><varname>SHMMAX</></>
       <entry>Maximum size of shared memory segment (bytes)</>
3608 3609
       <entry>250 kB + 8.2 kB * <xref linkend="guc-shared-buffers"> +
       14.2 kB * <xref linkend="guc-max-connections"> up to infinity</entry>
3610 3611 3612 3613 3614
      </row>

      <row>
       <entry><varname>SHMMIN</></>
       <entry>Minimum size of shared memory segment (bytes)</>
Bruce Momjian's avatar
Bruce Momjian committed
3615
       <entry>1</>
3616 3617
      </row>

3618 3619 3620
      <row>
       <entry><varname>SHMALL</></>
       <entry>Total amount of shared memory available (bytes or pages)</>
3621
       <entry>if bytes, same as <varname>SHMMAX</varname>; if pages, <literal>ceil(SHMMAX/PAGE_SIZE)</literal></>
3622 3623
      </row>

3624 3625 3626
      <row>
       <entry><varname>SHMSEG</></>
       <entry>Maximum number of shared memory segments per process</>
3627
       <entry>only 1 segment is needed, but the default is much higher</>
3628 3629 3630 3631 3632
      </row>

       <row>
        <entry><varname>SHMMNI</></>
        <entry>Maximum number of shared memory segments system-wide</>
Bruce Momjian's avatar
Bruce Momjian committed
3633
        <entry>like <varname>SHMSEG</> plus room for other applications</>
3634 3635 3636 3637 3638
       </row>

       <row>
        <entry><varname>SEMMNI</></>
        <entry>Maximum number of semaphore identifiers (i.e., sets)</>
3639
        <entry>at least <literal>ceil(max_connections / 16)</literal></>
3640 3641 3642 3643 3644
       </row>

       <row>
        <entry><varname>SEMMNS</></>
        <entry>Maximum number of semaphores system-wide</>
3645
        <entry><literal>ceil(max_connections / 16) * 17</literal> plus room for other applications</>
3646 3647 3648 3649 3650
       </row>

       <row>
        <entry><varname>SEMMSL</></>
        <entry>Maximum number of semaphores per set</>
3651
        <entry>at least 17</>
3652 3653 3654 3655 3656 3657 3658 3659 3660 3661 3662
       </row>

       <row>
        <entry><varname>SEMMAP</></>
        <entry>Number of entries in semaphore map</>
        <entry>see text</>
       </row>

       <row>
        <entry><varname>SEMVMX</></>
        <entry>Maximum value of semaphore</>
3663
        <entry>at least 1000 (The default is often 32767, don't change unless asked to.)</>
3664 3665 3666 3667 3668 3669 3670 3671
       </row>

     </tbody>
    </tgroup>
   </table>


   <para>
3672 3673 3674 3675 3676 3677 3678 3679 3680 3681 3682 3683 3684 3685
    <indexterm><primary>SHMMAX</primary></indexterm> The most important
    shared memory parameter is <varname>SHMMAX</>, the maximum size, in
    bytes, of a shared memory segment. If you get an error message from
    <function>shmget</> like <errorname>Invalid argument</>, it is
    possible that this limit has been exceeded. The size of the required
    shared memory segment varies both with the number of requested
    buffers (<option>-B</> option) and the number of allowed connections
    (<option>-N</> option), although the former is the most significant.
    (You can, as a temporary solution, lower these settings to eliminate
    the failure.) As a rough approximation, you can estimate the
    required segment size by multiplying the number of buffers and the
    block size (8 kB by default) plus ample overhead (at least half a
    megabyte). Any error message you might get will contain the size of
    the failed allocation request.
3686 3687 3688 3689
   </para>

   <para>
    Less likely to cause problems is the minimum size for shared
3690
    memory segments (<varname>SHMMIN</>), which should be at most
3691
    approximately 256 kB for <productname>PostgreSQL</> (it is
3692 3693 3694 3695 3696
    usually just 1). The maximum number of segments system-wide
    (<varname>SHMMNI</>) or per-process (<varname>SHMSEG</>) should
    not cause a problem unless your system has them set to zero. Some
    systems also have a limit on the total amount of shared memory in
    the system; see the platform-specific instructions below.
3697 3698 3699
   </para>

   <para>
3700
    <productname>PostgreSQL</> uses one semaphore per allowed connection
3701 3702
    (<option>-N</> option), in sets of 16.  Each such set will also
    contain a 17th semaphore which contains a <quote>magic
3703
    number</quote>, to detect collision with semaphore sets used by
3704 3705
    other applications. The maximum number of semaphores in the system
    is set by <varname>SEMMNS</>, which consequently must be at least
3706
    as high as <varname>max_connections</> plus one extra for each 16
3707
    allowed connections (see the formula in <xref
3708
    linkend="sysvipc-parameters">).  The parameter <varname>SEMMNI</>
3709 3710 3711 3712
    determines the limit on the number of semaphore sets that can
    exist on the system at one time.  Hence this parameter must be at
    least <literal>ceil(max_connections / 16)</>. Lowering the number
    of allowed connections is a temporary workaround for failures,
3713 3714
    which are usually confusingly worded <errorname>No space
    left on device</>, from the function <function>semget</>.
3715 3716 3717
   </para>

   <para>
3718
    In some cases it might also be necessary to increase
3719
    <varname>SEMMAP</> to be at least on the order of
3720 3721 3722 3723 3724 3725 3726 3727
    <varname>SEMMNS</>. This parameter defines the size of the semaphore
    resource map, in which each contiguous block of available semaphores
    needs an entry. When a semaphore set is freed it is either added to
    an existing entry that is adjacent to the freed block or it is
    registered under a new map entry. If the map is full, the freed
    semaphores get lost (until reboot). Fragmentation of the semaphore
    space could over time lead to fewer available semaphores than there
    should be.
3728 3729 3730 3731
   </para>

   <para>
    The <varname>SEMMSL</> parameter, which determines how many
3732
    semaphores can be in a set, must be at least 17 for
3733
    <productname>PostgreSQL</>.
3734 3735 3736
   </para>

   <para>
Tom Lane's avatar
Tom Lane committed
3737 3738
    Various other settings related to <quote>semaphore undo</>, such as
    <varname>SEMMNU</> and <varname>SEMUME</>, are not of concern
3739
    for <productname>PostgreSQL</>.
3740 3741 3742 3743 3744
   </para>

    <variablelist>

     <varlistentry>
3745
      <term><systemitem class="osname">BSD/OS</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3746
      <indexterm><primary>BSD/OS</><secondary>IPC configuration</></>
3747 3748 3749 3750 3751 3752
      <listitem>
       <formalpara>
        <title>Shared Memory</>
        <para>
         By default, only 4 MB of shared memory is supported. Keep in
         mind that shared memory is not pageable; it is locked in RAM.
3753 3754
         To increase the amount of shared memory supported by your
         system, add the following to your kernel configuration
3755
         file. A <varname>SHMALL</> value of 1024 represents 4 MB of
3756 3757
         shared memory. The following increases the maximum shared
         memory area to 32 MB:
3758
<programlisting>
3759
options "SHMALL=8192"
Bruce Momjian's avatar
Bruce Momjian committed
3760
options "SHMMAX=\(SHMALL*PAGE_SIZE\)"
3761
</programlisting>
3762 3763 3764
         For those running 4.3 or later, you will probably need to increase
         <varname>KERNEL_VIRTUAL_MB</> above the default <literal>248</>.
         Once all changes have been made, recompile the kernel, and reboot.
3765 3766 3767 3768
        </para>
       </formalpara>

       <para>
3769 3770
        For those running 4.0 and earlier releases, use <command>bpatch</>
        to find the <varname>sysptsize</> value in the current
3771
        kernel. This is computed dynamically at boot time.
3772 3773 3774 3775
<screen>
$ <userinput>bpatch -r sysptsize</>
<computeroutput>0x9 = 9</>
</screen>
3776
        Next, add <varname>SYSPTSIZE</> as a hard-coded value in the
3777
        kernel configuration file. Increase the value you found using
3778
        <command>bpatch</>. Add 1 for every additional 4 MB of
3779 3780
        shared memory you desire.
<programlisting>
3781
options "SYSPTSIZE=16"
3782
</programlisting>
3783
        <varname>sysptsize</> cannot be changed by <command>sysctl</command>.
3784 3785 3786 3787 3788
       </para>

       <formalpara>
        <title>Semaphores</>
        <para>
3789 3790 3791 3792
         You may need to increase the number of semaphores. By
         default, <productname>PostgreSQL</> allocates 34 semaphores,
         which is over half the default system total of 60.  Set the
         values you want in your kernel configuration file, e.g.:
3793 3794 3795 3796
<programlisting>
options "SEMMNI=40"
options "SEMMNS=240"
</programlisting>
3797 3798
        </para>
       </formalpara>
3799 3800 3801 3802 3803
      </listitem>
     </varlistentry>


     <varlistentry>
3804 3805 3806
      <term><systemitem class="osname">FreeBSD</></term>
      <term><systemitem class="osname">NetBSD</></term>
      <term><systemitem class="osname">OpenBSD</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3807 3808 3809
      <indexterm><primary>FreeBSD</><secondary>IPC configuration</></>
      <indexterm><primary>NetBSD</><secondary>IPC configuration</></>
      <indexterm><primary>OpenBSD</><secondary>IPC configuration</></>
3810 3811 3812 3813 3814 3815
      <listitem>
       <para>
        The options <varname>SYSVSHM</> and <varname>SYSVSEM</> need
        to be enabled when the kernel is compiled. (They are by
        default.) The maximum size of shared memory is determined by
        the option <varname>SHMMAXPGS</> (in pages). The following
Tom Lane's avatar
Tom Lane committed
3816
        shows an example of how to set the various parameters:
3817
<programlisting>
3818 3819 3820 3821 3822 3823 3824 3825
options         SYSVSHM
options         SHMMAXPGS=4096
options         SHMSEG=256

options         SYSVSEM
options         SEMMNI=256
options         SEMMNS=512
options         SEMMNU=256
3826 3827
options         SEMMAP=256
</programlisting>
3828 3829
        (On <systemitem class="osname">NetBSD</> and <systemitem
        class="osname">OpenBSD</> the key word is actually
3830
        <literal>option</literal> singular.)
3831
       </para>
3832
       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
3833 3834 3835 3836
        You might also want to configure your kernel to lock shared
        memory into RAM and prevent it from being paged out to swap.
        Use the <command>sysctl</> setting
        <literal>kern.ipc.shm_use_phys</>.
Peter Eisentraut's avatar
Peter Eisentraut committed
3837
       </para>
3838 3839 3840 3841 3842
      </listitem>
     </varlistentry>


     <varlistentry>
3843
      <term><systemitem class="osname">HP-UX</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3844
      <indexterm><primary>HP-UX</><secondary>IPC configuration</></>
3845 3846 3847
      <listitem>
       <para>
        The default settings tend to suffice for normal installations.
3848
        On <productname>HP-UX</> 10, the factory default for
3849 3850 3851 3852
        <varname>SEMMNS</> is 128, which might be too low for larger
        database sites.
       </para>
       <para>
3853
        <acronym>IPC</> parameters can be set in the <application>System
3854 3855
        Administration Manager</> (<acronym>SAM</>) under
        <menuchoice><guimenu>Kernel
3856 3857
        Configuration</><guimenuitem>Configurable Parameters</></>. Hit
        <guibutton>Create A New Kernel</> when you're done.
3858 3859 3860 3861 3862 3863
       </para>
      </listitem>
     </varlistentry>


     <varlistentry>
3864
      <term><systemitem class="osname">Linux</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3865
      <indexterm><primary>Linux</><secondary>IPC configuration</></>
3866 3867
      <listitem>
       <para>
3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 3878 3879
        The default shared memory limit (both
        <varname>SHMMAX</varname> and <varname>SHMALL</varname>) is 32
        MB in 2.2 kernels, but it can be changed in the
        <filename>proc</filename> file system (without reboot).  For
        example, to allow 128 MB:
<screen>
<prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmall</userinput>
<prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmmax</userinput>
</screen>
        You could put these commands into a script run at boot-time.
       </para>

3880
       <para>
3881 3882 3883 3884
        Alternatively, you can use <command>sysctl</command>, if
        available, to control these parameters.  Look for a file
        called <filename>/etc/sysctl.conf</filename> and add lines
        like the following to it:
3885 3886 3887 3888 3889
<programlisting>
kernel.shmall = 134217728
kernel.shmmax = 134217728
</programlisting>
        This file is usually processed at boot time, but
3890
        <command>sysctl</command> can also be called
3891 3892 3893
        explicitly later.
       </para>

3894
       <para>
3895 3896
        Other parameters are sufficiently sized for any application. If
        you want to see for yourself look in
3897 3898
        <filename>/usr/src/linux/include/asm-<replaceable>xxx</>/shmparam.h</>
        and <filename>/usr/src/linux/include/linux/sem.h</>.
3899 3900 3901 3902 3903
       </para>
      </listitem>
     </varlistentry>


3904
     <varlistentry>
3905
      <term><systemitem class="osname">MacOS X</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3906
      <indexterm><primary>MacOS X</><secondary>IPC configuration</></>
3907 3908
      <listitem>
       <para>
3909 3910
        In OS X 10.2 and earlier, edit the file
        <filename>/System/Library/StartupItems/SystemTuning/SystemTuning</>
3911
        and change the values in the following commands:
3912 3913 3914 3915 3916 3917 3918
<programlisting>
sysctl -w kern.sysv.shmmax
sysctl -w kern.sysv.shmmin
sysctl -w kern.sysv.shmmni
sysctl -w kern.sysv.shmseg
sysctl -w kern.sysv.shmall
</programlisting>
3919
        In OS X 10.3, these commands have been moved to <filename>/etc/rc</>
3920
        and must be edited there.
3921 3922 3923 3924 3925
       </para>
      </listitem>
     </varlistentry>


3926
     <varlistentry>
3927
      <term><systemitem class="osname">SCO OpenServer</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3928
      <indexterm><primary>SCO OpenServer</><secondary>IPC configuration</></>
3929 3930 3931 3932
      <listitem>
       <para>
        In the default configuration, only 512 kB of shared memory per
        segment is allowed, which is about enough for <option>-B 24 -N
3933
        12</>. To increase the setting, first change to the directory
3934
        <filename>/etc/conf/cf.d</>. To display the current value of
3935
        <varname>SHMMAX</>, run
3936 3937 3938
<programlisting>
./configure -y SHMMAX
</programlisting>
3939
        To set a new value for <varname>SHMMAX</>, run
3940 3941 3942 3943
<programlisting>
./configure SHMMAX=<replaceable>value</>
</programlisting>
        where <replaceable>value</> is the new value you want to use
3944
        (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel:
3945 3946 3947 3948 3949 3950 3951 3952
<programlisting>
./link_unix
</programlisting>
        and reboot.
       </para>
      </listitem>
     </varlistentry>

Bruce Momjian's avatar
Bruce Momjian committed
3953 3954 3955 3956 3957 3958 3959 3960 3961 3962 3963 3964 3965 3966 3967 3968 3969 3970 3971 3972
     <varlistentry>
      <term><systemitem class="osname">AIX</></term>
      <indexterm><primary>AIX</><secondary>IPC configuration</></>
      <listitem>
       <para>
        At least as of version 5.1, it should not be necessary to do
        any special configuration for such parameters as
        <varname>SHMMAX</varname>, as it appears this is configured to
        allow all memory to be used as shared memory.  That is the
        sort of configuration commonly used for other databases such
        as <application>DB/2</application>.</para>

       <para> It may, however, be necessary to modify the global
       <command>ulimit</command> information in
       <filename>/etc/security/limits</filename>, as the default hard
       limits for filesizes (<varname>fsize</varname>) and numbers of
       files (<varname>nofiles</varname>) may be too low.
       </para>
      </listitem>
     </varlistentry>      
3973 3974

     <varlistentry>
3975
      <term><systemitem class="osname">Solaris</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3976
      <indexterm><primary>Solaris</><secondary>IPC configuration</></>
3977 3978
      <listitem>
       <para>
3979 3980
        At least in version 2.6, the default maximum size of a shared
        memory segments is too low for <productname>PostgreSQL</>. The
3981 3982 3983 3984 3985 3986 3987 3988 3989 3990 3991 3992 3993
        relevant settings can be changed in <filename>/etc/system</>,
        for example:
<programlisting>
set shmsys:shminfo_shmmax=0x2000000
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=256
set shmsys:shminfo_shmseg=256

set semsys:seminfo_semmap=256
set semsys:seminfo_semmni=512
set semsys:seminfo_semmns=512
set semsys:seminfo_semmsl=32
</programlisting>
3994
        You need to reboot for the changes to take effect.
3995 3996 3997 3998
       </para>

       <para>
        See also <ulink
3999
        url="http://sunsite.uakom.sk/sunworldonline/swol-09-1997/swol-09-insidesolaris.html"></>
4000 4001 4002 4003 4004 4005 4006 4007
        for information on shared memory under
        <productname>Solaris</>.
       </para>
      </listitem>
     </varlistentry>


     <varlistentry>
4008
      <term><systemitem class="osname">UnixWare</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
4009
      <indexterm><primary>UnixWare</><secondary>IPC configuration</></>
4010 4011 4012 4013 4014 4015 4016 4017 4018 4019
      <listitem>
       <para>
        On <productname>UnixWare</> 7, the maximum size for shared
        memory segments is 512 kB in the default configuration. This
        is enough for about <option>-B 24 -N 12</>. To display the
        current value of <varname>SHMMAX</>, run
<programlisting>
/etc/conf/bin/idtune -g SHMMAX
</programlisting>
        which displays the current, default, minimum, and maximum
4020 4021
        values. To set a new value for <varname>SHMMAX</>,
        run
4022 4023 4024 4025 4026
<programlisting>
/etc/conf/bin/idtune SHMMAX <replaceable>value</>
</programlisting>
        where <replaceable>value</> is the new value you want to use
        (in bytes). After setting <varname>SHMMAX</>, rebuild the
4027
        kernel:
4028 4029 4030 4031 4032 4033 4034 4035 4036 4037 4038 4039
<programlisting>
/etc/conf/bin/idbuild -B
</programlisting>
        and reboot.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>

  </sect2>

4040 4041 4042 4043 4044

  <sect2>
   <title>Resource Limits</title>

   <para>
4045 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 4056 4057 4058
    Unix-like operating systems enforce various kinds of resource limits
    that might interfere with the operation of your
    <productname>PostgreSQL</productname> server. Of particular
    importance are limits on the number of processes per user, the
    number of open files per process, and the amount of memory available
    to each process. Each of these have a <quote>hard</quote> and a
    <quote>soft</quote> limit. The soft limit is what actually counts
    but it can be changed by the user up to the hard limit. The hard
    limit can only be changed by the root user. The system call
    <function>setrlimit</function> is responsible for setting these
    parameters. The shell's built-in command <command>ulimit</command>
    (Bourne shells) or <command>limit</command> (<application>csh</>) is
    used to control the resource limits from the command line. On
    BSD-derived systems the file <filename>/etc/login.conf</filename>
4059 4060
    controls the various resource limits set during login. See the
    operating system documentation for details. The relevant
4061
    parameters are <varname>maxproc</varname>,
4062 4063
    <varname>openfiles</varname>, and <varname>datasize</varname>. For
    example:
4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076
<programlisting>
default:\
...
        :datasize-cur=256M:\
        :maxproc-cur=256:\
        :openfiles-cur=256:\
...
</programlisting>
    (<literal>-cur</literal> is the soft limit.  Append
    <literal>-max</literal> to set the hard limit.)
   </para>

   <para>
4077
    Kernels can also have system-wide limits on some resources.
4078 4079 4080
    <itemizedlist>
     <listitem>
      <para>
4081 4082
      On <productname>Linux</productname>
      <filename>/proc/sys/fs/file-max</filename> determines the
4083
      maximum number of open files that the kernel will support.  It can
4084 4085 4086 4087 4088 4089
      be changed by writing a different number into the file or by
      adding an assignment in <filename>/etc/sysctl.conf</filename>.
      The maximum limit of files per process is fixed at the time the
      kernel is compiled; see
      <filename>/usr/src/linux/Documentation/proc.txt</filename> for
      more information.
4090 4091 4092
      </para>
     </listitem>
    </itemizedlist>
4093 4094 4095
   </para>

   <para>
4096
    The <productname>PostgreSQL</productname> server uses one process
4097 4098 4099 4100 4101 4102 4103 4104 4105 4106 4107 4108 4109 4110
    per connection so you should provide for at least as many processes
    as allowed connections, in addition to what you need for the rest
    of your system.  This is usually not a problem but if you run
    several servers on one machine things might get tight.
   </para>

   <para>
    The factory default limit on open files is often set to
    <quote>socially friendly</quote> values that allow many users to
    coexist on a machine without using an inappropriate fraction of
    the system resources.  If you run many servers on a machine this
    is perhaps what you want, but on dedicated servers you may want to
    raise this limit.
   </para>
4111 4112 4113

   <para>
    On the other side of the coin, some systems allow individual
4114 4115
    processes to open large numbers of files; if more than a few
    processes do so then the system-wide limit can easily be exceeded.
4116 4117 4118
    If you find this happening, and you do not want to alter the
    system-wide limit, you can set <productname>PostgreSQL</>'s <xref
    linkend="guc-max-files-per-process"> configuration parameter to
4119
    limit the consumption of open files.
4120
   </para>
4121
  </sect2>
4122

Peter Eisentraut's avatar
Peter Eisentraut committed
4123 4124 4125 4126
  <sect2>
   <title>Linux Memory Overcommit</title>

   <para>
4127
    In Linux 2.4 and later, the default virtual memory behavior is not
4128 4129 4130 4131 4132
    optimal for <productname>PostgreSQL</productname>. Because of the
    way that the kernel implements memory overcommit, the kernel may
    terminate the <productname>PostgreSQL</productname> server (the
    <filename>postmaster</filename> process) if the memory demands of
    another process cause the system to run out of virtual memory.
Peter Eisentraut's avatar
Peter Eisentraut committed
4133 4134 4135
   </para>

   <para>
4136 4137 4138
    If this happens, you will see a kernel message that looks like
    this (consult your system documentation and configuration on where
    to look for such a message):
Peter Eisentraut's avatar
Peter Eisentraut committed
4139 4140 4141
<programlisting>
Out of Memory: Killed process 12345 (postmaster). 
</programlisting>
4142
    This indicates that the <filename>postmaster</filename> process
4143 4144 4145
    has been terminated due to memory pressure.
    Although existing database connections will continue to function
    normally, no new connections will be accepted.  To recover,
4146
    <productname>PostgreSQL</productname> will need to be restarted.
Peter Eisentraut's avatar
Peter Eisentraut committed
4147 4148 4149
   </para>

   <para>
4150 4151
    One way to avoid this problem is to run
    <productname>PostgreSQL</productname>
Peter Eisentraut's avatar
Peter Eisentraut committed
4152
    on a machine where you can be sure that other processes will not
4153 4154 4155 4156 4157 4158 4159
    run the machine out of memory.
   </para>

   <para>
    On Linux 2.6 and later, a better solution is to modify the kernel's
    behavior so that it will not <quote>overcommit</> memory.  This is
    done by selecting strict overcommit mode via sysctl:
Peter Eisentraut's avatar
Peter Eisentraut committed
4160 4161 4162
<programlisting>
sysctl -w vm.overcommit_memory=2
</programlisting>
4163 4164 4165 4166
    or placing an equivalent entry in <filename>/etc/sysctl.conf</>.
    You may also wish to modify the related setting 
    <literal>vm.overcommit_ratio</>.  For details see the kernel documentation
    file <filename>Documentation/vm/overcommit-accounting</>.
Peter Eisentraut's avatar
Peter Eisentraut committed
4167 4168 4169
   </para>

   <para>
4170 4171 4172 4173 4174 4175 4176 4177 4178 4179 4180 4181
    Some vendors' Linux 2.4 kernels are reported to have early versions
    of the 2.6 overcommit sysctl.  However, setting
    <literal>vm.overcommit_memory</> to 2
    on a kernel that does not have the relevant code will make
    things worse not better.  It is recommended that you inspect
    the actual kernel source code (see the function
    <function>vm_enough_memory</> in the file <filename>mm/mmap.c</>)
    to verify what is supported in your copy before you try this in a 2.4
    installation.  The presence of the <filename>overcommit-accounting</>
    documentation file should <emphasis>not</> be taken as evidence that the
    feature is there.  If in any doubt, consult a kernel expert or your
    kernel vendor.
Peter Eisentraut's avatar
Peter Eisentraut committed
4182 4183
   </para>
  </sect2>
4184 4185 4186
 </sect1>


4187
 <sect1 id="postmaster-shutdown">
4188
  <title>Shutting Down the Server</title>
4189

Peter Eisentraut's avatar
Peter Eisentraut committed
4190 4191 4192 4193
  <indexterm zone="postmaster-shutdown">
   <primary>shutdown</>
  </indexterm>

4194
  <para>
4195
   There are several ways to shut down the database server. You control
4196 4197 4198
   the type of shutdown by sending different signals to the
   <command>postmaster</command> process.

4199 4200
   <variablelist>
    <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
4201
     <term><systemitem>SIGTERM</systemitem><indexterm><primary>SIGTERM</></></term>
4202 4203
     <listitem>
      <para>
4204 4205 4206 4207
       After receiving <systemitem>SIGTERM</systemitem>, the server
       disallows new connections, but lets existing sessions end their
       work normally. It shuts down only after all of the sessions
       terminate normally. This is the <firstterm>Smart
4208
       Shutdown</firstterm>.
4209 4210 4211 4212 4213
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
4214
     <term><systemitem>SIGINT</systemitem><indexterm><primary>SIGINT</></></term>
4215 4216
     <listitem>
      <para>
4217 4218
       The server disallows new connections and sends all existing
       server processes <systemitem>SIGTERM</systemitem>, which will cause them
4219
       to abort their current transactions and exit promptly. It then
4220
       waits for the server processes to exit and finally shuts down. This is the
4221
       <firstterm>Fast Shutdown</firstterm>.
4222 4223 4224 4225 4226
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
4227
     <term><systemitem>SIGQUIT</systemitem><indexterm><primary>SIGQUIT</></></term>
4228
     <listitem>
4229 4230 4231 4232 4233
      <para>
      This is the <firstterm>Immediate Shutdown</firstterm>, which
      will cause the <command>postmaster</command> process to send a
      <systemitem>SIGQUIT</systemitem> to all child processes and exit
      immediately (without properly shutting itself down). The child processes
4234 4235 4236 4237
      likewise exit immediately upon receiving
      <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by
      replaying the WAL log) upon next start-up. This is recommended
      only in emergencies.
4238 4239 4240 4241
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
4242
  </para>
4243

4244
   <important>
4245
    <para>
4246
     It is best not to use <systemitem>SIGKILL</systemitem> to shut down
4247
     the server. This will prevent the server from releasing
4248 4249
     shared memory and semaphores, which may then have to be done by
     manually.
4250
    </para>
4251
   </important>
4252

4253 4254 4255
  <para>
   The <acronym>PID</> of the <command>postmaster</command> process can be found using the
   <command>ps</command> program, or from the file
4256 4257 4258
   <filename>postmaster.pid</filename> in the data directory. So for
   example, to do a fast shutdown:
<screen>
Peter Eisentraut's avatar
Peter Eisentraut committed
4259
$ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
4260 4261 4262
</screen>
  </para>
  <para>
4263
   The program <command>pg_ctl</command> is a shell script
4264
   that provides a more convenient interface for shutting down the
4265
   server.
4266 4267 4268
  </para>
 </sect1>

4269
 <sect1 id="ssl-tcp">
4270
  <title>Secure TCP/IP Connections with SSL</title>
4271

4272 4273 4274 4275
  <indexterm zone="ssl-tcp">
   <primary>SSL</primary>
  </indexterm>

4276
  <para>
4277 4278
   <productname>PostgreSQL</> has native support for using
   <acronym>SSL</> connections to encrypt client/server communications
4279 4280 4281 4282
   for increased security. This requires that
   <productname>OpenSSL</productname> is installed on both client and
   server systems and that support in <productname>PostgreSQL</> is
   enabled at build time (see <xref linkend="installation">).
4283 4284 4285
  </para>

  <para>
4286 4287
   With <acronym>SSL</> support compiled in, the
   <productname>PostgreSQL</> server can be started with
4288
   <acronym>SSL</> enabled by setting the parameter
4289
   <xref linkend="guc-ssl"> to on in <filename>postgresql.conf</>. When
4290 4291
   starting in <acronym>SSL</> mode, the server will look for the
   files <filename>server.key</> and <filename>server.crt</> in the
4292 4293
   data directory, which should contain the server private key
   and certificate, respectively. These files must be set up correctly
4294 4295 4296
   before an <acronym>SSL</>-enabled server can start. If the private key is
   protected with a passphrase, the server will prompt for the
   passphrase and will not start until it has been entered.
4297 4298 4299
  </para>

  <para>
4300
   The server will listen for both standard and <acronym>SSL</>
4301
   connections on the same TCP port, and will negotiate with any
4302
   connecting client on whether to use <acronym>SSL</>. See <xref
4303
   linkend="auth-pg-hba-conf"> about how to force the server to
4304
   require use of <acronym>SSL</> for certain connections.
4305 4306 4307 4308
  </para>

  <para>
   For details on how to create your server private key and certificate,
4309 4310
   refer to the <productname>OpenSSL</> documentation. A simple
   self-signed certificate can be used to get started for testing, but a
4311
   certificate signed by a certificate authority (<acronym>CA</>) (either one of the global
4312 4313 4314 4315
   <acronym>CAs</> or a local one) should be used in production so the
   client can verify the server's identity. To create a quick
   self-signed certificate, use the following
   <productname>OpenSSL</productname> command:
4316
<programlisting>
4317
openssl req -new -text -out server.req
4318
</programlisting>
4319
   Fill out the information that <command>openssl</> asks for. Make sure
4320
   that you enter the local host name as <quote>Common Name</>; the challenge
4321
   password can be left blank. The program will generate a key that is
4322
   passphrase protected; it will not accept a passphrase that is less
4323 4324
   than four characters long. To remove the passphrase (as you must if
   you want automatic start-up of the server), run the commands
4325
<programlisting>
4326 4327
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
4328
</programlisting>
4329
   Enter the old passphrase to unlock the existing key. Now do
4330
<programlisting>
4331 4332
openssl req -x509 -in server.req -text -key server.key -out server.crt
chmod og-rwx server.key
4333
</programlisting>
4334
   to turn the certificate into a self-signed certificate and to copy the
Peter Eisentraut's avatar
Peter Eisentraut committed
4335
   key and certificate to where the server will look for them.
4336 4337 4338
  </para>
 </sect1>

4339
 <sect1 id="ssh-tunnels">
4340
  <title>Secure TCP/IP Connections with <application>SSH</application> Tunnels</title>
4341

4342 4343 4344 4345
  <indexterm zone="ssh-tunnels">
   <primary>ssh</primary>
  </indexterm>

4346
  <para>
4347
   One can use <application>SSH</application> to encrypt the network
4348
   connection between clients and a
4349
   <productname>PostgreSQL</productname> server. Done properly, this
4350
   provides an adequately secure network connection.
4351 4352 4353
  </para>

  <para>
4354
   First make sure that an <application>SSH</application> server is
4355 4356
   running properly on the same machine as the
   <productname>PostgreSQL</productname> server and that you can log in using
4357 4358
   <command>ssh</command> as some user. Then you can establish a secure
   tunnel with a command like this from the client machine:
4359
<programlisting>
4360
ssh -L 3333:foo.com:5432 joe@foo.com
4361 4362 4363
</programlisting>
   The first number in the <option>-L</option> argument, 3333, is the
   port number of your end of the tunnel; it can be chosen freely. The
4364
   second number, 5432, is the remote end of the tunnel: the port
Peter Eisentraut's avatar
Peter Eisentraut committed
4365
   number your server is using. The name or the address in between
4366 4367 4368 4369 4370 4371 4372 4373 4374
   the port numbers is the host with the database server you are going
   to connect to. In order to connect to the database server using
   this tunnel, you connect to port 3333 on the local machine:
<programlisting>
psql -h localhost -p 3333 template1
</programlisting>
   To the database server it will then look as though you are really
   user <literal>joe@foo.com</literal> and it will use whatever
   authentication procedure was set up for this user. In order for the
4375
   tunnel setup to succeed you must be allowed to connect via
4376
   <command>ssh</command> as <literal>joe@foo.com</literal>, just
4377
   as if you had attempted to use <command>ssh</command> to set up a
4378 4379 4380
   terminal session.
  </para>

4381 4382
  <tip>
   <para>
4383
    Several other applications exist that can provide secure tunnels using
4384 4385 4386 4387
    a procedure similar in concept to the one just described.
   </para>
  </tip>

4388
 </sect1>
4389 4390

</Chapter>
4391 4392 4393

<!-- Keep this comment at the end of the file
Local variables:
4394
mode:sgml
4395 4396 4397 4398 4399 4400 4401 4402 4403
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
4404
sgml-local-catalogs:("/usr/lib/sgml/catalog")
4405 4406 4407
sgml-local-ecat-files:nil
End:
-->