2 $PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.236 2004/02/03 17:34:02 tgl 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
27 this 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
125 <command>initdb</command>'s <option>-W</option> or
126 <option>--pwprompt</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 in the environment variable <envar>PGDATA</envar>.
170 If neither of these succeed, it will fail.
174 To start the <command>postmaster</command> in the
175 background, use the usual shell syntax:
177 $ <userinput>postmaster -D /usr/local/pgsql/data >logfile 2>&1 &</userinput>
179 It is an 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. In particular, in order
190 for the server to accept
191 TCP/IP<indexterm><primary>TCP/IP</primary></indexterm> connections
192 (rather than just Unix-domain socket ones), you must specify the
193 <option>-i</option> option.
197 This shell syntax can get tedious quickly. Therefore the shell
199 <command>pg_ctl</command><indexterm><primary>pg_ctl</primary></indexterm>
200 is provided to simplify some tasks. For example:
202 pg_ctl start -l logfile
204 will start the server in the background and put the output into the
205 named log file. The <option>-D</option> option has the same meaning
206 here as in the <command>postmaster</command>. <command>pg_ctl</command> is also
207 capable of stopping the server.
211 Normally, you will want to start the database server when the
212 computer boots.<indexterm><primary>booting</><secondary>starting
213 the server during</></> Autostart scripts are operating
214 system-specific. There are a few distributed with
215 <productname>PostgreSQL</productname> in the
216 <filename>contrib/start-scripts</> directory. This may require root
221 Different systems have different conventions for starting up daemons
222 at boot time. Many systems have a file
223 <filename>/etc/rc.local</filename> or
224 <filename>/etc/rc.d/rc.local</filename>. Others use
225 <filename>rc.d</> directories. Whatever you do, the server must be
226 run by the <productname>PostgreSQL</productname> user account
227 <emphasis>and not by root</emphasis> or any other user. Therefore you
228 probably should form your commands using <literal>su -c '...'
229 postgres</literal>. For example:
231 su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres
236 Here are a few more operating system specific suggestions. (Always
237 replace these with the proper installation directory and the user
243 For <productname>FreeBSD</productname>, look at the file
244 <filename>contrib/start-scripts/freebsd</filename> in the
245 <productname>PostgreSQL</productname> source distribution.
246 <indexterm><primary>FreeBSD</><secondary>start script</secondary></>
252 On <productname>OpenBSD</productname>, add the following lines
253 to the file <filename>/etc/rc.local</filename>:
254 <indexterm><primary>OpenBSD</><secondary>start script</secondary></>
256 if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postmaster ]; then
257 su - -c '/usr/local/pgsql/bin/pg_ctl start -l /var/postgresql/log -s' postgres
258 echo -n ' postgresql'
266 On <productname>Linux</productname> systems either add
267 <indexterm><primary>Linux</><secondary>start script</secondary></>
269 /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
271 to <filename>/etc/rc.d/rc.local</filename> or look at the file
272 <filename>contrib/start-scripts/linux</filename> in the
273 <productname>PostgreSQL</productname> source distribution.
279 On <productname>NetBSD</productname>, either use the
280 <productname>FreeBSD</productname> or
281 <productname>Linux</productname> start scripts, depending on
282 preference. <indexterm><primary>NetBSD</><secondary>start script</secondary></>
288 On <productname>Solaris</productname>, create a file called
289 <filename>/etc/init.d/postgresql</filename> that contains
291 <indexterm><primary>Solaris</><secondary>start script</secondary></>
293 su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
295 Then, create a symbolic link to it in <filename>/etc/rc3.d</> as
296 <filename>S99postgresql</>.
304 While the <command>postmaster</command> is running, its
305 <acronym>PID</acronym> is stored in the file
306 <filename>postmaster.pid</filename> in the data directory. This is
307 used to prevent multiple <command>postmaster</command> processes
308 running in the same data directory and can also be used for
309 shutting down the <command>postmaster</command> process.
312 <sect2 id="postmaster-start-failures">
313 <title>Server Start-up Failures</title>
316 There are several common reasons the server might fail to
317 start. Check the server's log file, or start it by hand (without
318 redirecting standard output or standard error) and see what error
319 messages appear. Below we explain some of the most common error
320 messages in more detail.
325 LOG: could not bind IPv4 socket: Address already in use
326 HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
327 FATAL: could not create TCP/IP listen socket
329 This usually means just what it suggests: you tried to start
330 another <command>postmaster</command> on the same port where one is already running.
331 However, if the kernel error message is not <computeroutput>Address
332 already in use</computeroutput> or some variant of that, there may
333 be a different problem. For example, trying to start a <command>postmaster</command>
334 on a reserved port number may draw something like:
336 $ <userinput>postmaster -i -p 666</userinput>
337 LOG: could not bind IPv4 socket: Permission denied
338 HINT: Is another postmaster already running on port 666? If not, wait a few seconds and retry.
339 FATAL: could not create TCP/IP listen socket
346 FATAL: could not create shared memory segment: Invalid argument
347 DETAIL: Failed system call was shmget(key=5440001, size=4011376640, 03600).
349 probably means your kernel's limit on the size of shared memory is
350 smaller than the work area <productname>PostgreSQL</productname>
351 is trying to create (4011376640 bytes in this example). Or it could
352 mean that you do not have System-V-style shared memory support
353 configured into your kernel at all. As a temporary workaround, you
354 can try starting the server with a smaller-than-normal number
355 of buffers (<option>-B</option> switch). You will eventually want
356 to reconfigure your kernel to increase the allowed shared memory
357 size. You may also see this message when trying to start multiple
358 servers on the same machine, if their total space requested
359 exceeds the kernel limit.
365 FATAL: could not create semaphores: No space left on device
366 DETAIL: Failed system call was semget(5440126, 17, 03600).
368 does <emphasis>not</emphasis> mean you've run out of disk
369 space. It means your kernel's limit on the number of <systemitem
370 class="osname">System V</> semaphores is smaller than the number
371 <productname>PostgreSQL</productname> wants to create. As above,
372 you may be able to work around the problem by starting the
373 server with a reduced number of allowed connections
374 (<option>-N</option> switch), but you'll eventually want to
375 increase the kernel limit.
379 If you get an <quote>illegal system call</> error, it is likely that
380 shared memory or semaphores are not supported in your kernel at
381 all. In that case your only option is to reconfigure the kernel to
382 enable these features.
386 Details about configuring <systemitem class="osname">System V</>
387 <acronym>IPC</> facilities are given in <xref linkend="sysvipc">.
391 <sect2 id="client-connection-problems">
392 <title>Client Connection Problems</title>
395 Although the error conditions possible on the client side are quite
396 varied and application-dependent, a few of them might be directly
397 related to how the server was started up. Conditions other than
398 those shown below should be documented with the respective client
404 psql: could not connect to server: Connection refused
405 Is the server running on host "server.joe.com" and accepting
406 TCP/IP connections on port 5432?
408 This is the generic <quote>I couldn't find a server to talk
409 to</quote> failure. It looks like the above when TCP/IP
410 communication is attempted. A common mistake is to forget to
411 configure the server to allow TCP/IP connections.
415 Alternatively, you'll get this when attempting Unix-domain socket
416 communication to a local server:
418 psql: could not connect to server: No such file or directory
419 Is the server running locally and accepting
420 connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
425 The last line is useful in verifying that the client is trying to
426 connect to the right place. If there is in fact no server
427 running there, the kernel error message will typically be either
428 <computeroutput>Connection refused</computeroutput> or
429 <computeroutput>No such file or directory</computeroutput>, as
430 illustrated. (It is important to realize that
431 <computeroutput>Connection refused</computeroutput> in this context
432 does <emphasis>not</emphasis> mean that the server got your
433 connection request and rejected it. That case will produce a
434 different message, as shown in <xref
435 linkend="client-authentication-problems">.) Other error messages
436 such as <computeroutput>Connection timed out</computeroutput> may
437 indicate more fundamental problems, like lack of network
443 <sect1 id="runtime-config">
444 <Title>Run-time Configuration</Title>
447 <primary>configuration</primary>
448 <secondary>of the server</secondary>
452 There are a lot of configuration parameters that affect the
453 behavior of the database system. In this subsection, we describe
454 how to set configuration parameters; the following subsections
455 discuss each parameter in detail.
459 All parameter names are case-insensitive. Every parameter takes a
460 value of one of the four types: boolean, integer, floating point,
461 and string. Boolean values are <literal>ON</literal>,
462 <literal>OFF</literal>, <literal>TRUE</literal>,
463 <literal>FALSE</literal>, <literal>YES</literal>,
464 <literal>NO</literal>, <literal>1</literal>, <literal>0</literal>
465 (case-insensitive) or any non-ambiguous prefix of these.
469 One way to set these parameters is to edit the file
470 <filename>postgresql.conf</filename><indexterm><primary>postgresql.conf</></>
471 in the data directory. (A default file is installed there.) An
472 example of what this file might look like is:
475 log_connections = yes
477 search_path = '$user, public'
479 One parameter is specified per line. The equal sign between name and
480 value is optional. Whitespace is insignificant and blank lines are
481 ignored. Hash marks (<literal>#</literal>) introduce comments
482 anywhere. Parameter values that are not simple identifiers or
483 numbers should be single-quoted.
488 <primary>SIGHUP</primary>
490 The configuration file is reread whenever the
491 <command>postmaster</command> process receives a
492 <systemitem>SIGHUP</> signal (which is most easily sent by means
493 of <literal>pg_ctl reload</>). The <command>postmaster</command>
494 also propagates this signal to all currently running server
495 processes so that existing sessions also get the new
496 value. Alternatively, you can send the signal to a single server
501 A second way to set these configuration parameters is to give them
502 as a command line option to the <command>postmaster</command>, such as:
504 postmaster -c log_connections=yes -c syslog=2
506 Command-line options override any conflicting settings in
507 <filename>postgresql.conf</filename>.
511 Occasionally it is also useful to give a command line option to
512 one particular session only. The environment variable
513 <envar>PGOPTIONS</envar> can be used for this purpose on the
516 env PGOPTIONS='-c geqo=off' psql
518 (This works for any <application>libpq</>-based client application, not just
519 <application>psql</application>.) Note that this won't work for
520 parameters that are fixed when the server is started, such as the port
525 Furthermore, it is possible to assign a set of option settings to
526 a user or a database. Whenever a session is started, the default
527 settings for the user and database involved are loaded. The
528 commands <command>ALTER DATABASE</command> and <command>ALTER
529 USER</command>, respectively, are used to configure these
530 settings. Per-database settings override anything received
531 from the <command>postmaster</command> command-line or the
532 configuration file, and in turn are overridden by per-user
533 settings; both are 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. Superusers are allowed to <command>SET</>
545 more values than ordinary users.
549 The <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title">
550 command allows inspection of the current values of all parameters.
554 The virtual table <structname>pg_settings</structname>
555 (described in <xref linkend="view-pg-settings">) also allows
556 displaying and updating session run-time parameters. It is equivalent
557 to <command>SHOW</> and <command>SET</>, but can be more convenient
558 to use because it can be joined with other tables, or selected from using
559 any desired selection condition.
562 <sect2 id="runtime-config-connection">
563 <title>Connections and Authentication</title>
565 <sect3 id="runtime-config-connection-settings">
566 <title>Connection Settings</title>
571 <term><varname>tcpip_socket</varname> (<type>boolean</type>)</term>
574 If this is true, then the server will accept TCP/IP connections.<indexterm><primary>TCP/IP</></>
575 Otherwise only local Unix domain socket connections are
576 accepted. It is off by default. This option can only be set at
583 <term><varname>max_connections</varname> (<type>integer</type>)</term>
586 Determines the maximum number of concurrent connections to the
587 database server. The default is typically 100, but may be less
588 if your kernel settings will not support it (as determined
589 during <application>initdb</>). This parameter can only be
594 Increasing this parameter may cause <productname>PostgreSQL</>
595 to request more <systemitem class="osname">System V</> shared
596 memory or semaphores than your operating system's default configuration
597 allows. See <xref linkend="sysvipc"> for information on how to
598 adjust these parameters, if necessary.
604 <term><varname>superuser_reserved_connections</varname>
605 (<type>integer</type>)</term>
608 Determines the number of <quote>connection slots</quote> that
609 are reserved for connections by <productname>PostgreSQL</>
610 superusers. At most <varname>max_connections</> connections can
611 ever be active simultaneously. Whenever the number of active
612 concurrent connections is at least <varname>max_connections</> minus
613 <varname>superuser_reserved_connections</varname>, new connections
614 will be accepted only for superusers.
618 The default value is 2. The value must be less than the value of
619 <varname>max_connections</varname>. This parameter can only be
626 <term><varname>port</varname> (<type>integer</type>)</term>
627 <indexterm><primary>port</></>
630 The TCP port the server listens on; 5432 by default. This
631 option can only be set at server start.
638 <term><varname>unix_socket_directory</varname> (<type>string</type>)</term>
641 Specifies the directory of the Unix-domain socket on which the
642 server is to listen for
643 connections from client applications. The default is normally
644 <filename>/tmp</filename>, but can be changed at build time.
650 <term><varname>unix_socket_group</varname> (<type>string</type>)</term>
653 Sets the group owner of the Unix domain socket. (The owning
654 user of the socket is always the user that starts the
655 server.) In combination with the option
656 <varname>unix_socket_permissions</varname> this can be used as
657 an additional access control mechanism for this socket type.
658 By default this is the empty string, which uses the default
659 group for the current user. This option can only be set at
666 <term><varname>unix_socket_permissions</varname> (<type>integer</type>)</term>
669 Sets the access permissions of the Unix domain socket. Unix
670 domain sockets use the usual Unix file system permission set.
671 The option value is expected to be an numeric mode
672 specification in the form accepted by the
673 <function>chmod</function> and <function>umask</function>
674 system calls. (To use the customary octal format the number
675 must start with a <literal>0</literal> (zero).)
679 The default permissions are <literal>0777</literal>, meaning
680 anyone can connect. Reasonable alternatives are
681 <literal>0770</literal> (only user and group, see also under
682 <varname>unix_socket_group</varname>) and <literal>0700</literal>
683 (only user). (Note that actually for a Unix domain socket, only write
684 permission matters and there is no point in setting or revoking
685 read or execute permissions.)
689 This access control mechanism is independent of the one
690 described in <xref linkend="client-authentication">.
694 This option can only be set at server start.
700 <term><varname>virtual_host</varname> (<type>string</type>)</term>
703 Specifies the host name or IP address on which the server is
704 to listen for connections from client applications. The
705 default is to listen on all configured addresses (including
706 <systemitem class="systemname">localhost</>).
712 <term><varname>rendezvous_name</varname> (<type>string</type>)</term>
715 Specifies the Rendezvous broadcast name. By default, the
716 computer name is used, specified as ''.
723 <sect3 id="runtime-config-connection-security">
724 <title>Security and Authentication</title>
728 <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
729 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
730 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
733 Maximum time to complete client authentication, in seconds. If a
734 would-be client has not completed the authentication protocol in
735 this much time, the server breaks the connection. This prevents
736 hung clients from occupying a connection indefinitely. This
737 option can only be set at server start or in the
738 <filename>postgresql.conf</filename> file. The default is 60.
745 <primary>SSL</primary>
748 <term><varname>ssl</varname> (<type>boolean</type>)</term>
751 Enables <acronym>SSL</> connections. Please read
752 <xref linkend="ssl-tcp"> before using this. The default
759 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
762 When a password is specified in <command>CREATE USER</> or
763 <command>ALTER USER</> without writing either <literal>ENCRYPTED</> or
764 <literal>UNENCRYPTED</>, this option determines whether the password is to be
765 encrypted. The default is on (encrypt the password).
771 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
774 Sets the location of the Kerberos server key file. See
775 <xref linkend="kerberos-auth"> for details.
781 <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
784 This allows per-database user names. It is off by default.
788 If this is on, you should create users as <literal>username@dbname</>.
789 When <literal>username</> is passed by a connecting client,
790 <literal>@</> and the database name is appended to the user
791 name and that database-specific user name is looked up by the
792 server. Note that when you create users with names containing
793 <literal>@</> within the SQL environment, you will need to
798 With this option enabled, you can still create ordinary global
799 users. Simply append <literal>@</> when specifying the user
800 name in the client. The <literal>@</> will be stripped off
801 before the user name is looked up by the server.
806 This feature is intended as a temporary measure until a
807 complete solution is found. At that time, this option will
818 <sect2 id="runtime-config-resource">
819 <title>Resource Consumption</title>
821 <sect3 id="runtime-config-resource-memory">
822 <title>Memory</title>
826 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
829 Sets the number of shared memory buffers used by the database
830 server. The default is typically 1000, but may be less if your
831 kernel settings will not support it (as determined during
832 <application>initdb</>). Each buffer is 8192 bytes, unless a
833 different value of <symbol>BLCKSZ</symbol> was chosen when building
834 the server. This setting must be at least 16, as well as at
835 least twice the value of <varname>max_connections</varname>;
836 however, settings significantly higher than the minimum are
837 usually needed for good performance. Values of a few thousand
838 are recommended for production installations. This option can
839 only be set at server start.
843 Increasing this parameter may cause <productname>PostgreSQL</>
844 to request more <systemitem class="osname">System V</> shared
845 memory than your operating system's default configuration
846 allows. See <xref linkend="sysvipc"> for information on how to
847 adjust these parameters, if necessary.
853 <term><varname>work_mem</varname> (<type>integer</type>)</term>
856 Specifies the amount of memory to be used by internal sort operations
857 and hash tables before switching to temporary disk files. The value is
858 specified in kilobytes, and defaults to 1024 kilobytes (1 MB).
859 Note that for a complex query, several sort or hash operations might be
860 running in parallel; each one will be allowed to use as much memory
861 as this value specifies before it starts to put data into temporary
862 files. Also, several running sessions could be doing such operations
863 concurrently. So the total memory used could be many
864 times the value of <varname>work_mem</varname>; it is necessary to
865 keep this fact in mind when choosing the value. Sort operations are
866 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
868 Hash tables are used in hash joins, hash-based aggregation, and
869 hash-based processing of <literal>IN</> subqueries.
875 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
878 Specifies the maximum amount of memory to be used in maintenance
879 operations, such as <command>VACUUM</command>, <command>CREATE
880 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>.
881 The value is specified in kilobytes, and defaults to 16384 kilobytes
882 (16 MB). Since only one of these operations can be executed at
883 a time by a database session, and an installation normally doesn't
884 have very many of them happening concurrently, it's safe to set this
885 value significantly larger than <varname>work_mem</varname>. Larger
886 settings may improve performance for vacuuming and for restoring
894 <sect3 id="runtime-config-resource-fsm">
895 <title>Free Space Map</title>
899 <term><varname>max_fsm_pages</varname> (<type>integer</type>)</term>
902 Sets the maximum number of disk pages for which free space will
903 be tracked in the shared free-space map. Six bytes of shared memory
904 are consumed for each page slot. This setting must be more than
905 16 * <varname>max_fsm_relations</varname>. The default is 20000.
906 This option can only be set at server start.
912 <term><varname>max_fsm_relations</varname> (<type>integer</type>)</term>
915 Sets the maximum number of relations (tables and indexes) for which
916 free space will be tracked in the shared free-space map. Roughly
917 fifty bytes of shared memory are consumed for each slot.
919 This option can only be set at server start.
926 <sect3 id="runtime-config-resource-kernel">
927 <title>Kernel Resource Usage</title>
931 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
934 Sets the maximum number of simultaneously open files allowed to each
935 server subprocess. The default is 1000. The limit actually used
936 by the code is the smaller of this setting and the result of
937 <literal>sysconf(_SC_OPEN_MAX)</literal>. Therefore, on systems
938 where <function>sysconf</> returns a reasonable limit, you don't
939 need to worry about this setting. But on some platforms
940 (notably, most BSD systems), <function>sysconf</> returns a
941 value that is much larger than the system can really support
942 when a large number of processes all try to open that many
943 files. If you find yourself seeing <quote>Too many open files</>
944 failures, try reducing this setting. This option can only be set
945 at server start or in the <filename>postgresql.conf</filename>
946 configuration file; if changed in the configuration file, it
947 only affects subsequently-started server subprocesses.
953 <term><varname>preload_libraries</varname> (<type>string</type>)</term>
954 <indexterm><primary>preload_libraries</></>
957 This variable specifies one or more shared libraries that are
958 to be preloaded at server start. A parameterless
959 initialization function can optionally be called for each
960 library. To specify that, add a colon and the name of the
961 initialization function after the library name. For example
962 <literal>'$libdir/mylib:mylib_init'</literal> would cause
963 <literal>mylib</> to be preloaded and <literal>mylib_init</>
964 to be executed. If more than one library is to be loaded,
965 separate their names with commas.
969 If <literal>mylib</> or <literal>mylib_init</> are not found, the
970 server will fail to start.
974 <productname>PostgreSQL</productname> procedural language
975 libraries may be preloaded in this way, typically by using the
976 syntax <literal>'$libdir/plXXX:plXXX_init'</literal> where
977 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
978 <literal>tcl</>, or <literal>python</>.
982 By preloading a shared library (and initializing it if
983 applicable), the library startup time is avoided when the
984 library is first used. However, the time to start each new
985 server process may increase, even if that process never
995 <sect2 id="runtime-config-wal">
996 <title>Write Ahead Log</title>
999 See also <xref linkend="wal-configuration"> for details on WAL
1003 <sect3 id="runtime-config-wal-settings">
1004 <title>Settings</title>
1009 <primary>fsync</primary>
1012 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1015 If this option is on, the <productname>PostgreSQL</> server
1016 will use the <function>fsync()</> system call in several places
1017 to make sure that updates are physically written to disk. This
1018 insures that a database cluster will recover to a
1019 consistent state after an operating system or hardware crash.
1020 (Crashes of the database server itself are <emphasis>not</>
1025 However, using <function>fsync()</function> results in a
1026 performance penalty: when a transaction is committed,
1027 <productname>PostgreSQL</productname> must wait for the
1028 operating system to flush the write-ahead log to disk. When
1029 <varname>fsync</varname> is disabled, the operating system is
1030 allowed to do its best in buffering, ordering, and delaying
1031 writes. This can result in significantly improved performance.
1032 However, if the system crashes, the results of the last few
1033 committed transactions may be lost in part or whole. In the
1034 worst case, unrecoverable data corruption may occur.
1038 Due to the risks involved, there is no universally correct
1039 setting for <varname>fsync</varname>. Some administrators
1040 always disable <varname>fsync</varname>, while others only
1041 turn it off for bulk loads, where there is a clear restart
1042 point if something goes wrong, whereas some administrators
1043 always leave <varname>fsync</varname> enabled. The default is
1044 to enable <varname>fsync</varname>, for maximum reliability.
1045 If you trust your operating system, your hardware, and your
1046 utility company (or your battery backup), you can consider
1047 disabling <varname>fsync</varname>.
1051 This option can only be set at server start or in the
1052 <filename>postgresql.conf</filename> file.
1058 <term><varname>wal_sync_method</varname> (<type>string</type>)</term>
1061 Method used for forcing WAL updates out to disk. Possible
1063 <literal>fsync</> (call <function>fsync()</> at each commit),
1064 <literal>fdatasync</> (call <function>fdatasync()</> at each commit),
1065 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>), and
1066 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>).
1067 Not all of these choices are available on all platforms.
1068 This option can only be set at server start or in the
1069 <filename>postgresql.conf</filename> file.
1075 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1078 Number of disk-page buffers in shared memory for WAL
1079 logging. The default is 8. This option can only be set at
1087 <sect3 id="runtime-config-wal-checkpoints">
1088 <title>Checkpoints</title>
1092 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1095 Maximum distance between automatic WAL checkpoints, in log
1096 file segments (each segment is normally 16 megabytes). The
1097 default is three. This option can only be set at server start
1098 or in the <filename>postgresql.conf</filename> file.
1104 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1107 Maximum time between automatic WAL checkpoints, in
1108 seconds. The default is 300 seconds. This option can only be
1109 set at server start or in the <filename>postgresql.conf</>
1116 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1119 Write a message to the server logs if checkpoints caused by
1120 the filling of checkpoint segment files happens more
1121 frequently than this number of seconds. The default is 30
1122 seconds. Zero turns off the warning.
1129 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1132 Time delay between writing a commit record to the WAL buffer
1133 and flushing the buffer out to disk, in microseconds. A
1134 nonzero delay allows multiple transactions to be committed
1135 with only one <function>fsync()</function> system call, if
1136 system load is high enough additional transactions may become
1137 ready to commit within the given interval. But the delay is
1138 just wasted if no other transactions become ready to
1139 commit. Therefore, the delay is only performed if at least
1140 <varname>commit_siblings</varname> other transactions are
1141 active at the instant that a server process has written its
1142 commit record. The default is zero (no delay).
1148 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1151 Minimum number of concurrent open transactions to require
1152 before performing the <varname>commit_delay</> delay. A larger
1153 value makes it more probable that at least one other
1154 transaction will become ready to commit during the delay
1155 interval. The default is five.
1164 <sect2 id="runtime-config-query">
1165 <title>Query Planning</title>
1167 <sect3 id="runtime-config-query-enable">
1168 <title>Planner Method Configuration</title>
1172 These configuration parameters provide a crude method for
1173 influencing the query plans chosen by the query optimizer. If
1174 the default plan chosen by the optimizer for a particular query
1175 is not optimal, a temporary solution may be found by using one
1176 of these configuration parameters to force the optimizer to
1177 choose a better plan. Other ways to improve the quality of the
1178 plans chosen by the optimizer include configuring the <xref
1179 linkend="runtime-config-query-constants"
1180 endterm="runtime-config-query-constants-title">, running
1181 <command>ANALYZE</command> more frequently, and increasing the
1182 amount of statistics collected for a particular column using
1183 <command>ALTER TABLE SET STATISTICS</command>.
1189 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
1192 Enables or disables the query planner's use of hashed
1193 aggregation plan types. The default is on. This is used for
1194 debugging the query planner.
1200 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
1203 Enables or disables the query planner's use of hash-join plan
1204 types. The default is on. This is used for debugging the query
1212 <primary>index scan</primary>
1215 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
1218 Enables or disables the query planner's use of index-scan plan
1219 types. The default is on. This is used for debugging the query
1226 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
1229 Enables or disables the query planner's use of merge-join plan
1230 types. The default is on. This is used for debugging the query
1237 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
1240 Enables or disables the query planner's use of nested-loop join
1241 plans. It's not possible to suppress nested-loop joins entirely,
1242 but turning this variable off discourages the planner from using
1243 one if there are other methods available. The default is
1244 on. This is used for debugging the query planner.
1251 <primary>sequential scan</primary>
1254 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
1257 Enables or disables the query planner's use of sequential scan
1258 plan types. It's not possible to suppress sequential scans
1259 entirely, but turning this variable off discourages the planner
1260 from using one if there are other methods available. The
1261 default is on. This is used for debugging the query planner.
1267 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
1270 Enables or disables the query planner's use of explicit sort
1271 steps. It's not possible to suppress explicit sorts entirely,
1272 but turning this variable off discourages the planner from
1273 using one if there are other methods available. The default
1274 is on. This is used for debugging the query planner.
1280 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
1283 Enables or disables the query planner's use of <acronym>TID</>
1284 scan plan types. The default is on. This is used for debugging
1292 <sect3 id="runtime-config-query-constants">
1293 <title id="runtime-config-query-constants-title">
1294 Planner Cost Constants
1299 Unfortunately, there is no well-defined method for determining
1300 ideal values for the family of <quote>cost</quote> variables that
1301 appear below. You are encouraged to experiment and share
1309 <term><varname>effective_cache_size</varname> (<type>floating point</type>)</term>
1312 Sets the planner's assumption about the effective size of the
1313 disk cache (that is, the portion of the kernel's disk cache
1314 that will be used for <productname>PostgreSQL</productname>
1315 data files). This is measured in disk pages, which are
1316 normally 8192 bytes each. The default is 1000.
1322 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
1325 Sets the query planner's estimate of the cost of a
1326 nonsequentially fetched disk page. This is measured as a
1327 multiple of the cost of a sequential page fetch. A higher
1328 value makes it more likely a sequential scan will be used, a
1329 lower value makes it more likely an index scan will be
1330 used. The default is four.
1336 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
1339 Sets the query planner's estimate of the cost of processing
1340 each row during a query. This is measured as a fraction of
1341 the cost of a sequential page fetch. The default is 0.01.
1347 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
1350 Sets the query planner's estimate of the cost of processing
1351 each index row during an index scan. This is measured as a
1352 fraction of the cost of a sequential page fetch. The default
1359 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
1362 Sets the planner's estimate of the cost of processing each
1363 operator in a <literal>WHERE</> clause. This is measured as a fraction of
1364 the cost of a sequential page fetch. The default is 0.0025.
1372 <sect3 id="runtime-config-query-geqo">
1373 <title>Genetic Query Optimizer</title>
1379 <primary>genetic query optimization</primary>
1382 <primary>GEQO</primary>
1383 <see>genetic query optimization</see>
1385 <term><varname>geqo</varname> (<type>boolean</type>)</term>
1388 Enables or disables genetic query optimization, which is an
1389 algorithm that attempts to do query planning without exhaustive
1390 searching. This is on by default. See also the various other
1391 <varname>geqo_</varname> settings.
1397 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
1400 Use genetic query optimization to plan queries with at least
1401 this many <literal>FROM</> items involved. (Note that an outer
1402 <literal>JOIN</> construct counts as only one <literal>FROM</>
1403 item.) The default is 12. For simpler queries it is usually best
1404 to use the deterministic, exhaustive planner, but for queries with
1405 many tables the deterministic planner takes too long.
1411 <term><varname>geqo_effort</varname> (<type>integer</type>)</term>
1412 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
1413 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
1414 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
1417 Various tuning parameters for the genetic query optimization
1418 algorithm. The recommended one to modify is
1419 <varname>geqo_effort</varname>, which can range from 1 to 10 with
1420 a default of 5. Larger values increase the time spent in planning
1421 but make it more likely that a good plan will be found.
1422 <varname>geqo_effort</varname> doesn't actually do anything directly,
1423 it is just used to compute the default values for the other
1424 parameters. If you prefer, you can set the other parameters by hand
1426 The pool size is the number of individuals in the genetic population.
1427 It must be at least two, and useful values are typically 100 to 1000.
1428 If it is set to zero (the default setting) then a suitable default
1429 is chosen based on <varname>geqo_effort</varname> and the number of
1430 tables in the query.
1431 Generations specifies the number of iterations of the algorithm.
1432 It must be at least one, and useful values are in the same range
1434 If it is set to zero (the default setting) then a suitable default
1435 is chosen based on the pool size.
1436 The run time of the algorithm is roughly proportional to the sum of
1437 pool size and generations.
1438 The selection bias is the selective pressure within the
1439 population. Values can be from 1.50 to 2.00; the latter is the
1447 <sect3 id="runtime-config-query-other">
1448 <title>Other Planner Options</title>
1453 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
1456 Sets the default statistics target for table columns that have not
1457 had a column-specific target set via <command>ALTER TABLE SET
1458 STATISTICS</>. Larger values increase the time needed to do
1459 <command>ANALYZE</>, but may improve the quality of the planner's
1460 estimates. The default is 10.
1466 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
1469 The planner will merge sub-queries into upper queries if the
1470 resulting <literal>FROM</literal> list would have no more than
1471 this many items. Smaller values reduce planning time but may
1472 yield inferior query plans. The default is 8. It is usually
1473 wise to keep this less than <varname>geqo_threshold</varname>.
1479 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
1482 The planner will rewrite explicit inner <literal>JOIN</>
1483 constructs into lists of <literal>FROM</> items whenever a
1484 list of no more than this many items in total would
1485 result. Prior to <productname>PostgreSQL</> 7.4, joins
1486 specified via the <literal>JOIN</literal> construct would
1487 never be reordered by the query planner. The query planner has
1488 subsequently been improved so that inner joins written in this
1489 form can be reordered; this configuration variable controls
1490 the extent to which this reordering is performed.
1493 At present, the order of outer joins specified via the
1494 <literal>JOIN</> construct is never adjusted by the query
1495 planner; therefore, <varname>join_collapse_limit</> has no
1496 effect on this behavior. The planner may be improved to
1497 reorder some classes of outer joins in a future release of
1498 <productname>PostgreSQL</productname>.
1504 By default, this variable is set to
1505 <varname>from_collapse_limit</varname>, which is appropriate
1506 for most uses. Setting it to 1 prevents any reordering of
1507 inner <literal>JOIN</>s. Thus, the explicit join order
1508 specified in the query will be the actual order in which the
1509 relations are joined. The query planner does not always choose
1510 the optimal join order; advanced users may elect to
1511 temporarily set this variable to 1, and then specify the join
1512 order they desire explicitly. Another consequence of setting
1513 this variable to 1 is that the query planner will behave more
1514 like the <productname>PostgreSQL</productname> 7.3 query
1515 planner, which some users might find useful for backward
1516 compatibility reasons.
1520 Setting this variable to a value between 1 and
1521 <varname>from_collapse_limit</varname> might be useful to
1522 trade off planning time against the quality of the chosen plan
1523 (higher values produce better plans).
1532 <sect2 id="runtime-config-logging">
1533 <title>Error Reporting and Logging</title>
1535 <indexterm zone="runtime-config-logging">
1536 <primary>server log</primary>
1539 <sect3 id="runtime-config-logging-syslog">
1540 <title>Syslog</title>
1542 <indexterm zone="runtime-config-logging-syslog">
1543 <primary>syslog</primary>
1549 <term><varname>syslog</varname> (<type>integer</type>)</term>
1552 <productname>PostgreSQL</productname> allows the use of
1553 <systemitem>syslog</systemitem> for logging. If this option is
1554 set to 1, messages go both to <systemitem>syslog</> and the
1555 standard output. A setting of 2 sends output only to
1556 <systemitem>syslog</>. (Some messages will still go to the
1557 standard output/error.) The default is 0, which means
1558 <systemitem>syslog</> is off. This option must be set at server
1565 <term><varname>syslog_facility</varname> (<type>string</type>)</term>
1568 This option determines the <application>syslog</application>
1569 <quote>facility</quote> to be used when logging via
1570 <application>syslog</application> is enabled. You may choose
1571 from <literal>LOCAL0</>, <literal>LOCAL1</>,
1572 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
1573 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
1574 the default is <literal>LOCAL0</>. See also the
1575 documentation of your system's
1576 <application>syslog</application>.
1582 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
1585 If logging to <application>syslog</> is enabled, this option
1586 determines the program name used to identify
1587 <productname>PostgreSQL</productname> messages in
1588 <application>syslog</application> log messages. The default is
1589 <literal>postgres</literal>.
1596 <sect3 id="runtime-config-logging-when">
1597 <title>When To Log</title>
1602 <term><varname>client_min_messages</varname> (<type>string</type>)</term>
1605 Controls which message levels are sent to the client.
1606 Valid values are <literal>DEBUG5</>,
1607 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
1608 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
1609 <literal>WARNING</>, and <literal>ERROR</>. Each level
1610 includes all the levels that follow it. The later the level,
1611 the fewer messages are sent. The default is
1612 <literal>NOTICE</>. Note that <literal>LOG</> has a different
1613 rank here than in <varname>log_min_messages</>.
1619 <term><varname>log_min_messages</varname> (<type>string</type>)</term>
1622 Controls which message levels are written to the server log.
1623 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
1624 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
1625 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
1626 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
1627 <literal>PANIC</>. Each level includes all the levels that
1628 follow it. The later the level, the fewer messages are sent
1629 to the log. The default is <literal>NOTICE</>. Note that
1630 <literal>LOG</> has a different rank here than in
1631 <varname>client_min_messages</>.
1632 Only superusers can increase this option.
1638 <term><varname>log_error_verbosity</varname> (<type>string</type>)</term>
1641 Controls the amount of detail written in the server log for each
1642 message that is logged. Valid values are <literal>TERSE</>,
1643 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
1644 fields to displayed messages.
1650 <term><varname>log_min_error_statement</varname> (<type>string</type>)</term>
1653 Controls whether or not the SQL statement that causes an error
1654 condition will also be recorded in the server log. All SQL
1655 statements that cause an error of the specified level, or a
1656 higher level, are logged. The default is
1657 <literal>PANIC</literal> (effectively turning this feature
1658 off for normal use). Valid values are <literal>DEBUG5</literal>,
1659 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
1660 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
1661 <literal>INFO</literal>, <literal>NOTICE</literal>,
1662 <literal>WARNING</literal>, <literal>ERROR</literal>,
1663 <literal>FATAL</literal>, and <literal>PANIC</literal>. For
1664 example, if you set this to <literal>ERROR</literal> then all
1665 SQL statements causing errors, fatal errors, or panics will be
1666 logged. Enabling this option can be helpful in tracking down
1667 the source of any errors that appear in the server log.
1668 Only superusers can increase this option.
1674 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
1677 Sets a minimum statement execution time (in milliseconds)
1678 for statement to be logged. All SQL statements
1679 that run in the time specified or longer will be logged with
1680 their duration. Setting this to zero will print
1681 all queries and their durations. Minus-one (the default)
1682 disables this. For example, if you set it to
1683 <literal>250</literal> then all SQL statements that run 250ms
1684 or longer will be logged. Enabling this option can be
1685 useful in tracking down unoptimized queries in your applications.
1686 Only superusers can increase this or set it to minus-one if this
1687 option is set by the administrator.
1693 <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
1696 Runs the server silently. If this option is set, the server
1697 will automatically run in background and any controlling terminals
1698 are disassociated. Thus, no messages are written to standard
1699 output or standard error (same effect as <command>postmaster</>'s <option>-S</option>
1701 <application>syslog</> logging is enabled, using this option is
1702 discouraged since it makes it impossible to see error messages.
1710 Here is a list of the various message severity levels used in
1714 <term><literal>DEBUG[1-5]</literal></term>
1717 Provides information for use by developers.
1723 <term><literal>INFO</literal></term>
1726 Provides information implicitly requested by the user,
1727 e.g., during <command>VACUUM VERBOSE</>.
1733 <term><literal>NOTICE</literal></term>
1736 Provides information that may be helpful to users, e.g.,
1737 truncation of long identifiers and the creation of indexes as part
1744 <term><literal>WARNING</literal></term>
1747 Provides warnings to the user, e.g., <command>COMMIT</>
1748 outside a transaction block.
1754 <term><literal>ERROR</literal></term>
1757 Reports an error that caused the current transaction to abort.
1763 <term><literal>LOG</literal></term>
1766 Reports information of interest to administrators, e.g.,
1767 checkpoint activity.
1773 <term><literal>FATAL</literal></term>
1776 Reports an error that caused the current session to abort.
1782 <term><literal>PANIC</literal></term>
1785 Reports an error that caused all sessions to abort.
1793 <sect3 id="runtime-config-logging-what">
1794 <title>What To Log</title>
1799 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
1800 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
1801 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
1802 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
1805 These options enable various debugging output to be sent to
1806 the client or server log. For each executed query, they print
1807 the resulting parse tree, the query rewriter output, or the
1808 execution plan. <varname>debug_pretty_print</varname> indents
1809 these displays to produce a more readable but much longer
1810 output format. <varname>client_min_messages</varname> or
1811 <varname>log_min_messages</varname> must be
1812 <literal>DEBUG1</literal> or lower to send output to the
1813 client or server logs. These options are off by default.
1819 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
1822 This outputs a line to the server logs detailing each successful
1823 connection. This is off by default, although it is probably very
1824 useful. This option can only be set at server start or in the
1825 <filename>postgresql.conf</filename> configuration file.
1832 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
1835 Causes the duration of every completed statement to be logged.
1836 To use this option, enable <varname>log_statement</> and
1837 <varname>log_pid</> so you can link the statement to the
1838 duration using the process ID. The default is off.
1839 Only superusers can turn off this option if it is enabled by
1846 <term><varname>log_pid</varname> (<type>boolean</type>)</term>
1849 Prefixes each message in the server log file with the process ID of
1850 the server process. This is useful to sort out which messages
1851 pertain to which connection. The default is off. This parameter
1852 does not affect messages logged via <application>syslog</>, which
1853 always contain the process ID.
1859 <term><varname>log_statement</varname> (<type>boolean</type>)</term>
1862 Causes each SQL statement to be logged. The default is off.
1863 <command>EXECUTE</> only displays the plan name, not the
1864 prepared query. Server-side languages like
1865 <application>PL/pgSQL</> that store functions in a cache only
1866 display their queries on first function call. Only superusers can
1867 turn off this option if it is enabled by the administrator.
1873 <term><varname>log_timestamp</varname> (<type>boolean</type>)</term>
1876 Prefixes each server log message with a time stamp. The default
1883 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
1886 By default, connection logs only show the IP address of the
1887 connecting host. If you want it to show the host name you can
1888 turn this on, but depending on your host name resolution setup
1889 it might impose a non-negligible performance penalty. This
1890 option can only be set at server start.
1896 <term><varname>log_source_port</varname> (<type>boolean</type>)</term>
1899 Shows the outgoing port number of the connecting host in the
1900 connection log messages. You could trace back the port number
1901 to find out what user initiated the connection. Other than
1902 that, it's pretty useless and therefore off by default. This
1903 option can only be set at server start.
1912 <sect2 id="runtime-config-statistics">
1913 <title>Runtime Statistics</title>
1915 <sect3 id="runtime-config-statistics-monitor">
1916 <title>Statistics Monitoring</title>
1920 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
1921 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
1922 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
1923 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
1926 For each query, write performance statistics of the respective
1927 module to the server log. This is a crude profiling
1928 instrument. All of these options are disabled by default.
1929 Only superusers can turn off any of these options if they have
1930 been enabled by the administrator.
1938 <sect3 id="runtime-config-statistics-collector">
1939 <title>Query and Index Statistics Collector</title>
1943 <term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>
1946 Controls whether the server should start the
1947 statistics-collection subprocess. This is on by default, but
1948 may be turned off if you know you have no interest in
1949 collecting statistics. This option can only be set at server
1956 <term><varname>stats_command_string</varname> (<type>boolean</type>)</term>
1959 Enables the collection of statistics on the currently
1960 executing command of each session, along with the time at
1961 which that command began execution. This option is off by
1962 default. Note that even when enabled, this information is not
1963 visible to all users, only to superusers and the user owning
1964 the session being reported on; so it should not represent a
1965 security risk. This data can be accessed via the
1966 <structname>pg_stat_activity</structname> system view; refer
1967 to <xref linkend="monitoring"> for more information.
1973 <term><varname>stats_block_level</varname> (<type>boolean</type>)</term>
1974 <term><varname>stats_row_level</varname> (<type>boolean</type>)</term>
1977 These enable the collection of block-level and row-level statistics
1978 on database activity, respectively. These options are off by
1979 default. This data can be accessed via the
1980 <structname>pg_stat</structname> and
1981 <structname>pg_statio</structname> family of system views;
1982 refer to <xref linkend="monitoring"> for more information.
1988 <term><varname>stats_reset_on_server_start</varname> (<type>boolean</type>)</term>
1991 If on, collected statistics are zeroed out whenever the server
1992 is restarted. If off, statistics are accumulated across server
1993 restarts. The default is on. This option can only be set at
2003 <sect2 id="runtime-config-client">
2004 <title>Client Connection Defaults</title>
2006 <sect3 id="runtime-config-client-statement">
2007 <title>Statement Behavior</title>
2011 <term><varname>search_path</varname> (<type>string</type>)</term>
2012 <indexterm><primary>search_path</></>
2013 <indexterm><primary>path</><secondary>for schemas</></>
2016 This variable specifies the order in which schemas are searched
2017 when an object (table, data type, function, etc.) is referenced by a
2018 simple name with no schema component. When there are objects of
2019 identical names in different schemas, the one found first
2020 in the search path is used. An object that is not in any of the
2021 schemas in the search path can only be referenced by specifying
2022 its containing schema with a qualified (dotted) name.
2026 The value for <varname>search_path</varname> has to be a comma-separated
2027 list of schema names. If one of the list items is
2028 the special value <literal>$user</literal>, then the schema
2029 having the name returned by <function>SESSION_USER</> is substituted, if there
2030 is such a schema. (If not, <literal>$user</literal> is ignored.)
2034 The system catalog schema, <literal>pg_catalog</>, is always
2035 searched, whether it is mentioned in the path or not. If it is
2036 mentioned in the path then it will be searched in the specified
2037 order. If <literal>pg_catalog</> is not in the path then it will
2038 be searched <emphasis>before</> searching any of the path items.
2039 It should also be noted that the temporary-table schema,
2040 <literal>pg_temp_<replaceable>nnn</></>, is implicitly searched before any of
2045 When objects are created without specifying a particular target
2046 schema, they will be placed in the first schema listed
2047 in the search path. An error is reported if the search path is
2052 The default value for this parameter is
2053 <literal>'$user, public'</literal> (where the second part will be
2054 ignored if there is no schema named <literal>public</>).
2055 This supports shared use of a database (where no users
2056 have private schemas, and all share use of <literal>public</>),
2057 private per-user schemas, and combinations of these. Other
2058 effects can be obtained by altering the default search path
2059 setting, either globally or per-user.
2063 The current effective value of the search path can be examined
2064 via the <acronym>SQL</acronym> function
2065 <function>current_schemas()</>. This is not quite the same as
2066 examining the value of <varname>search_path</varname>, since
2067 <function>current_schemas()</> shows how the requests
2068 appearing in <varname>search_path</varname> were resolved.
2072 For more information on schema handling, see <xref linkend="ddl-schemas">.
2078 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
2081 This parameter is normally true. When set false, it disables
2082 validation of the function body string in <command>CREATE FUNCTION</>.
2083 Disabling validation is occasionally useful to avoid problems such as
2084 forward references when restoring function definitions from a dump.
2091 <primary>transaction isolation level</primary>
2094 <term><varname>default_transaction_isolation</varname> (<type>string</type>)</term>
2097 Each SQL transaction has an isolation level, which can be
2098 either <quote>read uncommitted</quote>, <quote>read
2099 committed</quote>, <quote>repeatable read</quote>, or
2100 <quote>serializable</quote>. This parameter controls the
2101 default isolation level of each new transaction. The default
2102 is <quote>read committed</quote>.
2106 Consult <xref linkend="mvcc"> and <xref linkend="sql-set-transaction"> for more
2114 <primary>read-only transaction</primary>
2117 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
2120 A read-only SQL transaction cannot alter non-temporary tables.
2121 This parameter controls the default read-only status of each new
2122 transaction. The default is false (read/write).
2126 Consult <xref linkend="sql-set-transaction"> for more information.
2132 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
2135 Aborts any statement that takes over the specified number of
2136 milliseconds. A value of zero turns off the timer, which is
2144 <sect3 id="runtime-config-client-format">
2145 <title>Locale and Formatting</title>
2150 <term><varname>datestyle</varname> (<type>string</type>)</term>
2151 <indexterm><primary>date style</></>
2154 Sets the display format for date and time values, as well as
2155 the rules for interpreting ambiguous date input values.
2156 For historical reasons, this variable contains two independent
2157 components: the output format specification (<literal>ISO</>,
2158 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>) and
2159 the date field order specification (<literal>DMY</>, <literal>MDY</>,
2160 or <literal>YMD</>). These can be set separately or together.
2161 The keywords <literal>Euro</> and <literal>European</> are synonyms
2162 for <literal>DMY</>; the keywords <literal>US</>, <literal>NonEuro</>,
2163 and <literal>NonEuropean</> are synonyms for <literal>MDY</>.
2164 See <xref linkend="datatype-datetime"> for more information. The
2165 default is <literal>ISO, MDY</>.
2171 <term><varname>timezone</varname> (<type>string</type>)</term>
2172 <indexterm><primary>time zone</></>
2175 Sets the time zone for displaying and interpreting time
2176 stamps. The default is to use whatever the system environment
2177 specifies as the time zone. See <xref
2178 linkend="datatype-datetime"> for more information.
2184 <term><varname>australian_timezones</varname> (<type>boolean</type>)</term>
2185 <indexterm><primary>time zone</><secondary>Australian</></>
2188 If set to true, <literal>ACST</literal>,
2189 <literal>CST</literal>, <literal>EST</literal>, and
2190 <literal>SAT</literal> are interpreted as Australian time
2191 zones rather than as North/South American time zones and
2192 Saturday. The default is false.
2199 <primary>significant digits</primary>
2202 <primary>floating-point</primary>
2203 <secondary>display</secondary>
2206 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
2209 This parameter adjusts the number of digits displayed for
2210 floating-point values, including <type>float4</>, <type>float8</>,
2211 and geometric data types. The parameter value is added to the
2212 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
2213 as appropriate). The value can be set as high as 2, to include
2214 partially-significant digits; this is especially useful for dumping
2215 float data that needs to be restored exactly. Or it can be set
2216 negative to suppress unwanted digits.
2222 <term><varname>client_encoding</varname> (<type>string</type>)</term>
2223 <indexterm><primary>character set</></>
2226 Sets the client-side encoding (character set).
2227 The default is to use the database encoding.
2233 <term><varname>lc_messages</varname> (<type>string</type>)</term>
2236 Sets the language in which messages are displayed. Acceptable
2237 values are system-dependent; see <xref linkend="locale"> for
2238 more information. If this variable is set to the empty string
2239 (which is the default) then the value is inherited from the
2240 execution environment of the server in a system-dependent way.
2244 On some systems, this locale category does not exist. Setting
2245 this variable will still work, but there will be no effect.
2246 Also, there is a chance that no translated messages for the
2247 desired language exist. In that case you will continue to see
2248 the English messages.
2254 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
2257 Sets the locale to use for formatting monetary amounts, for
2258 example with the <function>to_char</function> family of
2259 functions. Acceptable values are system-dependent; see <xref
2260 linkend="locale"> for more information. If this variable is
2261 set to the empty string (which is the default) then the value
2262 is inherited from the execution environment of the server in a
2263 system-dependent way.
2269 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
2272 Sets the locale to use for formatting numbers, for example
2273 with the <function>to_char()</function> family of
2274 functions. Acceptable values are system-dependent; see <xref
2275 linkend="locale"> for more information. If this variable is
2276 set to the empty string (which is the default) then the value
2277 is inherited from the execution environment of the server in a
2278 system-dependent way.
2284 <term><varname>lc_time</varname> (<type>string</type>)</term>
2287 Sets the locale to use for formatting date and time values.
2288 (Currently, this setting does nothing, but it may in the
2289 future.) Acceptable values are system-dependent; see <xref
2290 linkend="locale"> for more information. If this variable is
2291 set to the empty string (which is the default) then the value
2292 is inherited from the execution environment of the server in a
2293 system-dependent way.
2301 <sect3 id="runtime-config-client-other">
2302 <title>Other Defaults</title>
2307 <term><varname>explain_pretty_print</varname> (<type>boolean</type>)</term>
2310 Determines whether <command>EXPLAIN VERBOSE</> uses the
2311 indented or non-indented format for displaying detailed
2312 query-tree dumps. The default is on.
2318 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
2319 <indexterm><primary>dynamic_library_path</></>
2320 <indexterm><primary>dynamic loading</></>
2323 If a dynamically loadable module needs to be opened and the
2324 specified name does not have a directory component (i.e. the
2325 name does not contain a slash), the system will search this
2326 path for the specified file. (The name that is used is the
2327 name specified in the <command>CREATE FUNCTION</command> or
2328 <command>LOAD</command> command.)
2332 The value for <varname>dynamic_library_path</varname> has to be a colon-separated
2333 list of absolute directory names. If a directory name starts
2334 with the special value <literal>$libdir</literal>, the
2335 compiled-in <productname>PostgreSQL</productname> package
2336 library directory is substituted. This where the modules
2337 provided by the <productname>PostgreSQL</productname>
2338 distribution are installed. (Use <literal>pg_config
2339 --pkglibdir</literal> to print the name of this directory.) For
2342 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
2347 The default value for this parameter is
2348 <literal>'$libdir'</literal>. If the value is set to an empty
2349 string, the automatic path search is turned off.
2353 This parameter can be changed at run time by superusers, but a
2354 setting done that way will only persist until the end of the
2355 client connection, so this method should be reserved for
2356 development purposes. The recommended way to set this parameter
2357 is in the <filename>postgresql.conf</filename> configuration
2364 <term><varname>max_expr_depth</varname> (<type>integer</type>)</term>
2367 Sets the maximum expression nesting depth of the parser. The
2368 default value of 10000 is high enough for any normal query,
2369 but you can raise it if needed. (But if you raise it too high,
2370 you run the risk of server crashes due to stack overflow.)
2379 <sect2 id="runtime-config-locks">
2380 <title>Lock Management</title>
2386 <primary>deadlock</primary>
2387 <secondary>timeout during</secondary>
2390 <primary>timeout</primary>
2391 <secondary>deadlock</secondary>
2394 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
2397 This is the amount of time, in milliseconds, to wait on a lock
2398 before checking to see if there is a deadlock condition. The
2399 check for deadlock is relatively slow, so the server doesn't run
2400 it every time it waits for a lock. We (optimistically?) assume
2401 that deadlocks are not common in production applications and
2402 just wait on the lock for a while before starting the check for a
2403 deadlock. Increasing this value reduces the amount of time
2404 wasted in needless deadlock checks, but slows down reporting of
2405 real deadlock errors. The default is 1000 (i.e., one second),
2406 which is probably about the smallest value you would want in
2407 practice. On a heavily loaded server you might want to raise it.
2408 Ideally the setting should exceed your typical transaction time,
2409 so as to improve the odds that a lock will be released before
2410 the waiter decides to check for deadlock.
2416 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
2419 The shared lock table is sized on the assumption that at most
2420 <varname>max_locks_per_transaction</varname> *
2421 <varname>max_connections</varname> distinct objects will need to
2422 be locked at any one time. The default, 64, has historically
2423 proven sufficient, but you might need to raise this value if you
2424 have clients that touch many different tables in a single
2425 transaction. This option can only be set at server start.
2433 <sect2 id="runtime-config-compatible">
2434 <title>Version and Platform Compatibility</title>
2436 <sect3 id="runtime-config-compatible-version">
2437 <title>Previous PostgreSQL Versions</title>
2441 <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
2442 <indexterm><primary>FROM</><secondary>missing</></>
2445 When <literal>true</>, tables that are referenced by a query will be
2446 automatically added to the <literal>FROM</> clause if not already
2447 present. The default is <literal>true</> for compatibility with
2448 previous releases of <productname>PostgreSQL</>. However, this
2449 behavior is not SQL-standard, and many people dislike it because it
2450 can mask mistakes. Set to <literal>false</> for the SQL-standard
2451 behavior of rejecting references to tables that are not listed in
2458 <term><varname>regex_flavor</varname> (<type>string</type>)</term>
2459 <indexterm><primary>regular expressions</></>
2462 The regular expression <quote>flavor</> can be set to
2463 <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
2464 The default is <literal>advanced</>. The <literal>extended</>
2465 setting may be useful for exact backwards compatibility with
2466 pre-7.4 releases of <productname>PostgreSQL</>.
2472 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
2473 <indexterm><primary>inheritance</></>
2476 This controls the inheritance semantics, in particular whether
2477 subtables are included by various commands by default. They were
2478 not included in versions prior to 7.1. If you need the old
2479 behavior you can set this variable to off, but in the long run
2480 you are encouraged to change your applications to use the
2481 <literal>ONLY</literal> key word to exclude subtables. See
2482 <xref linkend="ddl-inherit"> for more information about inheritance.
2488 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
2491 This controls whether <command>CREATE TABLE</command>
2492 and <command>CREATE TABLE AS</command> will include OIDs in
2493 newly-created tables, if neither <literal>WITH OIDS</literal>
2494 or <literal>WITHOUT OIDS</literal> have been specified. It
2495 also determines whether OIDs will be included in the table
2496 created by <command>SELECT INTO</command>. In
2497 <productname>PostgreSQL</productname> &version;
2498 <varname>default_with_oids</varname> defaults to
2499 true. This is also the behavior of previous versions
2500 of <productname>PostgreSQL</productname>. However, assuming
2501 that tables will contain OIDs by default is not
2502 encouraged. Therefore, this option will default to false in a
2503 future release of <productname>PostgreSQL</productname>.
2507 To ease compatibility with applications that make use of OIDs,
2508 this option should left enabled. To ease compatibility with
2509 future versions of <productname>PostgreSQL</productname>, this
2510 option should be disabled, and applications that require OIDs
2511 on certain tables should explictely specify <literal>WITH
2512 OIDS</literal> when issuing the <command>CREATE
2513 TABLE</command> statements for the tables in question.
2520 <sect3 id="runtime-config-compatible-clients">
2521 <title>Platform and Client Compatibility</title>
2525 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
2526 <indexterm><primary>IS NULL</></>
2529 When turned on, expressions of the form
2530 <literal><replaceable>expr</> = NULL</literal> (or <literal>NULL
2531 = <replaceable>expr</></literal>) are treated as
2532 <literal><replaceable>expr</> IS NULL</literal>, that is, they
2533 return true if <replaceable>expr</> evaluates to the null value,
2534 and false otherwise. The correct behavior of
2535 <literal><replaceable>expr</> = NULL</literal> is to always
2536 return null (unknown). Therefore this option defaults to off.
2540 However, filtered forms in <productname>Microsoft
2541 Access</productname> generate queries that appear to use
2542 <literal><replaceable>expr</> = NULL</literal> to test for
2543 null values, so if you use that interface to access the database you
2544 might want to turn this option on. Since expressions of the
2545 form <literal><replaceable>expr</> = NULL</literal> always
2546 return the null value (using the correct interpretation) they are not
2547 very useful and do not appear often in normal applications, so
2548 this option does little harm in practice. But new users are
2549 frequently confused about the semantics of expressions
2550 involving null values, so this option is not on by default.
2554 Note that this option only affects the literal <literal>=</>
2555 operator, not other comparison operators or other expressions
2556 that are computationally equivalent to some expression
2557 involving the equals operator (such as <literal>IN</literal>).
2558 Thus, this option is not a general fix for bad programming.
2562 Refer to <xref linkend="functions-comparison"> for related information.
2571 <sect2 id="runtime-config-compiler">
2572 <title>Compiled-in Options</title>
2575 The following options are available read-only, and are determined
2576 at source code compile time. As such, they have been excluded from the
2577 sample <filename>postgresql.conf</> file. They determine various aspects
2578 of <productname>PostgreSQL</productname> behavior that may be of interest
2579 to certain applications, particularly administrative front-ends.
2585 <term><varname>block_size</varname> (<type>integer</type>)</term>
2588 Shows the size of a disk block. It is determined by the value
2589 of <literal>BLCKSZ</> when building the server. The default
2590 value is 8192 bytes. The <varname>shared_buffers</varname> setting is
2591 influenced by <varname>block_size</varname>. See
2592 <xref linkend="runtime-config-resource"> for information.
2598 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
2601 Shows <literal>on</literal> if <productname>PostgreSQL</productname>
2602 was built with support for 64-bit integer dates and times. It is
2603 set by configuring with <literal>--enable-integer-datetimes</literal>.
2604 The default value is <literal>off</literal>.
2610 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
2613 Shows the maximum number of function arguments. It is determined by
2614 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
2615 default value is 32.
2621 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
2624 Shows the maximum identifier length. It is determined as one
2625 less than the value of <literal>NAMEDATALEN</> when building
2626 the server. The default value of <literal>NAMEDATALEN</> is
2627 64; therefore the default
2628 <varname>max_identifier_length</varname> is 63.
2634 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
2637 Shows the maximum number of index keys. It is determined by
2638 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
2639 default value is 32.
2647 <sect2 id="runtime-config-developer">
2648 <title>Developer Options</title>
2651 The following options are intended for work on the
2652 <productname>PostgreSQL</productname> source, and in some cases
2653 to assist with recovery of severely damaged databases. There
2654 should be no reason to use them in a production database setup.
2655 As such, they have been excluded from the sample
2656 <filename>postgresql.conf</> file. Note that many of these
2657 options require special source compilation flags to work at all.
2662 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
2665 Turns on various assertion checks. This is a debugging aid. If
2666 you are experiencing strange problems or crashes you might want
2667 to turn this on, as it might expose programming mistakes. To use
2668 this option, the macro <symbol>USE_ASSERT_CHECKING</symbol>
2669 must be defined when <productname>PostgreSQL</productname> is
2670 built (accomplished by the <command>configure</command> option
2671 <option>--enable-cassert</option>). Note that
2672 <varname>debug_assertions</varname> defaults to on if
2673 <productname>PostgreSQL</productname> has been built with
2680 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
2683 If nonzero, a delay of this many seconds occurs just after a new
2684 server process is forked, before it conducts the authentication
2685 process. This is intended to give an opportunity to attach to the
2686 server process with a debugger to trace down misbehavior in
2693 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
2696 Generates a great amount of debugging output for the
2697 <command>LISTEN</command> and <command>NOTIFY</command>
2698 commands. <varname>client_min_messages</varname> or
2699 <varname>log_min_messages</varname> must be
2700 <literal>DEBUG1</literal> or lower to send this output to the
2701 client or server log, respectively.
2707 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
2708 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
2709 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
2710 <term><varname>trace_lock_oidmin</varname> (<type>boolean</type>)</term>
2711 <term><varname>trace_lock_table</varname> (<type>boolean</type>)</term>
2712 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
2713 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
2716 Various other code tracing and debugging options.
2722 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
2725 If true, emit WAL-related debugging output. This option is
2726 only available if the <symbol>WAL_DEBUG</symbol> macro was
2727 defined when <productname>PostgreSQL</productname> was
2734 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
2737 Detection of a damaged page header normally causes
2738 <productname>PostgreSQL</> to report an error, aborting the current
2739 transaction. Setting <varname>zero_damaged_pages</> to true causes
2740 the system to instead report a warning, zero out the damaged page,
2741 and continue processing. This behavior <emphasis>will destroy data</>,
2742 namely all the rows on the damaged page. But it allows you to get
2743 past the error and retrieve rows from any undamaged pages that may
2744 be present in the table. So it is useful for recovering data if
2745 corruption has occurred due to hardware or software error. You should
2746 generally not set this true until you have given up hope of recovering
2747 data from the damaged page(s) of a table. The
2748 default setting is off, and it can only be changed by a superuser.
2754 <sect2 id="runtime-config-short">
2755 <title>Short Options</title>
2758 For convenience there are also single letter command-line option switches
2759 available for some parameters. They are described in <xref
2760 linkend="runtime-config-short-table">.
2763 <table id="runtime-config-short-table">
2764 <title>Short option key</title>
2768 <entry>Short option</entry>
2769 <entry>Equivalent</entry>
2775 <entry><option>-B <replaceable>x</replaceable></option></entry>
2776 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
2779 <entry><option>-d <replaceable>x</replaceable></option></entry>
2780 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
2783 <entry><option>-F</option></entry>
2784 <entry><literal>fsync = off</></entry>
2787 <entry><option>-h <replaceable>x</replaceable></option></entry>
2788 <entry><literal>virtual_host = <replaceable>x</replaceable></></entry>
2791 <entry><option>-i</option></entry>
2792 <entry><literal>tcpip_socket = on</></entry>
2795 <entry><option>-k <replaceable>x</replaceable></option></entry>
2796 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
2799 <entry><option>-l</option></entry>
2800 <entry><literal>ssl = on</></entry>
2803 <entry><option>-N <replaceable>x</replaceable></option></entry>
2804 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
2807 <entry><option>-p <replaceable>x</replaceable></option></entry>
2808 <entry><literal>port = <replaceable>x</replaceable></></entry>
2813 <option>-fi</option>, <option>-fh</option>,
2814 <option>-fm</option>, <option>-fn</option>,
2815 <option>-fs</option>, <option>-ft</option><footnote
2816 id="fn.runtime-config-short">
2818 For historical reasons, these options must be passed to
2819 the individual server process via the <option>-o</option>
2820 <command>postmaster</command> option, for example,
2822 $ <userinput>postmaster -o '-S 1024 -s'</userinput>
2824 or via <envar>PGOPTIONS</envar> from the client side, as
2830 <literal>enable_indexscan=off</>,
2831 <literal>enable_hashjoin=off</>,
2832 <literal>enable_mergejoin=off</>,
2833 <literal>enable_nestloop=off</>,
2834 <literal>enable_seqscan=off</>,
2835 <literal>enable_tidscan=off</>
2840 <entry><option>-s</option><footnoteref linkend="fn.runtime-config-short"></entry>
2841 <entry><literal>log_statement_stats = on</></entry>
2845 <entry><option>-S <replaceable>x</replaceable></option><footnoteref linkend="fn.runtime-config-short">
2847 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
2851 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option><footnoteref linkend="fn.runtime-config-short"></entry>
2852 <entry><literal>log_parser_stats=on</>,
2853 <literal>log_planner_stats=on</>,
2854 <literal>log_executor_stats=on</></entry>
2864 <sect1 id="kernel-resources">
2865 <title>Managing Kernel Resources</title>
2868 A large <productname>PostgreSQL</> installation can quickly exhaust
2869 various operating system resource limits. (On some systems, the
2870 factory defaults are so low that you don't even need a really
2871 <quote>large</> installation.) If you have encountered this kind of
2872 problem, keep reading.
2875 <sect2 id="sysvipc">
2876 <title>Shared Memory and Semaphores</title>
2878 <indexterm zone="sysvipc">
2879 <primary>shared memory</primary>
2882 <indexterm zone="sysvipc">
2883 <primary>semaphores</primary>
2887 Shared memory and semaphores are collectively referred to as
2888 <quote><systemitem class="osname">System V</>
2889 <acronym>IPC</></quote> (together with message queues, which are not
2890 relevant for <productname>PostgreSQL</>). Almost all modern
2891 operating systems provide these features, but not all of them have
2892 them turned on or sufficiently sized by default, especially systems
2893 with BSD heritage. (For the <systemitem class="osname">QNX</> and
2894 <systemitem class="osname">BeOS</> ports, <productname>PostgreSQL</>
2895 provides its own replacement implementation of these facilities.)
2899 The complete lack of these facilities is usually manifested by an
2900 <errorname>Illegal system call</> error upon server start. In
2901 that case there's nothing left to do but to reconfigure your
2902 kernel. <productname>PostgreSQL</> won't work without them.
2906 When <productname>PostgreSQL</> exceeds one of the various hard
2907 <acronym>IPC</> limits, the server will refuse to start and
2908 should leave an instructive error message describing the problem
2909 encountered and what to do about it. (See also <xref
2910 linkend="postmaster-start-failures">.) The relevant kernel
2911 parameters are named consistently across different systems; <xref
2912 linkend="sysvipc-parameters"> gives an overview. The methods to set
2913 them, however, vary. Suggestions for some platforms are given below.
2914 Be warned that it is often necessary to reboot your machine, and
2915 possibly even recompile the kernel, to change these settings.
2919 <table id="sysvipc-parameters">
2920 <title><systemitem class="osname">System V</> <acronym>IPC</> parameters</>
2926 <entry>Description</>
2927 <entry>Reasonable values</>
2933 <entry><varname>SHMMAX</></>
2934 <entry>Maximum size of shared memory segment (bytes)</>
2935 <entry>250 kB + 8.2 kB * <varname>shared_buffers</> + 14.2 kB * <varname>max_connections</> up to infinity</entry>
2939 <entry><varname>SHMMIN</></>
2940 <entry>Minimum size of shared memory segment (bytes)</>
2945 <entry><varname>SHMALL</></>
2946 <entry>Total amount of shared memory available (bytes or pages)</>
2947 <entry>if bytes, same as <varname>SHMMAX</varname>; if pages, <literal>ceil(SHMMAX/PAGE_SIZE)</literal></>
2951 <entry><varname>SHMSEG</></>
2952 <entry>Maximum number of shared memory segments per process</>
2953 <entry>only 1 segment is needed, but the default is much higher</>
2957 <entry><varname>SHMMNI</></>
2958 <entry>Maximum number of shared memory segments system-wide</>
2959 <entry>like <varname>SHMSEG</> plus room for other applications</>
2963 <entry><varname>SEMMNI</></>
2964 <entry>Maximum number of semaphore identifiers (i.e., sets)</>
2965 <entry>at least <literal>ceil(max_connections / 16)</literal></>
2969 <entry><varname>SEMMNS</></>
2970 <entry>Maximum number of semaphores system-wide</>
2971 <entry><literal>ceil(max_connections / 16) * 17</literal> plus room for other applications</>
2975 <entry><varname>SEMMSL</></>
2976 <entry>Maximum number of semaphores per set</>
2977 <entry>at least 17</>
2981 <entry><varname>SEMMAP</></>
2982 <entry>Number of entries in semaphore map</>
2987 <entry><varname>SEMVMX</></>
2988 <entry>Maximum value of semaphore</>
2989 <entry>at least 1000 (The default is often 32767, don't change unless asked to.)</>
2998 <indexterm><primary>SHMMAX</primary></indexterm> The most important
2999 shared memory parameter is <varname>SHMMAX</>, the maximum size, in
3000 bytes, of a shared memory segment. If you get an error message from
3001 <function>shmget</> like <errorname>Invalid argument</>, it is
3002 possible that this limit has been exceeded. The size of the required
3003 shared memory segment varies both with the number of requested
3004 buffers (<option>-B</> option) and the number of allowed connections
3005 (<option>-N</> option), although the former is the most significant.
3006 (You can, as a temporary solution, lower these settings to eliminate
3007 the failure.) As a rough approximation, you can estimate the
3008 required segment size by multiplying the number of buffers and the
3009 block size (8 kB by default) plus ample overhead (at least half a
3010 megabyte). Any error message you might get will contain the size of
3011 the failed allocation request.
3015 Less likely to cause problems is the minimum size for shared
3016 memory segments (<varname>SHMMIN</>), which should be at most
3017 approximately 256 kB for <productname>PostgreSQL</> (it is
3018 usually just 1). The maximum number of segments system-wide
3019 (<varname>SHMMNI</>) or per-process (<varname>SHMSEG</>) should
3020 not cause a problem unless your system has them set to zero. Some
3021 systems also have a limit on the total amount of shared memory in
3022 the system; see the platform-specific instructions below.
3026 <productname>PostgreSQL</> uses one semaphore per allowed connection
3027 (<option>-N</> option), in sets of 16. Each such set will also
3028 contain a 17th semaphore which contains a <quote>magic
3029 number</quote>, to detect collision with semaphore sets used by
3030 other applications. The maximum number of semaphores in the system
3031 is set by <varname>SEMMNS</>, which consequently must be at least
3032 as high as <varname>max_connections</> plus one extra for each 16
3033 allowed connections (see the formula in <xref
3034 linkend="sysvipc-parameters">). The parameter <varname>SEMMNI</>
3035 determines the limit on the number of semaphore sets that can
3036 exist on the system at one time. Hence this parameter must be at
3037 least <literal>ceil(max_connections / 16)</>. Lowering the number
3038 of allowed connections is a temporary workaround for failures,
3039 which are usually confusingly worded <errorname>No space
3040 left on device</>, from the function <function>semget</>.
3044 In some cases it might also be necessary to increase
3045 <varname>SEMMAP</> to be at least on the order of
3046 <varname>SEMMNS</>. This parameter defines the size of the semaphore
3047 resource map, in which each contiguous block of available semaphores
3048 needs an entry. When a semaphore set is freed it is either added to
3049 an existing entry that is adjacent to the freed block or it is
3050 registered under a new map entry. If the map is full, the freed
3051 semaphores get lost (until reboot). Fragmentation of the semaphore
3052 space could over time lead to fewer available semaphores than there
3057 The <varname>SEMMSL</> parameter, which determines how many
3058 semaphores can be in a set, must be at least 17 for
3059 <productname>PostgreSQL</>.
3063 Various other settings related to <quote>semaphore undo</>, such as
3064 <varname>SEMMNU</> and <varname>SEMUME</>, are not of concern
3065 for <productname>PostgreSQL</>.
3071 <term><systemitem class="osname">BSD/OS</></term>
3072 <indexterm><primary>BSD/OS</><secondary>IPC configuration</></>
3075 <title>Shared Memory</>
3077 By default, only 4 MB of shared memory is supported. Keep in
3078 mind that shared memory is not pageable; it is locked in RAM.
3079 To increase the amount of shared memory supported by your
3080 system, add the following to your kernel configuration
3081 file. A <varname>SHMALL</> value of 1024 represents 4 MB of
3082 shared memory. The following increases the maximum shared
3083 memory area to 32 MB:
3085 options "SHMALL=8192"
3086 options "SHMMAX=\(SHMALL*PAGE_SIZE\)"
3088 For those running 4.3 or later, you will probably need to increase
3089 <varname>KERNEL_VIRTUAL_MB</> above the default <literal>248</>.
3090 Once all changes have been made, recompile the kernel, and reboot.
3095 For those running 4.0 and earlier releases, use <command>bpatch</>
3096 to find the <varname>sysptsize</> value in the current
3097 kernel. This is computed dynamically at boot time.
3099 $ <userinput>bpatch -r sysptsize</>
3100 <computeroutput>0x9 = 9</>
3102 Next, add <varname>SYSPTSIZE</> as a hard-coded value in the
3103 kernel configuration file. Increase the value you found using
3104 <command>bpatch</>. Add 1 for every additional 4 MB of
3105 shared memory you desire.
3107 options "SYSPTSIZE=16"
3109 <varname>sysptsize</> cannot be changed by <command>sysctl</command>.
3113 <title>Semaphores</>
3115 You may need to increase the number of semaphores. By
3116 default, <productname>PostgreSQL</> allocates 34 semaphores,
3117 which is over half the default system total of 60. Set the
3118 values you want in your kernel configuration file, e.g.:
3121 options "SEMMNS=240"
3130 <term><systemitem class="osname">FreeBSD</></term>
3131 <term><systemitem class="osname">NetBSD</></term>
3132 <term><systemitem class="osname">OpenBSD</></term>
3133 <indexterm><primary>FreeBSD</><secondary>IPC configuration</></>
3134 <indexterm><primary>NetBSD</><secondary>IPC configuration</></>
3135 <indexterm><primary>OpenBSD</><secondary>IPC configuration</></>
3138 The options <varname>SYSVSHM</> and <varname>SYSVSEM</> need
3139 to be enabled when the kernel is compiled. (They are by
3140 default.) The maximum size of shared memory is determined by
3141 the option <varname>SHMMAXPGS</> (in pages). The following
3142 shows an example of how to set the various parameters:
3145 options SHMMAXPGS=4096
3154 (On <systemitem class="osname">NetBSD</> and <systemitem
3155 class="osname">OpenBSD</> the key word is actually
3156 <literal>option</literal> singular.)
3159 You might also want to configure your kernel to lock shared
3160 memory into RAM and prevent it from being paged out to swap.
3161 Use the <command>sysctl</> setting
3162 <literal>kern.ipc.shm_use_phys</>.
3169 <term><systemitem class="osname">HP-UX</></term>
3170 <indexterm><primary>HP-UX</><secondary>IPC configuration</></>
3173 The default settings tend to suffice for normal installations.
3174 On <productname>HP-UX</> 10, the factory default for
3175 <varname>SEMMNS</> is 128, which might be too low for larger
3179 <acronym>IPC</> parameters can be set in the <application>System
3180 Administration Manager</> (<acronym>SAM</>) under
3181 <menuchoice><guimenu>Kernel
3182 Configuration</><guimenuitem>Configurable Parameters</></>. Hit
3183 <guibutton>Create A New Kernel</> when you're done.
3190 <term><systemitem class="osname">Linux</></term>
3191 <indexterm><primary>Linux</><secondary>IPC configuration</></>
3194 The default shared memory limit (both
3195 <varname>SHMMAX</varname> and <varname>SHMALL</varname>) is 32
3196 MB in 2.2 kernels, but it can be changed in the
3197 <filename>proc</filename> file system (without reboot). For
3198 example, to allow 128 MB:
3200 <prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmall</userinput>
3201 <prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmmax</userinput>
3203 You could put these commands into a script run at boot-time.
3207 Alternatively, you can use <command>sysctl</command>, if
3208 available, to control these parameters. Look for a file
3209 called <filename>/etc/sysctl.conf</filename> and add lines
3210 like the following to it:
3212 kernel.shmall = 134217728
3213 kernel.shmmax = 134217728
3215 This file is usually processed at boot time, but
3216 <command>sysctl</command> can also be called
3221 Other parameters are sufficiently sized for any application. If
3222 you want to see for yourself look in
3223 <filename>/usr/src/linux/include/asm-<replaceable>xxx</>/shmpara
3224 m.h</> and <filename>/usr/src/linux/include/linux/sem.h</>.
3231 <term><systemitem class="osname">MacOS X</></term>
3232 <indexterm><primary>MacOS X</><secondary>IPC configuration</></>
3235 In OS X 10.2 and earlier, edit the file
3236 <filename>/System/Library/StartupItems/SystemTuning/SystemTuning</>
3237 and change the values in the following commands:
3239 sysctl -w kern.sysv.shmmax
3240 sysctl -w kern.sysv.shmmin
3241 sysctl -w kern.sysv.shmmni
3242 sysctl -w kern.sysv.shmseg
3243 sysctl -w kern.sysv.shmall
3245 In OS X 10.3, these commands have been moved to <filename>/etc/rc</>
3246 and must be edited there.
3253 <term><systemitem class="osname">SCO OpenServer</></term>
3254 <indexterm><primary>SCO OpenServer</><secondary>IPC configuration</></>
3257 In the default configuration, only 512 kB of shared memory per
3258 segment is allowed, which is about enough for <option>-B 24 -N
3259 12</>. To increase the setting, first change to the directory
3260 <filename>/etc/conf/cf.d</>. To display the current value of
3261 <varname>SHMMAX</>, run
3263 ./configure -y SHMMAX
3265 To set a new value for <varname>SHMMAX</>, run
3267 ./configure SHMMAX=<replaceable>value</>
3269 where <replaceable>value</> is the new value you want to use
3270 (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel:
3281 <term><systemitem class="osname">Solaris</></term>
3282 <indexterm><primary>Solaris</><secondary>IPC configuration</></>
3285 At least in version 2.6, the default maximum size of a shared
3286 memory segments is too low for <productname>PostgreSQL</>. The
3287 relevant settings can be changed in <filename>/etc/system</>,
3290 set shmsys:shminfo_shmmax=0x2000000
3291 set shmsys:shminfo_shmmin=1
3292 set shmsys:shminfo_shmmni=256
3293 set shmsys:shminfo_shmseg=256
3295 set semsys:seminfo_semmap=256
3296 set semsys:seminfo_semmni=512
3297 set semsys:seminfo_semmns=512
3298 set semsys:seminfo_semmsl=32
3300 You need to reboot for the changes to take effect.
3305 url="http://www.sunworld.com/swol-09-1997/swol-09-insidesolaris.html"></>
3306 for information on shared memory under
3307 <productname>Solaris</>.
3314 <term><systemitem class="osname">UnixWare</></term>
3315 <indexterm><primary>UnixWare</><secondary>IPC configuration</></>
3318 On <productname>UnixWare</> 7, the maximum size for shared
3319 memory segments is 512 kB in the default configuration. This
3320 is enough for about <option>-B 24 -N 12</>. To display the
3321 current value of <varname>SHMMAX</>, run
3323 /etc/conf/bin/idtune -g SHMMAX
3325 which displays the current, default, minimum, and maximum
3326 values. To set a new value for <varname>SHMMAX</>,
3329 /etc/conf/bin/idtune SHMMAX <replaceable>value</>
3331 where <replaceable>value</> is the new value you want to use
3332 (in bytes). After setting <varname>SHMMAX</>, rebuild the
3335 /etc/conf/bin/idbuild -B
3348 <title>Resource Limits</title>
3351 Unix-like operating systems enforce various kinds of resource limits
3352 that might interfere with the operation of your
3353 <productname>PostgreSQL</productname> server. Of particular
3354 importance are limits on the number of processes per user, the
3355 number of open files per process, and the amount of memory available
3356 to each process. Each of these have a <quote>hard</quote> and a
3357 <quote>soft</quote> limit. The soft limit is what actually counts
3358 but it can be changed by the user up to the hard limit. The hard
3359 limit can only be changed by the root user. The system call
3360 <function>setrlimit</function> is responsible for setting these
3361 parameters. The shell's built-in command <command>ulimit</command>
3362 (Bourne shells) or <command>limit</command> (<application>csh</>) is
3363 used to control the resource limits from the command line. On
3364 BSD-derived systems the file <filename>/etc/login.conf</filename>
3365 controls the various resource limits set during login. See the
3366 operating system documentation for details. The relevant
3367 parameters are <varname>maxproc</varname>,
3368 <varname>openfiles</varname>, and <varname>datasize</varname>. For
3373 :datasize-cur=256M:\
3375 :openfiles-cur=256:\
3378 (<literal>-cur</literal> is the soft limit. Append
3379 <literal>-max</literal> to set the hard limit.)
3383 Kernels can also have system-wide limits on some resources.
3387 On <productname>Linux</productname>
3388 <filename>/proc/sys/fs/file-max</filename> determines the
3389 maximum number of open files that the kernel will support. It can
3390 be changed by writing a different number into the file or by
3391 adding an assignment in <filename>/etc/sysctl.conf</filename>.
3392 The maximum limit of files per process is fixed at the time the
3393 kernel is compiled; see
3394 <filename>/usr/src/linux/Documentation/proc.txt</filename> for
3402 The <productname>PostgreSQL</productname> server uses one process
3403 per connection so you should provide for at least as many processes
3404 as allowed connections, in addition to what you need for the rest
3405 of your system. This is usually not a problem but if you run
3406 several servers on one machine things might get tight.
3410 The factory default limit on open files is often set to
3411 <quote>socially friendly</quote> values that allow many users to
3412 coexist on a machine without using an inappropriate fraction of
3413 the system resources. If you run many servers on a machine this
3414 is perhaps what you want, but on dedicated servers you may want to
3419 On the other side of the coin, some systems allow individual
3420 processes to open large numbers of files; if more than a few
3421 processes do so then the system-wide limit can easily be exceeded.
3422 If you find this happening, and you do not want to alter the system-wide
3423 limit, you can set <productname>PostgreSQL</productname>'s
3424 <varname>max_files_per_process</varname> configuration parameter to
3425 limit the consumption of open files.
3430 <title>Linux Memory Overcommit</title>
3433 In Linux 2.4 and later, the default virtual memory behavior is not
3434 optimal for <productname>PostgreSQL</productname>. Because of the
3435 way that the kernel implements memory overcommit, the kernel may
3436 terminate the <productname>PostgreSQL</productname> server (the
3437 <filename>postmaster</filename> process) if the memory demands of
3438 another process cause the system to run out of virtual memory.
3442 If this happens, you will see a kernel message that looks like
3443 this (consult your system documentation and configuration on where
3444 to look for such a message):
3446 Out of Memory: Killed process 12345 (postmaster).
3448 This indicates that the <filename>postmaster</filename> process
3449 has been terminated due to memory pressure.
3450 Although existing database connections will continue to function
3451 normally, no new connections will be accepted. To recover,
3452 <productname>PostgreSQL</productname> will need to be restarted.
3456 One way to avoid this problem is to run
3457 <productname>PostgreSQL</productname>
3458 on a machine where you can be sure that other processes will not
3459 run the machine out of memory.
3463 On Linux 2.6 and later, a better solution is to modify the kernel's
3464 behavior so that it will not <quote>overcommit</> memory. This is
3465 done by selecting strict overcommit mode via sysctl:
3467 sysctl -w vm.overcommit_memory=2
3469 or placing an equivalent entry in <filename>/etc/sysctl.conf</>.
3470 You may also wish to modify the related setting
3471 <literal>vm.overcommit_ratio</>. For details see the kernel documentation
3472 file <filename>Documentation/vm/overcommit-accounting</>.
3476 Some vendors' Linux 2.4 kernels are reported to have early versions
3477 of the 2.6 overcommit sysctl. However, setting
3478 <literal>vm.overcommit_memory</> to 2
3479 on a kernel that does not have the relevant code will make
3480 things worse not better. It is recommended that you inspect
3481 the actual kernel source code (see the function
3482 <function>vm_enough_memory</> in the file <filename>mm/mmap.c</>)
3483 to verify what is supported in your copy before you try this in a 2.4
3484 installation. The presence of the <filename>overcommit-accounting</>
3485 documentation file should <emphasis>not</> be taken as evidence that the
3486 feature is there. If in any doubt, consult a kernel expert or your
3493 <sect1 id="postmaster-shutdown">
3494 <title>Shutting Down the Server</title>
3496 <indexterm zone="postmaster-shutdown">
3497 <primary>shutdown</>
3501 There are several ways to shut down the database server. You control
3502 the type of shutdown by sending different signals to the
3503 <command>postmaster</command> process.
3507 <term><systemitem>SIGTERM</systemitem><indexterm><primary>SIGTERM</></></term>
3510 After receiving <systemitem>SIGTERM</systemitem>, the server
3511 disallows new connections, but lets existing sessions end their
3512 work normally. It shuts down only after all of the sessions
3513 terminate normally. This is the <firstterm>Smart
3514 Shutdown</firstterm>.
3520 <term><systemitem>SIGINT</systemitem><indexterm><primary>SIGINT</></></term>
3523 The server disallows new connections and sends all existing
3524 server processes <systemitem>SIGTERM</systemitem>, which will cause them
3525 to abort their current transactions and exit promptly. It then
3526 waits for the server processes to exit and finally shuts down. This is the
3527 <firstterm>Fast Shutdown</firstterm>.
3533 <term><systemitem>SIGQUIT</systemitem><indexterm><primary>SIGQUIT</></></term>
3536 This is the <firstterm>Immediate Shutdown</firstterm>, which
3537 will cause the <command>postmaster</command> process to send a
3538 <systemitem>SIGQUIT</systemitem> to all child processes and exit
3539 immediately (without properly shutting itself down). The child processes
3540 likewise exit immediately upon receiving
3541 <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by
3542 replaying the WAL log) upon next start-up. This is recommended
3543 only in emergencies.
3552 It is best not to use <systemitem>SIGKILL</systemitem> to shut down
3553 the server. This will prevent the server from releasing
3554 shared memory and semaphores, which may then have to be done by
3560 The <acronym>PID</> of the <command>postmaster</command> process can be found using the
3561 <command>ps</command> program, or from the file
3562 <filename>postmaster.pid</filename> in the data directory. So for
3563 example, to do a fast shutdown:
3565 $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
3569 The program <command>pg_ctl</command> is a shell script
3570 that provides a more convenient interface for shutting down the
3575 <sect1 id="ssl-tcp">
3576 <title>Secure TCP/IP Connections with SSL</title>
3578 <indexterm zone="ssl-tcp">
3579 <primary>SSL</primary>
3583 <productname>PostgreSQL</> has native support for using
3584 <acronym>SSL</> connections to encrypt client/server communications
3585 for increased security. This requires that
3586 <productname>OpenSSL</productname> is installed on both client and
3587 server systems and that support in <productname>PostgreSQL</> is
3588 enabled at build time (see <xref linkend="installation">).
3592 With <acronym>SSL</> support compiled in, the
3593 <productname>PostgreSQL</> server can be started with
3594 <acronym>SSL</> enabled by setting the parameter
3595 <varname>ssl</varname> to on in <filename>postgresql.conf</>. When
3596 starting in <acronym>SSL</> mode, the server will look for the
3597 files <filename>server.key</> and <filename>server.crt</> in the
3598 data directory, which should contain the server private key
3599 and certificate, respectively. These files must be set up correctly
3600 before an <acronym>SSL</>-enabled server can start. If the private key is
3601 protected with a passphrase, the server will prompt for the
3602 passphrase and will not start until it has been entered.
3606 The server will listen for both standard and <acronym>SSL</>
3607 connections on the same TCP port, and will negotiate with any
3608 connecting client on whether to use <acronym>SSL</>. See <xref
3609 linkend="auth-pg-hba-conf"> about how to force the server to
3610 require use of <acronym>SSL</> for certain connections.
3614 For details on how to create your server private key and certificate,
3615 refer to the <productname>OpenSSL</> documentation. A simple
3616 self-signed certificate can be used to get started for testing, but a
3617 certificate signed by a certificate authority (<acronym>CA</>) (either one of the global
3618 <acronym>CAs</> or a local one) should be used in production so the
3619 client can verify the server's identity. To create a quick
3620 self-signed certificate, use the following
3621 <productname>OpenSSL</productname> command:
3623 openssl req -new -text -out server.req
3625 Fill out the information that <command>openssl</> asks for. Make sure
3626 that you enter the local host name as <quote>Common Name</>; the challenge
3627 password can be left blank. The program will generate a key that is
3628 passphrase protected; it will not accept a passphrase that is less
3629 than four characters long. To remove the passphrase (as you must if
3630 you want automatic start-up of the server), run the commands
3632 openssl rsa -in privkey.pem -out server.key
3635 Enter the old passphrase to unlock the existing key. Now do
3637 openssl req -x509 -in server.req -text -key server.key -out server.crt
3638 chmod og-rwx server.key
3640 to turn the certificate into a self-signed certificate and to copy the
3641 key and certificate to where the server will look for them.
3645 <sect1 id="ssh-tunnels">
3646 <title>Secure TCP/IP Connections with <application>SSH</application> Tunnels</title>
3648 <indexterm zone="ssh-tunnels">
3649 <primary>ssh</primary>
3653 One can use <application>SSH</application> to encrypt the network
3654 connection between clients and a
3655 <productname>PostgreSQL</productname> server. Done properly, this
3656 provides an adequately secure network connection.
3660 First make sure that an <application>SSH</application> server is
3661 running properly on the same machine as the
3662 <productname>PostgreSQL</productname> server and that you can log in using
3663 <command>ssh</command> as some user. Then you can establish a secure
3664 tunnel with a command like this from the client machine:
3666 ssh -L 3333:foo.com:5432 joe@foo.com
3668 The first number in the <option>-L</option> argument, 3333, is the
3669 port number of your end of the tunnel; it can be chosen freely. The
3670 second number, 5432, is the remote end of the tunnel: the port
3671 number your server is using. The name or the address in between
3672 the port numbers is the host with the database server you are going
3673 to connect to. In order to connect to the database server using
3674 this tunnel, you connect to port 3333 on the local machine:
3676 psql -h localhost -p 3333 template1
3678 To the database server it will then look as though you are really
3679 user <literal>joe@foo.com</literal> and it will use whatever
3680 authentication procedure was set up for this user. In order for the
3681 tunnel setup to succeed you must be allowed to connect via
3682 <command>ssh</command> as <literal>joe@foo.com</literal>, just
3683 as if you had attempted to use <command>ssh</command> to set up a
3689 Several other applications exist that can provide secure tunnels using
3690 a procedure similar in concept to the one just described.
3698 <!-- Keep this comment at the end of the file
3703 sgml-minimize-attributes:nil
3704 sgml-always-quote-attributes:t
3707 sgml-parent-document:nil
3708 sgml-default-dtd-file:"./reference.ced"
3709 sgml-exposed-tags:nil
3710 sgml-local-catalogs:("/usr/lib/sgml/catalog")
3711 sgml-local-ecat-files:nil