2 $Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.104 2002/03/01 22:45:05 petere Exp $
6 <Title>Server Runtime 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 the world at
22 large, it is advisable to run <productname>PostgreSQL</productname> under a separate user
23 account. This user account should only own the data itself that is
24 being managed by the server, and should not be shared with other
25 daemons. (Thus, using the user <quote>nobody</quote> is a bad
26 idea.) It is not advisable to install the executables as owned by
27 this user account because that runs the risk of user-defined
28 functions gone astray or any other exploits compromising the
33 To add a user account to your system, look for a command
34 <command>useradd</command> or <command>adduser</command>. The user
35 name <systemitem>postgres</systemitem> is often used but by no means
40 <sect1 id="creating-cluster">
41 <title>Creating a database cluster</title>
44 <primary>database cluster</primary>
48 <primary>data area</primary>
49 <see>database cluster</see>
53 Before you can do anything, you must initialize a database storage
54 area on disk. We call this a <firstterm>database cluster</firstterm>.
55 (<acronym>SQL</acronym> speaks of a catalog cluster instead.) A
56 database cluster is a collection of databases that will be accessible
57 through a single instance of a running database server. After
58 initialization, a database cluster will contain one database named
59 <literal>template1</literal>. As the name suggests, this will be used
60 as a template for subsequently created databases; it should not be
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>, 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 being logged into
80 the <productname>PostgreSQL</productname> user account, which is described in the previous
87 <primary><envar>PGDATA</envar></primary>
89 As an alternative to the <option>-D</option> option, you can set
90 the environment variable <envar>PGDATA</envar>.
95 <command>initdb</command> will attempt to create the directory you
96 specify if it does not already exist. It is likely that it won't
97 have the permission to do so (if you followed our advice and
98 created an unprivileged account). In that case you should create the
99 directory yourself (as root) and transfer ownership of it to the
100 <productname>PostgreSQL</productname> user account. Here is how this might work:
102 root# <userinput>mkdir /usr/local/pgsql/data</userinput>
103 root# <userinput>chown postgres /usr/local/pgsql/data</userinput>
104 root# <userinput>su postgres</userinput>
105 postgres$ <userinput>initdb -D /usr/local/pgsql/data</userinput>
110 <command>initdb</command> will refuse to run if the data directory
111 looks like it belongs to an already initialized installation.
115 Because the data directory contains all the data stored in the
116 database, it is essential that it be well secured from unauthorized
117 access. <command>initdb</command> therefore revokes access
118 permissions from everyone but the <productname>PostgreSQL</productname> user account.
122 However, while the directory contents are secure, the default
123 <filename>pg_hba.conf</filename> authentication method of
124 <literal>trust</literal> allows any local user to connect to the database
125 and even become the database superuser. If you don't trust other local
126 users, we recommend you use <command>initdb</command>'s option
127 <option>-W</option> or <option>--pwprompt</option> to assign a
128 password to the database superuser. After <command>initdb</command>,
129 modify <filename>pg_hba.conf</filename> 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, possibly
132 more convenient approaches include using <literal>ident</literal>
133 authentication or file system permissions to restrict connections. See
134 <xref linkend="client-authentication"> for more information.)
138 <indexterm><primary>LC_COLLATE</></>
139 One surprise you might encounter while running <command>initdb</command> is
140 a notice similar to this one:
142 NOTICE: Initializing database with en_US collation order.
143 This locale setting will prevent use of index optimization for
144 LIKE and regexp searches. If you are concerned about speed of
145 such queries, you may wish to set LC_COLLATE to "C" and
146 re-initdb. For more information see the Administrator's Guide.
148 This notice is intended to warn you that the currently selected locale
149 will cause indexes to be sorted in an order that prevents them from
150 being used for LIKE and regular-expression searches. If you need
151 good performance of such searches, you should set your current locale
152 to <literal>C</> and re-run <command>initdb</command>. On most systems, setting the
153 current locale is done by changing the value of the environment variable
154 <literal>LC_ALL</literal> or <literal>LANG</literal>. The sort order used
155 within a particular database cluster is set by <command>initdb</command>
156 and cannot be changed later, short of dumping all data, rerunning <command>initdb</command>,
157 and reloading the data. So it's important to make this choice correctly now.
161 <sect1 id="postmaster-start">
162 <title>Starting the database server</title>
166 <primary>postmaster</primary>
168 Before anyone can access the database you must start the database
169 server. The database server is called
170 <firstterm>postmaster</firstterm>.
171 The postmaster must know where to find the data it is supposed
172 to work on. This is done with the <option>-D</option> option. Thus,
173 the simplest way to start the server is, for example,
175 $ <userinput>postmaster -D /usr/local/pgsql/data</userinput>
177 which will leave the server running in the foreground. This must
178 again be done while logged into the <productname>PostgreSQL</productname> user account. Without
179 a <option>-D</option>, the server will try to use the data
180 directory in the environment variable <envar>PGDATA</envar>; if
181 neither of these works it will fail.
185 To start the <application>postmaster</application> in the
186 background, use the usual shell syntax:
188 $ <userinput>postmaster -D /usr/local/pgsql/data > logfile 2>&1 &</userinput>
190 It is an extremely good idea to keep the server's <systemitem>stdout</> and <systemitem>stderr</>
191 output around somewhere, as suggested here. It will help both for auditing
192 purposes and to diagnose problems.
193 (See <xref linkend="logfile-maintenance"> for a more thorough discussion
194 of log file handling.)
199 <primary>TCP/IP</primary>
201 The postmaster also takes a number of other command line options.
202 For more information see the reference page and <xref linkend="runtime-config"> below.
203 In particular, in order for the server to accept
204 TCP/IP connections (rather than just Unix domain socket ones), you
205 must also specify the <option>-i</option> option.
210 <primary>pg_ctl</primary>
212 This shell syntax can get tedious quickly. Therefore the shell
213 script wrapper <application>pg_ctl</application> is provided that
214 encapsulates some of the tasks. E.g.,
216 pg_ctl start -l logfile
218 will start the server in the background and put the output into the
219 named log file. The <option>-D</option> option has the same
220 meaning as when invoking postmaster directly.
221 <application>pg_ctl</application> also implements a symmetric
222 <quote>stop</quote> operation.
226 Normally, you will want to start the database server when the
227 computer boots up. This is not required; the
228 <productname>PostgreSQL</productname> server can be run
229 successfully from non-privileged accounts without root
234 Different systems have different conventions for starting up
235 daemons at boot time, so you are advised to familiarize yourself
236 with them. Many systems have a file
237 <filename>/etc/rc.local</filename> or
238 <filename>/etc/rc.d/rc.local</filename> which is almost certainly
239 no bad place to put such a command. Whatever you do, the server
240 must be run by the <productname>PostgreSQL</productname> user account
241 <emphasis>and not by root</emphasis> or any other user. Therefore
242 you probably always want to form your command lines along the lines
243 of <literal>su -c '...' postgres</literal>, for example:
245 su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres
250 Here are a few more operating system specific suggestions. (Always
251 replace the proper installation directory and the user name you
257 For <productname>FreeBSD</productname>, take a look at the file
258 <filename>contrib/start-scripts/freebsd</filename> in the
259 <productname>PostgreSQL</productname> source distribution.
260 <indexterm><primary>FreeBSD</></>
266 On <productname>OpenBSD</productname>, add the following lines
267 to the file <filename>/etc/rc.local</filename>:
268 <indexterm><primary>OpenBSD</></>
270 if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postmaster ]; then
271 su - -c '/usr/local/pgsql/bin/pg_ctl start -l /var/postgresql/log -s' postgres
272 echo -n ' postgresql'
280 On <productname>Linux</productname> systems either add
281 <indexterm><primary>Linux</></>
283 /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
285 to <filename>/etc/rc.d/rc.local</filename> or look into the file
286 <filename>contrib/start-scripts/linux</filename> in the
287 <productname>PostgreSQL</productname> source distribution to
288 integrate the start and shutdown into the run level system.
294 On <productname>NetBSD</productname>, either use the
295 <productname>FreeBSD</productname> or
296 <productname>Linux</productname> start scripts, depending on
297 preference, as an example and place the file at
298 <filename>/usr/local/etc/rc.d/postgresql</filename>.
299 <indexterm><primary>NetBSD</></>
305 On <productname>Solaris</productname>, create a file called
306 <filename>/etc/init.d/postgresql</filename> to contain the following
308 <indexterm><primary>Solaris</></>
310 su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
312 Then, create a symbolic link to it in <filename>/etc/rc3.d</> as
313 <literal>S99postgresql</>.
321 While the <application>postmaster</application> is running, its
322 <acronym>PID</acronym> is in the file <filename>postmaster.pid</filename> in the data
323 directory. This is used as an interlock against multiple postmasters
324 running in the same data directory, and can also be used for
325 shutting down the postmaster.
328 <sect2 id="postmaster-start-failures">
329 <title>Server Start-up Failures</title>
332 There are several common reasons for the postmaster to fail to
333 start up. Check the postmaster's log file, or start it by hand
334 (without redirecting standard output or standard error) to see
335 what complaint messages appear. Some of the possible error
336 messages are reasonably self-explanatory, but here are some that
342 FATAL: StreamServerPort: bind() failed: Address already in use
343 Is another postmaster already running on that port?
345 This usually means just what it suggests: you tried to
346 start a second postmaster on the same port where one is already
347 running. However, if the kernel error message is not
348 <computeroutput>Address already in use</computeroutput> or some
349 variant of that wording, there may be a different problem. For
350 example, trying to start a postmaster on a reserved port number
351 may draw something like
353 $ <userinput>postmaster -i -p 666</userinput>
354 FATAL: StreamServerPort: bind() failed: Permission denied
355 Is another postmaster already running on that port?
362 IpcMemoryCreate: shmget(key=5440001, size=83918612, 01600) failed: Invalid argument
363 FATAL 1: ShmemCreate: cannot create region
365 probably means that your kernel's limit on the size of shared
366 memory areas is smaller than the buffer area that <productname>PostgreSQL</productname> is
367 trying to create (83918612 bytes in this example). Or it could
368 mean that you don't have System-V-style shared memory support
369 configured into your kernel at all. As a temporary workaround,
370 you can try starting the postmaster with a smaller-than-normal
371 number of buffers (<option>-B</option> switch). You will
372 eventually want to reconfigure your kernel to increase the
373 allowed shared memory size, however. You may see this message
374 when trying to start multiple postmasters on the same machine, if
375 their total space requests exceed the kernel limit.
381 IpcSemaphoreCreate: semget(key=5440026, num=16, 01600) failed: No space left on device
383 does <emphasis>not</emphasis> mean that you've run out of disk
384 space; it means that your kernel's limit on the number of System
385 V semaphores is smaller than the number
386 <productname>PostgreSQL</productname> wants to create. As above,
387 you may be able to work around the problem by starting the
388 postmaster with a reduced number of backend processes
389 (<option>-N</option> switch), but you'll eventually want to
390 increase the kernel limit.
394 If you get an <quote>illegal system call</> error, then it is likely that
395 shared memory or semaphores are not supported at all in your kernel. In
396 that case your only option is to re-configure the kernel to turn on these
401 Details about configuring <systemitem class="osname">System V</> <acronym>IPC</> facilities are given in
402 <xref linkend="sysvipc">.
406 <sect2 id="client-connection-problems">
407 <title>Client Connection Problems</title>
410 Although the possible error conditions on the client side are
411 both virtually infinite and application-dependent, a few of them
412 might be directly related to how the server was started up.
413 Conditions other than those shown below should be documented with
414 the respective client application.
419 psql: could not connect to server: Connection refused
420 Is the server running on host server.joe.com and accepting
421 TCP/IP connections on port 5432?
423 This is the generic <quote>I couldn't find a server to talk
424 to</quote> failure. It looks like the above when TCP/IP
425 communication is attempted. A common mistake is to forget the
426 <option>-i</option> option to allow the postmaster to accept TCP/IP
431 Alternatively, you'll get this when attempting
432 Unix-socket communication to a local postmaster:
434 psql: could not connect to server: Connection refused
435 Is the server running locally and accepting
436 connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
441 The last line is useful in verifying that the client is trying to
442 connect where it is supposed to. If there is in fact no
443 postmaster running there, the kernel error message will typically
444 be either <computeroutput>Connection refused</computeroutput> or
445 <computeroutput>No such file or directory</computeroutput>, as
446 illustrated. (It is particularly important to realize that
447 <computeroutput>Connection refused</computeroutput> in this
448 context does <emphasis>not</emphasis> mean that the postmaster
449 got your connection request and rejected it -- that case will
450 produce a different message, as shown in <xref
451 linkend="client-authentication-problems">.) Other error messages
452 such as <computeroutput>Connection timed out</computeroutput> may
453 indicate more fundamental problems, like lack of network
459 <sect1 id="runtime-config">
460 <Title>Run-time configuration</Title>
463 <primary>configuration</primary>
464 <secondary>server</secondary>
468 There are a lot of configuration parameters that affect the
469 behavior of the database system in some way or other. Here we
470 describe how to set them and the following subsections will
471 discuss each of them.
475 All parameter names are case-insensitive. Every parameter takes a
476 value of one of the four types Boolean, integer, floating point,
477 string as described below. Boolean values are
478 <literal>ON</literal>, <literal>OFF</literal>,
479 <literal>TRUE</literal>, <literal>FALSE</literal>,
480 <literal>YES</literal>, <literal>NO</literal>,
481 <literal>1</literal>, <literal>0</literal> (case-insensitive) or
482 any non-ambiguous prefix of these.
486 One way to set these options is to edit the file
487 <filename>postgresql.conf</filename> in the data directory.
488 (A default file is installed there.) An example of what
489 this file could look like is:
492 log_connections = yes
495 As you see, options are one per line. The equal sign between name
496 and value is optional. Whitespace is insignificant, blank lines
497 are ignored. Hash marks (<quote>#</quote>) introduce comments
503 <primary>SIGHUP</primary>
505 The configuration file is reread whenever the postmaster receives
506 a <systemitem>SIGHUP</> signal (which is most easily sent by means
507 of <literal>pg_ctl reload</>). The postmaster also propagates
508 this signal to all already-running backend processes, so that
509 existing sessions also get the new default.
510 Alternatively, you can send the signal to only one backend process
515 A second way to set these configuration parameters is to give them
516 as a command line option to the postmaster, such as
518 postmaster -c log_connections=yes -c syslog=2
520 which would have the same effect as the previous example.
521 Command-line options override any conflicting settings in
522 <filename>postgresql.conf</filename>.
526 Occasionally it is also useful to give a command line option to
527 one particular backend session only. The environment variable
528 <envar>PGOPTIONS</envar> can be used for this purpose on the
531 env PGOPTIONS='-c geqo=off' psql
533 (This works for any client application, not just
534 <application>psql</application>.) Note that this won't work for
535 options that are necessarily fixed once the server is started,
536 such as the port number.
540 Some options can be changed in individual SQL sessions with the
541 <command>SET</command> command, for example
543 => <userinput>SET ENABLE_SEQSCAN TO OFF;</userinput>
545 See the SQL command language reference for details on the syntax.
546 Furthermore, it is possible to assign a set of option settings to
547 a user or a database. Whenever a session is started, the default
548 settings for the user and database involved are loaded. The
549 commands <literal>ALTER DATABASE</literal> and <literal>ALTER
550 USER</literal>, respectively, are used to set this up.
553 <sect2 id="runtime-config-optimizer">
554 <title>Planner and Optimizer Tuning</title>
559 <term><varname>CPU_INDEX_TUPLE_COST</varname> (<type>floating point</type>)</term>
562 Sets the query optimizer's estimate of the cost of processing
563 each index tuple during an index scan. This is measured as a
564 fraction of the cost of a sequential page fetch.
570 <term><varname>CPU_OPERATOR_COST</varname> (<type>floating point</type>)</term>
573 Sets the optimizer's estimate of the cost of processing each
574 operator in a WHERE clause. This is measured as a fraction of
575 the cost of a sequential page fetch.
581 <term><varname>CPU_TUPLE_COST</varname> (<type>floating point</type>)</term>
584 Sets the query optimizer's estimate of the cost of processing
585 each tuple during a query. This is measured as a fraction of
586 the cost of a sequential page fetch.
592 <term><varname>EFFECTIVE_CACHE_SIZE</varname> (<type>floating point</type>)</term>
595 Sets the optimizer's assumption about the effective size of
596 the disk cache (that is, the portion of the kernel's disk
597 cache that will be used for
598 <productname>PostgreSQL</productname> data files). This is
599 measured in disk pages, which are normally 8 kB apiece.
605 <term><varname>ENABLE_HASHJOIN</varname> (<type>boolean</type>)</term>
608 Enables or disables the query planner's use of hash-join plan
609 types. The default is on. This is mostly useful to debug the
617 <primary>index scan</primary>
620 <term><varname>ENABLE_INDEXSCAN</varname> (<type>boolean</type>)</term>
623 Enables or disables the query planner's use of index-scan plan
624 types. The default is on. This is mostly useful to debug the
631 <term><varname>ENABLE_MERGEJOIN</varname> (<type>boolean</type>)</term>
634 Enables or disables the query planner's use of merge-join plan
635 types. The default is on. This is mostly useful to debug the
642 <term><varname>ENABLE_NESTLOOP</varname> (<type>boolean</type>)</term>
645 Enables or disables the query planner's use of nested-loop
646 join plans. It's not possible to suppress nested-loop joins
647 entirely, but turning this variable off discourages the
648 planner from using one if there is any other method available.
649 The default is on. This is mostly useful to debug the query
657 <primary>sequential scan</primary>
660 <term><varname>ENABLE_SEQSCAN</varname> (<type>boolean</type>)</term>
663 Enables or disables the query planner's use of sequential scan
664 plan types. It's not possible to suppress sequential scans
665 entirely, but turning this variable off discourages the
666 planner from using one if there is any other method available.
667 The default is on. This is mostly useful to debug the query
674 <term><varname>ENABLE_SORT</varname> (<type>boolean</type>)</term>
677 Enables or disables the query planner's use of explicit sort
678 steps. It's not possible to suppress explicit sorts entirely,
679 but turning this variable off discourages the planner from
680 using one if there is any other method available. The default
681 is on. This is mostly useful to debug the query planner.
687 <term><varname>ENABLE_TIDSCAN</varname> (<type>boolean</type>)</term>
690 Enables or disables the query planner's use of <acronym>TID</> scan plan
691 types. The default is on. This is mostly useful to debug the
699 <primary>genetic query optimization</primary>
702 <primary>GEQO</primary>
703 <see>genetic query optimization</see>
706 <term><varname>GEQO</varname> (<type>boolean</type>)</term>
709 Enables or disables genetic query optimization, which is an
710 algorithm that attempts to do query planning without
711 exhaustive search. This is on by default. See also the various
712 other <varname>GEQO_</varname> settings.
718 <term><varname>GEQO_EFFORT</varname> (<type>integer</type>)</term>
719 <term><varname>GEQO_GENERATIONS</varname> (<type>integer</type>)</term>
720 <term><varname>GEQO_POOL_SIZE</varname> (<type>integer</type>)</term>
721 <term><varname>GEQO_RANDOM_SEED</varname> (<type>integer</type>)</term>
722 <term><varname>GEQO_SELECTION_BIAS</varname> (<type>floating point</type>)</term>
725 Various tuning parameters for the genetic query optimization
726 algorithm: The pool size is the number of individuals in one
727 population. Valid values are between 128 and 1024. If it is
728 set to 0 (the default) a pool size of 2^(QS+1), where QS
729 is the number of FROM items in the query, is taken. The effort
730 is used to calculate a default for generations. Valid values
731 are between 1 and 80, 40 being the default. Generations
732 specifies the number of iterations in the algorithm. The
733 number must be a positive integer. If 0 is specified then
734 <literal>Effort * Log2(PoolSize)</literal> is used. The run time of the algorithm
735 is roughly proportional to the sum of pool size and
736 generations. The selection bias is the selective pressure
737 within the population. Values can be from 1.50 to 2.00; the
738 latter is the default. The random seed can be set to get
739 reproducible results from the algorithm. If it is set to -1
740 then the algorithm behaves non-deterministically.
746 <term><varname>GEQO_THRESHOLD</varname> (<type>integer</type>)</term>
749 Use genetic query optimization to plan queries with at least
750 this many <literal>FROM</> items involved. (Note that a <literal>JOIN</> construct
751 counts as only one <literal>FROM</> item.) The default is 11. For simpler
752 queries it is usually best to use the
753 deterministic, exhaustive planner. This parameter also controls
754 how hard the optimizer will try to merge subquery
755 <literal>FROM</literal> clauses into the upper query.
761 <term><varname>KSQO</varname> (<type>boolean</type>)</term>
764 The <firstterm>Key Set Query Optimizer</firstterm>
765 (<acronym>KSQO</acronym>) causes the query planner to convert
766 queries whose <literal>WHERE</> clause contains many OR'ed AND clauses
767 (such as <literal>WHERE (a=1 AND b=2) OR (a=2 AND b=3)
768 ...</literal>) into a union query. This method can be faster
769 than the default implementation, but it doesn't necessarily
770 give exactly the same results, since <literal>UNION</> implicitly adds a
771 <literal>SELECT DISTINCT</> clause to eliminate identical output rows.
772 <acronym>KSQO</acronym> is commonly used when working with products like
773 <productname>Microsoft Access</productname>, which tend to
774 generate queries of this form.
778 The <acronym>KSQO</acronym> algorithm used to be absolutely essential for queries
779 with many OR'ed AND clauses, but in
780 <productname>PostgreSQL</productname> 7.0 and later the standard
781 planner handles these queries fairly successfully. Hence the
788 <term><varname>RANDOM_PAGE_COST</varname> (<type>floating point</type>)</term>
791 Sets the query optimizer's estimate of the cost of a
792 nonsequentially fetched disk page. This is measured as a
793 multiple of the cost of a sequential page fetch.
802 Unfortunately, there is no well-defined method of determining
803 ideal values for the family of <quote>COST</quote> variables that
804 were just described. You are encouraged to experiment and share
812 <title>Logging and Debugging</title>
817 <term><varname>DEBUG_ASSERTIONS</varname> (<type>boolean</type>)</term>
820 Turns on various assertion checks. This is a debugging aid. If
821 you are experiencing strange problems or crashes you might
822 want to turn this on, as it might expose programming mistakes.
823 To use this option, the macro <literal>USE_ASSERT_CHECKING</literal>
824 must be defined when <productname>PostgreSQL</productname> is built (see the configure option
825 <literal>--enable-cassert</literal>). Note that
826 <literal>DEBUG_ASSERTIONS</literal> defaults to on if <productname>PostgreSQL</productname>
827 has been built this way.
833 <term><varname>DEBUG_LEVEL</varname> (<type>integer</type>)</term>
836 The higher this value is set, the more
837 <quote>debugging</quote> output of various sorts is generated
838 in the server log during operation. This option is 0 by
839 default, which means no debugging output. Values up to about 4
840 currently make sense.
846 <term><varname>DEBUG_PRINT_QUERY</varname> (<type>boolean</type>)</term>
847 <term><varname>DEBUG_PRINT_PARSE</varname> (<type>boolean</type>)</term>
848 <term><varname>DEBUG_PRINT_REWRITTEN</varname> (<type>boolean</type>)</term>
849 <term><varname>DEBUG_PRINT_PLAN</varname> (<type>boolean</type>)</term>
850 <term><varname>DEBUG_PRETTY_PRINT</varname> (<type>boolean</type>)</term>
853 These flags enable various debugging output to be sent to the
854 server log. For each executed query, prints either the query text,
855 the resulting parse tree, the query rewriter output, or the execution
856 plan. <option>DEBUG_PRETTY_PRINT</option> indents these displays
857 to produce a more readable but much longer output format.
858 Setting <option>DEBUG_LEVEL</option> above zero implicitly turns
859 on some of these flags.
865 <term><varname>HOSTNAME_LOOKUP</varname> (<type>boolean</type>)</term>
868 By default, connection logs only show the IP address of the
869 connecting host. If you want it to show the host name you can
870 turn this on, but depending on your host name resolution setup
871 it might impose a non-negligible performance penalty. This
872 option can only be set at server start.
878 <term><varname>LOG_CONNECTIONS</varname> (<type>boolean</type>)</term>
881 Prints a line informing about each successful connection in
882 the server log. This is off by default, although it is
883 probably very useful. This option can only be set at server
884 start or in the <filename>postgresql.conf</filename>
891 <term><varname>LOG_PID</varname> (<type>boolean</type>)</term>
894 Prefixes each server log message with the process ID of the
895 backend process. This is useful to sort out which messages
896 pertain to which connection. The default is off.
902 <term><varname>LOG_TIMESTAMP</varname> (<type>boolean</type>)</term>
905 Prefixes each server log message with a time stamp. The default
912 <term><varname>SHOW_QUERY_STATS</varname> (<type>boolean</type>)</term>
913 <term><varname>SHOW_PARSER_STATS</varname> (<type>boolean</type>)</term>
914 <term><varname>SHOW_PLANNER_STATS</varname> (<type>boolean</type>)</term>
915 <term><varname>SHOW_EXECUTOR_STATS</varname> (<type>boolean</type>)</term>
918 For each query, write performance statistics of the respective
919 module to the server log. This is a crude profiling
926 <term><varname>SHOW_SOURCE_PORT</varname> (<type>boolean</type>)</term>
929 Shows the outgoing port number of the connecting host in the
930 connection log messages. You could trace back the port number
931 to find out what user initiated the connection. Other than
932 that it's pretty useless and therefore off by default. This
933 option can only be set at server start.
939 <term><varname>STATS_COMMAND_STRING</varname> (<type>boolean</type>)</term>
940 <term><varname>STATS_BLOCK_LEVEL</varname> (<type>boolean</type>)</term>
941 <term><varname>STATS_ROW_LEVEL</varname> (<type>boolean</type>)</term>
944 These flags determine what information backends send to the statistics
945 collector process: current commands, block-level activity statistics,
946 or row-level activity statistics. All default to off. Enabling
947 statistics collection costs a small amount of time per query, but
948 is invaluable for debugging and performance tuning.
954 <term><varname>STATS_RESET_ON_SERVER_START</varname> (<type>boolean</type>)</term>
957 If on, collected statistics are zeroed out whenever the server
958 is restarted. If off, statistics are accumulated across server
959 restarts. The default is on. This option
960 can only be set at server start.
966 <term><varname>STATS_START_COLLECTOR</varname> (<type>boolean</type>)</term>
969 Controls whether the server should start the statistics-collection
970 subprocess. This is on by default, but may be turned off if you
971 know you have no interest in collecting statistics. This option
972 can only be set at server start.
978 <term><varname>SYSLOG</varname> (<type>integer</type>)</term>
981 <productname>PostgreSQL</productname> allows the use of
982 <systemitem>syslog</systemitem> for logging. If this option
983 is set to 1, messages go both to <systemitem>syslog</> and the standard
984 output. A setting of 2 sends output only to <systemitem>syslog</>. (Some
985 messages will still go to the standard output/error.) The
986 default is 0, which means <systemitem>syslog</> is off. This option must be
990 To use <systemitem>syslog</>, the build of
991 <productname>PostgreSQL</productname> must be configured with
992 the <option>--enable-syslog</option> option.
998 <term><varname>SYSLOG_FACILITY</varname> (<type>string</type>)</term>
1001 This option determines the <application>syslog</application>
1002 <quote>facility</quote> to be used when <application>syslog</application> is enabled.
1003 You may choose from LOCAL0, LOCAL1, LOCAL2, LOCAL3, LOCAL4,
1004 LOCAL5, LOCAL6, LOCAL7; the default is LOCAL0. See also the
1005 documentation of your system's
1006 <application>syslog</application>.
1012 <term><varname>SYSLOG_IDENT</varname> (<type>string</type>)</term>
1015 If logging to <application>syslog</> is enabled, this option determines the
1016 program name used to identify
1017 <productname>PostgreSQL</productname> messages in
1018 <application>syslog</application> log messages. The default
1019 is <literal>postgres</literal>.
1025 <term><varname>TRACE_NOTIFY</varname> (<type>boolean</type>)</term>
1028 Generates a great amount of debugging output for the
1029 <command>LISTEN</command> and <command>NOTIFY</command>
1038 <sect2 id="runtime-config-general">
1039 <title>General operation</title>
1044 <term><varname>AUSTRALIAN_TIMEZONES</varname> (<type>bool</type>)</term>
1045 <indexterm><primary>Australian time zones</></>
1048 If set to true, <literal>CST</literal>, <literal>EST</literal>,
1049 and <literal>SAT</literal> are interpreted as Australian
1050 time zones rather than as North American Central/Eastern
1051 time zones and Saturday. The default is false.
1057 <term><varname>AUTHENTICATION_TIMEOUT</varname> (<type>integer</type>)</term>
1058 <indexterm><primary>timeout</><secondary>authentication</></indexterm>
1061 Maximum time to complete client authentication, in seconds.
1062 If a would-be client has not completed the authentication protocol
1063 in this much time, the server unceremoniously breaks the connection.
1064 This prevents hung clients from occupying a connection indefinitely.
1065 This option can only be set at server start or in the
1066 <filename>postgresql.conf</filename> file.
1073 <primary>deadlock</primary>
1074 <secondary>timeout</secondary>
1077 <primary>timeout</primary>
1078 <secondary>deadlock</secondary>
1081 <term><varname>DEADLOCK_TIMEOUT</varname> (<type>integer</type>)</term>
1084 This is the amount of time, in milliseconds, to wait on a lock
1085 before checking to see if there is a deadlock condition or not.
1086 The check for deadlock is relatively slow, so we don't want to
1087 run it every time we wait for a lock. We (optimistically?)
1088 assume that deadlocks are not common in production applications,
1089 and just wait on the lock for awhile before starting to ask
1090 questions about whether it can ever get unlocked.
1091 Increasing this value reduces the amount of time wasted in
1092 needless deadlock checks, but slows down reporting of real deadlock
1093 errors. The default is 1000 (i.e., one second), which is probably
1094 about the smallest value you would want in practice. On a heavily
1095 loaded server you might want to raise it. Ideally the setting
1096 should exceed your typical transaction time, so as to improve the
1097 odds that the lock will be released before the waiter decides to
1099 This option can only be set at server start.
1106 <primary>transaction isolation level</primary>
1109 <term><varname>DEFAULT_TRANSACTION_ISOLATION</varname> (<type>string</type>)</term>
1112 Each SQL transaction has an isolation level, which can be
1113 either <quote>read committed</quote> or
1114 <quote>serializable</quote>. This parameter controls what the
1115 isolation level of each new transaction is set to. The
1116 default is read committed.
1120 Consult the <citetitle>PostgreSQL User's Guide</citetitle> and
1121 the command <command>SET TRANSACTION</command> for more
1128 <term><varname>DYNAMIC_LIBRARY_PATH</varname> (<type>string</type>)</term>
1129 <indexterm><primary>dynamic_library_path</></>
1130 <indexterm><primary>dynamic loading</></>
1133 If a dynamically loadable module needs to be opened and the
1134 specified name does not have a directory component (i.e., the
1135 name does not contain a slash), the system will search this
1136 path for the specified file. (The name that is used is the
1137 name specified in the <command>CREATE FUNCTION</command> or
1138 <command>LOAD</command> command.)
1142 The value for dynamic_library_path has to be a colon-separated
1143 list of absolute directory names. If a directory name starts
1144 with the special value <literal>$libdir</literal>, the
1145 compiled-in <productname>PostgreSQL</productname> package library directory, which is where the
1146 modules provided by the <productname>PostgreSQL</productname> distribution are installed,
1147 is substituted. (Use <literal>pg_config --pkglibdir</literal>
1148 to print the name of this directory.) An example value:
1151 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
1157 The default value for this parameter is
1158 <literal>$libdir</literal>. If the value is set to the empty
1159 string, the automatic path search is turned off.
1163 This parameter can be changed at run time by superusers, but
1164 note that a setting done that way will only persist till the
1165 end of the client connection, so this method should be
1166 reserved for development purposes. The recommended way to set
1167 this parameter is in the <filename>postgresql.conf</filename>
1175 <primary>fsync</primary>
1178 <term><varname>FSYNC</varname> (<type>boolean</type>)</term>
1181 If this option is on, the <productname>PostgreSQL</> backend
1182 will use the <function>fsync()</> system call in several
1183 places to make sure that updates are physically written to
1184 disk and do not hang around in the kernel buffer cache. This
1185 increases the chance by a large amount that a database
1186 installation will still be usable after an operating system or
1187 hardware crash. (Crashes of the database server itself do
1188 <emphasis>not</> affect this consideration.)
1192 However, this operation slows down <productname>PostgreSQL</>,
1193 because at all those points it has
1194 to block and wait for the operating system to flush the
1195 buffers. Without <function>fsync</>, the operating system is
1196 allowed to do its best in buffering, sorting, and delaying
1197 writes, which can make for a considerable performance
1198 increase. However, if the system crashes, the results of the
1199 last few committed transactions may be lost in part or whole;
1200 in the worst case, unrecoverable data corruption may occur.
1204 This option is the subject of an eternal debate in the
1205 <productname>PostgreSQL</> user and developer communities. Some
1206 always leave it off, some turn it off only for bulk loads,
1207 where there is a clear restart point if something goes wrong,
1208 some leave it on just to be on the safe side. Because it is
1209 the safe side, on is also the default. If you trust your
1210 operating system, your hardware, and your utility company (or
1211 better your UPS), you might want to disable <varname>fsync</varname>.
1215 It should be noted that the performance penalty from doing
1216 <function>fsync</>s is considerably less in <productname>PostgreSQL</> version
1217 7.1 than it was in prior releases. If you previously suppressed
1218 <function>fsync</>s because of performance problems, you may wish to reconsider
1223 This option can only be set at server start or in the
1224 <filename>postgresql.conf</filename> file.
1230 <term><varname>KRB_SERVER_KEYFILE</varname> (<type>string</type>)</term>
1233 Sets the location of the Kerberos server key file. See
1234 <xref linkend="kerberos-auth"> for details.
1240 <term><varname>MAX_CONNECTIONS</varname> (<type>integer</type>)</term>
1243 Determines how many concurrent connections the database server
1244 will allow. The default is 32 (unless altered while building
1245 the server). This parameter can only be set at server start.
1251 <term><varname>MAX_EXPR_DEPTH</varname> (<type>integer</type>)</term>
1254 Sets the maximum expression nesting depth that the parser will
1255 accept. The default value is high enough for any normal query,
1256 but you can raise it if you need to. (But if you raise it too
1257 high, you run the risk of backend crashes due to stack
1264 <term><varname>MAX_FILES_PER_PROCESS</varname> (<type>integer</type>)</term>
1267 Sets the maximum number of simultaneously open files in each server
1268 subprocess. The default is 1000. The limit actually used by the code
1269 is the smaller of this setting and the result of
1270 <literal>sysconf(_SC_OPEN_MAX)</literal>.
1271 Therefore, on systems where <function>sysconf</> returns a reasonable limit,
1272 you don't need to worry about this setting. But on some platforms
1273 (notably, most BSD systems), <function>sysconf</> returns a value that is much
1274 larger than the system can really support when a large number of
1275 processes all try to open that many files. If you find yourself
1276 seeing <quote>Too many open files</> failures, try reducing this
1278 This option can only be set at server start or in the
1279 <filename>postgresql.conf</filename> configuration file;
1280 if changed in the configuration file, it only affects
1281 subsequently-started server subprocesses.
1287 <term><varname>MAX_FSM_RELATIONS</varname> (<type>integer</type>)</term>
1290 Sets the maximum number of relations (tables) for which free space
1291 will be tracked in the shared free-space map.
1292 The default is 100. This option can only be set at server start.
1298 <term><varname>MAX_FSM_PAGES</varname> (<type>integer</type>)</term>
1301 Sets the maximum number of disk pages for which free space
1302 will be tracked in the shared free-space map.
1303 The default is 10000. This option can only be set at server start.
1309 <term><varname>MAX_LOCKS_PER_TRANSACTION</varname> (<type>integer</type>)</term>
1312 The shared lock table is sized on the assumption that at most
1313 <varname>max_locks_per_transaction</> * <varname>max_connections</varname> distinct objects will need
1314 to be locked at any one time. The default, 64, has historically
1315 proven sufficient, but you might need to raise this value if you
1316 have clients that touch many different tables in a single transaction.
1317 This option can only be set at server start.
1323 <term><varname>PASSWORD_ENCRYPTION</varname> (<type>boolean</type>)</term>
1326 When a password is specified in <command>CREATE USER</> or
1327 <command>ALTER USER</> without writing either ENCRYPTED or
1328 UNENCRYPTED, this flag determines whether the password is to be
1330 The default is off (do not encrypt the password), but this choice
1331 may change in a future release.
1337 <term><varname>PORT</varname> (<type>integer</type>)</term>
1338 <indexterm><primary>port</></>
1341 The TCP port the server listens on; 5432 by default. This
1342 option can only be set at server start.
1348 <term><varname>SHARED_BUFFERS</varname> (<type>integer</type>)</term>
1351 Sets the number of shared memory buffers the database server
1352 will use. The default is 64. Each buffer is typically 8192
1353 bytes. This option can only be set at server start.
1359 <term><varname>SILENT_MODE</varname> (<type>bool</type>)</term>
1362 Runs postmaster silently. If this option is set, postmaster
1363 will automatically run in background and any controlling ttys
1364 are disassociated, thus no messages are written to standard output or
1365 standard error (same effect as postmaster's -S option). Unless some
1366 logging system such as <application>syslog</> is enabled, using this option is
1367 discouraged since it makes it impossible to see error
1374 <term><varname>SORT_MEM</varname> (<type>integer</type>)</term>
1377 Specifies the amount of memory to be used by internal sorts
1378 and hashes before switching to temporary disk files. The value
1379 is specified in kilobytes, and defaults to 512 kilobytes. Note
1380 that for a complex query, several sorts and/or hashes might be
1381 running in parallel, and each one will be allowed to use as
1382 much memory as this value specifies before it starts to put
1383 data into temporary files. And don't forget that each running
1384 backend could be doing one or more sorts. So the total memory
1385 space needed could be many times the value of <varname>SORT_MEM</varname>.
1391 <term><varname>SQL_INHERITANCE</varname> (<type>bool</type>)</term>
1392 <indexterm><primary>inheritance</></>
1395 This controls the inheritance semantics, in particular whether
1396 subtables are included into the consideration of various
1397 commands by default. This was not the case in versions prior
1398 to 7.1. If you need the old behavior you can set this
1399 variable to off, but in the long run you are encouraged to
1400 change your applications to use the <literal>ONLY</literal>
1401 keyword to exclude subtables. See the SQL language reference
1402 and the <citetitle>User's Guide</citetitle> for more
1403 information about inheritance.
1410 <primary>SSL</primary>
1413 <term><varname>SSL</varname> (<type>boolean</type>)</term>
1416 Enables <acronym>SSL</> connections. Please read
1417 <xref linkend="ssl-tcp"> before using this. The default
1424 <term><varname>TCPIP_SOCKET</varname> (<type>boolean</type>)</term>
1427 If this is true, then the server will accept TCP/IP
1428 connections. Otherwise only local Unix domain socket
1429 connections are accepted. It is off by default. This option
1430 can only be set at server start.
1436 <term><varname>TRANSFORM_NULL_EQUALS</varname> (<type>boolean</type>)</term>
1437 <indexterm><primary>IS NULL</></>
1440 When turned on, expressions of the form
1441 <literal><replaceable>expr</> = NULL</literal> (or
1442 <literal>NULL = <replaceable>expr</></literal>) are treated as
1443 <literal><replaceable>expr</> IS NULL</literal>, that is, they
1444 return true if <replaceable>expr</> evaluates to the NULL
1445 value, and false otherwise. The correct behavior of
1446 <literal><replaceable>expr</> = NULL</literal> is to always
1447 return NULL (unknown). Therefore this option defaults to off.
1451 However, filtered forms in <productname>Microsoft
1452 Access</productname> generate queries that appear to use
1453 <literal><replaceable>expr</> = NULL</literal> to test for
1454 NULLs, so if you use that interface to access the database you
1455 might want to turn this option on. Since expressions of the
1456 form <literal><replaceable>expr</> = NULL</literal> always
1457 return NULL (using the correct interpretation) they are not
1458 very useful and do not appear often in normal applications, so
1459 this option does little harm in practice. But new users are
1460 frequently confused about the semantics of expressions
1461 involving NULL, so we do not turn this option on by default.
1465 Note that this option only affects the literal <literal>=</>
1466 operator, not other comparison operators or other expressions
1467 that are computationally equivalent to some expression
1468 involving the equals operator (such as <literal>IN</literal>).
1469 Thus, this option is not a general fix for bad programming.
1473 Refer to the <citetitle>User's Guide</citetitle> for related
1480 <term><varname>UNIX_SOCKET_DIRECTORY</varname> (<type>string</type>)</term>
1483 Specifies the directory of the Unix-domain socket on which the
1484 <application>postmaster</application> is to listen for
1485 connections from client applications. The default is normally
1486 <filename>/tmp</filename>, but can be changed at build time.
1492 <term><varname>UNIX_SOCKET_GROUP</varname> (<type>string</type>)</term>
1495 Sets the group owner of the Unix domain socket. (The owning
1496 user of the socket is always the user that starts the
1497 postmaster.) In combination with the option
1498 <option>UNIX_SOCKET_PERMISSIONS</option> this can be used as
1499 an additional access control mechanism for this socket type.
1500 By default this is the empty string, which uses the default
1501 group for the current user. This option can only be set at
1508 <term><varname>UNIX_SOCKET_PERMISSIONS</varname> (<type>integer</type>)</term>
1511 Sets the access permissions of the Unix domain socket. Unix
1512 domain sockets use the usual Unix file system permission set.
1513 The option value is expected to be an numeric mode
1514 specification in the form accepted by the
1515 <function>chmod</function> and <function>umask</function>
1516 system calls. (To use the customary octal format the number
1517 must start with a <literal>0</literal> (zero).)
1521 The default permissions are <literal>0777</literal>, meaning
1522 anyone can connect. Reasonable alternatives would be
1523 <literal>0770</literal> (only user and group, see also under
1524 <option>UNIX_SOCKET_GROUP</option>) and
1525 <literal>0700</literal> (only user). (Note that actually for
1526 a Unix socket, only write permission matters and there is no
1527 point in setting or revoking read or execute permissions.)
1531 This access control mechanism is independent from the one
1532 described in <xref linkend="client-authentication">.
1536 This option can only be set at server start.
1542 <term><varname>VACUUM_MEM</varname> (<type>integer</type>)</term>
1545 Specifies the maximum amount of memory to be used by
1546 <command>VACUUM</command> to keep track of to-be-reclaimed tuples.
1547 The value is specified in kilobytes, and defaults to 8192 kilobytes.
1548 Larger settings may improve the speed of vacuuming large tables
1549 that have many deleted tuples.
1555 <term><varname>VIRTUAL_HOST</varname> (<type>string</type>)</term>
1558 Specifies the TCP/IP host name or address on which the
1559 <application>postmaster</application> is to listen for
1560 connections from client applications. Defaults to
1561 listening on all configured addresses (including <systemitem class="systemname">localhost</>).
1570 <sect2 id="runtime-config-wal">
1574 See also <xref linkend="wal-configuration"> for details on WAL
1579 <term><varname>CHECKPOINT_SEGMENTS</varname> (<type>integer</type>)</term>
1582 Maximum distance between automatic WAL checkpoints, in log file
1583 segments (each segment is normally 16 megabytes).
1584 This option can only be set at server start or in the
1585 <filename>postgresql.conf</filename> file.
1591 <term><varname>CHECKPOINT_TIMEOUT</varname> (<type>integer</type>)</term>
1594 Maximum time between automatic WAL checkpoints, in seconds.
1595 This option can only be set at server start or in the
1596 <filename>postgresql.conf</filename> file.
1602 <term><varname>COMMIT_DELAY</varname> (<type>integer</type>)</term>
1605 Time delay between writing a commit record to the WAL buffer and
1606 flushing the buffer out to disk, in microseconds. A nonzero delay
1607 allows multiple transactions to be committed with only one <function>fsync</function> system call,
1608 if system load is high enough that additional transactions become
1609 ready to commit within the given interval. But the delay is just
1610 wasted time if no other transactions become ready to commit.
1611 Therefore, the delay is only performed if at least COMMIT_SIBLINGS
1612 other transactions are active at the instant that a backend has
1613 written its commit record.
1619 <term><varname>COMMIT_SIBLINGS</varname> (<type>integer</type>)</term>
1622 Minimum number of concurrent open transactions to require before
1623 performing the <varname>COMMIT_DELAY</> delay. A larger value makes it more
1624 probable that at least one other transaction will become ready to
1625 commit during the delay interval.
1631 <term><varname>WAL_BUFFERS</varname> (<type>integer</type>)</term>
1634 Number of disk-page buffers in shared memory for WAL log.
1635 This option can only be set at server start.
1641 <term><varname>WAL_DEBUG</varname> (<type>integer</type>)</term>
1644 If non-zero, turn on WAL-related debugging output on standard
1651 <term><varname>WAL_FILES</varname> (<type>integer</type>)</term>
1654 Number of log files that are created in advance at checkpoint
1655 time. This option can only be set at server start or in the
1656 <filename>postgresql.conf</filename> file.
1662 <term><varname>WAL_SYNC_METHOD</varname> (<type>string</type>)</term>
1665 Method used for forcing WAL updates out to disk. Possible
1667 <literal>FSYNC</> (call <function>fsync()</> at each commit),
1668 <literal>FDATASYNC</> (call <function>fdatasync()</> at each commit),
1669 <literal>OPEN_SYNC</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>), or
1670 <literal>OPEN_DATASYNC</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>).
1671 Not all of these choices are available on all platforms.
1672 This option can only be set at server start or in the
1673 <filename>postgresql.conf</filename> file.
1682 <sect2 id="runtime-config-short">
1683 <title>Short options</title>
1685 For convenience there are also single letter option switches
1686 available for many parameters. They are described in the following
1690 <title>Short option key</title>
1692 <colspec colnum="3" align="center">
1695 <entry>Short option</entry>
1696 <entry>Equivalent</entry>
1697 <entry>Remark</entry>
1702 <entry><option>-B <replaceable>x</replaceable></option></entry>
1703 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
1707 <entry><option>-d <replaceable>x</replaceable></option></entry>
1708 <entry><literal>debug_level = <replaceable>x</replaceable></></entry>
1712 <entry><option>-F</option></entry>
1713 <entry><literal>fsync = off</></entry>
1717 <entry><option>-h <replaceable>x</replaceable></option></entry>
1718 <entry><literal>virtual_host = <replaceable>x</replaceable></></entry>
1722 <entry><option>-i</option></entry>
1723 <entry><literal>tcpip_socket = on</></entry>
1727 <entry><option>-k <replaceable>x</replaceable></option></entry>
1728 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
1732 <entry><option>-l</option></entry>
1733 <entry><literal>ssl = on</></entry>
1737 <entry><option>-N <replaceable>x</replaceable></option></entry>
1738 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
1742 <entry><option>-p <replaceable>x</replaceable></option></entry>
1743 <entry><literal>port = <replaceable>x</replaceable></></entry>
1748 <entry><option>-fi</option>, <option>-fh</option>, <option>-fm</option>, <option>-fn</option>, <option>-fs</option>, <option>-ft</option></entry>
1749 <entry><literal>enable_indexscan=off</>, <literal>enable_hashjoin=off</>,
1750 <literal>enable_mergejoin=off</>, <literal>enable_nestloop=off</>, <literal>enable_seqscan=off</>,
1751 <literal>enable_tidscan=off</></entry>
1755 <entry><option>-S <replaceable>x</replaceable></option></entry>
1756 <entry><literal>sort_mem = <replaceable>x</replaceable></></entry>
1760 <entry><option>-s</option></entry>
1761 <entry><literal>show_query_stats = on</></entry>
1765 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option></entry>
1766 <entry><literal>show_parser_stats=on</>, <literal>show_planner_stats=on</>, <literal>show_executor_stats=on</></entry>
1772 For historical reasons, options marked <quote>*</quote> must be
1773 passed to the individual backend process via the
1774 <option>-o</option> postmaster option, for example,
1776 $ <userinput>postmaster -o '-S 1024 -s'</userinput>
1778 or via <envar>PGOPTIONS</envar> from the client side, as explained
1786 <sect1 id="kernel-resources">
1787 <title>Managing Kernel Resources</title>
1790 A large <productname>PostgreSQL</> installation can quickly hit
1791 various operating system resource limits. (On some systems, the
1792 factory defaults are so low that you don't even need a really
1793 <quote>large</> installation.) If you have encountered this kind of
1794 problem then keep reading.
1797 <sect2 id="sysvipc">
1798 <title>Shared Memory and Semaphores</title>
1800 <indexterm zone="sysvipc">
1801 <primary>shared memory</primary>
1804 <indexterm zone="sysvipc">
1805 <primary>semaphores</primary>
1809 Shared memory and semaphores are collectively referred to as
1810 <quote><systemitem class="osname">System V</> <acronym>IPC</></quote> (together with message queues, which are
1811 not relevant for <productname>PostgreSQL</>). Almost all modern
1812 operating systems provide these features, but not all of them have
1813 them turned on or sufficiently sized by default, especially
1814 systems with BSD heritage. (For the <systemitem class="osname">QNX</> and <systemitem class="osname">BeOS</> ports,
1815 <productname>PostgreSQL</> provides its own replacement
1816 implementation of these facilities.)
1820 The complete lack of these facilities is usually manifested by an
1821 <errorname>Illegal system call</> error upon postmaster start. In
1822 that case there's nothing left to do but to reconfigure your
1823 kernel -- <productname>PostgreSQL</> won't work without them.
1827 When <productname>PostgreSQL</> exceeds one of the various hard
1828 limits of the <acronym>IPC</> resources then the postmaster will refuse to
1829 start up and should leave a marginally instructive error message
1830 about which problem was encountered and what needs to be done
1831 about it. (See also <xref linkend="postmaster-start-failures">.)
1832 The relevant kernel parameters are named
1833 consistently across different systems; <xref
1834 linkend="sysvipc-parameters"> gives an overview. The methods to
1835 set them, however, vary; suggestions for some platforms are given
1836 below. Be warned that it is often necessary to reboot your
1837 machine at least, possibly even recompile the kernel, to change these
1842 <table id="sysvipc-parameters">
1843 <title><systemitem class="osname">System V</> <acronym>IPC</> parameters</>
1849 <entry>Description</>
1850 <entry>Reasonable values</>
1856 <entry><varname>SHMMAX</></>
1857 <entry>Maximum size of shared memory segment (bytes)</>
1858 <entry>250kB + 8.2kB * <varname>shared_buffers</> + 14.2kB * <varname>max_connections</> or infinity</entry>
1862 <entry><varname>SHMMIN</></>
1863 <entry>Minimum size of shared memory segment (bytes)</>
1868 <entry><varname>SHMALL</></>
1869 <entry>Total amount of shared memory available (bytes or pages)</>
1870 <entry>if bytes, same as <varname>SHMMAX</varname>; if pages, <literal>ceil(SHMMAX/PAGE_SIZE)</literal></>
1874 <entry><varname>SHMSEG</></>
1875 <entry>Maximum number of shared memory segments per process</>
1876 <entry>only 1 segment is needed, but the default is much higher</>
1880 <entry><varname>SHMMNI</></>
1881 <entry>Maximum number of shared memory segments system-wide</>
1882 <entry>like <varname>SHMSEG</> plus room for other applications</>
1886 <entry><varname>SEMMNI</></>
1887 <entry>Maximum number of semaphore identifiers (i.e., sets)</>
1888 <entry><literal>>= ceil(max_connections / 16)</literal></>
1892 <entry><varname>SEMMNS</></>
1893 <entry>Maximum number of semaphores system-wide</>
1894 <entry><literal>ceil(max_connections / 16) * 17</literal> + room for other applications</>
1898 <entry><varname>SEMMSL</></>
1899 <entry>Maximum number of semaphores per set</>
1904 <entry><varname>SEMMAP</></>
1905 <entry>Number of entries in semaphore map</>
1910 <entry><varname>SEMVMX</></>
1911 <entry>Maximum value of semaphore</>
1912 <entry>>= 255 (The default is often 32767, don't change unless asked to.)</>
1921 <indexterm><primary>SHMMAX</primary></indexterm>
1922 The most important shared memory parameter is <varname>SHMMAX</>,
1923 the maximum size, in bytes, that a shared memory segment can have.
1924 If you get an error message from <function>shmget</> along the
1925 lines of <errorname>Invalid argument</> then it is possible that
1926 this limit has been exceeded. The size of the required shared
1927 memory segments varies both with the number of requested buffers
1928 (<option>-B</> option) and the number of allowed connections
1929 (<option>-N</> option), although the former is the dominant item.
1930 (You can therefore, as a temporary solution, lower these settings
1931 to get rid of the failures.) As a rough approximation you can
1932 estimate the required segment size as the number of buffers times
1933 the block size (8 kB by default) plus ample overhead (at least
1934 half a megabyte). Any error message you might get will contain the
1935 size of the failed allocation request.
1939 Less likely to cause problems is the minimum size for shared
1940 memory segments (<varname>SHMMIN</>), which should be at most
1941 somewhere around 256 kB for <productname>PostgreSQL</> (it is
1942 usually just 1). The maximum number of segments system-wide
1943 (<varname>SHMMNI</>) or per-process (<varname>SHMSEG</>) should
1944 not cause a problem unless your system has them set to zero. Some
1945 systems also have a limit on the total amount of shared memory in
1946 the system; see the platform-specific instructions below.
1950 <productname>PostgreSQL</> uses one semaphore per allowed connection
1951 (<option>-N</> option), in sets of 16. Each such set will also
1952 contain a 17th semaphore which contains a <quote>magic
1953 number</quote>, to detect collision with semaphore sets used by
1954 other applications. The maximum number of semaphores in the system
1955 is set by <varname>SEMMNS</>, which consequently must be at least
1956 as high as the connection setting plus one extra for each 16
1957 allowed connections (see the formula in <xref
1958 linkend="sysvipc-parameters">). The parameter <varname>SEMMNI</>
1959 determines the limit on the number of semaphore sets that can
1960 exist on the system at one time. Hence this parameter must be at
1961 least <literal>ceil(max_connections / 16)</>. Lowering the number
1962 of allowed connections is a temporary workaround for failures,
1963 which are usually confusingly worded <quote><errorname>No space
1964 left on device</></>, from the function <function>semget()</>.
1968 In some cases it might also turn out to be necessary to increase
1969 <varname>SEMMAP</> to be at least on the order of
1970 <varname>SEMMNS</>. This parameter defines the size of the
1971 semaphore resource map, in which each contiguous block of available
1972 semaphores needs an entry. When a semaphore set is freed it is
1973 either added to an existing entry that is adjacent to the freed
1974 block or it is registered under a new map entry. If the map is
1975 full, the freed semaphores get lost (until reboot). Fragmentation
1976 of the semaphore space could therefore over time lead to less
1977 available semaphores than there should be.
1981 The <varname>SEMMSL</> parameter, which determines how many
1982 semaphores can be in a set, must be at least 17 for
1983 <productname>PostgreSQL</>.
1987 Various other settings related to <quote>semaphore undo</>, such as
1988 <varname>SEMMNU</> and <varname>SEMUME</>, are not of concern
1989 for <productname>PostgreSQL</>.
1997 <term><systemitem class="osname">BSD/OS</></term>
1998 <indexterm><primary>BSD/OS</></>
2001 <title>Shared Memory</>
2003 By default, only 4 MB of shared memory is supported. Keep in
2004 mind that shared memory is not pageable; it is locked in RAM.
2006 To increase the number of shared buffers supported by the
2007 postmaster, add the following to your kernel configuration file. A
2008 <varname>SHMALL</> value of 1024 represents 4MB of shared
2009 memory. The following increases the maximum shared memory area
2012 options "SHMALL=8192"
2013 options "SHMMAX=\(SHMALL*PAGE_SIZE\)"
2019 For those running 4.1 or later, just make the above changes,
2020 recompile the kernel, and reboot. For those running earlier
2021 releases, use <application>bpatch</> to find the
2022 <varname>sysptsize</> value in the current kernel. This is
2023 computed dynamically at boot time.
2025 $ <userinput>bpatch -r sysptsize</>
2026 <computeroutput>0x9 = 9</>
2028 Next, add <varname>SYSPTSIZE</> as a hard-coded value in the
2029 kernel configuration file. Increase the value you found using
2030 <application>bpatch</>. Add 1 for every additional 4 MB of
2031 shared memory you desire.
2033 options "SYSPTSIZE=16"
2035 <varname>sysptsize</> cannot be changed by <command>sysctl</command>.
2039 <title>Semaphores</>
2041 You may need to increase the number of semaphores. By
2042 default, <productname>PostgreSQL</> allocates 34 semaphores,
2043 which is over half the default system total of 60.
2048 Set the values you want in your kernel configuration file, e.g.:
2051 options "SEMMNS=240"
2053 options "SEMMNU=120"
2061 <term><systemitem class="osname">FreeBSD</></term>
2062 <term><systemitem class="osname">NetBSD</></term>
2063 <term><systemitem class="osname">OpenBSD</></term>
2064 <indexterm><primary>FreeBSD</></>
2065 <indexterm><primary>NetBSD</></>
2066 <indexterm><primary>OpenBSD</></>
2069 The options <varname>SYSVSHM</> and <varname>SYSVSEM</> need
2070 to be enabled when the kernel is compiled. (They are by
2071 default.) The maximum size of shared memory is determined by
2072 the option <varname>SHMMAXPGS</> (in pages). The following
2073 shows an example of how to set the various parameters:
2076 options SHMMAXPGS=4096
2085 (On <systemitem class="osname">NetBSD</> and <systemitem
2086 class="osname">OpenBSD</> the key word is actually
2087 <literal>option</literal> singular.)
2094 <term><systemitem class="osname">HP-UX</></term>
2095 <indexterm><primary>HP-UX</></>
2098 The default settings tend to suffice for normal installations.
2099 On <productname>HP-UX</> 10, the factory default for
2100 <varname>SEMMNS</> is 128, which might be too low for larger
2104 <acronym>IPC</> parameters can be set in the <application>System
2105 Administration Manager</> (<acronym>SAM</>) under
2106 <menuchoice><guimenu>Kernel
2107 Configuration</><guimenuitem>Configurable Parameters</></>.
2108 Hit <guibutton>Create A New Kernel</> when you're done.
2115 <term><systemitem class="osname">Linux</></term>
2116 <indexterm><primary>Linux</></>
2119 The default shared memory limit (both
2120 <varname>SHMMAX</varname> and <varname>SHMALL</varname>) is 32
2121 MB in 2.2 kernels, but it can be changed in the
2122 <filename>proc</filename> file system (without reboot). For
2123 example, to allow 128 MB:
2125 <prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmall</userinput>
2126 <prompt>$</prompt> <userinput>echo 134217728 >/proc/sys/kernel/shmmax</userinput>
2128 You could put these commands into a script run at boot-time.
2132 Alternatively, you can use
2133 <citerefentry><refentrytitle>sysctl</refentrytitle>
2134 <manvolnum>8</manvolnum></citerefentry>, if available, to
2135 control these parameters. Look for a file called
2136 <filename>/etc/sysctl.conf</filename> and add lines like the
2139 kernel.shmall = 134217728
2140 kernel.shmmax = 134217728
2142 This file is usually processed at boot time, but
2143 <application>sysctl</application> can also be called
2148 Other parameters are sufficiently sized for any application.
2149 If you want to see for yourself look into
2150 <filename>/usr/src/linux/include/asm-<replaceable>xxx</>/shmparam.h</>
2151 and <filename>/usr/src/linux/include/linux/sem.h</>.
2158 <term><systemitem class="osname">SCO OpenServer</></term>
2159 <indexterm><primary>SCO OpenServer</></>
2162 In the default configuration, only 512 kB of shared memory per
2163 segment is allowed, which is about enough for <option>-B 24 -N
2164 12</>. To increase the setting, first change the directory to
2165 <filename>/etc/conf/cf.d</>. To display the current value of
2166 <varname>SHMMAX</>, in bytes, run
2168 ./configure -y SHMMAX
2170 To set a new value for <varname>SHMMAX</>, run:
2172 ./configure SHMMAX=<replaceable>value</>
2174 where <replaceable>value</> is the new value you want to use
2175 (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel
2186 <term><systemitem class="osname">Solaris</></term>
2187 <indexterm><primary>Solaris</></>
2190 At least in version 2.6, the maximum size of a shared memory
2191 segment is set too low for <productname>PostgreSQL</>. The
2192 relevant settings can be changed in <filename>/etc/system</>,
2195 set shmsys:shminfo_shmmax=0x2000000
2196 set shmsys:shminfo_shmmin=1
2197 set shmsys:shminfo_shmmni=256
2198 set shmsys:shminfo_shmseg=256
2200 set semsys:seminfo_semmap=256
2201 set semsys:seminfo_semmni=512
2202 set semsys:seminfo_semmns=512
2203 set semsys:seminfo_semmsl=32
2205 You need to reboot to make the changes effective.
2210 url="http://www.sunworld.com/swol-09-1997/swol-09-insidesolaris.html"></>
2211 for information on shared memory under
2212 <productname>Solaris</>.
2219 <term><systemitem class="osname">UnixWare</></term>
2220 <indexterm><primary>UnixWare</></>
2223 On <productname>UnixWare</> 7, the maximum size for shared
2224 memory segments is 512 kB in the default configuration. This
2225 is enough for about <option>-B 24 -N 12</>. To display the
2226 current value of <varname>SHMMAX</>, run
2228 /etc/conf/bin/idtune -g SHMMAX
2230 which displays the current, default, minimum, and maximum
2231 values, in bytes. To set a new value for <varname>SHMMAX</>,
2234 /etc/conf/bin/idtune SHMMAX <replaceable>value</>
2236 where <replaceable>value</> is the new value you want to use
2237 (in bytes). After setting <varname>SHMMAX</>, rebuild the
2240 /etc/conf/bin/idbuild -B
2254 <title>Resource Limits</title>
2257 Unix-like operating systems enforce various kinds of resource
2258 limits that might interfere with the operation of your
2259 <productname>PostgreSQL</productname> server. Of importance are
2260 especially the limits on the number of processes per user, the
2261 number of open files per process, and the amount of memory
2262 available to a process. Each of these have a <quote>hard</quote>
2263 and a <quote>soft</quote> limit. The soft limit is what actually
2264 counts but it can be changed by the user up to the hard limit.
2265 The hard limit can only be changed by the root user. The system
2266 call <function>setrlimit</function> is responsible for setting
2267 these parameters. The shell's built-in command
2268 <command>ulimit</command> (Bourne shells) or
2269 <command>limit</command> (<application>csh</>) is used to control the resource
2270 limits from the command line. On BSD-derived systems the file
2271 <filename>/etc/login.conf</filename> controls what values the
2272 various resource limits are set to upon login. See
2273 <citerefentry><refentrytitle>login.conf</refentrytitle>
2274 <manvolnum>5</manvolnum></citerefentry> for details. The relevant
2275 parameters are <varname>maxproc</varname>,
2276 <varname>openfiles</varname>, and <varname>datasize</varname>.
2281 :datasize-cur=256M:\
2283 :openfiles-cur=256:\
2286 (<literal>-cur</literal> is the soft limit. Append
2287 <literal>-max</literal> to set the hard limit.)
2291 Kernels generally also have an implementation-dependent
2292 system-wide limit on some resources.
2296 On <productname>Linux</productname>
2297 <filename>/proc/sys/fs/file-max</filename> determines the
2298 maximum number of open files that the kernel will support. It can
2299 be changed by writing a different number into the file or by
2300 adding an assignment in <filename>/etc/sysctl.conf</filename>.
2301 The maximum limit of files per process is fixed at the time the
2302 kernel is compiled; see
2303 <filename>/usr/src/linux/Documentation/proc.txt</filename> for
2311 The <productname>PostgreSQL</productname> server uses one process
2312 per connection so you should provide for at least as many processes
2313 as allowed connections, in addition to what you need for the rest
2314 of your system. This is usually not a problem but if you run
2315 several servers on one machine things might get tight.
2319 The factory default limit on open files is often set to
2320 <quote>socially friendly</quote> values that allow many users to
2321 coexist on a machine without using an inappropriate fraction of
2322 the system resources. If you run many servers on a machine this
2323 is perhaps what you want, but on dedicated servers you may want to
2328 On the other side of the coin, some systems allow individual
2329 processes to open large numbers of files; if more than a few processes
2330 do so then the system-wide limit can easily be exceeded. If you find
2331 this happening, and don't want to alter the system-wide limit, you
2332 can set <productname>PostgreSQL</productname>'s
2333 <varname>max_files_per_process</varname> configuration parameter
2334 to limit its consumption of open files.
2341 <sect1 id="postmaster-shutdown">
2342 <title>Shutting down the server</title>
2345 Depending on your needs, there are several ways to shut down the
2346 database server when your work is done. The differentiation is
2347 done by what signal you send to the server process.
2350 <term><systemitem>SIGTERM</systemitem></term>
2353 After receiving <systemitem>SIGTERM</systemitem>, the postmaster disallows new
2354 connections, but lets existing backends end their work normally.
2355 It shuts down only after all of the backends terminate by client
2357 This is the <firstterm>Smart Shutdown</firstterm>.
2363 <term><systemitem>SIGINT</systemitem></term>
2366 The postmaster disallows new connections and sends all existing
2367 backends <systemitem>SIGTERM</systemitem>, which will cause them to abort their current
2368 transactions and exit promptly. It then waits for the backends to exit
2369 and finally shuts down the data base.
2370 This is the <firstterm>Fast Shutdown</firstterm>.
2376 <term><systemitem>SIGQUIT</systemitem></term>
2379 This is the <firstterm>Immediate Shutdown</firstterm> which
2380 will cause the postmaster to send a <systemitem>SIGQUIT</systemitem> to all backends and
2381 exit immediately (without properly shutting down the database
2382 system). The backends likewise exit immediately upon receiving
2383 <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by replaying the WAL log)
2384 upon next start-up. This is recommended only in emergencies.
2392 It is best not to use <systemitem>SIGKILL</systemitem> to shut down the postmaster. This
2393 will prevent the postmaster from releasing shared memory and
2394 semaphores, which you may then have to do by hand.
2398 The <acronym>PID</> of the postmaster process can be found using the
2399 <application>ps</application> program, or from the file
2400 <filename>postmaster.pid</filename> in the data directory. So for
2401 example, to do a fast shutdown:
2403 $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
2407 The program <application>pg_ctl</application> is a shell script
2408 that provides a more convenient interface for shutting down the
2413 <sect1 id="ssl-tcp">
2414 <title>Secure TCP/IP Connections with SSL</title>
2416 <indexterm zone="ssl-tcp">
2417 <primary>SSL</primary>
2421 <productname>PostgreSQL</> has native support for connections over
2422 <acronym>SSL</> to encrypt
2423 client/server communications for increased security. This requires
2424 <productname>OpenSSL</productname> to be installed on both client
2425 and server systems and support enabled at build time (see <xref
2426 linkend="installation">).
2430 With SSL support compiled in, the <productname>PostgreSQL</> server
2431 can be started with the argument <option>-l</> (ell) to enable
2432 SSL connections. When starting in SSL mode, the server will look
2433 for the files <filename>server.key</> and <filename>server.crt</> in
2434 the data directory. These files should contain the server private key
2435 and certificate respectively. These files must be set up correctly
2436 before an SSL-enabled server can start. If the private key is protected
2437 with a passphrase, the server will prompt for the passphrase and will
2438 not start until it has been entered.
2442 The server will listen for both standard and SSL connections
2443 on the same TCP/IP port, and will negotiate with any connecting
2444 client whether or not to use SSL.
2445 See <xref linkend="client-authentication">
2446 about how to force on the server side the use of SSL for certain
2451 For details on how to create your server private key and certificate,
2452 refer to the <productname>OpenSSL</> documentation. A simple self-signed
2453 certificate can be used to get started for testing, but a certificate signed
2454 by a <acronym>CA</> (either one of the global <acronym>CAs</> or a local one) should be used in
2455 production so the client can verify the server's identity. To create
2456 a quick self-signed certificate, use the following <productname>OpenSSL</productname> command:
2458 openssl req -new -text -out cert.req
2460 Fill out the information that <command>openssl</> asks for. Make sure that you enter
2461 the local host name as Common Name; the challenge password can be
2462 left blank. The script will generate a key that is passphrase protected;
2463 it will not accept a pass phrase that is less than four characters long.
2464 To remove the passphrase (as you must if you want automatic start-up of
2465 the server), run the commands
2467 openssl rsa -in privkey.pem -out cert.pem
2469 Enter the old passphrase to unlock the existing key. Now do
2471 openssl req -x509 -in cert.req -text -key cert.pem -out cert.cert
2472 cp cert.pem <replaceable>$PGDATA</replaceable>/server.key
2473 cp cert.cert <replaceable>$PGDATA</replaceable>/server.crt
2475 to turn the certificate into a self-signed certificate and to copy the
2476 key and certificate to where the server will look for them.
2480 <sect1 id="ssh-tunnels">
2481 <title>Secure TCP/IP Connections with <application>SSH</application> tunnels</title>
2483 <indexterm zone="ssh-tunnels">
2484 <primary>ssh</primary>
2488 <title>Acknowledgement</title>
2490 Idea taken from an email by Gene Selkov, Jr.
2491 (<email>selkovjr@mcs.anl.gov</>) written on 1999-09-08 in response
2492 to a question from Eric Marsden.
2497 One can use <productname>ssh</productname> to encrypt the network
2498 connection between clients and a
2499 <productname>PostgreSQL</productname> server. Done properly, this
2500 should lead to an adequately secure network connection.
2504 First make sure that an <application>ssh</application> server is
2505 running properly on the same machine as
2506 <productname>PostgreSQL</productname> and that you can log in using
2507 <command>ssh</command> as some user. Then you can establish a secure tunnel with a
2508 command like this from the client machine:
2510 $ <userinput>ssh -L 3333:foo.com:5432 joe@foo.com</userinput>
2512 The first number in the <option>-L</option> argument, 3333, is the
2513 port number of your end of the tunnel; it can be chosen freely. The
2514 second number, 5432, is the remote end of the tunnel -- the port
2515 number your server is using. The name or the address in between
2516 the port numbers is the host with the database server you are going
2517 to connect to. In order to connect to the database server using
2518 this tunnel, you connect to port 3333 on the local machine:
2520 psql -h localhost -p 3333 template1
2522 To the database server it will then look as though you are really
2523 user <literal>joe@foo.com</literal> and it will use whatever
2524 authentication procedure was set up for this user. In order for the
2525 tunnel setup to succeed you must be allowed to connect via <command>ssh</command> as
2526 <systemitem>joe@foo.com</systemitem>, just as if you had attempted to use <command>ssh</command> to set up a
2532 Several other products exist that can provide secure tunnels using
2533 a procedure similar in concept to the one just described.
2541 <!-- Keep this comment at the end of the file
2546 sgml-minimize-attributes:nil
2547 sgml-always-quote-attributes:t
2550 sgml-parent-document:nil
2551 sgml-default-dtd-file:"./reference.ced"
2552 sgml-exposed-tags:nil
2553 sgml-local-catalogs:("/usr/lib/sgml/catalog")
2554 sgml-local-ecat-files:nil
projects / postgresql / blob