runtime.sgml 177 KB
Newer Older
1
<!--
2
$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.282 2004/09/22 03:55:24 neilc 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</varname> is enabled, this option
1933
          sets the file names of the created log files.  The value
1934 1935
          is treated as a <systemitem>strftime</systemitem> pattern,
          so <literal>%</literal>-escapes
1936
          can be used to specify time-varying file names.
1937
          If no <literal>%</literal>-escapes are present,
1938 1939
          <productname>PostgreSQL</productname> will
          append the epoch of the new log file's open time.  For example,
1940 1941
          if <varname>log_filename</varname> were <literal>server_log</literal>, then the
          chosen file name would be <literal>server_log.1093827753</literal>
1942
          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
          When <varname>redirect_stderr</varname> is enabled, this option
1954 1955 1956 1957
          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
          When <varname>redirect_stderr</varname> is enabled, this option
1969 1970 1971 1972
          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
     <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>
1983
          When <varname>redirect_stderr</varname> is enabled, this option will cause
1984 1985 1986 1987 1988 1989
          <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
1990
          a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
1991 1992 1993 1994 1995
          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>
1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014
        <para>
          Example:  To keep 7 days of logs, one log file per day named
          <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>, 
          etc, and automatically overwrite last week's log with this week's log,
          set <varname>log_filename</varname> to <literal>server_log.%a</literal>, 
          <varname>log_truncate_on_rotation</varname> to <literal>true</literal>, and 
          <varname>log_rotation_age</varname> to <literal>1440</literal>.
        </para>
        <para>
          Example: To keep 24 hours of logs, one log file per hour, but 
          also rotate sooner if the log file size exceeds 1GB, set 
          <varname>log_filename</varname> to <literal>server_log.%H%M</literal>, 
          <varname>log_truncate_on_rotation</varname> to <literal>true</literal>, 
          <varname>log_rotation_age</varname> to <literal>60</literal>, and 
          <varname>log_rotation_size</varname> to <literal>1000000</literal>.
          Including <literal>%M</> in <varname>log_filename</varname> allows
          any size-driven rotations that may occur to select a filename
          different from the hour's initial filename.
        </para>
2015 2016 2017
       </listitem>
     </varlistentry>

2018
     <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
2019
      <term><varname>syslog_facility</varname> (<type>string</type>)</term>
2020 2021
       <listitem>
        <para>
2022
          When logging to <application>syslog</> is enabled, this option
2023 2024
          determines the <application>syslog</application>
          <quote>facility</quote> to be used.  You may choose
2025 2026 2027 2028 2029
          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
2030
          <application>syslog</application> daemon.
2031
          This option can only be set at server start.
2032 2033 2034 2035
        </para>
       </listitem>
     </varlistentry>
     
2036
     <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
2037
      <term><varname>syslog_ident</varname> (<type>string</type>)</term>
2038 2039
       <listitem>
        <para>
2040
         When logging to <application>syslog</> is enabled, this option
2041
         determines the program name used to identify
2042
         <productname>PostgreSQL</productname> messages in
2043
         <application>syslog</application> logs. The default is
2044
         <literal>postgres</literal>.
2045
          This option can only be set at server start.
2046 2047 2048
        </para>
       </listitem>
      </varlistentry>
2049 2050 2051 2052 2053 2054 2055 2056
      
      </variablelist>
    </sect3>
     <sect3 id="runtime-config-logging-when">
     <title>When To Log</title>

     <variablelist>

2057
     <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
2058
      <term><varname>client_min_messages</varname> (<type>string</type>)</term>
2059 2060 2061 2062 2063 2064 2065 2066 2067 2068
      <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
2069
        rank here than in <varname>log_min_messages</>.
2070 2071 2072 2073
       </para>
      </listitem>
     </varlistentry>

2074
     <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
2075
      <term><varname>log_min_messages</varname> (<type>string</type>)</term>
2076 2077
      <listitem>
       <para>
2078
        Controls which message levels are written to the server log.
Peter Eisentraut's avatar
Peter Eisentraut committed
2079
        Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
2080 2081 2082 2083 2084 2085 2086
        <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
2087
        <varname>client_min_messages</>.
2088
        Only superusers can increase this option.
2089 2090 2091 2092
       </para>
      </listitem>
     </varlistentry>

2093
     <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
2094
      <term><varname>log_error_verbosity</varname> (<type>string</type>)</term>
2095 2096
      <listitem>
       <para>
2097
        Controls the amount of detail written in the server log for each
Peter Eisentraut's avatar
Peter Eisentraut committed
2098 2099 2100
        message that is logged.  Valid values are <literal>TERSE</>,
        <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
        fields to displayed messages.
2101 2102 2103 2104
       </para>
      </listitem>
     </varlistentry>

2105
     <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
2106
      <term><varname>log_min_error_statement</varname> (<type>string</type>)</term>
2107 2108 2109 2110
      <listitem>
       <para>
        Controls whether or not the SQL statement that causes an error
        condition will also be recorded in the server log. All SQL
2111 2112
        statements that cause an error of the specified level or
        higher are logged.  The default is
2113
        <literal>PANIC</literal> (effectively turning this feature
2114
        off for normal use). Valid values are <literal>DEBUG5</literal>,
2115 2116 2117 2118 2119 2120 2121 2122 2123
        <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.
2124
        Only superusers can increase this option.
2125 2126 2127 2128
       </para>
      </listitem>
     </varlistentry>
     
2129
     <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
2130
      <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
2131 2132 2133
       <listitem>
        <para>
         Sets a minimum statement execution time (in milliseconds)
2134 2135
         that causes a statement to be logged.  All SQL statements
         that run for the time specified or longer will be logged with
2136 2137
         their duration.  Setting this to zero will print
         all queries and their durations.  Minus-one (the default)
2138
         disables the feature.  For example, if you set it to 
2139 2140 2141 2142 2143
         <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.
2144 2145 2146
        </para>
       </listitem>
      </varlistentry>
2147

2148
     <varlistentry id="guc-silent-mode" xreflabel="silent_mode">
2149
      <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
2150 2151
      <listitem>
       <para>
2152
        Runs the server silently. If this option is set, the server
2153
        will automatically run in background and any controlling
2154 2155 2156
        terminals are disassociated (same effect as
        <command>postmaster</>'s <option>-S</option> option).
        The server's standard output and standard error are redirected
2157 2158 2159
        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
2160
        is discouraged because it makes it impossible to see error messages.
2161 2162 2163 2164 2165
       </para>
      </listitem>
     </varlistentry>

     </variablelist>
2166 2167 2168

       <para>
        Here is a list of the various message severity levels used in
Peter Eisentraut's avatar
Peter Eisentraut committed
2169
        these settings:
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 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249
        <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>

2250 2251 2252
    </sect3>
     <sect3 id="runtime-config-logging-what">
     <title>What To Log</title>
2253

2254
     <variablelist>
2255

2256
     <varlistentry>
2257 2258 2259 2260
      <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>
2261 2262
      <listitem>
       <para>
2263 2264 2265 2266 2267 2268 2269 2270 2271
        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.
2272
       </para>
2273 2274
      </listitem>
     </varlistentry>
2275

2276
     <varlistentry id="guc-log-connections" xreflabel="log_connections">
2277
      <term><varname>log_connections</varname> (<type>boolean</type>)</term>
2278 2279 2280 2281 2282 2283 2284 2285 2286 2287
      <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>

2288
     <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
2289 2290 2291
      <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
2292 2293 2294 2295 2296
        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.
2297 2298 2299 2300 2301
       </para>
      </listitem>
     </varlistentry>


2302
     <varlistentry id="guc-log-duration" xreflabel="log_duration">
2303
      <term><varname>log_duration</varname> (<type>boolean</type>)</term>
2304 2305 2306
      <listitem>
       <para>
        Causes the duration of every completed statement to be logged.
2307
        To use this option, it is recommended that you also enable
2308
        <varname>log_statement</> and if not using <application>syslog</>
2309
        log the PID using <varname>log_line_prefix</> so that you
2310 2311 2312
        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.
2313 2314 2315 2316
       </para>
      </listitem>
     </varlistentry>
     
2317
     <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
Bruce Momjian's avatar
Add:  
Bruce Momjian committed
2318 2319 2320
      <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
      <listitem>
       <para>
2321 2322 2323 2324 2325 2326 2327
         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</>
2328
         produces its own 
2329 2330 2331
         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
2332 2333
         <filename>postgresql.conf</filename> configuration file.

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 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378
         <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) 
2379
             separated by a dot. The numbers
2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396
             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>
2397 2398 2399 2400 2401
             <entry>Transaction ID</entry>
             <entry>Yes</entry>
            </row>
            <row>
             <entry><literal>%q</literal></entry>
2402 2403 2404 2405 2406 2407 2408 2409 2410 2411 2412 2413 2414
             <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
2415 2416 2417 2418
       </para>
      </listitem>
     </varlistentry>

2419
     <varlistentry id="guc-log-statement" xreflabel="log_statement">
2420
      <term><varname>log_statement</varname> (<type>string</type>)</term>
2421 2422
      <listitem>
       <para>
2423
        Controls which SQL statements are logged. Valid values are
2424 2425 2426 2427 2428 2429 2430 2431
        <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
2432 2433 2434 2435 2436
        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.
2437
       </para>
2438 2439 2440

       <note>
        <para>
2441 2442 2443 2444
         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.
2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455
        </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>
2456 2457 2458
      </listitem>
     </varlistentry>

2459
     <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
2460
      <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
2461 2462
      <listitem>
       <para>
2463 2464 2465 2466 2467
        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.
2468 2469 2470 2471
       </para>
      </listitem>
     </varlistentry>

2472 2473 2474 2475
     </variablelist>
    </sect3>
   </sect2>

2476 2477 2478 2479 2480 2481 2482 2483
   <sect2 id="runtime-config-statistics">
    <title>Runtime Statistics</title>

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

     <varlistentry>
2484 2485 2486 2487
      <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>
2488 2489 2490 2491
      <listitem>
       <para>
        For each query, write performance statistics of the respective
        module to the server log. This is a crude profiling
2492 2493
        instrument.  <varname>log_statement_stats</varname> reports total
        statement statistics, while the others report per-state statistics.
2494 2495
        <varname>log_statement_stats</varname> can not be enabled with 
        the other options.  All of these options are disabled by default.  
2496 2497
        Only superusers can turn off any of these options if they have
        been enabled by the administrator.
2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508
       </para>
      </listitem>
     </varlistentry>

     </variablelist>

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

2509
     <varlistentry id="guc-stats-start-collector" xreflabel="stats_start_collector">
2510
      <term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>
2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521
      <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>

2522
     <varlistentry id="guc-stats-command-string" xreflabel="stats_command_string">
2523
      <term><varname>stats_command_string</varname> (<type>boolean</type>)</term>
2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538
      <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>

2539
     <varlistentry id="guc-stats-block-level" xreflabel="guc_stats_block_level">
2540
      <term><varname>stats_block_level</varname> (<type>boolean</type>)</term>
2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553
      <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">
2554
      <term><varname>stats_row_level</varname> (<type>boolean</type>)</term>
2555 2556
      <listitem>
       <para>
2557 2558 2559
        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
2560 2561 2562 2563 2564 2565 2566
        <structname>pg_stat</structname> and
        <structname>pg_statio</structname> family of system views;
        refer to <xref linkend="monitoring"> for more information.
       </para>
      </listitem>
     </varlistentry>

2567
     <varlistentry id="guc-stats-reset-on-server-start" xreflabel="stats_reset_on_server_start">
2568
      <term><varname>stats_reset_on_server_start</varname> (<type>boolean</type>)</term>
2569 2570 2571 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582
      <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>

2583 2584 2585 2586 2587 2588
   <sect2 id="runtime-config-client">
    <title>Client Connection Defaults</title>

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

2590
     <varlistentry id="guc-search-path" xreflabel="search_path">
2591
      <term><varname>search_path</varname> (<type>string</type>)</term>
2592 2593
      <indexterm><primary>search_path</></>
      <indexterm><primary>path</><secondary>for schemas</></>
2594 2595
      <listitem>
       <para>
2596 2597 2598 2599 2600 2601 2602
        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.
2603 2604 2605
       </para>

       <para>
2606
        The value for <varname>search_path</varname> has to be a comma-separated
2607 2608 2609 2610
        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.)
2611
       </para>
2612

2613
       <para>
2614 2615 2616 2617 2618 2619 2620 2621
        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.
2622
       </para>
2623

2624 2625 2626 2627 2628 2629
       <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>
2630

2631 2632 2633 2634 2635 2636 2637 2638 2639 2640
       <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>
2641

2642
       <para>
2643
        The current effective value of the search path can be examined
2644 2645
        via the <acronym>SQL</acronym> function
        <function>current_schemas()</>.  This is not quite the same as
2646
        examining the value of <varname>search_path</varname>, since
2647
        <function>current_schemas()</> shows how the requests
2648
        appearing in <varname>search_path</varname> were resolved.
2649 2650 2651 2652
       </para>

       <para>
        For more information on schema handling, see <xref linkend="ddl-schemas">.
2653 2654 2655 2656
       </para>
      </listitem>
     </varlistentry>

2657
     <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
2658 2659 2660
      <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
2661 2662 2663 2664 2665 2666
        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.
2667 2668 2669
       </para>
      </listitem>
     </varlistentry>
2670

2671
     <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
2672 2673 2674 2675
      <indexterm>
       <primary>transaction isolation level</primary>
      </indexterm>

2676
      <term><varname>default_transaction_isolation</varname> (<type>string</type>)</term>
2677 2678
      <listitem>
       <para>
2679 2680 2681 2682 2683 2684
        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>.
2685 2686 2687
       </para>

       <para>
2688
        Consult <xref linkend="mvcc"> and <xref linkend="sql-set-transaction"> for more
2689 2690 2691 2692 2693
        information.
       </para>
      </listitem>
     </varlistentry>

2694
     <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
2695 2696 2697 2698
      <indexterm>
       <primary>read-only transaction</primary>
      </indexterm>

2699
      <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711
      <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>
2712
     
2713
     <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
2714
      <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
2715 2716
      <listitem>
       <para>
2717
        Aborts any statement that takes over the specified number of
2718
        milliseconds.  A value of zero (the default) turns off the limitation.
2719 2720 2721
       </para>
      </listitem>
     </varlistentry>
2722 2723 2724 2725 2726
     
     </variablelist>
    </sect3>
     <sect3 id="runtime-config-client-format">
     <title>Locale and Formatting</title>
2727

2728
     <variablelist>
2729

2730 2731
     <varlistentry id="guc-datestyle" xreflabel="DateStyle">
      <term><varname>DateStyle</varname> (<type>string</type>)</term>
2732
      <indexterm><primary>date style</></>
2733 2734
      <listitem>
       <para>
2735 2736 2737
        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
2738
        components: the output format specification (<literal>ISO</>,
2739 2740 2741 2742 2743 2744 2745 2746
        <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
2747
        default is <literal>ISO, MDY</>.
2748 2749 2750 2751
       </para>
      </listitem>
     </varlistentry>

2752
     <varlistentry id="guc-timezone" xreflabel="timezone">
2753
      <term><varname>timezone</varname> (<type>string</type>)</term>
2754
      <indexterm><primary>time zone</></>
2755 2756
      <listitem>
       <para>
2757 2758 2759 2760
        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.
2761
       </para>
2762 2763 2764
      </listitem>
     </varlistentry>

2765
     <varlistentry id="guc-australian-timezones" xreflabel="australian_timezones">
2766
      <term><varname>australian_timezones</varname> (<type>boolean</type>)</term>
Peter Eisentraut's avatar
Peter Eisentraut committed
2767
      <indexterm><primary>time zone</><secondary>Australian</></>
2768 2769
      <listitem>
       <para>
2770 2771 2772 2773 2774
        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.
2775
       </para>
2776 2777
      </listitem>
     </varlistentry>
Tom Lane's avatar
Tom Lane committed
2778

2779
     <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
2780 2781 2782 2783
      <indexterm>
       <primary>significant digits</primary>
      </indexterm>
      <indexterm>
Peter Eisentraut's avatar
Peter Eisentraut committed
2784 2785
       <primary>floating-point</primary>
       <secondary>display</secondary>
2786 2787
      </indexterm>

2788
      <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
2789
      <listitem>
Tom Lane's avatar
Tom Lane committed
2790
       <para>
2791 2792 2793 2794 2795 2796 2797 2798
        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
2799
       </para>
2800 2801
      </listitem>
     </varlistentry>
2802

2803
     <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
2804
      <term><varname>client_encoding</varname> (<type>string</type>)</term>
Peter Eisentraut's avatar
Peter Eisentraut committed
2805
      <indexterm><primary>character set</></>
2806 2807 2808 2809 2810 2811 2812
      <listitem>
       <para>
        Sets the client-side encoding (character set).
        The default is to use the database encoding.
       </para>
      </listitem>
     </varlistentry>
2813

2814
     <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
2815
      <term><varname>lc_messages</varname> (<type>string</type>)</term>
2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834
      <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>

2835
     <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
2836
      <term><varname>lc_monetary</varname> (<type>string</type>)</term>
2837 2838
      <listitem>
       <para>
2839
        Sets the locale to use for formatting monetary amounts, for
2840
        example with the <function>to_char</function> family of
2841
        functions.  Acceptable values are system-dependent; see <xref
2842 2843 2844 2845 2846 2847 2848 2849
        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>

2850
     <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
2851
      <term><varname>lc_numeric</varname> (<type>string</type>)</term>
2852 2853 2854
      <listitem>
       <para>
        Sets the locale to use for formatting numbers, for example
2855
        with the <function>to_char</function> family of
2856 2857 2858 2859 2860 2861 2862 2863 2864
        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>

2865
     <varlistentry id="guc-lc-time" xreflabel="lc_time">
2866
      <term><varname>lc_time</varname> (<type>string</type>)</term>
2867 2868
      <listitem>
       <para>
2869 2870 2871
        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
2872 2873 2874 2875 2876 2877 2878 2879
        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>

2880
     </variablelist>
2881

2882 2883 2884
    </sect3>
     <sect3 id="runtime-config-client-other">
     <title>Other Defaults</title>
2885

2886
     <variablelist>
2887

2888
     <varlistentry id="guc-explain-pretty-print" xreflabel="explain_pretty_print">
2889
      <term><varname>explain_pretty_print</varname> (<type>boolean</type>)</term>
2890 2891
      <listitem>
       <para>
2892 2893 2894
        Determines whether <command>EXPLAIN VERBOSE</> uses the
        indented or non-indented format for displaying detailed
        query-tree dumps. The default is on.
2895 2896 2897 2898
       </para>
      </listitem>
     </varlistentry>

2899
     <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
2900
      <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
2901 2902
      <indexterm><primary>dynamic_library_path</></>
      <indexterm><primary>dynamic loading</></>
2903 2904
      <listitem>
       <para>
2905
        If a dynamically loadable module needs to be opened and the
2906 2907 2908
        file name specified in the <command>CREATE FUNCTION</command> or
        <command>LOAD</command> command
        does not have a directory component (i.e. the
2909
        name does not contain a slash), the system will search this
2910
        path for the required file.
2911 2912
       </para>

2913
       <para>
2914
        The value for <varname>dynamic_library_path</varname> has to be a
2915
        list of absolute directory paths separated by colons (or semi-colons
2916
        on Windows).  If a list element starts
2917
        with the special string <literal>$libdir</literal>, the
2918
        compiled-in <productname>PostgreSQL</productname> package
2919 2920 2921 2922 2923
        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:
2924 2925
<programlisting>
dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
2926
</programlisting>
2927
        or, in a Windows environment:
2928 2929
<programlisting>
dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
2930
</programlisting>
2931 2932 2933
       </para>

       <para>
2934 2935 2936
        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.
2937 2938 2939
       </para>

       <para>
2940 2941 2942 2943 2944 2945
        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.
2946 2947 2948 2949
       </para>
      </listitem>
     </varlistentry>

2950 2951 2952 2953 2954 2955 2956 2957 2958
     </variablelist>
    </sect3>
   </sect2>

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

     <variablelist>

2959
     <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
2960
      <indexterm>
2961
       <primary>deadlock</primary>
Peter Eisentraut's avatar
Peter Eisentraut committed
2962
       <secondary>timeout during</secondary>
2963 2964 2965 2966
      </indexterm>
      <indexterm>
       <primary>timeout</primary>
       <secondary>deadlock</secondary>
2967 2968
      </indexterm>

2969
      <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
2970 2971
      <listitem>
       <para>
2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985
        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.
2986 2987 2988 2989
       </para>
      </listitem>
     </varlistentry>

2990
     <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
2991
      <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
2992 2993
      <listitem>
       <para>
2994
        The shared lock table is sized on the assumption that at most
2995
        <varname>max_locks_per_transaction</varname> *
2996
        <varname>max_connections</varname> distinct objects will need to
2997 2998 2999 3000
        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.
3001 3002 3003 3004
       </para>
      </listitem>
     </varlistentry>

3005 3006 3007 3008 3009 3010 3011
     </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
3012
     <title>Previous PostgreSQL Versions</title>
3013 3014
     <variablelist>

3015
     <varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
3016
      <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3017
      <indexterm><primary>FROM</><secondary>missing</></>
3018 3019
      <listitem>
       <para>
3020
        When <literal>true</>, tables that are referenced by a query will be
Peter Eisentraut's avatar
Peter Eisentraut committed
3021 3022 3023 3024 3025 3026 3027
        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</>.
3028
       </para>
3029 3030
      </listitem>
     </varlistentry>
3031

3032
     <varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
3033
      <term><varname>regex_flavor</varname> (<type>string</type>)</term>
3034 3035
      <indexterm><primary>regular expressions</></>
      <listitem>
3036
       <para>
3037 3038
        The regular expression <quote>flavor</> can be set to
        <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
3039
        The default is <literal>advanced</>.  The <literal>extended</>
3040 3041
        setting may be useful for exact backwards compatibility with
        pre-7.4 releases of <productname>PostgreSQL</>.
3042 3043 3044 3045
       </para>
      </listitem>
     </varlistentry>

3046
     <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
3047
      <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
3048
      <indexterm><primary>inheritance</></>
3049 3050
      <listitem>
       <para>
3051 3052 3053 3054 3055 3056
        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
3057
        <xref linkend="ddl-inherit"> for more information about inheritance.
3058 3059 3060
       </para>
      </listitem>
     </varlistentry>
3061

3062
     <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
3063 3064 3065
      <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
3066 3067 3068
        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>
3069 3070
        nor <literal>WITHOUT OIDS</literal> have been specified. It
        also determines whether OIDs will be included in tables
3071
        created by <command>SELECT INTO</command>. In
3072
        <productname>PostgreSQL</productname> &version;
3073 3074 3075 3076
        <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
3077
        encouraged. This option will probably default to false in a
3078 3079 3080 3081 3082 3083 3084 3085
        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
3086
        on certain tables should explicitly specify <literal>WITH
3087 3088 3089 3090 3091 3092 3093
        OIDS</literal> when issuing the <command>CREATE
        TABLE</command> statements for the tables in question.
       </para>
      </listitem>
     </varlistentry>

     </variablelist>
3094 3095 3096 3097 3098
    </sect3>
    <sect3 id="runtime-config-compatible-clients">
     <title>Platform and Client Compatibility</title>
     <variablelist>

3099
     <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
3100
      <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
3101
      <indexterm><primary>IS NULL</></>
3102 3103 3104
      <listitem>
       <para>
        When turned on, expressions of the form
3105 3106
        <literal><replaceable>expr</> = NULL</literal> (or <literal>NULL
        = <replaceable>expr</></literal>) are treated as
3107
        <literal><replaceable>expr</> IS NULL</literal>, that is, they
3108
        return true if <replaceable>expr</> evaluates to the null value,
3109
        and false otherwise. The correct SQL-spec-compliant behavior of
3110
        <literal><replaceable>expr</> = NULL</literal> is to always
3111
        return null (unknown). Therefore this option defaults to off.
3112 3113 3114 3115 3116 3117
       </para>

       <para>
        However, filtered forms in <productname>Microsoft
        Access</productname> generate queries that appear to use
        <literal><replaceable>expr</> = NULL</literal> to test for
3118
        null values, so if you use that interface to access the database you
3119 3120
        might want to turn this option on.  Since expressions of the
        form <literal><replaceable>expr</> = NULL</literal> always
3121
        return the null value (using the correct interpretation) they are not
3122 3123 3124
        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
3125
        involving null values, so this option is not on by default.
3126 3127 3128
       </para>

       <para>
3129 3130
        Note that this option only affects the exact form <literal>= NULL</>,
        not other comparison operators or other expressions
3131 3132 3133 3134 3135 3136
        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>
3137
        Refer to <xref linkend="functions-comparison"> for related information.
3138 3139 3140 3141
       </para>
      </listitem>
     </varlistentry>

3142 3143
     </variablelist>
    </sect3>
3144
   </sect2>
3145

3146 3147
   <sect2 id="runtime-config-preset">
    <title>Preset Options</title>
3148 3149

    <para>
3150
     The following <quote>parameters</> are read-only, and are determined
3151 3152
     when <productname>PostgreSQL</productname> is compiled or when it is
     installed. As such, they have been excluded from the sample
3153 3154 3155 3156
     <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.
3157 3158 3159 3160
    </para>

    <variablelist>

3161
     <varlistentry id="guc-block-size" xreflabel="block_size">
3162 3163 3164 3165 3166
      <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
3167 3168 3169 3170
        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.
3171 3172 3173 3174
       </para>
      </listitem>
     </varlistentry>

3175
     <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
3176 3177 3178
      <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
3179
        Shows whether <productname>PostgreSQL</productname> was built
3180
        with support for 64-bit-integer dates and times.  It is set by
3181 3182 3183
        configuring with <literal>--enable-integer-datetimes</literal>
        when building <productname>PostgreSQL</productname>.  The
        default value is <literal>off</literal>.
3184 3185 3186 3187
       </para>
      </listitem>
     </varlistentry>

3188 3189 3190 3191 3192
     <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.
3193 3194
        See <xref linkend="locale"> for more information.
        The value is determined when the database cluster is initialized.
3195 3196 3197 3198 3199 3200 3201 3202 3203
       </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.
3204 3205 3206 3207
        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.
3208 3209 3210 3211
       </para>
      </listitem>
     </varlistentry>

3212
     <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
3213 3214 3215 3216 3217 3218 3219 3220 3221 3222
      <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>

3223
     <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
3224 3225 3226
      <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
3227 3228 3229 3230
        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
3231 3232 3233 3234 3235
        <varname>max_identifier_length</varname> is 63.
       </para>
      </listitem>
     </varlistentry>

3236
     <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
3237 3238 3239 3240 3241 3242 3243 3244 3245 3246
      <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>

3247 3248 3249 3250 3251 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269
     <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>

3270 3271 3272
    </variablelist>
   </sect2>

3273 3274 3275 3276
   <sect2 id="runtime-config-custom">
    <title>Customized Options</title>

    <para>
3277 3278 3279 3280
     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.
3281 3282 3283 3284 3285 3286 3287 3288 3289
    </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>
3290 3291 3292 3293 3294
        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
3295
        name, a dot, and a variable name.  <varname>custom_variable_classes</>
3296
        specifies all the class names in use in a particular installation.
3297 3298 3299 3300 3301 3302 3303
        This option can only be set at server start or in the
        <filename>postgresql.conf</filename> configuration file.
       </para>

      </listitem>
     </varlistentry>
    </variablelist>
3304 3305 3306 3307 3308 3309 3310 3311 3312 3313 3314 3315 3316 3317 3318 3319 3320 3321 3322 3323 3324 3325 3326 3327 3328 3329 3330 3331

    <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>
3332

3333
   <sect2 id="runtime-config-developer">
3334
    <title>Developer Options</title>
3335

3336
    <para>
3337 3338 3339 3340 3341 3342 3343
     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.
3344
    </para>
3345 3346

    <variablelist>
3347
     <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
3348
      <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
3349 3350
      <listitem>
       <para>
3351 3352 3353
        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
3354
        this option, the macro <symbol>USE_ASSERT_CHECKING</symbol>
3355 3356 3357
        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
3358
        <varname>debug_assertions</varname> defaults to on if
3359 3360
        <productname>PostgreSQL</productname> has been built with
        assertions enabled.
3361 3362 3363 3364
       </para>
      </listitem>
     </varlistentry>

3365 3366 3367 3368 3369 3370 3371 3372 3373 3374 3375
     <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>

3376
     <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
3377
      <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
3378 3379 3380
      <listitem>
       <para>
        If nonzero, a delay of this many seconds occurs just after a new
Peter Eisentraut's avatar
Peter Eisentraut committed
3381 3382 3383 3384
        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.
3385 3386 3387 3388
       </para>
      </listitem>
     </varlistentry>

3389
     <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
3390
      <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
3391 3392
      <listitem>
       <para>
3393 3394
        Generates a great amount of debugging output for the
        <command>LISTEN</command> and <command>NOTIFY</command>
3395 3396
        commands.  <xref linkend="guc-client-min-messages"> or
        <xref linkend="guc-log-min-messages"> must be
3397
        <literal>DEBUG1</literal> or lower to send this output to the
3398
        client or server log, respectively.
3399 3400 3401 3402
       </para>
      </listitem>
     </varlistentry>

3403
     <varlistentry>
3404 3405 3406 3407 3408 3409 3410
      <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>
3411 3412
      <listitem>
       <para>
3413
        Various other code tracing and debugging options.
3414 3415 3416 3417
       </para>
      </listitem>
     </varlistentry>

3418
     <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
3419
      <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
3420 3421
      <listitem>
       <para>
3422 3423 3424 3425
        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.
3426 3427 3428 3429
       </para>
      </listitem>
     </varlistentry>

3430
    <varlistentry id="guc-zero-damaged-pages" xreflabel="zero-damaged-pages">
3431
      <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
3432 3433
      <listitem>
       <para>
3434 3435
        Detection of a damaged page header normally causes
        <productname>PostgreSQL</> to report an error, aborting the current
3436
        transaction.  Setting <varname>zero_damaged_pages</> to true causes
3437 3438 3439 3440 3441 3442 3443 3444 3445
        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.
3446 3447 3448
       </para>
      </listitem>
     </varlistentry>
3449 3450
   </variablelist>
  </sect2>
3451 3452 3453
  <sect2 id="runtime-config-short">
   <title>Short Options</title>

3454
   <para>
3455 3456
    For convenience there are also single letter command-line option switches
    available for some parameters. They are described in <xref
3457 3458
    linkend="runtime-config-short-table">.
   </para>
3459

3460
    <table id="runtime-config-short-table">
3461
     <title>Short option key</title>
3462
     <tgroup cols="2">
3463 3464 3465 3466 3467 3468
      <thead>
       <row>
        <entry>Short option</entry>
        <entry>Equivalent</entry>
       </row>
      </thead>
3469

3470 3471
      <tbody>
       <row>
3472 3473
        <entry><option>-B <replaceable>x</replaceable></option></entry>
        <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
3474 3475
       </row>
       <row>
3476
        <entry><option>-d <replaceable>x</replaceable></option></entry>
3477
        <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
3478 3479
       </row>
       <row>
3480 3481
        <entry><option>-F</option></entry>
        <entry><literal>fsync = off</></entry>
3482
       </row>
3483
       <row>
3484
        <entry><option>-h <replaceable>x</replaceable></option></entry>
3485
        <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
3486
       </row>
3487
       <row>
3488
        <entry><option>-i</option></entry>
3489
        <entry><literal>listen_addresses = '*'</></entry>
3490 3491
       </row>
       <row>
3492 3493
        <entry><option>-k <replaceable>x</replaceable></option></entry>
        <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
3494
       </row>
3495
       <row>
3496 3497
        <entry><option>-l</option></entry>
        <entry><literal>ssl = on</></entry>
3498
       </row>
3499
       <row>
3500 3501
        <entry><option>-N <replaceable>x</replaceable></option></entry>
        <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
3502 3503
       </row>
       <row>
3504 3505
        <entry><option>-p <replaceable>x</replaceable></option></entry>
        <entry><literal>port = <replaceable>x</replaceable></></entry>
3506 3507 3508
       </row>

       <row>
3509 3510 3511 3512 3513 3514 3515
        <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
3516 3517
            the individual server process via the <option>-o</option>
            <command>postmaster</command> option, for example,
3518 3519 3520 3521 3522 3523 3524 3525 3526
<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>
3527 3528 3529 3530 3531 3532
          <literal>enable_indexscan = off</>,
          <literal>enable_hashjoin = off</>,
          <literal>enable_mergejoin = off</>,
          <literal>enable_nestloop = off</>,
          <literal>enable_seqscan = off</>,
          <literal>enable_tidscan = off</>
3533
         </entry>
3534
       </row>
3535

3536
       <row>
3537
        <entry><option>-s</option><footnoteref linkend="fn.runtime-config-short"></entry>
3538
        <entry><literal>log_statement_stats = on</></entry>
3539
       </row>
3540

3541
       <row>
3542 3543
        <entry><option>-S <replaceable>x</replaceable></option><footnoteref linkend="fn.runtime-config-short">
        </entry>
3544
        <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
3545
       </row>
3546

3547
       <row>
3548
        <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option><footnoteref linkend="fn.runtime-config-short"></entry>
3549 3550 3551
        <entry><literal>log_parser_stats = on</>,
        <literal>log_planner_stats = on</>, 
        <literal>log_executor_stats = on</></entry>
3552 3553 3554 3555 3556
       </row>
      </tbody>
     </tgroup>
    </table>

3557
  </sect2>
3558 3559
 </sect1>

3560

3561 3562 3563 3564
 <sect1 id="kernel-resources">
  <title>Managing Kernel Resources</title>

  <para>
3565
   A large <productname>PostgreSQL</> installation can quickly exhaust
3566 3567 3568
   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
3569
   problem, keep reading.
3570 3571 3572 3573 3574
  </para>

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

3575 3576 3577 3578 3579 3580 3581 3582
   <indexterm zone="sysvipc">
    <primary>shared memory</primary>
   </indexterm>

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

3583 3584
   <para>
    Shared memory and semaphores are collectively referred to as
3585 3586 3587
    <quote><systemitem class="osname">System V</>
    <acronym>IPC</></quote> (together with message queues, which are not
    relevant for <productname>PostgreSQL</>). Almost all modern
3588
    operating systems provide these features, but not all of them have
3589 3590 3591 3592
    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.)
3593 3594 3595 3596
   </para>

   <para>
    The complete lack of these facilities is usually manifested by an
3597
    <errorname>Illegal system call</> error upon server start. In
3598
    that case there's nothing left to do but to reconfigure your
3599
    kernel.  <productname>PostgreSQL</> won't work without them.
3600 3601 3602
   </para>

   <para>
3603
    When <productname>PostgreSQL</> exceeds one of the various hard
3604
    <acronym>IPC</> limits, the server will refuse to start and
3605 3606 3607 3608 3609 3610 3611 3612
    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.
3613 3614 3615 3616
   </para>


   <table id="sysvipc-parameters">
3617
    <title><systemitem class="osname">System V</> <acronym>IPC</> parameters</>
3618 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 3631

    <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)</>
3632 3633
       <entry>250 kB + 8.2 kB * <xref linkend="guc-shared-buffers"> +
       14.2 kB * <xref linkend="guc-max-connections"> up to infinity</entry>
3634 3635 3636 3637 3638
      </row>

      <row>
       <entry><varname>SHMMIN</></>
       <entry>Minimum size of shared memory segment (bytes)</>
Bruce Momjian's avatar
Bruce Momjian committed
3639
       <entry>1</>
3640 3641
      </row>

3642 3643 3644
      <row>
       <entry><varname>SHMALL</></>
       <entry>Total amount of shared memory available (bytes or pages)</>
3645
       <entry>if bytes, same as <varname>SHMMAX</varname>; if pages, <literal>ceil(SHMMAX/PAGE_SIZE)</literal></>
3646 3647
      </row>

3648 3649 3650
      <row>
       <entry><varname>SHMSEG</></>
       <entry>Maximum number of shared memory segments per process</>
3651
       <entry>only 1 segment is needed, but the default is much higher</>
3652 3653 3654 3655 3656
      </row>

       <row>
        <entry><varname>SHMMNI</></>
        <entry>Maximum number of shared memory segments system-wide</>
Bruce Momjian's avatar
Bruce Momjian committed
3657
        <entry>like <varname>SHMSEG</> plus room for other applications</>
3658 3659 3660 3661 3662
       </row>

       <row>
        <entry><varname>SEMMNI</></>
        <entry>Maximum number of semaphore identifiers (i.e., sets)</>
3663
        <entry>at least <literal>ceil(max_connections / 16)</literal></>
3664 3665 3666 3667 3668
       </row>

       <row>
        <entry><varname>SEMMNS</></>
        <entry>Maximum number of semaphores system-wide</>
3669
        <entry><literal>ceil(max_connections / 16) * 17</literal> plus room for other applications</>
3670 3671 3672 3673 3674
       </row>

       <row>
        <entry><varname>SEMMSL</></>
        <entry>Maximum number of semaphores per set</>
3675
        <entry>at least 17</>
3676 3677 3678 3679 3680 3681 3682 3683 3684 3685 3686
       </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</>
3687
        <entry>at least 1000 (The default is often 32767, don't change unless asked to.)</>
3688 3689 3690 3691 3692 3693 3694 3695
       </row>

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


   <para>
3696 3697 3698 3699 3700 3701 3702 3703 3704 3705 3706 3707 3708 3709
    <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.
3710 3711 3712 3713
   </para>

   <para>
    Less likely to cause problems is the minimum size for shared
3714
    memory segments (<varname>SHMMIN</>), which should be at most
3715
    approximately 256 kB for <productname>PostgreSQL</> (it is
3716 3717 3718 3719 3720
    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.
3721 3722 3723
   </para>

   <para>
3724
    <productname>PostgreSQL</> uses one semaphore per allowed connection
3725 3726
    (<option>-N</> option), in sets of 16.  Each such set will also
    contain a 17th semaphore which contains a <quote>magic
3727
    number</quote>, to detect collision with semaphore sets used by
3728 3729
    other applications. The maximum number of semaphores in the system
    is set by <varname>SEMMNS</>, which consequently must be at least
3730
    as high as <varname>max_connections</> plus one extra for each 16
3731
    allowed connections (see the formula in <xref
3732
    linkend="sysvipc-parameters">).  The parameter <varname>SEMMNI</>
3733 3734 3735 3736
    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,
3737 3738
    which are usually confusingly worded <errorname>No space
    left on device</>, from the function <function>semget</>.
3739 3740 3741
   </para>

   <para>
3742
    In some cases it might also be necessary to increase
3743
    <varname>SEMMAP</> to be at least on the order of
3744 3745 3746 3747 3748 3749 3750 3751
    <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.
3752 3753 3754 3755
   </para>

   <para>
    The <varname>SEMMSL</> parameter, which determines how many
3756
    semaphores can be in a set, must be at least 17 for
3757
    <productname>PostgreSQL</>.
3758 3759 3760
   </para>

   <para>
Tom Lane's avatar
Tom Lane committed
3761 3762
    Various other settings related to <quote>semaphore undo</>, such as
    <varname>SEMMNU</> and <varname>SEMUME</>, are not of concern
3763
    for <productname>PostgreSQL</>.
3764 3765 3766 3767 3768
   </para>

    <variablelist>

     <varlistentry>
3769
      <term><systemitem class="osname">BSD/OS</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3770
      <indexterm><primary>BSD/OS</><secondary>IPC configuration</></>
3771 3772 3773 3774 3775 3776
      <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.
3777 3778
         To increase the amount of shared memory supported by your
         system, add the following to your kernel configuration
3779
         file. A <varname>SHMALL</> value of 1024 represents 4 MB of
3780 3781
         shared memory. The following increases the maximum shared
         memory area to 32 MB:
3782
<programlisting>
3783
options "SHMALL=8192"
Bruce Momjian's avatar
Bruce Momjian committed
3784
options "SHMMAX=\(SHMALL*PAGE_SIZE\)"
3785
</programlisting>
3786 3787 3788
         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.
3789 3790 3791 3792
        </para>
       </formalpara>

       <para>
3793 3794
        For those running 4.0 and earlier releases, use <command>bpatch</>
        to find the <varname>sysptsize</> value in the current
3795
        kernel. This is computed dynamically at boot time.
3796 3797 3798 3799
<screen>
$ <userinput>bpatch -r sysptsize</>
<computeroutput>0x9 = 9</>
</screen>
3800
        Next, add <varname>SYSPTSIZE</> as a hard-coded value in the
3801
        kernel configuration file. Increase the value you found using
3802
        <command>bpatch</>. Add 1 for every additional 4 MB of
3803 3804
        shared memory you desire.
<programlisting>
3805
options "SYSPTSIZE=16"
3806
</programlisting>
3807
        <varname>sysptsize</> cannot be changed by <command>sysctl</command>.
3808 3809 3810 3811 3812
       </para>

       <formalpara>
        <title>Semaphores</>
        <para>
3813 3814 3815 3816
         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.:
3817 3818 3819 3820
<programlisting>
options "SEMMNI=40"
options "SEMMNS=240"
</programlisting>
3821 3822
        </para>
       </formalpara>
3823 3824 3825 3826 3827
      </listitem>
     </varlistentry>


     <varlistentry>
3828 3829 3830
      <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
3831 3832 3833
      <indexterm><primary>FreeBSD</><secondary>IPC configuration</></>
      <indexterm><primary>NetBSD</><secondary>IPC configuration</></>
      <indexterm><primary>OpenBSD</><secondary>IPC configuration</></>
3834 3835 3836 3837 3838 3839
      <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
3840
        shows an example of how to set the various parameters:
3841
<programlisting>
3842 3843 3844 3845 3846 3847 3848 3849
options         SYSVSHM
options         SHMMAXPGS=4096
options         SHMSEG=256

options         SYSVSEM
options         SEMMNI=256
options         SEMMNS=512
options         SEMMNU=256
3850 3851
options         SEMMAP=256
</programlisting>
3852 3853
        (On <systemitem class="osname">NetBSD</> and <systemitem
        class="osname">OpenBSD</> the key word is actually
3854
        <literal>option</literal> singular.)
3855
       </para>
3856
       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
3857 3858 3859 3860
        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
3861
       </para>
3862 3863 3864 3865 3866
      </listitem>
     </varlistentry>


     <varlistentry>
3867
      <term><systemitem class="osname">HP-UX</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3868
      <indexterm><primary>HP-UX</><secondary>IPC configuration</></>
3869 3870 3871
      <listitem>
       <para>
        The default settings tend to suffice for normal installations.
3872
        On <productname>HP-UX</> 10, the factory default for
3873 3874 3875 3876
        <varname>SEMMNS</> is 128, which might be too low for larger
        database sites.
       </para>
       <para>
3877
        <acronym>IPC</> parameters can be set in the <application>System
3878 3879
        Administration Manager</> (<acronym>SAM</>) under
        <menuchoice><guimenu>Kernel
3880 3881
        Configuration</><guimenuitem>Configurable Parameters</></>. Hit
        <guibutton>Create A New Kernel</> when you're done.
3882 3883 3884 3885 3886 3887
       </para>
      </listitem>
     </varlistentry>


     <varlistentry>
3888
      <term><systemitem class="osname">Linux</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3889
      <indexterm><primary>Linux</><secondary>IPC configuration</></>
3890 3891
      <listitem>
       <para>
3892 3893 3894 3895 3896 3897 3898 3899 3900 3901 3902 3903
        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>

3904
       <para>
3905 3906 3907 3908
        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:
3909 3910 3911 3912 3913
<programlisting>
kernel.shmall = 134217728
kernel.shmmax = 134217728
</programlisting>
        This file is usually processed at boot time, but
3914
        <command>sysctl</command> can also be called
3915 3916 3917
        explicitly later.
       </para>

3918
       <para>
3919 3920
        Other parameters are sufficiently sized for any application. If
        you want to see for yourself look in
3921 3922
        <filename>/usr/src/linux/include/asm-<replaceable>xxx</>/shmparam.h</>
        and <filename>/usr/src/linux/include/linux/sem.h</>.
3923 3924 3925 3926 3927
       </para>
      </listitem>
     </varlistentry>


3928
     <varlistentry>
3929
      <term><systemitem class="osname">MacOS X</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3930
      <indexterm><primary>MacOS X</><secondary>IPC configuration</></>
3931 3932
      <listitem>
       <para>
3933 3934
        In OS X 10.2 and earlier, edit the file
        <filename>/System/Library/StartupItems/SystemTuning/SystemTuning</>
3935
        and change the values in the following commands:
3936 3937 3938 3939 3940 3941 3942
<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>
3943
        In OS X 10.3, these commands have been moved to <filename>/etc/rc</>
3944
        and must be edited there.
3945 3946 3947 3948 3949
       </para>
      </listitem>
     </varlistentry>


3950
     <varlistentry>
3951
      <term><systemitem class="osname">SCO OpenServer</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
3952
      <indexterm><primary>SCO OpenServer</><secondary>IPC configuration</></>
3953 3954 3955 3956
      <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
3957
        12</>. To increase the setting, first change to the directory
3958
        <filename>/etc/conf/cf.d</>. To display the current value of
3959
        <varname>SHMMAX</>, run
3960 3961 3962
<programlisting>
./configure -y SHMMAX
</programlisting>
3963
        To set a new value for <varname>SHMMAX</>, run
3964 3965 3966 3967
<programlisting>
./configure SHMMAX=<replaceable>value</>
</programlisting>
        where <replaceable>value</> is the new value you want to use
3968
        (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel:
3969 3970 3971 3972 3973 3974 3975 3976
<programlisting>
./link_unix
</programlisting>
        and reboot.
       </para>
      </listitem>
     </varlistentry>

Bruce Momjian's avatar
Bruce Momjian committed
3977 3978 3979 3980 3981 3982 3983 3984 3985 3986 3987 3988 3989 3990 3991 3992 3993 3994 3995 3996
     <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>      
3997 3998

     <varlistentry>
3999
      <term><systemitem class="osname">Solaris</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
4000
      <indexterm><primary>Solaris</><secondary>IPC configuration</></>
4001 4002
      <listitem>
       <para>
4003 4004
        At least in version 2.6, the default maximum size of a shared
        memory segments is too low for <productname>PostgreSQL</>. The
4005 4006 4007 4008 4009 4010 4011 4012 4013 4014 4015 4016 4017
        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>
4018
        You need to reboot for the changes to take effect.
4019 4020 4021 4022
       </para>

       <para>
        See also <ulink
4023
        url="http://sunsite.uakom.sk/sunworldonline/swol-09-1997/swol-09-insidesolaris.html"></>
4024 4025 4026 4027 4028 4029 4030 4031
        for information on shared memory under
        <productname>Solaris</>.
       </para>
      </listitem>
     </varlistentry>


     <varlistentry>
4032
      <term><systemitem class="osname">UnixWare</></term>
Peter Eisentraut's avatar
Peter Eisentraut committed
4033
      <indexterm><primary>UnixWare</><secondary>IPC configuration</></>
4034 4035 4036 4037 4038 4039 4040 4041 4042 4043
      <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
4044 4045
        values. To set a new value for <varname>SHMMAX</>,
        run
4046 4047 4048 4049 4050
<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
4051
        kernel:
4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063
<programlisting>
/etc/conf/bin/idbuild -B
</programlisting>
        and reboot.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>

  </sect2>

4064 4065 4066 4067 4068

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

   <para>
4069 4070 4071 4072 4073 4074 4075 4076 4077 4078 4079 4080 4081 4082
    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>
4083 4084
    controls the various resource limits set during login. See the
    operating system documentation for details. The relevant
4085
    parameters are <varname>maxproc</varname>,
4086 4087
    <varname>openfiles</varname>, and <varname>datasize</varname>. For
    example:
4088 4089 4090 4091 4092 4093 4094 4095 4096 4097 4098 4099 4100
<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>
4101
    Kernels can also have system-wide limits on some resources.
4102 4103 4104
    <itemizedlist>
     <listitem>
      <para>
4105 4106
      On <productname>Linux</productname>
      <filename>/proc/sys/fs/file-max</filename> determines the
4107
      maximum number of open files that the kernel will support.  It can
4108 4109 4110 4111 4112 4113
      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.
4114 4115 4116
      </para>
     </listitem>
    </itemizedlist>
4117 4118 4119
   </para>

   <para>
4120
    The <productname>PostgreSQL</productname> server uses one process
4121 4122 4123 4124 4125 4126 4127 4128 4129 4130 4131 4132 4133 4134
    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>
4135 4136 4137

   <para>
    On the other side of the coin, some systems allow individual
4138 4139
    processes to open large numbers of files; if more than a few
    processes do so then the system-wide limit can easily be exceeded.
4140 4141 4142
    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
4143
    limit the consumption of open files.
4144
   </para>
4145
  </sect2>
4146

Peter Eisentraut's avatar
Peter Eisentraut committed
4147 4148 4149 4150
  <sect2>
   <title>Linux Memory Overcommit</title>

   <para>
4151
    In Linux 2.4 and later, the default virtual memory behavior is not
4152 4153 4154 4155 4156
    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
4157 4158 4159
   </para>

   <para>
4160 4161 4162
    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
4163 4164 4165
<programlisting>
Out of Memory: Killed process 12345 (postmaster). 
</programlisting>
4166
    This indicates that the <filename>postmaster</filename> process
4167 4168 4169
    has been terminated due to memory pressure.
    Although existing database connections will continue to function
    normally, no new connections will be accepted.  To recover,
4170
    <productname>PostgreSQL</productname> will need to be restarted.
Peter Eisentraut's avatar
Peter Eisentraut committed
4171 4172 4173
   </para>

   <para>
4174 4175
    One way to avoid this problem is to run
    <productname>PostgreSQL</productname>
Peter Eisentraut's avatar
Peter Eisentraut committed
4176
    on a machine where you can be sure that other processes will not
4177 4178 4179 4180 4181 4182 4183
    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
4184 4185 4186
<programlisting>
sysctl -w vm.overcommit_memory=2
</programlisting>
4187 4188 4189 4190
    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
4191 4192 4193
   </para>

   <para>
4194 4195 4196 4197 4198 4199 4200 4201 4202 4203 4204 4205
    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
4206 4207
   </para>
  </sect2>
4208 4209 4210
 </sect1>


4211
 <sect1 id="postmaster-shutdown">
4212
  <title>Shutting Down the Server</title>
4213

Peter Eisentraut's avatar
Peter Eisentraut committed
4214 4215 4216 4217
  <indexterm zone="postmaster-shutdown">
   <primary>shutdown</>
  </indexterm>

4218
  <para>
4219
   There are several ways to shut down the database server. You control
4220 4221 4222
   the type of shutdown by sending different signals to the
   <command>postmaster</command> process.

4223 4224
   <variablelist>
    <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
4225
     <term><systemitem>SIGTERM</systemitem><indexterm><primary>SIGTERM</></></term>
4226 4227
     <listitem>
      <para>
4228 4229 4230 4231
       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
4232
       Shutdown</firstterm>.
4233 4234 4235 4236 4237
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
4238
     <term><systemitem>SIGINT</systemitem><indexterm><primary>SIGINT</></></term>
4239 4240
     <listitem>
      <para>
4241 4242
       The server disallows new connections and sends all existing
       server processes <systemitem>SIGTERM</systemitem>, which will cause them
4243
       to abort their current transactions and exit promptly. It then
4244
       waits for the server processes to exit and finally shuts down. This is the
4245
       <firstterm>Fast Shutdown</firstterm>.
4246 4247 4248 4249 4250
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
4251
     <term><systemitem>SIGQUIT</systemitem><indexterm><primary>SIGQUIT</></></term>
4252
     <listitem>
4253 4254 4255 4256 4257
      <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
4258 4259 4260 4261
      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.
4262 4263 4264 4265
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
4266
  </para>
4267

4268
   <important>
4269
    <para>
4270
     It is best not to use <systemitem>SIGKILL</systemitem> to shut down
4271
     the server. This will prevent the server from releasing
4272 4273
     shared memory and semaphores, which may then have to be done by
     manually.
4274
    </para>
4275
   </important>
4276

4277 4278 4279
  <para>
   The <acronym>PID</> of the <command>postmaster</command> process can be found using the
   <command>ps</command> program, or from the file
4280 4281 4282
   <filename>postmaster.pid</filename> in the data directory. So for
   example, to do a fast shutdown:
<screen>
Peter Eisentraut's avatar
Peter Eisentraut committed
4283
$ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
4284 4285 4286
</screen>
  </para>
  <para>
4287
   The program <command>pg_ctl</command> is a shell script
4288
   that provides a more convenient interface for shutting down the
4289
   server.
4290 4291 4292
  </para>
 </sect1>

4293
 <sect1 id="ssl-tcp">
4294
  <title>Secure TCP/IP Connections with SSL</title>
4295

4296 4297 4298 4299
  <indexterm zone="ssl-tcp">
   <primary>SSL</primary>
  </indexterm>

4300
  <para>
4301 4302
   <productname>PostgreSQL</> has native support for using
   <acronym>SSL</> connections to encrypt client/server communications
4303 4304 4305 4306
   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">).
4307 4308 4309
  </para>

  <para>
4310 4311
   With <acronym>SSL</> support compiled in, the
   <productname>PostgreSQL</> server can be started with
4312
   <acronym>SSL</> enabled by setting the parameter
4313
   <xref linkend="guc-ssl"> to on in <filename>postgresql.conf</>. When
4314 4315
   starting in <acronym>SSL</> mode, the server will look for the
   files <filename>server.key</> and <filename>server.crt</> in the
4316 4317
   data directory, which should contain the server private key
   and certificate, respectively. These files must be set up correctly
4318 4319 4320
   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.
4321 4322 4323
  </para>

  <para>
4324
   The server will listen for both standard and <acronym>SSL</>
4325
   connections on the same TCP port, and will negotiate with any
4326
   connecting client on whether to use <acronym>SSL</>. See <xref
4327
   linkend="auth-pg-hba-conf"> about how to force the server to
4328
   require use of <acronym>SSL</> for certain connections.
4329 4330 4331 4332
  </para>

  <para>
   For details on how to create your server private key and certificate,
4333 4334
   refer to the <productname>OpenSSL</> documentation. A simple
   self-signed certificate can be used to get started for testing, but a
4335
   certificate signed by a certificate authority (<acronym>CA</>) (either one of the global
4336 4337 4338 4339
   <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:
4340
<programlisting>
4341
openssl req -new -text -out server.req
4342
</programlisting>
4343
   Fill out the information that <command>openssl</> asks for. Make sure
4344
   that you enter the local host name as <quote>Common Name</>; the challenge
4345
   password can be left blank. The program will generate a key that is
4346
   passphrase protected; it will not accept a passphrase that is less
4347 4348
   than four characters long. To remove the passphrase (as you must if
   you want automatic start-up of the server), run the commands
4349
<programlisting>
4350 4351
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
4352
</programlisting>
4353
   Enter the old passphrase to unlock the existing key. Now do
4354
<programlisting>
4355 4356
openssl req -x509 -in server.req -text -key server.key -out server.crt
chmod og-rwx server.key
4357
</programlisting>
4358
   to turn the certificate into a self-signed certificate and to copy the
Peter Eisentraut's avatar
Peter Eisentraut committed
4359
   key and certificate to where the server will look for them.
4360 4361 4362
  </para>
 </sect1>

4363
 <sect1 id="ssh-tunnels">
4364
  <title>Secure TCP/IP Connections with <application>SSH</application> Tunnels</title>
4365

4366 4367 4368 4369
  <indexterm zone="ssh-tunnels">
   <primary>ssh</primary>
  </indexterm>

4370
  <para>
4371
   One can use <application>SSH</application> to encrypt the network
4372
   connection between clients and a
4373
   <productname>PostgreSQL</productname> server. Done properly, this
4374
   provides an adequately secure network connection.
4375 4376 4377
  </para>

  <para>
4378
   First make sure that an <application>SSH</application> server is
4379 4380
   running properly on the same machine as the
   <productname>PostgreSQL</productname> server and that you can log in using
4381 4382
   <command>ssh</command> as some user. Then you can establish a secure
   tunnel with a command like this from the client machine:
4383
<programlisting>
4384
ssh -L 3333:foo.com:5432 joe@foo.com
4385 4386 4387
</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
4388
   second number, 5432, is the remote end of the tunnel: the port
Peter Eisentraut's avatar
Peter Eisentraut committed
4389
   number your server is using. The name or the address in between
4390 4391 4392 4393 4394 4395 4396 4397 4398
   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
4399
   tunnel setup to succeed you must be allowed to connect via
4400
   <command>ssh</command> as <literal>joe@foo.com</literal>, just
4401
   as if you had attempted to use <command>ssh</command> to set up a
4402 4403 4404
   terminal session.
  </para>

4405 4406
  <tip>
   <para>
4407
    Several other applications exist that can provide secure tunnels using
4408 4409 4410 4411
    a procedure similar in concept to the one just described.
   </para>
  </tip>

4412
 </sect1>
4413 4414

</Chapter>
4415 4416 4417

<!-- Keep this comment at the end of the file
Local variables:
4418
mode:sgml
4419 4420 4421 4422 4423 4424 4425 4426 4427
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
4428
sgml-local-catalogs:("/usr/lib/sgml/catalog")
4429 4430 4431
sgml-local-ecat-files:nil
End:
-->