2 $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.301 2005/01/08 22:13:35 tgl Exp $
6 <Title>Server Run-time Environment</Title>
9 This chapter discusses how to set up and run the database server
10 and its interactions with the operating system.
13 <sect1 id="postgres-user">
14 <title>The <productname>PostgreSQL</productname> User Account</title>
17 <primary>postgres user</primary>
21 As with any other server daemon that is accessible to the outside world,
22 it is advisable to run <productname>PostgreSQL</productname> under a
23 separate user account. This user account should only own the data
24 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 idea.) It is not advisable to install executables owned by this
27 user because compromised systems could then modify their own
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 name <systemitem>postgres</systemitem> is often used, and is assumed
35 throughout this book, but you can use another name if you like.
39 <sect1 id="creating-cluster">
40 <title>Creating a Database Cluster</title>
43 <primary>database cluster</primary>
47 <primary>data area</primary>
48 <see>database cluster</see>
52 Before you can do anything, you must initialize a database storage
53 area on disk. We call this a <firstterm>database cluster</firstterm>.
54 (<acronym>SQL</acronym> uses the term catalog cluster.) A
55 database cluster is a collection of databases that is managed by a
56 single instance of a running database server. After initialization, a
57 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 used for actual work. (See <xref linkend="managing-databases"> for
61 information about creating new databases within a cluster.)
65 In file system terms, a database cluster will be a single directory
66 under which all data will be stored. We call this the <firstterm>data
67 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
72 database cluster, use the command <xref
73 linkend="app-initdb">,<indexterm><primary>initdb</></> which is
74 installed with <productname>PostgreSQL</productname>. The desired
75 file system location of your database cluster is indicated by the
76 <option>-D</option> option, for example
78 <prompt>$</> <userinput>initdb -D /usr/local/pgsql/data</userinput>
80 Note that you must execute this command while logged into the
81 <productname>PostgreSQL</productname> user account, which is
82 described in the previous section.
87 As an alternative to the <option>-D</option> option, you can set
88 the environment variable <envar>PGDATA</envar>.
89 <indexterm><primary><envar>PGDATA</envar></primary></indexterm>
94 <command>initdb</command> will attempt to create the directory you
95 specify if it does not already exist. It is likely that it will not
96 have the permission to do so (if you followed our advice and created
97 an unprivileged account). In that case you should create the
98 directory yourself (as root) and change the owner to be the
99 <productname>PostgreSQL</productname> user. Here is how this might
102 root# <userinput>mkdir /usr/local/pgsql/data</userinput>
103 root# <userinput>chown postgres /usr/local/pgsql/data</userinput>
104 root# <userinput>su postgres</userinput>
105 postgres$ <userinput>initdb -D /usr/local/pgsql/data</userinput>
110 <command>initdb</command> will refuse to run if the data directory
111 looks like it has already been initialized.</para>
114 Because the data directory contains all the data stored in the
115 database, it is essential that it be secured from unauthorized
116 access. <command>initdb</command> therefore revokes access
117 permissions from everyone but the
118 <productname>PostgreSQL</productname> user.
122 However, while the directory contents are secure, the default
123 client authentication setup allows any local user to connect to the
124 database and even become the database superuser. If you do not
125 trust other local users, we recommend you use one of
126 <command>initdb</command>'s <option>-W</option>, <option>--pwprompt</option>
127 or <option>--pwfile</option> options to assign a password to the
128 database superuser.<indexterm><primary>password</><secondary>of the
129 superuser</></indexterm> Also, specify <option>-A md5</> or
130 <option>-A password</> so that the default <literal>trust</> authentication
131 mode is not used; or modify the generated <filename>pg_hba.conf</filename>
132 file after running <command>initdb</command>,
133 <emphasis>before</> you start the server for the first time. (Other
134 reasonable approaches include using <literal>ident</literal> authentication
135 or file system permissions to restrict connections. See <xref
136 linkend="client-authentication"> for more information.)
140 <command>initdb</command> also initializes the default
141 locale<indexterm><primary>locale</></> for the database cluster.
142 Normally, it will just take the locale settings in the environment
143 and apply them to the initialized database. It is possible to
144 specify a different locale for the database; more information about
145 that can be found in <xref linkend="locale">. The sort order used
146 within a particular database cluster is set by
147 <command>initdb</command> and cannot be changed later, short of
148 dumping all data, rerunning <command>initdb</command>, and reloading
149 the data. There is also a performance impact for using locales
150 other than <literal>C</> or <literal>POSIX</>. Therefore, it is
151 important to make this choice correctly the first time.
155 <command>initdb</command> also sets the default character set encoding
156 for the database cluster. Normally this should be chosen to match the
157 locale setting. For details see <xref linkend="multibyte">.
161 <sect1 id="postmaster-start">
162 <title>Starting the Database Server</title>
165 Before anyone can access the database, you must start the database
166 server. The database server program is called
167 <command>postmaster</command>.<indexterm><primary>postmaster</></>
168 The <command>postmaster</command> must know where to
169 find the data it is supposed to use. This is done with the
170 <option>-D</option> option. Thus, the simplest way to start the
173 $ <userinput>postmaster -D /usr/local/pgsql/data</userinput>
175 which will leave the server running in the foreground. This must be
176 done while logged into the <productname>PostgreSQL</productname> user
177 account. Without <option>-D</option>, the server will try to use
178 the data directory named by the environment variable <envar>PGDATA</envar>.
179 If that variable is not provided either, it will fail.
183 Normally it is better to start the <command>postmaster</command> in the
184 background. For this, use the usual shell syntax:
186 $ <userinput>postmaster -D /usr/local/pgsql/data >logfile 2>&1 &</userinput>
188 It is important to store the server's <systemitem>stdout</> and
189 <systemitem>stderr</> output somewhere, as shown above. It will help
190 for auditing purposes and to diagnose problems. (See <xref
191 linkend="logfile-maintenance"> for a more thorough discussion of log
196 The <command>postmaster</command> also takes a number of other
197 command line options. For more information, see the
198 <xref linkend="app-postmaster"> reference page
199 and <xref linkend="runtime-config"> below.
203 This shell syntax can get tedious quickly. Therefore the wrapper
205 <xref linkend="app-pg-ctl"><indexterm><primary>pg_ctl</primary></indexterm>
206 is provided to simplify some tasks. For example:
208 pg_ctl start -l logfile
210 will start the server in the background and put the output into the
211 named log file. The <option>-D</option> option has the same meaning
212 here as in the <command>postmaster</command>. <command>pg_ctl</command>
213 is also capable of stopping the server.
217 Normally, you will want to start the database server when the
218 computer boots.<indexterm><primary>booting</><secondary>starting
219 the server during</></> Autostart scripts are operating-system-specific.
220 There are a few distributed with
221 <productname>PostgreSQL</productname> in the
222 <filename>contrib/start-scripts</> directory. Installing one will require
227 Different systems have different conventions for starting up daemons
228 at boot time. Many systems have a file
229 <filename>/etc/rc.local</filename> or
230 <filename>/etc/rc.d/rc.local</filename>. Others use
231 <filename>rc.d</> directories. Whatever you do, the server must be
232 run by the <productname>PostgreSQL</productname> user account
233 <emphasis>and not by root</emphasis> or any other user. Therefore you
234 probably should form your commands using <literal>su -c '...'
235 postgres</literal>. For example:
237 su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres
242 Here are a few more operating-system-specific suggestions. (In each
243 case be sure to use the proper installation directory and user
244 name where we show generic values.)
249 For <productname>FreeBSD</productname>, look at the file
250 <filename>contrib/start-scripts/freebsd</filename> in the
251 <productname>PostgreSQL</productname> source distribution.
252 <indexterm><primary>FreeBSD</><secondary>start script</secondary></>
258 On <productname>OpenBSD</productname>, add the following lines
259 to the file <filename>/etc/rc.local</filename>:
260 <indexterm><primary>OpenBSD</><secondary>start script</secondary></>
262 if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postmaster ]; then
263 su - -c '/usr/local/pgsql/bin/pg_ctl start -l /var/postgresql/log -s' postgres
264 echo -n ' postgresql'
272 On <productname>Linux</productname> systems either add
273 <indexterm><primary>Linux</><secondary>start script</secondary></>
275 /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
277 to <filename>/etc/rc.d/rc.local</filename> or look at the file
278 <filename>contrib/start-scripts/linux</filename> in the
279 <productname>PostgreSQL</productname> source distribution.
285 On <productname>NetBSD</productname>, either use the
286 <productname>FreeBSD</productname> or
287 <productname>Linux</productname> start scripts, depending on
288 preference. <indexterm><primary>NetBSD</><secondary>start script</secondary></>
294 On <productname>Solaris</productname>, create a file called
295 <filename>/etc/init.d/postgresql</filename> that contains
297 <indexterm><primary>Solaris</><secondary>start script</secondary></>
299 su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
301 Then, create a symbolic link to it in <filename>/etc/rc3.d</> as
302 <filename>S99postgresql</>.
310 While the <command>postmaster</command> is running, its
311 <acronym>PID</acronym> is stored in the file
312 <filename>postmaster.pid</filename> in the data directory. This is
313 used to prevent multiple <command>postmaster</command> processes
314 running in the same data directory and can also be used for
315 shutting down the <command>postmaster</command> process.
318 <sect2 id="postmaster-start-failures">
319 <title>Server Start-up Failures</title>
322 There are several common reasons the server might fail to
323 start. Check the server's log file, or start it by hand (without
324 redirecting standard output or standard error) and see what error
325 messages appear. Below we explain some of the most common error
326 messages in more detail.
331 LOG: could not bind IPv4 socket: Address already in use
332 HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
333 FATAL: could not create TCP/IP listen socket
335 This usually means just what it suggests: you tried to start
336 another <command>postmaster</command> on the same port where one is already running.
337 However, if the kernel error message is not <computeroutput>Address
338 already in use</computeroutput> or some variant of that, there may
339 be a different problem. For example, trying to start a <command>postmaster</command>
340 on a reserved port number may draw something like:
342 $ <userinput>postmaster -p 666</userinput>
343 LOG: could not bind IPv4 socket: Permission denied
344 HINT: Is another postmaster already running on port 666? If not, wait a few seconds and retry.
345 FATAL: could not create TCP/IP listen socket
352 FATAL: could not create shared memory segment: Invalid argument
353 DETAIL: Failed system call was shmget(key=5440001, size=4011376640, 03600).
355 probably means your kernel's limit on the size of shared memory is
356 smaller than the work area <productname>PostgreSQL</productname>
357 is trying to create (4011376640 bytes in this example). Or it could
358 mean that you do not have System-V-style shared memory support
359 configured into your kernel at all. As a temporary workaround, you
360 can try starting the server with a smaller-than-normal number
361 of buffers (<option>-B</option> switch). You will eventually want
362 to reconfigure your kernel to increase the allowed shared memory
363 size. You may also see this message when trying to start multiple
364 servers on the same machine, if their total space requested
365 exceeds the kernel limit.
371 FATAL: could not create semaphores: No space left on device
372 DETAIL: Failed system call was semget(5440126, 17, 03600).
374 does <emphasis>not</emphasis> mean you've run out of disk
375 space. It means your kernel's limit on the number of <systemitem
376 class="osname">System V</> semaphores is smaller than the number
377 <productname>PostgreSQL</productname> wants to create. As above,
378 you may be able to work around the problem by starting the
379 server with a reduced number of allowed connections
380 (<option>-N</option> switch), but you'll eventually want to
381 increase the kernel limit.
385 If you get an <quote>illegal system call</> error, it is likely that
386 shared memory or semaphores are not supported in your kernel at
387 all. In that case your only option is to reconfigure the kernel to
388 enable these features.
392 Details about configuring <systemitem class="osname">System V</>
393 <acronym>IPC</> facilities are given in <xref linkend="sysvipc">.
397 <sect2 id="client-connection-problems">
398 <title>Client Connection Problems</title>
401 Although the error conditions possible on the client side are quite
402 varied and application-dependent, a few of them might be directly
403 related to how the server was started up. Conditions other than
404 those shown below should be documented with the respective client
410 psql: could not connect to server: Connection refused
411 Is the server running on host "server.joe.com" and accepting
412 TCP/IP connections on port 5432?
414 This is the generic <quote>I couldn't find a server to talk
415 to</quote> failure. It looks like the above when TCP/IP
416 communication is attempted. A common mistake is to forget to
417 configure the server to allow TCP/IP connections.
421 Alternatively, you'll get this when attempting Unix-domain socket
422 communication to a local server:
424 psql: could not connect to server: No such file or directory
425 Is the server running locally and accepting
426 connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
431 The last line is useful in verifying that the client is trying to
432 connect to the right place. If there is in fact no server
433 running there, the kernel error message will typically be either
434 <computeroutput>Connection refused</computeroutput> or
435 <computeroutput>No such file or directory</computeroutput>, as
436 illustrated. (It is important to realize that
437 <computeroutput>Connection refused</computeroutput> in this context
438 does <emphasis>not</emphasis> mean that the server got your
439 connection request and rejected it. That case will produce a
440 different message, as shown in <xref
441 linkend="client-authentication-problems">.) Other error messages
442 such as <computeroutput>Connection timed out</computeroutput> may
443 indicate more fundamental problems, like lack of network
449 <sect1 id="runtime-config">
450 <Title>Run-time Configuration</Title>
453 <primary>configuration</primary>
454 <secondary>of the server</secondary>
458 There are a lot of configuration parameters that affect the
459 behavior of the database system. In this subsection, we describe
460 how to set configuration parameters; the following subsections
461 discuss each parameter in detail.
465 All parameter names are case-insensitive. Every parameter takes a
466 value of one of four types: boolean, integer, floating point,
467 or string. Boolean values may be written as <literal>ON</literal>,
468 <literal>OFF</literal>, <literal>TRUE</literal>,
469 <literal>FALSE</literal>, <literal>YES</literal>,
470 <literal>NO</literal>, <literal>1</literal>, <literal>0</literal>
471 (all case-insensitive) or any unambiguous prefix of these.
475 One way to set these parameters is to edit the file
476 <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>,
477 which is normally kept in the data directory. (<command>initdb</>
478 installs a default copy there.) An example of what this file might look
482 log_connections = yes
483 log_destination = 'syslog'
484 search_path = '$user, public'
486 One parameter is specified per line. The equal sign between name and
487 value is optional. Whitespace is insignificant and blank lines are
488 ignored. Hash marks (<literal>#</literal>) introduce comments
489 anywhere. Parameter values that are not simple identifiers or
490 numbers must be single-quoted.
495 <primary>SIGHUP</primary>
497 The configuration file is reread whenever the
498 <command>postmaster</command> process receives a
499 <systemitem>SIGHUP</> signal (which is most easily sent by means
500 of <literal>pg_ctl reload</>). The <command>postmaster</command>
501 also propagates this signal to all currently running server
502 processes so that existing sessions also get the new
503 value. Alternatively, you can send the signal to a single server
504 process directly. Some parameters can only be set at server start;
505 any changes to their entries in the configuration file will be ignored
506 until the server is restarted.
510 A second way to set these configuration parameters is to give them
511 as a command line option to the <command>postmaster</command>, such as:
513 postmaster -c log_connections=yes -c log_destination='syslog'
515 Command-line options override any conflicting settings in
516 <filename>postgresql.conf</filename>. Note that this means you won't
517 be able to change the value on-the-fly by editing
518 <filename>postgresql.conf</filename>, so while the command-line
519 method may be convenient, it can cost you flexibility later.
523 Occasionally it is useful to give a command line option to
524 one particular session only. The environment variable
525 <envar>PGOPTIONS</envar> can be used for this purpose on the
528 env PGOPTIONS='-c geqo=off' psql
530 (This works for any <application>libpq</>-based client application, not
531 just <application>psql</application>.) Note that this won't work for
532 parameters that are fixed when the server is started or that must be
533 specified in <filename>postgresql.conf</filename>.
537 Furthermore, it is possible to assign a set of option settings to
538 a user or a database. Whenever a session is started, the default
539 settings for the user and database involved are loaded. The
540 commands <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
541 and <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">,
542 respectively, are used to configure these settings. Per-database
543 settings override anything received from the
544 <command>postmaster</command> command-line or the configuration
545 file, and in turn are overridden by per-user settings; both are
546 overridden by per-session options.
550 Some parameters can be changed in individual <acronym>SQL</acronym>
551 sessions with the <xref linkend="SQL-SET" endterm="SQL-SET-title">
552 command, for example:
554 SET ENABLE_SEQSCAN TO OFF;
556 If <command>SET</> is allowed, it overrides all other sources of
557 values for the parameter. Some parameters cannot be changed via
558 <command>SET</command>: for example, if they control behavior that
559 cannot reasonably be changed without restarting
560 <productname>PostgreSQL</productname>. Also, some parameters can
561 be modified via <command>SET</command> or <command>ALTER</> by superusers,
562 but not by ordinary users.
566 The <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title">
567 command allows inspection of the current values of all parameters.
571 The virtual table <structname>pg_settings</structname>
572 (described in <xref linkend="view-pg-settings">) also allows
573 displaying and updating session run-time parameters. It is equivalent
574 to <command>SHOW</> and <command>SET</>, but can be more convenient
575 to use because it can be joined with other tables, or selected from using
576 any desired selection condition.
579 <sect2 id="runtime-config-file-locations">
580 <title>File Locations</title>
583 In addition to the <filename>postgresql.conf</filename> file
584 already mentioned, <productname>PostgreSQL</productname> uses
585 two other manually-edited configuration files, which control
586 client authentication (their use is discussed in <xref
587 linkend="client-authentication">).
588 By default, all three configuration files are stored
589 in the database cluster's data directory. The options described
590 in this subsection allow the configuration files to be placed elsewhere.
591 (Doing so can ease administration. In particular it is often
592 easier to ensure that the configuration files are properly backed-up
593 when they are kept separate.)
597 <varlistentry id="guc-data-directory" xreflabel="data_directory">
598 <term><varname>data_directory</varname> (<type>string</type>)</term>
600 <primary><varname>data_directory</> configuration parameter</primary>
604 Specifies the directory to use for data storage.
605 This option can only be set at server start.
610 <varlistentry id="guc-config-file" xreflabel="config_file">
611 <term><varname>config_file</varname> (<type>string</type>)</term>
613 <primary><varname>config_file</> configuration parameter</primary>
617 Specifies the main server configuration file
618 (customarily called <filename>postgresql.conf</>).
619 This option can only be set on the postmaster command line.
624 <varlistentry id="guc-hba-file" xreflabel="hba_file">
625 <term><varname>hba_file</varname> (<type>string</type>)</term>
627 <primary><varname>hba_file</> configuration parameter</primary>
631 Specifies the configuration file for host-based authentication
632 (customarily called <filename>pg_hba.conf</>).
633 This option can only be set at server start.
638 <varlistentry id="guc-ident-file" xreflabel="ident_file">
639 <term><varname>ident_file</varname> (<type>string</type>)</term>
641 <primary><varname>ident_file</> configuration parameter</primary>
645 Specifies the configuration file for
646 <application>ident</> authentication
647 (customarily called <filename>pg_ident.conf</>).
648 This option can only be set at server start.
653 <varlistentry id="guc-external-pid-file" xreflabel="external_pid_file">
654 <term><varname>external_pid_file</varname> (<type>string</type>)</term>
656 <primary><varname>external_pid_file</> configuration parameter</primary>
660 Specifies the name of an additional process-id (PID) file that the
661 <application>postmaster</> should create for use by server
662 administration programs.
663 This option can only be set at server start.
670 In a default installation, none of the above options are set explicitly.
672 data directory is specified by the <option>-D</option> command-line
673 option or the <envar>PGDATA</envar> environment variable, and the
674 configuration files are all found within the data directory.
678 If you wish to keep the configuration files elsewhere than the
679 data directory, the postmaster's <option>-D</option>
680 command-line option or <envar>PGDATA</envar> environment variable
681 must point to the directory containing the configuration files,
682 and the <varname>data_directory</> option must be set in
683 <filename>postgresql.conf</filename> (or on the command line) to show
684 where the data directory is actually located. Notice that
685 <varname>data_directory</> overrides <option>-D</option> and
686 <envar>PGDATA</envar> for the location
687 of the data directory, but not for the location of the configuration
692 If you wish, you can specify the configuration file names and locations
693 individually using the options <varname>config_file</>,
694 <varname>hba_file</> and/or <varname>ident_file</>.
695 <varname>config_file</> can only be specified on the
696 <command>postmaster</command> command line, but the others can be
697 set within the main configuration file. If all three options plus
698 <varname>data_directory</> are explicitly set, then it is not necessary
699 to specify <option>-D</option> or <envar>PGDATA</envar>.
703 When setting any of these options, a relative path will be interpreted
704 with respect to the directory in which the <command>postmaster</command>
709 <sect2 id="runtime-config-connection">
710 <title>Connections and Authentication</title>
712 <sect3 id="runtime-config-connection-settings">
713 <title>Connection Settings</title>
717 <varlistentry id="guc-listen-addresses" xreflabel="listen_addresses">
718 <term><varname>listen_addresses</varname> (<type>string</type>)</term>
720 <primary><varname>listen_addresses</> configuration parameter</primary>
724 Specifies the TCP/IP address(es) on which the server is
725 to listen for connections from client applications.
726 The value takes the form of a comma-separated list of host names
727 and/or numeric IP addresses. The special entry <literal>*</>
728 corresponds to all available IP interfaces.
729 If the list is empty, the server does not listen on any IP interface
730 at all, in which case only Unix-domain sockets can be used to connect
732 The default value is <systemitem class="systemname">localhost</>,
733 which allows only local <quote>loopback</> connections to be made.
734 This parameter can only be set at server start.
739 <varlistentry id="guc-port" xreflabel="port">
740 <term><varname>port</varname> (<type>integer</type>)</term>
742 <primary><varname>port</> configuration parameter</primary>
746 The TCP port the server listens on; 5432 by default. Note that the
747 same port number is used for all IP addresses the server listens on.
748 This parameter can only be set at server start.
753 <varlistentry id="guc-max-connections" xreflabel="max_connections">
754 <term><varname>max_connections</varname> (<type>integer</type>)</term>
756 <primary><varname>max_connections</> configuration parameter</primary>
760 Determines the maximum number of concurrent connections to the
761 database server. The default is typically 100, but may be less
762 if your kernel settings will not support it (as determined
763 during <application>initdb</>). This parameter can only be
768 Increasing this parameter may cause <productname>PostgreSQL</>
769 to request more <systemitem class="osname">System V</> shared
770 memory or semaphores than your operating system's default configuration
771 allows. See <xref linkend="sysvipc"> for information on how to
772 adjust those parameters, if necessary.
777 <varlistentry id="guc-superuser-reserved-connections"
778 xreflabel="superuser_reserved_connections">
779 <term><varname>superuser_reserved_connections</varname>
780 (<type>integer</type>)</term>
782 <primary><varname>superuser_reserved_connections</> configuration parameter</primary>
786 Determines the number of connection <quote>slots</quote> that
787 are reserved for connections by <productname>PostgreSQL</>
788 superusers. At most <xref linkend="guc-max-connections">
789 connections can ever be active simultaneously. Whenever the
790 number of active concurrent connections is at least
791 <varname>max_connections</> minus
792 <varname>superuser_reserved_connections</varname>, new
793 connections will be accepted only for superusers.
797 The default value is 2. The value must be less than the value of
798 <varname>max_connections</varname>. This parameter can only be
804 <varlistentry id="guc-unix-socket-directory" xreflabel="unix_socket_directory">
805 <term><varname>unix_socket_directory</varname> (<type>string</type>)</term>
807 <primary><varname>unix_socket_directory</> configuration parameter</primary>
811 Specifies the directory of the Unix-domain socket on which the
812 server is to listen for
813 connections from client applications. The default is normally
814 <filename>/tmp</filename>, but can be changed at build time.
815 This parameter can only be set at server start.
820 <varlistentry id="guc-unix-socket-group" xreflabel="unix_socket_group">
821 <term><varname>unix_socket_group</varname> (<type>string</type>)</term>
823 <primary><varname>unix_socket_group</> configuration parameter</primary>
827 Sets the owning group of the Unix-domain socket. (The owning
828 user of the socket is always the user that starts the
829 server.) In combination with the option
830 <varname>unix_socket_permissions</varname> this can be used as
831 an additional access control mechanism for Unix-domain connections.
832 By default this is the empty string, which uses the default
833 group for the current user. This option can only be set at
839 <varlistentry id="guc-unix-socket-permissions" xreflabel="unix_socket_permissions">
840 <term><varname>unix_socket_permissions</varname> (<type>integer</type>)</term>
842 <primary><varname>unix_socket_permissions</> configuration parameter</primary>
846 Sets the access permissions of the Unix-domain socket. Unix-domain
847 sockets use the usual Unix file system permission set.
848 The option value is expected to be a numeric mode
849 specification in the form accepted by the
850 <function>chmod</function> and <function>umask</function>
851 system calls. (To use the customary octal format the number
852 must start with a <literal>0</literal> (zero).)
856 The default permissions are <literal>0777</literal>, meaning
857 anyone can connect. Reasonable alternatives are
858 <literal>0770</literal> (only user and group, see also
859 <varname>unix_socket_group</varname>) and <literal>0700</literal>
860 (only user). (Note that for a Unix-domain socket, only write
861 permission matters and so there is no point in setting or revoking
862 read or execute permissions.)
866 This access control mechanism is independent of the one
867 described in <xref linkend="client-authentication">.
871 This option can only be set at server start.
876 <varlistentry id="guc-rendezvous-name" xreflabel="rendezvous_name">
877 <term><varname>rendezvous_name</varname> (<type>string</type>)</term>
879 <primary><varname>rendezvous_name</> configuration parameter</primary>
883 Specifies the <productname>Rendezvous</productname> broadcast
884 name. By default, the computer name is used, specified as an
885 empty string ''. This option is ignored if the server was not
886 compiled with <productname>Rendezvous</productname> support. This
887 option can only be set at server start.
894 <sect3 id="runtime-config-connection-security">
895 <title>Security and Authentication</title>
898 <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
899 <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
900 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
901 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
903 <primary><varname>authentication_timeout</> configuration parameter</primary>
908 Maximum time to complete client authentication, in seconds. If a
909 would-be client has not completed the authentication protocol in
910 this much time, the server breaks the connection. This prevents
911 hung clients from occupying a connection indefinitely. This
912 option can only be set at server start or in the
913 <filename>postgresql.conf</filename> file. The default is 60.
918 <varlistentry id="guc-ssl" xreflabel="ssl">
919 <term><varname>ssl</varname> (<type>boolean</type>)</term>
921 <primary><varname>ssl</> configuration parameter</primary>
925 Enables <acronym>SSL</> connections. Please read
926 <xref linkend="ssl-tcp"> before using this. The default
927 is off. This parameter can only be set at server start.
932 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
933 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
935 <primary><varname>password_encryption</> configuration parameter</primary>
939 When a password is specified in <xref
940 linkend="sql-createuser" endterm="sql-createuser-title"> or
941 <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
942 without writing either <literal>ENCRYPTED</> or
943 <literal>UNENCRYPTED</>, this option determines whether the
944 password is to be encrypted. The default is on (encrypt the
950 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
951 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
953 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
957 Sets the location of the Kerberos server key file. See
958 <xref linkend="kerberos-auth"> for details.
963 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
964 <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
966 <primary><varname>db_user_namespace</> configuration parameter</primary>
970 This allows per-database user names. It is off by default.
974 If this is on, you should create users as <literal>username@dbname</>.
975 When <literal>username</> is passed by a connecting client,
976 <literal>@</> and the database name is appended to the user
977 name and that database-specific user name is looked up by the
978 server. Note that when you create users with names containing
979 <literal>@</> within the SQL environment, you will need to
984 With this option enabled, you can still create ordinary global
985 users. Simply append <literal>@</> when specifying the user
986 name in the client. The <literal>@</> will be stripped off
987 before the user name is looked up by the server.
992 This feature is intended as a temporary measure until a
993 complete solution is found. At that time, this option will
1004 <sect2 id="runtime-config-resource">
1005 <title>Resource Consumption</title>
1007 <sect3 id="runtime-config-resource-memory">
1008 <title>Memory</title>
1011 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
1012 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
1014 <primary><varname>shared_buffers</> configuration parameter</primary>
1018 Sets the number of shared memory buffers used by the database
1019 server. The default is typically 1000, but may be less if your
1020 kernel settings will not support it (as determined during
1021 <application>initdb</>). Each buffer is 8192 bytes, unless a
1022 different value of <symbol>BLCKSZ</symbol> was chosen when building
1023 the server. This setting must be at least 16, as well as at
1024 least twice the value of <xref linkend="guc-max-connections">;
1025 however, settings significantly higher than the minimum are
1026 usually needed for good performance. Values of a few thousand
1027 are recommended for production installations. This option can
1028 only be set at server start.
1032 Increasing this parameter may cause <productname>PostgreSQL</>
1033 to request more <systemitem class="osname">System V</> shared
1034 memory than your operating system's default configuration
1035 allows. See <xref linkend="sysvipc"> for information on how to
1036 adjust those parameters, if necessary.
1041 <varlistentry id="guc-work-mem" xreflabel="work_mem">
1042 <term><varname>work_mem</varname> (<type>integer</type>)</term>
1044 <primary><varname>work_mem</> configuration parameter</primary>
1048 Specifies the amount of memory to be used by internal sort operations
1049 and hash tables before switching to temporary disk files. The value is
1050 specified in kilobytes, and defaults to 1024 kilobytes (1 MB).
1051 Note that for a complex query, several sort or hash operations might be
1052 running in parallel; each one will be allowed to use as much memory
1053 as this value specifies before it starts to put data into temporary
1054 files. Also, several running sessions could be doing such operations
1055 concurrently. So the total memory used could be many
1056 times the value of <varname>work_mem</varname>; it is necessary to
1057 keep this fact in mind when choosing the value. Sort operations are
1058 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
1060 Hash tables are used in hash joins, hash-based aggregation, and
1061 hash-based processing of <literal>IN</> subqueries.
1066 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
1067 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
1069 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
1073 Specifies the maximum amount of memory to be used in maintenance
1074 operations, such as <command>VACUUM</command>, <command>CREATE
1075 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>.
1076 The value is specified in kilobytes, and defaults to 16384 kilobytes
1077 (16 MB). Since only one of these operations can be executed at
1078 a time by a database session, and an installation normally doesn't
1079 have very many of them happening concurrently, it's safe to set this
1080 value significantly larger than <varname>work_mem</varname>. Larger
1081 settings may improve performance for vacuuming and for restoring
1087 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
1088 <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
1090 <primary><varname>max_stack_depth</> configuration parameter</primary>
1094 Specifies the maximum safe depth of the server's execution stack.
1095 The ideal setting for this parameter is the actual stack size limit
1096 enforced by the kernel (as set by <literal>ulimit -s</> or local
1097 equivalent), less a safety margin of a megabyte or so. The safety
1098 margin is needed because the stack depth is not checked in every
1099 routine in the server, but only in key potentially-recursive routines
1100 such as expression evaluation. Setting the parameter higher than
1101 the actual kernel limit will mean that a runaway recursive function
1102 can crash an individual backend process. The default setting is
1103 2048 KB (two megabytes), which is conservatively small and unlikely
1104 to risk crashes. However, it may be too small to allow execution
1105 of complex functions.
1112 <sect3 id="runtime-config-resource-fsm">
1113 <title>Free Space Map</title>
1116 <varlistentry id="guc-max-fsm-pages" xreflabel="max_fsm_pages">
1117 <term><varname>max_fsm_pages</varname> (<type>integer</type>)</term>
1119 <primary><varname>max_fsm_pages</> configuration parameter</primary>
1123 Sets the maximum number of disk pages for which free space will
1124 be tracked in the shared free-space map. Six bytes of shared memory
1125 are consumed for each page slot. This setting must be more than
1126 16 * <varname>max_fsm_relations</varname>. The default is 20000.
1127 This option can only be set at server start.
1132 <varlistentry id="guc-max-fsm-relations" xreflabel="max_fsm_relations">
1133 <term><varname>max_fsm_relations</varname> (<type>integer</type>)</term>
1135 <primary><varname>max_fsm_relations</> configuration parameter</primary>
1139 Sets the maximum number of relations (tables and indexes) for which
1140 free space will be tracked in the shared free-space map. Roughly
1141 fifty bytes of shared memory are consumed for each slot.
1142 The default is 1000.
1143 This option can only be set at server start.
1150 <sect3 id="runtime-config-resource-kernel">
1151 <title>Kernel Resource Usage</title>
1154 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
1155 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
1157 <primary><varname>max_files_per_process</> configuration parameter</primary>
1161 Sets the maximum number of simultaneously open files allowed to each
1162 server subprocess. The default is 1000. If the kernel is enforcing
1163 a safe per-process limit, you don't need to worry about this setting.
1164 But on some platforms (notably, most BSD systems), the kernel will
1165 allow individual processes to open many more files than the system
1166 can really support when a large number of processes all try to open
1167 that many files. If you find yourself seeing <quote>Too many open
1168 files</> failures, try reducing this setting.
1169 This option can only be set at server start.
1174 <varlistentry id="guc-preload-libraries" xreflabel="preload_libraries">
1175 <term><varname>preload_libraries</varname> (<type>string</type>)</term>
1177 <primary><varname>preload_libraries</> configuration parameter</primary>
1181 This variable specifies one or more shared libraries that are
1182 to be preloaded at server start. A parameterless
1183 initialization function can optionally be called for each
1184 library. To specify that, add a colon and the name of the
1185 initialization function after the library name. For example
1186 <literal>'$libdir/mylib:mylib_init'</literal> would cause
1187 <literal>mylib</> to be preloaded and <literal>mylib_init</>
1188 to be executed. If more than one library is to be loaded,
1189 separate their names with commas.
1193 If a specified library or initialization function is not found,
1194 the server will fail to start.
1198 <productname>PostgreSQL</productname> procedural language
1199 libraries may be preloaded in this way, typically by using the
1200 syntax <literal>'$libdir/plXXX:plXXX_init'</literal> where
1201 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
1202 <literal>tcl</>, or <literal>python</>.
1206 By preloading a shared library (and initializing it if
1207 applicable), the library startup time is avoided when the
1208 library is first used. However, the time to start each new
1209 server process may increase slightly, even if that process never
1210 uses the library. So this option is recommended only for
1211 libraries that will be used in most sessions.
1219 <sect3 id="runtime-config-resource-vacuum-cost">
1220 <title>Cost-Based Vacuum Delay</title>
1223 During the execution of <xref linkend="sql-vacuum"
1224 endterm="sql-vacuum-title"> and <xref linkend="sql-analyze"
1225 endterm="sql-analyze-title"> commands, the system maintains an
1226 internal counter that keeps track of the estimated cost of the
1227 various I/O operations that are performed. When the accumulated
1228 cost reaches a limit (specified by
1229 <varname>vacuum_cost_limit</varname>), the process performing
1230 the operation will sleep for a while (specified by
1231 <varname>vacuum_cost_delay</varname>). Then it will reset the
1232 counter and continue execution.
1236 The intent of this feature is to allow administrators to reduce
1237 the I/O impact of these commands on concurrent database
1238 activity. There are many situations in which it is not very
1239 important that maintenance commands like
1240 <command>VACUUM</command> and <command>ANALYZE</command> finish
1241 quickly; however, it is usually very important that these
1242 commands do not significantly interfere with the ability of the
1243 system to perform other database operations. Cost-based vacuum
1244 delay provides a way for administrators to achieve this.
1248 This feature is disabled by default. To enable it, set the
1249 <varname>vacuum_cost_delay</varname> variable to a nonzero
1254 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1255 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)</term>
1257 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1261 The length of time, in milliseconds, that the process will sleep
1262 when the cost limit has been exceeded.
1263 The default value is 0, which disables the cost-based vacuum
1264 delay feature. Positive values enable cost-based vacuuming.
1265 Note that on many systems, the effective resolution
1266 of sleep delays is 10 milliseconds; setting
1267 <varname>vacuum_cost_delay</varname> to a value that is
1268 not a multiple of 10 may have the same results as setting it
1269 to the next higher multiple of 10.
1274 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1275 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
1277 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1281 The estimated cost for vacuuming a buffer found in the shared buffer
1282 cache. It represents the cost to lock the buffer pool, lookup
1283 the shared hash table and scan the content of the page. The
1289 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1290 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
1292 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1296 The estimated cost for vacuuming a buffer that has to be read from
1297 disk. This represents the effort to lock the buffer pool,
1298 lookup the shared hash table, read the desired block in from
1299 the disk and scan its content. The default value is 10.
1304 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1305 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
1307 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1311 The estimated cost charged when vacuum modifies a block that was
1312 previously clean. It represents the extra I/O required to
1313 flush the dirty block out to disk again. The default value is
1319 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1320 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
1322 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1326 The accumulated cost that will cause the vacuuming process to sleep.
1327 The default value is 200.
1335 There are certain operations that hold critical locks and should
1336 therefore complete as quickly as possible. Cost-based vacuum
1337 delays do not occur during such operations. Therefore it is
1338 possible that the cost accumulates far higher than the specified
1339 limit. To avoid uselessly long delays in such cases, the actual
1340 delay is calculated as <varname>vacuum_cost_delay</varname> *
1341 <varname>accumulated_balance</varname> /
1342 <varname>vacuum_cost_limit</varname> with a maximum of
1343 <varname>vacuum_cost_delay</varname> * 4.
1349 <sect3 id="runtime-config-resource-background-writer">
1350 <title>Background Writer</title>
1353 Beginning in <productname>PostgreSQL</> 8.0, there is a separate server
1354 process called the <firstterm>background writer</>, whose sole function
1355 is to issue writes of <quote>dirty</> shared buffers. The intent is
1356 that server processes handling user queries should seldom or never have
1357 to wait for a write to occur, because the background writer will do it.
1358 This arrangement also reduces the performance penalty associated with
1359 checkpoints. The background writer will continuously trickle out dirty
1360 pages to disk, so that only a few pages will need to be forced out when
1361 checkpoint time arrives, instead of the storm of dirty-buffer writes that
1362 formerly occurred at each checkpoint. However there is a net overall
1363 increase in I/O load, because where a repeatedly-dirtied page might
1364 before have been written only once per checkpoint interval, the
1365 background writer might write it several times in the same interval.
1366 In most situations a continuous low load is preferable to periodic
1367 spikes, but the parameters discussed in this section can be used to tune
1368 the behavior for local needs.
1372 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1373 <term><varname>bgwriter_delay</varname> (<type>integer</type>)</term>
1375 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1379 Specifies the delay between activity rounds for the
1380 background writer. In each round the writer issues writes
1381 for some number of dirty buffers (controllable by the
1382 following parameters). The selected buffers will always be
1383 the least recently used ones among the currently dirty
1384 buffers. It then sleeps for <varname>bgwriter_delay</>
1385 milliseconds, and repeats. The default value is 200. Note
1386 that on many systems, the effective resolution of sleep
1387 delays is 10 milliseconds; setting <varname>bgwriter_delay</>
1388 to a value that is not a multiple of 10 may have the same
1389 results as setting it to the next higher multiple of 10.
1390 This option can only be set at server start or in the
1391 <filename>postgresql.conf</filename> file.
1396 <varlistentry id="guc-bgwriter-percent" xreflabel="bgwriter_percent">
1397 <term><varname>bgwriter_percent</varname> (<type>integer</type>)</term>
1399 <primary><varname>bgwriter_percent</> configuration parameter</primary>
1403 In each round, no more than this percentage of the currently
1404 dirty buffers will be written (rounding up any fraction to
1405 the next whole number of buffers). The default value is
1406 1. This option can only be set at server start or in the
1407 <filename>postgresql.conf</filename> file.
1412 <varlistentry id="guc-bgwriter-maxpages" xreflabel="bgwriter_maxpages">
1413 <term><varname>bgwriter_maxpages</varname> (<type>integer</type>)</term>
1415 <primary><varname>bgwriter_maxpages</> configuration parameter</primary>
1419 In each round, no more than this many dirty buffers will be
1420 written. The default value is 100. This option can only be
1421 set at server start or in the
1422 <filename>postgresql.conf</filename> file.
1429 Smaller values of <varname>bgwriter_percent</varname> and
1430 <varname>bgwriter_maxpages</varname> reduce the extra I/O load
1431 caused by the background writer, but leave more work to be done
1432 at checkpoint time. To reduce load spikes at checkpoints,
1433 increase the values. To disable background writing entirely,
1434 set <varname>bgwriter_percent</varname> and/or
1435 <varname>bgwriter_maxpages</varname> to zero.
1441 <sect2 id="runtime-config-wal">
1442 <title>Write Ahead Log</title>
1445 See also <xref linkend="wal-configuration"> for details on WAL
1449 <sect3 id="runtime-config-wal-settings">
1450 <title>Settings</title>
1453 <varlistentry id="guc-fsync" xreflabel="fsync">
1455 <primary><varname>fsync</> configuration parameter</primary>
1457 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1460 If this option is on, the <productname>PostgreSQL</> server
1461 will use the <function>fsync()</> system call in several places
1462 to make sure that updates are physically written to disk. This
1463 insures that a database cluster will recover to a
1464 consistent state after an operating system or hardware crash.
1468 However, using <function>fsync()</function> results in a
1469 performance penalty: when a transaction is committed,
1470 <productname>PostgreSQL</productname> must wait for the
1471 operating system to flush the write-ahead log to disk. When
1472 <varname>fsync</varname> is disabled, the operating system is
1473 allowed to do its best in buffering, ordering, and delaying
1474 writes. This can result in significantly improved performance.
1475 However, if the system crashes, the results of the last few
1476 committed transactions may be lost in part or whole. In the
1477 worst case, unrecoverable data corruption may occur.
1478 (Crashes of the database server itself are <emphasis>not</>
1479 a risk factor here. Only an operating-system-level crash
1480 creates a risk of corruption.)
1484 Due to the risks involved, there is no universally correct
1485 setting for <varname>fsync</varname>. Some administrators
1486 always disable <varname>fsync</varname>, while others only
1487 turn it off for bulk loads, where there is a clear restart
1488 point if something goes wrong, whereas some administrators
1489 always leave <varname>fsync</varname> enabled. The default is
1490 to enable <varname>fsync</varname>, for maximum reliability.
1491 If you trust your operating system, your hardware, and your
1492 utility company (or your battery backup), you can consider
1493 disabling <varname>fsync</varname>.
1497 This option can only be set at server start or in the
1498 <filename>postgresql.conf</filename> file.
1503 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1504 <term><varname>wal_sync_method</varname> (<type>string</type>)</term>
1506 <primary><varname>wal_sync_method</> configuration parameter</primary>
1510 Method used for forcing WAL updates out to disk. Possible
1512 <literal>fsync</> (call <function>fsync()</> at each commit),
1513 <literal>fdatasync</> (call <function>fdatasync()</> at each commit),
1514 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>), and
1515 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>).
1516 Not all of these choices are available on all platforms.
1517 If <varname>fsync</varname> is off then this setting is irrelevant.
1518 This option can only be set at server start or in the
1519 <filename>postgresql.conf</filename> file.
1524 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1525 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1527 <primary><varname>wal_buffers</> configuration parameter</primary>
1531 Number of disk-page buffers allocated in shared memory for WAL data.
1532 The default is 8. The setting need only be large enough to hold
1533 the amount of WAL data generated by one typical transaction.
1534 This option can only be set at server start.
1539 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
1540 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1542 <primary><varname>commit_delay</> configuration parameter</primary>
1546 Time delay between writing a commit record to the WAL buffer
1547 and flushing the buffer out to disk, in microseconds. A
1548 nonzero delay can allow multiple transactions to be committed
1549 with only one <function>fsync()</function> system call, if
1550 system load is high enough that additional transactions become
1551 ready to commit within the given interval. But the delay is
1552 just wasted if no other transactions become ready to
1553 commit. Therefore, the delay is only performed if at least
1554 <varname>commit_siblings</varname> other transactions are
1555 active at the instant that a server process has written its
1556 commit record. The default is zero (no delay).
1561 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
1562 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1564 <primary><varname>commit_siblings</> configuration parameter</primary>
1568 Minimum number of concurrent open transactions to require
1569 before performing the <varname>commit_delay</> delay. A larger
1570 value makes it more probable that at least one other
1571 transaction will become ready to commit during the delay
1572 interval. The default is five.
1579 <sect3 id="runtime-config-wal-checkpoints">
1580 <title>Checkpoints</title>
1583 <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1584 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1586 <primary><varname>checkpoint_segments</> configuration parameter</primary>
1590 Maximum distance between automatic WAL checkpoints, in log
1591 file segments (each segment is normally 16 megabytes). The
1592 default is three. This option can only be set at server start
1593 or in the <filename>postgresql.conf</filename> file.
1598 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1599 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1601 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
1605 Maximum time between automatic WAL checkpoints, in
1606 seconds. The default is 300 seconds. This option can only be
1607 set at server start or in the <filename>postgresql.conf</>
1613 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1614 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1616 <primary><varname>checkpoint_warning</> configuration parameter</primary>
1620 Write a message to the server log if checkpoints caused by
1621 the filling of checkpoint segment files happen closer together
1622 than this many seconds. The default is 30 seconds.
1623 Zero turns off the warning.
1630 <sect3 id="runtime-config-wal-archiving">
1631 <title>Archiving</title>
1634 <varlistentry id="guc-archive-command" xreflabel="archive_command">
1635 <term><varname>archive_command</varname> (<type>string</type>)</term>
1637 <primary><varname>archive_command</> configuration parameter</primary>
1641 The shell command to execute to archive a completed segment of
1642 the WAL file series. If this is an empty string (the default),
1643 WAL archiving is disabled. Any <literal>%p</> in the string is
1644 replaced by the absolute path of the file to archive, and any
1645 <literal>%f</> is replaced by the file name only. Use
1646 <literal>%%</> to embed an actual <literal>%</> character in the
1647 command. For more information see <xref
1648 linkend="backup-archiving-wal">. This option can only be set at
1649 server start or in the <filename>postgresql.conf</filename>
1653 It is important for the command to return a zero exit status if
1654 and only if it succeeds. Examples:
1656 archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
1657 archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
1667 <sect2 id="runtime-config-query">
1668 <title>Query Planning</title>
1670 <sect3 id="runtime-config-query-enable">
1671 <title>Planner Method Configuration</title>
1674 These configuration parameters provide a crude method of
1675 influencing the query plans chosen by the query optimizer. If
1676 the default plan chosen by the optimizer for a particular query
1677 is not optimal, a temporary solution may be found by using one
1678 of these configuration parameters to force the optimizer to
1679 choose a different plan. Turning one of these settings off
1680 permanently is seldom a good idea, however.
1681 Better ways to improve the quality of the
1682 plans chosen by the optimizer include adjusting the <xref
1683 linkend="runtime-config-query-constants"
1684 endterm="runtime-config-query-constants-title">, running <xref
1685 linkend="sql-analyze" endterm="sql-analyze-title"> more
1686 frequently, increasing the value of the <xref
1687 linkend="guc-default-statistics-target"> configuration parameter,
1688 and increasing the amount of statistics collected for
1689 specific columns using <command>ALTER TABLE SET
1690 STATISTICS</command>.
1694 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
1695 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
1697 <primary><varname>enable_hashagg</> configuration parameter</primary>
1701 Enables or disables the query planner's use of hashed
1702 aggregation plan types. The default is on.
1707 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
1708 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
1710 <primary><varname>enable_hashjoin</> configuration parameter</primary>
1714 Enables or disables the query planner's use of hash-join plan
1715 types. The default is on.
1720 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
1721 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
1723 <primary>index scan</primary>
1726 <primary><varname>enable_indexscan</> configuration parameter</primary>
1730 Enables or disables the query planner's use of index-scan plan
1731 types. The default is on.
1736 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
1737 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
1739 <primary><varname>enable_mergejoin</> configuration parameter</primary>
1743 Enables or disables the query planner's use of merge-join plan
1744 types. The default is on.
1749 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
1750 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
1752 <primary><varname>enable_nestloop</> configuration parameter</primary>
1756 Enables or disables the query planner's use of nested-loop join
1757 plans. It's not possible to suppress nested-loop joins entirely,
1758 but turning this variable off discourages the planner from using
1759 one if there are other methods available. The default is
1765 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
1766 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
1768 <primary>sequential scan</primary>
1771 <primary><varname>enable_seqscan</> configuration parameter</primary>
1775 Enables or disables the query planner's use of sequential scan
1776 plan types. It's not possible to suppress sequential scans
1777 entirely, but turning this variable off discourages the planner
1778 from using one if there are other methods available. The
1784 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
1785 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
1787 <primary><varname>enable_sort</> configuration parameter</primary>
1791 Enables or disables the query planner's use of explicit sort
1792 steps. It's not possible to suppress explicit sorts entirely,
1793 but turning this variable off discourages the planner from
1794 using one if there are other methods available. The default
1800 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
1801 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
1803 <primary><varname>enable_tidscan</> configuration parameter</primary>
1807 Enables or disables the query planner's use of <acronym>TID</>
1808 scan plan types. The default is on.
1815 <sect3 id="runtime-config-query-constants">
1816 <title id="runtime-config-query-constants-title">
1817 Planner Cost Constants
1822 Unfortunately, there is no well-defined method for determining
1823 ideal values for the family of <quote>cost</quote> variables that
1824 appear below. You are encouraged to experiment and share
1831 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
1832 <term><varname>effective_cache_size</varname> (<type>floating point</type>)</term>
1834 <primary><varname>effective_cache_size</> configuration parameter</primary>
1838 Sets the planner's assumption about the effective size of the
1839 disk cache that is available to a single index scan. This is
1840 factored into estimates of the cost of using an index; a higher
1841 value makes it more likely index scans will be used, a lower
1842 value makes it more likely sequential scans will be used. When
1843 setting this parameter you should consider both
1844 <productname>PostgreSQL</productname>'s shared buffers and the
1845 portion of the kernel's disk cache that will be used for
1846 <productname>PostgreSQL</productname> data files. Also, take into
1847 account the expected number of concurrent queries using different
1848 indexes, since they will have to share the available space.
1849 This parameter has no effect on the size of shared memory
1850 allocated by PostgreSQL, nor does it reserve kernel disk cache;
1851 it is used only for estimation purposes.
1852 The value is measured in disk pages, which are
1853 normally 8192 bytes each. The default is 1000.
1858 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
1859 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
1861 <primary><varname>random_page_cost</> configuration parameter</primary>
1865 Sets the planner's estimate of the cost of a
1866 nonsequentially fetched disk page. This is measured as a
1867 multiple of the cost of a sequential page fetch. A higher
1868 value makes it more likely a sequential scan will be used, a
1869 lower value makes it more likely an index scan will be
1870 used. The default is four.
1875 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
1876 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
1878 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
1882 Sets the planner's estimate of the cost of processing
1883 each row during a query. This is measured as a fraction of
1884 the cost of a sequential page fetch. The default is 0.01.
1889 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
1890 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
1892 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
1896 Sets the planner's estimate of the cost of processing
1897 each index row during an index scan. This is measured as a
1898 fraction of the cost of a sequential page fetch. The default
1904 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
1905 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
1907 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
1911 Sets the planner's estimate of the cost of processing each
1912 operator in a <literal>WHERE</> clause. This is measured as a fraction of
1913 the cost of a sequential page fetch. The default is 0.0025.
1921 <sect3 id="runtime-config-query-geqo">
1922 <title>Genetic Query Optimizer</title>
1926 <varlistentry id="guc-geqo" xreflabel="geqo">
1928 <primary>genetic query optimization</primary>
1931 <primary>GEQO</primary>
1932 <see>genetic query optimization</see>
1935 <primary><varname>geqo</> configuration parameter</primary>
1937 <term><varname>geqo</varname> (<type>boolean</type>)</term>
1940 Enables or disables genetic query optimization, which is an
1941 algorithm that attempts to do query planning without
1942 exhaustive searching. This is on by default. The
1943 <varname>geqo_threshold</varname> variable provides a more
1944 granular way to disable GEQO for certain classes of queries.
1949 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
1950 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
1952 <primary><varname>geqo_threshold</> configuration parameter</primary>
1956 Use genetic query optimization to plan queries with at least
1957 this many <literal>FROM</> items involved. (Note that an outer
1958 <literal>JOIN</> construct counts as only one <literal>FROM</>
1959 item.) The default is 12. For simpler queries it is usually best
1960 to use the deterministic, exhaustive planner, but for queries with
1961 many tables the deterministic planner takes too long.
1966 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
1967 <term><varname>geqo_effort</varname>
1968 (<type>integer</type>)</term>
1970 <primary><varname>geqo_effort</> configuration parameter</primary>
1974 Controls the trade off between planning time and query plan
1975 efficiency in GEQO. This variable must be an integer in the
1976 range from 1 to 10. The default value is 5. Larger values
1977 increase the time spent doing query planning, but also
1978 increase the likelihood that an efficient query plan will be
1983 <varname>geqo_effort</varname> doesn't actually do anything
1984 directly; it is only used to compute the default values for
1985 the other variables that influence GEQO behavior (described
1986 below). If you prefer, you can set the other parameters by
1992 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
1993 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
1995 <primary><varname>geqo_pool_size</> configuration parameter</primary>
1999 Controls the pool size used by GEQO. The pool size is the
2000 number of individuals in the genetic population. It must be
2001 at least two, and useful values are typically 100 to 1000. If
2002 it is set to zero (the default setting) then a suitable
2003 default is chosen based on <varname>geqo_effort</varname> and
2004 the number of tables in the query.
2009 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
2010 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
2012 <primary><varname>geqo_generations</> configuration parameter</primary>
2016 Controls the number of generations used by GEQO. Generations
2017 specifies the number of iterations of the algorithm. It must
2018 be at least one, and useful values are in the same range as
2019 the pool size. If it is set to zero (the default setting)
2020 then a suitable default is chosen based on
2021 <varname>geqo_pool_size</varname>.
2026 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
2027 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
2029 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
2033 Controls the selection bias used by GEQO. The selection bias
2034 is the selective pressure within the population. Values can be
2035 from 1.50 to 2.00; the latter is the default.
2042 <sect3 id="runtime-config-query-other">
2043 <title>Other Planner Options</title>
2047 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
2048 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
2050 <primary><varname>default_statistics_target</> configuration parameter</primary>
2054 Sets the default statistics target for table columns that have
2055 not had a column-specific target set via <command>ALTER TABLE
2056 SET STATISTICS</>. Larger values increase the time needed to
2057 do <command>ANALYZE</>, but may improve the quality of the
2058 planner's estimates. The default is 10. For more information
2059 on the use of statistics by the <productname>PostgreSQL</>
2060 query planner, refer to <xref linkend="planner-stats">.
2065 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
2066 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
2068 <primary><varname>from_collapse_limit</> configuration parameter</primary>
2072 The planner will merge sub-queries into upper queries if the
2073 resulting <literal>FROM</literal> list would have no more than
2074 this many items. Smaller values reduce planning time but may
2075 yield inferior query plans. The default is 8. It is usually
2076 wise to keep this less than <xref linkend="guc-geqo-threshold">.
2081 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
2082 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
2084 <primary><varname>join_collapse_limit</> configuration parameter</primary>
2088 The planner will rewrite explicit inner <literal>JOIN</>
2089 constructs into lists of <literal>FROM</> items whenever a
2090 list of no more than this many items in total would
2091 result. Prior to <productname>PostgreSQL</> 7.4, joins
2092 specified via the <literal>JOIN</literal> construct would
2093 never be reordered by the query planner. The query planner has
2094 subsequently been improved so that inner joins written in this
2095 form can be reordered; this configuration parameter controls
2096 the extent to which this reordering is performed.
2099 At present, the order of outer joins specified via the
2100 <literal>JOIN</> construct is never adjusted by the query
2101 planner; therefore, <varname>join_collapse_limit</> has no
2102 effect on this behavior. The planner may be improved to
2103 reorder some classes of outer joins in a future release of
2104 <productname>PostgreSQL</productname>.
2110 By default, this variable is set the same as
2111 <varname>from_collapse_limit</varname>, which is appropriate
2112 for most uses. Setting it to 1 prevents any reordering of
2113 inner <literal>JOIN</>s. Thus, the explicit join order
2114 specified in the query will be the actual order in which the
2115 relations are joined. The query planner does not always choose
2116 the optimal join order; advanced users may elect to
2117 temporarily set this variable to 1, and then specify the join
2118 order they desire explicitly. Another consequence of setting
2119 this variable to 1 is that the query planner will behave more
2120 like the <productname>PostgreSQL</productname> 7.3 query
2121 planner, which some users might find useful for backward
2122 compatibility reasons.
2126 Setting this variable to a value between 1 and
2127 <varname>from_collapse_limit</varname> might be useful to
2128 trade off planning time against the quality of the chosen plan
2129 (higher values produce better plans).
2138 <sect2 id="runtime-config-logging">
2139 <title>Error Reporting and Logging</title>
2141 <indexterm zone="runtime-config-logging">
2142 <primary>server log</primary>
2145 <sect3 id="runtime-config-logging-where">
2146 <title>Where to log</title>
2148 <indexterm zone="runtime-config-logging-where">
2149 <primary>where to log</primary>
2154 <varlistentry id="guc-log-destination" xreflabel="log_destination">
2155 <term><varname>log_destination</varname> (<type>string</type>)</term>
2157 <primary><varname>log_destination</> configuration parameter</primary>
2161 <productname>PostgreSQL</productname> supports several methods
2162 for logging server messages, including
2163 <systemitem>stderr</systemitem> and
2164 <systemitem>syslog</systemitem>. On Windows,
2165 <systemitem>eventlog</systemitem> is also supported. Set this
2166 option to a list of desired log destinations separated by
2167 commas. The default is to log to <systemitem>stderr</systemitem>
2169 This option can only be set at server start or in the
2170 <filename>postgresql.conf</filename> configuration file.
2175 <varlistentry id="guc-redirect-stderr" xreflabel="redirect_stderr">
2176 <term><varname>redirect_stderr</varname> (<type>boolean</type>)</term>
2178 <primary><varname>redirect_stderr</> configuration parameter</primary>
2182 This option allows messages sent to <application>stderr</> to be
2183 captured and redirected into log files.
2184 This option, in combination with logging to <application>stderr</>,
2185 is often more useful than
2186 logging to <application>syslog</>, since some types of messages
2187 may not appear in <application>syslog</> output (a common example
2188 is dynamic-linker failure messages).
2189 This option can only be set at server start.
2194 <varlistentry id="guc-log-directory" xreflabel="log_directory">
2195 <term><varname>log_directory</varname> (<type>string</type>)</term>
2197 <primary><varname>log_directory</> configuration parameter</primary>
2201 When <varname>redirect_stderr</> is enabled, this option
2202 determines the directory in which log files will be created.
2203 It may be specified as an absolute path, or relative to the
2204 cluster data directory.
2205 This option can only be set at server start or in the
2206 <filename>postgresql.conf</filename> configuration file.
2211 <varlistentry id="guc-log-filename" xreflabel="log_filename">
2212 <term><varname>log_filename</varname> (<type>string</type>)</term>
2214 <primary><varname>log_filename</> configuration parameter</primary>
2218 When <varname>redirect_stderr</varname> is enabled, this option
2219 sets the file names of the created log files. The value
2220 is treated as a <systemitem>strftime</systemitem> pattern,
2221 so <literal>%</literal>-escapes
2222 can be used to specify time-varying file names.
2223 If no <literal>%</literal>-escapes are present,
2224 <productname>PostgreSQL</productname> will
2225 append the epoch of the new log file's open time. For example,
2226 if <varname>log_filename</varname> were <literal>server_log</literal>, then the
2227 chosen file name would be <literal>server_log.1093827753</literal>
2228 for a log starting at Sun Aug 29 19:02:33 2004 MST.
2229 This option can only be set at server start or in the
2230 <filename>postgresql.conf</filename> configuration file.
2235 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
2236 <term><varname>log_rotation_age</varname> (<type>integer</type>)</term>
2238 <primary><varname>log_rotation_age</> configuration parameter</primary>
2242 When <varname>redirect_stderr</varname> is enabled, this option
2243 determines the maximum lifetime of an individual log file.
2244 After this many minutes have elapsed, a new log file will
2245 be created. Set to zero to disable time-based creation of
2247 This option can only be set at server start or in the
2248 <filename>postgresql.conf</filename> configuration file.
2253 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
2254 <term><varname>log_rotation_size</varname> (<type>integer</type>)</term>
2256 <primary><varname>log_rotation_size</> configuration parameter</primary>
2260 When <varname>redirect_stderr</varname> is enabled, this option
2261 determines the maximum size of an individual log file.
2262 After this many kilobytes have been emitted into a log file,
2263 a new log file will be created. Set to zero to disable size-based
2264 creation of new log files.
2265 This option can only be set at server start or in the
2266 <filename>postgresql.conf</filename> configuration file.
2271 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
2272 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)</term>
2274 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
2278 When <varname>redirect_stderr</varname> is enabled, this option will cause
2279 <productname>PostgreSQL</productname> to truncate (overwrite),
2280 rather than append to, any existing log file of the same name.
2281 However, truncation will occur only when a new file is being opened
2282 due to time-based rotation, not during server startup or size-based
2283 rotation. When false, pre-existing files will be appended to in
2284 all cases. For example, using this option in combination with
2285 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
2286 would result in generating twenty-four hourly log files and then
2287 cyclically overwriting them.
2288 This option can only be set at server start or in the
2289 <filename>postgresql.conf</filename> configuration file.
2292 Example: To keep 7 days of logs, one log file per day named
2293 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
2294 etc, and automatically overwrite last week's log with this week's log,
2295 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
2296 <varname>log_truncate_on_rotation</varname> to <literal>true</literal>, and
2297 <varname>log_rotation_age</varname> to <literal>1440</literal>.
2300 Example: To keep 24 hours of logs, one log file per hour, but
2301 also rotate sooner if the log file size exceeds 1GB, set
2302 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
2303 <varname>log_truncate_on_rotation</varname> to <literal>true</literal>,
2304 <varname>log_rotation_age</varname> to <literal>60</literal>, and
2305 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
2306 Including <literal>%M</> in <varname>log_filename</varname> allows
2307 any size-driven rotations that may occur to select a filename
2308 different from the hour's initial filename.
2313 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
2314 <term><varname>syslog_facility</varname> (<type>string</type>)</term>
2316 <primary><varname>syslog_facility</> configuration parameter</primary>
2320 When logging to <application>syslog</> is enabled, this option
2321 determines the <application>syslog</application>
2322 <quote>facility</quote> to be used. You may choose
2323 from <literal>LOCAL0</>, <literal>LOCAL1</>,
2324 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
2325 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
2326 the default is <literal>LOCAL0</>. See also the
2327 documentation of your system's
2328 <application>syslog</application> daemon.
2329 This option can only be set at server start.
2334 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
2335 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
2337 <primary><varname>syslog_identity</> configuration parameter</primary>
2341 When logging to <application>syslog</> is enabled, this option
2342 determines the program name used to identify
2343 <productname>PostgreSQL</productname> messages in
2344 <application>syslog</application> logs. The default is
2345 <literal>postgres</literal>.
2346 This option can only be set at server start.
2353 <sect3 id="runtime-config-logging-when">
2354 <title>When To Log</title>
2358 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
2359 <term><varname>client_min_messages</varname> (<type>string</type>)</term>
2361 <primary><varname>client_min_messages</> configuration parameter</primary>
2365 Controls which message levels are sent to the client.
2366 Valid values are <literal>DEBUG5</>,
2367 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
2368 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
2369 <literal>WARNING</>, and <literal>ERROR</>. Each level
2370 includes all the levels that follow it. The later the level,
2371 the fewer messages are sent. The default is
2372 <literal>NOTICE</>. Note that <literal>LOG</> has a different
2373 rank here than in <varname>log_min_messages</>.
2378 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
2379 <term><varname>log_min_messages</varname> (<type>string</type>)</term>
2381 <primary><varname>log_min_messages</> configuration parameter</primary>
2385 Controls which message levels are written to the server log.
2386 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
2387 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
2388 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
2389 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
2390 <literal>PANIC</>. Each level includes all the levels that
2391 follow it. The later the level, the fewer messages are sent
2392 to the log. The default is <literal>NOTICE</>. Note that
2393 <literal>LOG</> has a different rank here than in
2394 <varname>client_min_messages</>.
2395 Only superusers can change this setting.
2400 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
2401 <term><varname>log_error_verbosity</varname> (<type>string</type>)</term>
2403 <primary><varname>log_error_verbosity</> configuration parameter</primary>
2407 Controls the amount of detail written in the server log for each
2408 message that is logged. Valid values are <literal>TERSE</>,
2409 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
2410 fields to displayed messages.
2411 Only superusers can change this setting.
2416 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
2417 <term><varname>log_min_error_statement</varname> (<type>string</type>)</term>
2419 <primary><varname>log_min_error_statement</> configuration parameter</primary>
2423 Controls whether or not the SQL statement that causes an error
2424 condition will also be recorded in the server log. All SQL
2425 statements that cause an error of the specified level or
2426 higher are logged. The default is
2427 <literal>PANIC</literal> (effectively turning this feature
2428 off for normal use). Valid values are <literal>DEBUG5</literal>,
2429 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
2430 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
2431 <literal>INFO</literal>, <literal>NOTICE</literal>,
2432 <literal>WARNING</literal>, <literal>ERROR</literal>,
2433 <literal>FATAL</literal>, and <literal>PANIC</literal>. For
2434 example, if you set this to <literal>ERROR</literal> then all
2435 SQL statements causing errors, fatal errors, or panics will be
2436 logged. Enabling this option can be helpful in tracking down
2437 the source of any errors that appear in the server log.
2438 Only superusers can change this setting.
2443 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
2444 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
2446 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
2450 Sets a minimum statement execution time (in milliseconds)
2451 that causes a statement to be logged. All SQL statements
2452 that run for the time specified or longer will be logged with
2453 their duration. Setting this to zero will print
2454 all queries and their durations. Minus-one (the default)
2455 disables the feature. For example, if you set it to
2456 <literal>250</literal> then all SQL statements that run 250ms
2457 or longer will be logged. Enabling this option can be
2458 useful in tracking down unoptimized queries in your applications.
2459 Only superusers can change this setting.
2464 <varlistentry id="guc-silent-mode" xreflabel="silent_mode">
2465 <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
2467 <primary><varname>silent_mode</> configuration parameter</primary>
2471 Runs the server silently. If this option is set, the server
2472 will automatically run in background and any controlling
2473 terminals are disassociated (same effect as
2474 <command>postmaster</>'s <option>-S</option> option).
2475 The server's standard output and standard error are redirected
2476 to <literal>/dev/null</>, so any messages sent to them will be lost.
2477 Unless <application>syslog</> logging is selected or
2478 <varname>redirect_stderr</> is enabled, using this option
2479 is discouraged because it makes it impossible to see error messages.
2487 Here is a list of the various message severity levels used in
2491 <term><literal>DEBUG[1-5]</literal></term>
2494 Provides information for use by developers.
2500 <term><literal>INFO</literal></term>
2503 Provides information implicitly requested by the user,
2504 e.g., during <command>VACUUM VERBOSE</>.
2510 <term><literal>NOTICE</literal></term>
2513 Provides information that may be helpful to users, e.g.,
2514 truncation of long identifiers and the creation of indexes as part
2521 <term><literal>WARNING</literal></term>
2524 Provides warnings to the user, e.g., <command>COMMIT</>
2525 outside a transaction block.
2531 <term><literal>ERROR</literal></term>
2534 Reports an error that caused the current command to abort.
2540 <term><literal>LOG</literal></term>
2543 Reports information of interest to administrators, e.g.,
2544 checkpoint activity.
2550 <term><literal>FATAL</literal></term>
2553 Reports an error that caused the current session to abort.
2559 <term><literal>PANIC</literal></term>
2562 Reports an error that caused all sessions to abort.
2570 <sect3 id="runtime-config-logging-what">
2571 <title>What To Log</title>
2576 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
2577 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
2578 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
2579 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
2581 <primary><varname>debug_print_parse</> configuration parameter</primary>
2584 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
2587 <primary><varname>debug_print_plan</> configuration parameter</primary>
2590 <primary><varname>debug_pretty_print</> configuration parameter</primary>
2594 These options enable various debugging output to be emitted.
2595 For each executed query, they print
2596 the resulting parse tree, the query rewriter output, or the
2597 execution plan. <varname>debug_pretty_print</varname> indents
2598 these displays to produce a more readable but much longer
2599 output format. <varname>client_min_messages</varname> or
2600 <varname>log_min_messages</varname> must be
2601 <literal>DEBUG1</literal> or lower to actually send this output
2602 to the client or the server log, respectively.
2603 These options are off by default.
2608 <varlistentry id="guc-log-connections" xreflabel="log_connections">
2609 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
2611 <primary><varname>log_connections</> configuration parameter</primary>
2615 This outputs a line to the server log detailing each successful
2616 connection. This is off by default, although it is probably very
2617 useful. This option can only be set at server start or in the
2618 <filename>postgresql.conf</filename> configuration file.
2623 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
2624 <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
2626 <primary><varname>log_disconnections</> configuration parameter</primary>
2630 This outputs a line in the server log similar to
2631 <varname>log_connections</varname> but at session termination,
2632 and includes the duration of the session. This is off by
2633 default. This option can only be set at server start or in the
2634 <filename>postgresql.conf</filename> configuration file.
2640 <varlistentry id="guc-log-duration" xreflabel="log_duration">
2641 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
2643 <primary><varname>log_duration</> configuration parameter</primary>
2647 Causes the duration of every completed statement which satisfies
2648 <varname>log_statement</> to be logged. When using this option,
2649 if you are not using <application>syslog</>, it is recommended
2650 that you log the PID or session ID using <varname>log_line_prefix</>
2651 so that you can link the statement to the
2652 duration using the process ID or session ID. The default is off.
2653 Only superusers can change this setting.
2658 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
2659 <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
2661 <primary><varname>log_line_prefix</> configuration parameter</primary>
2665 This is a <function>printf</>-style string that is output at the
2666 beginning of each log line. The default is an empty string.
2667 Each recognized escape is replaced as outlined
2668 below - anything else that looks like an escape is ignored. Other
2669 characters are copied straight to the log line. Some escapes are
2670 only recognised by session processes, and do not apply to
2671 background processes such as the postmaster. <application>Syslog</>
2673 time stamp and process ID information, so you probably do not want to
2674 use those escapes if you are using <application>syslog</>.
2675 This option can only be set at server start or in the
2676 <filename>postgresql.conf</filename> configuration file.
2682 <entry>Escape</entry>
2683 <entry>Effect</entry>
2684 <entry>Session only</entry>
2689 <entry><literal>%u</literal></entry>
2690 <entry>User name</entry>
2694 <entry><literal>%d</literal></entry>
2695 <entry>Database name</entry>
2699 <entry><literal>%r</literal></entry>
2700 <entry>Remote host name or IP address, and remote port</entry>
2704 <entry><literal>%p</literal></entry>
2705 <entry>Process ID</entry>
2709 <entry><literal>%t</literal></entry>
2710 <entry>Time stamp</entry>
2714 <entry><literal>%i</literal></entry>
2715 <entry>Command tag: This is the command that generated the log line.</entry>
2719 <entry><literal>%c</literal></entry>
2720 <entry>Session ID: A unique identifier for each session.
2721 It is 2 4-byte hexadecimal numbers (without leading zeros)
2722 separated by a dot. The numbers
2723 are the session start time and the process ID, so this can also
2724 be used as a space saving way of printing these items.</entry>
2728 <entry><literal>%l</literal></entry>
2729 <entry>Number of the log line for each process, starting at 1</entry>
2733 <entry><literal>%s</literal></entry>
2734 <entry>Session start time stamp</entry>
2738 <entry><literal>%x</literal></entry>
2739 <entry>Transaction ID</entry>
2743 <entry><literal>%q</literal></entry>
2744 <entry>Does not produce any output, but tells non-session
2745 processes to stop at this point in the string. Ignored by
2746 session processes.</entry>
2750 <entry><literal>%%</literal></entry>
2751 <entry>Literal <literal>%</></entry>
2761 <varlistentry id="guc-log-statement" xreflabel="log_statement">
2762 <term><varname>log_statement</varname> (<type>string</type>)</term>
2764 <primary><varname>log_statement</> configuration parameter</primary>
2768 Controls which SQL statements are logged. Valid values are
2769 <literal>none</>, <literal>ddl</>, <literal>mod</>, and
2770 <literal>all</>. <literal>ddl</> logs all data definition
2771 commands like <literal>CREATE</>, <literal>ALTER</>, and
2772 <literal>DROP</> commands. <literal>mod</> logs all
2773 <literal>ddl</> statements, plus <literal>INSERT</>,
2774 <literal>UPDATE</>, <literal>DELETE</>, <literal>TRUNCATE</>,
2775 and <literal>COPY FROM</>. <literal>PREPARE</> and
2776 <literal>EXPLAIN ANALYZE</> statements are also logged if their
2777 contained command is of an appropriate type.
2780 The default is <literal>none</>. Only superusers can change this
2786 The <command>EXECUTE</command> statement is not considered a
2787 <literal>ddl</> or <literal>mod</> statement. When it is logged,
2788 only the name of the prepared statement is reported, not the
2789 actual prepared statement.
2793 When a function is defined in the
2794 <application>PL/pgSQL</application>server-side language, any queries
2795 executed by the function will only be logged the first time that the
2796 function is invoked in a particular session. This is because
2797 <application>PL/pgSQL</application> keeps a cache of the
2798 query plans produced for the SQL statements in the function.
2804 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
2805 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
2807 <primary><varname>log_hostname</> configuration parameter</primary>
2811 By default, connection log messages only show the IP address of the
2812 connecting host. Turning on this option causes logging of the
2813 host name as well. Note that depending on your host name resolution
2814 setup this might impose a non-negligible performance penalty. This
2815 option can only be set at server start or in the
2816 <filename>postgresql.conf</filename> file.
2825 <sect2 id="runtime-config-statistics">
2826 <title>Runtime Statistics</title>
2828 <sect3 id="runtime-config-statistics-monitor">
2829 <title>Statistics Monitoring</title>
2833 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
2834 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
2835 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
2836 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
2838 <primary><varname>log_statement_stats</> configuration parameter</primary>
2841 <primary><varname>log_parser_stats</> configuration parameter</primary>
2844 <primary><varname>log_planner_stats</> configuration parameter</primary>
2847 <primary><varname>log_executor_stats</> configuration parameter</primary>
2851 For each query, write performance statistics of the respective
2852 module to the server log. This is a crude profiling
2853 instrument. <varname>log_statement_stats</varname> reports total
2854 statement statistics, while the others report per-module statistics.
2855 <varname>log_statement_stats</varname> cannot be enabled together with
2856 any of the per-module options. All of these options are disabled by
2857 default. Only superusers can change these settings.
2865 <sect3 id="runtime-config-statistics-collector">
2866 <title>Query and Index Statistics Collector</title>
2869 <varlistentry id="guc-stats-start-collector" xreflabel="stats_start_collector">
2870 <term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>
2872 <primary><varname>stats_start_collector</> configuration parameter</primary>
2876 Controls whether the server should start the
2877 statistics-collection subprocess. This is on by default, but
2878 may be turned off if you know you have no interest in
2879 collecting statistics. This option can only be set at server
2885 <varlistentry id="guc-stats-command-string" xreflabel="stats_command_string">
2886 <term><varname>stats_command_string</varname> (<type>boolean</type>)</term>
2888 <primary><varname>stats_command_string</> configuration parameter</primary>
2892 Enables the collection of statistics on the currently
2893 executing command of each session, along with the time at
2894 which that command began execution. This option is off by
2895 default. Note that even when enabled, this information is not
2896 visible to all users, only to superusers and the user owning
2897 the session being reported on; so it should not represent a
2898 security risk. This data can be accessed via the
2899 <structname>pg_stat_activity</structname> system view; refer
2900 to <xref linkend="monitoring"> for more information.
2905 <varlistentry id="guc-stats-block-level" xreflabel="stats_block_level">
2906 <term><varname>stats_block_level</varname> (<type>boolean</type>)</term>
2908 <primary><varname>stats_block_level</> configuration parameter</primary>
2912 Enables the collection of block-level statistics on database
2913 activity. This option is disabled by default. If this option
2914 is enabled, the data that is produced can be accessed via the
2915 <structname>pg_stat</structname> and
2916 <structname>pg_statio</structname> family of system views;
2917 refer to <xref linkend="monitoring"> for more information.
2922 <varlistentry id="guc-stats-row-level" xreflabel="stats_row_level">
2923 <term><varname>stats_row_level</varname> (<type>boolean</type>)</term>
2925 <primary><varname>stats_row_level</> configuration parameter</primary>
2929 Enables the collection of row-level statistics on database
2930 activity. This option is disabled by default. If this option
2931 is enabled, the data that is produced can be accessed via the
2932 <structname>pg_stat</structname> and
2933 <structname>pg_statio</structname> family of system views;
2934 refer to <xref linkend="monitoring"> for more information.
2939 <varlistentry id="guc-stats-reset-on-server-start" xreflabel="stats_reset_on_server_start">
2940 <term><varname>stats_reset_on_server_start</varname> (<type>boolean</type>)</term>
2942 <primary><varname>stats_reset_on_server_start</> configuration parameter</primary>
2946 If on, collected statistics are zeroed out whenever the server
2947 is restarted. If off, statistics are accumulated across server
2948 restarts. The default is on. This option can only be set at
2958 <sect2 id="runtime-config-client">
2959 <title>Client Connection Defaults</title>
2961 <sect3 id="runtime-config-client-statement">
2962 <title>Statement Behavior</title>
2965 <varlistentry id="guc-search-path" xreflabel="search_path">
2966 <term><varname>search_path</varname> (<type>string</type>)</term>
2968 <primary><varname>search_path</> configuration parameter</primary>
2970 <indexterm><primary>path</><secondary>for schemas</></>
2973 This variable specifies the order in which schemas are searched
2974 when an object (table, data type, function, etc.) is referenced by a
2975 simple name with no schema component. When there are objects of
2976 identical names in different schemas, the one found first
2977 in the search path is used. An object that is not in any of the
2978 schemas in the search path can only be referenced by specifying
2979 its containing schema with a qualified (dotted) name.
2983 The value for <varname>search_path</varname> has to be a comma-separated
2984 list of schema names. If one of the list items is
2985 the special value <literal>$user</literal>, then the schema
2986 having the name returned by <function>SESSION_USER</> is substituted, if there
2987 is such a schema. (If not, <literal>$user</literal> is ignored.)
2991 The system catalog schema, <literal>pg_catalog</>, is always
2992 searched, whether it is mentioned in the path or not. If it is
2993 mentioned in the path then it will be searched in the specified
2994 order. If <literal>pg_catalog</> is not in the path then it will
2995 be searched <emphasis>before</> searching any of the path items.
2996 It should also be noted that the temporary-table schema,
2997 <literal>pg_temp_<replaceable>nnn</></>, is implicitly searched before any of
3002 When objects are created without specifying a particular target
3003 schema, they will be placed in the first schema listed
3004 in the search path. An error is reported if the search path is
3009 The default value for this parameter is
3010 <literal>'$user, public'</literal> (where the second part will be
3011 ignored if there is no schema named <literal>public</>).
3012 This supports shared use of a database (where no users
3013 have private schemas, and all share use of <literal>public</>),
3014 private per-user schemas, and combinations of these. Other
3015 effects can be obtained by altering the default search path
3016 setting, either globally or per-user.
3020 The current effective value of the search path can be examined
3021 via the <acronym>SQL</acronym> function
3022 <function>current_schemas()</>. This is not quite the same as
3023 examining the value of <varname>search_path</varname>, since
3024 <function>current_schemas()</> shows how the requests
3025 appearing in <varname>search_path</varname> were resolved.
3029 For more information on schema handling, see <xref linkend="ddl-schemas">.
3034 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
3035 <term><varname>default_tablespace</varname> (<type>string</type>)</term>
3037 <primary><varname>default_tablespace</> configuration parameter</primary>
3039 <indexterm><primary>tablespace</><secondary>default</></>
3042 This variable specifies the default tablespace in which to create
3043 objects (tables and indexes) when a <command>CREATE</> command does
3044 not explicitly specify a tablespace.
3048 The value is either the name of a tablespace, or an empty string
3049 to specify using the default tablespace of the current database.
3050 If the value does not match the name of any existing tablespace,
3051 <productname>PostgreSQL</> will automatically use the default
3052 tablespace of the current database.
3056 For more information on tablespaces,
3057 see <xref linkend="manage-ag-tablespaces">.
3062 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
3063 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
3065 <primary><varname>check_function_bodies</> configuration parameter</primary>
3069 This parameter is normally true. When set to false, it disables
3070 validation of the function body string during <xref
3071 linkend="sql-createfunction"
3072 endterm="sql-createfunction-title">. Disabling validation is
3073 occasionally useful to avoid problems such as forward
3074 references when restoring function definitions from a dump.
3079 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
3081 <primary>transaction isolation level</primary>
3084 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
3086 <term><varname>default_transaction_isolation</varname> (<type>string</type>)</term>
3089 Each SQL transaction has an isolation level, which can be
3090 either <quote>read uncommitted</quote>, <quote>read
3091 committed</quote>, <quote>repeatable read</quote>, or
3092 <quote>serializable</quote>. This parameter controls the
3093 default isolation level of each new transaction. The default
3094 is <quote>read committed</quote>.
3098 Consult <xref linkend="mvcc"> and <xref linkend="sql-set-transaction"> for more
3104 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
3106 <primary>read-only transaction</primary>
3109 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
3112 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
3115 A read-only SQL transaction cannot alter non-temporary tables.
3116 This parameter controls the default read-only status of each new
3117 transaction. The default is false (read/write).
3121 Consult <xref linkend="sql-set-transaction"> for more information.
3126 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
3127 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
3129 <primary><varname>statement_timeout</> configuration parameter</primary>
3133 Abort any statement that takes over the specified number of
3134 milliseconds. A value of zero (the default) turns off the limitation.
3141 <sect3 id="runtime-config-client-format">
3142 <title>Locale and Formatting</title>
3146 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
3147 <term><varname>DateStyle</varname> (<type>string</type>)</term>
3149 <primary><varname>DateStyle</> configuration parameter</primary>
3153 Sets the display format for date and time values, as well as the
3154 rules for interpreting ambiguous date input values. For
3155 historical reasons, this variable contains two independent
3156 components: the output format specification (<literal>ISO</>,
3157 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
3158 and the input/output specification for year/month/day ordering
3159 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
3160 can be set separately or together. The keywords <literal>Euro</>
3161 and <literal>European</> are synonyms for <literal>DMY</>; the
3162 keywords <literal>US</>, <literal>NonEuro</>, and
3163 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
3164 <xref linkend="datatype-datetime"> for more information. The
3165 default is <literal>ISO, MDY</>.
3170 <varlistentry id="guc-timezone" xreflabel="timezone">
3171 <term><varname>timezone</varname> (<type>string</type>)</term>
3173 <primary><varname>timezone</> configuration parameter</primary>
3175 <indexterm><primary>time zone</></>
3178 Sets the time zone for displaying and interpreting time
3179 stamps. The default is 'unknown', which means to use whatever
3180 the system environment specifies as the time zone. See <xref
3181 linkend="datatype-datetime"> for more information.
3186 <varlistentry id="guc-australian-timezones" xreflabel="australian_timezones">
3187 <term><varname>australian_timezones</varname> (<type>boolean</type>)</term>
3189 <primary><varname>australian_timezones</> configuration parameter</primary>
3191 <indexterm><primary>time zone</><secondary>Australian</></>
3194 If set to true, <literal>ACST</literal>,
3195 <literal>CST</literal>, <literal>EST</literal>, and
3196 <literal>SAT</literal> are interpreted as Australian time
3197 zones rather than as North/South American time zones and
3198 Saturday. The default is false.
3203 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
3205 <primary>significant digits</primary>
3208 <primary>floating-point</primary>
3209 <secondary>display</secondary>
3212 <primary><varname>extra_float_digits</> configuration parameter</primary>
3215 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
3218 This parameter adjusts the number of digits displayed for
3219 floating-point values, including <type>float4</>, <type>float8</>,
3220 and geometric data types. The parameter value is added to the
3221 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
3222 as appropriate). The value can be set as high as 2, to include
3223 partially-significant digits; this is especially useful for dumping
3224 float data that needs to be restored exactly. Or it can be set
3225 negative to suppress unwanted digits.
3230 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
3231 <term><varname>client_encoding</varname> (<type>string</type>)</term>
3233 <primary><varname>client_encoding</> configuration parameter</primary>
3235 <indexterm><primary>character set</></>
3238 Sets the client-side encoding (character set).
3239 The default is to use the database encoding.
3244 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
3245 <term><varname>lc_messages</varname> (<type>string</type>)</term>
3247 <primary><varname>lc_messages</> configuration parameter</primary>
3251 Sets the language in which messages are displayed. Acceptable
3252 values are system-dependent; see <xref linkend="locale"> for
3253 more information. If this variable is set to the empty string
3254 (which is the default) then the value is inherited from the
3255 execution environment of the server in a system-dependent way.
3259 On some systems, this locale category does not exist. Setting
3260 this variable will still work, but there will be no effect.
3261 Also, there is a chance that no translated messages for the
3262 desired language exist. In that case you will continue to see
3263 the English messages.
3268 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
3269 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
3271 <primary><varname>lc_monetary</> configuration parameter</primary>
3275 Sets the locale to use for formatting monetary amounts, for
3276 example with the <function>to_char</function> family of
3277 functions. Acceptable values are system-dependent; see <xref
3278 linkend="locale"> for more information. If this variable is
3279 set to the empty string (which is the default) then the value
3280 is inherited from the execution environment of the server in a
3281 system-dependent way.
3286 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
3287 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
3289 <primary><varname>lc_numeric</> configuration parameter</primary>
3293 Sets the locale to use for formatting numbers, for example
3294 with the <function>to_char</function> family of
3295 functions. Acceptable values are system-dependent; see <xref
3296 linkend="locale"> for more information. If this variable is
3297 set to the empty string (which is the default) then the value
3298 is inherited from the execution environment of the server in a
3299 system-dependent way.
3304 <varlistentry id="guc-lc-time" xreflabel="lc_time">
3305 <term><varname>lc_time</varname> (<type>string</type>)</term>
3307 <primary><varname>lc_time</> configuration parameter</primary>
3311 Sets the locale to use for formatting date and time values.
3312 (Currently, this setting does nothing, but it may in the
3313 future.) Acceptable values are system-dependent; see <xref
3314 linkend="locale"> for more information. If this variable is
3315 set to the empty string (which is the default) then the value
3316 is inherited from the execution environment of the server in a
3317 system-dependent way.
3325 <sect3 id="runtime-config-client-other">
3326 <title>Other Defaults</title>
3330 <varlistentry id="guc-explain-pretty-print" xreflabel="explain_pretty_print">
3331 <term><varname>explain_pretty_print</varname> (<type>boolean</type>)</term>
3333 <primary><varname>explain_pretty_print</> configuration parameter</primary>
3337 Determines whether <command>EXPLAIN VERBOSE</> uses the
3338 indented or non-indented format for displaying detailed
3339 query-tree dumps. The default is on.
3344 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
3345 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
3347 <primary><varname>dynamic_library_path</> configuration parameter</primary>
3349 <indexterm><primary>dynamic loading</></>
3352 If a dynamically loadable module needs to be opened and the
3353 file name specified in the <command>CREATE FUNCTION</command> or
3354 <command>LOAD</command> command
3355 does not have a directory component (i.e. the
3356 name does not contain a slash), the system will search this
3357 path for the required file.
3361 The value for <varname>dynamic_library_path</varname> has to be a
3362 list of absolute directory paths separated by colons (or semi-colons
3363 on Windows). If a list element starts
3364 with the special string <literal>$libdir</literal>, the
3365 compiled-in <productname>PostgreSQL</productname> package
3366 library directory is substituted for <literal>$libdir</literal>. This
3367 is where the modules provided by the standard
3368 <productname>PostgreSQL</productname> distribution are installed.
3369 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
3370 this directory.) For example:
3372 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
3374 or, in a Windows environment:
3376 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
3381 The default value for this parameter is
3382 <literal>'$libdir'</literal>. If the value is set to an empty
3383 string, the automatic path search is turned off.
3387 This parameter can be changed at run time by superusers, but a
3388 setting done that way will only persist until the end of the
3389 client connection, so this method should be reserved for
3390 development purposes. The recommended way to set this parameter
3391 is in the <filename>postgresql.conf</filename> configuration
3401 <sect2 id="runtime-config-locks">
3402 <title>Lock Management</title>
3406 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
3408 <primary>deadlock</primary>
3409 <secondary>timeout during</secondary>
3412 <primary>timeout</primary>
3413 <secondary>deadlock</secondary>
3416 <primary><varname>deadlock_timeout</> configuration parameter</primary>
3419 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
3422 This is the amount of time, in milliseconds, to wait on a lock
3423 before checking to see if there is a deadlock condition. The
3424 check for deadlock is relatively slow, so the server doesn't run
3425 it every time it waits for a lock. We (optimistically?) assume
3426 that deadlocks are not common in production applications and
3427 just wait on the lock for a while before starting the check for a
3428 deadlock. Increasing this value reduces the amount of time
3429 wasted in needless deadlock checks, but slows down reporting of
3430 real deadlock errors. The default is 1000 (i.e., one second),
3431 which is probably about the smallest value you would want in
3432 practice. On a heavily loaded server you might want to raise it.
3433 Ideally the setting should exceed your typical transaction time,
3434 so as to improve the odds that a lock will be released before
3435 the waiter decides to check for deadlock.
3440 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
3441 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
3443 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
3447 The shared lock table is sized on the assumption that at most
3448 <varname>max_locks_per_transaction</varname> *
3449 <varname>max_connections</varname> distinct objects will need to
3450 be locked at any one time. (Thus, this parameter's name may be
3451 confusing: it is not a hard limit on the number of locks taken
3452 by any one transaction, but rather a maximum average value.)
3453 The default, 64, has historically
3454 proven sufficient, but you might need to raise this value if you
3455 have clients that touch many different tables in a single
3456 transaction. This option can only be set at server start.
3464 <sect2 id="runtime-config-compatible">
3465 <title>Version and Platform Compatibility</title>
3467 <sect3 id="runtime-config-compatible-version">
3468 <title>Previous PostgreSQL Versions</title>
3471 <varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
3472 <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
3473 <indexterm><primary>FROM</><secondary>missing</></>
3475 <primary><varname>add_missing_from</> configuration parameter</primary>
3479 When <literal>true</>, tables that are referenced by a query will be
3480 automatically added to the <literal>FROM</> clause if not already
3481 present. The default is <literal>true</> for compatibility with
3482 previous releases of <productname>PostgreSQL</>. However, this
3483 behavior is not SQL-standard, and many people dislike it because it
3484 can mask mistakes (such as referencing a table where you should have
3485 referenced its alias). Set to <literal>false</> for the SQL-standard
3486 behavior of rejecting references to tables that are not listed in
3492 <varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
3493 <term><varname>regex_flavor</varname> (<type>string</type>)</term>
3494 <indexterm><primary>regular expressions</></>
3496 <primary><varname>regex_flavor</> configuration parameter</primary>
3500 The regular expression <quote>flavor</> can be set to
3501 <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
3502 The default is <literal>advanced</>. The <literal>extended</>
3503 setting may be useful for exact backwards compatibility with
3504 pre-7.4 releases of <productname>PostgreSQL</>. See
3505 <xref linkend="posix-syntax-details"> for details.
3510 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
3511 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
3513 <primary><varname>sql_inheritance</> configuration parameter</primary>
3515 <indexterm><primary>inheritance</></>
3518 This controls the inheritance semantics, in particular whether
3519 subtables are included by various commands by default. They were
3520 not included in versions prior to 7.1. If you need the old
3521 behavior you can set this variable to off, but in the long run
3522 you are encouraged to change your applications to use the
3523 <literal>ONLY</literal> key word to exclude subtables. See
3524 <xref linkend="ddl-inherit"> for more information about inheritance.
3529 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
3530 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
3532 <primary><varname>default_with_oids</> configuration parameter</primary>
3536 This controls whether <command>CREATE TABLE</command> and
3537 <command>CREATE TABLE AS</command> include an OID column in
3538 newly-created tables, if neither <literal>WITH OIDS</literal>
3539 nor <literal>WITHOUT OIDS</literal> is specified. It also
3540 determines whether OIDs will be included in tables created by
3541 <command>SELECT INTO</command>. In
3542 <productname>PostgreSQL</productname> &version;
3543 <varname>default_with_oids</varname> defaults to true. This is
3544 also the behavior of previous versions of
3545 <productname>PostgreSQL</productname>. However, assuming that
3546 tables will contain OIDs by default is not encouraged. This
3547 option will probably default to false in a future release of
3548 <productname>PostgreSQL</productname>.
3552 To ease compatibility with applications that make use of OIDs,
3553 this option should left enabled. To ease compatibility with
3554 future versions of <productname>PostgreSQL</productname>, this
3555 option should be disabled, and applications that require OIDs
3556 on certain tables should explicitly specify <literal>WITH
3557 OIDS</literal> when those tables are created.
3564 <sect3 id="runtime-config-compatible-clients">
3565 <title>Platform and Client Compatibility</title>
3568 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
3569 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
3570 <indexterm><primary>IS NULL</></>
3572 <primary><varname>transform_null_equals</> configuration parameter</primary>
3576 When turned on, expressions of the form
3577 <literal><replaceable>expr</> = NULL</literal> (or <literal>NULL
3578 = <replaceable>expr</></literal>) are treated as
3579 <literal><replaceable>expr</> IS NULL</literal>, that is, they
3580 return true if <replaceable>expr</> evaluates to the null value,
3581 and false otherwise. The correct SQL-spec-compliant behavior of
3582 <literal><replaceable>expr</> = NULL</literal> is to always
3583 return null (unknown). Therefore this option defaults to off.
3587 However, filtered forms in <productname>Microsoft
3588 Access</productname> generate queries that appear to use
3589 <literal><replaceable>expr</> = NULL</literal> to test for
3590 null values, so if you use that interface to access the database you
3591 might want to turn this option on. Since expressions of the
3592 form <literal><replaceable>expr</> = NULL</literal> always
3593 return the null value (using the correct interpretation) they are not
3594 very useful and do not appear often in normal applications, so
3595 this option does little harm in practice. But new users are
3596 frequently confused about the semantics of expressions
3597 involving null values, so this option is not on by default.
3601 Note that this option only affects the exact form <literal>= NULL</>,
3602 not other comparison operators or other expressions
3603 that are computationally equivalent to some expression
3604 involving the equals operator (such as <literal>IN</literal>).
3605 Thus, this option is not a general fix for bad programming.
3609 Refer to <xref linkend="functions-comparison"> for related information.
3618 <sect2 id="runtime-config-preset">
3619 <title>Preset Options</title>
3622 The following <quote>parameters</> are read-only, and are determined
3623 when <productname>PostgreSQL</productname> is compiled or when it is
3624 installed. As such, they have been excluded from the sample
3625 <filename>postgresql.conf</> file. These options report
3626 various aspects of <productname>PostgreSQL</productname> behavior
3627 that may be of interest to certain applications, particularly
3628 administrative front-ends.
3633 <varlistentry id="guc-block-size" xreflabel="block_size">
3634 <term><varname>block_size</varname> (<type>integer</type>)</term>
3636 <primary><varname>block_size</> configuration parameter</primary>
3640 Shows the size of a disk block. It is determined by the value
3641 of <literal>BLCKSZ</> when building the server. The default
3642 value is 8192 bytes. The meaning of some configuration
3643 variables (such as <xref linkend="guc-shared-buffers">) is
3644 influenced by <varname>block_size</varname>. See <xref
3645 linkend="runtime-config-resource"> for information.
3650 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
3651 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
3653 <primary><varname>integer_datetimes</> configuration parameter</primary>
3657 Shows whether <productname>PostgreSQL</productname> was built
3658 with support for 64-bit-integer dates and times. It is set by
3659 configuring with <literal>--enable-integer-datetimes</literal>
3660 when building <productname>PostgreSQL</productname>. The
3661 default value is <literal>off</literal>.
3666 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
3667 <term><varname>lc_collate</varname> (<type>string</type>)</term>
3669 <primary><varname>lc_collate</> configuration parameter</primary>
3673 Shows the locale in which sorting of textual data is done.
3674 See <xref linkend="locale"> for more information.
3675 The value is determined when the database cluster is initialized.
3680 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
3681 <term><varname>lc_ctype</varname> (<type>string</type>)</term>
3683 <primary><varname>lc_ctype</> configuration parameter</primary>
3687 Shows the locale that determines character classifications.
3688 See <xref linkend="locale"> for more information.
3689 The value is determined when the database cluster is initialized.
3690 Ordinarily this will be the same as <varname>lc_collate</varname>,
3691 but for special applications it might be set differently.
3696 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
3697 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
3699 <primary><varname>max_function_args</> configuration parameter</primary>
3703 Shows the maximum number of function arguments. It is determined by
3704 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
3705 default value is 32.
3710 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
3711 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
3713 <primary><varname>max_identifier_length</> configuration parameter</primary>
3717 Shows the maximum identifier length. It is determined as one
3718 less than the value of <literal>NAMEDATALEN</> when building
3719 the server. The default value of <literal>NAMEDATALEN</> is
3720 64; therefore the default
3721 <varname>max_identifier_length</varname> is 63.
3726 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
3727 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
3729 <primary><varname>max_index_keys</> configuration parameter</primary>
3733 Shows the maximum number of index keys. It is determined by
3734 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
3735 default value is 32.
3740 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
3741 <term><varname>server_encoding</varname> (<type>string</type>)</term>
3743 <primary><varname>server_encoding</> configuration parameter</primary>
3745 <indexterm><primary>character set</></>
3748 Shows the database encoding (character set).
3749 It is determined when the database is created. Ordinarily,
3750 clients need only be concerned with the value of <xref
3751 linkend="guc-client-encoding">.
3756 <varlistentry id="guc-server-version" xreflabel="server_version">
3757 <term><varname>server_version</varname> (<type>string</type>)</term>
3759 <primary><varname>server_version</> configuration parameter</primary>
3763 Shows the version number of the server. It is determined by the
3764 value of <literal>PG_VERSION</> when building the server.
3772 <sect2 id="runtime-config-custom">
3773 <title>Customized Options</title>
3776 This feature was designed to allow options not normally known to
3777 <productname>PostgreSQL</productname> to be added by add-on modules
3778 (such as procedural languages). This allows add-on modules to be
3779 configured in the standard ways.
3784 <varlistentry id="guc-custom-variable-classes" xreflabel="custom_variable_classes">
3785 <term><varname>custom_variable_classes</varname> (<type>string</type>)</term>
3787 <primary><varname>custom_variable_classes</> configuration parameter</primary>
3791 This variable specifies one or several class names to be used for
3792 custom variables, in the form of a comma-separated list. A custom
3793 variable is a variable not normally known
3794 to <productname>PostgreSQL</productname> proper but used by some
3795 add-on module. Such variables must have names consisting of a class
3796 name, a dot, and a variable name. <varname>custom_variable_classes</>
3797 specifies all the class names in use in a particular installation.
3798 This option can only be set at server start or in the
3799 <filename>postgresql.conf</filename> configuration file.
3807 The difficulty with setting custom variables in
3808 <filename>postgresql.conf</> is that the file must be read before add-on
3809 modules have been loaded, and so custom variables would ordinarily be
3810 rejected as unknown. When <varname>custom_variable_classes</> is set,
3811 the server will accept definitions of arbitrary variables within each
3812 specified class. These variables will be treated as placeholders and
3813 will have no function until the module that defines them is loaded. When a
3814 module for a specific class is loaded, it will add the proper variable
3815 definitions for its class name, convert any placeholder
3816 values according to those definitions, and issue warnings for any
3817 placeholders of its class that remain (which presumably would be
3818 misspelled configuration variables).
3822 Here is an example of what <filename>postgresql.conf</> might contain
3823 when using custom variables:
3826 custom_variable_classes = 'plr,pljava'
3827 plr.path = '/usr/lib/R'
3829 plruby.bar = true # generates error, unknown class name
3834 <sect2 id="runtime-config-developer">
3835 <title>Developer Options</title>
3838 The following options are intended for work on the
3839 <productname>PostgreSQL</productname> source, and in some cases
3840 to assist with recovery of severely damaged databases. There
3841 should be no reason to use them in a production database setup.
3842 As such, they have been excluded from the sample
3843 <filename>postgresql.conf</> file. Note that many of these
3844 options require special source compilation flags to work at all.
3848 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
3849 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
3851 <primary><varname>debug_assertions</> configuration parameter</primary>
3855 Turns on various assertion checks. This is a debugging aid. If
3856 you are experiencing strange problems or crashes you might want
3857 to turn this on, as it might expose programming mistakes. To use
3858 this option, the macro <symbol>USE_ASSERT_CHECKING</symbol>
3859 must be defined when <productname>PostgreSQL</productname> is
3860 built (accomplished by the <command>configure</command> option
3861 <option>--enable-cassert</option>). Note that
3862 <varname>debug_assertions</varname> defaults to on if
3863 <productname>PostgreSQL</productname> has been built with
3869 <varlistentry id="guc-debug-shared-buffers" xreflabel="debug_shared_buffers">
3870 <term><varname>debug_shared_buffers</varname> (<type>integer</type>)</term>
3872 <primary><varname>debug_shared_buffers</> configuration parameter</primary>
3876 Number of seconds between ARC reports.
3877 If set greater than zero, emit ARC statistics to the log every so many
3878 seconds. Zero (the default) disables reporting.
3883 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
3884 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
3886 <primary><varname>pre_auth_delay</> configuration parameter</primary>
3890 If nonzero, a delay of this many seconds occurs just after a new
3891 server process is forked, before it conducts the authentication
3892 process. This is intended to give an opportunity to attach to the
3893 server process with a debugger to trace down misbehavior in
3899 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
3900 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
3902 <primary><varname>trace_notify</> configuration parameter</primary>
3906 Generates a great amount of debugging output for the
3907 <command>LISTEN</command> and <command>NOTIFY</command>
3908 commands. <xref linkend="guc-client-min-messages"> or
3909 <xref linkend="guc-log-min-messages"> must be
3910 <literal>DEBUG1</literal> or lower to send this output to the
3911 client or server log, respectively.
3917 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
3918 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
3919 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
3920 <term><varname>trace_lock_oidmin</varname> (<type>boolean</type>)</term>
3921 <term><varname>trace_lock_table</varname> (<type>boolean</type>)</term>
3922 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
3923 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
3926 Various other code tracing and debugging options.
3931 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
3932 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
3934 <primary><varname>wal_debug</> configuration parameter</primary>
3938 If true, emit WAL-related debugging output. This option is
3939 only available if the <symbol>WAL_DEBUG</symbol> macro was
3940 defined when <productname>PostgreSQL</productname> was
3946 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
3947 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
3949 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
3953 Detection of a damaged page header normally causes
3954 <productname>PostgreSQL</> to report an error, aborting the current
3955 command. Setting <varname>zero_damaged_pages</> to true causes
3956 the system to instead report a warning, zero out the damaged page,
3957 and continue processing. This behavior <emphasis>will destroy data</>,
3958 namely all the rows on the damaged page. But it allows you to get
3959 past the error and retrieve rows from any undamaged pages that may
3960 be present in the table. So it is useful for recovering data if
3961 corruption has occurred due to hardware or software error. You should
3962 generally not set this true until you have given up hope of recovering
3963 data from the damaged page(s) of a table. The
3964 default setting is off, and it can only be changed by a superuser.
3970 <sect2 id="runtime-config-short">
3971 <title>Short Options</title>
3974 For convenience there are also single letter command-line option switches
3975 available for some parameters. They are described in <xref
3976 linkend="runtime-config-short-table">.
3979 <table id="runtime-config-short-table">
3980 <title>Short option key</title>
3984 <entry>Short option</entry>
3985 <entry>Equivalent</entry>
3991 <entry><option>-B <replaceable>x</replaceable></option></entry>
3992 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
3995 <entry><option>-d <replaceable>x</replaceable></option></entry>
3996 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
3999 <entry><option>-F</option></entry>
4000 <entry><literal>fsync = off</></entry>
4003 <entry><option>-h <replaceable>x</replaceable></option></entry>
4004 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
4007 <entry><option>-i</option></entry>
4008 <entry><literal>listen_addresses = '*'</></entry>
4011 <entry><option>-k <replaceable>x</replaceable></option></entry>
4012 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
4015 <entry><option>-l</option></entry>
4016 <entry><literal>ssl = on</></entry>
4019 <entry><option>-N <replaceable>x</replaceable></option></entry>
4020 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
4023 <entry><option>-p <replaceable>x</replaceable></option></entry>
4024 <entry><literal>port = <replaceable>x</replaceable></></entry>
4029 <option>-fi</option>, <option>-fh</option>,
4030 <option>-fm</option>, <option>-fn</option>,
4031 <option>-fs</option>, <option>-ft</option><footnote
4032 id="fn.runtime-config-short">
4034 For historical reasons, these options must be passed to
4035 the individual server process via the <option>-o</option>
4036 <command>postmaster</command> option, for example,
4038 $ <userinput>postmaster -o '-S 1024 -s'</userinput>
4040 or via <envar>PGOPTIONS</envar> from the client side, as
4046 <literal>enable_indexscan = off</>,
4047 <literal>enable_hashjoin = off</>,
4048 <literal>enable_mergejoin = off</>,
4049 <literal>enable_nestloop = off</>,
4050 <literal>enable_seqscan = off</>,
4051 <literal>enable_tidscan = off</>
4056 <entry><option>-s</option><footnoteref linkend="fn.runtime-config-short"></entry>
4057 <entry><literal>log_statement_stats = on</></entry>
4061 <entry><option>-S <replaceable>x</replaceable></option><footnoteref linkend="fn.runtime-config-short">
4063 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
4067 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option><footnoteref linkend="fn.runtime-config-short"></entry>
4068 <entry><literal>log_parser_stats = on</>,
4069 <literal>log_planner_stats = on</>,
4070 <literal>log_executor_stats = on</></entry>
4080 <sect1 id="kernel-resources">
4081 <title>Managing Kernel Resources</title>
4084 A large <productname>PostgreSQL</> installation can quickly exhaust
4085 various operating system resource limits. (On some systems, the
4086 factory defaults are so low that you don't even need a really
4087 <quote>large</> installation.) If you have encountered this kind of
4088 problem, keep reading.
4091 <sect2 id="sysvipc">
4092 <title>Shared Memory and Semaphores</title>
4094 <indexterm zone="sysvipc">
4095 <primary>shared memory</primary>
4098 <indexterm zone="sysvipc">
4099 <primary>semaphores</primary>
4103 Shared memory and semaphores are collectively referred to as
4104 <quote><systemitem class="osname">System V</>
4105 <acronym>IPC</></quote> (together with message queues, which are not
4106 relevant for <productname>PostgreSQL</>). Almost all modern
4107 operating systems provide these features, but not all of them have
4108 them turned on or sufficiently sized by default, especially systems
4109 with BSD heritage. (For the <systemitem class="osname">QNX</> and
4110 <systemitem class="osname">BeOS</> ports, <productname>PostgreSQL</>
4111 provides its own replacement implementation of these facilities.)
4115 The complete lack of these facilities is usually manifested by an
4116 <errorname>Illegal system call</> error upon server start. In
4117 that case there's nothing left to do but to reconfigure your
4118 kernel. <productname>PostgreSQL</> won't work without them.
4122 When <productname>PostgreSQL</> exceeds one of the various hard
4123 <acronym>IPC</> limits, the server will refuse to start and
4124 should leave an instructive error message describing the problem
4125 encountered and what to do about it. (See also <xref
4126 linkend="postmaster-start-failures">.) The relevant kernel
4127 parameters are named consistently across different systems; <xref
4128 linkend="sysvipc-parameters"> gives an overview. The methods to set
4129 them, however, vary. Suggestions for some platforms are given below.
4130 Be warned that it is often necessary to reboot your machine, and
4131 possibly even recompile the kernel, to change these settings.
4135 <table id="sysvipc-parameters">
4136 <title><systemitem class="osname">System V</> <acronym>IPC</> parameters</>
4142 <entry>Description</>
4143 <entry>Reasonable values</>
4149 <entry><varname>SHMMAX</></>
4150 <entry>Maximum size of shared memory segment (bytes)</>
4151 <entry>250 kB + 8.2 kB * <xref linkend="guc-shared-buffers"> +
4152 14.2 kB * <xref linkend="guc-max-connections"> up to infinity</entry>
4156 <entry><varname>SHMMIN</></>
4157 <entry>Minimum size of shared memory segment (bytes)</>
4162 <entry><varname>SHMALL</></>
4163 <entry>Total amount of shared memory available (bytes or pages)</>
4164 <entry>if bytes, same as <varname>SHMMAX</varname>; if pages, <literal>ceil(SHMMAX/PAGE_SIZE)</literal></>
4168 <entry><varname>SHMSEG</></>
4169 <entry>Maximum number of shared memory segments per process</>
4170 <entry>only 1 segment is needed, but the default is much higher</>
4174 <entry><varname>SHMMNI</></>
4175 <entry>Maximum number of shared memory segments system-wide</>
4176 <entry>like <varname>SHMSEG</> plus room for other applications</>
4180 <entry><varname>SEMMNI</></>
4181 <entry>Maximum number of semaphore identifiers (i.e., sets)</>
4182 <entry>at least <literal>ceil(max_connections / 16)</literal></>
4186 <entry><varname>SEMMNS</></>
4187 <entry>Maximum number of semaphores system-wide</>
4188 <entry><literal>ceil(max_connections / 16) * 17</literal> plus room for other applications</>
4192 <entry><varname>SEMMSL</></>
4193 <entry>Maximum number of semaphores per set</>
4194 <entry>at least 17</>
4198 <entry><varname>SEMMAP</></>
4199 <entry>Number of entries in semaphore map</>
4204 <entry><varname>SEMVMX</></>
4205 <entry>Maximum value of semaphore</>
4206 <entry>at least 1000 (The default is often 32767, don't change unless forced to)</>
4215 <indexterm><primary>SHMMAX</primary></indexterm> The most important
4216 shared memory parameter is <varname>SHMMAX</>, the maximum size, in
4217 bytes, of a shared memory segment. If you get an error message from
4218 <function>shmget</> like <errorname>Invalid argument</>, it is
4219 likely that this limit has been exceeded. The size of the required
4220 shared memory segment varies both with the number of requested
4221 buffers (<option>-B</> option) and the number of allowed connections
4222 (<option>-N</> option), although the former is the most significant.
4223 (You can, as a temporary solution, lower these settings to eliminate
4224 the failure.) As a rough approximation, you can estimate the
4225 required segment size as suggested in <xref
4226 linkend="sysvipc-parameters">. Any error message you might get will
4227 contain the size of the failed allocation request.
4231 Some systems also have a limit on the total amount of shared memory in
4232 the system (<varname>SHMALL</>). Make sure this is large enough
4233 for <productname>PostgreSQL</> plus any other applications that
4234 are using shared memory segments. (Caution: <varname>SHMALL</>
4235 is measured in pages rather than bytes on many systems.)
4239 Less likely to cause problems is the minimum size for shared
4240 memory segments (<varname>SHMMIN</>), which should be at most
4241 approximately 256 kB for <productname>PostgreSQL</> (it is
4242 usually just 1). The maximum number of segments system-wide
4243 (<varname>SHMMNI</>) or per-process (<varname>SHMSEG</>) are unlikely
4244 to cause a problem unless your system has them set to zero.
4248 <productname>PostgreSQL</> uses one semaphore per allowed connection
4249 (<option>-N</> option), in sets of 16. Each such set will also
4250 contain a 17th semaphore which contains a <quote>magic
4251 number</quote>, to detect collision with semaphore sets used by
4252 other applications. The maximum number of semaphores in the system
4253 is set by <varname>SEMMNS</>, which consequently must be at least
4254 as high as <varname>max_connections</> plus one extra for each 16
4255 allowed connections (see the formula in <xref
4256 linkend="sysvipc-parameters">). The parameter <varname>SEMMNI</>
4257 determines the limit on the number of semaphore sets that can
4258 exist on the system at one time. Hence this parameter must be at
4259 least <literal>ceil(max_connections / 16)</>. Lowering the number
4260 of allowed connections is a temporary workaround for failures,
4261 which are usually confusingly worded <errorname>No space
4262 left on device</>, from the function <function>semget</>.
4266 In some cases it might also be necessary to increase
4267 <varname>SEMMAP</> to be at least on the order of
4268 <varname>SEMMNS</>. This parameter defines the size of the semaphore
4269 resource map, in which each contiguous block of available semaphores
4270 needs an entry. When a semaphore set is freed it is either added to
4271 an existing entry that is adjacent to the freed block or it is
4272 registered under a new map entry. If the map is full, the freed
4273 semaphores get lost (until reboot). Fragmentation of the semaphore
4274 space could over time lead to fewer available semaphores than there
4279 The <varname>SEMMSL</> parameter, which determines how many
4280 semaphores can be in a set, must be at least 17 for
4281 <productname>PostgreSQL</>.
4285 Various other settings related to <quote>semaphore undo</>, such as
4286 <varname>SEMMNU</> and <varname>SEMUME</>, are not of concern
4287 for <productname>PostgreSQL</>.
4293 <term><systemitem class="osname">BSD/OS</></term>
4294 <indexterm><primary>BSD/OS</><secondary>IPC configuration</></>
4297 <title>Shared Memory</>
4299 By default, only 4 MB of shared memory is supported. Keep in
4300 mind that shared memory is not pageable; it is locked in RAM.
4301 To increase the amount of shared memory supported by your
4302 system, add something like the following to your kernel configuration
4305 options "SHMALL=8192"
4306 options "SHMMAX=\(SHMALL*PAGE_SIZE\)"
4308 <varname>SHMALL</> is measured in 4KB pages, so a value of
4309 1024 represents 4 MB of shared memory. Therefore the above increases
4310 the maximum shared memory area to 32 MB.
4311 For those running 4.3 or later, you will probably also need to increase
4312 <varname>KERNEL_VIRTUAL_MB</> above the default <literal>248</>.
4313 Once all changes have been made, recompile the kernel, and reboot.
4318 For those running 4.0 and earlier releases, use <command>bpatch</>
4319 to find the <varname>sysptsize</> value in the current
4320 kernel. This is computed dynamically at boot time.
4322 $ <userinput>bpatch -r sysptsize</>
4323 <computeroutput>0x9 = 9</>
4325 Next, add <varname>SYSPTSIZE</> as a hard-coded value in the
4326 kernel configuration file. Increase the value you found using
4327 <command>bpatch</>. Add 1 for every additional 4 MB of
4328 shared memory you desire.
4330 options "SYSPTSIZE=16"
4332 <varname>sysptsize</> cannot be changed by <command>sysctl</command>.
4336 <title>Semaphores</>
4338 You will probably want to increase the number of semaphores
4339 as well; the default system total of 60 will only allow about
4340 50 <productname>PostgreSQL</productname> connections. Set the
4341 values you want in your kernel configuration file, e.g.:
4344 options "SEMMNS=240"
4353 <term><systemitem class="osname">FreeBSD</></term>
4354 <term><systemitem class="osname">NetBSD</></term>
4355 <term><systemitem class="osname">OpenBSD</></term>
4356 <indexterm><primary>FreeBSD</><secondary>IPC configuration</></>
4357 <indexterm><primary>NetBSD</><secondary>IPC configuration</></>
4358 <indexterm><primary>OpenBSD</><secondary>IPC configuration</></>
4361 The options <varname>SYSVSHM</> and <varname>SYSVSEM</> need
4362 to be enabled when the kernel is compiled. (They are by
4363 default.) The maximum size of shared memory is determined by
4364 the option <varname>SHMMAXPGS</> (in pages). The following
4365 shows an example of how to set the various parameters:
4368 options SHMMAXPGS=4096
4377 (On <systemitem class="osname">NetBSD</> and <systemitem
4378 class="osname">OpenBSD</> the key word is actually
4379 <literal>option</literal> singular.)
4382 You might also want to configure your kernel to lock shared
4383 memory into RAM and prevent it from being paged out to swap.
4384 Use the <command>sysctl</> setting
4385 <literal>kern.ipc.shm_use_phys</>.
4392 <term><systemitem class="osname">HP-UX</></term>
4393 <indexterm><primary>HP-UX</><secondary>IPC configuration</></>
4396 The default settings tend to suffice for normal installations.
4397 On <productname>HP-UX</> 10, the factory default for
4398 <varname>SEMMNS</> is 128, which might be too low for larger
4402 <acronym>IPC</> parameters can be set in the <application>System
4403 Administration Manager</> (<acronym>SAM</>) under
4404 <menuchoice><guimenu>Kernel
4405 Configuration</><guimenuitem>Configurable Parameters</></>. Hit
4406 <guibutton>Create A New Kernel</> when you're done.
4413 <term><systemitem class="osname">Linux</></term>
4414 <indexterm><primary>Linux</><secondary>IPC configuration</></>
4417 The default shared memory limit (both
4418 <varname>SHMMAX</varname> and <varname>SHMALL</varname>) is 32
4419 MB in 2.2 kernels, but it can be changed in the
4420 <filename>proc</filename> file system (without reboot). For
4421 example, to allow 128 MB:
4423 <prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmall</userinput>
4424 <prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmmax</userinput>
4426 You could put these commands into a script run at boot-time.
4430 Alternatively, you can use <command>sysctl</command>, if
4431 available, to control these parameters. Look for a file
4432 called <filename>/etc/sysctl.conf</filename> and add lines
4433 like the following to it:
4435 kernel.shmall = 134217728
4436 kernel.shmmax = 134217728
4438 This file is usually processed at boot time, but
4439 <command>sysctl</command> can also be called
4444 Other parameters are sufficiently sized for any application. If
4445 you want to see for yourself look in
4446 <filename>/usr/src/linux/include/asm-<replaceable>xxx</>/shmparam.h</>
4447 and <filename>/usr/src/linux/include/linux/sem.h</>.
4454 <term><systemitem class="osname">MacOS X</></term>
4455 <indexterm><primary>MacOS X</><secondary>IPC configuration</></>
4458 In OS X 10.2 and earlier, edit the file
4459 <filename>/System/Library/StartupItems/SystemTuning/SystemTuning</>
4460 and change the values in the following commands:
4462 sysctl -w kern.sysv.shmmax
4463 sysctl -w kern.sysv.shmmin
4464 sysctl -w kern.sysv.shmmni
4465 sysctl -w kern.sysv.shmseg
4466 sysctl -w kern.sysv.shmall
4468 In OS X 10.3, these commands have been moved to <filename>/etc/rc</>
4469 and must be edited there. You'll need to reboot to make changes
4470 take effect. Note that <filename>/etc/rc</> is usually
4471 overwritten by OS X updates (such as 10.3.6 to 10.3.7) so you
4472 should expect to have to redo your editing after each update.
4475 <varname>SHMALL</> is measured in 4KB pages on this platform.
4482 <term><systemitem class="osname">SCO OpenServer</></term>
4483 <indexterm><primary>SCO OpenServer</><secondary>IPC configuration</></>
4486 In the default configuration, only 512 kB of shared memory per
4487 segment is allowed, which is about enough for <option>-B 24 -N
4488 12</>. To increase the setting, first change to the directory
4489 <filename>/etc/conf/cf.d</>. To display the current value of
4490 <varname>SHMMAX</>, run
4492 ./configure -y SHMMAX
4494 To set a new value for <varname>SHMMAX</>, run
4496 ./configure SHMMAX=<replaceable>value</>
4498 where <replaceable>value</> is the new value you want to use
4499 (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel:
4509 <term><systemitem class="osname">AIX</></term>
4510 <indexterm><primary>AIX</><secondary>IPC configuration</></>
4513 At least as of version 5.1, it should not be necessary to do
4514 any special configuration for such parameters as
4515 <varname>SHMMAX</varname>, as it appears this is configured to
4516 allow all memory to be used as shared memory. That is the
4517 sort of configuration commonly used for other databases such
4518 as <application>DB/2</application>.</para>
4520 <para> It may, however, be necessary to modify the global
4521 <command>ulimit</command> information in
4522 <filename>/etc/security/limits</filename>, as the default hard
4523 limits for file sizes (<varname>fsize</varname>) and numbers of
4524 files (<varname>nofiles</varname>) may be too low.
4530 <term><systemitem class="osname">Solaris</></term>
4531 <indexterm><primary>Solaris</><secondary>IPC configuration</></>
4534 At least in version 2.6, the default maximum size of a shared
4535 memory segments is too low for <productname>PostgreSQL</>. The
4536 relevant settings can be changed in <filename>/etc/system</>,
4539 set shmsys:shminfo_shmmax=0x2000000
4540 set shmsys:shminfo_shmmin=1
4541 set shmsys:shminfo_shmmni=256
4542 set shmsys:shminfo_shmseg=256
4544 set semsys:seminfo_semmap=256
4545 set semsys:seminfo_semmni=512
4546 set semsys:seminfo_semmns=512
4547 set semsys:seminfo_semmsl=32
4549 You need to reboot for the changes to take effect.
4554 url="http://sunsite.uakom.sk/sunworldonline/swol-09-1997/swol-09-insidesolaris.html"></>
4555 for information on shared memory under
4556 <productname>Solaris</>.
4563 <term><systemitem class="osname">UnixWare</></term>
4564 <indexterm><primary>UnixWare</><secondary>IPC configuration</></>
4567 On <productname>UnixWare</> 7, the maximum size for shared
4568 memory segments is 512 kB in the default configuration. This
4569 is enough for about <option>-B 24 -N 12</>. To display the
4570 current value of <varname>SHMMAX</>, run
4572 /etc/conf/bin/idtune -g SHMMAX
4574 which displays the current, default, minimum, and maximum
4575 values. To set a new value for <varname>SHMMAX</>,
4578 /etc/conf/bin/idtune SHMMAX <replaceable>value</>
4580 where <replaceable>value</> is the new value you want to use
4581 (in bytes). After setting <varname>SHMMAX</>, rebuild the
4584 /etc/conf/bin/idbuild -B
4597 <title>Resource Limits</title>
4600 Unix-like operating systems enforce various kinds of resource limits
4601 that might interfere with the operation of your
4602 <productname>PostgreSQL</productname> server. Of particular
4603 importance are limits on the number of processes per user, the
4604 number of open files per process, and the amount of memory available
4605 to each process. Each of these have a <quote>hard</quote> and a
4606 <quote>soft</quote> limit. The soft limit is what actually counts
4607 but it can be changed by the user up to the hard limit. The hard
4608 limit can only be changed by the root user. The system call
4609 <function>setrlimit</function> is responsible for setting these
4610 parameters. The shell's built-in command <command>ulimit</command>
4611 (Bourne shells) or <command>limit</command> (<application>csh</>) is
4612 used to control the resource limits from the command line. On
4613 BSD-derived systems the file <filename>/etc/login.conf</filename>
4614 controls the various resource limits set during login. See the
4615 operating system documentation for details. The relevant
4616 parameters are <varname>maxproc</varname>,
4617 <varname>openfiles</varname>, and <varname>datasize</varname>. For
4622 :datasize-cur=256M:\
4624 :openfiles-cur=256:\
4627 (<literal>-cur</literal> is the soft limit. Append
4628 <literal>-max</literal> to set the hard limit.)
4632 Kernels can also have system-wide limits on some resources.
4636 On <productname>Linux</productname>
4637 <filename>/proc/sys/fs/file-max</filename> determines the
4638 maximum number of open files that the kernel will support. It can
4639 be changed by writing a different number into the file or by
4640 adding an assignment in <filename>/etc/sysctl.conf</filename>.
4641 The maximum limit of files per process is fixed at the time the
4642 kernel is compiled; see
4643 <filename>/usr/src/linux/Documentation/proc.txt</filename> for
4651 The <productname>PostgreSQL</productname> server uses one process
4652 per connection so you should provide for at least as many processes
4653 as allowed connections, in addition to what you need for the rest
4654 of your system. This is usually not a problem but if you run
4655 several servers on one machine things might get tight.
4659 The factory default limit on open files is often set to
4660 <quote>socially friendly</quote> values that allow many users to
4661 coexist on a machine without using an inappropriate fraction of
4662 the system resources. If you run many servers on a machine this
4663 is perhaps what you want, but on dedicated servers you may want to
4668 On the other side of the coin, some systems allow individual
4669 processes to open large numbers of files; if more than a few
4670 processes do so then the system-wide limit can easily be exceeded.
4671 If you find this happening, and you do not want to alter the
4672 system-wide limit, you can set <productname>PostgreSQL</>'s <xref
4673 linkend="guc-max-files-per-process"> configuration parameter to
4674 limit the consumption of open files.
4679 <title>Linux Memory Overcommit</title>
4682 In Linux 2.4 and later, the default virtual memory behavior is not
4683 optimal for <productname>PostgreSQL</productname>. Because of the
4684 way that the kernel implements memory overcommit, the kernel may
4685 terminate the <productname>PostgreSQL</productname> server (the
4686 <filename>postmaster</filename> process) if the memory demands of
4687 another process cause the system to run out of virtual memory.
4691 If this happens, you will see a kernel message that looks like
4692 this (consult your system documentation and configuration on where
4693 to look for such a message):
4695 Out of Memory: Killed process 12345 (postmaster).
4697 This indicates that the <filename>postmaster</filename> process
4698 has been terminated due to memory pressure.
4699 Although existing database connections will continue to function
4700 normally, no new connections will be accepted. To recover,
4701 <productname>PostgreSQL</productname> will need to be restarted.
4705 One way to avoid this problem is to run
4706 <productname>PostgreSQL</productname>
4707 on a machine where you can be sure that other processes will not
4708 run the machine out of memory.
4712 On Linux 2.6 and later, a better solution is to modify the kernel's
4713 behavior so that it will not <quote>overcommit</> memory. This is
4714 done by selecting strict overcommit mode via <command>sysctl</command>:
4716 sysctl -w vm.overcommit_memory=2
4718 or placing an equivalent entry in <filename>/etc/sysctl.conf</>.
4719 You may also wish to modify the related setting
4720 <literal>vm.overcommit_ratio</>. For details see the kernel documentation
4721 file <filename>Documentation/vm/overcommit-accounting</>.
4725 Some vendors' Linux 2.4 kernels are reported to have early versions
4726 of the 2.6 overcommit <command>sysctl</command> parameter. However, setting
4727 <literal>vm.overcommit_memory</> to 2
4728 on a kernel that does not have the relevant code will make
4729 things worse not better. It is recommended that you inspect
4730 the actual kernel source code (see the function
4731 <function>vm_enough_memory</> in the file <filename>mm/mmap.c</>)
4732 to verify what is supported in your copy before you try this in a 2.4
4733 installation. The presence of the <filename>overcommit-accounting</>
4734 documentation file should <emphasis>not</> be taken as evidence that the
4735 feature is there. If in any doubt, consult a kernel expert or your
4742 <sect1 id="postmaster-shutdown">
4743 <title>Shutting Down the Server</title>
4745 <indexterm zone="postmaster-shutdown">
4746 <primary>shutdown</>
4750 There are several ways to shut down the database server. You control
4751 the type of shutdown by sending different signals to the
4752 <command>postmaster</command> process.
4756 <term><systemitem>SIGTERM</systemitem><indexterm><primary>SIGTERM</></></term>
4759 After receiving <systemitem>SIGTERM</systemitem>, the server
4760 disallows new connections, but lets existing sessions end their
4761 work normally. It shuts down only after all of the sessions
4762 terminate normally. This is the <firstterm>Smart
4763 Shutdown</firstterm>.
4769 <term><systemitem>SIGINT</systemitem><indexterm><primary>SIGINT</></></term>
4772 The server disallows new connections and sends all existing
4773 server processes <systemitem>SIGTERM</systemitem>, which will cause them
4774 to abort their current transactions and exit promptly. It then
4775 waits for the server processes to exit and finally shuts down. This is the
4776 <firstterm>Fast Shutdown</firstterm>.
4782 <term><systemitem>SIGQUIT</systemitem><indexterm><primary>SIGQUIT</></></term>
4785 This is the <firstterm>Immediate Shutdown</firstterm>, which
4786 will cause the <command>postmaster</command> process to send a
4787 <systemitem>SIGQUIT</systemitem> to all child processes and exit
4788 immediately, without properly shutting itself down. The child processes
4789 likewise exit immediately upon receiving
4790 <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by
4791 replaying the WAL log) upon next start-up. This is recommended
4792 only in emergencies.
4800 The <xref linkend="app-pg-ctl"> program provides a convenient
4801 interface for sending these signals to shut down the server.
4805 Alternatively, you can send the signal directly using <command>kill</>.
4806 The <acronym>PID</> of the <command>postmaster</command> process can be
4807 found using the <command>ps</command> program, or from the file
4808 <filename>postmaster.pid</filename> in the data directory. For
4809 example, to do a fast shutdown:
4811 $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
4817 It is best not to use <systemitem>SIGKILL</systemitem> to shut down
4818 the server. Doing so will prevent the server from releasing
4819 shared memory and semaphores, which may then have to be done
4820 manually before a new server can be started. Furthermore,
4821 <systemitem>SIGKILL</systemitem> kills the <command>postmaster</command>
4822 process without letting it relay the signal to its subprocesses,
4823 so it will be necessary to kill the individual subprocesses by hand as
4829 <sect1 id="ssl-tcp">
4830 <title>Secure TCP/IP Connections with SSL</title>
4832 <indexterm zone="ssl-tcp">
4833 <primary>SSL</primary>
4837 <productname>PostgreSQL</> has native support for using
4838 <acronym>SSL</> connections to encrypt client/server communications
4839 for increased security. This requires that
4840 <productname>OpenSSL</productname> is installed on both client and
4841 server systems and that support in <productname>PostgreSQL</> is
4842 enabled at build time (see <xref linkend="installation">).
4846 With <acronym>SSL</> support compiled in, the
4847 <productname>PostgreSQL</> server can be started with
4848 <acronym>SSL</> enabled by setting the parameter
4849 <xref linkend="guc-ssl"> to <literal>on</> in
4850 <filename>postgresql.conf</>. When
4851 starting in <acronym>SSL</> mode, the server will look for the
4852 files <filename>server.key</> and <filename>server.crt</> in the
4853 data directory, which must contain the server private key
4854 and certificate, respectively. These files must be set up correctly
4855 before an <acronym>SSL</>-enabled server can start. If the private key is
4856 protected with a passphrase, the server will prompt for the
4857 passphrase and will not start until it has been entered.
4861 The server will listen for both standard and <acronym>SSL</>
4862 connections on the same TCP port, and will negotiate with any
4863 connecting client on whether to use <acronym>SSL</>. By default,
4864 this is at the client's option; see <xref
4865 linkend="auth-pg-hba-conf"> about how to set up the server to
4866 require use of <acronym>SSL</> for some or all connections.
4870 For details on how to create your server private key and certificate,
4871 refer to the <productname>OpenSSL</> documentation. A
4872 self-signed certificate can be used for testing, but a
4873 certificate signed by a certificate authority (<acronym>CA</>)
4874 (either one of the global
4875 <acronym>CAs</> or a local one) should be used in production so the
4876 client can verify the server's identity. To create a quick
4877 self-signed certificate, use the following
4878 <productname>OpenSSL</productname> command:
4880 openssl req -new -text -out server.req
4882 Fill out the information that <command>openssl</> asks for. Make sure
4883 that you enter the local host name as <quote>Common Name</>; the challenge
4884 password can be left blank. The program will generate a key that is
4885 passphrase protected; it will not accept a passphrase that is less
4886 than four characters long. To remove the passphrase (as you must if
4887 you want automatic start-up of the server), run the commands
4889 openssl rsa -in privkey.pem -out server.key
4892 Enter the old passphrase to unlock the existing key. Now do
4894 openssl req -x509 -in server.req -text -key server.key -out server.crt
4895 chmod og-rwx server.key
4897 to turn the certificate into a self-signed certificate and to copy the
4898 key and certificate to where the server will look for them.
4902 If verification of client certificates is required, place the
4903 certificates of the <acronym>CA</acronym>(s) you wish to check for in
4904 the file <filename>root.crt</filename> in the data directory. When
4905 present, a client certificate will be requested from the client
4906 during SSL connection startup, and it must have been signed by one of the
4907 certificates present in <filename>root.crt</filename>.
4911 When the <filename>root.crt</filename> file is not present, client
4912 certificates will not be requested or checked. In this mode, SSL
4913 provides communication security but not authentication.
4917 The files <filename>server.key</>, <filename>server.crt</>,
4918 and <filename>root.crt</filename> are only examined during server
4919 start; so you must restart the server to make changes in them take
4924 <sect1 id="ssh-tunnels">
4925 <title>Secure TCP/IP Connections with <application>SSH</application> Tunnels</title>
4927 <indexterm zone="ssh-tunnels">
4928 <primary>ssh</primary>
4932 One can use <application>SSH</application> to encrypt the network
4933 connection between clients and a
4934 <productname>PostgreSQL</productname> server. Done properly, this
4935 provides an adequately secure network connection, even for non-SSL-capable
4940 First make sure that an <application>SSH</application> server is
4941 running properly on the same machine as the
4942 <productname>PostgreSQL</productname> server and that you can log in using
4943 <command>ssh</command> as some user. Then you can establish a secure
4944 tunnel with a command like this from the client machine:
4946 ssh -L 3333:foo.com:5432 joe@foo.com
4948 The first number in the <option>-L</option> argument, 3333, is the
4949 port number of your end of the tunnel; it can be chosen freely. The
4950 second number, 5432, is the remote end of the tunnel: the port
4951 number your server is using. The name or IP address between
4952 the port numbers is the host with the database server you are going
4953 to connect to. In order to connect to the database server using
4954 this tunnel, you connect to port 3333 on the local machine:
4956 psql -h localhost -p 3333 template1
4958 To the database server it will then look as though you are really
4959 user <literal>joe@foo.com</literal> and it will use whatever
4960 authentication procedure was configured for connections from this
4961 user and host. Note that the server will not think the connection is
4962 SSL-encrypted, since in fact it is not encrypted between the
4963 <application>SSH</application> server and the
4964 <productname>PostgreSQL</productname> server. This should not pose any
4965 extra security risk as long as they are on the same machine.
4969 tunnel setup to succeed you must be allowed to connect via
4970 <command>ssh</command> as <literal>joe@foo.com</literal>, just
4971 as if you had attempted to use <command>ssh</command> to set up a
4977 Several other applications exist that can provide secure tunnels using
4978 a procedure similar in concept to the one just described.
4986 <!-- Keep this comment at the end of the file
4991 sgml-minimize-attributes:nil
4992 sgml-always-quote-attributes:t
4995 sgml-parent-document:nil
4996 sgml-default-dtd-file:"./reference.ced"
4997 sgml-exposed-tags:nil
4998 sgml-local-catalogs:("/usr/lib/sgml/catalog")
4999 sgml-local-ecat-files:nil