2 $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.269 2004/07/11 00:18:40 momjian Exp $
6 <Title>Server Run-time Environment</Title>
9 This chapter discusses how to set up and run the database server
10 and the 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 connected to 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 but is by no
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 instead.) A
55 database cluster is a collection of databases is accessible 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 information
61 about creating databases.)
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 <command>initdb</command>,<indexterm><primary>initdb</></> which is
73 installed with <productname>PostgreSQL</productname>. The desired
74 file system location of your database system is indicated by the
75 <option>-D</option> option, for example
77 <prompt>$</> <userinput>initdb -D /usr/local/pgsql/data</userinput>
79 Note that you must execute this command while logged into the
80 <productname>PostgreSQL</productname> user account, which is
81 described in the previous section.
86 As an alternative to the <option>-D</option> option, you can set
87 the environment variable <envar>PGDATA</envar>.
88 <indexterm><primary><envar>PGDATA</envar></primary></indexterm>
93 <command>initdb</command> will attempt to create the directory you
94 specify if it does not already exist. It is likely that it will not
95 have the permission to do so (if you followed our advice and created
96 an unprivileged account). In that case you should create the
97 directory yourself (as root) and change the owner to be the
98 <productname>PostgreSQL</productname> user. Here is how this might
101 root# <userinput>mkdir /usr/local/pgsql/data</userinput>
102 root# <userinput>chown postgres /usr/local/pgsql/data</userinput>
103 root# <userinput>su postgres</userinput>
104 postgres$ <userinput>initdb -D /usr/local/pgsql/data</userinput>
109 <command>initdb</command> will refuse to run if the data directory
110 looks like it it has already been initialized.</para>
113 Because the data directory contains all the data stored in the
114 database, it is essential that it be secured from unauthorized
115 access. <command>initdb</command> therefore revokes access
116 permissions from everyone but the
117 <productname>PostgreSQL</productname> user.
121 However, while the directory contents are secure, the default
122 client authentication setup allows any local user to connect to the
123 database and even become the database superuser. If you do not
124 trust other local users, we recommend you use one of
125 <command>initdb</command>'s <option>-W</option>, <option>--pwprompt</option>
126 or <option>--pwfile</option> option to assign a password to the
127 database superuser.<indexterm><primary>password</><secondary>of the
128 superuser</></indexterm> After <command>initdb</command>, modify
129 the <filename>pg_hba.conf</filename> file to use <literal>md5</> or
130 <literal>password</> instead of <literal>trust</> authentication
131 <emphasis>before</> you start the server for the first time. (Other
132 approaches include using <literal>ident</literal> authentication or
133 file system permissions to restrict connections. See <xref
134 linkend="client-authentication"> for more information.)
138 <command>initdb</command> also initializes the default
139 locale<indexterm><primary>locale</></> for the database cluster.
140 Normally, it will just take the locale settings in the environment
141 and apply them to the initialized database. It is possible to
142 specify a different locale for the database; more information about
143 that can be found in <xref linkend="locale">. The sort order used
144 within a particular database cluster is set by
145 <command>initdb</command> and cannot be changed later, short of
146 dumping all data, rerunning <command>initdb</command>, and
147 reloading the data. So it's important to make this choice correctly
152 <sect1 id="postmaster-start">
153 <title>Starting the Database Server</title>
156 Before anyone can access the database, you must start the database
157 server. The database server program is called
158 <command>postmaster</command>.<indexterm><primary>postmaster</></>
159 The <command>postmaster</command> must know where to
160 find the data it is supposed to use. This is done with the
161 <option>-D</option> option. Thus, the simplest way to start the
164 $ <userinput>postmaster -D /usr/local/pgsql/data</userinput>
166 which will leave the server running in the foreground. This must be
167 done while logged into the <productname>PostgreSQL</productname> user
168 account. Without <option>-D</option>, the server will try to use
169 the data directory named by the environment variable <envar>PGDATA</envar>.
170 If that variable is not provided either, it will fail.
174 Normally it is better to start the <command>postmaster</command> in the
175 background. For this, use the usual shell syntax:
177 $ <userinput>postmaster -D /usr/local/pgsql/data >logfile 2>&1 &</userinput>
179 It is important to store the server's <systemitem>stdout</> and
180 <systemitem>stderr</> output somewhere, as shown above. It will help
181 for auditing purposes and to diagnose problems. (See <xref
182 linkend="logfile-maintenance"> for a more thorough discussion of log
187 The <command>postmaster</command> also takes a number of other
188 command line options. For more information, see the reference page
189 and <xref linkend="runtime-config"> below.
193 This shell syntax can get tedious quickly. Therefore the shell
195 <command>pg_ctl</command><indexterm><primary>pg_ctl</primary></indexterm>
196 is provided to simplify some tasks. For example:
198 pg_ctl start -l logfile
200 will start the server in the background and put the output into the
201 named log file. The <option>-D</option> option has the same meaning
202 here as in the <command>postmaster</command>. <command>pg_ctl</command> is also
203 capable of stopping the server.
207 Normally, you will want to start the database server when the
208 computer boots.<indexterm><primary>booting</><secondary>starting
209 the server during</></> Autostart scripts are operating-system-specific.
210 There are a few distributed with
211 <productname>PostgreSQL</productname> in the
212 <filename>contrib/start-scripts</> directory. Installing one will require
217 Different systems have different conventions for starting up daemons
218 at boot time. Many systems have a file
219 <filename>/etc/rc.local</filename> or
220 <filename>/etc/rc.d/rc.local</filename>. Others use
221 <filename>rc.d</> directories. Whatever you do, the server must be
222 run by the <productname>PostgreSQL</productname> user account
223 <emphasis>and not by root</emphasis> or any other user. Therefore you
224 probably should form your commands using <literal>su -c '...'
225 postgres</literal>. For example:
227 su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres
232 Here are a few more operating-system-specific suggestions. (In each
233 case be sure to use the proper installation directory and user
234 name where we show generic values.)
239 For <productname>FreeBSD</productname>, look at the file
240 <filename>contrib/start-scripts/freebsd</filename> in the
241 <productname>PostgreSQL</productname> source distribution.
242 <indexterm><primary>FreeBSD</><secondary>start script</secondary></>
248 On <productname>OpenBSD</productname>, add the following lines
249 to the file <filename>/etc/rc.local</filename>:
250 <indexterm><primary>OpenBSD</><secondary>start script</secondary></>
252 if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postmaster ]; then
253 su - -c '/usr/local/pgsql/bin/pg_ctl start -l /var/postgresql/log -s' postgres
254 echo -n ' postgresql'
262 On <productname>Linux</productname> systems either add
263 <indexterm><primary>Linux</><secondary>start script</secondary></>
265 /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
267 to <filename>/etc/rc.d/rc.local</filename> or look at the file
268 <filename>contrib/start-scripts/linux</filename> in the
269 <productname>PostgreSQL</productname> source distribution.
275 On <productname>NetBSD</productname>, either use the
276 <productname>FreeBSD</productname> or
277 <productname>Linux</productname> start scripts, depending on
278 preference. <indexterm><primary>NetBSD</><secondary>start script</secondary></>
284 On <productname>Solaris</productname>, create a file called
285 <filename>/etc/init.d/postgresql</filename> that contains
287 <indexterm><primary>Solaris</><secondary>start script</secondary></>
289 su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
291 Then, create a symbolic link to it in <filename>/etc/rc3.d</> as
292 <filename>S99postgresql</>.
300 While the <command>postmaster</command> is running, its
301 <acronym>PID</acronym> is stored in the file
302 <filename>postmaster.pid</filename> in the data directory. This is
303 used to prevent multiple <command>postmaster</command> processes
304 running in the same data directory and can also be used for
305 shutting down the <command>postmaster</command> process.
308 <sect2 id="postmaster-start-failures">
309 <title>Server Start-up Failures</title>
312 There are several common reasons the server might fail to
313 start. Check the server's log file, or start it by hand (without
314 redirecting standard output or standard error) and see what error
315 messages appear. Below we explain some of the most common error
316 messages in more detail.
321 LOG: could not bind IPv4 socket: Address already in use
322 HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
323 FATAL: could not create TCP/IP listen socket
325 This usually means just what it suggests: you tried to start
326 another <command>postmaster</command> on the same port where one is already running.
327 However, if the kernel error message is not <computeroutput>Address
328 already in use</computeroutput> or some variant of that, there may
329 be a different problem. For example, trying to start a <command>postmaster</command>
330 on a reserved port number may draw something like:
332 $ <userinput>postmaster -p 666</userinput>
333 LOG: could not bind IPv4 socket: Permission denied
334 HINT: Is another postmaster already running on port 666? If not, wait a few seconds and retry.
335 FATAL: could not create TCP/IP listen socket
342 FATAL: could not create shared memory segment: Invalid argument
343 DETAIL: Failed system call was shmget(key=5440001, size=4011376640, 03600).
345 probably means your kernel's limit on the size of shared memory is
346 smaller than the work area <productname>PostgreSQL</productname>
347 is trying to create (4011376640 bytes in this example). Or it could
348 mean that you do not have System-V-style shared memory support
349 configured into your kernel at all. As a temporary workaround, you
350 can try starting the server with a smaller-than-normal number
351 of buffers (<option>-B</option> switch). You will eventually want
352 to reconfigure your kernel to increase the allowed shared memory
353 size. You may also see this message when trying to start multiple
354 servers on the same machine, if their total space requested
355 exceeds the kernel limit.
361 FATAL: could not create semaphores: No space left on device
362 DETAIL: Failed system call was semget(5440126, 17, 03600).
364 does <emphasis>not</emphasis> mean you've run out of disk
365 space. It means your kernel's limit on the number of <systemitem
366 class="osname">System V</> semaphores is smaller than the number
367 <productname>PostgreSQL</productname> wants to create. As above,
368 you may be able to work around the problem by starting the
369 server with a reduced number of allowed connections
370 (<option>-N</option> switch), but you'll eventually want to
371 increase the kernel limit.
375 If you get an <quote>illegal system call</> error, it is likely that
376 shared memory or semaphores are not supported in your kernel at
377 all. In that case your only option is to reconfigure the kernel to
378 enable these features.
382 Details about configuring <systemitem class="osname">System V</>
383 <acronym>IPC</> facilities are given in <xref linkend="sysvipc">.
387 <sect2 id="client-connection-problems">
388 <title>Client Connection Problems</title>
391 Although the error conditions possible on the client side are quite
392 varied and application-dependent, a few of them might be directly
393 related to how the server was started up. Conditions other than
394 those shown below should be documented with the respective client
400 psql: could not connect to server: Connection refused
401 Is the server running on host "server.joe.com" and accepting
402 TCP/IP connections on port 5432?
404 This is the generic <quote>I couldn't find a server to talk
405 to</quote> failure. It looks like the above when TCP/IP
406 communication is attempted. A common mistake is to forget to
407 configure the server to allow TCP/IP connections.
411 Alternatively, you'll get this when attempting Unix-domain socket
412 communication to a local server:
414 psql: could not connect to server: No such file or directory
415 Is the server running locally and accepting
416 connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
421 The last line is useful in verifying that the client is trying to
422 connect to the right place. If there is in fact no server
423 running there, the kernel error message will typically be either
424 <computeroutput>Connection refused</computeroutput> or
425 <computeroutput>No such file or directory</computeroutput>, as
426 illustrated. (It is important to realize that
427 <computeroutput>Connection refused</computeroutput> in this context
428 does <emphasis>not</emphasis> mean that the server got your
429 connection request and rejected it. That case will produce a
430 different message, as shown in <xref
431 linkend="client-authentication-problems">.) Other error messages
432 such as <computeroutput>Connection timed out</computeroutput> may
433 indicate more fundamental problems, like lack of network
439 <sect1 id="runtime-config">
440 <Title>Run-time Configuration</Title>
443 <primary>configuration</primary>
444 <secondary>of the server</secondary>
448 There are a lot of configuration parameters that affect the
449 behavior of the database system. In this subsection, we describe
450 how to set configuration parameters; the following subsections
451 discuss each parameter in detail.
455 All parameter names are case-insensitive. Every parameter takes a
456 value of one of the four types: boolean, integer, floating point,
457 and string. Boolean values are <literal>ON</literal>,
458 <literal>OFF</literal>, <literal>TRUE</literal>,
459 <literal>FALSE</literal>, <literal>YES</literal>,
460 <literal>NO</literal>, <literal>1</literal>, <literal>0</literal>
461 (case-insensitive) or any non-ambiguous prefix of these.
465 One way to set these parameters is to edit the file
466 <filename>postgresql.conf</filename><indexterm><primary>postgresql.conf</></>
467 in the data directory. (A default file is installed there.) An
468 example of what this file might look like is:
471 log_connections = yes
472 log_destination = 'syslog'
473 search_path = '$user, public'
475 One parameter is specified per line. The equal sign between name and
476 value is optional. Whitespace is insignificant and blank lines are
477 ignored. Hash marks (<literal>#</literal>) introduce comments
478 anywhere. Parameter values that are not simple identifiers or
479 numbers should be single-quoted.
484 <primary>SIGHUP</primary>
486 The configuration file is reread whenever the
487 <command>postmaster</command> process receives a
488 <systemitem>SIGHUP</> signal (which is most easily sent by means
489 of <literal>pg_ctl reload</>). The <command>postmaster</command>
490 also propagates this signal to all currently running server
491 processes so that existing sessions also get the new
492 value. Alternatively, you can send the signal to a single server
493 process directly. Some parameters can only be set at server start;
494 any changes to their entries in the configuration file will be ignored
495 until the server is restarted.
499 A second way to set these configuration parameters is to give them
500 as a command line option to the <command>postmaster</command>, such as:
502 postmaster -c log_connections=yes -c log_destination='syslog'
504 Command-line options override any conflicting settings in
505 <filename>postgresql.conf</filename>.
509 Occasionally it is also useful to give a command line option to
510 one particular session only. The environment variable
511 <envar>PGOPTIONS</envar> can be used for this purpose on the
514 env PGOPTIONS='-c geqo=off' psql
516 (This works for any <application>libpq</>-based client application, not just
517 <application>psql</application>.) Note that this won't work for
518 parameters that are fixed when the server is started, such as the port
523 Furthermore, it is possible to assign a set of option settings to
524 a user or a database. Whenever a session is started, the default
525 settings for the user and database involved are loaded. The
526 commands <xref linkend="sql-alterdatabase"
527 endterm="sql-alterdatabase-title"> and <xref
528 linkend="sql-alteruser" endterm="sql-alteruser-title">,
529 respectively, are used to configure these settings. Per-database
530 settings override anything received from the
531 <command>postmaster</command> command-line or the configuration
532 file, and in turn are overridden by per-user settings; both are
533 overridden by per-session options.
537 Some parameters can be changed in individual <acronym>SQL</acronym>
538 sessions with the <xref linkend="SQL-SET" endterm="SQL-SET-title">
539 command, for example:
541 SET ENABLE_SEQSCAN TO OFF;
543 If <command>SET</> is allowed, it overrides all other sources of
544 values for the parameter. Some parameters cannot be changed via
545 <command>SET</command>: for example, if they control behavior that
546 cannot reasonably be changed without restarting
547 <productname>PostgreSQL</productname>. Also, some parameters can
548 be modified via <command>SET</command> by superusers, but not by
553 The <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title">
554 command allows inspection of the current values of all parameters.
558 The virtual table <structname>pg_settings</structname>
559 (described in <xref linkend="view-pg-settings">) also allows
560 displaying and updating session run-time parameters. It is equivalent
561 to <command>SHOW</> and <command>SET</>, but can be more convenient
562 to use because it can be joined with other tables, or selected from using
563 any desired selection condition.
566 <sect2 id="runtime-config-configuration-files">
567 <title>Configuration Files</title>
571 <varlistentry id="guc-pgdata" xreflabel="pgdata">
572 <term><varname>pgdata</varname> (<type>string</type>)</term>
575 Specifies the directory to use for data storage (everything except
576 configuration files).
581 <varlistentry id="guc-hba-conf" xreflabel="hba-conf">
582 <term><varname>hba_conf</varname> (<type>string</type>)</term>
585 Specifies the file name to use for configuration of host-based
586 authentication (HBA).
591 <varlistentry id="guc-ident-conf" xreflabel="ident-conf">
592 <term><varname>ident_conf</varname> (<type>string</type>)</term>
595 Specifies the file name to use for configuration of
596 <application>ident</> authentication.
601 <varlistentry id="external-pidfile" xreflabel="external-pidfile">
602 <term><varname>external_pidfile</varname> (<type>string</type>)</term>
605 Specifies the location of an additional <application>postmaster</>
606 process-id (PID) file for use by server administration programs.
614 <sect2 id="runtime-config-connection">
615 <title>Connections and Authentication</title>
617 <sect3 id="runtime-config-connection-settings">
618 <title>Connection Settings</title>
622 <varlistentry id="guc-listen-addresses" xreflabel="listen_addresses">
623 <term><varname>listen_addresses</varname> (<type>string</type>)</term>
626 Specifies the TCP/IP address(es) on which the server is
627 to listen for connections from client applications.
628 The value takes the form of a space-separated list of host names
629 and/or numeric IP addresses. The special entry <literal>*</>
630 corresponds to all available IP interfaces.
631 If the list is empty, the server does not listen on any IP interface
632 at all, in which case only Unix-domain sockets can be used to connect
634 The default value is <systemitem class="systemname">localhost</>,
635 which allows only local <quote>loopback</> connections to be made.
636 This parameter can only be set at server start.
641 <varlistentry id="guc-port" xreflabel="port">
642 <term><varname>port</varname> (<type>integer</type>)</term>
643 <indexterm><primary>port</></>
646 The TCP port the server listens on; 5432 by default. Note that the
647 same port number is used for all IP addresses the server listens on.
648 This parameter can only be set at server start.
653 <varlistentry id="guc-max-connections" xreflabel="max_connections">
654 <term><varname>max_connections</varname> (<type>integer</type>)</term>
657 Determines the maximum number of concurrent connections to the
658 database server. The default is typically 100, but may be less
659 if your kernel settings will not support it (as determined
660 during <application>initdb</>). This parameter can only be
665 Increasing this parameter may cause <productname>PostgreSQL</>
666 to request more <systemitem class="osname">System V</> shared
667 memory or semaphores than your operating system's default configuration
668 allows. See <xref linkend="sysvipc"> for information on how to
669 adjust these parameters, if necessary.
674 <varlistentry id="guc-superuser-reserved-connections"
675 xreflabel="superuser_reserved_connections">
676 <term><varname>superuser_reserved_connections</varname>
677 (<type>integer</type>)</term>
680 Determines the number of <quote>connection slots</quote> that
681 are reserved for connections by <productname>PostgreSQL</>
682 superusers. At most <xref linkend="guc-max-connections">
683 connections can ever be active simultaneously. Whenever the
684 number of active concurrent connections is at least
685 <varname>max_connections</> minus
686 <varname>superuser_reserved_connections</varname>, new
687 connections will be accepted only for superusers.
691 The default value is 2. The value must be less than the value of
692 <varname>max_connections</varname>. This parameter can only be
699 <varlistentry id="guc-unix-socket-directory" xreflabel="unix_socket_directory">
700 <term><varname>unix_socket_directory</varname> (<type>string</type>)</term>
703 Specifies the directory of the Unix-domain socket on which the
704 server is to listen for
705 connections from client applications. The default is normally
706 <filename>/tmp</filename>, but can be changed at build time.
707 This parameter can only be set at server start.
712 <varlistentry id="guc-unix-socket-group" xreflabel="unix_socket_group">
713 <term><varname>unix_socket_group</varname> (<type>string</type>)</term>
716 Sets the group owner of the Unix domain socket. (The owning
717 user of the socket is always the user that starts the
718 server.) In combination with the option
719 <varname>unix_socket_permissions</varname> this can be used as
720 an additional access control mechanism for this socket type.
721 By default this is the empty string, which uses the default
722 group for the current user. This option can only be set at
728 <varlistentry id="guc-unix-socket-permissions" xreflabel="unix_socket_permissions">
729 <term><varname>unix_socket_permissions</varname> (<type>integer</type>)</term>
732 Sets the access permissions of the Unix domain socket. Unix
733 domain sockets use the usual Unix file system permission set.
734 The option value is expected to be an numeric mode
735 specification in the form accepted by the
736 <function>chmod</function> and <function>umask</function>
737 system calls. (To use the customary octal format the number
738 must start with a <literal>0</literal> (zero).)
742 The default permissions are <literal>0777</literal>, meaning
743 anyone can connect. Reasonable alternatives are
744 <literal>0770</literal> (only user and group, see also under
745 <varname>unix_socket_group</varname>) and <literal>0700</literal>
746 (only user). (Note that actually for a Unix domain socket, only write
747 permission matters and there is no point in setting or revoking
748 read or execute permissions.)
752 This access control mechanism is independent of the one
753 described in <xref linkend="client-authentication">.
757 This option can only be set at server start.
762 <varlistentry id="guc-rendezvous-name" xreflabel="rendezvous_name">
763 <term><varname>rendezvous_name</varname> (<type>string</type>)</term>
766 Specifies the <productname>Rendezvous</productname> broadcast
767 name. By default, the computer name is used, specified as an
768 empty string ''. This option is only meaningful on platforms
769 that support <productname>Rendezvous</productname>. This
770 option can only be set at server start.
777 <sect3 id="runtime-config-connection-security">
778 <title>Security and Authentication</title>
781 <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
782 <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
783 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
784 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
787 Maximum time to complete client authentication, in seconds. If a
788 would-be client has not completed the authentication protocol in
789 this much time, the server breaks the connection. This prevents
790 hung clients from occupying a connection indefinitely. This
791 option can only be set at server start or in the
792 <filename>postgresql.conf</filename> file. The default is 60.
797 <varlistentry id="guc-ssl" xreflabel="ssl">
799 <primary>SSL</primary>
801 <term><varname>ssl</varname> (<type>boolean</type>)</term>
804 Enables <acronym>SSL</> connections. Please read
805 <xref linkend="ssl-tcp"> before using this. The default
811 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
812 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
815 When a password is specified in <xref
816 linkend="sql-createuser" endterm="sql-createuser-title"> or
817 <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
818 without writing either <literal>ENCRYPTED</> or
819 <literal>UNENCRYPTED</>, this option determines whether the
820 password is to be encrypted. The default is on (encrypt the
826 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
827 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
830 Sets the location of the Kerberos server key file. See
831 <xref linkend="kerberos-auth"> for details.
836 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
837 <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
840 This allows per-database user names. It is off by default.
844 If this is on, you should create users as <literal>username@dbname</>.
845 When <literal>username</> is passed by a connecting client,
846 <literal>@</> and the database name is appended to the user
847 name and that database-specific user name is looked up by the
848 server. Note that when you create users with names containing
849 <literal>@</> within the SQL environment, you will need to
854 With this option enabled, you can still create ordinary global
855 users. Simply append <literal>@</> when specifying the user
856 name in the client. The <literal>@</> will be stripped off
857 before the user name is looked up by the server.
862 This feature is intended as a temporary measure until a
863 complete solution is found. At that time, this option will
874 <sect2 id="runtime-config-resource">
875 <title>Resource Consumption</title>
877 <sect3 id="runtime-config-resource-memory">
878 <title>Memory</title>
881 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
882 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
885 Sets the number of shared memory buffers used by the database
886 server. The default is typically 1000, but may be less if your
887 kernel settings will not support it (as determined during
888 <application>initdb</>). Each buffer is 8192 bytes, unless a
889 different value of <symbol>BLCKSZ</symbol> was chosen when building
890 the server. This setting must be at least 16, as well as at
891 least twice the value of <xref linkend="guc-max-connections">;
892 however, settings significantly higher than the minimum are
893 usually needed for good performance. Values of a few thousand
894 are recommended for production installations. This option can
895 only be set at server start.
899 Increasing this parameter may cause <productname>PostgreSQL</>
900 to request more <systemitem class="osname">System V</> shared
901 memory than your operating system's default configuration
902 allows. See <xref linkend="sysvipc"> for information on how to
903 adjust these parameters, if necessary.
908 <varlistentry id="guc-work-mem" xreflabel="work_mem">
909 <term><varname>work_mem</varname> (<type>integer</type>)</term>
912 Specifies the amount of memory to be used by internal sort operations
913 and hash tables before switching to temporary disk files. The value is
914 specified in kilobytes, and defaults to 1024 kilobytes (1 MB).
915 Note that for a complex query, several sort or hash operations might be
916 running in parallel; each one will be allowed to use as much memory
917 as this value specifies before it starts to put data into temporary
918 files. Also, several running sessions could be doing such operations
919 concurrently. So the total memory used could be many
920 times the value of <varname>work_mem</varname>; it is necessary to
921 keep this fact in mind when choosing the value. Sort operations are
922 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
924 Hash tables are used in hash joins, hash-based aggregation, and
925 hash-based processing of <literal>IN</> subqueries.
930 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
931 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
934 Specifies the maximum amount of memory to be used in maintenance
935 operations, such as <command>VACUUM</command>, <command>CREATE
936 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>.
937 The value is specified in kilobytes, and defaults to 16384 kilobytes
938 (16 MB). Since only one of these operations can be executed at
939 a time by a database session, and an installation normally doesn't
940 have very many of them happening concurrently, it's safe to set this
941 value significantly larger than <varname>work_mem</varname>. Larger
942 settings may improve performance for vacuuming and for restoring
948 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
949 <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
952 Specifies the maximum safe depth of the server's execution stack.
953 The ideal setting for this parameter is the actual stack size limit
954 enforced by the kernel (as set by <literal>ulimit -s</> or local
955 equivalent), less a safety margin of a megabyte or so. The safety
956 margin is needed because the stack depth is not checked in every
957 routine in the server, but only in key potentially-recursive routines
958 such as expression evaluation. Setting the parameter higher than
959 the actual kernel limit will mean that a runaway recursive function
960 can crash an individual backend process. The default setting is
961 2048 KB (two megabytes), which is conservatively small and unlikely
962 to risk crashes. However, it may be too small to allow execution
963 of complex functions.
970 <sect3 id="runtime-config-resource-fsm">
971 <title>Free Space Map</title>
974 <varlistentry id="guc-max-fsm-pages" xreflabel="max_fsm_pages">
975 <term><varname>max_fsm_pages</varname> (<type>integer</type>)</term>
978 Sets the maximum number of disk pages for which free space will
979 be tracked in the shared free-space map. Six bytes of shared memory
980 are consumed for each page slot. This setting must be more than
981 16 * <varname>max_fsm_relations</varname>. The default is 20000.
982 This option can only be set at server start.
987 <varlistentry id="guc-max-fsm-relations" xreflabel="max_fsm_relations">
988 <term><varname>max_fsm_relations</varname> (<type>integer</type>)</term>
991 Sets the maximum number of relations (tables and indexes) for which
992 free space will be tracked in the shared free-space map. Roughly
993 fifty bytes of shared memory are consumed for each slot.
995 This option can only be set at server start.
1002 <sect3 id="runtime-config-resource-kernel">
1003 <title>Kernel Resource Usage</title>
1006 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
1007 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
1010 Sets the maximum number of simultaneously open files allowed to each
1011 server subprocess. The default is 1000. If the kernel is enforcing
1012 a safe per-process limit, you don't need to worry about this setting.
1013 But on some platforms (notably, most BSD systems), the kernel will
1014 allow individual processes to open many more files than the system
1015 can really support when a large number of processes all try to open
1016 that many files. If you find yourself seeing <quote>Too many open
1017 files</> failures, try reducing this setting.
1018 This option can only be set at server start.
1023 <varlistentry id="guc-preload-libraries" xreflabel="preload_libraries">
1024 <term><varname>preload_libraries</varname> (<type>string</type>)</term>
1025 <indexterm><primary>preload_libraries</></>
1028 This variable specifies one or more shared libraries that are
1029 to be preloaded at server start. A parameterless
1030 initialization function can optionally be called for each
1031 library. To specify that, add a colon and the name of the
1032 initialization function after the library name. For example
1033 <literal>'$libdir/mylib:mylib_init'</literal> would cause
1034 <literal>mylib</> to be preloaded and <literal>mylib_init</>
1035 to be executed. If more than one library is to be loaded,
1036 separate their names with commas.
1040 If <literal>mylib</> or <literal>mylib_init</> are not found, the
1041 server will fail to start.
1045 <productname>PostgreSQL</productname> procedural language
1046 libraries may be preloaded in this way, typically by using the
1047 syntax <literal>'$libdir/plXXX:plXXX_init'</literal> where
1048 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
1049 <literal>tcl</>, or <literal>python</>.
1053 By preloading a shared library (and initializing it if
1054 applicable), the library startup time is avoided when the
1055 library is first used. However, the time to start each new
1056 server process may increase, even if that process never
1065 <sect3 id="runtime-config-resource-vacuum-cost">
1066 <title>Cost-Based Vacuum Delay</title>
1069 During the execution of <xref linkend="sql-vacuum"
1070 endterm="sql-vacuum-title"> and <xref linkend="sql-analyze"
1071 endterm="sql-analyze-title"> commands, the system maintains an
1072 internal counter that keeps track of the estimated cost of the
1073 various I/O operations that are performed. When the accumulated
1074 cost reaches a limit (specified by
1075 <varname>vacuum_cost_limit</varname>), the process performing
1076 the operation will sleep for a while (specified by
1077 <varname>vacuum_cost_naptime</varname>). Then it will reset the
1078 counter and continue execution.
1082 The intent of this feature is to allow administrators to reduce
1083 the I/O impact of these commands on concurrent database
1084 activity. There are some situations in which it is not very
1085 important that maintenance commands like
1086 <command>VACUUM</command> and <command>ANALYZE</command> finish
1087 quickly; however, it is usually very important that these
1088 commands do not significantly interfere with the ability of the
1089 system to perform other database operations. Cost-based vacuum
1090 delay provides a way for administrators to achieve this.
1094 This feature is disabled by default. To enable it, set the
1095 <varname>vacuum_cost_naptime</varname> variable to a nonzero
1100 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1101 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
1104 The estimated cost for vacuuming a buffer found in the shared buffer
1105 cache. It represents the cost to lock the buffer pool, lookup
1106 the shared hash table and scan the content of the page. The
1112 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1113 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
1116 The estimated cost for vacuuming a buffer that has to be read from
1117 disk. This represents the effort to lock the buffer pool,
1118 lookup the shared hash table, read the desired block in from
1119 the disk and scan its content. The default value is 10.
1124 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1125 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
1128 The estimated cost charged when vacuum modifies a block that was
1129 previously clean. It represents the extra I/O required to
1130 flush the dirty block out to disk again. The default value is
1136 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1137 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
1140 The accumulated cost that will cause the vacuuming process to briefly
1141 nap. The default value is 200.
1146 <varlistentry id="guc-vacuum-cost-naptime" xreflabel="vacuum_cost_naptime">
1147 <term><varname>vacuum_cost_naptime</varname> (<type>integer</type>)</term>
1150 The length of time, in milliseconds, that the process will nap
1151 when the cost limit has been exceeded.
1152 The default value is 0, which disables the cost-based vacuum
1153 delay feature. Positive values enable cost-based vacuuming.
1154 Note however that on many systems, the effective resolution
1155 of sleep delays is 10 milliseconds; setting
1156 <varname>vacuum_cost_naptime</varname> to a value that is
1157 not a multiple of 10 may have the same results as setting it
1158 to the next higher multiple of 10.
1166 There are certain bulk operations that hold critical locks and should
1167 therefore complete as quickly as possible. Cost-based vacuum
1168 delays do not occur during such operations. Therefore it is
1169 possible that the cost accumulates far higher than the specified
1170 limit. To avoid uselessly long delays in such cases, the actual
1171 naptime is calculated as <varname>vacuum_cost_naptime</varname> *
1172 <varname>accumulated_balance</varname> /
1173 <varname>vacuum_cost_limit</varname> with a maximum of
1174 <varname>vacuum_cost_naptime</varname> * 4.
1181 <sect2 id="runtime-config-wal">
1182 <title>Write Ahead Log</title>
1185 See also <xref linkend="wal-configuration"> for details on WAL
1189 <sect3 id="runtime-config-wal-settings">
1190 <title>Settings</title>
1193 <varlistentry id="guc-fsync" xreflabel="fsync">
1195 <primary>fsync</primary>
1198 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1201 If this option is on, the <productname>PostgreSQL</> server
1202 will use the <function>fsync()</> system call in several places
1203 to make sure that updates are physically written to disk. This
1204 insures that a database cluster will recover to a
1205 consistent state after an operating system or hardware crash.
1206 (Crashes of the database server itself are <emphasis>not</>
1211 However, using <function>fsync()</function> results in a
1212 performance penalty: when a transaction is committed,
1213 <productname>PostgreSQL</productname> must wait for the
1214 operating system to flush the write-ahead log to disk. When
1215 <varname>fsync</varname> is disabled, the operating system is
1216 allowed to do its best in buffering, ordering, and delaying
1217 writes. This can result in significantly improved performance.
1218 However, if the system crashes, the results of the last few
1219 committed transactions may be lost in part or whole. In the
1220 worst case, unrecoverable data corruption may occur.
1224 Due to the risks involved, there is no universally correct
1225 setting for <varname>fsync</varname>. Some administrators
1226 always disable <varname>fsync</varname>, while others only
1227 turn it off for bulk loads, where there is a clear restart
1228 point if something goes wrong, whereas some administrators
1229 always leave <varname>fsync</varname> enabled. The default is
1230 to enable <varname>fsync</varname>, for maximum reliability.
1231 If you trust your operating system, your hardware, and your
1232 utility company (or your battery backup), you can consider
1233 disabling <varname>fsync</varname>.
1237 This option can only be set at server start or in the
1238 <filename>postgresql.conf</filename> file.
1243 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1244 <term><varname>wal_sync_method</varname> (<type>string</type>)</term>
1247 Method used for forcing WAL updates out to disk. Possible
1249 <literal>fsync</> (call <function>fsync()</> at each commit),
1250 <literal>fdatasync</> (call <function>fdatasync()</> at each commit),
1251 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>), and
1252 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>).
1253 Not all of these choices are available on all platforms.
1254 This option can only be set at server start or in the
1255 <filename>postgresql.conf</filename> file.
1260 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1261 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1264 Number of disk-page buffers in shared memory for WAL
1265 logging. The default is 8. This option can only be set at
1273 <sect3 id="runtime-config-wal-checkpoints">
1274 <title>Checkpoints</title>
1277 <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1278 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1281 Maximum distance between automatic WAL checkpoints, in log
1282 file segments (each segment is normally 16 megabytes). The
1283 default is three. This option can only be set at server start
1284 or in the <filename>postgresql.conf</filename> file.
1289 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1290 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1293 Maximum time between automatic WAL checkpoints, in
1294 seconds. The default is 300 seconds. This option can only be
1295 set at server start or in the <filename>postgresql.conf</>
1301 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1302 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1305 Write a message to the server logs if checkpoints caused by
1306 the filling of checkpoint segment files happens more
1307 frequently than this number of seconds. The default is 30
1308 seconds. Zero turns off the warning.
1314 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
1315 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1318 Time delay between writing a commit record to the WAL buffer
1319 and flushing the buffer out to disk, in microseconds. A
1320 nonzero delay allows multiple transactions to be committed
1321 with only one <function>fsync()</function> system call, if
1322 system load is high enough additional transactions may become
1323 ready to commit within the given interval. But the delay is
1324 just wasted if no other transactions become ready to
1325 commit. Therefore, the delay is only performed if at least
1326 <varname>commit_siblings</varname> other transactions are
1327 active at the instant that a server process has written its
1328 commit record. The default is zero (no delay).
1333 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
1334 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1337 Minimum number of concurrent open transactions to require
1338 before performing the <varname>commit_delay</> delay. A larger
1339 value makes it more probable that at least one other
1340 transaction will become ready to commit during the delay
1341 interval. The default is five.
1350 <sect2 id="runtime-config-query">
1351 <title>Query Planning</title>
1353 <sect3 id="runtime-config-query-enable">
1354 <title>Planner Method Configuration</title>
1358 These configuration parameters provide a crude method for
1359 influencing the query plans chosen by the query optimizer. If
1360 the default plan chosen by the optimizer for a particular query
1361 is not optimal, a temporary solution may be found by using one
1362 of these configuration parameters to force the optimizer to
1363 choose a better plan. Other ways to improve the quality of the
1364 plans chosen by the optimizer include configuring the <xref
1365 linkend="runtime-config-query-constants"
1366 endterm="runtime-config-query-constants-title">, running <xref
1367 linkend="sql-analyze" endterm="sql-analyze-title"> more
1368 frequently, increasing the value of the <xref
1369 linkend="guc-default-statistics-target"> configuration parameter,
1370 and increasing the amount of statistics collected for a
1371 particular column using <command>ALTER TABLE SET
1372 STATISTICS</command>.
1377 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
1378 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
1381 Enables or disables the query planner's use of hashed
1382 aggregation plan types. The default is on. This is used for
1383 debugging the query planner.
1388 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
1389 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
1392 Enables or disables the query planner's use of hash-join plan
1393 types. The default is on. This is used for debugging the query
1399 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
1401 <primary>index scan</primary>
1404 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
1407 Enables or disables the query planner's use of index-scan plan
1408 types. The default is on. This is used for debugging the query
1414 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
1415 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
1418 Enables or disables the query planner's use of merge-join plan
1419 types. The default is on. This is used for debugging the query
1425 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
1426 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
1429 Enables or disables the query planner's use of nested-loop join
1430 plans. It's not possible to suppress nested-loop joins entirely,
1431 but turning this variable off discourages the planner from using
1432 one if there are other methods available. The default is
1433 on. This is used for debugging the query planner.
1438 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
1440 <primary>sequential scan</primary>
1443 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
1446 Enables or disables the query planner's use of sequential scan
1447 plan types. It's not possible to suppress sequential scans
1448 entirely, but turning this variable off discourages the planner
1449 from using one if there are other methods available. The
1450 default is on. This is used for debugging the query planner.
1455 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
1456 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
1459 Enables or disables the query planner's use of explicit sort
1460 steps. It's not possible to suppress explicit sorts entirely,
1461 but turning this variable off discourages the planner from
1462 using one if there are other methods available. The default
1463 is on. This is used for debugging the query planner.
1468 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
1469 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
1472 Enables or disables the query planner's use of <acronym>TID</>
1473 scan plan types. The default is on. This is used for debugging
1481 <sect3 id="runtime-config-query-constants">
1482 <title id="runtime-config-query-constants-title">
1483 Planner Cost Constants
1488 Unfortunately, there is no well-defined method for determining
1489 ideal values for the family of <quote>cost</quote> variables that
1490 appear below. You are encouraged to experiment and share
1497 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
1498 <term><varname>effective_cache_size</varname> (<type>floating point</type>)</term>
1501 Sets the planner's assumption about the effective size of the
1502 disk cache (that is, the portion of the kernel's disk cache
1503 that will be used for <productname>PostgreSQL</productname>
1504 data files). This is measured in disk pages, which are
1505 normally 8192 bytes each. The default is 1000.
1510 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
1511 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
1514 Sets the query planner's estimate of the cost of a
1515 nonsequentially fetched disk page. This is measured as a
1516 multiple of the cost of a sequential page fetch. A higher
1517 value makes it more likely a sequential scan will be used, a
1518 lower value makes it more likely an index scan will be
1519 used. The default is four.
1524 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
1525 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
1528 Sets the query planner's estimate of the cost of processing
1529 each row during a query. This is measured as a fraction of
1530 the cost of a sequential page fetch. The default is 0.01.
1535 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
1536 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
1539 Sets the query planner's estimate of the cost of processing
1540 each index row during an index scan. This is measured as a
1541 fraction of the cost of a sequential page fetch. The default
1547 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
1548 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
1551 Sets the planner's estimate of the cost of processing each
1552 operator in a <literal>WHERE</> clause. This is measured as a fraction of
1553 the cost of a sequential page fetch. The default is 0.0025.
1561 <sect3 id="runtime-config-query-geqo">
1562 <title>Genetic Query Optimizer</title>
1566 <varlistentry id="guc-geqo" xreflabel="geqo">
1568 <primary>genetic query optimization</primary>
1571 <primary>GEQO</primary>
1572 <see>genetic query optimization</see>
1574 <term><varname>geqo</varname> (<type>boolean</type>)</term>
1577 Enables or disables genetic query optimization, which is an
1578 algorithm that attempts to do query planning without
1579 exhaustive searching. This is on by default. The
1580 <varname>geqo_threshold</varname> variable provides a more
1581 granular way to disable GEQO for certain classes of queries.
1586 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
1587 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
1590 Use genetic query optimization to plan queries with at least
1591 this many <literal>FROM</> items involved. (Note that an outer
1592 <literal>JOIN</> construct counts as only one <literal>FROM</>
1593 item.) The default is 12. For simpler queries it is usually best
1594 to use the deterministic, exhaustive planner, but for queries with
1595 many tables the deterministic planner takes too long.
1600 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
1601 <term><varname>geqo_effort</varname>
1602 (<type>integer</type>)</term>
1605 Controls the tradeoff between planning time and query plan
1606 efficiency in GEQO. This variable must be an integer in the
1607 range from 1 to 10. The default value is 5. Larger values
1608 increase the time spent doing query planning, but also
1609 increase the likelyhood that an efficient query plan will be
1614 <varname>geqo_effort</varname> doesn't actually do anything
1615 directly; it is only used to compute the default values for
1616 the other variables that influence GEQO behavior (described
1617 below). If you prefer, you can set the other parameters by
1623 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
1624 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
1627 Controls the pool size used by GEQO. The pool size is the
1628 number of individuals in the genetic population. It must be
1629 at least two, and useful values are typically 100 to 1000. If
1630 it is set to zero (the default setting) then a suitable
1631 default is chosen based on <varname>geqo_effort</varname> and
1632 the number of tables in the query.
1637 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
1638 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
1641 Controls the number of generations used by GEQO. Generations
1642 specifies the number of iterations of the algorithm. It must
1643 be at least one, and useful values are in the same range as
1644 the pool size. If it is set to zero (the default setting)
1645 then a suitable default is chosen based on
1646 <varname>geqo_pool_size</varname>.
1651 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
1652 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
1655 Controls the selection bias used by GEQO. The selection bias
1656 is the selective pressure within the population. Values can be
1657 from 1.50 to 2.00; the latter is the default.
1664 <sect3 id="runtime-config-query-other">
1665 <title>Other Planner Options</title>
1669 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
1670 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
1673 Sets the default statistics target for table columns that have
1674 not had a column-specific target set via <command>ALTER TABLE
1675 SET STATISTICS</>. Larger values increase the time needed to
1676 do <command>ANALYZE</>, but may improve the quality of the
1677 planner's estimates. The default is 10. For more information
1678 on the use of statistics by the <productname>PostgreSQL</>
1679 query planner, refer to <xref linkend="planner-stats">.
1684 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
1685 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
1688 The planner will merge sub-queries into upper queries if the
1689 resulting <literal>FROM</literal> list would have no more than
1690 this many items. Smaller values reduce planning time but may
1691 yield inferior query plans. The default is 8. It is usually
1692 wise to keep this less than <xref linkend="guc-geqo-threshold">.
1697 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
1698 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
1701 The planner will rewrite explicit inner <literal>JOIN</>
1702 constructs into lists of <literal>FROM</> items whenever a
1703 list of no more than this many items in total would
1704 result. Prior to <productname>PostgreSQL</> 7.4, joins
1705 specified via the <literal>JOIN</literal> construct would
1706 never be reordered by the query planner. The query planner has
1707 subsequently been improved so that inner joins written in this
1708 form can be reordered; this configuration variable controls
1709 the extent to which this reordering is performed.
1712 At present, the order of outer joins specified via the
1713 <literal>JOIN</> construct is never adjusted by the query
1714 planner; therefore, <varname>join_collapse_limit</> has no
1715 effect on this behavior. The planner may be improved to
1716 reorder some classes of outer joins in a future release of
1717 <productname>PostgreSQL</productname>.
1723 By default, this variable is set to
1724 <varname>from_collapse_limit</varname>, which is appropriate
1725 for most uses. Setting it to 1 prevents any reordering of
1726 inner <literal>JOIN</>s. Thus, the explicit join order
1727 specified in the query will be the actual order in which the
1728 relations are joined. The query planner does not always choose
1729 the optimal join order; advanced users may elect to
1730 temporarily set this variable to 1, and then specify the join
1731 order they desire explicitly. Another consequence of setting
1732 this variable to 1 is that the query planner will behave more
1733 like the <productname>PostgreSQL</productname> 7.3 query
1734 planner, which some users might find useful for backward
1735 compatibility reasons.
1739 Setting this variable to a value between 1 and
1740 <varname>from_collapse_limit</varname> might be useful to
1741 trade off planning time against the quality of the chosen plan
1742 (higher values produce better plans).
1751 <sect2 id="runtime-config-logging">
1752 <title>Error Reporting and Logging</title>
1754 <indexterm zone="runtime-config-logging">
1755 <primary>server log</primary>
1758 <sect3 id="runtime-config-logging-where">
1759 <title>Where to log</title>
1761 <indexterm zone="runtime-config-logging-where">
1762 <primary>where to log</primary>
1767 <varlistentry id="guc-log-destination" xreflabel="log_destination">
1768 <term><varname>log_destination</varname> (<type>string</type>)</term>
1771 <productname>PostgreSQL</productname> supports several methods
1772 for loggning, including <systemitem>stderr</systemitem> and
1773 <systemitem>syslog</systemitem>. On Windows,
1774 <systemitem>eventlog</systemitem> is also supported. Set this
1775 option to a list of desired log destinations separated by a
1776 comma. The default is to log to <systemitem>stderr</systemitem>
1777 only. This option must be set at server start.
1782 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
1783 <term><varname>syslog_facility</varname> (<type>string</type>)</term>
1786 This option determines the <application>syslog</application>
1787 <quote>facility</quote> to be used when logging via
1788 <application>syslog</application> is enabled. You may choose
1789 from <literal>LOCAL0</>, <literal>LOCAL1</>,
1790 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
1791 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
1792 the default is <literal>LOCAL0</>. See also the
1793 documentation of your system's
1794 <application>syslog</application>.
1799 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
1800 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
1803 If logging to <application>syslog</> is enabled, this option
1804 determines the program name used to identify
1805 <productname>PostgreSQL</productname> messages in
1806 <application>syslog</application> log messages. The default is
1807 <literal>postgres</literal>.
1814 <sect3 id="runtime-config-logging-when">
1815 <title>When To Log</title>
1819 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
1820 <term><varname>client_min_messages</varname> (<type>string</type>)</term>
1823 Controls which message levels are sent to the client.
1824 Valid values are <literal>DEBUG5</>,
1825 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
1826 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
1827 <literal>WARNING</>, and <literal>ERROR</>. Each level
1828 includes all the levels that follow it. The later the level,
1829 the fewer messages are sent. The default is
1830 <literal>NOTICE</>. Note that <literal>LOG</> has a different
1831 rank here than in <varname>log_min_messages</>.
1836 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
1837 <term><varname>log_min_messages</varname> (<type>string</type>)</term>
1840 Controls which message levels are written to the server log.
1841 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
1842 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
1843 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
1844 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
1845 <literal>PANIC</>. Each level includes all the levels that
1846 follow it. The later the level, the fewer messages are sent
1847 to the log. The default is <literal>NOTICE</>. Note that
1848 <literal>LOG</> has a different rank here than in
1849 <varname>client_min_messages</>.
1850 Only superusers can increase this option.
1855 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
1856 <term><varname>log_error_verbosity</varname> (<type>string</type>)</term>
1859 Controls the amount of detail written in the server log for each
1860 message that is logged. Valid values are <literal>TERSE</>,
1861 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
1862 fields to displayed messages.
1867 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
1868 <term><varname>log_min_error_statement</varname> (<type>string</type>)</term>
1871 Controls whether or not the SQL statement that causes an error
1872 condition will also be recorded in the server log. All SQL
1873 statements that cause an error of the specified level, or a
1874 higher level, are logged. The default is
1875 <literal>PANIC</literal> (effectively turning this feature
1876 off for normal use). Valid values are <literal>DEBUG5</literal>,
1877 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
1878 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
1879 <literal>INFO</literal>, <literal>NOTICE</literal>,
1880 <literal>WARNING</literal>, <literal>ERROR</literal>,
1881 <literal>FATAL</literal>, and <literal>PANIC</literal>. For
1882 example, if you set this to <literal>ERROR</literal> then all
1883 SQL statements causing errors, fatal errors, or panics will be
1884 logged. Enabling this option can be helpful in tracking down
1885 the source of any errors that appear in the server log.
1886 Only superusers can increase this option.
1891 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
1892 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
1895 Sets a minimum statement execution time (in milliseconds)
1896 for statement to be logged. All SQL statements
1897 that run in the time specified or longer will be logged with
1898 their duration. Setting this to zero will print
1899 all queries and their durations. Minus-one (the default)
1900 disables this. For example, if you set it to
1901 <literal>250</literal> then all SQL statements that run 250ms
1902 or longer will be logged. Enabling this option can be
1903 useful in tracking down unoptimized queries in your applications.
1904 Only superusers can increase this or set it to minus-one if this
1905 option is set by the administrator.
1910 <varlistentry id="guc-silent-mode" xreflabel="silent_mode">
1911 <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
1914 Runs the server silently. If this option is set, the server
1915 will automatically run in background and any controlling
1916 terminals are disassociated. Thus, no messages are written to
1917 standard output or standard error (same effect as
1918 <command>postmaster</>'s <option>-S</option> option). Unless
1919 <application>syslog</> logging is enabled, using this option
1920 is discouraged because it makes it impossible to see error
1929 Here is a list of the various message severity levels used in
1933 <term><literal>DEBUG[1-5]</literal></term>
1936 Provides information for use by developers.
1942 <term><literal>INFO</literal></term>
1945 Provides information implicitly requested by the user,
1946 e.g., during <command>VACUUM VERBOSE</>.
1952 <term><literal>NOTICE</literal></term>
1955 Provides information that may be helpful to users, e.g.,
1956 truncation of long identifiers and the creation of indexes as part
1963 <term><literal>WARNING</literal></term>
1966 Provides warnings to the user, e.g., <command>COMMIT</>
1967 outside a transaction block.
1973 <term><literal>ERROR</literal></term>
1976 Reports an error that caused the current transaction to abort.
1982 <term><literal>LOG</literal></term>
1985 Reports information of interest to administrators, e.g.,
1986 checkpoint activity.
1992 <term><literal>FATAL</literal></term>
1995 Reports an error that caused the current session to abort.
2001 <term><literal>PANIC</literal></term>
2004 Reports an error that caused all sessions to abort.
2012 <sect3 id="runtime-config-logging-what">
2013 <title>What To Log</title>
2018 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
2019 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
2020 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
2021 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
2024 These options enable various debugging output to be sent to
2025 the client or server log. For each executed query, they print
2026 the resulting parse tree, the query rewriter output, or the
2027 execution plan. <varname>debug_pretty_print</varname> indents
2028 these displays to produce a more readable but much longer
2029 output format. <varname>client_min_messages</varname> or
2030 <varname>log_min_messages</varname> must be
2031 <literal>DEBUG1</literal> or lower to send output to the
2032 client or server logs. These options are off by default.
2037 <varlistentry id="guc-log-connections" xreflabel="log_connections">
2038 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
2041 This outputs a line to the server logs detailing each successful
2042 connection. This is off by default, although it is probably very
2043 useful. This option can only be set at server start or in the
2044 <filename>postgresql.conf</filename> configuration file.
2049 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
2050 <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
2053 This outputs a line in the server logs similar to
2054 <varname>log_connections</varname> but at session termination,
2055 and includes the duration of the session. This is off by
2056 default. This option can only be set at server start or in the
2057 <filename>postgresql.conf</filename> configuration file.
2063 <varlistentry id="guc-log-duration" xreflabel="log_duration">
2064 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
2067 Causes the duration of every completed statement to be logged.
2068 To use this option, it is recommended that you also enable
2069 <varname>log_statement</> and if not using <application>syslog</>
2070 log the PID using <varname>log_line_prefix</> so that you
2071 can link the statement to the duration using the process
2072 ID. The default is off. Only superusers can turn off this
2073 option if it is enabled by the administrator.
2078 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
2079 <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
2082 This is a <function>printf</>-style string that is output at the
2083 beginning of each log line. The default is an empty string.
2084 Each recognized escape is replaced as outlined
2085 below - anything else that looks like an escape is ignored. Other
2086 characters are copied straight to the log line. Some escapes are
2087 only recognised by session processes, and do not apply to
2088 processes without controlling sessions. <application>Syslog</> produces its own
2089 timestamp and process ID information, so you probably do not want to
2090 use those escapes if you are using <application>syslog</>.
2091 This option can only be set at server start or in the
2092 <filename>postgresql.conf</filename> configuration file.
2097 <entry>Escape</entry>
2098 <entry>Effect</entry>
2099 <entry>Session only</entry>
2104 <entry><literal>%u</literal></entry>
2105 <entry>User Name</entry>
2109 <entry><literal>%d</literal></entry>
2110 <entry>Database Name</entry>
2114 <entry><literal>%r</literal></entry>
2115 <entry>Remote Hostname or IP address, and Remote Port</entry>
2119 <entry><literal>%p</literal></entry>
2120 <entry>Process ID</entry>
2124 <entry><literal>%t</literal></entry>
2125 <entry>Timestamp</entry>
2129 <entry><literal>%i</literal></entry>
2130 <entry>Command Tag. This is the command which generated the log
2135 <entry><literal>%c</literal></entry>
2136 <entry>Session ID. A unique identifier for each session.
2137 It is 2 4-byte hexadecimal numbers (without leading zeros)
2138 separated by a dot. The numbers
2139 are the Session Start Time and the Process ID, so this can also
2140 be used as a space saving way of printing these items.</entry>
2144 <entry><literal>%l</literal></entry>
2145 <entry>Number of the log line for each process,
2146 starting at 1</entry>
2150 <entry><literal>%s</literal></entry>
2151 <entry>Session Start Timestamp</entry>
2155 <entry><literal>%x</literal></entry>
2156 <entry>Does not produce any output, but tells non-session
2157 processes to stop at this point in the string. Ignored by
2158 session backends.</entry>
2162 <entry><literal>%%</literal></entry>
2163 <entry>Literal <literal>%</></entry>
2173 <varlistentry id="guc-log-statement" xreflabel="log_statement">
2174 <term><varname>log_statement</varname> (<type>string</type>)</term>
2177 Controls which SQL statement are logged. Valid values are
2178 <literal>all</>, <literal>ddl</>, <literal>mod</>, and
2179 <literal>none</>. <literal>ddl</> logs all data definition
2180 commands like <literal>CREATE</>, <literal>ALTER</>, and
2181 <literal>DROP</> commands. <literal>mod</> logs all
2182 <literal>ddl</> statements, plus <literal>INSERT</>,
2183 <literal>UPDATE</>, <literal>DELETE</>, <literal>TRUNCATE</>,
2184 and <literal>COPY FROM</>. <literal>PREPARE</> and
2185 <literal>EXPLAIN ANALYZE</> statements are also considered for
2186 appropriate commands.
2189 The default is <literal>none</>. Only superusers can reduce
2190 the detail of this option if it has been set by an administrator.
2195 The <command>EXECUTE</command> statement not considered a
2196 <literal>ddl</> or <literal>mod</> statement. When it is logged,
2197 only the name of the prepared statement is reported, not the
2198 actual prepared statement.
2202 When a function is defined in a server-side language like
2203 <application>PL/pgSQL</application>, any queries executed by
2204 the function will only be logged the first time that the
2205 function is invoked in a particular session. This is because
2206 the <application>PL/pgSQL</application> keeps a cache of the
2207 query plans produced for the SQL statements in the function.
2213 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
2214 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
2217 By default, connection logs only show the IP address of the
2218 connecting host. If you want it to show the host name you can
2219 turn this on, but depending on your host name resolution setup
2220 it might impose a non-negligible performance penalty. This
2221 option can only be set at server start.
2230 <sect2 id="runtime-config-statistics">
2231 <title>Runtime Statistics</title>
2233 <sect3 id="runtime-config-statistics-monitor">
2234 <title>Statistics Monitoring</title>
2238 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
2239 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
2240 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
2241 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
2244 For each query, write performance statistics of the respective
2245 module to the server log. This is a crude profiling
2246 instrument. <varname>log_statement_stats</varname> reports total
2247 statement statistics, while the others report per-state statistics.
2248 All of these options are disabled by default.
2249 Only superusers can turn off any of these options if they have
2250 been enabled by the administrator.
2258 <sect3 id="runtime-config-statistics-collector">
2259 <title>Query and Index Statistics Collector</title>
2262 <varlistentry id="guc-stats-start-collector" xreflabel="stats_start_collector">
2263 <term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>
2266 Controls whether the server should start the
2267 statistics-collection subprocess. This is on by default, but
2268 may be turned off if you know you have no interest in
2269 collecting statistics. This option can only be set at server
2275 <varlistentry id="guc-stats-command-string" xreflabel="stats_command_string">
2276 <term><varname>stats_command_string</varname> (<type>boolean</type>)</term>
2279 Enables the collection of statistics on the currently
2280 executing command of each session, along with the time at
2281 which that command began execution. This option is off by
2282 default. Note that even when enabled, this information is not
2283 visible to all users, only to superusers and the user owning
2284 the session being reported on; so it should not represent a
2285 security risk. This data can be accessed via the
2286 <structname>pg_stat_activity</structname> system view; refer
2287 to <xref linkend="monitoring"> for more information.
2292 <varlistentry id="guc-stats-block-level" xreflabel="guc_stats_block_level">
2293 <term><varname>stats_block_level</varname> (<type>boolean</type>)</term>
2296 Enables the collection of block-level statistics on database
2297 activity. This option is disabled by default. If this option
2298 is enabled, the data that is produced can be accessed via the
2299 <structname>pg_stat</structname> and
2300 <structname>pg_statio</structname> family of system views;
2301 refer to <xref linkend="monitoring"> for more information.
2306 <varlistentry id="guc-stats-row-level" xreflabel="guc_stats_row_level">
2307 <term><varname>stats_row_level</varname> (<type>boolean</type>)</term>
2310 Enables the collection of row-level statistics on database
2311 activity. This option is disabled by default. If this option
2312 is enabled, the data that is produced can be accessed via the
2313 <structname>pg_stat</structname> and
2314 <structname>pg_statio</structname> family of system views;
2315 refer to <xref linkend="monitoring"> for more information.
2320 <varlistentry id="guc-stats-reset-on-server-start" xreflabel="stats_reset_on_server_start">
2321 <term><varname>stats_reset_on_server_start</varname> (<type>boolean</type>)</term>
2324 If on, collected statistics are zeroed out whenever the server
2325 is restarted. If off, statistics are accumulated across server
2326 restarts. The default is on. This option can only be set at
2336 <sect2 id="runtime-config-client">
2337 <title>Client Connection Defaults</title>
2339 <sect3 id="runtime-config-client-statement">
2340 <title>Statement Behavior</title>
2343 <varlistentry id="guc-search-path" xreflabel="search_path">
2344 <term><varname>search_path</varname> (<type>string</type>)</term>
2345 <indexterm><primary>search_path</></>
2346 <indexterm><primary>path</><secondary>for schemas</></>
2349 This variable specifies the order in which schemas are searched
2350 when an object (table, data type, function, etc.) is referenced by a
2351 simple name with no schema component. When there are objects of
2352 identical names in different schemas, the one found first
2353 in the search path is used. An object that is not in any of the
2354 schemas in the search path can only be referenced by specifying
2355 its containing schema with a qualified (dotted) name.
2359 The value for <varname>search_path</varname> has to be a comma-separated
2360 list of schema names. If one of the list items is
2361 the special value <literal>$user</literal>, then the schema
2362 having the name returned by <function>SESSION_USER</> is substituted, if there
2363 is such a schema. (If not, <literal>$user</literal> is ignored.)
2367 The system catalog schema, <literal>pg_catalog</>, is always
2368 searched, whether it is mentioned in the path or not. If it is
2369 mentioned in the path then it will be searched in the specified
2370 order. If <literal>pg_catalog</> is not in the path then it will
2371 be searched <emphasis>before</> searching any of the path items.
2372 It should also be noted that the temporary-table schema,
2373 <literal>pg_temp_<replaceable>nnn</></>, is implicitly searched before any of
2378 When objects are created without specifying a particular target
2379 schema, they will be placed in the first schema listed
2380 in the search path. An error is reported if the search path is
2385 The default value for this parameter is
2386 <literal>'$user, public'</literal> (where the second part will be
2387 ignored if there is no schema named <literal>public</>).
2388 This supports shared use of a database (where no users
2389 have private schemas, and all share use of <literal>public</>),
2390 private per-user schemas, and combinations of these. Other
2391 effects can be obtained by altering the default search path
2392 setting, either globally or per-user.
2396 The current effective value of the search path can be examined
2397 via the <acronym>SQL</acronym> function
2398 <function>current_schemas()</>. This is not quite the same as
2399 examining the value of <varname>search_path</varname>, since
2400 <function>current_schemas()</> shows how the requests
2401 appearing in <varname>search_path</varname> were resolved.
2405 For more information on schema handling, see <xref linkend="ddl-schemas">.
2410 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
2411 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
2414 This parameter is normally true. When set to false, it disables
2415 validation of the function body string in <xref
2416 linkend="sql-createfunction"
2417 endterm="sql-createfunction-title">. Disabling validation is
2418 occasionally useful to avoid problems such as forward
2419 references when restoring function definitions from a dump.
2424 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
2426 <primary>transaction isolation level</primary>
2429 <term><varname>default_transaction_isolation</varname> (<type>string</type>)</term>
2432 Each SQL transaction has an isolation level, which can be
2433 either <quote>read uncommitted</quote>, <quote>read
2434 committed</quote>, <quote>repeatable read</quote>, or
2435 <quote>serializable</quote>. This parameter controls the
2436 default isolation level of each new transaction. The default
2437 is <quote>read committed</quote>.
2441 Consult <xref linkend="mvcc"> and <xref linkend="sql-set-transaction"> for more
2447 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
2449 <primary>read-only transaction</primary>
2452 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
2455 A read-only SQL transaction cannot alter non-temporary tables.
2456 This parameter controls the default read-only status of each new
2457 transaction. The default is false (read/write).
2461 Consult <xref linkend="sql-set-transaction"> for more information.
2466 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
2467 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
2470 Aborts any statement that takes over the specified number of
2471 milliseconds. A value of zero turns off the timer, which is
2479 <sect3 id="runtime-config-client-format">
2480 <title>Locale and Formatting</title>
2484 <varlistentry id="guc-datestyle" xreflabel="datestyle">
2485 <term><varname>datestyle</varname> (<type>string</type>)</term>
2486 <indexterm><primary>date style</></>
2489 Sets the display format for date and time values, as well as the
2490 rules for interpreting ambiguous date input values. For
2491 historical reasons, this variable contains two independent
2492 components: the output format specification (<literal>ISO</>,
2493 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
2494 and the input/output specification for year/month/day ordering
2495 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
2496 can be set separately or together. The keywords <literal>Euro</>
2497 and <literal>European</> are synonyms for <literal>DMY</>; the
2498 keywords <literal>US</>, <literal>NonEuro</>, and
2499 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
2500 <xref linkend="datatype-datetime"> for more information. The
2501 default is <literal>ISO, MDY</>.
2506 <varlistentry id="guc-timezone" xreflabel="timezone">
2507 <term><varname>timezone</varname> (<type>string</type>)</term>
2508 <indexterm><primary>time zone</></>
2511 Sets the time zone for displaying and interpreting time
2512 stamps. The default is to use whatever the system environment
2513 specifies as the time zone. See <xref
2514 linkend="datatype-datetime"> for more information.
2519 <varlistentry id="guc-australian-timezones" xreflabel="australian_timezones">
2520 <term><varname>australian_timezones</varname> (<type>boolean</type>)</term>
2521 <indexterm><primary>time zone</><secondary>Australian</></>
2524 If set to true, <literal>ACST</literal>,
2525 <literal>CST</literal>, <literal>EST</literal>, and
2526 <literal>SAT</literal> are interpreted as Australian time
2527 zones rather than as North/South American time zones and
2528 Saturday. The default is false.
2533 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
2535 <primary>significant digits</primary>
2538 <primary>floating-point</primary>
2539 <secondary>display</secondary>
2542 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
2545 This parameter adjusts the number of digits displayed for
2546 floating-point values, including <type>float4</>, <type>float8</>,
2547 and geometric data types. The parameter value is added to the
2548 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
2549 as appropriate). The value can be set as high as 2, to include
2550 partially-significant digits; this is especially useful for dumping
2551 float data that needs to be restored exactly. Or it can be set
2552 negative to suppress unwanted digits.
2557 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
2558 <term><varname>client_encoding</varname> (<type>string</type>)</term>
2559 <indexterm><primary>character set</></>
2562 Sets the client-side encoding (character set).
2563 The default is to use the database encoding.
2568 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
2569 <term><varname>lc_messages</varname> (<type>string</type>)</term>
2572 Sets the language in which messages are displayed. Acceptable
2573 values are system-dependent; see <xref linkend="locale"> for
2574 more information. If this variable is set to the empty string
2575 (which is the default) then the value is inherited from the
2576 execution environment of the server in a system-dependent way.
2580 On some systems, this locale category does not exist. Setting
2581 this variable will still work, but there will be no effect.
2582 Also, there is a chance that no translated messages for the
2583 desired language exist. In that case you will continue to see
2584 the English messages.
2589 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
2590 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
2593 Sets the locale to use for formatting monetary amounts, for
2594 example with the <function>to_char</function> family of
2595 functions. Acceptable values are system-dependent; see <xref
2596 linkend="locale"> for more information. If this variable is
2597 set to the empty string (which is the default) then the value
2598 is inherited from the execution environment of the server in a
2599 system-dependent way.
2604 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
2605 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
2608 Sets the locale to use for formatting numbers, for example
2609 with the <function>to_char</function> family of
2610 functions. Acceptable values are system-dependent; see <xref
2611 linkend="locale"> for more information. If this variable is
2612 set to the empty string (which is the default) then the value
2613 is inherited from the execution environment of the server in a
2614 system-dependent way.
2619 <varlistentry id="guc-lc-time" xreflabel="lc_time">
2620 <term><varname>lc_time</varname> (<type>string</type>)</term>
2623 Sets the locale to use for formatting date and time values.
2624 (Currently, this setting does nothing, but it may in the
2625 future.) Acceptable values are system-dependent; see <xref
2626 linkend="locale"> for more information. If this variable is
2627 set to the empty string (which is the default) then the value
2628 is inherited from the execution environment of the server in a
2629 system-dependent way.
2637 <sect3 id="runtime-config-client-other">
2638 <title>Other Defaults</title>
2642 <varlistentry id="guc-explain-pretty-print" xreflabel="explain_pretty_print">
2643 <term><varname>explain_pretty_print</varname> (<type>boolean</type>)</term>
2646 Determines whether <command>EXPLAIN VERBOSE</> uses the
2647 indented or non-indented format for displaying detailed
2648 query-tree dumps. The default is on.
2653 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
2654 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
2655 <indexterm><primary>dynamic_library_path</></>
2656 <indexterm><primary>dynamic loading</></>
2659 If a dynamically loadable module needs to be opened and the
2660 specified name does not have a directory component (i.e. the
2661 name does not contain a slash), the system will search this
2662 path for the specified file. (The name that is used is the
2663 name specified in the <command>CREATE FUNCTION</command> or
2664 <command>LOAD</command> command.)
2668 The value for <varname>dynamic_library_path</varname> has to be a
2669 list of absolute directory names separated by colon or, in windows
2670 environments with semi-colon. If a directory name starts
2671 with the special value <literal>$libdir</literal>, the
2672 compiled-in <productname>PostgreSQL</productname> package
2673 library directory is substituted. This where the modules
2674 provided by the <productname>PostgreSQL</productname>
2675 distribution are installed. (Use <literal>pg_config
2676 --pkglibdir</literal> to print the name of this directory.) For
2679 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
2681 or, in a windows environment:
2683 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
2688 The default value for this parameter is
2689 <literal>'$libdir'</literal>. If the value is set to an empty
2690 string, the automatic path search is turned off.
2694 This parameter can be changed at run time by superusers, but a
2695 setting done that way will only persist until the end of the
2696 client connection, so this method should be reserved for
2697 development purposes. The recommended way to set this parameter
2698 is in the <filename>postgresql.conf</filename> configuration
2708 <sect2 id="runtime-config-locks">
2709 <title>Lock Management</title>
2713 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
2715 <primary>deadlock</primary>
2716 <secondary>timeout during</secondary>
2719 <primary>timeout</primary>
2720 <secondary>deadlock</secondary>
2723 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
2726 This is the amount of time, in milliseconds, to wait on a lock
2727 before checking to see if there is a deadlock condition. The
2728 check for deadlock is relatively slow, so the server doesn't run
2729 it every time it waits for a lock. We (optimistically?) assume
2730 that deadlocks are not common in production applications and
2731 just wait on the lock for a while before starting the check for a
2732 deadlock. Increasing this value reduces the amount of time
2733 wasted in needless deadlock checks, but slows down reporting of
2734 real deadlock errors. The default is 1000 (i.e., one second),
2735 which is probably about the smallest value you would want in
2736 practice. On a heavily loaded server you might want to raise it.
2737 Ideally the setting should exceed your typical transaction time,
2738 so as to improve the odds that a lock will be released before
2739 the waiter decides to check for deadlock.
2744 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
2745 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
2748 The shared lock table is sized on the assumption that at most
2749 <varname>max_locks_per_transaction</varname> *
2750 <varname>max_connections</varname> distinct objects will need to
2751 be locked at any one time. The default, 64, has historically
2752 proven sufficient, but you might need to raise this value if you
2753 have clients that touch many different tables in a single
2754 transaction. This option can only be set at server start.
2762 <sect2 id="runtime-config-compatible">
2763 <title>Version and Platform Compatibility</title>
2765 <sect3 id="runtime-config-compatible-version">
2766 <title>Previous PostgreSQL Versions</title>
2769 <varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
2770 <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
2771 <indexterm><primary>FROM</><secondary>missing</></>
2774 When <literal>true</>, tables that are referenced by a query will be
2775 automatically added to the <literal>FROM</> clause if not already
2776 present. The default is <literal>true</> for compatibility with
2777 previous releases of <productname>PostgreSQL</>. However, this
2778 behavior is not SQL-standard, and many people dislike it because it
2779 can mask mistakes. Set to <literal>false</> for the SQL-standard
2780 behavior of rejecting references to tables that are not listed in
2786 <varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
2787 <term><varname>regex_flavor</varname> (<type>string</type>)</term>
2788 <indexterm><primary>regular expressions</></>
2791 The regular expression <quote>flavor</> can be set to
2792 <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
2793 The default is <literal>advanced</>. The <literal>extended</>
2794 setting may be useful for exact backwards compatibility with
2795 pre-7.4 releases of <productname>PostgreSQL</>.
2800 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
2801 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
2802 <indexterm><primary>inheritance</></>
2805 This controls the inheritance semantics, in particular whether
2806 subtables are included by various commands by default. They were
2807 not included in versions prior to 7.1. If you need the old
2808 behavior you can set this variable to off, but in the long run
2809 you are encouraged to change your applications to use the
2810 <literal>ONLY</literal> key word to exclude subtables. See
2811 <xref linkend="ddl-inherit"> for more information about inheritance.
2816 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
2817 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
2820 This controls whether <command>CREATE TABLE</command>
2821 and <command>CREATE TABLE AS</command> will include OIDs in
2822 newly-created tables, if neither <literal>WITH OIDS</literal>
2823 or <literal>WITHOUT OIDS</literal> have been specified. It
2824 also determines whether OIDs will be included in the table
2825 created by <command>SELECT INTO</command>. In
2826 <productname>PostgreSQL</productname> &version;
2827 <varname>default_with_oids</varname> defaults to
2828 true. This is also the behavior of previous versions
2829 of <productname>PostgreSQL</productname>. However, assuming
2830 that tables will contain OIDs by default is not
2831 encouraged. Therefore, this option will default to false in a
2832 future release of <productname>PostgreSQL</productname>.
2836 To ease compatibility with applications that make use of OIDs,
2837 this option should left enabled. To ease compatibility with
2838 future versions of <productname>PostgreSQL</productname>, this
2839 option should be disabled, and applications that require OIDs
2840 on certain tables should explictely specify <literal>WITH
2841 OIDS</literal> when issuing the <command>CREATE
2842 TABLE</command> statements for the tables in question.
2849 <sect3 id="runtime-config-compatible-clients">
2850 <title>Platform and Client Compatibility</title>
2853 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
2854 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
2855 <indexterm><primary>IS NULL</></>
2858 When turned on, expressions of the form
2859 <literal><replaceable>expr</> = NULL</literal> (or <literal>NULL
2860 = <replaceable>expr</></literal>) are treated as
2861 <literal><replaceable>expr</> IS NULL</literal>, that is, they
2862 return true if <replaceable>expr</> evaluates to the null value,
2863 and false otherwise. The correct behavior of
2864 <literal><replaceable>expr</> = NULL</literal> is to always
2865 return null (unknown). Therefore this option defaults to off.
2869 However, filtered forms in <productname>Microsoft
2870 Access</productname> generate queries that appear to use
2871 <literal><replaceable>expr</> = NULL</literal> to test for
2872 null values, so if you use that interface to access the database you
2873 might want to turn this option on. Since expressions of the
2874 form <literal><replaceable>expr</> = NULL</literal> always
2875 return the null value (using the correct interpretation) they are not
2876 very useful and do not appear often in normal applications, so
2877 this option does little harm in practice. But new users are
2878 frequently confused about the semantics of expressions
2879 involving null values, so this option is not on by default.
2883 Note that this option only affects the literal <literal>=</>
2884 operator, not other comparison operators or other expressions
2885 that are computationally equivalent to some expression
2886 involving the equals operator (such as <literal>IN</literal>).
2887 Thus, this option is not a general fix for bad programming.
2891 Refer to <xref linkend="functions-comparison"> for related information.
2900 <sect2 id="runtime-config-compiler">
2901 <title>Compiled-in Options</title>
2904 The following options are available read-only, and are determined
2905 when <productname>PostgreSQL</productname> is compiled. As such,
2906 they have been excluded from the sample
2907 <filename>postgresql.conf</> file. These options determine
2908 various aspects of <productname>PostgreSQL</productname> behavior
2909 that may be of interest to certain applications, particularly
2910 administrative front-ends.
2915 <varlistentry id="guc-block-size" xreflabel="block_size">
2916 <term><varname>block_size</varname> (<type>integer</type>)</term>
2919 Shows the size of a disk block. It is determined by the value
2920 of <literal>BLCKSZ</> when building the server. The default
2921 value is 8192 bytes. The meaning of some configuration
2922 variables (such as <xref linkend="guc-shared-buffers">) is
2923 influenced by <varname>block_size</varname>. See <xref
2924 linkend="runtime-config-resource"> for information.
2929 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
2930 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
2933 Shows whether <productname>PostgreSQL</productname> was built
2934 with support for 64-bit integer dates and times. It is set by
2935 configuring with <literal>--enable-integer-datetimes</literal>
2936 when building <productname>PostgreSQL</productname>. The
2937 default value is <literal>off</literal>.
2942 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
2943 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
2946 Shows the maximum number of function arguments. It is determined by
2947 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
2948 default value is 32.
2953 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
2954 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
2957 Shows the maximum identifier length. It is determined as one
2958 less than the value of <literal>NAMEDATALEN</> when building
2959 the server. The default value of <literal>NAMEDATALEN</> is
2960 64; therefore the default
2961 <varname>max_identifier_length</varname> is 63.
2966 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
2967 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
2970 Shows the maximum number of index keys. It is determined by
2971 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
2972 default value is 32.
2980 <sect2 id="runtime-config-custom">
2981 <title>Customized Options</title>
2984 The following was designed to allow options not normally known to
2985 <productname>PostgreSQL</productname> to be declared in the posgresql.conf
2986 file and/or manipulated using the <command>SET</command> in a controlled
2987 manner so that add-on modules to the postgres proper (such as lanugage
2988 mappings for triggers and functions) can be configured in a unified way.
2993 <varlistentry id="guc-custom-variable-classes" xreflabel="custom-variable-classes">
2994 <term><varname>custom_variable_classes</varname> (<type>string</type>)</term>
2995 <indexterm><primary>custom_variable_classes</></>
2998 This variable specifies one or several classes to be used for custom
2999 variables. A custom variable is a variable not normally known to
3000 the <productname>PostgreSQL</productname> proper but used by some add
3005 Aribtrary variables can be defined for each class specified here. Those
3006 variables will be treated as placeholders and have no meaning until the
3007 module that defines them is loaded. When a module for a specific class is
3008 loaded, it will add the proper variable definitions for the class
3009 associated with it, convert any placeholder values according to those
3010 definitions, and issue warnings for any placeholders that then remains.
3014 Here is an example what custom variables might look like:
3017 custom_variable_class = 'plr,pljava'
3018 plr.foo = '/usr/lib/R'
3020 plruby.var = true <== this one would generate an error
3025 This option can only be set at server start or in the
3026 <filename>postgresql.conf</filename> configuration file.
3034 <sect2 id="runtime-config-developer">
3035 <title>Developer Options</title>
3038 The following options are intended for work on the
3039 <productname>PostgreSQL</productname> source, and in some cases
3040 to assist with recovery of severely damaged databases. There
3041 should be no reason to use them in a production database setup.
3042 As such, they have been excluded from the sample
3043 <filename>postgresql.conf</> file. Note that many of these
3044 options require special source compilation flags to work at all.
3048 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
3049 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
3052 Turns on various assertion checks. This is a debugging aid. If
3053 you are experiencing strange problems or crashes you might want
3054 to turn this on, as it might expose programming mistakes. To use
3055 this option, the macro <symbol>USE_ASSERT_CHECKING</symbol>
3056 must be defined when <productname>PostgreSQL</productname> is
3057 built (accomplished by the <command>configure</command> option
3058 <option>--enable-cassert</option>). Note that
3059 <varname>debug_assertions</varname> defaults to on if
3060 <productname>PostgreSQL</productname> has been built with
3066 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
3067 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
3070 If nonzero, a delay of this many seconds occurs just after a new
3071 server process is forked, before it conducts the authentication
3072 process. This is intended to give an opportunity to attach to the
3073 server process with a debugger to trace down misbehavior in
3079 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
3080 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
3083 Generates a great amount of debugging output for the
3084 <command>LISTEN</command> and <command>NOTIFY</command>
3085 commands. <xref linkend="guc-client-min-messages"> or
3086 <xref linkend="guc-log-min-messages"> must be
3087 <literal>DEBUG1</literal> or lower to send this output to the
3088 client or server log, respectively.
3094 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
3095 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
3096 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
3097 <term><varname>trace_lock_oidmin</varname> (<type>boolean</type>)</term>
3098 <term><varname>trace_lock_table</varname> (<type>boolean</type>)</term>
3099 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
3100 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
3103 Various other code tracing and debugging options.
3108 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
3109 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
3112 If true, emit WAL-related debugging output. This option is
3113 only available if the <symbol>WAL_DEBUG</symbol> macro was
3114 defined when <productname>PostgreSQL</productname> was
3120 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero-damaged-pages">
3121 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
3124 Detection of a damaged page header normally causes
3125 <productname>PostgreSQL</> to report an error, aborting the current
3126 transaction. Setting <varname>zero_damaged_pages</> to true causes
3127 the system to instead report a warning, zero out the damaged page,
3128 and continue processing. This behavior <emphasis>will destroy data</>,
3129 namely all the rows on the damaged page. But it allows you to get
3130 past the error and retrieve rows from any undamaged pages that may
3131 be present in the table. So it is useful for recovering data if
3132 corruption has occurred due to hardware or software error. You should
3133 generally not set this true until you have given up hope of recovering
3134 data from the damaged page(s) of a table. The
3135 default setting is off, and it can only be changed by a superuser.
3141 <sect2 id="runtime-config-short">
3142 <title>Short Options</title>
3145 For convenience there are also single letter command-line option switches
3146 available for some parameters. They are described in <xref
3147 linkend="runtime-config-short-table">.
3150 <table id="runtime-config-short-table">
3151 <title>Short option key</title>
3155 <entry>Short option</entry>
3156 <entry>Equivalent</entry>
3162 <entry><option>-B <replaceable>x</replaceable></option></entry>
3163 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
3166 <entry><option>-d <replaceable>x</replaceable></option></entry>
3167 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
3170 <entry><option>-F</option></entry>
3171 <entry><literal>fsync = off</></entry>
3174 <entry><option>-h <replaceable>x</replaceable></option></entry>
3175 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
3178 <entry><option>-i</option></entry>
3179 <entry><literal>listen_addresses = '*'</></entry>
3182 <entry><option>-k <replaceable>x</replaceable></option></entry>
3183 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
3186 <entry><option>-l</option></entry>
3187 <entry><literal>ssl = on</></entry>
3190 <entry><option>-N <replaceable>x</replaceable></option></entry>
3191 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
3194 <entry><option>-p <replaceable>x</replaceable></option></entry>
3195 <entry><literal>port = <replaceable>x</replaceable></></entry>
3200 <option>-fi</option>, <option>-fh</option>,
3201 <option>-fm</option>, <option>-fn</option>,
3202 <option>-fs</option>, <option>-ft</option><footnote
3203 id="fn.runtime-config-short">
3205 For historical reasons, these options must be passed to
3206 the individual server process via the <option>-o</option>
3207 <command>postmaster</command> option, for example,
3209 $ <userinput>postmaster -o '-S 1024 -s'</userinput>
3211 or via <envar>PGOPTIONS</envar> from the client side, as
3217 <literal>enable_indexscan = off</>,
3218 <literal>enable_hashjoin = off</>,
3219 <literal>enable_mergejoin = off</>,
3220 <literal>enable_nestloop = off</>,
3221 <literal>enable_seqscan = off</>,
3222 <literal>enable_tidscan = off</>
3227 <entry><option>-s</option><footnoteref linkend="fn.runtime-config-short"></entry>
3228 <entry><literal>log_statement_stats = on</></entry>
3232 <entry><option>-S <replaceable>x</replaceable></option><footnoteref linkend="fn.runtime-config-short">
3234 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
3238 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option><footnoteref linkend="fn.runtime-config-short"></entry>
3239 <entry><literal>log_parser_stats = on</>,
3240 <literal>log_planner_stats = on</>,
3241 <literal>log_executor_stats = on</></entry>
3251 <sect1 id="kernel-resources">
3252 <title>Managing Kernel Resources</title>
3255 A large <productname>PostgreSQL</> installation can quickly exhaust
3256 various operating system resource limits. (On some systems, the
3257 factory defaults are so low that you don't even need a really
3258 <quote>large</> installation.) If you have encountered this kind of
3259 problem, keep reading.
3262 <sect2 id="sysvipc">
3263 <title>Shared Memory and Semaphores</title>
3265 <indexterm zone="sysvipc">
3266 <primary>shared memory</primary>
3269 <indexterm zone="sysvipc">
3270 <primary>semaphores</primary>
3274 Shared memory and semaphores are collectively referred to as
3275 <quote><systemitem class="osname">System V</>
3276 <acronym>IPC</></quote> (together with message queues, which are not
3277 relevant for <productname>PostgreSQL</>). Almost all modern
3278 operating systems provide these features, but not all of them have
3279 them turned on or sufficiently sized by default, especially systems
3280 with BSD heritage. (For the <systemitem class="osname">QNX</> and
3281 <systemitem class="osname">BeOS</> ports, <productname>PostgreSQL</>
3282 provides its own replacement implementation of these facilities.)
3286 The complete lack of these facilities is usually manifested by an
3287 <errorname>Illegal system call</> error upon server start. In
3288 that case there's nothing left to do but to reconfigure your
3289 kernel. <productname>PostgreSQL</> won't work without them.
3293 When <productname>PostgreSQL</> exceeds one of the various hard
3294 <acronym>IPC</> limits, the server will refuse to start and
3295 should leave an instructive error message describing the problem
3296 encountered and what to do about it. (See also <xref
3297 linkend="postmaster-start-failures">.) The relevant kernel
3298 parameters are named consistently across different systems; <xref
3299 linkend="sysvipc-parameters"> gives an overview. The methods to set
3300 them, however, vary. Suggestions for some platforms are given below.
3301 Be warned that it is often necessary to reboot your machine, and
3302 possibly even recompile the kernel, to change these settings.
3306 <table id="sysvipc-parameters">
3307 <title><systemitem class="osname">System V</> <acronym>IPC</> parameters</>
3313 <entry>Description</>
3314 <entry>Reasonable values</>
3320 <entry><varname>SHMMAX</></>
3321 <entry>Maximum size of shared memory segment (bytes)</>
3322 <entry>250 kB + 8.2 kB * <xref linkend="guc-shared-buffers"> +
3323 14.2 kB * <xref linkend="guc-max-connections"> up to infinity</entry>
3327 <entry><varname>SHMMIN</></>
3328 <entry>Minimum size of shared memory segment (bytes)</>
3333 <entry><varname>SHMALL</></>
3334 <entry>Total amount of shared memory available (bytes or pages)</>
3335 <entry>if bytes, same as <varname>SHMMAX</varname>; if pages, <literal>ceil(SHMMAX/PAGE_SIZE)</literal></>
3339 <entry><varname>SHMSEG</></>
3340 <entry>Maximum number of shared memory segments per process</>
3341 <entry>only 1 segment is needed, but the default is much higher</>
3345 <entry><varname>SHMMNI</></>
3346 <entry>Maximum number of shared memory segments system-wide</>
3347 <entry>like <varname>SHMSEG</> plus room for other applications</>
3351 <entry><varname>SEMMNI</></>
3352 <entry>Maximum number of semaphore identifiers (i.e., sets)</>
3353 <entry>at least <literal>ceil(max_connections / 16)</literal></>
3357 <entry><varname>SEMMNS</></>
3358 <entry>Maximum number of semaphores system-wide</>
3359 <entry><literal>ceil(max_connections / 16) * 17</literal> plus room for other applications</>
3363 <entry><varname>SEMMSL</></>
3364 <entry>Maximum number of semaphores per set</>
3365 <entry>at least 17</>
3369 <entry><varname>SEMMAP</></>
3370 <entry>Number of entries in semaphore map</>
3375 <entry><varname>SEMVMX</></>
3376 <entry>Maximum value of semaphore</>
3377 <entry>at least 1000 (The default is often 32767, don't change unless asked to.)</>
3386 <indexterm><primary>SHMMAX</primary></indexterm> The most important
3387 shared memory parameter is <varname>SHMMAX</>, the maximum size, in
3388 bytes, of a shared memory segment. If you get an error message from
3389 <function>shmget</> like <errorname>Invalid argument</>, it is
3390 possible that this limit has been exceeded. The size of the required
3391 shared memory segment varies both with the number of requested
3392 buffers (<option>-B</> option) and the number of allowed connections
3393 (<option>-N</> option), although the former is the most significant.
3394 (You can, as a temporary solution, lower these settings to eliminate
3395 the failure.) As a rough approximation, you can estimate the
3396 required segment size by multiplying the number of buffers and the
3397 block size (8 kB by default) plus ample overhead (at least half a
3398 megabyte). Any error message you might get will contain the size of
3399 the failed allocation request.
3403 Less likely to cause problems is the minimum size for shared
3404 memory segments (<varname>SHMMIN</>), which should be at most
3405 approximately 256 kB for <productname>PostgreSQL</> (it is
3406 usually just 1). The maximum number of segments system-wide
3407 (<varname>SHMMNI</>) or per-process (<varname>SHMSEG</>) should
3408 not cause a problem unless your system has them set to zero. Some
3409 systems also have a limit on the total amount of shared memory in
3410 the system; see the platform-specific instructions below.
3414 <productname>PostgreSQL</> uses one semaphore per allowed connection
3415 (<option>-N</> option), in sets of 16. Each such set will also
3416 contain a 17th semaphore which contains a <quote>magic
3417 number</quote>, to detect collision with semaphore sets used by
3418 other applications. The maximum number of semaphores in the system
3419 is set by <varname>SEMMNS</>, which consequently must be at least
3420 as high as <varname>max_connections</> plus one extra for each 16
3421 allowed connections (see the formula in <xref
3422 linkend="sysvipc-parameters">). The parameter <varname>SEMMNI</>
3423 determines the limit on the number of semaphore sets that can
3424 exist on the system at one time. Hence this parameter must be at
3425 least <literal>ceil(max_connections / 16)</>. Lowering the number
3426 of allowed connections is a temporary workaround for failures,
3427 which are usually confusingly worded <errorname>No space
3428 left on device</>, from the function <function>semget</>.
3432 In some cases it might also be necessary to increase
3433 <varname>SEMMAP</> to be at least on the order of
3434 <varname>SEMMNS</>. This parameter defines the size of the semaphore
3435 resource map, in which each contiguous block of available semaphores
3436 needs an entry. When a semaphore set is freed it is either added to
3437 an existing entry that is adjacent to the freed block or it is
3438 registered under a new map entry. If the map is full, the freed
3439 semaphores get lost (until reboot). Fragmentation of the semaphore
3440 space could over time lead to fewer available semaphores than there
3445 The <varname>SEMMSL</> parameter, which determines how many
3446 semaphores can be in a set, must be at least 17 for
3447 <productname>PostgreSQL</>.
3451 Various other settings related to <quote>semaphore undo</>, such as
3452 <varname>SEMMNU</> and <varname>SEMUME</>, are not of concern
3453 for <productname>PostgreSQL</>.
3459 <term><systemitem class="osname">BSD/OS</></term>
3460 <indexterm><primary>BSD/OS</><secondary>IPC configuration</></>
3463 <title>Shared Memory</>
3465 By default, only 4 MB of shared memory is supported. Keep in
3466 mind that shared memory is not pageable; it is locked in RAM.
3467 To increase the amount of shared memory supported by your
3468 system, add the following to your kernel configuration
3469 file. A <varname>SHMALL</> value of 1024 represents 4 MB of
3470 shared memory. The following increases the maximum shared
3471 memory area to 32 MB:
3473 options "SHMALL=8192"
3474 options "SHMMAX=\(SHMALL*PAGE_SIZE\)"
3476 For those running 4.3 or later, you will probably need to increase
3477 <varname>KERNEL_VIRTUAL_MB</> above the default <literal>248</>.
3478 Once all changes have been made, recompile the kernel, and reboot.
3483 For those running 4.0 and earlier releases, use <command>bpatch</>
3484 to find the <varname>sysptsize</> value in the current
3485 kernel. This is computed dynamically at boot time.
3487 $ <userinput>bpatch -r sysptsize</>
3488 <computeroutput>0x9 = 9</>
3490 Next, add <varname>SYSPTSIZE</> as a hard-coded value in the
3491 kernel configuration file. Increase the value you found using
3492 <command>bpatch</>. Add 1 for every additional 4 MB of
3493 shared memory you desire.
3495 options "SYSPTSIZE=16"
3497 <varname>sysptsize</> cannot be changed by <command>sysctl</command>.
3501 <title>Semaphores</>
3503 You may need to increase the number of semaphores. By
3504 default, <productname>PostgreSQL</> allocates 34 semaphores,
3505 which is over half the default system total of 60. Set the
3506 values you want in your kernel configuration file, e.g.:
3509 options "SEMMNS=240"
3518 <term><systemitem class="osname">FreeBSD</></term>
3519 <term><systemitem class="osname">NetBSD</></term>
3520 <term><systemitem class="osname">OpenBSD</></term>
3521 <indexterm><primary>FreeBSD</><secondary>IPC configuration</></>
3522 <indexterm><primary>NetBSD</><secondary>IPC configuration</></>
3523 <indexterm><primary>OpenBSD</><secondary>IPC configuration</></>
3526 The options <varname>SYSVSHM</> and <varname>SYSVSEM</> need
3527 to be enabled when the kernel is compiled. (They are by
3528 default.) The maximum size of shared memory is determined by
3529 the option <varname>SHMMAXPGS</> (in pages). The following
3530 shows an example of how to set the various parameters:
3533 options SHMMAXPGS=4096
3542 (On <systemitem class="osname">NetBSD</> and <systemitem
3543 class="osname">OpenBSD</> the key word is actually
3544 <literal>option</literal> singular.)
3547 You might also want to configure your kernel to lock shared
3548 memory into RAM and prevent it from being paged out to swap.
3549 Use the <command>sysctl</> setting
3550 <literal>kern.ipc.shm_use_phys</>.
3557 <term><systemitem class="osname">HP-UX</></term>
3558 <indexterm><primary>HP-UX</><secondary>IPC configuration</></>
3561 The default settings tend to suffice for normal installations.
3562 On <productname>HP-UX</> 10, the factory default for
3563 <varname>SEMMNS</> is 128, which might be too low for larger
3567 <acronym>IPC</> parameters can be set in the <application>System
3568 Administration Manager</> (<acronym>SAM</>) under
3569 <menuchoice><guimenu>Kernel
3570 Configuration</><guimenuitem>Configurable Parameters</></>. Hit
3571 <guibutton>Create A New Kernel</> when you're done.
3578 <term><systemitem class="osname">Linux</></term>
3579 <indexterm><primary>Linux</><secondary>IPC configuration</></>
3582 The default shared memory limit (both
3583 <varname>SHMMAX</varname> and <varname>SHMALL</varname>) is 32
3584 MB in 2.2 kernels, but it can be changed in the
3585 <filename>proc</filename> file system (without reboot). For
3586 example, to allow 128 MB:
3588 <prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmall</userinput>
3589 <prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmmax</userinput>
3591 You could put these commands into a script run at boot-time.
3595 Alternatively, you can use <command>sysctl</command>, if
3596 available, to control these parameters. Look for a file
3597 called <filename>/etc/sysctl.conf</filename> and add lines
3598 like the following to it:
3600 kernel.shmall = 134217728
3601 kernel.shmmax = 134217728
3603 This file is usually processed at boot time, but
3604 <command>sysctl</command> can also be called
3609 Other parameters are sufficiently sized for any application. If
3610 you want to see for yourself look in
3611 <filename>/usr/src/linux/include/asm-<replaceable>xxx</>/shmparam.h</>
3612 and <filename>/usr/src/linux/include/linux/sem.h</>.
3619 <term><systemitem class="osname">MacOS X</></term>
3620 <indexterm><primary>MacOS X</><secondary>IPC configuration</></>
3623 In OS X 10.2 and earlier, edit the file
3624 <filename>/System/Library/StartupItems/SystemTuning/SystemTuning</>
3625 and change the values in the following commands:
3627 sysctl -w kern.sysv.shmmax
3628 sysctl -w kern.sysv.shmmin
3629 sysctl -w kern.sysv.shmmni
3630 sysctl -w kern.sysv.shmseg
3631 sysctl -w kern.sysv.shmall
3633 In OS X 10.3, these commands have been moved to <filename>/etc/rc</>
3634 and must be edited there.
3641 <term><systemitem class="osname">SCO OpenServer</></term>
3642 <indexterm><primary>SCO OpenServer</><secondary>IPC configuration</></>
3645 In the default configuration, only 512 kB of shared memory per
3646 segment is allowed, which is about enough for <option>-B 24 -N
3647 12</>. To increase the setting, first change to the directory
3648 <filename>/etc/conf/cf.d</>. To display the current value of
3649 <varname>SHMMAX</>, run
3651 ./configure -y SHMMAX
3653 To set a new value for <varname>SHMMAX</>, run
3655 ./configure SHMMAX=<replaceable>value</>
3657 where <replaceable>value</> is the new value you want to use
3658 (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel:
3668 <term><systemitem class="osname">AIX</></term>
3669 <indexterm><primary>AIX</><secondary>IPC configuration</></>
3672 At least as of version 5.1, it should not be necessary to do
3673 any special configuration for such parameters as
3674 <varname>SHMMAX</varname>, as it appears this is configured to
3675 allow all memory to be used as shared memory. That is the
3676 sort of configuration commonly used for other databases such
3677 as <application>DB/2</application>.</para>
3679 <para> It may, however, be necessary to modify the global
3680 <command>ulimit</command> information in
3681 <filename>/etc/security/limits</filename>, as the default hard
3682 limits for filesizes (<varname>fsize</varname>) and numbers of
3683 files (<varname>nofiles</varname>) may be too low.
3689 <term><systemitem class="osname">Solaris</></term>
3690 <indexterm><primary>Solaris</><secondary>IPC configuration</></>
3693 At least in version 2.6, the default maximum size of a shared
3694 memory segments is too low for <productname>PostgreSQL</>. The
3695 relevant settings can be changed in <filename>/etc/system</>,
3698 set shmsys:shminfo_shmmax=0x2000000
3699 set shmsys:shminfo_shmmin=1
3700 set shmsys:shminfo_shmmni=256
3701 set shmsys:shminfo_shmseg=256
3703 set semsys:seminfo_semmap=256
3704 set semsys:seminfo_semmni=512
3705 set semsys:seminfo_semmns=512
3706 set semsys:seminfo_semmsl=32
3708 You need to reboot for the changes to take effect.
3713 url="http://sunsite.uakom.sk/sunworldonline/swol-09-1997/swol-09-insidesolaris.html"></>
3714 for information on shared memory under
3715 <productname>Solaris</>.
3722 <term><systemitem class="osname">UnixWare</></term>
3723 <indexterm><primary>UnixWare</><secondary>IPC configuration</></>
3726 On <productname>UnixWare</> 7, the maximum size for shared
3727 memory segments is 512 kB in the default configuration. This
3728 is enough for about <option>-B 24 -N 12</>. To display the
3729 current value of <varname>SHMMAX</>, run
3731 /etc/conf/bin/idtune -g SHMMAX
3733 which displays the current, default, minimum, and maximum
3734 values. To set a new value for <varname>SHMMAX</>,
3737 /etc/conf/bin/idtune SHMMAX <replaceable>value</>
3739 where <replaceable>value</> is the new value you want to use
3740 (in bytes). After setting <varname>SHMMAX</>, rebuild the
3743 /etc/conf/bin/idbuild -B
3756 <title>Resource Limits</title>
3759 Unix-like operating systems enforce various kinds of resource limits
3760 that might interfere with the operation of your
3761 <productname>PostgreSQL</productname> server. Of particular
3762 importance are limits on the number of processes per user, the
3763 number of open files per process, and the amount of memory available
3764 to each process. Each of these have a <quote>hard</quote> and a
3765 <quote>soft</quote> limit. The soft limit is what actually counts
3766 but it can be changed by the user up to the hard limit. The hard
3767 limit can only be changed by the root user. The system call
3768 <function>setrlimit</function> is responsible for setting these
3769 parameters. The shell's built-in command <command>ulimit</command>
3770 (Bourne shells) or <command>limit</command> (<application>csh</>) is
3771 used to control the resource limits from the command line. On
3772 BSD-derived systems the file <filename>/etc/login.conf</filename>
3773 controls the various resource limits set during login. See the
3774 operating system documentation for details. The relevant
3775 parameters are <varname>maxproc</varname>,
3776 <varname>openfiles</varname>, and <varname>datasize</varname>. For
3781 :datasize-cur=256M:\
3783 :openfiles-cur=256:\
3786 (<literal>-cur</literal> is the soft limit. Append
3787 <literal>-max</literal> to set the hard limit.)
3791 Kernels can also have system-wide limits on some resources.
3795 On <productname>Linux</productname>
3796 <filename>/proc/sys/fs/file-max</filename> determines the
3797 maximum number of open files that the kernel will support. It can
3798 be changed by writing a different number into the file or by
3799 adding an assignment in <filename>/etc/sysctl.conf</filename>.
3800 The maximum limit of files per process is fixed at the time the
3801 kernel is compiled; see
3802 <filename>/usr/src/linux/Documentation/proc.txt</filename> for
3810 The <productname>PostgreSQL</productname> server uses one process
3811 per connection so you should provide for at least as many processes
3812 as allowed connections, in addition to what you need for the rest
3813 of your system. This is usually not a problem but if you run
3814 several servers on one machine things might get tight.
3818 The factory default limit on open files is often set to
3819 <quote>socially friendly</quote> values that allow many users to
3820 coexist on a machine without using an inappropriate fraction of
3821 the system resources. If you run many servers on a machine this
3822 is perhaps what you want, but on dedicated servers you may want to
3827 On the other side of the coin, some systems allow individual
3828 processes to open large numbers of files; if more than a few
3829 processes do so then the system-wide limit can easily be exceeded.
3830 If you find this happening, and you do not want to alter the
3831 system-wide limit, you can set <productname>PostgreSQL</>'s <xref
3832 linkend="guc-max-files-per-process"> configuration parameter to
3833 limit the consumption of open files.
3838 <title>Linux Memory Overcommit</title>
3841 In Linux 2.4 and later, the default virtual memory behavior is not
3842 optimal for <productname>PostgreSQL</productname>. Because of the
3843 way that the kernel implements memory overcommit, the kernel may
3844 terminate the <productname>PostgreSQL</productname> server (the
3845 <filename>postmaster</filename> process) if the memory demands of
3846 another process cause the system to run out of virtual memory.
3850 If this happens, you will see a kernel message that looks like
3851 this (consult your system documentation and configuration on where
3852 to look for such a message):
3854 Out of Memory: Killed process 12345 (postmaster).
3856 This indicates that the <filename>postmaster</filename> process
3857 has been terminated due to memory pressure.
3858 Although existing database connections will continue to function
3859 normally, no new connections will be accepted. To recover,
3860 <productname>PostgreSQL</productname> will need to be restarted.
3864 One way to avoid this problem is to run
3865 <productname>PostgreSQL</productname>
3866 on a machine where you can be sure that other processes will not
3867 run the machine out of memory.
3871 On Linux 2.6 and later, a better solution is to modify the kernel's
3872 behavior so that it will not <quote>overcommit</> memory. This is
3873 done by selecting strict overcommit mode via sysctl:
3875 sysctl -w vm.overcommit_memory=2
3877 or placing an equivalent entry in <filename>/etc/sysctl.conf</>.
3878 You may also wish to modify the related setting
3879 <literal>vm.overcommit_ratio</>. For details see the kernel documentation
3880 file <filename>Documentation/vm/overcommit-accounting</>.
3884 Some vendors' Linux 2.4 kernels are reported to have early versions
3885 of the 2.6 overcommit sysctl. However, setting
3886 <literal>vm.overcommit_memory</> to 2
3887 on a kernel that does not have the relevant code will make
3888 things worse not better. It is recommended that you inspect
3889 the actual kernel source code (see the function
3890 <function>vm_enough_memory</> in the file <filename>mm/mmap.c</>)
3891 to verify what is supported in your copy before you try this in a 2.4
3892 installation. The presence of the <filename>overcommit-accounting</>
3893 documentation file should <emphasis>not</> be taken as evidence that the
3894 feature is there. If in any doubt, consult a kernel expert or your
3901 <sect1 id="postmaster-shutdown">
3902 <title>Shutting Down the Server</title>
3904 <indexterm zone="postmaster-shutdown">
3905 <primary>shutdown</>
3909 There are several ways to shut down the database server. You control
3910 the type of shutdown by sending different signals to the
3911 <command>postmaster</command> process.
3915 <term><systemitem>SIGTERM</systemitem><indexterm><primary>SIGTERM</></></term>
3918 After receiving <systemitem>SIGTERM</systemitem>, the server
3919 disallows new connections, but lets existing sessions end their
3920 work normally. It shuts down only after all of the sessions
3921 terminate normally. This is the <firstterm>Smart
3922 Shutdown</firstterm>.
3928 <term><systemitem>SIGINT</systemitem><indexterm><primary>SIGINT</></></term>
3931 The server disallows new connections and sends all existing
3932 server processes <systemitem>SIGTERM</systemitem>, which will cause them
3933 to abort their current transactions and exit promptly. It then
3934 waits for the server processes to exit and finally shuts down. This is the
3935 <firstterm>Fast Shutdown</firstterm>.
3941 <term><systemitem>SIGQUIT</systemitem><indexterm><primary>SIGQUIT</></></term>
3944 This is the <firstterm>Immediate Shutdown</firstterm>, which
3945 will cause the <command>postmaster</command> process to send a
3946 <systemitem>SIGQUIT</systemitem> to all child processes and exit
3947 immediately (without properly shutting itself down). The child processes
3948 likewise exit immediately upon receiving
3949 <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by
3950 replaying the WAL log) upon next start-up. This is recommended
3951 only in emergencies.
3960 It is best not to use <systemitem>SIGKILL</systemitem> to shut down
3961 the server. This will prevent the server from releasing
3962 shared memory and semaphores, which may then have to be done by
3968 The <acronym>PID</> of the <command>postmaster</command> process can be found using the
3969 <command>ps</command> program, or from the file
3970 <filename>postmaster.pid</filename> in the data directory. So for
3971 example, to do a fast shutdown:
3973 $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
3977 The program <command>pg_ctl</command> is a shell script
3978 that provides a more convenient interface for shutting down the
3983 <sect1 id="ssl-tcp">
3984 <title>Secure TCP/IP Connections with SSL</title>
3986 <indexterm zone="ssl-tcp">
3987 <primary>SSL</primary>
3991 <productname>PostgreSQL</> has native support for using
3992 <acronym>SSL</> connections to encrypt client/server communications
3993 for increased security. This requires that
3994 <productname>OpenSSL</productname> is installed on both client and
3995 server systems and that support in <productname>PostgreSQL</> is
3996 enabled at build time (see <xref linkend="installation">).
4000 With <acronym>SSL</> support compiled in, the
4001 <productname>PostgreSQL</> server can be started with
4002 <acronym>SSL</> enabled by setting the parameter
4003 <xref linkend="guc-ssl"> to on in <filename>postgresql.conf</>. When
4004 starting in <acronym>SSL</> mode, the server will look for the
4005 files <filename>server.key</> and <filename>server.crt</> in the
4006 data directory, which should contain the server private key
4007 and certificate, respectively. These files must be set up correctly
4008 before an <acronym>SSL</>-enabled server can start. If the private key is
4009 protected with a passphrase, the server will prompt for the
4010 passphrase and will not start until it has been entered.
4014 The server will listen for both standard and <acronym>SSL</>
4015 connections on the same TCP port, and will negotiate with any
4016 connecting client on whether to use <acronym>SSL</>. See <xref
4017 linkend="auth-pg-hba-conf"> about how to force the server to
4018 require use of <acronym>SSL</> for certain connections.
4022 For details on how to create your server private key and certificate,
4023 refer to the <productname>OpenSSL</> documentation. A simple
4024 self-signed certificate can be used to get started for testing, but a
4025 certificate signed by a certificate authority (<acronym>CA</>) (either one of the global
4026 <acronym>CAs</> or a local one) should be used in production so the
4027 client can verify the server's identity. To create a quick
4028 self-signed certificate, use the following
4029 <productname>OpenSSL</productname> command:
4031 openssl req -new -text -out server.req
4033 Fill out the information that <command>openssl</> asks for. Make sure
4034 that you enter the local host name as <quote>Common Name</>; the challenge
4035 password can be left blank. The program will generate a key that is
4036 passphrase protected; it will not accept a passphrase that is less
4037 than four characters long. To remove the passphrase (as you must if
4038 you want automatic start-up of the server), run the commands
4040 openssl rsa -in privkey.pem -out server.key
4043 Enter the old passphrase to unlock the existing key. Now do
4045 openssl req -x509 -in server.req -text -key server.key -out server.crt
4046 chmod og-rwx server.key
4048 to turn the certificate into a self-signed certificate and to copy the
4049 key and certificate to where the server will look for them.
4053 <sect1 id="ssh-tunnels">
4054 <title>Secure TCP/IP Connections with <application>SSH</application> Tunnels</title>
4056 <indexterm zone="ssh-tunnels">
4057 <primary>ssh</primary>
4061 One can use <application>SSH</application> to encrypt the network
4062 connection between clients and a
4063 <productname>PostgreSQL</productname> server. Done properly, this
4064 provides an adequately secure network connection.
4068 First make sure that an <application>SSH</application> server is
4069 running properly on the same machine as the
4070 <productname>PostgreSQL</productname> server and that you can log in using
4071 <command>ssh</command> as some user. Then you can establish a secure
4072 tunnel with a command like this from the client machine:
4074 ssh -L 3333:foo.com:5432 joe@foo.com
4076 The first number in the <option>-L</option> argument, 3333, is the
4077 port number of your end of the tunnel; it can be chosen freely. The
4078 second number, 5432, is the remote end of the tunnel: the port
4079 number your server is using. The name or the address in between
4080 the port numbers is the host with the database server you are going
4081 to connect to. In order to connect to the database server using
4082 this tunnel, you connect to port 3333 on the local machine:
4084 psql -h localhost -p 3333 template1
4086 To the database server it will then look as though you are really
4087 user <literal>joe@foo.com</literal> and it will use whatever
4088 authentication procedure was set up for this user. In order for the
4089 tunnel setup to succeed you must be allowed to connect via
4090 <command>ssh</command> as <literal>joe@foo.com</literal>, just
4091 as if you had attempted to use <command>ssh</command> to set up a
4097 Several other applications exist that can provide secure tunnels using
4098 a procedure similar in concept to the one just described.
4106 <!-- Keep this comment at the end of the file
4111 sgml-minimize-attributes:nil
4112 sgml-always-quote-attributes:t
4115 sgml-parent-document:nil
4116 sgml-default-dtd-file:"./reference.ced"
4117 sgml-exposed-tags:nil
4118 sgml-local-catalogs:("/usr/lib/sgml/catalog")
4119 sgml-local-ecat-files:nil