2 $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.47 2006/02/05 18:19:14 momjian Exp $
4 <chapter Id="runtime-config">
5 <title>Server Configuration</title>
8 <primary>configuration</primary>
9 <secondary>of the server</secondary>
13 There are many configuration parameters that affect the behavior of
14 the database system. In the first section of this chapter, we
15 describe how to set configuration parameters. The subsequent sections
16 discuss each parameter in detail.
19 <sect1 id="config-setting">
20 <title>Setting Parameters</title>
23 All parameter names are case-insensitive. Every parameter takes a
24 value of one of four types: Boolean, integer, floating point,
25 or string. Boolean values may be written as <literal>ON</literal>,
26 <literal>OFF</literal>, <literal>TRUE</literal>,
27 <literal>FALSE</literal>, <literal>YES</literal>,
28 <literal>NO</literal>, <literal>1</literal>, <literal>0</literal>
29 (all case-insensitive) or any unambiguous prefix of these.
33 One way to set these parameters is to edit the file
34 <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>,
35 which is normally kept in the data directory. (<application>initdb</>
36 installs a default copy there.) An example of what this file might look
41 log_destination = 'syslog'
42 search_path = '"$user", public'
44 One parameter is specified per line. The equal sign between name and
45 value is optional. Whitespace is insignificant and blank lines are
46 ignored. Hash marks (<literal>#</literal>) introduce comments
47 anywhere. Parameter values that are not simple identifiers or
48 numbers must be single-quoted. To embed a single quote in a parameter
49 value, write either two quotes (preferred) or backslash-quote.
54 <primary>SIGHUP</primary>
56 The configuration file is reread whenever the
57 <command>postmaster</command> process receives a
58 <systemitem>SIGHUP</> signal (which is most easily sent by means
59 of <literal>pg_ctl reload</>). The <command>postmaster</command>
60 also propagates this signal to all currently running server
61 processes so that existing sessions also get the new
62 value. Alternatively, you can send the signal to a single server
63 process directly. Some parameters can only be set at server start;
64 any changes to their entries in the configuration file will be ignored
65 until the server is restarted.
69 A second way to set these configuration parameters is to give them
70 as a command line option to the <command>postmaster</command>, such as:
72 postmaster -c log_connections=yes -c log_destination='syslog'
74 Command-line options override any conflicting settings in
75 <filename>postgresql.conf</filename>. Note that this means you won't
76 be able to change the value on-the-fly by editing
77 <filename>postgresql.conf</filename>, so while the command-line
78 method may be convenient, it can cost you flexibility later.
82 Occasionally it is useful to give a command line option to
83 one particular session only. The environment variable
84 <envar>PGOPTIONS</envar> can be used for this purpose on the
87 env PGOPTIONS='-c geqo=off' psql
89 (This works for any <application>libpq</>-based client application, not
90 just <application>psql</application>.) Note that this won't work for
91 parameters that are fixed when the server is started or that must be
92 specified in <filename>postgresql.conf</filename>.
96 Furthermore, it is possible to assign a set of parameter settings to
97 a user or a database. Whenever a session is started, the default
98 settings for the user and database involved are loaded. The
99 commands <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
100 and <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">,
101 respectively, are used to configure these settings. Per-database
102 settings override anything received from the
103 <command>postmaster</command> command-line or the configuration
104 file, and in turn are overridden by per-user settings; both are
105 overridden by per-session settings.
109 Some parameters can be changed in individual <acronym>SQL</acronym>
110 sessions with the <xref linkend="SQL-SET" endterm="SQL-SET-title">
111 command, for example:
113 SET ENABLE_SEQSCAN TO OFF;
115 If <command>SET</> is allowed, it overrides all other sources of
116 values for the parameter. Some parameters cannot be changed via
117 <command>SET</command>: for example, if they control behavior that
118 cannot be changed without restarting the entire
119 <productname>PostgreSQL</productname> server. Also, some parameters can
120 be modified via <command>SET</command> or <command>ALTER</> by superusers,
121 but not by ordinary users.
125 The <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title">
126 command allows inspection of the current values of all parameters.
130 The virtual table <structname>pg_settings</structname>
131 (described in <xref linkend="view-pg-settings">) also allows
132 displaying and updating session run-time parameters. It is equivalent
133 to <command>SHOW</> and <command>SET</>, but can be more convenient
134 to use because it can be joined with other tables, or selected from using
135 any desired selection condition.
139 <sect1 id="runtime-config-file-locations">
140 <title>File Locations</title>
143 In addition to the <filename>postgresql.conf</filename> file
144 already mentioned, <productname>PostgreSQL</productname> uses
145 two other manually-edited configuration files, which control
146 client authentication (their use is discussed in <xref
147 linkend="client-authentication">). By default, all three
148 configuration files are stored in the database cluster's data
149 directory. The parameters described in this section allow the
150 configuration files to be placed elsewhere. (Doing so can ease
151 administration. In particular it is often easier to ensure that
152 the configuration files are properly backed-up when they are
157 <varlistentry id="guc-data-directory" xreflabel="data_directory">
158 <term><varname>data_directory</varname> (<type>string</type>)</term>
160 <primary><varname>data_directory</> configuration parameter</primary>
164 Specifies the directory to use for data storage.
165 This parameter can only be set at server start.
170 <varlistentry id="guc-config-file" xreflabel="config_file">
171 <term><varname>config_file</varname> (<type>string</type>)</term>
173 <primary><varname>config_file</> configuration parameter</primary>
177 Specifies the main server configuration file
178 (customarily called <filename>postgresql.conf</>).
179 This parameter can only be set on the postmaster command line.
184 <varlistentry id="guc-hba-file" xreflabel="hba_file">
185 <term><varname>hba_file</varname> (<type>string</type>)</term>
187 <primary><varname>hba_file</> configuration parameter</primary>
191 Specifies the configuration file for host-based authentication
192 (customarily called <filename>pg_hba.conf</>).
193 This parameter can only be set at server start.
198 <varlistentry id="guc-ident-file" xreflabel="ident_file">
199 <term><varname>ident_file</varname> (<type>string</type>)</term>
201 <primary><varname>ident_file</> configuration parameter</primary>
205 Specifies the configuration file for
206 <application>ident</> authentication
207 (customarily called <filename>pg_ident.conf</>).
208 This parameter can only be set at server start.
213 <varlistentry id="guc-external-pid-file" xreflabel="external_pid_file">
214 <term><varname>external_pid_file</varname> (<type>string</type>)</term>
216 <primary><varname>external_pid_file</> configuration parameter</primary>
220 Specifies the name of an additional process-id (PID) file that the
221 <application>postmaster</> should create for use by server
222 administration programs.
223 This parameter can only be set at server start.
230 In a default installation, none of the above parameters are set
231 explicitly. Instead, the
232 data directory is specified by the <option>-D</option> command-line
233 option or the <envar>PGDATA</envar> environment variable, and the
234 configuration files are all found within the data directory.
238 If you wish to keep the configuration files elsewhere than the
239 data directory, the postmaster's <option>-D</option>
240 command-line option or <envar>PGDATA</envar> environment variable
241 must point to the directory containing the configuration files,
242 and the <varname>data_directory</> parameter must be set in
243 <filename>postgresql.conf</filename> (or on the command line) to show
244 where the data directory is actually located. Notice that
245 <varname>data_directory</> overrides <option>-D</option> and
246 <envar>PGDATA</envar> for the location
247 of the data directory, but not for the location of the configuration
252 If you wish, you can specify the configuration file names and locations
253 individually using the parameters <varname>config_file</>,
254 <varname>hba_file</> and/or <varname>ident_file</>.
255 <varname>config_file</> can only be specified on the
256 <command>postmaster</command> command line, but the others can be
257 set within the main configuration file. If all three parameters plus
258 <varname>data_directory</> are explicitly set, then it is not necessary
259 to specify <option>-D</option> or <envar>PGDATA</envar>.
263 When setting any of these parameters, a relative path will be interpreted
264 with respect to the directory in which the <command>postmaster</command>
269 <sect1 id="runtime-config-connection">
270 <title>Connections and Authentication</title>
272 <sect2 id="runtime-config-connection-settings">
273 <title>Connection Settings</title>
277 <varlistentry id="guc-listen-addresses" xreflabel="listen_addresses">
278 <term><varname>listen_addresses</varname> (<type>string</type>)</term>
280 <primary><varname>listen_addresses</> configuration parameter</primary>
284 Specifies the TCP/IP address(es) on which the server is
285 to listen for connections from client applications.
286 The value takes the form of a comma-separated list of host names
287 and/or numeric IP addresses. The special entry <literal>*</>
288 corresponds to all available IP interfaces.
289 If the list is empty, the server does not listen on any IP interface
290 at all, in which case only Unix-domain sockets can be used to connect
292 The default value is <systemitem class="systemname">localhost</>,
293 which allows only local <quote>loopback</> connections to be made.
294 This parameter can only be set at server start.
299 <varlistentry id="guc-port" xreflabel="port">
300 <term><varname>port</varname> (<type>integer</type>)</term>
302 <primary><varname>port</> configuration parameter</primary>
306 The TCP port the server listens on; 5432 by default. Note that the
307 same port number is used for all IP addresses the server listens on.
308 This parameter can only be set at server start.
313 <varlistentry id="guc-max-connections" xreflabel="max_connections">
314 <term><varname>max_connections</varname> (<type>integer</type>)</term>
316 <primary><varname>max_connections</> configuration parameter</primary>
320 Determines the maximum number of concurrent connections to the
321 database server. The default is typically 100, but may be less
322 if your kernel settings will not support it (as determined
323 during <application>initdb</>). This parameter can only be
328 Increasing this parameter may cause <productname>PostgreSQL</>
329 to request more <systemitem class="osname">System V</> shared
330 memory or semaphores than your operating system's default configuration
331 allows. See <xref linkend="sysvipc"> for information on how to
332 adjust those parameters, if necessary.
337 <varlistentry id="guc-superuser-reserved-connections"
338 xreflabel="superuser_reserved_connections">
339 <term><varname>superuser_reserved_connections</varname>
340 (<type>integer</type>)</term>
342 <primary><varname>superuser_reserved_connections</> configuration parameter</primary>
346 Determines the number of connection <quote>slots</quote> that
347 are reserved for connections by <productname>PostgreSQL</>
348 superusers. At most <xref linkend="guc-max-connections">
349 connections can ever be active simultaneously. Whenever the
350 number of active concurrent connections is at least
351 <varname>max_connections</> minus
352 <varname>superuser_reserved_connections</varname>, new
353 connections will be accepted only for superusers.
357 The default value is 2. The value must be less than the value of
358 <varname>max_connections</varname>. This parameter can only be
364 <varlistentry id="guc-unix-socket-directory" xreflabel="unix_socket_directory">
365 <term><varname>unix_socket_directory</varname> (<type>string</type>)</term>
367 <primary><varname>unix_socket_directory</> configuration parameter</primary>
371 Specifies the directory of the Unix-domain socket on which the
372 server is to listen for
373 connections from client applications. The default is normally
374 <filename>/tmp</filename>, but can be changed at build time.
375 This parameter can only be set at server start.
380 <varlistentry id="guc-unix-socket-group" xreflabel="unix_socket_group">
381 <term><varname>unix_socket_group</varname> (<type>string</type>)</term>
383 <primary><varname>unix_socket_group</> configuration parameter</primary>
387 Sets the owning group of the Unix-domain socket. (The owning
388 user of the socket is always the user that starts the
389 server.) In combination with the parameter
390 <varname>unix_socket_permissions</varname> this can be used as
391 an additional access control mechanism for Unix-domain connections.
392 By default this is the empty string, which selects the default
393 group for the current user. This parameter can only be set at
399 <varlistentry id="guc-unix-socket-permissions" xreflabel="unix_socket_permissions">
400 <term><varname>unix_socket_permissions</varname> (<type>integer</type>)</term>
402 <primary><varname>unix_socket_permissions</> configuration parameter</primary>
406 Sets the access permissions of the Unix-domain socket. Unix-domain
407 sockets use the usual Unix file system permission set.
408 The parameter value is expected to be a numeric mode
409 specification in the form accepted by the
410 <function>chmod</function> and <function>umask</function>
411 system calls. (To use the customary octal format the number
412 must start with a <literal>0</literal> (zero).)
416 The default permissions are <literal>0777</literal>, meaning
417 anyone can connect. Reasonable alternatives are
418 <literal>0770</literal> (only user and group, see also
419 <varname>unix_socket_group</varname>) and <literal>0700</literal>
420 (only user). (Note that for a Unix-domain socket, only write
421 permission matters and so there is no point in setting or revoking
422 read or execute permissions.)
426 This access control mechanism is independent of the one
427 described in <xref linkend="client-authentication">.
431 This parameter can only be set at server start.
436 <varlistentry id="guc-bonjour-name" xreflabel="bonjour_name">
437 <term><varname>bonjour_name</varname> (<type>string</type>)</term>
439 <primary><varname>bonjour_name</> configuration parameter</primary>
443 Specifies the <productname>Bonjour</productname> broadcast
444 name. The computer name is used if this parameter is set to the
445 empty string <literal>''</> (which is the default). This parameter is
446 ignored if the server was not compiled with
447 <productname>Bonjour</productname> support.
448 This parameter can only be set at server start.
453 <varlistentry id="guc-tcp-keepalives-idle" xreflabel="tcp_keepalives_idle">
454 <term><varname>tcp_keepalives_idle</varname> (<type>integer</type>)</term>
456 <primary><varname>tcp_keepalives_idle</> configuration parameter</primary>
460 On systems that support the <symbol>TCP_KEEPIDLE</symbol> socket option, specifies the
461 number of seconds between sending keepalives on an otherwise idle
462 connection. A value of 0 uses the system default. If <symbol>TCP_KEEPIDLE</symbol> is
463 not supported, this parameter must be 0. This parameter is ignored for
464 connections made via a Unix-domain socket.
469 <varlistentry id="guc-tcp-keepalives-interval" xreflabel="tcp_keepalives_interval">
470 <term><varname>tcp_keepalives_interval</varname> (<type>integer</type>)</term>
472 <primary><varname>tcp_keepalives_interval</> configuration parameter</primary>
476 On systems that support the <symbol>TCP_KEEPINTVL</symbol> socket option, specifies how
477 long, in seconds, to wait for a response to a keepalive before
478 retransmitting. A value of 0 uses the system default. If <symbol>TCP_KEEPINTVL</symbol>
479 is not supported, this parameter must be 0. This parameter is ignored
480 for connections made via a Unix-domain socket.
485 <varlistentry id="guc-tcp-keepalives-count" xreflabel="tcp_keepalives_count">
486 <term><varname>tcp_keepalives_count</varname> (<type>integer</type>)</term>
488 <primary><varname>tcp_keepalives_count</> configuration parameter</primary>
492 On systems that support the <symbol>TCP_KEEPCNT</symbol> socket option, specifies how
493 many keepalives may be lost before the connection is considered dead.
494 A value of 0 uses the system default. If <symbol>TCP_KEEPCNT</symbol> is not
495 supported, this parameter must be 0. This parameter is ignored
496 for connections made via a Unix-domain socket.
503 <sect2 id="runtime-config-connection-security">
504 <title>Security and Authentication</title>
507 <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
508 <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
509 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
510 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
512 <primary><varname>authentication_timeout</> configuration parameter</primary>
517 Maximum time to complete client authentication, in seconds. If a
518 would-be client has not completed the authentication protocol in
519 this much time, the server breaks the connection. This prevents
520 hung clients from occupying a connection indefinitely.
522 This parameter can only be set in the <filename>postgresql.conf</>
523 file or on the server command line.
528 <varlistentry id="guc-ssl" xreflabel="ssl">
529 <term><varname>ssl</varname> (<type>boolean</type>)</term>
531 <primary><varname>ssl</> configuration parameter</primary>
535 Enables <acronym>SSL</> connections. Please read
536 <xref linkend="ssl-tcp"> before using this. The default
537 is <literal>off</>. This parameter can only be set at server
543 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
544 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
546 <primary><varname>password_encryption</> configuration parameter</primary>
550 When a password is specified in <xref
551 linkend="sql-createuser" endterm="sql-createuser-title"> or
552 <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
553 without writing either <literal>ENCRYPTED</> or
554 <literal>UNENCRYPTED</>, this parameter determines whether the
555 password is to be encrypted. The default is <literal>on</>
556 (encrypt the password).
561 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
562 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
564 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
568 Sets the location of the Kerberos server key file. See
569 <xref linkend="kerberos-auth"> for details. This parameter
570 can only be set at server start.
575 <varlistentry id="guc-krb-srvname" xreflabel="krb_srvname">
576 <term><varname>krb_srvname</varname> (<type>string</type>)</term>
578 <primary><varname>krb_srvname</> configuration parameter</primary>
582 Sets the Kerberos service name. See <xref linkend="kerberos-auth">
583 for details. This parameter can only be set at server start.
588 <varlistentry id="guc-krb-server-hostname" xreflabel="krb_server_hostname">
589 <term><varname>krb_server_hostname</varname> (<type>string</type>)</term>
591 <primary><varname>krb_server_hostname</> configuration parameter</primary>
595 Sets the host name part of the service principal.
596 This, combined with <varname>krb_srvname</>, is used to generate
597 the complete service principal, that is
598 <varname>krb_srvname</><literal>/</><varname>krb_server_hostname</><literal>@</>REALM.
599 If not set, the default is the server host name. See <xref linkend="kerberos-auth">
600 for details. This parameter can only be set at server start.
605 <varlistentry id="guc-krb-caseins-users" xreflabel="krb_caseins_users">
606 <term><varname>krb_caseins_users</varname> (<type>boolean</type>)</term>
608 <primary><varname>krb_caseins_users</varname> configuration parameter</primary>
612 Sets whether Kerberos user names should be treated case-insensitively.
613 The default is <literal>off</> (case sensitive). This parameter
614 can only be set at server start.
619 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
620 <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
622 <primary><varname>db_user_namespace</> configuration parameter</primary>
626 This parameter enables per-database user names. It is off by default.
627 This parameter can only be set in the <filename>postgresql.conf</>
628 file or on the server command line.
632 If this is on, you should create users as <literal>username@dbname</>.
633 When <literal>username</> is passed by a connecting client,
634 <literal>@</> and the database name are appended to the user
635 name and that database-specific user name is looked up by the
636 server. Note that when you create users with names containing
637 <literal>@</> within the SQL environment, you will need to
642 With this parameter enabled, you can still create ordinary global
643 users. Simply append <literal>@</> when specifying the user
644 name in the client. The <literal>@</> will be stripped off
645 before the user name is looked up by the server.
650 This feature is intended as a temporary measure until a
651 complete solution is found. At that time, this option will
662 <sect1 id="runtime-config-resource">
663 <title>Resource Consumption</title>
665 <sect2 id="runtime-config-resource-memory">
666 <title>Memory</title>
669 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
670 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
672 <primary><varname>shared_buffers</> configuration parameter</primary>
676 Sets the number of shared memory buffers used by the database
677 server. The default is typically 4000, but may be less if your
678 kernel settings will not support it (as determined during
679 <application>initdb</>). Each buffer is 8192 bytes, unless a
680 different value of <symbol>BLCKSZ</symbol> was chosen when building
681 the server. This setting must be at least 16, as well as at
682 least twice the value of <xref linkend="guc-max-connections">;
683 however, settings significantly higher than the minimum are
684 usually needed for good performance. Values of a few thousand
685 are recommended for production installations. This parameter can
686 only be set at server start.
690 Increasing this parameter may cause <productname>PostgreSQL</>
691 to request more <systemitem class="osname">System V</> shared
692 memory than your operating system's default configuration
693 allows. See <xref linkend="sysvipc"> for information on how to
694 adjust those parameters, if necessary.
699 <varlistentry id="guc-temp-buffers" xreflabel="temp_buffers">
700 <term><varname>temp_buffers</varname> (<type>integer</type>)</term>
702 <primary><varname>temp_buffers</> configuration parameter</primary>
706 Sets the maximum number of temporary buffers used by each database
707 session. These are session-local buffers used only for access
708 to temporary tables. The default is 1000. The setting can
709 be changed within individual sessions, but only up until the
710 first use of temporary tables within a session; subsequent
711 attempts to change the value will have no effect on that session.
715 A session will allocate temporary buffers as needed up to the limit
716 given by <varname>temp_buffers</>. The cost of setting a large
717 value in sessions that do not actually need a lot of temporary
718 buffers is only a buffer descriptor, or about 64 bytes, per
719 increment in <varname>temp_buffers</>. However if a buffer is
720 actually used an additional 8192 bytes will be consumed for it
721 (or in general, <symbol>BLCKSZ</symbol> bytes).
726 <varlistentry id="guc-max-prepared-transactions" xreflabel="max_prepared_transactions">
727 <term><varname>max_prepared_transactions</varname> (<type>integer</type>)</term>
729 <primary><varname>max_prepared_transactions</> configuration parameter</primary>
733 Sets the maximum number of transactions that can be in the
734 <quote>prepared</> state simultaneously (see <xref
735 linkend="sql-prepare-transaction"
736 endterm="sql-prepare-transaction-title">).
737 Setting this parameter to zero disables the prepared-transaction
740 This parameter can only be set at server start.
744 If you are not using prepared transactions, this parameter may as
745 well be set to zero. If you are using them, you will probably
746 want <varname>max_prepared_transactions</varname> to be at least
747 as large as <xref linkend="guc-max-connections">, to avoid unwanted
748 failures at the prepare step.
752 Increasing this parameter may cause <productname>PostgreSQL</>
753 to request more <systemitem class="osname">System V</> shared
754 memory than your operating system's default configuration
755 allows. See <xref linkend="sysvipc"> for information on how to
756 adjust those parameters, if necessary.
761 <varlistentry id="guc-work-mem" xreflabel="work_mem">
762 <term><varname>work_mem</varname> (<type>integer</type>)</term>
764 <primary><varname>work_mem</> configuration parameter</primary>
768 Specifies the amount of memory to be used by internal sort operations
769 and hash tables before switching to temporary disk files. The value is
770 specified in kilobytes, and defaults to 1024 kilobytes (1 MB).
771 Note that for a complex query, several sort or hash operations might be
772 running in parallel; each one will be allowed to use as much memory
773 as this value specifies before it starts to put data into temporary
774 files. Also, several running sessions could be doing such operations
775 concurrently. So the total memory used could be many
776 times the value of <varname>work_mem</varname>; it is necessary to
777 keep this fact in mind when choosing the value. Sort operations are
778 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
780 Hash tables are used in hash joins, hash-based aggregation, and
781 hash-based processing of <literal>IN</> subqueries.
786 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
787 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
789 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
793 Specifies the maximum amount of memory to be used in maintenance
794 operations, such as <command>VACUUM</command>, <command>CREATE
795 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>.
796 The value is specified in kilobytes, and defaults to 16384 kilobytes
797 (16 MB). Since only one of these operations can be executed at
798 a time by a database session, and an installation normally doesn't
799 have very many of them happening concurrently, it's safe to set this
800 value significantly larger than <varname>work_mem</varname>. Larger
801 settings may improve performance for vacuuming and for restoring
807 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
808 <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
810 <primary><varname>max_stack_depth</> configuration parameter</primary>
814 Specifies the maximum safe depth of the server's execution stack.
815 The ideal setting for this parameter is the actual stack size limit
816 enforced by the kernel (as set by <literal>ulimit -s</> or local
817 equivalent), less a safety margin of a megabyte or so. The safety
818 margin is needed because the stack depth is not checked in every
819 routine in the server, but only in key potentially-recursive routines
820 such as expression evaluation. Setting the parameter higher than
821 the actual kernel limit will mean that a runaway recursive function
822 can crash an individual backend process. The default setting is
823 2048 KB (two megabytes), which is conservatively small and unlikely
824 to risk crashes. However, it may be too small to allow execution
825 of complex functions.
826 Only superusers can change this setting.
833 <sect2 id="runtime-config-resource-fsm">
834 <title>Free Space Map</title>
837 <primary>free space map</primary>
841 These parameters control the size of the shared <firstterm>free space
842 map</>, which tracks the locations of unused space in the database.
843 An undersized free space map may cause the database to consume
844 increasing amounts of disk space over time, because free space that
845 is not in the map cannot be re-used; instead <productname>PostgreSQL</>
846 will request more disk space from the operating system when it needs
848 The last few lines displayed by a database-wide <command>VACUUM VERBOSE</>
849 command can help in determining if the current settings are adequate.
850 A <literal>NOTICE</> message is also printed during such an operation
851 if the current settings are too low.
855 Increasing these parameters may cause <productname>PostgreSQL</>
856 to request more <systemitem class="osname">System V</> shared
857 memory than your operating system's default configuration
858 allows. See <xref linkend="sysvipc"> for information on how to
859 adjust those parameters, if necessary.
863 <varlistentry id="guc-max-fsm-pages" xreflabel="max_fsm_pages">
864 <term><varname>max_fsm_pages</varname> (<type>integer</type>)</term>
866 <primary><varname>max_fsm_pages</> configuration parameter</primary>
870 Sets the maximum number of disk pages for which free space will
871 be tracked in the shared free-space map. Six bytes of shared memory
872 are consumed for each page slot. This setting must be more than
873 16 * <varname>max_fsm_relations</varname>. The default is chosen
874 by <application>initdb</> depending on the amount of available memory,
875 and can range from 20000 to 200000.
876 This parameter can only be set at server start.
881 <varlistentry id="guc-max-fsm-relations" xreflabel="max_fsm_relations">
882 <term><varname>max_fsm_relations</varname> (<type>integer</type>)</term>
884 <primary><varname>max_fsm_relations</> configuration parameter</primary>
888 Sets the maximum number of relations (tables and indexes) for which
889 free space will be tracked in the shared free-space map. Roughly
890 seventy bytes of shared memory are consumed for each slot.
892 This parameter can only be set at server start.
899 <sect2 id="runtime-config-resource-kernel">
900 <title>Kernel Resource Usage</title>
903 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
904 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
906 <primary><varname>max_files_per_process</> configuration parameter</primary>
910 Sets the maximum number of simultaneously open files allowed to each
911 server subprocess. The default is 1000. If the kernel is enforcing
912 a safe per-process limit, you don't need to worry about this setting.
913 But on some platforms (notably, most BSD systems), the kernel will
914 allow individual processes to open many more files than the system
915 can really support when a large number of processes all try to open
916 that many files. If you find yourself seeing <quote>Too many open
917 files</> failures, try reducing this setting.
918 This parameter can only be set at server start.
923 <varlistentry id="guc-preload-libraries" xreflabel="preload_libraries">
924 <term><varname>preload_libraries</varname> (<type>string</type>)</term>
926 <primary><varname>preload_libraries</> configuration parameter</primary>
930 This variable specifies one or more shared libraries that are
931 to be preloaded at server start. A parameterless
932 initialization function can optionally be called for each
933 library. To specify that, add a colon and the name of the
934 initialization function after the library name. For example
935 <literal>'$libdir/mylib:mylib_init'</literal> would cause
936 <literal>mylib</> to be preloaded and <literal>mylib_init</>
937 to be executed. If more than one library is to be loaded,
938 separate their names with commas.
942 If a specified library or initialization function is not found,
943 the server will fail to start.
947 <productname>PostgreSQL</productname> procedural language
948 libraries may be preloaded in this way, typically by using the
949 syntax <literal>'$libdir/plXXX:plXXX_init'</literal> where
950 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
951 <literal>tcl</>, or <literal>python</>.
955 By preloading a shared library (and initializing it if
956 applicable), the library startup time is avoided when the
957 library is first used. However, the time to start each new
958 server process may increase slightly, even if that process never
959 uses the library. So this parameter is recommended only for
960 libraries that will be used in most sessions.
968 <sect2 id="runtime-config-resource-vacuum-cost">
969 <title id="runtime-config-resource-vacuum-cost-title">
970 Cost-Based Vacuum Delay
974 During the execution of <xref linkend="sql-vacuum"
975 endterm="sql-vacuum-title"> and <xref linkend="sql-analyze"
976 endterm="sql-analyze-title"> commands, the system maintains an
977 internal counter that keeps track of the estimated cost of the
978 various I/O operations that are performed. When the accumulated
979 cost reaches a limit (specified by
980 <varname>vacuum_cost_limit</varname>), the process performing
981 the operation will sleep for a while (specified by
982 <varname>vacuum_cost_delay</varname>). Then it will reset the
983 counter and continue execution.
987 The intent of this feature is to allow administrators to reduce
988 the I/O impact of these commands on concurrent database
989 activity. There are many situations in which it is not very
990 important that maintenance commands like
991 <command>VACUUM</command> and <command>ANALYZE</command> finish
992 quickly; however, it is usually very important that these
993 commands do not significantly interfere with the ability of the
994 system to perform other database operations. Cost-based vacuum
995 delay provides a way for administrators to achieve this.
999 This feature is disabled by default. To enable it, set the
1000 <varname>vacuum_cost_delay</varname> variable to a nonzero
1005 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1006 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)</term>
1008 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1012 The length of time, in milliseconds, that the process will sleep
1013 when the cost limit has been exceeded.
1014 The default value is 0, which disables the cost-based vacuum
1015 delay feature. Positive values enable cost-based vacuuming.
1016 Note that on many systems, the effective resolution
1017 of sleep delays is 10 milliseconds; setting
1018 <varname>vacuum_cost_delay</varname> to a value that is
1019 not a multiple of 10 may have the same results as setting it
1020 to the next higher multiple of 10.
1025 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1026 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
1028 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1032 The estimated cost for vacuuming a buffer found in the shared buffer
1033 cache. It represents the cost to lock the buffer pool, lookup
1034 the shared hash table and scan the content of the page. The
1040 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1041 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
1043 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1047 The estimated cost for vacuuming a buffer that has to be read from
1048 disk. This represents the effort to lock the buffer pool,
1049 lookup the shared hash table, read the desired block in from
1050 the disk and scan its content. The default value is 10.
1055 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1056 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
1058 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1062 The estimated cost charged when vacuum modifies a block that was
1063 previously clean. It represents the extra I/O required to
1064 flush the dirty block out to disk again. The default value is
1070 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1071 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
1073 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1077 The accumulated cost that will cause the vacuuming process to sleep.
1078 The default value is 200.
1086 There are certain operations that hold critical locks and should
1087 therefore complete as quickly as possible. Cost-based vacuum
1088 delays do not occur during such operations. Therefore it is
1089 possible that the cost accumulates far higher than the specified
1090 limit. To avoid uselessly long delays in such cases, the actual
1091 delay is calculated as <varname>vacuum_cost_delay</varname> *
1092 <varname>accumulated_balance</varname> /
1093 <varname>vacuum_cost_limit</varname> with a maximum of
1094 <varname>vacuum_cost_delay</varname> * 4.
1099 <sect2 id="runtime-config-resource-background-writer">
1100 <title>Background Writer</title>
1103 Beginning in <productname>PostgreSQL</> 8.0, there is a separate server
1104 process called the <firstterm>background writer</>, whose sole function
1105 is to issue writes of <quote>dirty</> shared buffers. The intent is
1106 that server processes handling user queries should seldom or never have
1107 to wait for a write to occur, because the background writer will do it.
1108 This arrangement also reduces the performance penalty associated with
1109 checkpoints. The background writer will continuously trickle out dirty
1110 pages to disk, so that only a few pages will need to be forced out when
1111 checkpoint time arrives, instead of the storm of dirty-buffer writes that
1112 formerly occurred at each checkpoint. However there is a net overall
1113 increase in I/O load, because where a repeatedly-dirtied page might
1114 before have been written only once per checkpoint interval, the
1115 background writer might write it several times in the same interval.
1116 In most situations a continuous low load is preferable to periodic
1117 spikes, but the parameters discussed in this subsection can be used to tune
1118 the behavior for local needs.
1122 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1123 <term><varname>bgwriter_delay</varname> (<type>integer</type>)</term>
1125 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1129 Specifies the delay between activity rounds for the
1130 background writer. In each round the writer issues writes
1131 for some number of dirty buffers (controllable by the
1132 following parameters). It then sleeps for <varname>bgwriter_delay</>
1133 milliseconds, and repeats. The default value is 200. Note
1134 that on many systems, the effective resolution of sleep
1135 delays is 10 milliseconds; setting <varname>bgwriter_delay</>
1136 to a value that is not a multiple of 10 may have the same
1137 results as setting it to the next higher multiple of 10.
1138 This parameter can only be set in the <filename>postgresql.conf</>
1139 file or on the server command line.
1144 <varlistentry id="guc-bgwriter-lru-percent" xreflabel="bgwriter_lru_percent">
1145 <term><varname>bgwriter_lru_percent</varname> (<type>floating point</type>)</term>
1147 <primary><varname>bgwriter_lru_percent</> configuration parameter</primary>
1151 To reduce the probability that server processes will need to issue
1152 their own writes, the background writer tries to write buffers that
1153 are likely to be recycled soon. In each round, it examines up to
1154 <varname>bgwriter_lru_percent</> of the buffers that are nearest to
1155 being recycled, and writes any that are dirty.
1156 The default value is 1.0 (this is a percentage of the total number
1158 This parameter can only be set in the <filename>postgresql.conf</>
1159 file or on the server command line.
1164 <varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1165 <term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)</term>
1167 <primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1171 In each round, no more than this many buffers will be written
1172 as a result of scanning soon-to-be-recycled buffers.
1173 The default value is 5.
1174 This parameter can only be set in the <filename>postgresql.conf</>
1175 file or on the server command line.
1180 <varlistentry id="guc-bgwriter-all-percent" xreflabel="bgwriter_all_percent">
1181 <term><varname>bgwriter_all_percent</varname> (<type>floating point</type>)</term>
1183 <primary><varname>bgwriter_all_percent</> configuration parameter</primary>
1187 To reduce the amount of work that will be needed at checkpoint time,
1188 the background writer also does a circular scan through the entire
1189 buffer pool, writing buffers that are found to be dirty.
1190 In each round, it examines up to
1191 <varname>bgwriter_all_percent</> of the buffers for this purpose.
1192 The default value is 0.333 (this is a percentage of the total number
1193 of shared buffers). With the default <varname>bgwriter_delay</>
1194 setting, this will allow the entire shared buffer pool to be scanned
1195 about once per minute.
1196 This parameter can only be set in the <filename>postgresql.conf</>
1197 file or on the server command line.
1202 <varlistentry id="guc-bgwriter-all-maxpages" xreflabel="bgwriter_all_maxpages">
1203 <term><varname>bgwriter_all_maxpages</varname> (<type>integer</type>)</term>
1205 <primary><varname>bgwriter_all_maxpages</> configuration parameter</primary>
1209 In each round, no more than this many buffers will be written
1210 as a result of the scan of the entire buffer pool. (If this
1211 limit is reached, the scan stops, and resumes at the next buffer
1212 during the next round.)
1213 The default value is 5.
1214 This parameter can only be set in the <filename>postgresql.conf</>
1215 file or on the server command line.
1222 Smaller values of <varname>bgwriter_all_percent</varname> and
1223 <varname>bgwriter_all_maxpages</varname> reduce the extra I/O load
1224 caused by the background writer, but leave more work to be done
1225 at checkpoint time. To reduce load spikes at checkpoints,
1226 increase these two values.
1227 Similarly, smaller values of <varname>bgwriter_lru_percent</varname> and
1228 <varname>bgwriter_lru_maxpages</varname> reduce the extra I/O load
1229 caused by the background writer, but make it more likely that server
1230 processes will have to issue writes for themselves, delaying interactive
1232 To disable background writing entirely,
1233 set both <varname>maxpages</varname> values and/or both
1234 <varname>percent</varname> values to zero.
1239 <sect1 id="runtime-config-wal">
1240 <title>Write Ahead Log</title>
1243 See also <xref linkend="wal-configuration"> for details on WAL
1247 <sect2 id="runtime-config-wal-settings">
1248 <title>Settings</title>
1251 <varlistentry id="guc-fsync" xreflabel="fsync">
1253 <primary><varname>fsync</> configuration parameter</primary>
1255 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1258 If this parameter is on, the <productname>PostgreSQL</> server
1259 will try to make sure that updates are physically written to
1260 disk, by issuing <function>fsync()</> system calls or various
1261 equivalent methods (see <xref linkend="guc-wal-sync-method">).
1262 This ensures that the database cluster can recover to a
1263 consistent state after an operating system or hardware crash.
1267 However, using <varname>fsync</varname> results in a
1268 performance penalty: when a transaction is committed,
1269 <productname>PostgreSQL</productname> must wait for the
1270 operating system to flush the write-ahead log to disk. When
1271 <varname>fsync</varname> is disabled, the operating system is
1272 allowed to do its best in buffering, ordering, and delaying
1273 writes. This can result in significantly improved performance.
1274 However, if the system crashes, the results of the last few
1275 committed transactions may be lost in part or whole. In the
1276 worst case, unrecoverable data corruption may occur.
1277 (Crashes of the database software itself are <emphasis>not</>
1278 a risk factor here. Only an operating-system-level crash
1279 creates a risk of corruption.)
1283 Due to the risks involved, there is no universally correct
1284 setting for <varname>fsync</varname>. Some administrators
1285 always disable <varname>fsync</varname>, while others only
1286 turn it off during initial bulk data loads, where there is a clear
1287 restart point if something goes wrong. Others
1288 always leave <varname>fsync</varname> enabled. The default is
1289 to enable <varname>fsync</varname>, for maximum reliability.
1290 If you trust your operating system, your hardware, and your
1291 utility company (or your battery backup), you can consider
1292 disabling <varname>fsync</varname>.
1296 This parameter can only be set in the <filename>postgresql.conf</>
1297 file or on the server command line.
1298 If you turn this parameter off, also consider turning off
1299 <xref linkend="guc-full-page-writes">.
1304 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1305 <term><varname>wal_sync_method</varname> (<type>string</type>)</term>
1307 <primary><varname>wal_sync_method</> configuration parameter</primary>
1311 Method used for forcing WAL updates out to disk.
1312 If <varname>fsync</varname> is off then this setting is irrelevant,
1313 since updates will not be forced out at all.
1314 Possible values are:
1319 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>)
1324 <literal>fdatasync</> (call <function>fdatasync()</> at each commit)
1329 <literal>fsync_writethrough</> (call <function>fsync()</> at each commit, forcing write-through of any disk write cache)
1334 <literal>fsync</> (call <function>fsync()</> at each commit)
1339 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>)
1344 Not all of these choices are available on all platforms.
1345 The default is the first method in the above list that is supported
1347 This parameter can only be set in the <filename>postgresql.conf</>
1348 file or on the server command line.
1353 <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
1355 <primary><varname>full_page_writes</> configuration parameter</primary>
1357 <term><varname>full_page_writes</varname> (<type>boolean</type>)</term>
1360 When this parameter is on, the <productname>PostgreSQL</> server
1361 writes the entire content of each disk page to WAL during the
1362 first modification of that page after a checkpoint.
1363 This is needed because
1364 a page write that is in process during an operating system crash might
1365 be only partially completed, leading to an on-disk page
1366 that contains a mix of old and new data. The row-level change data
1367 normally stored in WAL will not be enough to completely restore
1368 such a page during post-crash recovery. Storing the full page image
1369 guarantees that the page can be correctly restored, but at a price
1370 in increasing the amount of data that must be written to WAL.
1371 (Because WAL replay always starts from a checkpoint, it is sufficient
1372 to do this during the first change of each page after a checkpoint.
1373 Therefore, one way to reduce the cost of full-page writes is to
1374 increase the checkpoint interval parameters.)
1378 Turning this parameter off speeds normal operation, but
1379 might lead to a corrupt database after an operating system crash
1380 or power failure. The risks are similar to turning off
1381 <varname>fsync</>, though smaller. It may be safe to turn off
1382 this parameter if you have hardware (such as a battery-backed disk
1383 controller) or filesystem software (e.g., Reiser4) that reduces
1384 the risk of partial page writes to an acceptably low level.
1388 Turning off this parameter does not affect use of
1389 WAL archiving for point-in-time recovery (PITR)
1390 (see <xref linkend="backup-online">).
1394 This parameter can only be set in the <filename>postgresql.conf</>
1395 file or on the server command line.
1396 The default is <literal>on</>.
1401 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1402 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1404 <primary><varname>wal_buffers</> configuration parameter</primary>
1408 Number of disk-page buffers allocated in shared memory for WAL data.
1409 The default is 8. The setting need only be large enough to hold
1410 the amount of WAL data generated by one typical transaction, since
1411 the data is written out to disk at every transaction commit.
1412 This parameter can only be set at server start.
1416 Increasing this parameter may cause <productname>PostgreSQL</>
1417 to request more <systemitem class="osname">System V</> shared
1418 memory than your operating system's default configuration
1419 allows. See <xref linkend="sysvipc"> for information on how to
1420 adjust those parameters, if necessary.
1425 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
1426 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1428 <primary><varname>commit_delay</> configuration parameter</primary>
1432 Time delay between writing a commit record to the WAL buffer
1433 and flushing the buffer out to disk, in microseconds. A
1434 nonzero delay can allow multiple transactions to be committed
1435 with only one <function>fsync()</function> system call, if
1436 system load is high enough that additional transactions become
1437 ready to commit within the given interval. But the delay is
1438 just wasted if no other transactions become ready to
1439 commit. Therefore, the delay is only performed if at least
1440 <varname>commit_siblings</varname> other transactions are
1441 active at the instant that a server process has written its
1442 commit record. The default is zero (no delay).
1447 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
1448 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1450 <primary><varname>commit_siblings</> configuration parameter</primary>
1454 Minimum number of concurrent open transactions to require
1455 before performing the <varname>commit_delay</> delay. A larger
1456 value makes it more probable that at least one other
1457 transaction will become ready to commit during the delay
1458 interval. The default is five.
1465 <sect2 id="runtime-config-wal-checkpoints">
1466 <title>Checkpoints</title>
1469 <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1470 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1472 <primary><varname>checkpoint_segments</> configuration parameter</primary>
1476 Maximum distance between automatic WAL checkpoints, in log
1477 file segments (each segment is normally 16 megabytes). The
1479 This parameter can only be set in the <filename>postgresql.conf</>
1480 file or on the server command line.
1485 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1486 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1488 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
1492 Maximum time between automatic WAL checkpoints, in
1493 seconds. The default is 300 seconds.
1494 This parameter can only be set in the <filename>postgresql.conf</>
1495 file or on the server command line.
1500 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1501 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1503 <primary><varname>checkpoint_warning</> configuration parameter</primary>
1507 Write a message to the server log if checkpoints caused by
1508 the filling of checkpoint segment files happen closer together
1509 than this many seconds (which suggests that
1510 <varname>checkpoint_segments</> ought to be raised). The default is
1511 30 seconds. Zero disables the warning.
1512 This parameter can only be set in the <filename>postgresql.conf</>
1513 file or on the server command line.
1520 <sect2 id="runtime-config-wal-archiving">
1521 <title>Archiving</title>
1524 <varlistentry id="guc-archive-command" xreflabel="archive_command">
1525 <term><varname>archive_command</varname> (<type>string</type>)</term>
1527 <primary><varname>archive_command</> configuration parameter</primary>
1531 The shell command to execute to archive a completed segment of
1532 the WAL file series. If this is an empty string (the default),
1533 WAL archiving is disabled. Any <literal>%p</> in the string is
1534 replaced by the absolute path of the file to archive, and any
1535 <literal>%f</> is replaced by the file name only. Use
1536 <literal>%%</> to embed an actual <literal>%</> character in the
1537 command. For more information see <xref
1538 linkend="backup-archiving-wal">.
1539 This parameter can only be set in the <filename>postgresql.conf</>
1540 file or on the server command line.
1543 It is important for the command to return a zero exit status if
1544 and only if it succeeds. Examples:
1546 archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
1547 archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
1557 <sect1 id="runtime-config-query">
1558 <title>Query Planning</title>
1560 <sect2 id="runtime-config-query-enable">
1561 <title>Planner Method Configuration</title>
1564 These configuration parameters provide a crude method of
1565 influencing the query plans chosen by the query optimizer. If
1566 the default plan chosen by the optimizer for a particular query
1567 is not optimal, a temporary solution may be found by using one
1568 of these configuration parameters to force the optimizer to
1569 choose a different plan. Turning one of these settings off
1570 permanently is seldom a good idea, however.
1571 Better ways to improve the quality of the
1572 plans chosen by the optimizer include adjusting the <xref
1573 linkend="runtime-config-query-constants"
1574 endterm="runtime-config-query-constants-title">, running <xref
1575 linkend="sql-analyze" endterm="sql-analyze-title"> more
1576 frequently, increasing the value of the <xref
1577 linkend="guc-default-statistics-target"> configuration parameter,
1578 and increasing the amount of statistics collected for
1579 specific columns using <command>ALTER TABLE SET
1580 STATISTICS</command>.
1584 <varlistentry id="guc-enable-bitmapscan" xreflabel="enable_bitmapscan">
1585 <term><varname>enable_bitmapscan</varname> (<type>boolean</type>)</term>
1587 <primary>bitmap scan</primary>
1590 <primary><varname>enable_bitmapscan</> configuration parameter</primary>
1594 Enables or disables the query planner's use of bitmap-scan plan
1595 types. The default is <literal>on</>.
1600 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
1601 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
1603 <primary><varname>enable_hashagg</> configuration parameter</primary>
1607 Enables or disables the query planner's use of hashed
1608 aggregation plan types. The default is <literal>on</>.
1613 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
1614 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
1616 <primary><varname>enable_hashjoin</> configuration parameter</primary>
1620 Enables or disables the query planner's use of hash-join plan
1621 types. The default is <literal>on</>.
1626 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
1627 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
1629 <primary>index scan</primary>
1632 <primary><varname>enable_indexscan</> configuration parameter</primary>
1636 Enables or disables the query planner's use of index-scan plan
1637 types. The default is <literal>on</>.
1642 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
1643 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
1645 <primary><varname>enable_mergejoin</> configuration parameter</primary>
1649 Enables or disables the query planner's use of merge-join plan
1650 types. The default is <literal>on</>.
1655 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
1656 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
1658 <primary><varname>enable_nestloop</> configuration parameter</primary>
1662 Enables or disables the query planner's use of nested-loop join
1663 plans. It's not possible to suppress nested-loop joins entirely,
1664 but turning this variable off discourages the planner from using
1665 one if there are other methods available. The default is
1671 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
1672 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
1674 <primary>sequential scan</primary>
1677 <primary><varname>enable_seqscan</> configuration parameter</primary>
1681 Enables or disables the query planner's use of sequential scan
1682 plan types. It's not possible to suppress sequential scans
1683 entirely, but turning this variable off discourages the planner
1684 from using one if there are other methods available. The
1685 default is <literal>on</>.
1690 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
1691 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
1693 <primary><varname>enable_sort</> configuration parameter</primary>
1697 Enables or disables the query planner's use of explicit sort
1698 steps. It's not possible to suppress explicit sorts entirely,
1699 but turning this variable off discourages the planner from
1700 using one if there are other methods available. The default
1706 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
1707 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
1709 <primary><varname>enable_tidscan</> configuration parameter</primary>
1713 Enables or disables the query planner's use of <acronym>TID</>
1714 scan plan types. The default is <literal>on</>.
1721 <sect2 id="runtime-config-query-constants">
1722 <title id="runtime-config-query-constants-title">
1723 Planner Cost Constants
1728 Unfortunately, there is no well-defined method for determining
1729 ideal values for the family of <quote>cost</quote> variables that
1730 appear below. You are encouraged to experiment and share
1737 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
1738 <term><varname>effective_cache_size</varname> (<type>floating point</type>)</term>
1740 <primary><varname>effective_cache_size</> configuration parameter</primary>
1744 Sets the planner's assumption about the effective size of the
1745 disk cache that is available to a single index scan. This is
1746 factored into estimates of the cost of using an index; a
1747 higher value makes it more likely index scans will be used, a
1748 lower value makes it more likely sequential scans will be
1749 used. When setting this parameter you should consider both
1750 <productname>PostgreSQL</productname>'s shared buffers and the
1751 portion of the kernel's disk cache that will be used for
1752 <productname>PostgreSQL</productname> data files. Also, take
1753 into account the expected number of concurrent queries using
1754 different indexes, since they will have to share the available
1755 space. This parameter has no effect on the size of shared
1756 memory allocated by <productname>PostgreSQL</productname>, nor
1757 does it reserve kernel disk cache; it is used only for
1758 estimation purposes. The value is measured in disk pages,
1759 which are normally 8192 bytes each. The default is 1000.
1764 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
1765 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
1767 <primary><varname>random_page_cost</> configuration parameter</primary>
1771 Sets the planner's estimate of the cost of a
1772 nonsequentially fetched disk page. This is measured as a
1773 multiple of the cost of a sequential page fetch. A higher
1774 value makes it more likely a sequential scan will be used, a
1775 lower value makes it more likely an index scan will be
1776 used. The default is four.
1781 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
1782 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
1784 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
1788 Sets the planner's estimate of the cost of processing
1789 each row during a query. This is measured as a fraction of
1790 the cost of a sequential page fetch. The default is 0.01.
1795 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
1796 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
1798 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
1802 Sets the planner's estimate of the cost of processing
1803 each index row during an index scan. This is measured as a
1804 fraction of the cost of a sequential page fetch. The default
1810 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
1811 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
1813 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
1817 Sets the planner's estimate of the cost of processing each
1818 operator in a <literal>WHERE</> clause. This is measured as a fraction of
1819 the cost of a sequential page fetch. The default is 0.0025.
1827 <sect2 id="runtime-config-query-geqo">
1828 <title>Genetic Query Optimizer</title>
1832 <varlistentry id="guc-geqo" xreflabel="geqo">
1834 <primary>genetic query optimization</primary>
1837 <primary>GEQO</primary>
1838 <see>genetic query optimization</see>
1841 <primary><varname>geqo</> configuration parameter</primary>
1843 <term><varname>geqo</varname> (<type>boolean</type>)</term>
1846 Enables or disables genetic query optimization, which is an
1847 algorithm that attempts to do query planning without
1848 exhaustive searching. This is on by default. The
1849 <varname>geqo_threshold</varname> variable provides a more
1850 granular way to disable GEQO for certain classes of queries.
1855 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
1856 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
1858 <primary><varname>geqo_threshold</> configuration parameter</primary>
1862 Use genetic query optimization to plan queries with at least
1863 this many <literal>FROM</> items involved. (Note that a
1864 <literal>FULL OUTER JOIN</> construct counts as only one <literal>FROM</>
1865 item.) The default is 12. For simpler queries it is usually best
1866 to use the deterministic, exhaustive planner, but for queries with
1867 many tables the deterministic planner takes too long.
1872 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
1873 <term><varname>geqo_effort</varname>
1874 (<type>integer</type>)</term>
1876 <primary><varname>geqo_effort</> configuration parameter</primary>
1880 Controls the trade off between planning time and query plan
1881 efficiency in GEQO. This variable must be an integer in the
1882 range from 1 to 10. The default value is 5. Larger values
1883 increase the time spent doing query planning, but also
1884 increase the likelihood that an efficient query plan will be
1889 <varname>geqo_effort</varname> doesn't actually do anything
1890 directly; it is only used to compute the default values for
1891 the other variables that influence GEQO behavior (described
1892 below). If you prefer, you can set the other parameters by
1898 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
1899 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
1901 <primary><varname>geqo_pool_size</> configuration parameter</primary>
1905 Controls the pool size used by GEQO. The pool size is the
1906 number of individuals in the genetic population. It must be
1907 at least two, and useful values are typically 100 to 1000. If
1908 it is set to zero (the default setting) then a suitable
1909 default is chosen based on <varname>geqo_effort</varname> and
1910 the number of tables in the query.
1915 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
1916 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
1918 <primary><varname>geqo_generations</> configuration parameter</primary>
1922 Controls the number of generations used by GEQO. Generations
1923 specifies the number of iterations of the algorithm. It must
1924 be at least one, and useful values are in the same range as
1925 the pool size. If it is set to zero (the default setting)
1926 then a suitable default is chosen based on
1927 <varname>geqo_pool_size</varname>.
1932 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
1933 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
1935 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
1939 Controls the selection bias used by GEQO. The selection bias
1940 is the selective pressure within the population. Values can be
1941 from 1.50 to 2.00; the latter is the default.
1948 <sect2 id="runtime-config-query-other">
1949 <title>Other Planner Options</title>
1953 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
1954 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
1956 <primary><varname>default_statistics_target</> configuration parameter</primary>
1960 Sets the default statistics target for table columns that have
1961 not had a column-specific target set via <command>ALTER TABLE
1962 SET STATISTICS</>. Larger values increase the time needed to
1963 do <command>ANALYZE</>, but may improve the quality of the
1964 planner's estimates. The default is 10. For more information
1965 on the use of statistics by the <productname>PostgreSQL</>
1966 query planner, refer to <xref linkend="planner-stats">.
1971 <varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
1972 <term><varname>constraint_exclusion</varname> (<type>boolean</type>)</term>
1974 <primary>constraint exclusion</primary>
1977 <primary><varname>constraint_exclusion</> configuration parameter</primary>
1981 Enables or disables the query planner's use of table constraints to
1982 optimize queries. The default is <literal>off</>.
1986 When this parameter is <literal>on</>, the planner compares
1987 query conditions with table <literal>CHECK</> constraints, and
1988 omits scanning tables for which the conditions contradict the
1989 constraints. For example:
1992 CREATE TABLE parent(key integer, ...);
1993 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
1994 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
1996 SELECT * FROM parent WHERE key = 2400;
1999 With constraint exclusion enabled, this <command>SELECT</>
2000 will not scan <structname>child1000</> at all. This can
2001 improve performance when inheritance is used to build
2006 Currently, <varname>constraint_exclusion</> is disabled by
2007 default because it risks incorrect results if query plans are
2008 cached — if a table constraint is changed or dropped,
2009 the previously generated plan might now be wrong, and there is
2010 no built-in mechanism to force re-planning. (This deficiency
2011 will probably be addressed in a future
2012 <productname>PostgreSQL</> release.) Another reason for
2013 keeping it off is that the constraint checks are relatively
2014 expensive, and in many circumstances will yield no savings.
2015 It is recommended to turn this on only if you are actually
2016 using partitioned tables designed to take advantage of the
2021 Refer to <xref linkend="ddl-partitioning"> for more information
2022 on using constraint exclusion and partitioning.
2027 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
2028 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
2030 <primary><varname>from_collapse_limit</> configuration parameter</primary>
2034 The planner will merge sub-queries into upper queries if the
2035 resulting <literal>FROM</literal> list would have no more than
2036 this many items. Smaller values reduce planning time but may
2037 yield inferior query plans. The default is 8. It is usually
2038 wise to keep this less than <xref linkend="guc-geqo-threshold">.
2039 For more information see <xref linkend="explicit-joins">.
2044 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
2045 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
2047 <primary><varname>join_collapse_limit</> configuration parameter</primary>
2051 The planner will rewrite explicit <literal>JOIN</>
2052 constructs (except <literal>FULL JOIN</>s) into lists of
2053 <literal>FROM</> items whenever a list of no more than this many items
2054 would result. Smaller values reduce planning time but may
2055 yield inferior query plans.
2059 By default, this variable is set the same as
2060 <varname>from_collapse_limit</varname>, which is appropriate
2061 for most uses. Setting it to 1 prevents any reordering of
2062 explicit <literal>JOIN</>s. Thus, the explicit join order
2063 specified in the query will be the actual order in which the
2064 relations are joined. The query planner does not always choose
2065 the optimal join order; advanced users may elect to
2066 temporarily set this variable to 1, and then specify the join
2067 order they desire explicitly.
2068 For more information see <xref linkend="explicit-joins">.
2077 <sect1 id="runtime-config-logging">
2078 <title>Error Reporting and Logging</title>
2080 <indexterm zone="runtime-config-logging">
2081 <primary>server log</primary>
2084 <sect2 id="runtime-config-logging-where">
2085 <title>Where To Log</title>
2087 <indexterm zone="runtime-config-logging-where">
2088 <primary>where to log</primary>
2093 <varlistentry id="guc-log-destination" xreflabel="log_destination">
2094 <term><varname>log_destination</varname> (<type>string</type>)</term>
2096 <primary><varname>log_destination</> configuration parameter</primary>
2100 <productname>PostgreSQL</productname> supports several methods
2101 for logging server messages, including
2102 <systemitem>stderr</systemitem> and
2103 <systemitem>syslog</systemitem>. On Windows,
2104 <systemitem>eventlog</systemitem> is also supported. Set this
2105 parameter to a list of desired log destinations separated by
2106 commas. The default is to log to <systemitem>stderr</systemitem>
2108 This parameter can only be set in the <filename>postgresql.conf</>
2109 file or on the server command line.
2114 <varlistentry id="guc-redirect-stderr" xreflabel="redirect_stderr">
2115 <term><varname>redirect_stderr</varname> (<type>boolean</type>)</term>
2117 <primary><varname>redirect_stderr</> configuration parameter</primary>
2121 This parameter allows messages sent to <application>stderr</> to be
2122 captured and redirected into log files.
2123 This method, in combination with logging to <application>stderr</>,
2124 is often more useful than
2125 logging to <application>syslog</>, since some types of messages
2126 may not appear in <application>syslog</> output (a common example
2127 is dynamic-linker failure messages).
2128 This parameter can only be set at server start.
2133 <varlistentry id="guc-log-directory" xreflabel="log_directory">
2134 <term><varname>log_directory</varname> (<type>string</type>)</term>
2136 <primary><varname>log_directory</> configuration parameter</primary>
2140 When <varname>redirect_stderr</> is enabled, this parameter
2141 determines the directory in which log files will be created.
2142 It may be specified as an absolute path, or relative to the
2143 cluster data directory.
2144 This parameter can only be set in the <filename>postgresql.conf</>
2145 file or on the server command line.
2150 <varlistentry id="guc-log-filename" xreflabel="log_filename">
2151 <term><varname>log_filename</varname> (<type>string</type>)</term>
2153 <primary><varname>log_filename</> configuration parameter</primary>
2157 When <varname>redirect_stderr</varname> is enabled, this parameter
2158 sets the file names of the created log files. The value
2159 is treated as a <systemitem>strftime</systemitem> pattern,
2160 so <literal>%</literal>-escapes
2161 can be used to specify time-varying file names.
2162 If no <literal>%</literal>-escapes are present,
2163 <productname>PostgreSQL</productname> will
2164 append the epoch of the new log file's open time. For example,
2165 if <varname>log_filename</varname> were <literal>server_log</literal>, then the
2166 chosen file name would be <literal>server_log.1093827753</literal>
2167 for a log starting at Sun Aug 29 19:02:33 2004 MST.
2168 This parameter can only be set in the <filename>postgresql.conf</>
2169 file or on the server command line.
2174 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
2175 <term><varname>log_rotation_age</varname> (<type>integer</type>)</term>
2177 <primary><varname>log_rotation_age</> configuration parameter</primary>
2181 When <varname>redirect_stderr</varname> is enabled, this parameter
2182 determines the maximum lifetime of an individual log file.
2183 After this many minutes have elapsed, a new log file will
2184 be created. Set to zero to disable time-based creation of
2186 This parameter can only be set in the <filename>postgresql.conf</>
2187 file or on the server command line.
2192 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
2193 <term><varname>log_rotation_size</varname> (<type>integer</type>)</term>
2195 <primary><varname>log_rotation_size</> configuration parameter</primary>
2199 When <varname>redirect_stderr</varname> is enabled, this parameter
2200 determines the maximum size of an individual log file.
2201 After this many kilobytes have been emitted into a log file,
2202 a new log file will be created. Set to zero to disable size-based
2203 creation of new log files.
2204 This parameter can only be set in the <filename>postgresql.conf</>
2205 file or on the server command line.
2210 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
2211 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)</term>
2213 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
2217 When <varname>redirect_stderr</varname> is enabled, this parameter will cause
2218 <productname>PostgreSQL</productname> to truncate (overwrite),
2219 rather than append to, any existing log file of the same name.
2220 However, truncation will occur only when a new file is being opened
2221 due to time-based rotation, not during server startup or size-based
2222 rotation. When off, pre-existing files will be appended to in
2223 all cases. For example, using this setting in combination with
2224 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
2225 would result in generating twenty-four hourly log files and then
2226 cyclically overwriting them.
2227 This parameter can only be set in the <filename>postgresql.conf</>
2228 file or on the server command line.
2231 Example: To keep 7 days of logs, one log file per day named
2232 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
2233 etc, and automatically overwrite last week's log with this week's log,
2234 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
2235 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>, and
2236 <varname>log_rotation_age</varname> to <literal>1440</literal>.
2239 Example: To keep 24 hours of logs, one log file per hour, but
2240 also rotate sooner if the log file size exceeds 1GB, set
2241 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
2242 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>,
2243 <varname>log_rotation_age</varname> to <literal>60</literal>, and
2244 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
2245 Including <literal>%M</> in <varname>log_filename</varname> allows
2246 any size-driven rotations that may occur to select a file name
2247 different from the hour's initial file name.
2252 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
2253 <term><varname>syslog_facility</varname> (<type>string</type>)</term>
2255 <primary><varname>syslog_facility</> configuration parameter</primary>
2259 When logging to <application>syslog</> is enabled, this parameter
2260 determines the <application>syslog</application>
2261 <quote>facility</quote> to be used. You may choose
2262 from <literal>LOCAL0</>, <literal>LOCAL1</>,
2263 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
2264 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
2265 the default is <literal>LOCAL0</>. See also the
2266 documentation of your system's
2267 <application>syslog</application> daemon.
2268 This parameter can only be set in the <filename>postgresql.conf</>
2269 file or on the server command line.
2274 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
2275 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
2277 <primary><varname>syslog_identity</> configuration parameter</primary>
2281 When logging to <application>syslog</> is enabled, this parameter
2282 determines the program name used to identify
2283 <productname>PostgreSQL</productname> messages in
2284 <application>syslog</application> logs. The default is
2285 <literal>postgres</literal>.
2286 This parameter can only be set in the <filename>postgresql.conf</>
2287 file or on the server command line.
2294 <sect2 id="runtime-config-logging-when">
2295 <title>When To Log</title>
2299 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
2300 <term><varname>client_min_messages</varname> (<type>string</type>)</term>
2302 <primary><varname>client_min_messages</> configuration parameter</primary>
2306 Controls which message levels are sent to the client.
2307 Valid values are <literal>DEBUG5</>,
2308 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
2309 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
2310 <literal>WARNING</>, <literal>ERROR</>, <literal>FATAL</>,
2311 and <literal>PANIC</>. Each level
2312 includes all the levels that follow it. The later the level,
2313 the fewer messages are sent. The default is
2314 <literal>NOTICE</>. Note that <literal>LOG</> has a different
2315 rank here than in <varname>log_min_messages</>.
2320 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
2321 <term><varname>log_min_messages</varname> (<type>string</type>)</term>
2323 <primary><varname>log_min_messages</> configuration parameter</primary>
2327 Controls which message levels are written to the server log.
2328 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
2329 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
2330 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
2331 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
2332 <literal>PANIC</>. Each level includes all the levels that
2333 follow it. The later the level, the fewer messages are sent
2334 to the log. The default is <literal>NOTICE</>. Note that
2335 <literal>LOG</> has a different rank here than in
2336 <varname>client_min_messages</>.
2337 Only superusers can change this setting.
2342 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
2343 <term><varname>log_error_verbosity</varname> (<type>string</type>)</term>
2345 <primary><varname>log_error_verbosity</> configuration parameter</primary>
2349 Controls the amount of detail written in the server log for each
2350 message that is logged. Valid values are <literal>TERSE</>,
2351 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
2352 fields to displayed messages.
2353 Only superusers can change this setting.
2358 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
2359 <term><varname>log_min_error_statement</varname> (<type>string</type>)</term>
2361 <primary><varname>log_min_error_statement</> configuration parameter</primary>
2365 Controls whether or not the SQL statement that causes an error
2366 condition will also be recorded in the server log. All SQL
2367 statements that cause an error of the specified level or
2368 higher are logged. The default is
2369 <literal>PANIC</literal> (effectively turning this feature
2370 off for normal use). Valid values are <literal>DEBUG5</literal>,
2371 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
2372 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
2373 <literal>INFO</literal>, <literal>NOTICE</literal>,
2374 <literal>WARNING</literal>, <literal>ERROR</literal>,
2375 <literal>FATAL</literal>, and <literal>PANIC</literal>. For
2376 example, if you set this to <literal>ERROR</literal> then all
2377 SQL statements causing errors, fatal errors, or panics will be
2378 logged. Enabling this parameter can be helpful in tracking down
2379 the source of any errors that appear in the server log.
2380 Only superusers can change this setting.
2385 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
2386 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
2388 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
2392 Logs the statement and its duration on a single log line if its
2393 duration is greater than or equal to the specified number of
2394 milliseconds. Setting this to zero will print all statements
2395 and their durations. Minus-one (the default) disables the
2396 feature. For example, if you set it to <literal>250</literal>
2397 then all SQL statements that run 250ms or longer will be
2398 logged. Enabling this parameter can be useful in tracking down
2399 unoptimized queries in your applications. This setting is
2400 independent of <varname>log_statement</varname> and
2401 <varname>log_duration</varname>. Only superusers can change
2407 <varlistentry id="guc-silent-mode" xreflabel="silent_mode">
2408 <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
2410 <primary><varname>silent_mode</> configuration parameter</primary>
2414 Runs the server silently. If this parameter is set, the server
2415 will automatically run in background and any controlling
2416 terminals are disassociated.
2417 The server's standard output and standard error are redirected
2418 to <literal>/dev/null</>, so any messages sent to them will be lost.
2419 Unless <application>syslog</> logging is selected or
2420 <varname>redirect_stderr</> is enabled, using this parameter
2421 is discouraged because it makes it impossible to see error messages.
2422 This parameter can only be set at server start.
2430 Here is a list of the various message severity levels used in
2434 <term><literal>DEBUG[1-5]</literal></term>
2437 Provides information for use by developers.
2443 <term><literal>INFO</literal></term>
2446 Provides information implicitly requested by the user,
2447 e.g., during <command>VACUUM VERBOSE</>.
2453 <term><literal>NOTICE</literal></term>
2456 Provides information that may be helpful to users, e.g.,
2457 truncation of long identifiers and the creation of indexes as part
2464 <term><literal>WARNING</literal></term>
2467 Provides warnings to the user, e.g., <command>COMMIT</>
2468 outside a transaction block.
2474 <term><literal>ERROR</literal></term>
2477 Reports an error that caused the current command to abort.
2483 <term><literal>LOG</literal></term>
2486 Reports information of interest to administrators, e.g.,
2487 checkpoint activity.
2493 <term><literal>FATAL</literal></term>
2496 Reports an error that caused the current session to abort.
2502 <term><literal>PANIC</literal></term>
2505 Reports an error that caused all sessions to abort.
2513 <sect2 id="runtime-config-logging-what">
2514 <title>What To Log</title>
2519 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
2520 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
2521 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
2522 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
2524 <primary><varname>debug_print_parse</> configuration parameter</primary>
2527 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
2530 <primary><varname>debug_print_plan</> configuration parameter</primary>
2533 <primary><varname>debug_pretty_print</> configuration parameter</primary>
2537 These parameters enable various debugging output to be emitted.
2538 For each executed query, they print
2539 the resulting parse tree, the query rewriter output, or the
2540 execution plan. <varname>debug_pretty_print</varname> indents
2541 these displays to produce a more readable but much longer
2542 output format. <varname>client_min_messages</varname> or
2543 <varname>log_min_messages</varname> must be
2544 <literal>DEBUG1</literal> or lower to actually send this output
2545 to the client or the server log, respectively.
2546 These parameters are off by default.
2551 <varlistentry id="guc-log-connections" xreflabel="log_connections">
2552 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
2554 <primary><varname>log_connections</> configuration parameter</primary>
2558 This outputs a line to the server log detailing each successful
2559 connection. This is off by default, although it is probably very
2560 useful. Some client programs, like <application>psql</>, attempt
2561 to connect twice while determining if a password is required, so
2562 duplicate <quote>connection received</> messages do not
2563 necessarily indicate a problem.
2564 This parameter can only be set in the <filename>postgresql.conf</>
2565 file or on the server command line.
2570 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
2571 <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
2573 <primary><varname>log_disconnections</> configuration parameter</primary>
2577 This outputs a line in the server log similar to
2578 <varname>log_connections</varname> but at session termination,
2579 and includes the duration of the session. This is off by
2581 This parameter can only be set in the <filename>postgresql.conf</>
2582 file or on the server command line.
2588 <varlistentry id="guc-log-duration" xreflabel="log_duration">
2589 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
2591 <primary><varname>log_duration</> configuration parameter</primary>
2595 Causes the duration of every completed statement which satisfies
2596 <varname>log_statement</> to be logged. When using this option,
2597 if you are not using <application>syslog</>, it is recommended
2598 that you log the PID or session ID using <varname>log_line_prefix</>
2599 so that you can link the statement message to the later
2600 duration message using the process ID or session ID. The default is
2601 <literal>off</>. Only superusers can change this setting.
2606 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
2607 <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
2609 <primary><varname>log_line_prefix</> configuration parameter</primary>
2613 This is a <function>printf</>-style string that is output at the
2614 beginning of each log line. The default is an empty string.
2615 Each recognized escape is replaced as outlined
2616 below - anything else that looks like an escape is ignored. Other
2617 characters are copied straight to the log line. Some escapes are
2618 only recognized by session processes, and do not apply to
2619 background processes such as the postmaster. <application>Syslog</>
2621 time stamp and process ID information, so you probably do not want to
2622 use those escapes if you are using <application>syslog</>.
2623 This parameter can only be set in the <filename>postgresql.conf</>
2624 file or on the server command line.
2630 <entry>Escape</entry>
2631 <entry>Effect</entry>
2632 <entry>Session only</entry>
2637 <entry><literal>%u</literal></entry>
2638 <entry>User name</entry>
2642 <entry><literal>%d</literal></entry>
2643 <entry>Database name</entry>
2647 <entry><literal>%r</literal></entry>
2648 <entry>Remote host name or IP address, and remote port</entry>
2652 <entry><literal>%h</literal></entry>
2653 <entry>Remote host name or IP address</entry>
2657 <entry><literal>%p</literal></entry>
2658 <entry>Process ID</entry>
2662 <entry><literal>%t</literal></entry>
2663 <entry>Time stamp (no milliseconds)</entry>
2667 <entry><literal>%m</literal></entry>
2668 <entry>Time stamp with milliseconds</entry>
2672 <entry><literal>%i</literal></entry>
2673 <entry>Command tag: This is the command that generated the log line.</entry>
2677 <entry><literal>%c</literal></entry>
2678 <entry>Session ID: A unique identifier for each session.
2679 It is 2 4-byte hexadecimal numbers (without leading zeros)
2680 separated by a dot. The numbers
2681 are the session start time and the process ID, so this can also
2682 be used as a space saving way of printing these items.</entry>
2686 <entry><literal>%l</literal></entry>
2687 <entry>Number of the log line for each process, starting at 1</entry>
2691 <entry><literal>%s</literal></entry>
2692 <entry>Session start time stamp</entry>
2696 <entry><literal>%x</literal></entry>
2697 <entry>Transaction ID</entry>
2701 <entry><literal>%q</literal></entry>
2702 <entry>Does not produce any output, but tells non-session
2703 processes to stop at this point in the string. Ignored by
2704 session processes.</entry>
2708 <entry><literal>%%</literal></entry>
2709 <entry>Literal <literal>%</></entry>
2719 <varlistentry id="guc-log-statement" xreflabel="log_statement">
2720 <term><varname>log_statement</varname> (<type>string</type>)</term>
2722 <primary><varname>log_statement</> configuration parameter</primary>
2726 Controls which SQL statements are logged. Valid values are
2727 <literal>none</>, <literal>ddl</>, <literal>mod</>, and
2728 <literal>all</>. <literal>ddl</> logs all data definition
2729 commands like <literal>CREATE</>, <literal>ALTER</>, and
2730 <literal>DROP</> commands. <literal>mod</> logs all
2731 <literal>ddl</> statements, plus <literal>INSERT</>,
2732 <literal>UPDATE</>, <literal>DELETE</>, <literal>TRUNCATE</>,
2733 and <literal>COPY FROM</>. <literal>PREPARE</> and
2734 <literal>EXPLAIN ANALYZE</> statements are also logged if their
2735 contained command is of an appropriate type.
2738 The default is <literal>none</>. Only superusers can change this
2744 The <command>EXECUTE</command> statement is not considered a
2745 <literal>ddl</> or <literal>mod</> statement. When it is logged,
2746 only the name of the prepared statement is reported, not the
2747 actual prepared statement.
2751 When a function is defined in the
2752 <application>PL/pgSQL</application>server-side language, any queries
2753 executed by the function will only be logged the first time that the
2754 function is invoked in a particular session. This is because
2755 <application>PL/pgSQL</application> keeps a cache of the
2756 query plans produced for the SQL statements in the function.
2762 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
2763 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
2765 <primary><varname>log_hostname</> configuration parameter</primary>
2769 By default, connection log messages only show the IP address of the
2770 connecting host. Turning on this parameter causes logging of the
2771 host name as well. Note that depending on your host name resolution
2772 setup this might impose a non-negligible performance penalty.
2773 This parameter can only be set in the <filename>postgresql.conf</>
2774 file or on the server command line.
2783 <sect1 id="runtime-config-statistics">
2784 <title>Run-Time Statistics</title>
2786 <sect2 id="runtime-config-statistics-monitor">
2787 <title>Statistics Monitoring</title>
2791 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
2792 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
2793 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
2794 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
2796 <primary><varname>log_statement_stats</> configuration parameter</primary>
2799 <primary><varname>log_parser_stats</> configuration parameter</primary>
2802 <primary><varname>log_planner_stats</> configuration parameter</primary>
2805 <primary><varname>log_executor_stats</> configuration parameter</primary>
2809 For each query, write performance statistics of the respective
2810 module to the server log. This is a crude profiling
2811 instrument. <varname>log_statement_stats</varname> reports total
2812 statement statistics, while the others report per-module statistics.
2813 <varname>log_statement_stats</varname> cannot be enabled together with
2814 any of the per-module options. All of these options are disabled by
2815 default. Only superusers can change these settings.
2823 <sect2 id="runtime-config-statistics-collector">
2824 <title>Query and Index Statistics Collector</title>
2827 These parameters control a server-wide statistics collection feature.
2828 When statistics collection is enabled, the data that is produced can be
2829 accessed via the <structname>pg_stat</structname> and
2830 <structname>pg_statio</structname> family of system views.
2831 Refer to <xref linkend="monitoring"> for more information.
2836 <varlistentry id="guc-stats-start-collector" xreflabel="stats_start_collector">
2837 <term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>
2839 <primary><varname>stats_start_collector</> configuration parameter</primary>
2843 Controls whether the server should start the
2844 statistics-collection subprocess. This is on by default, but
2845 may be turned off if you know you have no interest in
2846 collecting statistics or running autovacuum.
2847 This parameter can only be set at server start, because the collection
2848 subprocess cannot be started or stopped on-the-fly. (However, the
2849 extent to which statistics are actually gathered can be changed while
2850 the server is running, so long as the subprocess exists.)
2855 <varlistentry id="guc-stats-command-string" xreflabel="stats_command_string">
2856 <term><varname>stats_command_string</varname> (<type>boolean</type>)</term>
2858 <primary><varname>stats_command_string</> configuration parameter</primary>
2862 Enables the collection of statistics on the currently
2863 executing command of each session, along with the time at
2864 which that command began execution. This parameter is off by
2865 default. Note that even when enabled, this information is not
2866 visible to all users, only to superusers and the user owning
2867 the session being reported on; so it should not represent a
2869 Only superusers can change this setting.
2874 <varlistentry id="guc-stats-block-level" xreflabel="stats_block_level">
2875 <term><varname>stats_block_level</varname> (<type>boolean</type>)</term>
2877 <primary><varname>stats_block_level</> configuration parameter</primary>
2881 Enables the collection of block-level statistics on database
2882 activity. This parameter is off by default.
2883 Only superusers can change this setting.
2888 <varlistentry id="guc-stats-row-level" xreflabel="stats_row_level">
2889 <term><varname>stats_row_level</varname> (<type>boolean</type>)</term>
2891 <primary><varname>stats_row_level</> configuration parameter</primary>
2895 Enables the collection of row-level statistics on database
2896 activity. This parameter is off by default.
2897 Only superusers can change this setting.
2902 <varlistentry id="guc-stats-reset-on-server-start" xreflabel="stats_reset_on_server_start">
2903 <term><varname>stats_reset_on_server_start</varname> (<type>boolean</type>)</term>
2905 <primary><varname>stats_reset_on_server_start</> configuration parameter</primary>
2909 If on, collected statistics are zeroed out whenever the server
2910 is restarted. If off, statistics are accumulated across server
2911 restarts. The default is <literal>off</>. This parameter can only
2912 be set at server start.
2921 <sect1 id="runtime-config-autovacuum">
2922 <title>Automatic Vacuuming</title>
2925 <primary>autovacuum</primary>
2926 <secondary>configuration parameters</secondary>
2930 These settings control the behavior of the <firstterm>autovacuum</>
2931 feature. Refer to <xref linkend="autovacuum"> for
2937 <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
2938 <term><varname>autovacuum</varname> (<type>boolean</type>)</term>
2940 <primary><varname>autovacuum</> configuration parameter</primary>
2944 Controls whether the server should run the
2945 autovacuum daemon. This is off by default.
2946 <varname>stats_start_collector</> and <varname>stats_row_level</>
2947 must also be turned on for autovacuum to work.
2948 This parameter can only be set in the <filename>postgresql.conf</>
2949 file or on the server command line.
2954 <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
2955 <term><varname>autovacuum_naptime</varname> (<type>integer</type>)</term>
2957 <primary><varname>autovacuum_naptime</> configuration parameter</primary>
2961 Specifies the delay between activity rounds for the autovacuum
2962 daemon. In each round the daemon examines one database
2963 and issues <command>VACUUM</> and <command>ANALYZE</> commands
2964 as needed for tables in that database. The delay is measured
2965 in seconds, and the default is 60.
2966 This parameter can only be set in the <filename>postgresql.conf</>
2967 file or on the server command line.
2972 <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
2973 <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)</term>
2975 <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
2979 Specifies the minimum number of updated or deleted tuples needed
2980 to trigger a <command>VACUUM</> in any one table.
2981 The default is 1000.
2982 This parameter can only be set in the <filename>postgresql.conf</>
2983 file or on the server command line.
2984 This setting can be overridden for individual tables by entries in
2985 <structname>pg_autovacuum</>.
2990 <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
2991 <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)</term>
2993 <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
2997 Specifies the minimum number of inserted, updated or deleted tuples
2998 needed to trigger an <command>ANALYZE</> in any one table.
3000 This parameter can only be set in the <filename>postgresql.conf</>
3001 file or on the server command line.
3002 This setting can be overridden for individual tables by entries in
3003 <structname>pg_autovacuum</>.
3008 <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
3009 <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)</term>
3011 <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
3015 Specifies a fraction of the table size to add to
3016 <varname>autovacuum_vacuum_threshold</varname>
3017 when deciding whether to trigger a <command>VACUUM</>.
3019 This parameter can only be set in the <filename>postgresql.conf</>
3020 file or on the server command line.
3021 This setting can be overridden for individual tables by entries in
3022 <structname>pg_autovacuum</>.
3027 <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
3028 <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)</term>
3030 <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
3034 Specifies a fraction of the table size to add to
3035 <varname>autovacuum_analyze_threshold</varname>
3036 when deciding whether to trigger an <command>ANALYZE</>.
3038 This parameter can only be set in the <filename>postgresql.conf</>
3039 file or on the server command line.
3040 This setting can be overridden for individual tables by entries in
3041 <structname>pg_autovacuum</>.
3046 <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
3047 <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
3049 <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
3053 Specifies the cost delay value that will be used in automatic
3054 <command>VACUUM</> operations. If -1 is specified (which is the
3055 default), the regular
3056 <xref linkend="guc-vacuum-cost-delay"> value will be used.
3057 This parameter can only be set in the <filename>postgresql.conf</>
3058 file or on the server command line.
3059 This setting can be overridden for individual tables by entries in
3060 <structname>pg_autovacuum</>.
3065 <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
3066 <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
3068 <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
3072 Specifies the cost limit value that will be used in automatic
3073 <command>VACUUM</> operations. If -1 is specified (which is the
3074 default), the regular
3075 <xref linkend="guc-vacuum-cost-limit"> value will be used.
3076 This parameter can only be set in the <filename>postgresql.conf</>
3077 file or on the server command line.
3078 This setting can be overridden for individual tables by entries in
3079 <structname>pg_autovacuum</>.
3087 <sect1 id="runtime-config-client">
3088 <title>Client Connection Defaults</title>
3090 <sect2 id="runtime-config-client-statement">
3091 <title>Statement Behavior</title>
3094 <varlistentry id="guc-search-path" xreflabel="search_path">
3095 <term><varname>search_path</varname> (<type>string</type>)</term>
3097 <primary><varname>search_path</> configuration parameter</primary>
3099 <indexterm><primary>path</><secondary>for schemas</></>
3102 This variable specifies the order in which schemas are searched
3103 when an object (table, data type, function, etc.) is referenced by a
3104 simple name with no schema component. When there are objects of
3105 identical names in different schemas, the one found first
3106 in the search path is used. An object that is not in any of the
3107 schemas in the search path can only be referenced by specifying
3108 its containing schema with a qualified (dotted) name.
3112 The value for <varname>search_path</varname> has to be a comma-separated
3113 list of schema names. If one of the list items is
3114 the special value <literal>$user</literal>, then the schema
3115 having the name returned by <function>SESSION_USER</> is substituted, if there
3116 is such a schema. (If not, <literal>$user</literal> is ignored.)
3120 The system catalog schema, <literal>pg_catalog</>, is always
3121 searched, whether it is mentioned in the path or not. If it is
3122 mentioned in the path then it will be searched in the specified
3123 order. If <literal>pg_catalog</> is not in the path then it will
3124 be searched <emphasis>before</> searching any of the path items.
3125 It should also be noted that the temporary-table schema,
3126 <literal>pg_temp_<replaceable>nnn</></>, is implicitly searched before any of
3131 When objects are created without specifying a particular target
3132 schema, they will be placed in the first schema listed
3133 in the search path. An error is reported if the search path is
3138 The default value for this parameter is
3139 <literal>'"$user", public'</literal> (where the second part will be
3140 ignored if there is no schema named <literal>public</>).
3141 This supports shared use of a database (where no users
3142 have private schemas, and all share use of <literal>public</>),
3143 private per-user schemas, and combinations of these. Other
3144 effects can be obtained by altering the default search path
3145 setting, either globally or per-user.
3149 The current effective value of the search path can be examined
3150 via the <acronym>SQL</acronym> function
3151 <function>current_schemas()</>. This is not quite the same as
3152 examining the value of <varname>search_path</varname>, since
3153 <function>current_schemas()</> shows how the requests
3154 appearing in <varname>search_path</varname> were resolved.
3158 For more information on schema handling, see <xref linkend="ddl-schemas">.
3163 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
3164 <term><varname>default_tablespace</varname> (<type>string</type>)</term>
3166 <primary><varname>default_tablespace</> configuration parameter</primary>
3168 <indexterm><primary>tablespace</><secondary>default</></>
3171 This variable specifies the default tablespace in which to create
3172 objects (tables and indexes) when a <command>CREATE</> command does
3173 not explicitly specify a tablespace.
3177 The value is either the name of a tablespace, or an empty string
3178 to specify using the default tablespace of the current database.
3179 If the value does not match the name of any existing tablespace,
3180 <productname>PostgreSQL</> will automatically use the default
3181 tablespace of the current database.
3185 For more information on tablespaces,
3186 see <xref linkend="manage-ag-tablespaces">.
3191 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
3192 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
3194 <primary><varname>check_function_bodies</> configuration parameter</primary>
3198 This parameter is normally on. When set to <literal>off</>, it
3199 disables validation of the function body string during <xref
3200 linkend="sql-createfunction"
3201 endterm="sql-createfunction-title">. Disabling validation is
3202 occasionally useful to avoid problems such as forward references
3203 when restoring function definitions from a dump.
3208 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
3210 <primary>transaction isolation level</primary>
3213 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
3215 <term><varname>default_transaction_isolation</varname> (<type>string</type>)</term>
3218 Each SQL transaction has an isolation level, which can be
3219 either <quote>read uncommitted</quote>, <quote>read
3220 committed</quote>, <quote>repeatable read</quote>, or
3221 <quote>serializable</quote>. This parameter controls the
3222 default isolation level of each new transaction. The default
3223 is <quote>read committed</quote>.
3227 Consult <xref linkend="mvcc"> and <xref
3228 linkend="sql-set-transaction"
3229 endterm="sql-set-transaction-title"> for more information.
3234 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
3236 <primary>read-only transaction</primary>
3239 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
3242 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
3245 A read-only SQL transaction cannot alter non-temporary tables.
3246 This parameter controls the default read-only status of each new
3247 transaction. The default is <literal>off</> (read/write).
3251 Consult <xref linkend="sql-set-transaction"
3252 endterm="sql-set-transaction-title"> for more information.
3257 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
3258 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
3260 <primary><varname>statement_timeout</> configuration parameter</primary>
3264 Abort any statement that takes over the specified number of
3265 milliseconds. If <varname>log_min_error_statement</> is set to
3266 <literal>ERROR</> or lower, the statement that timed out will also be
3267 logged. A value of zero (the default) turns off the
3275 <sect2 id="runtime-config-client-format">
3276 <title>Locale and Formatting</title>
3280 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
3281 <term><varname>DateStyle</varname> (<type>string</type>)</term>
3283 <primary><varname>DateStyle</> configuration parameter</primary>
3287 Sets the display format for date and time values, as well as the
3288 rules for interpreting ambiguous date input values. For
3289 historical reasons, this variable contains two independent
3290 components: the output format specification (<literal>ISO</>,
3291 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
3292 and the input/output specification for year/month/day ordering
3293 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
3294 can be set separately or together. The keywords <literal>Euro</>
3295 and <literal>European</> are synonyms for <literal>DMY</>; the
3296 keywords <literal>US</>, <literal>NonEuro</>, and
3297 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
3298 <xref linkend="datatype-datetime"> for more information. The
3299 built-in default is <literal>ISO, MDY</>, but
3300 <application>initdb</application> will initialize the
3301 configuration file with a setting that corresponds to the
3302 behavior of the chosen <varname>lc_time</varname> locale.
3307 <varlistentry id="guc-timezone" xreflabel="timezone">
3308 <term><varname>timezone</varname> (<type>string</type>)</term>
3310 <primary><varname>timezone</> configuration parameter</primary>
3312 <indexterm><primary>time zone</></>
3315 Sets the time zone for displaying and interpreting time
3316 stamps. The default is 'unknown', which means to use whatever
3317 the system environment specifies as the time zone. See <xref
3318 linkend="datatype-datetime"> for more information.
3323 <varlistentry id="guc-australian-timezones" xreflabel="australian_timezones">
3324 <term><varname>australian_timezones</varname> (<type>boolean</type>)</term>
3326 <primary><varname>australian_timezones</> configuration parameter</primary>
3328 <indexterm><primary>time zone</><secondary>Australian</></>
3331 If set to on, <literal>ACST</literal>,
3332 <literal>CST</literal>, <literal>EST</literal>, and
3333 <literal>SAT</literal> are interpreted as Australian time
3334 zones rather than as North/South American time zones and
3335 Saturday. The default is <literal>off</>.
3340 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
3342 <primary>significant digits</primary>
3345 <primary>floating-point</primary>
3346 <secondary>display</secondary>
3349 <primary><varname>extra_float_digits</> configuration parameter</primary>
3352 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
3355 This parameter adjusts the number of digits displayed for
3356 floating-point values, including <type>float4</>, <type>float8</>,
3357 and geometric data types. The parameter value is added to the
3358 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
3359 as appropriate). The value can be set as high as 2, to include
3360 partially-significant digits; this is especially useful for dumping
3361 float data that needs to be restored exactly. Or it can be set
3362 negative to suppress unwanted digits.
3367 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
3368 <term><varname>client_encoding</varname> (<type>string</type>)</term>
3370 <primary><varname>client_encoding</> configuration parameter</primary>
3372 <indexterm><primary>character set</></>
3375 Sets the client-side encoding (character set).
3376 The default is to use the database encoding.
3381 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
3382 <term><varname>lc_messages</varname> (<type>string</type>)</term>
3384 <primary><varname>lc_messages</> configuration parameter</primary>
3388 Sets the language in which messages are displayed. Acceptable
3389 values are system-dependent; see <xref linkend="locale"> for
3390 more information. If this variable is set to the empty string
3391 (which is the default) then the value is inherited from the
3392 execution environment of the server in a system-dependent way.
3396 On some systems, this locale category does not exist. Setting
3397 this variable will still work, but there will be no effect.
3398 Also, there is a chance that no translated messages for the
3399 desired language exist. In that case you will continue to see
3400 the English messages.
3404 Only superusers can change this setting, because it affects the
3405 messages sent to the postmaster log as well as to the client.
3410 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
3411 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
3413 <primary><varname>lc_monetary</> configuration parameter</primary>
3417 Sets the locale to use for formatting monetary amounts, for
3418 example with the <function>to_char</function> family of
3419 functions. Acceptable values are system-dependent; see <xref
3420 linkend="locale"> for more information. If this variable is
3421 set to the empty string (which is the default) then the value
3422 is inherited from the execution environment of the server in a
3423 system-dependent way.
3428 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
3429 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
3431 <primary><varname>lc_numeric</> configuration parameter</primary>
3435 Sets the locale to use for formatting numbers, for example
3436 with the <function>to_char</function> family of
3437 functions. Acceptable values are system-dependent; see <xref
3438 linkend="locale"> for more information. If this variable is
3439 set to the empty string (which is the default) then the value
3440 is inherited from the execution environment of the server in a
3441 system-dependent way.
3446 <varlistentry id="guc-lc-time" xreflabel="lc_time">
3447 <term><varname>lc_time</varname> (<type>string</type>)</term>
3449 <primary><varname>lc_time</> configuration parameter</primary>
3453 Sets the locale to use for formatting date and time values.
3454 (Currently, this setting does nothing, but it may in the
3455 future.) Acceptable values are system-dependent; see <xref
3456 linkend="locale"> for more information. If this variable is
3457 set to the empty string (which is the default) then the value
3458 is inherited from the execution environment of the server in a
3459 system-dependent way.
3467 <sect2 id="runtime-config-client-other">
3468 <title>Other Defaults</title>
3472 <varlistentry id="guc-explain-pretty-print" xreflabel="explain_pretty_print">
3473 <term><varname>explain_pretty_print</varname> (<type>boolean</type>)</term>
3475 <primary><varname>explain_pretty_print</> configuration parameter</primary>
3479 Determines whether <command>EXPLAIN VERBOSE</> uses the
3480 indented or non-indented format for displaying detailed
3481 query-tree dumps. The default is <literal>on</>.
3486 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
3487 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
3489 <primary><varname>dynamic_library_path</> configuration parameter</primary>
3491 <indexterm><primary>dynamic loading</></>
3494 If a dynamically loadable module needs to be opened and the
3495 file name specified in the <command>CREATE FUNCTION</command> or
3496 <command>LOAD</command> command
3497 does not have a directory component (i.e. the
3498 name does not contain a slash), the system will search this
3499 path for the required file.
3503 The value for <varname>dynamic_library_path</varname> has to be a
3504 list of absolute directory paths separated by colons (or semi-colons
3505 on Windows). If a list element starts
3506 with the special string <literal>$libdir</literal>, the
3507 compiled-in <productname>PostgreSQL</productname> package
3508 library directory is substituted for <literal>$libdir</literal>. This
3509 is where the modules provided by the standard
3510 <productname>PostgreSQL</productname> distribution are installed.
3511 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
3512 this directory.) For example:
3514 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
3516 or, in a Windows environment:
3518 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
3523 The default value for this parameter is
3524 <literal>'$libdir'</literal>. If the value is set to an empty
3525 string, the automatic path search is turned off.
3529 This parameter can be changed at run time by superusers, but a
3530 setting done that way will only persist until the end of the
3531 client connection, so this method should be reserved for
3532 development purposes. The recommended way to set this parameter
3533 is in the <filename>postgresql.conf</filename> configuration
3543 <sect1 id="runtime-config-locks">
3544 <title>Lock Management</title>
3548 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
3550 <primary>deadlock</primary>
3551 <secondary>timeout during</secondary>
3554 <primary>timeout</primary>
3555 <secondary>deadlock</secondary>
3558 <primary><varname>deadlock_timeout</> configuration parameter</primary>
3561 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
3564 This is the amount of time, in milliseconds, to wait on a lock
3565 before checking to see if there is a deadlock condition. The
3566 check for deadlock is relatively slow, so the server doesn't run
3567 it every time it waits for a lock. We (optimistically?) assume
3568 that deadlocks are not common in production applications and
3569 just wait on the lock for a while before starting the check for a
3570 deadlock. Increasing this value reduces the amount of time
3571 wasted in needless deadlock checks, but slows down reporting of
3572 real deadlock errors. The default is 1000 (i.e., one second),
3573 which is probably about the smallest value you would want in
3574 practice. On a heavily loaded server you might want to raise it.
3575 Ideally the setting should exceed your typical transaction time,
3576 so as to improve the odds that a lock will be released before
3577 the waiter decides to check for deadlock.
3582 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
3583 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
3585 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
3589 The shared lock table is created with room to describe locks on
3590 <varname>max_locks_per_transaction</varname> *
3591 (<xref linkend="guc-max-connections"> +
3592 <xref linkend="guc-max-prepared-transactions">) objects;
3593 hence, no more than this many distinct objects can
3594 be locked at any one time. (Thus, this parameter's name may be
3595 confusing: it is not a hard limit on the number of locks taken
3596 by any one transaction, but rather a maximum average value.)
3597 The default, 64, has historically
3598 proven sufficient, but you might need to raise this value if you
3599 have clients that touch many different tables in a single
3600 transaction. This parameter can only be set at server start.
3604 Increasing this parameter may cause <productname>PostgreSQL</>
3605 to request more <systemitem class="osname">System V</> shared
3606 memory than your operating system's default configuration
3607 allows. See <xref linkend="sysvipc"> for information on how to
3608 adjust those parameters, if necessary.
3616 <sect1 id="runtime-config-compatible">
3617 <title>Version and Platform Compatibility</title>
3619 <sect2 id="runtime-config-compatible-version">
3620 <title>Previous PostgreSQL Versions</title>
3624 <varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
3625 <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
3626 <indexterm><primary>FROM</><secondary>missing</></>
3628 <primary><varname>add_missing_from</> configuration parameter</primary>
3632 When on, tables that are referenced by a query will be
3633 automatically added to the <literal>FROM</> clause if not
3634 already present. This behavior does not comply with the SQL
3635 standard and many people dislike it because it can mask mistakes
3636 (such as referencing a table where you should have referenced
3637 its alias). The default is <literal>off</>. This variable can be
3638 enabled for compatibility with releases of
3639 <productname>PostgreSQL</> prior to 8.1, where this behavior was
3644 Note that even when this variable is enabled, a warning
3645 message will be emitted for each implicit <literal>FROM</>
3646 entry referenced by a query. Users are encouraged to update
3647 their applications to not rely on this behavior, by adding all
3648 tables referenced by a query to the query's <literal>FROM</>
3649 clause (or its <literal>USING</> clause in the case of
3650 <command>DELETE</>).
3655 <varlistentry id="guc-array-nulls" xreflabel="array_nulls">
3656 <term><varname>array_nulls</varname> (<type>boolean</type>)</term>
3658 <primary><varname>array_nulls</> configuration parameter</primary>
3662 This controls whether the array input parser recognizes
3663 unquoted <literal>NULL</> as specifying a NULL array element.
3664 By default, this is <literal>on</>, allowing array values containing
3665 NULLs to be entered. However, <productname>PostgreSQL</> versions
3666 before 8.2 did not support NULLs in arrays, and therefore would
3667 treat <literal>NULL</> as specifying a normal array element with
3668 the string value <quote>NULL</>. For backwards compatibility with
3669 applications that require the old behavior, this variable can be
3670 turned <literal>off</>.
3674 Note that it is possible to create array values containing NULLs
3675 even when this variable is <literal>off</>.
3680 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
3681 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
3683 <primary><varname>default_with_oids</> configuration parameter</primary>
3687 This controls whether <command>CREATE TABLE</command> and
3688 <command>CREATE TABLE AS</command> include an OID column in
3689 newly-created tables, if neither <literal>WITH OIDS</literal>
3690 nor <literal>WITHOUT OIDS</literal> is specified. It also
3691 determines whether OIDs will be included in tables created by
3692 <command>SELECT INTO</command>. In <productname>PostgreSQL</>
3693 8.1 <varname>default_with_oids</> is disabled by default; in
3694 prior versions of <productname>PostgreSQL</productname>, it
3699 The use of OIDs in user tables is considered deprecated, so
3700 most installations should leave this variable disabled.
3701 Applications that require OIDs for a particular table should
3702 specify <literal>WITH OIDS</literal> when creating the
3703 table. This variable can be enabled for compatibility with old
3704 applications that do not follow this behavior.
3709 <varlistentry id="guc-escape-string-warning" xreflabel="escape_string_warning">
3710 <term><varname>escape_string_warning</varname> (<type>boolean</type>)</term>
3711 <indexterm><primary>strings</><secondary>escape</></>
3713 <primary><varname>escape_string_warning</> configuration parameter</primary>
3717 When on, a warning is issued if a backslash (<literal>\</>)
3718 appears in an ordinary string literal (<literal>'...'</>
3719 syntax). The default is <literal>off</>.
3722 Escape string syntax (<literal>E'...'</>) should be used for
3723 escapes, because in future versions of
3724 <productname>PostgreSQL</productname> ordinary strings will have
3725 the standard-conforming behavior of treating backslashes
3731 <varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
3732 <term><varname>regex_flavor</varname> (<type>string</type>)</term>
3733 <indexterm><primary>regular expressions</></>
3735 <primary><varname>regex_flavor</> configuration parameter</primary>
3739 The regular expression <quote>flavor</> can be set to
3740 <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
3741 The default is <literal>advanced</>. The <literal>extended</>
3742 setting may be useful for exact backwards compatibility with
3743 pre-7.4 releases of <productname>PostgreSQL</>. See
3744 <xref linkend="posix-syntax-details"> for details.
3749 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
3750 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
3752 <primary><varname>sql_inheritance</> configuration parameter</primary>
3754 <indexterm><primary>inheritance</></>
3757 This controls the inheritance semantics, in particular whether
3758 subtables are included by various commands by default. They were
3759 not included in versions prior to 7.1. If you need the old
3760 behavior you can set this variable to <literal>off</>, but in
3761 the long run you are encouraged to change your applications to
3762 use the <literal>ONLY</literal> key word to exclude subtables.
3763 See <xref linkend="ddl-inherit"> for more information about
3772 <sect2 id="runtime-config-compatible-clients">
3773 <title>Platform and Client Compatibility</title>
3776 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
3777 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
3778 <indexterm><primary>IS NULL</></>
3780 <primary><varname>transform_null_equals</> configuration parameter</primary>
3784 When on, expressions of the form <literal><replaceable>expr</> =
3785 NULL</literal> (or <literal>NULL =
3786 <replaceable>expr</></literal>) are treated as
3787 <literal><replaceable>expr</> IS NULL</literal>, that is, they
3788 return true if <replaceable>expr</> evaluates to the null value,
3789 and false otherwise. The correct SQL-spec-compliant behavior of
3790 <literal><replaceable>expr</> = NULL</literal> is to always
3791 return null (unknown). Therefore this parameter defaults to
3796 However, filtered forms in <productname>Microsoft
3797 Access</productname> generate queries that appear to use
3798 <literal><replaceable>expr</> = NULL</literal> to test for
3799 null values, so if you use that interface to access the database you
3800 might want to turn this option on. Since expressions of the
3801 form <literal><replaceable>expr</> = NULL</literal> always
3802 return the null value (using the correct interpretation) they are not
3803 very useful and do not appear often in normal applications, so
3804 this option does little harm in practice. But new users are
3805 frequently confused about the semantics of expressions
3806 involving null values, so this option is not on by default.
3810 Note that this option only affects the exact form <literal>= NULL</>,
3811 not other comparison operators or other expressions
3812 that are computationally equivalent to some expression
3813 involving the equals operator (such as <literal>IN</literal>).
3814 Thus, this option is not a general fix for bad programming.
3818 Refer to <xref linkend="functions-comparison"> for related information.
3827 <sect1 id="runtime-config-preset">
3828 <title>Preset Options</title>
3831 The following <quote>parameters</> are read-only, and are determined
3832 when <productname>PostgreSQL</productname> is compiled or when it is
3833 installed. As such, they have been excluded from the sample
3834 <filename>postgresql.conf</> file. These options report
3835 various aspects of <productname>PostgreSQL</productname> behavior
3836 that may be of interest to certain applications, particularly
3837 administrative front-ends.
3842 <varlistentry id="guc-block-size" xreflabel="block_size">
3843 <term><varname>block_size</varname> (<type>integer</type>)</term>
3845 <primary><varname>block_size</> configuration parameter</primary>
3849 Reports the size of a disk block. It is determined by the value
3850 of <literal>BLCKSZ</> when building the server. The default
3851 value is 8192 bytes. The meaning of some configuration
3852 variables (such as <xref linkend="guc-shared-buffers">) is
3853 influenced by <varname>block_size</varname>. See <xref
3854 linkend="runtime-config-resource"> for information.
3859 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
3860 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
3862 <primary><varname>integer_datetimes</> configuration parameter</primary>
3866 Reports whether <productname>PostgreSQL</productname> was built
3867 with support for 64-bit-integer dates and times. It is set by
3868 configuring with <literal>--enable-integer-datetimes</literal>
3869 when building <productname>PostgreSQL</productname>. The
3870 default value is <literal>off</literal>.
3875 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
3876 <term><varname>lc_collate</varname> (<type>string</type>)</term>
3878 <primary><varname>lc_collate</> configuration parameter</primary>
3882 Reports the locale in which sorting of textual data is done.
3883 See <xref linkend="locale"> for more information.
3884 The value is determined when the database cluster is initialized.
3889 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
3890 <term><varname>lc_ctype</varname> (<type>string</type>)</term>
3892 <primary><varname>lc_ctype</> configuration parameter</primary>
3896 Reports the locale that determines character classifications.
3897 See <xref linkend="locale"> for more information.
3898 The value is determined when the database cluster is initialized.
3899 Ordinarily this will be the same as <varname>lc_collate</varname>,
3900 but for special applications it might be set differently.
3905 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
3906 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
3908 <primary><varname>max_function_args</> configuration parameter</primary>
3912 Reports the maximum number of function arguments. It is determined by
3913 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
3914 default value is 100.
3919 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
3920 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
3922 <primary><varname>max_identifier_length</> configuration parameter</primary>
3926 Reports the maximum identifier length. It is determined as one
3927 less than the value of <literal>NAMEDATALEN</> when building
3928 the server. The default value of <literal>NAMEDATALEN</> is
3929 64; therefore the default
3930 <varname>max_identifier_length</varname> is 63.
3935 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
3936 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
3938 <primary><varname>max_index_keys</> configuration parameter</primary>
3942 Reports the maximum number of index keys. It is determined by
3943 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
3944 default value is 32.
3949 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
3950 <term><varname>server_encoding</varname> (<type>string</type>)</term>
3952 <primary><varname>server_encoding</> configuration parameter</primary>
3954 <indexterm><primary>character set</></>
3957 Reports the database encoding (character set).
3958 It is determined when the database is created. Ordinarily,
3959 clients need only be concerned with the value of <xref
3960 linkend="guc-client-encoding">.
3965 <varlistentry id="guc-server-version" xreflabel="server_version">
3966 <term><varname>server_version</varname> (<type>string</type>)</term>
3968 <primary><varname>server_version</> configuration parameter</primary>
3972 Reports the version number of the server. It is determined by the
3973 value of <literal>PG_VERSION</> when building the server.
3978 <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
3979 <term><varname>standard_conforming_strings</varname> (<type>boolean</type>)</term>
3980 <indexterm><primary>strings</><secondary>escape</></>
3982 <primary><varname>standard_conforming_strings</> configuration parameter</primary>
3986 Reports whether ordinary string literals
3987 (<literal>'...'</>) treat backslashes literally, as specified in
3988 the SQL standard. The value is currently always <literal>off</>,
3989 indicating that backslashes are treated as escapes. It is planned
3990 that this will change to <literal>on</> in a future
3991 <productname>PostgreSQL</productname> release when string literal
3992 syntax changes to meet the standard. Applications may check this
3993 parameter to determine how string literals will be processed.
3994 The presence of this parameter can also be taken as an indication
3995 that the escape string syntax (<literal>E'...'</>) is supported.
4003 <sect1 id="runtime-config-custom">
4004 <title>Customized Options</title>
4007 This feature was designed to allow parameters not normally known to
4008 <productname>PostgreSQL</productname> to be added by add-on modules
4009 (such as procedural languages). This allows add-on modules to be
4010 configured in the standard ways.
4015 <varlistentry id="guc-custom-variable-classes" xreflabel="custom_variable_classes">
4016 <term><varname>custom_variable_classes</varname> (<type>string</type>)</term>
4018 <primary><varname>custom_variable_classes</> configuration parameter</primary>
4022 This variable specifies one or several class names to be used for
4023 custom variables, in the form of a comma-separated list. A custom
4024 variable is a variable not normally known
4025 to <productname>PostgreSQL</productname> proper but used by some
4026 add-on module. Such variables must have names consisting of a class
4027 name, a dot, and a variable name. <varname>custom_variable_classes</>
4028 specifies all the class names in use in a particular installation.
4029 This parameter can only be set in the <filename>postgresql.conf</>
4030 file or on the server command line.
4038 The difficulty with setting custom variables in
4039 <filename>postgresql.conf</> is that the file must be read before add-on
4040 modules have been loaded, and so custom variables would ordinarily be
4041 rejected as unknown. When <varname>custom_variable_classes</> is set,
4042 the server will accept definitions of arbitrary variables within each
4043 specified class. These variables will be treated as placeholders and
4044 will have no function until the module that defines them is loaded. When a
4045 module for a specific class is loaded, it will add the proper variable
4046 definitions for its class name, convert any placeholder
4047 values according to those definitions, and issue warnings for any
4048 placeholders of its class that remain (which presumably would be
4049 misspelled configuration variables).
4053 Here is an example of what <filename>postgresql.conf</> might contain
4054 when using custom variables:
4057 custom_variable_classes = 'plr,plperl'
4058 plr.path = '/usr/lib/R'
4059 plperl.use_strict = true
4060 plruby.use_strict = true # generates error: unknown class name
4065 <sect1 id="runtime-config-developer">
4066 <title>Developer Options</title>
4069 The following parameters are intended for work on the
4070 <productname>PostgreSQL</productname> source, and in some cases
4071 to assist with recovery of severely damaged databases. There
4072 should be no reason to use them in a production database setup.
4073 As such, they have been excluded from the sample
4074 <filename>postgresql.conf</> file. Note that many of these
4075 parameters require special source compilation flags to work at all.
4079 <varlistentry id="guc-allow-system-table-mods" xreflabel="allow_system_table_mods">
4080 <term><varname>allow_system_table_mods</varname> (<type>boolean</type>)</term>
4082 <primary><varname>allow_system_table_mods</varname> configuration parameter</primary>
4086 Allows modification of the structure of system tables.
4087 This is used by <command>initdb</command>.
4088 This parameter can only be set at server start.
4093 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
4094 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
4096 <primary><varname>debug_assertions</> configuration parameter</primary>
4100 Turns on various assertion checks. This is a debugging aid. If
4101 you are experiencing strange problems or crashes you might want
4102 to turn this on, as it might expose programming mistakes. To use
4103 this parameter, the macro <symbol>USE_ASSERT_CHECKING</symbol>
4104 must be defined when <productname>PostgreSQL</productname> is
4105 built (accomplished by the <command>configure</command> option
4106 <option>--enable-cassert</option>). Note that
4107 <varname>debug_assertions</varname> defaults to <literal>on</>
4108 if <productname>PostgreSQL</productname> has been built with
4114 <varlistentry id="guc-ignore-system-indexes" xreflabel="ignore_system_indexes">
4115 <term><varname>ignore_system_indexes</varname> (<type>boolean</type>)</term>
4117 <primary><varname>ignore_system_indexes</varname> configuration parameter</primary>
4121 Ignore system indexes when reading system tables (but still
4122 update the indexes when modifying the tables). This is useful
4123 when recovering from damaged system indexes.
4124 This parameter cannot be changed after session start.
4129 <varlistentry id="guc-post-auth-delay" xreflabel="post_auth_delay">
4130 <term><varname>post_auth_delay</varname> (<type>integer</type>)</term>
4132 <primary><varname>post_auth_delay</> configuration parameter</primary>
4136 If nonzero, a delay of this many seconds occurs when a new
4137 server process is started, after it conducts the
4138 authentication procedure. This is intended to give an
4139 opportunity to attach to the server process with a debugger.
4140 This parameter cannot be changed after session start.
4145 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
4146 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
4148 <primary><varname>pre_auth_delay</> configuration parameter</primary>
4152 If nonzero, a delay of this many seconds occurs just after a
4153 new server process is forked, before it conducts the
4154 authentication procedure. This is intended to give an
4155 opportunity to attach to the server process with a debugger to
4156 trace down misbehavior in authentication.
4157 This parameter can only be set in the <filename>postgresql.conf</>
4158 file or on the server command line.
4163 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
4164 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
4166 <primary><varname>trace_notify</> configuration parameter</primary>
4170 Generates a great amount of debugging output for the
4171 <command>LISTEN</command> and <command>NOTIFY</command>
4172 commands. <xref linkend="guc-client-min-messages"> or
4173 <xref linkend="guc-log-min-messages"> must be
4174 <literal>DEBUG1</literal> or lower to send this output to the
4175 client or server log, respectively.
4180 <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
4181 <term><varname>trace_sort</varname> (<type>boolean</type>)</term>
4183 <primary><varname>trace_sort</> configuration parameter</primary>
4187 If on, emit information about resource usage during sort operations.
4188 This parameter is only available if the <symbol>TRACE_SORT</symbol> macro
4189 was defined when <productname>PostgreSQL</productname> was compiled.
4190 (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
4196 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
4197 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
4198 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
4199 <term><varname>trace_lock_oidmin</varname> (<type>boolean</type>)</term>
4200 <term><varname>trace_lock_table</varname> (<type>boolean</type>)</term>
4201 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
4202 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
4205 Various other code tracing and debugging options.
4210 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
4211 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
4213 <primary><varname>wal_debug</> configuration parameter</primary>
4217 If on, emit WAL-related debugging output. This parameter is
4218 only available if the <symbol>WAL_DEBUG</symbol> macro was
4219 defined when <productname>PostgreSQL</productname> was
4225 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
4226 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
4228 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
4232 Detection of a damaged page header normally causes
4233 <productname>PostgreSQL</> to report an error, aborting the current
4234 command. Setting <varname>zero_damaged_pages</> to on causes
4235 the system to instead report a warning, zero out the damaged page,
4236 and continue processing. This behavior <emphasis>will destroy data</>,
4237 namely all the rows on the damaged page. But it allows you to get
4238 past the error and retrieve rows from any undamaged pages that may
4239 be present in the table. So it is useful for recovering data if
4240 corruption has occurred due to hardware or software error. You should
4241 generally not set this on until you have given up hope of recovering
4242 data from the damaged page(s) of a table. The
4243 default setting is <literal>off</>, and it can only be changed
4250 <sect1 id="runtime-config-short">
4251 <title>Short Options</title>
4254 For convenience there are also single letter command-line option
4255 switches available for some parameters. They are described in
4256 <xref linkend="runtime-config-short-table">. Some of these
4257 options exist for historical reasons, and their presence as a
4258 single-letter option does not necessarily indicate an endorsement
4259 to use the option heavily.
4262 <table id="runtime-config-short-table">
4263 <title>Short option key</title>
4267 <entry>Short option</entry>
4268 <entry>Equivalent</entry>
4274 <entry><option>-A <replaceable>x</replaceable></option></entry>
4275 <entry><literal>debug_assertions = <replaceable>x</replaceable></></entry>
4278 <entry><option>-B <replaceable>x</replaceable></option></entry>
4279 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
4282 <entry><option>-d <replaceable>x</replaceable></option></entry>
4283 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
4286 <entry><option>-e</option></entry>
4287 <entry><literal>datestyle = euro</></entry>
4291 <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
4292 <option>-fm</option>, <option>-fn</option>,
4293 <option>-fs</option>, <option>-ft</option>
4296 <literal>enable_bitmapscan = off</>,
4297 <literal>enable_hashjoin = off</>,
4298 <literal>enable_indexscan = off</>,
4299 <literal>enable_mergejoin = off</>,
4300 <literal>enable_nestloop = off</>,
4301 <literal>enable_seqscan = off</>,
4302 <literal>enable_tidscan = off</>
4306 <entry><option>-F</option></entry>
4307 <entry><literal>fsync = off</></entry>
4310 <entry><option>-h <replaceable>x</replaceable></option></entry>
4311 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
4314 <entry><option>-i</option></entry>
4315 <entry><literal>listen_addresses = '*'</></entry>
4318 <entry><option>-k <replaceable>x</replaceable></option></entry>
4319 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
4322 <entry><option>-l</option></entry>
4323 <entry><literal>ssl = on</></entry>
4326 <entry><option>-N <replaceable>x</replaceable></option></entry>
4327 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
4330 <entry><option>-O</option></entry>
4331 <entry><literal>allow_system_table_mods = on</></entry>
4334 <entry><option>-p <replaceable>x</replaceable></option></entry>
4335 <entry><literal>port = <replaceable>x</replaceable></></entry>
4338 <entry><option>-P</option></entry>
4339 <entry><literal>ignore_system_indexes = on</></entry>
4342 <entry><option>-s</option></entry>
4343 <entry><literal>log_statement_stats = on</></entry>
4346 <entry><option>-S <replaceable>x</replaceable></option></entry>
4347 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
4350 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option></entry>
4351 <entry><literal>log_parser_stats = on</>,
4352 <literal>log_planner_stats = on</>,
4353 <literal>log_executor_stats = on</></entry>
4356 <entry><option>-W <replaceable>x</replaceable></option></entry>
4357 <entry><literal>post_auth_delay = <replaceable>x</replaceable></></entry>
4366 <!-- Keep this comment at the end of the file
4371 sgml-minimize-attributes:nil
4372 sgml-always-quote-attributes:t
4375 sgml-parent-document:nil
4376 sgml-default-dtd-file:"./reference.ced"
4377 sgml-exposed-tags:nil
4378 sgml-local-catalogs:("/usr/lib/sgml/catalog")
4379 sgml-local-ecat-files:nil