2 $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.40 2005/12/23 00:38:03 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 chapters. 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 option 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 options.
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 reasonably be changed without restarting
119 <productname>PostgreSQL</productname>. 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 options 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 option 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 option 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 option 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 option 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 option can only be set at server start.
230 In a default installation, none of the above options are set explicitly.
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</> option 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 options <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 options 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 options, 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 option
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 uses the default
393 group for the current user. This option 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 option 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 option 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. By default, the computer name is used, specified as an
445 empty string ''. This option is ignored if the server was not
446 compiled with <productname>Bonjour</productname> support. This
447 option can only be set at server start.
452 <varlistentry id="guc-tcp-keepalives-idle" xreflabel="tcp_keepalives_idle">
453 <term><varname>tcp_keepalives_idle</varname> (<type>integer</type>)</term>
455 <primary><varname>tcp_keepalives_idle</> configuration parameter</primary>
459 On systems that support the <symbol>TCP_KEEPIDLE</symbol> socket option, specifies the
460 number of seconds between sending keepalives on an otherwise idle
461 connection. A value of 0 uses the system default. If <symbol>TCP_KEEPIDLE</symbol> is
462 not supported, this parameter must be 0. This option is ignored for
463 connections made via a Unix-domain socket.
468 <varlistentry id="guc-tcp-keepalives-interval" xreflabel="tcp_keepalives_interval">
469 <term><varname>tcp_keepalives_interval</varname> (<type>integer</type>)</term>
471 <primary><varname>tcp_keepalives_interval</> configuration parameter</primary>
475 On systems that support the <symbol>TCP_KEEPINTVL</symbol> socket option, specifies how
476 long, in seconds, to wait for a response to a keepalive before
477 retransmitting. A value of 0 uses the system default. If <symbol>TCP_KEEPINTVL</symbol>
478 is not supported, this parameter must be 0. This option is ignored
479 for connections made via a Unix-domain socket.
484 <varlistentry id="guc-tcp-keepalives-count" xreflabel="tcp_keepalives_count">
485 <term><varname>tcp_keepalives_count</varname> (<type>integer</type>)</term>
487 <primary><varname>tcp_keepalives_count</> configuration parameter</primary>
491 On systems that support the <symbol>TCP_KEEPCNT</symbol> socket option, specifies how
492 many keepalives may be lost before the connection is considered dead.
493 A value of 0 uses the system default. If <symbol>TCP_KEEPCNT</symbol> is not
494 supported, this parameter must be 0. This option is ignored
495 for connections made via a Unix-domain socket.
502 <sect2 id="runtime-config-connection-security">
503 <title>Security and Authentication</title>
506 <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
507 <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
508 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
509 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
511 <primary><varname>authentication_timeout</> configuration parameter</primary>
516 Maximum time to complete client authentication, in seconds. If a
517 would-be client has not completed the authentication protocol in
518 this much time, the server breaks the connection. This prevents
519 hung clients from occupying a connection indefinitely. This
520 option can only be set at server start or in the
521 <filename>postgresql.conf</filename> file. The default is 60.
526 <varlistentry id="guc-ssl" xreflabel="ssl">
527 <term><varname>ssl</varname> (<type>boolean</type>)</term>
529 <primary><varname>ssl</> configuration parameter</primary>
533 Enables <acronym>SSL</> connections. Please read
534 <xref linkend="ssl-tcp"> before using this. The default
535 is <literal>off</>. This parameter can only be set at server
541 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
542 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
544 <primary><varname>password_encryption</> configuration parameter</primary>
548 When a password is specified in <xref
549 linkend="sql-createuser" endterm="sql-createuser-title"> or
550 <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
551 without writing either <literal>ENCRYPTED</> or
552 <literal>UNENCRYPTED</>, this option determines whether the
553 password is to be encrypted. The default is <literal>on</>
554 (encrypt the password).
559 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
560 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
562 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
566 Sets the location of the Kerberos server key file. See
567 <xref linkend="kerberos-auth"> for details. This parameter
568 can only be set at server start.
573 <varlistentry id="guc-krb-srvname" xreflabel="krb_srvname">
574 <term><varname>krb_srvname</varname> (<type>string</type>)</term>
576 <primary><varname>krb_srvname</> configuration parameter</primary>
580 Sets the Kerberos service name. See <xref linkend="kerberos-auth">
581 for details. This parameter can only be set at server start.
586 <varlistentry id="guc-krb-server-hostname" xreflabel="krb_server_hostname">
587 <term><varname>krb_server_hostname</varname> (<type>string</type>)</term>
589 <primary><varname>krb_server_hostname</> configuration parameter</primary>
593 Sets the host name part of the service principal.
594 This, combined with <varname>krb_srvname</>, is used to generate
595 the complete service principal, that is
596 <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 enables per-database user names. It is off by default.
630 If this is on, you should create users as <literal>username@dbname</>.
631 When <literal>username</> is passed by a connecting client,
632 <literal>@</> and the database name are appended to the user
633 name and that database-specific user name is looked up by the
634 server. Note that when you create users with names containing
635 <literal>@</> within the SQL environment, you will need to
640 With this option enabled, you can still create ordinary global
641 users. Simply append <literal>@</> when specifying the user
642 name in the client. The <literal>@</> will be stripped off
643 before the user name is looked up by the server.
648 This feature is intended as a temporary measure until a
649 complete solution is found. At that time, this option will
660 <sect1 id="runtime-config-resource">
661 <title>Resource Consumption</title>
663 <sect2 id="runtime-config-resource-memory">
664 <title>Memory</title>
667 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
668 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
670 <primary><varname>shared_buffers</> configuration parameter</primary>
674 Sets the number of shared memory buffers used by the database
675 server. The default is typically 1000, but may be less if your
676 kernel settings will not support it (as determined during
677 <application>initdb</>). Each buffer is 8192 bytes, unless a
678 different value of <symbol>BLCKSZ</symbol> was chosen when building
679 the server. This setting must be at least 16, as well as at
680 least twice the value of <xref linkend="guc-max-connections">;
681 however, settings significantly higher than the minimum are
682 usually needed for good performance. Values of a few thousand
683 are recommended for production installations. This option can
684 only be set at server start.
688 Increasing this parameter may cause <productname>PostgreSQL</>
689 to request more <systemitem class="osname">System V</> shared
690 memory than your operating system's default configuration
691 allows. See <xref linkend="sysvipc"> for information on how to
692 adjust those parameters, if necessary.
697 <varlistentry id="guc-temp-buffers" xreflabel="temp_buffers">
698 <term><varname>temp_buffers</varname> (<type>integer</type>)</term>
700 <primary><varname>temp_buffers</> configuration parameter</primary>
704 Sets the maximum number of temporary buffers used by each database
705 session. These are session-local buffers used only for access
706 to temporary tables. The default is 1000. The setting can
707 be changed within individual sessions, but only up until the
708 first use of temporary tables within a session; subsequent
709 attempts to change the value will have no effect on that session.
713 A session will allocate temporary buffers as needed up to the limit
714 given by <varname>temp_buffers</>. The cost of setting a large
715 value in sessions that do not actually need a lot of temporary
716 buffers is only a buffer descriptor, or about 64 bytes, per
717 increment in <varname>temp_buffers</>. However if a buffer is
718 actually used an additional 8192 bytes will be consumed for it
719 (or in general, <symbol>BLCKSZ</symbol> bytes).
724 <varlistentry id="guc-max-prepared-transactions" xreflabel="max_prepared_transactions">
725 <term><varname>max_prepared_transactions</varname> (<type>integer</type>)</term>
727 <primary><varname>max_prepared_transactions</> configuration parameter</primary>
731 Sets the maximum number of transactions that can be in the
732 <quote>prepared</> state simultaneously (see <xref
733 linkend="sql-prepare-transaction"
734 endterm="sql-prepare-transaction-title">).
735 Setting this parameter to zero disables the prepared-transaction
738 This option can only be set at server start.
742 If you are not using prepared transactions, this parameter may as
743 well be set to zero. If you are using them, you will probably
744 want <varname>max_prepared_transactions</varname> to be at least
745 as large as <xref linkend="guc-max-connections">, to avoid unwanted
746 failures at the prepare step.
750 Increasing this parameter may cause <productname>PostgreSQL</>
751 to request more <systemitem class="osname">System V</> shared
752 memory than your operating system's default configuration
753 allows. See <xref linkend="sysvipc"> for information on how to
754 adjust those parameters, if necessary.
759 <varlistentry id="guc-work-mem" xreflabel="work_mem">
760 <term><varname>work_mem</varname> (<type>integer</type>)</term>
762 <primary><varname>work_mem</> configuration parameter</primary>
766 Specifies the amount of memory to be used by internal sort operations
767 and hash tables before switching to temporary disk files. The value is
768 specified in kilobytes, and defaults to 1024 kilobytes (1 MB).
769 Note that for a complex query, several sort or hash operations might be
770 running in parallel; each one will be allowed to use as much memory
771 as this value specifies before it starts to put data into temporary
772 files. Also, several running sessions could be doing such operations
773 concurrently. So the total memory used could be many
774 times the value of <varname>work_mem</varname>; it is necessary to
775 keep this fact in mind when choosing the value. Sort operations are
776 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
778 Hash tables are used in hash joins, hash-based aggregation, and
779 hash-based processing of <literal>IN</> subqueries.
784 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
785 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
787 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
791 Specifies the maximum amount of memory to be used in maintenance
792 operations, such as <command>VACUUM</command>, <command>CREATE
793 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>.
794 The value is specified in kilobytes, and defaults to 16384 kilobytes
795 (16 MB). Since only one of these operations can be executed at
796 a time by a database session, and an installation normally doesn't
797 have very many of them happening concurrently, it's safe to set this
798 value significantly larger than <varname>work_mem</varname>. Larger
799 settings may improve performance for vacuuming and for restoring
805 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
806 <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
808 <primary><varname>max_stack_depth</> configuration parameter</primary>
812 Specifies the maximum safe depth of the server's execution stack.
813 The ideal setting for this parameter is the actual stack size limit
814 enforced by the kernel (as set by <literal>ulimit -s</> or local
815 equivalent), less a safety margin of a megabyte or so. The safety
816 margin is needed because the stack depth is not checked in every
817 routine in the server, but only in key potentially-recursive routines
818 such as expression evaluation. Setting the parameter higher than
819 the actual kernel limit will mean that a runaway recursive function
820 can crash an individual backend process. The default setting is
821 2048 KB (two megabytes), which is conservatively small and unlikely
822 to risk crashes. However, it may be too small to allow execution
823 of complex functions.
830 <sect2 id="runtime-config-resource-fsm">
831 <title>Free Space Map</title>
834 <primary>free space map</primary>
838 These parameters control the size of the shared <firstterm>free space
839 map</>, which tracks the locations of unused space in the database.
840 An undersized free space map may cause the database to consume
841 increasing amounts of disk space over time, because free space that
842 is not in the map cannot be re-used; instead <productname>PostgreSQL</>
843 will request more disk space from the operating system when it needs
845 The last few lines displayed by a database-wide <command>VACUUM VERBOSE</>
846 command can help in determining if the current settings are adequate.
847 A <literal>NOTICE</> message is also printed during such an operation
848 if the current settings are too low.
852 Increasing these parameters may cause <productname>PostgreSQL</>
853 to request more <systemitem class="osname">System V</> shared
854 memory than your operating system's default configuration
855 allows. See <xref linkend="sysvipc"> for information on how to
856 adjust those parameters, if necessary.
860 <varlistentry id="guc-max-fsm-pages" xreflabel="max_fsm_pages">
861 <term><varname>max_fsm_pages</varname> (<type>integer</type>)</term>
863 <primary><varname>max_fsm_pages</> configuration parameter</primary>
867 Sets the maximum number of disk pages for which free space will
868 be tracked in the shared free-space map. Six bytes of shared memory
869 are consumed for each page slot. This setting must be more than
870 16 * <varname>max_fsm_relations</varname>. The default is 20000.
871 This option can only be set at server start.
876 <varlistentry id="guc-max-fsm-relations" xreflabel="max_fsm_relations">
877 <term><varname>max_fsm_relations</varname> (<type>integer</type>)</term>
879 <primary><varname>max_fsm_relations</> configuration parameter</primary>
883 Sets the maximum number of relations (tables and indexes) for which
884 free space will be tracked in the shared free-space map. Roughly
885 seventy bytes of shared memory are consumed for each slot.
887 This option can only be set at server start.
894 <sect2 id="runtime-config-resource-kernel">
895 <title>Kernel Resource Usage</title>
898 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
899 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
901 <primary><varname>max_files_per_process</> configuration parameter</primary>
905 Sets the maximum number of simultaneously open files allowed to each
906 server subprocess. The default is 1000. If the kernel is enforcing
907 a safe per-process limit, you don't need to worry about this setting.
908 But on some platforms (notably, most BSD systems), the kernel will
909 allow individual processes to open many more files than the system
910 can really support when a large number of processes all try to open
911 that many files. If you find yourself seeing <quote>Too many open
912 files</> failures, try reducing this setting.
913 This option can only be set at server start.
918 <varlistentry id="guc-preload-libraries" xreflabel="preload_libraries">
919 <term><varname>preload_libraries</varname> (<type>string</type>)</term>
921 <primary><varname>preload_libraries</> configuration parameter</primary>
925 This variable specifies one or more shared libraries that are
926 to be preloaded at server start. A parameterless
927 initialization function can optionally be called for each
928 library. To specify that, add a colon and the name of the
929 initialization function after the library name. For example
930 <literal>'$libdir/mylib:mylib_init'</literal> would cause
931 <literal>mylib</> to be preloaded and <literal>mylib_init</>
932 to be executed. If more than one library is to be loaded,
933 separate their names with commas.
937 If a specified library or initialization function is not found,
938 the server will fail to start.
942 <productname>PostgreSQL</productname> procedural language
943 libraries may be preloaded in this way, typically by using the
944 syntax <literal>'$libdir/plXXX:plXXX_init'</literal> where
945 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
946 <literal>tcl</>, or <literal>python</>.
950 By preloading a shared library (and initializing it if
951 applicable), the library startup time is avoided when the
952 library is first used. However, the time to start each new
953 server process may increase slightly, even if that process never
954 uses the library. So this option is recommended only for
955 libraries that will be used in most sessions.
963 <sect2 id="runtime-config-resource-vacuum-cost">
964 <title id="runtime-config-resource-vacuum-cost-title">
965 Cost-Based Vacuum Delay
969 During the execution of <xref linkend="sql-vacuum"
970 endterm="sql-vacuum-title"> and <xref linkend="sql-analyze"
971 endterm="sql-analyze-title"> commands, the system maintains an
972 internal counter that keeps track of the estimated cost of the
973 various I/O operations that are performed. When the accumulated
974 cost reaches a limit (specified by
975 <varname>vacuum_cost_limit</varname>), the process performing
976 the operation will sleep for a while (specified by
977 <varname>vacuum_cost_delay</varname>). Then it will reset the
978 counter and continue execution.
982 The intent of this feature is to allow administrators to reduce
983 the I/O impact of these commands on concurrent database
984 activity. There are many situations in which it is not very
985 important that maintenance commands like
986 <command>VACUUM</command> and <command>ANALYZE</command> finish
987 quickly; however, it is usually very important that these
988 commands do not significantly interfere with the ability of the
989 system to perform other database operations. Cost-based vacuum
990 delay provides a way for administrators to achieve this.
994 This feature is disabled by default. To enable it, set the
995 <varname>vacuum_cost_delay</varname> variable to a nonzero
1000 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1001 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)</term>
1003 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1007 The length of time, in milliseconds, that the process will sleep
1008 when the cost limit has been exceeded.
1009 The default value is 0, which disables the cost-based vacuum
1010 delay feature. Positive values enable cost-based vacuuming.
1011 Note that on many systems, the effective resolution
1012 of sleep delays is 10 milliseconds; setting
1013 <varname>vacuum_cost_delay</varname> to a value that is
1014 not a multiple of 10 may have the same results as setting it
1015 to the next higher multiple of 10.
1020 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1021 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
1023 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1027 The estimated cost for vacuuming a buffer found in the shared buffer
1028 cache. It represents the cost to lock the buffer pool, lookup
1029 the shared hash table and scan the content of the page. The
1035 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1036 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
1038 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1042 The estimated cost for vacuuming a buffer that has to be read from
1043 disk. This represents the effort to lock the buffer pool,
1044 lookup the shared hash table, read the desired block in from
1045 the disk and scan its content. The default value is 10.
1050 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1051 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
1053 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1057 The estimated cost charged when vacuum modifies a block that was
1058 previously clean. It represents the extra I/O required to
1059 flush the dirty block out to disk again. The default value is
1065 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1066 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
1068 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1072 The accumulated cost that will cause the vacuuming process to sleep.
1073 The default value is 200.
1081 There are certain operations that hold critical locks and should
1082 therefore complete as quickly as possible. Cost-based vacuum
1083 delays do not occur during such operations. Therefore it is
1084 possible that the cost accumulates far higher than the specified
1085 limit. To avoid uselessly long delays in such cases, the actual
1086 delay is calculated as <varname>vacuum_cost_delay</varname> *
1087 <varname>accumulated_balance</varname> /
1088 <varname>vacuum_cost_limit</varname> with a maximum of
1089 <varname>vacuum_cost_delay</varname> * 4.
1094 <sect2 id="runtime-config-resource-background-writer">
1095 <title>Background Writer</title>
1098 Beginning in <productname>PostgreSQL</> 8.0, there is a separate server
1099 process called the <firstterm>background writer</>, whose sole function
1100 is to issue writes of <quote>dirty</> shared buffers. The intent is
1101 that server processes handling user queries should seldom or never have
1102 to wait for a write to occur, because the background writer will do it.
1103 This arrangement also reduces the performance penalty associated with
1104 checkpoints. The background writer will continuously trickle out dirty
1105 pages to disk, so that only a few pages will need to be forced out when
1106 checkpoint time arrives, instead of the storm of dirty-buffer writes that
1107 formerly occurred at each checkpoint. However there is a net overall
1108 increase in I/O load, because where a repeatedly-dirtied page might
1109 before have been written only once per checkpoint interval, the
1110 background writer might write it several times in the same interval.
1111 In most situations a continuous low load is preferable to periodic
1112 spikes, but the parameters discussed in this subsection can be used to tune
1113 the behavior for local needs.
1117 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1118 <term><varname>bgwriter_delay</varname> (<type>integer</type>)</term>
1120 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1124 Specifies the delay between activity rounds for the
1125 background writer. In each round the writer issues writes
1126 for some number of dirty buffers (controllable by the
1127 following parameters). It then sleeps for <varname>bgwriter_delay</>
1128 milliseconds, and repeats. The default value is 200. Note
1129 that on many systems, the effective resolution of sleep
1130 delays is 10 milliseconds; setting <varname>bgwriter_delay</>
1131 to a value that is not a multiple of 10 may have the same
1132 results as setting it to the next higher multiple of 10.
1133 This option can only be set at server start or in the
1134 <filename>postgresql.conf</filename> file.
1139 <varlistentry id="guc-bgwriter-lru-percent" xreflabel="bgwriter_lru_percent">
1140 <term><varname>bgwriter_lru_percent</varname> (<type>floating point</type>)</term>
1142 <primary><varname>bgwriter_lru_percent</> configuration parameter</primary>
1146 To reduce the probability that server processes will need to issue
1147 their own writes, the background writer tries to write buffers that
1148 are likely to be recycled soon. In each round, it examines up to
1149 <varname>bgwriter_lru_percent</> of the buffers that are nearest to
1150 being recycled, and writes any that are dirty.
1151 The default value is 1.0 (this is a percentage of the total number
1153 This option can only be set at server start or in the
1154 <filename>postgresql.conf</filename> file.
1159 <varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1160 <term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)</term>
1162 <primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1166 In each round, no more than this many buffers will be written
1167 as a result of scanning soon-to-be-recycled buffers.
1168 The default value is 5.
1169 This option can only be set at server start or in the
1170 <filename>postgresql.conf</filename> file.
1175 <varlistentry id="guc-bgwriter-all-percent" xreflabel="bgwriter_all_percent">
1176 <term><varname>bgwriter_all_percent</varname> (<type>floating point</type>)</term>
1178 <primary><varname>bgwriter_all_percent</> configuration parameter</primary>
1182 To reduce the amount of work that will be needed at checkpoint time,
1183 the background writer also does a circular scan through the entire
1184 buffer pool, writing buffers that are found to be dirty.
1185 In each round, it examines up to
1186 <varname>bgwriter_all_percent</> of the buffers for this purpose.
1187 The default value is 0.333 (this is a percentage of the total number
1188 of shared buffers). With the default <varname>bgwriter_delay</>
1189 setting, this will allow the entire shared buffer pool to be scanned
1190 about once per minute.
1191 This option can only be set at server start or in the
1192 <filename>postgresql.conf</filename> file.
1197 <varlistentry id="guc-bgwriter-all-maxpages" xreflabel="bgwriter_all_maxpages">
1198 <term><varname>bgwriter_all_maxpages</varname> (<type>integer</type>)</term>
1200 <primary><varname>bgwriter_all_maxpages</> configuration parameter</primary>
1204 In each round, no more than this many buffers will be written
1205 as a result of the scan of the entire buffer pool. (If this
1206 limit is reached, the scan stops, and resumes at the next buffer
1207 during the next round.)
1208 The default value is 5.
1209 This option can only be set at server start or in the
1210 <filename>postgresql.conf</filename> file.
1217 Smaller values of <varname>bgwriter_all_percent</varname> and
1218 <varname>bgwriter_all_maxpages</varname> reduce the extra I/O load
1219 caused by the background writer, but leave more work to be done
1220 at checkpoint time. To reduce load spikes at checkpoints,
1221 increase these two values.
1222 Similarly, smaller values of <varname>bgwriter_lru_percent</varname> and
1223 <varname>bgwriter_lru_maxpages</varname> reduce the extra I/O load
1224 caused by the background writer, but make it more likely that server
1225 processes will have to issue writes for themselves, delaying interactive
1227 To disable background writing entirely,
1228 set both <varname>maxpages</varname> values and/or both
1229 <varname>percent</varname> values to zero.
1234 <sect1 id="runtime-config-wal">
1235 <title>Write Ahead Log</title>
1238 See also <xref linkend="wal-configuration"> for details on WAL
1242 <sect2 id="runtime-config-wal-settings">
1243 <title>Settings</title>
1246 <varlistentry id="guc-fsync" xreflabel="fsync">
1248 <primary><varname>fsync</> configuration parameter</primary>
1250 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1253 If this option is on, the <productname>PostgreSQL</> server
1254 will try to make sure that updates are physically written to
1255 disk, by issuing <function>fsync()</> system calls or various
1256 equivalent methods (see <xref linkend="guc-wal-sync-method">).
1257 This ensures that the database cluster can recover to a
1258 consistent state after an operating system or hardware crash.
1262 However, using <varname>fsync</varname> results in a
1263 performance penalty: when a transaction is committed,
1264 <productname>PostgreSQL</productname> must wait for the
1265 operating system to flush the write-ahead log to disk. When
1266 <varname>fsync</varname> is disabled, the operating system is
1267 allowed to do its best in buffering, ordering, and delaying
1268 writes. This can result in significantly improved performance.
1269 However, if the system crashes, the results of the last few
1270 committed transactions may be lost in part or whole. In the
1271 worst case, unrecoverable data corruption may occur.
1272 (Crashes of the database software itself are <emphasis>not</>
1273 a risk factor here. Only an operating-system-level crash
1274 creates a risk of corruption.)
1278 Due to the risks involved, there is no universally correct
1279 setting for <varname>fsync</varname>. Some administrators
1280 always disable <varname>fsync</varname>, while others only
1281 turn it off during initial bulk data loads, where there is a clear
1282 restart point if something goes wrong. Others
1283 always leave <varname>fsync</varname> enabled. The default is
1284 to enable <varname>fsync</varname>, for maximum reliability.
1285 If you trust your operating system, your hardware, and your
1286 utility company (or your battery backup), you can consider
1287 disabling <varname>fsync</varname>.
1291 This option can only be set at server start or in the
1292 <filename>postgresql.conf</filename> file. If you turn
1293 this option off, also consider turning off
1294 <xref linkend="guc-full-page-writes">.
1299 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1300 <term><varname>wal_sync_method</varname> (<type>string</type>)</term>
1302 <primary><varname>wal_sync_method</> configuration parameter</primary>
1306 Method used for forcing WAL updates out to disk.
1307 If <varname>fsync</varname> is off then this setting is irrelevant,
1308 since updates will not be forced out at all.
1309 Possible values are:
1314 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>)
1319 <literal>fdatasync</> (call <function>fdatasync()</> at each commit)
1324 <literal>fsync_writethrough</> (call <function>fsync()</> at each commit, forcing write-through of any disk write cache)
1329 <literal>fsync</> (call <function>fsync()</> at each commit)
1334 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>)
1339 Not all of these choices are available on all platforms.
1340 The default is the first method in the above list that is supported.
1341 This option can only be set at server start or in the
1342 <filename>postgresql.conf</filename> file.
1347 <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
1349 <primary><varname>full_page_writes</> configuration parameter</primary>
1351 <term><varname>full_page_writes</varname> (<type>boolean</type>)</term>
1354 When this option is on, the <productname>PostgreSQL</> server
1355 writes the entire content of each disk page to WAL during the
1356 first modification of that page after a checkpoint.
1357 This is needed because
1358 a page write that is in process during an operating system crash might
1359 be only partially completed, leading to an on-disk page
1360 that contains a mix of old and new data. The row-level change data
1361 normally stored in WAL will not be enough to completely restore
1362 such a page during post-crash recovery. Storing the full page image
1363 guarantees that the page can be correctly restored, but at a price
1364 in increasing the amount of data that must be written to WAL.
1365 (Because WAL replay always starts from a checkpoint, it is sufficient
1366 to do this during the first change of each page after a checkpoint.
1367 Therefore, one way to reduce the cost of full-page writes is to
1368 increase the checkpoint interval parameters.)
1372 Turning this option off speeds normal operation, but
1373 might lead to a corrupt database after an operating system crash
1374 or power failure. The risks are similar to turning off
1375 <varname>fsync</>, though smaller. It may be safe to turn off
1376 this option if you have hardware (such as a battery-backed disk
1377 controller) or filesystem software (e.g., Reiser4) that reduces
1378 the risk of partial page writes to an acceptably low level.
1382 Turning off this option does not affect use of
1383 WAL archiving for point-in-time recovery (PITR)
1384 (see <xref linkend="backup-online">).
1388 This option can only be set at server start or in the
1389 <filename>postgresql.conf</filename> file. The default is
1395 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1396 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1398 <primary><varname>wal_buffers</> configuration parameter</primary>
1402 Number of disk-page buffers allocated in shared memory for WAL data.
1403 The default is 8. The setting need only be large enough to hold
1404 the amount of WAL data generated by one typical transaction, since
1405 the data is written out to disk at every transaction commit.
1406 This option can only be set at server start.
1410 Increasing this parameter may cause <productname>PostgreSQL</>
1411 to request more <systemitem class="osname">System V</> shared
1412 memory than your operating system's default configuration
1413 allows. See <xref linkend="sysvipc"> for information on how to
1414 adjust those parameters, if necessary.
1419 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
1420 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1422 <primary><varname>commit_delay</> configuration parameter</primary>
1426 Time delay between writing a commit record to the WAL buffer
1427 and flushing the buffer out to disk, in microseconds. A
1428 nonzero delay can allow multiple transactions to be committed
1429 with only one <function>fsync()</function> system call, if
1430 system load is high enough that additional transactions become
1431 ready to commit within the given interval. But the delay is
1432 just wasted if no other transactions become ready to
1433 commit. Therefore, the delay is only performed if at least
1434 <varname>commit_siblings</varname> other transactions are
1435 active at the instant that a server process has written its
1436 commit record. The default is zero (no delay).
1441 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
1442 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1444 <primary><varname>commit_siblings</> configuration parameter</primary>
1448 Minimum number of concurrent open transactions to require
1449 before performing the <varname>commit_delay</> delay. A larger
1450 value makes it more probable that at least one other
1451 transaction will become ready to commit during the delay
1452 interval. The default is five.
1459 <sect2 id="runtime-config-wal-checkpoints">
1460 <title>Checkpoints</title>
1463 <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1464 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1466 <primary><varname>checkpoint_segments</> configuration parameter</primary>
1470 Maximum distance between automatic WAL checkpoints, in log
1471 file segments (each segment is normally 16 megabytes). The
1472 default is three. This option can only be set at server start
1473 or in the <filename>postgresql.conf</filename> file.
1478 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1479 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1481 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
1485 Maximum time between automatic WAL checkpoints, in
1486 seconds. The default is 300 seconds. This option can only be
1487 set at server start or in the <filename>postgresql.conf</>
1493 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1494 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1496 <primary><varname>checkpoint_warning</> configuration parameter</primary>
1500 Write a message to the server log if checkpoints caused by
1501 the filling of checkpoint segment files happen closer together
1502 than this many seconds (which suggests that
1503 <varname>checkpoint_segments</> ought to be raised). The default is
1504 30 seconds. Zero disables the warning.
1511 <sect2 id="runtime-config-wal-archiving">
1512 <title>Archiving</title>
1515 <varlistentry id="guc-archive-command" xreflabel="archive_command">
1516 <term><varname>archive_command</varname> (<type>string</type>)</term>
1518 <primary><varname>archive_command</> configuration parameter</primary>
1522 The shell command to execute to archive a completed segment of
1523 the WAL file series. If this is an empty string (the default),
1524 WAL archiving is disabled. Any <literal>%p</> in the string is
1525 replaced by the absolute path of the file to archive, and any
1526 <literal>%f</> is replaced by the file name only. Use
1527 <literal>%%</> to embed an actual <literal>%</> character in the
1528 command. For more information see <xref
1529 linkend="backup-archiving-wal">. This option can only be set at
1530 server start or in the <filename>postgresql.conf</filename>
1534 It is important for the command to return a zero exit status if
1535 and only if it succeeds. Examples:
1537 archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
1538 archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
1548 <sect1 id="runtime-config-query">
1549 <title>Query Planning</title>
1551 <sect2 id="runtime-config-query-enable">
1552 <title>Planner Method Configuration</title>
1555 These configuration parameters provide a crude method of
1556 influencing the query plans chosen by the query optimizer. If
1557 the default plan chosen by the optimizer for a particular query
1558 is not optimal, a temporary solution may be found by using one
1559 of these configuration parameters to force the optimizer to
1560 choose a different plan. Turning one of these settings off
1561 permanently is seldom a good idea, however.
1562 Better ways to improve the quality of the
1563 plans chosen by the optimizer include adjusting the <xref
1564 linkend="runtime-config-query-constants"
1565 endterm="runtime-config-query-constants-title">, running <xref
1566 linkend="sql-analyze" endterm="sql-analyze-title"> more
1567 frequently, increasing the value of the <xref
1568 linkend="guc-default-statistics-target"> configuration parameter,
1569 and increasing the amount of statistics collected for
1570 specific columns using <command>ALTER TABLE SET
1571 STATISTICS</command>.
1575 <varlistentry id="guc-enable-bitmapscan" xreflabel="enable_bitmapscan">
1576 <term><varname>enable_bitmapscan</varname> (<type>boolean</type>)</term>
1578 <primary>bitmap scan</primary>
1581 <primary><varname>enable_bitmapscan</> configuration parameter</primary>
1585 Enables or disables the query planner's use of bitmap-scan plan
1586 types. The default is <literal>on</>.
1591 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
1592 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
1594 <primary><varname>enable_hashagg</> configuration parameter</primary>
1598 Enables or disables the query planner's use of hashed
1599 aggregation plan types. The default is <literal>on</>.
1604 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
1605 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
1607 <primary><varname>enable_hashjoin</> configuration parameter</primary>
1611 Enables or disables the query planner's use of hash-join plan
1612 types. The default is <literal>on</>.
1617 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
1618 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
1620 <primary>index scan</primary>
1623 <primary><varname>enable_indexscan</> configuration parameter</primary>
1627 Enables or disables the query planner's use of index-scan plan
1628 types. The default is <literal>on</>.
1633 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
1634 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
1636 <primary><varname>enable_mergejoin</> configuration parameter</primary>
1640 Enables or disables the query planner's use of merge-join plan
1641 types. The default is <literal>on</>.
1646 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
1647 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
1649 <primary><varname>enable_nestloop</> configuration parameter</primary>
1653 Enables or disables the query planner's use of nested-loop join
1654 plans. It's not possible to suppress nested-loop joins entirely,
1655 but turning this variable off discourages the planner from using
1656 one if there are other methods available. The default is
1662 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
1663 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
1665 <primary>sequential scan</primary>
1668 <primary><varname>enable_seqscan</> configuration parameter</primary>
1672 Enables or disables the query planner's use of sequential scan
1673 plan types. It's not possible to suppress sequential scans
1674 entirely, but turning this variable off discourages the planner
1675 from using one if there are other methods available. The
1676 default is <literal>on</>.
1681 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
1682 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
1684 <primary><varname>enable_sort</> configuration parameter</primary>
1688 Enables or disables the query planner's use of explicit sort
1689 steps. It's not possible to suppress explicit sorts entirely,
1690 but turning this variable off discourages the planner from
1691 using one if there are other methods available. The default
1697 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
1698 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
1700 <primary><varname>enable_tidscan</> configuration parameter</primary>
1704 Enables or disables the query planner's use of <acronym>TID</>
1705 scan plan types. The default is <literal>on</>.
1712 <sect2 id="runtime-config-query-constants">
1713 <title id="runtime-config-query-constants-title">
1714 Planner Cost Constants
1719 Unfortunately, there is no well-defined method for determining
1720 ideal values for the family of <quote>cost</quote> variables that
1721 appear below. You are encouraged to experiment and share
1728 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
1729 <term><varname>effective_cache_size</varname> (<type>floating point</type>)</term>
1731 <primary><varname>effective_cache_size</> configuration parameter</primary>
1735 Sets the planner's assumption about the effective size of the
1736 disk cache that is available to a single index scan. This is
1737 factored into estimates of the cost of using an index; a
1738 higher value makes it more likely index scans will be used, a
1739 lower value makes it more likely sequential scans will be
1740 used. When setting this parameter you should consider both
1741 <productname>PostgreSQL</productname>'s shared buffers and the
1742 portion of the kernel's disk cache that will be used for
1743 <productname>PostgreSQL</productname> data files. Also, take
1744 into account the expected number of concurrent queries using
1745 different indexes, since they will have to share the available
1746 space. This parameter has no effect on the size of shared
1747 memory allocated by <productname>PostgreSQL</productname>, nor
1748 does it reserve kernel disk cache; it is used only for
1749 estimation purposes. The value is measured in disk pages,
1750 which are normally 8192 bytes each. The default is 1000.
1755 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
1756 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
1758 <primary><varname>random_page_cost</> configuration parameter</primary>
1762 Sets the planner's estimate of the cost of a
1763 nonsequentially fetched disk page. This is measured as a
1764 multiple of the cost of a sequential page fetch. A higher
1765 value makes it more likely a sequential scan will be used, a
1766 lower value makes it more likely an index scan will be
1767 used. The default is four.
1772 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
1773 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
1775 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
1779 Sets the planner's estimate of the cost of processing
1780 each row during a query. This is measured as a fraction of
1781 the cost of a sequential page fetch. The default is 0.01.
1786 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
1787 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
1789 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
1793 Sets the planner's estimate of the cost of processing
1794 each index row during an index scan. This is measured as a
1795 fraction of the cost of a sequential page fetch. The default
1801 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
1802 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
1804 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
1808 Sets the planner's estimate of the cost of processing each
1809 operator in a <literal>WHERE</> clause. This is measured as a fraction of
1810 the cost of a sequential page fetch. The default is 0.0025.
1818 <sect2 id="runtime-config-query-geqo">
1819 <title>Genetic Query Optimizer</title>
1823 <varlistentry id="guc-geqo" xreflabel="geqo">
1825 <primary>genetic query optimization</primary>
1828 <primary>GEQO</primary>
1829 <see>genetic query optimization</see>
1832 <primary><varname>geqo</> configuration parameter</primary>
1834 <term><varname>geqo</varname> (<type>boolean</type>)</term>
1837 Enables or disables genetic query optimization, which is an
1838 algorithm that attempts to do query planning without
1839 exhaustive searching. This is on by default. The
1840 <varname>geqo_threshold</varname> variable provides a more
1841 granular way to disable GEQO for certain classes of queries.
1846 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
1847 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
1849 <primary><varname>geqo_threshold</> configuration parameter</primary>
1853 Use genetic query optimization to plan queries with at least
1854 this many <literal>FROM</> items involved. (Note that an outer
1855 <literal>JOIN</> construct counts as only one <literal>FROM</>
1856 item.) The default is 12. For simpler queries it is usually best
1857 to use the deterministic, exhaustive planner, but for queries with
1858 many tables the deterministic planner takes too long.
1863 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
1864 <term><varname>geqo_effort</varname>
1865 (<type>integer</type>)</term>
1867 <primary><varname>geqo_effort</> configuration parameter</primary>
1871 Controls the trade off between planning time and query plan
1872 efficiency in GEQO. This variable must be an integer in the
1873 range from 1 to 10. The default value is 5. Larger values
1874 increase the time spent doing query planning, but also
1875 increase the likelihood that an efficient query plan will be
1880 <varname>geqo_effort</varname> doesn't actually do anything
1881 directly; it is only used to compute the default values for
1882 the other variables that influence GEQO behavior (described
1883 below). If you prefer, you can set the other parameters by
1889 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
1890 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
1892 <primary><varname>geqo_pool_size</> configuration parameter</primary>
1896 Controls the pool size used by GEQO. The pool size is the
1897 number of individuals in the genetic population. It must be
1898 at least two, and useful values are typically 100 to 1000. If
1899 it is set to zero (the default setting) then a suitable
1900 default is chosen based on <varname>geqo_effort</varname> and
1901 the number of tables in the query.
1906 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
1907 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
1909 <primary><varname>geqo_generations</> configuration parameter</primary>
1913 Controls the number of generations used by GEQO. Generations
1914 specifies the number of iterations of the algorithm. It must
1915 be at least one, and useful values are in the same range as
1916 the pool size. If it is set to zero (the default setting)
1917 then a suitable default is chosen based on
1918 <varname>geqo_pool_size</varname>.
1923 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
1924 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
1926 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
1930 Controls the selection bias used by GEQO. The selection bias
1931 is the selective pressure within the population. Values can be
1932 from 1.50 to 2.00; the latter is the default.
1939 <sect2 id="runtime-config-query-other">
1940 <title>Other Planner Options</title>
1944 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
1945 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
1947 <primary><varname>default_statistics_target</> configuration parameter</primary>
1951 Sets the default statistics target for table columns that have
1952 not had a column-specific target set via <command>ALTER TABLE
1953 SET STATISTICS</>. Larger values increase the time needed to
1954 do <command>ANALYZE</>, but may improve the quality of the
1955 planner's estimates. The default is 10. For more information
1956 on the use of statistics by the <productname>PostgreSQL</>
1957 query planner, refer to <xref linkend="planner-stats">.
1962 <varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
1963 <term><varname>constraint_exclusion</varname> (<type>boolean</type>)</term>
1965 <primary>constraint exclusion</primary>
1968 <primary><varname>constraint_exclusion</> configuration parameter</primary>
1972 Enables or disables the query planner's use of table constraints to
1973 optimize queries. The default is <literal>off</>.
1977 When this parameter is <literal>on</>, the planner compares
1978 query conditions with table <literal>CHECK</> constraints, and
1979 omits scanning tables for which the conditions contradict the
1980 constraints. (Presently this is done only for child tables of
1981 inheritance scans.) For example:
1984 CREATE TABLE parent(key integer, ...);
1985 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
1986 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
1988 SELECT * FROM parent WHERE key = 2400;
1991 With constraint exclusion enabled, this <command>SELECT</>
1992 will not scan <structname>child1000</> at all. This can
1993 improve performance when inheritance is used to build
1998 Currently, <varname>constraint_exclusion</> is disabled by
1999 default because it risks incorrect results if query plans are
2000 cached — if a table constraint is changed or dropped,
2001 the previously generated plan might now be wrong, and there is
2002 no built-in mechanism to force re-planning. (This deficiency
2003 will probably be addressed in a future
2004 <productname>PostgreSQL</> release.) Another reason for
2005 keeping it off is that the constraint checks are relatively
2006 expensive, and in many circumstances will yield no savings.
2007 It is recommended to turn this on only if you are actually
2008 using partitioned tables designed to take advantage of the
2013 Refer to <xref linkend="ddl-partitioning"> for more information
2014 on using constraint exclusion and partitioning.
2019 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
2020 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
2022 <primary><varname>from_collapse_limit</> configuration parameter</primary>
2026 The planner will merge sub-queries into upper queries if the
2027 resulting <literal>FROM</literal> list would have no more than
2028 this many items. Smaller values reduce planning time but may
2029 yield inferior query plans. The default is 8. It is usually
2030 wise to keep this less than <xref linkend="guc-geqo-threshold">.
2031 For more information see <xref linkend="explicit-joins">.
2036 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
2037 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
2039 <primary><varname>join_collapse_limit</> configuration parameter</primary>
2043 The planner will rewrite explicit <literal>JOIN</>
2044 constructs (except <literal>FULL JOIN</>s) into lists of
2045 <literal>FROM</> items whenever a list of no more than this many items
2046 would result. Smaller values reduce planning time but may
2047 yield inferior query plans.
2051 By default, this variable is set the same as
2052 <varname>from_collapse_limit</varname>, which is appropriate
2053 for most uses. Setting it to 1 prevents any reordering of
2054 explicit <literal>JOIN</>s. Thus, the explicit join order
2055 specified in the query will be the actual order in which the
2056 relations are joined. The query planner does not always choose
2057 the optimal join order; advanced users may elect to
2058 temporarily set this variable to 1, and then specify the join
2059 order they desire explicitly.
2060 For more information see <xref linkend="explicit-joins">.
2069 <sect1 id="runtime-config-logging">
2070 <title>Error Reporting and Logging</title>
2072 <indexterm zone="runtime-config-logging">
2073 <primary>server log</primary>
2076 <sect2 id="runtime-config-logging-where">
2077 <title>Where To Log</title>
2079 <indexterm zone="runtime-config-logging-where">
2080 <primary>where to log</primary>
2085 <varlistentry id="guc-log-destination" xreflabel="log_destination">
2086 <term><varname>log_destination</varname> (<type>string</type>)</term>
2088 <primary><varname>log_destination</> configuration parameter</primary>
2092 <productname>PostgreSQL</productname> supports several methods
2093 for logging server messages, including
2094 <systemitem>stderr</systemitem> and
2095 <systemitem>syslog</systemitem>. On Windows,
2096 <systemitem>eventlog</systemitem> is also supported. Set this
2097 option to a list of desired log destinations separated by
2098 commas. The default is to log to <systemitem>stderr</systemitem>
2100 This option can only be set at server start or in the
2101 <filename>postgresql.conf</filename> configuration file.
2106 <varlistentry id="guc-redirect-stderr" xreflabel="redirect_stderr">
2107 <term><varname>redirect_stderr</varname> (<type>boolean</type>)</term>
2109 <primary><varname>redirect_stderr</> configuration parameter</primary>
2113 This option allows messages sent to <application>stderr</> to be
2114 captured and redirected into log files.
2115 This option, in combination with logging to <application>stderr</>,
2116 is often more useful than
2117 logging to <application>syslog</>, since some types of messages
2118 may not appear in <application>syslog</> output (a common example
2119 is dynamic-linker failure messages).
2120 This option can only be set at server start.
2125 <varlistentry id="guc-log-directory" xreflabel="log_directory">
2126 <term><varname>log_directory</varname> (<type>string</type>)</term>
2128 <primary><varname>log_directory</> configuration parameter</primary>
2132 When <varname>redirect_stderr</> is enabled, this option
2133 determines the directory in which log files will be created.
2134 It may be specified as an absolute path, or relative to the
2135 cluster data directory.
2136 This option can only be set at server start or in the
2137 <filename>postgresql.conf</filename> configuration file.
2142 <varlistentry id="guc-log-filename" xreflabel="log_filename">
2143 <term><varname>log_filename</varname> (<type>string</type>)</term>
2145 <primary><varname>log_filename</> configuration parameter</primary>
2149 When <varname>redirect_stderr</varname> is enabled, this option
2150 sets the file names of the created log files. The value
2151 is treated as a <systemitem>strftime</systemitem> pattern,
2152 so <literal>%</literal>-escapes
2153 can be used to specify time-varying file names.
2154 If no <literal>%</literal>-escapes are present,
2155 <productname>PostgreSQL</productname> will
2156 append the epoch of the new log file's open time. For example,
2157 if <varname>log_filename</varname> were <literal>server_log</literal>, then the
2158 chosen file name would be <literal>server_log.1093827753</literal>
2159 for a log starting at Sun Aug 29 19:02:33 2004 MST.
2160 This option can only be set at server start or in the
2161 <filename>postgresql.conf</filename> configuration file.
2166 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
2167 <term><varname>log_rotation_age</varname> (<type>integer</type>)</term>
2169 <primary><varname>log_rotation_age</> configuration parameter</primary>
2173 When <varname>redirect_stderr</varname> is enabled, this option
2174 determines the maximum lifetime of an individual log file.
2175 After this many minutes have elapsed, a new log file will
2176 be created. Set to zero to disable time-based creation of
2178 This option can only be set at server start or in the
2179 <filename>postgresql.conf</filename> configuration file.
2184 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
2185 <term><varname>log_rotation_size</varname> (<type>integer</type>)</term>
2187 <primary><varname>log_rotation_size</> configuration parameter</primary>
2191 When <varname>redirect_stderr</varname> is enabled, this option
2192 determines the maximum size of an individual log file.
2193 After this many kilobytes have been emitted into a log file,
2194 a new log file will be created. Set to zero to disable size-based
2195 creation of new log files.
2196 This option can only be set at server start or in the
2197 <filename>postgresql.conf</filename> configuration file.
2202 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
2203 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)</term>
2205 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
2209 When <varname>redirect_stderr</varname> is enabled, this option will cause
2210 <productname>PostgreSQL</productname> to truncate (overwrite),
2211 rather than append to, any existing log file of the same name.
2212 However, truncation will occur only when a new file is being opened
2213 due to time-based rotation, not during server startup or size-based
2214 rotation. When off, pre-existing files will be appended to in
2215 all cases. For example, using this option in combination with
2216 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
2217 would result in generating twenty-four hourly log files and then
2218 cyclically overwriting them.
2219 This option can only be set at server start or in the
2220 <filename>postgresql.conf</filename> configuration file.
2223 Example: To keep 7 days of logs, one log file per day named
2224 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
2225 etc, and automatically overwrite last week's log with this week's log,
2226 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
2227 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>, and
2228 <varname>log_rotation_age</varname> to <literal>1440</literal>.
2231 Example: To keep 24 hours of logs, one log file per hour, but
2232 also rotate sooner if the log file size exceeds 1GB, set
2233 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
2234 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>,
2235 <varname>log_rotation_age</varname> to <literal>60</literal>, and
2236 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
2237 Including <literal>%M</> in <varname>log_filename</varname> allows
2238 any size-driven rotations that may occur to select a file name
2239 different from the hour's initial file name.
2244 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
2245 <term><varname>syslog_facility</varname> (<type>string</type>)</term>
2247 <primary><varname>syslog_facility</> configuration parameter</primary>
2251 When logging to <application>syslog</> is enabled, this option
2252 determines the <application>syslog</application>
2253 <quote>facility</quote> to be used. You may choose
2254 from <literal>LOCAL0</>, <literal>LOCAL1</>,
2255 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
2256 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
2257 the default is <literal>LOCAL0</>. See also the
2258 documentation of your system's
2259 <application>syslog</application> daemon.
2260 This option can only be set at server start or in the
2261 <filename>postgresql.conf</filename> configuration file.
2266 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
2267 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
2269 <primary><varname>syslog_identity</> configuration parameter</primary>
2273 When logging to <application>syslog</> is enabled, this option
2274 determines the program name used to identify
2275 <productname>PostgreSQL</productname> messages in
2276 <application>syslog</application> logs. The default is
2277 <literal>postgres</literal>.
2278 This option can only be set at server start or in the
2279 <filename>postgresql.conf</filename> configuration file.
2286 <sect2 id="runtime-config-logging-when">
2287 <title>When To Log</title>
2291 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
2292 <term><varname>client_min_messages</varname> (<type>string</type>)</term>
2294 <primary><varname>client_min_messages</> configuration parameter</primary>
2298 Controls which message levels are sent to the client.
2299 Valid values are <literal>DEBUG5</>,
2300 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
2301 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
2302 <literal>WARNING</>, <literal>ERROR</>, <literal>FATAL</>,
2303 and <literal>PANIC</>. Each level
2304 includes all the levels that follow it. The later the level,
2305 the fewer messages are sent. The default is
2306 <literal>NOTICE</>. Note that <literal>LOG</> has a different
2307 rank here than in <varname>log_min_messages</>.
2312 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
2313 <term><varname>log_min_messages</varname> (<type>string</type>)</term>
2315 <primary><varname>log_min_messages</> configuration parameter</primary>
2319 Controls which message levels are written to the server log.
2320 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
2321 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
2322 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
2323 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
2324 <literal>PANIC</>. Each level includes all the levels that
2325 follow it. The later the level, the fewer messages are sent
2326 to the log. The default is <literal>NOTICE</>. Note that
2327 <literal>LOG</> has a different rank here than in
2328 <varname>client_min_messages</>.
2329 Only superusers can change this setting.
2334 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
2335 <term><varname>log_error_verbosity</varname> (<type>string</type>)</term>
2337 <primary><varname>log_error_verbosity</> configuration parameter</primary>
2341 Controls the amount of detail written in the server log for each
2342 message that is logged. Valid values are <literal>TERSE</>,
2343 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
2344 fields to displayed messages.
2345 Only superusers can change this setting.
2350 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
2351 <term><varname>log_min_error_statement</varname> (<type>string</type>)</term>
2353 <primary><varname>log_min_error_statement</> configuration parameter</primary>
2357 Controls whether or not the SQL statement that causes an error
2358 condition will also be recorded in the server log. All SQL
2359 statements that cause an error of the specified level or
2360 higher are logged. The default is
2361 <literal>PANIC</literal> (effectively turning this feature
2362 off for normal use). Valid values are <literal>DEBUG5</literal>,
2363 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
2364 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
2365 <literal>INFO</literal>, <literal>NOTICE</literal>,
2366 <literal>WARNING</literal>, <literal>ERROR</literal>,
2367 <literal>FATAL</literal>, and <literal>PANIC</literal>. For
2368 example, if you set this to <literal>ERROR</literal> then all
2369 SQL statements causing errors, fatal errors, or panics will be
2370 logged. Enabling this option can be helpful in tracking down
2371 the source of any errors that appear in the server log.
2372 Only superusers can change this setting.
2377 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
2378 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
2380 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
2384 Logs the statement and its duration on a single log line if its
2385 duration is greater than or equal to the specified number of
2386 milliseconds. Setting this to zero will print all statements
2387 and their durations. Minus-one (the default) disables the
2388 feature. For example, if you set it to <literal>250</literal>
2389 then all SQL statements that run 250ms or longer will be
2390 logged. Enabling this option can be useful in tracking down
2391 unoptimized queries in your applications. This setting is
2392 independent of <varname>log_statement</varname> and
2393 <varname>log_duration</varname>. Only superusers can change
2399 <varlistentry id="guc-silent-mode" xreflabel="silent_mode">
2400 <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
2402 <primary><varname>silent_mode</> configuration parameter</primary>
2406 Runs the server silently. If this option is set, the server
2407 will automatically run in background and any controlling
2408 terminals are disassociated (same effect as
2409 <command>postmaster</>'s <option>-S</option> option).
2410 The server's standard output and standard error are redirected
2411 to <literal>/dev/null</>, so any messages sent to them will be lost.
2412 Unless <application>syslog</> logging is selected or
2413 <varname>redirect_stderr</> is enabled, using this option
2414 is discouraged because it makes it impossible to see error messages.
2422 Here is a list of the various message severity levels used in
2426 <term><literal>DEBUG[1-5]</literal></term>
2429 Provides information for use by developers.
2435 <term><literal>INFO</literal></term>
2438 Provides information implicitly requested by the user,
2439 e.g., during <command>VACUUM VERBOSE</>.
2445 <term><literal>NOTICE</literal></term>
2448 Provides information that may be helpful to users, e.g.,
2449 truncation of long identifiers and the creation of indexes as part
2456 <term><literal>WARNING</literal></term>
2459 Provides warnings to the user, e.g., <command>COMMIT</>
2460 outside a transaction block.
2466 <term><literal>ERROR</literal></term>
2469 Reports an error that caused the current command to abort.
2475 <term><literal>LOG</literal></term>
2478 Reports information of interest to administrators, e.g.,
2479 checkpoint activity.
2485 <term><literal>FATAL</literal></term>
2488 Reports an error that caused the current session to abort.
2494 <term><literal>PANIC</literal></term>
2497 Reports an error that caused all sessions to abort.
2505 <sect2 id="runtime-config-logging-what">
2506 <title>What To Log</title>
2511 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
2512 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
2513 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
2514 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
2516 <primary><varname>debug_print_parse</> configuration parameter</primary>
2519 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
2522 <primary><varname>debug_print_plan</> configuration parameter</primary>
2525 <primary><varname>debug_pretty_print</> configuration parameter</primary>
2529 These options enable various debugging output to be emitted.
2530 For each executed query, they print
2531 the resulting parse tree, the query rewriter output, or the
2532 execution plan. <varname>debug_pretty_print</varname> indents
2533 these displays to produce a more readable but much longer
2534 output format. <varname>client_min_messages</varname> or
2535 <varname>log_min_messages</varname> must be
2536 <literal>DEBUG1</literal> or lower to actually send this output
2537 to the client or the server log, respectively.
2538 These options are off by default.
2543 <varlistentry id="guc-log-connections" xreflabel="log_connections">
2544 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
2546 <primary><varname>log_connections</> configuration parameter</primary>
2550 This outputs a line to the server log detailing each successful
2551 connection. This is off by default, although it is probably very
2552 useful. Some client programs, like <application>psql</>, attempt
2553 to connect twice while determining if a password is required, so
2554 duplicate <quote>connection received</> messages do not
2555 necessarily indicate a problem. This option can only be set at
2556 server start or in the <filename>postgresql.conf</> configuration file.
2561 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
2562 <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
2564 <primary><varname>log_disconnections</> configuration parameter</primary>
2568 This outputs a line in the server log similar to
2569 <varname>log_connections</varname> but at session termination,
2570 and includes the duration of the session. This is off by
2571 default. This option can only be set at server start or in the
2572 <filename>postgresql.conf</filename> configuration file.
2578 <varlistentry id="guc-log-duration" xreflabel="log_duration">
2579 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
2581 <primary><varname>log_duration</> configuration parameter</primary>
2585 Causes the duration of every completed statement which satisfies
2586 <varname>log_statement</> to be logged. When using this option,
2587 if you are not using <application>syslog</>, it is recommended
2588 that you log the PID or session ID using <varname>log_line_prefix</>
2589 so that you can link the statement message to the later
2590 duration message using the process ID or session ID. The default is
2591 <literal>off</>. Only superusers can change this setting.
2596 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
2597 <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
2599 <primary><varname>log_line_prefix</> configuration parameter</primary>
2603 This is a <function>printf</>-style string that is output at the
2604 beginning of each log line. The default is an empty string.
2605 Each recognized escape is replaced as outlined
2606 below - anything else that looks like an escape is ignored. Other
2607 characters are copied straight to the log line. Some escapes are
2608 only recognized by session processes, and do not apply to
2609 background processes such as the postmaster. <application>Syslog</>
2611 time stamp and process ID information, so you probably do not want to
2612 use those escapes if you are using <application>syslog</>.
2613 This option can only be set at server start or in the
2614 <filename>postgresql.conf</filename> configuration file.
2620 <entry>Escape</entry>
2621 <entry>Effect</entry>
2622 <entry>Session only</entry>
2627 <entry><literal>%u</literal></entry>
2628 <entry>User name</entry>
2632 <entry><literal>%d</literal></entry>
2633 <entry>Database name</entry>
2637 <entry><literal>%r</literal></entry>
2638 <entry>Remote host name or IP address, and remote port</entry>
2642 <entry><literal>%h</literal></entry>
2643 <entry>Remote host name or IP address</entry>
2647 <entry><literal>%p</literal></entry>
2648 <entry>Process ID</entry>
2652 <entry><literal>%t</literal></entry>
2653 <entry>Time stamp (no milliseconds)</entry>
2657 <entry><literal>%m</literal></entry>
2658 <entry>Time stamp with milliseconds</entry>
2662 <entry><literal>%i</literal></entry>
2663 <entry>Command tag: This is the command that generated the log line.</entry>
2667 <entry><literal>%c</literal></entry>
2668 <entry>Session ID: A unique identifier for each session.
2669 It is 2 4-byte hexadecimal numbers (without leading zeros)
2670 separated by a dot. The numbers
2671 are the session start time and the process ID, so this can also
2672 be used as a space saving way of printing these items.</entry>
2676 <entry><literal>%l</literal></entry>
2677 <entry>Number of the log line for each process, starting at 1</entry>
2681 <entry><literal>%s</literal></entry>
2682 <entry>Session start time stamp</entry>
2686 <entry><literal>%x</literal></entry>
2687 <entry>Transaction ID</entry>
2691 <entry><literal>%q</literal></entry>
2692 <entry>Does not produce any output, but tells non-session
2693 processes to stop at this point in the string. Ignored by
2694 session processes.</entry>
2698 <entry><literal>%%</literal></entry>
2699 <entry>Literal <literal>%</></entry>
2709 <varlistentry id="guc-log-statement" xreflabel="log_statement">
2710 <term><varname>log_statement</varname> (<type>string</type>)</term>
2712 <primary><varname>log_statement</> configuration parameter</primary>
2716 Controls which SQL statements are logged. Valid values are
2717 <literal>none</>, <literal>ddl</>, <literal>mod</>, and
2718 <literal>all</>. <literal>ddl</> logs all data definition
2719 commands like <literal>CREATE</>, <literal>ALTER</>, and
2720 <literal>DROP</> commands. <literal>mod</> logs all
2721 <literal>ddl</> statements, plus <literal>INSERT</>,
2722 <literal>UPDATE</>, <literal>DELETE</>, <literal>TRUNCATE</>,
2723 and <literal>COPY FROM</>. <literal>PREPARE</> and
2724 <literal>EXPLAIN ANALYZE</> statements are also logged if their
2725 contained command is of an appropriate type.
2728 The default is <literal>none</>. Only superusers can change this
2734 The <command>EXECUTE</command> statement is not considered a
2735 <literal>ddl</> or <literal>mod</> statement. When it is logged,
2736 only the name of the prepared statement is reported, not the
2737 actual prepared statement.
2741 When a function is defined in the
2742 <application>PL/pgSQL</application>server-side language, any queries
2743 executed by the function will only be logged the first time that the
2744 function is invoked in a particular session. This is because
2745 <application>PL/pgSQL</application> keeps a cache of the
2746 query plans produced for the SQL statements in the function.
2752 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
2753 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
2755 <primary><varname>log_hostname</> configuration parameter</primary>
2759 By default, connection log messages only show the IP address of the
2760 connecting host. Turning on this option causes logging of the
2761 host name as well. Note that depending on your host name resolution
2762 setup this might impose a non-negligible performance penalty. This
2763 option can only be set at server start or in the
2764 <filename>postgresql.conf</filename> file.
2773 <sect1 id="runtime-config-statistics">
2774 <title>Run-Time Statistics</title>
2776 <sect2 id="runtime-config-statistics-monitor">
2777 <title>Statistics Monitoring</title>
2781 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
2782 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
2783 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
2784 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
2786 <primary><varname>log_statement_stats</> configuration parameter</primary>
2789 <primary><varname>log_parser_stats</> configuration parameter</primary>
2792 <primary><varname>log_planner_stats</> configuration parameter</primary>
2795 <primary><varname>log_executor_stats</> configuration parameter</primary>
2799 For each query, write performance statistics of the respective
2800 module to the server log. This is a crude profiling
2801 instrument. <varname>log_statement_stats</varname> reports total
2802 statement statistics, while the others report per-module statistics.
2803 <varname>log_statement_stats</varname> cannot be enabled together with
2804 any of the per-module options. All of these options are disabled by
2805 default. Only superusers can change these settings.
2813 <sect2 id="runtime-config-statistics-collector">
2814 <title>Query and Index Statistics Collector</title>
2817 <varlistentry id="guc-stats-start-collector" xreflabel="stats_start_collector">
2818 <term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>
2820 <primary><varname>stats_start_collector</> configuration parameter</primary>
2824 Controls whether the server should start the
2825 statistics-collection subprocess. This is on by default, but
2826 may be turned off if you know you have no interest in
2827 collecting statistics. This option can only be set at server
2833 <varlistentry id="guc-stats-command-string" xreflabel="stats_command_string">
2834 <term><varname>stats_command_string</varname> (<type>boolean</type>)</term>
2836 <primary><varname>stats_command_string</> configuration parameter</primary>
2840 Enables the collection of statistics on the currently
2841 executing command of each session, along with the time at
2842 which that command began execution. This option is off by
2843 default. Note that even when enabled, this information is not
2844 visible to all users, only to superusers and the user owning
2845 the session being reported on; so it should not represent a
2846 security risk. This data can be accessed via the
2847 <structname>pg_stat_activity</structname> system view; refer
2848 to <xref linkend="monitoring"> for more information.
2853 <varlistentry id="guc-stats-block-level" xreflabel="stats_block_level">
2854 <term><varname>stats_block_level</varname> (<type>boolean</type>)</term>
2856 <primary><varname>stats_block_level</> configuration parameter</primary>
2860 Enables the collection of block-level statistics on database
2861 activity. This option is disabled by default. If this option
2862 is enabled, the data that is produced can be accessed via the
2863 <structname>pg_stat</structname> and
2864 <structname>pg_statio</structname> family of system views;
2865 refer to <xref linkend="monitoring"> for more information.
2870 <varlistentry id="guc-stats-row-level" xreflabel="stats_row_level">
2871 <term><varname>stats_row_level</varname> (<type>boolean</type>)</term>
2873 <primary><varname>stats_row_level</> configuration parameter</primary>
2877 Enables the collection of row-level statistics on database
2878 activity. This option is disabled by default. If this option
2879 is enabled, the data that is produced can be accessed via the
2880 <structname>pg_stat</structname> and
2881 <structname>pg_statio</structname> family of system views;
2882 refer to <xref linkend="monitoring"> for more information.
2887 <varlistentry id="guc-stats-reset-on-server-start" xreflabel="stats_reset_on_server_start">
2888 <term><varname>stats_reset_on_server_start</varname> (<type>boolean</type>)</term>
2890 <primary><varname>stats_reset_on_server_start</> configuration parameter</primary>
2894 If on, collected statistics are zeroed out whenever the server
2895 is restarted. If off, statistics are accumulated across server
2896 restarts. The default is <literal>off</>. This option can only
2897 be set at server start.
2906 <sect1 id="runtime-config-autovacuum">
2907 <title>Automatic Vacuuming</title>
2910 <primary>autovacuum</primary>
2911 <secondary>global configuration parameters</secondary>
2915 These settings control the default behavior for the <firstterm>autovacuum
2916 daemon</firstterm>. Please refer to <xref linkend="autovacuum"> for
2922 <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
2923 <term><varname>autovacuum</varname> (<type>boolean</type>)</term>
2925 <primary><varname>autovacuum</> configuration parameter</primary>
2929 Controls whether the server should start the
2930 autovacuum subprocess. This is off by default.
2931 <varname>stats_start_collector</> and <varname>stats_row_level</>
2932 must also be on for this to start.
2933 This option can only be set at server start or in the
2934 <filename>postgresql.conf</filename> file.
2939 <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
2940 <term><varname>autovacuum_naptime</varname> (<type>integer</type>)</term>
2942 <primary><varname>autovacuum_naptime</> configuration parameter</primary>
2946 Specifies the delay between activity rounds for the autovacuum
2947 subprocess. In each round the subprocess examines one database
2948 and issues <command>VACUUM</> and <command>ANALYZE</> commands
2949 as needed for tables in that database. The delay is measured
2950 in seconds, and the default is 60.
2951 This option can only be set at server start or in the
2952 <filename>postgresql.conf</filename> file.
2957 <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
2958 <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)</term>
2960 <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
2964 Specifies the minimum number of updated or deleted tuples needed
2965 to trigger a <command>VACUUM</> in any one table.
2966 The default is 1000.
2967 This option can only be set at server start or in the
2968 <filename>postgresql.conf</filename> file.
2969 This setting can be overridden for individual tables by entries in
2970 <structname>pg_autovacuum</>.
2975 <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
2976 <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)</term>
2978 <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
2982 Specifies the minimum number of inserted, updated or deleted tuples
2983 needed to trigger an <command>ANALYZE</> in any one table.
2985 This option can only be set at server start or in the
2986 <filename>postgresql.conf</filename> file.
2987 This setting can be overridden for individual tables by entries in
2988 <structname>pg_autovacuum</>.
2993 <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
2994 <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)</term>
2996 <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
3000 Specifies a fraction of the table size to add to
3001 <varname>autovacuum_vacuum_threshold</varname>
3002 when deciding whether to trigger a <command>VACUUM</>.
3004 This option can only be set at server start or in the
3005 <filename>postgresql.conf</filename> file.
3006 This setting can be overridden for individual tables by entries in
3007 <structname>pg_autovacuum</>.
3012 <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
3013 <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)</term>
3015 <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
3019 Specifies a fraction of the table size to add to
3020 <varname>autovacuum_analyze_threshold</varname>
3021 when deciding whether to trigger an <command>ANALYZE</>.
3023 This option can only be set at server start or in the
3024 <filename>postgresql.conf</filename> file.
3025 This setting can be overridden for individual tables by entries in
3026 <structname>pg_autovacuum</>.
3031 <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
3032 <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
3034 <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
3038 Specifies the cost delay value that will be used in automatic
3039 <command>VACUUM</> operations. If -1 is specified (which is the
3040 default), the regular
3041 <xref linkend="guc-vacuum-cost-delay"> value will be used.
3042 This setting can be overridden for individual tables by entries in
3043 <structname>pg_autovacuum</>.
3048 <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
3049 <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
3051 <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
3055 Specifies the cost limit value that will be used in automatic
3056 <command>VACUUM</> operations. If -1 is specified (which is the
3057 default), the regular
3058 <xref linkend="guc-vacuum-cost-limit"> value will be used.
3059 This setting can be overridden for individual tables by entries in
3060 <structname>pg_autovacuum</>.
3068 <sect1 id="runtime-config-client">
3069 <title>Client Connection Defaults</title>
3071 <sect2 id="runtime-config-client-statement">
3072 <title>Statement Behavior</title>
3075 <varlistentry id="guc-search-path" xreflabel="search_path">
3076 <term><varname>search_path</varname> (<type>string</type>)</term>
3078 <primary><varname>search_path</> configuration parameter</primary>
3080 <indexterm><primary>path</><secondary>for schemas</></>
3083 This variable specifies the order in which schemas are searched
3084 when an object (table, data type, function, etc.) is referenced by a
3085 simple name with no schema component. When there are objects of
3086 identical names in different schemas, the one found first
3087 in the search path is used. An object that is not in any of the
3088 schemas in the search path can only be referenced by specifying
3089 its containing schema with a qualified (dotted) name.
3093 The value for <varname>search_path</varname> has to be a comma-separated
3094 list of schema names. If one of the list items is
3095 the special value <literal>$user</literal>, then the schema
3096 having the name returned by <function>SESSION_USER</> is substituted, if there
3097 is such a schema. (If not, <literal>$user</literal> is ignored.)
3101 The system catalog schema, <literal>pg_catalog</>, is always
3102 searched, whether it is mentioned in the path or not. If it is
3103 mentioned in the path then it will be searched in the specified
3104 order. If <literal>pg_catalog</> is not in the path then it will
3105 be searched <emphasis>before</> searching any of the path items.
3106 It should also be noted that the temporary-table schema,
3107 <literal>pg_temp_<replaceable>nnn</></>, is implicitly searched before any of
3112 When objects are created without specifying a particular target
3113 schema, they will be placed in the first schema listed
3114 in the search path. An error is reported if the search path is
3119 The default value for this parameter is
3120 <literal>'"$user", public'</literal> (where the second part will be
3121 ignored if there is no schema named <literal>public</>).
3122 This supports shared use of a database (where no users
3123 have private schemas, and all share use of <literal>public</>),
3124 private per-user schemas, and combinations of these. Other
3125 effects can be obtained by altering the default search path
3126 setting, either globally or per-user.
3130 The current effective value of the search path can be examined
3131 via the <acronym>SQL</acronym> function
3132 <function>current_schemas()</>. This is not quite the same as
3133 examining the value of <varname>search_path</varname>, since
3134 <function>current_schemas()</> shows how the requests
3135 appearing in <varname>search_path</varname> were resolved.
3139 For more information on schema handling, see <xref linkend="ddl-schemas">.
3144 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
3145 <term><varname>default_tablespace</varname> (<type>string</type>)</term>
3147 <primary><varname>default_tablespace</> configuration parameter</primary>
3149 <indexterm><primary>tablespace</><secondary>default</></>
3152 This variable specifies the default tablespace in which to create
3153 objects (tables and indexes) when a <command>CREATE</> command does
3154 not explicitly specify a tablespace.
3158 The value is either the name of a tablespace, or an empty string
3159 to specify using the default tablespace of the current database.
3160 If the value does not match the name of any existing tablespace,
3161 <productname>PostgreSQL</> will automatically use the default
3162 tablespace of the current database.
3166 For more information on tablespaces,
3167 see <xref linkend="manage-ag-tablespaces">.
3172 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
3173 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
3175 <primary><varname>check_function_bodies</> configuration parameter</primary>
3179 This parameter is normally on. When set to <literal>off</>, it
3180 disables validation of the function body string during <xref
3181 linkend="sql-createfunction"
3182 endterm="sql-createfunction-title">. Disabling validation is
3183 occasionally useful to avoid problems such as forward references
3184 when restoring function definitions from a dump.
3189 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
3191 <primary>transaction isolation level</primary>
3194 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
3196 <term><varname>default_transaction_isolation</varname> (<type>string</type>)</term>
3199 Each SQL transaction has an isolation level, which can be
3200 either <quote>read uncommitted</quote>, <quote>read
3201 committed</quote>, <quote>repeatable read</quote>, or
3202 <quote>serializable</quote>. This parameter controls the
3203 default isolation level of each new transaction. The default
3204 is <quote>read committed</quote>.
3208 Consult <xref linkend="mvcc"> and <xref
3209 linkend="sql-set-transaction"
3210 endterm="sql-set-transaction-title"> for more information.
3215 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
3217 <primary>read-only transaction</primary>
3220 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
3223 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
3226 A read-only SQL transaction cannot alter non-temporary tables.
3227 This parameter controls the default read-only status of each new
3228 transaction. The default is <literal>off</> (read/write).
3232 Consult <xref linkend="sql-set-transaction"
3233 endterm="sql-set-transaction-title"> for more information.
3238 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
3239 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
3241 <primary><varname>statement_timeout</> configuration parameter</primary>
3245 Abort any statement that takes over the specified number of
3246 milliseconds. If <varname>log_min_error_statement</> is set to
3247 <literal>ERROR</> or lower, the statement that timed out will also be
3248 logged. A value of zero (the default) turns off the
3256 <sect2 id="runtime-config-client-format">
3257 <title>Locale and Formatting</title>
3261 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
3262 <term><varname>DateStyle</varname> (<type>string</type>)</term>
3264 <primary><varname>DateStyle</> configuration parameter</primary>
3268 Sets the display format for date and time values, as well as the
3269 rules for interpreting ambiguous date input values. For
3270 historical reasons, this variable contains two independent
3271 components: the output format specification (<literal>ISO</>,
3272 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
3273 and the input/output specification for year/month/day ordering
3274 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
3275 can be set separately or together. The keywords <literal>Euro</>
3276 and <literal>European</> are synonyms for <literal>DMY</>; the
3277 keywords <literal>US</>, <literal>NonEuro</>, and
3278 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
3279 <xref linkend="datatype-datetime"> for more information. The
3280 built-in default is <literal>ISO, MDY</>, but
3281 <application>initdb</application> will initialize the
3282 configuration file with a setting that corresponds to the
3283 behavior of the chosen <varname>lc_time</varname> locale.
3288 <varlistentry id="guc-timezone" xreflabel="timezone">
3289 <term><varname>timezone</varname> (<type>string</type>)</term>
3291 <primary><varname>timezone</> configuration parameter</primary>
3293 <indexterm><primary>time zone</></>
3296 Sets the time zone for displaying and interpreting time
3297 stamps. The default is 'unknown', which means to use whatever
3298 the system environment specifies as the time zone. See <xref
3299 linkend="datatype-datetime"> for more information.
3304 <varlistentry id="guc-australian-timezones" xreflabel="australian_timezones">
3305 <term><varname>australian_timezones</varname> (<type>boolean</type>)</term>
3307 <primary><varname>australian_timezones</> configuration parameter</primary>
3309 <indexterm><primary>time zone</><secondary>Australian</></>
3312 If set to on, <literal>ACST</literal>,
3313 <literal>CST</literal>, <literal>EST</literal>, and
3314 <literal>SAT</literal> are interpreted as Australian time
3315 zones rather than as North/South American time zones and
3316 Saturday. The default is <literal>off</>.
3321 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
3323 <primary>significant digits</primary>
3326 <primary>floating-point</primary>
3327 <secondary>display</secondary>
3330 <primary><varname>extra_float_digits</> configuration parameter</primary>
3333 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
3336 This parameter adjusts the number of digits displayed for
3337 floating-point values, including <type>float4</>, <type>float8</>,
3338 and geometric data types. The parameter value is added to the
3339 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
3340 as appropriate). The value can be set as high as 2, to include
3341 partially-significant digits; this is especially useful for dumping
3342 float data that needs to be restored exactly. Or it can be set
3343 negative to suppress unwanted digits.
3348 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
3349 <term><varname>client_encoding</varname> (<type>string</type>)</term>
3351 <primary><varname>client_encoding</> configuration parameter</primary>
3353 <indexterm><primary>character set</></>
3356 Sets the client-side encoding (character set).
3357 The default is to use the database encoding.
3362 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
3363 <term><varname>lc_messages</varname> (<type>string</type>)</term>
3365 <primary><varname>lc_messages</> configuration parameter</primary>
3369 Sets the language in which messages are displayed. Acceptable
3370 values are system-dependent; see <xref linkend="locale"> for
3371 more information. If this variable is set to the empty string
3372 (which is the default) then the value is inherited from the
3373 execution environment of the server in a system-dependent way.
3377 On some systems, this locale category does not exist. Setting
3378 this variable will still work, but there will be no effect.
3379 Also, there is a chance that no translated messages for the
3380 desired language exist. In that case you will continue to see
3381 the English messages.
3386 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
3387 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
3389 <primary><varname>lc_monetary</> configuration parameter</primary>
3393 Sets the locale to use for formatting monetary amounts, for
3394 example with the <function>to_char</function> family of
3395 functions. Acceptable values are system-dependent; see <xref
3396 linkend="locale"> for more information. If this variable is
3397 set to the empty string (which is the default) then the value
3398 is inherited from the execution environment of the server in a
3399 system-dependent way.
3404 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
3405 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
3407 <primary><varname>lc_numeric</> configuration parameter</primary>
3411 Sets the locale to use for formatting numbers, for example
3412 with the <function>to_char</function> family of
3413 functions. Acceptable values are system-dependent; see <xref
3414 linkend="locale"> for more information. If this variable is
3415 set to the empty string (which is the default) then the value
3416 is inherited from the execution environment of the server in a
3417 system-dependent way.
3422 <varlistentry id="guc-lc-time" xreflabel="lc_time">
3423 <term><varname>lc_time</varname> (<type>string</type>)</term>
3425 <primary><varname>lc_time</> configuration parameter</primary>
3429 Sets the locale to use for formatting date and time values.
3430 (Currently, this setting does nothing, but it may in the
3431 future.) Acceptable values are system-dependent; see <xref
3432 linkend="locale"> for more information. If this variable is
3433 set to the empty string (which is the default) then the value
3434 is inherited from the execution environment of the server in a
3435 system-dependent way.
3443 <sect2 id="runtime-config-client-other">
3444 <title>Other Defaults</title>
3448 <varlistentry id="guc-explain-pretty-print" xreflabel="explain_pretty_print">
3449 <term><varname>explain_pretty_print</varname> (<type>boolean</type>)</term>
3451 <primary><varname>explain_pretty_print</> configuration parameter</primary>
3455 Determines whether <command>EXPLAIN VERBOSE</> uses the
3456 indented or non-indented format for displaying detailed
3457 query-tree dumps. The default is <literal>on</>.
3462 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
3463 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
3465 <primary><varname>dynamic_library_path</> configuration parameter</primary>
3467 <indexterm><primary>dynamic loading</></>
3470 If a dynamically loadable module needs to be opened and the
3471 file name specified in the <command>CREATE FUNCTION</command> or
3472 <command>LOAD</command> command
3473 does not have a directory component (i.e. the
3474 name does not contain a slash), the system will search this
3475 path for the required file.
3479 The value for <varname>dynamic_library_path</varname> has to be a
3480 list of absolute directory paths separated by colons (or semi-colons
3481 on Windows). If a list element starts
3482 with the special string <literal>$libdir</literal>, the
3483 compiled-in <productname>PostgreSQL</productname> package
3484 library directory is substituted for <literal>$libdir</literal>. This
3485 is where the modules provided by the standard
3486 <productname>PostgreSQL</productname> distribution are installed.
3487 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
3488 this directory.) For example:
3490 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
3492 or, in a Windows environment:
3494 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
3499 The default value for this parameter is
3500 <literal>'$libdir'</literal>. If the value is set to an empty
3501 string, the automatic path search is turned off.
3505 This parameter can be changed at run time by superusers, but a
3506 setting done that way will only persist until the end of the
3507 client connection, so this method should be reserved for
3508 development purposes. The recommended way to set this parameter
3509 is in the <filename>postgresql.conf</filename> configuration
3519 <sect1 id="runtime-config-locks">
3520 <title>Lock Management</title>
3524 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
3526 <primary>deadlock</primary>
3527 <secondary>timeout during</secondary>
3530 <primary>timeout</primary>
3531 <secondary>deadlock</secondary>
3534 <primary><varname>deadlock_timeout</> configuration parameter</primary>
3537 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
3540 This is the amount of time, in milliseconds, to wait on a lock
3541 before checking to see if there is a deadlock condition. The
3542 check for deadlock is relatively slow, so the server doesn't run
3543 it every time it waits for a lock. We (optimistically?) assume
3544 that deadlocks are not common in production applications and
3545 just wait on the lock for a while before starting the check for a
3546 deadlock. Increasing this value reduces the amount of time
3547 wasted in needless deadlock checks, but slows down reporting of
3548 real deadlock errors. The default is 1000 (i.e., one second),
3549 which is probably about the smallest value you would want in
3550 practice. On a heavily loaded server you might want to raise it.
3551 Ideally the setting should exceed your typical transaction time,
3552 so as to improve the odds that a lock will be released before
3553 the waiter decides to check for deadlock.
3558 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
3559 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
3561 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
3565 The shared lock table is created with room to describe locks on
3566 <varname>max_locks_per_transaction</varname> *
3567 (<xref linkend="guc-max-connections"> +
3568 <xref linkend="guc-max-prepared-transactions">) objects;
3569 hence, no more than this many distinct objects can
3570 be locked at any one time. (Thus, this parameter's name may be
3571 confusing: it is not a hard limit on the number of locks taken
3572 by any one transaction, but rather a maximum average value.)
3573 The default, 64, has historically
3574 proven sufficient, but you might need to raise this value if you
3575 have clients that touch many different tables in a single
3576 transaction. This option can only be set at server start.
3580 Increasing this parameter may cause <productname>PostgreSQL</>
3581 to request more <systemitem class="osname">System V</> shared
3582 memory than your operating system's default configuration
3583 allows. See <xref linkend="sysvipc"> for information on how to
3584 adjust those parameters, if necessary.
3592 <sect1 id="runtime-config-compatible">
3593 <title>Version and Platform Compatibility</title>
3595 <sect2 id="runtime-config-compatible-version">
3596 <title>Previous PostgreSQL Versions</title>
3600 <varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
3601 <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
3602 <indexterm><primary>FROM</><secondary>missing</></>
3604 <primary><varname>add_missing_from</> configuration parameter</primary>
3608 When on, tables that are referenced by a query will be
3609 automatically added to the <literal>FROM</> clause if not
3610 already present. This behavior does not comply with the SQL
3611 standard and many people dislike it because it can mask mistakes
3612 (such as referencing a table where you should have referenced
3613 its alias). The default is <literal>off</>. This variable can be
3614 enabled for compatibility with releases of
3615 <productname>PostgreSQL</> prior to 8.1, where this behavior was
3620 Note that even when this variable is enabled, a warning
3621 message will be emitted for each implicit <literal>FROM</>
3622 entry referenced by a query. Users are encouraged to update
3623 their applications to not rely on this behavior, by adding all
3624 tables referenced by a query to the query's <literal>FROM</>
3625 clause (or its <literal>USING</> clause in the case of
3626 <command>DELETE</>).
3631 <varlistentry id="guc-array-nulls" xreflabel="array_nulls">
3632 <term><varname>array_nulls</varname> (<type>boolean</type>)</term>
3634 <primary><varname>array_nulls</> configuration parameter</primary>
3638 This controls whether the array input parser recognizes
3639 unquoted <literal>NULL</> as specifying a NULL array element.
3640 By default, this is <literal>on</>, allowing array values containing
3641 NULLs to be entered. However, <productname>PostgreSQL</> versions
3642 before 8.2 did not support NULLs in arrays, and therefore would
3643 treat <literal>NULL</> as specifying a normal array element with
3644 the string value <quote>NULL</>. For backwards compatibility with
3645 applications that require the old behavior, this variable can be
3646 turned <literal>off</>.
3650 Note that it is possible to create array values containing NULLs
3651 even when this variable is <literal>off</>.
3656 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
3657 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
3659 <primary><varname>default_with_oids</> configuration parameter</primary>
3663 This controls whether <command>CREATE TABLE</command> and
3664 <command>CREATE TABLE AS</command> include an OID column in
3665 newly-created tables, if neither <literal>WITH OIDS</literal>
3666 nor <literal>WITHOUT OIDS</literal> is specified. It also
3667 determines whether OIDs will be included in tables created by
3668 <command>SELECT INTO</command>. In <productname>PostgreSQL</>
3669 8.1 <varname>default_with_oids</> is disabled by default; in
3670 prior versions of <productname>PostgreSQL</productname>, it
3675 The use of OIDs in user tables is considered deprecated, so
3676 most installations should leave this variable disabled.
3677 Applications that require OIDs for a particular table should
3678 specify <literal>WITH OIDS</literal> when creating the
3679 table. This variable can be enabled for compatibility with old
3680 applications that do not follow this behavior.
3685 <varlistentry id="guc-escape-string-warning" xreflabel="escape_string_warning">
3686 <term><varname>escape_string_warning</varname> (<type>boolean</type>)</term>
3687 <indexterm><primary>strings</><secondary>escape</></>
3689 <primary><varname>escape_string_warning</> configuration parameter</primary>
3693 When on, a warning is issued if a backslash (<literal>\</>)
3694 appears in an ordinary string literal (<literal>'...'</>
3695 syntax). The default is <literal>off</>.
3698 Escape string syntax (<literal>E'...'</>) should be used for
3699 escapes, because in future versions of
3700 <productname>PostgreSQL</productname> ordinary strings will have
3701 the standard-conforming behavior of treating backslashes
3707 <varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
3708 <term><varname>regex_flavor</varname> (<type>string</type>)</term>
3709 <indexterm><primary>regular expressions</></>
3711 <primary><varname>regex_flavor</> configuration parameter</primary>
3715 The regular expression <quote>flavor</> can be set to
3716 <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
3717 The default is <literal>advanced</>. The <literal>extended</>
3718 setting may be useful for exact backwards compatibility with
3719 pre-7.4 releases of <productname>PostgreSQL</>. See
3720 <xref linkend="posix-syntax-details"> for details.
3725 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
3726 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
3728 <primary><varname>sql_inheritance</> configuration parameter</primary>
3730 <indexterm><primary>inheritance</></>
3733 This controls the inheritance semantics, in particular whether
3734 subtables are included by various commands by default. They were
3735 not included in versions prior to 7.1. If you need the old
3736 behavior you can set this variable to <literal>off</>, but in
3737 the long run you are encouraged to change your applications to
3738 use the <literal>ONLY</literal> key word to exclude subtables.
3739 See <xref linkend="ddl-inherit"> for more information about
3748 <sect2 id="runtime-config-compatible-clients">
3749 <title>Platform and Client Compatibility</title>
3752 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
3753 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
3754 <indexterm><primary>IS NULL</></>
3756 <primary><varname>transform_null_equals</> configuration parameter</primary>
3760 When on, expressions of the form <literal><replaceable>expr</> =
3761 NULL</literal> (or <literal>NULL =
3762 <replaceable>expr</></literal>) are treated as
3763 <literal><replaceable>expr</> IS NULL</literal>, that is, they
3764 return true if <replaceable>expr</> evaluates to the null value,
3765 and false otherwise. The correct SQL-spec-compliant behavior of
3766 <literal><replaceable>expr</> = NULL</literal> is to always
3767 return null (unknown). Therefore this option defaults to
3772 However, filtered forms in <productname>Microsoft
3773 Access</productname> generate queries that appear to use
3774 <literal><replaceable>expr</> = NULL</literal> to test for
3775 null values, so if you use that interface to access the database you
3776 might want to turn this option on. Since expressions of the
3777 form <literal><replaceable>expr</> = NULL</literal> always
3778 return the null value (using the correct interpretation) they are not
3779 very useful and do not appear often in normal applications, so
3780 this option does little harm in practice. But new users are
3781 frequently confused about the semantics of expressions
3782 involving null values, so this option is not on by default.
3786 Note that this option only affects the exact form <literal>= NULL</>,
3787 not other comparison operators or other expressions
3788 that are computationally equivalent to some expression
3789 involving the equals operator (such as <literal>IN</literal>).
3790 Thus, this option is not a general fix for bad programming.
3794 Refer to <xref linkend="functions-comparison"> for related information.
3803 <sect1 id="runtime-config-preset">
3804 <title>Preset Options</title>
3807 The following <quote>parameters</> are read-only, and are determined
3808 when <productname>PostgreSQL</productname> is compiled or when it is
3809 installed. As such, they have been excluded from the sample
3810 <filename>postgresql.conf</> file. These options report
3811 various aspects of <productname>PostgreSQL</productname> behavior
3812 that may be of interest to certain applications, particularly
3813 administrative front-ends.
3818 <varlistentry id="guc-block-size" xreflabel="block_size">
3819 <term><varname>block_size</varname> (<type>integer</type>)</term>
3821 <primary><varname>block_size</> configuration parameter</primary>
3825 Reports the size of a disk block. It is determined by the value
3826 of <literal>BLCKSZ</> when building the server. The default
3827 value is 8192 bytes. The meaning of some configuration
3828 variables (such as <xref linkend="guc-shared-buffers">) is
3829 influenced by <varname>block_size</varname>. See <xref
3830 linkend="runtime-config-resource"> for information.
3835 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
3836 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
3838 <primary><varname>integer_datetimes</> configuration parameter</primary>
3842 Reports whether <productname>PostgreSQL</productname> was built
3843 with support for 64-bit-integer dates and times. It is set by
3844 configuring with <literal>--enable-integer-datetimes</literal>
3845 when building <productname>PostgreSQL</productname>. The
3846 default value is <literal>off</literal>.
3851 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
3852 <term><varname>lc_collate</varname> (<type>string</type>)</term>
3854 <primary><varname>lc_collate</> configuration parameter</primary>
3858 Reports the locale in which sorting of textual data is done.
3859 See <xref linkend="locale"> for more information.
3860 The value is determined when the database cluster is initialized.
3865 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
3866 <term><varname>lc_ctype</varname> (<type>string</type>)</term>
3868 <primary><varname>lc_ctype</> configuration parameter</primary>
3872 Reports the locale that determines character classifications.
3873 See <xref linkend="locale"> for more information.
3874 The value is determined when the database cluster is initialized.
3875 Ordinarily this will be the same as <varname>lc_collate</varname>,
3876 but for special applications it might be set differently.
3881 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
3882 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
3884 <primary><varname>max_function_args</> configuration parameter</primary>
3888 Reports the maximum number of function arguments. It is determined by
3889 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
3890 default value is 100.
3895 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
3896 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
3898 <primary><varname>max_identifier_length</> configuration parameter</primary>
3902 Reports the maximum identifier length. It is determined as one
3903 less than the value of <literal>NAMEDATALEN</> when building
3904 the server. The default value of <literal>NAMEDATALEN</> is
3905 64; therefore the default
3906 <varname>max_identifier_length</varname> is 63.
3911 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
3912 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
3914 <primary><varname>max_index_keys</> configuration parameter</primary>
3918 Reports the maximum number of index keys. It is determined by
3919 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
3920 default value is 32.
3925 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
3926 <term><varname>server_encoding</varname> (<type>string</type>)</term>
3928 <primary><varname>server_encoding</> configuration parameter</primary>
3930 <indexterm><primary>character set</></>
3933 Reports the database encoding (character set).
3934 It is determined when the database is created. Ordinarily,
3935 clients need only be concerned with the value of <xref
3936 linkend="guc-client-encoding">.
3941 <varlistentry id="guc-server-version" xreflabel="server_version">
3942 <term><varname>server_version</varname> (<type>string</type>)</term>
3944 <primary><varname>server_version</> configuration parameter</primary>
3948 Reports the version number of the server. It is determined by the
3949 value of <literal>PG_VERSION</> when building the server.
3954 <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
3955 <term><varname>standard_conforming_strings</varname> (<type>boolean</type>)</term>
3956 <indexterm><primary>strings</><secondary>escape</></>
3958 <primary><varname>standard_conforming_strings</> configuration parameter</primary>
3962 Reports whether ordinary string literals
3963 (<literal>'...'</>) treat backslashes literally, as specified in
3964 the SQL standard. The value is currently always <literal>off</>,
3965 indicating that backslashes are treated as escapes. It is planned
3966 that this will change to <literal>on</> in a future
3967 <productname>PostgreSQL</productname> release when string literal
3968 syntax changes to meet the standard. Applications may check this
3969 parameter to determine how string literals will be processed.
3970 The presence of this parameter can also be taken as an indication
3971 that the escape string syntax (<literal>E'...'</>) is supported.
3979 <sect1 id="runtime-config-custom">
3980 <title>Customized Options</title>
3983 This feature was designed to allow options not normally known to
3984 <productname>PostgreSQL</productname> to be added by add-on modules
3985 (such as procedural languages). This allows add-on modules to be
3986 configured in the standard ways.
3991 <varlistentry id="guc-custom-variable-classes" xreflabel="custom_variable_classes">
3992 <term><varname>custom_variable_classes</varname> (<type>string</type>)</term>
3994 <primary><varname>custom_variable_classes</> configuration parameter</primary>
3998 This variable specifies one or several class names to be used for
3999 custom variables, in the form of a comma-separated list. A custom
4000 variable is a variable not normally known
4001 to <productname>PostgreSQL</productname> proper but used by some
4002 add-on module. Such variables must have names consisting of a class
4003 name, a dot, and a variable name. <varname>custom_variable_classes</>
4004 specifies all the class names in use in a particular installation.
4005 This option can only be set at server start or in the
4006 <filename>postgresql.conf</filename> configuration file.
4014 The difficulty with setting custom variables in
4015 <filename>postgresql.conf</> is that the file must be read before add-on
4016 modules have been loaded, and so custom variables would ordinarily be
4017 rejected as unknown. When <varname>custom_variable_classes</> is set,
4018 the server will accept definitions of arbitrary variables within each
4019 specified class. These variables will be treated as placeholders and
4020 will have no function until the module that defines them is loaded. When a
4021 module for a specific class is loaded, it will add the proper variable
4022 definitions for its class name, convert any placeholder
4023 values according to those definitions, and issue warnings for any
4024 placeholders of its class that remain (which presumably would be
4025 misspelled configuration variables).
4029 Here is an example of what <filename>postgresql.conf</> might contain
4030 when using custom variables:
4033 custom_variable_classes = 'plr,plperl'
4034 plr.path = '/usr/lib/R'
4035 plperl.use_strict = true
4036 plruby.use_strict = true # generates error: unknown class name
4041 <sect1 id="runtime-config-developer">
4042 <title>Developer Options</title>
4045 The following options are intended for work on the
4046 <productname>PostgreSQL</productname> source, and in some cases
4047 to assist with recovery of severely damaged databases. There
4048 should be no reason to use them in a production database setup.
4049 As such, they have been excluded from the sample
4050 <filename>postgresql.conf</> file. Note that many of these
4051 options require special source compilation flags to work at all.
4055 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
4056 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
4058 <primary><varname>debug_assertions</> configuration parameter</primary>
4062 Turns on various assertion checks. This is a debugging aid. If
4063 you are experiencing strange problems or crashes you might want
4064 to turn this on, as it might expose programming mistakes. To use
4065 this option, the macro <symbol>USE_ASSERT_CHECKING</symbol>
4066 must be defined when <productname>PostgreSQL</productname> is
4067 built (accomplished by the <command>configure</command> option
4068 <option>--enable-cassert</option>). Note that
4069 <varname>debug_assertions</varname> defaults to <literal>on</>
4070 if <productname>PostgreSQL</productname> has been built with
4076 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
4077 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
4079 <primary><varname>pre_auth_delay</> configuration parameter</primary>
4083 If nonzero, a delay of this many seconds occurs just after a new
4084 server process is forked, before it conducts the authentication
4085 process. This is intended to give an opportunity to attach to the
4086 server process with a debugger to trace down misbehavior in
4092 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
4093 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
4095 <primary><varname>trace_notify</> configuration parameter</primary>
4099 Generates a great amount of debugging output for the
4100 <command>LISTEN</command> and <command>NOTIFY</command>
4101 commands. <xref linkend="guc-client-min-messages"> or
4102 <xref linkend="guc-log-min-messages"> must be
4103 <literal>DEBUG1</literal> or lower to send this output to the
4104 client or server log, respectively.
4109 <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
4110 <term><varname>trace_sort</varname> (<type>boolean</type>)</term>
4112 <primary><varname>trace_sort</> configuration parameter</primary>
4116 If on, emit information about resource usage during sort operations.
4117 This option is only available if the <symbol>TRACE_SORT</symbol> macro
4118 was defined when <productname>PostgreSQL</productname> was compiled.
4119 (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
4125 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
4126 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
4127 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
4128 <term><varname>trace_lock_oidmin</varname> (<type>boolean</type>)</term>
4129 <term><varname>trace_lock_table</varname> (<type>boolean</type>)</term>
4130 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
4131 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
4134 Various other code tracing and debugging options.
4139 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
4140 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
4142 <primary><varname>wal_debug</> configuration parameter</primary>
4146 If on, emit WAL-related debugging output. This option is
4147 only available if the <symbol>WAL_DEBUG</symbol> macro was
4148 defined when <productname>PostgreSQL</productname> was
4154 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
4155 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
4157 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
4161 Detection of a damaged page header normally causes
4162 <productname>PostgreSQL</> to report an error, aborting the current
4163 command. Setting <varname>zero_damaged_pages</> to on causes
4164 the system to instead report a warning, zero out the damaged page,
4165 and continue processing. This behavior <emphasis>will destroy data</>,
4166 namely all the rows on the damaged page. But it allows you to get
4167 past the error and retrieve rows from any undamaged pages that may
4168 be present in the table. So it is useful for recovering data if
4169 corruption has occurred due to hardware or software error. You should
4170 generally not set this on until you have given up hope of recovering
4171 data from the damaged page(s) of a table. The
4172 default setting is <literal>off</>, and it can only be changed
4179 <sect1 id="runtime-config-short">
4180 <title>Short Options</title>
4183 For convenience there are also single letter command-line option switches
4184 available for some parameters. They are described in <xref
4185 linkend="runtime-config-short-table">.
4188 <table id="runtime-config-short-table">
4189 <title>Short option key</title>
4193 <entry>Short option</entry>
4194 <entry>Equivalent</entry>
4200 <entry><option>-B <replaceable>x</replaceable></option></entry>
4201 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
4204 <entry><option>-d <replaceable>x</replaceable></option></entry>
4205 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
4208 <entry><option>-F</option></entry>
4209 <entry><literal>fsync = off</></entry>
4212 <entry><option>-h <replaceable>x</replaceable></option></entry>
4213 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
4216 <entry><option>-i</option></entry>
4217 <entry><literal>listen_addresses = '*'</></entry>
4220 <entry><option>-k <replaceable>x</replaceable></option></entry>
4221 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
4224 <entry><option>-l</option></entry>
4225 <entry><literal>ssl = on</></entry>
4228 <entry><option>-N <replaceable>x</replaceable></option></entry>
4229 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
4232 <entry><option>-p <replaceable>x</replaceable></option></entry>
4233 <entry><literal>port = <replaceable>x</replaceable></></entry>
4238 <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
4239 <option>-fm</option>, <option>-fn</option>,
4240 <option>-fs</option>, <option>-ft</option><footnote
4241 id="fn.runtime-config-short">
4243 For historical reasons, these options must be passed to
4244 the individual server process via the <option>-o</option>
4245 <command>postmaster</command> option, for example,
4247 $ <userinput>postmaster -o '-S 1024 -s'</userinput>
4249 or via <envar>PGOPTIONS</envar> from the client side, as
4255 <literal>enable_bitmapscan = off</>,
4256 <literal>enable_hashjoin = off</>,
4257 <literal>enable_indexscan = off</>,
4258 <literal>enable_mergejoin = off</>,
4259 <literal>enable_nestloop = off</>,
4260 <literal>enable_seqscan = off</>,
4261 <literal>enable_tidscan = off</>
4266 <entry><option>-s</option><footnoteref linkend="fn.runtime-config-short"></entry>
4267 <entry><literal>log_statement_stats = on</></entry>
4271 <entry><option>-S <replaceable>x</replaceable></option><footnoteref linkend="fn.runtime-config-short">
4273 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
4277 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option><footnoteref linkend="fn.runtime-config-short"></entry>
4278 <entry><literal>log_parser_stats = on</>,
4279 <literal>log_planner_stats = on</>,
4280 <literal>log_executor_stats = on</></entry>
4289 <!-- Keep this comment at the end of the file
4294 sgml-minimize-attributes:nil
4295 sgml-always-quote-attributes:t
4298 sgml-parent-document:nil
4299 sgml-default-dtd-file:"./reference.ced"
4300 sgml-exposed-tags:nil
4301 sgml-local-catalogs:("/usr/lib/sgml/catalog")
4302 sgml-local-ecat-files:nil