1 <!-- doc/src/sgml/config.sgml -->
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>
21 <sect2 id="config-setting-names-values">
22 <title>Parameter Names and Values</title>
25 All parameter names are case-insensitive. Every parameter takes a
26 value of one of five types: Boolean, integer, floating point,
27 string or enum. Boolean values can be written as <literal>on</literal>,
28 <literal>off</literal>, <literal>true</literal>,
29 <literal>false</literal>, <literal>yes</literal>,
30 <literal>no</literal>, <literal>1</literal>, <literal>0</literal>
31 (all case-insensitive) or any unambiguous prefix of these.
35 Some settings specify a memory or time value. Each of these has an
36 implicit unit, which is either kilobytes, blocks (typically eight
37 kilobytes), milliseconds, seconds, or minutes. Default units can be
38 found by referencing <structname>pg_settings</>.<structfield>unit</>.
40 a different unit can also be specified explicitly. Valid memory units
41 are <literal>kB</literal> (kilobytes), <literal>MB</literal>
42 (megabytes), and <literal>GB</literal> (gigabytes); valid time units
43 are <literal>ms</literal> (milliseconds), <literal>s</literal>
44 (seconds), <literal>min</literal> (minutes), <literal>h</literal>
45 (hours), and <literal>d</literal> (days). Note that the multiplier
46 for memory units is 1024, not 1000.
50 Parameters of type <quote>enum</> are specified in the same way as string
51 parameters, but are restricted to a limited set of values. The allowed
53 from <structname>pg_settings</>.<structfield>enumvals</>.
54 Enum parameter values are case-insensitive.
58 <sect2 id="config-setting-configuration-file">
59 <title>Setting Parameters via the Configuration File</title>
62 One way to set these parameters is to edit the file
63 <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>,
64 which is normally kept in the data directory. (A default copy is
65 installed there when the database cluster directory is
66 initialized.) An example of what this file might look like is:
70 log_destination = 'syslog'
71 search_path = '"$user", public'
72 shared_buffers = 128MB
74 One parameter is specified per line. The equal sign between name and
75 value is optional. Whitespace is insignificant and blank lines are
76 ignored. Hash marks (<literal>#</literal>) designate the remainder of the
77 line as a comment. Parameter values that are not simple identifiers or
78 numbers must be single-quoted. To embed a single quote in a parameter
79 value, write either two quotes (preferred) or backslash-quote.
84 <primary><literal>include</></primary>
85 <secondary>in configuration file</secondary>
87 In addition to parameter settings, the <filename>postgresql.conf</>
88 file can contain <firstterm>include directives</>, which specify
89 another file to read and process as if it were inserted into the
90 configuration file at this point. This feature allows a configuration
91 file to be divided into physically separate parts.
92 Include directives simply look like:
96 If the file name is not an absolute path, it is taken as relative to
97 the directory containing the referencing configuration file.
98 Inclusions can be nested.
103 <primary><literal>include_if_exists</></primary>
104 <secondary>in configuration file</secondary>
106 There is also an <literal>include_if_exists</> directive, which acts
107 the same as the <literal>include</> directive, except for the behavior
108 when the referenced file does not exist or cannot be read. A regular
109 <literal>include</> will consider this an error condition, but
110 <literal>include_if_exists</> merely logs a message and continues
111 processing the referencing configuration file.
116 <primary>SIGHUP</primary>
118 The configuration file is reread whenever the main server process
119 receives a <systemitem>SIGHUP</> signal; this is most easily done by
120 running <literal>pg_ctl reload</> from the command-line or by calling
121 the SQL function <function>pg_reload_conf()</function>. The main
123 also propagates this signal to all currently running server
124 processes so that existing sessions also get the new
125 value. Alternatively, you can send the signal to a single server
126 process directly. Some parameters can only be set at server start;
127 any changes to their entries in the configuration file will be ignored
128 until the server is restarted. Invalid parameter settings in the
129 configuration file are likewise ignored (but logged) during
130 <systemitem>SIGHUP</> processing.
134 <sect2 id="config-setting-other-methods">
135 <title>Other Ways to Set Parameters</title>
138 A second way to set these configuration parameters is to give them
139 as a command-line option to the <command>postgres</command> command,
142 postgres -c log_connections=yes -c log_destination='syslog'
144 Command-line options override any conflicting settings in
145 <filename>postgresql.conf</filename>. Note that this means you won't
146 be able to change the value on-the-fly by editing
147 <filename>postgresql.conf</filename>, so while the command-line
148 method might be convenient, it can cost you flexibility later.
152 Occasionally it is useful to give a command line option to
153 one particular session only. The environment variable
154 <envar>PGOPTIONS</envar> can be used for this purpose on the
157 env PGOPTIONS='-c geqo=off' psql
159 (This works for any <application>libpq</>-based client application, not
160 just <application>psql</application>.) Note that this won't work for
161 parameters that are fixed when the server is started or that must be
162 specified in <filename>postgresql.conf</filename>.
166 Furthermore, it is possible to assign a set of parameter settings to
167 a user or a database. Whenever a session is started, the default
168 settings for the user and database involved are loaded. The
169 commands <xref linkend="sql-alterrole">
170 and <xref linkend="sql-alterdatabase">,
171 respectively, are used to configure these settings. Per-database
172 settings override anything received from the
173 <command>postgres</command> command-line or the configuration
174 file, and in turn are overridden by per-user settings; both are
175 overridden by per-session settings.
179 Some parameters can be changed in individual <acronym>SQL</acronym>
180 sessions with the <xref linkend="SQL-SET">
181 command, for example:
183 SET ENABLE_SEQSCAN TO OFF;
185 If <command>SET</> is allowed, it overrides all other sources of
186 values for the parameter. Some parameters cannot be changed via
187 <command>SET</command>: for example, if they control behavior that
188 cannot be changed without restarting the entire
189 <productname>PostgreSQL</productname> server. Also, some parameters
190 require superuser permission to change via <command>SET</command> or
195 <sect2 id="config-setting-examining">
196 <title>Examining Parameter Settings</title>
199 The <xref linkend="SQL-SHOW">
200 command allows inspection of the current values of all parameters.
204 The virtual table <structname>pg_settings</structname> also allows
205 displaying and updating session run-time parameters; see <xref
206 linkend="view-pg-settings"> for details and a description of the
207 different variable types and when they can be changed.
208 <structname>pg_settings</structname> is equivalent to <command>SHOW</>
209 and <command>SET</>, but can be more convenient
210 to use because it can be joined with other tables, or selected from using
211 any desired selection condition. It also contains more information about
212 each parameter than is available from <command>SHOW</>.
218 <sect1 id="runtime-config-file-locations">
219 <title>File Locations</title>
222 In addition to the <filename>postgresql.conf</filename> file
223 already mentioned, <productname>PostgreSQL</productname> uses
224 two other manually-edited configuration files, which control
225 client authentication (their use is discussed in <xref
226 linkend="client-authentication">). By default, all three
227 configuration files are stored in the database cluster's data
228 directory. The parameters described in this section allow the
229 configuration files to be placed elsewhere. (Doing so can ease
230 administration. In particular it is often easier to ensure that
231 the configuration files are properly backed-up when they are
236 <varlistentry id="guc-data-directory" xreflabel="data_directory">
237 <term><varname>data_directory</varname> (<type>string</type>)</term>
239 <primary><varname>data_directory</> configuration parameter</primary>
243 Specifies the directory to use for data storage.
244 This parameter can only be set at server start.
249 <varlistentry id="guc-config-file" xreflabel="config_file">
250 <term><varname>config_file</varname> (<type>string</type>)</term>
252 <primary><varname>config_file</> configuration parameter</primary>
256 Specifies the main server configuration file
257 (customarily called <filename>postgresql.conf</>).
258 This parameter can only be set on the <command>postgres</command> command line.
263 <varlistentry id="guc-hba-file" xreflabel="hba_file">
264 <term><varname>hba_file</varname> (<type>string</type>)</term>
266 <primary><varname>hba_file</> configuration parameter</primary>
270 Specifies the configuration file for host-based authentication
271 (customarily called <filename>pg_hba.conf</>).
272 This parameter can only be set at server start.
277 <varlistentry id="guc-ident-file" xreflabel="ident_file">
278 <term><varname>ident_file</varname> (<type>string</type>)</term>
280 <primary><varname>ident_file</> configuration parameter</primary>
284 Specifies the configuration file for
285 <xref linkend="auth-username-maps"> user name mapping
286 (customarily called <filename>pg_ident.conf</>).
287 This parameter can only be set at server start.
292 <varlistentry id="guc-external-pid-file" xreflabel="external_pid_file">
293 <term><varname>external_pid_file</varname> (<type>string</type>)</term>
295 <primary><varname>external_pid_file</> configuration parameter</primary>
299 Specifies the name of an additional process-ID (PID) file that the
300 server should create for use by server administration programs.
301 This parameter can only be set at server start.
308 In a default installation, none of the above parameters are set
309 explicitly. Instead, the
310 data directory is specified by the <option>-D</option> command-line
311 option or the <envar>PGDATA</envar> environment variable, and the
312 configuration files are all found within the data directory.
316 If you wish to keep the configuration files elsewhere than the
317 data directory, the <command>postgres</command> <option>-D</option>
318 command-line option or <envar>PGDATA</envar> environment variable
319 must point to the directory containing the configuration files,
320 and the <varname>data_directory</> parameter must be set in
321 <filename>postgresql.conf</filename> (or on the command line) to show
322 where the data directory is actually located. Notice that
323 <varname>data_directory</> overrides <option>-D</option> and
324 <envar>PGDATA</envar> for the location
325 of the data directory, but not for the location of the configuration
330 If you wish, you can specify the configuration file names and locations
331 individually using the parameters <varname>config_file</>,
332 <varname>hba_file</> and/or <varname>ident_file</>.
333 <varname>config_file</> can only be specified on the
334 <command>postgres</command> command line, but the others can be
335 set within the main configuration file. If all three parameters plus
336 <varname>data_directory</> are explicitly set, then it is not necessary
337 to specify <option>-D</option> or <envar>PGDATA</envar>.
341 When setting any of these parameters, a relative path will be interpreted
342 with respect to the directory in which <command>postgres</command>
347 <sect1 id="runtime-config-connection">
348 <title>Connections and Authentication</title>
350 <sect2 id="runtime-config-connection-settings">
351 <title>Connection Settings</title>
355 <varlistentry id="guc-listen-addresses" xreflabel="listen_addresses">
356 <term><varname>listen_addresses</varname> (<type>string</type>)</term>
358 <primary><varname>listen_addresses</> configuration parameter</primary>
362 Specifies the TCP/IP address(es) on which the server is
363 to listen for connections from client applications.
364 The value takes the form of a comma-separated list of host names
365 and/or numeric IP addresses. The special entry <literal>*</>
366 corresponds to all available IP interfaces. The entry
367 <literal>0.0.0.0</> allows listening for all IPv4 addresses and
368 <literal>::</> allows listening for all IPv6 addresses.
369 If the list is empty, the server does not listen on any IP interface
370 at all, in which case only Unix-domain sockets can be used to connect
372 The default value is <systemitem class="systemname">localhost</>,
373 which allows only local TCP/IP <quote>loopback</> connections to be
374 made. While client authentication (<xref
375 linkend="client-authentication">) allows fine-grained control
376 over who can access the server, <varname>listen_addresses</varname>
377 controls which interfaces accept connection attempts, which
378 can help prevent repeated malicious connection requests on
379 insecure network interfaces. This parameter can only be set
385 <varlistentry id="guc-port" xreflabel="port">
386 <term><varname>port</varname> (<type>integer</type>)</term>
388 <primary><varname>port</> configuration parameter</primary>
392 The TCP port the server listens on; 5432 by default. Note that the
393 same port number is used for all IP addresses the server listens on.
394 This parameter can only be set at server start.
399 <varlistentry id="guc-max-connections" xreflabel="max_connections">
400 <term><varname>max_connections</varname> (<type>integer</type>)</term>
402 <primary><varname>max_connections</> configuration parameter</primary>
406 Determines the maximum number of concurrent connections to the
407 database server. The default is typically 100 connections, but
408 might be less if your kernel settings will not support it (as
409 determined during <application>initdb</>). This parameter can
410 only be set at server start.
414 When running a standby server, you must set this parameter to the
415 same or higher value than on the master server. Otherwise, queries
416 will not be allowed in the standby server.
421 <varlistentry id="guc-superuser-reserved-connections"
422 xreflabel="superuser_reserved_connections">
423 <term><varname>superuser_reserved_connections</varname>
424 (<type>integer</type>)</term>
426 <primary><varname>superuser_reserved_connections</> configuration parameter</primary>
430 Determines the number of connection <quote>slots</quote> that
431 are reserved for connections by <productname>PostgreSQL</>
432 superusers. At most <xref linkend="guc-max-connections">
433 connections can ever be active simultaneously. Whenever the
434 number of active concurrent connections is at least
435 <varname>max_connections</> minus
436 <varname>superuser_reserved_connections</varname>, new
437 connections will be accepted only for superusers, and no
438 new replication connections will be accepted.
442 The default value is three connections. The value must be less
443 than the value of <varname>max_connections</varname>. This
444 parameter can only be set at server start.
449 <varlistentry id="guc-unix-socket-directories" xreflabel="unix_socket_directories">
450 <term><varname>unix_socket_directories</varname> (<type>string</type>)</term>
452 <primary><varname>unix_socket_directories</> configuration parameter</primary>
456 Specifies the directory of the Unix-domain socket(s) on which the
457 server is to listen for connections from client applications.
458 Multiple sockets can be created by listing multiple directories
459 separated by commas. Whitespace between entries is
460 ignored; surround a directory name with double quotes if you need
461 to include whitespace or commas in the name.
463 specifies not listening on any Unix-domain sockets, in which case
464 only TCP/IP sockets can be used to connect to the server.
465 The default value is normally
466 <filename>/tmp</filename>, but that can be changed at build time.
467 This parameter can only be set at server start.
471 In addition to the socket file itself, which is named
472 <literal>.s.PGSQL.<replaceable>nnnn</></literal> where
473 <replaceable>nnnn</> is the server's port number, an ordinary file
474 named <literal>.s.PGSQL.<replaceable>nnnn</>.lock</literal> will be
475 created in each of the <varname>unix_socket_directories</> directories.
476 Neither file should ever be removed manually.
480 This parameter is irrelevant on Windows, which does not have
486 <varlistentry id="guc-unix-socket-group" xreflabel="unix_socket_group">
487 <term><varname>unix_socket_group</varname> (<type>string</type>)</term>
489 <primary><varname>unix_socket_group</> configuration parameter</primary>
493 Sets the owning group of the Unix-domain socket(s). (The owning
494 user of the sockets is always the user that starts the
495 server.) In combination with the parameter
496 <varname>unix_socket_permissions</varname> this can be used as
497 an additional access control mechanism for Unix-domain connections.
498 By default this is the empty string, which uses the default
499 group of the server user. This parameter can only be set at
504 This parameter is irrelevant on Windows, which does not have
510 <varlistentry id="guc-unix-socket-permissions" xreflabel="unix_socket_permissions">
511 <term><varname>unix_socket_permissions</varname> (<type>integer</type>)</term>
513 <primary><varname>unix_socket_permissions</> configuration parameter</primary>
517 Sets the access permissions of the Unix-domain socket(s). Unix-domain
518 sockets use the usual Unix file system permission set.
519 The parameter value is expected to be a numeric mode
520 specified in the format accepted by the
521 <function>chmod</function> and <function>umask</function>
522 system calls. (To use the customary octal format the number
523 must start with a <literal>0</literal> (zero).)
527 The default permissions are <literal>0777</literal>, meaning
528 anyone can connect. Reasonable alternatives are
529 <literal>0770</literal> (only user and group, see also
530 <varname>unix_socket_group</varname>) and <literal>0700</literal>
531 (only user). (Note that for a Unix-domain socket, only write
532 permission matters, so there is no point in setting or revoking
533 read or execute permissions.)
537 This access control mechanism is independent of the one
538 described in <xref linkend="client-authentication">.
542 This parameter can only be set at server start.
546 This parameter is irrelevant on Windows, which does not have
552 <varlistentry id="guc-bonjour" xreflabel="bonjour">
553 <term><varname>bonjour</varname> (<type>boolean</type>)</term>
555 <primary><varname>bonjour</> configuration parameter</primary>
559 Enables advertising the server's existence via
560 <productname>Bonjour</productname>. The default is off.
561 This parameter can only be set at server start.
566 <varlistentry id="guc-bonjour-name" xreflabel="bonjour_name">
567 <term><varname>bonjour_name</varname> (<type>string</type>)</term>
569 <primary><varname>bonjour_name</> configuration parameter</primary>
573 Specifies the <productname>Bonjour</productname> service
574 name. The computer name is used if this parameter is set to the
575 empty string <literal>''</> (which is the default). This parameter is
576 ignored if the server was not compiled with
577 <productname>Bonjour</productname> support.
578 This parameter can only be set at server start.
583 <varlistentry id="guc-tcp-keepalives-idle" xreflabel="tcp_keepalives_idle">
584 <term><varname>tcp_keepalives_idle</varname> (<type>integer</type>)</term>
586 <primary><varname>tcp_keepalives_idle</> configuration parameter</primary>
590 Specifies the number of seconds before sending a keepalive packet on
591 an otherwise idle connection. A value of 0 uses the system default.
592 This parameter is supported only on systems that support the
593 <symbol>TCP_KEEPIDLE</> or <symbol>TCP_KEEPALIVE</> symbols, and on
594 Windows; on other systems, it must be zero. This parameter is ignored
595 for connections made via a Unix-domain socket.
599 On Windows, a value of 0 will set this parameter to 2 hours,
600 since Windows does not provide a way to read the system default value.
606 <varlistentry id="guc-tcp-keepalives-interval" xreflabel="tcp_keepalives_interval">
607 <term><varname>tcp_keepalives_interval</varname> (<type>integer</type>)</term>
609 <primary><varname>tcp_keepalives_interval</> configuration parameter</primary>
613 Specifies the number of seconds between sending keepalives on an
614 otherwise idle connection. A value of 0 uses the system default.
615 This parameter is supported only on systems that support the
616 <symbol>TCP_KEEPINTVL</> symbol, and on Windows; on other systems, it
617 must be zero. This parameter is ignored for connections made via a
622 On Windows, a value of 0 will set this parameter to 1 second,
623 since Windows does not provide a way to read the system default value.
629 <varlistentry id="guc-tcp-keepalives-count" xreflabel="tcp_keepalives_count">
630 <term><varname>tcp_keepalives_count</varname> (<type>integer</type>)</term>
632 <primary><varname>tcp_keepalives_count</> configuration parameter</primary>
636 Specifies the number of keepalive packets to send on an otherwise idle
637 connection. A value of 0 uses the system default. This parameter is
638 supported only on systems that support the <symbol>TCP_KEEPCNT</>
639 symbol; on other systems, it must be zero. This parameter is ignored
640 for connections made via a Unix-domain socket.
644 This parameter is not supported on Windows, and must be zero.
652 <sect2 id="runtime-config-connection-security">
653 <title>Security and Authentication</title>
656 <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
657 <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
658 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
659 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
661 <primary><varname>authentication_timeout</> configuration parameter</primary>
666 Maximum time to complete client authentication, in seconds. If a
667 would-be client has not completed the authentication protocol in
668 this much time, the server closes the connection. This prevents
669 hung clients from occupying a connection indefinitely.
670 The default is one minute (<literal>1m</>).
671 This parameter can only be set in the <filename>postgresql.conf</>
672 file or on the server command line.
677 <varlistentry id="guc-ssl" xreflabel="ssl">
678 <term><varname>ssl</varname> (<type>boolean</type>)</term>
680 <primary><varname>ssl</> configuration parameter</primary>
684 Enables <acronym>SSL</> connections. Please read
685 <xref linkend="ssl-tcp"> before using this. The default
686 is <literal>off</>. This parameter can only be set at server
687 start. <acronym>SSL</> communication is only possible with
693 <varlistentry id="guc-ssl-ca-file" xreflabel="ssl_ca_file">
694 <term><varname>ssl_ca_file</varname> (<type>string</type>)</term>
696 <primary><varname>ssl_ca_file</> configuration parameter</primary>
700 Specifies the name of the file containing the SSL server certificate
701 authority (CA). The default is empty, meaning no CA file is loaded,
702 and client certificate verification is not performed. (In previous
703 releases of PostgreSQL, the name of this file was hard-coded
704 as <filename>root.crt</filename>.) Relative paths are relative to the
705 data directory. This parameter can only be set at server start.
710 <varlistentry id="guc-ssl-cert-file" xreflabel="ssl_cert_file">
711 <term><varname>ssl_cert_file</varname> (<type>string</type>)</term>
713 <primary><varname>ssl_cert_file</> configuration parameter</primary>
717 Specifies the name of the file containing the SSL server certificate.
718 The default is <filename>server.crt</filename>. Relative paths are
719 relative to the data directory. This parameter can only be set at
725 <varlistentry id="guc-ssl-crl-file" xreflabel="ssl_crl_file">
726 <term><varname>ssl_crl_file</varname> (<type>string</type>)</term>
728 <primary><varname>ssl_crl_file</> configuration parameter</primary>
732 Specifies the name of the file containing the SSL server certificate
733 revocation list (CRL). The default is empty, meaning no CRL file is
734 loaded. (In previous releases of PostgreSQL, the name of this file was
735 hard-coded as <filename>root.crl</filename>.) Relative paths are
736 relative to the data directory. This parameter can only be set at
742 <varlistentry id="guc-ssl-key-file" xreflabel="ssl_key_file">
743 <term><varname>ssl_key_file</varname> (<type>string</type>)</term>
745 <primary><varname>ssl_key_file</> configuration parameter</primary>
749 Specifies the name of the file containing the SSL server private key.
750 The default is <filename>server.key</filename>. Relative paths are
751 relative to the data directory. This parameter can only be set at
757 <varlistentry id="guc-ssl-renegotiation-limit" xreflabel="ssl_renegotiation_limit">
758 <term><varname>ssl_renegotiation_limit</varname> (<type>integer</type>)</term>
760 <primary><varname>ssl_renegotiation_limit</> configuration parameter</primary>
764 Specifies how much data can flow over an <acronym>SSL</>-encrypted
765 connection before renegotiation of the session keys will take
766 place. Renegotiation decreases an attacker's chances of doing
767 cryptanalysis when large amounts of traffic can be examined, but it
768 also carries a large performance penalty. The sum of sent and received
769 traffic is used to check the limit. If this parameter is set to 0,
770 renegotiation is disabled. The default is <literal>512MB</>.
774 SSL libraries from before November 2009 are insecure when using SSL
775 renegotiation, due to a vulnerability in the SSL protocol. As a
776 stop-gap fix for this vulnerability, some vendors shipped SSL
777 libraries incapable of doing renegotiation. If any such libraries
778 are in use on the client or server, SSL renegotiation should be
785 <varlistentry id="guc-ssl-ciphers" xreflabel="ssl_ciphers">
786 <term><varname>ssl_ciphers</varname> (<type>string</type>)</term>
788 <primary><varname>ssl_ciphers</> configuration parameter</primary>
792 Specifies a list of <acronym>SSL</> ciphers that are allowed to be
793 used on secure connections. See the <application>openssl</>
794 manual page for a list of supported ciphers.
799 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
800 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
802 <primary><varname>password_encryption</> configuration parameter</primary>
806 When a password is specified in <xref
807 linkend="sql-createuser"> or
808 <xref linkend="sql-alterrole">
809 without writing either <literal>ENCRYPTED</> or
810 <literal>UNENCRYPTED</>, this parameter determines whether the
811 password is to be encrypted. The default is <literal>on</>
812 (encrypt the password).
817 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
818 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
820 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
824 Sets the location of the Kerberos server key file. See
825 <xref linkend="kerberos-auth"> or <xref linkend="gssapi-auth">
826 for details. This parameter can only be set in the
827 <filename>postgresql.conf</> file or on the server command line.
832 <varlistentry id="guc-krb-srvname" xreflabel="krb_srvname">
833 <term><varname>krb_srvname</varname> (<type>string</type>)</term>
835 <primary><varname>krb_srvname</> configuration parameter</primary>
839 Sets the Kerberos service name. See <xref linkend="kerberos-auth">
840 for details. This parameter can only be set in the
841 <filename>postgresql.conf</> file or on the server command line.
846 <varlistentry id="guc-krb-caseins-users" xreflabel="krb_caseins_users">
847 <term><varname>krb_caseins_users</varname> (<type>boolean</type>)</term>
849 <primary><varname>krb_caseins_users</varname> configuration parameter</primary>
853 Sets whether Kerberos and GSSAPI user names should be treated
855 The default is <literal>off</> (case sensitive). This parameter can only be
856 set in the <filename>postgresql.conf</> file or on the server command line.
861 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
862 <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
864 <primary><varname>db_user_namespace</> configuration parameter</primary>
868 This parameter enables per-database user names. It is off by default.
869 This parameter can only be set in the <filename>postgresql.conf</>
870 file or on the server command line.
874 If this is on, you should create users as <literal>username@dbname</>.
875 When <literal>username</> is passed by a connecting client,
876 <literal>@</> and the database name are appended to the user
877 name and that database-specific user name is looked up by the
878 server. Note that when you create users with names containing
879 <literal>@</> within the SQL environment, you will need to
884 With this parameter enabled, you can still create ordinary global
885 users. Simply append <literal>@</> when specifying the user
886 name in the client, e.g. <literal>joe@</>. The <literal>@</>
887 will be stripped off before the user name is looked up by the
892 <varname>db_user_namespace</> causes the client's and
893 server's user name representation to differ.
894 Authentication checks are always done with the server's user name
895 so authentication methods must be configured for the
896 server's user name, not the client's. Because
897 <literal>md5</> uses the user name as salt on both the
898 client and server, <literal>md5</> cannot be used with
899 <varname>db_user_namespace</>.
904 This feature is intended as a temporary measure until a
905 complete solution is found. At that time, this option will
916 <sect1 id="runtime-config-resource">
917 <title>Resource Consumption</title>
919 <sect2 id="runtime-config-resource-memory">
920 <title>Memory</title>
923 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
924 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
926 <primary><varname>shared_buffers</> configuration parameter</primary>
930 Sets the amount of memory the database server uses for shared
931 memory buffers. The default is typically 32 megabytes
932 (<literal>32MB</>), but might be less if your kernel settings will
933 not support it (as determined during <application>initdb</>).
934 This setting must be at least 128 kilobytes. (Non-default
935 values of <symbol>BLCKSZ</symbol> change the minimum.) However,
936 settings significantly higher than the minimum are usually needed
937 for good performance. This parameter can only be set at server start.
941 If you have a dedicated database server with 1GB or more of RAM, a
942 reasonable starting value for <varname>shared_buffers</varname> is 25%
943 of the memory in your system. There are some workloads where even
944 large settings for <varname>shared_buffers</varname> are effective, but
945 because <productname>PostgreSQL</productname> also relies on the
946 operating system cache, it is unlikely that an allocation of more than
947 40% of RAM to <varname>shared_buffers</varname> will work better than a
948 smaller amount. Larger settings for <varname>shared_buffers</varname>
949 usually require a corresponding increase in
950 <varname>checkpoint_segments</varname>, in order to spread out the
951 process of writing large quantities of new or changed data over a
952 longer period of time.
956 On systems with less than 1GB of RAM, a smaller percentage of RAM is
957 appropriate, so as to leave adequate space for the operating system.
958 Also, on Windows, large values for <varname>shared_buffers</varname>
959 aren't as effective. You may find better results keeping the setting
960 relatively low and using the operating system cache more instead. The
961 useful range for <varname>shared_buffers</varname> on Windows systems
962 is generally from 64MB to 512MB.
968 <varlistentry id="guc-temp-buffers" xreflabel="temp_buffers">
969 <term><varname>temp_buffers</varname> (<type>integer</type>)</term>
971 <primary><varname>temp_buffers</> configuration parameter</primary>
975 Sets the maximum number of temporary buffers used by each database
976 session. These are session-local buffers used only for access to
977 temporary tables. The default is eight megabytes
978 (<literal>8MB</>). The setting can be changed within individual
979 sessions, but only before the first use of temporary tables
980 within the session; subsequent attempts to change the value will
981 have no effect on that session.
985 A session will allocate temporary buffers as needed up to the limit
986 given by <varname>temp_buffers</>. The cost of setting a large
987 value in sessions that do not actually need many temporary
988 buffers is only a buffer descriptor, or about 64 bytes, per
989 increment in <varname>temp_buffers</>. However if a buffer is
990 actually used an additional 8192 bytes will be consumed for it
991 (or in general, <symbol>BLCKSZ</symbol> bytes).
996 <varlistentry id="guc-max-prepared-transactions" xreflabel="max_prepared_transactions">
997 <term><varname>max_prepared_transactions</varname> (<type>integer</type>)</term>
999 <primary><varname>max_prepared_transactions</> configuration parameter</primary>
1003 Sets the maximum number of transactions that can be in the
1004 <quote>prepared</> state simultaneously (see <xref
1005 linkend="sql-prepare-transaction">).
1006 Setting this parameter to zero (which is the default)
1007 disables the prepared-transaction feature.
1008 This parameter can only be set at server start.
1012 If you are not planning to use prepared transactions, this parameter
1013 should be set to zero to prevent accidental creation of prepared
1014 transactions. If you are using prepared transactions, you will
1015 probably want <varname>max_prepared_transactions</varname> to be at
1016 least as large as <xref linkend="guc-max-connections">, so that every
1017 session can have a prepared transaction pending.
1021 When running a standby server, you must set this parameter to the
1022 same or higher value than on the master server. Otherwise, queries
1023 will not be allowed in the standby server.
1028 <varlistentry id="guc-work-mem" xreflabel="work_mem">
1029 <term><varname>work_mem</varname> (<type>integer</type>)</term>
1031 <primary><varname>work_mem</> configuration parameter</primary>
1035 Specifies the amount of memory to be used by internal sort operations
1036 and hash tables before writing to temporary disk files. The value
1037 defaults to one megabyte (<literal>1MB</>).
1038 Note that for a complex query, several sort or hash operations might be
1039 running in parallel; each operation will be allowed to use as much memory
1040 as this value specifies before it starts to write data into temporary
1041 files. Also, several running sessions could be doing such operations
1042 concurrently. Therefore, the total memory used could be many
1043 times the value of <varname>work_mem</varname>; it is necessary to
1044 keep this fact in mind when choosing the value. Sort operations are
1045 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
1047 Hash tables are used in hash joins, hash-based aggregation, and
1048 hash-based processing of <literal>IN</> subqueries.
1053 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
1054 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
1056 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
1060 Specifies the maximum amount of memory to be used by maintenance
1061 operations, such as <command>VACUUM</command>, <command>CREATE
1062 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults
1063 to 16 megabytes (<literal>16MB</>). Since only one of these
1064 operations can be executed at a time by a database session, and
1065 an installation normally doesn't have many of them running
1066 concurrently, it's safe to set this value significantly larger
1067 than <varname>work_mem</varname>. Larger settings might improve
1068 performance for vacuuming and for restoring database dumps.
1071 Note that when autovacuum runs, up to
1072 <xref linkend="guc-autovacuum-max-workers"> times this memory may be
1073 allocated, so be careful not to set the default value too high.
1078 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
1079 <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
1081 <primary><varname>max_stack_depth</> configuration parameter</primary>
1085 Specifies the maximum safe depth of the server's execution stack.
1086 The ideal setting for this parameter is the actual stack size limit
1087 enforced by the kernel (as set by <literal>ulimit -s</> or local
1088 equivalent), less a safety margin of a megabyte or so. The safety
1089 margin is needed because the stack depth is not checked in every
1090 routine in the server, but only in key potentially-recursive routines
1091 such as expression evaluation. The default setting is two
1092 megabytes (<literal>2MB</>), which is conservatively small and
1093 unlikely to risk crashes. However, it might be too small to allow
1094 execution of complex functions. Only superusers can change this
1099 Setting <varname>max_stack_depth</> higher than
1100 the actual kernel limit will mean that a runaway recursive function
1101 can crash an individual backend process. On platforms where
1102 <productname>PostgreSQL</productname> can determine the kernel limit,
1103 the server will not allow this variable to be set to an unsafe
1104 value. However, not all platforms provide the information,
1105 so caution is recommended in selecting a value.
1113 <sect2 id="runtime-config-resource-disk">
1117 <varlistentry id="guc-temp-file-limit" xreflabel="temp_file_limit">
1118 <term><varname>temp_file_limit</varname> (<type>integer</type>)</term>
1120 <primary><varname>temp_file_limit</> configuration parameter</primary>
1124 Specifies the maximum amount of disk space that a session can use
1125 for temporary files, such as sort and hash temporary files, or the
1126 storage file for a held cursor. A transaction attempting to exceed
1127 this limit will be cancelled.
1128 The value is specified in kilobytes, and <literal>-1</> (the
1129 default) means no limit.
1130 Only superusers can change this setting.
1133 This setting constrains the total space used at any instant by all
1134 temporary files used by a given <productname>PostgreSQL</> session.
1135 It should be noted that disk space used for explicit temporary
1136 tables, as opposed to temporary files used behind-the-scenes in query
1137 execution, does <emphasis>not</emphasis> count against this limit.
1145 <sect2 id="runtime-config-resource-kernel">
1146 <title>Kernel Resource Usage</title>
1149 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
1150 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
1152 <primary><varname>max_files_per_process</> configuration parameter</primary>
1156 Sets the maximum number of simultaneously open files allowed to each
1157 server subprocess. The default is one thousand files. If the kernel is enforcing
1158 a safe per-process limit, you don't need to worry about this setting.
1159 But on some platforms (notably, most BSD systems), the kernel will
1160 allow individual processes to open many more files than the system
1161 can actually support if many processes all try to open
1162 that many files. If you find yourself seeing <quote>Too many open
1163 files</> failures, try reducing this setting.
1164 This parameter can only be set at server start.
1169 <varlistentry id="guc-shared-preload-libraries" xreflabel="shared_preload_libraries">
1170 <term><varname>shared_preload_libraries</varname> (<type>string</type>)</term>
1172 <primary><varname>shared_preload_libraries</> configuration parameter</primary>
1176 This variable specifies one or more shared libraries
1177 to be preloaded at server start. For example,
1178 <literal>'$libdir/mylib'</literal> would cause
1179 <literal>mylib.so</> (or on some platforms,
1180 <literal>mylib.sl</>) to be preloaded from the installation's
1181 standard library directory.
1182 All library names are converted to lower case unless double-quoted.
1183 If more than one library is to be loaded, separate their names
1184 with commas. This parameter can only be set at server start.
1188 <productname>PostgreSQL</productname> procedural language
1189 libraries can be preloaded in this way, typically by using the
1190 syntax <literal>'$libdir/plXXX'</literal> where
1191 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
1192 <literal>tcl</>, or <literal>python</>.
1196 By preloading a shared library, the library startup time is avoided
1197 when the library is first used. However, the time to start each new
1198 server process might increase slightly, even if that process never
1199 uses the library. So this parameter is recommended only for
1200 libraries that will be used in most sessions.
1205 On Windows hosts, preloading a library at server start will not reduce
1206 the time required to start each new server process; each server process
1207 will re-load all preload libraries. However, <varname>shared_preload_libraries
1208 </varname> is still useful on Windows hosts because some shared libraries may
1209 need to perform certain operations that only take place at postmaster start
1210 (for example, a shared library may need to reserve lightweight locks
1211 or shared memory and you can't do that after the postmaster has started).
1215 If a specified library is not found,
1216 the server will fail to start.
1220 Every PostgreSQL-supported library has a <quote>magic
1221 block</> that is checked to guarantee compatibility.
1222 For this reason, non-PostgreSQL libraries cannot be
1231 <sect2 id="runtime-config-resource-vacuum-cost">
1232 <title>Cost-based Vacuum Delay</title>
1235 During the execution of <xref linkend="sql-vacuum">
1236 and <xref linkend="sql-analyze">
1237 commands, the system maintains an
1238 internal counter that keeps track of the estimated cost of the
1239 various I/O operations that are performed. When the accumulated
1240 cost reaches a limit (specified by
1241 <varname>vacuum_cost_limit</varname>), the process performing
1242 the operation will sleep for a short period of time, as specified by
1243 <varname>vacuum_cost_delay</varname>. Then it will reset the
1244 counter and continue execution.
1248 The intent of this feature is to allow administrators to reduce
1249 the I/O impact of these commands on concurrent database
1250 activity. There are many situations where it is not
1251 important that maintenance commands like
1252 <command>VACUUM</command> and <command>ANALYZE</command> finish
1253 quickly; however, it is usually very important that these
1254 commands do not significantly interfere with the ability of the
1255 system to perform other database operations. Cost-based vacuum
1256 delay provides a way for administrators to achieve this.
1260 This feature is disabled by default for manually issued
1261 <command>VACUUM</command> commands. To enable it, set the
1262 <varname>vacuum_cost_delay</varname> variable to a nonzero
1267 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1268 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)</term>
1270 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1274 The length of time, in milliseconds, that the process will sleep
1275 when the cost limit has been exceeded.
1276 The default value is zero, which disables the cost-based vacuum
1277 delay feature. Positive values enable cost-based vacuuming.
1278 Note that on many systems, the effective resolution
1279 of sleep delays is 10 milliseconds; setting
1280 <varname>vacuum_cost_delay</varname> to a value that is
1281 not a multiple of 10 might have the same results as setting it
1282 to the next higher multiple of 10.
1286 When using cost-based vacuuming, appropriate values for
1287 <varname>vacuum_cost_delay</> are usually quite small, perhaps
1288 10 or 20 milliseconds. Adjusting vacuum's resource consumption
1289 is best done by changing the other vacuum cost parameters.
1294 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1295 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
1297 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1301 The estimated cost for vacuuming a buffer found in the shared buffer
1302 cache. It represents the cost to lock the buffer pool, lookup
1303 the shared hash table and scan the content of the page. The
1304 default value is one.
1309 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1310 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
1312 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1316 The estimated cost for vacuuming a buffer that has to be read from
1317 disk. This represents the effort to lock the buffer pool,
1318 lookup the shared hash table, read the desired block in from
1319 the disk and scan its content. The default value is 10.
1324 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1325 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
1327 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1331 The estimated cost charged when vacuum modifies a block that was
1332 previously clean. It represents the extra I/O required to
1333 flush the dirty block out to disk again. The default value is
1339 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1340 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
1342 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1346 The accumulated cost that will cause the vacuuming process to sleep.
1347 The default value is 200.
1355 There are certain operations that hold critical locks and should
1356 therefore complete as quickly as possible. Cost-based vacuum
1357 delays do not occur during such operations. Therefore it is
1358 possible that the cost accumulates far higher than the specified
1359 limit. To avoid uselessly long delays in such cases, the actual
1360 delay is calculated as <varname>vacuum_cost_delay</varname> *
1361 <varname>accumulated_balance</varname> /
1362 <varname>vacuum_cost_limit</varname> with a maximum of
1363 <varname>vacuum_cost_delay</varname> * 4.
1368 <sect2 id="runtime-config-resource-background-writer">
1369 <title>Background Writer</title>
1372 There is a separate server
1373 process called the <firstterm>background writer</>, whose function
1374 is to issue writes of <quote>dirty</> (new or modified) shared
1375 buffers. It writes shared buffers so server processes handling
1376 user queries seldom or never need to wait for a write to occur.
1377 However, the background writer does cause a net overall
1378 increase in I/O load, because while a repeatedly-dirtied page might
1379 otherwise be written only once per checkpoint interval, the
1380 background writer might write it several times as it is dirtied
1381 in the same interval. The parameters discussed in this subsection
1382 can be used to tune the behavior for local needs.
1386 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1387 <term><varname>bgwriter_delay</varname> (<type>integer</type>)</term>
1389 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1393 Specifies the delay between activity rounds for the
1394 background writer. In each round the writer issues writes
1395 for some number of dirty buffers (controllable by the
1396 following parameters). It then sleeps for <varname>bgwriter_delay</>
1397 milliseconds, and repeats. When there are no dirty buffers in the
1398 buffer pool, though, it goes into a longer sleep regardless of
1399 <varname>bgwriter_delay</>. The default value is 200
1400 milliseconds (<literal>200ms</>). Note that on many systems, the
1401 effective resolution of sleep delays is 10 milliseconds; setting
1402 <varname>bgwriter_delay</> to a value that is not a multiple of 10
1403 might have the same results as setting it to the next higher multiple
1404 of 10. This parameter can only be set in the
1405 <filename>postgresql.conf</> file or on the server command line.
1410 <varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1411 <term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)</term>
1413 <primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1417 In each round, no more than this many buffers will be written
1418 by the background writer. Setting this to zero disables
1419 background writing. (Note that checkpoints, which are managed by
1420 a separate, dedicated auxiliary process, are unaffected.)
1421 The default value is 100 buffers.
1422 This parameter can only be set in the <filename>postgresql.conf</>
1423 file or on the server command line.
1428 <varlistentry id="guc-bgwriter-lru-multiplier" xreflabel="bgwriter_lru_multiplier">
1429 <term><varname>bgwriter_lru_multiplier</varname> (<type>floating point</type>)</term>
1431 <primary><varname>bgwriter_lru_multiplier</> configuration parameter</primary>
1435 The number of dirty buffers written in each round is based on the
1436 number of new buffers that have been needed by server processes
1437 during recent rounds. The average recent need is multiplied by
1438 <varname>bgwriter_lru_multiplier</> to arrive at an estimate of the
1439 number of buffers that will be needed during the next round. Dirty
1440 buffers are written until there are that many clean, reusable buffers
1441 available. (However, no more than <varname>bgwriter_lru_maxpages</>
1442 buffers will be written per round.)
1443 Thus, a setting of 1.0 represents a <quote>just in time</> policy
1444 of writing exactly the number of buffers predicted to be needed.
1445 Larger values provide some cushion against spikes in demand,
1446 while smaller values intentionally leave writes to be done by
1449 This parameter can only be set in the <filename>postgresql.conf</>
1450 file or on the server command line.
1457 Smaller values of <varname>bgwriter_lru_maxpages</varname> and
1458 <varname>bgwriter_lru_multiplier</varname> reduce the extra I/O load
1459 caused by the background writer, but make it more likely that server
1460 processes will have to issue writes for themselves, delaying interactive
1465 <sect2 id="runtime-config-resource-async-behavior">
1466 <title>Asynchronous Behavior</title>
1469 <varlistentry id="guc-effective-io-concurrency" xreflabel="effective_io_concurrency">
1470 <term><varname>effective_io_concurrency</varname> (<type>integer</type>)</term>
1472 <primary><varname>effective_io_concurrency</> configuration parameter</primary>
1476 Sets the number of concurrent disk I/O operations that
1477 <productname>PostgreSQL</> expects can be executed
1478 simultaneously. Raising this value will increase the number of I/O
1479 operations that any individual <productname>PostgreSQL</> session
1480 attempts to initiate in parallel. The allowed range is 1 to 1000,
1481 or zero to disable issuance of asynchronous I/O requests. Currently,
1482 this setting only affects bitmap heap scans.
1486 A good starting point for this setting is the number of separate
1487 drives comprising a RAID 0 stripe or RAID 1 mirror being used for the
1488 database. (For RAID 5 the parity drive should not be counted.)
1489 However, if the database is often busy with multiple queries issued in
1490 concurrent sessions, lower values may be sufficient to keep the disk
1491 array busy. A value higher than needed to keep the disks busy will
1492 only result in extra CPU overhead.
1496 For more exotic systems, such as memory-based storage or a RAID array
1497 that is limited by bus bandwidth, the correct value might be the
1498 number of I/O paths available. Some experimentation may be needed
1499 to find the best value.
1503 Asynchronous I/O depends on an effective <function>posix_fadvise</>
1504 function, which some operating systems lack. If the function is not
1505 present then setting this parameter to anything but zero will result
1506 in an error. On some operating systems (e.g., Solaris), the function
1507 is present but does not actually do anything.
1515 <sect1 id="runtime-config-wal">
1516 <title>Write Ahead Log</title>
1519 See also <xref linkend="wal-configuration"> for details on WAL
1520 and checkpoint tuning.
1523 <sect2 id="runtime-config-wal-settings">
1524 <title>Settings</title>
1527 <varlistentry id="guc-wal-level" xreflabel="wal_level">
1528 <term><varname>wal_level</varname> (<type>enum</type>)</term>
1530 <primary><varname>wal_level</> configuration parameter</primary>
1534 <varname>wal_level</> determines how much information is written
1535 to the WAL. The default value is <literal>minimal</>, which writes
1536 only the information needed to recover from a crash or immediate
1537 shutdown. <literal>archive</> adds logging required for WAL archiving,
1538 and <literal>hot_standby</> further adds information required to run
1539 read-only queries on a standby server.
1540 This parameter can only be set at server start.
1543 In <literal>minimal</> level, WAL-logging of some bulk
1544 operations can be safely skipped, which can make those
1545 operations much faster (see <xref linkend="populate-pitr">).
1546 Operations in which this optimization can be applied include:
1548 <member><command>CREATE TABLE AS</></member>
1549 <member><command>CREATE INDEX</></member>
1550 <member><command>CLUSTER</></member>
1551 <member><command>COPY</> into tables that were created or truncated in the same
1552 transaction</member>
1554 But minimal WAL does not contain
1555 enough information to reconstruct the data from a base backup and the
1556 WAL logs, so either <literal>archive</> or <literal>hot_standby</>
1557 level must be used to enable
1558 WAL archiving (<xref linkend="guc-archive-mode">) and streaming
1562 In <literal>hot_standby</> level, the same information is logged as
1563 with <literal>archive</>, plus information needed to reconstruct
1564 the status of running transactions from the WAL. To enable read-only
1565 queries on a standby server, <varname>wal_level</> must be set to
1566 <literal>hot_standby</> on the primary, and
1567 <xref linkend="guc-hot-standby"> must be enabled in the standby. It is
1568 thought that there is
1569 little measurable difference in performance between using
1570 <literal>hot_standby</> and <literal>archive</> levels, so feedback
1571 is welcome if any production impacts are noticeable.
1576 <varlistentry id="guc-fsync" xreflabel="fsync">
1578 <primary><varname>fsync</> configuration parameter</primary>
1580 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1583 If this parameter is on, the <productname>PostgreSQL</> server
1584 will try to make sure that updates are physically written to
1585 disk, by issuing <function>fsync()</> system calls or various
1586 equivalent methods (see <xref linkend="guc-wal-sync-method">).
1587 This ensures that the database cluster can recover to a
1588 consistent state after an operating system or hardware crash.
1592 While turning off <varname>fsync</varname> is often a performance
1593 benefit, this can result in unrecoverable data corruption in
1594 the event of a power failure or system crash. Thus it
1595 is only advisable to turn off <varname>fsync</varname> if
1596 you can easily recreate your entire database from external
1601 Examples of safe circumstances for turning off
1602 <varname>fsync</varname> include the initial loading of a new
1603 database cluster from a backup file, using a database cluster
1604 for processing a batch of data after which the database
1605 will be thrown away and recreated,
1606 or for a read-only database clone which
1607 gets recreated frequently and is not used for failover. High
1608 quality hardware alone is not a sufficient justification for
1609 turning off <varname>fsync</varname>.
1613 In many situations, turning off <xref linkend="guc-synchronous-commit">
1614 for noncritical transactions can provide much of the potential
1615 performance benefit of turning off <varname>fsync</varname>, without
1616 the attendant risks of data corruption.
1620 <varname>fsync</varname> can only be set in the <filename>postgresql.conf</>
1621 file or on the server command line.
1622 If you turn this parameter off, also consider turning off
1623 <xref linkend="guc-full-page-writes">.
1628 <varlistentry id="guc-synchronous-commit" xreflabel="synchronous_commit">
1629 <term><varname>synchronous_commit</varname> (<type>enum</type>)</term>
1631 <primary><varname>synchronous_commit</> configuration parameter</primary>
1635 Specifies whether transaction commit will wait for WAL records
1636 to be written to disk before the command returns a <quote>success</>
1637 indication to the client. Valid values are <literal>on</>,
1638 <literal>remote_write</>, <literal>local</>, and <literal>off</>.
1639 The default, and safe, setting
1640 is <literal>on</>. When <literal>off</>, there can be a delay between
1641 when success is reported to the client and when the transaction is
1642 really guaranteed to be safe against a server crash. (The maximum
1643 delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
1644 <xref linkend="guc-fsync">, setting this parameter to <literal>off</>
1645 does not create any risk of database inconsistency: an operating
1646 system or database crash might
1647 result in some recent allegedly-committed transactions being lost, but
1648 the database state will be just the same as if those transactions had
1649 been aborted cleanly. So, turning <varname>synchronous_commit</> off
1650 can be a useful alternative when performance is more important than
1651 exact certainty about the durability of a transaction. For more
1652 discussion see <xref linkend="wal-async-commit">.
1655 If <xref linkend="guc-synchronous-standby-names"> is set, this
1656 parameter also controls whether or not transaction commits will wait
1657 for the transaction's WAL records to be replicated to the standby
1659 When set to <literal>on</>, commits will wait until a reply
1660 from the current synchronous standby indicates it has received
1661 the commit record of the transaction and flushed it to disk. This
1662 ensures the transaction will not be lost unless both primary and
1663 standby suffer corruption of their database storage.
1664 When set to <literal>remote_write</>, commits will wait
1665 until a reply from the current synchronous standby indicates it has
1666 received the commit record of the transaction and written it out to
1667 the standby's operating system, but the data has not necessarily
1668 reached stable storage on the standby. This setting is sufficient to
1669 ensure data preservation even if the standby instance of
1670 <productname>PostgreSQL</> were to crash, but not if the standby
1671 suffers an operating-system-level crash.
1675 replication is in use, it will normally be sensible either to wait
1676 for both local flush to disk and replication of WAL records, or
1677 to allow the transaction to commit asynchronously. However, the
1678 setting <literal>local</> is available for transactions that
1679 wish to wait for local flush to disk, but not synchronous replication.
1680 If <varname>synchronous_standby_names</> is not set, the settings
1681 <literal>on</>, <literal>remote_write</> and <literal>local</> all
1682 provide the same synchronization level: transaction commits only wait
1683 for local flush to disk.
1686 This parameter can be changed at any time; the behavior for any
1687 one transaction is determined by the setting in effect when it
1688 commits. It is therefore possible, and useful, to have some
1689 transactions commit synchronously and others asynchronously.
1690 For example, to make a single multistatement transaction commit
1691 asynchronously when the default is the opposite, issue <command>SET
1692 LOCAL synchronous_commit TO OFF</> within the transaction.
1697 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1698 <term><varname>wal_sync_method</varname> (<type>enum</type>)</term>
1700 <primary><varname>wal_sync_method</> configuration parameter</primary>
1704 Method used for forcing WAL updates out to disk.
1705 If <varname>fsync</varname> is off then this setting is irrelevant,
1706 since WAL file updates will not be forced out at all.
1707 Possible values are:
1712 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>)
1717 <literal>fdatasync</> (call <function>fdatasync()</> at each commit)
1722 <literal>fsync</> (call <function>fsync()</> at each commit)
1727 <literal>fsync_writethrough</> (call <function>fsync()</> at each commit, forcing write-through of any disk write cache)
1732 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>)
1737 The <literal>open_</>* options also use <literal>O_DIRECT</> if available.
1738 Not all of these choices are available on all platforms.
1739 The default is the first method in the above list that is supported
1740 by the platform, except that <literal>fdatasync</> is the default on
1741 Linux. The default is not necessarily ideal; it might be
1742 necessary to change this setting or other aspects of your system
1743 configuration in order to create a crash-safe configuration or
1744 achieve optimal performance.
1745 These aspects are discussed in <xref linkend="wal-reliability">.
1746 This parameter can only be set in the <filename>postgresql.conf</>
1747 file or on the server command line.
1752 <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
1754 <primary><varname>full_page_writes</> configuration parameter</primary>
1756 <term><varname>full_page_writes</varname> (<type>boolean</type>)</term>
1759 When this parameter is on, the <productname>PostgreSQL</> server
1760 writes the entire content of each disk page to WAL during the
1761 first modification of that page after a checkpoint.
1762 This is needed because
1763 a page write that is in process during an operating system crash might
1764 be only partially completed, leading to an on-disk page
1765 that contains a mix of old and new data. The row-level change data
1766 normally stored in WAL will not be enough to completely restore
1767 such a page during post-crash recovery. Storing the full page image
1768 guarantees that the page can be correctly restored, but at the price
1769 of increasing the amount of data that must be written to WAL.
1770 (Because WAL replay always starts from a checkpoint, it is sufficient
1771 to do this during the first change of each page after a checkpoint.
1772 Therefore, one way to reduce the cost of full-page writes is to
1773 increase the checkpoint interval parameters.)
1777 Turning this parameter off speeds normal operation, but
1778 might lead to either unrecoverable data corruption, or silent
1779 data corruption, after a system failure. The risks are similar to turning off
1780 <varname>fsync</varname>, though smaller, and it should be turned off
1781 only based on the same circumstances recommended for that parameter.
1785 Turning off this parameter does not affect use of
1786 WAL archiving for point-in-time recovery (PITR)
1787 (see <xref linkend="continuous-archiving">).
1791 This parameter can only be set in the <filename>postgresql.conf</>
1792 file or on the server command line.
1793 The default is <literal>on</>.
1798 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1799 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1801 <primary><varname>wal_buffers</> configuration parameter</primary>
1805 The amount of shared memory used for WAL data that has not yet been
1806 written to disk. The default setting of -1 selects a size equal to
1807 1/32nd (about 3%) of <xref linkend="guc-shared-buffers">, but not less
1808 than <literal>64kB</literal> nor more than the size of one WAL
1809 segment, typically <literal>16MB</literal>. This value can be set
1810 manually if the automatic choice is too large or too small,
1811 but any positive value less than <literal>32kB</literal> will be
1812 treated as <literal>32kB</literal>.
1813 This parameter can only be set at server start.
1817 The contents of the WAL buffers are written out to disk at every
1818 transaction commit, so extremely large values are unlikely to
1819 provide a significant benefit. However, setting this value to at
1820 least a few megabytes can improve write performance on a busy
1821 server where many clients are committing at once. The auto-tuning
1822 selected by the default setting of -1 should give reasonable
1823 results in most cases.
1829 <varlistentry id="guc-wal-writer-delay" xreflabel="wal_writer_delay">
1830 <term><varname>wal_writer_delay</varname> (<type>integer</type>)</term>
1832 <primary><varname>wal_writer_delay</> configuration parameter</primary>
1836 Specifies the delay between activity rounds for the WAL writer.
1837 In each round the writer will flush WAL to disk. It then sleeps for
1838 <varname>wal_writer_delay</> milliseconds, and repeats. The default
1839 value is 200 milliseconds (<literal>200ms</>). Note that on many
1840 systems, the effective resolution of sleep delays is 10 milliseconds;
1841 setting <varname>wal_writer_delay</> to a value that is not a multiple
1842 of 10 might have the same results as setting it to the next higher
1843 multiple of 10. This parameter can only be set in the
1844 <filename>postgresql.conf</> file or on the server command line.
1849 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
1850 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1852 <primary><varname>commit_delay</> configuration parameter</primary>
1856 <varname>commit_delay</varname> adds a time delay, set in
1857 microseconds, before a WAL flush is initiated. This can improve
1858 group commit throughput by allowing a larger number of transactions
1859 to commit via a single WAL flush, if system load is high enough
1860 that additional transactions become ready to commit within the
1861 given interval. However, it also increases latency by up to
1862 <varname>commit_delay</varname> microseconds for each WAL
1863 flush. Because the delay is just wasted if no other transactions
1864 become ready to commit, it is only performed if at least
1865 <varname>commit_siblings</varname> other transactions are active
1866 immediately before a flush would otherwise have been initiated.
1867 In <productname>PostgreSQL</> releases prior to 9.3,
1868 <varname>commit_delay</varname> behaved differently and was much
1869 less effective: it affected only commits, rather than all WAL flushes,
1870 and waited for the entire configured delay even if the WAL flush
1871 was completed sooner. Beginning in <productname>PostgreSQL</> 9.3,
1872 the first process that becomes ready to flush waits for the configured
1873 interval, while subsequent processes wait only until the leader
1874 completes the flush. The default <varname>commit_delay</> is zero
1880 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
1881 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1883 <primary><varname>commit_siblings</> configuration parameter</primary>
1887 Minimum number of concurrent open transactions to require
1888 before performing the <varname>commit_delay</> delay. A larger
1889 value makes it more probable that at least one other
1890 transaction will become ready to commit during the delay
1891 interval. The default is five transactions.
1898 <sect2 id="runtime-config-wal-checkpoints">
1899 <title>Checkpoints</title>
1902 <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1903 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1905 <primary><varname>checkpoint_segments</> configuration parameter</primary>
1909 Maximum number of log file segments between automatic WAL
1910 checkpoints (each segment is normally 16 megabytes). The default
1911 is three segments. Increasing this parameter can increase the
1912 amount of time needed for crash recovery.
1913 This parameter can only be set in the <filename>postgresql.conf</>
1914 file or on the server command line.
1919 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1920 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1922 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
1926 Maximum time between automatic WAL checkpoints, in
1927 seconds. The default is five minutes (<literal>5min</>).
1928 Increasing this parameter can increase the amount of time needed
1930 This parameter can only be set in the <filename>postgresql.conf</>
1931 file or on the server command line.
1936 <varlistentry id="guc-checkpoint-completion-target" xreflabel="checkpoint_completion_target">
1937 <term><varname>checkpoint_completion_target</varname> (<type>floating point</type>)</term>
1939 <primary><varname>checkpoint_completion_target</> configuration parameter</primary>
1943 Specifies the target of checkpoint completion, as a fraction of
1944 total time between checkpoints. The default is 0.5.
1946 This parameter can only be set in the <filename>postgresql.conf</>
1947 file or on the server command line.
1952 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1953 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1955 <primary><varname>checkpoint_warning</> configuration parameter</primary>
1959 Write a message to the server log if checkpoints caused by
1960 the filling of checkpoint segment files happen closer together
1961 than this many seconds (which suggests that
1962 <varname>checkpoint_segments</> ought to be raised). The default is
1963 30 seconds (<literal>30s</>). Zero disables the warning.
1964 This parameter can only be set in the <filename>postgresql.conf</>
1965 file or on the server command line.
1972 <sect2 id="runtime-config-wal-archiving">
1973 <title>Archiving</title>
1976 <varlistentry id="guc-archive-mode" xreflabel="archive_mode">
1977 <term><varname>archive_mode</varname> (<type>boolean</type>)</term>
1979 <primary><varname>archive_mode</> configuration parameter</primary>
1983 When <varname>archive_mode</> is enabled, completed WAL segments
1984 are sent to archive storage by setting
1985 <xref linkend="guc-archive-command">.
1986 <varname>archive_mode</> and <varname>archive_command</> are
1987 separate variables so that <varname>archive_command</> can be
1988 changed without leaving archiving mode.
1989 This parameter can only be set at server start.
1990 <varname>archive_mode</> cannot be enabled when
1991 <varname>wal_level</> is set to <literal>minimal</>.
1996 <varlistentry id="guc-archive-command" xreflabel="archive_command">
1997 <term><varname>archive_command</varname> (<type>string</type>)</term>
1999 <primary><varname>archive_command</> configuration parameter</primary>
2003 The shell command to execute to archive a completed WAL file
2004 segment. Any <literal>%p</> in the string is
2005 replaced by the path name of the file to archive, and any
2006 <literal>%f</> is replaced by only the file name.
2007 (The path name is relative to the working directory of the server,
2008 i.e., the cluster's data directory.)
2009 Use <literal>%%</> to embed an actual <literal>%</> character in the
2010 command. It is important for the command to return a zero
2011 exit status only if it succeeds. For more information see
2012 <xref linkend="backup-archiving-wal">.
2015 This parameter can only be set in the <filename>postgresql.conf</>
2016 file or on the server command line. It is ignored unless
2017 <varname>archive_mode</> was enabled at server start.
2018 If <varname>archive_command</> is an empty string (the default) while
2019 <varname>archive_mode</> is enabled, WAL archiving is temporarily
2020 disabled, but the server continues to accumulate WAL segment files in
2021 the expectation that a command will soon be provided. Setting
2022 <varname>archive_command</> to a command that does nothing but
2023 return true, e.g. <literal>/bin/true</> (<literal>REM</> on
2024 Windows), effectively disables
2025 archiving, but also breaks the chain of WAL files needed for
2026 archive recovery, so it should only be used in unusual circumstances.
2031 <varlistentry id="guc-archive-timeout" xreflabel="archive_timeout">
2032 <term><varname>archive_timeout</varname> (<type>integer</type>)</term>
2034 <primary><varname>archive_timeout</> configuration parameter</primary>
2038 The <xref linkend="guc-archive-command"> is only invoked for
2039 completed WAL segments. Hence, if your server generates little WAL
2040 traffic (or has slack periods where it does so), there could be a
2041 long delay between the completion of a transaction and its safe
2042 recording in archive storage. To limit how old unarchived
2043 data can be, you can set <varname>archive_timeout</> to force the
2044 server to switch to a new WAL segment file periodically. When this
2045 parameter is greater than zero, the server will switch to a new
2046 segment file whenever this many seconds have elapsed since the last
2047 segment file switch, and there has been any database activity,
2048 including a single checkpoint. (Increasing
2049 <varname>checkpoint_timeout</> will reduce unnecessary
2050 checkpoints on an idle system.)
2051 Note that archived files that are closed early
2052 due to a forced switch are still the same length as completely full
2053 files. Therefore, it is unwise to use a very short
2054 <varname>archive_timeout</> — it will bloat your archive
2055 storage. <varname>archive_timeout</> settings of a minute or so are
2056 usually reasonable. You should consider using streaming replication,
2057 instead of archiving, if you want data to be copied off the master
2058 server more quickly than that.
2059 This parameter can only be set in the
2060 <filename>postgresql.conf</> file or on the server command line.
2070 <sect1 id="runtime-config-replication">
2071 <title>Replication</title>
2074 These settings control the behavior of the built-in
2075 <firstterm>streaming replication</> feature (see
2076 <xref linkend="streaming-replication">). Servers will be either a
2077 Master or a Standby server. Masters can send data, while Standby(s)
2078 are always receivers of replicated data. When cascading replication
2079 (see <xref linkend="cascading-replication">) is used, Standby server(s)
2080 can also be senders, as well as receivers.
2081 Parameters are mainly for Sending and Standby servers, though some
2082 parameters have meaning only on the Master server. Settings may vary
2083 across the cluster without problems if that is required.
2086 <sect2 id="runtime-config-replication-sender">
2087 <title>Sending Server(s)</title>
2090 These parameters can be set on any server that is
2091 to send replication data to one or more standby servers.
2092 The master is always a sending server, so these parameters must
2093 always be set on the master.
2094 The role and meaning of these parameters does not change after a
2095 standby becomes the master.
2099 <varlistentry id="guc-max-wal-senders" xreflabel="max_wal_senders">
2100 <term><varname>max_wal_senders</varname> (<type>integer</type>)</term>
2102 <primary><varname>max_wal_senders</> configuration parameter</primary>
2106 Specifies the maximum number of concurrent connections from
2107 standby servers or streaming base backup clients (i.e., the
2108 maximum number of simultaneously running WAL sender
2109 processes). The default is zero, meaning replication is
2110 disabled. WAL sender processes count towards the total number
2111 of connections, so the parameter cannot be set higher than
2112 <xref linkend="guc-max-connections">. This parameter can only
2113 be set at server start. <varname>wal_level</> must be set
2114 to <literal>archive</> or <literal>hot_standby</> to allow
2115 connections from standby servers.
2120 <varlistentry id="guc-wal-keep-segments" xreflabel="wal_keep_segments">
2121 <term><varname>wal_keep_segments</varname> (<type>integer</type>)</term>
2123 <primary><varname>wal_keep_segments</> configuration parameter</primary>
2127 Specifies the minimum number of past log file segments kept in the
2128 <filename>pg_xlog</>
2129 directory, in case a standby server needs to fetch them for streaming
2130 replication. Each segment is normally 16 megabytes. If a standby
2131 server connected to the sending server falls behind by more than
2132 <varname>wal_keep_segments</> segments, the sending server might remove
2133 a WAL segment still needed by the standby, in which case the
2134 replication connection will be terminated. Downstream connections
2135 will also eventually fail as a result. (However, the standby
2136 server can recover by fetching the segment from archive, if WAL
2137 archiving is in use.)
2141 This sets only the minimum number of segments retained in
2142 <filename>pg_xlog</>; the system might need to retain more segments
2143 for WAL archival or to recover from a checkpoint. If
2144 <varname>wal_keep_segments</> is zero (the default), the system
2145 doesn't keep any extra segments for standby purposes, so the number
2146 of old WAL segments available to standby servers is a function of
2147 the location of the previous checkpoint and status of WAL
2149 This parameter can only be set in the
2150 <filename>postgresql.conf</> file or on the server command line.
2155 <varlistentry id="guc-replication-timeout" xreflabel="replication_timeout">
2156 <term><varname>replication_timeout</varname> (<type>integer</type>)</term>
2158 <primary><varname>replication_timeout</> configuration parameter</primary>
2162 Terminate replication connections that are inactive longer
2163 than the specified number of milliseconds. This is useful for
2164 the sending server to detect a standby crash or network outage.
2165 A value of zero disables the timeout mechanism. This parameter
2167 the <filename>postgresql.conf</> file or on the server command line.
2168 The default value is 60 seconds.
2171 To prevent connections from being terminated prematurely,
2172 <xref linkend="guc-wal-receiver-status-interval">
2173 must be enabled on the standby, and its value must be less than the
2174 value of <varname>replication_timeout</>.
2182 <sect2 id="runtime-config-replication-master">
2183 <title>Master Server</title>
2186 These parameters can be set on the master/primary server that is
2187 to send replication data to one or more standby servers.
2188 Note that in addition to these parameters,
2189 <xref linkend="guc-wal-level"> must be set appropriately on the master
2190 server, and optionally WAL archiving can be enabled as
2191 well (see <xref linkend="runtime-config-wal-archiving">).
2192 The values of these parameters on standby servers are irrelevant,
2193 although you may wish to set them there in preparation for the
2194 possibility of a standby becoming the master.
2199 <varlistentry id="guc-synchronous-standby-names" xreflabel="synchronous_standby_names">
2200 <term><varname>synchronous_standby_names</varname> (<type>string</type>)</term>
2202 <primary><varname>synchronous_standby_names</> configuration parameter</primary>
2206 Specifies a comma-separated list of standby names that can support
2207 <firstterm>synchronous replication</>, as described in
2208 <xref linkend="synchronous-replication">.
2209 At any one time there will be at most one active synchronous standby;
2210 transactions waiting for commit will be allowed to proceed after
2211 this standby server confirms receipt of their data.
2212 The synchronous standby will be the first standby named in this list
2213 that is both currently connected and streaming data in real-time
2214 (as shown by a state of <literal>streaming</literal> in the
2215 <link linkend="monitoring-stats-views-table">
2216 <literal>pg_stat_replication</></link> view).
2217 Other standby servers appearing later in this list represent potential
2218 synchronous standbys.
2219 If the current synchronous standby disconnects for whatever reason,
2220 it will be replaced immediately with the next-highest-priority standby.
2221 Specifying more than one standby name can allow very high availability.
2224 The name of a standby server for this purpose is the
2225 <varname>application_name</> setting of the standby, as set in the
2226 <varname>primary_conninfo</> of the standby's walreceiver. There is
2227 no mechanism to enforce uniqueness. In case of duplicates one of the
2228 matching standbys will be chosen to be the synchronous standby, though
2229 exactly which one is indeterminate.
2230 The special entry <literal>*</> matches any
2231 <varname>application_name</>, including the default application name
2232 of <literal>walreceiver</>.
2235 If no synchronous standby names are specified here, then synchronous
2236 replication is not enabled and transaction commits will not wait for
2237 replication. This is the default configuration. Even when
2238 synchronous replication is enabled, individual transactions can be
2239 configured not to wait for replication by setting the
2240 <xref linkend="guc-synchronous-commit"> parameter to
2241 <literal>local</> or <literal>off</>.
2244 This parameter can only be set in the <filename>postgresql.conf</>
2245 file or on the server command line.
2250 <varlistentry id="guc-vacuum-defer-cleanup-age" xreflabel="vacuum_defer_cleanup_age">
2251 <term><varname>vacuum_defer_cleanup_age</varname> (<type>integer</type>)</term>
2253 <primary><varname>vacuum_defer_cleanup_age</> configuration parameter</primary>
2257 Specifies the number of transactions by which <command>VACUUM</> and
2258 <acronym>HOT</> updates will defer cleanup of dead row versions. The
2259 default is zero transactions, meaning that dead row versions can be
2260 removed as soon as possible, that is, as soon as they are no longer
2261 visible to any open transaction. You may wish to set this to a
2262 non-zero value on a primary server that is supporting hot standby
2263 servers, as described in <xref linkend="hot-standby">. This allows
2264 more time for queries on the standby to complete without incurring
2265 conflicts due to early cleanup of rows. However, since the value
2266 is measured in terms of number of write transactions occurring on the
2267 primary server, it is difficult to predict just how much additional
2268 grace time will be made available to standby queries.
2269 This parameter can only be set in the <filename>postgresql.conf</>
2270 file or on the server command line.
2273 You should also consider setting <varname>hot_standby_feedback</>
2274 on standby server(s) as an alternative to using this parameter.
2282 <sect2 id="runtime-config-replication-standby">
2283 <title>Standby Servers</title>
2286 These settings control the behavior of a standby server that is
2287 to receive replication data. Their values on the master server
2293 <varlistentry id="guc-hot-standby" xreflabel="hot_standby">
2294 <term><varname>hot_standby</varname> (<type>boolean</type>)</term>
2296 <primary><varname>hot_standby</> configuration parameter</primary>
2300 Specifies whether or not you can connect and run queries during
2301 recovery, as described in <xref linkend="hot-standby">.
2302 The default value is <literal>off</literal>.
2303 This parameter can only be set at server start. It only has effect
2304 during archive recovery or in standby mode.
2309 <varlistentry id="guc-max-standby-archive-delay" xreflabel="max_standby_archive_delay">
2310 <term><varname>max_standby_archive_delay</varname> (<type>integer</type>)</term>
2312 <primary><varname>max_standby_archive_delay</> configuration parameter</primary>
2316 When Hot Standby is active, this parameter determines how long the
2317 standby server should wait before canceling standby queries that
2318 conflict with about-to-be-applied WAL entries, as described in
2319 <xref linkend="hot-standby-conflict">.
2320 <varname>max_standby_archive_delay</> applies when WAL data is
2321 being read from WAL archive (and is therefore not current).
2322 The default is 30 seconds. Units are milliseconds if not specified.
2323 A value of -1 allows the standby to wait forever for conflicting
2324 queries to complete.
2325 This parameter can only be set in the <filename>postgresql.conf</>
2326 file or on the server command line.
2329 Note that <varname>max_standby_archive_delay</> is not the same as the
2330 maximum length of time a query can run before cancellation; rather it
2331 is the maximum total time allowed to apply any one WAL segment's data.
2332 Thus, if one query has resulted in significant delay earlier in the
2333 WAL segment, subsequent conflicting queries will have much less grace
2339 <varlistentry id="guc-max-standby-streaming-delay" xreflabel="max_standby_streaming_delay">
2340 <term><varname>max_standby_streaming_delay</varname> (<type>integer</type>)</term>
2342 <primary><varname>max_standby_streaming_delay</> configuration parameter</primary>
2346 When Hot Standby is active, this parameter determines how long the
2347 standby server should wait before canceling standby queries that
2348 conflict with about-to-be-applied WAL entries, as described in
2349 <xref linkend="hot-standby-conflict">.
2350 <varname>max_standby_streaming_delay</> applies when WAL data is
2351 being received via streaming replication.
2352 The default is 30 seconds. Units are milliseconds if not specified.
2353 A value of -1 allows the standby to wait forever for conflicting
2354 queries to complete.
2355 This parameter can only be set in the <filename>postgresql.conf</>
2356 file or on the server command line.
2359 Note that <varname>max_standby_streaming_delay</> is not the same as
2360 the maximum length of time a query can run before cancellation; rather
2361 it is the maximum total time allowed to apply WAL data once it has
2362 been received from the primary server. Thus, if one query has
2363 resulted in significant delay, subsequent conflicting queries will
2364 have much less grace time until the standby server has caught up
2370 <varlistentry id="guc-wal-receiver-status-interval" xreflabel="wal_receiver_status_interval">
2371 <term><varname>wal_receiver_status_interval</varname> (<type>integer</type>)</term>
2373 <primary><varname>wal_receiver_status_interval</> configuration parameter</primary>
2377 Specifies the minimum frequency for the WAL receiver
2378 process on the standby to send information about replication progress
2379 to the primary or upstream standby, where it can be seen using the
2380 <link linkend="monitoring-stats-views-table">
2381 <literal>pg_stat_replication</></link> view. The standby will report
2382 the last transaction log position it has written, the last position it
2383 has flushed to disk, and the last position it has applied.
2385 value is the maximum interval, in seconds, between reports. Updates are
2386 sent each time the write or flush positions change, or at least as
2387 often as specified by this parameter. Thus, the apply position may
2388 lag slightly behind the true position. Setting this parameter to zero
2389 disables status updates completely. This parameter can only be set in
2390 the <filename>postgresql.conf</> file or on the server command line.
2391 The default value is 10 seconds.
2394 When <xref linkend="guc-replication-timeout"> is enabled on a sending server,
2395 <varname>wal_receiver_status_interval</> must be enabled, and its value
2396 must be less than the value of <varname>replication_timeout</>.
2401 <varlistentry id="guc-hot-standby-feedback" xreflabel="hot_standby">
2402 <term><varname>hot_standby_feedback</varname> (<type>boolean</type>)</term>
2404 <primary><varname>hot_standby_feedback</> configuration parameter</primary>
2408 Specifies whether or not a hot standby will send feedback to the primary
2410 about queries currently executing on the standby. This parameter can
2411 be used to eliminate query cancels caused by cleanup records, but
2412 can cause database bloat on the primary for some workloads.
2413 Feedback messages will not be sent more frequently than once per
2414 <varname>wal_receiver_status_interval</>. The default value is
2415 <literal>off</literal>. This parameter can only be set in the
2416 <filename>postgresql.conf</> file or on the server command line.
2419 If cascaded replication is in use the feedback is passed upstream
2420 until it eventually reaches the primary. Standbys make no other use
2421 of feedback they receive other than to pass upstream.
2430 <sect1 id="runtime-config-query">
2431 <title>Query Planning</title>
2433 <sect2 id="runtime-config-query-enable">
2434 <title>Planner Method Configuration</title>
2437 These configuration parameters provide a crude method of
2438 influencing the query plans chosen by the query optimizer. If
2439 the default plan chosen by the optimizer for a particular query
2440 is not optimal, a <emphasis>temporary</> solution is to use one
2441 of these configuration parameters to force the optimizer to
2442 choose a different plan.
2443 Better ways to improve the quality of the
2444 plans chosen by the optimizer include adjusting the planer cost
2445 constants (see <xref linkend="runtime-config-query-constants">),
2446 running <xref linkend="sql-analyze"> manually, increasing
2447 the value of the <xref
2448 linkend="guc-default-statistics-target"> configuration parameter,
2449 and increasing the amount of statistics collected for
2450 specific columns using <command>ALTER TABLE SET
2451 STATISTICS</command>.
2455 <varlistentry id="guc-enable-bitmapscan" xreflabel="enable_bitmapscan">
2456 <term><varname>enable_bitmapscan</varname> (<type>boolean</type>)</term>
2458 <primary>bitmap scan</primary>
2461 <primary><varname>enable_bitmapscan</> configuration parameter</primary>
2465 Enables or disables the query planner's use of bitmap-scan plan
2466 types. The default is <literal>on</>.
2471 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
2472 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
2474 <primary><varname>enable_hashagg</> configuration parameter</primary>
2478 Enables or disables the query planner's use of hashed
2479 aggregation plan types. The default is <literal>on</>.
2484 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
2485 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
2487 <primary><varname>enable_hashjoin</> configuration parameter</primary>
2491 Enables or disables the query planner's use of hash-join plan
2492 types. The default is <literal>on</>.
2497 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
2498 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
2500 <primary>index scan</primary>
2503 <primary><varname>enable_indexscan</> configuration parameter</primary>
2507 Enables or disables the query planner's use of index-scan plan
2508 types. The default is <literal>on</>.
2513 <varlistentry id="guc-enable-indexonlyscan" xreflabel="enable_indexonlyscan">
2514 <term><varname>enable_indexonlyscan</varname> (<type>boolean</type>)</term>
2516 <primary>index-only scan</primary>
2519 <primary><varname>enable_indexonlyscan</> configuration parameter</primary>
2523 Enables or disables the query planner's use of index-only-scan plan
2524 types. The default is <literal>on</>.
2529 <varlistentry id="guc-enable-material" xreflabel="enable_material">
2530 <term><varname>enable_material</varname> (<type>boolean</type>)</term>
2532 <primary><varname>enable_material</> configuration parameter</primary>
2536 Enables or disables the query planner's use of materialization.
2537 It is impossible to suppress materialization entirely,
2538 but turning this variable off prevents the planner from inserting
2539 materialize nodes except in cases where it is required for correctness.
2540 The default is <literal>on</>.
2545 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
2546 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
2548 <primary><varname>enable_mergejoin</> configuration parameter</primary>
2552 Enables or disables the query planner's use of merge-join plan
2553 types. The default is <literal>on</>.
2558 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
2559 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
2561 <primary><varname>enable_nestloop</> configuration parameter</primary>
2565 Enables or disables the query planner's use of nested-loop join
2566 plans. It is impossible to suppress nested-loop joins entirely,
2567 but turning this variable off discourages the planner from using
2568 one if there are other methods available. The default is
2574 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
2575 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
2577 <primary>sequential scan</primary>
2580 <primary><varname>enable_seqscan</> configuration parameter</primary>
2584 Enables or disables the query planner's use of sequential scan
2585 plan types. It is impossible to suppress sequential scans
2586 entirely, but turning this variable off discourages the planner
2587 from using one if there are other methods available. The
2588 default is <literal>on</>.
2593 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
2594 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
2596 <primary><varname>enable_sort</> configuration parameter</primary>
2600 Enables or disables the query planner's use of explicit sort
2601 steps. It is impossible to suppress explicit sorts entirely,
2602 but turning this variable off discourages the planner from
2603 using one if there are other methods available. The default
2609 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
2610 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
2612 <primary><varname>enable_tidscan</> configuration parameter</primary>
2616 Enables or disables the query planner's use of <acronym>TID</>
2617 scan plan types. The default is <literal>on</>.
2624 <sect2 id="runtime-config-query-constants">
2625 <title>Planner Cost Constants</title>
2628 The <firstterm>cost</> variables described in this section are measured
2629 on an arbitrary scale. Only their relative values matter, hence
2630 scaling them all up or down by the same factor will result in no change
2631 in the planner's choices. By default, these cost variables are based on
2632 the cost of sequential page fetches; that is,
2633 <varname>seq_page_cost</> is conventionally set to <literal>1.0</>
2634 and the other cost variables are set with reference to that. But
2635 you can use a different scale if you prefer, such as actual execution
2636 times in milliseconds on a particular machine.
2641 Unfortunately, there is no well-defined method for determining ideal
2642 values for the cost variables. They are best treated as averages over
2643 the entire mix of queries that a particular installation will receive. This
2644 means that changing them on the basis of just a few experiments is very
2651 <varlistentry id="guc-seq-page-cost" xreflabel="seq_page_cost">
2652 <term><varname>seq_page_cost</varname> (<type>floating point</type>)</term>
2654 <primary><varname>seq_page_cost</> configuration parameter</primary>
2658 Sets the planner's estimate of the cost of a disk page fetch
2659 that is part of a series of sequential fetches. The default is 1.0.
2660 This value can be overridden for tables and indexes in a particular
2661 tablespace by setting the tablespace parameter of the same name
2662 (see <xref linkend="sql-altertablespace">).
2667 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
2668 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
2670 <primary><varname>random_page_cost</> configuration parameter</primary>
2674 Sets the planner's estimate of the cost of a
2675 non-sequentially-fetched disk page. The default is 4.0.
2676 This value can be overridden for tables and indexes in a particular
2677 tablespace by setting the tablespace parameter of the same name
2678 (see <xref linkend="sql-altertablespace">).
2682 Reducing this value relative to <varname>seq_page_cost</>
2683 will cause the system to prefer index scans; raising it will
2684 make index scans look relatively more expensive. You can raise
2685 or lower both values together to change the importance of disk I/O
2686 costs relative to CPU costs, which are described by the following
2691 Random access to mechanical disk storage is normally much more expensive
2692 than four-times sequential access. However, a lower default is used
2693 (4.0) because the majority of random accesses to disk, such as indexed
2694 reads, are assumed to be in cache. The default value can be thought of
2695 as modeling random access as 40 times slower than sequential, while
2696 expecting 90% of random reads to be cached.
2700 If you believe a 90% cache rate is an incorrect assumption
2701 for your workload, you can increase random_page_cost to better
2702 reflect the true cost of random storage reads. Correspondingly,
2703 if your data is likely to be completely in cache, such as when
2704 the database is smaller than the total server memory, decreasing
2705 random_page_cost can be appropriate. Storage that has a low random
2706 read cost relative to sequential, e.g. solid-state drives, might
2707 also be better modeled with a lower value for random_page_cost.
2712 Although the system will let you set <varname>random_page_cost</> to
2713 less than <varname>seq_page_cost</>, it is not physically sensible
2714 to do so. However, setting them equal makes sense if the database
2715 is entirely cached in RAM, since in that case there is no penalty
2716 for touching pages out of sequence. Also, in a heavily-cached
2717 database you should lower both values relative to the CPU parameters,
2718 since the cost of fetching a page already in RAM is much smaller
2719 than it would normally be.
2725 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
2726 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
2728 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
2732 Sets the planner's estimate of the cost of processing
2733 each row during a query.
2734 The default is 0.01.
2739 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
2740 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
2742 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
2746 Sets the planner's estimate of the cost of processing
2747 each index entry during an index scan.
2748 The default is 0.005.
2753 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
2754 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
2756 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
2760 Sets the planner's estimate of the cost of processing each
2761 operator or function executed during a query.
2762 The default is 0.0025.
2767 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
2768 <term><varname>effective_cache_size</varname> (<type>integer</type>)</term>
2770 <primary><varname>effective_cache_size</> configuration parameter</primary>
2774 Sets the planner's assumption about the effective size of the
2775 disk cache that is available to a single query. This is
2776 factored into estimates of the cost of using an index; a
2777 higher value makes it more likely index scans will be used, a
2778 lower value makes it more likely sequential scans will be
2779 used. When setting this parameter you should consider both
2780 <productname>PostgreSQL</productname>'s shared buffers and the
2781 portion of the kernel's disk cache that will be used for
2782 <productname>PostgreSQL</productname> data files. Also, take
2783 into account the expected number of concurrent queries on different
2784 tables, since they will have to share the available
2785 space. This parameter has no effect on the size of shared
2786 memory allocated by <productname>PostgreSQL</productname>, nor
2787 does it reserve kernel disk cache; it is used only for estimation
2788 purposes. The system also does not assume data remains in
2789 the disk cache between queries. The default is 128 megabytes
2790 (<literal>128MB</>).
2798 <sect2 id="runtime-config-query-geqo">
2799 <title>Genetic Query Optimizer</title>
2802 The genetic query optimizer (GEQO) is an algorithm that does query
2803 planning using heuristic searching. This reduces planning time for
2804 complex queries (those joining many relations), at the cost of producing
2805 plans that are sometimes inferior to those found by the normal
2806 exhaustive-search algorithm.
2807 For more information see <xref linkend="geqo">.
2812 <varlistentry id="guc-geqo" xreflabel="geqo">
2814 <primary>genetic query optimization</primary>
2817 <primary>GEQO</primary>
2818 <see>genetic query optimization</see>
2821 <primary><varname>geqo</> configuration parameter</primary>
2823 <term><varname>geqo</varname> (<type>boolean</type>)</term>
2826 Enables or disables genetic query optimization.
2827 This is on by default. It is usually best not to turn it off in
2828 production; the <varname>geqo_threshold</varname> variable provides
2829 more granular control of GEQO.
2834 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
2835 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
2837 <primary><varname>geqo_threshold</> configuration parameter</primary>
2841 Use genetic query optimization to plan queries with at least
2842 this many <literal>FROM</> items involved. (Note that a
2843 <literal>FULL OUTER JOIN</> construct counts as only one <literal>FROM</>
2844 item.) The default is 12. For simpler queries it is usually best
2845 to use the regular, exhaustive-search planner, but for queries with
2846 many tables the exhaustive search takes too long, often
2847 longer than the penalty of executing a suboptimal plan. Thus,
2848 a threshold on the size of the query is a convenient way to manage
2854 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
2855 <term><varname>geqo_effort</varname>
2856 (<type>integer</type>)</term>
2858 <primary><varname>geqo_effort</> configuration parameter</primary>
2862 Controls the trade-off between planning time and query plan
2863 quality in GEQO. This variable must be an integer in the
2864 range from 1 to 10. The default value is five. Larger values
2865 increase the time spent doing query planning, but also
2866 increase the likelihood that an efficient query plan will be
2871 <varname>geqo_effort</varname> doesn't actually do anything
2872 directly; it is only used to compute the default values for
2873 the other variables that influence GEQO behavior (described
2874 below). If you prefer, you can set the other parameters by
2880 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
2881 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
2883 <primary><varname>geqo_pool_size</> configuration parameter</primary>
2887 Controls the pool size used by GEQO, that is the
2888 number of individuals in the genetic population. It must be
2889 at least two, and useful values are typically 100 to 1000. If
2890 it is set to zero (the default setting) then a suitable
2891 value is chosen based on <varname>geqo_effort</varname> and
2892 the number of tables in the query.
2897 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
2898 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
2900 <primary><varname>geqo_generations</> configuration parameter</primary>
2904 Controls the number of generations used by GEQO, that is
2905 the number of iterations of the algorithm. It must
2906 be at least one, and useful values are in the same range as
2907 the pool size. If it is set to zero (the default setting)
2908 then a suitable value is chosen based on
2909 <varname>geqo_pool_size</varname>.
2914 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
2915 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
2917 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
2921 Controls the selection bias used by GEQO. The selection bias
2922 is the selective pressure within the population. Values can be
2923 from 1.50 to 2.00; the latter is the default.
2928 <varlistentry id="guc-geqo-seed" xreflabel="geqo_seed">
2929 <term><varname>geqo_seed</varname> (<type>floating point</type>)</term>
2931 <primary><varname>geqo_seed</> configuration parameter</primary>
2935 Controls the initial value of the random number generator used
2936 by GEQO to select random paths through the join order search space.
2937 The value can range from zero (the default) to one. Varying the
2938 value changes the set of join paths explored, and may result in a
2939 better or worse best path being found.
2946 <sect2 id="runtime-config-query-other">
2947 <title>Other Planner Options</title>
2951 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
2952 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
2954 <primary><varname>default_statistics_target</> configuration parameter</primary>
2958 Sets the default statistics target for table columns without
2959 a column-specific target set via <command>ALTER TABLE
2960 SET STATISTICS</>. Larger values increase the time needed to
2961 do <command>ANALYZE</>, but might improve the quality of the
2962 planner's estimates. The default is 100. For more information
2963 on the use of statistics by the <productname>PostgreSQL</>
2964 query planner, refer to <xref linkend="planner-stats">.
2969 <varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
2970 <term><varname>constraint_exclusion</varname> (<type>enum</type>)</term>
2972 <primary>constraint exclusion</primary>
2975 <primary><varname>constraint_exclusion</> configuration parameter</primary>
2979 Controls the query planner's use of table constraints to
2981 The allowed values of <varname>constraint_exclusion</> are
2982 <literal>on</> (examine constraints for all tables),
2983 <literal>off</> (never examine constraints), and
2984 <literal>partition</> (examine constraints only for inheritance child
2985 tables and <literal>UNION ALL</> subqueries).
2986 <literal>partition</> is the default setting.
2987 It is often used with inheritance and partitioned tables to
2988 improve performance.
2992 When this parameter allows it for a particular table, the planner
2993 compares query conditions with the table's <literal>CHECK</>
2994 constraints, and omits scanning tables for which the conditions
2995 contradict the constraints. For example:
2998 CREATE TABLE parent(key integer, ...);
2999 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
3000 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
3002 SELECT * FROM parent WHERE key = 2400;
3005 With constraint exclusion enabled, this <command>SELECT</>
3006 will not scan <structname>child1000</> at all, improving performance.
3010 Currently, constraint exclusion is enabled by default
3011 only for cases that are often used to implement table partitioning.
3012 Turning it on for all tables imposes extra planning overhead that is
3013 quite noticeable on simple queries, and most often will yield no
3014 benefit for simple queries. If you have no partitioned tables
3015 you might prefer to turn it off entirely.
3019 Refer to <xref linkend="ddl-partitioning-constraint-exclusion"> for
3020 more information on using constraint exclusion and partitioning.
3025 <varlistentry id="guc-cursor-tuple-fraction" xreflabel="cursor_tuple_fraction">
3026 <term><varname>cursor_tuple_fraction</varname> (<type>floating point</type>)</term>
3028 <primary><varname>cursor_tuple_fraction</> configuration parameter</primary>
3032 Sets the planner's estimate of the fraction of a cursor's rows that
3033 will be retrieved. The default is 0.1. Smaller values of this
3034 setting bias the planner towards using <quote>fast start</> plans
3035 for cursors, which will retrieve the first few rows quickly while
3036 perhaps taking a long time to fetch all rows. Larger values
3037 put more emphasis on the total estimated time. At the maximum
3038 setting of 1.0, cursors are planned exactly like regular queries,
3039 considering only the total estimated time and not how soon the
3040 first rows might be delivered.
3045 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
3046 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
3048 <primary><varname>from_collapse_limit</> configuration parameter</primary>
3052 The planner will merge sub-queries into upper queries if the
3053 resulting <literal>FROM</literal> list would have no more than
3054 this many items. Smaller values reduce planning time but might
3055 yield inferior query plans. The default is eight.
3056 For more information see <xref linkend="explicit-joins">.
3060 Setting this value to <xref linkend="guc-geqo-threshold"> or more
3061 may trigger use of the GEQO planner, resulting in non-optimal
3062 plans. See <xref linkend="runtime-config-query-geqo">.
3067 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
3068 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
3070 <primary><varname>join_collapse_limit</> configuration parameter</primary>
3074 The planner will rewrite explicit <literal>JOIN</>
3075 constructs (except <literal>FULL JOIN</>s) into lists of
3076 <literal>FROM</> items whenever a list of no more than this many items
3077 would result. Smaller values reduce planning time but might
3078 yield inferior query plans.
3082 By default, this variable is set the same as
3083 <varname>from_collapse_limit</varname>, which is appropriate
3084 for most uses. Setting it to 1 prevents any reordering of
3085 explicit <literal>JOIN</>s. Thus, the explicit join order
3086 specified in the query will be the actual order in which the
3087 relations are joined. Because the query planner does not always choose
3088 the optimal join order, advanced users can elect to
3089 temporarily set this variable to 1, and then specify the join
3090 order they desire explicitly.
3091 For more information see <xref linkend="explicit-joins">.
3095 Setting this value to <xref linkend="guc-geqo-threshold"> or more
3096 may trigger use of the GEQO planner, resulting in non-optimal
3097 plans. See <xref linkend="runtime-config-query-geqo">.
3106 <sect1 id="runtime-config-logging">
3107 <title>Error Reporting and Logging</title>
3109 <indexterm zone="runtime-config-logging">
3110 <primary>server log</primary>
3113 <sect2 id="runtime-config-logging-where">
3114 <title>Where To Log</title>
3116 <indexterm zone="runtime-config-logging-where">
3117 <primary>where to log</primary>
3122 <varlistentry id="guc-log-destination" xreflabel="log_destination">
3123 <term><varname>log_destination</varname> (<type>string</type>)</term>
3125 <primary><varname>log_destination</> configuration parameter</primary>
3129 <productname>PostgreSQL</productname> supports several methods
3130 for logging server messages, including
3131 <systemitem>stderr</systemitem>, <systemitem>csvlog</systemitem> and
3132 <systemitem>syslog</systemitem>. On Windows,
3133 <systemitem>eventlog</systemitem> is also supported. Set this
3134 parameter to a list of desired log destinations separated by
3135 commas. The default is to log to <systemitem>stderr</systemitem>
3137 This parameter can only be set in the <filename>postgresql.conf</>
3138 file or on the server command line.
3141 If <systemitem>csvlog</> is included in <varname>log_destination</>,
3142 log entries are output in <quote>comma separated
3143 value</> (<acronym>CSV</>) format, which is convenient for
3144 loading logs into programs.
3145 See <xref linkend="runtime-config-logging-csvlog"> for details.
3146 <xref linkend="guc-logging-collector"> must be enabled to generate
3147 CSV-format log output.
3152 On most Unix systems, you will need to alter the configuration of
3153 your system's <application>syslog</application> daemon in order
3154 to make use of the <systemitem>syslog</systemitem> option for
3155 <varname>log_destination</>. <productname>PostgreSQL</productname>
3156 can log to <application>syslog</application> facilities
3157 <literal>LOCAL0</> through <literal>LOCAL7</> (see <xref
3158 linkend="guc-syslog-facility">), but the default
3159 <application>syslog</application> configuration on most platforms
3160 will discard all such messages. You will need to add something like:
3162 local0.* /var/log/postgresql
3164 to the <application>syslog</application> daemon's configuration file
3168 On Windows, when you use the <literal>eventlog</literal>
3169 option for <varname>log_destination</>, you should
3170 register an event source and its library with the operating
3171 system so that the Windows Event Viewer can display event
3172 log messages cleanly.
3173 See <xref linkend="event-log-registration"> for details.
3179 <varlistentry id="guc-logging-collector" xreflabel="logging_collector">
3180 <term><varname>logging_collector</varname> (<type>boolean</type>)</term>
3182 <primary><varname>logging_collector</> configuration parameter</primary>
3186 This parameter enables the <firstterm>logging collector</>, which
3187 is a background process that captures log messages
3188 sent to <systemitem>stderr</> and redirects them into log files.
3189 This approach is often more useful than
3190 logging to <application>syslog</>, since some types of messages
3191 might not appear in <application>syslog</> output. (One common
3192 example is dynamic-linker failure messages; another is error messages
3193 produced by scripts such as <varname>archive_command</>.)
3194 This parameter can only be set at server start.
3199 It is possible to log to <systemitem>stderr</> without using the
3200 logging collector; the log messages will just go to wherever the
3201 server's <systemitem>stderr</> is directed. However, that method is
3202 only suitable for low log volumes, since it provides no convenient
3203 way to rotate log files. Also, on some platforms not using the
3204 logging collector can result in lost or garbled log output, because
3205 multiple processes writing concurrently to the same log file can
3206 overwrite each other's output.
3212 The logging collector is designed to never lose messages. This means
3213 that in case of extremely high load, server processes could be
3214 blocked while trying to send additional log messages when the
3215 collector has fallen behind. In contrast, <application>syslog</>
3216 prefers to drop messages if it cannot write them, which means it
3217 may fail to log some messages in such cases but it will not block
3218 the rest of the system.
3225 <varlistentry id="guc-log-directory" xreflabel="log_directory">
3226 <term><varname>log_directory</varname> (<type>string</type>)</term>
3228 <primary><varname>log_directory</> configuration parameter</primary>
3232 When <varname>logging_collector</> is enabled,
3233 this parameter determines the directory in which log files will be created.
3234 It can be specified as an absolute path, or relative to the
3235 cluster data directory.
3236 This parameter can only be set in the <filename>postgresql.conf</>
3237 file or on the server command line.
3242 <varlistentry id="guc-log-filename" xreflabel="log_filename">
3243 <term><varname>log_filename</varname> (<type>string</type>)</term>
3245 <primary><varname>log_filename</> configuration parameter</primary>
3249 When <varname>logging_collector</varname> is enabled,
3250 this parameter sets the file names of the created log files. The value
3251 is treated as a <systemitem>strftime</systemitem> pattern,
3252 so <literal>%</literal>-escapes can be used to specify time-varying
3253 file names. (Note that if there are
3254 any time-zone-dependent <literal>%</literal>-escapes, the computation
3255 is done in the zone specified
3256 by <xref linkend="guc-log-timezone">.)
3257 The supported <literal>%</literal>-escapes are similar to those
3258 listed in the Open Group's <ulink
3259 url="http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html">strftime
3260 </ulink> specification.
3261 Note that the system's <systemitem>strftime</systemitem> is not used
3262 directly, so platform-specific (nonstandard) extensions do not work.
3265 If you specify a file name without escapes, you should plan to
3266 use a log rotation utility to avoid eventually filling the
3267 entire disk. In releases prior to 8.4, if
3268 no <literal>%</literal> escapes were
3269 present, <productname>PostgreSQL</productname> would append
3270 the epoch of the new log file's creation time, but this is no
3274 If CSV-format output is enabled in <varname>log_destination</>,
3275 <literal>.csv</> will be appended to the timestamped
3276 log file name to create the file name for CSV-format output.
3277 (If <varname>log_filename</> ends in <literal>.log</>, the suffix is
3279 In the case of the example above, the CSV
3280 file name will be <literal>server_log.1093827753.csv</literal>.
3283 This parameter can only be set in the <filename>postgresql.conf</>
3284 file or on the server command line.
3289 <varlistentry id="guc-log-file-mode" xreflabel="log_file_mode">
3290 <term><varname>log_file_mode</varname> (<type>integer</type>)</term>
3292 <primary><varname>log_file_mode</> configuration parameter</primary>
3296 On Unix systems this parameter sets the permissions for log files
3297 when <varname>logging_collector</varname> is enabled. (On Microsoft
3298 Windows this parameter is ignored.)
3299 The parameter value is expected to be a numeric mode
3300 specified in the format accepted by the
3301 <function>chmod</function> and <function>umask</function>
3302 system calls. (To use the customary octal format the number
3303 must start with a <literal>0</literal> (zero).)
3306 The default permissions are <literal>0600</>, meaning only the
3307 server owner can read or write the log files. The other commonly
3308 useful setting is <literal>0640</>, allowing members of the owner's
3309 group to read the files. Note however that to make use of such a
3310 setting, you'll need to alter <xref linkend="guc-log-directory"> to
3311 store the files somewhere outside the cluster data directory. In
3312 any case, it's unwise to make the log files world-readable, since
3313 they might contain sensitive data.
3316 This parameter can only be set in the <filename>postgresql.conf</>
3317 file or on the server command line.
3322 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
3323 <term><varname>log_rotation_age</varname> (<type>integer</type>)</term>
3325 <primary><varname>log_rotation_age</> configuration parameter</primary>
3329 When <varname>logging_collector</varname> is enabled,
3330 this parameter determines the maximum lifetime of an individual log file.
3331 After this many minutes have elapsed, a new log file will
3332 be created. Set to zero to disable time-based creation of
3334 This parameter can only be set in the <filename>postgresql.conf</>
3335 file or on the server command line.
3340 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
3341 <term><varname>log_rotation_size</varname> (<type>integer</type>)</term>
3343 <primary><varname>log_rotation_size</> configuration parameter</primary>
3347 When <varname>logging_collector</varname> is enabled,
3348 this parameter determines the maximum size of an individual log file.
3349 After this many kilobytes have been emitted into a log file,
3350 a new log file will be created. Set to zero to disable size-based
3351 creation of new log files.
3352 This parameter can only be set in the <filename>postgresql.conf</>
3353 file or on the server command line.
3358 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
3359 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)</term>
3361 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
3365 When <varname>logging_collector</varname> is enabled,
3366 this parameter will cause <productname>PostgreSQL</productname> to truncate (overwrite),
3367 rather than append to, any existing log file of the same name.
3368 However, truncation will occur only when a new file is being opened
3369 due to time-based rotation, not during server startup or size-based
3370 rotation. When off, pre-existing files will be appended to in
3371 all cases. For example, using this setting in combination with
3372 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
3373 would result in generating twenty-four hourly log files and then
3374 cyclically overwriting them.
3375 This parameter can only be set in the <filename>postgresql.conf</>
3376 file or on the server command line.
3379 Example: To keep 7 days of logs, one log file per day named
3380 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
3381 etc, and automatically overwrite last week's log with this week's log,
3382 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
3383 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>, and
3384 <varname>log_rotation_age</varname> to <literal>1440</literal>.
3387 Example: To keep 24 hours of logs, one log file per hour, but
3388 also rotate sooner if the log file size exceeds 1GB, set
3389 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
3390 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>,
3391 <varname>log_rotation_age</varname> to <literal>60</literal>, and
3392 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
3393 Including <literal>%M</> in <varname>log_filename</varname> allows
3394 any size-driven rotations that might occur to select a file name
3395 different from the hour's initial file name.
3400 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
3401 <term><varname>syslog_facility</varname> (<type>enum</type>)</term>
3403 <primary><varname>syslog_facility</> configuration parameter</primary>
3407 When logging to <application>syslog</> is enabled, this parameter
3408 determines the <application>syslog</application>
3409 <quote>facility</quote> to be used. You can choose
3410 from <literal>LOCAL0</>, <literal>LOCAL1</>,
3411 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
3412 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
3413 the default is <literal>LOCAL0</>. See also the
3414 documentation of your system's
3415 <application>syslog</application> daemon.
3416 This parameter can only be set in the <filename>postgresql.conf</>
3417 file or on the server command line.
3422 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
3423 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
3425 <primary><varname>syslog_identity</> configuration parameter</primary>
3429 When logging to <application>syslog</> is enabled, this parameter
3430 determines the program name used to identify
3431 <productname>PostgreSQL</productname> messages in
3432 <application>syslog</application> logs. The default is
3433 <literal>postgres</literal>.
3434 This parameter can only be set in the <filename>postgresql.conf</>
3435 file or on the server command line.
3440 <varlistentry id="guc-event-source" xreflabel="event_source">
3441 <term><varname>event_source</varname> (<type>string</type>)</term>
3443 <primary><varname>event_source</> configuration parameter</primary>
3447 When logging to <application>event log</> is enabled, this parameter
3448 determines the program name used to identify
3449 <productname>PostgreSQL</productname> messages in
3450 the log. The default is <literal>PostgreSQL</literal>.
3451 This parameter can only be set in the <filename>postgresql.conf</>
3452 file or on the server command line.
3459 <sect2 id="runtime-config-logging-when">
3460 <title>When To Log</title>
3464 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
3465 <term><varname>client_min_messages</varname> (<type>enum</type>)</term>
3467 <primary><varname>client_min_messages</> configuration parameter</primary>
3471 Controls which message levels are sent to the client.
3472 Valid values are <literal>DEBUG5</>,
3473 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
3474 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
3475 <literal>WARNING</>, <literal>ERROR</>, <literal>FATAL</>,
3476 and <literal>PANIC</>. Each level
3477 includes all the levels that follow it. The later the level,
3478 the fewer messages are sent. The default is
3479 <literal>NOTICE</>. Note that <literal>LOG</> has a different
3480 rank here than in <varname>log_min_messages</>.
3485 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
3486 <term><varname>log_min_messages</varname> (<type>enum</type>)</term>
3488 <primary><varname>log_min_messages</> configuration parameter</primary>
3492 Controls which message levels are written to the server log.
3493 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
3494 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
3495 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
3496 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
3497 <literal>PANIC</>. Each level includes all the levels that
3498 follow it. The later the level, the fewer messages are sent
3499 to the log. The default is <literal>WARNING</>. Note that
3500 <literal>LOG</> has a different rank here than in
3501 <varname>client_min_messages</>.
3502 Only superusers can change this setting.
3507 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
3508 <term><varname>log_min_error_statement</varname> (<type>enum</type>)</term>
3510 <primary><varname>log_min_error_statement</> configuration parameter</primary>
3514 Controls which SQL statements that cause an error
3515 condition are recorded in the server log. The current
3516 SQL statement is included in the log entry for any message of
3517 the specified severity or higher.
3518 Valid values are <literal>DEBUG5</literal>,
3519 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
3520 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
3521 <literal>INFO</literal>, <literal>NOTICE</literal>,
3522 <literal>WARNING</literal>, <literal>ERROR</literal>,
3523 <literal>LOG</literal>,
3524 <literal>FATAL</literal>, and <literal>PANIC</literal>.
3525 The default is <literal>ERROR</literal>, which means statements
3526 causing errors, log messages, fatal errors, or panics will be logged.
3527 To effectively turn off logging of failing statements,
3528 set this parameter to <literal>PANIC</literal>.
3529 Only superusers can change this setting.
3534 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
3535 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
3537 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
3541 Causes the duration of each completed statement to be logged
3542 if the statement ran for at least the specified number of
3543 milliseconds. Setting this to zero prints all statement durations.
3544 Minus-one (the default) disables logging statement durations.
3545 For example, if you set it to <literal>250ms</literal>
3546 then all SQL statements that run 250ms or longer will be
3547 logged. Enabling this parameter can be helpful in tracking down
3548 unoptimized queries in your applications.
3549 Only superusers can change this setting.
3553 For clients using extended query protocol, durations of the Parse,
3554 Bind, and Execute steps are logged independently.
3559 When using this option together with
3560 <xref linkend="guc-log-statement">,
3561 the text of statements that are logged because of
3562 <varname>log_statement</> will not be repeated in the
3563 duration log message.
3564 If you are not using <application>syslog</>, it is recommended
3565 that you log the PID or session ID using
3566 <xref linkend="guc-log-line-prefix">
3567 so that you can link the statement message to the later
3568 duration message using the process ID or session ID.
3577 <xref linkend="runtime-config-severity-levels"> explains the message
3578 severity levels used by <productname>PostgreSQL</>. If logging output
3579 is sent to <systemitem>syslog</systemitem> or Windows'
3580 <systemitem>eventlog</systemitem>, the severity levels are translated
3581 as shown in the table.
3584 <table id="runtime-config-severity-levels">
3585 <title>Message Severity Levels</title>
3589 <entry>Severity</entry>
3590 <entry>Usage</entry>
3591 <entry><systemitem>syslog</></entry>
3592 <entry><systemitem>eventlog</></entry>
3598 <entry><literal>DEBUG1..DEBUG5</></entry>
3599 <entry>Provides successively-more-detailed information for use by
3601 <entry><literal>DEBUG</></entry>
3602 <entry><literal>INFORMATION</></entry>
3606 <entry><literal>INFO</></entry>
3607 <entry>Provides information implicitly requested by the user,
3608 e.g., output from <command>VACUUM VERBOSE</>.</entry>
3609 <entry><literal>INFO</></entry>
3610 <entry><literal>INFORMATION</></entry>
3614 <entry><literal>NOTICE</></entry>
3615 <entry>Provides information that might be helpful to users, e.g.,
3616 notice of truncation of long identifiers.</entry>
3617 <entry><literal>NOTICE</></entry>
3618 <entry><literal>INFORMATION</></entry>
3622 <entry><literal>WARNING</></entry>
3623 <entry>Provides warnings of likely problems, e.g., <command>COMMIT</>
3624 outside a transaction block.</entry>
3625 <entry><literal>NOTICE</></entry>
3626 <entry><literal>WARNING</></entry>
3630 <entry><literal>ERROR</></entry>
3631 <entry>Reports an error that caused the current command to
3633 <entry><literal>WARNING</></entry>
3634 <entry><literal>ERROR</></entry>
3638 <entry><literal>LOG</></entry>
3639 <entry>Reports information of interest to administrators, e.g.,
3640 checkpoint activity.</entry>
3641 <entry><literal>INFO</></entry>
3642 <entry><literal>INFORMATION</></entry>
3646 <entry><literal>FATAL</></entry>
3647 <entry>Reports an error that caused the current session to
3649 <entry><literal>ERR</></entry>
3650 <entry><literal>ERROR</></entry>
3654 <entry><literal>PANIC</></entry>
3655 <entry>Reports an error that caused all database sessions to abort.</entry>
3656 <entry><literal>CRIT</></entry>
3657 <entry><literal>ERROR</></entry>
3664 <sect2 id="runtime-config-logging-what">
3665 <title>What To Log</title>
3669 <varlistentry id="guc-application-name" xreflabel="application_name">
3670 <term><varname>application_name</varname> (<type>string</type>)</term>
3672 <primary><varname>application_name</> configuration parameter</primary>
3676 The <varname>application_name</varname> can be any string of less than
3677 <symbol>NAMEDATALEN</> characters (64 characters in a standard build).
3678 It is typically set by an application upon connection to the server.
3679 The name will be displayed in the <structname>pg_stat_activity</> view
3680 and included in CSV log entries. It can also be included in regular
3681 log entries via the <xref linkend="guc-log-line-prefix"> parameter.
3682 Only printable ASCII characters may be used in the
3683 <varname>application_name</varname> value. Other characters will be
3684 replaced with question marks (<literal>?</literal>).
3690 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
3691 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
3692 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
3694 <primary><varname>debug_print_parse</> configuration parameter</primary>
3697 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
3700 <primary><varname>debug_print_plan</> configuration parameter</primary>
3704 These parameters enable various debugging output to be emitted.
3705 When set, they print the resulting parse tree, the query rewriter
3706 output, or the execution plan for each executed query.
3707 These messages are emitted at <literal>LOG</> message level, so by
3708 default they will appear in the server log but will not be sent to the
3709 client. You can change that by adjusting
3710 <xref linkend="guc-client-min-messages"> and/or
3711 <xref linkend="guc-log-min-messages">.
3712 These parameters are off by default.
3718 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
3720 <primary><varname>debug_pretty_print</> configuration parameter</primary>
3724 When set, <varname>debug_pretty_print</varname> indents the messages
3725 produced by <varname>debug_print_parse</varname>,
3726 <varname>debug_print_rewritten</varname>, or
3727 <varname>debug_print_plan</varname>. This results in more readable
3728 but much longer output than the <quote>compact</> format used when
3729 it is off. It is on by default.
3734 <varlistentry id="guc-log-checkpoints" xreflabel="log_checkpoints">
3735 <term><varname>log_checkpoints</varname> (<type>boolean</type>)</term>
3737 <primary><varname>log_checkpoints</> configuration parameter</primary>
3741 Causes checkpoints and restartpoints to be logged in the server log.
3742 Some statistics are included in the log messages, including the number
3743 of buffers written and the time spent writing them.
3744 This parameter can only be set in the <filename>postgresql.conf</>
3745 file or on the server command line. The default is off.
3750 <varlistentry id="guc-log-connections" xreflabel="log_connections">
3751 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
3753 <primary><varname>log_connections</> configuration parameter</primary>
3757 Causes each attempted connection to the server to be logged,
3758 as well as successful completion of client authentication.
3759 This parameter cannot be changed after session start.
3765 Some client programs, like <application>psql</>, attempt
3766 to connect twice while determining if a password is required, so
3767 duplicate <quote>connection received</> messages do not
3768 necessarily indicate a problem.
3774 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
3775 <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
3777 <primary><varname>log_disconnections</> configuration parameter</primary>
3781 This outputs a line in the server log similar to
3782 <varname>log_connections</varname> but at session termination,
3783 and includes the duration of the session. This is off by
3785 This parameter cannot be changed after session start.
3791 <varlistentry id="guc-log-duration" xreflabel="log_duration">
3792 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
3794 <primary><varname>log_duration</> configuration parameter</primary>
3798 Causes the duration of every completed statement to be logged.
3799 The default is <literal>off</>.
3800 Only superusers can change this setting.
3804 For clients using extended query protocol, durations of the Parse,
3805 Bind, and Execute steps are logged independently.
3810 The difference between setting this option and setting
3811 <xref linkend="guc-log-min-duration-statement"> to zero is that
3812 exceeding <varname>log_min_duration_statement</> forces the text of
3813 the query to be logged, but this option doesn't. Thus, if
3814 <varname>log_duration</> is <literal>on</> and
3815 <varname>log_min_duration_statement</> has a positive value, all
3816 durations are logged but the query text is included only for
3817 statements exceeding the threshold. This behavior can be useful for
3818 gathering statistics in high-load installations.
3824 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
3825 <term><varname>log_error_verbosity</varname> (<type>enum</type>)</term>
3827 <primary><varname>log_error_verbosity</> configuration parameter</primary>
3831 Controls the amount of detail written in the server log for each
3832 message that is logged. Valid values are <literal>TERSE</>,
3833 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
3834 fields to displayed messages. <literal>TERSE</> excludes
3835 the logging of <literal>DETAIL</>, <literal>HINT</>,
3836 <literal>QUERY</>, and <literal>CONTEXT</> error information.
3837 <literal>VERBOSE</> output includes the <symbol>SQLSTATE</> error
3838 code (see also <xref linkend="errcodes-appendix">) and the source code file name, function name,
3839 and line number that generated the error.
3840 Only superusers can change this setting.
3845 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
3846 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
3848 <primary><varname>log_hostname</> configuration parameter</primary>
3852 By default, connection log messages only show the IP address of the
3853 connecting host. Turning this parameter on causes logging of the
3854 host name as well. Note that depending on your host name resolution
3855 setup this might impose a non-negligible performance penalty.
3856 This parameter can only be set in the <filename>postgresql.conf</>
3857 file or on the server command line.
3862 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
3863 <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
3865 <primary><varname>log_line_prefix</> configuration parameter</primary>
3869 This is a <function>printf</>-style string that is output at the
3870 beginning of each log line.
3871 <literal>%</> characters begin <quote>escape sequences</>
3872 that are replaced with status information as outlined below.
3873 Unrecognized escapes are ignored. Other
3874 characters are copied straight to the log line. Some escapes are
3875 only recognized by session processes, and are ignored by
3876 background processes such as the main server process.
3877 This parameter can only be set in the <filename>postgresql.conf</>
3878 file or on the server command line. The default is an empty string.
3884 <entry>Escape</entry>
3885 <entry>Effect</entry>
3886 <entry>Session only</entry>
3891 <entry><literal>%a</literal></entry>
3892 <entry>Application name</entry>
3896 <entry><literal>%u</literal></entry>
3897 <entry>User name</entry>
3901 <entry><literal>%d</literal></entry>
3902 <entry>Database name</entry>
3906 <entry><literal>%r</literal></entry>
3907 <entry>Remote host name or IP address, and remote port</entry>
3911 <entry><literal>%h</literal></entry>
3912 <entry>Remote host name or IP address</entry>
3916 <entry><literal>%p</literal></entry>
3917 <entry>Process ID</entry>
3921 <entry><literal>%t</literal></entry>
3922 <entry>Time stamp without milliseconds</entry>
3926 <entry><literal>%m</literal></entry>
3927 <entry>Time stamp with milliseconds</entry>
3931 <entry><literal>%i</literal></entry>
3932 <entry>Command tag: type of session's current command</entry>
3936 <entry><literal>%e</literal></entry>
3937 <entry>SQLSTATE error code</entry>
3941 <entry><literal>%c</literal></entry>
3942 <entry>Session ID: see below</entry>
3946 <entry><literal>%l</literal></entry>
3947 <entry>Number of the log line for each session or process, starting at 1</entry>
3951 <entry><literal>%s</literal></entry>
3952 <entry>Process start time stamp</entry>
3956 <entry><literal>%v</literal></entry>
3957 <entry>Virtual transaction ID (backendID/localXID)</entry>
3961 <entry><literal>%x</literal></entry>
3962 <entry>Transaction ID (0 if none is assigned)</entry>
3966 <entry><literal>%q</literal></entry>
3967 <entry>Produces no output, but tells non-session
3968 processes to stop at this point in the string; ignored by
3969 session processes</entry>
3973 <entry><literal>%%</literal></entry>
3974 <entry>Literal <literal>%</></entry>
3981 The <literal>%c</> escape prints a quasi-unique session identifier,
3982 consisting of two 4-byte hexadecimal numbers (without leading zeros)
3983 separated by a dot. The numbers are the process start time and the
3984 process ID, so <literal>%c</> can also be used as a space saving way
3985 of printing those items. For example, to generate the session
3986 identifier from <literal>pg_stat_activity</>, use this query:
3988 SELECT to_hex(EXTRACT(EPOCH FROM backend_start)::integer) || '.' ||
3990 FROM pg_stat_activity;
3997 If you set a nonempty value for <varname>log_line_prefix</>,
3998 you should usually make its last character be a space, to provide
3999 visual separation from the rest of the log line. A punctuation
4000 character can be used too.
4006 <application>Syslog</> produces its own
4007 time stamp and process ID information, so you probably do not want to
4008 include those escapes if you are logging to <application>syslog</>.
4014 <varlistentry id="guc-log-lock-waits" xreflabel="log_lock_waits">
4015 <term><varname>log_lock_waits</varname> (<type>boolean</type>)</term>
4017 <primary><varname>log_lock_waits</> configuration parameter</primary>
4021 Controls whether a log message is produced when a session waits
4022 longer than <xref linkend="guc-deadlock-timeout"> to acquire a
4023 lock. This is useful in determining if lock waits are causing
4024 poor performance. The default is <literal>off</>.
4029 <varlistentry id="guc-log-statement" xreflabel="log_statement">
4030 <term><varname>log_statement</varname> (<type>enum</type>)</term>
4032 <primary><varname>log_statement</> configuration parameter</primary>
4036 Controls which SQL statements are logged. Valid values are
4037 <literal>none</> (off), <literal>ddl</>, <literal>mod</>, and
4038 <literal>all</> (all statements). <literal>ddl</> logs all data definition
4039 statements, such as <command>CREATE</>, <command>ALTER</>, and
4040 <command>DROP</> statements. <literal>mod</> logs all
4041 <literal>ddl</> statements, plus data-modifying statements
4042 such as <command>INSERT</>,
4043 <command>UPDATE</>, <command>DELETE</>, <command>TRUNCATE</>,
4044 and <command>COPY FROM</>.
4045 <command>PREPARE</>, <command>EXECUTE</>, and
4046 <command>EXPLAIN ANALYZE</> statements are also logged if their
4047 contained command is of an appropriate type. For clients using
4048 extended query protocol, logging occurs when an Execute message
4049 is received, and values of the Bind parameters are included
4050 (with any embedded single-quote marks doubled).
4054 The default is <literal>none</>. Only superusers can change this
4060 Statements that contain simple syntax errors are not logged
4061 even by the <varname>log_statement</> = <literal>all</> setting,
4062 because the log message is emitted only after basic parsing has
4063 been done to determine the statement type. In the case of extended
4064 query protocol, this setting likewise does not log statements that
4065 fail before the Execute phase (i.e., during parse analysis or
4066 planning). Set <varname>log_min_error_statement</> to
4067 <literal>ERROR</> (or lower) to log such statements.
4073 <varlistentry id="guc-log-temp-files" xreflabel="log_temp_files">
4074 <term><varname>log_temp_files</varname> (<type>integer</type>)</term>
4076 <primary><varname>log_temp_files</> configuration parameter</primary>
4080 Controls logging of temporary file names and sizes.
4081 Temporary files can be
4082 created for sorts, hashes, and temporary query results.
4083 A log entry is made for each temporary file when it is deleted.
4084 A value of zero logs all temporary file information, while positive
4085 values log only files whose size is greater than or equal to
4086 the specified number of kilobytes. The
4087 default setting is -1, which disables such logging.
4088 Only superusers can change this setting.
4093 <varlistentry id="guc-log-timezone" xreflabel="log_timezone">
4094 <term><varname>log_timezone</varname> (<type>string</type>)</term>
4096 <primary><varname>log_timezone</> configuration parameter</primary>
4100 Sets the time zone used for timestamps written in the server log.
4101 Unlike <xref linkend="guc-timezone">, this value is cluster-wide,
4102 so that all sessions will report timestamps consistently.
4103 The built-in default is <literal>GMT</>, but that is typically
4104 overridden in <filename>postgresql.conf</>; <application>initdb</>
4105 will install a setting there corresponding to its system environment.
4106 See <xref linkend="datatype-timezones"> for more information.
4107 This parameter can only be set in the <filename>postgresql.conf</>
4108 file or on the server command line.
4115 <sect2 id="runtime-config-logging-csvlog">
4116 <title>Using CSV-Format Log Output</title>
4119 Including <literal>csvlog</> in the <varname>log_destination</> list
4120 provides a convenient way to import log files into a database table.
4121 This option emits log lines in comma-separated-values
4122 (<acronym>CSV</>) format,
4124 time stamp with milliseconds,
4128 client host:port number,
4130 per-session line number,
4133 virtual transaction ID,
4134 regular transaction ID,
4138 error message detail,
4140 internal query that led to the error (if any),
4141 character count of the error position therein,
4143 user query that led to the error (if any and enabled by
4144 <varname>log_min_error_statement</>),
4145 character count of the error position therein,
4146 location of the error in the PostgreSQL source code
4147 (if <varname>log_error_verbosity</> is set to <literal>verbose</>),
4148 and application name.
4149 Here is a sample table definition for storing CSV-format log output:
4152 CREATE TABLE postgres_log
4154 log_time timestamp(3) with time zone,
4158 connection_from text,
4160 session_line_num bigint,
4162 session_start_time timestamp with time zone,
4163 virtual_transaction_id text,
4164 transaction_id bigint,
4165 error_severity text,
4166 sql_state_code text,
4170 internal_query text,
4171 internal_query_pos integer,
4176 application_name text,
4177 PRIMARY KEY (session_id, session_line_num)
4183 To import a log file into this table, use the <command>COPY FROM</>
4187 COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
4192 There are a few things you need to do to simplify importing CSV log
4198 Set <varname>log_filename</varname> and
4199 <varname>log_rotation_age</> to provide a consistent,
4200 predictable naming scheme for your log files. This lets you
4201 predict what the file name will be and know when an individual log
4202 file is complete and therefore ready to be imported.
4208 Set <varname>log_rotation_size</varname> to 0 to disable
4209 size-based log rotation, as it makes the log file name difficult
4216 Set <varname>log_truncate_on_rotation</varname> to <literal>on</> so
4217 that old log data isn't mixed with the new in the same file.
4223 The table definition above includes a primary key specification.
4224 This is useful to protect against accidentally importing the same
4225 information twice. The <command>COPY</> command commits all of the
4226 data it imports at one time, so any error will cause the entire
4227 import to fail. If you import a partial log file and later import
4228 the file again when it is complete, the primary key violation will
4229 cause the import to fail. Wait until the log is complete and
4230 closed before importing. This procedure will also protect against
4231 accidentally importing a partial line that hasn't been completely
4232 written, which would also cause <command>COPY</> to fail.
4241 <sect1 id="runtime-config-statistics">
4242 <title>Run-time Statistics</title>
4244 <sect2 id="runtime-config-statistics-collector">
4245 <title>Query and Index Statistics Collector</title>
4248 These parameters control server-wide statistics collection features.
4249 When statistics collection is enabled, the data that is produced can be
4250 accessed via the <structname>pg_stat</structname> and
4251 <structname>pg_statio</structname> family of system views.
4252 Refer to <xref linkend="monitoring"> for more information.
4257 <varlistentry id="guc-track-activities" xreflabel="track_activities">
4258 <term><varname>track_activities</varname> (<type>boolean</type>)</term>
4260 <primary><varname>track_activities</> configuration parameter</primary>
4264 Enables the collection of information on the currently
4265 executing command of each session, along with the time when
4266 that command began execution. This parameter is on by
4267 default. Note that even when enabled, this information is not
4268 visible to all users, only to superusers and the user owning
4269 the session being reported on, so it should not represent a
4271 Only superusers can change this setting.
4276 <varlistentry id="guc-track-activity-query-size" xreflabel="track_activity_query_size">
4277 <term><varname>track_activity_query_size</varname> (<type>integer</type>)</term>
4279 <primary><varname>track_activity_query_size</> configuration parameter</primary>
4283 Specifies the number of bytes reserved to track the currently
4284 executing command for each active session, for the
4285 <structname>pg_stat_activity</>.<structfield>query</> field.
4286 The default value is 1024. This parameter can only be set at server
4292 <varlistentry id="guc-track-counts" xreflabel="track_counts">
4293 <term><varname>track_counts</varname> (<type>boolean</type>)</term>
4295 <primary><varname>track_counts</> configuration parameter</primary>
4299 Enables collection of statistics on database activity.
4300 This parameter is on by default, because the autovacuum
4301 daemon needs the collected information.
4302 Only superusers can change this setting.
4307 <varlistentry id="guc-track-io-timing" xreflabel="track_io_timing">
4308 <term><varname>track_io_timing</varname> (<type>boolean</type>)</term>
4310 <primary><varname>track_io_timing</> configuration parameter</primary>
4314 Enables timing of database I/O calls. This parameter is off by
4315 default, because it will repeatedly query the operating system for
4316 the current time, which may cause significant overhead on some
4317 platforms. You can use the <xref linkend="pgtesttiming"> tool to
4318 measure the overhead of timing on your system.
4319 I/O timing information is
4320 displayed in <xref linkend="pg-stat-database-view">, in the output of
4321 <xref linkend="sql-explain"> when the <literal>BUFFERS</> option is
4322 used, and by <xref linkend="pgstatstatements">. Only superusers can
4323 change this setting.
4328 <varlistentry id="guc-track-functions" xreflabel="track_functions">
4329 <term><varname>track_functions</varname> (<type>enum</type>)</term>
4331 <primary><varname>track_functions</> configuration parameter</primary>
4335 Enables tracking of function call counts and time used. Specify
4336 <literal>pl</literal> to track only procedural-language functions,
4337 <literal>all</literal> to also track SQL and C language functions.
4338 The default is <literal>none</literal>, which disables function
4339 statistics tracking. Only superusers can change this setting.
4344 SQL-language functions that are simple enough to be <quote>inlined</>
4345 into the calling query will not be tracked, regardless of this
4352 <varlistentry id="guc-update-process-title" xreflabel="update_process_title">
4353 <term><varname>update_process_title</varname> (<type>boolean</type>)</term>
4355 <primary><varname>update_process_title</> configuration parameter</primary>
4359 Enables updating of the process title every time a new SQL command
4360 is received by the server. The process title is typically viewed
4361 by the <command>ps</> command,
4362 or in Windows by using the <application>Process Explorer</>.
4363 Only superusers can change this setting.
4368 <varlistentry id="guc-stats-temp-directory" xreflabel="stats_temp_directory">
4369 <term><varname>stats_temp_directory</varname> (<type>string</type>)</term>
4371 <primary><varname>stats_temp_directory</> configuration parameter</primary>
4375 Sets the directory to store temporary statistics data in. This can be
4376 a path relative to the data directory or an absolute path. The default
4377 is <filename>pg_stat_tmp</filename>. Pointing this at a RAM-based
4378 file system will decrease physical I/O requirements and can lead to
4379 improved performance.
4380 This parameter can only be set in the <filename>postgresql.conf</>
4381 file or on the server command line.
4389 <sect2 id="runtime-config-statistics-monitor">
4390 <title>Statistics Monitoring</title>
4394 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
4395 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
4396 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
4397 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
4399 <primary><varname>log_statement_stats</> configuration parameter</primary>
4402 <primary><varname>log_parser_stats</> configuration parameter</primary>
4405 <primary><varname>log_planner_stats</> configuration parameter</primary>
4408 <primary><varname>log_executor_stats</> configuration parameter</primary>
4412 For each query, output performance statistics of the respective
4413 module to the server log. This is a crude profiling
4414 instrument, similar to the Unix <function>getrusage()</> operating
4415 system facility. <varname>log_statement_stats</varname> reports total
4416 statement statistics, while the others report per-module statistics.
4417 <varname>log_statement_stats</varname> cannot be enabled together with
4418 any of the per-module options. All of these options are disabled by
4419 default. Only superusers can change these settings.
4429 <sect1 id="runtime-config-autovacuum">
4430 <title>Automatic Vacuuming</title>
4433 <primary>autovacuum</primary>
4434 <secondary>configuration parameters</secondary>
4438 These settings control the behavior of the <firstterm>autovacuum</>
4439 feature. Refer to <xref linkend="autovacuum"> for
4445 <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
4446 <term><varname>autovacuum</varname> (<type>boolean</type>)</term>
4448 <primary><varname>autovacuum</> configuration parameter</primary>
4452 Controls whether the server should run the
4453 autovacuum launcher daemon. This is on by default; however,
4454 <xref linkend="guc-track-counts"> must also be enabled for
4456 This parameter can only be set in the <filename>postgresql.conf</>
4457 file or on the server command line.
4460 Note that even when this parameter is disabled, the system
4461 will launch autovacuum processes if necessary to
4462 prevent transaction ID wraparound. See <xref
4463 linkend="vacuum-for-wraparound"> for more information.
4468 <varlistentry id="guc-log-autovacuum-min-duration" xreflabel="log_autovacuum_min_duration">
4469 <term><varname>log_autovacuum_min_duration</varname> (<type>integer</type>)</term>
4471 <primary><varname>log_autovacuum_min_duration</> configuration parameter</primary>
4475 Causes each action executed by autovacuum to be logged if it ran for at
4476 least the specified number of milliseconds. Setting this to zero logs
4477 all autovacuum actions. Minus-one (the default) disables logging
4478 autovacuum actions. For example, if you set this to
4479 <literal>250ms</literal> then all automatic vacuums and analyzes that run
4480 250ms or longer will be logged. In addition, when this parameter is
4481 set to any value other than <literal>-1</literal>, a message will be
4482 logged if an autovacuum action is skipped due to the existence of a
4483 conflicting lock. Enabling this parameter can be helpful
4484 in tracking autovacuum activity. This setting can only be set in
4485 the <filename>postgresql.conf</> file or on the server command line.
4490 <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
4491 <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)</term>
4493 <primary><varname>autovacuum_max_workers</> configuration parameter</primary>
4497 Specifies the maximum number of autovacuum processes (other than the
4498 autovacuum launcher) which may be running at any one time. The default
4499 is three. This parameter can only be set at server start.
4504 <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
4505 <term><varname>autovacuum_naptime</varname> (<type>integer</type>)</term>
4507 <primary><varname>autovacuum_naptime</> configuration parameter</primary>
4511 Specifies the minimum delay between autovacuum runs on any given
4512 database. In each round the daemon examines the
4513 database and issues <command>VACUUM</> and <command>ANALYZE</> commands
4514 as needed for tables in that database. The delay is measured
4515 in seconds, and the default is one minute (<literal>1min</>).
4516 This parameter can only be set in the <filename>postgresql.conf</>
4517 file or on the server command line.
4522 <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
4523 <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)</term>
4525 <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
4529 Specifies the minimum number of updated or deleted tuples needed
4530 to trigger a <command>VACUUM</> in any one table.
4531 The default is 50 tuples.
4532 This parameter can only be set in the <filename>postgresql.conf</>
4533 file or on the server command line.
4534 This setting can be overridden for individual tables by
4535 changing storage parameters.
4540 <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
4541 <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)</term>
4543 <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
4547 Specifies the minimum number of inserted, updated or deleted tuples
4548 needed to trigger an <command>ANALYZE</> in any one table.
4549 The default is 50 tuples.
4550 This parameter can only be set in the <filename>postgresql.conf</>
4551 file or on the server command line.
4552 This setting can be overridden for individual tables by
4553 changing storage parameters.
4558 <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
4559 <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)</term>
4561 <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
4565 Specifies a fraction of the table size to add to
4566 <varname>autovacuum_vacuum_threshold</varname>
4567 when deciding whether to trigger a <command>VACUUM</>.
4568 The default is 0.2 (20% of table size).
4569 This parameter can only be set in the <filename>postgresql.conf</>
4570 file or on the server command line.
4571 This setting can be overridden for individual tables by
4572 changing storage parameters.
4577 <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
4578 <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)</term>
4580 <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
4584 Specifies a fraction of the table size to add to
4585 <varname>autovacuum_analyze_threshold</varname>
4586 when deciding whether to trigger an <command>ANALYZE</>.
4587 The default is 0.1 (10% of table size).
4588 This parameter can only be set in the <filename>postgresql.conf</>
4589 file or on the server command line.
4590 This setting can be overridden for individual tables by
4591 changing storage parameters.
4596 <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
4597 <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)</term>
4599 <primary><varname>autovacuum_freeze_max_age</> configuration parameter</primary>
4603 Specifies the maximum age (in transactions) that a table's
4604 <structname>pg_class</>.<structfield>relfrozenxid</> field can
4605 attain before a <command>VACUUM</> operation is forced
4606 to prevent transaction ID wraparound within the table.
4607 Note that the system will launch autovacuum processes to
4608 prevent wraparound even when autovacuum is otherwise disabled.
4612 Vacuum also allows removal of old files from the
4613 <filename>pg_clog</> subdirectory, which is why the default
4614 is a relatively low 200 million transactions.
4615 This parameter can only be set at server start, but the setting
4616 can be reduced for individual tables by
4617 changing storage parameters.
4618 For more information see <xref linkend="vacuum-for-wraparound">.
4623 <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
4624 <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
4626 <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
4630 Specifies the cost delay value that will be used in automatic
4631 <command>VACUUM</> operations. If -1 is specified, the regular
4632 <xref linkend="guc-vacuum-cost-delay"> value will be used.
4633 The default value is 20 milliseconds.
4634 This parameter can only be set in the <filename>postgresql.conf</>
4635 file or on the server command line.
4636 This setting can be overridden for individual tables by
4637 changing storage parameters.
4642 <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
4643 <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
4645 <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
4649 Specifies the cost limit value that will be used in automatic
4650 <command>VACUUM</> operations. If -1 is specified (which is the
4651 default), the regular
4652 <xref linkend="guc-vacuum-cost-limit"> value will be used. Note that
4653 the value is distributed proportionally among the running autovacuum
4654 workers, if there is more than one, so that the sum of the limits of
4655 each worker never exceeds the limit on this variable.
4656 This parameter can only be set in the <filename>postgresql.conf</>
4657 file or on the server command line.
4658 This setting can be overridden for individual tables by
4659 changing storage parameters.
4667 <sect1 id="runtime-config-client">
4668 <title>Client Connection Defaults</title>
4670 <sect2 id="runtime-config-client-statement">
4671 <title>Statement Behavior</title>
4674 <varlistentry id="guc-search-path" xreflabel="search_path">
4675 <term><varname>search_path</varname> (<type>string</type>)</term>
4677 <primary><varname>search_path</> configuration parameter</primary>
4679 <indexterm><primary>path</><secondary>for schemas</></>
4682 This variable specifies the order in which schemas are searched
4683 when an object (table, data type, function, etc.) is referenced by a
4684 simple name with no schema specified. When there are objects of
4685 identical names in different schemas, the one found first
4686 in the search path is used. An object that is not in any of the
4687 schemas in the search path can only be referenced by specifying
4688 its containing schema with a qualified (dotted) name.
4692 The value for <varname>search_path</varname> must be a comma-separated
4693 list of schema names. Any name that is not an existing schema, or is
4694 a schema for which the user does not have <literal>USAGE</>
4695 permission, is silently ignored.
4699 If one of the list items is the special name
4700 <literal>$user</literal>, then the schema having the name returned by
4701 <function>SESSION_USER</> is substituted, if there is such a schema
4702 and the user has <literal>USAGE</> permission for it.
4703 (If not, <literal>$user</literal> is ignored.)
4707 The system catalog schema, <literal>pg_catalog</>, is always
4708 searched, whether it is mentioned in the path or not. If it is
4709 mentioned in the path then it will be searched in the specified
4710 order. If <literal>pg_catalog</> is not in the path then it will
4711 be searched <emphasis>before</> searching any of the path items.
4715 Likewise, the current session's temporary-table schema,
4716 <literal>pg_temp_<replaceable>nnn</></>, is always searched if it
4717 exists. It can be explicitly listed in the path by using the
4718 alias <literal>pg_temp</>. If it is not listed in the path then
4719 it is searched first (even before <literal>pg_catalog</>). However,
4720 the temporary schema is only searched for relation (table, view,
4721 sequence, etc) and data type names. It is never searched for
4722 function or operator names.
4726 When objects are created without specifying a particular target
4727 schema, they will be placed in the first valid schema named in
4728 <varname>search_path</varname>. An error is reported if the search
4733 The default value for this parameter is
4734 <literal>"$user", public</literal>.
4735 This setting supports shared use of a database (where no users
4736 have private schemas, and all share use of <literal>public</>),
4737 private per-user schemas, and combinations of these. Other
4738 effects can be obtained by altering the default search path
4739 setting, either globally or per-user.
4743 The current effective value of the search path can be examined
4744 via the <acronym>SQL</acronym> function
4745 <function>current_schemas</>
4746 (see <xref linkend="functions-info">).
4747 This is not quite the same as
4748 examining the value of <varname>search_path</varname>, since
4749 <function>current_schemas</> shows how the items
4750 appearing in <varname>search_path</varname> were resolved.
4754 For more information on schema handling, see <xref linkend="ddl-schemas">.
4759 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
4760 <term><varname>default_tablespace</varname> (<type>string</type>)</term>
4762 <primary><varname>default_tablespace</> configuration parameter</primary>
4764 <indexterm><primary>tablespace</><secondary>default</></>
4767 This variable specifies the default tablespace in which to create
4768 objects (tables and indexes) when a <command>CREATE</> command does
4769 not explicitly specify a tablespace.
4773 The value is either the name of a tablespace, or an empty string
4774 to specify using the default tablespace of the current database.
4775 If the value does not match the name of any existing tablespace,
4776 <productname>PostgreSQL</> will automatically use the default
4777 tablespace of the current database. If a nondefault tablespace
4778 is specified, the user must have <literal>CREATE</> privilege
4779 for it, or creation attempts will fail.
4783 This variable is not used for temporary tables; for them,
4784 <xref linkend="guc-temp-tablespaces"> is consulted instead.
4788 This variable is also not used when creating databases.
4789 By default, a new database inherits its tablespace setting from
4790 the template database it is copied from.
4794 For more information on tablespaces,
4795 see <xref linkend="manage-ag-tablespaces">.
4800 <varlistentry id="guc-temp-tablespaces" xreflabel="temp_tablespaces">
4801 <term><varname>temp_tablespaces</varname> (<type>string</type>)</term>
4803 <primary><varname>temp_tablespaces</> configuration parameter</primary>
4805 <indexterm><primary>tablespace</><secondary>temporary</></>
4808 This variable specifies tablespaces in which to create temporary
4809 objects (temp tables and indexes on temp tables) when a
4810 <command>CREATE</> command does not explicitly specify a tablespace.
4811 Temporary files for purposes such as sorting large data sets
4812 are also created in these tablespaces.
4816 The value is a list of names of tablespaces. When there is more than
4817 one name in the list, <productname>PostgreSQL</> chooses a random
4818 member of the list each time a temporary object is to be created;
4819 except that within a transaction, successively created temporary
4820 objects are placed in successive tablespaces from the list.
4821 If the selected element of the list is an empty string,
4822 <productname>PostgreSQL</> will automatically use the default
4823 tablespace of the current database instead.
4827 When <varname>temp_tablespaces</> is set interactively, specifying a
4828 nonexistent tablespace is an error, as is specifying a tablespace for
4829 which the user does not have <literal>CREATE</> privilege. However,
4830 when using a previously set value, nonexistent tablespaces are
4831 ignored, as are tablespaces for which the user lacks
4832 <literal>CREATE</> privilege. In particular, this rule applies when
4833 using a value set in <filename>postgresql.conf</>.
4837 The default value is an empty string, which results in all temporary
4838 objects being created in the default tablespace of the current
4843 See also <xref linkend="guc-default-tablespace">.
4848 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
4849 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
4851 <primary><varname>check_function_bodies</> configuration parameter</primary>
4855 This parameter is normally on. When set to <literal>off</>, it
4856 disables validation of the function body string during <xref
4857 linkend="sql-createfunction">. Disabling validation is
4858 occasionally useful to avoid problems such as forward references
4859 when restoring function definitions from a dump.
4864 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
4866 <primary>transaction isolation level</primary>
4867 <secondary>setting default</secondary>
4870 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
4872 <term><varname>default_transaction_isolation</varname> (<type>enum</type>)</term>
4875 Each SQL transaction has an isolation level, which can be
4876 either <quote>read uncommitted</quote>, <quote>read
4877 committed</quote>, <quote>repeatable read</quote>, or
4878 <quote>serializable</quote>. This parameter controls the
4879 default isolation level of each new transaction. The default
4880 is <quote>read committed</quote>.
4884 Consult <xref linkend="mvcc"> and <xref
4885 linkend="sql-set-transaction"> for more information.
4890 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
4892 <primary>read-only transaction</primary>
4893 <secondary>setting default</secondary>
4896 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
4899 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
4902 A read-only SQL transaction cannot alter non-temporary tables.
4903 This parameter controls the default read-only status of each new
4904 transaction. The default is <literal>off</> (read/write).
4908 Consult <xref linkend="sql-set-transaction"> for more information.
4913 <varlistentry id="guc-default-transaction-deferrable" xreflabel="default_transaction_deferrable">
4915 <primary>deferrable transaction</primary>
4916 <secondary>setting default</secondary>
4919 <primary><varname>default_transaction_deferrable</> configuration parameter</primary>
4922 <term><varname>default_transaction_deferrable</varname> (<type>boolean</type>)</term>
4925 When running at the <literal>serializable</> isolation level,
4926 a deferrable read-only SQL transaction may be delayed before
4927 it is allowed to proceed. However, once it begins executing
4928 it does not incur any of the overhead required to ensure
4929 serializability; so serialization code will have no reason to
4930 force it to abort because of concurrent updates, making this
4931 option suitable for long-running read-only transactions.
4935 This parameter controls the default deferrable status of each
4936 new transaction. It currently has no effect on read-write
4937 transactions or those operating at isolation levels lower
4938 than <literal>serializable</>. The default is <literal>off</>.
4942 Consult <xref linkend="sql-set-transaction"> for more information.
4948 <varlistentry id="guc-session-replication-role" xreflabel="session_replication_role">
4949 <term><varname>session_replication_role</varname> (<type>enum</type>)</term>
4951 <primary><varname>session_replication_role</> configuration parameter</primary>
4955 Controls firing of replication-related triggers and rules for the
4956 current session. Setting this variable requires
4957 superuser privilege and results in discarding any previously cached
4958 query plans. Possible values are <literal>origin</> (the default),
4959 <literal>replica</> and <literal>local</>.
4960 See <xref linkend="sql-altertable"> for
4966 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
4967 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
4969 <primary><varname>statement_timeout</> configuration parameter</primary>
4973 Abort any statement that takes over the specified number of
4974 milliseconds, starting from the time the command arrives at the server
4975 from the client. If <varname>log_min_error_statement</> is set to
4976 <literal>ERROR</> or lower, the statement that timed out will also be
4977 logged. A value of zero (the default) turns this off.
4981 Setting <varname>statement_timeout</> in
4982 <filename>postgresql.conf</> is not recommended because it
4983 affects all sessions.
4988 <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
4989 <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)</term>
4991 <primary><varname>vacuum_freeze_table_age</> configuration parameter</primary>
4995 <command>VACUUM</> performs a whole-table scan if the table's
4996 <structname>pg_class</>.<structfield>relfrozenxid</> field has reached
4997 the age specified by this setting. The default is 150 million
4998 transactions. Although users can set this value anywhere from zero to
4999 one billion, <command>VACUUM</> will silently limit the effective value
5000 to 95% of <xref linkend="guc-autovacuum-freeze-max-age">, so that a
5001 periodical manual <command>VACUUM</> has a chance to run before an
5002 anti-wraparound autovacuum is launched for the table. For more
5004 <xref linkend="vacuum-for-wraparound">.
5009 <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
5010 <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)</term>
5012 <primary><varname>vacuum_freeze_min_age</> configuration parameter</primary>
5016 Specifies the cutoff age (in transactions) that <command>VACUUM</>
5017 should use to decide whether to replace transaction IDs with
5018 <literal>FrozenXID</> while scanning a table.
5019 The default is 50 million transactions. Although
5020 users can set this value anywhere from zero to one billion,
5021 <command>VACUUM</> will silently limit the effective value to half
5022 the value of <xref linkend="guc-autovacuum-freeze-max-age">, so
5023 that there is not an unreasonably short time between forced
5024 autovacuums. For more information see <xref
5025 linkend="vacuum-for-wraparound">.
5030 <varlistentry id="guc-bytea-output" xreflabel="bytea_output">
5031 <term><varname>bytea_output</varname> (<type>enum</type>)</term>
5033 <primary><varname>bytea_output</> configuration parameter</primary>
5037 Sets the output format for values of type <type>bytea</type>.
5038 Valid values are <literal>hex</literal> (the default)
5039 and <literal>escape</literal> (the traditional PostgreSQL
5040 format). See <xref linkend="datatype-binary"> for more
5041 information. The <type>bytea</type> type always
5042 accepts both formats on input, regardless of this setting.
5047 <varlistentry id="guc-xmlbinary" xreflabel="xmlbinary">
5048 <term><varname>xmlbinary</varname> (<type>enum</type>)</term>
5050 <primary><varname>xmlbinary</> configuration parameter</primary>
5054 Sets how binary values are to be encoded in XML. This applies
5055 for example when <type>bytea</type> values are converted to
5056 XML by the functions <function>xmlelement</function> or
5057 <function>xmlforest</function>. Possible values are
5058 <literal>base64</literal> and <literal>hex</literal>, which
5059 are both defined in the XML Schema standard. The default is
5060 <literal>base64</literal>. For further information about
5061 XML-related functions, see <xref linkend="functions-xml">.
5065 The actual choice here is mostly a matter of taste,
5066 constrained only by possible restrictions in client
5067 applications. Both methods support all possible values,
5068 although the hex encoding will be somewhat larger than the
5074 <varlistentry id="guc-xmloption" xreflabel="xmloption">
5075 <term><varname>xmloption</varname> (<type>enum</type>)</term>
5077 <primary><varname>xmloption</> configuration parameter</primary>
5080 <primary><varname>SET XML OPTION</></primary>
5083 <primary>XML option</primary>
5087 Sets whether <literal>DOCUMENT</literal> or
5088 <literal>CONTENT</literal> is implicit when converting between
5089 XML and character string values. See <xref
5090 linkend="datatype-xml"> for a description of this. Valid
5091 values are <literal>DOCUMENT</literal> and
5092 <literal>CONTENT</literal>. The default is
5093 <literal>CONTENT</literal>.
5097 According to the SQL standard, the command to set this option is
5099 SET XML OPTION { DOCUMENT | CONTENT };
5101 This syntax is also available in PostgreSQL.
5108 <sect2 id="runtime-config-client-format">
5109 <title>Locale and Formatting</title>
5113 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
5114 <term><varname>DateStyle</varname> (<type>string</type>)</term>
5116 <primary><varname>DateStyle</> configuration parameter</primary>
5120 Sets the display format for date and time values, as well as the
5121 rules for interpreting ambiguous date input values. For
5122 historical reasons, this variable contains two independent
5123 components: the output format specification (<literal>ISO</>,
5124 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
5125 and the input/output specification for year/month/day ordering
5126 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
5127 can be set separately or together. The keywords <literal>Euro</>
5128 and <literal>European</> are synonyms for <literal>DMY</>; the
5129 keywords <literal>US</>, <literal>NonEuro</>, and
5130 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
5131 <xref linkend="datatype-datetime"> for more information. The
5132 built-in default is <literal>ISO, MDY</>, but
5133 <application>initdb</application> will initialize the
5134 configuration file with a setting that corresponds to the
5135 behavior of the chosen <varname>lc_time</varname> locale.
5140 <varlistentry id="guc-intervalstyle" xreflabel="IntervalStyle">
5141 <term><varname>IntervalStyle</varname> (<type>enum</type>)</term>
5143 <primary><varname>IntervalStyle</> configuration parameter</primary>
5147 Sets the display format for interval values.
5148 The value <literal>sql_standard</> will produce
5149 output matching <acronym>SQL</acronym> standard interval literals.
5150 The value <literal>postgres</> (which is the default) will produce
5151 output matching <productname>PostgreSQL</> releases prior to 8.4
5152 when the <xref linkend="guc-datestyle">
5153 parameter was set to <literal>ISO</>.
5154 The value <literal>postgres_verbose</> will produce output
5155 matching <productname>PostgreSQL</> releases prior to 8.4
5156 when the <varname>DateStyle</>
5157 parameter was set to non-<literal>ISO</> output.
5158 The value <literal>iso_8601</> will produce output matching the time
5159 interval <quote>format with designators</> defined in section
5160 4.4.3.2 of ISO 8601.
5163 The <varname>IntervalStyle</> parameter also affects the
5164 interpretation of ambiguous interval input. See
5165 <xref linkend="datatype-interval-input"> for more information.
5170 <varlistentry id="guc-timezone" xreflabel="TimeZone">
5171 <term><varname>TimeZone</varname> (<type>string</type>)</term>
5173 <primary><varname>TimeZone</> configuration parameter</primary>
5175 <indexterm><primary>time zone</></>
5178 Sets the time zone for displaying and interpreting time stamps.
5179 The built-in default is <literal>GMT</>, but that is typically
5180 overridden in <filename>postgresql.conf</>; <application>initdb</>
5181 will install a setting there corresponding to its system environment.
5182 See <xref linkend="datatype-timezones"> for more information.
5187 <varlistentry id="guc-timezone-abbreviations" xreflabel="timezone_abbreviations">
5188 <term><varname>timezone_abbreviations</varname> (<type>string</type>)</term>
5190 <primary><varname>timezone_abbreviations</> configuration parameter</primary>
5192 <indexterm><primary>time zone names</></>
5195 Sets the collection of time zone abbreviations that will be accepted
5196 by the server for datetime input. The default is <literal>'Default'</>,
5197 which is a collection that works in most of the world; there are
5198 also <literal>'Australia'</literal> and <literal>'India'</literal>, and other collections can be defined
5199 for a particular installation. See <xref
5200 linkend="datetime-appendix"> for more information.
5205 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
5207 <primary>significant digits</primary>
5210 <primary>floating-point</primary>
5211 <secondary>display</secondary>
5214 <primary><varname>extra_float_digits</> configuration parameter</primary>
5217 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
5220 This parameter adjusts the number of digits displayed for
5221 floating-point values, including <type>float4</>, <type>float8</>,
5222 and geometric data types. The parameter value is added to the
5223 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
5224 as appropriate). The value can be set as high as 3, to include
5225 partially-significant digits; this is especially useful for dumping
5226 float data that needs to be restored exactly. Or it can be set
5227 negative to suppress unwanted digits.
5232 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
5233 <term><varname>client_encoding</varname> (<type>string</type>)</term>
5235 <primary><varname>client_encoding</> configuration parameter</primary>
5237 <indexterm><primary>character set</></>
5240 Sets the client-side encoding (character set).
5241 The default is to use the database encoding.
5242 The character sets supported by the <productname>PostgreSQL</productname>
5243 server are described in <xref linkend="multibyte-charset-supported">.
5248 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
5249 <term><varname>lc_messages</varname> (<type>string</type>)</term>
5251 <primary><varname>lc_messages</> configuration parameter</primary>
5255 Sets the language in which messages are displayed. Acceptable
5256 values are system-dependent; see <xref linkend="locale"> for
5257 more information. If this variable is set to the empty string
5258 (which is the default) then the value is inherited from the
5259 execution environment of the server in a system-dependent way.
5263 On some systems, this locale category does not exist. Setting
5264 this variable will still work, but there will be no effect.
5265 Also, there is a chance that no translated messages for the
5266 desired language exist. In that case you will continue to see
5267 the English messages.
5271 Only superusers can change this setting, because it affects the
5272 messages sent to the server log as well as to the client, and
5273 an improper value might obscure the readability of the server
5279 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
5280 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
5282 <primary><varname>lc_monetary</> configuration parameter</primary>
5286 Sets the locale to use for formatting monetary amounts, for
5287 example with the <function>to_char</function> family of
5288 functions. Acceptable values are system-dependent; see <xref
5289 linkend="locale"> for more information. If this variable is
5290 set to the empty string (which is the default) then the value
5291 is inherited from the execution environment of the server in a
5292 system-dependent way.
5297 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
5298 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
5300 <primary><varname>lc_numeric</> configuration parameter</primary>
5304 Sets the locale to use for formatting numbers, for example
5305 with the <function>to_char</function> family of
5306 functions. Acceptable values are system-dependent; see <xref
5307 linkend="locale"> for more information. If this variable is
5308 set to the empty string (which is the default) then the value
5309 is inherited from the execution environment of the server in a
5310 system-dependent way.
5315 <varlistentry id="guc-lc-time" xreflabel="lc_time">
5316 <term><varname>lc_time</varname> (<type>string</type>)</term>
5318 <primary><varname>lc_time</> configuration parameter</primary>
5322 Sets the locale to use for formatting dates and times, for example
5323 with the <function>to_char</function> family of
5324 functions. Acceptable values are system-dependent; see <xref
5325 linkend="locale"> for more information. If this variable is
5326 set to the empty string (which is the default) then the value
5327 is inherited from the execution environment of the server in a
5328 system-dependent way.
5333 <varlistentry id="guc-default-text-search-config" xreflabel="default_text_search_config">
5334 <term><varname>default_text_search_config</varname> (<type>string</type>)</term>
5336 <primary><varname>default_text_search_config</> configuration parameter</primary>
5340 Selects the text search configuration that is used by those variants
5341 of the text search functions that do not have an explicit argument
5342 specifying the configuration.
5343 See <xref linkend="textsearch"> for further information.
5344 The built-in default is <literal>pg_catalog.simple</>, but
5345 <application>initdb</application> will initialize the
5346 configuration file with a setting that corresponds to the
5347 chosen <varname>lc_ctype</varname> locale, if a configuration
5348 matching that locale can be identified.
5356 <sect2 id="runtime-config-client-other">
5357 <title>Other Defaults</title>
5361 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
5362 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
5364 <primary><varname>dynamic_library_path</> configuration parameter</primary>
5366 <indexterm><primary>dynamic loading</></>
5369 If a dynamically loadable module needs to be opened and the
5370 file name specified in the <command>CREATE FUNCTION</command> or
5371 <command>LOAD</command> command
5372 does not have a directory component (i.e., the
5373 name does not contain a slash), the system will search this
5374 path for the required file.
5378 The value for <varname>dynamic_library_path</varname> must be a
5379 list of absolute directory paths separated by colons (or semi-colons
5380 on Windows). If a list element starts
5381 with the special string <literal>$libdir</literal>, the
5382 compiled-in <productname>PostgreSQL</productname> package
5383 library directory is substituted for <literal>$libdir</literal>; this
5384 is where the modules provided by the standard
5385 <productname>PostgreSQL</productname> distribution are installed.
5386 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
5387 this directory.) For example:
5389 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
5391 or, in a Windows environment:
5393 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
5398 The default value for this parameter is
5399 <literal>'$libdir'</literal>. If the value is set to an empty
5400 string, the automatic path search is turned off.
5404 This parameter can be changed at run time by superusers, but a
5405 setting done that way will only persist until the end of the
5406 client connection, so this method should be reserved for
5407 development purposes. The recommended way to set this parameter
5408 is in the <filename>postgresql.conf</filename> configuration
5414 <varlistentry id="guc-gin-fuzzy-search-limit" xreflabel="gin_fuzzy_search_limit">
5415 <term><varname>gin_fuzzy_search_limit</varname> (<type>integer</type>)</term>
5417 <primary><varname>gin_fuzzy_search_limit</> configuration parameter</primary>
5421 Soft upper limit of the size of the set returned by GIN index scans. For more
5422 information see <xref linkend="gin-tips">.
5427 <varlistentry id="guc-local-preload-libraries" xreflabel="local_preload_libraries">
5428 <term><varname>local_preload_libraries</varname> (<type>string</type>)</term>
5430 <primary><varname>local_preload_libraries</> configuration parameter</primary>
5433 <primary><filename>$libdir/plugins</></primary>
5437 This variable specifies one or more shared libraries that are
5438 to be preloaded at connection start. If more than one library
5439 is to be loaded, separate their names with commas. All library
5440 names are converted to lower case unless double-quoted.
5441 This parameter cannot be changed after the start of a particular
5446 Because this is not a superuser-only option, the libraries
5447 that can be loaded are restricted to those appearing in the
5448 <filename>plugins</> subdirectory of the installation's
5449 standard library directory. (It is the database administrator's
5450 responsibility to ensure that only <quote>safe</> libraries
5451 are installed there.) Entries in <varname>local_preload_libraries</>
5452 can specify this directory explicitly, for example
5453 <literal>$libdir/plugins/mylib</literal>, or just specify
5454 the library name — <literal>mylib</literal> would have
5455 the same effect as <literal>$libdir/plugins/mylib</literal>.
5459 Unlike <xref linkend="guc-shared-preload-libraries">, there is no
5460 performance advantage to loading a library at session
5461 start rather than when it is first used. Rather, the intent of
5462 this feature is to allow debugging or performance-measurement
5463 libraries to be loaded into specific sessions without an explicit
5464 <command>LOAD</> command being given. For example, debugging could
5465 be enabled for all sessions under a given user name by setting
5466 this parameter with <command>ALTER ROLE SET</>.
5470 If a specified library is not found,
5471 the connection attempt will fail.
5475 Every PostgreSQL-supported library has a <quote>magic
5476 block</> that is checked to guarantee compatibility.
5477 For this reason, non-PostgreSQL libraries cannot be
5487 <sect1 id="runtime-config-locks">
5488 <title>Lock Management</title>
5492 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
5494 <primary>deadlock</primary>
5495 <secondary>timeout during</secondary>
5498 <primary>timeout</primary>
5499 <secondary>deadlock</secondary>
5502 <primary><varname>deadlock_timeout</> configuration parameter</primary>
5505 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
5508 This is the amount of time, in milliseconds, to wait on a lock
5509 before checking to see if there is a deadlock condition. The
5510 check for deadlock is relatively expensive, so the server doesn't run
5511 it every time it waits for a lock. We optimistically assume
5512 that deadlocks are not common in production applications and
5513 just wait on the lock for a while before checking for a
5514 deadlock. Increasing this value reduces the amount of time
5515 wasted in needless deadlock checks, but slows down reporting of
5516 real deadlock errors. The default is one second (<literal>1s</>),
5517 which is probably about the smallest value you would want in
5518 practice. On a heavily loaded server you might want to raise it.
5519 Ideally the setting should exceed your typical transaction time,
5520 so as to improve the odds that a lock will be released before
5521 the waiter decides to check for deadlock. Only superusers can change
5526 When <xref linkend="guc-log-lock-waits"> is set,
5527 this parameter also determines the length of time to wait before
5528 a log message is issued about the lock wait. If you are trying
5529 to investigate locking delays you might want to set a shorter than
5530 normal <varname>deadlock_timeout</varname>.
5535 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
5536 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
5538 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
5542 The shared lock table tracks locks on
5543 <varname>max_locks_per_transaction</varname> * (<xref
5544 linkend="guc-max-connections"> + <xref
5545 linkend="guc-max-prepared-transactions">) objects (e.g., tables);
5546 hence, no more than this many distinct objects can be locked at
5547 any one time. This parameter controls the average number of object
5548 locks allocated for each transaction; individual transactions
5549 can lock more objects as long as the locks of all transactions
5550 fit in the lock table. This is <emphasis>not</> the number of
5551 rows that can be locked; that value is unlimited. The default,
5552 64, has historically proven sufficient, but you might need to
5553 raise this value if you have clients that touch many different
5554 tables in a single transaction. This parameter can only be set at
5559 When running a standby server, you must set this parameter to the
5560 same or higher value than on the master server. Otherwise, queries
5561 will not be allowed in the standby server.
5566 <varlistentry id="guc-max-pred-locks-per-transaction" xreflabel="max_pred_locks_per_transaction">
5567 <term><varname>max_pred_locks_per_transaction</varname> (<type>integer</type>)</term>
5569 <primary><varname>max_pred_locks_per_transaction</> configuration parameter</primary>
5573 The shared predicate lock table tracks locks on
5574 <varname>max_pred_locks_per_transaction</varname> * (<xref
5575 linkend="guc-max-connections"> + <xref
5576 linkend="guc-max-prepared-transactions">) objects (e.g., tables);
5577 hence, no more than this many distinct objects can be locked at
5578 any one time. This parameter controls the average number of object
5579 locks allocated for each transaction; individual transactions
5580 can lock more objects as long as the locks of all transactions
5581 fit in the lock table. This is <emphasis>not</> the number of
5582 rows that can be locked; that value is unlimited. The default,
5583 64, has generally been sufficient in testing, but you might need to
5584 raise this value if you have clients that touch many different
5585 tables in a single serializable transaction. This parameter can
5586 only be set at server start.
5595 <sect1 id="runtime-config-compatible">
5596 <title>Version and Platform Compatibility</title>
5598 <sect2 id="runtime-config-compatible-version">
5599 <title>Previous PostgreSQL Versions</title>
5603 <varlistentry id="guc-array-nulls" xreflabel="array_nulls">
5604 <term><varname>array_nulls</varname> (<type>boolean</type>)</term>
5606 <primary><varname>array_nulls</> configuration parameter</primary>
5610 This controls whether the array input parser recognizes
5611 unquoted <literal>NULL</> as specifying a null array element.
5612 By default, this is <literal>on</>, allowing array values containing
5613 null values to be entered. However, <productname>PostgreSQL</> versions
5614 before 8.2 did not support null values in arrays, and therefore would
5615 treat <literal>NULL</> as specifying a normal array element with
5616 the string value <quote>NULL</>. For backward compatibility with
5617 applications that require the old behavior, this variable can be
5618 turned <literal>off</>.
5622 Note that it is possible to create array values containing null values
5623 even when this variable is <literal>off</>.
5628 <varlistentry id="guc-backslash-quote" xreflabel="backslash_quote">
5629 <term><varname>backslash_quote</varname> (<type>enum</type>)</term>
5630 <indexterm><primary>strings</><secondary>backslash quotes</></>
5632 <primary><varname>backslash_quote</> configuration parameter</primary>
5636 This controls whether a quote mark can be represented by
5637 <literal>\'</> in a string literal. The preferred, SQL-standard way
5638 to represent a quote mark is by doubling it (<literal>''</>) but
5639 <productname>PostgreSQL</> has historically also accepted
5640 <literal>\'</>. However, use of <literal>\'</> creates security risks
5641 because in some client character set encodings, there are multibyte
5642 characters in which the last byte is numerically equivalent to ASCII
5643 <literal>\</>. If client-side code does escaping incorrectly then a
5644 SQL-injection attack is possible. This risk can be prevented by
5645 making the server reject queries in which a quote mark appears to be
5646 escaped by a backslash.
5647 The allowed values of <varname>backslash_quote</> are
5648 <literal>on</> (allow <literal>\'</> always),
5649 <literal>off</> (reject always), and
5650 <literal>safe_encoding</> (allow only if client encoding does not
5651 allow ASCII <literal>\</> within a multibyte character).
5652 <literal>safe_encoding</> is the default setting.
5656 Note that in a standard-conforming string literal, <literal>\</> just
5657 means <literal>\</> anyway. This parameter only affects the handling of
5658 non-standard-conforming literals, including
5659 escape string syntax (<literal>E'...'</>).
5664 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
5665 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
5667 <primary><varname>default_with_oids</> configuration parameter</primary>
5671 This controls whether <command>CREATE TABLE</command> and
5672 <command>CREATE TABLE AS</command> include an OID column in
5673 newly-created tables, if neither <literal>WITH OIDS</literal>
5674 nor <literal>WITHOUT OIDS</literal> is specified. It also
5675 determines whether OIDs will be included in tables created by
5676 <command>SELECT INTO</command>. The parameter is <literal>off</>
5677 by default; in <productname>PostgreSQL</> 8.0 and earlier, it
5682 The use of OIDs in user tables is considered deprecated, so
5683 most installations should leave this variable disabled.
5684 Applications that require OIDs for a particular table should
5685 specify <literal>WITH OIDS</literal> when creating the
5686 table. This variable can be enabled for compatibility with old
5687 applications that do not follow this behavior.
5692 <varlistentry id="guc-escape-string-warning" xreflabel="escape_string_warning">
5693 <term><varname>escape_string_warning</varname> (<type>boolean</type>)</term>
5694 <indexterm><primary>strings</><secondary>escape warning</></>
5696 <primary><varname>escape_string_warning</> configuration parameter</primary>
5700 When on, a warning is issued if a backslash (<literal>\</>)
5701 appears in an ordinary string literal (<literal>'...'</>
5702 syntax) and <varname>standard_conforming_strings</varname> is off.
5703 The default is <literal>on</>.
5706 Applications that wish to use backslash as escape should be
5707 modified to use escape string syntax (<literal>E'...'</>),
5708 because the default behavior of ordinary strings is now to treat
5709 backslash as an ordinary character, per SQL standard. This variable
5710 can be enabled to help locate code that needs to be changed.
5715 <varlistentry id="guc-lo-compat-privileges" xreflabel="lo_compat_privileges">
5716 <term><varname>lo_compat_privileges</varname> (<type>boolean</type>)</term>
5718 <primary><varname>lo_compat_privileges</> configuration parameter</primary>
5722 In <productname>PostgreSQL</> releases prior to 9.0, large objects
5723 did not have access privileges and were, in effect, readable and
5724 writable by all users. Setting this variable to <literal>on</>
5725 disables the new privilege checks, for compatibility with prior
5726 releases. The default is <literal>off</>.
5729 Setting this variable does not disable all security checks related to
5730 large objects — only those for which the default behavior has
5731 changed in <productname>PostgreSQL</> 9.0.
5732 For example, <literal>lo_import()</literal> and
5733 <literal>lo_export()</literal> need superuser privileges independent
5739 <varlistentry id="guc-quote-all-identifiers" xreflabel="quote-all-identifiers">
5740 <term><varname>quote_all_identifiers</varname> (<type>boolean</type>)</term>
5742 <primary><varname>quote_all_identifiers</> configuration parameter</primary>
5746 When the database generates SQL, force all identifiers to be quoted,
5747 even if they are not (currently) keywords. This will affect the
5748 output of <command>EXPLAIN</> as well as the results of functions
5749 like <function>pg_get_viewdef</>. See also the
5750 <option>--quote-all-identifiers</option> option of
5751 <xref linkend="app-pgdump"> and <xref linkend="app-pg-dumpall">.
5756 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
5757 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
5759 <primary><varname>sql_inheritance</> configuration parameter</primary>
5761 <indexterm><primary>inheritance</></>
5764 This controls the inheritance semantics. If turned <literal>off</>,
5765 subtables are not accessed by various commands by default; basically
5766 an implied <literal>ONLY</literal> key word. This was added for
5767 compatibility with releases prior to 7.1. See
5768 <xref linkend="ddl-inherit"> for more information.
5773 <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
5774 <term><varname>standard_conforming_strings</varname> (<type>boolean</type>)</term>
5775 <indexterm><primary>strings</><secondary>standard conforming</></>
5777 <primary><varname>standard_conforming_strings</> configuration parameter</primary>
5781 This controls whether ordinary string literals
5782 (<literal>'...'</>) treat backslashes literally, as specified in
5784 Beginning in <productname>PostgreSQL</productname> 9.1, the default is
5785 <literal>on</> (prior releases defaulted to <literal>off</>).
5786 Applications can check this
5787 parameter to determine how string literals will be processed.
5788 The presence of this parameter can also be taken as an indication
5789 that the escape string syntax (<literal>E'...'</>) is supported.
5790 Escape string syntax (<xref linkend="sql-syntax-strings-escape">)
5791 should be used if an application desires
5792 backslashes to be treated as escape characters.
5797 <varlistentry id="guc-synchronize-seqscans" xreflabel="synchronize_seqscans">
5798 <term><varname>synchronize_seqscans</varname> (<type>boolean</type>)</term>
5800 <primary><varname>synchronize_seqscans</> configuration parameter</primary>
5804 This allows sequential scans of large tables to synchronize with each
5805 other, so that concurrent scans read the same block at about the
5806 same time and hence share the I/O workload. When this is enabled,
5807 a scan might start in the middle of the table and then <quote>wrap
5808 around</> the end to cover all rows, so as to synchronize with the
5809 activity of scans already in progress. This can result in
5810 unpredictable changes in the row ordering returned by queries that
5811 have no <literal>ORDER BY</> clause. Setting this parameter to
5812 <literal>off</> ensures the pre-8.3 behavior in which a sequential
5813 scan always starts from the beginning of the table. The default
5822 <sect2 id="runtime-config-compatible-clients">
5823 <title>Platform and Client Compatibility</title>
5826 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
5827 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
5828 <indexterm><primary>IS NULL</></>
5830 <primary><varname>transform_null_equals</> configuration parameter</primary>
5834 When on, expressions of the form <literal><replaceable>expr</> =
5835 NULL</literal> (or <literal>NULL =
5836 <replaceable>expr</></literal>) are treated as
5837 <literal><replaceable>expr</> IS NULL</literal>, that is, they
5838 return true if <replaceable>expr</> evaluates to the null value,
5839 and false otherwise. The correct SQL-spec-compliant behavior of
5840 <literal><replaceable>expr</> = NULL</literal> is to always
5841 return null (unknown). Therefore this parameter defaults to
5846 However, filtered forms in <productname>Microsoft
5847 Access</productname> generate queries that appear to use
5848 <literal><replaceable>expr</> = NULL</literal> to test for
5849 null values, so if you use that interface to access the database you
5850 might want to turn this option on. Since expressions of the
5851 form <literal><replaceable>expr</> = NULL</literal> always
5852 return the null value (using the SQL standard interpretation), they are not
5853 very useful and do not appear often in normal applications so
5854 this option does little harm in practice. But new users are
5855 frequently confused about the semantics of expressions
5856 involving null values, so this option is off by default.
5860 Note that this option only affects the exact form <literal>= NULL</>,
5861 not other comparison operators or other expressions
5862 that are computationally equivalent to some expression
5863 involving the equals operator (such as <literal>IN</literal>).
5864 Thus, this option is not a general fix for bad programming.
5868 Refer to <xref linkend="functions-comparison"> for related information.
5877 <sect1 id="runtime-config-error-handling">
5878 <title>Error Handling</title>
5882 <varlistentry id="guc-exit-on-error" xreflabel="exit_on_error">
5883 <term><varname>exit_on_error</varname> (<type>boolean</type>)</term>
5885 <primary><varname>exit_on_error</> configuration parameter</primary>
5889 If true, any error will terminate the current session. By default,
5890 this is set to false, so that only FATAL errors will terminate the
5896 <varlistentry id="guc-restart-after-crash" xreflabel="restart_after_crash">
5897 <term><varname>restart_after_crash</varname> (<type>boolean</type>)</term>
5899 <primary><varname>restart_after_crash</> configuration parameter</primary>
5903 When set to true, which is the default, <productname>PostgreSQL</>
5904 will automatically reinitialize after a backend crash. Leaving this
5905 value set to true is normally the best way to maximize the availability
5906 of the database. However, in some circumstances, such as when
5907 <productname>PostgreSQL</> is being invoked by clusterware, it may be
5908 useful to disable the restart so that the clusterware can gain
5909 control and take any actions it deems appropriate.
5918 <sect1 id="runtime-config-preset">
5919 <title>Preset Options</title>
5922 The following <quote>parameters</> are read-only, and are determined
5923 when <productname>PostgreSQL</productname> is compiled or when it is
5924 installed. As such, they have been excluded from the sample
5925 <filename>postgresql.conf</> file. These options report
5926 various aspects of <productname>PostgreSQL</productname> behavior
5927 that might be of interest to certain applications, particularly
5928 administrative front-ends.
5933 <varlistentry id="guc-block-size" xreflabel="block_size">
5934 <term><varname>block_size</varname> (<type>integer</type>)</term>
5936 <primary><varname>block_size</> configuration parameter</primary>
5940 Reports the size of a disk block. It is determined by the value
5941 of <literal>BLCKSZ</> when building the server. The default
5942 value is 8192 bytes. The meaning of some configuration
5943 variables (such as <xref linkend="guc-shared-buffers">) is
5944 influenced by <varname>block_size</varname>. See <xref
5945 linkend="runtime-config-resource"> for information.
5950 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
5951 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
5953 <primary><varname>integer_datetimes</> configuration parameter</primary>
5957 Reports whether <productname>PostgreSQL</> was built with
5958 support for 64-bit-integer dates and times. This can be
5959 disabled by configuring with <literal>--disable-integer-datetimes</>
5960 when building <productname>PostgreSQL</>. The default value is
5961 <literal>on</literal>.
5966 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
5967 <term><varname>lc_collate</varname> (<type>string</type>)</term>
5969 <primary><varname>lc_collate</> configuration parameter</primary>
5973 Reports the locale in which sorting of textual data is done.
5974 See <xref linkend="locale"> for more information.
5975 This value is determined when a database is created.
5980 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
5981 <term><varname>lc_ctype</varname> (<type>string</type>)</term>
5983 <primary><varname>lc_ctype</> configuration parameter</primary>
5987 Reports the locale that determines character classifications.
5988 See <xref linkend="locale"> for more information.
5989 This value is determined when a database is created.
5990 Ordinarily this will be the same as <varname>lc_collate</varname>,
5991 but for special applications it might be set differently.
5996 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
5997 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
5999 <primary><varname>max_function_args</> configuration parameter</primary>
6003 Reports the maximum number of function arguments. It is determined by
6004 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
6005 default value is 100 arguments.
6010 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
6011 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
6013 <primary><varname>max_identifier_length</> configuration parameter</primary>
6017 Reports the maximum identifier length. It is determined as one
6018 less than the value of <literal>NAMEDATALEN</> when building
6019 the server. The default value of <literal>NAMEDATALEN</> is
6020 64; therefore the default
6021 <varname>max_identifier_length</varname> is 63 bytes, which
6022 can be less than 63 characters when using multibyte encodings.
6027 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
6028 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
6030 <primary><varname>max_index_keys</> configuration parameter</primary>
6034 Reports the maximum number of index keys. It is determined by
6035 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
6036 default value is 32 keys.
6041 <varlistentry id="guc-segment-size" xreflabel="segment_size">
6042 <term><varname>segment_size</varname> (<type>integer</type>)</term>
6044 <primary><varname>segment_size</> configuration parameter</primary>
6048 Reports the number of blocks (pages) that can be stored within a file
6049 segment. It is determined by the value of <literal>RELSEG_SIZE</>
6050 when building the server. The maximum size of a segment file in bytes
6051 is equal to <varname>segment_size</> multiplied by
6052 <varname>block_size</>; by default this is 1GB.
6057 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
6058 <term><varname>server_encoding</varname> (<type>string</type>)</term>
6060 <primary><varname>server_encoding</> configuration parameter</primary>
6062 <indexterm><primary>character set</></>
6065 Reports the database encoding (character set).
6066 It is determined when the database is created. Ordinarily,
6067 clients need only be concerned with the value of <xref
6068 linkend="guc-client-encoding">.
6073 <varlistentry id="guc-server-version" xreflabel="server_version">
6074 <term><varname>server_version</varname> (<type>string</type>)</term>
6076 <primary><varname>server_version</> configuration parameter</primary>
6080 Reports the version number of the server. It is determined by the
6081 value of <literal>PG_VERSION</> when building the server.
6086 <varlistentry id="guc-server-version-num" xreflabel="server_version_num">
6087 <term><varname>server_version_num</varname> (<type>integer</type>)</term>
6089 <primary><varname>server_version_num</> configuration parameter</primary>
6093 Reports the version number of the server as an integer. It is determined
6094 by the value of <literal>PG_VERSION_NUM</> when building the server.
6099 <varlistentry id="guc-wal-block-size" xreflabel="wal_block_size">
6100 <term><varname>wal_block_size</varname> (<type>integer</type>)</term>
6102 <primary><varname>wal_block_size</> configuration parameter</primary>
6106 Reports the size of a WAL disk block. It is determined by the value
6107 of <literal>XLOG_BLCKSZ</> when building the server. The default value
6113 <varlistentry id="guc-wal-segment-size" xreflabel="wal_segment_size">
6114 <term><varname>wal_segment_size</varname> (<type>integer</type>)</term>
6116 <primary><varname>wal_segment_size</> configuration parameter</primary>
6120 Reports the number of blocks (pages) in a WAL segment file.
6121 The total size of a WAL segment file in bytes is equal to
6122 <varname>wal_segment_size</> multiplied by <varname>wal_block_size</>;
6123 by default this is 16MB. See <xref linkend="wal-configuration"> for
6132 <sect1 id="runtime-config-custom">
6133 <title>Customized Options</title>
6136 This feature was designed to allow parameters not normally known to
6137 <productname>PostgreSQL</productname> to be added by add-on modules
6138 (such as procedural languages). This allows extension modules to be
6139 configured in the standard ways.
6143 Custom options have two-part names: an extension name, then a dot, then
6144 the parameter name proper, much like qualified names in SQL. An example
6145 is <literal>plpgsql.variable_conflict</>.
6149 Because custom options may need to be set in processes that have not
6150 loaded the relevant extension module, <productname>PostgreSQL</>
6151 will accept a setting for any two-part parameter name. Such variables
6152 are treated as placeholders and have no function until the module that
6153 defines them is loaded. When an extension module is loaded, it will add
6154 its variable definitions, convert any placeholder values according to
6155 those definitions, and issue warnings for any unrecognized placeholders
6156 that begin with its extension name.
6160 <sect1 id="runtime-config-developer">
6161 <title>Developer Options</title>
6164 The following parameters are intended for work on the
6165 <productname>PostgreSQL</productname> source code, and in some cases
6166 to assist with recovery of severely damaged databases. There
6167 should be no reason to use them on a production database.
6168 As such, they have been excluded from the sample
6169 <filename>postgresql.conf</> file. Note that many of these
6170 parameters require special source compilation flags to work at all.
6174 <varlistentry id="guc-allow-system-table-mods" xreflabel="allow_system_table_mods">
6175 <term><varname>allow_system_table_mods</varname> (<type>boolean</type>)</term>
6177 <primary><varname>allow_system_table_mods</varname> configuration parameter</primary>
6181 Allows modification of the structure of system tables.
6182 This is used by <command>initdb</command>.
6183 This parameter can only be set at server start.
6188 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
6189 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
6191 <primary><varname>debug_assertions</> configuration parameter</primary>
6195 Turns on various assertion checks. This is a debugging aid. If
6196 you are experiencing strange problems or crashes you might want
6197 to turn this on, as it might expose programming mistakes. To use
6198 this parameter, the macro <symbol>USE_ASSERT_CHECKING</symbol>
6199 must be defined when <productname>PostgreSQL</productname> is
6200 built (accomplished by the <command>configure</command> option
6201 <option>--enable-cassert</option>). Note that
6202 <varname>debug_assertions</varname> defaults to <literal>on</>
6203 if <productname>PostgreSQL</productname> has been built with
6209 <varlistentry id="guc-ignore-system-indexes" xreflabel="ignore_system_indexes">
6210 <term><varname>ignore_system_indexes</varname> (<type>boolean</type>)</term>
6212 <primary><varname>ignore_system_indexes</varname> configuration parameter</primary>
6216 Ignore system indexes when reading system tables (but still
6217 update the indexes when modifying the tables). This is useful
6218 when recovering from damaged system indexes.
6219 This parameter cannot be changed after session start.
6224 <varlistentry id="guc-post-auth-delay" xreflabel="post_auth_delay">
6225 <term><varname>post_auth_delay</varname> (<type>integer</type>)</term>
6227 <primary><varname>post_auth_delay</> configuration parameter</primary>
6231 If nonzero, a delay of this many seconds occurs when a new
6232 server process is started, after it conducts the
6233 authentication procedure. This is intended to give developers an
6234 opportunity to attach to the server process with a debugger.
6235 This parameter cannot be changed after session start.
6240 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
6241 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
6243 <primary><varname>pre_auth_delay</> configuration parameter</primary>
6247 If nonzero, a delay of this many seconds occurs just after a
6248 new server process is forked, before it conducts the
6249 authentication procedure. This is intended to give developers an
6250 opportunity to attach to the server process with a debugger to
6251 trace down misbehavior in authentication.
6252 This parameter can only be set in the <filename>postgresql.conf</>
6253 file or on the server command line.
6258 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
6259 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
6261 <primary><varname>trace_notify</> configuration parameter</primary>
6265 Generates a great amount of debugging output for the
6266 <command>LISTEN</command> and <command>NOTIFY</command>
6267 commands. <xref linkend="guc-client-min-messages"> or
6268 <xref linkend="guc-log-min-messages"> must be
6269 <literal>DEBUG1</literal> or lower to send this output to the
6270 client or server logs, respectively.
6275 <varlistentry id="guc-trace-recovery-messages" xreflabel="trace_recovery_messages">
6276 <term><varname>trace_recovery_messages</varname> (<type>enum</type>)</term>
6278 <primary><varname>trace_recovery_messages</> configuration parameter</primary>
6282 Enables logging of recovery-related debugging output that otherwise
6283 would not be logged. This parameter allows the user to override the
6284 normal setting of <xref linkend="guc-log-min-messages">, but only for
6285 specific messages. This is intended for use in debugging Hot Standby.
6286 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
6287 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>, and
6288 <literal>LOG</>. The default, <literal>LOG</>, does not affect
6289 logging decisions at all. The other values cause recovery-related
6290 debug messages of that priority or higher to be logged as though they
6291 had <literal>LOG</> priority; for common settings of
6292 <varname>log_min_messages</> this results in unconditionally sending
6293 them to the server log.
6294 This parameter can only be set in the <filename>postgresql.conf</>
6295 file or on the server command line.
6300 <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
6301 <term><varname>trace_sort</varname> (<type>boolean</type>)</term>
6303 <primary><varname>trace_sort</> configuration parameter</primary>
6307 If on, emit information about resource usage during sort operations.
6308 This parameter is only available if the <symbol>TRACE_SORT</symbol> macro
6309 was defined when <productname>PostgreSQL</productname> was compiled.
6310 (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
6316 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
6318 <primary><varname>trace_locks</> configuration parameter</primary>
6322 If on, emit information about lock usage. Information dumped
6323 includes the type of lock operation, the type of lock and the unique
6324 identifier of the object being locked or unlocked. Also included
6325 are bit masks for the lock types already granted on this object as
6326 well as for the lock types awaited on this object. For each lock
6327 type a count of the number of granted locks and waiting locks is
6328 also dumped as well as the totals. An example of the log file output
6331 LOG: LockAcquire: new: lock(0xb7acd844) id(24688,24696,0,0,0,1)
6332 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
6333 wait(0) type(AccessShareLock)
6334 LOG: GrantLock: lock(0xb7acd844) id(24688,24696,0,0,0,1)
6335 grantMask(2) req(1,0,0,0,0,0,0)=1 grant(1,0,0,0,0,0,0)=1
6336 wait(0) type(AccessShareLock)
6337 LOG: UnGrantLock: updated: lock(0xb7acd844) id(24688,24696,0,0,0,1)
6338 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
6339 wait(0) type(AccessShareLock)
6340 LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
6341 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
6342 wait(0) type(INVALID)
6344 Details of the structure being dumped may be found in
6345 <filename>src/include/storage/lock.h</filename>.
6348 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6349 macro was defined when <productname>PostgreSQL</productname> was
6356 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
6358 <primary><varname>trace_lwlocks</> configuration parameter</primary>
6362 If on, emit information about lightweight lock usage. Lightweight
6363 locks are intended primarily to provide mutual exclusion of access
6364 to shared-memory data structures.
6367 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6368 macro was defined when <productname>PostgreSQL</productname> was
6375 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
6377 <primary><varname>trace_userlocks</> configuration parameter</primary>
6381 If on, emit information about user lock usage. Output is the same
6382 as for <symbol>trace_locks</symbol>, only for advisory locks.
6385 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6386 macro was defined when <productname>PostgreSQL</productname> was
6393 <term><varname>trace_lock_oidmin</varname> (<type>integer</type>)</term>
6395 <primary><varname>trace_lock_oidmin</> configuration parameter</primary>
6399 If set, do not trace locks for tables below this OID. (use to avoid
6400 output on system tables)
6403 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6404 macro was defined when <productname>PostgreSQL</productname> was
6411 <term><varname>trace_lock_table</varname> (<type>integer</type>)</term>
6413 <primary><varname>trace_lock_table</> configuration parameter</primary>
6417 Unconditionally trace locks on this table (OID).
6420 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6421 macro was defined when <productname>PostgreSQL</productname> was
6428 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
6430 <primary><varname>debug_deadlocks</> configuration parameter</primary>
6434 If set, dumps information about all current locks when a
6435 deadlock timeout occurs.
6438 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6439 macro was defined when <productname>PostgreSQL</productname> was
6446 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
6448 <primary><varname>log_btree_build_stats</> configuration parameter</primary>
6452 If set, logs system resource usage statistics (memory and CPU) on
6453 various B-tree operations.
6456 This parameter is only available if the <symbol>BTREE_BUILD_STATS</symbol>
6457 macro was defined when <productname>PostgreSQL</productname> was
6463 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
6464 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
6466 <primary><varname>wal_debug</> configuration parameter</primary>
6470 If on, emit WAL-related debugging output. This parameter is
6471 only available if the <symbol>WAL_DEBUG</symbol> macro was
6472 defined when <productname>PostgreSQL</productname> was
6478 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
6479 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
6481 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
6485 Detection of a damaged page header normally causes
6486 <productname>PostgreSQL</> to report an error, aborting the current
6487 transaction. Setting <varname>zero_damaged_pages</> to on causes
6488 the system to instead report a warning, zero out the damaged
6489 page in memory, and continue processing. This behavior <emphasis>will destroy data</>,
6490 namely all the rows on the damaged page. However, it does allow you to get
6491 past the error and retrieve rows from any undamaged pages that might
6492 be present in the table. It is useful for recovering data if
6493 corruption has occurred due to a hardware or software error. You should
6494 generally not set this on until you have given up hope of recovering
6495 data from the damaged pages of a table. Zeroed-out pages are not
6496 forced to disk so it is recommended to recreate the table or
6497 the index before turning this parameter off again. The
6498 default setting is <literal>off</>, and it can only be changed
6505 <sect1 id="runtime-config-short">
6506 <title>Short Options</title>
6509 For convenience there are also single letter command-line option
6510 switches available for some parameters. They are described in
6511 <xref linkend="runtime-config-short-table">. Some of these
6512 options exist for historical reasons, and their presence as a
6513 single-letter option does not necessarily indicate an endorsement
6514 to use the option heavily.
6517 <table id="runtime-config-short-table">
6518 <title>Short Option Key</title>
6522 <entry>Short Option</entry>
6523 <entry>Equivalent</entry>
6529 <entry><option>-A <replaceable>x</replaceable></option></entry>
6530 <entry><literal>debug_assertions = <replaceable>x</replaceable></></entry>
6533 <entry><option>-B <replaceable>x</replaceable></option></entry>
6534 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
6537 <entry><option>-d <replaceable>x</replaceable></option></entry>
6538 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
6541 <entry><option>-e</option></entry>
6542 <entry><literal>datestyle = euro</></entry>
6546 <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
6547 <option>-fm</option>, <option>-fn</option>, <option>-fo</option>,
6548 <option>-fs</option>, <option>-ft</option>
6551 <literal>enable_bitmapscan = off</>,
6552 <literal>enable_hashjoin = off</>,
6553 <literal>enable_indexscan = off</>,
6554 <literal>enable_mergejoin = off</>,
6555 <literal>enable_nestloop = off</>,
6556 <literal>enable_indexonlyscan = off</>,
6557 <literal>enable_seqscan = off</>,
6558 <literal>enable_tidscan = off</>
6562 <entry><option>-F</option></entry>
6563 <entry><literal>fsync = off</></entry>
6566 <entry><option>-h <replaceable>x</replaceable></option></entry>
6567 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
6570 <entry><option>-i</option></entry>
6571 <entry><literal>listen_addresses = '*'</></entry>
6574 <entry><option>-k <replaceable>x</replaceable></option></entry>
6575 <entry><literal>unix_socket_directories = <replaceable>x</replaceable></></entry>
6578 <entry><option>-l</option></entry>
6579 <entry><literal>ssl = on</></entry>
6582 <entry><option>-N <replaceable>x</replaceable></option></entry>
6583 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
6586 <entry><option>-O</option></entry>
6587 <entry><literal>allow_system_table_mods = on</></entry>
6590 <entry><option>-p <replaceable>x</replaceable></option></entry>
6591 <entry><literal>port = <replaceable>x</replaceable></></entry>
6594 <entry><option>-P</option></entry>
6595 <entry><literal>ignore_system_indexes = on</></entry>
6598 <entry><option>-s</option></entry>
6599 <entry><literal>log_statement_stats = on</></entry>
6602 <entry><option>-S <replaceable>x</replaceable></option></entry>
6603 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
6606 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option></entry>
6607 <entry><literal>log_parser_stats = on</>,
6608 <literal>log_planner_stats = on</>,
6609 <literal>log_executor_stats = on</></entry>
6612 <entry><option>-W <replaceable>x</replaceable></option></entry>
6613 <entry><literal>post_auth_delay = <replaceable>x</replaceable></></entry>