1 <!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.195 2008/11/11 02:42:31 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
568 start. <acronym>SSL</> communication is only possible with
574 <varlistentry id="guc-ssl-ciphers" xreflabel="ssl_ciphers">
575 <term><varname>ssl_ciphers</varname> (<type>string</type>)</term>
577 <primary><varname>ssl_ciphers</> configuration parameter</primary>
581 Specifies a list of <acronym>SSL</> ciphers that are allowed to be
582 used on secure connections. See the <application>openssl</>
583 manual page for a list of supported ciphers.
588 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
589 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
591 <primary><varname>password_encryption</> configuration parameter</primary>
595 When a password is specified in <xref
596 linkend="sql-createuser" endterm="sql-createuser-title"> or
597 <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
598 without writing either <literal>ENCRYPTED</> or
599 <literal>UNENCRYPTED</>, this parameter determines whether the
600 password is to be encrypted. The default is <literal>on</>
601 (encrypt the password).
606 <varlistentry id="guc-krb-realm" xreflabel="krb_realm">
607 <term><varname>krb_realm</varname> (<type>string</type>)</term>
609 <primary><varname>krb_realm</> configuration parameter</primary>
613 Sets the realm to match Kerberos, GSSAPI and SSPI user names against.
614 See <xref linkend="kerberos-auth">, <xref linkend="gssapi-auth"> or
615 <xref linkend="sspi-auth"> for details. This parameter can only be
621 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
622 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
624 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
628 Sets the location of the Kerberos server key file. See
629 <xref linkend="kerberos-auth"> or <xref linkend="gssapi-auth">
630 for details. This parameter can only be set at server start.
635 <varlistentry id="guc-krb-srvname" xreflabel="krb_srvname">
636 <term><varname>krb_srvname</varname> (<type>string</type>)</term>
638 <primary><varname>krb_srvname</> configuration parameter</primary>
642 Sets the Kerberos service name. See <xref linkend="kerberos-auth">
643 for details. This parameter can only be set at server start.
648 <varlistentry id="guc-krb-server-hostname" xreflabel="krb_server_hostname">
649 <term><varname>krb_server_hostname</varname> (<type>string</type>)</term>
651 <primary><varname>krb_server_hostname</> configuration parameter</primary>
655 Sets the host name part of the service principal.
656 This, combined with <varname>krb_srvname</>, is used to generate
657 the complete service principal, that is
658 <varname>krb_srvname</><literal>/</><varname>krb_server_hostname</><literal>@</>REALM.
659 If not set, the default is the server host name. See <xref linkend="kerberos-auth">
660 for details. This parameter can only be set at server start.
665 <varlistentry id="guc-krb-caseins-users" xreflabel="krb_caseins_users">
666 <term><varname>krb_caseins_users</varname> (<type>boolean</type>)</term>
668 <primary><varname>krb_caseins_users</varname> configuration parameter</primary>
672 Sets whether Kerberos and GSSAPI user names should be treated
674 The default is <literal>off</> (case sensitive). This parameter
675 can only be set at server start.
680 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
681 <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
683 <primary><varname>db_user_namespace</> configuration parameter</primary>
687 This parameter enables per-database user names. It is off by default.
688 This parameter can only be set in the <filename>postgresql.conf</>
689 file or on the server command line.
693 If this is on, you should create users as <literal>username@dbname</>.
694 When <literal>username</> is passed by a connecting client,
695 <literal>@</> and the database name are appended to the user
696 name and that database-specific user name is looked up by the
697 server. Note that when you create users with names containing
698 <literal>@</> within the SQL environment, you will need to
703 With this parameter enabled, you can still create ordinary global
704 users. Simply append <literal>@</> when specifying the user
705 name in the client. The <literal>@</> will be stripped off
706 before the user name is looked up by the server.
711 This feature is intended as a temporary measure until a
712 complete solution is found. At that time, this option will
723 <sect1 id="runtime-config-resource">
724 <title>Resource Consumption</title>
726 <sect2 id="runtime-config-resource-memory">
727 <title>Memory</title>
730 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
731 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
733 <primary><varname>shared_buffers</> configuration parameter</primary>
737 Sets the amount of memory the database server uses for shared
738 memory buffers. The default is typically 32 megabytes
739 (<literal>32MB</>), but might be less if your kernel settings will
740 not support it (as determined during <application>initdb</>).
741 This setting must be at least 128 kilobytes. (Non-default
742 values of <symbol>BLCKSZ</symbol> change the minimum.) However,
743 settings significantly higher than the minimum are usually needed
744 for good performance. Several tens of megabytes are recommended
745 for production installations. This parameter can only be set at
750 Increasing this parameter might cause <productname>PostgreSQL</>
751 to request more <systemitem class="osname">System V</> shared
752 memory than your operating system's default configuration
753 allows. See <xref linkend="sysvipc"> for information on how to
754 adjust those parameters, if necessary.
759 <varlistentry id="guc-temp-buffers" xreflabel="temp_buffers">
760 <term><varname>temp_buffers</varname> (<type>integer</type>)</term>
762 <primary><varname>temp_buffers</> configuration parameter</primary>
766 Sets the maximum number of temporary buffers used by each database
767 session. These are session-local buffers used only for access to
768 temporary tables. The default is eight megabytes
769 (<literal>8MB</>). The setting can be changed within individual
770 sessions, but only up until the first use of temporary tables
771 within a session; subsequent attempts to change the value will
772 have no effect on that session.
776 A session will allocate temporary buffers as needed up to the limit
777 given by <varname>temp_buffers</>. The cost of setting a large
778 value in sessions that do not actually need a lot of temporary
779 buffers is only a buffer descriptor, or about 64 bytes, per
780 increment in <varname>temp_buffers</>. However if a buffer is
781 actually used an additional 8192 bytes will be consumed for it
782 (or in general, <symbol>BLCKSZ</symbol> bytes).
787 <varlistentry id="guc-max-prepared-transactions" xreflabel="max_prepared_transactions">
788 <term><varname>max_prepared_transactions</varname> (<type>integer</type>)</term>
790 <primary><varname>max_prepared_transactions</> configuration parameter</primary>
794 Sets the maximum number of transactions that can be in the
795 <quote>prepared</> state simultaneously (see <xref
796 linkend="sql-prepare-transaction"
797 endterm="sql-prepare-transaction-title">).
798 Setting this parameter to zero disables the prepared-transaction
800 The default is five transactions.
801 This parameter can only be set at server start.
805 If you are not using prepared transactions, this parameter may as
806 well be set to zero. If you are using them, you will probably
807 want <varname>max_prepared_transactions</varname> to be at least
808 as large as <xref linkend="guc-max-connections">, to avoid unwanted
809 failures at the prepare step.
813 Increasing this parameter might cause <productname>PostgreSQL</>
814 to request more <systemitem class="osname">System V</> shared
815 memory than your operating system's default configuration
816 allows. See <xref linkend="sysvipc"> for information on how to
817 adjust those parameters, if necessary.
822 <varlistentry id="guc-work-mem" xreflabel="work_mem">
823 <term><varname>work_mem</varname> (<type>integer</type>)</term>
825 <primary><varname>work_mem</> configuration parameter</primary>
829 Specifies the amount of memory to be used by internal sort operations
830 and hash tables before switching to temporary disk files. The value
831 defaults to one megabyte (<literal>1MB</>).
832 Note that for a complex query, several sort or hash operations might be
833 running in parallel; each one will be allowed to use as much memory
834 as this value specifies before it starts to put data into temporary
835 files. Also, several running sessions could be doing such operations
836 concurrently. So the total memory used could be many
837 times the value of <varname>work_mem</varname>; it is necessary to
838 keep this fact in mind when choosing the value. Sort operations are
839 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
841 Hash tables are used in hash joins, hash-based aggregation, and
842 hash-based processing of <literal>IN</> subqueries.
847 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
848 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
850 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
854 Specifies the maximum amount of memory to be used in maintenance
855 operations, such as <command>VACUUM</command>, <command>CREATE
856 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults
857 to 16 megabytes (<literal>16MB</>). Since only one of these
858 operations can be executed at a time by a database session, and
859 an installation normally doesn't have many of them running
860 concurrently, it's safe to set this value significantly larger
861 than <varname>work_mem</varname>. Larger settings might improve
862 performance for vacuuming and for restoring database dumps.
867 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
868 <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
870 <primary><varname>max_stack_depth</> configuration parameter</primary>
874 Specifies the maximum safe depth of the server's execution stack.
875 The ideal setting for this parameter is the actual stack size limit
876 enforced by the kernel (as set by <literal>ulimit -s</> or local
877 equivalent), less a safety margin of a megabyte or so. The safety
878 margin is needed because the stack depth is not checked in every
879 routine in the server, but only in key potentially-recursive routines
880 such as expression evaluation. The default setting is two
881 megabytes (<literal>2MB</>), which is conservatively small and
882 unlikely to risk crashes. However, it might be too small to allow
883 execution of complex functions. Only superusers can change this
888 Setting <varname>max_stack_depth</> higher than
889 the actual kernel limit will mean that a runaway recursive function
890 can crash an individual backend process. On platforms where
891 <productname>PostgreSQL</productname> can determine the kernel limit,
892 it will not let you set this variable to an unsafe value. However,
893 not all platforms provide the information, so caution is recommended
894 in selecting a value.
902 <sect2 id="runtime-config-resource-kernel">
903 <title>Kernel Resource Usage</title>
906 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
907 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
909 <primary><varname>max_files_per_process</> configuration parameter</primary>
913 Sets the maximum number of simultaneously open files allowed to each
914 server subprocess. The default is one thousand files. If the kernel is enforcing
915 a safe per-process limit, you don't need to worry about this setting.
916 But on some platforms (notably, most BSD systems), the kernel will
917 allow individual processes to open many more files than the system
918 can really support when a large number of processes all try to open
919 that many files. If you find yourself seeing <quote>Too many open
920 files</> failures, try reducing this setting.
921 This parameter can only be set at server start.
926 <varlistentry id="guc-shared-preload-libraries" xreflabel="shared_preload_libraries">
927 <term><varname>shared_preload_libraries</varname> (<type>string</type>)</term>
929 <primary><varname>shared_preload_libraries</> configuration parameter</primary>
933 This variable specifies one or more shared libraries that are
934 to be preloaded at server start. If more than one library is to be
935 loaded, separate their names with commas. For example,
936 <literal>'$libdir/mylib'</literal> would cause
937 <literal>mylib.so</> (or on some platforms,
938 <literal>mylib.sl</>) to be preloaded from the installation's
939 standard library directory.
940 This parameter can only be set at server start.
944 <productname>PostgreSQL</productname> procedural language
945 libraries can be preloaded in this way, typically by using the
946 syntax <literal>'$libdir/plXXX'</literal> where
947 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
948 <literal>tcl</>, or <literal>python</>.
952 By preloading a shared library, the library startup time is avoided
953 when the library is first used. However, the time to start each new
954 server process might increase slightly, even if that process never
955 uses the library. So this parameter is recommended only for
956 libraries that will be used in most sessions.
961 On Windows hosts, preloading a library at server start will not reduce
962 the time required to start each new server process; each server process
963 will re-load all preload libraries. However, <varname>shared_preload_libraries
964 </varname> is still useful on Windows hosts because some shared libraries may
965 need to perform certain operations that only take place at postmaster start
966 (for example, a shared library may need to reserve lightweight locks
967 or shared memory and you can't do that after the postmaster has started).
971 If a specified library is not found,
972 the server will fail to start.
976 Every PostgreSQL-supported library has a <quote>magic
977 block</> that is checked to guarantee compatibility.
978 For this reason, non-PostgreSQL libraries cannot be
987 <sect2 id="runtime-config-resource-vacuum-cost">
988 <title id="runtime-config-resource-vacuum-cost-title">
989 Cost-Based Vacuum Delay
993 During the execution of <xref linkend="sql-vacuum"
994 endterm="sql-vacuum-title"> and <xref linkend="sql-analyze"
995 endterm="sql-analyze-title"> commands, the system maintains an
996 internal counter that keeps track of the estimated cost of the
997 various I/O operations that are performed. When the accumulated
998 cost reaches a limit (specified by
999 <varname>vacuum_cost_limit</varname>), the process performing
1000 the operation will sleep for a while (specified by
1001 <varname>vacuum_cost_delay</varname>). Then it will reset the
1002 counter and continue execution.
1006 The intent of this feature is to allow administrators to reduce
1007 the I/O impact of these commands on concurrent database
1008 activity. There are many situations in which it is not very
1009 important that maintenance commands like
1010 <command>VACUUM</command> and <command>ANALYZE</command> finish
1011 quickly; however, it is usually very important that these
1012 commands do not significantly interfere with the ability of the
1013 system to perform other database operations. Cost-based vacuum
1014 delay provides a way for administrators to achieve this.
1018 This feature is disabled by default. To enable it, set the
1019 <varname>vacuum_cost_delay</varname> variable to a nonzero
1024 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1025 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)</term>
1027 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1031 The length of time, in milliseconds, that the process will sleep
1032 when the cost limit has been exceeded.
1033 The default value is zero, which disables the cost-based vacuum
1034 delay feature. Positive values enable cost-based vacuuming.
1035 Note that on many systems, the effective resolution
1036 of sleep delays is 10 milliseconds; setting
1037 <varname>vacuum_cost_delay</varname> to a value that is
1038 not a multiple of 10 might have the same results as setting it
1039 to the next higher multiple of 10.
1044 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1045 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
1047 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1051 The estimated cost for vacuuming a buffer found in the shared buffer
1052 cache. It represents the cost to lock the buffer pool, lookup
1053 the shared hash table and scan the content of the page. The
1054 default value is one.
1059 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1060 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
1062 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1066 The estimated cost for vacuuming a buffer that has to be read from
1067 disk. This represents the effort to lock the buffer pool,
1068 lookup the shared hash table, read the desired block in from
1069 the disk and scan its content. The default value is 10.
1074 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1075 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
1077 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1081 The estimated cost charged when vacuum modifies a block that was
1082 previously clean. It represents the extra I/O required to
1083 flush the dirty block out to disk again. The default value is
1089 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1090 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
1092 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1096 The accumulated cost that will cause the vacuuming process to sleep.
1097 The default value is 200.
1105 There are certain operations that hold critical locks and should
1106 therefore complete as quickly as possible. Cost-based vacuum
1107 delays do not occur during such operations. Therefore it is
1108 possible that the cost accumulates far higher than the specified
1109 limit. To avoid uselessly long delays in such cases, the actual
1110 delay is calculated as <varname>vacuum_cost_delay</varname> *
1111 <varname>accumulated_balance</varname> /
1112 <varname>vacuum_cost_limit</varname> with a maximum of
1113 <varname>vacuum_cost_delay</varname> * 4.
1118 <sect2 id="runtime-config-resource-background-writer">
1119 <title>Background Writer</title>
1122 There is a separate server
1123 process called the <firstterm>background writer</>, whose function
1124 is to issue writes of <quote>dirty</> shared buffers. The intent is
1125 that server processes handling user queries should seldom or never have
1126 to wait for a write to occur, because the background writer will do it.
1127 However there is a net overall
1128 increase in I/O load, because a repeatedly-dirtied page might
1129 otherwise be written only once per checkpoint interval, but the
1130 background writer might write it several times in the same interval.
1131 The parameters discussed in this subsection can be used to
1132 tune the behavior for local needs.
1136 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1137 <term><varname>bgwriter_delay</varname> (<type>integer</type>)</term>
1139 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1143 Specifies the delay between activity rounds for the
1144 background writer. In each round the writer issues writes
1145 for some number of dirty buffers (controllable by the
1146 following parameters). It then sleeps for <varname>bgwriter_delay</>
1147 milliseconds, and repeats. The default value is 200 milliseconds
1148 (<literal>200ms</>). Note that on many systems, the effective
1149 resolution of sleep delays is 10 milliseconds; setting
1150 <varname>bgwriter_delay</> to a value that is not a multiple of
1151 10 might have the same results as setting it to the next higher
1152 multiple of 10. This parameter can only be set in the
1153 <filename>postgresql.conf</> file or on the server command line.
1158 <varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1159 <term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)</term>
1161 <primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1165 In each round, no more than this many buffers will be written
1166 by the background writer. Setting this to zero disables
1167 background writing (except for checkpoint activity).
1168 The default value is 100 buffers.
1169 This parameter can only be set in the <filename>postgresql.conf</>
1170 file or on the server command line.
1175 <varlistentry id="guc-bgwriter-lru-multiplier" xreflabel="bgwriter_lru_multiplier">
1176 <term><varname>bgwriter_lru_multiplier</varname> (<type>floating point</type>)</term>
1178 <primary><varname>bgwriter_lru_multiplier</> configuration parameter</primary>
1182 The number of dirty buffers written in each round is based on the
1183 number of new buffers that have been needed by server processes
1184 during recent rounds. The average recent need is multiplied by
1185 <varname>bgwriter_lru_multiplier</> to arrive at an estimate of the
1186 number of buffers that will be needed during the next round. Dirty
1187 buffers are written until there are that many clean, reusable buffers
1188 available. (However, no more than <varname>bgwriter_lru_maxpages</>
1189 buffers will be written per round.)
1190 Thus, a setting of 1.0 represents a <quote>just in time</> policy
1191 of writing exactly the number of buffers predicted to be needed.
1192 Larger values provide some cushion against spikes in demand,
1193 while smaller values intentionally leave writes to be done by
1196 This parameter can only be set in the <filename>postgresql.conf</>
1197 file or on the server command line.
1204 Smaller values of <varname>bgwriter_lru_maxpages</varname> and
1205 <varname>bgwriter_lru_multiplier</varname> reduce the extra I/O load
1206 caused by the background writer, but make it more likely that server
1207 processes will have to issue writes for themselves, delaying interactive
1213 <sect1 id="runtime-config-wal">
1214 <title>Write Ahead Log</title>
1217 See also <xref linkend="wal-configuration"> for details on WAL
1218 and checkpoint tuning.
1221 <sect2 id="runtime-config-wal-settings">
1222 <title>Settings</title>
1225 <varlistentry id="guc-fsync" xreflabel="fsync">
1227 <primary><varname>fsync</> configuration parameter</primary>
1229 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1232 If this parameter is on, the <productname>PostgreSQL</> server
1233 will try to make sure that updates are physically written to
1234 disk, by issuing <function>fsync()</> system calls or various
1235 equivalent methods (see <xref linkend="guc-wal-sync-method">).
1236 This ensures that the database cluster can recover to a
1237 consistent state after an operating system or hardware crash.
1241 However, using <varname>fsync</varname> results in a
1242 performance penalty: when a transaction is committed,
1243 <productname>PostgreSQL</productname> must wait for the
1244 operating system to flush the write-ahead log to disk. When
1245 <varname>fsync</varname> is disabled, the operating system is
1246 allowed to do its best in buffering, ordering, and delaying
1247 writes. This can result in significantly improved performance.
1248 However, if the system crashes, the results of the last few
1249 committed transactions might be lost in part or whole. In the
1250 worst case, unrecoverable data corruption might occur.
1251 (Crashes of the database software itself are <emphasis>not</>
1252 a risk factor here. Only an operating-system-level crash
1253 creates a risk of corruption.)
1257 Due to the risks involved, there is no universally correct
1258 setting for <varname>fsync</varname>. Some administrators
1259 always disable <varname>fsync</varname>, while others only
1260 turn it off during initial bulk data loads, where there is a clear
1261 restart point if something goes wrong. Others
1262 always leave <varname>fsync</varname> enabled. The default is
1263 to enable <varname>fsync</varname>, for maximum reliability.
1264 If you trust your operating system, your hardware, and your
1265 utility company (or your battery backup), you can consider
1266 disabling <varname>fsync</varname>.
1270 In many situations, turning off <xref linkend="guc-synchronous-commit">
1271 for noncritical transactions can provide much of the potential
1272 performance benefit of turning off <varname>fsync</varname>, without
1273 the attendant risks of data corruption.
1277 This parameter can only be set in the <filename>postgresql.conf</>
1278 file or on the server command line.
1279 If you turn this parameter off, also consider turning off
1280 <xref linkend="guc-full-page-writes">.
1285 <varlistentry id="guc-synchronous-commit" xreflabel="synchronous_commit">
1286 <term><varname>synchronous_commit</varname> (<type>boolean</type>)</term>
1288 <primary><varname>synchronous_commit</> configuration parameter</primary>
1292 Specifies whether transaction commit will wait for WAL records
1293 to be written to disk before the command returns a <quote>success</>
1294 indication to the client. The default, and safe, setting is
1295 <literal>on</>. When <literal>off</>, there can be a delay between
1296 when success is reported to the client and when the transaction is
1297 really guaranteed to be safe against a server crash. (The maximum
1298 delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
1299 <xref linkend="guc-fsync">, setting this parameter to <literal>off</>
1300 does not create any risk of database inconsistency: a crash might
1301 result in some recent allegedly-committed transactions being lost, but
1302 the database state will be just the same as if those transactions had
1303 been aborted cleanly. So, turning <varname>synchronous_commit</> off
1304 can be a useful alternative when performance is more important than
1305 exact certainty about the durability of a transaction. For more
1306 discussion see <xref linkend="wal-async-commit">.
1309 This parameter can be changed at any time; the behavior for any
1310 one transaction is determined by the setting in effect when it
1311 commits. It is therefore possible, and useful, to have some
1312 transactions commit synchronously and others asynchronously.
1313 For example, to make a single multi-statement transaction commit
1314 asynchronously when the default is the opposite, issue <command>SET
1315 LOCAL synchronous_commit TO OFF</> within the transaction.
1320 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1321 <term><varname>wal_sync_method</varname> (<type>string</type>)</term>
1323 <primary><varname>wal_sync_method</> configuration parameter</primary>
1327 Method used for forcing WAL updates out to disk.
1328 If <varname>fsync</varname> is off then this setting is irrelevant,
1329 since updates will not be forced out at all.
1330 Possible values are:
1335 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>)
1340 <literal>fdatasync</> (call <function>fdatasync()</> at each commit)
1345 <literal>fsync_writethrough</> (call <function>fsync()</> at each commit, forcing write-through of any disk write cache)
1350 <literal>fsync</> (call <function>fsync()</> at each commit)
1355 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>)
1360 Not all of these choices are available on all platforms.
1361 The default is the first method in the above list that is supported
1363 The <literal>open_</>* options also use <literal>O_DIRECT</> if available.
1364 This parameter can only be set in the <filename>postgresql.conf</>
1365 file or on the server command line.
1370 <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
1372 <primary><varname>full_page_writes</> configuration parameter</primary>
1374 <term><varname>full_page_writes</varname> (<type>boolean</type>)</term>
1377 When this parameter is on, the <productname>PostgreSQL</> server
1378 writes the entire content of each disk page to WAL during the
1379 first modification of that page after a checkpoint.
1380 This is needed because
1381 a page write that is in process during an operating system crash might
1382 be only partially completed, leading to an on-disk page
1383 that contains a mix of old and new data. The row-level change data
1384 normally stored in WAL will not be enough to completely restore
1385 such a page during post-crash recovery. Storing the full page image
1386 guarantees that the page can be correctly restored, but at a price
1387 in increasing the amount of data that must be written to WAL.
1388 (Because WAL replay always starts from a checkpoint, it is sufficient
1389 to do this during the first change of each page after a checkpoint.
1390 Therefore, one way to reduce the cost of full-page writes is to
1391 increase the checkpoint interval parameters.)
1395 Turning this parameter off speeds normal operation, but
1396 might lead to a corrupt database after an operating system crash
1397 or power failure. The risks are similar to turning off
1398 <varname>fsync</>, though smaller. It might be safe to turn off
1399 this parameter if you have hardware (such as a battery-backed disk
1400 controller) or file-system software that reduces
1401 the risk of partial page writes to an acceptably low level (e.g., ReiserFS 4).
1405 Turning off this parameter does not affect use of
1406 WAL archiving for point-in-time recovery (PITR)
1407 (see <xref linkend="continuous-archiving">).
1411 This parameter can only be set in the <filename>postgresql.conf</>
1412 file or on the server command line.
1413 The default is <literal>on</>.
1418 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1419 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1421 <primary><varname>wal_buffers</> configuration parameter</primary>
1425 The amount of memory used in shared memory for WAL data. The
1426 default is 64 kilobytes (<literal>64kB</>). The setting need only
1427 be large enough to hold the amount of WAL data generated by one
1428 typical transaction, since the data is written out to disk at
1429 every transaction commit. This parameter can only be set at server
1434 Increasing this parameter might cause <productname>PostgreSQL</>
1435 to request more <systemitem class="osname">System V</> shared
1436 memory than your operating system's default configuration
1437 allows. See <xref linkend="sysvipc"> for information on how to
1438 adjust those parameters, if necessary.
1443 <varlistentry id="guc-wal-writer-delay" xreflabel="wal_writer_delay">
1444 <term><varname>wal_writer_delay</varname> (<type>integer</type>)</term>
1446 <primary><varname>wal_writer_delay</> configuration parameter</primary>
1450 Specifies the delay between activity rounds for the WAL writer.
1451 In each round the writer will flush WAL to disk. It then sleeps for
1452 <varname>wal_writer_delay</> milliseconds, and repeats. The default
1453 value is 200 milliseconds (<literal>200ms</>). Note that on many
1454 systems, the effective resolution of sleep delays is 10 milliseconds;
1455 setting <varname>wal_writer_delay</> to a value that is not a multiple
1456 of 10 might have the same results as setting it to the next higher
1457 multiple of 10. This parameter can only be set in the
1458 <filename>postgresql.conf</> file or on the server command line.
1463 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
1464 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1466 <primary><varname>commit_delay</> configuration parameter</primary>
1470 Time delay between writing a commit record to the WAL buffer
1471 and flushing the buffer out to disk, in microseconds. A
1472 nonzero delay can allow multiple transactions to be committed
1473 with only one <function>fsync()</function> system call, if
1474 system load is high enough that additional transactions become
1475 ready to commit within the given interval. But the delay is
1476 just wasted if no other transactions become ready to
1477 commit. Therefore, the delay is only performed if at least
1478 <varname>commit_siblings</varname> other transactions are
1479 active at the instant that a server process has written its
1480 commit record. The default is zero (no delay).
1485 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
1486 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1488 <primary><varname>commit_siblings</> configuration parameter</primary>
1492 Minimum number of concurrent open transactions to require
1493 before performing the <varname>commit_delay</> delay. A larger
1494 value makes it more probable that at least one other
1495 transaction will become ready to commit during the delay
1496 interval. The default is five transactions.
1503 <sect2 id="runtime-config-wal-checkpoints">
1504 <title>Checkpoints</title>
1507 <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1508 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1510 <primary><varname>checkpoint_segments</> configuration parameter</primary>
1514 Maximum number of log file segments between automatic WAL
1515 checkpoints (each segment is normally 16 megabytes). The default
1516 is three segments. Increasing this parameter can increase the
1517 amount of time needed for crash recovery.
1518 This parameter can only be set in the <filename>postgresql.conf</>
1519 file or on the server command line.
1524 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1525 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1527 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
1531 Maximum time between automatic WAL checkpoints, in
1532 seconds. The default is five minutes (<literal>5min</>).
1533 Increasing this parameter can increase the amount of time needed
1535 This parameter can only be set in the <filename>postgresql.conf</>
1536 file or on the server command line.
1541 <varlistentry id="guc-checkpoint-completion-target" xreflabel="checkpoint_completion_target">
1542 <term><varname>checkpoint_completion_target</varname> (<type>floating point</type>)</term>
1544 <primary><varname>checkpoint_completion_target</> configuration parameter</primary>
1548 Specifies the target length of checkpoints, as a fraction of
1549 the checkpoint interval. The default is 0.5.
1551 This parameter can only be set in the <filename>postgresql.conf</>
1552 file or on the server command line.
1557 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1558 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1560 <primary><varname>checkpoint_warning</> configuration parameter</primary>
1564 Write a message to the server log if checkpoints caused by
1565 the filling of checkpoint segment files happen closer together
1566 than this many seconds (which suggests that
1567 <varname>checkpoint_segments</> ought to be raised). The default is
1568 30 seconds (<literal>30s</>). Zero disables the warning.
1569 This parameter can only be set in the <filename>postgresql.conf</>
1570 file or on the server command line.
1577 <sect2 id="runtime-config-wal-archiving">
1578 <title>Archiving</title>
1581 <varlistentry id="guc-archive-mode" xreflabel="archive_mode">
1582 <term><varname>archive_mode</varname> (<type>boolean</type>)</term>
1584 <primary><varname>archive_mode</> configuration parameter</primary>
1588 When <varname>archive_mode</> is enabled, completed WAL segments
1589 can be sent to archive storage by setting
1590 <xref linkend="guc-archive-command">.
1591 <varname>archive_mode</> and <varname>archive_command</> are
1592 separate variables so that <varname>archive_command</> can be
1593 changed without leaving archiving mode.
1594 This parameter can only be set at server start.
1599 <varlistentry id="guc-archive-command" xreflabel="archive_command">
1600 <term><varname>archive_command</varname> (<type>string</type>)</term>
1602 <primary><varname>archive_command</> configuration parameter</primary>
1606 The shell command to execute to archive a completed segment of
1607 the WAL file series. Any <literal>%p</> in the string is
1608 replaced by the path name of the file to archive, and any
1609 <literal>%f</> is replaced by the file name only.
1610 (The path name is relative to the working directory of the server,
1611 i.e., the cluster's data directory.)
1612 Use <literal>%%</> to embed an actual <literal>%</> character in the
1613 command. For more information see <xref
1614 linkend="backup-archiving-wal">.
1615 This parameter can only be set in the <filename>postgresql.conf</>
1616 file or on the server command line. It is ignored unless
1617 <varname>archive_mode</> was enabled at server start.
1618 If <varname>archive_command</> is an empty string (the default) while
1619 <varname>archive_mode</> is enabled, then WAL archiving is temporarily
1620 disabled, but the server continues to accumulate WAL segment files in
1621 the expectation that a command will soon be provided.
1624 It is important for the command to return a zero exit status if
1625 and only if it succeeds. Examples:
1627 archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
1628 archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows
1634 <varlistentry id="guc-archive-timeout" xreflabel="archive_timeout">
1635 <term><varname>archive_timeout</varname> (<type>integer</type>)</term>
1637 <primary><varname>archive_timeout</> configuration parameter</primary>
1641 The <xref linkend="guc-archive-command"> is only invoked on
1642 completed WAL segments. Hence, if your server generates little WAL
1643 traffic (or has slack periods where it does so), there could be a
1644 long delay between the completion of a transaction and its safe
1645 recording in archive storage. To put a limit on how old unarchived
1646 data can be, you can set <varname>archive_timeout</> to force the
1647 server to switch to a new WAL segment file periodically. When this
1648 parameter is greater than zero, the server will switch to a new
1649 segment file whenever this many seconds have elapsed since the last
1650 segment file switch. Note that archived files that are closed early
1651 due to a forced switch are still the same length as completely full
1652 files. Therefore, it is unwise to use a very short
1653 <varname>archive_timeout</> — it will bloat your archive
1654 storage. <varname>archive_timeout</> settings of a minute or so are
1655 usually reasonable. This parameter can only be set in the
1656 <filename>postgresql.conf</> file or on the server command line.
1665 <sect1 id="runtime-config-query">
1666 <title>Query Planning</title>
1668 <sect2 id="runtime-config-query-enable">
1669 <title>Planner Method Configuration</title>
1672 These configuration parameters provide a crude method of
1673 influencing the query plans chosen by the query optimizer. If
1674 the default plan chosen by the optimizer for a particular query
1675 is not optimal, a temporary solution can be found by using one
1676 of these configuration parameters to force the optimizer to
1677 choose a different plan. Turning one of these settings off
1678 permanently is seldom a good idea, however.
1679 Better ways to improve the quality of the
1680 plans chosen by the optimizer include adjusting the <xref
1681 linkend="runtime-config-query-constants"
1682 endterm="runtime-config-query-constants-title">, running <xref
1683 linkend="sql-analyze" endterm="sql-analyze-title"> more
1684 frequently, increasing the value of the <xref
1685 linkend="guc-default-statistics-target"> configuration parameter,
1686 and increasing the amount of statistics collected for
1687 specific columns using <command>ALTER TABLE SET
1688 STATISTICS</command>.
1692 <varlistentry id="guc-enable-bitmapscan" xreflabel="enable_bitmapscan">
1693 <term><varname>enable_bitmapscan</varname> (<type>boolean</type>)</term>
1695 <primary>bitmap scan</primary>
1698 <primary><varname>enable_bitmapscan</> configuration parameter</primary>
1702 Enables or disables the query planner's use of bitmap-scan plan
1703 types. The default is <literal>on</>.
1708 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
1709 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
1711 <primary><varname>enable_hashagg</> configuration parameter</primary>
1715 Enables or disables the query planner's use of hashed
1716 aggregation plan types. The default is <literal>on</>.
1721 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
1722 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
1724 <primary><varname>enable_hashjoin</> configuration parameter</primary>
1728 Enables or disables the query planner's use of hash-join plan
1729 types. The default is <literal>on</>.
1734 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
1735 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
1737 <primary>index scan</primary>
1740 <primary><varname>enable_indexscan</> configuration parameter</primary>
1744 Enables or disables the query planner's use of index-scan plan
1745 types. The default is <literal>on</>.
1750 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
1751 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
1753 <primary><varname>enable_mergejoin</> configuration parameter</primary>
1757 Enables or disables the query planner's use of merge-join plan
1758 types. The default is <literal>on</>.
1763 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
1764 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
1766 <primary><varname>enable_nestloop</> configuration parameter</primary>
1770 Enables or disables the query planner's use of nested-loop join
1771 plans. It's not possible to suppress nested-loop joins entirely,
1772 but turning this variable off discourages the planner from using
1773 one if there are other methods available. The default is
1779 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
1780 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
1782 <primary>sequential scan</primary>
1785 <primary><varname>enable_seqscan</> configuration parameter</primary>
1789 Enables or disables the query planner's use of sequential scan
1790 plan types. It's not possible to suppress sequential scans
1791 entirely, but turning this variable off discourages the planner
1792 from using one if there are other methods available. The
1793 default is <literal>on</>.
1798 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
1799 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
1801 <primary><varname>enable_sort</> configuration parameter</primary>
1805 Enables or disables the query planner's use of explicit sort
1806 steps. It's not possible to suppress explicit sorts entirely,
1807 but turning this variable off discourages the planner from
1808 using one if there are other methods available. The default
1814 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
1815 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
1817 <primary><varname>enable_tidscan</> configuration parameter</primary>
1821 Enables or disables the query planner's use of <acronym>TID</>
1822 scan plan types. The default is <literal>on</>.
1829 <sect2 id="runtime-config-query-constants">
1830 <title id="runtime-config-query-constants-title">
1831 Planner Cost Constants
1835 The <firstterm>cost</> variables described in this section are measured
1836 on an arbitrary scale. Only their relative values matter, hence
1837 scaling them all up or down by the same factor will result in no change
1838 in the planner's choices. Traditionally, these variables have been
1839 referenced to sequential page fetches as the unit of cost; that is,
1840 <varname>seq_page_cost</> is conventionally set to <literal>1.0</>
1841 and the other cost variables are set with reference to that. But
1842 you can use a different scale if you prefer, such as actual execution
1843 times in milliseconds on a particular machine.
1848 Unfortunately, there is no well-defined method for determining ideal
1849 values for the cost variables. They are best treated as averages over
1850 the entire mix of queries that a particular installation will get. This
1851 means that changing them on the basis of just a few experiments is very
1858 <varlistentry id="guc-seq-page-cost" xreflabel="seq_page_cost">
1859 <term><varname>seq_page_cost</varname> (<type>floating point</type>)</term>
1861 <primary><varname>seq_page_cost</> configuration parameter</primary>
1865 Sets the planner's estimate of the cost of a disk page fetch
1866 that is part of a series of sequential fetches. The default is 1.0.
1871 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
1872 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
1874 <primary><varname>random_page_cost</> configuration parameter</primary>
1878 Sets the planner's estimate of the cost of a
1879 non-sequentially-fetched disk page. The default is 4.0.
1880 Reducing this value relative to <varname>seq_page_cost</>
1881 will cause the system to prefer index scans; raising it will
1882 make index scans look relatively more expensive. You can raise
1883 or lower both values together to change the importance of disk I/O
1884 costs relative to CPU costs, which are described by the following
1890 Although the system will let you set <varname>random_page_cost</> to
1891 less than <varname>seq_page_cost</>, it is not physically sensible
1892 to do so. However, setting them equal makes sense if the database
1893 is entirely cached in RAM, since in that case there is no penalty
1894 for touching pages out of sequence. Also, in a heavily-cached
1895 database you should lower both values relative to the CPU parameters,
1896 since the cost of fetching a page already in RAM is much smaller
1897 than it would normally be.
1903 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
1904 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
1906 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
1910 Sets the planner's estimate of the cost of processing
1911 each row during a query.
1912 The default is 0.01.
1917 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
1918 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
1920 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
1924 Sets the planner's estimate of the cost of processing
1925 each index entry during an index scan.
1926 The default is 0.005.
1931 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
1932 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
1934 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
1938 Sets the planner's estimate of the cost of processing each
1939 operator or function executed during a query.
1940 The default is 0.0025.
1945 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
1946 <term><varname>effective_cache_size</varname> (<type>integer</type>)</term>
1948 <primary><varname>effective_cache_size</> configuration parameter</primary>
1952 Sets the planner's assumption about the effective size of the
1953 disk cache that is available to a single query. This is
1954 factored into estimates of the cost of using an index; a
1955 higher value makes it more likely index scans will be used, a
1956 lower value makes it more likely sequential scans will be
1957 used. When setting this parameter you should consider both
1958 <productname>PostgreSQL</productname>'s shared buffers and the
1959 portion of the kernel's disk cache that will be used for
1960 <productname>PostgreSQL</productname> data files. Also, take
1961 into account the expected number of concurrent queries on different
1962 tables, since they will have to share the available
1963 space. This parameter has no effect on the size of shared
1964 memory allocated by <productname>PostgreSQL</productname>, nor
1965 does it reserve kernel disk cache; it is used only for estimation
1966 purposes. The default is 128 megabytes (<literal>128MB</>).
1974 <sect2 id="runtime-config-query-geqo">
1975 <title>Genetic Query Optimizer</title>
1979 <varlistentry id="guc-geqo" xreflabel="geqo">
1981 <primary>genetic query optimization</primary>
1984 <primary>GEQO</primary>
1985 <see>genetic query optimization</see>
1988 <primary><varname>geqo</> configuration parameter</primary>
1990 <term><varname>geqo</varname> (<type>boolean</type>)</term>
1993 Enables or disables genetic query optimization, which is an
1994 algorithm that attempts to do query planning without
1995 exhaustive searching. This is on by default. The
1996 <varname>geqo_threshold</varname> variable provides a more
1997 granular way to disable GEQO for certain classes of queries.
2002 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
2003 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
2005 <primary><varname>geqo_threshold</> configuration parameter</primary>
2009 Use genetic query optimization to plan queries with at least
2010 this many <literal>FROM</> items involved. (Note that a
2011 <literal>FULL OUTER JOIN</> construct counts as only one <literal>FROM</>
2012 item.) The default is 12. For simpler queries it is usually best
2013 to use the deterministic, exhaustive planner, but for queries with
2014 many tables the deterministic planner takes too long.
2019 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
2020 <term><varname>geqo_effort</varname>
2021 (<type>integer</type>)</term>
2023 <primary><varname>geqo_effort</> configuration parameter</primary>
2027 Controls the trade off between planning time and query plan
2028 efficiency in GEQO. This variable must be an integer in the
2029 range from 1 to 10. The default value is five. Larger values
2030 increase the time spent doing query planning, but also
2031 increase the likelihood that an efficient query plan will be
2036 <varname>geqo_effort</varname> doesn't actually do anything
2037 directly; it is only used to compute the default values for
2038 the other variables that influence GEQO behavior (described
2039 below). If you prefer, you can set the other parameters by
2045 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
2046 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
2048 <primary><varname>geqo_pool_size</> configuration parameter</primary>
2052 Controls the pool size used by GEQO. The pool size is the
2053 number of individuals in the genetic population. It must be
2054 at least two, and useful values are typically 100 to 1000. If
2055 it is set to zero (the default setting) then a suitable
2056 default is chosen based on <varname>geqo_effort</varname> and
2057 the number of tables in the query.
2062 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
2063 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
2065 <primary><varname>geqo_generations</> configuration parameter</primary>
2069 Controls the number of generations used by GEQO. Generations
2070 specifies the number of iterations of the algorithm. It must
2071 be at least one, and useful values are in the same range as
2072 the pool size. If it is set to zero (the default setting)
2073 then a suitable default is chosen based on
2074 <varname>geqo_pool_size</varname>.
2079 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
2080 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
2082 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
2086 Controls the selection bias used by GEQO. The selection bias
2087 is the selective pressure within the population. Values can be
2088 from 1.50 to 2.00; the latter is the default.
2095 <sect2 id="runtime-config-query-other">
2096 <title>Other Planner Options</title>
2100 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
2101 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
2103 <primary><varname>default_statistics_target</> configuration parameter</primary>
2107 Sets the default statistics target for table columns that have
2108 not had a column-specific target set via <command>ALTER TABLE
2109 SET STATISTICS</>. Larger values increase the time needed to
2110 do <command>ANALYZE</>, but might improve the quality of the
2111 planner's estimates. The default is 10. For more information
2112 on the use of statistics by the <productname>PostgreSQL</>
2113 query planner, refer to <xref linkend="planner-stats">.
2118 <varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
2119 <term><varname>constraint_exclusion</varname> (<type>boolean</type>)</term>
2121 <primary>constraint exclusion</primary>
2124 <primary><varname>constraint_exclusion</> configuration parameter</primary>
2128 Enables or disables the query planner's use of table constraints to
2129 optimize queries. The default is <literal>off</>.
2133 When this parameter is <literal>on</>, the planner compares
2134 query conditions with table <literal>CHECK</> constraints, and
2135 omits scanning tables for which the conditions contradict the
2136 constraints. For example:
2139 CREATE TABLE parent(key integer, ...);
2140 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
2141 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
2143 SELECT * FROM parent WHERE key = 2400;
2146 With constraint exclusion enabled, this <command>SELECT</>
2147 will not scan <structname>child1000</> at all. This can
2148 improve performance when inheritance is used to build
2153 Currently, <varname>constraint_exclusion</> is disabled by
2154 default because the constraint checks are relatively
2155 expensive, and in many circumstances will yield no savings.
2156 It is recommended to turn this on only if you are actually
2157 using partitioned tables designed to take advantage of the
2162 Refer to <xref linkend="ddl-partitioning"> for more information
2163 on using constraint exclusion and partitioning.
2168 <varlistentry id="guc-cursor-tuple-fraction" xreflabel="cursor_tuple_fraction">
2169 <term><varname>cursor_tuple_fraction</varname> (<type>floating point</type>)</term>
2171 <primary><varname>cursor_tuple_fraction</> configuration parameter</primary>
2175 Sets the planner's estimate of the fraction of a cursor's rows that
2176 will be retrieved. The default is 0.1. Smaller values of this
2177 setting bias the planner towards using <quote>fast start</> plans
2178 for cursors, which will retrieve the first few rows quickly while
2179 perhaps taking a long time to fetch all rows. Larger values
2180 put more emphasis on the total estimated time. At the maximum
2181 setting of 1.0, cursors are planned exactly like regular queries,
2182 considering only the total estimated time and not how soon the
2183 first rows might be delivered.
2188 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
2189 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
2191 <primary><varname>from_collapse_limit</> configuration parameter</primary>
2195 The planner will merge sub-queries into upper queries if the
2196 resulting <literal>FROM</literal> list would have no more than
2197 this many items. Smaller values reduce planning time but might
2198 yield inferior query plans. The default is eight. It is usually
2199 wise to keep this less than <xref linkend="guc-geqo-threshold">.
2200 For more information see <xref linkend="explicit-joins">.
2205 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
2206 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
2208 <primary><varname>join_collapse_limit</> configuration parameter</primary>
2212 The planner will rewrite explicit <literal>JOIN</>
2213 constructs (except <literal>FULL JOIN</>s) into lists of
2214 <literal>FROM</> items whenever a list of no more than this many items
2215 would result. Smaller values reduce planning time but might
2216 yield inferior query plans.
2220 By default, this variable is set the same as
2221 <varname>from_collapse_limit</varname>, which is appropriate
2222 for most uses. Setting it to 1 prevents any reordering of
2223 explicit <literal>JOIN</>s. Thus, the explicit join order
2224 specified in the query will be the actual order in which the
2225 relations are joined. The query planner does not always choose
2226 the optimal join order; advanced users can elect to
2227 temporarily set this variable to 1, and then specify the join
2228 order they desire explicitly.
2229 For more information see <xref linkend="explicit-joins">.
2238 <sect1 id="runtime-config-logging">
2239 <title>Error Reporting and Logging</title>
2241 <indexterm zone="runtime-config-logging">
2242 <primary>server log</primary>
2245 <sect2 id="runtime-config-logging-where">
2246 <title>Where To Log</title>
2248 <indexterm zone="runtime-config-logging-where">
2249 <primary>where to log</primary>
2254 <varlistentry id="guc-log-destination" xreflabel="log_destination">
2255 <term><varname>log_destination</varname> (<type>string</type>)</term>
2257 <primary><varname>log_destination</> configuration parameter</primary>
2261 <productname>PostgreSQL</productname> supports several methods
2262 for logging server messages, including
2263 <systemitem>stderr</systemitem>, <systemitem>csvlog</systemitem> and
2264 <systemitem>syslog</systemitem>. On Windows,
2265 <systemitem>eventlog</systemitem> is also supported. Set this
2266 parameter to a list of desired log destinations separated by
2267 commas. The default is to log to <systemitem>stderr</systemitem>
2269 This parameter can only be set in the <filename>postgresql.conf</>
2270 file or on the server command line.
2273 If <systemitem>csvlog</> is included in <varname>log_destination</>,
2274 log entries are output in <quote>comma separated
2275 value</> format, which is convenient for loading them into programs.
2276 See <xref linkend="runtime-config-logging-csvlog"> for details.
2277 <varname>logging_collector</varname> must be enabled to generate
2278 CSV-format log output.
2283 On most Unix systems, you will need to alter the configuration of
2284 your system's <application>syslog</application> daemon in order
2285 to make use of the <systemitem>syslog</systemitem> option for
2286 <varname>log_destination</>. <productname>PostgreSQL</productname>
2287 can log to <application>syslog</application> facilities
2288 <literal>LOCAL0</> through <literal>LOCAL7</> (see <xref
2289 linkend="guc-syslog-facility">), but the default
2290 <application>syslog</application> configuration on most platforms
2291 will discard all such messages. You will need to add something like
2293 local0.* /var/log/postgresql
2295 to the <application>syslog</application> daemon's configuration file
2302 <varlistentry id="guc-logging-collector" xreflabel="logging_collector">
2303 <term><varname>logging_collector</varname> (<type>boolean</type>)</term>
2305 <primary><varname>logging_collector</> configuration parameter</primary>
2309 This parameter allows messages sent to <application>stderr</>,
2310 and CSV-format log output, to be
2311 captured and redirected into log files.
2312 This approach is often more useful than
2313 logging to <application>syslog</>, since some types of messages
2314 might not appear in <application>syslog</> output (a common example
2315 is dynamic-linker failure messages).
2316 This parameter can only be set at server start.
2321 <varlistentry id="guc-log-directory" xreflabel="log_directory">
2322 <term><varname>log_directory</varname> (<type>string</type>)</term>
2324 <primary><varname>log_directory</> configuration parameter</primary>
2328 When <varname>logging_collector</> is enabled,
2329 this parameter determines the directory in which log files will be created.
2330 It can be specified as an absolute path, or relative to the
2331 cluster data directory.
2332 This parameter can only be set in the <filename>postgresql.conf</>
2333 file or on the server command line.
2338 <varlistentry id="guc-log-filename" xreflabel="log_filename">
2339 <term><varname>log_filename</varname> (<type>string</type>)</term>
2341 <primary><varname>log_filename</> configuration parameter</primary>
2345 When <varname>logging_collector</varname> is enabled,
2346 this parameter sets the file names of the created log files. The value
2347 is treated as a <systemitem>strftime</systemitem> pattern,
2348 so <literal>%</literal>-escapes can be used to specify time-varying
2349 file names. (Note that if there are
2350 any time-zone-dependent <literal>%</literal>-escapes, the computation
2351 is done in the zone specified by <xref linkend="guc-log-timezone">.)
2352 If no <literal>%</literal>-escapes are present,
2353 <productname>PostgreSQL</productname> will append the epoch of the new
2354 log file's creation time. For example, if
2355 <varname>log_filename</varname> were <literal>server_log</literal>,
2356 then the chosen file name would be <literal>server_log.1093827753</>
2357 for a log starting at Sun Aug 29 19:02:33 2004 MST.
2358 Note that the system's <systemitem>strftime</systemitem> is not used
2359 directly, so platform-specific (nonstandard) extensions do not work.
2362 If CSV-format output is enabled in <varname>log_destination</>,
2363 <literal>.csv</> will be appended to the timestamped
2364 log file name to create the file name for CSV-format output.
2365 (If <varname>log_filename</> ends in <literal>.log</>, the suffix is
2367 In the case of the example above, the CSV
2368 file name will be <literal>server_log.1093827753.csv</literal>.
2371 This parameter can only be set in the <filename>postgresql.conf</>
2372 file or on the server command line.
2377 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
2378 <term><varname>log_rotation_age</varname> (<type>integer</type>)</term>
2380 <primary><varname>log_rotation_age</> configuration parameter</primary>
2384 When <varname>logging_collector</varname> is enabled,
2385 this parameter determines the maximum lifetime of an individual log file.
2386 After this many minutes have elapsed, a new log file will
2387 be created. Set to zero to disable time-based creation of
2389 This parameter can only be set in the <filename>postgresql.conf</>
2390 file or on the server command line.
2395 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
2396 <term><varname>log_rotation_size</varname> (<type>integer</type>)</term>
2398 <primary><varname>log_rotation_size</> configuration parameter</primary>
2402 When <varname>logging_collector</varname> is enabled,
2403 this parameter determines the maximum size of an individual log file.
2404 After this many kilobytes have been emitted into a log file,
2405 a new log file will be created. Set to zero to disable size-based
2406 creation of new log files.
2407 This parameter can only be set in the <filename>postgresql.conf</>
2408 file or on the server command line.
2413 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
2414 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)</term>
2416 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
2420 When <varname>logging_collector</varname> is enabled,
2421 this parameter will cause <productname>PostgreSQL</productname> to truncate (overwrite),
2422 rather than append to, any existing log file of the same name.
2423 However, truncation will occur only when a new file is being opened
2424 due to time-based rotation, not during server startup or size-based
2425 rotation. When off, pre-existing files will be appended to in
2426 all cases. For example, using this setting in combination with
2427 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
2428 would result in generating twenty-four hourly log files and then
2429 cyclically overwriting them.
2430 This parameter can only be set in the <filename>postgresql.conf</>
2431 file or on the server command line.
2434 Example: To keep 7 days of logs, one log file per day named
2435 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
2436 etc, and automatically overwrite last week's log with this week's log,
2437 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
2438 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>, and
2439 <varname>log_rotation_age</varname> to <literal>1440</literal>.
2442 Example: To keep 24 hours of logs, one log file per hour, but
2443 also rotate sooner if the log file size exceeds 1GB, set
2444 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
2445 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>,
2446 <varname>log_rotation_age</varname> to <literal>60</literal>, and
2447 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
2448 Including <literal>%M</> in <varname>log_filename</varname> allows
2449 any size-driven rotations that might occur to select a file name
2450 different from the hour's initial file name.
2455 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
2456 <term><varname>syslog_facility</varname> (<type>string</type>)</term>
2458 <primary><varname>syslog_facility</> configuration parameter</primary>
2462 When logging to <application>syslog</> is enabled, this parameter
2463 determines the <application>syslog</application>
2464 <quote>facility</quote> to be used. You can choose
2465 from <literal>LOCAL0</>, <literal>LOCAL1</>,
2466 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
2467 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
2468 the default is <literal>LOCAL0</>. See also the
2469 documentation of your system's
2470 <application>syslog</application> daemon.
2471 This parameter can only be set in the <filename>postgresql.conf</>
2472 file or on the server command line.
2477 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
2478 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
2480 <primary><varname>syslog_identity</> configuration parameter</primary>
2484 When logging to <application>syslog</> is enabled, this parameter
2485 determines the program name used to identify
2486 <productname>PostgreSQL</productname> messages in
2487 <application>syslog</application> logs. The default is
2488 <literal>postgres</literal>.
2489 This parameter can only be set in the <filename>postgresql.conf</>
2490 file or on the server command line.
2497 <sect2 id="runtime-config-logging-when">
2498 <title>When To Log</title>
2502 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
2503 <term><varname>client_min_messages</varname> (<type>string</type>)</term>
2505 <primary><varname>client_min_messages</> configuration parameter</primary>
2509 Controls which message levels are sent to the client.
2510 Valid values are <literal>DEBUG5</>,
2511 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
2512 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
2513 <literal>WARNING</>, <literal>ERROR</>, <literal>FATAL</>,
2514 and <literal>PANIC</>. Each level
2515 includes all the levels that follow it. The later the level,
2516 the fewer messages are sent. The default is
2517 <literal>NOTICE</>. Note that <literal>LOG</> has a different
2518 rank here than in <varname>log_min_messages</>.
2523 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
2524 <term><varname>log_min_messages</varname> (<type>string</type>)</term>
2526 <primary><varname>log_min_messages</> configuration parameter</primary>
2530 Controls which message levels are written to the server log.
2531 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
2532 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
2533 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
2534 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
2535 <literal>PANIC</>. Each level includes all the levels that
2536 follow it. The later the level, the fewer messages are sent
2537 to the log. The default is <literal>WARNING</>. Note that
2538 <literal>LOG</> has a different rank here than in
2539 <varname>client_min_messages</>.
2540 Only superusers can change this setting.
2545 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
2546 <term><varname>log_error_verbosity</varname> (<type>string</type>)</term>
2548 <primary><varname>log_error_verbosity</> configuration parameter</primary>
2552 Controls the amount of detail written in the server log for each
2553 message that is logged. Valid values are <literal>TERSE</>,
2554 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
2555 fields to displayed messages.
2556 Only superusers can change this setting.
2561 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
2562 <term><varname>log_min_error_statement</varname> (<type>string</type>)</term>
2564 <primary><varname>log_min_error_statement</> configuration parameter</primary>
2568 Controls whether or not the SQL statement that causes an error
2569 condition will be recorded in the server log. The current
2570 SQL statement is included in the log entry for any message of
2571 the specified severity or higher.
2572 Valid values are <literal>DEBUG5</literal>,
2573 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
2574 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
2575 <literal>INFO</literal>, <literal>NOTICE</literal>,
2576 <literal>WARNING</literal>, <literal>ERROR</literal>,
2577 <literal>LOG</literal>,
2578 <literal>FATAL</literal>, and <literal>PANIC</literal>.
2579 The default is <literal>ERROR</literal>, which means statements
2580 causing errors, log messages, fatal errors, or panics will be logged.
2581 To effectively turn off logging of failing statements,
2582 set this parameter to <literal>PANIC</literal>.
2583 Only superusers can change this setting.
2588 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
2589 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
2591 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
2595 Causes the duration of each completed statement to be logged
2596 if the statement ran for at least the specified number of
2597 milliseconds. Setting this to zero prints all statement durations.
2598 Minus-one (the default) disables logging statement durations.
2599 For example, if you set it to <literal>250ms</literal>
2600 then all SQL statements that run 250ms or longer will be
2601 logged. Enabling this parameter can be helpful in tracking down
2602 unoptimized queries in your applications.
2603 Only superusers can change this setting.
2607 For clients using extended query protocol, durations of the Parse,
2608 Bind, and Execute steps are logged independently.
2613 When using this option together with
2614 <xref linkend="guc-log-statement">,
2615 the text of statements that are logged because of
2616 <varname>log_statement</> will not be repeated in the
2617 duration log message.
2618 If you are not using <application>syslog</>, it is recommended
2619 that you log the PID or session ID using
2620 <xref linkend="guc-log-line-prefix">
2621 so that you can link the statement message to the later
2622 duration message using the process ID or session ID.
2628 <varlistentry id="guc-silent-mode" xreflabel="silent_mode">
2629 <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
2631 <primary><varname>silent_mode</> configuration parameter</primary>
2635 Runs the server silently. If this parameter is set, the server
2636 will automatically run in background and any controlling
2637 terminals are disassociated.
2638 The server's standard output and standard error are redirected
2639 to <literal>/dev/null</>, so any messages sent to them will be lost.
2640 Unless <application>syslog</> logging is selected or
2641 <varname>logging_collector</> is enabled, using this parameter
2642 is discouraged because it makes it impossible to see error messages.
2643 This parameter can only be set at server start.
2651 <xref linkend="runtime-config-severity-levels"> explains the message
2652 severity levels used by <productname>PostgreSQL</>. If logging output
2653 is sent to <systemitem>syslog</systemitem> or Windows'
2654 <systemitem>eventlog</systemitem>, the severity levels are translated
2655 as shown in the table.
2658 <table id="runtime-config-severity-levels">
2659 <title>Message severity levels</title>
2663 <entry>Severity</entry>
2664 <entry>Usage</entry>
2665 <entry><systemitem>syslog</></entry>
2666 <entry><systemitem>eventlog</></entry>
2672 <entry><literal>DEBUG1..DEBUG5</></entry>
2673 <entry>Provides successively-more-detailed information for use by
2675 <entry><literal>DEBUG</></entry>
2676 <entry><literal>INFORMATION</></entry>
2680 <entry><literal>INFO</></entry>
2681 <entry>Provides information implicitly requested by the user,
2682 e.g., output from <command>VACUUM VERBOSE</>.</entry>
2683 <entry><literal>INFO</></entry>
2684 <entry><literal>INFORMATION</></entry>
2688 <entry><literal>NOTICE</></entry>
2689 <entry>Provides information that might be helpful to users, e.g.,
2690 notice of truncation of long identifiers.</entry>
2691 <entry><literal>NOTICE</></entry>
2692 <entry><literal>INFORMATION</></entry>
2696 <entry><literal>WARNING</></entry>
2697 <entry>Provides warnings of likely problems, e.g., <command>COMMIT</>
2698 outside a transaction block.</entry>
2699 <entry><literal>NOTICE</></entry>
2700 <entry><literal>WARNING</></entry>
2704 <entry><literal>ERROR</></entry>
2705 <entry>Reports an error that caused the current command to
2707 <entry><literal>WARNING</></entry>
2708 <entry><literal>ERROR</></entry>
2712 <entry><literal>LOG</></entry>
2713 <entry>Reports information of interest to administrators, e.g.,
2714 checkpoint activity.</entry>
2715 <entry><literal>INFO</></entry>
2716 <entry><literal>INFORMATION</></entry>
2720 <entry><literal>FATAL</></entry>
2721 <entry>Reports an error that caused the current session to
2723 <entry><literal>ERR</></entry>
2724 <entry><literal>ERROR</></entry>
2728 <entry><literal>PANIC</></entry>
2729 <entry>Reports an error that caused all database sessions to abort.</entry>
2730 <entry><literal>CRIT</></entry>
2731 <entry><literal>ERROR</></entry>
2738 <sect2 id="runtime-config-logging-what">
2739 <title>What To Log</title>
2744 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
2745 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
2746 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
2748 <primary><varname>debug_print_parse</> configuration parameter</primary>
2751 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
2754 <primary><varname>debug_print_plan</> configuration parameter</primary>
2758 These parameters enable various debugging output to be emitted.
2759 When set, they print the resulting parse tree, the query rewriter
2760 output, or the execution plan for each executed query.
2761 These messages are emitted at <literal>LOG</> message level, so by
2762 default they will appear in the server log but will not be sent to the
2763 client. You can change that by adjusting
2764 <xref linkend="guc-client-min-messages"> and/or
2765 <xref linkend="guc-log-min-messages">.
2766 These parameters are off by default.
2772 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
2774 <primary><varname>debug_pretty_print</> configuration parameter</primary>
2778 When set, <varname>debug_pretty_print</varname> indents the messages
2779 produced by <varname>debug_print_parse</varname>,
2780 <varname>debug_print_rewritten</varname>, or
2781 <varname>debug_print_plan</varname>. This results in more readable
2782 but much longer output than the <quote>compact</> format used when
2783 it is off. It is on by default.
2788 <varlistentry id="guc-log-checkpoints" xreflabel="log_checkpoints">
2789 <term><varname>log_checkpoints</varname> (<type>boolean</type>)</term>
2791 <primary><varname>log_checkpoints</> configuration parameter</primary>
2795 Causes checkpoints to be logged in the server log. Some
2796 statistics about each checkpoint are included in the log messages,
2797 including the number of buffers written and the time spent writing
2799 This parameter can only be set in the <filename>postgresql.conf</>
2800 file or on the server command line. The default is off.
2805 <varlistentry id="guc-log-connections" xreflabel="log_connections">
2806 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
2808 <primary><varname>log_connections</> configuration parameter</primary>
2812 Causes each attempted connection to the server to be logged,
2813 as well as successful completion of client authentication.
2814 This parameter can only be set in the <filename>postgresql.conf</>
2815 file or on the server command line. The default is off.
2820 Some client programs, like <application>psql</>, attempt
2821 to connect twice while determining if a password is required, so
2822 duplicate <quote>connection received</> messages do not
2823 necessarily indicate a problem.
2829 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
2830 <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
2832 <primary><varname>log_disconnections</> configuration parameter</primary>
2836 This outputs a line in the server log similar to
2837 <varname>log_connections</varname> but at session termination,
2838 and includes the duration of the session. This is off by
2840 This parameter can only be set in the <filename>postgresql.conf</>
2841 file or on the server command line.
2847 <varlistentry id="guc-log-duration" xreflabel="log_duration">
2848 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
2850 <primary><varname>log_duration</> configuration parameter</primary>
2854 Causes the duration of every completed statement to be logged.
2855 The default is <literal>off</>.
2856 Only superusers can change this setting.
2860 For clients using extended query protocol, durations of the Parse,
2861 Bind, and Execute steps are logged independently.
2866 The difference between setting this option and setting
2867 <xref linkend="guc-log-min-duration-statement"> to zero is that
2868 exceeding <varname>log_min_duration_statement</> forces the text of
2869 the query to be logged, but this option doesn't. Thus, if
2870 <varname>log_duration</> is <literal>on</> and
2871 <varname>log_min_duration_statement</> has a positive value, all
2872 durations are logged but the query text is included only for
2873 statements exceeding the threshold. This behavior can be useful for
2874 gathering statistics in high-load installations.
2880 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
2881 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
2883 <primary><varname>log_hostname</> configuration parameter</primary>
2887 By default, connection log messages only show the IP address of the
2888 connecting host. Turning on this parameter causes logging of the
2889 host name as well. Note that depending on your host name resolution
2890 setup this might impose a non-negligible performance penalty.
2891 This parameter can only be set in the <filename>postgresql.conf</>
2892 file or on the server command line.
2897 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
2898 <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
2900 <primary><varname>log_line_prefix</> configuration parameter</primary>
2904 This is a <function>printf</>-style string that is output at the
2905 beginning of each log line.
2906 <literal>%</> characters begin <quote>escape sequences</>
2907 that are replaced with status information as outlined below.
2908 Unrecognized escapes are ignored. Other
2909 characters are copied straight to the log line. Some escapes are
2910 only recognized by session processes, and do not apply to
2911 background processes such as the main server process.
2912 This parameter can only be set in the <filename>postgresql.conf</>
2913 file or on the server command line. The default is an empty string.
2919 <entry>Escape</entry>
2920 <entry>Effect</entry>
2921 <entry>Session only</entry>
2926 <entry><literal>%u</literal></entry>
2927 <entry>User name</entry>
2931 <entry><literal>%d</literal></entry>
2932 <entry>Database name</entry>
2936 <entry><literal>%r</literal></entry>
2937 <entry>Remote host name or IP address, and remote port</entry>
2941 <entry><literal>%h</literal></entry>
2942 <entry>Remote host name or IP address</entry>
2946 <entry><literal>%p</literal></entry>
2947 <entry>Process ID</entry>
2951 <entry><literal>%t</literal></entry>
2952 <entry>Time stamp without milliseconds</entry>
2956 <entry><literal>%m</literal></entry>
2957 <entry>Time stamp with milliseconds</entry>
2961 <entry><literal>%i</literal></entry>
2962 <entry>Command tag: type of session's current command</entry>
2966 <entry><literal>%c</literal></entry>
2967 <entry>Session ID: see below</entry>
2971 <entry><literal>%l</literal></entry>
2972 <entry>Number of the log line for each session or process, starting at 1</entry>
2976 <entry><literal>%s</literal></entry>
2977 <entry>Process start time stamp</entry>
2981 <entry><literal>%v</literal></entry>
2982 <entry>Virtual transaction ID (backendID/localXID)</entry>
2986 <entry><literal>%x</literal></entry>
2987 <entry>Transaction ID (0 if none is assigned)</entry>
2991 <entry><literal>%q</literal></entry>
2992 <entry>Produces no output, but tells non-session
2993 processes to stop at this point in the string; ignored by
2994 session processes</entry>
2998 <entry><literal>%%</literal></entry>
2999 <entry>Literal <literal>%</></entry>
3006 The <literal>%c</> escape prints a quasi-unique session identifier,
3007 consisting of two 4-byte hexadecimal numbers (without leading zeros)
3008 separated by a dot. The numbers are the process start time and the
3009 process ID, so <literal>%c</> can also be used as a space saving way
3010 of printing those items.
3015 If you set a nonempty value for <varname>log_line_prefix</>,
3016 you should usually make its last character be a space, to provide
3017 visual separation from the rest of the log line. A punctuation
3018 character could be used too.
3024 <application>Syslog</> produces its own
3025 time stamp and process ID information, so you probably do not want to
3026 use those escapes if you are logging to <application>syslog</>.
3032 <varlistentry id="guc-log-lock-waits" xreflabel="log_lock_waits">
3033 <term><varname>log_lock_waits</varname> (<type>boolean</type>)</term>
3035 <primary><varname>log_lock_waits</> configuration parameter</primary>
3039 Controls whether a log message is produced when a session waits
3040 longer than <xref linkend="guc-deadlock-timeout"> to acquire a
3041 lock. This is useful in determining if lock waits are causing
3042 poor performance. The default is <literal>off</>.
3047 <varlistentry id="guc-log-statement" xreflabel="log_statement">
3048 <term><varname>log_statement</varname> (<type>string</type>)</term>
3050 <primary><varname>log_statement</> configuration parameter</primary>
3054 Controls which SQL statements are logged. Valid values are
3055 <literal>none</>, <literal>ddl</>, <literal>mod</>, and
3056 <literal>all</>. <literal>ddl</> logs all data definition
3057 statements, such as <command>CREATE</>, <command>ALTER</>, and
3058 <command>DROP</> statements. <literal>mod</> logs all
3059 <literal>ddl</> statements, plus data-modifying statements
3060 such as <command>INSERT</>,
3061 <command>UPDATE</>, <command>DELETE</>, <command>TRUNCATE</>,
3062 and <command>COPY FROM</>.
3063 <command>PREPARE</>, <command>EXECUTE</>, and
3064 <command>EXPLAIN ANALYZE</> statements are also logged if their
3065 contained command is of an appropriate type. For clients using
3066 extended query protocol, logging occurs when an Execute message
3067 is received, and values of the Bind parameters are included
3068 (with any embedded single-quote marks doubled).
3072 The default is <literal>none</>. Only superusers can change this
3078 Statements that contain simple syntax errors are not logged
3079 even by the <varname>log_statement</> = <literal>all</> setting,
3080 because the log message is emitted only after basic parsing has
3081 been done to determine the statement type. In the case of extended
3082 query protocol, this setting likewise does not log statements that
3083 fail before the Execute phase (i.e., during parse analysis or
3084 planning). Set <varname>log_min_error_statement</> to
3085 <literal>ERROR</> (or lower) to log such statements.
3091 <varlistentry id="guc-log-temp-files" xreflabel="log_temp_files">
3092 <term><varname>log_temp_files</varname> (<type>integer</type>)</term>
3094 <primary><varname>log_temp_files</> configuration parameter</primary>
3098 Controls whether temporary files are logged when deleted.
3099 Temporary files can be
3100 created for sorts, hashes, and temporary query results.
3101 A value of zero logs all temporary files, and positive
3102 values log only files whose size is equal or greater than
3103 the specified number of kilobytes. The
3104 default is <literal>-1</>, which disables this logging.
3105 Only superusers can change this setting.
3110 <varlistentry id="guc-log-timezone" xreflabel="log_timezone">
3111 <term><varname>log_timezone</varname> (<type>string</type>)</term>
3113 <primary><varname>log_timezone</> configuration parameter</primary>
3117 Sets the time zone used for timestamps written in the log.
3118 Unlike <xref linkend="guc-timezone">, this value is cluster-wide,
3119 so that all sessions will report timestamps consistently.
3120 The default is <literal>unknown</>, which means to use whatever
3121 the system environment specifies as the time zone. See <xref
3122 linkend="datatype-timezones"> for more information.
3123 This parameter can only be set in the <filename>postgresql.conf</>
3124 file or on the server command line.
3131 <sect2 id="runtime-config-logging-csvlog">
3132 <title>Using CSV-Format Log Output</title>
3135 Including <literal>csvlog</> in the <varname>log_destination</> list
3136 provides a convenient way to import log files into a database table.
3137 This option emits log lines in comma-separated-value format,
3138 with these columns: timestamp with milliseconds, user name, database
3139 name, process ID, host:port number, session ID, per-session or -process line
3140 number, command tag, session start time, virtual transaction ID,
3141 regular transaction id, error severity, SQL state code, error message,
3142 error message detail, hint, internal query that led to the error (if
3143 any), character count of the error position thereof, error context,
3144 user query that led to the error (if any and enabled by
3145 <varname>log_min_error_statement</>), character count of the error
3146 position thereof, location of the error in the PostgreSQL source code
3147 (if <varname>log_error_verbosity</> is set to <literal>verbose</>).
3148 Here is a sample table definition for storing CSV-format log output:
3151 CREATE TABLE postgres_log
3153 log_time timestamp(3) with time zone,
3157 connection_from text,
3159 session_line_num bigint,
3161 session_start_time timestamp with time zone,
3162 virtual_transaction_id text,
3163 transaction_id bigint,
3164 error_severity text,
3165 sql_state_code text,
3169 internal_query text,
3170 internal_query_pos integer,
3175 PRIMARY KEY (session_id, session_line_num)
3181 To import a log file into this table, use the <command>COPY FROM</>
3185 COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
3190 There are a few things you need to do to simplify importing CSV log
3191 files easily and automatically:
3196 Set <varname>log_filename</varname> and
3197 <varname>log_rotation_age</> to provide a consistent,
3198 predictable naming scheme for your log files. This lets you
3199 predict what the file name will be and know when an individual log
3200 file is complete and therefore ready to be imported.
3206 Set <varname>log_rotation_size</varname> to 0 to disable
3207 size-based log rotation, as it makes the log file name difficult
3214 Set <varname>log_truncate_on_rotation</varname> to <literal>on</> so
3215 that old log data isn't mixed with the new in the same file.
3221 The table definition above includes a primary key specification.
3222 This is useful to protect against accidentally importing the same
3223 information twice. The <command>COPY</> command commits all of the
3224 data it imports at one time, so any error will cause the entire
3225 import to fail. If you import a partial log file and later import
3226 the file again when it is complete, the primary key violation will
3227 cause the import to fail. Wait until the log is complete and
3228 closed before importing. This procedure will also protect against
3229 accidentally importing a partial line that hasn't been completely
3230 written, which would also cause <command>COPY</> to fail.
3239 <sect1 id="runtime-config-statistics">
3240 <title>Run-Time Statistics</title>
3242 <sect2 id="runtime-config-statistics-collector">
3243 <title>Query and Index Statistics Collector</title>
3246 These parameters control server-wide statistics collection features.
3247 When statistics collection is enabled, the data that is produced can be
3248 accessed via the <structname>pg_stat</structname> and
3249 <structname>pg_statio</structname> family of system views.
3250 Refer to <xref linkend="monitoring"> for more information.
3255 <varlistentry id="guc-track-activities" xreflabel="track_activities">
3256 <term><varname>track_activities</varname> (<type>boolean</type>)</term>
3258 <primary><varname>track_activities</> configuration parameter</primary>
3262 Enables the collection of information on the currently
3263 executing command of each session, along with the time at
3264 which that command began execution. This parameter is on by
3265 default. Note that even when enabled, this information is not
3266 visible to all users, only to superusers and the user owning
3267 the session being reported on; so it should not represent a
3269 Only superusers can change this setting.
3274 <varlistentry id="guc-track-activity-query-size" xreflabel="track_activity_query_size">
3275 <term><varname>track_activity_query_size</varname> (<type>integer</type>)</term>
3277 <primary><varname>track_activity_query_size</> configuration parameter</primary>
3281 Specifies the number of bytes reserved to track the currently
3282 executing command for each active session, for the
3283 <structname>pg_stat_activity</>.<structfield>current_query</> field.
3284 The default value is 1024. This parameter can only be set at server
3290 <varlistentry id="guc-track-counts" xreflabel="track_counts">
3291 <term><varname>track_counts</varname> (<type>boolean</type>)</term>
3293 <primary><varname>track_counts</> configuration parameter</primary>
3297 Enables collection of statistics on database activity.
3298 This parameter is on by default, because the autovacuum
3299 daemon needs the collected information.
3300 Only superusers can change this setting.
3305 <varlistentry id="guc-track-functions" xreflabel="track_functions">
3306 <term><varname>track_functions</varname> (<type>string</type>)</term>
3308 <primary><varname>track_functions</> configuration parameter</primary>
3312 Enables tracking of function call counts and time used. Specify
3313 <literal>pl</literal> to count only procedural language functions,
3314 <literal>all</literal> to also track SQL and C language functions.
3315 The default is <literal>none</literal>.
3316 Only superusers can change this setting.
3321 <varlistentry id="guc-update-process-title" xreflabel="update_process_title">
3322 <term><varname>update_process_title</varname> (<type>boolean</type>)</term>
3324 <primary><varname>update_process_title</> configuration parameter</primary>
3328 Enables updating of the process title every time a new SQL command
3329 is received by the server. The process title is typically viewed
3330 by the <command>ps</> command,
3331 or in Windows by using the <application>Process Explorer</>.
3332 Only superusers can change this setting.
3337 <varlistentry id="guc-stats-temp-directory" xreflabel="stats_temp_directory">
3338 <term><varname>stats_temp_directory</varname> (<type>string</type>)</term>
3340 <primary><varname>stats_temp_directory</> configuration parameter</primary>
3344 Sets the directory to store temporary statistics data in. This can be
3345 a path relative to the data directory or an absolute path. The default
3346 is <filename>pg_stat_tmp</filename>. Pointing this at a RAM based
3347 filesystem will decrease physical I/O requirements and can lead to
3348 improved performance.
3349 This parameter can only be set in the <filename>postgresql.conf</>
3350 file or on the server command line.
3358 <sect2 id="runtime-config-statistics-monitor">
3359 <title>Statistics Monitoring</title>
3363 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
3364 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
3365 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
3366 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
3368 <primary><varname>log_statement_stats</> configuration parameter</primary>
3371 <primary><varname>log_parser_stats</> configuration parameter</primary>
3374 <primary><varname>log_planner_stats</> configuration parameter</primary>
3377 <primary><varname>log_executor_stats</> configuration parameter</primary>
3381 For each query, write performance statistics of the respective
3382 module to the server log. This is a crude profiling
3383 instrument. <varname>log_statement_stats</varname> reports total
3384 statement statistics, while the others report per-module statistics.
3385 <varname>log_statement_stats</varname> cannot be enabled together with
3386 any of the per-module options. All of these options are disabled by
3387 default. Only superusers can change these settings.
3397 <sect1 id="runtime-config-autovacuum">
3398 <title>Automatic Vacuuming</title>
3401 <primary>autovacuum</primary>
3402 <secondary>configuration parameters</secondary>
3406 These settings control the behavior of the <firstterm>autovacuum</>
3407 feature. Refer to <xref linkend="autovacuum"> for
3413 <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
3414 <term><varname>autovacuum</varname> (<type>boolean</type>)</term>
3416 <primary><varname>autovacuum</> configuration parameter</primary>
3420 Controls whether the server should run the
3421 autovacuum launcher daemon. This is on by default; however,
3422 <xref linkend="guc-track-counts"> must also be turned on for
3424 This parameter can only be set in the <filename>postgresql.conf</>
3425 file or on the server command line.
3428 Note that even when this parameter is disabled, the system
3429 will launch autovacuum processes if necessary to
3430 prevent transaction ID wraparound. See <xref
3431 linkend="vacuum-for-wraparound"> for more information.
3436 <varlistentry id="guc-log-autovacuum-min-duration" xreflabel="log_autovacuum_min_duration">
3437 <term><varname>log_autovacuum_min_duration</varname> (<type>integer</type>)</term>
3439 <primary><varname>log_autovacuum_min_duration</> configuration parameter</primary>
3443 Causes each action executed by autovacuum to be logged if it ran for at
3444 least the specified number of milliseconds. Setting this to zero logs
3445 all autovacuum actions. Minus-one (the default) disables logging
3446 autovacuum actions. For example, if you set this to
3447 <literal>250ms</literal> then all automatic vacuums and analyzes that run
3448 250ms or longer will be logged. Enabling this parameter can be helpful
3449 in tracking autovacuum activity. This setting can only be set in
3450 the <filename>postgresql.conf</> file or on the server command line.
3455 <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
3456 <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)</term>
3458 <primary><varname>autovacuum_max_workers</> configuration parameter</primary>
3462 Specifies the maximum number of autovacuum processes (other than the
3463 autovacuum launcher) which may be running at any one time. The default
3464 is three. This parameter can only be set in
3465 the <filename>postgresql.conf</> file or on the server command line.
3470 <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
3471 <term><varname>autovacuum_naptime</varname> (<type>integer</type>)</term>
3473 <primary><varname>autovacuum_naptime</> configuration parameter</primary>
3477 Specifies the minimum delay between autovacuum runs on any given
3478 database. In each round the daemon examines the
3479 database and issues <command>VACUUM</> and <command>ANALYZE</> commands
3480 as needed for tables in that database. The delay is measured
3481 in seconds, and the default is one minute (<literal>1m</>).
3482 This parameter can only be set in the <filename>postgresql.conf</>
3483 file or on the server command line.
3488 <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
3489 <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)</term>
3491 <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
3495 Specifies the minimum number of updated or deleted tuples needed
3496 to trigger a <command>VACUUM</> in any one table.
3497 The default is 50 tuples.
3498 This parameter can only be set in the <filename>postgresql.conf</>
3499 file or on the server command line.
3500 This setting can be overridden for individual tables by entries in
3501 <structname>pg_autovacuum</>.
3506 <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
3507 <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)</term>
3509 <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
3513 Specifies the minimum number of inserted, updated or deleted tuples
3514 needed to trigger an <command>ANALYZE</> in any one table.
3515 The default is 50 tuples.
3516 This parameter can only be set in the <filename>postgresql.conf</>
3517 file or on the server command line.
3518 This setting can be overridden for individual tables by entries in
3519 <structname>pg_autovacuum</>.
3524 <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
3525 <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)</term>
3527 <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
3531 Specifies a fraction of the table size to add to
3532 <varname>autovacuum_vacuum_threshold</varname>
3533 when deciding whether to trigger a <command>VACUUM</>.
3534 The default is 0.2 (20% of table size).
3535 This parameter can only be set in the <filename>postgresql.conf</>
3536 file or on the server command line.
3537 This setting can be overridden for individual tables by entries in
3538 <structname>pg_autovacuum</>.
3543 <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
3544 <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)</term>
3546 <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
3550 Specifies a fraction of the table size to add to
3551 <varname>autovacuum_analyze_threshold</varname>
3552 when deciding whether to trigger an <command>ANALYZE</>.
3553 The default is 0.1 (10% of table size).
3554 This parameter can only be set in the <filename>postgresql.conf</>
3555 file or on the server command line.
3556 This setting can be overridden for individual tables by entries in
3557 <structname>pg_autovacuum</>.
3562 <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
3563 <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)</term>
3565 <primary><varname>autovacuum_freeze_max_age</> configuration parameter</primary>
3569 Specifies the maximum age (in transactions) that a table's
3570 <structname>pg_class</>.<structfield>relfrozenxid</> field can
3571 attain before a <command>VACUUM</> operation is forced to prevent
3572 transaction ID wraparound within the table. Note that the system
3573 will launch autovacuum processes to prevent wraparound even when
3574 autovacuum is otherwise disabled.
3575 The default is 200 million transactions.
3576 This parameter can only be set at server start, but the setting
3577 can be reduced for individual tables by entries in
3578 <structname>pg_autovacuum</>.
3579 For more information see <xref linkend="vacuum-for-wraparound">.
3584 <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
3585 <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
3587 <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
3591 Specifies the cost delay value that will be used in automatic
3592 <command>VACUUM</> operations. If <literal>-1</> is
3593 specified, the regular
3594 <xref linkend="guc-vacuum-cost-delay"> value will be used.
3595 The default value is 20 milliseconds.
3596 This parameter can only be set in the <filename>postgresql.conf</>
3597 file or on the server command line.
3598 This setting can be overridden for individual tables by entries in
3599 <structname>pg_autovacuum</>.
3604 <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
3605 <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
3607 <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
3611 Specifies the cost limit value that will be used in automatic
3612 <command>VACUUM</> operations. If <literal>-1</> is specified (which is the
3613 default), the regular
3614 <xref linkend="guc-vacuum-cost-limit"> value will be used. Note that
3615 the value is distributed proportionally among the running autovacuum
3616 workers, if there is more than one, so that the sum of the limits of
3617 each worker never exceeds the limit on this variable.
3618 This parameter can only be set in the <filename>postgresql.conf</>
3619 file or on the server command line.
3620 This setting can be overridden for individual tables by entries in
3621 <structname>pg_autovacuum</>.
3629 <sect1 id="runtime-config-client">
3630 <title>Client Connection Defaults</title>
3632 <sect2 id="runtime-config-client-statement">
3633 <title>Statement Behavior</title>
3636 <varlistentry id="guc-search-path" xreflabel="search_path">
3637 <term><varname>search_path</varname> (<type>string</type>)</term>
3639 <primary><varname>search_path</> configuration parameter</primary>
3641 <indexterm><primary>path</><secondary>for schemas</></>
3644 This variable specifies the order in which schemas are searched
3645 when an object (table, data type, function, etc.) is referenced by a
3646 simple name with no schema component. When there are objects of
3647 identical names in different schemas, the one found first
3648 in the search path is used. An object that is not in any of the
3649 schemas in the search path can only be referenced by specifying
3650 its containing schema with a qualified (dotted) name.
3654 The value for <varname>search_path</varname> has to be a comma-separated
3655 list of schema names. If one of the list items is
3656 the special value <literal>$user</literal>, then the schema
3657 having the name returned by <function>SESSION_USER</> is substituted, if there
3658 is such a schema. (If not, <literal>$user</literal> is ignored.)
3662 The system catalog schema, <literal>pg_catalog</>, is always
3663 searched, whether it is mentioned in the path or not. If it is
3664 mentioned in the path then it will be searched in the specified
3665 order. If <literal>pg_catalog</> is not in the path then it will
3666 be searched <emphasis>before</> searching any of the path items.
3670 Likewise, the current session's temporary-table schema,
3671 <literal>pg_temp_<replaceable>nnn</></>, is always searched if it
3672 exists. It can be explicitly listed in the path by using the
3673 alias <literal>pg_temp</>. If it is not listed in the path then
3674 it is searched first (before even <literal>pg_catalog</>). However,
3675 the temporary schema is only searched for relation (table, view,
3676 sequence, etc) and data type names. It will never be searched for
3677 function or operator names.
3681 When objects are created without specifying a particular target
3682 schema, they will be placed in the first schema listed
3683 in the search path. An error is reported if the search path is
3688 The default value for this parameter is
3689 <literal>'"$user", public'</literal> (where the second part will be
3690 ignored if there is no schema named <literal>public</>).
3691 This supports shared use of a database (where no users
3692 have private schemas, and all share use of <literal>public</>),
3693 private per-user schemas, and combinations of these. Other
3694 effects can be obtained by altering the default search path
3695 setting, either globally or per-user.
3699 The current effective value of the search path can be examined
3700 via the <acronym>SQL</acronym> function
3701 <function>current_schemas()</>. This is not quite the same as
3702 examining the value of <varname>search_path</varname>, since
3703 <function>current_schemas()</> shows how the requests
3704 appearing in <varname>search_path</varname> were resolved.
3708 For more information on schema handling, see <xref linkend="ddl-schemas">.
3713 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
3714 <term><varname>default_tablespace</varname> (<type>string</type>)</term>
3716 <primary><varname>default_tablespace</> configuration parameter</primary>
3718 <indexterm><primary>tablespace</><secondary>default</></>
3721 This variable specifies the default tablespace in which to create
3722 objects (tables and indexes) when a <command>CREATE</> command does
3723 not explicitly specify a tablespace.
3727 The value is either the name of a tablespace, or an empty string
3728 to specify using the default tablespace of the current database.
3729 If the value does not match the name of any existing tablespace,
3730 <productname>PostgreSQL</> will automatically use the default
3731 tablespace of the current database. If a nondefault tablespace
3732 is specified, the user must have <literal>CREATE</> privilege
3733 for it, or creation attempts will fail.
3737 This variable is not used for temporary tables; for them,
3738 <xref linkend="guc-temp-tablespaces"> is consulted instead.
3742 For more information on tablespaces,
3743 see <xref linkend="manage-ag-tablespaces">.
3748 <varlistentry id="guc-temp-tablespaces" xreflabel="temp_tablespaces">
3749 <term><varname>temp_tablespaces</varname> (<type>string</type>)</term>
3751 <primary><varname>temp_tablespaces</> configuration parameter</primary>
3753 <indexterm><primary>tablespace</><secondary>temporary</></>
3756 This variable specifies tablespace(s) in which to create temporary
3757 objects (temp tables and indexes on temp tables) when a
3758 <command>CREATE</> command does not explicitly specify a tablespace.
3759 Temporary files for purposes such as sorting large data sets
3760 are also created in these tablespace(s).
3764 The value is a list of names of tablespaces. When there is more than
3765 one name in the list, <productname>PostgreSQL</> chooses a random
3766 member of the list each time a temporary object is to be created;
3767 except that within a transaction, successively created temporary
3768 objects are placed in successive tablespaces from the list.
3769 If the selected element of the list is an empty string,
3770 <productname>PostgreSQL</> will automatically use the default
3771 tablespace of the current database instead.
3775 When <varname>temp_tablespaces</> is set interactively, specifying a
3776 nonexistent tablespace is an error, as is specifying a tablespace for
3777 which the user does not have <literal>CREATE</> privilege. However,
3778 when using a previously set value, nonexistent tablespaces are
3779 ignored, as are tablespaces for which the user lacks
3780 <literal>CREATE</> privilege. In particular, this rule applies when
3781 using a value set in <filename>postgresql.conf</>.
3785 The default value is an empty string, which results in all temporary
3786 objects being created in the default tablespace of the current
3791 See also <xref linkend="guc-default-tablespace">.
3796 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
3797 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
3799 <primary><varname>check_function_bodies</> configuration parameter</primary>
3803 This parameter is normally on. When set to <literal>off</>, it
3804 disables validation of the function body string during <xref
3805 linkend="sql-createfunction"
3806 endterm="sql-createfunction-title">. Disabling validation is
3807 occasionally useful to avoid problems such as forward references
3808 when restoring function definitions from a dump.
3813 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
3815 <primary>transaction isolation level</primary>
3818 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
3820 <term><varname>default_transaction_isolation</varname> (<type>string</type>)</term>
3823 Each SQL transaction has an isolation level, which can be
3824 either <quote>read uncommitted</quote>, <quote>read
3825 committed</quote>, <quote>repeatable read</quote>, or
3826 <quote>serializable</quote>. This parameter controls the
3827 default isolation level of each new transaction. The default
3828 is <quote>read committed</quote>.
3832 Consult <xref linkend="mvcc"> and <xref
3833 linkend="sql-set-transaction"
3834 endterm="sql-set-transaction-title"> for more information.
3839 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
3841 <primary>read-only transaction</primary>
3844 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
3847 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
3850 A read-only SQL transaction cannot alter non-temporary tables.
3851 This parameter controls the default read-only status of each new
3852 transaction. The default is <literal>off</> (read/write).
3856 Consult <xref linkend="sql-set-transaction"
3857 endterm="sql-set-transaction-title"> for more information.
3862 <varlistentry id="guc-session-replication-role" xreflabel="session_replication_role">
3863 <term><varname>session_replication_role</varname> (<type>string</type>)</term>
3865 <primary><varname>session_replication_role</> configuration parameter</primary>
3869 Controls firing of replication-related triggers and rules for the
3870 current session. Setting this variable requires
3871 superuser privilege and results in discarding any previously cached
3872 query plans. Possible values are <literal>origin</> (the default),
3873 <literal>replica</> and <literal>local</>.
3874 See <xref linkend="sql-altertable" endterm="sql-altertable-title"> for
3880 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
3881 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
3883 <primary><varname>statement_timeout</> configuration parameter</primary>
3887 Abort any statement that takes over the specified number of
3888 milliseconds, starting from the time the command arrives at the server
3889 from the client. If <varname>log_min_error_statement</> is set to
3890 <literal>ERROR</> or lower, the statement that timed out will also be
3891 logged. A value of zero (the default) turns off the
3896 Setting <varname>statement_timeout</> in
3897 <filename>postgresql.conf</> is not recommended because it
3898 affects all sessions.
3903 <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
3904 <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)</term>
3906 <primary><varname>vacuum_freeze_min_age</> configuration parameter</primary>
3910 Specifies the cutoff age (in transactions) that <command>VACUUM</>
3911 should use to decide whether to replace transaction IDs with
3912 <literal>FrozenXID</> while scanning a table.
3913 The default is 100 million transactions. Although
3914 users can set this value anywhere from zero to one billion,
3915 <command>VACUUM</> will silently limit the effective value to half
3916 the value of <xref linkend="guc-autovacuum-freeze-max-age">, so
3917 that there is not an unreasonably short time between forced
3918 autovacuums. For more information see <xref
3919 linkend="vacuum-for-wraparound">.
3924 <varlistentry id="guc-xmlbinary" xreflabel="xmlbinary">
3925 <term><varname>xmlbinary</varname> (<type>string</type>)</term>
3927 <primary><varname>xmlbinary</> configuration parameter</primary>
3931 Sets how binary values are to be encoded in XML. This applies
3932 for example when <type>bytea</type> values are converted to
3933 XML by the functions <function>xmlelement</function> or
3934 <function>xmlforest</function>. Possible values are
3935 <literal>base64</literal> and <literal>hex</literal>, which
3936 are both defined in the XML Schema standard. The default is
3937 <literal>base64</literal>. For further information about
3938 XML-related functions, see <xref linkend="functions-xml">.
3942 The actual choice here is mostly a matter of taste,
3943 constrained only by possible restrictions in client
3944 applications. Both methods support all possible values,
3945 although the hex encoding will be somewhat larger than the
3951 <varlistentry id="guc-xmloption" xreflabel="xmloption">
3952 <term><varname>xmloption</varname> (<type>string</type>)</term>
3954 <primary><varname>xmloption</> configuration parameter</primary>
3957 <primary><varname>SET XML OPTION</></primary>
3960 <primary>XML option</primary>
3964 Sets whether <literal>DOCUMENT</literal> or
3965 <literal>CONTENT</literal> is implicit when converting between
3966 XML and character string values. See <xref
3967 linkend="datatype-xml"> for a description of this. Valid
3968 values are <literal>DOCUMENT</literal> and
3969 <literal>CONTENT</literal>. The default is
3970 <literal>CONTENT</literal>.
3974 According to the SQL standard, the command to set this option is
3976 SET XML OPTION { DOCUMENT | CONTENT };
3978 This syntax is also available in PostgreSQL.
3985 <sect2 id="runtime-config-client-format">
3986 <title>Locale and Formatting</title>
3990 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
3991 <term><varname>DateStyle</varname> (<type>string</type>)</term>
3993 <primary><varname>DateStyle</> configuration parameter</primary>
3997 Sets the display format for date and time values, as well as the
3998 rules for interpreting ambiguous date input values. For
3999 historical reasons, this variable contains two independent
4000 components: the output format specification (<literal>ISO</>,
4001 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
4002 and the input/output specification for year/month/day ordering
4003 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
4004 can be set separately or together. The keywords <literal>Euro</>
4005 and <literal>European</> are synonyms for <literal>DMY</>; the
4006 keywords <literal>US</>, <literal>NonEuro</>, and
4007 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
4008 <xref linkend="datatype-datetime"> for more information. The
4009 built-in default is <literal>ISO, MDY</>, but
4010 <application>initdb</application> will initialize the
4011 configuration file with a setting that corresponds to the
4012 behavior of the chosen <varname>lc_time</varname> locale.
4017 <varlistentry id="guc-intervalstyle" xreflabel="IntervalStyle">
4018 <term><varname>IntervalStyle</varname> (<type>string</type>)</term>
4020 <primary><varname>IntervalStyle</> configuration parameter</primary>
4024 Sets the display format for interval values.
4025 The value <literal>sql_standard</> will produce
4026 output matching <acronym>SQL</acronym> standard interval literals.
4027 The value <literal>postgres</> (which is the default) will produce
4028 output matching <productname>PostgreSQL</> releases prior to 8.4
4029 when the <xref linkend="guc-datestyle">
4030 parameter was set to <literal>ISO</>.
4031 The value <literal>postgres_verbose</> will produce output
4032 matching <productname>PostgreSQL</> releases prior to 8.4
4033 when the <varname>DateStyle</>
4034 parameter was set to non-<literal>ISO</> output.
4035 The value <literal>iso_8601</> will produce output matching the time
4036 interval <quote>format with designators</> defined in section
4037 4.4.3.2 of ISO 8601.
4040 The <varname>IntervalStyle</> parameter also affects the
4041 interpretation of ambiguous interval input. See
4042 <xref linkend="datatype-interval-input"> for more information.
4047 <varlistentry id="guc-timezone" xreflabel="timezone">
4048 <term><varname>timezone</varname> (<type>string</type>)</term>
4050 <primary><varname>timezone</> configuration parameter</primary>
4052 <indexterm><primary>time zone</></>
4055 Sets the time zone for displaying and interpreting time stamps.
4056 The default is <literal>unknown</>, which means to use whatever
4057 the system environment specifies as the time zone. See <xref
4058 linkend="datatype-timezones"> for more
4064 <varlistentry id="guc-timezone-abbreviations" xreflabel="timezone_abbreviations">
4065 <term><varname>timezone_abbreviations</varname> (<type>string</type>)</term>
4067 <primary><varname>timezone_abbreviations</> configuration parameter</primary>
4069 <indexterm><primary>time zone names</></>
4072 Sets the collection of time zone abbreviations that will be accepted
4073 by the server for datetime input. The default is <literal>'Default'</>,
4074 which is a collection that works in most of the world; there are
4075 also 'Australia' and 'India', and other collections can be defined
4076 for a particular installation. See <xref
4077 linkend="datetime-appendix"> for more information.
4082 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
4084 <primary>significant digits</primary>
4087 <primary>floating-point</primary>
4088 <secondary>display</secondary>
4091 <primary><varname>extra_float_digits</> configuration parameter</primary>
4094 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
4097 This parameter adjusts the number of digits displayed for
4098 floating-point values, including <type>float4</>, <type>float8</>,
4099 and geometric data types. The parameter value is added to the
4100 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
4101 as appropriate). The value can be set as high as 2, to include
4102 partially-significant digits; this is especially useful for dumping
4103 float data that needs to be restored exactly. Or it can be set
4104 negative to suppress unwanted digits.
4109 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
4110 <term><varname>client_encoding</varname> (<type>string</type>)</term>
4112 <primary><varname>client_encoding</> configuration parameter</primary>
4114 <indexterm><primary>character set</></>
4117 Sets the client-side encoding (character set).
4118 The default is to use the database encoding.
4123 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
4124 <term><varname>lc_messages</varname> (<type>string</type>)</term>
4126 <primary><varname>lc_messages</> configuration parameter</primary>
4130 Sets the language in which messages are displayed. Acceptable
4131 values are system-dependent; see <xref linkend="locale"> for
4132 more information. If this variable is set to the empty string
4133 (which is the default) then the value is inherited from the
4134 execution environment of the server in a system-dependent way.
4138 On some systems, this locale category does not exist. Setting
4139 this variable will still work, but there will be no effect.
4140 Also, there is a chance that no translated messages for the
4141 desired language exist. In that case you will continue to see
4142 the English messages.
4146 Only superusers can change this setting, because it affects the
4147 messages sent to the server log as well as to the client.
4152 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
4153 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
4155 <primary><varname>lc_monetary</> configuration parameter</primary>
4159 Sets the locale to use for formatting monetary amounts, for
4160 example with the <function>to_char</function> family of
4161 functions. Acceptable values are system-dependent; see <xref
4162 linkend="locale"> for more information. If this variable is
4163 set to the empty string (which is the default) then the value
4164 is inherited from the execution environment of the server in a
4165 system-dependent way.
4170 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
4171 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
4173 <primary><varname>lc_numeric</> configuration parameter</primary>
4177 Sets the locale to use for formatting numbers, for example
4178 with the <function>to_char</function> family of
4179 functions. Acceptable values are system-dependent; see <xref
4180 linkend="locale"> for more information. If this variable is
4181 set to the empty string (which is the default) then the value
4182 is inherited from the execution environment of the server in a
4183 system-dependent way.
4188 <varlistentry id="guc-lc-time" xreflabel="lc_time">
4189 <term><varname>lc_time</varname> (<type>string</type>)</term>
4191 <primary><varname>lc_time</> configuration parameter</primary>
4195 Sets the locale to use for formatting dates and times, for example
4196 with the <function>to_char</function> family of
4197 functions. Acceptable values are system-dependent; see <xref
4198 linkend="locale"> for more information. If this variable is
4199 set to the empty string (which is the default) then the value
4200 is inherited from the execution environment of the server in a
4201 system-dependent way.
4206 <varlistentry id="guc-default-text-search-config" xreflabel="default_text_search_config">
4207 <term><varname>default_text_search_config</varname> (<type>string</type>)</term>
4209 <primary><varname>default_text_search_config</> configuration parameter</primary>
4213 Selects the text search configuration that is used by those variants
4214 of the text search functions that do not have an explicit argument
4215 specifying the configuration.
4216 See <xref linkend="textsearch"> for further information.
4217 The built-in default is <literal>pg_catalog.simple</>, but
4218 <application>initdb</application> will initialize the
4219 configuration file with a setting that corresponds to the
4220 chosen <varname>lc_ctype</varname> locale, if a configuration
4221 matching that locale can be identified.
4229 <sect2 id="runtime-config-client-other">
4230 <title>Other Defaults</title>
4234 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
4235 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
4237 <primary><varname>dynamic_library_path</> configuration parameter</primary>
4239 <indexterm><primary>dynamic loading</></>
4242 If a dynamically loadable module needs to be opened and the
4243 file name specified in the <command>CREATE FUNCTION</command> or
4244 <command>LOAD</command> command
4245 does not have a directory component (i.e. the
4246 name does not contain a slash), the system will search this
4247 path for the required file.
4251 The value for <varname>dynamic_library_path</varname> has to be a
4252 list of absolute directory paths separated by colons (or semi-colons
4253 on Windows). If a list element starts
4254 with the special string <literal>$libdir</literal>, the
4255 compiled-in <productname>PostgreSQL</productname> package
4256 library directory is substituted for <literal>$libdir</literal>. This
4257 is where the modules provided by the standard
4258 <productname>PostgreSQL</productname> distribution are installed.
4259 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
4260 this directory.) For example:
4262 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
4264 or, in a Windows environment:
4266 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
4271 The default value for this parameter is
4272 <literal>'$libdir'</literal>. If the value is set to an empty
4273 string, the automatic path search is turned off.
4277 This parameter can be changed at run time by superusers, but a
4278 setting done that way will only persist until the end of the
4279 client connection, so this method should be reserved for
4280 development purposes. The recommended way to set this parameter
4281 is in the <filename>postgresql.conf</filename> configuration
4287 <varlistentry id="guc-gin-fuzzy-search-limit" xreflabel="gin_fuzzy_search_limit">
4288 <term><varname>gin_fuzzy_search_limit</varname> (<type>integer</type>)</term>
4290 <primary><varname>gin_fuzzy_search_limit</> configuration parameter</primary>
4294 Soft upper limit of the size of the set returned by GIN index. For more
4295 information see <xref linkend="gin-tips">.
4300 <varlistentry id="guc-local-preload-libraries" xreflabel="local_preload_libraries">
4301 <term><varname>local_preload_libraries</varname> (<type>string</type>)</term>
4303 <primary><varname>local_preload_libraries</> configuration parameter</primary>
4306 <primary><filename>$libdir/plugins</></primary>
4310 This variable specifies one or more shared libraries that are
4311 to be preloaded at connection start. If more than one library
4312 is to be loaded, separate their names with commas.
4313 This parameter cannot be changed after the start of a particular
4318 Because this is not a superuser-only option, the libraries
4319 that can be loaded are restricted to those appearing in the
4320 <filename>plugins</> subdirectory of the installation's
4321 standard library directory. (It is the database administrator's
4322 responsibility to ensure that only <quote>safe</> libraries
4323 are installed there.) Entries in <varname>local_preload_libraries</>
4324 can specify this directory explicitly, for example
4325 <literal>$libdir/plugins/mylib</literal>, or just specify
4326 the library name — <literal>mylib</literal> would have
4327 the same effect as <literal>$libdir/plugins/mylib</literal>.
4331 There is no performance advantage to loading a library at session
4332 start rather than when it is first used. Rather, the intent of
4333 this feature is to allow debugging or performance-measurement
4334 libraries to be loaded into specific sessions without an explicit
4335 <command>LOAD</> command being given. For example, debugging could
4336 be enabled for all sessions under a given user name by setting
4337 this parameter with <command>ALTER USER SET</>.
4341 If a specified library is not found,
4342 the connection attempt will fail.
4346 Every PostgreSQL-supported library has a <quote>magic
4347 block</> that is checked to guarantee compatibility.
4348 For this reason, non-PostgreSQL libraries cannot be
4358 <sect1 id="runtime-config-locks">
4359 <title>Lock Management</title>
4363 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
4365 <primary>deadlock</primary>
4366 <secondary>timeout during</secondary>
4369 <primary>timeout</primary>
4370 <secondary>deadlock</secondary>
4373 <primary><varname>deadlock_timeout</> configuration parameter</primary>
4376 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
4379 This is the amount of time, in milliseconds, to wait on a lock
4380 before checking to see if there is a deadlock condition. The
4381 check for deadlock is relatively slow, so the server doesn't run
4382 it every time it waits for a lock. We optimistically assume
4383 that deadlocks are not common in production applications and
4384 just wait on the lock for a while before starting the check for a
4385 deadlock. Increasing this value reduces the amount of time
4386 wasted in needless deadlock checks, but slows down reporting of
4387 real deadlock errors. The default is one second (<literal>1s</>),
4388 which is probably about the smallest value you would want in
4389 practice. On a heavily loaded server you might want to raise it.
4390 Ideally the setting should exceed your typical transaction time,
4391 so as to improve the odds that a lock will be released before
4392 the waiter decides to check for deadlock.
4396 When <xref linkend="guc-log-lock-waits"> is set,
4397 this parameter also determines the length of time to wait before
4398 a log message is issued about the lock wait. If you are trying
4399 to investigate locking delays you might want to set a shorter than
4400 normal <varname>deadlock_timeout</varname>.
4405 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
4406 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
4408 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
4412 The shared lock table is created to track locks on
4413 <varname>max_locks_per_transaction</varname> * (<xref
4414 linkend="guc-max-connections"> + <xref
4415 linkend="guc-max-prepared-transactions">) objects (e.g. tables);
4416 hence, no more than this many distinct objects can be locked at
4417 any one time. This parameter controls the average number of object
4418 locks allocated for each transaction; individual transactions
4419 can lock more objects as long as the locks of all transactions
4420 fit in the lock table. This is <emphasis>not</> the number of
4421 rows that can be locked; that value is unlimited. The default,
4422 64, has historically proven sufficient, but you might need to
4423 raise this value if you have clients that touch many different
4424 tables in a single transaction. This parameter can only be set at
4429 Increasing this parameter might cause <productname>PostgreSQL</>
4430 to request more <systemitem class="osname">System V</> shared
4431 memory than your operating system's default configuration
4432 allows. See <xref linkend="sysvipc"> for information on how to
4433 adjust those parameters, if necessary.
4441 <sect1 id="runtime-config-compatible">
4442 <title>Version and Platform Compatibility</title>
4444 <sect2 id="runtime-config-compatible-version">
4445 <title>Previous PostgreSQL Versions</title>
4449 <varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
4450 <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
4451 <indexterm><primary>FROM</><secondary>missing</></>
4453 <primary><varname>add_missing_from</> configuration parameter</primary>
4457 When on, tables that are referenced by a query will be
4458 automatically added to the <literal>FROM</> clause if not
4459 already present. This behavior does not comply with the SQL
4460 standard and many people dislike it because it can mask mistakes
4461 (such as referencing a table where you should have referenced
4462 its alias). The default is <literal>off</>. This variable can be
4463 enabled for compatibility with releases of
4464 <productname>PostgreSQL</> prior to 8.1, where this behavior was
4469 Note that even when this variable is enabled, a warning
4470 message will be emitted for each implicit <literal>FROM</>
4471 entry referenced by a query. Users are encouraged to update
4472 their applications to not rely on this behavior, by adding all
4473 tables referenced by a query to the query's <literal>FROM</>
4474 clause (or its <literal>USING</> clause in the case of
4475 <command>DELETE</>).
4480 <varlistentry id="guc-array-nulls" xreflabel="array_nulls">
4481 <term><varname>array_nulls</varname> (<type>boolean</type>)</term>
4483 <primary><varname>array_nulls</> configuration parameter</primary>
4487 This controls whether the array input parser recognizes
4488 unquoted <literal>NULL</> as specifying a null array element.
4489 By default, this is <literal>on</>, allowing array values containing
4490 null values to be entered. However, <productname>PostgreSQL</> versions
4491 before 8.2 did not support null values in arrays, and therefore would
4492 treat <literal>NULL</> as specifying a normal array element with
4493 the string value <quote>NULL</>. For backwards compatibility with
4494 applications that require the old behavior, this variable can be
4495 turned <literal>off</>.
4499 Note that it is possible to create array values containing null values
4500 even when this variable is <literal>off</>.
4505 <varlistentry id="guc-backslash-quote" xreflabel="backslash_quote">
4506 <term><varname>backslash_quote</varname> (<type>string</type>)</term>
4507 <indexterm><primary>strings</><secondary>backslash quotes</></>
4509 <primary><varname>backslash_quote</> configuration parameter</primary>
4513 This controls whether a quote mark can be represented by
4514 <literal>\'</> in a string literal. The preferred, SQL-standard way
4515 to represent a quote mark is by doubling it (<literal>''</>) but
4516 <productname>PostgreSQL</> has historically also accepted
4517 <literal>\'</>. However, use of <literal>\'</> creates security risks
4518 because in some client character set encodings, there are multibyte
4519 characters in which the last byte is numerically equivalent to ASCII
4520 <literal>\</>. If client-side code does escaping incorrectly then a
4521 SQL-injection attack is possible. This risk can be prevented by
4522 making the server reject queries in which a quote mark appears to be
4523 escaped by a backslash.
4524 The allowed values of <varname>backslash_quote</> are
4525 <literal>on</> (allow <literal>\'</> always),
4526 <literal>off</> (reject always), and
4527 <literal>safe_encoding</> (allow only if client encoding does not
4528 allow ASCII <literal>\</> within a multibyte character).
4529 <literal>safe_encoding</> is the default setting.
4533 Note that in a standard-conforming string literal, <literal>\</> just
4534 means <literal>\</> anyway. This parameter affects the handling of
4535 non-standard-conforming literals, including
4536 escape string syntax (<literal>E'...'</>).
4541 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
4542 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
4544 <primary><varname>default_with_oids</> configuration parameter</primary>
4548 This controls whether <command>CREATE TABLE</command> and
4549 <command>CREATE TABLE AS</command> include an OID column in
4550 newly-created tables, if neither <literal>WITH OIDS</literal>
4551 nor <literal>WITHOUT OIDS</literal> is specified. It also
4552 determines whether OIDs will be included in tables created by
4553 <command>SELECT INTO</command>. In <productname>PostgreSQL</>
4554 8.1 <varname>default_with_oids</> is <literal>off</> by default; in
4555 prior versions of <productname>PostgreSQL</productname>, it
4560 The use of OIDs in user tables is considered deprecated, so
4561 most installations should leave this variable disabled.
4562 Applications that require OIDs for a particular table should
4563 specify <literal>WITH OIDS</literal> when creating the
4564 table. This variable can be enabled for compatibility with old
4565 applications that do not follow this behavior.
4570 <varlistentry id="guc-escape-string-warning" xreflabel="escape_string_warning">
4571 <term><varname>escape_string_warning</varname> (<type>boolean</type>)</term>
4572 <indexterm><primary>strings</><secondary>escape warning</></>
4574 <primary><varname>escape_string_warning</> configuration parameter</primary>
4578 When on, a warning is issued if a backslash (<literal>\</>)
4579 appears in an ordinary string literal (<literal>'...'</>
4580 syntax) and <varname>standard_conforming_strings</varname> is off.
4581 The default is <literal>on</>.
4584 Applications that wish to use backslash as escape should be
4585 modified to use escape string syntax (<literal>E'...'</>),
4586 because the default behavior of ordinary strings will change
4587 in a future release for SQL compatibility. This variable can
4588 be enabled to help detect applications that will break.
4593 <varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
4594 <term><varname>regex_flavor</varname> (<type>string</type>)</term>
4595 <indexterm><primary>regular expressions</></>
4597 <primary><varname>regex_flavor</> configuration parameter</primary>
4601 The regular expression <quote>flavor</> can be set to
4602 <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
4603 The default is <literal>advanced</>. The <literal>extended</>
4604 setting might be useful for exact backwards compatibility with
4605 pre-7.4 releases of <productname>PostgreSQL</>. See
4606 <xref linkend="posix-syntax-details"> for details.
4611 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
4612 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
4614 <primary><varname>sql_inheritance</> configuration parameter</primary>
4616 <indexterm><primary>inheritance</></>
4619 This controls the inheritance semantics. If turned <literal>off</>,
4620 subtables are not included by various commands by default; basically
4621 an implied <literal>ONLY</literal> key word. This was added for
4622 compatibility with releases prior to 7.1. See
4623 <xref linkend="ddl-inherit"> for more information.
4628 <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
4629 <term><varname>standard_conforming_strings</varname> (<type>boolean</type>)</term>
4630 <indexterm><primary>strings</><secondary>standard conforming</></>
4632 <primary><varname>standard_conforming_strings</> configuration parameter</primary>
4636 This controls whether ordinary string literals
4637 (<literal>'...'</>) treat backslashes literally, as specified in
4639 The default is currently <literal>off</>, causing
4640 <productname>PostgreSQL</productname> to have its historical
4641 behavior of treating backslashes as escape characters.
4642 The default will change to <literal>on</> in a future release
4643 to improve compatibility with the standard.
4644 Applications can check this
4645 parameter to determine how string literals will be processed.
4646 The presence of this parameter can also be taken as an indication
4647 that the escape string syntax (<literal>E'...'</>) is supported.
4648 Escape string syntax should be used if an application desires
4649 backslashes to be treated as escape characters.
4654 <varlistentry id="guc-synchronize-seqscans" xreflabel="synchronize_seqscans">
4655 <term><varname>synchronize_seqscans</varname> (<type>boolean</type>)</term>
4657 <primary><varname>synchronize_seqscans</> configuration parameter</primary>
4661 This allows sequential scans of large tables to synchronize with each
4662 other, so that concurrent scans read the same block at about the
4663 same time and hence share the I/O workload. When this is enabled,
4664 a scan might start in the middle of the table and then <quote>wrap
4665 around</> the end to cover all rows, so as to synchronize with the
4666 activity of scans already in progress. This can result in
4667 unpredictable changes in the row ordering returned by queries that
4668 have no <literal>ORDER BY</> clause. Setting this parameter to
4669 <literal>off</> ensures the pre-8.3 behavior in which a sequential
4670 scan always starts from the beginning of the table. The default
4679 <sect2 id="runtime-config-compatible-clients">
4680 <title>Platform and Client Compatibility</title>
4683 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
4684 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
4685 <indexterm><primary>IS NULL</></>
4687 <primary><varname>transform_null_equals</> configuration parameter</primary>
4691 When on, expressions of the form <literal><replaceable>expr</> =
4692 NULL</literal> (or <literal>NULL =
4693 <replaceable>expr</></literal>) are treated as
4694 <literal><replaceable>expr</> IS NULL</literal>, that is, they
4695 return true if <replaceable>expr</> evaluates to the null value,
4696 and false otherwise. The correct SQL-spec-compliant behavior of
4697 <literal><replaceable>expr</> = NULL</literal> is to always
4698 return null (unknown). Therefore this parameter defaults to
4703 However, filtered forms in <productname>Microsoft
4704 Access</productname> generate queries that appear to use
4705 <literal><replaceable>expr</> = NULL</literal> to test for
4706 null values, so if you use that interface to access the database you
4707 might want to turn this option on. Since expressions of the
4708 form <literal><replaceable>expr</> = NULL</literal> always
4709 return the null value (using the correct interpretation) they are not
4710 very useful and do not appear often in normal applications, so
4711 this option does little harm in practice. But new users are
4712 frequently confused about the semantics of expressions
4713 involving null values, so this option is not on by default.
4717 Note that this option only affects the exact form <literal>= NULL</>,
4718 not other comparison operators or other expressions
4719 that are computationally equivalent to some expression
4720 involving the equals operator (such as <literal>IN</literal>).
4721 Thus, this option is not a general fix for bad programming.
4725 Refer to <xref linkend="functions-comparison"> for related information.
4734 <sect1 id="runtime-config-preset">
4735 <title>Preset Options</title>
4738 The following <quote>parameters</> are read-only, and are determined
4739 when <productname>PostgreSQL</productname> is compiled or when it is
4740 installed. As such, they have been excluded from the sample
4741 <filename>postgresql.conf</> file. These options report
4742 various aspects of <productname>PostgreSQL</productname> behavior
4743 that might be of interest to certain applications, particularly
4744 administrative front-ends.
4749 <varlistentry id="guc-block-size" xreflabel="block_size">
4750 <term><varname>block_size</varname> (<type>integer</type>)</term>
4752 <primary><varname>block_size</> configuration parameter</primary>
4756 Reports the size of a disk block. It is determined by the value
4757 of <literal>BLCKSZ</> when building the server. The default
4758 value is 8192 bytes. The meaning of some configuration
4759 variables (such as <xref linkend="guc-shared-buffers">) is
4760 influenced by <varname>block_size</varname>. See <xref
4761 linkend="runtime-config-resource"> for information.
4766 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
4767 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
4769 <primary><varname>integer_datetimes</> configuration parameter</primary>
4773 Reports whether <productname>PostgreSQL</> was built with
4774 support for 64-bit-integer dates and times. This can be
4775 disabled by configuring with <literal>--disable-integer-datetimes</>
4776 when building <productname>PostgreSQL</>. The default value is
4777 <literal>on</literal>.
4782 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
4783 <term><varname>lc_collate</varname> (<type>string</type>)</term>
4785 <primary><varname>lc_collate</> configuration parameter</primary>
4789 Reports the locale in which sorting of textual data is done.
4790 See <xref linkend="locale"> for more information.
4791 The value is determined when the database cluster is initialized.
4796 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
4797 <term><varname>lc_ctype</varname> (<type>string</type>)</term>
4799 <primary><varname>lc_ctype</> configuration parameter</primary>
4803 Reports the locale that determines character classifications.
4804 See <xref linkend="locale"> for more information.
4805 The value is determined when the database cluster is initialized.
4806 Ordinarily this will be the same as <varname>lc_collate</varname>,
4807 but for special applications it might be set differently.
4812 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
4813 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
4815 <primary><varname>max_function_args</> configuration parameter</primary>
4819 Reports the maximum number of function arguments. It is determined by
4820 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
4821 default value is 100 arguments.
4826 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
4827 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
4829 <primary><varname>max_identifier_length</> configuration parameter</primary>
4833 Reports the maximum identifier length. It is determined as one
4834 less than the value of <literal>NAMEDATALEN</> when building
4835 the server. The default value of <literal>NAMEDATALEN</> is
4836 64; therefore the default
4837 <varname>max_identifier_length</varname> is 63 bytes.
4842 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
4843 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
4845 <primary><varname>max_index_keys</> configuration parameter</primary>
4849 Reports the maximum number of index keys. It is determined by
4850 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
4851 default value is 32 keys.
4856 <varlistentry id="guc-segment-size" xreflabel="segment_size">
4857 <term><varname>segment_size</varname> (<type>integer</type>)</term>
4859 <primary><varname>segment_size</> configuration parameter</primary>
4863 Reports the number of blocks (pages) that can be stored within a file
4864 segment. It is determined by the value of <literal>RELSEG_SIZE</>
4865 when building the server. The maximum size of a segment file in bytes
4866 is equal to <varname>segment_size</> multiplied by
4867 <varname>block_size</>; by default this is 1GB.
4872 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
4873 <term><varname>server_encoding</varname> (<type>string</type>)</term>
4875 <primary><varname>server_encoding</> configuration parameter</primary>
4877 <indexterm><primary>character set</></>
4880 Reports the database encoding (character set).
4881 It is determined when the database is created. Ordinarily,
4882 clients need only be concerned with the value of <xref
4883 linkend="guc-client-encoding">.
4888 <varlistentry id="guc-server-version" xreflabel="server_version">
4889 <term><varname>server_version</varname> (<type>string</type>)</term>
4891 <primary><varname>server_version</> configuration parameter</primary>
4895 Reports the version number of the server. It is determined by the
4896 value of <literal>PG_VERSION</> when building the server.
4901 <varlistentry id="guc-server-version-num" xreflabel="server_version_num">
4902 <term><varname>server_version_num</varname> (<type>integer</type>)</term>
4904 <primary><varname>server_version_num</> configuration parameter</primary>
4908 Reports the version number of the server as an integer. It is determined
4909 by the value of <literal>PG_VERSION_NUM</> when building the server.
4914 <varlistentry id="guc-wal-block-size" xreflabel="wal_block_size">
4915 <term><varname>wal_block_size</varname> (<type>integer</type>)</term>
4917 <primary><varname>wal_block_size</> configuration parameter</primary>
4921 Reports the size of a WAL disk block. It is determined by the value
4922 of <literal>XLOG_BLCKSZ</> when building the server. The default value
4928 <varlistentry id="guc-wal-segment-size" xreflabel="wal_segment_size">
4929 <term><varname>wal_segment_size</varname> (<type>integer</type>)</term>
4931 <primary><varname>wal_segment_size</> configuration parameter</primary>
4935 Reports the number of blocks (pages) in a WAL segment file.
4936 The total size of a WAL segment file in bytes is equal to
4937 <varname>wal_segment_size</> multiplied by <varname>wal_block_size</>;
4938 by default this is 16MB. See <xref linkend="wal-configuration"> for
4947 <sect1 id="runtime-config-custom">
4948 <title>Customized Options</title>
4951 This feature was designed to allow parameters not normally known to
4952 <productname>PostgreSQL</productname> to be added by add-on modules
4953 (such as procedural languages). This allows add-on modules to be
4954 configured in the standard ways.
4959 <varlistentry id="guc-custom-variable-classes" xreflabel="custom_variable_classes">
4960 <term><varname>custom_variable_classes</varname> (<type>string</type>)</term>
4962 <primary><varname>custom_variable_classes</> configuration parameter</primary>
4966 This variable specifies one or several class names to be used for
4967 custom variables, in the form of a comma-separated list. A custom
4968 variable is a variable not normally known
4969 to <productname>PostgreSQL</productname> proper but used by some
4970 add-on module. Such variables must have names consisting of a class
4971 name, a dot, and a variable name. <varname>custom_variable_classes</>
4972 specifies all the class names in use in a particular installation.
4973 This parameter can only be set in the <filename>postgresql.conf</>
4974 file or on the server command line.
4982 The difficulty with setting custom variables in
4983 <filename>postgresql.conf</> is that the file must be read before add-on
4984 modules have been loaded, and so custom variables would ordinarily be
4985 rejected as unknown. When <varname>custom_variable_classes</> is set,
4986 the server will accept definitions of arbitrary variables within each
4987 specified class. These variables will be treated as placeholders and
4988 will have no function until the module that defines them is loaded. When a
4989 module for a specific class is loaded, it will add the proper variable
4990 definitions for its class name, convert any placeholder
4991 values according to those definitions, and issue warnings for any
4992 placeholders of its class that remain (which presumably would be
4993 misspelled configuration variables).
4997 Here is an example of what <filename>postgresql.conf</> might contain
4998 when using custom variables:
5001 custom_variable_classes = 'plr,plperl'
5002 plr.path = '/usr/lib/R'
5003 plperl.use_strict = true
5004 plruby.use_strict = true # generates error: unknown class name
5009 <sect1 id="runtime-config-developer">
5010 <title>Developer Options</title>
5013 The following parameters are intended for work on the
5014 <productname>PostgreSQL</productname> source, and in some cases
5015 to assist with recovery of severely damaged databases. There
5016 should be no reason to use them in a production database setup.
5017 As such, they have been excluded from the sample
5018 <filename>postgresql.conf</> file. Note that many of these
5019 parameters require special source compilation flags to work at all.
5023 <varlistentry id="guc-allow-system-table-mods" xreflabel="allow_system_table_mods">
5024 <term><varname>allow_system_table_mods</varname> (<type>boolean</type>)</term>
5026 <primary><varname>allow_system_table_mods</varname> configuration parameter</primary>
5030 Allows modification of the structure of system tables.
5031 This is used by <command>initdb</command>.
5032 This parameter can only be set at server start.
5037 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
5038 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
5040 <primary><varname>debug_assertions</> configuration parameter</primary>
5044 Turns on various assertion checks. This is a debugging aid. If
5045 you are experiencing strange problems or crashes you might want
5046 to turn this on, as it might expose programming mistakes. To use
5047 this parameter, the macro <symbol>USE_ASSERT_CHECKING</symbol>
5048 must be defined when <productname>PostgreSQL</productname> is
5049 built (accomplished by the <command>configure</command> option
5050 <option>--enable-cassert</option>). Note that
5051 <varname>debug_assertions</varname> defaults to <literal>on</>
5052 if <productname>PostgreSQL</productname> has been built with
5058 <varlistentry id="guc-ignore-system-indexes" xreflabel="ignore_system_indexes">
5059 <term><varname>ignore_system_indexes</varname> (<type>boolean</type>)</term>
5061 <primary><varname>ignore_system_indexes</varname> configuration parameter</primary>
5065 Ignore system indexes when reading system tables (but still
5066 update the indexes when modifying the tables). This is useful
5067 when recovering from damaged system indexes.
5068 This parameter cannot be changed after session start.
5073 <varlistentry id="guc-post-auth-delay" xreflabel="post_auth_delay">
5074 <term><varname>post_auth_delay</varname> (<type>integer</type>)</term>
5076 <primary><varname>post_auth_delay</> configuration parameter</primary>
5080 If nonzero, a delay of this many seconds occurs when a new
5081 server process is started, after it conducts the
5082 authentication procedure. This is intended to give an
5083 opportunity to attach to the server process with a debugger.
5084 This parameter cannot be changed after session start.
5089 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
5090 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
5092 <primary><varname>pre_auth_delay</> configuration parameter</primary>
5096 If nonzero, a delay of this many seconds occurs just after a
5097 new server process is forked, before it conducts the
5098 authentication procedure. This is intended to give an
5099 opportunity to attach to the server process with a debugger to
5100 trace down misbehavior in authentication.
5101 This parameter can only be set in the <filename>postgresql.conf</>
5102 file or on the server command line.
5107 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
5108 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
5110 <primary><varname>trace_notify</> configuration parameter</primary>
5114 Generates a great amount of debugging output for the
5115 <command>LISTEN</command> and <command>NOTIFY</command>
5116 commands. <xref linkend="guc-client-min-messages"> or
5117 <xref linkend="guc-log-min-messages"> must be
5118 <literal>DEBUG1</literal> or lower to send this output to the
5119 client or server log, respectively.
5124 <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
5125 <term><varname>trace_sort</varname> (<type>boolean</type>)</term>
5127 <primary><varname>trace_sort</> configuration parameter</primary>
5131 If on, emit information about resource usage during sort operations.
5132 This parameter is only available if the <symbol>TRACE_SORT</symbol> macro
5133 was defined when <productname>PostgreSQL</productname> was compiled.
5134 (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
5140 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
5142 <primary><varname>trace_locks</> configuration parameter</primary>
5146 If on, emit information about lock usage. Information dumped
5147 includes the type of lock operation, the type of lock and the unique
5148 identifier of the object being locked or unlocked. Also included
5149 are bitmasks for the lock types already granted on this object as
5150 well as for the lock types awaited on this object. For each lock
5151 type a count of the number of granted locks and waiting locks is
5152 also dumped as well as the totals. An example of the log file output
5156 LOG: LockAcquire: new: lock(0xb7acd844) id(24688,24696,0,0,0,1)
5157 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
5158 wait(0) type(AccessShareLock)
5161 LOG: GrantLock: lock(0xb7acd844) id(24688,24696,0,0,0,1)
5162 grantMask(2) req(1,0,0,0,0,0,0)=1 grant(1,0,0,0,0,0,0)=1
5163 wait(0) type(AccessShareLock)
5167 LOG: UnGrantLock: updated: lock(0xb7acd844) id(24688,24696,0,0,0,1)
5168 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
5169 wait(0) type(AccessShareLock)
5172 LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
5173 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
5174 wait(0) type(INVALID)
5177 Details of the structure being dumped may be found in
5178 src/include/storage/lock.h
5181 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5182 macro was defined when <productname>PostgreSQL</productname> was
5189 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
5191 <primary><varname>trace_lwlocks</> configuration parameter</primary>
5195 If on, emit information about lightweight lock usage. Lightweight
5196 locks are intended primarily to provide mutual exclusion of access
5197 to shared-memory data structures.
5200 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5201 macro was defined when <productname>PostgreSQL</productname> was
5208 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
5210 <primary><varname>trace_userlocks</> configuration parameter</primary>
5214 If on, emit information about user lock usage. Output is the same
5215 as for <symbol>trace_locks</symbol>, only for user locks.
5218 User locks were removed as of PostgreSQL version 8.2. This option
5219 currently has no effect.
5222 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5223 macro was defined when <productname>PostgreSQL</productname> was
5230 <term><varname>trace_lock_oidmin</varname> (<type>integer</type>)</term>
5232 <primary><varname>trace_lock_oidmin</> configuration parameter</primary>
5236 If set, do not trace locks for tables below this OID. (use to avoid
5237 output on system tables)
5240 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5241 macro was defined when <productname>PostgreSQL</productname> was
5248 <term><varname>trace_lock_table</varname> (<type>integer</type>)</term>
5250 <primary><varname>trace_lock_table</> configuration parameter</primary>
5254 Unconditionally trace locks on this table (OID).
5257 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5258 macro was defined when <productname>PostgreSQL</productname> was
5265 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
5267 <primary><varname>debug_deadlocks</> configuration parameter</primary>
5271 If set, dumps information about all current locks when a
5272 DeadLockTimeout occurs.
5275 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5276 macro was defined when <productname>PostgreSQL</productname> was
5283 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
5285 <primary><varname>log_btree_build_stats</> configuration parameter</primary>
5289 If set, logs system resource usage statistics (memory and CPU) on
5290 various btree operations.
5293 This parameter is only available if the <symbol>BTREE_BUILD_STATS</symbol>
5294 macro was defined when <productname>PostgreSQL</productname> was
5300 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
5301 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
5303 <primary><varname>wal_debug</> configuration parameter</primary>
5307 If on, emit WAL-related debugging output. This parameter is
5308 only available if the <symbol>WAL_DEBUG</symbol> macro was
5309 defined when <productname>PostgreSQL</productname> was
5315 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
5316 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
5318 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
5322 Detection of a damaged page header normally causes
5323 <productname>PostgreSQL</> to report an error, aborting the current
5324 command. Setting <varname>zero_damaged_pages</> to on causes
5325 the system to instead report a warning, zero out the damaged page,
5326 and continue processing. This behavior <emphasis>will destroy data</>,
5327 namely all the rows on the damaged page. But it allows you to get
5328 past the error and retrieve rows from any undamaged pages that might
5329 be present in the table. So it is useful for recovering data if
5330 corruption has occurred due to hardware or software error. You should
5331 generally not set this on until you have given up hope of recovering
5332 data from the damaged page(s) of a table. The
5333 default setting is <literal>off</>, and it can only be changed
5340 <sect1 id="runtime-config-short">
5341 <title>Short Options</title>
5344 For convenience there are also single letter command-line option
5345 switches available for some parameters. They are described in
5346 <xref linkend="runtime-config-short-table">. Some of these
5347 options exist for historical reasons, and their presence as a
5348 single-letter option does not necessarily indicate an endorsement
5349 to use the option heavily.
5352 <table id="runtime-config-short-table">
5353 <title>Short option key</title>
5357 <entry>Short option</entry>
5358 <entry>Equivalent</entry>
5364 <entry><option>-A <replaceable>x</replaceable></option></entry>
5365 <entry><literal>debug_assertions = <replaceable>x</replaceable></></entry>
5368 <entry><option>-B <replaceable>x</replaceable></option></entry>
5369 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
5372 <entry><option>-d <replaceable>x</replaceable></option></entry>
5373 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
5376 <entry><option>-e</option></entry>
5377 <entry><literal>datestyle = euro</></entry>
5381 <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
5382 <option>-fm</option>, <option>-fn</option>,
5383 <option>-fs</option>, <option>-ft</option>
5386 <literal>enable_bitmapscan = off</>,
5387 <literal>enable_hashjoin = off</>,
5388 <literal>enable_indexscan = off</>,
5389 <literal>enable_mergejoin = off</>,
5390 <literal>enable_nestloop = off</>,
5391 <literal>enable_seqscan = off</>,
5392 <literal>enable_tidscan = off</>
5396 <entry><option>-F</option></entry>
5397 <entry><literal>fsync = off</></entry>
5400 <entry><option>-h <replaceable>x</replaceable></option></entry>
5401 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
5404 <entry><option>-i</option></entry>
5405 <entry><literal>listen_addresses = '*'</></entry>
5408 <entry><option>-k <replaceable>x</replaceable></option></entry>
5409 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
5412 <entry><option>-l</option></entry>
5413 <entry><literal>ssl = on</></entry>
5416 <entry><option>-N <replaceable>x</replaceable></option></entry>
5417 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
5420 <entry><option>-O</option></entry>
5421 <entry><literal>allow_system_table_mods = on</></entry>
5424 <entry><option>-p <replaceable>x</replaceable></option></entry>
5425 <entry><literal>port = <replaceable>x</replaceable></></entry>
5428 <entry><option>-P</option></entry>
5429 <entry><literal>ignore_system_indexes = on</></entry>
5432 <entry><option>-s</option></entry>
5433 <entry><literal>log_statement_stats = on</></entry>
5436 <entry><option>-S <replaceable>x</replaceable></option></entry>
5437 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
5440 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option></entry>
5441 <entry><literal>log_parser_stats = on</>,
5442 <literal>log_planner_stats = on</>,
5443 <literal>log_executor_stats = on</></entry>
5446 <entry><option>-W <replaceable>x</replaceable></option></entry>
5447 <entry><literal>post_auth_delay = <replaceable>x</replaceable></></entry>