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>
22 All parameter names are case-insensitive. Every parameter takes a
23 value of one of five types: Boolean, integer, floating point,
24 string or enum. Boolean values can be written as <literal>on</literal>,
25 <literal>off</literal>, <literal>true</literal>,
26 <literal>false</literal>, <literal>yes</literal>,
27 <literal>no</literal>, <literal>1</literal>, <literal>0</literal>
28 (all case-insensitive) or any unambiguous prefix of these.
32 Some settings specify a memory or time value. Each of these has an
33 implicit unit, which is either kilobytes, blocks (typically eight
34 kilobytes), milliseconds, seconds, or minutes. Default units can be
35 found by referencing <structname>pg_settings</>.<structfield>unit</>.
37 a different unit can also be specified explicitly. Valid memory units
38 are <literal>kB</literal> (kilobytes), <literal>MB</literal>
39 (megabytes), and <literal>GB</literal> (gigabytes); valid time units
40 are <literal>ms</literal> (milliseconds), <literal>s</literal>
41 (seconds), <literal>min</literal> (minutes), <literal>h</literal>
42 (hours), and <literal>d</literal> (days). Note that the multiplier
43 for memory units is 1024, not 1000.
47 Parameters of type <quote>enum</> are specified in the same way as string
48 parameters, but are restricted to a limited set of values. The allowed
50 from <structname>pg_settings</>.<structfield>enumvals</>.
51 Enum parameter values are case-insensitive.
55 One way to set these parameters is to edit the file
56 <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>,
57 which is normally kept in the data directory. (A default copy is
58 installed there when the database cluster directory is
59 initialized.) An example of what this file might look like is:
63 log_destination = 'syslog'
64 search_path = '"$user", public'
65 shared_buffers = 128MB
67 One parameter is specified per line. The equal sign between name and
68 value is optional. Whitespace is insignificant and blank lines are
69 ignored. Hash marks (<literal>#</literal>) designate the rest of the
70 line as a comment. Parameter values that are not simple identifiers or
71 numbers must be single-quoted. To embed a single quote in a parameter
72 value, write either two quotes (preferred) or backslash-quote.
77 <primary><literal>include</></primary>
78 <secondary>in configuration file</secondary>
80 In addition to parameter settings, the <filename>postgresql.conf</>
81 file can contain <firstterm>include directives</>, which specify
82 another file to read and process as if it were inserted into the
83 configuration file at this point. Include directives simply look like:
87 If the file name is not an absolute path, it is taken as relative to
88 the directory containing the referencing configuration file.
89 Inclusions can be nested.
94 <primary><literal>include_if_exists</></primary>
95 <secondary>in configuration file</secondary>
97 Use the same approach as the <literal>include</> directive, continuing
98 normally if the file does not exist. A regular <literal>include</>
99 will stop with an error if the referenced file is missing, while
100 <literal>include_if_exists</> does not. A warning about the missing
106 <primary>SIGHUP</primary>
108 The configuration file is reread whenever the main server process receives a
109 <systemitem>SIGHUP</> signal (which is most easily sent by means
110 of <literal>pg_ctl reload</>). The main server process
111 also propagates this signal to all currently running server
112 processes so that existing sessions also get the new
113 value. Alternatively, you can send the signal to a single server
114 process directly. Some parameters can only be set at server start;
115 any changes to their entries in the configuration file will be ignored
116 until the server is restarted. Invalid parameter settings in the
117 configuration file are likewise ignored (but logged) during
118 <systemitem>SIGHUP</> processing.
122 A second way to set these configuration parameters is to give them
123 as a command-line option to the <command>postgres</command> command, such as:
125 postgres -c log_connections=yes -c log_destination='syslog'
127 Command-line options override any conflicting settings in
128 <filename>postgresql.conf</filename>. Note that this means you won't
129 be able to change the value on-the-fly by editing
130 <filename>postgresql.conf</filename>, so while the command-line
131 method might be convenient, it can cost you flexibility later.
135 Occasionally it is useful to give a command line option to
136 one particular session only. The environment variable
137 <envar>PGOPTIONS</envar> can be used for this purpose on the
140 env PGOPTIONS='-c geqo=off' psql
142 (This works for any <application>libpq</>-based client application, not
143 just <application>psql</application>.) Note that this won't work for
144 parameters that are fixed when the server is started or that must be
145 specified in <filename>postgresql.conf</filename>.
149 Furthermore, it is possible to assign a set of parameter settings to
150 a user or a database. Whenever a session is started, the default
151 settings for the user and database involved are loaded. The
152 commands <xref linkend="sql-alterrole">
153 and <xref linkend="sql-alterdatabase">,
154 respectively, are used to configure these settings. Per-database
155 settings override anything received from the
156 <command>postgres</command> command-line or the configuration
157 file, and in turn are overridden by per-user settings; both are
158 overridden by per-session settings.
162 Some parameters can be changed in individual <acronym>SQL</acronym>
163 sessions with the <xref linkend="SQL-SET">
164 command, for example:
166 SET ENABLE_SEQSCAN TO OFF;
168 If <command>SET</> is allowed, it overrides all other sources of
169 values for the parameter. Some parameters cannot be changed via
170 <command>SET</command>: for example, if they control behavior that
171 cannot be changed without restarting the entire
172 <productname>PostgreSQL</productname> server. Also,
173 some <command>SET</command> or <command>ALTER</> parameter modifications
174 require superuser permission.
178 The <xref linkend="SQL-SHOW">
179 command allows inspection of the current values of all parameters.
183 The virtual table <structname>pg_settings</structname> also allows
184 displaying and updating session run-time parameters; see <xref
185 linkend="view-pg-settings"> for details and a description of the
186 different variable types and when they can be changed.
187 <structname>pg_settings</structname> is equivalent to <command>SHOW</>
188 and <command>SET</>, but can be more convenient
189 to use because it can be joined with other tables, or selected from using
190 any desired selection condition. It also contains more information about
191 what values are allowed for the parameters.
195 <sect1 id="runtime-config-file-locations">
196 <title>File Locations</title>
199 In addition to the <filename>postgresql.conf</filename> file
200 already mentioned, <productname>PostgreSQL</productname> uses
201 two other manually-edited configuration files, which control
202 client authentication (their use is discussed in <xref
203 linkend="client-authentication">). By default, all three
204 configuration files are stored in the database cluster's data
205 directory. The parameters described in this section allow the
206 configuration files to be placed elsewhere. (Doing so can ease
207 administration. In particular it is often easier to ensure that
208 the configuration files are properly backed-up when they are
213 <varlistentry id="guc-data-directory" xreflabel="data_directory">
214 <term><varname>data_directory</varname> (<type>string</type>)</term>
216 <primary><varname>data_directory</> configuration parameter</primary>
220 Specifies the directory to use for data storage.
221 This parameter can only be set at server start.
226 <varlistentry id="guc-config-file" xreflabel="config_file">
227 <term><varname>config_file</varname> (<type>string</type>)</term>
229 <primary><varname>config_file</> configuration parameter</primary>
233 Specifies the main server configuration file
234 (customarily called <filename>postgresql.conf</>).
235 This parameter can only be set on the <command>postgres</command> command line.
240 <varlistentry id="guc-hba-file" xreflabel="hba_file">
241 <term><varname>hba_file</varname> (<type>string</type>)</term>
243 <primary><varname>hba_file</> configuration parameter</primary>
247 Specifies the configuration file for host-based authentication
248 (customarily called <filename>pg_hba.conf</>).
249 This parameter can only be set at server start.
254 <varlistentry id="guc-ident-file" xreflabel="ident_file">
255 <term><varname>ident_file</varname> (<type>string</type>)</term>
257 <primary><varname>ident_file</> configuration parameter</primary>
261 Specifies the configuration file for
262 <xref linkend="auth-username-maps"> user name mapping
263 (customarily called <filename>pg_ident.conf</>).
264 This parameter can only be set at server start.
269 <varlistentry id="guc-external-pid-file" xreflabel="external_pid_file">
270 <term><varname>external_pid_file</varname> (<type>string</type>)</term>
272 <primary><varname>external_pid_file</> configuration parameter</primary>
276 Specifies the name of an additional process-ID (PID) file that the
277 server should create for use by server administration programs.
278 This parameter can only be set at server start.
285 In a default installation, none of the above parameters are set
286 explicitly. Instead, the
287 data directory is specified by the <option>-D</option> command-line
288 option or the <envar>PGDATA</envar> environment variable, and the
289 configuration files are all found within the data directory.
293 If you wish to keep the configuration files elsewhere than the
294 data directory, the <command>postgres</command> <option>-D</option>
295 command-line option or <envar>PGDATA</envar> environment variable
296 must point to the directory containing the configuration files,
297 and the <varname>data_directory</> parameter must be set in
298 <filename>postgresql.conf</filename> (or on the command line) to show
299 where the data directory is actually located. Notice that
300 <varname>data_directory</> overrides <option>-D</option> and
301 <envar>PGDATA</envar> for the location
302 of the data directory, but not for the location of the configuration
307 If you wish, you can specify the configuration file names and locations
308 individually using the parameters <varname>config_file</>,
309 <varname>hba_file</> and/or <varname>ident_file</>.
310 <varname>config_file</> can only be specified on the
311 <command>postgres</command> command line, but the others can be
312 set within the main configuration file. If all three parameters plus
313 <varname>data_directory</> are explicitly set, then it is not necessary
314 to specify <option>-D</option> or <envar>PGDATA</envar>.
318 When setting any of these parameters, a relative path will be interpreted
319 with respect to the directory in which <command>postgres</command>
324 <sect1 id="runtime-config-connection">
325 <title>Connections and Authentication</title>
327 <sect2 id="runtime-config-connection-settings">
328 <title>Connection Settings</title>
332 <varlistentry id="guc-listen-addresses" xreflabel="listen_addresses">
333 <term><varname>listen_addresses</varname> (<type>string</type>)</term>
335 <primary><varname>listen_addresses</> configuration parameter</primary>
339 Specifies the TCP/IP address(es) on which the server is
340 to listen for connections from client applications.
341 The value takes the form of a comma-separated list of host names
342 and/or numeric IP addresses. The special entry <literal>*</>
343 corresponds to all available IP interfaces. The entry
344 <literal>0.0.0.0</> allows listening for all IPv4 addresses and
345 <literal>::</> allows listening for all IPv6 addresses.
346 If the list is empty, the server does not listen on any IP interface
347 at all, in which case only Unix-domain sockets can be used to connect
349 The default value is <systemitem class="systemname">localhost</>,
350 which allows only local TCP/IP <quote>loopback</> connections to be
351 made. While client authentication (<xref
352 linkend="client-authentication">) allows fine-grained control
353 over who can access the server, <varname>listen_addresses</varname>
354 controls which interfaces accept connection attempts, which
355 can help prevent repeated malicious connection requests on
356 insecure network interfaces. This parameter can only be set
362 <varlistentry id="guc-port" xreflabel="port">
363 <term><varname>port</varname> (<type>integer</type>)</term>
365 <primary><varname>port</> configuration parameter</primary>
369 The TCP port the server listens on; 5432 by default. Note that the
370 same port number is used for all IP addresses the server listens on.
371 This parameter can only be set at server start.
376 <varlistentry id="guc-max-connections" xreflabel="max_connections">
377 <term><varname>max_connections</varname> (<type>integer</type>)</term>
379 <primary><varname>max_connections</> configuration parameter</primary>
383 Determines the maximum number of concurrent connections to the
384 database server. The default is typically 100 connections, but
385 might be less if your kernel settings will not support it (as
386 determined during <application>initdb</>). This parameter can
387 only be set at server start.
391 Increasing this parameter might cause <productname>PostgreSQL</>
392 to request more <systemitem class="osname">System V</> shared
393 memory or semaphores than your operating system's default configuration
394 allows. See <xref linkend="sysvipc"> for information on how to
395 adjust those parameters, if necessary.
399 When running a standby server, you must set this parameter to the
400 same or higher value than on the master server. Otherwise, queries
401 will not be allowed in the standby server.
406 <varlistentry id="guc-superuser-reserved-connections"
407 xreflabel="superuser_reserved_connections">
408 <term><varname>superuser_reserved_connections</varname>
409 (<type>integer</type>)</term>
411 <primary><varname>superuser_reserved_connections</> configuration parameter</primary>
415 Determines the number of connection <quote>slots</quote> that
416 are reserved for connections by <productname>PostgreSQL</>
417 superusers. At most <xref linkend="guc-max-connections">
418 connections can ever be active simultaneously. Whenever the
419 number of active concurrent connections is at least
420 <varname>max_connections</> minus
421 <varname>superuser_reserved_connections</varname>, new
422 connections will be accepted only for superusers, and no
423 new replication connections will be accepted.
427 The default value is three connections. The value must be less
428 than the value of <varname>max_connections</varname>. This
429 parameter can only be set at server start.
434 <varlistentry id="guc-unix-socket-directory" xreflabel="unix_socket_directory">
435 <term><varname>unix_socket_directory</varname> (<type>string</type>)</term>
437 <primary><varname>unix_socket_directory</> configuration parameter</primary>
441 Specifies the directory of the Unix-domain socket on which the
442 server is to listen for
443 connections from client applications. The default is normally
444 <filename>/tmp</filename>, but can be changed at build time.
445 This parameter can only be set at server start.
449 In addition to the socket file itself, which is named
450 <literal>.s.PGSQL.<replaceable>nnnn</></literal> where
451 <replaceable>nnnn</> is the server's port number, an ordinary file
452 named <literal>.s.PGSQL.<replaceable>nnnn</>.lock</literal> will be
453 created in the <varname>unix_socket_directory</> directory. Neither
454 file should ever be removed manually.
458 This parameter is irrelevant on Windows, which does not have
464 <varlistentry id="guc-unix-socket-group" xreflabel="unix_socket_group">
465 <term><varname>unix_socket_group</varname> (<type>string</type>)</term>
467 <primary><varname>unix_socket_group</> configuration parameter</primary>
471 Sets the owning group of the Unix-domain socket. (The owning
472 user of the socket is always the user that starts the
473 server.) In combination with the parameter
474 <varname>unix_socket_permissions</varname> this can be used as
475 an additional access control mechanism for Unix-domain connections.
476 By default this is the empty string, which uses the default
477 group of the server user. This parameter can only be set at
482 This parameter is irrelevant on Windows, which does not have
488 <varlistentry id="guc-unix-socket-permissions" xreflabel="unix_socket_permissions">
489 <term><varname>unix_socket_permissions</varname> (<type>integer</type>)</term>
491 <primary><varname>unix_socket_permissions</> configuration parameter</primary>
495 Sets the access permissions of the Unix-domain socket. Unix-domain
496 sockets use the usual Unix file system permission set.
497 The parameter value is expected to be a numeric mode
498 specified in the format accepted by the
499 <function>chmod</function> and <function>umask</function>
500 system calls. (To use the customary octal format the number
501 must start with a <literal>0</literal> (zero).)
505 The default permissions are <literal>0777</literal>, meaning
506 anyone can connect. Reasonable alternatives are
507 <literal>0770</literal> (only user and group, see also
508 <varname>unix_socket_group</varname>) and <literal>0700</literal>
509 (only user). (Note that for a Unix-domain socket, only write
510 permission matters, so there is no point in setting or revoking
511 read or execute permissions.)
515 This access control mechanism is independent of the one
516 described in <xref linkend="client-authentication">.
520 This parameter can only be set at server start.
524 This parameter is irrelevant on Windows, which does not have
530 <varlistentry id="guc-bonjour" xreflabel="bonjour">
531 <term><varname>bonjour</varname> (<type>boolean</type>)</term>
533 <primary><varname>bonjour</> configuration parameter</primary>
537 Enables advertising the server's existence via
538 <productname>Bonjour</productname>. The default is off.
539 This parameter can only be set at server start.
544 <varlistentry id="guc-bonjour-name" xreflabel="bonjour_name">
545 <term><varname>bonjour_name</varname> (<type>string</type>)</term>
547 <primary><varname>bonjour_name</> configuration parameter</primary>
551 Specifies the <productname>Bonjour</productname> service
552 name. The computer name is used if this parameter is set to the
553 empty string <literal>''</> (which is the default). This parameter is
554 ignored if the server was not compiled with
555 <productname>Bonjour</productname> support.
556 This parameter can only be set at server start.
561 <varlistentry id="guc-tcp-keepalives-idle" xreflabel="tcp_keepalives_idle">
562 <term><varname>tcp_keepalives_idle</varname> (<type>integer</type>)</term>
564 <primary><varname>tcp_keepalives_idle</> configuration parameter</primary>
568 Specifies the number of seconds before sending a keepalive packet on
569 an otherwise idle connection. A value of 0 uses the system default.
570 This parameter is supported only on systems that support the
571 <symbol>TCP_KEEPIDLE</> or <symbol>TCP_KEEPALIVE</> symbols, and on
572 Windows; on other systems, it must be zero. This parameter is ignored
573 for connections made via a Unix-domain socket.
577 On Windows, a value of 0 will set this parameter to 2 hours,
578 since Windows does not provide a way to read the system default value.
584 <varlistentry id="guc-tcp-keepalives-interval" xreflabel="tcp_keepalives_interval">
585 <term><varname>tcp_keepalives_interval</varname> (<type>integer</type>)</term>
587 <primary><varname>tcp_keepalives_interval</> configuration parameter</primary>
591 Specifies the number of seconds between sending keepalives on an
592 otherwise idle connection. A value of 0 uses the system default.
593 This parameter is supported only on systems that support the
594 <symbol>TCP_KEEPINTVL</> symbol, and on Windows; on other systems, it
595 must be zero. This parameter is ignored for connections made via a
600 On Windows, a value of 0 will set this parameter to 1 second,
601 since Windows does not provide a way to read the system default value.
607 <varlistentry id="guc-tcp-keepalives-count" xreflabel="tcp_keepalives_count">
608 <term><varname>tcp_keepalives_count</varname> (<type>integer</type>)</term>
610 <primary><varname>tcp_keepalives_count</> configuration parameter</primary>
614 Specifies the number of keepalive packets to send on an otherwise idle
615 connection. A value of 0 uses the system default. This parameter is
616 supported only on systems that support the <symbol>TCP_KEEPCNT</>
617 symbol; on other systems, it must be zero. This parameter is ignored
618 for connections made via a Unix-domain socket.
622 This parameter is not supported on Windows, and must be zero.
630 <sect2 id="runtime-config-connection-security">
631 <title>Security and Authentication</title>
634 <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
635 <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
636 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
637 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
639 <primary><varname>authentication_timeout</> configuration parameter</primary>
644 Maximum time to complete client authentication, in seconds. If a
645 would-be client has not completed the authentication protocol in
646 this much time, the server closes the connection. This prevents
647 hung clients from occupying a connection indefinitely.
648 The default is one minute (<literal>1m</>).
649 This parameter can only be set in the <filename>postgresql.conf</>
650 file or on the server command line.
655 <varlistentry id="guc-ssl" xreflabel="ssl">
656 <term><varname>ssl</varname> (<type>boolean</type>)</term>
658 <primary><varname>ssl</> configuration parameter</primary>
662 Enables <acronym>SSL</> connections. Please read
663 <xref linkend="ssl-tcp"> before using this. The default
664 is <literal>off</>. This parameter can only be set at server
665 start. <acronym>SSL</> communication is only possible with
671 <varlistentry id="guc-ssl-ca-file" xreflabel="ssl_ca_file">
672 <term><varname>ssl_ca_file</varname> (<type>string</type>)</term>
674 <primary><varname>ssl_ca_file</> configuration parameter</primary>
678 Specifies the name of the file containing the SSL server certificate
679 authority (CA). The default is empty, meaning no CA file is loaded,
680 and client certificate verification is not performed. (In previous
681 releases of PostgreSQL, the name of this file was hard-coded
682 as <filename>root.crt</filename>.) Relative paths are relative to the
683 data directory. This parameter can only be set at server start.
688 <varlistentry id="guc-ssl-cert-file" xreflabel="ssl_cert_file">
689 <term><varname>ssl_cert_file</varname> (<type>string</type>)</term>
691 <primary><varname>ssl_cert_file</> configuration parameter</primary>
695 Specifies the name of the file containing the SSL server certificate.
696 The default is <filename>server.crt</filename>. Relative paths are
697 relative to the data directory. This parameter can only be set at
703 <varlistentry id="guc-ssl-crl-file" xreflabel="ssl_crl_file">
704 <term><varname>ssl_crl_file</varname> (<type>string</type>)</term>
706 <primary><varname>ssl_crl_file</> configuration parameter</primary>
710 Specifies the name of the file containing the SSL server certificate
711 revocation list (CRL). The default is empty, meaning no CRL file is
712 loaded. (In previous releases of PostgreSQL, the name of this file was
713 hard-coded as <filename>root.crl</filename>.) Relative paths are
714 relative to the data directory. This parameter can only be set at
720 <varlistentry id="guc-ssl-key-file" xreflabel="ssl_key_file">
721 <term><varname>ssl_key_file</varname> (<type>string</type>)</term>
723 <primary><varname>ssl_key_file</> configuration parameter</primary>
727 Specifies the name of the file containing the SSL server private key.
728 The default is <filename>server.key</filename>. Relative paths are
729 relative to the data directory. This parameter can only be set at
735 <varlistentry id="guc-ssl-renegotiation-limit" xreflabel="ssl_renegotiation_limit">
736 <term><varname>ssl_renegotiation_limit</varname> (<type>integer</type>)</term>
738 <primary><varname>ssl_renegotiation_limit</> configuration parameter</primary>
742 Specifies how much data can flow over an <acronym>SSL</>-encrypted
743 connection before renegotiation of the session keys will take
744 place. Renegotiation decreases an attacker's chances of doing
745 cryptanalysis when large amounts of traffic can be examined, but it
746 also carries a large performance penalty. The sum of sent and received
747 traffic is used to check the limit. If this parameter is set to 0,
748 renegotiation is disabled. The default is <literal>512MB</>.
752 SSL libraries from before November 2009 are insecure when using SSL
753 renegotiation, due to a vulnerability in the SSL protocol. As a
754 stop-gap fix for this vulnerability, some vendors shipped SSL
755 libraries incapable of doing renegotiation. If any such libraries
756 are in use on the client or server, SSL renegotiation should be
763 <varlistentry id="guc-ssl-ciphers" xreflabel="ssl_ciphers">
764 <term><varname>ssl_ciphers</varname> (<type>string</type>)</term>
766 <primary><varname>ssl_ciphers</> configuration parameter</primary>
770 Specifies a list of <acronym>SSL</> ciphers that are allowed to be
771 used on secure connections. See the <application>openssl</>
772 manual page for a list of supported ciphers.
777 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
778 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
780 <primary><varname>password_encryption</> configuration parameter</primary>
784 When a password is specified in <xref
785 linkend="sql-createuser"> or
786 <xref linkend="sql-alterrole">
787 without writing either <literal>ENCRYPTED</> or
788 <literal>UNENCRYPTED</>, this parameter determines whether the
789 password is to be encrypted. The default is <literal>on</>
790 (encrypt the password).
795 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
796 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
798 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
802 Sets the location of the Kerberos server key file. See
803 <xref linkend="kerberos-auth"> or <xref linkend="gssapi-auth">
804 for details. This parameter can only be set in the
805 <filename>postgresql.conf</> file or on the server command line.
810 <varlistentry id="guc-krb-srvname" xreflabel="krb_srvname">
811 <term><varname>krb_srvname</varname> (<type>string</type>)</term>
813 <primary><varname>krb_srvname</> configuration parameter</primary>
817 Sets the Kerberos service name. See <xref linkend="kerberos-auth">
818 for details. This parameter can only be set in the
819 <filename>postgresql.conf</> file or on the server command line.
824 <varlistentry id="guc-krb-caseins-users" xreflabel="krb_caseins_users">
825 <term><varname>krb_caseins_users</varname> (<type>boolean</type>)</term>
827 <primary><varname>krb_caseins_users</varname> configuration parameter</primary>
831 Sets whether Kerberos and GSSAPI user names should be treated
833 The default is <literal>off</> (case sensitive). This parameter can only be
834 set in the <filename>postgresql.conf</> file or on the server command line.
839 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
840 <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
842 <primary><varname>db_user_namespace</> configuration parameter</primary>
846 This parameter enables per-database user names. It is off by default.
847 This parameter can only be set in the <filename>postgresql.conf</>
848 file or on the server command line.
852 If this is on, you should create users as <literal>username@dbname</>.
853 When <literal>username</> is passed by a connecting client,
854 <literal>@</> and the database name are appended to the user
855 name and that database-specific user name is looked up by the
856 server. Note that when you create users with names containing
857 <literal>@</> within the SQL environment, you will need to
862 With this parameter enabled, you can still create ordinary global
863 users. Simply append <literal>@</> when specifying the user
864 name in the client, e.g. <literal>joe@</>. The <literal>@</>
865 will be stripped off before the user name is looked up by the
870 <varname>db_user_namespace</> causes the client's and
871 server's user name representation to differ.
872 Authentication checks are always done with the server's user name
873 so authentication methods must be configured for the
874 server's user name, not the client's. Because
875 <literal>md5</> uses the user name as salt on both the
876 client and server, <literal>md5</> cannot be used with
877 <varname>db_user_namespace</>.
882 This feature is intended as a temporary measure until a
883 complete solution is found. At that time, this option will
894 <sect1 id="runtime-config-resource">
895 <title>Resource Consumption</title>
897 <sect2 id="runtime-config-resource-memory">
898 <title>Memory</title>
901 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
902 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
904 <primary><varname>shared_buffers</> configuration parameter</primary>
908 Sets the amount of memory the database server uses for shared
909 memory buffers. The default is typically 32 megabytes
910 (<literal>32MB</>), but might be less if your kernel settings will
911 not support it (as determined during <application>initdb</>).
912 This setting must be at least 128 kilobytes. (Non-default
913 values of <symbol>BLCKSZ</symbol> change the minimum.) However,
914 settings significantly higher than the minimum are usually needed
915 for good performance. This parameter can only be set at server start.
919 If you have a dedicated database server with 1GB or more of RAM, a
920 reasonable starting value for <varname>shared_buffers</varname> is 25%
921 of the memory in your system. There are some workloads where even
922 large settings for <varname>shared_buffers</varname> are effective, but
923 because <productname>PostgreSQL</productname> also relies on the
924 operating system cache, it is unlikely that an allocation of more than
925 40% of RAM to <varname>shared_buffers</varname> will work better than a
926 smaller amount. Larger settings for <varname>shared_buffers</varname>
927 usually require a corresponding increase in
928 <varname>checkpoint_segments</varname>, in order to spread out the
929 process of writing large quantities of new or changed data over a
930 longer period of time.
934 On systems with less than 1GB of RAM, a smaller percentage of RAM is
935 appropriate, so as to leave adequate space for the operating system.
936 Also, on Windows, large values for <varname>shared_buffers</varname>
937 aren't as effective. You may find better results keeping the setting
938 relatively low and using the operating system cache more instead. The
939 useful range for <varname>shared_buffers</varname> on Windows systems
940 is generally from 64MB to 512MB.
944 Increasing this parameter might cause <productname>PostgreSQL</>
945 to request more <systemitem class="osname">System V</> shared
946 memory than your operating system's default configuration
947 allows. See <xref linkend="sysvipc"> for information on how to
948 adjust those parameters, if necessary.
953 <varlistentry id="guc-temp-buffers" xreflabel="temp_buffers">
954 <term><varname>temp_buffers</varname> (<type>integer</type>)</term>
956 <primary><varname>temp_buffers</> configuration parameter</primary>
960 Sets the maximum number of temporary buffers used by each database
961 session. These are session-local buffers used only for access to
962 temporary tables. The default is eight megabytes
963 (<literal>8MB</>). The setting can be changed within individual
964 sessions, but only before the first use of temporary tables
965 within the session; subsequent attempts to change the value will
966 have no effect on that session.
970 A session will allocate temporary buffers as needed up to the limit
971 given by <varname>temp_buffers</>. The cost of setting a large
972 value in sessions that do not actually need many temporary
973 buffers is only a buffer descriptor, or about 64 bytes, per
974 increment in <varname>temp_buffers</>. However if a buffer is
975 actually used an additional 8192 bytes will be consumed for it
976 (or in general, <symbol>BLCKSZ</symbol> bytes).
981 <varlistentry id="guc-max-prepared-transactions" xreflabel="max_prepared_transactions">
982 <term><varname>max_prepared_transactions</varname> (<type>integer</type>)</term>
984 <primary><varname>max_prepared_transactions</> configuration parameter</primary>
988 Sets the maximum number of transactions that can be in the
989 <quote>prepared</> state simultaneously (see <xref
990 linkend="sql-prepare-transaction">).
991 Setting this parameter to zero (which is the default)
992 disables the prepared-transaction feature.
993 This parameter can only be set at server start.
997 If you are not planning to use prepared transactions, this parameter
998 should be set to zero to prevent accidental creation of prepared
999 transactions. If you are using prepared transactions, you will
1000 probably want <varname>max_prepared_transactions</varname> to be at
1001 least as large as <xref linkend="guc-max-connections">, so that every
1002 session can have a prepared transaction pending.
1006 Increasing this parameter might cause <productname>PostgreSQL</>
1007 to request more <systemitem class="osname">System V</> shared
1008 memory than your operating system's default configuration
1009 allows. See <xref linkend="sysvipc"> for information on how to
1010 adjust those parameters, if necessary.
1014 When running a standby server, you must set this parameter to the
1015 same or higher value than on the master server. Otherwise, queries
1016 will not be allowed in the standby server.
1021 <varlistentry id="guc-work-mem" xreflabel="work_mem">
1022 <term><varname>work_mem</varname> (<type>integer</type>)</term>
1024 <primary><varname>work_mem</> configuration parameter</primary>
1028 Specifies the amount of memory to be used by internal sort operations
1029 and hash tables before writing to temporary disk files. The value
1030 defaults to one megabyte (<literal>1MB</>).
1031 Note that for a complex query, several sort or hash operations might be
1032 running in parallel; each operation will be allowed to use as much memory
1033 as this value specifies before it starts to write data into temporary
1034 files. Also, several running sessions could be doing such operations
1035 concurrently. Therefore, the total memory used could be many
1036 times the value of <varname>work_mem</varname>; it is necessary to
1037 keep this fact in mind when choosing the value. Sort operations are
1038 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
1040 Hash tables are used in hash joins, hash-based aggregation, and
1041 hash-based processing of <literal>IN</> subqueries.
1046 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
1047 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
1049 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
1053 Specifies the maximum amount of memory to be used by maintenance
1054 operations, such as <command>VACUUM</command>, <command>CREATE
1055 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults
1056 to 16 megabytes (<literal>16MB</>). Since only one of these
1057 operations can be executed at a time by a database session, and
1058 an installation normally doesn't have many of them running
1059 concurrently, it's safe to set this value significantly larger
1060 than <varname>work_mem</varname>. Larger settings might improve
1061 performance for vacuuming and for restoring database dumps.
1064 Note that when autovacuum runs, up to
1065 <xref linkend="guc-autovacuum-max-workers"> times this memory may be
1066 allocated, so be careful not to set the default value too high.
1071 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
1072 <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
1074 <primary><varname>max_stack_depth</> configuration parameter</primary>
1078 Specifies the maximum safe depth of the server's execution stack.
1079 The ideal setting for this parameter is the actual stack size limit
1080 enforced by the kernel (as set by <literal>ulimit -s</> or local
1081 equivalent), less a safety margin of a megabyte or so. The safety
1082 margin is needed because the stack depth is not checked in every
1083 routine in the server, but only in key potentially-recursive routines
1084 such as expression evaluation. The default setting is two
1085 megabytes (<literal>2MB</>), which is conservatively small and
1086 unlikely to risk crashes. However, it might be too small to allow
1087 execution of complex functions. Only superusers can change this
1092 Setting <varname>max_stack_depth</> higher than
1093 the actual kernel limit will mean that a runaway recursive function
1094 can crash an individual backend process. On platforms where
1095 <productname>PostgreSQL</productname> can determine the kernel limit,
1096 the server will not allow this variable to be set to an unsafe
1097 value. However, not all platforms provide the information,
1098 so caution is recommended in selecting a value.
1106 <sect2 id="runtime-config-resource-disk">
1110 <varlistentry id="guc-temp-file-limit" xreflabel="temp_file_limit">
1111 <term><varname>temp_file_limit</varname> (<type>integer</type>)</term>
1113 <primary><varname>temp_file_limit</> configuration parameter</primary>
1117 Specifies the maximum amount of disk space that a session can use
1118 for temporary files, such as sort and hash temporary files, or the
1119 storage file for a held cursor.
1120 The value is specified in kilobytes, and <literal>-1</> (the
1121 default) means no limit.
1122 Only superusers can change this setting.
1125 This setting constrains the total space used at any instant by all
1126 temporary files used by a given <productname>PostgreSQL</> session.
1127 It should be noted that disk space used for explicit temporary
1128 tables, as opposed to temporary files used behind-the-scenes in query
1129 execution, does <emphasis>not</emphasis> count against this limit.
1137 <sect2 id="runtime-config-resource-kernel">
1138 <title>Kernel Resource Usage</title>
1141 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
1142 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
1144 <primary><varname>max_files_per_process</> configuration parameter</primary>
1148 Sets the maximum number of simultaneously open files allowed to each
1149 server subprocess. The default is one thousand files. If the kernel is enforcing
1150 a safe per-process limit, you don't need to worry about this setting.
1151 But on some platforms (notably, most BSD systems), the kernel will
1152 allow individual processes to open many more files than the system
1153 can actually support if many processes all try to open
1154 that many files. If you find yourself seeing <quote>Too many open
1155 files</> failures, try reducing this setting.
1156 This parameter can only be set at server start.
1161 <varlistentry id="guc-shared-preload-libraries" xreflabel="shared_preload_libraries">
1162 <term><varname>shared_preload_libraries</varname> (<type>string</type>)</term>
1164 <primary><varname>shared_preload_libraries</> configuration parameter</primary>
1168 This variable specifies one or more shared libraries
1169 to be preloaded at server start. For example,
1170 <literal>'$libdir/mylib'</literal> would cause
1171 <literal>mylib.so</> (or on some platforms,
1172 <literal>mylib.sl</>) to be preloaded from the installation's
1173 standard library directory.
1174 All library names are converted to lower case unless double-quoted.
1175 If more than one library is to be loaded, separate their names
1176 with commas. This parameter can only be set at server start.
1180 <productname>PostgreSQL</productname> procedural language
1181 libraries can be preloaded in this way, typically by using the
1182 syntax <literal>'$libdir/plXXX'</literal> where
1183 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
1184 <literal>tcl</>, or <literal>python</>.
1188 By preloading a shared library, the library startup time is avoided
1189 when the library is first used. However, the time to start each new
1190 server process might increase slightly, even if that process never
1191 uses the library. So this parameter is recommended only for
1192 libraries that will be used in most sessions.
1197 On Windows hosts, preloading a library at server start will not reduce
1198 the time required to start each new server process; each server process
1199 will re-load all preload libraries. However, <varname>shared_preload_libraries
1200 </varname> is still useful on Windows hosts because some shared libraries may
1201 need to perform certain operations that only take place at postmaster start
1202 (for example, a shared library may need to reserve lightweight locks
1203 or shared memory and you can't do that after the postmaster has started).
1207 If a specified library is not found,
1208 the server will fail to start.
1212 Every PostgreSQL-supported library has a <quote>magic
1213 block</> that is checked to guarantee compatibility.
1214 For this reason, non-PostgreSQL libraries cannot be
1223 <sect2 id="runtime-config-resource-vacuum-cost">
1224 <title>Cost-based Vacuum Delay</title>
1227 During the execution of <xref linkend="sql-vacuum">
1228 and <xref linkend="sql-analyze">
1229 commands, the system maintains an
1230 internal counter that keeps track of the estimated cost of the
1231 various I/O operations that are performed. When the accumulated
1232 cost reaches a limit (specified by
1233 <varname>vacuum_cost_limit</varname>), the process performing
1234 the operation will sleep for a short period of time, as specified by
1235 <varname>vacuum_cost_delay</varname>. Then it will reset the
1236 counter and continue execution.
1240 The intent of this feature is to allow administrators to reduce
1241 the I/O impact of these commands on concurrent database
1242 activity. There are many situations where it is not
1243 important that maintenance commands like
1244 <command>VACUUM</command> and <command>ANALYZE</command> finish
1245 quickly; however, it is usually very important that these
1246 commands do not significantly interfere with the ability of the
1247 system to perform other database operations. Cost-based vacuum
1248 delay provides a way for administrators to achieve this.
1252 This feature is disabled by default for manually issued
1253 <command>VACUUM</command> commands. To enable it, set the
1254 <varname>vacuum_cost_delay</varname> variable to a nonzero
1259 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1260 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)</term>
1262 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1266 The length of time, in milliseconds, that the process will sleep
1267 when the cost limit has been exceeded.
1268 The default value is zero, which disables the cost-based vacuum
1269 delay feature. Positive values enable cost-based vacuuming.
1270 Note that on many systems, the effective resolution
1271 of sleep delays is 10 milliseconds; setting
1272 <varname>vacuum_cost_delay</varname> to a value that is
1273 not a multiple of 10 might have the same results as setting it
1274 to the next higher multiple of 10.
1278 When using cost-based vacuuming, appropriate values for
1279 <varname>vacuum_cost_delay</> are usually quite small, perhaps
1280 10 or 20 milliseconds. Adjusting vacuum's resource consumption
1281 is best done by changing the other vacuum cost parameters.
1286 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1287 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
1289 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1293 The estimated cost for vacuuming a buffer found in the shared buffer
1294 cache. It represents the cost to lock the buffer pool, lookup
1295 the shared hash table and scan the content of the page. The
1296 default value is one.
1301 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1302 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
1304 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1308 The estimated cost for vacuuming a buffer that has to be read from
1309 disk. This represents the effort to lock the buffer pool,
1310 lookup the shared hash table, read the desired block in from
1311 the disk and scan its content. The default value is 10.
1316 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1317 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
1319 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1323 The estimated cost charged when vacuum modifies a block that was
1324 previously clean. It represents the extra I/O required to
1325 flush the dirty block out to disk again. The default value is
1331 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1332 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
1334 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1338 The accumulated cost that will cause the vacuuming process to sleep.
1339 The default value is 200.
1347 There are certain operations that hold critical locks and should
1348 therefore complete as quickly as possible. Cost-based vacuum
1349 delays do not occur during such operations. Therefore it is
1350 possible that the cost accumulates far higher than the specified
1351 limit. To avoid uselessly long delays in such cases, the actual
1352 delay is calculated as <varname>vacuum_cost_delay</varname> *
1353 <varname>accumulated_balance</varname> /
1354 <varname>vacuum_cost_limit</varname> with a maximum of
1355 <varname>vacuum_cost_delay</varname> * 4.
1360 <sect2 id="runtime-config-resource-background-writer">
1361 <title>Background Writer</title>
1364 There is a separate server
1365 process called the <firstterm>background writer</>, whose function
1366 is to issue writes of <quote>dirty</> (new or modified) shared
1367 buffers. It writes shared buffers so server processes handling
1368 user queries seldom or never need to wait for a write to occur.
1369 However, the background writer does cause a net overall
1370 increase in I/O load, because while a repeatedly-dirtied page might
1371 otherwise be written only once per checkpoint interval, the
1372 background writer might write it several times as it is dirtied
1373 in the same interval. The parameters discussed in this subsection
1374 can be used to tune the behavior for local needs.
1378 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1379 <term><varname>bgwriter_delay</varname> (<type>integer</type>)</term>
1381 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1385 Specifies the delay between activity rounds for the
1386 background writer. In each round the writer issues writes
1387 for some number of dirty buffers (controllable by the
1388 following parameters). It then sleeps for <varname>bgwriter_delay</>
1389 milliseconds, and repeats. When there are no dirty buffers in the
1390 buffer pool, though, it goes into a longer sleep regardless of
1391 <varname>bgwriter_delay</>. The default value is 200
1392 milliseconds (<literal>200ms</>). Note that on many systems, the
1393 effective resolution of sleep delays is 10 milliseconds; setting
1394 <varname>bgwriter_delay</> to a value that is not a multiple of 10
1395 might have the same results as setting it to the next higher multiple
1396 of 10. This parameter can only be set in the
1397 <filename>postgresql.conf</> file or on the server command line.
1402 <varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1403 <term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)</term>
1405 <primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1409 In each round, no more than this many buffers will be written
1410 by the background writer. Setting this to zero disables
1411 background writing. (Note that checkpoints, which are managed by
1412 a separate, dedicated auxiliary process, are unaffected.)
1413 The default value is 100 buffers.
1414 This parameter can only be set in the <filename>postgresql.conf</>
1415 file or on the server command line.
1420 <varlistentry id="guc-bgwriter-lru-multiplier" xreflabel="bgwriter_lru_multiplier">
1421 <term><varname>bgwriter_lru_multiplier</varname> (<type>floating point</type>)</term>
1423 <primary><varname>bgwriter_lru_multiplier</> configuration parameter</primary>
1427 The number of dirty buffers written in each round is based on the
1428 number of new buffers that have been needed by server processes
1429 during recent rounds. The average recent need is multiplied by
1430 <varname>bgwriter_lru_multiplier</> to arrive at an estimate of the
1431 number of buffers that will be needed during the next round. Dirty
1432 buffers are written until there are that many clean, reusable buffers
1433 available. (However, no more than <varname>bgwriter_lru_maxpages</>
1434 buffers will be written per round.)
1435 Thus, a setting of 1.0 represents a <quote>just in time</> policy
1436 of writing exactly the number of buffers predicted to be needed.
1437 Larger values provide some cushion against spikes in demand,
1438 while smaller values intentionally leave writes to be done by
1441 This parameter can only be set in the <filename>postgresql.conf</>
1442 file or on the server command line.
1449 Smaller values of <varname>bgwriter_lru_maxpages</varname> and
1450 <varname>bgwriter_lru_multiplier</varname> reduce the extra I/O load
1451 caused by the background writer, but make it more likely that server
1452 processes will have to issue writes for themselves, delaying interactive
1457 <sect2 id="runtime-config-resource-async-behavior">
1458 <title>Asynchronous Behavior</title>
1461 <varlistentry id="guc-effective-io-concurrency" xreflabel="effective_io_concurrency">
1462 <term><varname>effective_io_concurrency</varname> (<type>integer</type>)</term>
1464 <primary><varname>effective_io_concurrency</> configuration parameter</primary>
1468 Sets the number of concurrent disk I/O operations that
1469 <productname>PostgreSQL</> expects can be executed
1470 simultaneously. Raising this value will increase the number of I/O
1471 operations that any individual <productname>PostgreSQL</> session
1472 attempts to initiate in parallel. The allowed range is 1 to 1000,
1473 or zero to disable issuance of asynchronous I/O requests. Currently,
1474 this setting only affects bitmap heap scans.
1478 A good starting point for this setting is the number of separate
1479 drives comprising a RAID 0 stripe or RAID 1 mirror being used for the
1480 database. (For RAID 5 the parity drive should not be counted.)
1481 However, if the database is often busy with multiple queries issued in
1482 concurrent sessions, lower values may be sufficient to keep the disk
1483 array busy. A value higher than needed to keep the disks busy will
1484 only result in extra CPU overhead.
1488 For more exotic systems, such as memory-based storage or a RAID array
1489 that is limited by bus bandwidth, the correct value might be the
1490 number of I/O paths available. Some experimentation may be needed
1491 to find the best value.
1495 Asynchronous I/O depends on an effective <function>posix_fadvise</>
1496 function, which some operating systems lack. If the function is not
1497 present then setting this parameter to anything but zero will result
1498 in an error. On some operating systems (e.g., Solaris), the function
1499 is present but does not actually do anything.
1507 <sect1 id="runtime-config-wal">
1508 <title>Write Ahead Log</title>
1511 See also <xref linkend="wal-configuration"> for details on WAL
1512 and checkpoint tuning.
1515 <sect2 id="runtime-config-wal-settings">
1516 <title>Settings</title>
1519 <varlistentry id="guc-wal-level" xreflabel="wal_level">
1520 <term><varname>wal_level</varname> (<type>enum</type>)</term>
1522 <primary><varname>wal_level</> configuration parameter</primary>
1526 <varname>wal_level</> determines how much information is written
1527 to the WAL. The default value is <literal>minimal</>, which writes
1528 only the information needed to recover from a crash or immediate
1529 shutdown. <literal>archive</> adds logging required for WAL archiving,
1530 and <literal>hot_standby</> further adds information required to run
1531 read-only queries on a standby server.
1532 This parameter can only be set at server start.
1535 In <literal>minimal</> level, WAL-logging of some bulk
1536 operations can be safely skipped, which can make those
1537 operations much faster (see <xref linkend="populate-pitr">).
1538 Operations in which this optimization can be applied include:
1540 <member><command>CREATE TABLE AS</></member>
1541 <member><command>CREATE INDEX</></member>
1542 <member><command>CLUSTER</></member>
1543 <member><command>COPY</> into tables that were created or truncated in the same
1544 transaction</member>
1546 But minimal WAL does not contain
1547 enough information to reconstruct the data from a base backup and the
1548 WAL logs, so either <literal>archive</> or <literal>hot_standby</>
1549 level must be used to enable
1550 WAL archiving (<xref linkend="guc-archive-mode">) and streaming
1554 In <literal>hot_standby</> level, the same information is logged as
1555 with <literal>archive</>, plus information needed to reconstruct
1556 the status of running transactions from the WAL. To enable read-only
1557 queries on a standby server, <varname>wal_level</> must be set to
1558 <literal>hot_standby</> on the primary, and
1559 <xref linkend="guc-hot-standby"> must be enabled in the standby. It is
1560 thought that there is
1561 little measurable difference in performance between using
1562 <literal>hot_standby</> and <literal>archive</> levels, so feedback
1563 is welcome if any production impacts are noticeable.
1568 <varlistentry id="guc-fsync" xreflabel="fsync">
1570 <primary><varname>fsync</> configuration parameter</primary>
1572 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1575 If this parameter is on, the <productname>PostgreSQL</> server
1576 will try to make sure that updates are physically written to
1577 disk, by issuing <function>fsync()</> system calls or various
1578 equivalent methods (see <xref linkend="guc-wal-sync-method">).
1579 This ensures that the database cluster can recover to a
1580 consistent state after an operating system or hardware crash.
1584 While turning off <varname>fsync</varname> is often a performance
1585 benefit, this can result in unrecoverable data corruption in
1586 the event of a power failure or system crash. Thus it
1587 is only advisable to turn off <varname>fsync</varname> if
1588 you can easily recreate your entire database from external
1593 Examples of safe circumstances for turning off
1594 <varname>fsync</varname> include the initial loading of a new
1595 database cluster from a backup file, using a database cluster
1596 for processing a batch of data after which the database
1597 will be thrown away and recreated,
1598 or for a read-only database clone which
1599 gets recreated frequently and is not used for failover. High
1600 quality hardware alone is not a sufficient justification for
1601 turning off <varname>fsync</varname>.
1605 In many situations, turning off <xref linkend="guc-synchronous-commit">
1606 for noncritical transactions can provide much of the potential
1607 performance benefit of turning off <varname>fsync</varname>, without
1608 the attendant risks of data corruption.
1612 <varname>fsync</varname> can only be set in the <filename>postgresql.conf</>
1613 file or on the server command line.
1614 If you turn this parameter off, also consider turning off
1615 <xref linkend="guc-full-page-writes">.
1620 <varlistentry id="guc-synchronous-commit" xreflabel="synchronous_commit">
1621 <term><varname>synchronous_commit</varname> (<type>enum</type>)</term>
1623 <primary><varname>synchronous_commit</> configuration parameter</primary>
1627 Specifies whether transaction commit will wait for WAL records
1628 to be written to disk before the command returns a <quote>success</>
1629 indication to the client. Valid values are <literal>on</>, <literal>write</>,
1630 <literal>local</>, and <literal>off</>. The default, and safe, value
1631 is <literal>on</>. When <literal>off</>, there can be a delay between
1632 when success is reported to the client and when the transaction is
1633 really guaranteed to be safe against a server crash. (The maximum
1634 delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
1635 <xref linkend="guc-fsync">, setting this parameter to <literal>off</>
1636 does not create any risk of database inconsistency: an operating
1637 system or database crash might
1638 result in some recent allegedly-committed transactions being lost, but
1639 the database state will be just the same as if those transactions had
1640 been aborted cleanly. So, turning <varname>synchronous_commit</> off
1641 can be a useful alternative when performance is more important than
1642 exact certainty about the durability of a transaction. For more
1643 discussion see <xref linkend="wal-async-commit">.
1646 If <xref linkend="guc-synchronous-standby-names"> is set, this
1647 parameter also controls whether or not transaction commit will wait
1648 for the transaction's WAL records to be flushed to disk and replicated
1649 to the standby server. When <literal>write</>, the commit wait will
1650 last until a reply from the current synchronous standby indicates
1651 it has received the commit record of the transaction to memory.
1652 Normally this causes no data loss at the time of failover. However,
1653 if both primary and standby crash, and the database cluster of
1654 the primary gets corrupted, recent committed transactions might
1655 be lost. When <literal>on</>, the commit wait will last until a reply
1656 from the current synchronous standby indicates it has flushed
1657 the commit record of the transaction to durable storage. This
1658 avoids any data loss unless the database cluster of both primary and
1659 standby gets corrupted simultaneously. If synchronous
1660 replication is in use, it will normally be sensible either to wait
1661 for both local flush and replication of WAL records, or
1662 to allow the transaction to commit asynchronously. However, the
1663 special value <literal>local</> is available for transactions that
1664 wish to wait for local flush to disk, but not synchronous replication.
1667 This parameter can be changed at any time; the behavior for any
1668 one transaction is determined by the setting in effect when it
1669 commits. It is therefore possible, and useful, to have some
1670 transactions commit synchronously and others asynchronously.
1671 For example, to make a single multistatement transaction commit
1672 asynchronously when the default is the opposite, issue <command>SET
1673 LOCAL synchronous_commit TO OFF</> within the transaction.
1678 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1679 <term><varname>wal_sync_method</varname> (<type>enum</type>)</term>
1681 <primary><varname>wal_sync_method</> configuration parameter</primary>
1685 Method used for forcing WAL updates out to disk.
1686 If <varname>fsync</varname> is off then this setting is irrelevant,
1687 since WAL file updates will not be forced out at all.
1688 Possible values are:
1693 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>)
1698 <literal>fdatasync</> (call <function>fdatasync()</> at each commit)
1703 <literal>fsync</> (call <function>fsync()</> at each commit)
1708 <literal>fsync_writethrough</> (call <function>fsync()</> at each commit, forcing write-through of any disk write cache)
1713 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>)
1718 The <literal>open_</>* options also use <literal>O_DIRECT</> if available.
1719 Not all of these choices are available on all platforms.
1720 The default is the first method in the above list that is supported
1721 by the platform, except that <literal>fdatasync</> is the default on
1722 Linux. The default is not necessarily ideal; it might be
1723 necessary to change this setting or other aspects of your system
1724 configuration in order to create a crash-safe configuration or
1725 achieve optimal performance.
1726 These aspects are discussed in <xref linkend="wal-reliability">.
1727 This parameter can only be set in the <filename>postgresql.conf</>
1728 file or on the server command line.
1733 <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
1735 <primary><varname>full_page_writes</> configuration parameter</primary>
1737 <term><varname>full_page_writes</varname> (<type>boolean</type>)</term>
1740 When this parameter is on, the <productname>PostgreSQL</> server
1741 writes the entire content of each disk page to WAL during the
1742 first modification of that page after a checkpoint.
1743 This is needed because
1744 a page write that is in process during an operating system crash might
1745 be only partially completed, leading to an on-disk page
1746 that contains a mix of old and new data. The row-level change data
1747 normally stored in WAL will not be enough to completely restore
1748 such a page during post-crash recovery. Storing the full page image
1749 guarantees that the page can be correctly restored, but at the price
1750 of increasing the amount of data that must be written to WAL.
1751 (Because WAL replay always starts from a checkpoint, it is sufficient
1752 to do this during the first change of each page after a checkpoint.
1753 Therefore, one way to reduce the cost of full-page writes is to
1754 increase the checkpoint interval parameters.)
1758 Turning this parameter off speeds normal operation, but
1759 might lead to either unrecoverable data corruption, or silent
1760 data corruption, after a system failure. The risks are similar to turning off
1761 <varname>fsync</varname>, though smaller, and it should be turned off
1762 only based on the same circumstances recommended for that parameter.
1766 Turning off this parameter does not affect use of
1767 WAL archiving for point-in-time recovery (PITR)
1768 (see <xref linkend="continuous-archiving">).
1772 This parameter can only be set in the <filename>postgresql.conf</>
1773 file or on the server command line.
1774 The default is <literal>on</>.
1779 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1780 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1782 <primary><varname>wal_buffers</> configuration parameter</primary>
1786 The amount of shared memory used for WAL data that has not yet been
1787 written to disk. The default setting of -1 selects a size equal to
1788 1/32nd (about 3%) of <xref linkend="guc-shared-buffers">, but not less
1789 than <literal>64kB</literal> nor more than the size of one WAL
1790 segment, typically <literal>16MB</literal>. This value can be set
1791 manually if the automatic choice is too large or too small,
1792 but any positive value less than <literal>32kB</literal> will be
1793 treated as <literal>32kB</literal>.
1794 This parameter can only be set at server start.
1798 The contents of the WAL buffers are written out to disk at every
1799 transaction commit, so extremely large values are unlikely to
1800 provide a significant benefit. However, setting this value to at
1801 least a few megabytes can improve write performance on a busy
1802 server where many clients are committing at once. The auto-tuning
1803 selected by the default setting of -1 should give reasonable
1804 results in most cases.
1808 Increasing this parameter might cause <productname>PostgreSQL</>
1809 to request more <systemitem class="osname">System V</> shared
1810 memory than your operating system's default configuration
1811 allows. See <xref linkend="sysvipc"> for information on how to
1812 adjust those parameters, if necessary.
1817 <varlistentry id="guc-wal-writer-delay" xreflabel="wal_writer_delay">
1818 <term><varname>wal_writer_delay</varname> (<type>integer</type>)</term>
1820 <primary><varname>wal_writer_delay</> configuration parameter</primary>
1824 Specifies the delay between activity rounds for the WAL writer.
1825 In each round the writer will flush WAL to disk. It then sleeps for
1826 <varname>wal_writer_delay</> milliseconds, and repeats. The default
1827 value is 200 milliseconds (<literal>200ms</>). Note that on many
1828 systems, the effective resolution of sleep delays is 10 milliseconds;
1829 setting <varname>wal_writer_delay</> to a value that is not a multiple
1830 of 10 might have the same results as setting it to the next higher
1831 multiple of 10. This parameter can only be set in the
1832 <filename>postgresql.conf</> file or on the server command line.
1837 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
1838 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1840 <primary><varname>commit_delay</> configuration parameter</primary>
1844 When the commit data for a transaction is flushed to disk, any
1845 additional commits ready at that time are also flushed out.
1846 <varname>commit_delay</varname> adds a time delay, set in
1847 microseconds, before a transaction attempts to
1848 flush the WAL buffer out to disk. A nonzero delay can allow more
1849 transactions to be committed with only one flush operation, if
1850 system load is high enough that additional transactions become
1851 ready to commit within the given interval. But the delay is
1852 just wasted if no other transactions become ready to
1853 commit. Therefore, the delay is only performed if at least
1854 <varname>commit_siblings</varname> other transactions are
1855 active at the instant that a server process has written its
1857 The default <varname>commit_delay</> is zero (no delay).
1858 Since all pending commit data will be written at every flush
1859 regardless of this setting, it is rare that adding delay
1860 by increasing this parameter will actually improve performance.
1865 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
1866 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1868 <primary><varname>commit_siblings</> configuration parameter</primary>
1872 Minimum number of concurrent open transactions to require
1873 before performing the <varname>commit_delay</> delay. A larger
1874 value makes it more probable that at least one other
1875 transaction will become ready to commit during the delay
1876 interval. The default is five transactions.
1883 <sect2 id="runtime-config-wal-checkpoints">
1884 <title>Checkpoints</title>
1887 <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1888 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1890 <primary><varname>checkpoint_segments</> configuration parameter</primary>
1894 Maximum number of log file segments between automatic WAL
1895 checkpoints (each segment is normally 16 megabytes). The default
1896 is three segments. Increasing this parameter can increase the
1897 amount of time needed for crash recovery.
1898 This parameter can only be set in the <filename>postgresql.conf</>
1899 file or on the server command line.
1904 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1905 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1907 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
1911 Maximum time between automatic WAL checkpoints, in
1912 seconds. The default is five minutes (<literal>5min</>).
1913 Increasing this parameter can increase the amount of time needed
1915 This parameter can only be set in the <filename>postgresql.conf</>
1916 file or on the server command line.
1921 <varlistentry id="guc-checkpoint-completion-target" xreflabel="checkpoint_completion_target">
1922 <term><varname>checkpoint_completion_target</varname> (<type>floating point</type>)</term>
1924 <primary><varname>checkpoint_completion_target</> configuration parameter</primary>
1928 Specifies the target of checkpoint completion, as a fraction of
1929 total time between checkpoints. The default is 0.5.
1931 This parameter can only be set in the <filename>postgresql.conf</>
1932 file or on the server command line.
1937 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1938 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1940 <primary><varname>checkpoint_warning</> configuration parameter</primary>
1944 Write a message to the server log if checkpoints caused by
1945 the filling of checkpoint segment files happen closer together
1946 than this many seconds (which suggests that
1947 <varname>checkpoint_segments</> ought to be raised). The default is
1948 30 seconds (<literal>30s</>). Zero disables the warning.
1949 This parameter can only be set in the <filename>postgresql.conf</>
1950 file or on the server command line.
1957 <sect2 id="runtime-config-wal-archiving">
1958 <title>Archiving</title>
1961 <varlistentry id="guc-archive-mode" xreflabel="archive_mode">
1962 <term><varname>archive_mode</varname> (<type>boolean</type>)</term>
1964 <primary><varname>archive_mode</> configuration parameter</primary>
1968 When <varname>archive_mode</> is enabled, completed WAL segments
1969 are sent to archive storage by setting
1970 <xref linkend="guc-archive-command">.
1971 <varname>archive_mode</> and <varname>archive_command</> are
1972 separate variables so that <varname>archive_command</> can be
1973 changed without leaving archiving mode.
1974 This parameter can only be set at server start.
1975 <varname>archive_mode</> cannot be enabled when
1976 <varname>wal_level</> is set to <literal>minimal</>.
1981 <varlistentry id="guc-archive-command" xreflabel="archive_command">
1982 <term><varname>archive_command</varname> (<type>string</type>)</term>
1984 <primary><varname>archive_command</> configuration parameter</primary>
1988 The shell command to execute to archive a completed WAL file
1989 segment. Any <literal>%p</> in the string is
1990 replaced by the path name of the file to archive, and any
1991 <literal>%f</> is replaced by only the file name.
1992 (The path name is relative to the working directory of the server,
1993 i.e., the cluster's data directory.)
1994 Use <literal>%%</> to embed an actual <literal>%</> character in the
1995 command. It is important for the command to return a zero
1996 exit status only if it succeeds. For more information see
1997 <xref linkend="backup-archiving-wal">.
2000 This parameter can only be set in the <filename>postgresql.conf</>
2001 file or on the server command line. It is ignored unless
2002 <varname>archive_mode</> was enabled at server start.
2003 If <varname>archive_command</> is an empty string (the default) while
2004 <varname>archive_mode</> is enabled, WAL archiving is temporarily
2005 disabled, but the server continues to accumulate WAL segment files in
2006 the expectation that a command will soon be provided. Setting
2007 <varname>archive_command</> to a command that does nothing but
2008 return true, e.g. <literal>/bin/true</> (<literal>REM</> on
2009 Windows), effectively disables
2010 archiving, but also breaks the chain of WAL files needed for
2011 archive recovery, so it should only be used in unusual circumstances.
2016 <varlistentry id="guc-archive-timeout" xreflabel="archive_timeout">
2017 <term><varname>archive_timeout</varname> (<type>integer</type>)</term>
2019 <primary><varname>archive_timeout</> configuration parameter</primary>
2023 The <xref linkend="guc-archive-command"> is only invoked for
2024 completed WAL segments. Hence, if your server generates little WAL
2025 traffic (or has slack periods where it does so), there could be a
2026 long delay between the completion of a transaction and its safe
2027 recording in archive storage. To limit how old unarchived
2028 data can be, you can set <varname>archive_timeout</> to force the
2029 server to switch to a new WAL segment file periodically. When this
2030 parameter is greater than zero, the server will switch to a new
2031 segment file whenever this many seconds have elapsed since the last
2032 segment file switch, and there has been any database activity,
2033 including a single checkpoint. (Increasing
2034 <varname>checkpoint_timeout</> will reduce unnecessary
2035 checkpoints on an idle system.)
2036 Note that archived files that are closed early
2037 due to a forced switch are still the same length as completely full
2038 files. Therefore, it is unwise to use a very short
2039 <varname>archive_timeout</> — it will bloat your archive
2040 storage. <varname>archive_timeout</> settings of a minute or so are
2041 usually reasonable. You should consider using streaming replication,
2042 instead of archiving, if you want data to be copied off the master
2043 server more quickly than that.
2044 This parameter can only be set in the
2045 <filename>postgresql.conf</> file or on the server command line.
2055 <sect1 id="runtime-config-replication">
2056 <title>Replication</title>
2059 These settings control the behavior of the built-in
2060 <firstterm>streaming replication</> feature (see
2061 <xref linkend="streaming-replication">). Servers will be either a
2062 Master or a Standby server. Masters can send data, while Standby(s)
2063 are always receivers of replicated data. When cascading replication
2064 (see <xref linkend="cascading-replication">) is used, Standby server(s)
2065 can also be senders, as well as receivers.
2066 Parameters are mainly for Sending and Standby servers, though some
2067 parameters have meaning only on the Master server. Settings may vary
2068 across the cluster without problems if that is required.
2071 <sect2 id="runtime-config-replication-sender">
2072 <title>Sending Server(s)</title>
2075 These parameters can be set on any server that is
2076 to send replication data to one or more standby servers.
2077 The master is always a sending server, so these parameters must
2078 always be set on the master.
2079 The role and meaning of these parameters does not change after a
2080 standby becomes the master.
2084 <varlistentry id="guc-max-wal-senders" xreflabel="max_wal_senders">
2085 <term><varname>max_wal_senders</varname> (<type>integer</type>)</term>
2087 <primary><varname>max_wal_senders</> configuration parameter</primary>
2091 Specifies the maximum number of concurrent connections from standby
2092 servers or streaming base backup clients (i.e., the maximum number of
2093 simultaneously running WAL sender
2094 processes). The default is zero. This parameter can only be set at
2095 server start. <varname>wal_level</> must be set to <literal>archive</>
2096 or <literal>hot_standby</> to allow connections from standby servers.
2101 <varlistentry id="guc-wal-keep-segments" xreflabel="wal_keep_segments">
2102 <term><varname>wal_keep_segments</varname> (<type>integer</type>)</term>
2104 <primary><varname>wal_keep_segments</> configuration parameter</primary>
2108 Specifies the minimum number of past log file segments kept in the
2109 <filename>pg_xlog</>
2110 directory, in case a standby server needs to fetch them for streaming
2111 replication. Each segment is normally 16 megabytes. If a standby
2112 server connected to the sending server falls behind by more than
2113 <varname>wal_keep_segments</> segments, the sending server might remove
2114 a WAL segment still needed by the standby, in which case the
2115 replication connection will be terminated. Downstream connections
2116 will also eventually fail as a result. (However, the standby
2117 server can recover by fetching the segment from archive, if WAL
2118 archiving is in use.)
2122 This sets only the minimum number of segments retained in
2123 <filename>pg_xlog</>; the system might need to retain more segments
2124 for WAL archival or to recover from a checkpoint. If
2125 <varname>wal_keep_segments</> is zero (the default), the system
2126 doesn't keep any extra segments for standby purposes, so the number
2127 of old WAL segments available to standby servers is a function of
2128 the location of the previous checkpoint and status of WAL
2130 This parameter can only be set in the
2131 <filename>postgresql.conf</> file or on the server command line.
2136 <varlistentry id="guc-replication-timeout" xreflabel="replication_timeout">
2137 <term><varname>replication_timeout</varname> (<type>integer</type>)</term>
2139 <primary><varname>replication_timeout</> configuration parameter</primary>
2143 Terminate replication connections that are inactive longer
2144 than the specified number of milliseconds. This is useful for
2145 the sending server to detect a standby crash or network outage.
2146 A value of zero disables the timeout mechanism. This parameter
2148 the <filename>postgresql.conf</> file or on the server command line.
2149 The default value is 60 seconds.
2152 To prevent connections from being terminated prematurely,
2153 <xref linkend="guc-wal-receiver-status-interval">
2154 must be enabled on the standby, and its value must be less than the
2155 value of <varname>replication_timeout</>.
2163 <sect2 id="runtime-config-replication-master">
2164 <title>Master Server</title>
2167 These parameters can be set on the master/primary server that is
2168 to send replication data to one or more standby servers.
2169 Note that in addition to these parameters,
2170 <xref linkend="guc-wal-level"> must be set appropriately on the master
2171 server, and optionally WAL archiving can be enabled as
2172 well (see <xref linkend="runtime-config-wal-archiving">).
2173 The values of these parameters on standby servers are irrelevant,
2174 although you may wish to set them there in preparation for the
2175 possibility of a standby becoming the master.
2180 <varlistentry id="guc-synchronous-standby-names" xreflabel="synchronous_standby_names">
2181 <term><varname>synchronous_standby_names</varname> (<type>string</type>)</term>
2183 <primary><varname>synchronous_standby_names</> configuration parameter</primary>
2187 Specifies a comma-separated list of standby names that can support
2188 <firstterm>synchronous replication</>, as described in
2189 <xref linkend="synchronous-replication">.
2190 At any one time there will be at most one active synchronous standby;
2191 transactions waiting for commit will be allowed to proceed after
2192 this standby server confirms receipt of their data.
2193 The synchronous standby will be the first standby named in this list
2194 that is both currently connected and streaming data in real-time
2195 (as shown by a state of <literal>streaming</literal> in the
2196 <link linkend="monitoring-stats-views-table">
2197 <literal>pg_stat_replication</></link> view).
2198 Other standby servers appearing later in this list represent potential
2199 synchronous standbys.
2200 If the current synchronous standby disconnects for whatever reason,
2201 it will be replaced immediately with the next-highest-priority standby.
2202 Specifying more than one standby name can allow very high availability.
2205 The name of a standby server for this purpose is the
2206 <varname>application_name</> setting of the standby, as set in the
2207 <varname>primary_conninfo</> of the standby's walreceiver. There is
2208 no mechanism to enforce uniqueness. In case of duplicates one of the
2209 matching standbys will be chosen to be the synchronous standby, though
2210 exactly which one is indeterminate.
2211 The special entry <literal>*</> matches any
2212 <varname>application_name</>, including the default application name
2213 of <literal>walreceiver</>.
2216 If no synchronous standby names are specified here, then synchronous
2217 replication is not enabled and transaction commits will not wait for
2218 replication. This is the default configuration. Even when
2219 synchronous replication is enabled, individual transactions can be
2220 configured not to wait for replication by setting the
2221 <xref linkend="guc-synchronous-commit"> parameter to
2222 <literal>local</> or <literal>off</>.
2225 This parameter can only be set in the <filename>postgresql.conf</>
2226 file or on the server command line.
2231 <varlistentry id="guc-vacuum-defer-cleanup-age" xreflabel="vacuum_defer_cleanup_age">
2232 <term><varname>vacuum_defer_cleanup_age</varname> (<type>integer</type>)</term>
2234 <primary><varname>vacuum_defer_cleanup_age</> configuration parameter</primary>
2238 Specifies the number of transactions by which <command>VACUUM</> and
2239 <acronym>HOT</> updates will defer cleanup of dead row versions. The
2240 default is zero transactions, meaning that dead row versions can be
2241 removed as soon as possible, that is, as soon as they are no longer
2242 visible to any open transaction. You may wish to set this to a
2243 non-zero value on a primary server that is supporting hot standby
2244 servers, as described in <xref linkend="hot-standby">. This allows
2245 more time for queries on the standby to complete without incurring
2246 conflicts due to early cleanup of rows. However, since the value
2247 is measured in terms of number of write transactions occurring on the
2248 primary server, it is difficult to predict just how much additional
2249 grace time will be made available to standby queries.
2250 This parameter can only be set in the <filename>postgresql.conf</>
2251 file or on the server command line.
2254 You should also consider setting <varname>hot_standby_feedback</>
2255 on standby server(s) as an alternative to using this parameter.
2263 <sect2 id="runtime-config-replication-standby">
2264 <title>Standby Servers</title>
2267 These settings control the behavior of a standby server that is
2268 to receive replication data. Their values on the master server
2274 <varlistentry id="guc-hot-standby" xreflabel="hot_standby">
2275 <term><varname>hot_standby</varname> (<type>boolean</type>)</term>
2277 <primary><varname>hot_standby</> configuration parameter</primary>
2281 Specifies whether or not you can connect and run queries during
2282 recovery, as described in <xref linkend="hot-standby">.
2283 The default value is <literal>off</literal>.
2284 This parameter can only be set at server start. It only has effect
2285 during archive recovery or in standby mode.
2290 <varlistentry id="guc-max-standby-archive-delay" xreflabel="max_standby_archive_delay">
2291 <term><varname>max_standby_archive_delay</varname> (<type>integer</type>)</term>
2293 <primary><varname>max_standby_archive_delay</> configuration parameter</primary>
2297 When Hot Standby is active, this parameter determines how long the
2298 standby server should wait before canceling standby queries that
2299 conflict with about-to-be-applied WAL entries, as described in
2300 <xref linkend="hot-standby-conflict">.
2301 <varname>max_standby_archive_delay</> applies when WAL data is
2302 being read from WAL archive (and is therefore not current).
2303 The default is 30 seconds. Units are milliseconds if not specified.
2304 A value of -1 allows the standby to wait forever for conflicting
2305 queries to complete.
2306 This parameter can only be set in the <filename>postgresql.conf</>
2307 file or on the server command line.
2310 Note that <varname>max_standby_archive_delay</> is not the same as the
2311 maximum length of time a query can run before cancellation; rather it
2312 is the maximum total time allowed to apply any one WAL segment's data.
2313 Thus, if one query has resulted in significant delay earlier in the
2314 WAL segment, subsequent conflicting queries will have much less grace
2320 <varlistentry id="guc-max-standby-streaming-delay" xreflabel="max_standby_streaming_delay">
2321 <term><varname>max_standby_streaming_delay</varname> (<type>integer</type>)</term>
2323 <primary><varname>max_standby_streaming_delay</> configuration parameter</primary>
2327 When Hot Standby is active, this parameter determines how long the
2328 standby server should wait before canceling standby queries that
2329 conflict with about-to-be-applied WAL entries, as described in
2330 <xref linkend="hot-standby-conflict">.
2331 <varname>max_standby_streaming_delay</> applies when WAL data is
2332 being received via streaming replication.
2333 The default is 30 seconds. Units are milliseconds if not specified.
2334 A value of -1 allows the standby to wait forever for conflicting
2335 queries to complete.
2336 This parameter can only be set in the <filename>postgresql.conf</>
2337 file or on the server command line.
2340 Note that <varname>max_standby_streaming_delay</> is not the same as
2341 the maximum length of time a query can run before cancellation; rather
2342 it is the maximum total time allowed to apply WAL data once it has
2343 been received from the primary server. Thus, if one query has
2344 resulted in significant delay, subsequent conflicting queries will
2345 have much less grace time until the standby server has caught up
2351 <varlistentry id="guc-wal-receiver-status-interval" xreflabel="wal_receiver_status_interval">
2352 <term><varname>wal_receiver_status_interval</varname> (<type>integer</type>)</term>
2354 <primary><varname>wal_receiver_status_interval</> configuration parameter</primary>
2358 Specifies the minimum frequency for the WAL receiver
2359 process on the standby to send information about replication progress
2360 to the primary or upstream standby, where it can be seen using the
2361 <link linkend="monitoring-stats-views-table">
2362 <literal>pg_stat_replication</></link> view. The standby will report
2363 the last transaction log position it has written, the last position it
2364 has flushed to disk, and the last position it has applied.
2366 value is the maximum interval, in seconds, between reports. Updates are
2367 sent each time the write or flush positions change, or at least as
2368 often as specified by this parameter. Thus, the apply position may
2369 lag slightly behind the true position. Setting this parameter to zero
2370 disables status updates completely. This parameter can only be set in
2371 the <filename>postgresql.conf</> file or on the server command line.
2372 The default value is 10 seconds.
2375 When <xref linkend="guc-replication-timeout"> is enabled on a sending server,
2376 <varname>wal_receiver_status_interval</> must be enabled, and its value
2377 must be less than the value of <varname>replication_timeout</>.
2382 <varlistentry id="guc-hot-standby-feedback" xreflabel="hot_standby">
2383 <term><varname>hot_standby_feedback</varname> (<type>boolean</type>)</term>
2385 <primary><varname>hot_standby_feedback</> configuration parameter</primary>
2389 Specifies whether or not a hot standby will send feedback to the primary
2391 about queries currently executing on the standby. This parameter can
2392 be used to eliminate query cancels caused by cleanup records, but
2393 can cause database bloat on the primary for some workloads.
2394 Feedback messages will not be sent more frequently than once per
2395 <varname>wal_receiver_status_interval</>. The default value is
2396 <literal>off</literal>. This parameter can only be set in the
2397 <filename>postgresql.conf</> file or on the server command line.
2400 If cascaded replication is in use the feedback is passed upstream
2401 until it eventually reaches the primary. Standbys make no other use
2402 of feedback they receive other than to pass upstream.
2411 <sect1 id="runtime-config-query">
2412 <title>Query Planning</title>
2414 <sect2 id="runtime-config-query-enable">
2415 <title>Planner Method Configuration</title>
2418 These configuration parameters provide a crude method of
2419 influencing the query plans chosen by the query optimizer. If
2420 the default plan chosen by the optimizer for a particular query
2421 is not optimal, a <emphasis>temporary</> solution is to use one
2422 of these configuration parameters to force the optimizer to
2423 choose a different plan.
2424 Better ways to improve the quality of the
2425 plans chosen by the optimizer include adjusting the planer cost
2426 constants (see <xref linkend="runtime-config-query-constants">),
2427 running <xref linkend="sql-analyze"> manually, increasing
2428 the value of the <xref
2429 linkend="guc-default-statistics-target"> configuration parameter,
2430 and increasing the amount of statistics collected for
2431 specific columns using <command>ALTER TABLE SET
2432 STATISTICS</command>.
2436 <varlistentry id="guc-enable-bitmapscan" xreflabel="enable_bitmapscan">
2437 <term><varname>enable_bitmapscan</varname> (<type>boolean</type>)</term>
2439 <primary>bitmap scan</primary>
2442 <primary><varname>enable_bitmapscan</> configuration parameter</primary>
2446 Enables or disables the query planner's use of bitmap-scan plan
2447 types. The default is <literal>on</>.
2452 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
2453 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
2455 <primary><varname>enable_hashagg</> configuration parameter</primary>
2459 Enables or disables the query planner's use of hashed
2460 aggregation plan types. The default is <literal>on</>.
2465 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
2466 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
2468 <primary><varname>enable_hashjoin</> configuration parameter</primary>
2472 Enables or disables the query planner's use of hash-join plan
2473 types. The default is <literal>on</>.
2478 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
2479 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
2481 <primary>index scan</primary>
2484 <primary><varname>enable_indexscan</> configuration parameter</primary>
2488 Enables or disables the query planner's use of index-scan plan
2489 types. The default is <literal>on</>.
2494 <varlistentry id="guc-enable-indexonlyscan" xreflabel="enable_indexonlyscan">
2495 <term><varname>enable_indexonlyscan</varname> (<type>boolean</type>)</term>
2497 <primary>index-only scan</primary>
2500 <primary><varname>enable_indexonlyscan</> configuration parameter</primary>
2504 Enables or disables the query planner's use of index-only-scan plan
2505 types. The default is <literal>on</>.
2510 <varlistentry id="guc-enable-material" xreflabel="enable_material">
2511 <term><varname>enable_material</varname> (<type>boolean</type>)</term>
2513 <primary><varname>enable_material</> configuration parameter</primary>
2517 Enables or disables the query planner's use of materialization.
2518 It is impossible to suppress materialization entirely,
2519 but turning this variable off prevents the planner from inserting
2520 materialize nodes except in cases where it is required for correctness.
2521 The default is <literal>on</>.
2526 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
2527 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
2529 <primary><varname>enable_mergejoin</> configuration parameter</primary>
2533 Enables or disables the query planner's use of merge-join plan
2534 types. The default is <literal>on</>.
2539 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
2540 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
2542 <primary><varname>enable_nestloop</> configuration parameter</primary>
2546 Enables or disables the query planner's use of nested-loop join
2547 plans. It is impossible to suppress nested-loop joins entirely,
2548 but turning this variable off discourages the planner from using
2549 one if there are other methods available. The default is
2555 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
2556 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
2558 <primary>sequential scan</primary>
2561 <primary><varname>enable_seqscan</> configuration parameter</primary>
2565 Enables or disables the query planner's use of sequential scan
2566 plan types. It is impossible to suppress sequential scans
2567 entirely, but turning this variable off discourages the planner
2568 from using one if there are other methods available. The
2569 default is <literal>on</>.
2574 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
2575 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
2577 <primary><varname>enable_sort</> configuration parameter</primary>
2581 Enables or disables the query planner's use of explicit sort
2582 steps. It is impossible to suppress explicit sorts entirely,
2583 but turning this variable off discourages the planner from
2584 using one if there are other methods available. The default
2590 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
2591 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
2593 <primary><varname>enable_tidscan</> configuration parameter</primary>
2597 Enables or disables the query planner's use of <acronym>TID</>
2598 scan plan types. The default is <literal>on</>.
2605 <sect2 id="runtime-config-query-constants">
2606 <title>Planner Cost Constants</title>
2609 The <firstterm>cost</> variables described in this section are measured
2610 on an arbitrary scale. Only their relative values matter, hence
2611 scaling them all up or down by the same factor will result in no change
2612 in the planner's choices. By default, these cost variables are based on
2613 the cost of sequential page fetches; that is,
2614 <varname>seq_page_cost</> is conventionally set to <literal>1.0</>
2615 and the other cost variables are set with reference to that. But
2616 you can use a different scale if you prefer, such as actual execution
2617 times in milliseconds on a particular machine.
2622 Unfortunately, there is no well-defined method for determining ideal
2623 values for the cost variables. They are best treated as averages over
2624 the entire mix of queries that a particular installation will receive. This
2625 means that changing them on the basis of just a few experiments is very
2632 <varlistentry id="guc-seq-page-cost" xreflabel="seq_page_cost">
2633 <term><varname>seq_page_cost</varname> (<type>floating point</type>)</term>
2635 <primary><varname>seq_page_cost</> configuration parameter</primary>
2639 Sets the planner's estimate of the cost of a disk page fetch
2640 that is part of a series of sequential fetches. The default is 1.0.
2641 This value can be overridden for tables and indexes in a particular
2642 tablespace by setting the tablespace parameter of the same name
2643 (see <xref linkend="sql-altertablespace">).
2648 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
2649 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
2651 <primary><varname>random_page_cost</> configuration parameter</primary>
2655 Sets the planner's estimate of the cost of a
2656 non-sequentially-fetched disk page. The default is 4.0.
2657 This value can be overridden for tables and indexes in a particular
2658 tablespace by setting the tablespace parameter of the same name
2659 (see <xref linkend="sql-altertablespace">).
2663 Reducing this value relative to <varname>seq_page_cost</>
2664 will cause the system to prefer index scans; raising it will
2665 make index scans look relatively more expensive. You can raise
2666 or lower both values together to change the importance of disk I/O
2667 costs relative to CPU costs, which are described by the following
2672 Random access to mechanical disk storage is normally much more expensive
2673 than four-times sequential access. However, a lower default is used
2674 (4.0) because the majority of random accesses to disk, such as indexed
2675 reads, are assumed to be in cache. The default value can be thought of
2676 as modeling random access as 40 times slower than sequential, while
2677 expecting 90% of random reads to be cached.
2681 If you believe a 90% cache rate is an incorrect assumption
2682 for your workload, you can increase random_page_cost to better
2683 reflect the true cost of random storage reads. Correspondingly,
2684 if your data is likely to be completely in cache, such as when
2685 the database is smaller than the total server memory, decreasing
2686 random_page_cost can be appropriate. Storage that has a low random
2687 read cost relative to sequential, e.g. solid-state drives, might
2688 also be better modeled with a lower value for random_page_cost.
2693 Although the system will let you set <varname>random_page_cost</> to
2694 less than <varname>seq_page_cost</>, it is not physically sensible
2695 to do so. However, setting them equal makes sense if the database
2696 is entirely cached in RAM, since in that case there is no penalty
2697 for touching pages out of sequence. Also, in a heavily-cached
2698 database you should lower both values relative to the CPU parameters,
2699 since the cost of fetching a page already in RAM is much smaller
2700 than it would normally be.
2706 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
2707 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
2709 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
2713 Sets the planner's estimate of the cost of processing
2714 each row during a query.
2715 The default is 0.01.
2720 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
2721 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
2723 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
2727 Sets the planner's estimate of the cost of processing
2728 each index entry during an index scan.
2729 The default is 0.005.
2734 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
2735 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
2737 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
2741 Sets the planner's estimate of the cost of processing each
2742 operator or function executed during a query.
2743 The default is 0.0025.
2748 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
2749 <term><varname>effective_cache_size</varname> (<type>integer</type>)</term>
2751 <primary><varname>effective_cache_size</> configuration parameter</primary>
2755 Sets the planner's assumption about the effective size of the
2756 disk cache that is available to a single query. This is
2757 factored into estimates of the cost of using an index; a
2758 higher value makes it more likely index scans will be used, a
2759 lower value makes it more likely sequential scans will be
2760 used. When setting this parameter you should consider both
2761 <productname>PostgreSQL</productname>'s shared buffers and the
2762 portion of the kernel's disk cache that will be used for
2763 <productname>PostgreSQL</productname> data files. Also, take
2764 into account the expected number of concurrent queries on different
2765 tables, since they will have to share the available
2766 space. This parameter has no effect on the size of shared
2767 memory allocated by <productname>PostgreSQL</productname>, nor
2768 does it reserve kernel disk cache; it is used only for estimation
2769 purposes. The system also does not assume data remains in
2770 the disk cache between queries. The default is 128 megabytes
2771 (<literal>128MB</>).
2779 <sect2 id="runtime-config-query-geqo">
2780 <title>Genetic Query Optimizer</title>
2783 The genetic query optimizer (GEQO) is an algorithm that does query
2784 planning using heuristic searching. This reduces planning time for
2785 complex queries (those joining many relations), at the cost of producing
2786 plans that are sometimes inferior to those found by the normal
2787 exhaustive-search algorithm.
2788 For more information see <xref linkend="geqo">.
2793 <varlistentry id="guc-geqo" xreflabel="geqo">
2795 <primary>genetic query optimization</primary>
2798 <primary>GEQO</primary>
2799 <see>genetic query optimization</see>
2802 <primary><varname>geqo</> configuration parameter</primary>
2804 <term><varname>geqo</varname> (<type>boolean</type>)</term>
2807 Enables or disables genetic query optimization.
2808 This is on by default. It is usually best not to turn it off in
2809 production; the <varname>geqo_threshold</varname> variable provides
2810 more granular control of GEQO.
2815 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
2816 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
2818 <primary><varname>geqo_threshold</> configuration parameter</primary>
2822 Use genetic query optimization to plan queries with at least
2823 this many <literal>FROM</> items involved. (Note that a
2824 <literal>FULL OUTER JOIN</> construct counts as only one <literal>FROM</>
2825 item.) The default is 12. For simpler queries it is usually best
2826 to use the regular, exhaustive-search planner, but for queries with
2827 many tables the exhaustive search takes too long, often
2828 longer than the penalty of executing a suboptimal plan. Thus,
2829 a threshold on the size of the query is a convenient way to manage
2835 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
2836 <term><varname>geqo_effort</varname>
2837 (<type>integer</type>)</term>
2839 <primary><varname>geqo_effort</> configuration parameter</primary>
2843 Controls the trade-off between planning time and query plan
2844 quality in GEQO. This variable must be an integer in the
2845 range from 1 to 10. The default value is five. Larger values
2846 increase the time spent doing query planning, but also
2847 increase the likelihood that an efficient query plan will be
2852 <varname>geqo_effort</varname> doesn't actually do anything
2853 directly; it is only used to compute the default values for
2854 the other variables that influence GEQO behavior (described
2855 below). If you prefer, you can set the other parameters by
2861 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
2862 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
2864 <primary><varname>geqo_pool_size</> configuration parameter</primary>
2868 Controls the pool size used by GEQO, that is the
2869 number of individuals in the genetic population. It must be
2870 at least two, and useful values are typically 100 to 1000. If
2871 it is set to zero (the default setting) then a suitable
2872 value is chosen based on <varname>geqo_effort</varname> and
2873 the number of tables in the query.
2878 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
2879 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
2881 <primary><varname>geqo_generations</> configuration parameter</primary>
2885 Controls the number of generations used by GEQO, that is
2886 the number of iterations of the algorithm. It must
2887 be at least one, and useful values are in the same range as
2888 the pool size. If it is set to zero (the default setting)
2889 then a suitable value is chosen based on
2890 <varname>geqo_pool_size</varname>.
2895 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
2896 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
2898 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
2902 Controls the selection bias used by GEQO. The selection bias
2903 is the selective pressure within the population. Values can be
2904 from 1.50 to 2.00; the latter is the default.
2909 <varlistentry id="guc-geqo-seed" xreflabel="geqo_seed">
2910 <term><varname>geqo_seed</varname> (<type>floating point</type>)</term>
2912 <primary><varname>geqo_seed</> configuration parameter</primary>
2916 Controls the initial value of the random number generator used
2917 by GEQO to select random paths through the join order search space.
2918 The value can range from zero (the default) to one. Varying the
2919 value changes the set of join paths explored, and may result in a
2920 better or worse best path being found.
2927 <sect2 id="runtime-config-query-other">
2928 <title>Other Planner Options</title>
2932 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
2933 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
2935 <primary><varname>default_statistics_target</> configuration parameter</primary>
2939 Sets the default statistics target for table columns without
2940 a column-specific target set via <command>ALTER TABLE
2941 SET STATISTICS</>. Larger values increase the time needed to
2942 do <command>ANALYZE</>, but might improve the quality of the
2943 planner's estimates. The default is 100. For more information
2944 on the use of statistics by the <productname>PostgreSQL</>
2945 query planner, refer to <xref linkend="planner-stats">.
2950 <varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
2951 <term><varname>constraint_exclusion</varname> (<type>enum</type>)</term>
2953 <primary>constraint exclusion</primary>
2956 <primary><varname>constraint_exclusion</> configuration parameter</primary>
2960 Controls the query planner's use of table constraints to
2962 The allowed values of <varname>constraint_exclusion</> are
2963 <literal>on</> (examine constraints for all tables),
2964 <literal>off</> (never examine constraints), and
2965 <literal>partition</> (examine constraints only for inheritance child
2966 tables and <literal>UNION ALL</> subqueries).
2967 <literal>partition</> is the default setting.
2968 It is often used with inheritance and partitioned tables to
2969 improve performance.
2973 When this parameter allows it for a particular table, the planner
2974 compares query conditions with the table's <literal>CHECK</>
2975 constraints, and omits scanning tables for which the conditions
2976 contradict the constraints. For example:
2979 CREATE TABLE parent(key integer, ...);
2980 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
2981 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
2983 SELECT * FROM parent WHERE key = 2400;
2986 With constraint exclusion enabled, this <command>SELECT</>
2987 will not scan <structname>child1000</> at all, improving performance.
2991 Currently, constraint exclusion is enabled by default
2992 only for cases that are often used to implement table partitioning.
2993 Turning it on for all tables imposes extra planning overhead that is
2994 quite noticeable on simple queries, and most often will yield no
2995 benefit for simple queries. If you have no partitioned tables
2996 you might prefer to turn it off entirely.
3000 Refer to <xref linkend="ddl-partitioning-constraint-exclusion"> for
3001 more information on using constraint exclusion and partitioning.
3006 <varlistentry id="guc-cursor-tuple-fraction" xreflabel="cursor_tuple_fraction">
3007 <term><varname>cursor_tuple_fraction</varname> (<type>floating point</type>)</term>
3009 <primary><varname>cursor_tuple_fraction</> configuration parameter</primary>
3013 Sets the planner's estimate of the fraction of a cursor's rows that
3014 will be retrieved. The default is 0.1. Smaller values of this
3015 setting bias the planner towards using <quote>fast start</> plans
3016 for cursors, which will retrieve the first few rows quickly while
3017 perhaps taking a long time to fetch all rows. Larger values
3018 put more emphasis on the total estimated time. At the maximum
3019 setting of 1.0, cursors are planned exactly like regular queries,
3020 considering only the total estimated time and not how soon the
3021 first rows might be delivered.
3026 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
3027 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
3029 <primary><varname>from_collapse_limit</> configuration parameter</primary>
3033 The planner will merge sub-queries into upper queries if the
3034 resulting <literal>FROM</literal> list would have no more than
3035 this many items. Smaller values reduce planning time but might
3036 yield inferior query plans. The default is eight.
3037 For more information see <xref linkend="explicit-joins">.
3041 Setting this value to <xref linkend="guc-geqo-threshold"> or more
3042 may trigger use of the GEQO planner, resulting in non-optimal
3043 plans. See <xref linkend="runtime-config-query-geqo">.
3048 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
3049 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
3051 <primary><varname>join_collapse_limit</> configuration parameter</primary>
3055 The planner will rewrite explicit <literal>JOIN</>
3056 constructs (except <literal>FULL JOIN</>s) into lists of
3057 <literal>FROM</> items whenever a list of no more than this many items
3058 would result. Smaller values reduce planning time but might
3059 yield inferior query plans.
3063 By default, this variable is set the same as
3064 <varname>from_collapse_limit</varname>, which is appropriate
3065 for most uses. Setting it to 1 prevents any reordering of
3066 explicit <literal>JOIN</>s. Thus, the explicit join order
3067 specified in the query will be the actual order in which the
3068 relations are joined. Because the query planner does not always choose
3069 the optimal join order, advanced users can elect to
3070 temporarily set this variable to 1, and then specify the join
3071 order they desire explicitly.
3072 For more information see <xref linkend="explicit-joins">.
3076 Setting this value to <xref linkend="guc-geqo-threshold"> or more
3077 may trigger use of the GEQO planner, resulting in non-optimal
3078 plans. See <xref linkend="runtime-config-query-geqo">.
3087 <sect1 id="runtime-config-logging">
3088 <title>Error Reporting and Logging</title>
3090 <indexterm zone="runtime-config-logging">
3091 <primary>server log</primary>
3094 <sect2 id="runtime-config-logging-where">
3095 <title>Where To Log</title>
3097 <indexterm zone="runtime-config-logging-where">
3098 <primary>where to log</primary>
3103 <varlistentry id="guc-log-destination" xreflabel="log_destination">
3104 <term><varname>log_destination</varname> (<type>string</type>)</term>
3106 <primary><varname>log_destination</> configuration parameter</primary>
3110 <productname>PostgreSQL</productname> supports several methods
3111 for logging server messages, including
3112 <systemitem>stderr</systemitem>, <systemitem>csvlog</systemitem> and
3113 <systemitem>syslog</systemitem>. On Windows,
3114 <systemitem>eventlog</systemitem> is also supported. Set this
3115 parameter to a list of desired log destinations separated by
3116 commas. The default is to log to <systemitem>stderr</systemitem>
3118 This parameter can only be set in the <filename>postgresql.conf</>
3119 file or on the server command line.
3122 If <systemitem>csvlog</> is included in <varname>log_destination</>,
3123 log entries are output in <quote>comma separated
3124 value</> (<acronym>CSV</>) format, which is convenient for
3125 loading logs into programs.
3126 See <xref linkend="runtime-config-logging-csvlog"> for details.
3127 <xref linkend="guc-logging-collector"> must be enabled to generate
3128 CSV-format log output.
3133 On most Unix systems, you will need to alter the configuration of
3134 your system's <application>syslog</application> daemon in order
3135 to make use of the <systemitem>syslog</systemitem> option for
3136 <varname>log_destination</>. <productname>PostgreSQL</productname>
3137 can log to <application>syslog</application> facilities
3138 <literal>LOCAL0</> through <literal>LOCAL7</> (see <xref
3139 linkend="guc-syslog-facility">), but the default
3140 <application>syslog</application> configuration on most platforms
3141 will discard all such messages. You will need to add something like:
3143 local0.* /var/log/postgresql
3145 to the <application>syslog</application> daemon's configuration file
3149 On Windows, when you use the <literal>eventlog</literal>
3150 option for <varname>log_destination</>, you should
3151 register an event source and its library with the operating
3152 system so that the Windows Event Viewer can display event
3153 log messages cleanly.
3154 See <xref linkend="event-log-registration"> for details.
3160 <varlistentry id="guc-logging-collector" xreflabel="logging_collector">
3161 <term><varname>logging_collector</varname> (<type>boolean</type>)</term>
3163 <primary><varname>logging_collector</> configuration parameter</primary>
3167 This parameter enables the <firstterm>logging collector</>, which
3168 is a background process that captures log messages
3169 sent to <systemitem>stderr</> and redirects them into log files.
3170 This approach is often more useful than
3171 logging to <application>syslog</>, since some types of messages
3172 might not appear in <application>syslog</> output. (One common
3173 example is dynamic-linker failure messages; another is error messages
3174 produced by scripts such as <varname>archive_command</>.)
3175 This parameter can only be set at server start.
3180 It is possible to log to <systemitem>stderr</> without using the
3181 logging collector; the log messages will just go to wherever the
3182 server's <systemitem>stderr</> is directed. However, that method is
3183 only suitable for low log volumes, since it provides no convenient
3184 way to rotate log files. Also, on some platforms not using the
3185 logging collector can result in lost or garbled log output, because
3186 multiple processes writing concurrently to the same log file can
3187 overwrite each other's output.
3193 The logging collector is designed to never lose messages. This means
3194 that in case of extremely high load, server processes could be
3195 blocked while trying to send additional log messages when the
3196 collector has fallen behind. In contrast, <application>syslog</>
3197 prefers to drop messages if it cannot write them, which means it
3198 may fail to log some messages in such cases but it will not block
3199 the rest of the system.
3206 <varlistentry id="guc-log-directory" xreflabel="log_directory">
3207 <term><varname>log_directory</varname> (<type>string</type>)</term>
3209 <primary><varname>log_directory</> configuration parameter</primary>
3213 When <varname>logging_collector</> is enabled,
3214 this parameter determines the directory in which log files will be created.
3215 It can be specified as an absolute path, or relative to the
3216 cluster data directory.
3217 This parameter can only be set in the <filename>postgresql.conf</>
3218 file or on the server command line.
3223 <varlistentry id="guc-log-filename" xreflabel="log_filename">
3224 <term><varname>log_filename</varname> (<type>string</type>)</term>
3226 <primary><varname>log_filename</> configuration parameter</primary>
3230 When <varname>logging_collector</varname> is enabled,
3231 this parameter sets the file names of the created log files. The value
3232 is treated as a <systemitem>strftime</systemitem> pattern,
3233 so <literal>%</literal>-escapes can be used to specify time-varying
3234 file names. (Note that if there are
3235 any time-zone-dependent <literal>%</literal>-escapes, the computation
3236 is done in the zone specified
3237 by <xref linkend="guc-log-timezone">.)
3238 The supported <literal>%</literal>-escapes are similar to those
3239 listed in the Open Group's <ulink
3240 url="http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html">strftime
3241 </ulink> specification.
3242 Note that the system's <systemitem>strftime</systemitem> is not used
3243 directly, so platform-specific (nonstandard) extensions do not work.
3246 If you specify a file name without escapes, you should plan to
3247 use a log rotation utility to avoid eventually filling the
3248 entire disk. In releases prior to 8.4, if
3249 no <literal>%</literal> escapes were
3250 present, <productname>PostgreSQL</productname> would append
3251 the epoch of the new log file's creation time, but this is no
3255 If CSV-format output is enabled in <varname>log_destination</>,
3256 <literal>.csv</> will be appended to the timestamped
3257 log file name to create the file name for CSV-format output.
3258 (If <varname>log_filename</> ends in <literal>.log</>, the suffix is
3260 In the case of the example above, the CSV
3261 file name will be <literal>server_log.1093827753.csv</literal>.
3264 This parameter can only be set in the <filename>postgresql.conf</>
3265 file or on the server command line.
3270 <varlistentry id="guc-log-file-mode" xreflabel="log_file_mode">
3271 <term><varname>log_file_mode</varname> (<type>integer</type>)</term>
3273 <primary><varname>log_file_mode</> configuration parameter</primary>
3277 On Unix systems this parameter sets the permissions for log files
3278 when <varname>logging_collector</varname> is enabled. (On Microsoft
3279 Windows this parameter is ignored.)
3280 The parameter value is expected to be a numeric mode
3281 specified in the format accepted by the
3282 <function>chmod</function> and <function>umask</function>
3283 system calls. (To use the customary octal format the number
3284 must start with a <literal>0</literal> (zero).)
3287 The default permissions are <literal>0600</>, meaning only the
3288 server owner can read or write the log files. The other commonly
3289 useful setting is <literal>0640</>, allowing members of the owner's
3290 group to read the files. Note however that to make use of such a
3291 setting, you'll need to alter <xref linkend="guc-log-directory"> to
3292 store the files somewhere outside the cluster data directory. In
3293 any case, it's unwise to make the log files world-readable, since
3294 they might contain sensitive data.
3297 This parameter can only be set in the <filename>postgresql.conf</>
3298 file or on the server command line.
3303 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
3304 <term><varname>log_rotation_age</varname> (<type>integer</type>)</term>
3306 <primary><varname>log_rotation_age</> configuration parameter</primary>
3310 When <varname>logging_collector</varname> is enabled,
3311 this parameter determines the maximum lifetime of an individual log file.
3312 After this many minutes have elapsed, a new log file will
3313 be created. Set to zero to disable time-based creation of
3315 This parameter can only be set in the <filename>postgresql.conf</>
3316 file or on the server command line.
3321 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
3322 <term><varname>log_rotation_size</varname> (<type>integer</type>)</term>
3324 <primary><varname>log_rotation_size</> configuration parameter</primary>
3328 When <varname>logging_collector</varname> is enabled,
3329 this parameter determines the maximum size of an individual log file.
3330 After this many kilobytes have been emitted into a log file,
3331 a new log file will be created. Set to zero to disable size-based
3332 creation of new log files.
3333 This parameter can only be set in the <filename>postgresql.conf</>
3334 file or on the server command line.
3339 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
3340 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)</term>
3342 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
3346 When <varname>logging_collector</varname> is enabled,
3347 this parameter will cause <productname>PostgreSQL</productname> to truncate (overwrite),
3348 rather than append to, any existing log file of the same name.
3349 However, truncation will occur only when a new file is being opened
3350 due to time-based rotation, not during server startup or size-based
3351 rotation. When off, pre-existing files will be appended to in
3352 all cases. For example, using this setting in combination with
3353 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
3354 would result in generating twenty-four hourly log files and then
3355 cyclically overwriting them.
3356 This parameter can only be set in the <filename>postgresql.conf</>
3357 file or on the server command line.
3360 Example: To keep 7 days of logs, one log file per day named
3361 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
3362 etc, and automatically overwrite last week's log with this week's log,
3363 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
3364 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>, and
3365 <varname>log_rotation_age</varname> to <literal>1440</literal>.
3368 Example: To keep 24 hours of logs, one log file per hour, but
3369 also rotate sooner if the log file size exceeds 1GB, set
3370 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
3371 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>,
3372 <varname>log_rotation_age</varname> to <literal>60</literal>, and
3373 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
3374 Including <literal>%M</> in <varname>log_filename</varname> allows
3375 any size-driven rotations that might occur to select a file name
3376 different from the hour's initial file name.
3381 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
3382 <term><varname>syslog_facility</varname> (<type>enum</type>)</term>
3384 <primary><varname>syslog_facility</> configuration parameter</primary>
3388 When logging to <application>syslog</> is enabled, this parameter
3389 determines the <application>syslog</application>
3390 <quote>facility</quote> to be used. You can choose
3391 from <literal>LOCAL0</>, <literal>LOCAL1</>,
3392 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
3393 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
3394 the default is <literal>LOCAL0</>. See also the
3395 documentation of your system's
3396 <application>syslog</application> daemon.
3397 This parameter can only be set in the <filename>postgresql.conf</>
3398 file or on the server command line.
3403 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
3404 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
3406 <primary><varname>syslog_identity</> configuration parameter</primary>
3410 When logging to <application>syslog</> is enabled, this parameter
3411 determines the program name used to identify
3412 <productname>PostgreSQL</productname> messages in
3413 <application>syslog</application> logs. The default is
3414 <literal>postgres</literal>.
3415 This parameter can only be set in the <filename>postgresql.conf</>
3416 file or on the server command line.
3421 <varlistentry id="guc-event-source" xreflabel="event_source">
3422 <term><varname>event_source</varname> (<type>string</type>)</term>
3424 <primary><varname>event_source</> configuration parameter</primary>
3428 When logging to <application>event log</> is enabled, this parameter
3429 determines the program name used to identify
3430 <productname>PostgreSQL</productname> messages in
3431 the log. The default is <literal>PostgreSQL</literal>.
3432 This parameter can only be set in the <filename>postgresql.conf</>
3433 file or on the server command line.
3440 <sect2 id="runtime-config-logging-when">
3441 <title>When To Log</title>
3445 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
3446 <term><varname>client_min_messages</varname> (<type>enum</type>)</term>
3448 <primary><varname>client_min_messages</> configuration parameter</primary>
3452 Controls which message levels are sent to the client.
3453 Valid values are <literal>DEBUG5</>,
3454 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
3455 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
3456 <literal>WARNING</>, <literal>ERROR</>, <literal>FATAL</>,
3457 and <literal>PANIC</>. Each level
3458 includes all the levels that follow it. The later the level,
3459 the fewer messages are sent. The default is
3460 <literal>NOTICE</>. Note that <literal>LOG</> has a different
3461 rank here than in <varname>log_min_messages</>.
3466 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
3467 <term><varname>log_min_messages</varname> (<type>enum</type>)</term>
3469 <primary><varname>log_min_messages</> configuration parameter</primary>
3473 Controls which message levels are written to the server log.
3474 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
3475 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
3476 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
3477 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
3478 <literal>PANIC</>. Each level includes all the levels that
3479 follow it. The later the level, the fewer messages are sent
3480 to the log. The default is <literal>WARNING</>. Note that
3481 <literal>LOG</> has a different rank here than in
3482 <varname>client_min_messages</>.
3483 Only superusers can change this setting.
3488 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
3489 <term><varname>log_min_error_statement</varname> (<type>enum</type>)</term>
3491 <primary><varname>log_min_error_statement</> configuration parameter</primary>
3495 Controls which SQL statements that cause an error
3496 condition are recorded in the server log. The current
3497 SQL statement is included in the log entry for any message of
3498 the specified severity or higher.
3499 Valid values are <literal>DEBUG5</literal>,
3500 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
3501 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
3502 <literal>INFO</literal>, <literal>NOTICE</literal>,
3503 <literal>WARNING</literal>, <literal>ERROR</literal>,
3504 <literal>LOG</literal>,
3505 <literal>FATAL</literal>, and <literal>PANIC</literal>.
3506 The default is <literal>ERROR</literal>, which means statements
3507 causing errors, log messages, fatal errors, or panics will be logged.
3508 To effectively turn off logging of failing statements,
3509 set this parameter to <literal>PANIC</literal>.
3510 Only superusers can change this setting.
3515 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
3516 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
3518 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
3522 Causes the duration of each completed statement to be logged
3523 if the statement ran for at least the specified number of
3524 milliseconds. Setting this to zero prints all statement durations.
3525 Minus-one (the default) disables logging statement durations.
3526 For example, if you set it to <literal>250ms</literal>
3527 then all SQL statements that run 250ms or longer will be
3528 logged. Enabling this parameter can be helpful in tracking down
3529 unoptimized queries in your applications.
3530 Only superusers can change this setting.
3534 For clients using extended query protocol, durations of the Parse,
3535 Bind, and Execute steps are logged independently.
3540 When using this option together with
3541 <xref linkend="guc-log-statement">,
3542 the text of statements that are logged because of
3543 <varname>log_statement</> will not be repeated in the
3544 duration log message.
3545 If you are not using <application>syslog</>, it is recommended
3546 that you log the PID or session ID using
3547 <xref linkend="guc-log-line-prefix">
3548 so that you can link the statement message to the later
3549 duration message using the process ID or session ID.
3558 <xref linkend="runtime-config-severity-levels"> explains the message
3559 severity levels used by <productname>PostgreSQL</>. If logging output
3560 is sent to <systemitem>syslog</systemitem> or Windows'
3561 <systemitem>eventlog</systemitem>, the severity levels are translated
3562 as shown in the table.
3565 <table id="runtime-config-severity-levels">
3566 <title>Message Severity Levels</title>
3570 <entry>Severity</entry>
3571 <entry>Usage</entry>
3572 <entry><systemitem>syslog</></entry>
3573 <entry><systemitem>eventlog</></entry>
3579 <entry><literal>DEBUG1..DEBUG5</></entry>
3580 <entry>Provides successively-more-detailed information for use by
3582 <entry><literal>DEBUG</></entry>
3583 <entry><literal>INFORMATION</></entry>
3587 <entry><literal>INFO</></entry>
3588 <entry>Provides information implicitly requested by the user,
3589 e.g., output from <command>VACUUM VERBOSE</>.</entry>
3590 <entry><literal>INFO</></entry>
3591 <entry><literal>INFORMATION</></entry>
3595 <entry><literal>NOTICE</></entry>
3596 <entry>Provides information that might be helpful to users, e.g.,
3597 notice of truncation of long identifiers.</entry>
3598 <entry><literal>NOTICE</></entry>
3599 <entry><literal>INFORMATION</></entry>
3603 <entry><literal>WARNING</></entry>
3604 <entry>Provides warnings of likely problems, e.g., <command>COMMIT</>
3605 outside a transaction block.</entry>
3606 <entry><literal>NOTICE</></entry>
3607 <entry><literal>WARNING</></entry>
3611 <entry><literal>ERROR</></entry>
3612 <entry>Reports an error that caused the current command to
3614 <entry><literal>WARNING</></entry>
3615 <entry><literal>ERROR</></entry>
3619 <entry><literal>LOG</></entry>
3620 <entry>Reports information of interest to administrators, e.g.,
3621 checkpoint activity.</entry>
3622 <entry><literal>INFO</></entry>
3623 <entry><literal>INFORMATION</></entry>
3627 <entry><literal>FATAL</></entry>
3628 <entry>Reports an error that caused the current session to
3630 <entry><literal>ERR</></entry>
3631 <entry><literal>ERROR</></entry>
3635 <entry><literal>PANIC</></entry>
3636 <entry>Reports an error that caused all database sessions to abort.</entry>
3637 <entry><literal>CRIT</></entry>
3638 <entry><literal>ERROR</></entry>
3645 <sect2 id="runtime-config-logging-what">
3646 <title>What To Log</title>
3650 <varlistentry id="guc-application-name" xreflabel="application_name">
3651 <term><varname>application_name</varname> (<type>string</type>)</term>
3653 <primary><varname>application_name</> configuration parameter</primary>
3657 The <varname>application_name</varname> can be any string of less than
3658 <symbol>NAMEDATALEN</> characters (64 characters in a standard build).
3659 It is typically set by an application upon connection to the server.
3660 The name will be displayed in the <structname>pg_stat_activity</> view
3661 and included in CSV log entries. It can also be included in regular
3662 log entries via the <xref linkend="guc-log-line-prefix"> parameter.
3663 Only printable ASCII characters may be used in the
3664 <varname>application_name</varname> value. Other characters will be
3665 replaced with question marks (<literal>?</literal>).
3671 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
3672 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
3673 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
3675 <primary><varname>debug_print_parse</> configuration parameter</primary>
3678 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
3681 <primary><varname>debug_print_plan</> configuration parameter</primary>
3685 These parameters enable various debugging output to be emitted.
3686 When set, they print the resulting parse tree, the query rewriter
3687 output, or the execution plan for each executed query.
3688 These messages are emitted at <literal>LOG</> message level, so by
3689 default they will appear in the server log but will not be sent to the
3690 client. You can change that by adjusting
3691 <xref linkend="guc-client-min-messages"> and/or
3692 <xref linkend="guc-log-min-messages">.
3693 These parameters are off by default.
3699 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
3701 <primary><varname>debug_pretty_print</> configuration parameter</primary>
3705 When set, <varname>debug_pretty_print</varname> indents the messages
3706 produced by <varname>debug_print_parse</varname>,
3707 <varname>debug_print_rewritten</varname>, or
3708 <varname>debug_print_plan</varname>. This results in more readable
3709 but much longer output than the <quote>compact</> format used when
3710 it is off. It is on by default.
3715 <varlistentry id="guc-log-checkpoints" xreflabel="log_checkpoints">
3716 <term><varname>log_checkpoints</varname> (<type>boolean</type>)</term>
3718 <primary><varname>log_checkpoints</> configuration parameter</primary>
3722 Causes checkpoints and restartpoints to be logged in the server log.
3723 Some statistics are included in the log messages, including the number
3724 of buffers written and the time spent writing them.
3725 This parameter can only be set in the <filename>postgresql.conf</>
3726 file or on the server command line. The default is off.
3731 <varlistentry id="guc-log-connections" xreflabel="log_connections">
3732 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
3734 <primary><varname>log_connections</> configuration parameter</primary>
3738 Causes each attempted connection to the server to be logged,
3739 as well as successful completion of client authentication.
3740 This parameter cannot be changed after session start.
3746 Some client programs, like <application>psql</>, attempt
3747 to connect twice while determining if a password is required, so
3748 duplicate <quote>connection received</> messages do not
3749 necessarily indicate a problem.
3755 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
3756 <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
3758 <primary><varname>log_disconnections</> configuration parameter</primary>
3762 This outputs a line in the server log similar to
3763 <varname>log_connections</varname> but at session termination,
3764 and includes the duration of the session. This is off by
3766 This parameter cannot be changed after session start.
3772 <varlistentry id="guc-log-duration" xreflabel="log_duration">
3773 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
3775 <primary><varname>log_duration</> configuration parameter</primary>
3779 Causes the duration of every completed statement to be logged.
3780 The default is <literal>off</>.
3781 Only superusers can change this setting.
3785 For clients using extended query protocol, durations of the Parse,
3786 Bind, and Execute steps are logged independently.
3791 The difference between setting this option and setting
3792 <xref linkend="guc-log-min-duration-statement"> to zero is that
3793 exceeding <varname>log_min_duration_statement</> forces the text of
3794 the query to be logged, but this option doesn't. Thus, if
3795 <varname>log_duration</> is <literal>on</> and
3796 <varname>log_min_duration_statement</> has a positive value, all
3797 durations are logged but the query text is included only for
3798 statements exceeding the threshold. This behavior can be useful for
3799 gathering statistics in high-load installations.
3805 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
3806 <term><varname>log_error_verbosity</varname> (<type>enum</type>)</term>
3808 <primary><varname>log_error_verbosity</> configuration parameter</primary>
3812 Controls the amount of detail written in the server log for each
3813 message that is logged. Valid values are <literal>TERSE</>,
3814 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
3815 fields to displayed messages. <literal>TERSE</> excludes
3816 the logging of <literal>DETAIL</>, <literal>HINT</>,
3817 <literal>QUERY</>, and <literal>CONTEXT</> error information.
3818 <literal>VERBOSE</> output includes the <symbol>SQLSTATE</> error
3819 code (see also <xref linkend="errcodes-appendix">) and the source code file name, function name,
3820 and line number that generated the error.
3821 Only superusers can change this setting.
3826 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
3827 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
3829 <primary><varname>log_hostname</> configuration parameter</primary>
3833 By default, connection log messages only show the IP address of the
3834 connecting host. Turning this parameter on causes logging of the
3835 host name as well. Note that depending on your host name resolution
3836 setup this might impose a non-negligible performance penalty.
3837 This parameter can only be set in the <filename>postgresql.conf</>
3838 file or on the server command line.
3843 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
3844 <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
3846 <primary><varname>log_line_prefix</> configuration parameter</primary>
3850 This is a <function>printf</>-style string that is output at the
3851 beginning of each log line.
3852 <literal>%</> characters begin <quote>escape sequences</>
3853 that are replaced with status information as outlined below.
3854 Unrecognized escapes are ignored. Other
3855 characters are copied straight to the log line. Some escapes are
3856 only recognized by session processes, and are ignored by
3857 background processes such as the main server process.
3858 This parameter can only be set in the <filename>postgresql.conf</>
3859 file or on the server command line. The default is an empty string.
3865 <entry>Escape</entry>
3866 <entry>Effect</entry>
3867 <entry>Session only</entry>
3872 <entry><literal>%a</literal></entry>
3873 <entry>Application name</entry>
3877 <entry><literal>%u</literal></entry>
3878 <entry>User name</entry>
3882 <entry><literal>%d</literal></entry>
3883 <entry>Database name</entry>
3887 <entry><literal>%r</literal></entry>
3888 <entry>Remote host name or IP address, and remote port</entry>
3892 <entry><literal>%h</literal></entry>
3893 <entry>Remote host name or IP address</entry>
3897 <entry><literal>%p</literal></entry>
3898 <entry>Process ID</entry>
3902 <entry><literal>%t</literal></entry>
3903 <entry>Time stamp without milliseconds</entry>
3907 <entry><literal>%m</literal></entry>
3908 <entry>Time stamp with milliseconds</entry>
3912 <entry><literal>%i</literal></entry>
3913 <entry>Command tag: type of session's current command</entry>
3917 <entry><literal>%e</literal></entry>
3918 <entry>SQLSTATE error code</entry>
3922 <entry><literal>%c</literal></entry>
3923 <entry>Session ID: see below</entry>
3927 <entry><literal>%l</literal></entry>
3928 <entry>Number of the log line for each session or process, starting at 1</entry>
3932 <entry><literal>%s</literal></entry>
3933 <entry>Process start time stamp</entry>
3937 <entry><literal>%v</literal></entry>
3938 <entry>Virtual transaction ID (backendID/localXID)</entry>
3942 <entry><literal>%x</literal></entry>
3943 <entry>Transaction ID (0 if none is assigned)</entry>
3947 <entry><literal>%q</literal></entry>
3948 <entry>Produces no output, but tells non-session
3949 processes to stop at this point in the string; ignored by
3950 session processes</entry>
3954 <entry><literal>%%</literal></entry>
3955 <entry>Literal <literal>%</></entry>
3962 The <literal>%c</> escape prints a quasi-unique session identifier,
3963 consisting of two 4-byte hexadecimal numbers (without leading zeros)
3964 separated by a dot. The numbers are the process start time and the
3965 process ID, so <literal>%c</> can also be used as a space saving way
3966 of printing those items. For example, to generate the session
3967 identifier from <literal>pg_stat_activity</>, use this query:
3969 SELECT to_hex(EXTRACT(EPOCH FROM backend_start)::integer) || '.' ||
3971 FROM pg_stat_activity;
3978 If you set a nonempty value for <varname>log_line_prefix</>,
3979 you should usually make its last character be a space, to provide
3980 visual separation from the rest of the log line. A punctuation
3981 character can be used too.
3987 <application>Syslog</> produces its own
3988 time stamp and process ID information, so you probably do not want to
3989 include those escapes if you are logging to <application>syslog</>.
3995 <varlistentry id="guc-log-lock-waits" xreflabel="log_lock_waits">
3996 <term><varname>log_lock_waits</varname> (<type>boolean</type>)</term>
3998 <primary><varname>log_lock_waits</> configuration parameter</primary>
4002 Controls whether a log message is produced when a session waits
4003 longer than <xref linkend="guc-deadlock-timeout"> to acquire a
4004 lock. This is useful in determining if lock waits are causing
4005 poor performance. The default is <literal>off</>.
4010 <varlistentry id="guc-log-statement" xreflabel="log_statement">
4011 <term><varname>log_statement</varname> (<type>enum</type>)</term>
4013 <primary><varname>log_statement</> configuration parameter</primary>
4017 Controls which SQL statements are logged. Valid values are
4018 <literal>none</> (off), <literal>ddl</>, <literal>mod</>, and
4019 <literal>all</> (all statements). <literal>ddl</> logs all data definition
4020 statements, such as <command>CREATE</>, <command>ALTER</>, and
4021 <command>DROP</> statements. <literal>mod</> logs all
4022 <literal>ddl</> statements, plus data-modifying statements
4023 such as <command>INSERT</>,
4024 <command>UPDATE</>, <command>DELETE</>, <command>TRUNCATE</>,
4025 and <command>COPY FROM</>.
4026 <command>PREPARE</>, <command>EXECUTE</>, and
4027 <command>EXPLAIN ANALYZE</> statements are also logged if their
4028 contained command is of an appropriate type. For clients using
4029 extended query protocol, logging occurs when an Execute message
4030 is received, and values of the Bind parameters are included
4031 (with any embedded single-quote marks doubled).
4035 The default is <literal>none</>. Only superusers can change this
4041 Statements that contain simple syntax errors are not logged
4042 even by the <varname>log_statement</> = <literal>all</> setting,
4043 because the log message is emitted only after basic parsing has
4044 been done to determine the statement type. In the case of extended
4045 query protocol, this setting likewise does not log statements that
4046 fail before the Execute phase (i.e., during parse analysis or
4047 planning). Set <varname>log_min_error_statement</> to
4048 <literal>ERROR</> (or lower) to log such statements.
4054 <varlistentry id="guc-log-temp-files" xreflabel="log_temp_files">
4055 <term><varname>log_temp_files</varname> (<type>integer</type>)</term>
4057 <primary><varname>log_temp_files</> configuration parameter</primary>
4061 Controls logging of temporary file names and sizes.
4062 Temporary files can be
4063 created for sorts, hashes, and temporary query results.
4064 A log entry is made for each temporary file when it is deleted.
4065 A value of zero logs all temporary file information, while positive
4066 values log only files whose size is greater than or equal to
4067 the specified number of kilobytes. The
4068 default setting is -1, which disables such logging.
4069 Only superusers can change this setting.
4074 <varlistentry id="guc-log-timezone" xreflabel="log_timezone">
4075 <term><varname>log_timezone</varname> (<type>string</type>)</term>
4077 <primary><varname>log_timezone</> configuration parameter</primary>
4081 Sets the time zone used for timestamps written in the server log.
4082 Unlike <xref linkend="guc-timezone">, this value is cluster-wide,
4083 so that all sessions will report timestamps consistently.
4084 The built-in default is <literal>GMT</>, but that is typically
4085 overridden in <filename>postgresql.conf</>; <application>initdb</>
4086 will install a setting there corresponding to its system environment.
4087 See <xref linkend="datatype-timezones"> for more information.
4088 This parameter can only be set in the <filename>postgresql.conf</>
4089 file or on the server command line.
4096 <sect2 id="runtime-config-logging-csvlog">
4097 <title>Using CSV-Format Log Output</title>
4100 Including <literal>csvlog</> in the <varname>log_destination</> list
4101 provides a convenient way to import log files into a database table.
4102 This option emits log lines in comma-separated-values
4103 (<acronym>CSV</>) format,
4105 time stamp with milliseconds,
4109 client host:port number,
4111 per-session line number,
4114 virtual transaction ID,
4115 regular transaction ID,
4119 error message detail,
4121 internal query that led to the error (if any),
4122 character count of the error position therein,
4124 user query that led to the error (if any and enabled by
4125 <varname>log_min_error_statement</>),
4126 character count of the error position therein,
4127 location of the error in the PostgreSQL source code
4128 (if <varname>log_error_verbosity</> is set to <literal>verbose</>),
4129 and application name.
4130 Here is a sample table definition for storing CSV-format log output:
4133 CREATE TABLE postgres_log
4135 log_time timestamp(3) with time zone,
4139 connection_from text,
4141 session_line_num bigint,
4143 session_start_time timestamp with time zone,
4144 virtual_transaction_id text,
4145 transaction_id bigint,
4146 error_severity text,
4147 sql_state_code text,
4151 internal_query text,
4152 internal_query_pos integer,
4157 application_name text,
4158 PRIMARY KEY (session_id, session_line_num)
4164 To import a log file into this table, use the <command>COPY FROM</>
4168 COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
4173 There are a few things you need to do to simplify importing CSV log
4179 Set <varname>log_filename</varname> and
4180 <varname>log_rotation_age</> to provide a consistent,
4181 predictable naming scheme for your log files. This lets you
4182 predict what the file name will be and know when an individual log
4183 file is complete and therefore ready to be imported.
4189 Set <varname>log_rotation_size</varname> to 0 to disable
4190 size-based log rotation, as it makes the log file name difficult
4197 Set <varname>log_truncate_on_rotation</varname> to <literal>on</> so
4198 that old log data isn't mixed with the new in the same file.
4204 The table definition above includes a primary key specification.
4205 This is useful to protect against accidentally importing the same
4206 information twice. The <command>COPY</> command commits all of the
4207 data it imports at one time, so any error will cause the entire
4208 import to fail. If you import a partial log file and later import
4209 the file again when it is complete, the primary key violation will
4210 cause the import to fail. Wait until the log is complete and
4211 closed before importing. This procedure will also protect against
4212 accidentally importing a partial line that hasn't been completely
4213 written, which would also cause <command>COPY</> to fail.
4222 <sect1 id="runtime-config-statistics">
4223 <title>Run-time Statistics</title>
4225 <sect2 id="runtime-config-statistics-collector">
4226 <title>Query and Index Statistics Collector</title>
4229 These parameters control server-wide statistics collection features.
4230 When statistics collection is enabled, the data that is produced can be
4231 accessed via the <structname>pg_stat</structname> and
4232 <structname>pg_statio</structname> family of system views.
4233 Refer to <xref linkend="monitoring"> for more information.
4238 <varlistentry id="guc-track-activities" xreflabel="track_activities">
4239 <term><varname>track_activities</varname> (<type>boolean</type>)</term>
4241 <primary><varname>track_activities</> configuration parameter</primary>
4245 Enables the collection of information on the currently
4246 executing command of each session, along with the time when
4247 that command began execution. This parameter is on by
4248 default. Note that even when enabled, this information is not
4249 visible to all users, only to superusers and the user owning
4250 the session being reported on, so it should not represent a
4252 Only superusers can change this setting.
4257 <varlistentry id="guc-track-activity-query-size" xreflabel="track_activity_query_size">
4258 <term><varname>track_activity_query_size</varname> (<type>integer</type>)</term>
4260 <primary><varname>track_activity_query_size</> configuration parameter</primary>
4264 Specifies the number of bytes reserved to track the currently
4265 executing command for each active session, for the
4266 <structname>pg_stat_activity</>.<structfield>query</> field.
4267 The default value is 1024. This parameter can only be set at server
4273 <varlistentry id="guc-track-counts" xreflabel="track_counts">
4274 <term><varname>track_counts</varname> (<type>boolean</type>)</term>
4276 <primary><varname>track_counts</> configuration parameter</primary>
4280 Enables collection of statistics on database activity.
4281 This parameter is on by default, because the autovacuum
4282 daemon needs the collected information.
4283 Only superusers can change this setting.
4288 <varlistentry id="guc-track-iotiming" xreflabel="track_iotiming">
4289 <term><varname>track_iotiming</varname> (<type>boolean</type>)</term>
4291 <primary><varname>track_iotiming</> configuration parameter</primary>
4295 Enables timing of database I/O calls. This parameter is off by
4296 default, because it will repeatedly query the operating system for
4297 the current time, which may cause significant overhead on some
4298 platforms. You can use the <xref linkend="pgtesttiming"> tool to
4299 measure the overhead of timing on your system. Timing information is
4300 displayed in <xref linkend="pg-stat-database-view">, in the output of
4301 <xref linkend="sql-explain"> when the <literal>BUFFERS</> option is
4302 used, and by <xref linkend="pgstatstatements">. Only superusers can
4303 change this setting.
4308 <varlistentry id="guc-track-functions" xreflabel="track_functions">
4309 <term><varname>track_functions</varname> (<type>enum</type>)</term>
4311 <primary><varname>track_functions</> configuration parameter</primary>
4315 Enables tracking of function call counts and time used. Specify
4316 <literal>pl</literal> to track only procedural-language functions,
4317 <literal>all</literal> to also track SQL and C language functions.
4318 The default is <literal>none</literal>, which disables function
4319 statistics tracking. Only superusers can change this setting.
4324 SQL-language functions that are simple enough to be <quote>inlined</>
4325 into the calling query will not be tracked, regardless of this
4332 <varlistentry id="guc-update-process-title" xreflabel="update_process_title">
4333 <term><varname>update_process_title</varname> (<type>boolean</type>)</term>
4335 <primary><varname>update_process_title</> configuration parameter</primary>
4339 Enables updating of the process title every time a new SQL command
4340 is received by the server. The process title is typically viewed
4341 by the <command>ps</> command,
4342 or in Windows by using the <application>Process Explorer</>.
4343 Only superusers can change this setting.
4348 <varlistentry id="guc-stats-temp-directory" xreflabel="stats_temp_directory">
4349 <term><varname>stats_temp_directory</varname> (<type>string</type>)</term>
4351 <primary><varname>stats_temp_directory</> configuration parameter</primary>
4355 Sets the directory to store temporary statistics data in. This can be
4356 a path relative to the data directory or an absolute path. The default
4357 is <filename>pg_stat_tmp</filename>. Pointing this at a RAM-based
4358 file system will decrease physical I/O requirements and can lead to
4359 improved performance.
4360 This parameter can only be set in the <filename>postgresql.conf</>
4361 file or on the server command line.
4369 <sect2 id="runtime-config-statistics-monitor">
4370 <title>Statistics Monitoring</title>
4374 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
4375 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
4376 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
4377 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
4379 <primary><varname>log_statement_stats</> configuration parameter</primary>
4382 <primary><varname>log_parser_stats</> configuration parameter</primary>
4385 <primary><varname>log_planner_stats</> configuration parameter</primary>
4388 <primary><varname>log_executor_stats</> configuration parameter</primary>
4392 For each query, output performance statistics of the respective
4393 module to the server log. This is a crude profiling
4394 instrument, similar to the Unix <function>getrusage()</> operating
4395 system facility. <varname>log_statement_stats</varname> reports total
4396 statement statistics, while the others report per-module statistics.
4397 <varname>log_statement_stats</varname> cannot be enabled together with
4398 any of the per-module options. All of these options are disabled by
4399 default. Only superusers can change these settings.
4409 <sect1 id="runtime-config-autovacuum">
4410 <title>Automatic Vacuuming</title>
4413 <primary>autovacuum</primary>
4414 <secondary>configuration parameters</secondary>
4418 These settings control the behavior of the <firstterm>autovacuum</>
4419 feature. Refer to <xref linkend="autovacuum"> for
4425 <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
4426 <term><varname>autovacuum</varname> (<type>boolean</type>)</term>
4428 <primary><varname>autovacuum</> configuration parameter</primary>
4432 Controls whether the server should run the
4433 autovacuum launcher daemon. This is on by default; however,
4434 <xref linkend="guc-track-counts"> must also be enabled for
4436 This parameter can only be set in the <filename>postgresql.conf</>
4437 file or on the server command line.
4440 Note that even when this parameter is disabled, the system
4441 will launch autovacuum processes if necessary to
4442 prevent transaction ID wraparound. See <xref
4443 linkend="vacuum-for-wraparound"> for more information.
4448 <varlistentry id="guc-log-autovacuum-min-duration" xreflabel="log_autovacuum_min_duration">
4449 <term><varname>log_autovacuum_min_duration</varname> (<type>integer</type>)</term>
4451 <primary><varname>log_autovacuum_min_duration</> configuration parameter</primary>
4455 Causes each action executed by autovacuum to be logged if it ran for at
4456 least the specified number of milliseconds. Setting this to zero logs
4457 all autovacuum actions. Minus-one (the default) disables logging
4458 autovacuum actions. For example, if you set this to
4459 <literal>250ms</literal> then all automatic vacuums and analyzes that run
4460 250ms or longer will be logged. In addition, when this parameter is
4461 set to any value other than <literal>-1</literal>, a message will be
4462 logged if an autovacuum action is skipped due to the existence of a
4463 conflicting lock. Enabling this parameter can be helpful
4464 in tracking autovacuum activity. This setting can only be set in
4465 the <filename>postgresql.conf</> file or on the server command line.
4470 <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
4471 <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)</term>
4473 <primary><varname>autovacuum_max_workers</> configuration parameter</primary>
4477 Specifies the maximum number of autovacuum processes (other than the
4478 autovacuum launcher) which may be running at any one time. The default
4479 is three. This parameter can only be set at server start.
4484 <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
4485 <term><varname>autovacuum_naptime</varname> (<type>integer</type>)</term>
4487 <primary><varname>autovacuum_naptime</> configuration parameter</primary>
4491 Specifies the minimum delay between autovacuum runs on any given
4492 database. In each round the daemon examines the
4493 database and issues <command>VACUUM</> and <command>ANALYZE</> commands
4494 as needed for tables in that database. The delay is measured
4495 in seconds, and the default is one minute (<literal>1min</>).
4496 This parameter can only be set in the <filename>postgresql.conf</>
4497 file or on the server command line.
4502 <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
4503 <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)</term>
4505 <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
4509 Specifies the minimum number of updated or deleted tuples needed
4510 to trigger a <command>VACUUM</> in any one table.
4511 The default is 50 tuples.
4512 This parameter can only be set in the <filename>postgresql.conf</>
4513 file or on the server command line.
4514 This setting can be overridden for individual tables by
4515 changing storage parameters.
4520 <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
4521 <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)</term>
4523 <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
4527 Specifies the minimum number of inserted, updated or deleted tuples
4528 needed to trigger an <command>ANALYZE</> in any one table.
4529 The default is 50 tuples.
4530 This parameter can only be set in the <filename>postgresql.conf</>
4531 file or on the server command line.
4532 This setting can be overridden for individual tables by
4533 changing storage parameters.
4538 <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
4539 <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)</term>
4541 <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
4545 Specifies a fraction of the table size to add to
4546 <varname>autovacuum_vacuum_threshold</varname>
4547 when deciding whether to trigger a <command>VACUUM</>.
4548 The default is 0.2 (20% of table size).
4549 This parameter can only be set in the <filename>postgresql.conf</>
4550 file or on the server command line.
4551 This setting can be overridden for individual tables by
4552 changing storage parameters.
4557 <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
4558 <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)</term>
4560 <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
4564 Specifies a fraction of the table size to add to
4565 <varname>autovacuum_analyze_threshold</varname>
4566 when deciding whether to trigger an <command>ANALYZE</>.
4567 The default is 0.1 (10% of table size).
4568 This parameter can only be set in the <filename>postgresql.conf</>
4569 file or on the server command line.
4570 This setting can be overridden for individual tables by
4571 changing storage parameters.
4576 <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
4577 <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)</term>
4579 <primary><varname>autovacuum_freeze_max_age</> configuration parameter</primary>
4583 Specifies the maximum age (in transactions) that a table's
4584 <structname>pg_class</>.<structfield>relfrozenxid</> field can
4585 attain before a <command>VACUUM</> operation is forced
4586 to prevent transaction ID wraparound within the table.
4587 Note that the system will launch autovacuum processes to
4588 prevent wraparound even when autovacuum is otherwise disabled.
4592 Vacuum also allows removal of old files from the
4593 <filename>pg_clog</> subdirectory, which is why the default
4594 is a relatively low 200 million transactions.
4595 This parameter can only be set at server start, but the setting
4596 can be reduced for individual tables by
4597 changing storage parameters.
4598 For more information see <xref linkend="vacuum-for-wraparound">.
4603 <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
4604 <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
4606 <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
4610 Specifies the cost delay value that will be used in automatic
4611 <command>VACUUM</> operations. If -1 is specified, the regular
4612 <xref linkend="guc-vacuum-cost-delay"> value will be used.
4613 The default value is 20 milliseconds.
4614 This parameter can only be set in the <filename>postgresql.conf</>
4615 file or on the server command line.
4616 This setting can be overridden for individual tables by
4617 changing storage parameters.
4622 <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
4623 <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
4625 <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
4629 Specifies the cost limit value that will be used in automatic
4630 <command>VACUUM</> operations. If -1 is specified (which is the
4631 default), the regular
4632 <xref linkend="guc-vacuum-cost-limit"> value will be used. Note that
4633 the value is distributed proportionally among the running autovacuum
4634 workers, if there is more than one, so that the sum of the limits of
4635 each worker never exceeds the limit on this variable.
4636 This parameter can only be set in the <filename>postgresql.conf</>
4637 file or on the server command line.
4638 This setting can be overridden for individual tables by
4639 changing storage parameters.
4647 <sect1 id="runtime-config-client">
4648 <title>Client Connection Defaults</title>
4650 <sect2 id="runtime-config-client-statement">
4651 <title>Statement Behavior</title>
4654 <varlistentry id="guc-search-path" xreflabel="search_path">
4655 <term><varname>search_path</varname> (<type>string</type>)</term>
4657 <primary><varname>search_path</> configuration parameter</primary>
4659 <indexterm><primary>path</><secondary>for schemas</></>
4662 This variable specifies the order in which schemas are searched
4663 when an object (table, data type, function, etc.) is referenced by a
4664 simple name with no schema specified. When there are objects of
4665 identical names in different schemas, the one found first
4666 in the search path is used. An object that is not in any of the
4667 schemas in the search path can only be referenced by specifying
4668 its containing schema with a qualified (dotted) name.
4672 The value for <varname>search_path</varname> must be a comma-separated
4673 list of schema names. Any name that is not an existing schema, or is
4674 a schema for which the user does not have <literal>USAGE</>
4675 permission, is silently ignored.
4679 If one of the list items is the special name
4680 <literal>$user</literal>, then the schema having the name returned by
4681 <function>SESSION_USER</> is substituted, if there is such a schema
4682 and the user has <literal>USAGE</> permission for it.
4683 (If not, <literal>$user</literal> is ignored.)
4687 The system catalog schema, <literal>pg_catalog</>, is always
4688 searched, whether it is mentioned in the path or not. If it is
4689 mentioned in the path then it will be searched in the specified
4690 order. If <literal>pg_catalog</> is not in the path then it will
4691 be searched <emphasis>before</> searching any of the path items.
4695 Likewise, the current session's temporary-table schema,
4696 <literal>pg_temp_<replaceable>nnn</></>, is always searched if it
4697 exists. It can be explicitly listed in the path by using the
4698 alias <literal>pg_temp</>. If it is not listed in the path then
4699 it is searched first (even before <literal>pg_catalog</>). However,
4700 the temporary schema is only searched for relation (table, view,
4701 sequence, etc) and data type names. It is never searched for
4702 function or operator names.
4706 When objects are created without specifying a particular target
4707 schema, they will be placed in the first valid schema named in
4708 <varname>search_path</varname>. An error is reported if the search
4713 The default value for this parameter is
4714 <literal>"$user", public</literal>.
4715 This setting supports shared use of a database (where no users
4716 have private schemas, and all share use of <literal>public</>),
4717 private per-user schemas, and combinations of these. Other
4718 effects can be obtained by altering the default search path
4719 setting, either globally or per-user.
4723 The current effective value of the search path can be examined
4724 via the <acronym>SQL</acronym> function
4725 <function>current_schemas</>
4726 (see <xref linkend="functions-info">).
4727 This is not quite the same as
4728 examining the value of <varname>search_path</varname>, since
4729 <function>current_schemas</> shows how the items
4730 appearing in <varname>search_path</varname> were resolved.
4734 For more information on schema handling, see <xref linkend="ddl-schemas">.
4739 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
4740 <term><varname>default_tablespace</varname> (<type>string</type>)</term>
4742 <primary><varname>default_tablespace</> configuration parameter</primary>
4744 <indexterm><primary>tablespace</><secondary>default</></>
4747 This variable specifies the default tablespace in which to create
4748 objects (tables and indexes) when a <command>CREATE</> command does
4749 not explicitly specify a tablespace.
4753 The value is either the name of a tablespace, or an empty string
4754 to specify using the default tablespace of the current database.
4755 If the value does not match the name of any existing tablespace,
4756 <productname>PostgreSQL</> will automatically use the default
4757 tablespace of the current database. If a nondefault tablespace
4758 is specified, the user must have <literal>CREATE</> privilege
4759 for it, or creation attempts will fail.
4763 This variable is not used for temporary tables; for them,
4764 <xref linkend="guc-temp-tablespaces"> is consulted instead.
4768 This variable is also not used when creating databases.
4769 By default, a new database inherits its tablespace setting from
4770 the template database it is copied from.
4774 For more information on tablespaces,
4775 see <xref linkend="manage-ag-tablespaces">.
4780 <varlistentry id="guc-temp-tablespaces" xreflabel="temp_tablespaces">
4781 <term><varname>temp_tablespaces</varname> (<type>string</type>)</term>
4783 <primary><varname>temp_tablespaces</> configuration parameter</primary>
4785 <indexterm><primary>tablespace</><secondary>temporary</></>
4788 This variable specifies tablespaces in which to create temporary
4789 objects (temp tables and indexes on temp tables) when a
4790 <command>CREATE</> command does not explicitly specify a tablespace.
4791 Temporary files for purposes such as sorting large data sets
4792 are also created in these tablespaces.
4796 The value is a list of names of tablespaces. When there is more than
4797 one name in the list, <productname>PostgreSQL</> chooses a random
4798 member of the list each time a temporary object is to be created;
4799 except that within a transaction, successively created temporary
4800 objects are placed in successive tablespaces from the list.
4801 If the selected element of the list is an empty string,
4802 <productname>PostgreSQL</> will automatically use the default
4803 tablespace of the current database instead.
4807 When <varname>temp_tablespaces</> is set interactively, specifying a
4808 nonexistent tablespace is an error, as is specifying a tablespace for
4809 which the user does not have <literal>CREATE</> privilege. However,
4810 when using a previously set value, nonexistent tablespaces are
4811 ignored, as are tablespaces for which the user lacks
4812 <literal>CREATE</> privilege. In particular, this rule applies when
4813 using a value set in <filename>postgresql.conf</>.
4817 The default value is an empty string, which results in all temporary
4818 objects being created in the default tablespace of the current
4823 See also <xref linkend="guc-default-tablespace">.
4828 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
4829 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
4831 <primary><varname>check_function_bodies</> configuration parameter</primary>
4835 This parameter is normally on. When set to <literal>off</>, it
4836 disables validation of the function body string during <xref
4837 linkend="sql-createfunction">. Disabling validation is
4838 occasionally useful to avoid problems such as forward references
4839 when restoring function definitions from a dump.
4844 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
4846 <primary>transaction isolation level</primary>
4847 <secondary>setting default</secondary>
4850 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
4852 <term><varname>default_transaction_isolation</varname> (<type>enum</type>)</term>
4855 Each SQL transaction has an isolation level, which can be
4856 either <quote>read uncommitted</quote>, <quote>read
4857 committed</quote>, <quote>repeatable read</quote>, or
4858 <quote>serializable</quote>. This parameter controls the
4859 default isolation level of each new transaction. The default
4860 is <quote>read committed</quote>.
4864 Consult <xref linkend="mvcc"> and <xref
4865 linkend="sql-set-transaction"> for more information.
4870 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
4872 <primary>read-only transaction</primary>
4873 <secondary>setting default</secondary>
4876 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
4879 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
4882 A read-only SQL transaction cannot alter non-temporary tables.
4883 This parameter controls the default read-only status of each new
4884 transaction. The default is <literal>off</> (read/write).
4888 Consult <xref linkend="sql-set-transaction"> for more information.
4893 <varlistentry id="guc-default-transaction-deferrable" xreflabel="default_transaction_deferrable">
4895 <primary>deferrable transaction</primary>
4896 <secondary>setting default</secondary>
4899 <primary><varname>default_transaction_deferrable</> configuration parameter</primary>
4902 <term><varname>default_transaction_deferrable</varname> (<type>boolean</type>)</term>
4905 When running at the <literal>serializable</> isolation level,
4906 a deferrable read-only SQL transaction may be delayed before
4907 it is allowed to proceed. However, once it begins executing
4908 it does not incur any of the overhead required to ensure
4909 serializability; so serialization code will have no reason to
4910 force it to abort because of concurrent updates, making this
4911 option suitable for long-running read-only transactions.
4915 This parameter controls the default deferrable status of each
4916 new transaction. It currently has no effect on read-write
4917 transactions or those operating at isolation levels lower
4918 than <literal>serializable</>. The default is <literal>off</>.
4922 Consult <xref linkend="sql-set-transaction"> for more information.
4928 <varlistentry id="guc-session-replication-role" xreflabel="session_replication_role">
4929 <term><varname>session_replication_role</varname> (<type>enum</type>)</term>
4931 <primary><varname>session_replication_role</> configuration parameter</primary>
4935 Controls firing of replication-related triggers and rules for the
4936 current session. Setting this variable requires
4937 superuser privilege and results in discarding any previously cached
4938 query plans. Possible values are <literal>origin</> (the default),
4939 <literal>replica</> and <literal>local</>.
4940 See <xref linkend="sql-altertable"> for
4946 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
4947 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
4949 <primary><varname>statement_timeout</> configuration parameter</primary>
4953 Abort any statement that takes over the specified number of
4954 milliseconds, starting from the time the command arrives at the server
4955 from the client. If <varname>log_min_error_statement</> is set to
4956 <literal>ERROR</> or lower, the statement that timed out will also be
4957 logged. A value of zero (the default) turns this off.
4961 Setting <varname>statement_timeout</> in
4962 <filename>postgresql.conf</> is not recommended because it
4963 affects all sessions.
4968 <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
4969 <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)</term>
4971 <primary><varname>vacuum_freeze_table_age</> configuration parameter</primary>
4975 <command>VACUUM</> performs a whole-table scan if the table's
4976 <structname>pg_class</>.<structfield>relfrozenxid</> field has reached
4977 the age specified by this setting. The default is 150 million
4978 transactions. Although users can set this value anywhere from zero to
4979 one billion, <command>VACUUM</> will silently limit the effective value
4980 to 95% of <xref linkend="guc-autovacuum-freeze-max-age">, so that a
4981 periodical manual <command>VACUUM</> has a chance to run before an
4982 anti-wraparound autovacuum is launched for the table. For more
4984 <xref linkend="vacuum-for-wraparound">.
4989 <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
4990 <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)</term>
4992 <primary><varname>vacuum_freeze_min_age</> configuration parameter</primary>
4996 Specifies the cutoff age (in transactions) that <command>VACUUM</>
4997 should use to decide whether to replace transaction IDs with
4998 <literal>FrozenXID</> while scanning a table.
4999 The default is 50 million transactions. Although
5000 users can set this value anywhere from zero to one billion,
5001 <command>VACUUM</> will silently limit the effective value to half
5002 the value of <xref linkend="guc-autovacuum-freeze-max-age">, so
5003 that there is not an unreasonably short time between forced
5004 autovacuums. For more information see <xref
5005 linkend="vacuum-for-wraparound">.
5010 <varlistentry id="guc-bytea-output" xreflabel="bytea_output">
5011 <term><varname>bytea_output</varname> (<type>enum</type>)</term>
5013 <primary><varname>bytea_output</> configuration parameter</primary>
5017 Sets the output format for values of type <type>bytea</type>.
5018 Valid values are <literal>hex</literal> (the default)
5019 and <literal>escape</literal> (the traditional PostgreSQL
5020 format). See <xref linkend="datatype-binary"> for more
5021 information. The <type>bytea</type> type always
5022 accepts both formats on input, regardless of this setting.
5027 <varlistentry id="guc-xmlbinary" xreflabel="xmlbinary">
5028 <term><varname>xmlbinary</varname> (<type>enum</type>)</term>
5030 <primary><varname>xmlbinary</> configuration parameter</primary>
5034 Sets how binary values are to be encoded in XML. This applies
5035 for example when <type>bytea</type> values are converted to
5036 XML by the functions <function>xmlelement</function> or
5037 <function>xmlforest</function>. Possible values are
5038 <literal>base64</literal> and <literal>hex</literal>, which
5039 are both defined in the XML Schema standard. The default is
5040 <literal>base64</literal>. For further information about
5041 XML-related functions, see <xref linkend="functions-xml">.
5045 The actual choice here is mostly a matter of taste,
5046 constrained only by possible restrictions in client
5047 applications. Both methods support all possible values,
5048 although the hex encoding will be somewhat larger than the
5054 <varlistentry id="guc-xmloption" xreflabel="xmloption">
5055 <term><varname>xmloption</varname> (<type>enum</type>)</term>
5057 <primary><varname>xmloption</> configuration parameter</primary>
5060 <primary><varname>SET XML OPTION</></primary>
5063 <primary>XML option</primary>
5067 Sets whether <literal>DOCUMENT</literal> or
5068 <literal>CONTENT</literal> is implicit when converting between
5069 XML and character string values. See <xref
5070 linkend="datatype-xml"> for a description of this. Valid
5071 values are <literal>DOCUMENT</literal> and
5072 <literal>CONTENT</literal>. The default is
5073 <literal>CONTENT</literal>.
5077 According to the SQL standard, the command to set this option is
5079 SET XML OPTION { DOCUMENT | CONTENT };
5081 This syntax is also available in PostgreSQL.
5088 <sect2 id="runtime-config-client-format">
5089 <title>Locale and Formatting</title>
5093 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
5094 <term><varname>DateStyle</varname> (<type>string</type>)</term>
5096 <primary><varname>DateStyle</> configuration parameter</primary>
5100 Sets the display format for date and time values, as well as the
5101 rules for interpreting ambiguous date input values. For
5102 historical reasons, this variable contains two independent
5103 components: the output format specification (<literal>ISO</>,
5104 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
5105 and the input/output specification for year/month/day ordering
5106 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
5107 can be set separately or together. The keywords <literal>Euro</>
5108 and <literal>European</> are synonyms for <literal>DMY</>; the
5109 keywords <literal>US</>, <literal>NonEuro</>, and
5110 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
5111 <xref linkend="datatype-datetime"> for more information. The
5112 built-in default is <literal>ISO, MDY</>, but
5113 <application>initdb</application> will initialize the
5114 configuration file with a setting that corresponds to the
5115 behavior of the chosen <varname>lc_time</varname> locale.
5120 <varlistentry id="guc-intervalstyle" xreflabel="IntervalStyle">
5121 <term><varname>IntervalStyle</varname> (<type>enum</type>)</term>
5123 <primary><varname>IntervalStyle</> configuration parameter</primary>
5127 Sets the display format for interval values.
5128 The value <literal>sql_standard</> will produce
5129 output matching <acronym>SQL</acronym> standard interval literals.
5130 The value <literal>postgres</> (which is the default) will produce
5131 output matching <productname>PostgreSQL</> releases prior to 8.4
5132 when the <xref linkend="guc-datestyle">
5133 parameter was set to <literal>ISO</>.
5134 The value <literal>postgres_verbose</> will produce output
5135 matching <productname>PostgreSQL</> releases prior to 8.4
5136 when the <varname>DateStyle</>
5137 parameter was set to non-<literal>ISO</> output.
5138 The value <literal>iso_8601</> will produce output matching the time
5139 interval <quote>format with designators</> defined in section
5140 4.4.3.2 of ISO 8601.
5143 The <varname>IntervalStyle</> parameter also affects the
5144 interpretation of ambiguous interval input. See
5145 <xref linkend="datatype-interval-input"> for more information.
5150 <varlistentry id="guc-timezone" xreflabel="timezone">
5151 <term><varname>timezone</varname> (<type>string</type>)</term>
5153 <primary><varname>timezone</> configuration parameter</primary>
5155 <indexterm><primary>time zone</></>
5158 Sets the time zone for displaying and interpreting time stamps.
5159 The built-in default is <literal>GMT</>, but that is typically
5160 overridden in <filename>postgresql.conf</>; <application>initdb</>
5161 will install a setting there corresponding to its system environment.
5162 See <xref linkend="datatype-timezones"> for more information.
5167 <varlistentry id="guc-timezone-abbreviations" xreflabel="timezone_abbreviations">
5168 <term><varname>timezone_abbreviations</varname> (<type>string</type>)</term>
5170 <primary><varname>timezone_abbreviations</> configuration parameter</primary>
5172 <indexterm><primary>time zone names</></>
5175 Sets the collection of time zone abbreviations that will be accepted
5176 by the server for datetime input. The default is <literal>'Default'</>,
5177 which is a collection that works in most of the world; there are
5178 also <literal>'Australia'</literal> and <literal>'India'</literal>, and other collections can be defined
5179 for a particular installation. See <xref
5180 linkend="datetime-appendix"> for more information.
5185 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
5187 <primary>significant digits</primary>
5190 <primary>floating-point</primary>
5191 <secondary>display</secondary>
5194 <primary><varname>extra_float_digits</> configuration parameter</primary>
5197 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
5200 This parameter adjusts the number of digits displayed for
5201 floating-point values, including <type>float4</>, <type>float8</>,
5202 and geometric data types. The parameter value is added to the
5203 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
5204 as appropriate). The value can be set as high as 3, to include
5205 partially-significant digits; this is especially useful for dumping
5206 float data that needs to be restored exactly. Or it can be set
5207 negative to suppress unwanted digits.
5212 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
5213 <term><varname>client_encoding</varname> (<type>string</type>)</term>
5215 <primary><varname>client_encoding</> configuration parameter</primary>
5217 <indexterm><primary>character set</></>
5220 Sets the client-side encoding (character set).
5221 The default is to use the database encoding.
5222 The character sets supported by the <productname>PostgreSQL</productname>
5223 server are described in <xref linkend="multibyte-charset-supported">.
5228 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
5229 <term><varname>lc_messages</varname> (<type>string</type>)</term>
5231 <primary><varname>lc_messages</> configuration parameter</primary>
5235 Sets the language in which messages are displayed. Acceptable
5236 values are system-dependent; see <xref linkend="locale"> for
5237 more information. If this variable is set to the empty string
5238 (which is the default) then the value is inherited from the
5239 execution environment of the server in a system-dependent way.
5243 On some systems, this locale category does not exist. Setting
5244 this variable will still work, but there will be no effect.
5245 Also, there is a chance that no translated messages for the
5246 desired language exist. In that case you will continue to see
5247 the English messages.
5251 Only superusers can change this setting, because it affects the
5252 messages sent to the server log as well as to the client, and
5253 an improper value might obscure the readability of the server
5259 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
5260 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
5262 <primary><varname>lc_monetary</> configuration parameter</primary>
5266 Sets the locale to use for formatting monetary amounts, for
5267 example with the <function>to_char</function> family of
5268 functions. Acceptable values are system-dependent; see <xref
5269 linkend="locale"> for more information. If this variable is
5270 set to the empty string (which is the default) then the value
5271 is inherited from the execution environment of the server in a
5272 system-dependent way.
5277 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
5278 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
5280 <primary><varname>lc_numeric</> configuration parameter</primary>
5284 Sets the locale to use for formatting numbers, for example
5285 with the <function>to_char</function> family of
5286 functions. Acceptable values are system-dependent; see <xref
5287 linkend="locale"> for more information. If this variable is
5288 set to the empty string (which is the default) then the value
5289 is inherited from the execution environment of the server in a
5290 system-dependent way.
5295 <varlistentry id="guc-lc-time" xreflabel="lc_time">
5296 <term><varname>lc_time</varname> (<type>string</type>)</term>
5298 <primary><varname>lc_time</> configuration parameter</primary>
5302 Sets the locale to use for formatting dates and times, for example
5303 with the <function>to_char</function> family of
5304 functions. Acceptable values are system-dependent; see <xref
5305 linkend="locale"> for more information. If this variable is
5306 set to the empty string (which is the default) then the value
5307 is inherited from the execution environment of the server in a
5308 system-dependent way.
5313 <varlistentry id="guc-default-text-search-config" xreflabel="default_text_search_config">
5314 <term><varname>default_text_search_config</varname> (<type>string</type>)</term>
5316 <primary><varname>default_text_search_config</> configuration parameter</primary>
5320 Selects the text search configuration that is used by those variants
5321 of the text search functions that do not have an explicit argument
5322 specifying the configuration.
5323 See <xref linkend="textsearch"> for further information.
5324 The built-in default is <literal>pg_catalog.simple</>, but
5325 <application>initdb</application> will initialize the
5326 configuration file with a setting that corresponds to the
5327 chosen <varname>lc_ctype</varname> locale, if a configuration
5328 matching that locale can be identified.
5336 <sect2 id="runtime-config-client-other">
5337 <title>Other Defaults</title>
5341 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
5342 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
5344 <primary><varname>dynamic_library_path</> configuration parameter</primary>
5346 <indexterm><primary>dynamic loading</></>
5349 If a dynamically loadable module needs to be opened and the
5350 file name specified in the <command>CREATE FUNCTION</command> or
5351 <command>LOAD</command> command
5352 does not have a directory component (i.e., the
5353 name does not contain a slash), the system will search this
5354 path for the required file.
5358 The value for <varname>dynamic_library_path</varname> must be a
5359 list of absolute directory paths separated by colons (or semi-colons
5360 on Windows). If a list element starts
5361 with the special string <literal>$libdir</literal>, the
5362 compiled-in <productname>PostgreSQL</productname> package
5363 library directory is substituted for <literal>$libdir</literal>; this
5364 is where the modules provided by the standard
5365 <productname>PostgreSQL</productname> distribution are installed.
5366 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
5367 this directory.) For example:
5369 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
5371 or, in a Windows environment:
5373 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
5378 The default value for this parameter is
5379 <literal>'$libdir'</literal>. If the value is set to an empty
5380 string, the automatic path search is turned off.
5384 This parameter can be changed at run time by superusers, but a
5385 setting done that way will only persist until the end of the
5386 client connection, so this method should be reserved for
5387 development purposes. The recommended way to set this parameter
5388 is in the <filename>postgresql.conf</filename> configuration
5394 <varlistentry id="guc-gin-fuzzy-search-limit" xreflabel="gin_fuzzy_search_limit">
5395 <term><varname>gin_fuzzy_search_limit</varname> (<type>integer</type>)</term>
5397 <primary><varname>gin_fuzzy_search_limit</> configuration parameter</primary>
5401 Soft upper limit of the size of the set returned by GIN index scans. For more
5402 information see <xref linkend="gin-tips">.
5407 <varlistentry id="guc-local-preload-libraries" xreflabel="local_preload_libraries">
5408 <term><varname>local_preload_libraries</varname> (<type>string</type>)</term>
5410 <primary><varname>local_preload_libraries</> configuration parameter</primary>
5413 <primary><filename>$libdir/plugins</></primary>
5417 This variable specifies one or more shared libraries that are
5418 to be preloaded at connection start. If more than one library
5419 is to be loaded, separate their names with commas. All library
5420 names are converted to lower case unless double-quoted.
5421 This parameter cannot be changed after the start of a particular
5426 Because this is not a superuser-only option, the libraries
5427 that can be loaded are restricted to those appearing in the
5428 <filename>plugins</> subdirectory of the installation's
5429 standard library directory. (It is the database administrator's
5430 responsibility to ensure that only <quote>safe</> libraries
5431 are installed there.) Entries in <varname>local_preload_libraries</>
5432 can specify this directory explicitly, for example
5433 <literal>$libdir/plugins/mylib</literal>, or just specify
5434 the library name — <literal>mylib</literal> would have
5435 the same effect as <literal>$libdir/plugins/mylib</literal>.
5439 Unlike <xref linkend="guc-shared-preload-libraries">, there is no
5440 performance advantage to loading a library at session
5441 start rather than when it is first used. Rather, the intent of
5442 this feature is to allow debugging or performance-measurement
5443 libraries to be loaded into specific sessions without an explicit
5444 <command>LOAD</> command being given. For example, debugging could
5445 be enabled for all sessions under a given user name by setting
5446 this parameter with <command>ALTER ROLE SET</>.
5450 If a specified library is not found,
5451 the connection attempt will fail.
5455 Every PostgreSQL-supported library has a <quote>magic
5456 block</> that is checked to guarantee compatibility.
5457 For this reason, non-PostgreSQL libraries cannot be
5467 <sect1 id="runtime-config-locks">
5468 <title>Lock Management</title>
5472 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
5474 <primary>deadlock</primary>
5475 <secondary>timeout during</secondary>
5478 <primary>timeout</primary>
5479 <secondary>deadlock</secondary>
5482 <primary><varname>deadlock_timeout</> configuration parameter</primary>
5485 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
5488 This is the amount of time, in milliseconds, to wait on a lock
5489 before checking to see if there is a deadlock condition. The
5490 check for deadlock is relatively expensive, so the server doesn't run
5491 it every time it waits for a lock. We optimistically assume
5492 that deadlocks are not common in production applications and
5493 just wait on the lock for a while before checking for a
5494 deadlock. Increasing this value reduces the amount of time
5495 wasted in needless deadlock checks, but slows down reporting of
5496 real deadlock errors. The default is one second (<literal>1s</>),
5497 which is probably about the smallest value you would want in
5498 practice. On a heavily loaded server you might want to raise it.
5499 Ideally the setting should exceed your typical transaction time,
5500 so as to improve the odds that a lock will be released before
5501 the waiter decides to check for deadlock. Only superusers can change
5506 When <xref linkend="guc-log-lock-waits"> is set,
5507 this parameter also determines the length of time to wait before
5508 a log message is issued about the lock wait. If you are trying
5509 to investigate locking delays you might want to set a shorter than
5510 normal <varname>deadlock_timeout</varname>.
5515 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
5516 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
5518 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
5522 The shared lock table tracks locks on
5523 <varname>max_locks_per_transaction</varname> * (<xref
5524 linkend="guc-max-connections"> + <xref
5525 linkend="guc-max-prepared-transactions">) objects (e.g., tables);
5526 hence, no more than this many distinct objects can be locked at
5527 any one time. This parameter controls the average number of object
5528 locks allocated for each transaction; individual transactions
5529 can lock more objects as long as the locks of all transactions
5530 fit in the lock table. This is <emphasis>not</> the number of
5531 rows that can be locked; that value is unlimited. The default,
5532 64, has historically proven sufficient, but you might need to
5533 raise this value if you have clients that touch many different
5534 tables in a single transaction. This parameter can only be set at
5539 Increasing this parameter might cause <productname>PostgreSQL</>
5540 to request more <systemitem class="osname">System V</> shared
5541 memory than your operating system's default configuration
5542 allows. See <xref linkend="sysvipc"> for information on how to
5543 adjust those parameters, if necessary.
5547 When running a standby server, you must set this parameter to the
5548 same or higher value than on the master server. Otherwise, queries
5549 will not be allowed in the standby server.
5554 <varlistentry id="guc-max-pred-locks-per-transaction" xreflabel="max_pred_locks_per_transaction">
5555 <term><varname>max_pred_locks_per_transaction</varname> (<type>integer</type>)</term>
5557 <primary><varname>max_pred_locks_per_transaction</> configuration parameter</primary>
5561 The shared predicate lock table tracks locks on
5562 <varname>max_pred_locks_per_transaction</varname> * (<xref
5563 linkend="guc-max-connections"> + <xref
5564 linkend="guc-max-prepared-transactions">) objects (e.g., tables);
5565 hence, no more than this many distinct objects can be locked at
5566 any one time. This parameter controls the average number of object
5567 locks allocated for each transaction; individual transactions
5568 can lock more objects as long as the locks of all transactions
5569 fit in the lock table. This is <emphasis>not</> the number of
5570 rows that can be locked; that value is unlimited. The default,
5571 64, has generally been sufficient in testing, but you might need to
5572 raise this value if you have clients that touch many different
5573 tables in a single serializable transaction. This parameter can
5574 only be set at server start.
5578 Increasing this parameter might cause <productname>PostgreSQL</>
5579 to request more <systemitem class="osname">System V</> shared
5580 memory than your operating system's default configuration
5581 allows. See <xref linkend="sysvipc"> for information on how to
5582 adjust those parameters, if necessary.
5590 <sect1 id="runtime-config-compatible">
5591 <title>Version and Platform Compatibility</title>
5593 <sect2 id="runtime-config-compatible-version">
5594 <title>Previous PostgreSQL Versions</title>
5598 <varlistentry id="guc-array-nulls" xreflabel="array_nulls">
5599 <term><varname>array_nulls</varname> (<type>boolean</type>)</term>
5601 <primary><varname>array_nulls</> configuration parameter</primary>
5605 This controls whether the array input parser recognizes
5606 unquoted <literal>NULL</> as specifying a null array element.
5607 By default, this is <literal>on</>, allowing array values containing
5608 null values to be entered. However, <productname>PostgreSQL</> versions
5609 before 8.2 did not support null values in arrays, and therefore would
5610 treat <literal>NULL</> as specifying a normal array element with
5611 the string value <quote>NULL</>. For backward compatibility with
5612 applications that require the old behavior, this variable can be
5613 turned <literal>off</>.
5617 Note that it is possible to create array values containing null values
5618 even when this variable is <literal>off</>.
5623 <varlistentry id="guc-backslash-quote" xreflabel="backslash_quote">
5624 <term><varname>backslash_quote</varname> (<type>enum</type>)</term>
5625 <indexterm><primary>strings</><secondary>backslash quotes</></>
5627 <primary><varname>backslash_quote</> configuration parameter</primary>
5631 This controls whether a quote mark can be represented by
5632 <literal>\'</> in a string literal. The preferred, SQL-standard way
5633 to represent a quote mark is by doubling it (<literal>''</>) but
5634 <productname>PostgreSQL</> has historically also accepted
5635 <literal>\'</>. However, use of <literal>\'</> creates security risks
5636 because in some client character set encodings, there are multibyte
5637 characters in which the last byte is numerically equivalent to ASCII
5638 <literal>\</>. If client-side code does escaping incorrectly then a
5639 SQL-injection attack is possible. This risk can be prevented by
5640 making the server reject queries in which a quote mark appears to be
5641 escaped by a backslash.
5642 The allowed values of <varname>backslash_quote</> are
5643 <literal>on</> (allow <literal>\'</> always),
5644 <literal>off</> (reject always), and
5645 <literal>safe_encoding</> (allow only if client encoding does not
5646 allow ASCII <literal>\</> within a multibyte character).
5647 <literal>safe_encoding</> is the default setting.
5651 Note that in a standard-conforming string literal, <literal>\</> just
5652 means <literal>\</> anyway. This parameter only affects the handling of
5653 non-standard-conforming literals, including
5654 escape string syntax (<literal>E'...'</>).
5659 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
5660 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
5662 <primary><varname>default_with_oids</> configuration parameter</primary>
5666 This controls whether <command>CREATE TABLE</command> and
5667 <command>CREATE TABLE AS</command> include an OID column in
5668 newly-created tables, if neither <literal>WITH OIDS</literal>
5669 nor <literal>WITHOUT OIDS</literal> is specified. It also
5670 determines whether OIDs will be included in tables created by
5671 <command>SELECT INTO</command>. The parameter is <literal>off</>
5672 by default; in <productname>PostgreSQL</> 8.0 and earlier, it
5677 The use of OIDs in user tables is considered deprecated, so
5678 most installations should leave this variable disabled.
5679 Applications that require OIDs for a particular table should
5680 specify <literal>WITH OIDS</literal> when creating the
5681 table. This variable can be enabled for compatibility with old
5682 applications that do not follow this behavior.
5687 <varlistentry id="guc-escape-string-warning" xreflabel="escape_string_warning">
5688 <term><varname>escape_string_warning</varname> (<type>boolean</type>)</term>
5689 <indexterm><primary>strings</><secondary>escape warning</></>
5691 <primary><varname>escape_string_warning</> configuration parameter</primary>
5695 When on, a warning is issued if a backslash (<literal>\</>)
5696 appears in an ordinary string literal (<literal>'...'</>
5697 syntax) and <varname>standard_conforming_strings</varname> is off.
5698 The default is <literal>on</>.
5701 Applications that wish to use backslash as escape should be
5702 modified to use escape string syntax (<literal>E'...'</>),
5703 because the default behavior of ordinary strings is now to treat
5704 backslash as an ordinary character, per SQL standard. This variable
5705 can be enabled to help locate code that needs to be changed.
5710 <varlistentry id="guc-lo-compat-privileges" xreflabel="lo_compat_privileges">
5711 <term><varname>lo_compat_privileges</varname> (<type>boolean</type>)</term>
5713 <primary><varname>lo_compat_privileges</> configuration parameter</primary>
5717 In <productname>PostgreSQL</> releases prior to 9.0, large objects
5718 did not have access privileges and were, in effect, readable and
5719 writable by all users. Setting this variable to <literal>on</>
5720 disables the new privilege checks, for compatibility with prior
5721 releases. The default is <literal>off</>.
5724 Setting this variable does not disable all security checks related to
5725 large objects — only those for which the default behavior has
5726 changed in <productname>PostgreSQL</> 9.0.
5727 For example, <literal>lo_import()</literal> and
5728 <literal>lo_export()</literal> need superuser privileges independent
5734 <varlistentry id="guc-quote-all-identifiers" xreflabel="quote-all-identifiers">
5735 <term><varname>quote_all_identifiers</varname> (<type>boolean</type>)</term>
5737 <primary><varname>quote_all_identifiers</> configuration parameter</primary>
5741 When the database generates SQL, force all identifiers to be quoted,
5742 even if they are not (currently) keywords. This will affect the
5743 output of <command>EXPLAIN</> as well as the results of functions
5744 like <function>pg_get_viewdef</>. See also the
5745 <option>--quote-all-identifiers</option> option of
5746 <xref linkend="app-pgdump"> and <xref linkend="app-pg-dumpall">.
5751 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
5752 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
5754 <primary><varname>sql_inheritance</> configuration parameter</primary>
5756 <indexterm><primary>inheritance</></>
5759 This controls the inheritance semantics. If turned <literal>off</>,
5760 subtables are not accessed by various commands by default; basically
5761 an implied <literal>ONLY</literal> key word. This was added for
5762 compatibility with releases prior to 7.1. See
5763 <xref linkend="ddl-inherit"> for more information.
5768 <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
5769 <term><varname>standard_conforming_strings</varname> (<type>boolean</type>)</term>
5770 <indexterm><primary>strings</><secondary>standard conforming</></>
5772 <primary><varname>standard_conforming_strings</> configuration parameter</primary>
5776 This controls whether ordinary string literals
5777 (<literal>'...'</>) treat backslashes literally, as specified in
5779 Beginning in <productname>PostgreSQL</productname> 9.1, the default is
5780 <literal>on</> (prior releases defaulted to <literal>off</>).
5781 Applications can check this
5782 parameter to determine how string literals will be processed.
5783 The presence of this parameter can also be taken as an indication
5784 that the escape string syntax (<literal>E'...'</>) is supported.
5785 Escape string syntax (<xref linkend="sql-syntax-strings-escape">)
5786 should be used if an application desires
5787 backslashes to be treated as escape characters.
5792 <varlistentry id="guc-synchronize-seqscans" xreflabel="synchronize_seqscans">
5793 <term><varname>synchronize_seqscans</varname> (<type>boolean</type>)</term>
5795 <primary><varname>synchronize_seqscans</> configuration parameter</primary>
5799 This allows sequential scans of large tables to synchronize with each
5800 other, so that concurrent scans read the same block at about the
5801 same time and hence share the I/O workload. When this is enabled,
5802 a scan might start in the middle of the table and then <quote>wrap
5803 around</> the end to cover all rows, so as to synchronize with the
5804 activity of scans already in progress. This can result in
5805 unpredictable changes in the row ordering returned by queries that
5806 have no <literal>ORDER BY</> clause. Setting this parameter to
5807 <literal>off</> ensures the pre-8.3 behavior in which a sequential
5808 scan always starts from the beginning of the table. The default
5817 <sect2 id="runtime-config-compatible-clients">
5818 <title>Platform and Client Compatibility</title>
5821 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
5822 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
5823 <indexterm><primary>IS NULL</></>
5825 <primary><varname>transform_null_equals</> configuration parameter</primary>
5829 When on, expressions of the form <literal><replaceable>expr</> =
5830 NULL</literal> (or <literal>NULL =
5831 <replaceable>expr</></literal>) are treated as
5832 <literal><replaceable>expr</> IS NULL</literal>, that is, they
5833 return true if <replaceable>expr</> evaluates to the null value,
5834 and false otherwise. The correct SQL-spec-compliant behavior of
5835 <literal><replaceable>expr</> = NULL</literal> is to always
5836 return null (unknown). Therefore this parameter defaults to
5841 However, filtered forms in <productname>Microsoft
5842 Access</productname> generate queries that appear to use
5843 <literal><replaceable>expr</> = NULL</literal> to test for
5844 null values, so if you use that interface to access the database you
5845 might want to turn this option on. Since expressions of the
5846 form <literal><replaceable>expr</> = NULL</literal> always
5847 return the null value (using the SQL standard interpretation), they are not
5848 very useful and do not appear often in normal applications so
5849 this option does little harm in practice. But new users are
5850 frequently confused about the semantics of expressions
5851 involving null values, so this option is off by default.
5855 Note that this option only affects the exact form <literal>= NULL</>,
5856 not other comparison operators or other expressions
5857 that are computationally equivalent to some expression
5858 involving the equals operator (such as <literal>IN</literal>).
5859 Thus, this option is not a general fix for bad programming.
5863 Refer to <xref linkend="functions-comparison"> for related information.
5872 <sect1 id="runtime-config-error-handling">
5873 <title>Error Handling</title>
5877 <varlistentry id="guc-exit-on-error" xreflabel="exit_on_error">
5878 <term><varname>exit_on_error</varname> (<type>boolean</type>)</term>
5880 <primary><varname>exit_on_error</> configuration parameter</primary>
5884 If true, any error will terminate the current session. By default,
5885 this is set to false, so that only FATAL errors will terminate the
5891 <varlistentry id="guc-restart-after-crash" xreflabel="restart_after_crash">
5892 <term><varname>restart_after_crash</varname> (<type>boolean</type>)</term>
5894 <primary><varname>restart_after_crash</> configuration parameter</primary>
5898 When set to true, which is the default, <productname>PostgreSQL</>
5899 will automatically reinitialize after a backend crash. Leaving this
5900 value set to true is normally the best way to maximize the availability
5901 of the database. However, in some circumstances, such as when
5902 <productname>PostgreSQL</> is being invoked by clusterware, it may be
5903 useful to disable the restart so that the clusterware can gain
5904 control and take any actions it deems appropriate.
5913 <sect1 id="runtime-config-preset">
5914 <title>Preset Options</title>
5917 The following <quote>parameters</> are read-only, and are determined
5918 when <productname>PostgreSQL</productname> is compiled or when it is
5919 installed. As such, they have been excluded from the sample
5920 <filename>postgresql.conf</> file. These options report
5921 various aspects of <productname>PostgreSQL</productname> behavior
5922 that might be of interest to certain applications, particularly
5923 administrative front-ends.
5928 <varlistentry id="guc-block-size" xreflabel="block_size">
5929 <term><varname>block_size</varname> (<type>integer</type>)</term>
5931 <primary><varname>block_size</> configuration parameter</primary>
5935 Reports the size of a disk block. It is determined by the value
5936 of <literal>BLCKSZ</> when building the server. The default
5937 value is 8192 bytes. The meaning of some configuration
5938 variables (such as <xref linkend="guc-shared-buffers">) is
5939 influenced by <varname>block_size</varname>. See <xref
5940 linkend="runtime-config-resource"> for information.
5945 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
5946 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
5948 <primary><varname>integer_datetimes</> configuration parameter</primary>
5952 Reports whether <productname>PostgreSQL</> was built with
5953 support for 64-bit-integer dates and times. This can be
5954 disabled by configuring with <literal>--disable-integer-datetimes</>
5955 when building <productname>PostgreSQL</>. The default value is
5956 <literal>on</literal>.
5961 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
5962 <term><varname>lc_collate</varname> (<type>string</type>)</term>
5964 <primary><varname>lc_collate</> configuration parameter</primary>
5968 Reports the locale in which sorting of textual data is done.
5969 See <xref linkend="locale"> for more information.
5970 This value is determined when a database is created.
5975 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
5976 <term><varname>lc_ctype</varname> (<type>string</type>)</term>
5978 <primary><varname>lc_ctype</> configuration parameter</primary>
5982 Reports the locale that determines character classifications.
5983 See <xref linkend="locale"> for more information.
5984 This value is determined when a database is created.
5985 Ordinarily this will be the same as <varname>lc_collate</varname>,
5986 but for special applications it might be set differently.
5991 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
5992 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
5994 <primary><varname>max_function_args</> configuration parameter</primary>
5998 Reports the maximum number of function arguments. It is determined by
5999 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
6000 default value is 100 arguments.
6005 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
6006 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
6008 <primary><varname>max_identifier_length</> configuration parameter</primary>
6012 Reports the maximum identifier length. It is determined as one
6013 less than the value of <literal>NAMEDATALEN</> when building
6014 the server. The default value of <literal>NAMEDATALEN</> is
6015 64; therefore the default
6016 <varname>max_identifier_length</varname> is 63 bytes, which
6017 can be less than 63 characters when using multibyte encodings.
6022 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
6023 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
6025 <primary><varname>max_index_keys</> configuration parameter</primary>
6029 Reports the maximum number of index keys. It is determined by
6030 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
6031 default value is 32 keys.
6036 <varlistentry id="guc-segment-size" xreflabel="segment_size">
6037 <term><varname>segment_size</varname> (<type>integer</type>)</term>
6039 <primary><varname>segment_size</> configuration parameter</primary>
6043 Reports the number of blocks (pages) that can be stored within a file
6044 segment. It is determined by the value of <literal>RELSEG_SIZE</>
6045 when building the server. The maximum size of a segment file in bytes
6046 is equal to <varname>segment_size</> multiplied by
6047 <varname>block_size</>; by default this is 1GB.
6052 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
6053 <term><varname>server_encoding</varname> (<type>string</type>)</term>
6055 <primary><varname>server_encoding</> configuration parameter</primary>
6057 <indexterm><primary>character set</></>
6060 Reports the database encoding (character set).
6061 It is determined when the database is created. Ordinarily,
6062 clients need only be concerned with the value of <xref
6063 linkend="guc-client-encoding">.
6068 <varlistentry id="guc-server-version" xreflabel="server_version">
6069 <term><varname>server_version</varname> (<type>string</type>)</term>
6071 <primary><varname>server_version</> configuration parameter</primary>
6075 Reports the version number of the server. It is determined by the
6076 value of <literal>PG_VERSION</> when building the server.
6081 <varlistentry id="guc-server-version-num" xreflabel="server_version_num">
6082 <term><varname>server_version_num</varname> (<type>integer</type>)</term>
6084 <primary><varname>server_version_num</> configuration parameter</primary>
6088 Reports the version number of the server as an integer. It is determined
6089 by the value of <literal>PG_VERSION_NUM</> when building the server.
6094 <varlistentry id="guc-wal-block-size" xreflabel="wal_block_size">
6095 <term><varname>wal_block_size</varname> (<type>integer</type>)</term>
6097 <primary><varname>wal_block_size</> configuration parameter</primary>
6101 Reports the size of a WAL disk block. It is determined by the value
6102 of <literal>XLOG_BLCKSZ</> when building the server. The default value
6108 <varlistentry id="guc-wal-segment-size" xreflabel="wal_segment_size">
6109 <term><varname>wal_segment_size</varname> (<type>integer</type>)</term>
6111 <primary><varname>wal_segment_size</> configuration parameter</primary>
6115 Reports the number of blocks (pages) in a WAL segment file.
6116 The total size of a WAL segment file in bytes is equal to
6117 <varname>wal_segment_size</> multiplied by <varname>wal_block_size</>;
6118 by default this is 16MB. See <xref linkend="wal-configuration"> for
6127 <sect1 id="runtime-config-custom">
6128 <title>Customized Options</title>
6131 This feature was designed to allow parameters not normally known to
6132 <productname>PostgreSQL</productname> to be added by add-on modules
6133 (such as procedural languages). This allows extension modules to be
6134 configured in the standard ways.
6138 Custom options have two-part names: an extension name, then a dot, then
6139 the parameter name proper, much like qualified names in SQL. An example
6140 is <literal>plpgsql.variable_conflict</>.
6144 Because custom options may need to be set in processes that have not
6145 loaded the relevant extension module, <productname>PostgreSQL</>
6146 will accept a setting for any two-part parameter name. Such variables
6147 are treated as placeholders and have no function until the module that
6148 defines them is loaded. When an extension module is loaded, it will add
6149 its variable definitions, convert any placeholder values according to
6150 those definitions, and issue warnings for any unrecognized placeholders
6151 that begin with its extension name.
6155 <sect1 id="runtime-config-developer">
6156 <title>Developer Options</title>
6159 The following parameters are intended for work on the
6160 <productname>PostgreSQL</productname> source code, and in some cases
6161 to assist with recovery of severely damaged databases. There
6162 should be no reason to use them on a production database.
6163 As such, they have been excluded from the sample
6164 <filename>postgresql.conf</> file. Note that many of these
6165 parameters require special source compilation flags to work at all.
6169 <varlistentry id="guc-allow-system-table-mods" xreflabel="allow_system_table_mods">
6170 <term><varname>allow_system_table_mods</varname> (<type>boolean</type>)</term>
6172 <primary><varname>allow_system_table_mods</varname> configuration parameter</primary>
6176 Allows modification of the structure of system tables.
6177 This is used by <command>initdb</command>.
6178 This parameter can only be set at server start.
6183 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
6184 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
6186 <primary><varname>debug_assertions</> configuration parameter</primary>
6190 Turns on various assertion checks. This is a debugging aid. If
6191 you are experiencing strange problems or crashes you might want
6192 to turn this on, as it might expose programming mistakes. To use
6193 this parameter, the macro <symbol>USE_ASSERT_CHECKING</symbol>
6194 must be defined when <productname>PostgreSQL</productname> is
6195 built (accomplished by the <command>configure</command> option
6196 <option>--enable-cassert</option>). Note that
6197 <varname>debug_assertions</varname> defaults to <literal>on</>
6198 if <productname>PostgreSQL</productname> has been built with
6204 <varlistentry id="guc-ignore-system-indexes" xreflabel="ignore_system_indexes">
6205 <term><varname>ignore_system_indexes</varname> (<type>boolean</type>)</term>
6207 <primary><varname>ignore_system_indexes</varname> configuration parameter</primary>
6211 Ignore system indexes when reading system tables (but still
6212 update the indexes when modifying the tables). This is useful
6213 when recovering from damaged system indexes.
6214 This parameter cannot be changed after session start.
6219 <varlistentry id="guc-post-auth-delay" xreflabel="post_auth_delay">
6220 <term><varname>post_auth_delay</varname> (<type>integer</type>)</term>
6222 <primary><varname>post_auth_delay</> configuration parameter</primary>
6226 If nonzero, a delay of this many seconds occurs when a new
6227 server process is started, after it conducts the
6228 authentication procedure. This is intended to give developers an
6229 opportunity to attach to the server process with a debugger.
6230 This parameter cannot be changed after session start.
6235 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
6236 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
6238 <primary><varname>pre_auth_delay</> configuration parameter</primary>
6242 If nonzero, a delay of this many seconds occurs just after a
6243 new server process is forked, before it conducts the
6244 authentication procedure. This is intended to give developers an
6245 opportunity to attach to the server process with a debugger to
6246 trace down misbehavior in authentication.
6247 This parameter can only be set in the <filename>postgresql.conf</>
6248 file or on the server command line.
6253 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
6254 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
6256 <primary><varname>trace_notify</> configuration parameter</primary>
6260 Generates a great amount of debugging output for the
6261 <command>LISTEN</command> and <command>NOTIFY</command>
6262 commands. <xref linkend="guc-client-min-messages"> or
6263 <xref linkend="guc-log-min-messages"> must be
6264 <literal>DEBUG1</literal> or lower to send this output to the
6265 client or server logs, respectively.
6270 <varlistentry id="guc-trace-recovery-messages" xreflabel="trace_recovery_messages">
6271 <term><varname>trace_recovery_messages</varname> (<type>enum</type>)</term>
6273 <primary><varname>trace_recovery_messages</> configuration parameter</primary>
6277 Enables logging of recovery-related debugging output that otherwise
6278 would not be logged. This parameter allows the user to override the
6279 normal setting of <xref linkend="guc-log-min-messages">, but only for
6280 specific messages. This is intended for use in debugging Hot Standby.
6281 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
6282 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>, and
6283 <literal>LOG</>. The default, <literal>LOG</>, does not affect
6284 logging decisions at all. The other values cause recovery-related
6285 debug messages of that priority or higher to be logged as though they
6286 had <literal>LOG</> priority; for common settings of
6287 <varname>log_min_messages</> this results in unconditionally sending
6288 them to the server log.
6289 This parameter can only be set in the <filename>postgresql.conf</>
6290 file or on the server command line.
6295 <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
6296 <term><varname>trace_sort</varname> (<type>boolean</type>)</term>
6298 <primary><varname>trace_sort</> configuration parameter</primary>
6302 If on, emit information about resource usage during sort operations.
6303 This parameter is only available if the <symbol>TRACE_SORT</symbol> macro
6304 was defined when <productname>PostgreSQL</productname> was compiled.
6305 (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
6311 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
6313 <primary><varname>trace_locks</> configuration parameter</primary>
6317 If on, emit information about lock usage. Information dumped
6318 includes the type of lock operation, the type of lock and the unique
6319 identifier of the object being locked or unlocked. Also included
6320 are bit masks for the lock types already granted on this object as
6321 well as for the lock types awaited on this object. For each lock
6322 type a count of the number of granted locks and waiting locks is
6323 also dumped as well as the totals. An example of the log file output
6326 LOG: LockAcquire: new: lock(0xb7acd844) id(24688,24696,0,0,0,1)
6327 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
6328 wait(0) type(AccessShareLock)
6329 LOG: GrantLock: lock(0xb7acd844) id(24688,24696,0,0,0,1)
6330 grantMask(2) req(1,0,0,0,0,0,0)=1 grant(1,0,0,0,0,0,0)=1
6331 wait(0) type(AccessShareLock)
6332 LOG: UnGrantLock: updated: lock(0xb7acd844) id(24688,24696,0,0,0,1)
6333 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
6334 wait(0) type(AccessShareLock)
6335 LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
6336 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
6337 wait(0) type(INVALID)
6339 Details of the structure being dumped may be found in
6340 <filename>src/include/storage/lock.h</filename>.
6343 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6344 macro was defined when <productname>PostgreSQL</productname> was
6351 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
6353 <primary><varname>trace_lwlocks</> configuration parameter</primary>
6357 If on, emit information about lightweight lock usage. Lightweight
6358 locks are intended primarily to provide mutual exclusion of access
6359 to shared-memory data structures.
6362 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6363 macro was defined when <productname>PostgreSQL</productname> was
6370 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
6372 <primary><varname>trace_userlocks</> configuration parameter</primary>
6376 If on, emit information about user lock usage. Output is the same
6377 as for <symbol>trace_locks</symbol>, only for advisory locks.
6380 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6381 macro was defined when <productname>PostgreSQL</productname> was
6388 <term><varname>trace_lock_oidmin</varname> (<type>integer</type>)</term>
6390 <primary><varname>trace_lock_oidmin</> configuration parameter</primary>
6394 If set, do not trace locks for tables below this OID. (use to avoid
6395 output on system tables)
6398 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6399 macro was defined when <productname>PostgreSQL</productname> was
6406 <term><varname>trace_lock_table</varname> (<type>integer</type>)</term>
6408 <primary><varname>trace_lock_table</> configuration parameter</primary>
6412 Unconditionally trace locks on this table (OID).
6415 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6416 macro was defined when <productname>PostgreSQL</productname> was
6423 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
6425 <primary><varname>debug_deadlocks</> configuration parameter</primary>
6429 If set, dumps information about all current locks when a
6430 deadlock timeout occurs.
6433 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
6434 macro was defined when <productname>PostgreSQL</productname> was
6441 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
6443 <primary><varname>log_btree_build_stats</> configuration parameter</primary>
6447 If set, logs system resource usage statistics (memory and CPU) on
6448 various B-tree operations.
6451 This parameter is only available if the <symbol>BTREE_BUILD_STATS</symbol>
6452 macro was defined when <productname>PostgreSQL</productname> was
6458 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
6459 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
6461 <primary><varname>wal_debug</> configuration parameter</primary>
6465 If on, emit WAL-related debugging output. This parameter is
6466 only available if the <symbol>WAL_DEBUG</symbol> macro was
6467 defined when <productname>PostgreSQL</productname> was
6473 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
6474 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
6476 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
6480 Detection of a damaged page header normally causes
6481 <productname>PostgreSQL</> to report an error, aborting the current
6482 transaction. Setting <varname>zero_damaged_pages</> to on causes
6483 the system to instead report a warning, zero out the damaged
6484 page in memory, and continue processing. This behavior <emphasis>will destroy data</>,
6485 namely all the rows on the damaged page. However, it does allow you to get
6486 past the error and retrieve rows from any undamaged pages that might
6487 be present in the table. It is useful for recovering data if
6488 corruption has occurred due to a hardware or software error. You should
6489 generally not set this on until you have given up hope of recovering
6490 data from the damaged pages of a table. Zeroed-out pages are not
6491 forced to disk so it is recommended to recreate the table or
6492 the index before turning this parameter off again. The
6493 default setting is <literal>off</>, and it can only be changed
6500 <sect1 id="runtime-config-short">
6501 <title>Short Options</title>
6504 For convenience there are also single letter command-line option
6505 switches available for some parameters. They are described in
6506 <xref linkend="runtime-config-short-table">. Some of these
6507 options exist for historical reasons, and their presence as a
6508 single-letter option does not necessarily indicate an endorsement
6509 to use the option heavily.
6512 <table id="runtime-config-short-table">
6513 <title>Short Option Key</title>
6517 <entry>Short Option</entry>
6518 <entry>Equivalent</entry>
6524 <entry><option>-A <replaceable>x</replaceable></option></entry>
6525 <entry><literal>debug_assertions = <replaceable>x</replaceable></></entry>
6528 <entry><option>-B <replaceable>x</replaceable></option></entry>
6529 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
6532 <entry><option>-d <replaceable>x</replaceable></option></entry>
6533 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
6536 <entry><option>-e</option></entry>
6537 <entry><literal>datestyle = euro</></entry>
6541 <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
6542 <option>-fm</option>, <option>-fn</option>, <option>-fo</option>,
6543 <option>-fs</option>, <option>-ft</option>
6546 <literal>enable_bitmapscan = off</>,
6547 <literal>enable_hashjoin = off</>,
6548 <literal>enable_indexscan = off</>,
6549 <literal>enable_mergejoin = off</>,
6550 <literal>enable_nestloop = off</>,
6551 <literal>enable_indexonlyscan = off</>,
6552 <literal>enable_seqscan = off</>,
6553 <literal>enable_tidscan = off</>
6557 <entry><option>-F</option></entry>
6558 <entry><literal>fsync = off</></entry>
6561 <entry><option>-h <replaceable>x</replaceable></option></entry>
6562 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
6565 <entry><option>-i</option></entry>
6566 <entry><literal>listen_addresses = '*'</></entry>
6569 <entry><option>-k <replaceable>x</replaceable></option></entry>
6570 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
6573 <entry><option>-l</option></entry>
6574 <entry><literal>ssl = on</></entry>
6577 <entry><option>-N <replaceable>x</replaceable></option></entry>
6578 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
6581 <entry><option>-O</option></entry>
6582 <entry><literal>allow_system_table_mods = on</></entry>
6585 <entry><option>-p <replaceable>x</replaceable></option></entry>
6586 <entry><literal>port = <replaceable>x</replaceable></></entry>
6589 <entry><option>-P</option></entry>
6590 <entry><literal>ignore_system_indexes = on</></entry>
6593 <entry><option>-s</option></entry>
6594 <entry><literal>log_statement_stats = on</></entry>
6597 <entry><option>-S <replaceable>x</replaceable></option></entry>
6598 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
6601 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option></entry>
6602 <entry><literal>log_parser_stats = on</>,
6603 <literal>log_planner_stats = on</>,
6604 <literal>log_executor_stats = on</></entry>
6607 <entry><option>-W <replaceable>x</replaceable></option></entry>
6608 <entry><literal>post_auth_delay = <replaceable>x</replaceable></></entry>