1 <!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.177 2008/05/02 21:26:09 tgl Exp $ -->
3 <chapter Id="runtime-config">
4 <title>Server Configuration</title>
7 <primary>configuration</primary>
8 <secondary>of the server</secondary>
12 There are many configuration parameters that affect the behavior of
13 the database system. In the first section of this chapter, we
14 describe how to set configuration parameters. The subsequent sections
15 discuss each parameter in detail.
18 <sect1 id="config-setting">
19 <title>Setting Parameters</title>
22 All parameter names are case-insensitive. Every parameter takes a
23 value of one of four types: Boolean, integer, floating point,
24 or string. Boolean values can be written as <literal>ON</literal>,
25 <literal>OFF</literal>, <literal>TRUE</literal>,
26 <literal>FALSE</literal>, <literal>YES</literal>,
27 <literal>NO</literal>, <literal>1</literal>, <literal>0</literal>
28 (all case-insensitive) or any unambiguous prefix of these.
32 Some settings specify a memory or time value. Each of these has an
33 implicit unit, which is either kilobytes, blocks (typically eight
34 kilobytes), milliseconds, seconds, or minutes. Default units can be
35 queried by referencing <literal>pg_settings.unit</>. For convenience,
36 a different unit can also be specified explicitly. Valid memory units
37 are <literal>kB</literal> (kilobytes), <literal>MB</literal>
38 (megabytes), and <literal>GB</literal> (gigabytes); valid time units
39 are <literal>ms</literal> (milliseconds), <literal>s</literal>
40 (seconds), <literal>min</literal> (minutes), <literal>h</literal>
41 (hours), and <literal>d</literal> (days). Note that the multiplier
42 for memory units is 1024, not 1000.
46 One way to set these parameters is to edit the file
47 <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>,
48 which is normally kept in the data directory. (<application>initdb</>
49 installs a default copy there.) An example of what this file might look
54 log_destination = 'syslog'
55 search_path = '"$user", public'
56 shared_buffers = 128MB
58 One parameter is specified per line. The equal sign between name and
59 value is optional. Whitespace is insignificant and blank lines are
60 ignored. Hash marks (<literal>#</literal>) introduce comments
61 anywhere. Parameter values that are not simple identifiers or
62 numbers must be single-quoted. To embed a single quote in a parameter
63 value, write either two quotes (preferred) or backslash-quote.
68 <primary><literal>include</></primary>
69 <secondary>in configuration file</secondary>
71 In addition to parameter settings, the <filename>postgresql.conf</>
72 file can contain <firstterm>include directives</>, which specify
73 another file to read and process as if it were inserted into the
74 configuration file at this point. Include directives simply look like:
78 If the file name is not an absolute path, it is taken as relative to
79 the directory containing the referencing configuration file.
80 Inclusions can be nested.
85 <primary>SIGHUP</primary>
87 The configuration file is reread whenever the main server process receives a
88 <systemitem>SIGHUP</> signal (which is most easily sent by means
89 of <literal>pg_ctl reload</>). The main server process
90 also propagates this signal to all currently running server
91 processes so that existing sessions also get the new
92 value. Alternatively, you can send the signal to a single server
93 process directly. Some parameters can only be set at server start;
94 any changes to their entries in the configuration file will be ignored
95 until the server is restarted.
99 A second way to set these configuration parameters is to give them
100 as a command-line option to the <command>postgres</command> command, such as:
102 postgres -c log_connections=yes -c log_destination='syslog'
104 Command-line options override any conflicting settings in
105 <filename>postgresql.conf</filename>. Note that this means you won't
106 be able to change the value on-the-fly by editing
107 <filename>postgresql.conf</filename>, so while the command-line
108 method might be convenient, it can cost you flexibility later.
112 Occasionally it is useful to give a command line option to
113 one particular session only. The environment variable
114 <envar>PGOPTIONS</envar> can be used for this purpose on the
117 env PGOPTIONS='-c geqo=off' psql
119 (This works for any <application>libpq</>-based client application, not
120 just <application>psql</application>.) Note that this won't work for
121 parameters that are fixed when the server is started or that must be
122 specified in <filename>postgresql.conf</filename>.
126 Furthermore, it is possible to assign a set of parameter settings to
127 a user or a database. Whenever a session is started, the default
128 settings for the user and database involved are loaded. The
129 commands <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
130 and <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">,
131 respectively, are used to configure these settings. Per-database
132 settings override anything received from the
133 <command>postgres</command> command-line or the configuration
134 file, and in turn are overridden by per-user settings; both are
135 overridden by per-session settings.
139 Some parameters can be changed in individual <acronym>SQL</acronym>
140 sessions with the <xref linkend="SQL-SET" endterm="SQL-SET-title">
141 command, for example:
143 SET ENABLE_SEQSCAN TO OFF;
145 If <command>SET</> is allowed, it overrides all other sources of
146 values for the parameter. Some parameters cannot be changed via
147 <command>SET</command>: for example, if they control behavior that
148 cannot be changed without restarting the entire
149 <productname>PostgreSQL</productname> server. Also, some parameters can
150 be modified via <command>SET</command> or <command>ALTER</> by superusers,
151 but not by ordinary users.
155 The <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title">
156 command allows inspection of the current values of all parameters.
160 The virtual table <structname>pg_settings</structname>
161 (described in <xref linkend="view-pg-settings">) also allows
162 displaying and updating session run-time parameters. It is equivalent
163 to <command>SHOW</> and <command>SET</>, but can be more convenient
164 to use because it can be joined with other tables, or selected from using
165 any desired selection condition. It also contains more information about
166 what values are allowed for the parameters.
170 <sect1 id="runtime-config-file-locations">
171 <title>File Locations</title>
174 In addition to the <filename>postgresql.conf</filename> file
175 already mentioned, <productname>PostgreSQL</productname> uses
176 two other manually-edited configuration files, which control
177 client authentication (their use is discussed in <xref
178 linkend="client-authentication">). By default, all three
179 configuration files are stored in the database cluster's data
180 directory. The parameters described in this section allow the
181 configuration files to be placed elsewhere. (Doing so can ease
182 administration. In particular it is often easier to ensure that
183 the configuration files are properly backed-up when they are
188 <varlistentry id="guc-data-directory" xreflabel="data_directory">
189 <term><varname>data_directory</varname> (<type>string</type>)</term>
191 <primary><varname>data_directory</> configuration parameter</primary>
195 Specifies the directory to use for data storage.
196 This parameter can only be set at server start.
201 <varlistentry id="guc-config-file" xreflabel="config_file">
202 <term><varname>config_file</varname> (<type>string</type>)</term>
204 <primary><varname>config_file</> configuration parameter</primary>
208 Specifies the main server configuration file
209 (customarily called <filename>postgresql.conf</>).
210 This parameter can only be set on the <command>postgres</command> command line.
215 <varlistentry id="guc-hba-file" xreflabel="hba_file">
216 <term><varname>hba_file</varname> (<type>string</type>)</term>
218 <primary><varname>hba_file</> configuration parameter</primary>
222 Specifies the configuration file for host-based authentication
223 (customarily called <filename>pg_hba.conf</>).
224 This parameter can only be set at server start.
229 <varlistentry id="guc-ident-file" xreflabel="ident_file">
230 <term><varname>ident_file</varname> (<type>string</type>)</term>
232 <primary><varname>ident_file</> configuration parameter</primary>
236 Specifies the configuration file for
237 <application>ident</> authentication
238 (customarily called <filename>pg_ident.conf</>).
239 This parameter can only be set at server start.
244 <varlistentry id="guc-external-pid-file" xreflabel="external_pid_file">
245 <term><varname>external_pid_file</varname> (<type>string</type>)</term>
247 <primary><varname>external_pid_file</> configuration parameter</primary>
251 Specifies the name of an additional process-id (PID) file that the
252 server should create for use by server administration programs.
253 This parameter can only be set at server start.
260 In a default installation, none of the above parameters are set
261 explicitly. Instead, the
262 data directory is specified by the <option>-D</option> command-line
263 option or the <envar>PGDATA</envar> environment variable, and the
264 configuration files are all found within the data directory.
268 If you wish to keep the configuration files elsewhere than the
269 data directory, the <command>postgres</command> <option>-D</option>
270 command-line option or <envar>PGDATA</envar> environment variable
271 must point to the directory containing the configuration files,
272 and the <varname>data_directory</> parameter must be set in
273 <filename>postgresql.conf</filename> (or on the command line) to show
274 where the data directory is actually located. Notice that
275 <varname>data_directory</> overrides <option>-D</option> and
276 <envar>PGDATA</envar> for the location
277 of the data directory, but not for the location of the configuration
282 If you wish, you can specify the configuration file names and locations
283 individually using the parameters <varname>config_file</>,
284 <varname>hba_file</> and/or <varname>ident_file</>.
285 <varname>config_file</> can only be specified on the
286 <command>postgres</command> command line, but the others can be
287 set within the main configuration file. If all three parameters plus
288 <varname>data_directory</> are explicitly set, then it is not necessary
289 to specify <option>-D</option> or <envar>PGDATA</envar>.
293 When setting any of these parameters, a relative path will be interpreted
294 with respect to the directory in which <command>postgres</command>
299 <sect1 id="runtime-config-connection">
300 <title>Connections and Authentication</title>
302 <sect2 id="runtime-config-connection-settings">
303 <title>Connection Settings</title>
307 <varlistentry id="guc-listen-addresses" xreflabel="listen_addresses">
308 <term><varname>listen_addresses</varname> (<type>string</type>)</term>
310 <primary><varname>listen_addresses</> configuration parameter</primary>
314 Specifies the TCP/IP address(es) on which the server is
315 to listen for connections from client applications.
316 The value takes the form of a comma-separated list of host names
317 and/or numeric IP addresses. The special entry <literal>*</>
318 corresponds to all available IP interfaces.
319 If the list is empty, the server does not listen on any IP interface
320 at all, in which case only Unix-domain sockets can be used to connect
322 The default value is <systemitem class="systemname">localhost</>,
323 which allows only local <quote>loopback</> connections to be made.
324 This parameter can only be set at server start.
329 <varlistentry id="guc-port" xreflabel="port">
330 <term><varname>port</varname> (<type>integer</type>)</term>
332 <primary><varname>port</> configuration parameter</primary>
336 The TCP port the server listens on; 5432 by default. Note that the
337 same port number is used for all IP addresses the server listens on.
338 This parameter can only be set at server start.
343 <varlistentry id="guc-max-connections" xreflabel="max_connections">
344 <term><varname>max_connections</varname> (<type>integer</type>)</term>
346 <primary><varname>max_connections</> configuration parameter</primary>
350 Determines the maximum number of concurrent connections to the
351 database server. The default is typically 100 connections, but
352 might be less if your kernel settings will not support it (as
353 determined during <application>initdb</>). This parameter can
354 only be set at server start.
358 Increasing this parameter might cause <productname>PostgreSQL</>
359 to request more <systemitem class="osname">System V</> shared
360 memory or semaphores than your operating system's default configuration
361 allows. See <xref linkend="sysvipc"> for information on how to
362 adjust those parameters, if necessary.
367 <varlistentry id="guc-superuser-reserved-connections"
368 xreflabel="superuser_reserved_connections">
369 <term><varname>superuser_reserved_connections</varname>
370 (<type>integer</type>)</term>
372 <primary><varname>superuser_reserved_connections</> configuration parameter</primary>
376 Determines the number of connection <quote>slots</quote> that
377 are reserved for connections by <productname>PostgreSQL</>
378 superusers. At most <xref linkend="guc-max-connections">
379 connections can ever be active simultaneously. Whenever the
380 number of active concurrent connections is at least
381 <varname>max_connections</> minus
382 <varname>superuser_reserved_connections</varname>, new
383 connections will be accepted only for superusers.
387 The default value is three connections. The value must be less
388 than the value of <varname>max_connections</varname>. This
389 parameter can only be set at server start.
394 <varlistentry id="guc-unix-socket-directory" xreflabel="unix_socket_directory">
395 <term><varname>unix_socket_directory</varname> (<type>string</type>)</term>
397 <primary><varname>unix_socket_directory</> configuration parameter</primary>
401 Specifies the directory of the Unix-domain socket on which the
402 server is to listen for
403 connections from client applications. The default is normally
404 <filename>/tmp</filename>, but can be changed at build time.
405 This parameter can only be set at server start.
410 <varlistentry id="guc-unix-socket-group" xreflabel="unix_socket_group">
411 <term><varname>unix_socket_group</varname> (<type>string</type>)</term>
413 <primary><varname>unix_socket_group</> configuration parameter</primary>
417 Sets the owning group of the Unix-domain socket. (The owning
418 user of the socket is always the user that starts the
419 server.) In combination with the parameter
420 <varname>unix_socket_permissions</varname> this can be used as
421 an additional access control mechanism for Unix-domain connections.
422 By default this is the empty string, which selects the default
423 group for the current user. This parameter can only be set at
429 <varlistentry id="guc-unix-socket-permissions" xreflabel="unix_socket_permissions">
430 <term><varname>unix_socket_permissions</varname> (<type>integer</type>)</term>
432 <primary><varname>unix_socket_permissions</> configuration parameter</primary>
436 Sets the access permissions of the Unix-domain socket. Unix-domain
437 sockets use the usual Unix file system permission set.
438 The parameter value is expected to be a numeric mode
439 specification in the form accepted by the
440 <function>chmod</function> and <function>umask</function>
441 system calls. (To use the customary octal format the number
442 must start with a <literal>0</literal> (zero).)
446 The default permissions are <literal>0777</literal>, meaning
447 anyone can connect. Reasonable alternatives are
448 <literal>0770</literal> (only user and group, see also
449 <varname>unix_socket_group</varname>) and <literal>0700</literal>
450 (only user). (Note that for a Unix-domain socket, only write
451 permission matters and so there is no point in setting or revoking
452 read or execute permissions.)
456 This access control mechanism is independent of the one
457 described in <xref linkend="client-authentication">.
461 This parameter can only be set at server start.
466 <varlistentry id="guc-bonjour-name" xreflabel="bonjour_name">
467 <term><varname>bonjour_name</varname> (<type>string</type>)</term>
469 <primary><varname>bonjour_name</> configuration parameter</primary>
473 Specifies the <productname>Bonjour</productname> broadcast
474 name. The computer name is used if this parameter is set to the
475 empty string <literal>''</> (which is the default). This parameter is
476 ignored if the server was not compiled with
477 <productname>Bonjour</productname> support.
478 This parameter can only be set at server start.
483 <varlistentry id="guc-tcp-keepalives-idle" xreflabel="tcp_keepalives_idle">
484 <term><varname>tcp_keepalives_idle</varname> (<type>integer</type>)</term>
486 <primary><varname>tcp_keepalives_idle</> configuration parameter</primary>
490 On systems that support the <symbol>TCP_KEEPIDLE</symbol> socket option, specifies the
491 number of seconds between sending keepalives on an otherwise idle
492 connection. A value of zero uses the system default. If <symbol>TCP_KEEPIDLE</symbol> is
493 not supported, this parameter must be zero. This parameter is ignored for
494 connections made via a Unix-domain socket.
499 <varlistentry id="guc-tcp-keepalives-interval" xreflabel="tcp_keepalives_interval">
500 <term><varname>tcp_keepalives_interval</varname> (<type>integer</type>)</term>
502 <primary><varname>tcp_keepalives_interval</> configuration parameter</primary>
506 On systems that support the <symbol>TCP_KEEPINTVL</symbol> socket option, specifies how
507 long, in seconds, to wait for a response to a keepalive before
508 retransmitting. A value of zero uses the system default. If <symbol>TCP_KEEPINTVL</symbol>
509 is not supported, this parameter must be zero. This parameter is ignored
510 for connections made via a Unix-domain socket.
515 <varlistentry id="guc-tcp-keepalives-count" xreflabel="tcp_keepalives_count">
516 <term><varname>tcp_keepalives_count</varname> (<type>integer</type>)</term>
518 <primary><varname>tcp_keepalives_count</> configuration parameter</primary>
522 On systems that support the <symbol>TCP_KEEPCNT</symbol> socket option, specifies how
523 many keepalives can be lost before the connection is considered dead.
524 A value of zero uses the system default. If <symbol>TCP_KEEPCNT</symbol> is not
525 supported, this parameter must be zero. This parameter is ignored
526 for connections made via a Unix-domain socket.
533 <sect2 id="runtime-config-connection-security">
534 <title>Security and Authentication</title>
537 <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
538 <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
539 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
540 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
542 <primary><varname>authentication_timeout</> configuration parameter</primary>
547 Maximum time to complete client authentication, in seconds. If a
548 would-be client has not completed the authentication protocol in
549 this much time, the server breaks the connection. This prevents
550 hung clients from occupying a connection indefinitely.
551 The default is one minute (<literal>1m</>).
552 This parameter can only be set in the <filename>postgresql.conf</>
553 file or on the server command line.
558 <varlistentry id="guc-ssl" xreflabel="ssl">
559 <term><varname>ssl</varname> (<type>boolean</type>)</term>
561 <primary><varname>ssl</> configuration parameter</primary>
565 Enables <acronym>SSL</> connections. Please read
566 <xref linkend="ssl-tcp"> before using this. The default
567 is <literal>off</>. This parameter can only be set at server
573 <varlistentry id="guc-ssl-ciphers" xreflabel="ssl_ciphers">
574 <term><varname>ssl_ciphers</varname> (<type>string</type>)</term>
576 <primary><varname>ssl_ciphers</> configuration parameter</primary>
580 Specifies a list of <acronym>SSL</> ciphers that are allowed to be
581 used on secure connections. See the <application>openssl</>
582 manual page for a list of supported ciphers.
587 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
588 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
590 <primary><varname>password_encryption</> configuration parameter</primary>
594 When a password is specified in <xref
595 linkend="sql-createuser" endterm="sql-createuser-title"> or
596 <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
597 without writing either <literal>ENCRYPTED</> or
598 <literal>UNENCRYPTED</>, this parameter determines whether the
599 password is to be encrypted. The default is <literal>on</>
600 (encrypt the password).
605 <varlistentry id="guc-krb-realm" xreflabel="krb_realm">
606 <term><varname>krb_realm</varname> (<type>string</type>)</term>
608 <primary><varname>krb_realm</> configuration parameter</primary>
612 Sets the realm to match Kerberos, GSSAPI and SSPI user names against.
613 See <xref linkend="kerberos-auth">, <xref linkend="gssapi-auth"> or
614 <xref linkend="sspi-auth"> for details. This parameter can only be
620 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
621 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
623 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
627 Sets the location of the Kerberos server key file. See
628 <xref linkend="kerberos-auth"> or <xref linkend="gssapi-auth">
629 for details. This parameter can only be set at server start.
634 <varlistentry id="guc-krb-srvname" xreflabel="krb_srvname">
635 <term><varname>krb_srvname</varname> (<type>string</type>)</term>
637 <primary><varname>krb_srvname</> configuration parameter</primary>
641 Sets the Kerberos service name. See <xref linkend="kerberos-auth">
642 for details. This parameter can only be set at server start.
647 <varlistentry id="guc-krb-server-hostname" xreflabel="krb_server_hostname">
648 <term><varname>krb_server_hostname</varname> (<type>string</type>)</term>
650 <primary><varname>krb_server_hostname</> configuration parameter</primary>
654 Sets the host name part of the service principal.
655 This, combined with <varname>krb_srvname</>, is used to generate
656 the complete service principal, that is
657 <varname>krb_srvname</><literal>/</><varname>krb_server_hostname</><literal>@</>REALM.
658 If not set, the default is the server host name. See <xref linkend="kerberos-auth">
659 for details. This parameter can only be set at server start.
664 <varlistentry id="guc-krb-caseins-users" xreflabel="krb_caseins_users">
665 <term><varname>krb_caseins_users</varname> (<type>boolean</type>)</term>
667 <primary><varname>krb_caseins_users</varname> configuration parameter</primary>
671 Sets whether Kerberos and GSSAPI user names should be treated
673 The default is <literal>off</> (case sensitive). This parameter
674 can only be set at server start.
679 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
680 <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
682 <primary><varname>db_user_namespace</> configuration parameter</primary>
686 This parameter enables per-database user names. It is off by default.
687 This parameter can only be set in the <filename>postgresql.conf</>
688 file or on the server command line.
692 If this is on, you should create users as <literal>username@dbname</>.
693 When <literal>username</> is passed by a connecting client,
694 <literal>@</> and the database name are appended to the user
695 name and that database-specific user name is looked up by the
696 server. Note that when you create users with names containing
697 <literal>@</> within the SQL environment, you will need to
702 With this parameter enabled, you can still create ordinary global
703 users. Simply append <literal>@</> when specifying the user
704 name in the client. The <literal>@</> will be stripped off
705 before the user name is looked up by the server.
710 This feature is intended as a temporary measure until a
711 complete solution is found. At that time, this option will
722 <sect1 id="runtime-config-resource">
723 <title>Resource Consumption</title>
725 <sect2 id="runtime-config-resource-memory">
726 <title>Memory</title>
729 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
730 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
732 <primary><varname>shared_buffers</> configuration parameter</primary>
736 Sets the amount of memory the database server uses for shared
737 memory buffers. The default is typically 32 megabytes
738 (<literal>32MB</>), but might be less if your kernel settings will
739 not support it (as determined during <application>initdb</>).
740 This setting must be at least 128 kilobytes. (Non-default
741 values of <symbol>BLCKSZ</symbol> change the minimum.) However,
742 settings significantly higher than the minimum are usually needed
743 for good performance. Several tens of megabytes are recommended
744 for production installations. This parameter can only be set at
749 Increasing this parameter might cause <productname>PostgreSQL</>
750 to request more <systemitem class="osname">System V</> shared
751 memory than your operating system's default configuration
752 allows. See <xref linkend="sysvipc"> for information on how to
753 adjust those parameters, if necessary.
758 <varlistentry id="guc-temp-buffers" xreflabel="temp_buffers">
759 <term><varname>temp_buffers</varname> (<type>integer</type>)</term>
761 <primary><varname>temp_buffers</> configuration parameter</primary>
765 Sets the maximum number of temporary buffers used by each database
766 session. These are session-local buffers used only for access to
767 temporary tables. The default is eight megabytes
768 (<literal>8MB</>). The setting can be changed within individual
769 sessions, but only up until the first use of temporary tables
770 within a session; subsequent attempts to change the value will
771 have no effect on that session.
775 A session will allocate temporary buffers as needed up to the limit
776 given by <varname>temp_buffers</>. The cost of setting a large
777 value in sessions that do not actually need a lot of temporary
778 buffers is only a buffer descriptor, or about 64 bytes, per
779 increment in <varname>temp_buffers</>. However if a buffer is
780 actually used an additional 8192 bytes will be consumed for it
781 (or in general, <symbol>BLCKSZ</symbol> bytes).
786 <varlistentry id="guc-max-prepared-transactions" xreflabel="max_prepared_transactions">
787 <term><varname>max_prepared_transactions</varname> (<type>integer</type>)</term>
789 <primary><varname>max_prepared_transactions</> configuration parameter</primary>
793 Sets the maximum number of transactions that can be in the
794 <quote>prepared</> state simultaneously (see <xref
795 linkend="sql-prepare-transaction"
796 endterm="sql-prepare-transaction-title">).
797 Setting this parameter to zero disables the prepared-transaction
799 The default is five transactions.
800 This parameter can only be set at server start.
804 If you are not using prepared transactions, this parameter may as
805 well be set to zero. If you are using them, you will probably
806 want <varname>max_prepared_transactions</varname> to be at least
807 as large as <xref linkend="guc-max-connections">, to avoid unwanted
808 failures at the prepare step.
812 Increasing this parameter might cause <productname>PostgreSQL</>
813 to request more <systemitem class="osname">System V</> shared
814 memory than your operating system's default configuration
815 allows. See <xref linkend="sysvipc"> for information on how to
816 adjust those parameters, if necessary.
821 <varlistentry id="guc-work-mem" xreflabel="work_mem">
822 <term><varname>work_mem</varname> (<type>integer</type>)</term>
824 <primary><varname>work_mem</> configuration parameter</primary>
828 Specifies the amount of memory to be used by internal sort operations
829 and hash tables before switching to temporary disk files. The value
830 defaults to one megabyte (<literal>1MB</>).
831 Note that for a complex query, several sort or hash operations might be
832 running in parallel; each one will be allowed to use as much memory
833 as this value specifies before it starts to put data into temporary
834 files. Also, several running sessions could be doing such operations
835 concurrently. So the total memory used could be many
836 times the value of <varname>work_mem</varname>; it is necessary to
837 keep this fact in mind when choosing the value. Sort operations are
838 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
840 Hash tables are used in hash joins, hash-based aggregation, and
841 hash-based processing of <literal>IN</> subqueries.
846 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
847 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
849 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
853 Specifies the maximum amount of memory to be used in maintenance
854 operations, such as <command>VACUUM</command>, <command>CREATE
855 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults
856 to 16 megabytes (<literal>16MB</>). Since only one of these
857 operations can be executed at a time by a database session, and
858 an installation normally doesn't have many of them running
859 concurrently, it's safe to set this value significantly larger
860 than <varname>work_mem</varname>. Larger settings might improve
861 performance for vacuuming and for restoring database dumps.
866 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
867 <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
869 <primary><varname>max_stack_depth</> configuration parameter</primary>
873 Specifies the maximum safe depth of the server's execution stack.
874 The ideal setting for this parameter is the actual stack size limit
875 enforced by the kernel (as set by <literal>ulimit -s</> or local
876 equivalent), less a safety margin of a megabyte or so. The safety
877 margin is needed because the stack depth is not checked in every
878 routine in the server, but only in key potentially-recursive routines
879 such as expression evaluation. The default setting is two
880 megabytes (<literal>2MB</>), which is conservatively small and
881 unlikely to risk crashes. However, it might be too small to allow
882 execution of complex functions. Only superusers can change this
887 Setting <varname>max_stack_depth</> higher than
888 the actual kernel limit will mean that a runaway recursive function
889 can crash an individual backend process. On platforms where
890 <productname>PostgreSQL</productname> can determine the kernel limit,
891 it will not let you set this variable to an unsafe value. However,
892 not all platforms provide the information, so caution is recommended
893 in selecting a value.
900 <sect2 id="runtime-config-resource-fsm">
901 <title>Free Space Map</title>
904 <primary>free space map</primary>
908 These parameters control the size of the shared <firstterm>free space
909 map</> (<acronym>FSM</>), which tracks the locations of unused space in the database.
910 An undersized free space map can cause the database to consume
911 increasing amounts of disk space over time, because free space that
912 is not in the map cannot be re-used; instead <productname>PostgreSQL</>
913 will request more disk space from the operating system when it needs
915 The last few lines displayed by a database-wide <command>VACUUM VERBOSE</>
916 command can help in determining if the current settings are adequate.
917 A <literal>NOTICE</> message is also printed during such an operation
918 if the current settings are too low.
922 Increasing these parameters might cause <productname>PostgreSQL</>
923 to request more <systemitem class="osname">System V</> shared
924 memory than your operating system's default configuration
925 allows. See <xref linkend="sysvipc"> for information on how to
926 adjust those parameters, if necessary.
930 <varlistentry id="guc-max-fsm-pages" xreflabel="max_fsm_pages">
931 <term><varname>max_fsm_pages</varname> (<type>integer</type>)</term>
933 <primary><varname>max_fsm_pages</> configuration parameter</primary>
937 Sets the maximum number of disk pages for which free space will
938 be tracked in the shared free-space map. Six bytes of shared memory
939 are consumed for each page slot. This setting must be at least
940 16 * <varname>max_fsm_relations</varname>. The default is chosen
941 by <application>initdb</> depending on the amount of available memory,
942 and can range from 20k to 200k pages.
943 This parameter can only be set at server start.
948 <varlistentry id="guc-max-fsm-relations" xreflabel="max_fsm_relations">
949 <term><varname>max_fsm_relations</varname> (<type>integer</type>)</term>
951 <primary><varname>max_fsm_relations</> configuration parameter</primary>
955 Sets the maximum number of relations (tables and indexes) for which
956 free space will be tracked in the shared free-space map. Roughly
957 seventy bytes of shared memory are consumed for each slot.
958 The default is one thousand relations.
959 This parameter can only be set at server start.
968 See the <xref linkend="sql-vacuum" endterm="sql-vacuum-title">
969 command for information on setting this parameter.
975 <sect2 id="runtime-config-resource-kernel">
976 <title>Kernel Resource Usage</title>
979 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
980 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
982 <primary><varname>max_files_per_process</> configuration parameter</primary>
986 Sets the maximum number of simultaneously open files allowed to each
987 server subprocess. The default is one thousand files. If the kernel is enforcing
988 a safe per-process limit, you don't need to worry about this setting.
989 But on some platforms (notably, most BSD systems), the kernel will
990 allow individual processes to open many more files than the system
991 can really support when a large number of processes all try to open
992 that many files. If you find yourself seeing <quote>Too many open
993 files</> failures, try reducing this setting.
994 This parameter can only be set at server start.
999 <varlistentry id="guc-shared-preload-libraries" xreflabel="shared_preload_libraries">
1000 <term><varname>shared_preload_libraries</varname> (<type>string</type>)</term>
1002 <primary><varname>shared_preload_libraries</> configuration parameter</primary>
1006 This variable specifies one or more shared libraries that are
1007 to be preloaded at server start. If more than one library is to be
1008 loaded, separate their names with commas. For example,
1009 <literal>'$libdir/mylib'</literal> would cause
1010 <literal>mylib.so</> (or on some platforms,
1011 <literal>mylib.sl</>) to be preloaded from the installation's
1012 standard library directory.
1013 This parameter can only be set at server start.
1017 <productname>PostgreSQL</productname> procedural language
1018 libraries can be preloaded in this way, typically by using the
1019 syntax <literal>'$libdir/plXXX'</literal> where
1020 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
1021 <literal>tcl</>, or <literal>python</>.
1025 By preloading a shared library, the library startup time is avoided
1026 when the library is first used. However, the time to start each new
1027 server process might increase slightly, even if that process never
1028 uses the library. So this parameter is recommended only for
1029 libraries that will be used in most sessions.
1034 On Windows hosts, preloading a library at server start will not reduce
1035 the time required to start each new server process; each server process
1036 will re-load all preload libraries. However, <varname>shared_preload_libraries
1037 </varname> is still useful on Windows hosts because some shared libraries may
1038 need to perform certain operations that only take place at postmaster start
1039 (for example, a shared library may need to reserve lightweight locks
1040 or shared memory and you can't do that after the postmaster has started).
1044 If a specified library is not found,
1045 the server will fail to start.
1049 Every PostgreSQL-supported library has a <quote>magic
1050 block</> that is checked to guarantee compatibility.
1051 For this reason, non-PostgreSQL libraries cannot be
1060 <sect2 id="runtime-config-resource-vacuum-cost">
1061 <title id="runtime-config-resource-vacuum-cost-title">
1062 Cost-Based Vacuum Delay
1066 During the execution of <xref linkend="sql-vacuum"
1067 endterm="sql-vacuum-title"> and <xref linkend="sql-analyze"
1068 endterm="sql-analyze-title"> commands, the system maintains an
1069 internal counter that keeps track of the estimated cost of the
1070 various I/O operations that are performed. When the accumulated
1071 cost reaches a limit (specified by
1072 <varname>vacuum_cost_limit</varname>), the process performing
1073 the operation will sleep for a while (specified by
1074 <varname>vacuum_cost_delay</varname>). Then it will reset the
1075 counter and continue execution.
1079 The intent of this feature is to allow administrators to reduce
1080 the I/O impact of these commands on concurrent database
1081 activity. There are many situations in which it is not very
1082 important that maintenance commands like
1083 <command>VACUUM</command> and <command>ANALYZE</command> finish
1084 quickly; however, it is usually very important that these
1085 commands do not significantly interfere with the ability of the
1086 system to perform other database operations. Cost-based vacuum
1087 delay provides a way for administrators to achieve this.
1091 This feature is disabled by default. To enable it, set the
1092 <varname>vacuum_cost_delay</varname> variable to a nonzero
1097 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1098 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)</term>
1100 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1104 The length of time, in milliseconds, that the process will sleep
1105 when the cost limit has been exceeded.
1106 The default value is zero, which disables the cost-based vacuum
1107 delay feature. Positive values enable cost-based vacuuming.
1108 Note that on many systems, the effective resolution
1109 of sleep delays is 10 milliseconds; setting
1110 <varname>vacuum_cost_delay</varname> to a value that is
1111 not a multiple of 10 might have the same results as setting it
1112 to the next higher multiple of 10.
1117 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1118 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
1120 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1124 The estimated cost for vacuuming a buffer found in the shared buffer
1125 cache. It represents the cost to lock the buffer pool, lookup
1126 the shared hash table and scan the content of the page. The
1127 default value is one.
1132 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1133 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
1135 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1139 The estimated cost for vacuuming a buffer that has to be read from
1140 disk. This represents the effort to lock the buffer pool,
1141 lookup the shared hash table, read the desired block in from
1142 the disk and scan its content. The default value is 10.
1147 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1148 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
1150 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1154 The estimated cost charged when vacuum modifies a block that was
1155 previously clean. It represents the extra I/O required to
1156 flush the dirty block out to disk again. The default value is
1162 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1163 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
1165 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1169 The accumulated cost that will cause the vacuuming process to sleep.
1170 The default value is 200.
1178 There are certain operations that hold critical locks and should
1179 therefore complete as quickly as possible. Cost-based vacuum
1180 delays do not occur during such operations. Therefore it is
1181 possible that the cost accumulates far higher than the specified
1182 limit. To avoid uselessly long delays in such cases, the actual
1183 delay is calculated as <varname>vacuum_cost_delay</varname> *
1184 <varname>accumulated_balance</varname> /
1185 <varname>vacuum_cost_limit</varname> with a maximum of
1186 <varname>vacuum_cost_delay</varname> * 4.
1191 <sect2 id="runtime-config-resource-background-writer">
1192 <title>Background Writer</title>
1195 There is a separate server
1196 process called the <firstterm>background writer</>, whose function
1197 is to issue writes of <quote>dirty</> shared buffers. The intent is
1198 that server processes handling user queries should seldom or never have
1199 to wait for a write to occur, because the background writer will do it.
1200 However there is a net overall
1201 increase in I/O load, because a repeatedly-dirtied page might
1202 otherwise be written only once per checkpoint interval, but the
1203 background writer might write it several times in the same interval.
1204 The parameters discussed in this subsection can be used to
1205 tune the behavior for local needs.
1209 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1210 <term><varname>bgwriter_delay</varname> (<type>integer</type>)</term>
1212 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1216 Specifies the delay between activity rounds for the
1217 background writer. In each round the writer issues writes
1218 for some number of dirty buffers (controllable by the
1219 following parameters). It then sleeps for <varname>bgwriter_delay</>
1220 milliseconds, and repeats. The default value is 200 milliseconds
1221 (<literal>200ms</>). Note that on many systems, the effective
1222 resolution of sleep delays is 10 milliseconds; setting
1223 <varname>bgwriter_delay</> to a value that is not a multiple of
1224 10 might have the same results as setting it to the next higher
1225 multiple of 10. This parameter can only be set in the
1226 <filename>postgresql.conf</> file or on the server command line.
1231 <varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1232 <term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)</term>
1234 <primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1238 In each round, no more than this many buffers will be written
1239 by the background writer. Setting this to zero disables
1240 background writing (except for checkpoint activity).
1241 The default value is 100 buffers.
1242 This parameter can only be set in the <filename>postgresql.conf</>
1243 file or on the server command line.
1248 <varlistentry id="guc-bgwriter-lru-multiplier" xreflabel="bgwriter_lru_multiplier">
1249 <term><varname>bgwriter_lru_multiplier</varname> (<type>floating point</type>)</term>
1251 <primary><varname>bgwriter_lru_multiplier</> configuration parameter</primary>
1255 The number of dirty buffers written in each round is based on the
1256 number of new buffers that have been needed by server processes
1257 during recent rounds. The average recent need is multiplied by
1258 <varname>bgwriter_lru_multiplier</> to arrive at an estimate of the
1259 number of buffers that will be needed during the next round. Dirty
1260 buffers are written until there are that many clean, reusable buffers
1261 available. (However, no more than <varname>bgwriter_lru_maxpages</>
1262 buffers will be written per round.)
1263 Thus, a setting of 1.0 represents a <quote>just in time</> policy
1264 of writing exactly the number of buffers predicted to be needed.
1265 Larger values provide some cushion against spikes in demand,
1266 while smaller values intentionally leave writes to be done by
1269 This parameter can only be set in the <filename>postgresql.conf</>
1270 file or on the server command line.
1277 Smaller values of <varname>bgwriter_lru_maxpages</varname> and
1278 <varname>bgwriter_lru_multiplier</varname> reduce the extra I/O load
1279 caused by the background writer, but make it more likely that server
1280 processes will have to issue writes for themselves, delaying interactive
1286 <sect1 id="runtime-config-wal">
1287 <title>Write Ahead Log</title>
1290 See also <xref linkend="wal-configuration"> for details on WAL
1291 and checkpoint tuning.
1294 <sect2 id="runtime-config-wal-settings">
1295 <title>Settings</title>
1298 <varlistentry id="guc-fsync" xreflabel="fsync">
1300 <primary><varname>fsync</> configuration parameter</primary>
1302 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1305 If this parameter is on, the <productname>PostgreSQL</> server
1306 will try to make sure that updates are physically written to
1307 disk, by issuing <function>fsync()</> system calls or various
1308 equivalent methods (see <xref linkend="guc-wal-sync-method">).
1309 This ensures that the database cluster can recover to a
1310 consistent state after an operating system or hardware crash.
1314 However, using <varname>fsync</varname> results in a
1315 performance penalty: when a transaction is committed,
1316 <productname>PostgreSQL</productname> must wait for the
1317 operating system to flush the write-ahead log to disk. When
1318 <varname>fsync</varname> is disabled, the operating system is
1319 allowed to do its best in buffering, ordering, and delaying
1320 writes. This can result in significantly improved performance.
1321 However, if the system crashes, the results of the last few
1322 committed transactions might be lost in part or whole. In the
1323 worst case, unrecoverable data corruption might occur.
1324 (Crashes of the database software itself are <emphasis>not</>
1325 a risk factor here. Only an operating-system-level crash
1326 creates a risk of corruption.)
1330 Due to the risks involved, there is no universally correct
1331 setting for <varname>fsync</varname>. Some administrators
1332 always disable <varname>fsync</varname>, while others only
1333 turn it off during initial bulk data loads, where there is a clear
1334 restart point if something goes wrong. Others
1335 always leave <varname>fsync</varname> enabled. The default is
1336 to enable <varname>fsync</varname>, for maximum reliability.
1337 If you trust your operating system, your hardware, and your
1338 utility company (or your battery backup), you can consider
1339 disabling <varname>fsync</varname>.
1343 In many situations, turning off <xref linkend="guc-synchronous-commit">
1344 for noncritical transactions can provide much of the potential
1345 performance benefit of turning off <varname>fsync</varname>, without
1346 the attendant risks of data corruption.
1350 This parameter can only be set in the <filename>postgresql.conf</>
1351 file or on the server command line.
1352 If you turn this parameter off, also consider turning off
1353 <xref linkend="guc-full-page-writes">.
1358 <varlistentry id="guc-synchronous-commit" xreflabel="synchronous_commit">
1359 <term><varname>synchronous_commit</varname> (<type>boolean</type>)</term>
1361 <primary><varname>synchronous_commit</> configuration parameter</primary>
1365 Specifies whether transaction commit will wait for WAL records
1366 to be written to disk before the command returns a <quote>success</>
1367 indication to the client. The default, and safe, setting is
1368 <literal>on</>. When <literal>off</>, there can be a delay between
1369 when success is reported to the client and when the transaction is
1370 really guaranteed to be safe against a server crash. (The maximum
1371 delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
1372 <xref linkend="guc-fsync">, setting this parameter to <literal>off</>
1373 does not create any risk of database inconsistency: a crash might
1374 result in some recent allegedly-committed transactions being lost, but
1375 the database state will be just the same as if those transactions had
1376 been aborted cleanly. So, turning <varname>synchronous_commit</> off
1377 can be a useful alternative when performance is more important than
1378 exact certainty about the durability of a transaction. For more
1379 discussion see <xref linkend="wal-async-commit">.
1382 This parameter can be changed at any time; the behavior for any
1383 one transaction is determined by the setting in effect when it
1384 commits. It is therefore possible, and useful, to have some
1385 transactions commit synchronously and others asynchronously.
1386 For example, to make a single multi-statement transaction commit
1387 asynchronously when the default is the opposite, issue <command>SET
1388 LOCAL synchronous_commit TO OFF</> within the transaction.
1393 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1394 <term><varname>wal_sync_method</varname> (<type>string</type>)</term>
1396 <primary><varname>wal_sync_method</> configuration parameter</primary>
1400 Method used for forcing WAL updates out to disk.
1401 If <varname>fsync</varname> is off then this setting is irrelevant,
1402 since updates will not be forced out at all.
1403 Possible values are:
1408 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>)
1413 <literal>fdatasync</> (call <function>fdatasync()</> at each commit)
1418 <literal>fsync_writethrough</> (call <function>fsync()</> at each commit, forcing write-through of any disk write cache)
1423 <literal>fsync</> (call <function>fsync()</> at each commit)
1428 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>)
1433 Not all of these choices are available on all platforms.
1434 The default is the first method in the above list that is supported
1436 The <literal>open_</>* options also use <literal>O_DIRECT</> if available.
1437 This parameter can only be set in the <filename>postgresql.conf</>
1438 file or on the server command line.
1443 <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
1445 <primary><varname>full_page_writes</> configuration parameter</primary>
1447 <term><varname>full_page_writes</varname> (<type>boolean</type>)</term>
1450 When this parameter is on, the <productname>PostgreSQL</> server
1451 writes the entire content of each disk page to WAL during the
1452 first modification of that page after a checkpoint.
1453 This is needed because
1454 a page write that is in process during an operating system crash might
1455 be only partially completed, leading to an on-disk page
1456 that contains a mix of old and new data. The row-level change data
1457 normally stored in WAL will not be enough to completely restore
1458 such a page during post-crash recovery. Storing the full page image
1459 guarantees that the page can be correctly restored, but at a price
1460 in increasing the amount of data that must be written to WAL.
1461 (Because WAL replay always starts from a checkpoint, it is sufficient
1462 to do this during the first change of each page after a checkpoint.
1463 Therefore, one way to reduce the cost of full-page writes is to
1464 increase the checkpoint interval parameters.)
1468 Turning this parameter off speeds normal operation, but
1469 might lead to a corrupt database after an operating system crash
1470 or power failure. The risks are similar to turning off
1471 <varname>fsync</>, though smaller. It might be safe to turn off
1472 this parameter if you have hardware (such as a battery-backed disk
1473 controller) or file-system software that reduces
1474 the risk of partial page writes to an acceptably low level (e.g., ReiserFS 4).
1478 Turning off this parameter does not affect use of
1479 WAL archiving for point-in-time recovery (PITR)
1480 (see <xref linkend="continuous-archiving">).
1484 This parameter can only be set in the <filename>postgresql.conf</>
1485 file or on the server command line.
1486 The default is <literal>on</>.
1491 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1492 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1494 <primary><varname>wal_buffers</> configuration parameter</primary>
1498 The amount of memory used in shared memory for WAL data. The
1499 default is 64 kilobytes (<literal>64kB</>). The setting need only
1500 be large enough to hold the amount of WAL data generated by one
1501 typical transaction, since the data is written out to disk at
1502 every transaction commit. This parameter can only be set at server
1507 Increasing this parameter might cause <productname>PostgreSQL</>
1508 to request more <systemitem class="osname">System V</> shared
1509 memory than your operating system's default configuration
1510 allows. See <xref linkend="sysvipc"> for information on how to
1511 adjust those parameters, if necessary.
1516 <varlistentry id="guc-wal-writer-delay" xreflabel="wal_writer_delay">
1517 <term><varname>wal_writer_delay</varname> (<type>integer</type>)</term>
1519 <primary><varname>wal_writer_delay</> configuration parameter</primary>
1523 Specifies the delay between activity rounds for the WAL writer.
1524 In each round the writer will flush WAL to disk. It then sleeps for
1525 <varname>wal_writer_delay</> milliseconds, and repeats. The default
1526 value is 200 milliseconds (<literal>200ms</>). Note that on many
1527 systems, the effective resolution of sleep delays is 10 milliseconds;
1528 setting <varname>wal_writer_delay</> to a value that is not a multiple
1529 of 10 might have the same results as setting it to the next higher
1530 multiple of 10. This parameter can only be set in the
1531 <filename>postgresql.conf</> file or on the server command line.
1536 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
1537 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1539 <primary><varname>commit_delay</> configuration parameter</primary>
1543 Time delay between writing a commit record to the WAL buffer
1544 and flushing the buffer out to disk, in microseconds. A
1545 nonzero delay can allow multiple transactions to be committed
1546 with only one <function>fsync()</function> system call, if
1547 system load is high enough that additional transactions become
1548 ready to commit within the given interval. But the delay is
1549 just wasted if no other transactions become ready to
1550 commit. Therefore, the delay is only performed if at least
1551 <varname>commit_siblings</varname> other transactions are
1552 active at the instant that a server process has written its
1553 commit record. The default is zero (no delay).
1558 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
1559 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1561 <primary><varname>commit_siblings</> configuration parameter</primary>
1565 Minimum number of concurrent open transactions to require
1566 before performing the <varname>commit_delay</> delay. A larger
1567 value makes it more probable that at least one other
1568 transaction will become ready to commit during the delay
1569 interval. The default is five transactions.
1576 <sect2 id="runtime-config-wal-checkpoints">
1577 <title>Checkpoints</title>
1580 <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1581 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1583 <primary><varname>checkpoint_segments</> configuration parameter</primary>
1587 Maximum number of log file segments between automatic WAL
1588 checkpoints (each segment is normally 16 megabytes). The default
1589 is three segments. Increasing this parameter can increase the
1590 amount of time needed for crash recovery.
1591 This parameter can only be set in the <filename>postgresql.conf</>
1592 file or on the server command line.
1597 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1598 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1600 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
1604 Maximum time between automatic WAL checkpoints, in
1605 seconds. The default is five minutes (<literal>5min</>).
1606 Increasing this parameter can increase the amount of time needed
1608 This parameter can only be set in the <filename>postgresql.conf</>
1609 file or on the server command line.
1614 <varlistentry id="guc-checkpoint-completion-target" xreflabel="checkpoint_completion_target">
1615 <term><varname>checkpoint_completion_target</varname> (<type>floating point</type>)</term>
1617 <primary><varname>checkpoint_completion_target</> configuration parameter</primary>
1621 Specifies the target length of checkpoints, as a fraction of
1622 the checkpoint interval. The default is 0.5.
1624 This parameter can only be set in the <filename>postgresql.conf</>
1625 file or on the server command line.
1630 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1631 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1633 <primary><varname>checkpoint_warning</> configuration parameter</primary>
1637 Write a message to the server log if checkpoints caused by
1638 the filling of checkpoint segment files happen closer together
1639 than this many seconds (which suggests that
1640 <varname>checkpoint_segments</> ought to be raised). The default is
1641 30 seconds (<literal>30s</>). Zero disables the warning.
1642 This parameter can only be set in the <filename>postgresql.conf</>
1643 file or on the server command line.
1650 <sect2 id="runtime-config-wal-archiving">
1651 <title>Archiving</title>
1654 <varlistentry id="guc-archive-mode" xreflabel="archive_mode">
1655 <term><varname>archive_mode</varname> (<type>boolean</type>)</term>
1657 <primary><varname>archive_mode</> configuration parameter</primary>
1661 When <varname>archive_mode</> is enabled, completed WAL segments
1662 can be sent to archive storage by setting
1663 <xref linkend="guc-archive-command">.
1664 <varname>archive_mode</> and <varname>archive_command</> are
1665 separate variables so that <varname>archive_command</> can be
1666 changed without leaving archiving mode.
1667 This parameter can only be set at server start.
1672 <varlistentry id="guc-archive-command" xreflabel="archive_command">
1673 <term><varname>archive_command</varname> (<type>string</type>)</term>
1675 <primary><varname>archive_command</> configuration parameter</primary>
1679 The shell command to execute to archive a completed segment of
1680 the WAL file series. Any <literal>%p</> in the string is
1681 replaced by the path name of the file to archive, and any
1682 <literal>%f</> is replaced by the file name only.
1683 (The path name is relative to the working directory of the server,
1684 i.e., the cluster's data directory.)
1685 Use <literal>%%</> to embed an actual <literal>%</> character in the
1686 command. For more information see <xref
1687 linkend="backup-archiving-wal">.
1688 This parameter can only be set in the <filename>postgresql.conf</>
1689 file or on the server command line. It is ignored unless
1690 <varname>archive_mode</> was enabled at server start.
1691 If <varname>archive_command</> is an empty string (the default) while
1692 <varname>archive_mode</> is enabled, then WAL archiving is temporarily
1693 disabled, but the server continues to accumulate WAL segment files in
1694 the expectation that a command will soon be provided.
1697 It is important for the command to return a zero exit status if
1698 and only if it succeeds. Examples:
1700 archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
1701 archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
1707 <varlistentry id="guc-archive-timeout" xreflabel="archive_timeout">
1708 <term><varname>archive_timeout</varname> (<type>integer</type>)</term>
1710 <primary><varname>archive_timeout</> configuration parameter</primary>
1714 The <xref linkend="guc-archive-command"> is only invoked on
1715 completed WAL segments. Hence, if your server generates little WAL
1716 traffic (or has slack periods where it does so), there could be a
1717 long delay between the completion of a transaction and its safe
1718 recording in archive storage. To put a limit on how old unarchived
1719 data can be, you can set <varname>archive_timeout</> to force the
1720 server to switch to a new WAL segment file periodically. When this
1721 parameter is greater than zero, the server will switch to a new
1722 segment file whenever this many seconds have elapsed since the last
1723 segment file switch. Note that archived files that are closed early
1724 due to a forced switch are still the same length as completely full
1725 files. Therefore, it is unwise to use a very short
1726 <varname>archive_timeout</> — it will bloat your archive
1727 storage. <varname>archive_timeout</> settings of a minute or so are
1728 usually reasonable. This parameter can only be set in the
1729 <filename>postgresql.conf</> file or on the server command line.
1738 <sect1 id="runtime-config-query">
1739 <title>Query Planning</title>
1741 <sect2 id="runtime-config-query-enable">
1742 <title>Planner Method Configuration</title>
1745 These configuration parameters provide a crude method of
1746 influencing the query plans chosen by the query optimizer. If
1747 the default plan chosen by the optimizer for a particular query
1748 is not optimal, a temporary solution can be found by using one
1749 of these configuration parameters to force the optimizer to
1750 choose a different plan. Turning one of these settings off
1751 permanently is seldom a good idea, however.
1752 Better ways to improve the quality of the
1753 plans chosen by the optimizer include adjusting the <xref
1754 linkend="runtime-config-query-constants"
1755 endterm="runtime-config-query-constants-title">, running <xref
1756 linkend="sql-analyze" endterm="sql-analyze-title"> more
1757 frequently, increasing the value of the <xref
1758 linkend="guc-default-statistics-target"> configuration parameter,
1759 and increasing the amount of statistics collected for
1760 specific columns using <command>ALTER TABLE SET
1761 STATISTICS</command>.
1765 <varlistentry id="guc-enable-bitmapscan" xreflabel="enable_bitmapscan">
1766 <term><varname>enable_bitmapscan</varname> (<type>boolean</type>)</term>
1768 <primary>bitmap scan</primary>
1771 <primary><varname>enable_bitmapscan</> configuration parameter</primary>
1775 Enables or disables the query planner's use of bitmap-scan plan
1776 types. The default is <literal>on</>.
1781 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
1782 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
1784 <primary><varname>enable_hashagg</> configuration parameter</primary>
1788 Enables or disables the query planner's use of hashed
1789 aggregation plan types. The default is <literal>on</>.
1794 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
1795 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
1797 <primary><varname>enable_hashjoin</> configuration parameter</primary>
1801 Enables or disables the query planner's use of hash-join plan
1802 types. The default is <literal>on</>.
1807 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
1808 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
1810 <primary>index scan</primary>
1813 <primary><varname>enable_indexscan</> configuration parameter</primary>
1817 Enables or disables the query planner's use of index-scan plan
1818 types. The default is <literal>on</>.
1823 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
1824 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
1826 <primary><varname>enable_mergejoin</> configuration parameter</primary>
1830 Enables or disables the query planner's use of merge-join plan
1831 types. The default is <literal>on</>.
1836 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
1837 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
1839 <primary><varname>enable_nestloop</> configuration parameter</primary>
1843 Enables or disables the query planner's use of nested-loop join
1844 plans. It's not possible to suppress nested-loop joins entirely,
1845 but turning this variable off discourages the planner from using
1846 one if there are other methods available. The default is
1852 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
1853 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
1855 <primary>sequential scan</primary>
1858 <primary><varname>enable_seqscan</> configuration parameter</primary>
1862 Enables or disables the query planner's use of sequential scan
1863 plan types. It's not possible to suppress sequential scans
1864 entirely, but turning this variable off discourages the planner
1865 from using one if there are other methods available. The
1866 default is <literal>on</>.
1871 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
1872 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
1874 <primary><varname>enable_sort</> configuration parameter</primary>
1878 Enables or disables the query planner's use of explicit sort
1879 steps. It's not possible to suppress explicit sorts entirely,
1880 but turning this variable off discourages the planner from
1881 using one if there are other methods available. The default
1887 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
1888 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
1890 <primary><varname>enable_tidscan</> configuration parameter</primary>
1894 Enables or disables the query planner's use of <acronym>TID</>
1895 scan plan types. The default is <literal>on</>.
1902 <sect2 id="runtime-config-query-constants">
1903 <title id="runtime-config-query-constants-title">
1904 Planner Cost Constants
1908 The <firstterm>cost</> variables described in this section are measured
1909 on an arbitrary scale. Only their relative values matter, hence
1910 scaling them all up or down by the same factor will result in no change
1911 in the planner's choices. Traditionally, these variables have been
1912 referenced to sequential page fetches as the unit of cost; that is,
1913 <varname>seq_page_cost</> is conventionally set to <literal>1.0</>
1914 and the other cost variables are set with reference to that. But
1915 you can use a different scale if you prefer, such as actual execution
1916 times in milliseconds on a particular machine.
1921 Unfortunately, there is no well-defined method for determining ideal
1922 values for the cost variables. They are best treated as averages over
1923 the entire mix of queries that a particular installation will get. This
1924 means that changing them on the basis of just a few experiments is very
1931 <varlistentry id="guc-seq-page-cost" xreflabel="seq_page_cost">
1932 <term><varname>seq_page_cost</varname> (<type>floating point</type>)</term>
1934 <primary><varname>seq_page_cost</> configuration parameter</primary>
1938 Sets the planner's estimate of the cost of a disk page fetch
1939 that is part of a series of sequential fetches. The default is 1.0.
1944 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
1945 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
1947 <primary><varname>random_page_cost</> configuration parameter</primary>
1951 Sets the planner's estimate of the cost of a
1952 non-sequentially-fetched disk page. The default is 4.0.
1953 Reducing this value relative to <varname>seq_page_cost</>
1954 will cause the system to prefer index scans; raising it will
1955 make index scans look relatively more expensive. You can raise
1956 or lower both values together to change the importance of disk I/O
1957 costs relative to CPU costs, which are described by the following
1963 Although the system will let you set <varname>random_page_cost</> to
1964 less than <varname>seq_page_cost</>, it is not physically sensible
1965 to do so. However, setting them equal makes sense if the database
1966 is entirely cached in RAM, since in that case there is no penalty
1967 for touching pages out of sequence. Also, in a heavily-cached
1968 database you should lower both values relative to the CPU parameters,
1969 since the cost of fetching a page already in RAM is much smaller
1970 than it would normally be.
1976 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
1977 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
1979 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
1983 Sets the planner's estimate of the cost of processing
1984 each row during a query.
1985 The default is 0.01.
1990 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
1991 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
1993 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
1997 Sets the planner's estimate of the cost of processing
1998 each index entry during an index scan.
1999 The default is 0.005.
2004 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
2005 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
2007 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
2011 Sets the planner's estimate of the cost of processing each
2012 operator or function executed during a query.
2013 The default is 0.0025.
2018 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
2019 <term><varname>effective_cache_size</varname> (<type>integer</type>)</term>
2021 <primary><varname>effective_cache_size</> configuration parameter</primary>
2025 Sets the planner's assumption about the effective size of the
2026 disk cache that is available to a single query. This is
2027 factored into estimates of the cost of using an index; a
2028 higher value makes it more likely index scans will be used, a
2029 lower value makes it more likely sequential scans will be
2030 used. When setting this parameter you should consider both
2031 <productname>PostgreSQL</productname>'s shared buffers and the
2032 portion of the kernel's disk cache that will be used for
2033 <productname>PostgreSQL</productname> data files. Also, take
2034 into account the expected number of concurrent queries on different
2035 tables, since they will have to share the available
2036 space. This parameter has no effect on the size of shared
2037 memory allocated by <productname>PostgreSQL</productname>, nor
2038 does it reserve kernel disk cache; it is used only for estimation
2039 purposes. The default is 128 megabytes (<literal>128MB</>).
2047 <sect2 id="runtime-config-query-geqo">
2048 <title>Genetic Query Optimizer</title>
2052 <varlistentry id="guc-geqo" xreflabel="geqo">
2054 <primary>genetic query optimization</primary>
2057 <primary>GEQO</primary>
2058 <see>genetic query optimization</see>
2061 <primary><varname>geqo</> configuration parameter</primary>
2063 <term><varname>geqo</varname> (<type>boolean</type>)</term>
2066 Enables or disables genetic query optimization, which is an
2067 algorithm that attempts to do query planning without
2068 exhaustive searching. This is on by default. The
2069 <varname>geqo_threshold</varname> variable provides a more
2070 granular way to disable GEQO for certain classes of queries.
2075 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
2076 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
2078 <primary><varname>geqo_threshold</> configuration parameter</primary>
2082 Use genetic query optimization to plan queries with at least
2083 this many <literal>FROM</> items involved. (Note that a
2084 <literal>FULL OUTER JOIN</> construct counts as only one <literal>FROM</>
2085 item.) The default is 12. For simpler queries it is usually best
2086 to use the deterministic, exhaustive planner, but for queries with
2087 many tables the deterministic planner takes too long.
2092 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
2093 <term><varname>geqo_effort</varname>
2094 (<type>integer</type>)</term>
2096 <primary><varname>geqo_effort</> configuration parameter</primary>
2100 Controls the trade off between planning time and query plan
2101 efficiency in GEQO. This variable must be an integer in the
2102 range from 1 to 10. The default value is five. Larger values
2103 increase the time spent doing query planning, but also
2104 increase the likelihood that an efficient query plan will be
2109 <varname>geqo_effort</varname> doesn't actually do anything
2110 directly; it is only used to compute the default values for
2111 the other variables that influence GEQO behavior (described
2112 below). If you prefer, you can set the other parameters by
2118 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
2119 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
2121 <primary><varname>geqo_pool_size</> configuration parameter</primary>
2125 Controls the pool size used by GEQO. The pool size is the
2126 number of individuals in the genetic population. It must be
2127 at least two, and useful values are typically 100 to 1000. If
2128 it is set to zero (the default setting) then a suitable
2129 default is chosen based on <varname>geqo_effort</varname> and
2130 the number of tables in the query.
2135 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
2136 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
2138 <primary><varname>geqo_generations</> configuration parameter</primary>
2142 Controls the number of generations used by GEQO. Generations
2143 specifies the number of iterations of the algorithm. It must
2144 be at least one, and useful values are in the same range as
2145 the pool size. If it is set to zero (the default setting)
2146 then a suitable default is chosen based on
2147 <varname>geqo_pool_size</varname>.
2152 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
2153 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
2155 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
2159 Controls the selection bias used by GEQO. The selection bias
2160 is the selective pressure within the population. Values can be
2161 from 1.50 to 2.00; the latter is the default.
2168 <sect2 id="runtime-config-query-other">
2169 <title>Other Planner Options</title>
2173 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
2174 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
2176 <primary><varname>default_statistics_target</> configuration parameter</primary>
2180 Sets the default statistics target for table columns that have
2181 not had a column-specific target set via <command>ALTER TABLE
2182 SET STATISTICS</>. Larger values increase the time needed to
2183 do <command>ANALYZE</>, but might improve the quality of the
2184 planner's estimates. The default is 10. For more information
2185 on the use of statistics by the <productname>PostgreSQL</>
2186 query planner, refer to <xref linkend="planner-stats">.
2191 <varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
2192 <term><varname>constraint_exclusion</varname> (<type>boolean</type>)</term>
2194 <primary>constraint exclusion</primary>
2197 <primary><varname>constraint_exclusion</> configuration parameter</primary>
2201 Enables or disables the query planner's use of table constraints to
2202 optimize queries. The default is <literal>off</>.
2206 When this parameter is <literal>on</>, the planner compares
2207 query conditions with table <literal>CHECK</> constraints, and
2208 omits scanning tables for which the conditions contradict the
2209 constraints. For example:
2212 CREATE TABLE parent(key integer, ...);
2213 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
2214 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
2216 SELECT * FROM parent WHERE key = 2400;
2219 With constraint exclusion enabled, this <command>SELECT</>
2220 will not scan <structname>child1000</> at all. This can
2221 improve performance when inheritance is used to build
2226 Currently, <varname>constraint_exclusion</> is disabled by
2227 default because the constraint checks are relatively
2228 expensive, and in many circumstances will yield no savings.
2229 It is recommended to turn this on only if you are actually
2230 using partitioned tables designed to take advantage of the
2235 Refer to <xref linkend="ddl-partitioning"> for more information
2236 on using constraint exclusion and partitioning.
2241 <varlistentry id="guc-cursor-tuple-fraction" xreflabel="cursor_tuple_fraction">
2242 <term><varname>cursor_tuple_fraction</varname> (<type>floating point</type>)</term>
2244 <primary><varname>cursor_tuple_fraction</> configuration parameter</primary>
2248 Sets the planner's estimate of the fraction of a cursor's rows that
2249 will be retrieved. The default is 0.1. Smaller values of this
2250 setting bias the planner towards using <quote>fast start</> plans
2251 for cursors, which will retrieve the first few rows quickly while
2252 perhaps taking a long time to fetch all rows. Larger values
2253 put more emphasis on the total estimated time. At the maximum
2254 setting of 1.0, cursors are planned exactly like regular queries,
2255 considering only the total estimated time and not how soon the
2256 first rows might be delivered.
2261 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
2262 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
2264 <primary><varname>from_collapse_limit</> configuration parameter</primary>
2268 The planner will merge sub-queries into upper queries if the
2269 resulting <literal>FROM</literal> list would have no more than
2270 this many items. Smaller values reduce planning time but might
2271 yield inferior query plans. The default is eight. It is usually
2272 wise to keep this less than <xref linkend="guc-geqo-threshold">.
2273 For more information see <xref linkend="explicit-joins">.
2278 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
2279 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
2281 <primary><varname>join_collapse_limit</> configuration parameter</primary>
2285 The planner will rewrite explicit <literal>JOIN</>
2286 constructs (except <literal>FULL JOIN</>s) into lists of
2287 <literal>FROM</> items whenever a list of no more than this many items
2288 would result. Smaller values reduce planning time but might
2289 yield inferior query plans.
2293 By default, this variable is set the same as
2294 <varname>from_collapse_limit</varname>, which is appropriate
2295 for most uses. Setting it to 1 prevents any reordering of
2296 explicit <literal>JOIN</>s. Thus, the explicit join order
2297 specified in the query will be the actual order in which the
2298 relations are joined. The query planner does not always choose
2299 the optimal join order; advanced users can elect to
2300 temporarily set this variable to 1, and then specify the join
2301 order they desire explicitly.
2302 For more information see <xref linkend="explicit-joins">.
2311 <sect1 id="runtime-config-logging">
2312 <title>Error Reporting and Logging</title>
2314 <indexterm zone="runtime-config-logging">
2315 <primary>server log</primary>
2318 <sect2 id="runtime-config-logging-where">
2319 <title>Where To Log</title>
2321 <indexterm zone="runtime-config-logging-where">
2322 <primary>where to log</primary>
2327 <varlistentry id="guc-log-destination" xreflabel="log_destination">
2328 <term><varname>log_destination</varname> (<type>string</type>)</term>
2330 <primary><varname>log_destination</> configuration parameter</primary>
2334 <productname>PostgreSQL</productname> supports several methods
2335 for logging server messages, including
2336 <systemitem>stderr</systemitem>, <systemitem>csvlog</systemitem> and
2337 <systemitem>syslog</systemitem>. On Windows,
2338 <systemitem>eventlog</systemitem> is also supported. Set this
2339 parameter to a list of desired log destinations separated by
2340 commas. The default is to log to <systemitem>stderr</systemitem>
2342 This parameter can only be set in the <filename>postgresql.conf</>
2343 file or on the server command line.
2346 If <systemitem>csvlog</> is included in <varname>log_destination</>,
2347 log entries are output in <quote>comma separated
2348 value</> format, which is convenient for loading them into programs.
2349 See <xref linkend="runtime-config-logging-csvlog"> for details.
2350 <varname>logging_collector</varname> must be enabled to generate
2351 CSV-format log output.
2356 On most Unix systems, you will need to alter the configuration of
2357 your system's <application>syslog</application> daemon in order
2358 to make use of the <systemitem>syslog</systemitem> option for
2359 <varname>log_destination</>. <productname>PostgreSQL</productname>
2360 can log to <application>syslog</application> facilities
2361 <literal>LOCAL0</> through <literal>LOCAL7</> (see <xref
2362 linkend="guc-syslog-facility">), but the default
2363 <application>syslog</application> configuration on most platforms
2364 will discard all such messages. You will need to add something like
2366 local0.* /var/log/postgresql
2368 to the <application>syslog</application> daemon's configuration file
2375 <varlistentry id="guc-logging-collector" xreflabel="logging_collector">
2376 <term><varname>logging_collector</varname> (<type>boolean</type>)</term>
2378 <primary><varname>logging_collector</> configuration parameter</primary>
2382 This parameter allows messages sent to <application>stderr</>,
2383 and CSV-format log output, to be
2384 captured and redirected into log files.
2385 This approach is often more useful than
2386 logging to <application>syslog</>, since some types of messages
2387 might not appear in <application>syslog</> output (a common example
2388 is dynamic-linker failure messages).
2389 This parameter can only be set at server start.
2394 <varlistentry id="guc-log-directory" xreflabel="log_directory">
2395 <term><varname>log_directory</varname> (<type>string</type>)</term>
2397 <primary><varname>log_directory</> configuration parameter</primary>
2401 When <varname>logging_collector</> is enabled,
2402 this parameter determines the directory in which log files will be created.
2403 It can be specified as an absolute path, or relative to the
2404 cluster data directory.
2405 This parameter can only be set in the <filename>postgresql.conf</>
2406 file or on the server command line.
2411 <varlistentry id="guc-log-filename" xreflabel="log_filename">
2412 <term><varname>log_filename</varname> (<type>string</type>)</term>
2414 <primary><varname>log_filename</> configuration parameter</primary>
2418 When <varname>logging_collector</varname> is enabled,
2419 this parameter sets the file names of the created log files. The value
2420 is treated as a <systemitem>strftime</systemitem> pattern,
2421 so <literal>%</literal>-escapes can be used to specify time-varying
2422 file names. (Note that if there are
2423 any time-zone-dependent <literal>%</literal>-escapes, the computation
2424 is done in the zone specified by <xref linkend="guc-log-timezone">.)
2425 If no <literal>%</literal>-escapes are present,
2426 <productname>PostgreSQL</productname> will append the epoch of the new
2427 log file's creation time. For example, if
2428 <varname>log_filename</varname> were <literal>server_log</literal>,
2429 then the chosen file name would be <literal>server_log.1093827753</>
2430 for a log starting at Sun Aug 29 19:02:33 2004 MST.
2431 This parameter can only be set in the <filename>postgresql.conf</>
2432 file or on the server command line.
2435 If CSV-format output is enabled in <varname>log_destination</>,
2436 <literal>.csv</> will be appended to the timestamped
2437 log file name to create the file name for CSV-format output.
2438 (If <varname>log_filename</> ends in <literal>.log</>, the suffix is
2440 In the case of the example above, the CSV
2441 file name will be <literal>server_log.1093827753.csv</literal>.
2446 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
2447 <term><varname>log_rotation_age</varname> (<type>integer</type>)</term>
2449 <primary><varname>log_rotation_age</> configuration parameter</primary>
2453 When <varname>logging_collector</varname> is enabled,
2454 this parameter determines the maximum lifetime of an individual log file.
2455 After this many minutes have elapsed, a new log file will
2456 be created. Set to zero to disable time-based creation of
2458 This parameter can only be set in the <filename>postgresql.conf</>
2459 file or on the server command line.
2464 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
2465 <term><varname>log_rotation_size</varname> (<type>integer</type>)</term>
2467 <primary><varname>log_rotation_size</> configuration parameter</primary>
2471 When <varname>logging_collector</varname> is enabled,
2472 this parameter determines the maximum size of an individual log file.
2473 After this many kilobytes have been emitted into a log file,
2474 a new log file will be created. Set to zero to disable size-based
2475 creation of new log files.
2476 This parameter can only be set in the <filename>postgresql.conf</>
2477 file or on the server command line.
2482 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
2483 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)</term>
2485 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
2489 When <varname>logging_collector</varname> is enabled,
2490 this parameter will cause <productname>PostgreSQL</productname> to truncate (overwrite),
2491 rather than append to, any existing log file of the same name.
2492 However, truncation will occur only when a new file is being opened
2493 due to time-based rotation, not during server startup or size-based
2494 rotation. When off, pre-existing files will be appended to in
2495 all cases. For example, using this setting in combination with
2496 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
2497 would result in generating twenty-four hourly log files and then
2498 cyclically overwriting them.
2499 This parameter can only be set in the <filename>postgresql.conf</>
2500 file or on the server command line.
2503 Example: To keep 7 days of logs, one log file per day named
2504 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
2505 etc, and automatically overwrite last week's log with this week's log,
2506 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
2507 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>, and
2508 <varname>log_rotation_age</varname> to <literal>1440</literal>.
2511 Example: To keep 24 hours of logs, one log file per hour, but
2512 also rotate sooner if the log file size exceeds 1GB, set
2513 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
2514 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>,
2515 <varname>log_rotation_age</varname> to <literal>60</literal>, and
2516 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
2517 Including <literal>%M</> in <varname>log_filename</varname> allows
2518 any size-driven rotations that might occur to select a file name
2519 different from the hour's initial file name.
2524 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
2525 <term><varname>syslog_facility</varname> (<type>string</type>)</term>
2527 <primary><varname>syslog_facility</> configuration parameter</primary>
2531 When logging to <application>syslog</> is enabled, this parameter
2532 determines the <application>syslog</application>
2533 <quote>facility</quote> to be used. You can choose
2534 from <literal>LOCAL0</>, <literal>LOCAL1</>,
2535 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
2536 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
2537 the default is <literal>LOCAL0</>. See also the
2538 documentation of your system's
2539 <application>syslog</application> daemon.
2540 This parameter can only be set in the <filename>postgresql.conf</>
2541 file or on the server command line.
2546 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
2547 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
2549 <primary><varname>syslog_identity</> configuration parameter</primary>
2553 When logging to <application>syslog</> is enabled, this parameter
2554 determines the program name used to identify
2555 <productname>PostgreSQL</productname> messages in
2556 <application>syslog</application> logs. The default is
2557 <literal>postgres</literal>.
2558 This parameter can only be set in the <filename>postgresql.conf</>
2559 file or on the server command line.
2566 <sect2 id="runtime-config-logging-when">
2567 <title>When To Log</title>
2571 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
2572 <term><varname>client_min_messages</varname> (<type>string</type>)</term>
2574 <primary><varname>client_min_messages</> configuration parameter</primary>
2578 Controls which message levels are sent to the client.
2579 Valid values are <literal>DEBUG5</>,
2580 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
2581 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
2582 <literal>WARNING</>, <literal>ERROR</>, <literal>FATAL</>,
2583 and <literal>PANIC</>. Each level
2584 includes all the levels that follow it. The later the level,
2585 the fewer messages are sent. The default is
2586 <literal>NOTICE</>. Note that <literal>LOG</> has a different
2587 rank here than in <varname>log_min_messages</>.
2592 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
2593 <term><varname>log_min_messages</varname> (<type>string</type>)</term>
2595 <primary><varname>log_min_messages</> configuration parameter</primary>
2599 Controls which message levels are written to the server log.
2600 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
2601 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
2602 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
2603 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
2604 <literal>PANIC</>. Each level includes all the levels that
2605 follow it. The later the level, the fewer messages are sent
2606 to the log. The default is <literal>WARNING</>. Note that
2607 <literal>LOG</> has a different rank here than in
2608 <varname>client_min_messages</>.
2609 Only superusers can change this setting.
2614 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
2615 <term><varname>log_error_verbosity</varname> (<type>string</type>)</term>
2617 <primary><varname>log_error_verbosity</> configuration parameter</primary>
2621 Controls the amount of detail written in the server log for each
2622 message that is logged. Valid values are <literal>TERSE</>,
2623 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
2624 fields to displayed messages.
2625 Only superusers can change this setting.
2630 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
2631 <term><varname>log_min_error_statement</varname> (<type>string</type>)</term>
2633 <primary><varname>log_min_error_statement</> configuration parameter</primary>
2637 Controls whether or not the SQL statement that causes an error
2638 condition will be recorded in the server log. The current
2639 SQL statement is included in the log entry for any message of
2640 the specified severity or higher.
2641 Valid values are <literal>DEBUG5</literal>,
2642 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
2643 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
2644 <literal>INFO</literal>, <literal>NOTICE</literal>,
2645 <literal>WARNING</literal>, <literal>ERROR</literal>,
2646 <literal>LOG</literal>,
2647 <literal>FATAL</literal>, and <literal>PANIC</literal>.
2648 The default is <literal>ERROR</literal>, which means statements
2649 causing errors, log messages, fatal errors, or panics will be logged.
2650 To effectively turn off logging of failing statements,
2651 set this parameter to <literal>PANIC</literal>.
2652 Only superusers can change this setting.
2657 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
2658 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
2660 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
2664 Causes the duration of each completed statement to be logged
2665 if the statement ran for at least the specified number of
2666 milliseconds. Setting this to zero prints all statement durations.
2667 Minus-one (the default) disables logging statement durations.
2668 For example, if you set it to <literal>250ms</literal>
2669 then all SQL statements that run 250ms or longer will be
2670 logged. Enabling this parameter can be helpful in tracking down
2671 unoptimized queries in your applications.
2672 Only superusers can change this setting.
2676 For clients using extended query protocol, durations of the Parse,
2677 Bind, and Execute steps are logged independently.
2682 When using this option together with
2683 <xref linkend="guc-log-statement">,
2684 the text of statements that are logged because of
2685 <varname>log_statement</> will not be repeated in the
2686 duration log message.
2687 If you are not using <application>syslog</>, it is recommended
2688 that you log the PID or session ID using
2689 <xref linkend="guc-log-line-prefix">
2690 so that you can link the statement message to the later
2691 duration message using the process ID or session ID.
2697 <varlistentry id="guc-silent-mode" xreflabel="silent_mode">
2698 <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
2700 <primary><varname>silent_mode</> configuration parameter</primary>
2704 Runs the server silently. If this parameter is set, the server
2705 will automatically run in background and any controlling
2706 terminals are disassociated.
2707 The server's standard output and standard error are redirected
2708 to <literal>/dev/null</>, so any messages sent to them will be lost.
2709 Unless <application>syslog</> logging is selected or
2710 <varname>logging_collector</> is enabled, using this parameter
2711 is discouraged because it makes it impossible to see error messages.
2712 This parameter can only be set at server start.
2720 <xref linkend="runtime-config-severity-levels"> explains the message
2721 severity levels used by <productname>PostgreSQL</>. If logging output
2722 is sent to <systemitem>syslog</systemitem> or Windows'
2723 <systemitem>eventlog</systemitem>, the severity levels are translated
2724 as shown in the table.
2727 <table id="runtime-config-severity-levels">
2728 <title>Message severity levels</title>
2732 <entry>Severity</entry>
2733 <entry>Usage</entry>
2734 <entry><systemitem>syslog</></entry>
2735 <entry><systemitem>eventlog</></entry>
2741 <entry><literal>DEBUG1..DEBUG5</></entry>
2742 <entry>Provides successively-more-detailed information for use by
2744 <entry><literal>DEBUG</></entry>
2745 <entry><literal>INFORMATION</></entry>
2749 <entry><literal>INFO</></entry>
2750 <entry>Provides information implicitly requested by the user,
2751 e.g., output from <command>VACUUM VERBOSE</>.</entry>
2752 <entry><literal>INFO</></entry>
2753 <entry><literal>INFORMATION</></entry>
2757 <entry><literal>NOTICE</></entry>
2758 <entry>Provides information that might be helpful to users, e.g.,
2759 notice of truncation of long identifiers.</entry>
2760 <entry><literal>NOTICE</></entry>
2761 <entry><literal>INFORMATION</></entry>
2765 <entry><literal>WARNING</></entry>
2766 <entry>Provides warnings of likely problems, e.g., <command>COMMIT</>
2767 outside a transaction block.</entry>
2768 <entry><literal>NOTICE</></entry>
2769 <entry><literal>WARNING</></entry>
2773 <entry><literal>ERROR</></entry>
2774 <entry>Reports an error that caused the current command to
2776 <entry><literal>WARNING</></entry>
2777 <entry><literal>ERROR</></entry>
2781 <entry><literal>LOG</></entry>
2782 <entry>Reports information of interest to administrators, e.g.,
2783 checkpoint activity.</entry>
2784 <entry><literal>INFO</></entry>
2785 <entry><literal>INFORMATION</></entry>
2789 <entry><literal>FATAL</></entry>
2790 <entry>Reports an error that caused the current session to
2792 <entry><literal>ERR</></entry>
2793 <entry><literal>ERROR</></entry>
2797 <entry><literal>PANIC</></entry>
2798 <entry>Reports an error that caused all database sessions to abort.</entry>
2799 <entry><literal>CRIT</></entry>
2800 <entry><literal>ERROR</></entry>
2807 <sect2 id="runtime-config-logging-what">
2808 <title>What To Log</title>
2813 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
2814 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
2815 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
2816 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
2818 <primary><varname>debug_print_parse</> configuration parameter</primary>
2821 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
2824 <primary><varname>debug_print_plan</> configuration parameter</primary>
2827 <primary><varname>debug_pretty_print</> configuration parameter</primary>
2831 These parameters enable various debugging output to be emitted.
2832 For each executed query, they print
2833 the resulting parse tree, the query rewriter output, or the
2834 execution plan. <varname>debug_pretty_print</varname> indents
2835 these displays to produce a more readable but much longer
2836 output format. <varname>client_min_messages</varname> or
2837 <varname>log_min_messages</varname> must be
2838 <literal>DEBUG1</literal> or lower to actually send this output
2839 to the client or the server log, respectively.
2840 These parameters are off by default.
2845 <varlistentry id="guc-log-checkpoints" xreflabel="log_checkpoints">
2846 <term><varname>log_checkpoints</varname> (<type>boolean</type>)</term>
2848 <primary><varname>log_checkpoints</> configuration parameter</primary>
2852 Causes checkpoints to be logged in the server log. Some
2853 statistics about each checkpoint are included in the log messages,
2854 including the number of buffers written and the time spent writing
2856 This parameter can only be set in the <filename>postgresql.conf</>
2857 file or on the server command line. The default is off.
2862 <varlistentry id="guc-log-connections" xreflabel="log_connections">
2863 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
2865 <primary><varname>log_connections</> configuration parameter</primary>
2869 Causes each attempted connection to the server to be logged,
2870 as well as successful completion of client authentication.
2871 This parameter can only be set in the <filename>postgresql.conf</>
2872 file or on the server command line. The default is off.
2877 Some client programs, like <application>psql</>, attempt
2878 to connect twice while determining if a password is required, so
2879 duplicate <quote>connection received</> messages do not
2880 necessarily indicate a problem.
2886 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
2887 <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
2889 <primary><varname>log_disconnections</> configuration parameter</primary>
2893 This outputs a line in the server log similar to
2894 <varname>log_connections</varname> but at session termination,
2895 and includes the duration of the session. This is off by
2897 This parameter can only be set in the <filename>postgresql.conf</>
2898 file or on the server command line.
2904 <varlistentry id="guc-log-duration" xreflabel="log_duration">
2905 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
2907 <primary><varname>log_duration</> configuration parameter</primary>
2911 Causes the duration of every completed statement to be logged.
2912 The default is <literal>off</>.
2913 Only superusers can change this setting.
2917 For clients using extended query protocol, durations of the Parse,
2918 Bind, and Execute steps are logged independently.
2923 The difference between setting this option and setting
2924 <xref linkend="guc-log-min-duration-statement"> to zero is that
2925 exceeding <varname>log_min_duration_statement</> forces the text of
2926 the query to be logged, but this option doesn't. Thus, if
2927 <varname>log_duration</> is <literal>on</> and
2928 <varname>log_min_duration_statement</> has a positive value, all
2929 durations are logged but the query text is included only for
2930 statements exceeding the threshold. This behavior can be useful for
2931 gathering statistics in high-load installations.
2937 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
2938 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
2940 <primary><varname>log_hostname</> configuration parameter</primary>
2944 By default, connection log messages only show the IP address of the
2945 connecting host. Turning on this parameter causes logging of the
2946 host name as well. Note that depending on your host name resolution
2947 setup this might impose a non-negligible performance penalty.
2948 This parameter can only be set in the <filename>postgresql.conf</>
2949 file or on the server command line.
2954 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
2955 <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
2957 <primary><varname>log_line_prefix</> configuration parameter</primary>
2961 This is a <function>printf</>-style string that is output at the
2962 beginning of each log line.
2963 <literal>%</> characters begin <quote>escape sequences</>
2964 that are replaced with status information as outlined below.
2965 Unrecognized escapes are ignored. Other
2966 characters are copied straight to the log line. Some escapes are
2967 only recognized by session processes, and do not apply to
2968 background processes such as the main server process.
2969 This parameter can only be set in the <filename>postgresql.conf</>
2970 file or on the server command line. The default is an empty string.
2976 <entry>Escape</entry>
2977 <entry>Effect</entry>
2978 <entry>Session only</entry>
2983 <entry><literal>%u</literal></entry>
2984 <entry>User name</entry>
2988 <entry><literal>%d</literal></entry>
2989 <entry>Database name</entry>
2993 <entry><literal>%r</literal></entry>
2994 <entry>Remote host name or IP address, and remote port</entry>
2998 <entry><literal>%h</literal></entry>
2999 <entry>Remote host name or IP address</entry>
3003 <entry><literal>%p</literal></entry>
3004 <entry>Process ID</entry>
3008 <entry><literal>%t</literal></entry>
3009 <entry>Time stamp without milliseconds</entry>
3013 <entry><literal>%m</literal></entry>
3014 <entry>Time stamp with milliseconds</entry>
3018 <entry><literal>%i</literal></entry>
3019 <entry>Command tag: type of session's current command</entry>
3023 <entry><literal>%c</literal></entry>
3024 <entry>Session ID: see below</entry>
3028 <entry><literal>%l</literal></entry>
3029 <entry>Number of the log line for each session or process, starting at 1</entry>
3033 <entry><literal>%s</literal></entry>
3034 <entry>Process start time stamp</entry>
3038 <entry><literal>%v</literal></entry>
3039 <entry>Virtual transaction ID (backendID/localXID)</entry>
3043 <entry><literal>%x</literal></entry>
3044 <entry>Transaction ID (0 if none is assigned)</entry>
3048 <entry><literal>%q</literal></entry>
3049 <entry>Produces no output, but tells non-session
3050 processes to stop at this point in the string; ignored by
3051 session processes</entry>
3055 <entry><literal>%%</literal></entry>
3056 <entry>Literal <literal>%</></entry>
3063 The <literal>%c</> escape prints a quasi-unique session identifier,
3064 consisting of two 4-byte hexadecimal numbers (without leading zeros)
3065 separated by a dot. The numbers are the process start time and the
3066 process ID, so <literal>%c</> can also be used as a space saving way
3067 of printing those items.
3072 If you set a nonempty value for <varname>log_line_prefix</>,
3073 you should usually make its last character be a space, to provide
3074 visual separation from the rest of the log line. A punctuation
3075 character could be used too.
3081 <application>Syslog</> produces its own
3082 time stamp and process ID information, so you probably do not want to
3083 use those escapes if you are logging to <application>syslog</>.
3089 <varlistentry id="guc-log-lock-waits" xreflabel="log_lock_waits">
3090 <term><varname>log_lock_waits</varname> (<type>boolean</type>)</term>
3092 <primary><varname>log_lock_waits</> configuration parameter</primary>
3096 Controls whether a log message is produced when a session waits
3097 longer than <xref linkend="guc-deadlock-timeout"> to acquire a
3098 lock. This is useful in determining if lock waits are causing
3099 poor performance. The default is <literal>off</>.
3104 <varlistentry id="guc-log-statement" xreflabel="log_statement">
3105 <term><varname>log_statement</varname> (<type>string</type>)</term>
3107 <primary><varname>log_statement</> configuration parameter</primary>
3111 Controls which SQL statements are logged. Valid values are
3112 <literal>none</>, <literal>ddl</>, <literal>mod</>, and
3113 <literal>all</>. <literal>ddl</> logs all data definition
3114 statements, such as <command>CREATE</>, <command>ALTER</>, and
3115 <command>DROP</> statements. <literal>mod</> logs all
3116 <literal>ddl</> statements, plus data-modifying statements
3117 such as <command>INSERT</>,
3118 <command>UPDATE</>, <command>DELETE</>, <command>TRUNCATE</>,
3119 and <command>COPY FROM</>.
3120 <command>PREPARE</>, <command>EXECUTE</>, and
3121 <command>EXPLAIN ANALYZE</> statements are also logged if their
3122 contained command is of an appropriate type. For clients using
3123 extended query protocol, logging occurs when an Execute message
3124 is received, and values of the Bind parameters are included
3125 (with any embedded single-quote marks doubled).
3129 The default is <literal>none</>. Only superusers can change this
3135 Statements that contain simple syntax errors are not logged
3136 even by the <varname>log_statement</> = <literal>all</> setting,
3137 because the log message is emitted only after basic parsing has
3138 been done to determine the statement type. In the case of extended
3139 query protocol, this setting likewise does not log statements that
3140 fail before the Execute phase (i.e., during parse analysis or
3141 planning). Set <varname>log_min_error_statement</> to
3142 <literal>ERROR</> (or lower) to log such statements.
3148 <varlistentry id="guc-log-temp-files" xreflabel="log_temp_files">
3149 <term><varname>log_temp_files</varname> (<type>integer</type>)</term>
3151 <primary><varname>log_temp_files</> configuration parameter</primary>
3155 Controls whether temporary files are logged when deleted.
3156 Temporary files can be
3157 created for sorts, hashes, and temporary query results.
3158 A value of zero logs all temporary files, and positive
3159 values log only files whose size is equal or greater than
3160 the specified number of kilobytes. The
3161 default is <literal>-1</>, which disables this logging.
3166 <varlistentry id="guc-log-timezone" xreflabel="log_timezone">
3167 <term><varname>log_timezone</varname> (<type>string</type>)</term>
3169 <primary><varname>log_timezone</> configuration parameter</primary>
3173 Sets the time zone used for timestamps written in the log.
3174 Unlike <xref linkend="guc-timezone">, this value is cluster-wide,
3175 so that all sessions will report timestamps consistently.
3176 The default is <literal>unknown</>, which means to use whatever
3177 the system environment specifies as the time zone. See <xref
3178 linkend="datatype-timezones"> for more information.
3179 This parameter can only be set in the <filename>postgresql.conf</>
3180 file or on the server command line.
3187 <sect2 id="runtime-config-logging-csvlog">
3188 <title>Using CSV-Format Log Output</title>
3191 Including <literal>csvlog</> in the <varname>log_destination</> list
3192 provides a convenient way to import log files into a database table.
3193 This option emits log lines in comma-separated-value format,
3194 with these columns: timestamp with milliseconds, user name, database
3195 name, process ID, host:port number, session ID, per-session or -process line
3196 number, command tag, session start time, virtual transaction ID,
3197 regular transaction id, error severity, SQL state code, error message,
3198 error message detail, hint, internal query that led to the error (if
3199 any), character count of the error position thereof, error context,
3200 user query that led to the error (if any and enabled by
3201 <varname>log_min_error_statement</>), character count of the error
3202 position thereof, location of the error in the PostgreSQL source code
3203 (if <varname>log_error_verbosity</> is set to <literal>verbose</>).
3204 Here is a sample table definition for storing CSV-format log output:
3207 CREATE TABLE postgres_log
3209 log_time timestamp(3) with time zone,
3213 connection_from text,
3215 session_line_num bigint,
3217 session_start_time timestamp with time zone,
3218 virtual_transaction_id text,
3219 transaction_id bigint,
3220 error_severity text,
3221 sql_state_code text,
3225 internal_query text,
3226 internal_query_pos integer,
3231 PRIMARY KEY (session_id, session_line_num)
3237 To import a log file into this table, use the <command>COPY FROM</>
3241 COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
3246 There are a few things you need to do to simplify importing CSV log
3247 files easily and automatically:
3252 Set <varname>log_filename</varname> and
3253 <varname>log_rotation_age</> to provide a consistent,
3254 predictable naming scheme for your log files. This lets you
3255 predict what the file name will be and know when an individual log
3256 file is complete and therefore ready to be imported.
3262 Set <varname>log_rotation_size</varname> to 0 to disable
3263 size-based log rotation, as it makes the log file name difficult
3270 Set <varname>log_truncate_on_rotation</varname> to <literal>on</> so
3271 that old log data isn't mixed with the new in the same file.
3277 The table definition above includes a primary key specification.
3278 This is useful to protect against accidentally importing the same
3279 information twice. The <command>COPY</> command commits all of the
3280 data it imports at one time, so any error will cause the entire
3281 import to fail. If you import a partial log file and later import
3282 the file again when it is complete, the primary key violation will
3283 cause the import to fail. Wait until the log is complete and
3284 closed before importing. This procedure will also protect against
3285 accidentally importing a partial line that hasn't been completely
3286 written, which would also cause <command>COPY</> to fail.
3295 <sect1 id="runtime-config-statistics">
3296 <title>Run-Time Statistics</title>
3298 <sect2 id="runtime-config-statistics-collector">
3299 <title>Query and Index Statistics Collector</title>
3302 These parameters control server-wide statistics collection features.
3303 When statistics collection is enabled, the data that is produced can be
3304 accessed via the <structname>pg_stat</structname> and
3305 <structname>pg_statio</structname> family of system views.
3306 Refer to <xref linkend="monitoring"> for more information.
3311 <varlistentry id="guc-track-activities" xreflabel="track_activities">
3312 <term><varname>track_activities</varname> (<type>boolean</type>)</term>
3314 <primary><varname>track_activities</> configuration parameter</primary>
3318 Enables the collection of information on the currently
3319 executing command of each session, along with the time at
3320 which that command began execution. This parameter is on by
3321 default. Note that even when enabled, this information is not
3322 visible to all users, only to superusers and the user owning
3323 the session being reported on; so it should not represent a
3325 Only superusers can change this setting.
3330 <varlistentry id="guc-track-counts" xreflabel="track_counts">
3331 <term><varname>track_counts</varname> (<type>boolean</type>)</term>
3333 <primary><varname>track_counts</> configuration parameter</primary>
3337 Enables collection of statistics on database activity.
3338 This parameter is on by default, because the autovacuum
3339 daemon needs the collected information.
3340 Only superusers can change this setting.
3345 <varlistentry id="guc-update-process-title" xreflabel="update_process_title">
3346 <term><varname>update_process_title</varname> (<type>boolean</type>)</term>
3348 <primary><varname>update_process_title</> configuration parameter</primary>
3352 Enables updating of the process title every time a new SQL command
3353 is received by the server. The process title is typically viewed
3354 by the <command>ps</> command,
3355 or in Windows by using the <application>Process Explorer</>.
3356 Only superusers can change this setting.
3364 <sect2 id="runtime-config-statistics-monitor">
3365 <title>Statistics Monitoring</title>
3369 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
3370 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
3371 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
3372 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
3374 <primary><varname>log_statement_stats</> configuration parameter</primary>
3377 <primary><varname>log_parser_stats</> configuration parameter</primary>
3380 <primary><varname>log_planner_stats</> configuration parameter</primary>
3383 <primary><varname>log_executor_stats</> configuration parameter</primary>
3387 For each query, write performance statistics of the respective
3388 module to the server log. This is a crude profiling
3389 instrument. <varname>log_statement_stats</varname> reports total
3390 statement statistics, while the others report per-module statistics.
3391 <varname>log_statement_stats</varname> cannot be enabled together with
3392 any of the per-module options. All of these options are disabled by
3393 default. Only superusers can change these settings.
3403 <sect1 id="runtime-config-autovacuum">
3404 <title>Automatic Vacuuming</title>
3407 <primary>autovacuum</primary>
3408 <secondary>configuration parameters</secondary>
3412 These settings control the behavior of the <firstterm>autovacuum</>
3413 feature. Refer to <xref linkend="autovacuum"> for
3419 <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
3420 <term><varname>autovacuum</varname> (<type>boolean</type>)</term>
3422 <primary><varname>autovacuum</> configuration parameter</primary>
3426 Controls whether the server should run the
3427 autovacuum launcher daemon. This is on by default; however,
3428 <xref linkend="guc-track-counts"> must also be turned on for
3430 This parameter can only be set in the <filename>postgresql.conf</>
3431 file or on the server command line.
3434 Note that even when this parameter is disabled, the system
3435 will launch autovacuum processes if necessary to
3436 prevent transaction ID wraparound. See <xref
3437 linkend="vacuum-for-wraparound"> for more information.
3442 <varlistentry id="guc-log-autovacuum-min-duration" xreflabel="log_autovacuum_min_duration">
3443 <term><varname>log_autovacuum_min_duration</varname> (<type>integer</type>)</term>
3445 <primary><varname>log_autovacuum_min_duration</> configuration parameter</primary>
3449 Causes each action executed by autovacuum to be logged if it ran for at
3450 least the specified number of milliseconds. Setting this to zero logs
3451 all autovacuum actions. Minus-one (the default) disables logging
3452 autovacuum actions. For example, if you set this to
3453 <literal>250ms</literal> then all automatic vacuums and analyzes that run
3454 250ms or longer will be logged. Enabling this parameter can be helpful
3455 in tracking autovacuum activity. This setting can only be set in
3456 the <filename>postgresql.conf</> file or on the server command line.
3461 <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
3462 <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)</term>
3464 <primary><varname>autovacuum_max_workers</> configuration parameter</primary>
3468 Specifies the maximum number of autovacuum processes (other than the
3469 autovacuum launcher) which may be running at any one time. The default
3470 is three. This parameter can only be set in
3471 the <filename>postgresql.conf</> file or on the server command line.
3476 <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
3477 <term><varname>autovacuum_naptime</varname> (<type>integer</type>)</term>
3479 <primary><varname>autovacuum_naptime</> configuration parameter</primary>
3483 Specifies the minimum delay between autovacuum runs on any given
3484 database. In each round the daemon examines the
3485 database and issues <command>VACUUM</> and <command>ANALYZE</> commands
3486 as needed for tables in that database. The delay is measured
3487 in seconds, and the default is one minute (<literal>1m</>).
3488 This parameter can only be set in the <filename>postgresql.conf</>
3489 file or on the server command line.
3494 <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
3495 <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)</term>
3497 <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
3501 Specifies the minimum number of updated or deleted tuples needed
3502 to trigger a <command>VACUUM</> in any one table.
3503 The default is 50 tuples.
3504 This parameter can only be set in the <filename>postgresql.conf</>
3505 file or on the server command line.
3506 This setting can be overridden for individual tables by entries in
3507 <structname>pg_autovacuum</>.
3512 <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
3513 <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)</term>
3515 <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
3519 Specifies the minimum number of inserted, updated or deleted tuples
3520 needed to trigger an <command>ANALYZE</> in any one table.
3521 The default is 50 tuples.
3522 This parameter can only be set in the <filename>postgresql.conf</>
3523 file or on the server command line.
3524 This setting can be overridden for individual tables by entries in
3525 <structname>pg_autovacuum</>.
3530 <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
3531 <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)</term>
3533 <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
3537 Specifies a fraction of the table size to add to
3538 <varname>autovacuum_vacuum_threshold</varname>
3539 when deciding whether to trigger a <command>VACUUM</>.
3540 The default is 0.2 (20% of table size).
3541 This parameter can only be set in the <filename>postgresql.conf</>
3542 file or on the server command line.
3543 This setting can be overridden for individual tables by entries in
3544 <structname>pg_autovacuum</>.
3549 <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
3550 <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)</term>
3552 <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
3556 Specifies a fraction of the table size to add to
3557 <varname>autovacuum_analyze_threshold</varname>
3558 when deciding whether to trigger an <command>ANALYZE</>.
3559 The default is 0.1 (10% of table size).
3560 This parameter can only be set in the <filename>postgresql.conf</>
3561 file or on the server command line.
3562 This setting can be overridden for individual tables by entries in
3563 <structname>pg_autovacuum</>.
3568 <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
3569 <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)</term>
3571 <primary><varname>autovacuum_freeze_max_age</> configuration parameter</primary>
3575 Specifies the maximum age (in transactions) that a table's
3576 <structname>pg_class</>.<structfield>relfrozenxid</> field can
3577 attain before a <command>VACUUM</> operation is forced to prevent
3578 transaction ID wraparound within the table. Note that the system
3579 will launch autovacuum processes to prevent wraparound even when
3580 autovacuum is otherwise disabled.
3581 The default is 200 million transactions.
3582 This parameter can only be set at server start, but the setting
3583 can be reduced for individual tables by entries in
3584 <structname>pg_autovacuum</>.
3585 For more information see <xref linkend="vacuum-for-wraparound">.
3590 <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
3591 <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
3593 <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
3597 Specifies the cost delay value that will be used in automatic
3598 <command>VACUUM</> operations. If <literal>-1</> is
3599 specified, the regular
3600 <xref linkend="guc-vacuum-cost-delay"> value will be used.
3601 The default value is 20 milliseconds.
3602 This parameter can only be set in the <filename>postgresql.conf</>
3603 file or on the server command line.
3604 This setting can be overridden for individual tables by entries in
3605 <structname>pg_autovacuum</>.
3610 <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
3611 <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
3613 <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
3617 Specifies the cost limit value that will be used in automatic
3618 <command>VACUUM</> operations. If <literal>-1</> is specified (which is the
3619 default), the regular
3620 <xref linkend="guc-vacuum-cost-limit"> value will be used. Note that
3621 the value is distributed proportionally among the running autovacuum
3622 workers, if there is more than one, so that the sum of the limits of
3623 each worker never exceeds the limit on this variable.
3624 This parameter can only be set in the <filename>postgresql.conf</>
3625 file or on the server command line.
3626 This setting can be overridden for individual tables by entries in
3627 <structname>pg_autovacuum</>.
3635 <sect1 id="runtime-config-client">
3636 <title>Client Connection Defaults</title>
3638 <sect2 id="runtime-config-client-statement">
3639 <title>Statement Behavior</title>
3642 <varlistentry id="guc-search-path" xreflabel="search_path">
3643 <term><varname>search_path</varname> (<type>string</type>)</term>
3645 <primary><varname>search_path</> configuration parameter</primary>
3647 <indexterm><primary>path</><secondary>for schemas</></>
3650 This variable specifies the order in which schemas are searched
3651 when an object (table, data type, function, etc.) is referenced by a
3652 simple name with no schema component. When there are objects of
3653 identical names in different schemas, the one found first
3654 in the search path is used. An object that is not in any of the
3655 schemas in the search path can only be referenced by specifying
3656 its containing schema with a qualified (dotted) name.
3660 The value for <varname>search_path</varname> has to be a comma-separated
3661 list of schema names. If one of the list items is
3662 the special value <literal>$user</literal>, then the schema
3663 having the name returned by <function>SESSION_USER</> is substituted, if there
3664 is such a schema. (If not, <literal>$user</literal> is ignored.)
3668 The system catalog schema, <literal>pg_catalog</>, is always
3669 searched, whether it is mentioned in the path or not. If it is
3670 mentioned in the path then it will be searched in the specified
3671 order. If <literal>pg_catalog</> is not in the path then it will
3672 be searched <emphasis>before</> searching any of the path items.
3676 Likewise, the current session's temporary-table schema,
3677 <literal>pg_temp_<replaceable>nnn</></>, is always searched if it
3678 exists. It can be explicitly listed in the path by using the
3679 alias <literal>pg_temp</>. If it is not listed in the path then
3680 it is searched first (before even <literal>pg_catalog</>). However,
3681 the temporary schema is only searched for relation (table, view,
3682 sequence, etc) and data type names. It will never be searched for
3683 function or operator names.
3687 When objects are created without specifying a particular target
3688 schema, they will be placed in the first schema listed
3689 in the search path. An error is reported if the search path is
3694 The default value for this parameter is
3695 <literal>'"$user", public'</literal> (where the second part will be
3696 ignored if there is no schema named <literal>public</>).
3697 This supports shared use of a database (where no users
3698 have private schemas, and all share use of <literal>public</>),
3699 private per-user schemas, and combinations of these. Other
3700 effects can be obtained by altering the default search path
3701 setting, either globally or per-user.
3705 The current effective value of the search path can be examined
3706 via the <acronym>SQL</acronym> function
3707 <function>current_schemas()</>. This is not quite the same as
3708 examining the value of <varname>search_path</varname>, since
3709 <function>current_schemas()</> shows how the requests
3710 appearing in <varname>search_path</varname> were resolved.
3714 For more information on schema handling, see <xref linkend="ddl-schemas">.
3719 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
3720 <term><varname>default_tablespace</varname> (<type>string</type>)</term>
3722 <primary><varname>default_tablespace</> configuration parameter</primary>
3724 <indexterm><primary>tablespace</><secondary>default</></>
3727 This variable specifies the default tablespace in which to create
3728 objects (tables and indexes) when a <command>CREATE</> command does
3729 not explicitly specify a tablespace.
3733 The value is either the name of a tablespace, or an empty string
3734 to specify using the default tablespace of the current database.
3735 If the value does not match the name of any existing tablespace,
3736 <productname>PostgreSQL</> will automatically use the default
3737 tablespace of the current database. If a nondefault tablespace
3738 is specified, the user must have <literal>CREATE</> privilege
3739 for it, or creation attempts will fail.
3743 This variable is not used for temporary tables; for them,
3744 <xref linkend="guc-temp-tablespaces"> is consulted instead.
3748 For more information on tablespaces,
3749 see <xref linkend="manage-ag-tablespaces">.
3754 <varlistentry id="guc-temp-tablespaces" xreflabel="temp_tablespaces">
3755 <term><varname>temp_tablespaces</varname> (<type>string</type>)</term>
3757 <primary><varname>temp_tablespaces</> configuration parameter</primary>
3759 <indexterm><primary>tablespace</><secondary>temporary</></>
3762 This variable specifies tablespace(s) in which to create temporary
3763 objects (temp tables and indexes on temp tables) when a
3764 <command>CREATE</> command does not explicitly specify a tablespace.
3765 Temporary files for purposes such as sorting large data sets
3766 are also created in these tablespace(s).
3770 The value is a list of names of tablespaces. When there is more than
3771 one name in the list, <productname>PostgreSQL</> chooses a random
3772 member of the list each time a temporary object is to be created;
3773 except that within a transaction, successively created temporary
3774 objects are placed in successive tablespaces from the list.
3775 If the selected element of the list is an empty string,
3776 <productname>PostgreSQL</> will automatically use the default
3777 tablespace of the current database instead.
3781 When <varname>temp_tablespaces</> is set interactively, specifying a
3782 nonexistent tablespace is an error, as is specifying a tablespace for
3783 which the user does not have <literal>CREATE</> privilege. However,
3784 when using a previously set value, nonexistent tablespaces are
3785 ignored, as are tablespaces for which the user lacks
3786 <literal>CREATE</> privilege. In particular, this rule applies when
3787 using a value set in <filename>postgresql.conf</>.
3791 The default value is an empty string, which results in all temporary
3792 objects being created in the default tablespace of the current
3797 See also <xref linkend="guc-default-tablespace">.
3802 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
3803 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
3805 <primary><varname>check_function_bodies</> configuration parameter</primary>
3809 This parameter is normally on. When set to <literal>off</>, it
3810 disables validation of the function body string during <xref
3811 linkend="sql-createfunction"
3812 endterm="sql-createfunction-title">. Disabling validation is
3813 occasionally useful to avoid problems such as forward references
3814 when restoring function definitions from a dump.
3819 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
3821 <primary>transaction isolation level</primary>
3824 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
3826 <term><varname>default_transaction_isolation</varname> (<type>string</type>)</term>
3829 Each SQL transaction has an isolation level, which can be
3830 either <quote>read uncommitted</quote>, <quote>read
3831 committed</quote>, <quote>repeatable read</quote>, or
3832 <quote>serializable</quote>. This parameter controls the
3833 default isolation level of each new transaction. The default
3834 is <quote>read committed</quote>.
3838 Consult <xref linkend="mvcc"> and <xref
3839 linkend="sql-set-transaction"
3840 endterm="sql-set-transaction-title"> for more information.
3845 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
3847 <primary>read-only transaction</primary>
3850 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
3853 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
3856 A read-only SQL transaction cannot alter non-temporary tables.
3857 This parameter controls the default read-only status of each new
3858 transaction. The default is <literal>off</> (read/write).
3862 Consult <xref linkend="sql-set-transaction"
3863 endterm="sql-set-transaction-title"> for more information.
3868 <varlistentry id="guc-session-replication-role" xreflabel="session_replication_role">
3869 <term><varname>session_replication_role</varname> (<type>string</type>)</term>
3871 <primary><varname>session_replication_role</> configuration parameter</primary>
3875 Controls firing of replication-related triggers and rules for the
3876 current session. Setting this variable requires
3877 superuser privilege and results in discarding any previously cached
3878 query plans. Possible values are <literal>origin</> (the default),
3879 <literal>replica</> and <literal>local</>.
3880 See <xref linkend="sql-altertable" endterm="sql-altertable-title"> for
3886 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
3887 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
3889 <primary><varname>statement_timeout</> configuration parameter</primary>
3893 Abort any statement that takes over the specified number of
3894 milliseconds, starting from the time the command arrives at the server
3895 from the client. If <varname>log_min_error_statement</> is set to
3896 <literal>ERROR</> or lower, the statement that timed out will also be
3897 logged. A value of zero (the default) turns off the
3902 Setting <varname>statement_timeout</> in
3903 <filename>postgresql.conf</> is not recommended because it
3904 affects all sessions.
3909 <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
3910 <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)</term>
3912 <primary><varname>vacuum_freeze_min_age</> configuration parameter</primary>
3916 Specifies the cutoff age (in transactions) that <command>VACUUM</>
3917 should use to decide whether to replace transaction IDs with
3918 <literal>FrozenXID</> while scanning a table.
3919 The default is 100 million transactions. Although
3920 users can set this value anywhere from zero to one billion,
3921 <command>VACUUM</> will silently limit the effective value to half
3922 the value of <xref linkend="guc-autovacuum-freeze-max-age">, so
3923 that there is not an unreasonably short time between forced
3924 autovacuums. For more information see <xref
3925 linkend="vacuum-for-wraparound">.
3930 <varlistentry id="guc-xmlbinary" xreflabel="xmlbinary">
3931 <term><varname>xmlbinary</varname> (<type>string</type>)</term>
3933 <primary><varname>xmlbinary</> configuration parameter</primary>
3937 Sets how binary values are to be encoded in XML. This applies
3938 for example when <type>bytea</type> values are converted to
3939 XML by the functions <function>xmlelement</function> or
3940 <function>xmlforest</function>. Possible values are
3941 <literal>base64</literal> and <literal>hex</literal>, which
3942 are both defined in the XML Schema standard. The default is
3943 <literal>base64</literal>. For further information about
3944 XML-related functions, see <xref linkend="functions-xml">.
3948 The actual choice here is mostly a matter of taste,
3949 constrained only by possible restrictions in client
3950 applications. Both methods support all possible values,
3951 although the hex encoding will be somewhat larger than the
3957 <varlistentry id="guc-xmloption" xreflabel="xmloption">
3958 <term><varname>xmloption</varname> (<type>string</type>)</term>
3960 <primary><varname>xmloption</> configuration parameter</primary>
3963 <primary><varname>SET XML OPTION</></primary>
3966 <primary>XML option</primary>
3970 Sets whether <literal>DOCUMENT</literal> or
3971 <literal>CONTENT</literal> is implicit when converting between
3972 XML and character string values. See <xref
3973 linkend="datatype-xml"> for a description of this. Valid
3974 values are <literal>DOCUMENT</literal> and
3975 <literal>CONTENT</literal>. The default is
3976 <literal>CONTENT</literal>.
3980 According to the SQL standard, the command to set this option is
3982 SET XML OPTION { DOCUMENT | CONTENT };
3984 This syntax is also available in PostgreSQL.
3991 <sect2 id="runtime-config-client-format">
3992 <title>Locale and Formatting</title>
3996 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
3997 <term><varname>DateStyle</varname> (<type>string</type>)</term>
3999 <primary><varname>DateStyle</> configuration parameter</primary>
4003 Sets the display format for date and time values, as well as the
4004 rules for interpreting ambiguous date input values. For
4005 historical reasons, this variable contains two independent
4006 components: the output format specification (<literal>ISO</>,
4007 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
4008 and the input/output specification for year/month/day ordering
4009 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
4010 can be set separately or together. The keywords <literal>Euro</>
4011 and <literal>European</> are synonyms for <literal>DMY</>; the
4012 keywords <literal>US</>, <literal>NonEuro</>, and
4013 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
4014 <xref linkend="datatype-datetime"> for more information. The
4015 built-in default is <literal>ISO, MDY</>, but
4016 <application>initdb</application> will initialize the
4017 configuration file with a setting that corresponds to the
4018 behavior of the chosen <varname>lc_time</varname> locale.
4023 <varlistentry id="guc-timezone" xreflabel="timezone">
4024 <term><varname>timezone</varname> (<type>string</type>)</term>
4026 <primary><varname>timezone</> configuration parameter</primary>
4028 <indexterm><primary>time zone</></>
4031 Sets the time zone for displaying and interpreting time stamps.
4032 The default is <literal>unknown</>, which means to use whatever
4033 the system environment specifies as the time zone. See <xref
4034 linkend="datatype-timezones"> for more
4040 <varlistentry id="guc-timezone-abbreviations" xreflabel="timezone_abbreviations">
4041 <term><varname>timezone_abbreviations</varname> (<type>string</type>)</term>
4043 <primary><varname>timezone_abbreviations</> configuration parameter</primary>
4045 <indexterm><primary>time zone names</></>
4048 Sets the collection of time zone abbreviations that will be accepted
4049 by the server for datetime input. The default is <literal>'Default'</>,
4050 which is a collection that works in most of the world; there are
4051 also 'Australia' and 'India', and other collections can be defined
4052 for a particular installation. See <xref
4053 linkend="datetime-appendix"> for more information.
4058 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
4060 <primary>significant digits</primary>
4063 <primary>floating-point</primary>
4064 <secondary>display</secondary>
4067 <primary><varname>extra_float_digits</> configuration parameter</primary>
4070 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
4073 This parameter adjusts the number of digits displayed for
4074 floating-point values, including <type>float4</>, <type>float8</>,
4075 and geometric data types. The parameter value is added to the
4076 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
4077 as appropriate). The value can be set as high as 2, to include
4078 partially-significant digits; this is especially useful for dumping
4079 float data that needs to be restored exactly. Or it can be set
4080 negative to suppress unwanted digits.
4085 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
4086 <term><varname>client_encoding</varname> (<type>string</type>)</term>
4088 <primary><varname>client_encoding</> configuration parameter</primary>
4090 <indexterm><primary>character set</></>
4093 Sets the client-side encoding (character set).
4094 The default is to use the database encoding.
4099 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
4100 <term><varname>lc_messages</varname> (<type>string</type>)</term>
4102 <primary><varname>lc_messages</> configuration parameter</primary>
4106 Sets the language in which messages are displayed. Acceptable
4107 values are system-dependent; see <xref linkend="locale"> for
4108 more information. If this variable is set to the empty string
4109 (which is the default) then the value is inherited from the
4110 execution environment of the server in a system-dependent way.
4114 On some systems, this locale category does not exist. Setting
4115 this variable will still work, but there will be no effect.
4116 Also, there is a chance that no translated messages for the
4117 desired language exist. In that case you will continue to see
4118 the English messages.
4122 Only superusers can change this setting, because it affects the
4123 messages sent to the server log as well as to the client.
4128 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
4129 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
4131 <primary><varname>lc_monetary</> configuration parameter</primary>
4135 Sets the locale to use for formatting monetary amounts, for
4136 example with the <function>to_char</function> family of
4137 functions. Acceptable values are system-dependent; see <xref
4138 linkend="locale"> for more information. If this variable is
4139 set to the empty string (which is the default) then the value
4140 is inherited from the execution environment of the server in a
4141 system-dependent way.
4146 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
4147 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
4149 <primary><varname>lc_numeric</> configuration parameter</primary>
4153 Sets the locale to use for formatting numbers, for example
4154 with the <function>to_char</function> family of
4155 functions. Acceptable values are system-dependent; see <xref
4156 linkend="locale"> for more information. If this variable is
4157 set to the empty string (which is the default) then the value
4158 is inherited from the execution environment of the server in a
4159 system-dependent way.
4164 <varlistentry id="guc-lc-time" xreflabel="lc_time">
4165 <term><varname>lc_time</varname> (<type>string</type>)</term>
4167 <primary><varname>lc_time</> configuration parameter</primary>
4171 Sets the locale to use for formatting date and time values.
4172 (Currently, this setting does nothing, but it might in the
4173 future.) Acceptable values are system-dependent; see <xref
4174 linkend="locale"> for more information. If this variable is
4175 set to the empty string (which is the default) then the value
4176 is inherited from the execution environment of the server in a
4177 system-dependent way.
4182 <varlistentry id="guc-default-text-search-config" xreflabel="default_text_search_config">
4183 <term><varname>default_text_search_config</varname> (<type>string</type>)</term>
4185 <primary><varname>default_text_search_config</> configuration parameter</primary>
4189 Selects the text search configuration that is used by those variants
4190 of the text search functions that do not have an explicit argument
4191 specifying the configuration.
4192 See <xref linkend="textsearch"> for further information.
4193 The built-in default is <literal>pg_catalog.simple</>, but
4194 <application>initdb</application> will initialize the
4195 configuration file with a setting that corresponds to the
4196 chosen <varname>lc_ctype</varname> locale, if a configuration
4197 matching that locale can be identified.
4205 <sect2 id="runtime-config-client-other">
4206 <title>Other Defaults</title>
4210 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
4211 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
4213 <primary><varname>dynamic_library_path</> configuration parameter</primary>
4215 <indexterm><primary>dynamic loading</></>
4218 If a dynamically loadable module needs to be opened and the
4219 file name specified in the <command>CREATE FUNCTION</command> or
4220 <command>LOAD</command> command
4221 does not have a directory component (i.e. the
4222 name does not contain a slash), the system will search this
4223 path for the required file.
4227 The value for <varname>dynamic_library_path</varname> has to be a
4228 list of absolute directory paths separated by colons (or semi-colons
4229 on Windows). If a list element starts
4230 with the special string <literal>$libdir</literal>, the
4231 compiled-in <productname>PostgreSQL</productname> package
4232 library directory is substituted for <literal>$libdir</literal>. This
4233 is where the modules provided by the standard
4234 <productname>PostgreSQL</productname> distribution are installed.
4235 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
4236 this directory.) For example:
4238 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
4240 or, in a Windows environment:
4242 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
4247 The default value for this parameter is
4248 <literal>'$libdir'</literal>. If the value is set to an empty
4249 string, the automatic path search is turned off.
4253 This parameter can be changed at run time by superusers, but a
4254 setting done that way will only persist until the end of the
4255 client connection, so this method should be reserved for
4256 development purposes. The recommended way to set this parameter
4257 is in the <filename>postgresql.conf</filename> configuration
4263 <varlistentry id="guc-gin-fuzzy-search-limit" xreflabel="gin_fuzzy_search_limit">
4264 <term><varname>gin_fuzzy_search_limit</varname> (<type>integer</type>)</term>
4266 <primary><varname>gin_fuzzy_search_limit</> configuration parameter</primary>
4270 Soft upper limit of the size of the set returned by GIN index. For more
4271 information see <xref linkend="gin-tips">.
4276 <varlistentry id="guc-local-preload-libraries" xreflabel="local_preload_libraries">
4277 <term><varname>local_preload_libraries</varname> (<type>string</type>)</term>
4279 <primary><varname>local_preload_libraries</> configuration parameter</primary>
4282 <primary><filename>$libdir/plugins</></primary>
4286 This variable specifies one or more shared libraries that are
4287 to be preloaded at connection start. If more than one library
4288 is to be loaded, separate their names with commas.
4289 This parameter cannot be changed after the start of a particular
4294 Because this is not a superuser-only option, the libraries
4295 that can be loaded are restricted to those appearing in the
4296 <filename>plugins</> subdirectory of the installation's
4297 standard library directory. (It is the database administrator's
4298 responsibility to ensure that only <quote>safe</> libraries
4299 are installed there.) Entries in <varname>local_preload_libraries</>
4300 can specify this directory explicitly, for example
4301 <literal>$libdir/plugins/mylib</literal>, or just specify
4302 the library name — <literal>mylib</literal> would have
4303 the same effect as <literal>$libdir/plugins/mylib</literal>.
4307 There is no performance advantage to loading a library at session
4308 start rather than when it is first used. Rather, the intent of
4309 this feature is to allow debugging or performance-measurement
4310 libraries to be loaded into specific sessions without an explicit
4311 <command>LOAD</> command being given. For example, debugging could
4312 be enabled for all sessions under a given user name by setting
4313 this parameter with <command>ALTER USER SET</>.
4317 If a specified library is not found,
4318 the connection attempt will fail.
4322 Every PostgreSQL-supported library has a <quote>magic
4323 block</> that is checked to guarantee compatibility.
4324 For this reason, non-PostgreSQL libraries cannot be
4334 <sect1 id="runtime-config-locks">
4335 <title>Lock Management</title>
4339 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
4341 <primary>deadlock</primary>
4342 <secondary>timeout during</secondary>
4345 <primary>timeout</primary>
4346 <secondary>deadlock</secondary>
4349 <primary><varname>deadlock_timeout</> configuration parameter</primary>
4352 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
4355 This is the amount of time, in milliseconds, to wait on a lock
4356 before checking to see if there is a deadlock condition. The
4357 check for deadlock is relatively slow, so the server doesn't run
4358 it every time it waits for a lock. We optimistically assume
4359 that deadlocks are not common in production applications and
4360 just wait on the lock for a while before starting the check for a
4361 deadlock. Increasing this value reduces the amount of time
4362 wasted in needless deadlock checks, but slows down reporting of
4363 real deadlock errors. The default is one second (<literal>1s</>),
4364 which is probably about the smallest value you would want in
4365 practice. On a heavily loaded server you might want to raise it.
4366 Ideally the setting should exceed your typical transaction time,
4367 so as to improve the odds that a lock will be released before
4368 the waiter decides to check for deadlock.
4372 When <xref linkend="guc-log-lock-waits"> is set,
4373 this parameter also determines the length of time to wait before
4374 a log message is issued about the lock wait. If you are trying
4375 to investigate locking delays you might want to set a shorter than
4376 normal <varname>deadlock_timeout</varname>.
4381 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
4382 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
4384 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
4388 The shared lock table is created to track locks on
4389 <varname>max_locks_per_transaction</varname> * (<xref
4390 linkend="guc-max-connections"> + <xref
4391 linkend="guc-max-prepared-transactions">) objects (e.g. tables);
4392 hence, no more than this many distinct objects can be locked at
4393 any one time. This parameter controls the average number of object
4394 locks allocated for each transaction; individual transactions
4395 can lock more objects as long as the locks of all transactions
4396 fit in the lock table. This is <emphasis>not</> the number of
4397 rows that can be locked; that value is unlimited. The default,
4398 64, has historically proven sufficient, but you might need to
4399 raise this value if you have clients that touch many different
4400 tables in a single transaction. This parameter can only be set at
4405 Increasing this parameter might cause <productname>PostgreSQL</>
4406 to request more <systemitem class="osname">System V</> shared
4407 memory than your operating system's default configuration
4408 allows. See <xref linkend="sysvipc"> for information on how to
4409 adjust those parameters, if necessary.
4417 <sect1 id="runtime-config-compatible">
4418 <title>Version and Platform Compatibility</title>
4420 <sect2 id="runtime-config-compatible-version">
4421 <title>Previous PostgreSQL Versions</title>
4425 <varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
4426 <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
4427 <indexterm><primary>FROM</><secondary>missing</></>
4429 <primary><varname>add_missing_from</> configuration parameter</primary>
4433 When on, tables that are referenced by a query will be
4434 automatically added to the <literal>FROM</> clause if not
4435 already present. This behavior does not comply with the SQL
4436 standard and many people dislike it because it can mask mistakes
4437 (such as referencing a table where you should have referenced
4438 its alias). The default is <literal>off</>. This variable can be
4439 enabled for compatibility with releases of
4440 <productname>PostgreSQL</> prior to 8.1, where this behavior was
4445 Note that even when this variable is enabled, a warning
4446 message will be emitted for each implicit <literal>FROM</>
4447 entry referenced by a query. Users are encouraged to update
4448 their applications to not rely on this behavior, by adding all
4449 tables referenced by a query to the query's <literal>FROM</>
4450 clause (or its <literal>USING</> clause in the case of
4451 <command>DELETE</>).
4456 <varlistentry id="guc-array-nulls" xreflabel="array_nulls">
4457 <term><varname>array_nulls</varname> (<type>boolean</type>)</term>
4459 <primary><varname>array_nulls</> configuration parameter</primary>
4463 This controls whether the array input parser recognizes
4464 unquoted <literal>NULL</> as specifying a null array element.
4465 By default, this is <literal>on</>, allowing array values containing
4466 null values to be entered. However, <productname>PostgreSQL</> versions
4467 before 8.2 did not support null values in arrays, and therefore would
4468 treat <literal>NULL</> as specifying a normal array element with
4469 the string value <quote>NULL</>. For backwards compatibility with
4470 applications that require the old behavior, this variable can be
4471 turned <literal>off</>.
4475 Note that it is possible to create array values containing null values
4476 even when this variable is <literal>off</>.
4481 <varlistentry id="guc-backslash-quote" xreflabel="backslash_quote">
4482 <term><varname>backslash_quote</varname> (<type>string</type>)</term>
4483 <indexterm><primary>strings</><secondary>backslash quotes</></>
4485 <primary><varname>backslash_quote</> configuration parameter</primary>
4489 This controls whether a quote mark can be represented by
4490 <literal>\'</> in a string literal. The preferred, SQL-standard way
4491 to represent a quote mark is by doubling it (<literal>''</>) but
4492 <productname>PostgreSQL</> has historically also accepted
4493 <literal>\'</>. However, use of <literal>\'</> creates security risks
4494 because in some client character set encodings, there are multibyte
4495 characters in which the last byte is numerically equivalent to ASCII
4496 <literal>\</>. If client-side code does escaping incorrectly then a
4497 SQL-injection attack is possible. This risk can be prevented by
4498 making the server reject queries in which a quote mark appears to be
4499 escaped by a backslash.
4500 The allowed values of <varname>backslash_quote</> are
4501 <literal>on</> (allow <literal>\'</> always),
4502 <literal>off</> (reject always), and
4503 <literal>safe_encoding</> (allow only if client encoding does not
4504 allow ASCII <literal>\</> within a multibyte character).
4505 <literal>safe_encoding</> is the default setting.
4509 Note that in a standard-conforming string literal, <literal>\</> just
4510 means <literal>\</> anyway. This parameter affects the handling of
4511 non-standard-conforming literals, including
4512 escape string syntax (<literal>E'...'</>).
4517 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
4518 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
4520 <primary><varname>default_with_oids</> configuration parameter</primary>
4524 This controls whether <command>CREATE TABLE</command> and
4525 <command>CREATE TABLE AS</command> include an OID column in
4526 newly-created tables, if neither <literal>WITH OIDS</literal>
4527 nor <literal>WITHOUT OIDS</literal> is specified. It also
4528 determines whether OIDs will be included in tables created by
4529 <command>SELECT INTO</command>. In <productname>PostgreSQL</>
4530 8.1 <varname>default_with_oids</> is <literal>off</> by default; in
4531 prior versions of <productname>PostgreSQL</productname>, it
4536 The use of OIDs in user tables is considered deprecated, so
4537 most installations should leave this variable disabled.
4538 Applications that require OIDs for a particular table should
4539 specify <literal>WITH OIDS</literal> when creating the
4540 table. This variable can be enabled for compatibility with old
4541 applications that do not follow this behavior.
4546 <varlistentry id="guc-escape-string-warning" xreflabel="escape_string_warning">
4547 <term><varname>escape_string_warning</varname> (<type>boolean</type>)</term>
4548 <indexterm><primary>strings</><secondary>escape warning</></>
4550 <primary><varname>escape_string_warning</> configuration parameter</primary>
4554 When on, a warning is issued if a backslash (<literal>\</>)
4555 appears in an ordinary string literal (<literal>'...'</>
4556 syntax) and <varname>standard_conforming_strings</varname> is off.
4557 The default is <literal>on</>.
4560 Applications that wish to use backslash as escape should be
4561 modified to use escape string syntax (<literal>E'...'</>),
4562 because the default behavior of ordinary strings will change
4563 in a future release for SQL compatibility. This variable can
4564 be enabled to help detect applications that will break.
4569 <varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
4570 <term><varname>regex_flavor</varname> (<type>string</type>)</term>
4571 <indexterm><primary>regular expressions</></>
4573 <primary><varname>regex_flavor</> configuration parameter</primary>
4577 The regular expression <quote>flavor</> can be set to
4578 <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
4579 The default is <literal>advanced</>. The <literal>extended</>
4580 setting might be useful for exact backwards compatibility with
4581 pre-7.4 releases of <productname>PostgreSQL</>. See
4582 <xref linkend="posix-syntax-details"> for details.
4587 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
4588 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
4590 <primary><varname>sql_inheritance</> configuration parameter</primary>
4592 <indexterm><primary>inheritance</></>
4595 This controls the inheritance semantics. If turned <literal>off</>,
4596 subtables are not included by various commands by default; basically
4597 an implied <literal>ONLY</literal> key word. This was added for
4598 compatibility with releases prior to 7.1. See
4599 <xref linkend="ddl-inherit"> for more information.
4604 <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
4605 <term><varname>standard_conforming_strings</varname> (<type>boolean</type>)</term>
4606 <indexterm><primary>strings</><secondary>standard conforming</></>
4608 <primary><varname>standard_conforming_strings</> configuration parameter</primary>
4612 This controls whether ordinary string literals
4613 (<literal>'...'</>) treat backslashes literally, as specified in
4615 The default is currently <literal>off</>, causing
4616 <productname>PostgreSQL</productname> to have its historical
4617 behavior of treating backslashes as escape characters.
4618 The default will change to <literal>on</> in a future release
4619 to improve compatibility with the standard.
4620 Applications can check this
4621 parameter to determine how string literals will be processed.
4622 The presence of this parameter can also be taken as an indication
4623 that the escape string syntax (<literal>E'...'</>) is supported.
4624 Escape string syntax should be used if an application desires
4625 backslashes to be treated as escape characters.
4630 <varlistentry id="guc-synchronize-seqscans" xreflabel="synchronize_seqscans">
4631 <term><varname>synchronize_seqscans</varname> (<type>boolean</type>)</term>
4633 <primary><varname>synchronize_seqscans</> configuration parameter</primary>
4637 This allows sequential scans of large tables to synchronize with each
4638 other, so that concurrent scans read the same block at about the
4639 same time and hence share the I/O workload. When this is enabled,
4640 a scan might start in the middle of the table and then <quote>wrap
4641 around</> the end to cover all rows, so as to synchronize with the
4642 activity of scans already in progress. This can result in
4643 unpredictable changes in the row ordering returned by queries that
4644 have no <literal>ORDER BY</> clause. Setting this parameter to
4645 <literal>off</> ensures the pre-8.3 behavior in which a sequential
4646 scan always starts from the beginning of the table. The default
4655 <sect2 id="runtime-config-compatible-clients">
4656 <title>Platform and Client Compatibility</title>
4659 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
4660 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
4661 <indexterm><primary>IS NULL</></>
4663 <primary><varname>transform_null_equals</> configuration parameter</primary>
4667 When on, expressions of the form <literal><replaceable>expr</> =
4668 NULL</literal> (or <literal>NULL =
4669 <replaceable>expr</></literal>) are treated as
4670 <literal><replaceable>expr</> IS NULL</literal>, that is, they
4671 return true if <replaceable>expr</> evaluates to the null value,
4672 and false otherwise. The correct SQL-spec-compliant behavior of
4673 <literal><replaceable>expr</> = NULL</literal> is to always
4674 return null (unknown). Therefore this parameter defaults to
4679 However, filtered forms in <productname>Microsoft
4680 Access</productname> generate queries that appear to use
4681 <literal><replaceable>expr</> = NULL</literal> to test for
4682 null values, so if you use that interface to access the database you
4683 might want to turn this option on. Since expressions of the
4684 form <literal><replaceable>expr</> = NULL</literal> always
4685 return the null value (using the correct interpretation) they are not
4686 very useful and do not appear often in normal applications, so
4687 this option does little harm in practice. But new users are
4688 frequently confused about the semantics of expressions
4689 involving null values, so this option is not on by default.
4693 Note that this option only affects the exact form <literal>= NULL</>,
4694 not other comparison operators or other expressions
4695 that are computationally equivalent to some expression
4696 involving the equals operator (such as <literal>IN</literal>).
4697 Thus, this option is not a general fix for bad programming.
4701 Refer to <xref linkend="functions-comparison"> for related information.
4710 <sect1 id="runtime-config-preset">
4711 <title>Preset Options</title>
4714 The following <quote>parameters</> are read-only, and are determined
4715 when <productname>PostgreSQL</productname> is compiled or when it is
4716 installed. As such, they have been excluded from the sample
4717 <filename>postgresql.conf</> file. These options report
4718 various aspects of <productname>PostgreSQL</productname> behavior
4719 that might be of interest to certain applications, particularly
4720 administrative front-ends.
4725 <varlistentry id="guc-block-size" xreflabel="block_size">
4726 <term><varname>block_size</varname> (<type>integer</type>)</term>
4728 <primary><varname>block_size</> configuration parameter</primary>
4732 Reports the size of a disk block. It is determined by the value
4733 of <literal>BLCKSZ</> when building the server. The default
4734 value is 8192 bytes. The meaning of some configuration
4735 variables (such as <xref linkend="guc-shared-buffers">) is
4736 influenced by <varname>block_size</varname>. See <xref
4737 linkend="runtime-config-resource"> for information.
4742 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
4743 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
4745 <primary><varname>integer_datetimes</> configuration parameter</primary>
4749 Reports whether <productname>PostgreSQL</> was built with
4750 support for 64-bit-integer dates and times. This can be
4751 disabled by configuring with <literal>--disable-integer-datetimes</>
4752 when building <productname>PostgreSQL</>. The default value is
4753 <literal>on</literal>.
4758 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
4759 <term><varname>lc_collate</varname> (<type>string</type>)</term>
4761 <primary><varname>lc_collate</> configuration parameter</primary>
4765 Reports the locale in which sorting of textual data is done.
4766 See <xref linkend="locale"> for more information.
4767 The value is determined when the database cluster is initialized.
4772 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
4773 <term><varname>lc_ctype</varname> (<type>string</type>)</term>
4775 <primary><varname>lc_ctype</> configuration parameter</primary>
4779 Reports the locale that determines character classifications.
4780 See <xref linkend="locale"> for more information.
4781 The value is determined when the database cluster is initialized.
4782 Ordinarily this will be the same as <varname>lc_collate</varname>,
4783 but for special applications it might be set differently.
4788 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
4789 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
4791 <primary><varname>max_function_args</> configuration parameter</primary>
4795 Reports the maximum number of function arguments. It is determined by
4796 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
4797 default value is 100 arguments.
4802 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
4803 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
4805 <primary><varname>max_identifier_length</> configuration parameter</primary>
4809 Reports the maximum identifier length. It is determined as one
4810 less than the value of <literal>NAMEDATALEN</> when building
4811 the server. The default value of <literal>NAMEDATALEN</> is
4812 64; therefore the default
4813 <varname>max_identifier_length</varname> is 63 bytes.
4818 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
4819 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
4821 <primary><varname>max_index_keys</> configuration parameter</primary>
4825 Reports the maximum number of index keys. It is determined by
4826 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
4827 default value is 32 keys.
4832 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
4833 <term><varname>server_encoding</varname> (<type>string</type>)</term>
4835 <primary><varname>server_encoding</> configuration parameter</primary>
4837 <indexterm><primary>character set</></>
4840 Reports the database encoding (character set).
4841 It is determined when the database is created. Ordinarily,
4842 clients need only be concerned with the value of <xref
4843 linkend="guc-client-encoding">.
4848 <varlistentry id="guc-server-version" xreflabel="server_version">
4849 <term><varname>server_version</varname> (<type>string</type>)</term>
4851 <primary><varname>server_version</> configuration parameter</primary>
4855 Reports the version number of the server. It is determined by the
4856 value of <literal>PG_VERSION</> when building the server.
4861 <varlistentry id="guc-server-version-num" xreflabel="server_version_num">
4862 <term><varname>server_version_num</varname> (<type>integer</type>)</term>
4864 <primary><varname>server_version_num</> configuration parameter</primary>
4868 Reports the version number of the server as an integer. It is determined
4869 by the value of <literal>PG_VERSION_NUM</> when building the server.
4877 <sect1 id="runtime-config-custom">
4878 <title>Customized Options</title>
4881 This feature was designed to allow parameters not normally known to
4882 <productname>PostgreSQL</productname> to be added by add-on modules
4883 (such as procedural languages). This allows add-on modules to be
4884 configured in the standard ways.
4889 <varlistentry id="guc-custom-variable-classes" xreflabel="custom_variable_classes">
4890 <term><varname>custom_variable_classes</varname> (<type>string</type>)</term>
4892 <primary><varname>custom_variable_classes</> configuration parameter</primary>
4896 This variable specifies one or several class names to be used for
4897 custom variables, in the form of a comma-separated list. A custom
4898 variable is a variable not normally known
4899 to <productname>PostgreSQL</productname> proper but used by some
4900 add-on module. Such variables must have names consisting of a class
4901 name, a dot, and a variable name. <varname>custom_variable_classes</>
4902 specifies all the class names in use in a particular installation.
4903 This parameter can only be set in the <filename>postgresql.conf</>
4904 file or on the server command line.
4912 The difficulty with setting custom variables in
4913 <filename>postgresql.conf</> is that the file must be read before add-on
4914 modules have been loaded, and so custom variables would ordinarily be
4915 rejected as unknown. When <varname>custom_variable_classes</> is set,
4916 the server will accept definitions of arbitrary variables within each
4917 specified class. These variables will be treated as placeholders and
4918 will have no function until the module that defines them is loaded. When a
4919 module for a specific class is loaded, it will add the proper variable
4920 definitions for its class name, convert any placeholder
4921 values according to those definitions, and issue warnings for any
4922 placeholders of its class that remain (which presumably would be
4923 misspelled configuration variables).
4927 Here is an example of what <filename>postgresql.conf</> might contain
4928 when using custom variables:
4931 custom_variable_classes = 'plr,plperl'
4932 plr.path = '/usr/lib/R'
4933 plperl.use_strict = true
4934 plruby.use_strict = true # generates error: unknown class name
4939 <sect1 id="runtime-config-developer">
4940 <title>Developer Options</title>
4943 The following parameters are intended for work on the
4944 <productname>PostgreSQL</productname> source, and in some cases
4945 to assist with recovery of severely damaged databases. There
4946 should be no reason to use them in a production database setup.
4947 As such, they have been excluded from the sample
4948 <filename>postgresql.conf</> file. Note that many of these
4949 parameters require special source compilation flags to work at all.
4953 <varlistentry id="guc-allow-system-table-mods" xreflabel="allow_system_table_mods">
4954 <term><varname>allow_system_table_mods</varname> (<type>boolean</type>)</term>
4956 <primary><varname>allow_system_table_mods</varname> configuration parameter</primary>
4960 Allows modification of the structure of system tables.
4961 This is used by <command>initdb</command>.
4962 This parameter can only be set at server start.
4967 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
4968 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
4970 <primary><varname>debug_assertions</> configuration parameter</primary>
4974 Turns on various assertion checks. This is a debugging aid. If
4975 you are experiencing strange problems or crashes you might want
4976 to turn this on, as it might expose programming mistakes. To use
4977 this parameter, the macro <symbol>USE_ASSERT_CHECKING</symbol>
4978 must be defined when <productname>PostgreSQL</productname> is
4979 built (accomplished by the <command>configure</command> option
4980 <option>--enable-cassert</option>). Note that
4981 <varname>debug_assertions</varname> defaults to <literal>on</>
4982 if <productname>PostgreSQL</productname> has been built with
4988 <varlistentry id="guc-ignore-system-indexes" xreflabel="ignore_system_indexes">
4989 <term><varname>ignore_system_indexes</varname> (<type>boolean</type>)</term>
4991 <primary><varname>ignore_system_indexes</varname> configuration parameter</primary>
4995 Ignore system indexes when reading system tables (but still
4996 update the indexes when modifying the tables). This is useful
4997 when recovering from damaged system indexes.
4998 This parameter cannot be changed after session start.
5003 <varlistentry id="guc-post-auth-delay" xreflabel="post_auth_delay">
5004 <term><varname>post_auth_delay</varname> (<type>integer</type>)</term>
5006 <primary><varname>post_auth_delay</> configuration parameter</primary>
5010 If nonzero, a delay of this many seconds occurs when a new
5011 server process is started, after it conducts the
5012 authentication procedure. This is intended to give an
5013 opportunity to attach to the server process with a debugger.
5014 This parameter cannot be changed after session start.
5019 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
5020 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
5022 <primary><varname>pre_auth_delay</> configuration parameter</primary>
5026 If nonzero, a delay of this many seconds occurs just after a
5027 new server process is forked, before it conducts the
5028 authentication procedure. This is intended to give an
5029 opportunity to attach to the server process with a debugger to
5030 trace down misbehavior in authentication.
5031 This parameter can only be set in the <filename>postgresql.conf</>
5032 file or on the server command line.
5037 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
5038 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
5040 <primary><varname>trace_notify</> configuration parameter</primary>
5044 Generates a great amount of debugging output for the
5045 <command>LISTEN</command> and <command>NOTIFY</command>
5046 commands. <xref linkend="guc-client-min-messages"> or
5047 <xref linkend="guc-log-min-messages"> must be
5048 <literal>DEBUG1</literal> or lower to send this output to the
5049 client or server log, respectively.
5054 <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
5055 <term><varname>trace_sort</varname> (<type>boolean</type>)</term>
5057 <primary><varname>trace_sort</> configuration parameter</primary>
5061 If on, emit information about resource usage during sort operations.
5062 This parameter is only available if the <symbol>TRACE_SORT</symbol> macro
5063 was defined when <productname>PostgreSQL</productname> was compiled.
5064 (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
5070 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
5071 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
5072 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
5073 <term><varname>trace_lock_oidmin</varname> (<type>boolean</type>)</term>
5074 <term><varname>trace_lock_table</varname> (<type>boolean</type>)</term>
5075 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
5076 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
5079 Various other code tracing and debugging options.
5084 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
5085 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
5087 <primary><varname>wal_debug</> configuration parameter</primary>
5091 If on, emit WAL-related debugging output. This parameter is
5092 only available if the <symbol>WAL_DEBUG</symbol> macro was
5093 defined when <productname>PostgreSQL</productname> was
5099 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
5100 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
5102 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
5106 Detection of a damaged page header normally causes
5107 <productname>PostgreSQL</> to report an error, aborting the current
5108 command. Setting <varname>zero_damaged_pages</> to on causes
5109 the system to instead report a warning, zero out the damaged page,
5110 and continue processing. This behavior <emphasis>will destroy data</>,
5111 namely all the rows on the damaged page. But it allows you to get
5112 past the error and retrieve rows from any undamaged pages that might
5113 be present in the table. So it is useful for recovering data if
5114 corruption has occurred due to hardware or software error. You should
5115 generally not set this on until you have given up hope of recovering
5116 data from the damaged page(s) of a table. The
5117 default setting is <literal>off</>, and it can only be changed
5124 <sect1 id="runtime-config-short">
5125 <title>Short Options</title>
5128 For convenience there are also single letter command-line option
5129 switches available for some parameters. They are described in
5130 <xref linkend="runtime-config-short-table">. Some of these
5131 options exist for historical reasons, and their presence as a
5132 single-letter option does not necessarily indicate an endorsement
5133 to use the option heavily.
5136 <table id="runtime-config-short-table">
5137 <title>Short option key</title>
5141 <entry>Short option</entry>
5142 <entry>Equivalent</entry>
5148 <entry><option>-A <replaceable>x</replaceable></option></entry>
5149 <entry><literal>debug_assertions = <replaceable>x</replaceable></></entry>
5152 <entry><option>-B <replaceable>x</replaceable></option></entry>
5153 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
5156 <entry><option>-d <replaceable>x</replaceable></option></entry>
5157 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
5160 <entry><option>-e</option></entry>
5161 <entry><literal>datestyle = euro</></entry>
5165 <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
5166 <option>-fm</option>, <option>-fn</option>,
5167 <option>-fs</option>, <option>-ft</option>
5170 <literal>enable_bitmapscan = off</>,
5171 <literal>enable_hashjoin = off</>,
5172 <literal>enable_indexscan = off</>,
5173 <literal>enable_mergejoin = off</>,
5174 <literal>enable_nestloop = off</>,
5175 <literal>enable_seqscan = off</>,
5176 <literal>enable_tidscan = off</>
5180 <entry><option>-F</option></entry>
5181 <entry><literal>fsync = off</></entry>
5184 <entry><option>-h <replaceable>x</replaceable></option></entry>
5185 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
5188 <entry><option>-i</option></entry>
5189 <entry><literal>listen_addresses = '*'</></entry>
5192 <entry><option>-k <replaceable>x</replaceable></option></entry>
5193 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
5196 <entry><option>-l</option></entry>
5197 <entry><literal>ssl = on</></entry>
5200 <entry><option>-N <replaceable>x</replaceable></option></entry>
5201 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
5204 <entry><option>-O</option></entry>
5205 <entry><literal>allow_system_table_mods = on</></entry>
5208 <entry><option>-p <replaceable>x</replaceable></option></entry>
5209 <entry><literal>port = <replaceable>x</replaceable></></entry>
5212 <entry><option>-P</option></entry>
5213 <entry><literal>ignore_system_indexes = on</></entry>
5216 <entry><option>-s</option></entry>
5217 <entry><literal>log_statement_stats = on</></entry>
5220 <entry><option>-S <replaceable>x</replaceable></option></entry>
5221 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
5224 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option></entry>
5225 <entry><literal>log_parser_stats = on</>,
5226 <literal>log_planner_stats = on</>,
5227 <literal>log_executor_stats = on</></entry>
5230 <entry><option>-W <replaceable>x</replaceable></option></entry>
5231 <entry><literal>post_auth_delay = <replaceable>x</replaceable></></entry>