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 interact with configuration parameters. The subsequent sections
15 discuss each parameter in detail.
18 <sect1 id="config-setting">
19 <title>Setting Parameters</title>
21 <sect2 id="config-setting-names-values">
22 <title>Parameter Names and Values</title>
25 All parameter names are case-insensitive. Every parameter takes a
26 value of one of five types: boolean, string, integer, floating point,
27 or enumerated (enum). The type determines the syntax for setting the
34 <emphasis>Boolean:</emphasis>
35 Values can be written as
36 <literal>on</literal>,
37 <literal>off</literal>,
38 <literal>true</literal>,
39 <literal>false</literal>,
40 <literal>yes</literal>,
41 <literal>no</literal>,
44 (all case-insensitive) or any unambiguous prefix of one of these.
50 <emphasis>String:</emphasis>
51 In general, enclose the value in single quotes, doubling any single
52 quotes within the value. Quotes can usually be omitted if the value
53 is a simple number or identifier, however.
59 <emphasis>Numeric (integer and floating point):</emphasis>
60 A decimal point is permitted only for floating-point parameters.
61 Do not use thousands separators. Quotes are not required.
67 <emphasis>Numeric with Unit:</emphasis>
68 Some numeric parameters have an implicit unit, because they describe
69 quantities of memory or time. The unit might be kilobytes, blocks
70 (typically eight kilobytes), milliseconds, seconds, or minutes.
71 An unadorned numeric value for one of these settings will use the
72 setting's default unit, which can be learned from
73 <structname>pg_settings</>.<structfield>unit</>.
74 For convenience, settings can be given with a unit specified explicitly,
75 for example <literal>'120 ms'</> for a time value, and they will be
76 converted to whatever the parameter's actual unit is. Note that the
77 value must be written as a string (with quotes) to use this feature.
78 The unit name is case-sensitive, and there can be whitespace between
79 the numeric value and the unit.
84 Valid memory units are <literal>kB</literal> (kilobytes),
85 <literal>MB</literal> (megabytes), <literal>GB</literal>
86 (gigabytes), and <literal>TB</literal> (terabytes).
87 The multiplier for memory units is 1024, not 1000.
93 Valid time units are <literal>ms</literal> (milliseconds),
94 <literal>s</literal> (seconds), <literal>min</literal> (minutes),
95 <literal>h</literal> (hours), and <literal>d</literal> (days).
104 <emphasis>Enumerated:</emphasis>
105 Enumerated-type parameters are written in the same way as string
106 parameters, but are restricted to have one of a limited set of
107 values. The values allowable for such a parameter can be found from
108 <structname>pg_settings</>.<structfield>enumvals</>.
109 Enum parameter values are case-insensitive.
115 <sect2 id="config-setting-configuration-file">
116 <title>Parameter Interaction via the Configuration File</title>
119 The most fundamental way to set these parameters is to edit the file
120 <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>,
121 which is normally kept in the data directory. A default copy is
122 installed when the database cluster directory is initialized.
123 An example of what this file might look like is:
126 log_connections = yes
127 log_destination = 'syslog'
128 search_path = '"$user", public'
129 shared_buffers = 128MB
131 One parameter is specified per line. The equal sign between name and
132 value is optional. Whitespace is insignificant (except within a quoted
133 parameter value) and blank lines are
134 ignored. Hash marks (<literal>#</literal>) designate the remainder
135 of the line as a comment. Parameter values that are not simple
136 identifiers or numbers must be single-quoted. To embed a single
137 quote in a parameter value, write either two quotes (preferred)
142 Parameters set in this way provide default values for the cluster.
143 The settings seen by active sessions will be these values unless they
144 are overridden. The following sections describe ways in which the
145 administrator or user can override these defaults.
150 <primary>SIGHUP</primary>
152 The configuration file is reread whenever the main server process
153 receives a <systemitem>SIGHUP</> signal; this signal is most easily
154 sent by running <literal>pg_ctl reload</> from the command line or by
155 calling the SQL function <function>pg_reload_conf()</function>. The main
156 server process also propagates this signal to all currently running
157 server processes, so that existing sessions also adopt the new values
158 (this will happen after they complete any currently-executing client
159 command). Alternatively, you can
160 send the signal to a single server process directly. Some parameters
161 can only be set at server start; any changes to their entries in the
162 configuration file will be ignored until the server is restarted.
163 Invalid parameter settings in the configuration file are likewise
164 ignored (but logged) during <systemitem>SIGHUP</> processing.
168 In addition to <filename>postgresql.conf</>,
169 a <productname>PostgreSQL</productname> data directory contains a file
170 <filename>postgresql.auto.conf</><indexterm><primary>postgresql.auto.conf</></>,
171 which has the same format as <filename>postgresql.conf</> but should
172 never be edited manually. This file holds settings provided through
173 the <xref linkend="SQL-ALTERSYSTEM"> command. This file is automatically
174 read whenever <filename>postgresql.conf</> is, and its settings take
175 effect in the same way. Settings in <filename>postgresql.auto.conf</>
176 override those in <filename>postgresql.conf</>.
181 <link linkend="view-pg-file-settings"><structname>pg_file_settings</structname></link>
182 can be helpful for pre-testing changes to the configuration file, or for
183 diagnosing problems if a <systemitem>SIGHUP</> signal did not have the
188 <sect2 id="config-setting-sql-command-interaction">
189 <title>Parameter Interaction via SQL</title>
192 <productname>PostgreSQL</productname> provides three SQL
193 commands to establish configuration defaults.
194 The already-mentioned <xref linkend="SQL-ALTERSYSTEM"> command
195 provides a SQL-accessible means of changing global defaults; it is
196 functionally equivalent to editing <filename>postgresql.conf</>.
197 In addition, there are two commands that allow setting of defaults
198 on a per-database or per-role basis:
204 The <xref linkend="sql-alterdatabase"> command allows global
205 settings to be overridden on a per-database basis.
211 The <xref linkend="sql-alterrole"> command allows both global and
212 per-database settings to be overridden with user-specific values.
218 Values set with <command>ALTER DATABASE</> and <command>ALTER ROLE</>
219 are applied only when starting a fresh database session. They
220 override values obtained from the configuration files or server
221 command line, and constitute defaults for the rest of the session.
222 Note that some settings cannot be changed after server start, and
223 so cannot be set with these commands (or the ones listed below).
227 Once a client is connected to the database, <productname>PostgreSQL</>
228 provides two additional SQL commands (and equivalent functions) to
229 interact with session-local configuration settings:
235 The <xref linkend="SQL-SHOW"> command allows inspection of the
236 current value of all parameters. The corresponding function is
237 <function>current_setting(setting_name text)</function>.
243 The <xref linkend="SQL-SET"> command allows modification of the
244 current value of those parameters that can be set locally to a
245 session; it has no effect on other sessions.
246 The corresponding function is
247 <function>set_config(setting_name, new_value, is_local)</function>.
253 In addition, the system view <link
254 linkend="view-pg-settings"><structname>pg_settings</></> can be
255 used to view and change session-local values:
261 Querying this view is similar to using <command>SHOW ALL</> but
262 provides more detail. It is also more flexible, since it's possible
263 to specify filter conditions or join against other relations.
269 Using <xref linkend="SQL-UPDATE"> on this view, specifically
270 updating the <structname>setting</> column, is the equivalent
271 of issuing <command>SET</> commands. For example, the equivalent of
273 SET configuration_parameter TO DEFAULT;
277 UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';
286 <title>Parameter Interaction via the Shell</title>
289 In addition to setting global defaults or attaching
290 overrides at the database or role level, you can pass settings to
291 <productname>PostgreSQL</productname> via shell facilities.
292 Both the server and <application>libpq</> client library
293 accept parameter values via the shell.
299 During server startup, parameter settings can be
300 passed to the <command>postgres</command> command via the
301 <option>-c</> command-line parameter. For example,
303 postgres -c log_connections=yes -c log_destination='syslog'
305 Settings provided in this way override those set via
306 <filename>postgresql.conf</> or <command>ALTER SYSTEM</>,
307 so they cannot be changed globally without restarting the server.
313 When starting a client session via <application>libpq</>,
314 parameter settings can be
315 specified using the <envar>PGOPTIONS</envar> environment variable.
316 Settings established in this way constitute defaults for the life
317 of the session, but do not affect other sessions.
318 For historical reasons, the format of <envar>PGOPTIONS</envar> is
319 similar to that used when launching the <command>postgres</command>
320 command; specifically, the <option>-c</> flag must be specified.
323 env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql
328 Other clients and libraries might provide their own mechanisms,
329 via the shell or otherwise, that allow the user to alter session
330 settings without direct use of SQL commands.
337 <sect2 id="config-includes">
338 <title>Managing Configuration File Contents</title>
341 <productname>PostgreSQL</> provides several features for breaking
342 down complex <filename>postgresql.conf</> files into sub-files.
343 These features are especially useful when managing multiple servers
344 with related, but not identical, configurations.
349 <primary><literal>include</></primary>
350 <secondary>in configuration file</secondary>
352 In addition to individual parameter settings,
353 the <filename>postgresql.conf</> file can contain <firstterm>include
354 directives</>, which specify another file to read and process as if
355 it were inserted into the configuration file at this point. This
356 feature allows a configuration file to be divided into physically
357 separate parts. Include directives simply look like:
361 If the file name is not an absolute path, it is taken as relative to
362 the directory containing the referencing configuration file.
363 Inclusions can be nested.
368 <primary><literal>include_if_exists</></primary>
369 <secondary>in configuration file</secondary>
371 There is also an <literal>include_if_exists</> directive, which acts
372 the same as the <literal>include</> directive, except
373 when the referenced file does not exist or cannot be read. A regular
374 <literal>include</> will consider this an error condition, but
375 <literal>include_if_exists</> merely logs a message and continues
376 processing the referencing configuration file.
381 <primary><literal>include_dir</></primary>
382 <secondary>in configuration file</secondary>
384 The <filename>postgresql.conf</> file can also contain
385 <literal>include_dir</literal> directives, which specify an entire
386 directory of configuration files to include. These look like
388 include_dir 'directory'
390 Non-absolute directory names are taken as relative to the directory
391 containing the referencing configuration file. Within the specified
392 directory, only non-directory files whose names end with the
393 suffix <literal>.conf</literal> will be included. File names that
394 start with the <literal>.</literal> character are also ignored, to
395 prevent mistakes since such files are hidden on some platforms. Multiple
396 files within an include directory are processed in file name order
397 (according to C locale rules, i.e. numbers before letters, and
398 uppercase letters before lowercase ones).
402 Include files or directories can be used to logically separate portions
403 of the database configuration, rather than having a single large
404 <filename>postgresql.conf</> file. Consider a company that has two
405 database servers, each with a different amount of memory. There are
406 likely elements of the configuration both will share, for things such
407 as logging. But memory-related parameters on the server will vary
408 between the two. And there might be server specific customizations,
409 too. One way to manage this situation is to break the custom
410 configuration changes for your site into three files. You could add
411 this to the end of your <filename>postgresql.conf</> file to include
414 include 'shared.conf'
415 include 'memory.conf'
416 include 'server.conf'
418 All systems would have the same <filename>shared.conf</>. Each
419 server with a particular amount of memory could share the
420 same <filename>memory.conf</>; you might have one for all servers
421 with 8GB of RAM, another for those having 16GB. And
422 finally <filename>server.conf</> could have truly server-specific
423 configuration information in it.
427 Another possibility is to create a configuration file directory and
428 put this information into files there. For example, a <filename>conf.d</>
429 directory could be referenced at the end of <filename>postgresql.conf</>:
433 Then you could name the files in the <filename>conf.d</> directory
440 This naming convention establishes a clear order in which these
441 files will be loaded. This is important because only the last
442 setting encountered for a particular parameter while the server is
443 reading configuration files will be used. In this example,
444 something set in <filename>conf.d/02server.conf</> would override a
445 value set in <filename>conf.d/01memory.conf</>.
449 You might instead use this approach to naming the files
456 This sort of arrangement gives a unique name for each configuration file
457 variation. This can help eliminate ambiguity when several servers have
458 their configurations all stored in one place, such as in a version
459 control repository. (Storing database configuration files under version
460 control is another good practice to consider.)
465 <sect1 id="runtime-config-file-locations">
466 <title>File Locations</title>
469 In addition to the <filename>postgresql.conf</filename> file
470 already mentioned, <productname>PostgreSQL</productname> uses
471 two other manually-edited configuration files, which control
472 client authentication (their use is discussed in <xref
473 linkend="client-authentication">). By default, all three
474 configuration files are stored in the database cluster's data
475 directory. The parameters described in this section allow the
476 configuration files to be placed elsewhere. (Doing so can ease
477 administration. In particular it is often easier to ensure that
478 the configuration files are properly backed-up when they are
483 <varlistentry id="guc-data-directory" xreflabel="data_directory">
484 <term><varname>data_directory</varname> (<type>string</type>)
486 <primary><varname>data_directory</> configuration parameter</primary>
491 Specifies the directory to use for data storage.
492 This parameter can only be set at server start.
497 <varlistentry id="guc-config-file" xreflabel="config_file">
498 <term><varname>config_file</varname> (<type>string</type>)
500 <primary><varname>config_file</> configuration parameter</primary>
505 Specifies the main server configuration file
506 (customarily called <filename>postgresql.conf</>).
507 This parameter can only be set on the <command>postgres</command> command line.
512 <varlistentry id="guc-hba-file" xreflabel="hba_file">
513 <term><varname>hba_file</varname> (<type>string</type>)
515 <primary><varname>hba_file</> configuration parameter</primary>
520 Specifies the configuration file for host-based authentication
521 (customarily called <filename>pg_hba.conf</>).
522 This parameter can only be set at server start.
527 <varlistentry id="guc-ident-file" xreflabel="ident_file">
528 <term><varname>ident_file</varname> (<type>string</type>)
530 <primary><varname>ident_file</> configuration parameter</primary>
535 Specifies the configuration file for
536 <xref linkend="auth-username-maps"> user name mapping
537 (customarily called <filename>pg_ident.conf</>).
538 This parameter can only be set at server start.
543 <varlistentry id="guc-external-pid-file" xreflabel="external_pid_file">
544 <term><varname>external_pid_file</varname> (<type>string</type>)
546 <primary><varname>external_pid_file</> configuration parameter</primary>
551 Specifies the name of an additional process-ID (PID) file that the
552 server should create for use by server administration programs.
553 This parameter can only be set at server start.
560 In a default installation, none of the above parameters are set
561 explicitly. Instead, the
562 data directory is specified by the <option>-D</option> command-line
563 option or the <envar>PGDATA</envar> environment variable, and the
564 configuration files are all found within the data directory.
568 If you wish to keep the configuration files elsewhere than the
569 data directory, the <command>postgres</command> <option>-D</option>
570 command-line option or <envar>PGDATA</envar> environment variable
571 must point to the directory containing the configuration files,
572 and the <varname>data_directory</> parameter must be set in
573 <filename>postgresql.conf</filename> (or on the command line) to show
574 where the data directory is actually located. Notice that
575 <varname>data_directory</> overrides <option>-D</option> and
576 <envar>PGDATA</envar> for the location
577 of the data directory, but not for the location of the configuration
582 If you wish, you can specify the configuration file names and locations
583 individually using the parameters <varname>config_file</>,
584 <varname>hba_file</> and/or <varname>ident_file</>.
585 <varname>config_file</> can only be specified on the
586 <command>postgres</command> command line, but the others can be
587 set within the main configuration file. If all three parameters plus
588 <varname>data_directory</> are explicitly set, then it is not necessary
589 to specify <option>-D</option> or <envar>PGDATA</envar>.
593 When setting any of these parameters, a relative path will be interpreted
594 with respect to the directory in which <command>postgres</command>
599 <sect1 id="runtime-config-connection">
600 <title>Connections and Authentication</title>
602 <sect2 id="runtime-config-connection-settings">
603 <title>Connection Settings</title>
607 <varlistentry id="guc-listen-addresses" xreflabel="listen_addresses">
608 <term><varname>listen_addresses</varname> (<type>string</type>)
610 <primary><varname>listen_addresses</> configuration parameter</primary>
615 Specifies the TCP/IP address(es) on which the server is
616 to listen for connections from client applications.
617 The value takes the form of a comma-separated list of host names
618 and/or numeric IP addresses. The special entry <literal>*</>
619 corresponds to all available IP interfaces. The entry
620 <literal>0.0.0.0</> allows listening for all IPv4 addresses and
621 <literal>::</> allows listening for all IPv6 addresses.
622 If the list is empty, the server does not listen on any IP interface
623 at all, in which case only Unix-domain sockets can be used to connect
625 The default value is <systemitem class="systemname">localhost</>,
626 which allows only local TCP/IP <quote>loopback</> connections to be
627 made. While client authentication (<xref
628 linkend="client-authentication">) allows fine-grained control
629 over who can access the server, <varname>listen_addresses</varname>
630 controls which interfaces accept connection attempts, which
631 can help prevent repeated malicious connection requests on
632 insecure network interfaces. This parameter can only be set
638 <varlistentry id="guc-port" xreflabel="port">
639 <term><varname>port</varname> (<type>integer</type>)
641 <primary><varname>port</> configuration parameter</primary>
646 The TCP port the server listens on; 5432 by default. Note that the
647 same port number is used for all IP addresses the server listens on.
648 This parameter can only be set at server start.
653 <varlistentry id="guc-max-connections" xreflabel="max_connections">
654 <term><varname>max_connections</varname> (<type>integer</type>)
656 <primary><varname>max_connections</> configuration parameter</primary>
661 Determines the maximum number of concurrent connections to the
662 database server. The default is typically 100 connections, but
663 might be less if your kernel settings will not support it (as
664 determined during <application>initdb</>). This parameter can
665 only be set at server start.
669 When running a standby server, you must set this parameter to the
670 same or higher value than on the master server. Otherwise, queries
671 will not be allowed in the standby server.
676 <varlistentry id="guc-superuser-reserved-connections"
677 xreflabel="superuser_reserved_connections">
678 <term><varname>superuser_reserved_connections</varname>
679 (<type>integer</type>)
681 <primary><varname>superuser_reserved_connections</> configuration parameter</primary>
686 Determines the number of connection <quote>slots</quote> that
687 are reserved for connections by <productname>PostgreSQL</>
688 superusers. At most <xref linkend="guc-max-connections">
689 connections can ever be active simultaneously. Whenever the
690 number of active concurrent connections is at least
691 <varname>max_connections</> minus
692 <varname>superuser_reserved_connections</varname>, new
693 connections will be accepted only for superusers, and no
694 new replication connections will be accepted.
698 The default value is three connections. The value must be less
699 than the value of <varname>max_connections</varname>. This
700 parameter can only be set at server start.
705 <varlistentry id="guc-unix-socket-directories" xreflabel="unix_socket_directories">
706 <term><varname>unix_socket_directories</varname> (<type>string</type>)
708 <primary><varname>unix_socket_directories</> configuration parameter</primary>
713 Specifies the directory of the Unix-domain socket(s) on which the
714 server is to listen for connections from client applications.
715 Multiple sockets can be created by listing multiple directories
716 separated by commas. Whitespace between entries is
717 ignored; surround a directory name with double quotes if you need
718 to include whitespace or commas in the name.
720 specifies not listening on any Unix-domain sockets, in which case
721 only TCP/IP sockets can be used to connect to the server.
722 The default value is normally
723 <filename>/tmp</filename>, but that can be changed at build time.
724 This parameter can only be set at server start.
728 In addition to the socket file itself, which is named
729 <literal>.s.PGSQL.<replaceable>nnnn</></literal> where
730 <replaceable>nnnn</> is the server's port number, an ordinary file
731 named <literal>.s.PGSQL.<replaceable>nnnn</>.lock</literal> will be
732 created in each of the <varname>unix_socket_directories</> directories.
733 Neither file should ever be removed manually.
737 This parameter is irrelevant on Windows, which does not have
743 <varlistentry id="guc-unix-socket-group" xreflabel="unix_socket_group">
744 <term><varname>unix_socket_group</varname> (<type>string</type>)
746 <primary><varname>unix_socket_group</> configuration parameter</primary>
751 Sets the owning group of the Unix-domain socket(s). (The owning
752 user of the sockets is always the user that starts the
753 server.) In combination with the parameter
754 <varname>unix_socket_permissions</varname> this can be used as
755 an additional access control mechanism for Unix-domain connections.
756 By default this is the empty string, which uses the default
757 group of the server user. This parameter can only be set at
762 This parameter is irrelevant on Windows, which does not have
768 <varlistentry id="guc-unix-socket-permissions" xreflabel="unix_socket_permissions">
769 <term><varname>unix_socket_permissions</varname> (<type>integer</type>)
771 <primary><varname>unix_socket_permissions</> configuration parameter</primary>
776 Sets the access permissions of the Unix-domain socket(s). Unix-domain
777 sockets use the usual Unix file system permission set.
778 The parameter value is expected to be a numeric mode
779 specified in the format accepted by the
780 <function>chmod</function> and <function>umask</function>
781 system calls. (To use the customary octal format the number
782 must start with a <literal>0</literal> (zero).)
786 The default permissions are <literal>0777</literal>, meaning
787 anyone can connect. Reasonable alternatives are
788 <literal>0770</literal> (only user and group, see also
789 <varname>unix_socket_group</varname>) and <literal>0700</literal>
790 (only user). (Note that for a Unix-domain socket, only write
791 permission matters, so there is no point in setting or revoking
792 read or execute permissions.)
796 This access control mechanism is independent of the one
797 described in <xref linkend="client-authentication">.
801 This parameter can only be set at server start.
805 This parameter is irrelevant on systems, notably Solaris as of Solaris
806 10, that ignore socket permissions entirely. There, one can achieve a
807 similar effect by pointing <varname>unix_socket_directories</> to a
808 directory having search permission limited to the desired audience.
809 This parameter is also irrelevant on Windows, which does not have
815 <varlistentry id="guc-bonjour" xreflabel="bonjour">
816 <term><varname>bonjour</varname> (<type>boolean</type>)
818 <primary><varname>bonjour</> configuration parameter</primary>
823 Enables advertising the server's existence via
824 <productname>Bonjour</productname>. The default is off.
825 This parameter can only be set at server start.
830 <varlistentry id="guc-bonjour-name" xreflabel="bonjour_name">
831 <term><varname>bonjour_name</varname> (<type>string</type>)
833 <primary><varname>bonjour_name</> configuration parameter</primary>
838 Specifies the <productname>Bonjour</productname> service
839 name. The computer name is used if this parameter is set to the
840 empty string <literal>''</> (which is the default). This parameter is
841 ignored if the server was not compiled with
842 <productname>Bonjour</productname> support.
843 This parameter can only be set at server start.
848 <varlistentry id="guc-tcp-keepalives-idle" xreflabel="tcp_keepalives_idle">
849 <term><varname>tcp_keepalives_idle</varname> (<type>integer</type>)
851 <primary><varname>tcp_keepalives_idle</> configuration parameter</primary>
856 Specifies the number of seconds of inactivity after which TCP
857 should send a keepalive message to the client. A value of 0 uses
859 This parameter is supported only on systems that support the
860 <symbol>TCP_KEEPIDLE</> or <symbol>TCP_KEEPALIVE</> symbols, and on
861 Windows; on other systems, it must be zero.
862 In sessions connected via a Unix-domain socket, this parameter is
863 ignored and always reads as zero.
867 On Windows, a value of 0 will set this parameter to 2 hours,
868 since Windows does not provide a way to read the system default value.
874 <varlistentry id="guc-tcp-keepalives-interval" xreflabel="tcp_keepalives_interval">
875 <term><varname>tcp_keepalives_interval</varname> (<type>integer</type>)
877 <primary><varname>tcp_keepalives_interval</> configuration parameter</primary>
882 Specifies the number of seconds after which a TCP keepalive message
883 that is not acknowledged by the client should be retransmitted.
884 A value of 0 uses the system default.
885 This parameter is supported only on systems that support the
886 <symbol>TCP_KEEPINTVL</> symbol, and on Windows; on other systems, it
888 In sessions connected via a Unix-domain socket, this parameter is
889 ignored and always reads as zero.
893 On Windows, a value of 0 will set this parameter to 1 second,
894 since Windows does not provide a way to read the system default value.
900 <varlistentry id="guc-tcp-keepalives-count" xreflabel="tcp_keepalives_count">
901 <term><varname>tcp_keepalives_count</varname> (<type>integer</type>)
903 <primary><varname>tcp_keepalives_count</> configuration parameter</primary>
908 Specifies the number of TCP keepalives that can be lost before
909 the server's connection to the client is considered dead. A value of 0
910 uses the system default. This parameter is
911 supported only on systems that support the <symbol>TCP_KEEPCNT</>
912 symbol; on other systems, it must be zero.
913 In sessions connected via a Unix-domain socket, this parameter is
914 ignored and always reads as zero.
918 This parameter is not supported on Windows, and must be zero.
926 <sect2 id="runtime-config-connection-security">
927 <title>Security and Authentication</title>
930 <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
931 <term><varname>authentication_timeout</varname> (<type>integer</type>)
932 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
933 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
935 <primary><varname>authentication_timeout</> configuration parameter</primary>
941 Maximum time to complete client authentication, in seconds. If a
942 would-be client has not completed the authentication protocol in
943 this much time, the server closes the connection. This prevents
944 hung clients from occupying a connection indefinitely.
945 The default is one minute (<literal>1m</>).
946 This parameter can only be set in the <filename>postgresql.conf</>
947 file or on the server command line.
952 <varlistentry id="guc-ssl" xreflabel="ssl">
953 <term><varname>ssl</varname> (<type>boolean</type>)
955 <primary><varname>ssl</> configuration parameter</primary>
960 Enables <acronym>SSL</> connections. Please read
961 <xref linkend="ssl-tcp"> before using this. The default
962 is <literal>off</>. This parameter can only be set at server
963 start. <acronym>SSL</> communication is only possible with
969 <varlistentry id="guc-ssl-ca-file" xreflabel="ssl_ca_file">
970 <term><varname>ssl_ca_file</varname> (<type>string</type>)
972 <primary><varname>ssl_ca_file</> configuration parameter</primary>
977 Specifies the name of the file containing the SSL server certificate
978 authority (CA). The default is empty, meaning no CA file is loaded,
979 and client certificate verification is not performed. (In previous
980 releases of PostgreSQL, the name of this file was hard-coded
981 as <filename>root.crt</filename>.) Relative paths are relative to the
982 data directory. This parameter can only be set at server start.
987 <varlistentry id="guc-ssl-cert-file" xreflabel="ssl_cert_file">
988 <term><varname>ssl_cert_file</varname> (<type>string</type>)
990 <primary><varname>ssl_cert_file</> configuration parameter</primary>
995 Specifies the name of the file containing the SSL server certificate.
996 The default is <filename>server.crt</filename>. Relative paths are
997 relative to the data directory. This parameter can only be set at
1003 <varlistentry id="guc-ssl-crl-file" xreflabel="ssl_crl_file">
1004 <term><varname>ssl_crl_file</varname> (<type>string</type>)
1006 <primary><varname>ssl_crl_file</> configuration parameter</primary>
1011 Specifies the name of the file containing the SSL server certificate
1012 revocation list (CRL). The default is empty, meaning no CRL file is
1013 loaded. (In previous releases of PostgreSQL, the name of this file was
1014 hard-coded as <filename>root.crl</filename>.) Relative paths are
1015 relative to the data directory. This parameter can only be set at
1021 <varlistentry id="guc-ssl-key-file" xreflabel="ssl_key_file">
1022 <term><varname>ssl_key_file</varname> (<type>string</type>)
1024 <primary><varname>ssl_key_file</> configuration parameter</primary>
1029 Specifies the name of the file containing the SSL server private key.
1030 The default is <filename>server.key</filename>. Relative paths are
1031 relative to the data directory. This parameter can only be set at
1037 <varlistentry id="guc-ssl-ciphers" xreflabel="ssl_ciphers">
1038 <term><varname>ssl_ciphers</varname> (<type>string</type>)
1040 <primary><varname>ssl_ciphers</> configuration parameter</primary>
1045 Specifies a list of <acronym>SSL</> cipher suites that are allowed to be
1046 used on secure connections. See
1047 the <citerefentry><refentrytitle>ciphers</></citerefentry> manual page
1048 in the <application>OpenSSL</> package for the syntax of this setting
1049 and a list of supported values. The default value is
1050 <literal>HIGH:MEDIUM:+3DES:!aNULL</>. It is usually reasonable,
1051 unless you have specific security requirements. This parameter can only
1052 be set at server start.
1056 Explanation of the default value:
1059 <term><literal>HIGH</literal></term>
1062 Cipher suites that use ciphers from <literal>HIGH</> group (e.g.,
1063 AES, Camellia, 3DES)
1069 <term><literal>MEDIUM</literal></term>
1072 Cipher suites that use ciphers from <literal>MEDIUM</> group
1079 <term><literal>+3DES</literal></term>
1082 The OpenSSL default order for <literal>HIGH</> is problematic
1083 because it orders 3DES higher than AES128. This is wrong because
1084 3DES offers less security than AES128, and it is also much
1085 slower. <literal>+3DES</> reorders it after all other
1086 <literal>HIGH</> and <literal>MEDIUM</> ciphers.
1092 <term><literal>!aNULL</literal></term>
1095 Disables anonymous cipher suites that do no authentication. Such
1096 cipher suites are vulnerable to man-in-the-middle attacks and
1097 therefore should not be used.
1105 Available cipher suite details will vary across OpenSSL versions. Use
1107 <literal>openssl ciphers -v 'HIGH:MEDIUM:+3DES:!aNULL'</literal> to
1108 see actual details for the currently installed <application>OpenSSL</>
1109 version. Note that this list is filtered at run time based on the
1115 <varlistentry id="guc-ssl-prefer-server-ciphers" xreflabel="ssl_prefer_server_ciphers">
1116 <term><varname>ssl_prefer_server_ciphers</varname> (<type>bool</type>)
1118 <primary><varname>ssl_prefer_server_ciphers</> configuration parameter</primary>
1123 Specifies whether to use the server's SSL cipher preferences, rather
1124 than the client's. The default is true. This parameter can only be
1125 set at server start.
1129 Older PostgreSQL versions do not have this setting and always use the
1130 client's preferences. This setting is mainly for backward
1131 compatibility with those versions. Using the server's preferences is
1132 usually better because it is more likely that the server is appropriately
1138 <varlistentry id="guc-ssl-ecdh-curve" xreflabel="ssl_ecdh_curve">
1139 <term><varname>ssl_ecdh_curve</varname> (<type>string</type>)
1141 <primary><varname>ssl_ecdh_curve</> configuration parameter</primary>
1146 Specifies the name of the curve to use in <acronym>ECDH</> key
1147 exchange. It needs to be supported by all clients that connect.
1148 It does not need to be same curve as used by server's Elliptic
1149 Curve key. The default is <literal>prime256v1</>. This parameter
1150 can only be set at server start.
1154 OpenSSL names for most common curves:
1155 <literal>prime256v1</> (NIST P-256),
1156 <literal>secp384r1</> (NIST P-384),
1157 <literal>secp521r1</> (NIST P-521).
1161 The full list of available curves can be shown with the command
1162 <command>openssl ecparam -list_curves</command>. Not all of them
1163 are usable in <acronym>TLS</> though.
1168 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
1169 <term><varname>password_encryption</varname> (<type>enum</type>)
1171 <primary><varname>password_encryption</> configuration parameter</primary>
1176 When a password is specified in <xref linkend="sql-createuser"> or
1177 <xref linkend="sql-alterrole"> without writing either <literal>ENCRYPTED</>
1178 or <literal>UNENCRYPTED</>, this parameter determines whether the
1179 password is to be encrypted. The default value is <literal>md5</>, which
1180 stores the password as an MD5 hash. Setting this to <literal>plain</> stores
1181 it in plaintext. <literal>on</> and <literal>off</> are also accepted, as
1182 aliases for <literal>md5</> and <literal>plain</>, respectively.
1188 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
1189 <term><varname>krb_server_keyfile</varname> (<type>string</type>)
1191 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
1196 Sets the location of the Kerberos server key file. See
1197 <xref linkend="gssapi-auth">
1198 for details. This parameter can only be set in the
1199 <filename>postgresql.conf</> file or on the server command line.
1204 <varlistentry id="guc-krb-caseins-users" xreflabel="krb_caseins_users">
1205 <term><varname>krb_caseins_users</varname> (<type>boolean</type>)
1207 <primary><varname>krb_caseins_users</varname> configuration parameter</primary>
1212 Sets whether GSSAPI user names should be treated
1214 The default is <literal>off</> (case sensitive). This parameter can only be
1215 set in the <filename>postgresql.conf</> file or on the server command line.
1220 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
1221 <term><varname>db_user_namespace</varname> (<type>boolean</type>)
1223 <primary><varname>db_user_namespace</> configuration parameter</primary>
1228 This parameter enables per-database user names. It is off by default.
1229 This parameter can only be set in the <filename>postgresql.conf</>
1230 file or on the server command line.
1234 If this is on, you should create users as <replaceable>username@dbname</>.
1235 When <replaceable>username</> is passed by a connecting client,
1236 <literal>@</> and the database name are appended to the user
1237 name and that database-specific user name is looked up by the
1238 server. Note that when you create users with names containing
1239 <literal>@</> within the SQL environment, you will need to
1240 quote the user name.
1244 With this parameter enabled, you can still create ordinary global
1245 users. Simply append <literal>@</> when specifying the user
1246 name in the client, e.g. <literal>joe@</>. The <literal>@</>
1247 will be stripped off before the user name is looked up by the
1252 <varname>db_user_namespace</> causes the client's and
1253 server's user name representation to differ.
1254 Authentication checks are always done with the server's user name
1255 so authentication methods must be configured for the
1256 server's user name, not the client's. Because
1257 <literal>md5</> uses the user name as salt on both the
1258 client and server, <literal>md5</> cannot be used with
1259 <varname>db_user_namespace</>.
1264 This feature is intended as a temporary measure until a
1265 complete solution is found. At that time, this option will
1276 <sect1 id="runtime-config-resource">
1277 <title>Resource Consumption</title>
1279 <sect2 id="runtime-config-resource-memory">
1280 <title>Memory</title>
1283 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
1284 <term><varname>shared_buffers</varname> (<type>integer</type>)
1286 <primary><varname>shared_buffers</> configuration parameter</primary>
1291 Sets the amount of memory the database server uses for shared
1292 memory buffers. The default is typically 128 megabytes
1293 (<literal>128MB</>), but might be less if your kernel settings will
1294 not support it (as determined during <application>initdb</>).
1295 This setting must be at least 128 kilobytes. (Non-default
1296 values of <symbol>BLCKSZ</symbol> change the minimum.) However,
1297 settings significantly higher than the minimum are usually needed
1298 for good performance. This parameter can only be set at server start.
1302 If you have a dedicated database server with 1GB or more of RAM, a
1303 reasonable starting value for <varname>shared_buffers</varname> is 25%
1304 of the memory in your system. There are some workloads where even
1305 larger settings for <varname>shared_buffers</varname> are effective, but
1306 because <productname>PostgreSQL</productname> also relies on the
1307 operating system cache, it is unlikely that an allocation of more than
1308 40% of RAM to <varname>shared_buffers</varname> will work better than a
1309 smaller amount. Larger settings for <varname>shared_buffers</varname>
1310 usually require a corresponding increase in
1311 <varname>max_wal_size</varname>, in order to spread out the
1312 process of writing large quantities of new or changed data over a
1313 longer period of time.
1317 On systems with less than 1GB of RAM, a smaller percentage of RAM is
1318 appropriate, so as to leave adequate space for the operating system.
1324 <varlistentry id="guc-huge-pages" xreflabel="huge_pages">
1325 <term><varname>huge_pages</varname> (<type>enum</type>)
1327 <primary><varname>huge_pages</> configuration parameter</primary>
1332 Enables/disables the use of huge memory pages. Valid values are
1333 <literal>try</literal> (the default), <literal>on</literal>,
1334 and <literal>off</literal>.
1338 At present, this feature is supported only on Linux. The setting is
1339 ignored on other systems when set to <literal>try</literal>.
1343 The use of huge pages results in smaller page tables and less CPU time
1344 spent on memory management, increasing performance. For more details,
1345 see <xref linkend="linux-huge-pages">.
1349 With <varname>huge_pages</varname> set to <literal>try</literal>,
1350 the server will try to use huge pages, but fall back to using
1351 normal allocation if that fails. With <literal>on</literal>, failure
1352 to use huge pages will prevent the server from starting up. With
1353 <literal>off</literal>, huge pages will not be used.
1358 <varlistentry id="guc-temp-buffers" xreflabel="temp_buffers">
1359 <term><varname>temp_buffers</varname> (<type>integer</type>)
1361 <primary><varname>temp_buffers</> configuration parameter</primary>
1366 Sets the maximum number of temporary buffers used by each database
1367 session. These are session-local buffers used only for access to
1368 temporary tables. The default is eight megabytes
1369 (<literal>8MB</>). The setting can be changed within individual
1370 sessions, but only before the first use of temporary tables
1371 within the session; subsequent attempts to change the value will
1372 have no effect on that session.
1376 A session will allocate temporary buffers as needed up to the limit
1377 given by <varname>temp_buffers</>. The cost of setting a large
1378 value in sessions that do not actually need many temporary
1379 buffers is only a buffer descriptor, or about 64 bytes, per
1380 increment in <varname>temp_buffers</>. However if a buffer is
1381 actually used an additional 8192 bytes will be consumed for it
1382 (or in general, <symbol>BLCKSZ</symbol> bytes).
1387 <varlistentry id="guc-max-prepared-transactions" xreflabel="max_prepared_transactions">
1388 <term><varname>max_prepared_transactions</varname> (<type>integer</type>)
1390 <primary><varname>max_prepared_transactions</> configuration parameter</primary>
1395 Sets the maximum number of transactions that can be in the
1396 <quote>prepared</> state simultaneously (see <xref
1397 linkend="sql-prepare-transaction">).
1398 Setting this parameter to zero (which is the default)
1399 disables the prepared-transaction feature.
1400 This parameter can only be set at server start.
1404 If you are not planning to use prepared transactions, this parameter
1405 should be set to zero to prevent accidental creation of prepared
1406 transactions. If you are using prepared transactions, you will
1407 probably want <varname>max_prepared_transactions</varname> to be at
1408 least as large as <xref linkend="guc-max-connections">, so that every
1409 session can have a prepared transaction pending.
1413 When running a standby server, you must set this parameter to the
1414 same or higher value than on the master server. Otherwise, queries
1415 will not be allowed in the standby server.
1420 <varlistentry id="guc-work-mem" xreflabel="work_mem">
1421 <term><varname>work_mem</varname> (<type>integer</type>)
1423 <primary><varname>work_mem</> configuration parameter</primary>
1428 Specifies the amount of memory to be used by internal sort operations
1429 and hash tables before writing to temporary disk files. The value
1430 defaults to four megabytes (<literal>4MB</>).
1431 Note that for a complex query, several sort or hash operations might be
1432 running in parallel; each operation will be allowed to use as much memory
1433 as this value specifies before it starts to write data into temporary
1434 files. Also, several running sessions could be doing such operations
1435 concurrently. Therefore, the total memory used could be many
1436 times the value of <varname>work_mem</varname>; it is necessary to
1437 keep this fact in mind when choosing the value. Sort operations are
1438 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
1440 Hash tables are used in hash joins, hash-based aggregation, and
1441 hash-based processing of <literal>IN</> subqueries.
1446 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
1447 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)
1449 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
1454 Specifies the maximum amount of memory to be used by maintenance
1455 operations, such as <command>VACUUM</command>, <command>CREATE
1456 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults
1457 to 64 megabytes (<literal>64MB</>). Since only one of these
1458 operations can be executed at a time by a database session, and
1459 an installation normally doesn't have many of them running
1460 concurrently, it's safe to set this value significantly larger
1461 than <varname>work_mem</varname>. Larger settings might improve
1462 performance for vacuuming and for restoring database dumps.
1465 Note that when autovacuum runs, up to
1466 <xref linkend="guc-autovacuum-max-workers"> times this memory
1467 may be allocated, so be careful not to set the default value
1468 too high. It may be useful to control for this by separately
1469 setting <xref linkend="guc-autovacuum-work-mem">.
1474 <varlistentry id="guc-replacement-sort-tuples" xreflabel="replacement_sort_tuples">
1475 <term><varname>replacement_sort_tuples</varname> (<type>integer</type>)
1477 <primary><varname>replacement_sort_tuples</> configuration parameter</primary>
1482 When the number of tuples to be sorted is smaller than this number,
1483 a sort will produce its first output run using replacement selection
1484 rather than quicksort. This may be useful in memory-constrained
1485 environments where tuples that are input into larger sort operations
1486 have a strong physical-to-logical correlation. Note that this does
1487 not include input tuples with an <emphasis>inverse</emphasis>
1488 correlation. It is possible for the replacement selection algorithm
1489 to generate one long run that requires no merging, where use of the
1490 default strategy would result in many runs that must be merged
1491 to produce a final sorted output. This may allow sort
1492 operations to complete sooner.
1495 The default is 150,000 tuples. Note that higher values are typically
1496 not much more effective, and may be counter-productive, since the
1497 priority queue is sensitive to the size of available CPU cache, whereas
1498 the default strategy sorts runs using a <firstterm>cache
1499 oblivious</firstterm> algorithm. This property allows the default sort
1500 strategy to automatically and transparently make effective use
1501 of available CPU cache.
1504 Setting <varname>maintenance_work_mem</varname> to its default
1505 value usually prevents utility command external sorts (e.g.,
1506 sorts used by <command>CREATE INDEX</> to build B-Tree
1507 indexes) from ever using replacement selection sort, unless the
1508 input tuples are quite wide.
1513 <varlistentry id="guc-autovacuum-work-mem" xreflabel="autovacuum_work_mem">
1514 <term><varname>autovacuum_work_mem</varname> (<type>integer</type>)
1516 <primary><varname>autovacuum_work_mem</> configuration parameter</primary>
1521 Specifies the maximum amount of memory to be used by each
1522 autovacuum worker process. It defaults to -1, indicating that
1523 the value of <xref linkend="guc-maintenance-work-mem"> should
1524 be used instead. The setting has no effect on the behavior of
1525 <command>VACUUM</command> when run in other contexts.
1530 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
1531 <term><varname>max_stack_depth</varname> (<type>integer</type>)
1533 <primary><varname>max_stack_depth</> configuration parameter</primary>
1538 Specifies the maximum safe depth of the server's execution stack.
1539 The ideal setting for this parameter is the actual stack size limit
1540 enforced by the kernel (as set by <literal>ulimit -s</> or local
1541 equivalent), less a safety margin of a megabyte or so. The safety
1542 margin is needed because the stack depth is not checked in every
1543 routine in the server, but only in key potentially-recursive routines
1544 such as expression evaluation. The default setting is two
1545 megabytes (<literal>2MB</>), which is conservatively small and
1546 unlikely to risk crashes. However, it might be too small to allow
1547 execution of complex functions. Only superusers can change this
1552 Setting <varname>max_stack_depth</> higher than
1553 the actual kernel limit will mean that a runaway recursive function
1554 can crash an individual backend process. On platforms where
1555 <productname>PostgreSQL</productname> can determine the kernel limit,
1556 the server will not allow this variable to be set to an unsafe
1557 value. However, not all platforms provide the information,
1558 so caution is recommended in selecting a value.
1563 <varlistentry id="guc-dynamic-shared-memory-type" xreflabel="dynamic_shared_memory_type">
1564 <term><varname>dynamic_shared_memory_type</varname> (<type>enum</type>)
1566 <primary><varname>dynamic_shared_memory_type</> configuration parameter</primary>
1571 Specifies the dynamic shared memory implementation that the server
1572 should use. Possible values are <literal>posix</> (for POSIX shared
1573 memory allocated using <literal>shm_open</>), <literal>sysv</literal>
1574 (for System V shared memory allocated via <literal>shmget</>),
1575 <literal>windows</> (for Windows shared memory), <literal>mmap</>
1576 (to simulate shared memory using memory-mapped files stored in the
1577 data directory), and <literal>none</> (to disable this feature).
1578 Not all values are supported on all platforms; the first supported
1579 option is the default for that platform. The use of the
1580 <literal>mmap</> option, which is not the default on any platform,
1581 is generally discouraged because the operating system may write
1582 modified pages back to disk repeatedly, increasing system I/O load;
1583 however, it may be useful for debugging, when the
1584 <literal>pg_dynshmem</> directory is stored on a RAM disk, or when
1585 other shared memory facilities are not available.
1593 <sect2 id="runtime-config-resource-disk">
1597 <varlistentry id="guc-temp-file-limit" xreflabel="temp_file_limit">
1598 <term><varname>temp_file_limit</varname> (<type>integer</type>)
1600 <primary><varname>temp_file_limit</> configuration parameter</primary>
1605 Specifies the maximum amount of disk space that a process can use
1606 for temporary files, such as sort and hash temporary files, or the
1607 storage file for a held cursor. A transaction attempting to exceed
1608 this limit will be canceled.
1609 The value is specified in kilobytes, and <literal>-1</> (the
1610 default) means no limit.
1611 Only superusers can change this setting.
1614 This setting constrains the total space used at any instant by all
1615 temporary files used by a given <productname>PostgreSQL</> process.
1616 It should be noted that disk space used for explicit temporary
1617 tables, as opposed to temporary files used behind-the-scenes in query
1618 execution, does <emphasis>not</emphasis> count against this limit.
1626 <sect2 id="runtime-config-resource-kernel">
1627 <title>Kernel Resource Usage</title>
1630 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
1631 <term><varname>max_files_per_process</varname> (<type>integer</type>)
1633 <primary><varname>max_files_per_process</> configuration parameter</primary>
1638 Sets the maximum number of simultaneously open files allowed to each
1639 server subprocess. The default is one thousand files. If the kernel is enforcing
1640 a safe per-process limit, you don't need to worry about this setting.
1641 But on some platforms (notably, most BSD systems), the kernel will
1642 allow individual processes to open many more files than the system
1643 can actually support if many processes all try to open
1644 that many files. If you find yourself seeing <quote>Too many open
1645 files</> failures, try reducing this setting.
1646 This parameter can only be set at server start.
1653 <sect2 id="runtime-config-resource-vacuum-cost">
1654 <title>Cost-based Vacuum Delay</title>
1657 During the execution of <xref linkend="sql-vacuum">
1658 and <xref linkend="sql-analyze">
1659 commands, the system maintains an
1660 internal counter that keeps track of the estimated cost of the
1661 various I/O operations that are performed. When the accumulated
1662 cost reaches a limit (specified by
1663 <varname>vacuum_cost_limit</varname>), the process performing
1664 the operation will sleep for a short period of time, as specified by
1665 <varname>vacuum_cost_delay</varname>. Then it will reset the
1666 counter and continue execution.
1670 The intent of this feature is to allow administrators to reduce
1671 the I/O impact of these commands on concurrent database
1672 activity. There are many situations where it is not
1673 important that maintenance commands like
1674 <command>VACUUM</command> and <command>ANALYZE</command> finish
1675 quickly; however, it is usually very important that these
1676 commands do not significantly interfere with the ability of the
1677 system to perform other database operations. Cost-based vacuum
1678 delay provides a way for administrators to achieve this.
1682 This feature is disabled by default for manually issued
1683 <command>VACUUM</command> commands. To enable it, set the
1684 <varname>vacuum_cost_delay</varname> variable to a nonzero
1689 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1690 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)
1692 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1697 The length of time, in milliseconds, that the process will sleep
1698 when the cost limit has been exceeded.
1699 The default value is zero, which disables the cost-based vacuum
1700 delay feature. Positive values enable cost-based vacuuming.
1701 Note that on many systems, the effective resolution
1702 of sleep delays is 10 milliseconds; setting
1703 <varname>vacuum_cost_delay</varname> to a value that is
1704 not a multiple of 10 might have the same results as setting it
1705 to the next higher multiple of 10.
1709 When using cost-based vacuuming, appropriate values for
1710 <varname>vacuum_cost_delay</> are usually quite small, perhaps
1711 10 or 20 milliseconds. Adjusting vacuum's resource consumption
1712 is best done by changing the other vacuum cost parameters.
1717 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1718 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)
1720 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1725 The estimated cost for vacuuming a buffer found in the shared buffer
1726 cache. It represents the cost to lock the buffer pool, lookup
1727 the shared hash table and scan the content of the page. The
1728 default value is one.
1733 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1734 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)
1736 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1741 The estimated cost for vacuuming a buffer that has to be read from
1742 disk. This represents the effort to lock the buffer pool,
1743 lookup the shared hash table, read the desired block in from
1744 the disk and scan its content. The default value is 10.
1749 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1750 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)
1752 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1757 The estimated cost charged when vacuum modifies a block that was
1758 previously clean. It represents the extra I/O required to
1759 flush the dirty block out to disk again. The default value is
1765 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1766 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)
1768 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1773 The accumulated cost that will cause the vacuuming process to sleep.
1774 The default value is 200.
1782 There are certain operations that hold critical locks and should
1783 therefore complete as quickly as possible. Cost-based vacuum
1784 delays do not occur during such operations. Therefore it is
1785 possible that the cost accumulates far higher than the specified
1786 limit. To avoid uselessly long delays in such cases, the actual
1787 delay is calculated as <varname>vacuum_cost_delay</varname> *
1788 <varname>accumulated_balance</varname> /
1789 <varname>vacuum_cost_limit</varname> with a maximum of
1790 <varname>vacuum_cost_delay</varname> * 4.
1795 <sect2 id="runtime-config-resource-background-writer">
1796 <title>Background Writer</title>
1799 There is a separate server
1800 process called the <firstterm>background writer</>, whose function
1801 is to issue writes of <quote>dirty</> (new or modified) shared
1802 buffers. It writes shared buffers so server processes handling
1803 user queries seldom or never need to wait for a write to occur.
1804 However, the background writer does cause a net overall
1805 increase in I/O load, because while a repeatedly-dirtied page might
1806 otherwise be written only once per checkpoint interval, the
1807 background writer might write it several times as it is dirtied
1808 in the same interval. The parameters discussed in this subsection
1809 can be used to tune the behavior for local needs.
1813 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1814 <term><varname>bgwriter_delay</varname> (<type>integer</type>)
1816 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1821 Specifies the delay between activity rounds for the
1822 background writer. In each round the writer issues writes
1823 for some number of dirty buffers (controllable by the
1824 following parameters). It then sleeps for <varname>bgwriter_delay</>
1825 milliseconds, and repeats. When there are no dirty buffers in the
1826 buffer pool, though, it goes into a longer sleep regardless of
1827 <varname>bgwriter_delay</>. The default value is 200
1828 milliseconds (<literal>200ms</>). Note that on many systems, the
1829 effective resolution of sleep delays is 10 milliseconds; setting
1830 <varname>bgwriter_delay</> to a value that is not a multiple of 10
1831 might have the same results as setting it to the next higher multiple
1832 of 10. This parameter can only be set in the
1833 <filename>postgresql.conf</> file or on the server command line.
1838 <varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1839 <term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)
1841 <primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1846 In each round, no more than this many buffers will be written
1847 by the background writer. Setting this to zero disables
1848 background writing. (Note that checkpoints, which are managed by
1849 a separate, dedicated auxiliary process, are unaffected.)
1850 The default value is 100 buffers.
1851 This parameter can only be set in the <filename>postgresql.conf</>
1852 file or on the server command line.
1857 <varlistentry id="guc-bgwriter-lru-multiplier" xreflabel="bgwriter_lru_multiplier">
1858 <term><varname>bgwriter_lru_multiplier</varname> (<type>floating point</type>)
1860 <primary><varname>bgwriter_lru_multiplier</> configuration parameter</primary>
1865 The number of dirty buffers written in each round is based on the
1866 number of new buffers that have been needed by server processes
1867 during recent rounds. The average recent need is multiplied by
1868 <varname>bgwriter_lru_multiplier</> to arrive at an estimate of the
1869 number of buffers that will be needed during the next round. Dirty
1870 buffers are written until there are that many clean, reusable buffers
1871 available. (However, no more than <varname>bgwriter_lru_maxpages</>
1872 buffers will be written per round.)
1873 Thus, a setting of 1.0 represents a <quote>just in time</> policy
1874 of writing exactly the number of buffers predicted to be needed.
1875 Larger values provide some cushion against spikes in demand,
1876 while smaller values intentionally leave writes to be done by
1879 This parameter can only be set in the <filename>postgresql.conf</>
1880 file or on the server command line.
1885 <varlistentry id="guc-bgwriter-flush-after" xreflabel="bgwriter_flush_after">
1886 <term><varname>bgwriter_flush_after</varname> (<type>integer</type>)
1888 <primary><varname>bgwriter_flush_after</> configuration parameter</primary>
1893 Whenever more than <varname>bgwriter_flush_after</varname> bytes have
1894 been written by the bgwriter, attempt to force the OS to issue these
1895 writes to the underlying storage. Doing so will limit the amount of
1896 dirty data in the kernel's page cache, reducing the likelihood of
1897 stalls when an fsync is issued at the end of a checkpoint, or when
1898 the OS writes data back in larger batches in the background. Often
1899 that will result in greatly reduced transaction latency, but there
1900 also are some cases, especially with workloads that are bigger than
1901 <xref linkend="guc-shared-buffers">, but smaller than the OS's page
1902 cache, where performance might degrade. This setting may have no
1903 effect on some platforms. The valid range is between
1904 <literal>0</literal>, which disables forced writeback, and
1905 <literal>2MB</literal>. The default is <literal>512kB</> on Linux,
1906 <literal>0</> elsewhere. (If <symbol>BLCKSZ</symbol> is not 8kB,
1907 the default and maximum values scale proportionally to it.)
1908 This parameter can only be set in the <filename>postgresql.conf</>
1909 file or on the server command line.
1916 Smaller values of <varname>bgwriter_lru_maxpages</varname> and
1917 <varname>bgwriter_lru_multiplier</varname> reduce the extra I/O load
1918 caused by the background writer, but make it more likely that server
1919 processes will have to issue writes for themselves, delaying interactive
1924 <sect2 id="runtime-config-resource-async-behavior">
1925 <title>Asynchronous Behavior</title>
1928 <varlistentry id="guc-effective-io-concurrency" xreflabel="effective_io_concurrency">
1929 <term><varname>effective_io_concurrency</varname> (<type>integer</type>)
1931 <primary><varname>effective_io_concurrency</> configuration parameter</primary>
1936 Sets the number of concurrent disk I/O operations that
1937 <productname>PostgreSQL</> expects can be executed
1938 simultaneously. Raising this value will increase the number of I/O
1939 operations that any individual <productname>PostgreSQL</> session
1940 attempts to initiate in parallel. The allowed range is 1 to 1000,
1941 or zero to disable issuance of asynchronous I/O requests. Currently,
1942 this setting only affects bitmap heap scans.
1946 For magnetic drives, a good starting point for this setting is the
1948 drives comprising a RAID 0 stripe or RAID 1 mirror being used for the
1949 database. (For RAID 5 the parity drive should not be counted.)
1950 However, if the database is often busy with multiple queries issued in
1951 concurrent sessions, lower values may be sufficient to keep the disk
1952 array busy. A value higher than needed to keep the disks busy will
1953 only result in extra CPU overhead.
1954 SSDs and other memory-based storage can often process many
1955 concurrent requests, so the best value might be in the hundreds.
1959 Asynchronous I/O depends on an effective <function>posix_fadvise</>
1960 function, which some operating systems lack. If the function is not
1961 present then setting this parameter to anything but zero will result
1962 in an error. On some operating systems (e.g., Solaris), the function
1963 is present but does not actually do anything.
1967 The default is 1 on supported systems, otherwise 0. This value can
1968 be overridden for tables in a particular tablespace by setting the
1969 tablespace parameter of the same name (see
1970 <xref linkend="sql-altertablespace">).
1975 <varlistentry id="guc-max-worker-processes" xreflabel="max_worker_processes">
1976 <term><varname>max_worker_processes</varname> (<type>integer</type>)
1978 <primary><varname>max_worker_processes</> configuration parameter</primary>
1983 Sets the maximum number of background processes that the system
1984 can support. This parameter can only be set at server start. The
1989 When running a standby server, you must set this parameter to the
1990 same or higher value than on the master server. Otherwise, queries
1991 will not be allowed in the standby server.
1996 <varlistentry id="guc-max-parallel-workers-per-gather" xreflabel="max_parallel_workers_per_gather">
1997 <term><varname>max_parallel_workers_per_gather</varname> (<type>integer</type>)
1999 <primary><varname>max_parallel_workers_per_gather</> configuration parameter</primary>
2004 Sets the maximum number of workers that can be started by a single
2005 <literal>Gather</literal> node. Parallel workers are taken from the
2006 pool of processes established by
2007 <xref linkend="guc-max-worker-processes">, limited by
2008 <xref linkend="guc-max-parallel-workers">. Note that the requested
2009 number of workers may not actually be available at runtime. If this
2010 occurs, the plan will run with fewer workers than expected, which may
2011 be inefficient. The default value is 2. Setting this value to 0
2012 disables parallel query execution.
2016 Note that parallel queries may consume very substantially more
2017 resources than non-parallel queries, because each worker process is
2018 a completely separate process which has roughly the same impact on the
2019 system as an additional user session. This should be taken into
2020 account when choosing a value for this setting, as well as when
2021 configuring other settings that control resource utilization, such
2022 as <xref linkend="guc-work-mem">. Resource limits such as
2023 <varname>work_mem</> are applied individually to each worker,
2024 which means the total utilization may be much higher across all
2025 processes than it would normally be for any single process.
2026 For example, a parallel query using 4 workers may use up to 5 times
2027 as much CPU time, memory, I/O bandwidth, and so forth as a query which
2028 uses no workers at all.
2032 For more information on parallel query, see
2033 <xref linkend="parallel-query">.
2038 <varlistentry id="guc-max-parallel-workers" xreflabel="max_parallel_workers">
2039 <term><varname>max_parallel_workers</varname> (<type>integer</type>)
2041 <primary><varname>max_parallel_workers</> configuration parameter</primary>
2046 Sets the maximum number of workers that the system can support for
2047 parallel queries. The default value is 8. When increasing or
2048 decreasing this value, consider also adjusting
2049 <xref linkend="guc-max-parallel-workers-per-gather">.
2054 <varlistentry id="guc-backend-flush-after" xreflabel="backend_flush_after">
2055 <term><varname>backend_flush_after</varname> (<type>integer</type>)
2057 <primary><varname>backend_flush_after</> configuration parameter</primary>
2062 Whenever more than <varname>backend_flush_after</varname> bytes have
2063 been written by a single backend, attempt to force the OS to issue
2064 these writes to the underlying storage. Doing so will limit the
2065 amount of dirty data in the kernel's page cache, reducing the
2066 likelihood of stalls when an fsync is issued at the end of a
2067 checkpoint, or when the OS writes data back in larger batches in the
2068 background. Often that will result in greatly reduced transaction
2069 latency, but there also are some cases, especially with workloads
2070 that are bigger than <xref linkend="guc-shared-buffers">, but smaller
2071 than the OS's page cache, where performance might degrade. This
2072 setting may have no effect on some platforms. The valid range is
2073 between <literal>0</literal>, which disables forced writeback,
2074 and <literal>2MB</literal>. The default is <literal>0</>, i.e., no
2075 forced writeback. (If <symbol>BLCKSZ</symbol> is not 8kB,
2076 the maximum value scales proportionally to it.)
2081 <varlistentry id="guc-old-snapshot-threshold" xreflabel="old_snapshot_threshold">
2082 <term><varname>old_snapshot_threshold</varname> (<type>integer</type>)
2084 <primary><varname>old_snapshot_threshold</> configuration parameter</primary>
2089 Sets the minimum time that a snapshot can be used without risk of a
2090 <literal>snapshot too old</> error occurring when using the snapshot.
2091 This parameter can only be set at server start.
2095 Beyond the threshold, old data may be vacuumed away. This can help
2096 prevent bloat in the face of snapshots which remain in use for a
2097 long time. To prevent incorrect results due to cleanup of data which
2098 would otherwise be visible to the snapshot, an error is generated
2099 when the snapshot is older than this threshold and the snapshot is
2100 used to read a page which has been modified since the snapshot was
2105 A value of <literal>-1</> disables this feature, and is the default.
2106 Useful values for production work probably range from a small number
2107 of hours to a few days. The setting will be coerced to a granularity
2108 of minutes, and small numbers (such as <literal>0</> or
2109 <literal>1min</>) are only allowed because they may sometimes be
2110 useful for testing. While a setting as high as <literal>60d</> is
2111 allowed, please note that in many workloads extreme bloat or
2112 transaction ID wraparound may occur in much shorter time frames.
2116 When this feature is enabled, freed space at the end of a relation
2117 cannot be released to the operating system, since that could remove
2118 information needed to detect the <literal>snapshot too old</>
2119 condition. All space allocated to a relation remains associated with
2120 that relation for reuse only within that relation unless explicitly
2121 freed (for example, with <command>VACUUM FULL</>).
2125 This setting does not attempt to guarantee that an error will be
2126 generated under any particular circumstances. In fact, if the
2127 correct results can be generated from (for example) a cursor which
2128 has materialized a result set, no error will be generated even if the
2129 underlying rows in the referenced table have been vacuumed away.
2130 Some tables cannot safely be vacuumed early, and so will not be
2131 affected by this setting. Examples include system catalogs and any
2132 table which has a hash index. For such tables this setting will
2133 neither reduce bloat nor create a possibility of a <literal>snapshot
2134 too old</> error on scanning.
2142 <sect1 id="runtime-config-wal">
2143 <title>Write Ahead Log</title>
2146 For additional information on tuning these settings,
2147 see <xref linkend="wal-configuration">.
2150 <sect2 id="runtime-config-wal-settings">
2151 <title>Settings</title>
2154 <varlistentry id="guc-wal-level" xreflabel="wal_level">
2155 <term><varname>wal_level</varname> (<type>enum</type>)
2157 <primary><varname>wal_level</> configuration parameter</primary>
2162 <varname>wal_level</> determines how much information is written
2163 to the WAL. The default value is <literal>minimal</>, which writes
2164 only the information needed to recover from a crash or immediate
2165 shutdown. <literal>replica</> adds logging required for WAL
2166 archiving as well as information required to run
2167 read-only queries on a standby server. Finally,
2168 <literal>logical</> adds information necessary to support logical
2169 decoding. Each level includes the information logged at all lower
2170 levels. This parameter can only be set at server start.
2173 In <literal>minimal</> level, WAL-logging of some bulk
2174 operations can be safely skipped, which can make those
2175 operations much faster (see <xref linkend="populate-pitr">).
2176 Operations in which this optimization can be applied include:
2178 <member><command>CREATE TABLE AS</></member>
2179 <member><command>CREATE INDEX</></member>
2180 <member><command>CLUSTER</></member>
2181 <member><command>COPY</> into tables that were created or truncated in the same
2182 transaction</member>
2184 But minimal WAL does not contain enough information to reconstruct the
2185 data from a base backup and the WAL logs, so <literal>replica</> or
2186 higher must be used to enable WAL archiving
2187 (<xref linkend="guc-archive-mode">) and streaming replication.
2190 In <literal>logical</> level, the same information is logged as
2191 with <literal>replica</>, plus information needed to allow
2192 extracting logical change sets from the WAL. Using a level of
2193 <literal>logical</> will increase the WAL volume, particularly if many
2194 tables are configured for <literal>REPLICA IDENTITY FULL</literal> and
2195 many <command>UPDATE</> and <command>DELETE</> statements are
2199 In releases prior to 9.6, this parameter also allowed the
2200 values <literal>archive</literal> and <literal>hot_standby</literal>.
2201 These are still accepted but mapped to <literal>replica</literal>.
2206 <varlistentry id="guc-fsync" xreflabel="fsync">
2207 <term><varname>fsync</varname> (<type>boolean</type>)
2209 <primary><varname>fsync</> configuration parameter</primary>
2214 If this parameter is on, the <productname>PostgreSQL</> server
2215 will try to make sure that updates are physically written to
2216 disk, by issuing <function>fsync()</> system calls or various
2217 equivalent methods (see <xref linkend="guc-wal-sync-method">).
2218 This ensures that the database cluster can recover to a
2219 consistent state after an operating system or hardware crash.
2223 While turning off <varname>fsync</varname> is often a performance
2224 benefit, this can result in unrecoverable data corruption in
2225 the event of a power failure or system crash. Thus it
2226 is only advisable to turn off <varname>fsync</varname> if
2227 you can easily recreate your entire database from external
2232 Examples of safe circumstances for turning off
2233 <varname>fsync</varname> include the initial loading of a new
2234 database cluster from a backup file, using a database cluster
2235 for processing a batch of data after which the database
2236 will be thrown away and recreated,
2237 or for a read-only database clone which
2238 gets recreated frequently and is not used for failover. High
2239 quality hardware alone is not a sufficient justification for
2240 turning off <varname>fsync</varname>.
2244 For reliable recovery when changing <varname>fsync</varname>
2245 off to on, it is necessary to force all modified buffers in the
2246 kernel to durable storage. This can be done while the cluster
2247 is shutdown or while fsync is on by running <command>initdb
2248 --sync-only</command>, running <command>sync</>, unmounting the
2249 file system, or rebooting the server.
2253 In many situations, turning off <xref linkend="guc-synchronous-commit">
2254 for noncritical transactions can provide much of the potential
2255 performance benefit of turning off <varname>fsync</varname>, without
2256 the attendant risks of data corruption.
2260 <varname>fsync</varname> can only be set in the <filename>postgresql.conf</>
2261 file or on the server command line.
2262 If you turn this parameter off, also consider turning off
2263 <xref linkend="guc-full-page-writes">.
2268 <varlistentry id="guc-synchronous-commit" xreflabel="synchronous_commit">
2269 <term><varname>synchronous_commit</varname> (<type>enum</type>)
2271 <primary><varname>synchronous_commit</> configuration parameter</primary>
2276 Specifies whether transaction commit will wait for WAL records
2277 to be written to disk before the command returns a <quote>success</>
2278 indication to the client. Valid values are <literal>on</>,
2279 <literal>remote_apply</>, <literal>remote_write</>, <literal>local</>,
2280 and <literal>off</>. The default, and safe, setting
2281 is <literal>on</>. When <literal>off</>, there can be a delay between
2282 when success is reported to the client and when the transaction is
2283 really guaranteed to be safe against a server crash. (The maximum
2284 delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
2285 <xref linkend="guc-fsync">, setting this parameter to <literal>off</>
2286 does not create any risk of database inconsistency: an operating
2287 system or database crash might
2288 result in some recent allegedly-committed transactions being lost, but
2289 the database state will be just the same as if those transactions had
2290 been aborted cleanly. So, turning <varname>synchronous_commit</> off
2291 can be a useful alternative when performance is more important than
2292 exact certainty about the durability of a transaction. For more
2293 discussion see <xref linkend="wal-async-commit">.
2296 If <xref linkend="guc-synchronous-standby-names"> is non-empty, this
2297 parameter also controls whether or not transaction commits will wait
2298 for their WAL records to be replicated to the standby server(s).
2299 When set to <literal>on</>, commits will wait until replies
2300 from the current synchronous standby(s) indicate they have received
2301 the commit record of the transaction and flushed it to disk. This
2302 ensures the transaction will not be lost unless both the primary and
2303 all synchronous standbys suffer corruption of their database storage.
2304 When set to <literal>remote_apply</>, commits will wait until replies
2305 from the current synchronous standby(s) indicate they have received the
2306 commit record of the transaction and applied it, so that it has become
2307 visible to queries on the standby(s).
2308 When set to <literal>remote_write</>, commits will wait until replies
2309 from the current synchronous standby(s) indicate they have
2310 received the commit record of the transaction and written it out to
2311 their operating system. This setting is sufficient to
2312 ensure data preservation even if a standby instance of
2313 <productname>PostgreSQL</> were to crash, but not if the standby
2314 suffers an operating-system-level crash, since the data has not
2315 necessarily reached stable storage on the standby.
2316 Finally, the setting <literal>local</> causes commits to wait for
2317 local flush to disk, but not for replication. This is not usually
2318 desirable when synchronous replication is in use, but is provided for
2322 If <varname>synchronous_standby_names</> is empty, the settings
2323 <literal>on</>, <literal>remote_apply</>, <literal>remote_write</>
2324 and <literal>local</> all provide the same synchronization level:
2325 transaction commits only wait for local flush to disk.
2328 This parameter can be changed at any time; the behavior for any
2329 one transaction is determined by the setting in effect when it
2330 commits. It is therefore possible, and useful, to have some
2331 transactions commit synchronously and others asynchronously.
2332 For example, to make a single multistatement transaction commit
2333 asynchronously when the default is the opposite, issue <command>SET
2334 LOCAL synchronous_commit TO OFF</> within the transaction.
2339 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
2340 <term><varname>wal_sync_method</varname> (<type>enum</type>)
2342 <primary><varname>wal_sync_method</> configuration parameter</primary>
2347 Method used for forcing WAL updates out to disk.
2348 If <varname>fsync</varname> is off then this setting is irrelevant,
2349 since WAL file updates will not be forced out at all.
2350 Possible values are:
2355 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>)
2360 <literal>fdatasync</> (call <function>fdatasync()</> at each commit)
2365 <literal>fsync</> (call <function>fsync()</> at each commit)
2370 <literal>fsync_writethrough</> (call <function>fsync()</> at each commit, forcing write-through of any disk write cache)
2375 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>)
2380 The <literal>open_</>* options also use <literal>O_DIRECT</> if available.
2381 Not all of these choices are available on all platforms.
2382 The default is the first method in the above list that is supported
2383 by the platform, except that <literal>fdatasync</> is the default on
2384 Linux. The default is not necessarily ideal; it might be
2385 necessary to change this setting or other aspects of your system
2386 configuration in order to create a crash-safe configuration or
2387 achieve optimal performance.
2388 These aspects are discussed in <xref linkend="wal-reliability">.
2389 This parameter can only be set in the <filename>postgresql.conf</>
2390 file or on the server command line.
2395 <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
2396 <term><varname>full_page_writes</varname> (<type>boolean</type>)
2398 <primary><varname>full_page_writes</> configuration parameter</primary>
2403 When this parameter is on, the <productname>PostgreSQL</> server
2404 writes the entire content of each disk page to WAL during the
2405 first modification of that page after a checkpoint.
2406 This is needed because
2407 a page write that is in process during an operating system crash might
2408 be only partially completed, leading to an on-disk page
2409 that contains a mix of old and new data. The row-level change data
2410 normally stored in WAL will not be enough to completely restore
2411 such a page during post-crash recovery. Storing the full page image
2412 guarantees that the page can be correctly restored, but at the price
2413 of increasing the amount of data that must be written to WAL.
2414 (Because WAL replay always starts from a checkpoint, it is sufficient
2415 to do this during the first change of each page after a checkpoint.
2416 Therefore, one way to reduce the cost of full-page writes is to
2417 increase the checkpoint interval parameters.)
2421 Turning this parameter off speeds normal operation, but
2422 might lead to either unrecoverable data corruption, or silent
2423 data corruption, after a system failure. The risks are similar to turning off
2424 <varname>fsync</varname>, though smaller, and it should be turned off
2425 only based on the same circumstances recommended for that parameter.
2429 Turning off this parameter does not affect use of
2430 WAL archiving for point-in-time recovery (PITR)
2431 (see <xref linkend="continuous-archiving">).
2435 This parameter can only be set in the <filename>postgresql.conf</>
2436 file or on the server command line.
2437 The default is <literal>on</>.
2442 <varlistentry id="guc-wal-log-hints" xreflabel="wal_log_hints">
2443 <term><varname>wal_log_hints</varname> (<type>boolean</type>)
2445 <primary><varname>wal_log_hints</> configuration parameter</primary>
2450 When this parameter is <literal>on</>, the <productname>PostgreSQL</>
2451 server writes the entire content of each disk page to WAL during the
2452 first modification of that page after a checkpoint, even for
2453 non-critical modifications of so-called hint bits.
2457 If data checksums are enabled, hint bit updates are always WAL-logged
2458 and this setting is ignored. You can use this setting to test how much
2459 extra WAL-logging would occur if your database had data checksums
2464 This parameter can only be set at server start. The default value is <literal>off</>.
2469 <varlistentry id="guc-wal-compression" xreflabel="wal_compression">
2470 <term><varname>wal_compression</varname> (<type>boolean</type>)
2472 <primary><varname>wal_compression</> configuration parameter</primary>
2477 When this parameter is <literal>on</>, the <productname>PostgreSQL</>
2478 server compresses a full page image written to WAL when
2479 <xref linkend="guc-full-page-writes"> is on or during a base backup.
2480 A compressed page image will be decompressed during WAL replay.
2481 The default value is <literal>off</>.
2482 Only superusers can change this setting.
2486 Turning this parameter on can reduce the WAL volume without
2487 increasing the risk of unrecoverable data corruption,
2488 but at the cost of some extra CPU spent on the compression during
2489 WAL logging and on the decompression during WAL replay.
2494 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
2495 <term><varname>wal_buffers</varname> (<type>integer</type>)
2497 <primary><varname>wal_buffers</> configuration parameter</primary>
2502 The amount of shared memory used for WAL data that has not yet been
2503 written to disk. The default setting of -1 selects a size equal to
2504 1/32nd (about 3%) of <xref linkend="guc-shared-buffers">, but not less
2505 than <literal>64kB</literal> nor more than the size of one WAL
2506 segment, typically <literal>16MB</literal>. This value can be set
2507 manually if the automatic choice is too large or too small,
2508 but any positive value less than <literal>32kB</literal> will be
2509 treated as <literal>32kB</literal>.
2510 This parameter can only be set at server start.
2514 The contents of the WAL buffers are written out to disk at every
2515 transaction commit, so extremely large values are unlikely to
2516 provide a significant benefit. However, setting this value to at
2517 least a few megabytes can improve write performance on a busy
2518 server where many clients are committing at once. The auto-tuning
2519 selected by the default setting of -1 should give reasonable
2520 results in most cases.
2526 <varlistentry id="guc-wal-writer-delay" xreflabel="wal_writer_delay">
2527 <term><varname>wal_writer_delay</varname> (<type>integer</type>)
2529 <primary><varname>wal_writer_delay</> configuration parameter</primary>
2534 Specifies how often the WAL writer flushes WAL. After flushing WAL it
2535 sleeps for <varname>wal_writer_delay</> milliseconds, unless woken up
2536 by an asynchronously committing transaction. If the last flush
2537 happened less than <varname>wal_writer_delay</> milliseconds ago and
2538 less than <varname>wal_writer_flush_after</> bytes of WAL have been
2539 produced since, then WAL is only written to the operating system, not
2541 The default value is 200 milliseconds (<literal>200ms</>). Note that
2542 on many systems, the effective resolution of sleep delays is 10
2543 milliseconds; setting <varname>wal_writer_delay</> to a value that is
2544 not a multiple of 10 might have the same results as setting it to the
2545 next higher multiple of 10. This parameter can only be set in the
2546 <filename>postgresql.conf</> file or on the server command line.
2551 <varlistentry id="guc-wal-writer-flush-after" xreflabel="wal_writer_flush_after">
2552 <term><varname>wal_writer_flush_after</varname> (<type>integer</type>)
2554 <primary><varname>wal_writer_flush_after</> configuration parameter</primary>
2559 Specifies how often the WAL writer flushes WAL. If the last flush
2560 happened less than <varname>wal_writer_delay</> milliseconds ago and
2561 less than <varname>wal_writer_flush_after</> bytes of WAL have been
2562 produced since, then WAL is only written to the operating system, not
2563 flushed to disk. If <varname>wal_writer_flush_after</> is set
2564 to <literal>0</> then WAL data is flushed immediately. The default is
2565 <literal>1MB</literal>. This parameter can only be set in the
2566 <filename>postgresql.conf</> file or on the server command line.
2571 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
2572 <term><varname>commit_delay</varname> (<type>integer</type>)
2574 <primary><varname>commit_delay</> configuration parameter</primary>
2579 <varname>commit_delay</varname> adds a time delay, measured in
2580 microseconds, before a WAL flush is initiated. This can improve
2581 group commit throughput by allowing a larger number of transactions
2582 to commit via a single WAL flush, if system load is high enough
2583 that additional transactions become ready to commit within the
2584 given interval. However, it also increases latency by up to
2585 <varname>commit_delay</varname> microseconds for each WAL
2586 flush. Because the delay is just wasted if no other transactions
2587 become ready to commit, a delay is only performed if at least
2588 <varname>commit_siblings</varname> other transactions are active
2589 when a flush is about to be initiated. Also, no delays are
2590 performed if <varname>fsync</varname> is disabled.
2591 The default <varname>commit_delay</> is zero (no delay).
2592 Only superusers can change this setting.
2595 In <productname>PostgreSQL</> releases prior to 9.3,
2596 <varname>commit_delay</varname> behaved differently and was much
2597 less effective: it affected only commits, rather than all WAL flushes,
2598 and waited for the entire configured delay even if the WAL flush
2599 was completed sooner. Beginning in <productname>PostgreSQL</> 9.3,
2600 the first process that becomes ready to flush waits for the configured
2601 interval, while subsequent processes wait only until the leader
2602 completes the flush operation.
2607 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
2608 <term><varname>commit_siblings</varname> (<type>integer</type>)
2610 <primary><varname>commit_siblings</> configuration parameter</primary>
2615 Minimum number of concurrent open transactions to require
2616 before performing the <varname>commit_delay</> delay. A larger
2617 value makes it more probable that at least one other
2618 transaction will become ready to commit during the delay
2619 interval. The default is five transactions.
2626 <sect2 id="runtime-config-wal-checkpoints">
2627 <title>Checkpoints</title>
2630 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
2631 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)
2633 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
2638 Maximum time between automatic WAL checkpoints, in seconds.
2639 The valid range is between 30 seconds and one day.
2640 The default is five minutes (<literal>5min</>).
2641 Increasing this parameter can increase the amount of time needed
2643 This parameter can only be set in the <filename>postgresql.conf</>
2644 file or on the server command line.
2649 <varlistentry id="guc-checkpoint-completion-target" xreflabel="checkpoint_completion_target">
2650 <term><varname>checkpoint_completion_target</varname> (<type>floating point</type>)
2652 <primary><varname>checkpoint_completion_target</> configuration parameter</primary>
2657 Specifies the target of checkpoint completion, as a fraction of
2658 total time between checkpoints. The default is 0.5.
2659 This parameter can only be set in the <filename>postgresql.conf</>
2660 file or on the server command line.
2665 <varlistentry id="guc-checkpoint-flush-after" xreflabel="checkpoint_flush_after">
2666 <term><varname>checkpoint_flush_after</varname> (<type>integer</type>)
2668 <primary><varname>checkpoint_flush_after</> configuration parameter</primary>
2673 Whenever more than <varname>checkpoint_flush_after</varname> bytes
2674 have been written while performing a checkpoint, attempt to force the
2675 OS to issue these writes to the underlying storage. Doing so will
2676 limit the amount of dirty data in the kernel's page cache, reducing
2677 the likelihood of stalls when an fsync is issued at the end of the
2678 checkpoint, or when the OS writes data back in larger batches in the
2679 background. Often that will result in greatly reduced transaction
2680 latency, but there also are some cases, especially with workloads
2681 that are bigger than <xref linkend="guc-shared-buffers">, but smaller
2682 than the OS's page cache, where performance might degrade. This
2683 setting may have no effect on some platforms. The valid range is
2684 between <literal>0</literal>, which disables forced writeback,
2685 and <literal>2MB</literal>. The default is <literal>256kB</> on
2686 Linux, <literal>0</> elsewhere. (If <symbol>BLCKSZ</symbol> is not
2687 8kB, the default and maximum values scale proportionally to it.)
2688 This parameter can only be set in the <filename>postgresql.conf</>
2689 file or on the server command line.
2694 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
2695 <term><varname>checkpoint_warning</varname> (<type>integer</type>)
2697 <primary><varname>checkpoint_warning</> configuration parameter</primary>
2702 Write a message to the server log if checkpoints caused by
2703 the filling of checkpoint segment files happen closer together
2704 than this many seconds (which suggests that
2705 <varname>max_wal_size</> ought to be raised). The default is
2706 30 seconds (<literal>30s</>). Zero disables the warning.
2707 No warnings will be generated if <varname>checkpoint_timeout</varname>
2708 is less than <varname>checkpoint_warning</varname>.
2709 This parameter can only be set in the <filename>postgresql.conf</>
2710 file or on the server command line.
2715 <varlistentry id="guc-max-wal-size" xreflabel="max_wal_size">
2716 <term><varname>max_wal_size</varname> (<type>integer</type>)
2718 <primary><varname>max_wal_size</> configuration parameter</primary>
2723 Maximum size to let the WAL grow to between automatic WAL
2724 checkpoints. This is a soft limit; WAL size can exceed
2725 <varname>max_wal_size</> under special circumstances, like
2726 under heavy load, a failing <varname>archive_command</>, or a high
2727 <varname>wal_keep_segments</> setting. The default is 1 GB.
2728 Increasing this parameter can increase the amount of time needed for
2730 This parameter can only be set in the <filename>postgresql.conf</>
2731 file or on the server command line.
2736 <varlistentry id="guc-min-wal-size" xreflabel="min_wal_size">
2737 <term><varname>min_wal_size</varname> (<type>integer</type>)
2739 <primary><varname>min_wal_size</> configuration parameter</primary>
2744 As long as WAL disk usage stays below this setting, old WAL files are
2745 always recycled for future use at a checkpoint, rather than removed.
2746 This can be used to ensure that enough WAL space is reserved to
2747 handle spikes in WAL usage, for example when running large batch
2748 jobs. The default is 80 MB.
2749 This parameter can only be set in the <filename>postgresql.conf</>
2750 file or on the server command line.
2757 <sect2 id="runtime-config-wal-archiving">
2758 <title>Archiving</title>
2761 <varlistentry id="guc-archive-mode" xreflabel="archive_mode">
2762 <term><varname>archive_mode</varname> (<type>enum</type>)
2764 <primary><varname>archive_mode</> configuration parameter</primary>
2769 When <varname>archive_mode</> is enabled, completed WAL segments
2770 are sent to archive storage by setting
2771 <xref linkend="guc-archive-command">. In addition to <literal>off</>,
2772 to disable, there are two modes: <literal>on</>, and
2773 <literal>always</>. During normal operation, there is no
2774 difference between the two modes, but when set to <literal>always</>
2775 the WAL archiver is enabled also during archive recovery or standby
2776 mode. In <literal>always</> mode, all files restored from the archive
2777 or streamed with streaming replication will be archived (again). See
2778 <xref linkend="continuous-archiving-in-standby"> for details.
2781 <varname>archive_mode</> and <varname>archive_command</> are
2782 separate variables so that <varname>archive_command</> can be
2783 changed without leaving archiving mode.
2784 This parameter can only be set at server start.
2785 <varname>archive_mode</> cannot be enabled when
2786 <varname>wal_level</> is set to <literal>minimal</>.
2791 <varlistentry id="guc-archive-command" xreflabel="archive_command">
2792 <term><varname>archive_command</varname> (<type>string</type>)
2794 <primary><varname>archive_command</> configuration parameter</primary>
2799 The local shell command to execute to archive a completed WAL file
2800 segment. Any <literal>%p</> in the string is
2801 replaced by the path name of the file to archive, and any
2802 <literal>%f</> is replaced by only the file name.
2803 (The path name is relative to the working directory of the server,
2804 i.e., the cluster's data directory.)
2805 Use <literal>%%</> to embed an actual <literal>%</> character in the
2806 command. It is important for the command to return a zero
2807 exit status only if it succeeds. For more information see
2808 <xref linkend="backup-archiving-wal">.
2811 This parameter can only be set in the <filename>postgresql.conf</>
2812 file or on the server command line. It is ignored unless
2813 <varname>archive_mode</> was enabled at server start.
2814 If <varname>archive_command</> is an empty string (the default) while
2815 <varname>archive_mode</> is enabled, WAL archiving is temporarily
2816 disabled, but the server continues to accumulate WAL segment files in
2817 the expectation that a command will soon be provided. Setting
2818 <varname>archive_command</> to a command that does nothing but
2819 return true, e.g. <literal>/bin/true</> (<literal>REM</> on
2820 Windows), effectively disables
2821 archiving, but also breaks the chain of WAL files needed for
2822 archive recovery, so it should only be used in unusual circumstances.
2827 <varlistentry id="guc-archive-timeout" xreflabel="archive_timeout">
2828 <term><varname>archive_timeout</varname> (<type>integer</type>)
2830 <primary><varname>archive_timeout</> configuration parameter</primary>
2835 The <xref linkend="guc-archive-command"> is only invoked for
2836 completed WAL segments. Hence, if your server generates little WAL
2837 traffic (or has slack periods where it does so), there could be a
2838 long delay between the completion of a transaction and its safe
2839 recording in archive storage. To limit how old unarchived
2840 data can be, you can set <varname>archive_timeout</> to force the
2841 server to switch to a new WAL segment file periodically. When this
2842 parameter is greater than zero, the server will switch to a new
2843 segment file whenever this many seconds have elapsed since the last
2844 segment file switch, and there has been any database activity,
2845 including a single checkpoint. (Increasing
2846 <varname>checkpoint_timeout</> will reduce unnecessary
2847 checkpoints on an idle system.)
2848 Note that archived files that are closed early
2849 due to a forced switch are still the same length as completely full
2850 files. Therefore, it is unwise to use a very short
2851 <varname>archive_timeout</> — it will bloat your archive
2852 storage. <varname>archive_timeout</> settings of a minute or so are
2853 usually reasonable. You should consider using streaming replication,
2854 instead of archiving, if you want data to be copied off the master
2855 server more quickly than that.
2856 This parameter can only be set in the
2857 <filename>postgresql.conf</> file or on the server command line.
2867 <sect1 id="runtime-config-replication">
2868 <title>Replication</title>
2871 These settings control the behavior of the built-in
2872 <firstterm>streaming replication</> feature (see
2873 <xref linkend="streaming-replication">). Servers will be either a
2874 Master or a Standby server. Masters can send data, while Standby(s)
2875 are always receivers of replicated data. When cascading replication
2876 (see <xref linkend="cascading-replication">) is used, Standby server(s)
2877 can also be senders, as well as receivers.
2878 Parameters are mainly for Sending and Standby servers, though some
2879 parameters have meaning only on the Master server. Settings may vary
2880 across the cluster without problems if that is required.
2883 <sect2 id="runtime-config-replication-sender">
2884 <title>Sending Server(s)</title>
2887 These parameters can be set on any server that is
2888 to send replication data to one or more standby servers.
2889 The master is always a sending server, so these parameters must
2890 always be set on the master.
2891 The role and meaning of these parameters does not change after a
2892 standby becomes the master.
2896 <varlistentry id="guc-max-wal-senders" xreflabel="max_wal_senders">
2897 <term><varname>max_wal_senders</varname> (<type>integer</type>)
2899 <primary><varname>max_wal_senders</> configuration parameter</primary>
2904 Specifies the maximum number of concurrent connections from
2905 standby servers or streaming base backup clients (i.e., the
2906 maximum number of simultaneously running WAL sender
2907 processes). The default is zero, meaning replication is
2908 disabled. WAL sender processes count towards the total number
2909 of connections, so the parameter cannot be set higher than
2910 <xref linkend="guc-max-connections">. Abrupt streaming client
2911 disconnection might cause an orphaned connection slot until
2912 a timeout is reached, so this parameter should be set slightly
2913 higher than the maximum number of expected clients so disconnected
2914 clients can immediately reconnect. This parameter can only
2915 be set at server start. <varname>wal_level</> must be set to
2916 <literal>replica</> or higher to allow connections from standby
2922 <varlistentry id="guc-max-replication-slots" xreflabel="max_replication_slots">
2923 <term><varname>max_replication_slots</varname> (<type>integer</type>)
2925 <primary><varname>max_replication_slots</> configuration parameter</primary>
2930 Specifies the maximum number of replication slots
2931 (see <xref linkend="streaming-replication-slots">) that the server
2932 can support. The default is zero. This parameter can only be set at
2934 <varname>wal_level</varname> must be set
2935 to <literal>replica</literal> or higher to allow replication slots to
2936 be used. Setting it to a lower value than the number of currently
2937 existing replication slots will prevent the server from starting.
2942 <varlistentry id="guc-wal-keep-segments" xreflabel="wal_keep_segments">
2943 <term><varname>wal_keep_segments</varname> (<type>integer</type>)
2945 <primary><varname>wal_keep_segments</> configuration parameter</primary>
2950 Specifies the minimum number of past log file segments kept in the
2952 directory, in case a standby server needs to fetch them for streaming
2953 replication. Each segment is normally 16 megabytes. If a standby
2954 server connected to the sending server falls behind by more than
2955 <varname>wal_keep_segments</> segments, the sending server might remove
2956 a WAL segment still needed by the standby, in which case the
2957 replication connection will be terminated. Downstream connections
2958 will also eventually fail as a result. (However, the standby
2959 server can recover by fetching the segment from archive, if WAL
2960 archiving is in use.)
2964 This sets only the minimum number of segments retained in
2965 <filename>pg_wal</>; the system might need to retain more segments
2966 for WAL archival or to recover from a checkpoint. If
2967 <varname>wal_keep_segments</> is zero (the default), the system
2968 doesn't keep any extra segments for standby purposes, so the number
2969 of old WAL segments available to standby servers is a function of
2970 the location of the previous checkpoint and status of WAL
2972 This parameter can only be set in the
2973 <filename>postgresql.conf</> file or on the server command line.
2978 <varlistentry id="guc-wal-sender-timeout" xreflabel="wal_sender_timeout">
2979 <term><varname>wal_sender_timeout</varname> (<type>integer</type>)
2981 <primary><varname>wal_sender_timeout</> configuration parameter</primary>
2986 Terminate replication connections that are inactive longer
2987 than the specified number of milliseconds. This is useful for
2988 the sending server to detect a standby crash or network outage.
2989 A value of zero disables the timeout mechanism. This parameter
2991 the <filename>postgresql.conf</> file or on the server command line.
2992 The default value is 60 seconds.
2997 <varlistentry id="guc-track-commit-timestamp" xreflabel="track_commit_timestamp">
2998 <term><varname>track_commit_timestamp</varname> (<type>bool</type>)
3000 <primary><varname>track_commit_timestamp</> configuration parameter</primary>
3005 Record commit time of transactions. This parameter
3006 can only be set in <filename>postgresql.conf</> file or on the server
3007 command line. The default value is <literal>off</literal>.
3015 <sect2 id="runtime-config-replication-master">
3016 <title>Master Server</title>
3019 These parameters can be set on the master/primary server that is
3020 to send replication data to one or more standby servers.
3021 Note that in addition to these parameters,
3022 <xref linkend="guc-wal-level"> must be set appropriately on the master
3023 server, and optionally WAL archiving can be enabled as
3024 well (see <xref linkend="runtime-config-wal-archiving">).
3025 The values of these parameters on standby servers are irrelevant,
3026 although you may wish to set them there in preparation for the
3027 possibility of a standby becoming the master.
3032 <varlistentry id="guc-synchronous-standby-names" xreflabel="synchronous_standby_names">
3033 <term><varname>synchronous_standby_names</varname> (<type>string</type>)
3035 <primary><varname>synchronous_standby_names</> configuration parameter</primary>
3040 Specifies a list of standby servers that can support
3041 <firstterm>synchronous replication</>, as described in
3042 <xref linkend="synchronous-replication">.
3043 There will be one or more active synchronous standbys;
3044 transactions waiting for commit will be allowed to proceed after
3045 these standby servers confirm receipt of their data.
3046 The synchronous standbys will be those whose names appear
3047 earlier in this list, and
3048 that are both currently connected and streaming data in real-time
3049 (as shown by a state of <literal>streaming</literal> in the
3050 <link linkend="monitoring-stats-views-table">
3051 <literal>pg_stat_replication</></link> view).
3052 Other standby servers appearing later in this list represent potential
3053 synchronous standbys. If any of the current synchronous
3054 standbys disconnects for whatever reason,
3055 it will be replaced immediately with the next-highest-priority standby.
3056 Specifying more than one standby name can allow very high availability.
3059 This parameter specifies a list of standby servers using
3060 either of the following syntaxes:
3062 <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="parameter">standby_name</replaceable> [, ...] )
3063 <replaceable class="parameter">standby_name</replaceable> [, ...]
3065 where <replaceable class="parameter">num_sync</replaceable> is
3066 the number of synchronous standbys that transactions need to
3067 wait for replies from,
3068 and <replaceable class="parameter">standby_name</replaceable>
3069 is the name of a standby server. For example, a setting of
3070 <literal>3 (s1, s2, s3, s4)</> makes transaction commits wait
3071 until their WAL records are received by three higher-priority standbys
3072 chosen from standby servers <literal>s1</>, <literal>s2</>,
3073 <literal>s3</> and <literal>s4</>.
3076 The second syntax was used before <productname>PostgreSQL</>
3077 version 9.6 and is still supported. It's the same as the first syntax
3078 with <replaceable class="parameter">num_sync</replaceable> equal to 1.
3079 For example, <literal>1 (s1, s2)</> and
3080 <literal>s1, s2</> have the same meaning: either <literal>s1</>
3081 or <literal>s2</> is chosen as a synchronous standby.
3084 The name of a standby server for this purpose is the
3085 <varname>application_name</> setting of the standby, as set in the
3086 <varname>primary_conninfo</> of the standby's WAL receiver. There is
3087 no mechanism to enforce uniqueness. In case of duplicates one of the
3088 matching standbys will be considered as higher priority, though
3089 exactly which one is indeterminate.
3090 The special entry <literal>*</> matches any
3091 <varname>application_name</>, including the default application name
3092 of <literal>walreceiver</>.
3096 Each <replaceable class="parameter">standby_name</replaceable>
3097 should have the form of a valid SQL identifier, unless it
3098 is <literal>*</>. You can use double-quoting if necessary. But note
3099 that <replaceable class="parameter">standby_name</replaceable>s are
3100 compared to standby application names case-insensitively, whether
3101 double-quoted or not.
3105 If no synchronous standby names are specified here, then synchronous
3106 replication is not enabled and transaction commits will not wait for
3107 replication. This is the default configuration. Even when
3108 synchronous replication is enabled, individual transactions can be
3109 configured not to wait for replication by setting the
3110 <xref linkend="guc-synchronous-commit"> parameter to
3111 <literal>local</> or <literal>off</>.
3114 This parameter can only be set in the <filename>postgresql.conf</>
3115 file or on the server command line.
3120 <varlistentry id="guc-vacuum-defer-cleanup-age" xreflabel="vacuum_defer_cleanup_age">
3121 <term><varname>vacuum_defer_cleanup_age</varname> (<type>integer</type>)
3123 <primary><varname>vacuum_defer_cleanup_age</> configuration parameter</primary>
3128 Specifies the number of transactions by which <command>VACUUM</> and
3129 <acronym>HOT</> updates will defer cleanup of dead row versions. The
3130 default is zero transactions, meaning that dead row versions can be
3131 removed as soon as possible, that is, as soon as they are no longer
3132 visible to any open transaction. You may wish to set this to a
3133 non-zero value on a primary server that is supporting hot standby
3134 servers, as described in <xref linkend="hot-standby">. This allows
3135 more time for queries on the standby to complete without incurring
3136 conflicts due to early cleanup of rows. However, since the value
3137 is measured in terms of number of write transactions occurring on the
3138 primary server, it is difficult to predict just how much additional
3139 grace time will be made available to standby queries.
3140 This parameter can only be set in the <filename>postgresql.conf</>
3141 file or on the server command line.
3144 You should also consider setting <varname>hot_standby_feedback</>
3145 on standby server(s) as an alternative to using this parameter.
3148 This does not prevent cleanup of dead rows which have reached the age
3149 specified by <varname>old_snapshot_threshold</>.
3157 <sect2 id="runtime-config-replication-standby">
3158 <title>Standby Servers</title>
3161 These settings control the behavior of a standby server that is
3162 to receive replication data. Their values on the master server
3168 <varlistentry id="guc-hot-standby" xreflabel="hot_standby">
3169 <term><varname>hot_standby</varname> (<type>boolean</type>)
3171 <primary><varname>hot_standby</> configuration parameter</primary>
3176 Specifies whether or not you can connect and run queries during
3177 recovery, as described in <xref linkend="hot-standby">.
3178 The default value is <literal>off</literal>.
3179 This parameter can only be set at server start. It only has effect
3180 during archive recovery or in standby mode.
3185 <varlistentry id="guc-max-standby-archive-delay" xreflabel="max_standby_archive_delay">
3186 <term><varname>max_standby_archive_delay</varname> (<type>integer</type>)
3188 <primary><varname>max_standby_archive_delay</> configuration parameter</primary>
3193 When Hot Standby is active, this parameter determines how long the
3194 standby server should wait before canceling standby queries that
3195 conflict with about-to-be-applied WAL entries, as described in
3196 <xref linkend="hot-standby-conflict">.
3197 <varname>max_standby_archive_delay</> applies when WAL data is
3198 being read from WAL archive (and is therefore not current).
3199 The default is 30 seconds. Units are milliseconds if not specified.
3200 A value of -1 allows the standby to wait forever for conflicting
3201 queries to complete.
3202 This parameter can only be set in the <filename>postgresql.conf</>
3203 file or on the server command line.
3206 Note that <varname>max_standby_archive_delay</> is not the same as the
3207 maximum length of time a query can run before cancellation; rather it
3208 is the maximum total time allowed to apply any one WAL segment's data.
3209 Thus, if one query has resulted in significant delay earlier in the
3210 WAL segment, subsequent conflicting queries will have much less grace
3216 <varlistentry id="guc-max-standby-streaming-delay" xreflabel="max_standby_streaming_delay">
3217 <term><varname>max_standby_streaming_delay</varname> (<type>integer</type>)
3219 <primary><varname>max_standby_streaming_delay</> configuration parameter</primary>
3224 When Hot Standby is active, this parameter determines how long the
3225 standby server should wait before canceling standby queries that
3226 conflict with about-to-be-applied WAL entries, as described in
3227 <xref linkend="hot-standby-conflict">.
3228 <varname>max_standby_streaming_delay</> applies when WAL data is
3229 being received via streaming replication.
3230 The default is 30 seconds. Units are milliseconds if not specified.
3231 A value of -1 allows the standby to wait forever for conflicting
3232 queries to complete.
3233 This parameter can only be set in the <filename>postgresql.conf</>
3234 file or on the server command line.
3237 Note that <varname>max_standby_streaming_delay</> is not the same as
3238 the maximum length of time a query can run before cancellation; rather
3239 it is the maximum total time allowed to apply WAL data once it has
3240 been received from the primary server. Thus, if one query has
3241 resulted in significant delay, subsequent conflicting queries will
3242 have much less grace time until the standby server has caught up
3248 <varlistentry id="guc-wal-receiver-status-interval" xreflabel="wal_receiver_status_interval">
3249 <term><varname>wal_receiver_status_interval</varname> (<type>integer</type>)
3251 <primary><varname>wal_receiver_status_interval</> configuration parameter</primary>
3256 Specifies the minimum frequency for the WAL receiver
3257 process on the standby to send information about replication progress
3258 to the primary or upstream standby, where it can be seen using the
3259 <link linkend="monitoring-stats-views-table">
3260 <literal>pg_stat_replication</></link> view. The standby will report
3261 the last transaction log position it has written, the last position it
3262 has flushed to disk, and the last position it has applied.
3264 value is the maximum interval, in seconds, between reports. Updates are
3265 sent each time the write or flush positions change, or at least as
3266 often as specified by this parameter. Thus, the apply position may
3267 lag slightly behind the true position. Setting this parameter to zero
3268 disables status updates completely. This parameter can only be set in
3269 the <filename>postgresql.conf</> file or on the server command line.
3270 The default value is 10 seconds.
3275 <varlistentry id="guc-hot-standby-feedback" xreflabel="hot_standby_feedback">
3276 <term><varname>hot_standby_feedback</varname> (<type>boolean</type>)
3278 <primary><varname>hot_standby_feedback</> configuration parameter</primary>
3283 Specifies whether or not a hot standby will send feedback to the primary
3285 about queries currently executing on the standby. This parameter can
3286 be used to eliminate query cancels caused by cleanup records, but
3287 can cause database bloat on the primary for some workloads.
3288 Feedback messages will not be sent more frequently than once per
3289 <varname>wal_receiver_status_interval</>. The default value is
3290 <literal>off</literal>. This parameter can only be set in the
3291 <filename>postgresql.conf</> file or on the server command line.
3294 If cascaded replication is in use the feedback is passed upstream
3295 until it eventually reaches the primary. Standbys make no other use
3296 of feedback they receive other than to pass upstream.
3299 This setting does not override the behavior of
3300 <varname>old_snapshot_threshold</> on the primary; a snapshot on the
3301 standby which exceeds the primary's age threshold can become invalid,
3302 resulting in cancellation of transactions on the standby. This is
3303 because <varname>old_snapshot_threshold</> is intended to provide an
3304 absolute limit on the time which dead rows can contribute to bloat,
3305 which would otherwise be violated because of the configuration of a
3311 <varlistentry id="guc-wal-receiver-timeout" xreflabel="wal_receiver_timeout">
3312 <term><varname>wal_receiver_timeout</varname> (<type>integer</type>)
3314 <primary><varname>wal_receiver_timeout</> configuration parameter</primary>
3319 Terminate replication connections that are inactive longer
3320 than the specified number of milliseconds. This is useful for
3321 the receiving standby server to detect a primary node crash or network
3323 A value of zero disables the timeout mechanism. This parameter
3325 the <filename>postgresql.conf</> file or on the server command line.
3326 The default value is 60 seconds.
3331 <varlistentry id="guc-wal-retrieve-retry-interval" xreflabel="wal_retrieve_retry_interval">
3332 <term><varname>wal_retrieve_retry_interval</varname> (<type>integer</type>)
3334 <primary><varname>wal_retrieve_retry_interval</> configuration parameter</primary>
3339 Specify how long the standby server should wait when WAL data is not
3340 available from any sources (streaming replication,
3341 local <filename>pg_wal</> or WAL archive) before retrying to
3342 retrieve WAL data. This parameter can only be set in the
3343 <filename>postgresql.conf</> file or on the server command line.
3344 The default value is 5 seconds. Units are milliseconds if not specified.
3347 This parameter is useful in configurations where a node in recovery
3348 needs to control the amount of time to wait for new WAL data to be
3349 available. For example, in archive recovery, it is possible to
3350 make the recovery more responsive in the detection of a new WAL
3351 log file by reducing the value of this parameter. On a system with
3352 low WAL activity, increasing it reduces the amount of requests necessary
3353 to access WAL archives, something useful for example in cloud
3354 environments where the amount of times an infrastructure is accessed
3355 is taken into account.
3364 <sect1 id="runtime-config-query">
3365 <title>Query Planning</title>
3367 <sect2 id="runtime-config-query-enable">
3368 <title>Planner Method Configuration</title>
3371 These configuration parameters provide a crude method of
3372 influencing the query plans chosen by the query optimizer. If
3373 the default plan chosen by the optimizer for a particular query
3374 is not optimal, a <emphasis>temporary</> solution is to use one
3375 of these configuration parameters to force the optimizer to
3376 choose a different plan.
3377 Better ways to improve the quality of the
3378 plans chosen by the optimizer include adjusting the planer cost
3379 constants (see <xref linkend="runtime-config-query-constants">),
3380 running <xref linkend="sql-analyze"> manually, increasing
3381 the value of the <xref
3382 linkend="guc-default-statistics-target"> configuration parameter,
3383 and increasing the amount of statistics collected for
3384 specific columns using <command>ALTER TABLE SET
3385 STATISTICS</command>.
3389 <varlistentry id="guc-enable-bitmapscan" xreflabel="enable_bitmapscan">
3390 <term><varname>enable_bitmapscan</varname> (<type>boolean</type>)
3392 <primary>bitmap scan</primary>
3395 <primary><varname>enable_bitmapscan</> configuration parameter</primary>
3400 Enables or disables the query planner's use of bitmap-scan plan
3401 types. The default is <literal>on</>.
3406 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
3407 <term><varname>enable_hashagg</varname> (<type>boolean</type>)
3409 <primary><varname>enable_hashagg</> configuration parameter</primary>
3414 Enables or disables the query planner's use of hashed
3415 aggregation plan types. The default is <literal>on</>.
3420 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
3421 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)
3423 <primary><varname>enable_hashjoin</> configuration parameter</primary>
3428 Enables or disables the query planner's use of hash-join plan
3429 types. The default is <literal>on</>.
3434 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
3435 <term><varname>enable_indexscan</varname> (<type>boolean</type>)
3437 <primary>index scan</primary>
3440 <primary><varname>enable_indexscan</> configuration parameter</primary>
3445 Enables or disables the query planner's use of index-scan plan
3446 types. The default is <literal>on</>.
3451 <varlistentry id="guc-enable-indexonlyscan" xreflabel="enable_indexonlyscan">
3452 <term><varname>enable_indexonlyscan</varname> (<type>boolean</type>)
3454 <primary><varname>enable_indexonlyscan</> configuration parameter</primary>
3459 Enables or disables the query planner's use of index-only-scan plan
3460 types (see <xref linkend="indexes-index-only-scans">).
3461 The default is <literal>on</>.
3466 <varlistentry id="guc-enable-material" xreflabel="enable_material">
3467 <term><varname>enable_material</varname> (<type>boolean</type>)
3469 <primary><varname>enable_material</> configuration parameter</primary>
3474 Enables or disables the query planner's use of materialization.
3475 It is impossible to suppress materialization entirely,
3476 but turning this variable off prevents the planner from inserting
3477 materialize nodes except in cases where it is required for correctness.
3478 The default is <literal>on</>.
3483 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
3484 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)
3486 <primary><varname>enable_mergejoin</> configuration parameter</primary>
3491 Enables or disables the query planner's use of merge-join plan
3492 types. The default is <literal>on</>.
3497 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
3498 <term><varname>enable_nestloop</varname> (<type>boolean</type>)
3500 <primary><varname>enable_nestloop</> configuration parameter</primary>
3505 Enables or disables the query planner's use of nested-loop join
3506 plans. It is impossible to suppress nested-loop joins entirely,
3507 but turning this variable off discourages the planner from using
3508 one if there are other methods available. The default is
3514 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
3515 <term><varname>enable_seqscan</varname> (<type>boolean</type>)
3517 <primary>sequential scan</primary>
3520 <primary><varname>enable_seqscan</> configuration parameter</primary>
3525 Enables or disables the query planner's use of sequential scan
3526 plan types. It is impossible to suppress sequential scans
3527 entirely, but turning this variable off discourages the planner
3528 from using one if there are other methods available. The
3529 default is <literal>on</>.
3534 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
3535 <term><varname>enable_sort</varname> (<type>boolean</type>)
3537 <primary><varname>enable_sort</> configuration parameter</primary>
3542 Enables or disables the query planner's use of explicit sort
3543 steps. It is impossible to suppress explicit sorts entirely,
3544 but turning this variable off discourages the planner from
3545 using one if there are other methods available. The default
3551 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
3552 <term><varname>enable_tidscan</varname> (<type>boolean</type>)
3554 <primary><varname>enable_tidscan</> configuration parameter</primary>
3559 Enables or disables the query planner's use of <acronym>TID</>
3560 scan plan types. The default is <literal>on</>.
3567 <sect2 id="runtime-config-query-constants">
3568 <title>Planner Cost Constants</title>
3571 The <firstterm>cost</> variables described in this section are measured
3572 on an arbitrary scale. Only their relative values matter, hence
3573 scaling them all up or down by the same factor will result in no change
3574 in the planner's choices. By default, these cost variables are based on
3575 the cost of sequential page fetches; that is,
3576 <varname>seq_page_cost</> is conventionally set to <literal>1.0</>
3577 and the other cost variables are set with reference to that. But
3578 you can use a different scale if you prefer, such as actual execution
3579 times in milliseconds on a particular machine.
3584 Unfortunately, there is no well-defined method for determining ideal
3585 values for the cost variables. They are best treated as averages over
3586 the entire mix of queries that a particular installation will receive. This
3587 means that changing them on the basis of just a few experiments is very
3594 <varlistentry id="guc-seq-page-cost" xreflabel="seq_page_cost">
3595 <term><varname>seq_page_cost</varname> (<type>floating point</type>)
3597 <primary><varname>seq_page_cost</> configuration parameter</primary>
3602 Sets the planner's estimate of the cost of a disk page fetch
3603 that is part of a series of sequential fetches. The default is 1.0.
3604 This value can be overridden for tables and indexes in a particular
3605 tablespace by setting the tablespace parameter of the same name
3606 (see <xref linkend="sql-altertablespace">).
3611 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
3612 <term><varname>random_page_cost</varname> (<type>floating point</type>)
3614 <primary><varname>random_page_cost</> configuration parameter</primary>
3619 Sets the planner's estimate of the cost of a
3620 non-sequentially-fetched disk page. The default is 4.0.
3621 This value can be overridden for tables and indexes in a particular
3622 tablespace by setting the tablespace parameter of the same name
3623 (see <xref linkend="sql-altertablespace">).
3627 Reducing this value relative to <varname>seq_page_cost</>
3628 will cause the system to prefer index scans; raising it will
3629 make index scans look relatively more expensive. You can raise
3630 or lower both values together to change the importance of disk I/O
3631 costs relative to CPU costs, which are described by the following
3636 Random access to mechanical disk storage is normally much more expensive
3637 than four times sequential access. However, a lower default is used
3638 (4.0) because the majority of random accesses to disk, such as indexed
3639 reads, are assumed to be in cache. The default value can be thought of
3640 as modeling random access as 40 times slower than sequential, while
3641 expecting 90% of random reads to be cached.
3645 If you believe a 90% cache rate is an incorrect assumption
3646 for your workload, you can increase random_page_cost to better
3647 reflect the true cost of random storage reads. Correspondingly,
3648 if your data is likely to be completely in cache, such as when
3649 the database is smaller than the total server memory, decreasing
3650 random_page_cost can be appropriate. Storage that has a low random
3651 read cost relative to sequential, e.g. solid-state drives, might
3652 also be better modeled with a lower value for random_page_cost.
3657 Although the system will let you set <varname>random_page_cost</> to
3658 less than <varname>seq_page_cost</>, it is not physically sensible
3659 to do so. However, setting them equal makes sense if the database
3660 is entirely cached in RAM, since in that case there is no penalty
3661 for touching pages out of sequence. Also, in a heavily-cached
3662 database you should lower both values relative to the CPU parameters,
3663 since the cost of fetching a page already in RAM is much smaller
3664 than it would normally be.
3670 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
3671 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)
3673 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
3678 Sets the planner's estimate of the cost of processing
3679 each row during a query.
3680 The default is 0.01.
3685 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
3686 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)
3688 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
3693 Sets the planner's estimate of the cost of processing
3694 each index entry during an index scan.
3695 The default is 0.005.
3700 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
3701 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)
3703 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
3708 Sets the planner's estimate of the cost of processing each
3709 operator or function executed during a query.
3710 The default is 0.0025.
3715 <varlistentry id="guc-parallel-setup-cost" xreflabel="parallel_setup_cost">
3716 <term><varname>parallel_setup_cost</varname> (<type>floating point</type>)
3718 <primary><varname>parallel_setup_cost</> configuration parameter</primary>
3723 Sets the planner's estimate of the cost of launching parallel worker
3725 The default is 1000.
3730 <varlistentry id="guc-parallel-tuple-cost" xreflabel="parallel_tuple_cost">
3731 <term><varname>parallel_tuple_cost</varname> (<type>floating point</type>)
3733 <primary><varname>parallel_tuple_cost</> configuration parameter</primary>
3738 Sets the planner's estimate of the cost of transferring one tuple
3739 from a parallel worker process to another process.
3745 <varlistentry id="guc-min-parallel-relation-size" xreflabel="min_parallel_relation_size">
3746 <term><varname>min_parallel_relation_size</varname> (<type>integer</type>)
3748 <primary><varname>min_parallel_relation_size</> configuration parameter</primary>
3753 Sets the minimum size of relations to be considered for parallel scan.
3754 The default is 8 megabytes (<literal>8MB</>).
3759 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
3760 <term><varname>effective_cache_size</varname> (<type>integer</type>)
3762 <primary><varname>effective_cache_size</> configuration parameter</primary>
3767 Sets the planner's assumption about the effective size of the
3768 disk cache that is available to a single query. This is
3769 factored into estimates of the cost of using an index; a
3770 higher value makes it more likely index scans will be used, a
3771 lower value makes it more likely sequential scans will be
3772 used. When setting this parameter you should consider both
3773 <productname>PostgreSQL</productname>'s shared buffers and the
3774 portion of the kernel's disk cache that will be used for
3775 <productname>PostgreSQL</productname> data files. Also, take
3776 into account the expected number of concurrent queries on different
3777 tables, since they will have to share the available
3778 space. This parameter has no effect on the size of shared
3779 memory allocated by <productname>PostgreSQL</productname>, nor
3780 does it reserve kernel disk cache; it is used only for estimation
3781 purposes. The system also does not assume data remains in
3782 the disk cache between queries. The default is 4 gigabytes
3791 <sect2 id="runtime-config-query-geqo">
3792 <title>Genetic Query Optimizer</title>
3795 The genetic query optimizer (GEQO) is an algorithm that does query
3796 planning using heuristic searching. This reduces planning time for
3797 complex queries (those joining many relations), at the cost of producing
3798 plans that are sometimes inferior to those found by the normal
3799 exhaustive-search algorithm.
3800 For more information see <xref linkend="geqo">.
3805 <varlistentry id="guc-geqo" xreflabel="geqo">
3806 <term><varname>geqo</varname> (<type>boolean</type>)
3808 <primary>genetic query optimization</primary>
3811 <primary>GEQO</primary>
3812 <see>genetic query optimization</see>
3815 <primary><varname>geqo</> configuration parameter</primary>
3820 Enables or disables genetic query optimization.
3821 This is on by default. It is usually best not to turn it off in
3822 production; the <varname>geqo_threshold</varname> variable provides
3823 more granular control of GEQO.
3828 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
3829 <term><varname>geqo_threshold</varname> (<type>integer</type>)
3831 <primary><varname>geqo_threshold</> configuration parameter</primary>
3836 Use genetic query optimization to plan queries with at least
3837 this many <literal>FROM</> items involved. (Note that a
3838 <literal>FULL OUTER JOIN</> construct counts as only one <literal>FROM</>
3839 item.) The default is 12. For simpler queries it is usually best
3840 to use the regular, exhaustive-search planner, but for queries with
3841 many tables the exhaustive search takes too long, often
3842 longer than the penalty of executing a suboptimal plan. Thus,
3843 a threshold on the size of the query is a convenient way to manage
3849 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
3850 <term><varname>geqo_effort</varname> (<type>integer</type>)
3852 <primary><varname>geqo_effort</> configuration parameter</primary>
3857 Controls the trade-off between planning time and query plan
3858 quality in GEQO. This variable must be an integer in the
3859 range from 1 to 10. The default value is five. Larger values
3860 increase the time spent doing query planning, but also
3861 increase the likelihood that an efficient query plan will be
3866 <varname>geqo_effort</varname> doesn't actually do anything
3867 directly; it is only used to compute the default values for
3868 the other variables that influence GEQO behavior (described
3869 below). If you prefer, you can set the other parameters by
3875 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
3876 <term><varname>geqo_pool_size</varname> (<type>integer</type>)
3878 <primary><varname>geqo_pool_size</> configuration parameter</primary>
3883 Controls the pool size used by GEQO, that is the
3884 number of individuals in the genetic population. It must be
3885 at least two, and useful values are typically 100 to 1000. If
3886 it is set to zero (the default setting) then a suitable
3887 value is chosen based on <varname>geqo_effort</varname> and
3888 the number of tables in the query.
3893 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
3894 <term><varname>geqo_generations</varname> (<type>integer</type>)
3896 <primary><varname>geqo_generations</> configuration parameter</primary>
3901 Controls the number of generations used by GEQO, that is
3902 the number of iterations of the algorithm. It must
3903 be at least one, and useful values are in the same range as
3904 the pool size. If it is set to zero (the default setting)
3905 then a suitable value is chosen based on
3906 <varname>geqo_pool_size</varname>.
3911 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
3912 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)
3914 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
3919 Controls the selection bias used by GEQO. The selection bias
3920 is the selective pressure within the population. Values can be
3921 from 1.50 to 2.00; the latter is the default.
3926 <varlistentry id="guc-geqo-seed" xreflabel="geqo_seed">
3927 <term><varname>geqo_seed</varname> (<type>floating point</type>)
3929 <primary><varname>geqo_seed</> configuration parameter</primary>
3934 Controls the initial value of the random number generator used
3935 by GEQO to select random paths through the join order search space.
3936 The value can range from zero (the default) to one. Varying the
3937 value changes the set of join paths explored, and may result in a
3938 better or worse best path being found.
3945 <sect2 id="runtime-config-query-other">
3946 <title>Other Planner Options</title>
3950 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
3951 <term><varname>default_statistics_target</varname> (<type>integer</type>)
3953 <primary><varname>default_statistics_target</> configuration parameter</primary>
3958 Sets the default statistics target for table columns without
3959 a column-specific target set via <command>ALTER TABLE
3960 SET STATISTICS</>. Larger values increase the time needed to
3961 do <command>ANALYZE</>, but might improve the quality of the
3962 planner's estimates. The default is 100. For more information
3963 on the use of statistics by the <productname>PostgreSQL</>
3964 query planner, refer to <xref linkend="planner-stats">.
3969 <varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
3970 <term><varname>constraint_exclusion</varname> (<type>enum</type>)
3972 <primary>constraint exclusion</primary>
3975 <primary><varname>constraint_exclusion</> configuration parameter</primary>
3980 Controls the query planner's use of table constraints to
3982 The allowed values of <varname>constraint_exclusion</> are
3983 <literal>on</> (examine constraints for all tables),
3984 <literal>off</> (never examine constraints), and
3985 <literal>partition</> (examine constraints only for inheritance child
3986 tables and <literal>UNION ALL</> subqueries).
3987 <literal>partition</> is the default setting.
3988 It is often used with inheritance and partitioned tables to
3989 improve performance.
3993 When this parameter allows it for a particular table, the planner
3994 compares query conditions with the table's <literal>CHECK</>
3995 constraints, and omits scanning tables for which the conditions
3996 contradict the constraints. For example:
3999 CREATE TABLE parent(key integer, ...);
4000 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
4001 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
4003 SELECT * FROM parent WHERE key = 2400;
4006 With constraint exclusion enabled, this <command>SELECT</>
4007 will not scan <structname>child1000</> at all, improving performance.
4011 Currently, constraint exclusion is enabled by default
4012 only for cases that are often used to implement table partitioning.
4013 Turning it on for all tables imposes extra planning overhead that is
4014 quite noticeable on simple queries, and most often will yield no
4015 benefit for simple queries. If you have no partitioned tables
4016 you might prefer to turn it off entirely.
4020 Refer to <xref linkend="ddl-partitioning-constraint-exclusion"> for
4021 more information on using constraint exclusion and partitioning.
4026 <varlistentry id="guc-cursor-tuple-fraction" xreflabel="cursor_tuple_fraction">
4027 <term><varname>cursor_tuple_fraction</varname> (<type>floating point</type>)
4029 <primary><varname>cursor_tuple_fraction</> configuration parameter</primary>
4034 Sets the planner's estimate of the fraction of a cursor's rows that
4035 will be retrieved. The default is 0.1. Smaller values of this
4036 setting bias the planner towards using <quote>fast start</> plans
4037 for cursors, which will retrieve the first few rows quickly while
4038 perhaps taking a long time to fetch all rows. Larger values
4039 put more emphasis on the total estimated time. At the maximum
4040 setting of 1.0, cursors are planned exactly like regular queries,
4041 considering only the total estimated time and not how soon the
4042 first rows might be delivered.
4047 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
4048 <term><varname>from_collapse_limit</varname> (<type>integer</type>)
4050 <primary><varname>from_collapse_limit</> configuration parameter</primary>
4055 The planner will merge sub-queries into upper queries if the
4056 resulting <literal>FROM</literal> list would have no more than
4057 this many items. Smaller values reduce planning time but might
4058 yield inferior query plans. The default is eight.
4059 For more information see <xref linkend="explicit-joins">.
4063 Setting this value to <xref linkend="guc-geqo-threshold"> or more
4064 may trigger use of the GEQO planner, resulting in non-optimal
4065 plans. See <xref linkend="runtime-config-query-geqo">.
4070 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
4071 <term><varname>join_collapse_limit</varname> (<type>integer</type>)
4073 <primary><varname>join_collapse_limit</> configuration parameter</primary>
4078 The planner will rewrite explicit <literal>JOIN</>
4079 constructs (except <literal>FULL JOIN</>s) into lists of
4080 <literal>FROM</> items whenever a list of no more than this many items
4081 would result. Smaller values reduce planning time but might
4082 yield inferior query plans.
4086 By default, this variable is set the same as
4087 <varname>from_collapse_limit</varname>, which is appropriate
4088 for most uses. Setting it to 1 prevents any reordering of
4089 explicit <literal>JOIN</>s. Thus, the explicit join order
4090 specified in the query will be the actual order in which the
4091 relations are joined. Because the query planner does not always choose
4092 the optimal join order, advanced users can elect to
4093 temporarily set this variable to 1, and then specify the join
4094 order they desire explicitly.
4095 For more information see <xref linkend="explicit-joins">.
4099 Setting this value to <xref linkend="guc-geqo-threshold"> or more
4100 may trigger use of the GEQO planner, resulting in non-optimal
4101 plans. See <xref linkend="runtime-config-query-geqo">.
4106 <varlistentry id="guc-force-parallel-mode" xreflabel="force_parallel_mode">
4107 <term><varname>force_parallel_mode</varname> (<type>enum</type>)
4109 <primary><varname>force_parallel_mode</> configuration parameter</primary>
4114 Allows the use of parallel queries for testing purposes even in cases
4115 where no performance benefit is expected.
4116 The allowed values of <varname>force_parallel_mode</> are
4117 <literal>off</> (use parallel mode only when it is expected to improve
4118 performance), <literal>on</> (force parallel query for all queries
4119 for which it is thought to be safe), and <literal>regress</> (like
4120 <literal>on</>, but with additional behavior changes as explained
4125 More specifically, setting this value to <literal>on</> will add
4126 a <literal>Gather</> node to the top of any query plan for which this
4127 appears to be safe, so that the query runs inside of a parallel worker.
4128 Even when a parallel worker is not available or cannot be used,
4129 operations such as starting a subtransaction that would be prohibited
4130 in a parallel query context will be prohibited unless the planner
4131 believes that this will cause the query to fail. If failures or
4132 unexpected results occur when this option is set, some functions used
4133 by the query may need to be marked <literal>PARALLEL UNSAFE</literal>
4134 (or, possibly, <literal>PARALLEL RESTRICTED</literal>).
4138 Setting this value to <literal>regress</> has all of the same effects
4139 as setting it to <literal>on</> plus some additional effects that are
4140 intended to facilitate automated regression testing. Normally,
4141 messages from a parallel worker include a context line indicating that,
4142 but a setting of <literal>regress</> suppresses this line so that the
4143 output is the same as in non-parallel execution. Also,
4144 the <literal>Gather</> nodes added to plans by this setting are hidden
4145 in <literal>EXPLAIN</> output so that the output matches what
4146 would be obtained if this setting were turned <literal>off</>.
4155 <sect1 id="runtime-config-logging">
4156 <title>Error Reporting and Logging</title>
4158 <indexterm zone="runtime-config-logging">
4159 <primary>server log</primary>
4162 <sect2 id="runtime-config-logging-where">
4163 <title>Where To Log</title>
4165 <indexterm zone="runtime-config-logging-where">
4166 <primary>where to log</primary>
4171 <varlistentry id="guc-log-destination" xreflabel="log_destination">
4172 <term><varname>log_destination</varname> (<type>string</type>)
4174 <primary><varname>log_destination</> configuration parameter</primary>
4179 <productname>PostgreSQL</productname> supports several methods
4180 for logging server messages, including
4181 <systemitem>stderr</systemitem>, <systemitem>csvlog</systemitem> and
4182 <systemitem>syslog</systemitem>. On Windows,
4183 <systemitem>eventlog</systemitem> is also supported. Set this
4184 parameter to a list of desired log destinations separated by
4185 commas. The default is to log to <systemitem>stderr</systemitem>
4187 This parameter can only be set in the <filename>postgresql.conf</>
4188 file or on the server command line.
4191 If <systemitem>csvlog</> is included in <varname>log_destination</>,
4192 log entries are output in <quote>comma separated
4193 value</> (<acronym>CSV</>) format, which is convenient for
4194 loading logs into programs.
4195 See <xref linkend="runtime-config-logging-csvlog"> for details.
4196 <xref linkend="guc-logging-collector"> must be enabled to generate
4197 CSV-format log output.
4202 On most Unix systems, you will need to alter the configuration of
4203 your system's <application>syslog</application> daemon in order
4204 to make use of the <systemitem>syslog</systemitem> option for
4205 <varname>log_destination</>. <productname>PostgreSQL</productname>
4206 can log to <application>syslog</application> facilities
4207 <literal>LOCAL0</> through <literal>LOCAL7</> (see <xref
4208 linkend="guc-syslog-facility">), but the default
4209 <application>syslog</application> configuration on most platforms
4210 will discard all such messages. You will need to add something like:
4212 local0.* /var/log/postgresql
4214 to the <application>syslog</application> daemon's configuration file
4218 On Windows, when you use the <literal>eventlog</literal>
4219 option for <varname>log_destination</>, you should
4220 register an event source and its library with the operating
4221 system so that the Windows Event Viewer can display event
4222 log messages cleanly.
4223 See <xref linkend="event-log-registration"> for details.
4229 <varlistentry id="guc-logging-collector" xreflabel="logging_collector">
4230 <term><varname>logging_collector</varname> (<type>boolean</type>)
4232 <primary><varname>logging_collector</> configuration parameter</primary>
4237 This parameter enables the <firstterm>logging collector</>, which
4238 is a background process that captures log messages
4239 sent to <systemitem>stderr</> and redirects them into log files.
4240 This approach is often more useful than
4241 logging to <application>syslog</>, since some types of messages
4242 might not appear in <application>syslog</> output. (One common
4243 example is dynamic-linker failure messages; another is error messages
4244 produced by scripts such as <varname>archive_command</>.)
4245 This parameter can only be set at server start.
4250 It is possible to log to <systemitem>stderr</> without using the
4251 logging collector; the log messages will just go to wherever the
4252 server's <systemitem>stderr</> is directed. However, that method is
4253 only suitable for low log volumes, since it provides no convenient
4254 way to rotate log files. Also, on some platforms not using the
4255 logging collector can result in lost or garbled log output, because
4256 multiple processes writing concurrently to the same log file can
4257 overwrite each other's output.
4263 The logging collector is designed to never lose messages. This means
4264 that in case of extremely high load, server processes could be
4265 blocked while trying to send additional log messages when the
4266 collector has fallen behind. In contrast, <application>syslog</>
4267 prefers to drop messages if it cannot write them, which means it
4268 may fail to log some messages in such cases but it will not block
4269 the rest of the system.
4276 <varlistentry id="guc-log-directory" xreflabel="log_directory">
4277 <term><varname>log_directory</varname> (<type>string</type>)
4279 <primary><varname>log_directory</> configuration parameter</primary>
4284 When <varname>logging_collector</> is enabled,
4285 this parameter determines the directory in which log files will be created.
4286 It can be specified as an absolute path, or relative to the
4287 cluster data directory.
4288 This parameter can only be set in the <filename>postgresql.conf</>
4289 file or on the server command line.
4290 The default is <literal>pg_log</literal>.
4295 <varlistentry id="guc-log-filename" xreflabel="log_filename">
4296 <term><varname>log_filename</varname> (<type>string</type>)
4298 <primary><varname>log_filename</> configuration parameter</primary>
4303 When <varname>logging_collector</varname> is enabled,
4304 this parameter sets the file names of the created log files. The value
4305 is treated as a <function>strftime</function> pattern,
4306 so <literal>%</literal>-escapes can be used to specify time-varying
4307 file names. (Note that if there are
4308 any time-zone-dependent <literal>%</literal>-escapes, the computation
4309 is done in the zone specified
4310 by <xref linkend="guc-log-timezone">.)
4311 The supported <literal>%</literal>-escapes are similar to those
4312 listed in the Open Group's <ulink
4313 url="http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html">strftime
4314 </ulink> specification.
4315 Note that the system's <function>strftime</function> is not used
4316 directly, so platform-specific (nonstandard) extensions do not work.
4317 The default is <literal>postgresql-%Y-%m-%d_%H%M%S.log</literal>.
4320 If you specify a file name without escapes, you should plan to
4321 use a log rotation utility to avoid eventually filling the
4322 entire disk. In releases prior to 8.4, if
4323 no <literal>%</literal> escapes were
4324 present, <productname>PostgreSQL</productname> would append
4325 the epoch of the new log file's creation time, but this is no
4329 If CSV-format output is enabled in <varname>log_destination</>,
4330 <literal>.csv</> will be appended to the timestamped
4331 log file name to create the file name for CSV-format output.
4332 (If <varname>log_filename</> ends in <literal>.log</>, the suffix is
4336 This parameter can only be set in the <filename>postgresql.conf</>
4337 file or on the server command line.
4342 <varlistentry id="guc-log-file-mode" xreflabel="log_file_mode">
4343 <term><varname>log_file_mode</varname> (<type>integer</type>)
4345 <primary><varname>log_file_mode</> configuration parameter</primary>
4350 On Unix systems this parameter sets the permissions for log files
4351 when <varname>logging_collector</varname> is enabled. (On Microsoft
4352 Windows this parameter is ignored.)
4353 The parameter value is expected to be a numeric mode
4354 specified in the format accepted by the
4355 <function>chmod</function> and <function>umask</function>
4356 system calls. (To use the customary octal format the number
4357 must start with a <literal>0</literal> (zero).)
4360 The default permissions are <literal>0600</>, meaning only the
4361 server owner can read or write the log files. The other commonly
4362 useful setting is <literal>0640</>, allowing members of the owner's
4363 group to read the files. Note however that to make use of such a
4364 setting, you'll need to alter <xref linkend="guc-log-directory"> to
4365 store the files somewhere outside the cluster data directory. In
4366 any case, it's unwise to make the log files world-readable, since
4367 they might contain sensitive data.
4370 This parameter can only be set in the <filename>postgresql.conf</>
4371 file or on the server command line.
4376 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
4377 <term><varname>log_rotation_age</varname> (<type>integer</type>)
4379 <primary><varname>log_rotation_age</> configuration parameter</primary>
4384 When <varname>logging_collector</varname> is enabled,
4385 this parameter determines the maximum lifetime of an individual log file.
4386 After this many minutes have elapsed, a new log file will
4387 be created. Set to zero to disable time-based creation of
4389 This parameter can only be set in the <filename>postgresql.conf</>
4390 file or on the server command line.
4395 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
4396 <term><varname>log_rotation_size</varname> (<type>integer</type>)
4398 <primary><varname>log_rotation_size</> configuration parameter</primary>
4403 When <varname>logging_collector</varname> is enabled,
4404 this parameter determines the maximum size of an individual log file.
4405 After this many kilobytes have been emitted into a log file,
4406 a new log file will be created. Set to zero to disable size-based
4407 creation of new log files.
4408 This parameter can only be set in the <filename>postgresql.conf</>
4409 file or on the server command line.
4414 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
4415 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)
4417 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
4422 When <varname>logging_collector</varname> is enabled,
4423 this parameter will cause <productname>PostgreSQL</productname> to truncate (overwrite),
4424 rather than append to, any existing log file of the same name.
4425 However, truncation will occur only when a new file is being opened
4426 due to time-based rotation, not during server startup or size-based
4427 rotation. When off, pre-existing files will be appended to in
4428 all cases. For example, using this setting in combination with
4429 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
4430 would result in generating twenty-four hourly log files and then
4431 cyclically overwriting them.
4432 This parameter can only be set in the <filename>postgresql.conf</>
4433 file or on the server command line.
4436 Example: To keep 7 days of logs, one log file per day named
4437 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
4438 etc, and automatically overwrite last week's log with this week's log,
4439 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
4440 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>, and
4441 <varname>log_rotation_age</varname> to <literal>1440</literal>.
4444 Example: To keep 24 hours of logs, one log file per hour, but
4445 also rotate sooner if the log file size exceeds 1GB, set
4446 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
4447 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>,
4448 <varname>log_rotation_age</varname> to <literal>60</literal>, and
4449 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
4450 Including <literal>%M</> in <varname>log_filename</varname> allows
4451 any size-driven rotations that might occur to select a file name
4452 different from the hour's initial file name.
4457 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
4458 <term><varname>syslog_facility</varname> (<type>enum</type>)
4460 <primary><varname>syslog_facility</> configuration parameter</primary>
4465 When logging to <application>syslog</> is enabled, this parameter
4466 determines the <application>syslog</application>
4467 <quote>facility</quote> to be used. You can choose
4468 from <literal>LOCAL0</>, <literal>LOCAL1</>,
4469 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
4470 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
4471 the default is <literal>LOCAL0</>. See also the
4472 documentation of your system's
4473 <application>syslog</application> daemon.
4474 This parameter can only be set in the <filename>postgresql.conf</>
4475 file or on the server command line.
4480 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
4481 <term><varname>syslog_ident</varname> (<type>string</type>)
4483 <primary><varname>syslog_ident</> configuration parameter</primary>
4488 When logging to <application>syslog</> is enabled, this parameter
4489 determines the program name used to identify
4490 <productname>PostgreSQL</productname> messages in
4491 <application>syslog</application> logs. The default is
4492 <literal>postgres</literal>.
4493 This parameter can only be set in the <filename>postgresql.conf</>
4494 file or on the server command line.
4499 <varlistentry id="guc-syslog-sequence-numbers" xreflabel="syslog_sequence_numbers">
4500 <term><varname>syslog_sequence_numbers</varname> (<type>boolean</type>)
4502 <primary><varname>syslog_sequence_numbers</> configuration parameter</primary>
4508 When logging to <application>syslog</application> and this is on (the
4509 default), then each message will be prefixed by an increasing
4510 sequence number (such as <literal>[2]</literal>). This circumvents
4511 the <quote>--- last message repeated N times ---</quote> suppression
4512 that many syslog implementations perform by default. In more modern
4513 syslog implementations, repeated message suppression can be configured
4514 (for example, <literal>$RepeatedMsgReduction</literal>
4515 in <productname>rsyslog</productname>), so this might not be
4516 necessary. Also, you could turn this off if you actually want to
4517 suppress repeated messages.
4521 This parameter can only be set in the <filename>postgresql.conf</>
4522 file or on the server command line.
4527 <varlistentry id="guc-syslog-split-messages" xreflabel="syslog_split_messages">
4528 <term><varname>syslog_split_messages</varname> (<type>boolean</type>)
4530 <primary><varname>syslog_split_messages</> configuration parameter</primary>
4535 When logging to <application>syslog</> is enabled, this parameter
4536 determines how messages are delivered to syslog. When on (the
4537 default), messages are split by lines, and long lines are split so
4538 that they will fit into 1024 bytes, which is a typical size limit for
4539 traditional syslog implementations. When off, PostgreSQL server log
4540 messages are delivered to the syslog service as is, and it is up to
4541 the syslog service to cope with the potentially bulky messages.
4545 If syslog is ultimately logging to a text file, then the effect will
4546 be the same either way, and it is best to leave the setting on, since
4547 most syslog implementations either cannot handle large messages or
4548 would need to be specially configured to handle them. But if syslog
4549 is ultimately writing into some other medium, it might be necessary or
4550 more useful to keep messages logically together.
4554 This parameter can only be set in the <filename>postgresql.conf</>
4555 file or on the server command line.
4560 <varlistentry id="guc-event-source" xreflabel="event_source">
4561 <term><varname>event_source</varname> (<type>string</type>)
4563 <primary><varname>event_source</> configuration parameter</primary>
4568 When logging to <application>event log</> is enabled, this parameter
4569 determines the program name used to identify
4570 <productname>PostgreSQL</productname> messages in
4571 the log. The default is <literal>PostgreSQL</literal>.
4572 This parameter can only be set in the <filename>postgresql.conf</>
4573 file or on the server command line.
4580 <sect2 id="runtime-config-logging-when">
4581 <title>When To Log</title>
4585 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
4586 <term><varname>client_min_messages</varname> (<type>enum</type>)
4588 <primary><varname>client_min_messages</> configuration parameter</primary>
4593 Controls which message levels are sent to the client.
4594 Valid values are <literal>DEBUG5</>,
4595 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
4596 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
4597 <literal>WARNING</>, <literal>ERROR</>, <literal>FATAL</>,
4598 and <literal>PANIC</>. Each level
4599 includes all the levels that follow it. The later the level,
4600 the fewer messages are sent. The default is
4601 <literal>NOTICE</>. Note that <literal>LOG</> has a different
4602 rank here than in <varname>log_min_messages</>.
4607 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
4608 <term><varname>log_min_messages</varname> (<type>enum</type>)
4610 <primary><varname>log_min_messages</> configuration parameter</primary>
4615 Controls which message levels are written to the server log.
4616 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
4617 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
4618 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
4619 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
4620 <literal>PANIC</>. Each level includes all the levels that
4621 follow it. The later the level, the fewer messages are sent
4622 to the log. The default is <literal>WARNING</>. Note that
4623 <literal>LOG</> has a different rank here than in
4624 <varname>client_min_messages</>.
4625 Only superusers can change this setting.
4630 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
4631 <term><varname>log_min_error_statement</varname> (<type>enum</type>)
4633 <primary><varname>log_min_error_statement</> configuration parameter</primary>
4638 Controls which SQL statements that cause an error
4639 condition are recorded in the server log. The current
4640 SQL statement is included in the log entry for any message of
4641 the specified severity or higher.
4642 Valid values are <literal>DEBUG5</literal>,
4643 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
4644 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
4645 <literal>INFO</literal>, <literal>NOTICE</literal>,
4646 <literal>WARNING</literal>, <literal>ERROR</literal>,
4647 <literal>LOG</literal>,
4648 <literal>FATAL</literal>, and <literal>PANIC</literal>.
4649 The default is <literal>ERROR</literal>, which means statements
4650 causing errors, log messages, fatal errors, or panics will be logged.
4651 To effectively turn off logging of failing statements,
4652 set this parameter to <literal>PANIC</literal>.
4653 Only superusers can change this setting.
4658 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
4659 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)
4661 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
4666 Causes the duration of each completed statement to be logged
4667 if the statement ran for at least the specified number of
4668 milliseconds. Setting this to zero prints all statement durations.
4669 Minus-one (the default) disables logging statement durations.
4670 For example, if you set it to <literal>250ms</literal>
4671 then all SQL statements that run 250ms or longer will be
4672 logged. Enabling this parameter can be helpful in tracking down
4673 unoptimized queries in your applications.
4674 Only superusers can change this setting.
4678 For clients using extended query protocol, durations of the Parse,
4679 Bind, and Execute steps are logged independently.
4684 When using this option together with
4685 <xref linkend="guc-log-statement">,
4686 the text of statements that are logged because of
4687 <varname>log_statement</> will not be repeated in the
4688 duration log message.
4689 If you are not using <application>syslog</>, it is recommended
4690 that you log the PID or session ID using
4691 <xref linkend="guc-log-line-prefix">
4692 so that you can link the statement message to the later
4693 duration message using the process ID or session ID.
4702 <xref linkend="runtime-config-severity-levels"> explains the message
4703 severity levels used by <productname>PostgreSQL</>. If logging output
4704 is sent to <systemitem>syslog</systemitem> or Windows'
4705 <systemitem>eventlog</systemitem>, the severity levels are translated
4706 as shown in the table.
4709 <table id="runtime-config-severity-levels">
4710 <title>Message Severity Levels</title>
4714 <entry>Severity</entry>
4715 <entry>Usage</entry>
4716 <entry><systemitem>syslog</></entry>
4717 <entry><systemitem>eventlog</></entry>
4723 <entry><literal>DEBUG1..DEBUG5</></entry>
4724 <entry>Provides successively-more-detailed information for use by
4726 <entry><literal>DEBUG</></entry>
4727 <entry><literal>INFORMATION</></entry>
4731 <entry><literal>INFO</></entry>
4732 <entry>Provides information implicitly requested by the user,
4733 e.g., output from <command>VACUUM VERBOSE</>.</entry>
4734 <entry><literal>INFO</></entry>
4735 <entry><literal>INFORMATION</></entry>
4739 <entry><literal>NOTICE</></entry>
4740 <entry>Provides information that might be helpful to users, e.g.,
4741 notice of truncation of long identifiers.</entry>
4742 <entry><literal>NOTICE</></entry>
4743 <entry><literal>INFORMATION</></entry>
4747 <entry><literal>WARNING</></entry>
4748 <entry>Provides warnings of likely problems, e.g., <command>COMMIT</>
4749 outside a transaction block.</entry>
4750 <entry><literal>NOTICE</></entry>
4751 <entry><literal>WARNING</></entry>
4755 <entry><literal>ERROR</></entry>
4756 <entry>Reports an error that caused the current command to
4758 <entry><literal>WARNING</></entry>
4759 <entry><literal>ERROR</></entry>
4763 <entry><literal>LOG</></entry>
4764 <entry>Reports information of interest to administrators, e.g.,
4765 checkpoint activity.</entry>
4766 <entry><literal>INFO</></entry>
4767 <entry><literal>INFORMATION</></entry>
4771 <entry><literal>FATAL</></entry>
4772 <entry>Reports an error that caused the current session to
4774 <entry><literal>ERR</></entry>
4775 <entry><literal>ERROR</></entry>
4779 <entry><literal>PANIC</></entry>
4780 <entry>Reports an error that caused all database sessions to abort.</entry>
4781 <entry><literal>CRIT</></entry>
4782 <entry><literal>ERROR</></entry>
4789 <sect2 id="runtime-config-logging-what">
4790 <title>What To Log</title>
4794 <varlistentry id="guc-application-name" xreflabel="application_name">
4795 <term><varname>application_name</varname> (<type>string</type>)
4797 <primary><varname>application_name</> configuration parameter</primary>
4802 The <varname>application_name</varname> can be any string of less than
4803 <symbol>NAMEDATALEN</> characters (64 characters in a standard build).
4804 It is typically set by an application upon connection to the server.
4805 The name will be displayed in the <structname>pg_stat_activity</> view
4806 and included in CSV log entries. It can also be included in regular
4807 log entries via the <xref linkend="guc-log-line-prefix"> parameter.
4808 Only printable ASCII characters may be used in the
4809 <varname>application_name</varname> value. Other characters will be
4810 replaced with question marks (<literal>?</literal>).
4816 <term><varname>debug_print_parse</varname> (<type>boolean</type>)
4818 <primary><varname>debug_print_parse</> configuration parameter</primary>
4821 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)
4823 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
4826 <term><varname>debug_print_plan</varname> (<type>boolean</type>)
4828 <primary><varname>debug_print_plan</> configuration parameter</primary>
4833 These parameters enable various debugging output to be emitted.
4834 When set, they print the resulting parse tree, the query rewriter
4835 output, or the execution plan for each executed query.
4836 These messages are emitted at <literal>LOG</> message level, so by
4837 default they will appear in the server log but will not be sent to the
4838 client. You can change that by adjusting
4839 <xref linkend="guc-client-min-messages"> and/or
4840 <xref linkend="guc-log-min-messages">.
4841 These parameters are off by default.
4847 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)
4849 <primary><varname>debug_pretty_print</> configuration parameter</primary>
4854 When set, <varname>debug_pretty_print</varname> indents the messages
4855 produced by <varname>debug_print_parse</varname>,
4856 <varname>debug_print_rewritten</varname>, or
4857 <varname>debug_print_plan</varname>. This results in more readable
4858 but much longer output than the <quote>compact</> format used when
4859 it is off. It is on by default.
4864 <varlistentry id="guc-log-checkpoints" xreflabel="log_checkpoints">
4865 <term><varname>log_checkpoints</varname> (<type>boolean</type>)
4867 <primary><varname>log_checkpoints</> configuration parameter</primary>
4872 Causes checkpoints and restartpoints to be logged in the server log.
4873 Some statistics are included in the log messages, including the number
4874 of buffers written and the time spent writing them.
4875 This parameter can only be set in the <filename>postgresql.conf</>
4876 file or on the server command line. The default is off.
4881 <varlistentry id="guc-log-connections" xreflabel="log_connections">
4882 <term><varname>log_connections</varname> (<type>boolean</type>)
4884 <primary><varname>log_connections</> configuration parameter</primary>
4889 Causes each attempted connection to the server to be logged,
4890 as well as successful completion of client authentication.
4891 Only superusers can change this parameter at session start,
4892 and it cannot be changed at all within a session.
4893 The default is <literal>off</>.
4898 Some client programs, like <application>psql</>, attempt
4899 to connect twice while determining if a password is required, so
4900 duplicate <quote>connection received</> messages do not
4901 necessarily indicate a problem.
4907 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
4908 <term><varname>log_disconnections</varname> (<type>boolean</type>)
4910 <primary><varname>log_disconnections</> configuration parameter</primary>
4915 Causes session terminations to be logged. The log output
4916 provides information similar to <varname>log_connections</varname>,
4917 plus the duration of the session.
4918 Only superusers can change this parameter at session start,
4919 and it cannot be changed at all within a session.
4920 The default is <literal>off</>.
4926 <varlistentry id="guc-log-duration" xreflabel="log_duration">
4927 <term><varname>log_duration</varname> (<type>boolean</type>)
4929 <primary><varname>log_duration</> configuration parameter</primary>
4934 Causes the duration of every completed statement to be logged.
4935 The default is <literal>off</>.
4936 Only superusers can change this setting.
4940 For clients using extended query protocol, durations of the Parse,
4941 Bind, and Execute steps are logged independently.
4946 The difference between setting this option and setting
4947 <xref linkend="guc-log-min-duration-statement"> to zero is that
4948 exceeding <varname>log_min_duration_statement</> forces the text of
4949 the query to be logged, but this option doesn't. Thus, if
4950 <varname>log_duration</> is <literal>on</> and
4951 <varname>log_min_duration_statement</> has a positive value, all
4952 durations are logged but the query text is included only for
4953 statements exceeding the threshold. This behavior can be useful for
4954 gathering statistics in high-load installations.
4960 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
4961 <term><varname>log_error_verbosity</varname> (<type>enum</type>)
4963 <primary><varname>log_error_verbosity</> configuration parameter</primary>
4968 Controls the amount of detail written in the server log for each
4969 message that is logged. Valid values are <literal>TERSE</>,
4970 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
4971 fields to displayed messages. <literal>TERSE</> excludes
4972 the logging of <literal>DETAIL</>, <literal>HINT</>,
4973 <literal>QUERY</>, and <literal>CONTEXT</> error information.
4974 <literal>VERBOSE</> output includes the <symbol>SQLSTATE</> error
4975 code (see also <xref linkend="errcodes-appendix">) and the source code file name, function name,
4976 and line number that generated the error.
4977 Only superusers can change this setting.
4982 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
4983 <term><varname>log_hostname</varname> (<type>boolean</type>)
4985 <primary><varname>log_hostname</> configuration parameter</primary>
4990 By default, connection log messages only show the IP address of the
4991 connecting host. Turning this parameter on causes logging of the
4992 host name as well. Note that depending on your host name resolution
4993 setup this might impose a non-negligible performance penalty.
4994 This parameter can only be set in the <filename>postgresql.conf</>
4995 file or on the server command line.
5000 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
5001 <term><varname>log_line_prefix</varname> (<type>string</type>)
5003 <primary><varname>log_line_prefix</> configuration parameter</primary>
5008 This is a <function>printf</>-style string that is output at the
5009 beginning of each log line.
5010 <literal>%</> characters begin <quote>escape sequences</>
5011 that are replaced with status information as outlined below.
5012 Unrecognized escapes are ignored. Other
5013 characters are copied straight to the log line. Some escapes are
5014 only recognized by session processes, and will be treated as empty by
5015 background processes such as the main server process. Status
5016 information may be aligned either left or right by specifying a
5017 numeric literal after the % and before the option. A negative
5018 value will cause the status information to be padded on the
5019 right with spaces to give it a minimum width, whereas a positive
5020 value will pad on the left. Padding can be useful to aid human
5021 readability in log files.
5022 This parameter can only be set in the <filename>postgresql.conf</>
5023 file or on the server command line. The default is
5024 <literal>'%m [%p] '</> which logs a time stamp and the process ID.
5030 <entry>Escape</entry>
5031 <entry>Effect</entry>
5032 <entry>Session only</entry>
5037 <entry><literal>%a</literal></entry>
5038 <entry>Application name</entry>
5042 <entry><literal>%u</literal></entry>
5043 <entry>User name</entry>
5047 <entry><literal>%d</literal></entry>
5048 <entry>Database name</entry>
5052 <entry><literal>%r</literal></entry>
5053 <entry>Remote host name or IP address, and remote port</entry>
5057 <entry><literal>%h</literal></entry>
5058 <entry>Remote host name or IP address</entry>
5062 <entry><literal>%p</literal></entry>
5063 <entry>Process ID</entry>
5067 <entry><literal>%t</literal></entry>
5068 <entry>Time stamp without milliseconds</entry>
5072 <entry><literal>%m</literal></entry>
5073 <entry>Time stamp with milliseconds</entry>
5077 <entry><literal>%n</literal></entry>
5078 <entry>Time stamp with milliseconds (as a Unix epoch)</entry>
5082 <entry><literal>%i</literal></entry>
5083 <entry>Command tag: type of session's current command</entry>
5087 <entry><literal>%e</literal></entry>
5088 <entry>SQLSTATE error code</entry>
5092 <entry><literal>%c</literal></entry>
5093 <entry>Session ID: see below</entry>
5097 <entry><literal>%l</literal></entry>
5098 <entry>Number of the log line for each session or process, starting at 1</entry>
5102 <entry><literal>%s</literal></entry>
5103 <entry>Process start time stamp</entry>
5107 <entry><literal>%v</literal></entry>
5108 <entry>Virtual transaction ID (backendID/localXID)</entry>
5112 <entry><literal>%x</literal></entry>
5113 <entry>Transaction ID (0 if none is assigned)</entry>
5117 <entry><literal>%q</literal></entry>
5118 <entry>Produces no output, but tells non-session
5119 processes to stop at this point in the string; ignored by
5120 session processes</entry>
5124 <entry><literal>%%</literal></entry>
5125 <entry>Literal <literal>%</></entry>
5132 The <literal>%c</> escape prints a quasi-unique session identifier,
5133 consisting of two 4-byte hexadecimal numbers (without leading zeros)
5134 separated by a dot. The numbers are the process start time and the
5135 process ID, so <literal>%c</> can also be used as a space saving way
5136 of printing those items. For example, to generate the session
5137 identifier from <literal>pg_stat_activity</>, use this query:
5139 SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
5141 FROM pg_stat_activity;
5148 If you set a nonempty value for <varname>log_line_prefix</>,
5149 you should usually make its last character be a space, to provide
5150 visual separation from the rest of the log line. A punctuation
5151 character can be used too.
5157 <application>Syslog</> produces its own
5158 time stamp and process ID information, so you probably do not want to
5159 include those escapes if you are logging to <application>syslog</>.
5165 The <literal>%q</> escape is useful when including information that is
5166 only available in session (backend) context like user or database
5169 log_line_prefix = '%m [%p] %q%u@%d/%a '
5176 <varlistentry id="guc-log-lock-waits" xreflabel="log_lock_waits">
5177 <term><varname>log_lock_waits</varname> (<type>boolean</type>)
5179 <primary><varname>log_lock_waits</> configuration parameter</primary>
5184 Controls whether a log message is produced when a session waits
5185 longer than <xref linkend="guc-deadlock-timeout"> to acquire a
5186 lock. This is useful in determining if lock waits are causing
5187 poor performance. The default is <literal>off</>.
5192 <varlistentry id="guc-log-statement" xreflabel="log_statement">
5193 <term><varname>log_statement</varname> (<type>enum</type>)
5195 <primary><varname>log_statement</> configuration parameter</primary>
5200 Controls which SQL statements are logged. Valid values are
5201 <literal>none</> (off), <literal>ddl</>, <literal>mod</>, and
5202 <literal>all</> (all statements). <literal>ddl</> logs all data definition
5203 statements, such as <command>CREATE</>, <command>ALTER</>, and
5204 <command>DROP</> statements. <literal>mod</> logs all
5205 <literal>ddl</> statements, plus data-modifying statements
5206 such as <command>INSERT</>,
5207 <command>UPDATE</>, <command>DELETE</>, <command>TRUNCATE</>,
5208 and <command>COPY FROM</>.
5209 <command>PREPARE</>, <command>EXECUTE</>, and
5210 <command>EXPLAIN ANALYZE</> statements are also logged if their
5211 contained command is of an appropriate type. For clients using
5212 extended query protocol, logging occurs when an Execute message
5213 is received, and values of the Bind parameters are included
5214 (with any embedded single-quote marks doubled).
5218 The default is <literal>none</>. Only superusers can change this
5224 Statements that contain simple syntax errors are not logged
5225 even by the <varname>log_statement</> = <literal>all</> setting,
5226 because the log message is emitted only after basic parsing has
5227 been done to determine the statement type. In the case of extended
5228 query protocol, this setting likewise does not log statements that
5229 fail before the Execute phase (i.e., during parse analysis or
5230 planning). Set <varname>log_min_error_statement</> to
5231 <literal>ERROR</> (or lower) to log such statements.
5237 <varlistentry id="guc-log-replication-commands" xreflabel="log_replication_commands">
5238 <term><varname>log_replication_commands</varname> (<type>boolean</type>)
5240 <primary><varname>log_replication_commands</> configuration parameter</primary>
5245 Causes each replication command to be logged in the server log.
5246 See <xref linkend="protocol-replication"> for more information about
5247 replication command. The default value is <literal>off</>.
5248 Only superusers can change this setting.
5253 <varlistentry id="guc-log-temp-files" xreflabel="log_temp_files">
5254 <term><varname>log_temp_files</varname> (<type>integer</type>)
5256 <primary><varname>log_temp_files</> configuration parameter</primary>
5261 Controls logging of temporary file names and sizes.
5262 Temporary files can be
5263 created for sorts, hashes, and temporary query results.
5264 A log entry is made for each temporary file when it is deleted.
5265 A value of zero logs all temporary file information, while positive
5266 values log only files whose size is greater than or equal to
5267 the specified number of kilobytes. The
5268 default setting is -1, which disables such logging.
5269 Only superusers can change this setting.
5274 <varlistentry id="guc-log-timezone" xreflabel="log_timezone">
5275 <term><varname>log_timezone</varname> (<type>string</type>)
5277 <primary><varname>log_timezone</> configuration parameter</primary>
5282 Sets the time zone used for timestamps written in the server log.
5283 Unlike <xref linkend="guc-timezone">, this value is cluster-wide,
5284 so that all sessions will report timestamps consistently.
5285 The built-in default is <literal>GMT</>, but that is typically
5286 overridden in <filename>postgresql.conf</>; <application>initdb</>
5287 will install a setting there corresponding to its system environment.
5288 See <xref linkend="datatype-timezones"> for more information.
5289 This parameter can only be set in the <filename>postgresql.conf</>
5290 file or on the server command line.
5297 <sect2 id="runtime-config-logging-csvlog">
5298 <title>Using CSV-Format Log Output</title>
5301 Including <literal>csvlog</> in the <varname>log_destination</> list
5302 provides a convenient way to import log files into a database table.
5303 This option emits log lines in comma-separated-values
5304 (<acronym>CSV</>) format,
5306 time stamp with milliseconds,
5310 client host:port number,
5312 per-session line number,
5315 virtual transaction ID,
5316 regular transaction ID,
5320 error message detail,
5322 internal query that led to the error (if any),
5323 character count of the error position therein,
5325 user query that led to the error (if any and enabled by
5326 <varname>log_min_error_statement</>),
5327 character count of the error position therein,
5328 location of the error in the PostgreSQL source code
5329 (if <varname>log_error_verbosity</> is set to <literal>verbose</>),
5330 and application name.
5331 Here is a sample table definition for storing CSV-format log output:
5334 CREATE TABLE postgres_log
5336 log_time timestamp(3) with time zone,
5340 connection_from text,
5342 session_line_num bigint,
5344 session_start_time timestamp with time zone,
5345 virtual_transaction_id text,
5346 transaction_id bigint,
5347 error_severity text,
5348 sql_state_code text,
5352 internal_query text,
5353 internal_query_pos integer,
5358 application_name text,
5359 PRIMARY KEY (session_id, session_line_num)
5365 To import a log file into this table, use the <command>COPY FROM</>
5369 COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
5374 There are a few things you need to do to simplify importing CSV log
5380 Set <varname>log_filename</varname> and
5381 <varname>log_rotation_age</> to provide a consistent,
5382 predictable naming scheme for your log files. This lets you
5383 predict what the file name will be and know when an individual log
5384 file is complete and therefore ready to be imported.
5390 Set <varname>log_rotation_size</varname> to 0 to disable
5391 size-based log rotation, as it makes the log file name difficult
5398 Set <varname>log_truncate_on_rotation</varname> to <literal>on</> so
5399 that old log data isn't mixed with the new in the same file.
5405 The table definition above includes a primary key specification.
5406 This is useful to protect against accidentally importing the same
5407 information twice. The <command>COPY</> command commits all of the
5408 data it imports at one time, so any error will cause the entire
5409 import to fail. If you import a partial log file and later import
5410 the file again when it is complete, the primary key violation will
5411 cause the import to fail. Wait until the log is complete and
5412 closed before importing. This procedure will also protect against
5413 accidentally importing a partial line that hasn't been completely
5414 written, which would also cause <command>COPY</> to fail.
5422 <title>Process Title</title>
5425 These settings control how process titles of server processes are
5426 modified. Process titles are typically viewed using programs like
5427 <application>ps</> or, on Windows, <application>Process Explorer</>.
5428 See <xref linkend="monitoring-ps"> for details.
5432 <varlistentry id="guc-cluster-name" xreflabel="cluster_name">
5433 <term><varname>cluster_name</varname> (<type>string</type>)
5435 <primary><varname>cluster_name</> configuration parameter</primary>
5440 Sets the cluster name that appears in the process title for all
5441 server processes in this cluster. The name can be any string of less
5442 than <symbol>NAMEDATALEN</> characters (64 characters in a standard
5443 build). Only printable ASCII characters may be used in the
5444 <varname>cluster_name</varname> value. Other characters will be
5445 replaced with question marks (<literal>?</literal>). No name is shown
5446 if this parameter is set to the empty string <literal>''</> (which is
5447 the default). This parameter can only be set at server start.
5452 <varlistentry id="guc-update-process-title" xreflabel="update_process_title">
5453 <term><varname>update_process_title</varname> (<type>boolean</type>)
5455 <primary><varname>update_process_title</> configuration parameter</primary>
5460 Enables updating of the process title every time a new SQL command
5461 is received by the server.
5462 This setting defaults to <literal>on</> on most platforms, but it
5463 defaults to <literal>off</> on Windows due to that platform's larger
5464 overhead for updating the process title.
5465 Only superusers can change this setting.
5473 <sect1 id="runtime-config-statistics">
5474 <title>Run-time Statistics</title>
5476 <sect2 id="runtime-config-statistics-collector">
5477 <title>Query and Index Statistics Collector</title>
5480 These parameters control server-wide statistics collection features.
5481 When statistics collection is enabled, the data that is produced can be
5482 accessed via the <structname>pg_stat</structname> and
5483 <structname>pg_statio</structname> family of system views.
5484 Refer to <xref linkend="monitoring"> for more information.
5489 <varlistentry id="guc-track-activities" xreflabel="track_activities">
5490 <term><varname>track_activities</varname> (<type>boolean</type>)
5492 <primary><varname>track_activities</> configuration parameter</primary>
5497 Enables the collection of information on the currently
5498 executing command of each session, along with the time when
5499 that command began execution. This parameter is on by
5500 default. Note that even when enabled, this information is not
5501 visible to all users, only to superusers and the user owning
5502 the session being reported on, so it should not represent a
5504 Only superusers can change this setting.
5509 <varlistentry id="guc-track-activity-query-size" xreflabel="track_activity_query_size">
5510 <term><varname>track_activity_query_size</varname> (<type>integer</type>)
5512 <primary><varname>track_activity_query_size</> configuration parameter</primary>
5517 Specifies the number of bytes reserved to track the currently
5518 executing command for each active session, for the
5519 <structname>pg_stat_activity</>.<structfield>query</> field.
5520 The default value is 1024. This parameter can only be set at server
5526 <varlistentry id="guc-track-counts" xreflabel="track_counts">
5527 <term><varname>track_counts</varname> (<type>boolean</type>)
5529 <primary><varname>track_counts</> configuration parameter</primary>
5534 Enables collection of statistics on database activity.
5535 This parameter is on by default, because the autovacuum
5536 daemon needs the collected information.
5537 Only superusers can change this setting.
5542 <varlistentry id="guc-track-io-timing" xreflabel="track_io_timing">
5543 <term><varname>track_io_timing</varname> (<type>boolean</type>)
5545 <primary><varname>track_io_timing</> configuration parameter</primary>
5550 Enables timing of database I/O calls. This parameter is off by
5551 default, because it will repeatedly query the operating system for
5552 the current time, which may cause significant overhead on some
5553 platforms. You can use the <xref linkend="pgtesttiming"> tool to
5554 measure the overhead of timing on your system.
5555 I/O timing information is
5556 displayed in <xref linkend="pg-stat-database-view">, in the output of
5557 <xref linkend="sql-explain"> when the <literal>BUFFERS</> option is
5558 used, and by <xref linkend="pgstatstatements">. Only superusers can
5559 change this setting.
5564 <varlistentry id="guc-track-functions" xreflabel="track_functions">
5565 <term><varname>track_functions</varname> (<type>enum</type>)
5567 <primary><varname>track_functions</> configuration parameter</primary>
5572 Enables tracking of function call counts and time used. Specify
5573 <literal>pl</literal> to track only procedural-language functions,
5574 <literal>all</literal> to also track SQL and C language functions.
5575 The default is <literal>none</literal>, which disables function
5576 statistics tracking. Only superusers can change this setting.
5581 SQL-language functions that are simple enough to be <quote>inlined</>
5582 into the calling query will not be tracked, regardless of this
5589 <varlistentry id="guc-stats-temp-directory" xreflabel="stats_temp_directory">
5590 <term><varname>stats_temp_directory</varname> (<type>string</type>)
5592 <primary><varname>stats_temp_directory</> configuration parameter</primary>
5597 Sets the directory to store temporary statistics data in. This can be
5598 a path relative to the data directory or an absolute path. The default
5599 is <filename>pg_stat_tmp</filename>. Pointing this at a RAM-based
5600 file system will decrease physical I/O requirements and can lead to
5601 improved performance.
5602 This parameter can only be set in the <filename>postgresql.conf</>
5603 file or on the server command line.
5611 <sect2 id="runtime-config-statistics-monitor">
5612 <title>Statistics Monitoring</title>
5616 <term><varname>log_statement_stats</varname> (<type>boolean</type>)
5618 <primary><varname>log_statement_stats</> configuration parameter</primary>
5621 <term><varname>log_parser_stats</varname> (<type>boolean</type>)
5623 <primary><varname>log_parser_stats</> configuration parameter</primary>
5626 <term><varname>log_planner_stats</varname> (<type>boolean</type>)
5628 <primary><varname>log_planner_stats</> configuration parameter</primary>
5631 <term><varname>log_executor_stats</varname> (<type>boolean</type>)
5633 <primary><varname>log_executor_stats</> configuration parameter</primary>
5638 For each query, output performance statistics of the respective
5639 module to the server log. This is a crude profiling
5640 instrument, similar to the Unix <function>getrusage()</> operating
5641 system facility. <varname>log_statement_stats</varname> reports total
5642 statement statistics, while the others report per-module statistics.
5643 <varname>log_statement_stats</varname> cannot be enabled together with
5644 any of the per-module options. All of these options are disabled by
5645 default. Only superusers can change these settings.
5655 <sect1 id="runtime-config-autovacuum">
5656 <title>Automatic Vacuuming</title>
5659 <primary>autovacuum</primary>
5660 <secondary>configuration parameters</secondary>
5664 These settings control the behavior of the <firstterm>autovacuum</>
5665 feature. Refer to <xref linkend="autovacuum"> for more information.
5666 Note that many of these settings can be overridden on a per-table
5667 basis; see <xref linkend="sql-createtable-storage-parameters"
5668 endterm="sql-createtable-storage-parameters-title">.
5673 <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
5674 <term><varname>autovacuum</varname> (<type>boolean</type>)
5676 <primary><varname>autovacuum</> configuration parameter</primary>
5681 Controls whether the server should run the
5682 autovacuum launcher daemon. This is on by default; however,
5683 <xref linkend="guc-track-counts"> must also be enabled for
5685 This parameter can only be set in the <filename>postgresql.conf</>
5686 file or on the server command line; however, autovacuuming can be
5687 disabled for individual tables by changing table storage parameters.
5690 Note that even when this parameter is disabled, the system
5691 will launch autovacuum processes if necessary to
5692 prevent transaction ID wraparound. See <xref
5693 linkend="vacuum-for-wraparound"> for more information.
5698 <varlistentry id="guc-log-autovacuum-min-duration" xreflabel="log_autovacuum_min_duration">
5699 <term><varname>log_autovacuum_min_duration</varname> (<type>integer</type>)
5701 <primary><varname>log_autovacuum_min_duration</> configuration parameter</primary>
5706 Causes each action executed by autovacuum to be logged if it ran for at
5707 least the specified number of milliseconds. Setting this to zero logs
5708 all autovacuum actions. Minus-one (the default) disables logging
5709 autovacuum actions. For example, if you set this to
5710 <literal>250ms</literal> then all automatic vacuums and analyzes that run
5711 250ms or longer will be logged. In addition, when this parameter is
5712 set to any value other than <literal>-1</literal>, a message will be
5713 logged if an autovacuum action is skipped due to the existence of a
5714 conflicting lock. Enabling this parameter can be helpful
5715 in tracking autovacuum activity. This parameter can only be set in
5716 the <filename>postgresql.conf</> file or on the server command line;
5717 but the setting can be overridden for individual tables by
5718 changing table storage parameters.
5723 <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
5724 <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)
5726 <primary><varname>autovacuum_max_workers</> configuration parameter</primary>
5731 Specifies the maximum number of autovacuum processes (other than the
5732 autovacuum launcher) that may be running at any one time. The default
5733 is three. This parameter can only be set at server start.
5738 <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
5739 <term><varname>autovacuum_naptime</varname> (<type>integer</type>)
5741 <primary><varname>autovacuum_naptime</> configuration parameter</primary>
5746 Specifies the minimum delay between autovacuum runs on any given
5747 database. In each round the daemon examines the
5748 database and issues <command>VACUUM</> and <command>ANALYZE</> commands
5749 as needed for tables in that database. The delay is measured
5750 in seconds, and the default is one minute (<literal>1min</>).
5751 This parameter can only be set in the <filename>postgresql.conf</>
5752 file or on the server command line.
5757 <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
5758 <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)
5760 <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
5765 Specifies the minimum number of updated or deleted tuples needed
5766 to trigger a <command>VACUUM</> in any one table.
5767 The default is 50 tuples.
5768 This parameter can only be set in the <filename>postgresql.conf</>
5769 file or on the server command line;
5770 but the setting can be overridden for individual tables by
5771 changing table storage parameters.
5776 <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
5777 <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)
5779 <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
5784 Specifies the minimum number of inserted, updated or deleted tuples
5785 needed to trigger an <command>ANALYZE</> in any one table.
5786 The default is 50 tuples.
5787 This parameter can only be set in the <filename>postgresql.conf</>
5788 file or on the server command line;
5789 but the setting can be overridden for individual tables by
5790 changing table storage parameters.
5795 <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
5796 <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)
5798 <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
5803 Specifies a fraction of the table size to add to
5804 <varname>autovacuum_vacuum_threshold</varname>
5805 when deciding whether to trigger a <command>VACUUM</>.
5806 The default is 0.2 (20% of table size).
5807 This parameter can only be set in the <filename>postgresql.conf</>
5808 file or on the server command line;
5809 but the setting can be overridden for individual tables by
5810 changing table storage parameters.
5815 <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
5816 <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)
5818 <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
5823 Specifies a fraction of the table size to add to
5824 <varname>autovacuum_analyze_threshold</varname>
5825 when deciding whether to trigger an <command>ANALYZE</>.
5826 The default is 0.1 (10% of table size).
5827 This parameter can only be set in the <filename>postgresql.conf</>
5828 file or on the server command line;
5829 but the setting can be overridden for individual tables by
5830 changing table storage parameters.
5835 <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
5836 <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)
5838 <primary><varname>autovacuum_freeze_max_age</> configuration parameter</primary>
5843 Specifies the maximum age (in transactions) that a table's
5844 <structname>pg_class</>.<structfield>relfrozenxid</> field can
5845 attain before a <command>VACUUM</> operation is forced
5846 to prevent transaction ID wraparound within the table.
5847 Note that the system will launch autovacuum processes to
5848 prevent wraparound even when autovacuum is otherwise disabled.
5852 Vacuum also allows removal of old files from the
5853 <filename>pg_clog</> subdirectory, which is why the default
5854 is a relatively low 200 million transactions.
5855 This parameter can only be set at server start, but the setting
5856 can be reduced for individual tables by
5857 changing table storage parameters.
5858 For more information see <xref linkend="vacuum-for-wraparound">.
5863 <varlistentry id="guc-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
5864 <term><varname>autovacuum_multixact_freeze_max_age</varname> (<type>integer</type>)
5866 <primary><varname>autovacuum_multixact_freeze_max_age</varname> configuration parameter</primary>
5871 Specifies the maximum age (in multixacts) that a table's
5872 <structname>pg_class</>.<structfield>relminmxid</> field can
5873 attain before a <command>VACUUM</> operation is forced to
5874 prevent multixact ID wraparound within the table.
5875 Note that the system will launch autovacuum processes to
5876 prevent wraparound even when autovacuum is otherwise disabled.
5880 Vacuuming multixacts also allows removal of old files from the
5881 <filename>pg_multixact/members</> and <filename>pg_multixact/offsets</>
5882 subdirectories, which is why the default is a relatively low
5883 400 million multixacts.
5884 This parameter can only be set at server start, but the setting can
5885 be reduced for individual tables by changing table storage parameters.
5886 For more information see <xref linkend="vacuum-for-multixact-wraparound">.
5891 <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
5892 <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)
5894 <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
5899 Specifies the cost delay value that will be used in automatic
5900 <command>VACUUM</> operations. If -1 is specified, the regular
5901 <xref linkend="guc-vacuum-cost-delay"> value will be used.
5902 The default value is 20 milliseconds.
5903 This parameter can only be set in the <filename>postgresql.conf</>
5904 file or on the server command line;
5905 but the setting can be overridden for individual tables by
5906 changing table storage parameters.
5911 <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
5912 <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)
5914 <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
5919 Specifies the cost limit value that will be used in automatic
5920 <command>VACUUM</> operations. If -1 is specified (which is the
5921 default), the regular
5922 <xref linkend="guc-vacuum-cost-limit"> value will be used. Note that
5923 the value is distributed proportionally among the running autovacuum
5924 workers, if there is more than one, so that the sum of the limits for
5925 each worker does not exceed the value of this variable.
5926 This parameter can only be set in the <filename>postgresql.conf</>
5927 file or on the server command line;
5928 but the setting can be overridden for individual tables by
5929 changing table storage parameters.
5937 <sect1 id="runtime-config-client">
5938 <title>Client Connection Defaults</title>
5940 <sect2 id="runtime-config-client-statement">
5941 <title>Statement Behavior</title>
5944 <varlistentry id="guc-search-path" xreflabel="search_path">
5945 <term><varname>search_path</varname> (<type>string</type>)
5947 <primary><varname>search_path</> configuration parameter</primary>
5949 <indexterm><primary>path</><secondary>for schemas</></>
5953 This variable specifies the order in which schemas are searched
5954 when an object (table, data type, function, etc.) is referenced by a
5955 simple name with no schema specified. When there are objects of
5956 identical names in different schemas, the one found first
5957 in the search path is used. An object that is not in any of the
5958 schemas in the search path can only be referenced by specifying
5959 its containing schema with a qualified (dotted) name.
5963 The value for <varname>search_path</varname> must be a comma-separated
5964 list of schema names. Any name that is not an existing schema, or is
5965 a schema for which the user does not have <literal>USAGE</>
5966 permission, is silently ignored.
5970 If one of the list items is the special name
5971 <literal>$user</literal>, then the schema having the name returned by
5972 <function>SESSION_USER</> is substituted, if there is such a schema
5973 and the user has <literal>USAGE</> permission for it.
5974 (If not, <literal>$user</literal> is ignored.)
5978 The system catalog schema, <literal>pg_catalog</>, is always
5979 searched, whether it is mentioned in the path or not. If it is
5980 mentioned in the path then it will be searched in the specified
5981 order. If <literal>pg_catalog</> is not in the path then it will
5982 be searched <emphasis>before</> searching any of the path items.
5986 Likewise, the current session's temporary-table schema,
5987 <literal>pg_temp_<replaceable>nnn</></>, is always searched if it
5988 exists. It can be explicitly listed in the path by using the
5989 alias <literal>pg_temp</><indexterm><primary>pg_temp</></>. If it is not listed in the path then
5990 it is searched first (even before <literal>pg_catalog</>). However,
5991 the temporary schema is only searched for relation (table, view,
5992 sequence, etc) and data type names. It is never searched for
5993 function or operator names.
5997 When objects are created without specifying a particular target
5998 schema, they will be placed in the first valid schema named in
5999 <varname>search_path</varname>. An error is reported if the search
6004 The default value for this parameter is
6005 <literal>"$user", public</literal>.
6006 This setting supports shared use of a database (where no users
6007 have private schemas, and all share use of <literal>public</>),
6008 private per-user schemas, and combinations of these. Other
6009 effects can be obtained by altering the default search path
6010 setting, either globally or per-user.
6014 The current effective value of the search path can be examined
6015 via the <acronym>SQL</acronym> function
6016 <function>current_schemas</>
6017 (see <xref linkend="functions-info">).
6018 This is not quite the same as
6019 examining the value of <varname>search_path</varname>, since
6020 <function>current_schemas</> shows how the items
6021 appearing in <varname>search_path</varname> were resolved.
6025 For more information on schema handling, see <xref linkend="ddl-schemas">.
6030 <varlistentry id="guc-row-security" xreflabel="row_security">
6031 <term><varname>row_security</varname> (<type>boolean</type>)
6033 <primary><varname>row_security</> configuration parameter</primary>
6038 This variable controls whether to raise an error in lieu of applying a
6039 row security policy. When set to <literal>on</>, policies apply
6040 normally. When set to <literal>off</>, queries fail which would
6041 otherwise apply at least one policy. The default is <literal>on</>.
6042 Change to <literal>off</> where limited row visibility could cause
6043 incorrect results; for example, <application>pg_dump</> makes that
6044 change by default. This variable has no effect on roles which bypass
6045 every row security policy, to wit, superusers and roles with
6046 the <literal>BYPASSRLS</> attribute.
6050 For more information on row security policies,
6051 see <xref linkend="SQL-CREATEPOLICY">.
6056 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
6057 <term><varname>default_tablespace</varname> (<type>string</type>)
6059 <primary><varname>default_tablespace</> configuration parameter</primary>
6061 <indexterm><primary>tablespace</><secondary>default</></>
6065 This variable specifies the default tablespace in which to create
6066 objects (tables and indexes) when a <command>CREATE</> command does
6067 not explicitly specify a tablespace.
6071 The value is either the name of a tablespace, or an empty string
6072 to specify using the default tablespace of the current database.
6073 If the value does not match the name of any existing tablespace,
6074 <productname>PostgreSQL</> will automatically use the default
6075 tablespace of the current database. If a nondefault tablespace
6076 is specified, the user must have <literal>CREATE</> privilege
6077 for it, or creation attempts will fail.
6081 This variable is not used for temporary tables; for them,
6082 <xref linkend="guc-temp-tablespaces"> is consulted instead.
6086 This variable is also not used when creating databases.
6087 By default, a new database inherits its tablespace setting from
6088 the template database it is copied from.
6092 For more information on tablespaces,
6093 see <xref linkend="manage-ag-tablespaces">.
6098 <varlistentry id="guc-temp-tablespaces" xreflabel="temp_tablespaces">
6099 <term><varname>temp_tablespaces</varname> (<type>string</type>)
6101 <primary><varname>temp_tablespaces</> configuration parameter</primary>
6103 <indexterm><primary>tablespace</><secondary>temporary</></>
6107 This variable specifies tablespaces in which to create temporary
6108 objects (temp tables and indexes on temp tables) when a
6109 <command>CREATE</> command does not explicitly specify a tablespace.
6110 Temporary files for purposes such as sorting large data sets
6111 are also created in these tablespaces.
6115 The value is a list of names of tablespaces. When there is more than
6116 one name in the list, <productname>PostgreSQL</> chooses a random
6117 member of the list each time a temporary object is to be created;
6118 except that within a transaction, successively created temporary
6119 objects are placed in successive tablespaces from the list.
6120 If the selected element of the list is an empty string,
6121 <productname>PostgreSQL</> will automatically use the default
6122 tablespace of the current database instead.
6126 When <varname>temp_tablespaces</> is set interactively, specifying a
6127 nonexistent tablespace is an error, as is specifying a tablespace for
6128 which the user does not have <literal>CREATE</> privilege. However,
6129 when using a previously set value, nonexistent tablespaces are
6130 ignored, as are tablespaces for which the user lacks
6131 <literal>CREATE</> privilege. In particular, this rule applies when
6132 using a value set in <filename>postgresql.conf</>.
6136 The default value is an empty string, which results in all temporary
6137 objects being created in the default tablespace of the current
6142 See also <xref linkend="guc-default-tablespace">.
6147 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
6148 <term><varname>check_function_bodies</varname> (<type>boolean</type>)
6150 <primary><varname>check_function_bodies</> configuration parameter</primary>
6155 This parameter is normally on. When set to <literal>off</>, it
6156 disables validation of the function body string during <xref
6157 linkend="sql-createfunction">. Disabling validation avoids side
6158 effects of the validation process and avoids false positives due
6159 to problems such as forward references. Set this parameter
6160 to <literal>off</> before loading functions on behalf of other
6161 users; <application>pg_dump</> does so automatically.
6166 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
6167 <term><varname>default_transaction_isolation</varname> (<type>enum</type>)
6169 <primary>transaction isolation level</primary>
6170 <secondary>setting default</secondary>
6173 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
6178 Each SQL transaction has an isolation level, which can be
6179 either <quote>read uncommitted</quote>, <quote>read
6180 committed</quote>, <quote>repeatable read</quote>, or
6181 <quote>serializable</quote>. This parameter controls the
6182 default isolation level of each new transaction. The default
6183 is <quote>read committed</quote>.
6187 Consult <xref linkend="mvcc"> and <xref
6188 linkend="sql-set-transaction"> for more information.
6193 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
6194 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)
6196 <primary>read-only transaction</primary>
6197 <secondary>setting default</secondary>
6200 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
6205 A read-only SQL transaction cannot alter non-temporary tables.
6206 This parameter controls the default read-only status of each new
6207 transaction. The default is <literal>off</> (read/write).
6211 Consult <xref linkend="sql-set-transaction"> for more information.
6216 <varlistentry id="guc-default-transaction-deferrable" xreflabel="default_transaction_deferrable">
6217 <term><varname>default_transaction_deferrable</varname> (<type>boolean</type>)
6219 <primary>deferrable transaction</primary>
6220 <secondary>setting default</secondary>
6223 <primary><varname>default_transaction_deferrable</> configuration parameter</primary>
6228 When running at the <literal>serializable</> isolation level,
6229 a deferrable read-only SQL transaction may be delayed before
6230 it is allowed to proceed. However, once it begins executing
6231 it does not incur any of the overhead required to ensure
6232 serializability; so serialization code will have no reason to
6233 force it to abort because of concurrent updates, making this
6234 option suitable for long-running read-only transactions.
6238 This parameter controls the default deferrable status of each
6239 new transaction. It currently has no effect on read-write
6240 transactions or those operating at isolation levels lower
6241 than <literal>serializable</>. The default is <literal>off</>.
6245 Consult <xref linkend="sql-set-transaction"> for more information.
6251 <varlistentry id="guc-session-replication-role" xreflabel="session_replication_role">
6252 <term><varname>session_replication_role</varname> (<type>enum</type>)
6254 <primary><varname>session_replication_role</> configuration parameter</primary>
6259 Controls firing of replication-related triggers and rules for the
6260 current session. Setting this variable requires
6261 superuser privilege and results in discarding any previously cached
6262 query plans. Possible values are <literal>origin</> (the default),
6263 <literal>replica</> and <literal>local</>.
6264 See <xref linkend="sql-altertable"> for
6270 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
6271 <term><varname>statement_timeout</varname> (<type>integer</type>)
6273 <primary><varname>statement_timeout</> configuration parameter</primary>
6278 Abort any statement that takes more than the specified number of
6279 milliseconds, starting from the time the command arrives at the server
6280 from the client. If <varname>log_min_error_statement</> is set to
6281 <literal>ERROR</> or lower, the statement that timed out will also be
6282 logged. A value of zero (the default) turns this off.
6286 Setting <varname>statement_timeout</> in
6287 <filename>postgresql.conf</> is not recommended because it would
6288 affect all sessions.
6293 <varlistentry id="guc-lock-timeout" xreflabel="lock_timeout">
6294 <term><varname>lock_timeout</varname> (<type>integer</type>)
6296 <primary><varname>lock_timeout</> configuration parameter</primary>
6301 Abort any statement that waits longer than the specified number of
6302 milliseconds while attempting to acquire a lock on a table, index,
6303 row, or other database object. The time limit applies separately to
6304 each lock acquisition attempt. The limit applies both to explicit
6305 locking requests (such as <command>LOCK TABLE</>, or <command>SELECT
6306 FOR UPDATE</> without <literal>NOWAIT</>) and to implicitly-acquired
6307 locks. If <varname>log_min_error_statement</> is set to
6308 <literal>ERROR</> or lower, the statement that timed out will be
6309 logged. A value of zero (the default) turns this off.
6313 Unlike <varname>statement_timeout</>, this timeout can only occur
6314 while waiting for locks. Note that if <varname>statement_timeout</>
6315 is nonzero, it is rather pointless to set <varname>lock_timeout</> to
6316 the same or larger value, since the statement timeout would always
6321 Setting <varname>lock_timeout</> in
6322 <filename>postgresql.conf</> is not recommended because it would
6323 affect all sessions.
6328 <varlistentry id="guc-idle-in-transaction-session-timeout" xreflabel="idle_in_transaction_session_timeout">
6329 <term><varname>idle_in_transaction_session_timeout</varname> (<type>integer</type>)
6331 <primary><varname>idle_in_transaction_session_timeout</> configuration parameter</primary>
6336 Terminate any session with an open transaction that has been idle for
6337 longer than the specified duration in milliseconds. This allows any
6338 locks held by that session to be released and the connection slot to be reused;
6339 it also allows tuples visible only to this transaction to be vacuumed. See
6340 <xref linkend="routine-vacuuming"> for more details about this.
6343 The default value of 0 disables this feature.
6348 <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
6349 <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)
6351 <primary><varname>vacuum_freeze_table_age</> configuration parameter</primary>
6356 <command>VACUUM</> performs an aggressive scan if the table's
6357 <structname>pg_class</>.<structfield>relfrozenxid</> field has reached
6358 the age specified by this setting. An aggressive scan differs from
6359 a regular <command>VACUUM</> in that it visits every page that might
6360 contain unfrozen XIDs or MXIDs, not just those that might contain dead
6361 tuples. The default is 150 million transactions. Although users can
6362 set this value anywhere from zero to two billions, <command>VACUUM</>
6363 will silently limit the effective value to 95% of
6364 <xref linkend="guc-autovacuum-freeze-max-age">, so that a
6365 periodical manual <command>VACUUM</> has a chance to run before an
6366 anti-wraparound autovacuum is launched for the table. For more
6368 <xref linkend="vacuum-for-wraparound">.
6373 <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
6374 <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)
6376 <primary><varname>vacuum_freeze_min_age</> configuration parameter</primary>
6381 Specifies the cutoff age (in transactions) that <command>VACUUM</>
6382 should use to decide whether to freeze row versions
6383 while scanning a table.
6384 The default is 50 million transactions. Although
6385 users can set this value anywhere from zero to one billion,
6386 <command>VACUUM</> will silently limit the effective value to half
6387 the value of <xref linkend="guc-autovacuum-freeze-max-age">, so
6388 that there is not an unreasonably short time between forced
6389 autovacuums. For more information see <xref
6390 linkend="vacuum-for-wraparound">.
6395 <varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
6396 <term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)
6398 <primary><varname>vacuum_multixact_freeze_table_age</> configuration parameter</primary>
6403 <command>VACUUM</> performs an aggressive scan if the table's
6404 <structname>pg_class</>.<structfield>relminmxid</> field has reached
6405 the age specified by this setting. An aggressive scan differs from
6406 a regular <command>VACUUM</> in that it visits every page that might
6407 contain unfrozen XIDs or MXIDs, not just those that might contain dead
6408 tuples. The default is 150 million multixacts.
6409 Although users can set this value anywhere from zero to two billions,
6410 <command>VACUUM</> will silently limit the effective value to 95% of
6411 <xref linkend="guc-autovacuum-multixact-freeze-max-age">, so that a
6412 periodical manual <command>VACUUM</> has a chance to run before an
6413 anti-wraparound is launched for the table.
6414 For more information see <xref linkend="vacuum-for-multixact-wraparound">.
6419 <varlistentry id="guc-vacuum-multixact-freeze-min-age" xreflabel="vacuum_multixact_freeze_min_age">
6420 <term><varname>vacuum_multixact_freeze_min_age</varname> (<type>integer</type>)
6422 <primary><varname>vacuum_multixact_freeze_min_age</> configuration parameter</primary>
6427 Specifies the cutoff age (in multixacts) that <command>VACUUM</>
6428 should use to decide whether to replace multixact IDs with a newer
6429 transaction ID or multixact ID while scanning a table. The default
6430 is 5 million multixacts.
6431 Although users can set this value anywhere from zero to one billion,
6432 <command>VACUUM</> will silently limit the effective value to half
6433 the value of <xref linkend="guc-autovacuum-multixact-freeze-max-age">,
6434 so that there is not an unreasonably short time between forced
6436 For more information see <xref linkend="vacuum-for-multixact-wraparound">.
6441 <varlistentry id="guc-bytea-output" xreflabel="bytea_output">
6442 <term><varname>bytea_output</varname> (<type>enum</type>)
6444 <primary><varname>bytea_output</> configuration parameter</primary>
6449 Sets the output format for values of type <type>bytea</type>.
6450 Valid values are <literal>hex</literal> (the default)
6451 and <literal>escape</literal> (the traditional PostgreSQL
6452 format). See <xref linkend="datatype-binary"> for more
6453 information. The <type>bytea</type> type always
6454 accepts both formats on input, regardless of this setting.
6459 <varlistentry id="guc-xmlbinary" xreflabel="xmlbinary">
6460 <term><varname>xmlbinary</varname> (<type>enum</type>)
6462 <primary><varname>xmlbinary</> configuration parameter</primary>
6467 Sets how binary values are to be encoded in XML. This applies
6468 for example when <type>bytea</type> values are converted to
6469 XML by the functions <function>xmlelement</function> or
6470 <function>xmlforest</function>. Possible values are
6471 <literal>base64</literal> and <literal>hex</literal>, which
6472 are both defined in the XML Schema standard. The default is
6473 <literal>base64</literal>. For further information about
6474 XML-related functions, see <xref linkend="functions-xml">.
6478 The actual choice here is mostly a matter of taste,
6479 constrained only by possible restrictions in client
6480 applications. Both methods support all possible values,
6481 although the hex encoding will be somewhat larger than the
6487 <varlistentry id="guc-xmloption" xreflabel="xmloption">
6488 <term><varname>xmloption</varname> (<type>enum</type>)
6490 <primary><varname>xmloption</> configuration parameter</primary>
6493 <primary><varname>SET XML OPTION</></primary>
6496 <primary>XML option</primary>
6501 Sets whether <literal>DOCUMENT</literal> or
6502 <literal>CONTENT</literal> is implicit when converting between
6503 XML and character string values. See <xref
6504 linkend="datatype-xml"> for a description of this. Valid
6505 values are <literal>DOCUMENT</literal> and
6506 <literal>CONTENT</literal>. The default is
6507 <literal>CONTENT</literal>.
6511 According to the SQL standard, the command to set this option is
6513 SET XML OPTION { DOCUMENT | CONTENT };
6515 This syntax is also available in PostgreSQL.
6520 <varlistentry id="guc-gin-pending-list-limit" xreflabel="gin_pending_list_limit">
6521 <term><varname>gin_pending_list_limit</varname> (<type>integer</type>)
6523 <primary><varname>gin_pending_list_limit</> configuration parameter</primary>
6528 Sets the maximum size of the GIN pending list which is used
6529 when <literal>fastupdate</> is enabled. If the list grows
6530 larger than this maximum size, it is cleaned up by moving
6531 the entries in it to the main GIN data structure in bulk.
6532 The default is four megabytes (<literal>4MB</>). This setting
6533 can be overridden for individual GIN indexes by changing
6534 index storage parameters.
6535 See <xref linkend="gin-fast-update"> and <xref linkend="gin-tips">
6536 for more information.
6543 <sect2 id="runtime-config-client-format">
6544 <title>Locale and Formatting</title>
6548 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
6549 <term><varname>DateStyle</varname> (<type>string</type>)
6551 <primary><varname>DateStyle</> configuration parameter</primary>
6556 Sets the display format for date and time values, as well as the
6557 rules for interpreting ambiguous date input values. For
6558 historical reasons, this variable contains two independent
6559 components: the output format specification (<literal>ISO</>,
6560 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
6561 and the input/output specification for year/month/day ordering
6562 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
6563 can be set separately or together. The keywords <literal>Euro</>
6564 and <literal>European</> are synonyms for <literal>DMY</>; the
6565 keywords <literal>US</>, <literal>NonEuro</>, and
6566 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
6567 <xref linkend="datatype-datetime"> for more information. The
6568 built-in default is <literal>ISO, MDY</>, but
6569 <application>initdb</application> will initialize the
6570 configuration file with a setting that corresponds to the
6571 behavior of the chosen <varname>lc_time</varname> locale.
6576 <varlistentry id="guc-intervalstyle" xreflabel="IntervalStyle">
6577 <term><varname>IntervalStyle</varname> (<type>enum</type>)
6579 <primary><varname>IntervalStyle</> configuration parameter</primary>
6584 Sets the display format for interval values.
6585 The value <literal>sql_standard</> will produce
6586 output matching <acronym>SQL</acronym> standard interval literals.
6587 The value <literal>postgres</> (which is the default) will produce
6588 output matching <productname>PostgreSQL</> releases prior to 8.4
6589 when the <xref linkend="guc-datestyle">
6590 parameter was set to <literal>ISO</>.
6591 The value <literal>postgres_verbose</> will produce output
6592 matching <productname>PostgreSQL</> releases prior to 8.4
6593 when the <varname>DateStyle</>
6594 parameter was set to non-<literal>ISO</> output.
6595 The value <literal>iso_8601</> will produce output matching the time
6596 interval <quote>format with designators</> defined in section
6597 4.4.3.2 of ISO 8601.
6600 The <varname>IntervalStyle</> parameter also affects the
6601 interpretation of ambiguous interval input. See
6602 <xref linkend="datatype-interval-input"> for more information.
6607 <varlistentry id="guc-timezone" xreflabel="TimeZone">
6608 <term><varname>TimeZone</varname> (<type>string</type>)
6610 <primary><varname>TimeZone</> configuration parameter</primary>
6612 <indexterm><primary>time zone</></>
6616 Sets the time zone for displaying and interpreting time stamps.
6617 The built-in default is <literal>GMT</>, but that is typically
6618 overridden in <filename>postgresql.conf</>; <application>initdb</>
6619 will install a setting there corresponding to its system environment.
6620 See <xref linkend="datatype-timezones"> for more information.
6625 <varlistentry id="guc-timezone-abbreviations" xreflabel="timezone_abbreviations">
6626 <term><varname>timezone_abbreviations</varname> (<type>string</type>)
6628 <primary><varname>timezone_abbreviations</> configuration parameter</primary>
6630 <indexterm><primary>time zone names</></>
6634 Sets the collection of time zone abbreviations that will be accepted
6635 by the server for datetime input. The default is <literal>'Default'</>,
6636 which is a collection that works in most of the world; there are
6637 also <literal>'Australia'</literal> and <literal>'India'</literal>,
6638 and other collections can be defined for a particular installation.
6639 See <xref linkend="datetime-config-files"> for more information.
6644 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
6645 <term><varname>extra_float_digits</varname> (<type>integer</type>)
6647 <primary>significant digits</primary>
6650 <primary>floating-point</primary>
6651 <secondary>display</secondary>
6654 <primary><varname>extra_float_digits</> configuration parameter</primary>
6659 This parameter adjusts the number of digits displayed for
6660 floating-point values, including <type>float4</>, <type>float8</>,
6661 and geometric data types. The parameter value is added to the
6662 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
6663 as appropriate). The value can be set as high as 3, to include
6664 partially-significant digits; this is especially useful for dumping
6665 float data that needs to be restored exactly. Or it can be set
6666 negative to suppress unwanted digits.
6667 See also <xref linkend="datatype-float">.
6672 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
6673 <term><varname>client_encoding</varname> (<type>string</type>)
6675 <primary><varname>client_encoding</> configuration parameter</primary>
6677 <indexterm><primary>character set</></>
6681 Sets the client-side encoding (character set).
6682 The default is to use the database encoding.
6683 The character sets supported by the <productname>PostgreSQL</productname>
6684 server are described in <xref linkend="multibyte-charset-supported">.
6689 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
6690 <term><varname>lc_messages</varname> (<type>string</type>)
6692 <primary><varname>lc_messages</> configuration parameter</primary>
6697 Sets the language in which messages are displayed. Acceptable
6698 values are system-dependent; see <xref linkend="locale"> for
6699 more information. If this variable is set to the empty string
6700 (which is the default) then the value is inherited from the
6701 execution environment of the server in a system-dependent way.
6705 On some systems, this locale category does not exist. Setting
6706 this variable will still work, but there will be no effect.
6707 Also, there is a chance that no translated messages for the
6708 desired language exist. In that case you will continue to see
6709 the English messages.
6713 Only superusers can change this setting, because it affects the
6714 messages sent to the server log as well as to the client, and
6715 an improper value might obscure the readability of the server
6721 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
6722 <term><varname>lc_monetary</varname> (<type>string</type>)
6724 <primary><varname>lc_monetary</> configuration parameter</primary>
6729 Sets the locale to use for formatting monetary amounts, for
6730 example with the <function>to_char</function> family of
6731 functions. Acceptable values are system-dependent; see <xref
6732 linkend="locale"> for more information. If this variable is
6733 set to the empty string (which is the default) then the value
6734 is inherited from the execution environment of the server in a
6735 system-dependent way.
6740 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
6741 <term><varname>lc_numeric</varname> (<type>string</type>)
6743 <primary><varname>lc_numeric</> configuration parameter</primary>
6748 Sets the locale to use for formatting numbers, for example
6749 with the <function>to_char</function> family of
6750 functions. Acceptable values are system-dependent; see <xref
6751 linkend="locale"> for more information. If this variable is
6752 set to the empty string (which is the default) then the value
6753 is inherited from the execution environment of the server in a
6754 system-dependent way.
6759 <varlistentry id="guc-lc-time" xreflabel="lc_time">
6760 <term><varname>lc_time</varname> (<type>string</type>)
6762 <primary><varname>lc_time</> configuration parameter</primary>
6767 Sets the locale to use for formatting dates and times, for example
6768 with the <function>to_char</function> family of
6769 functions. Acceptable values are system-dependent; see <xref
6770 linkend="locale"> for more information. If this variable is
6771 set to the empty string (which is the default) then the value
6772 is inherited from the execution environment of the server in a
6773 system-dependent way.
6778 <varlistentry id="guc-default-text-search-config" xreflabel="default_text_search_config">
6779 <term><varname>default_text_search_config</varname> (<type>string</type>)
6781 <primary><varname>default_text_search_config</> configuration parameter</primary>
6786 Selects the text search configuration that is used by those variants
6787 of the text search functions that do not have an explicit argument
6788 specifying the configuration.
6789 See <xref linkend="textsearch"> for further information.
6790 The built-in default is <literal>pg_catalog.simple</>, but
6791 <application>initdb</application> will initialize the
6792 configuration file with a setting that corresponds to the
6793 chosen <varname>lc_ctype</varname> locale, if a configuration
6794 matching that locale can be identified.
6803 <sect2 id="runtime-config-client-preload">
6804 <title>Shared Library Preloading</title>
6807 Several settings are available for preloading shared libraries into the
6808 server, in order to load additional functionality or achieve performance
6809 benefits. For example, a setting of
6810 <literal>'$libdir/mylib'</literal> would cause
6811 <literal>mylib.so</> (or on some platforms,
6812 <literal>mylib.sl</>) to be preloaded from the installation's standard
6813 library directory. The differences between the settings are when they
6814 take effect and what privileges are required to change them.
6818 <productname>PostgreSQL</productname> procedural language libraries can
6819 be preloaded in this way, typically by using the
6820 syntax <literal>'$libdir/plXXX'</literal> where
6821 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
6822 <literal>tcl</>, or <literal>python</>.
6826 For each parameter, if more than one library is to be loaded, separate
6827 their names with commas. All library names are converted to lower case
6828 unless double-quoted.
6832 Only shared libraries specifically intended to be used with PostgreSQL
6833 can be loaded this way. Every PostgreSQL-supported library has
6834 a <quote>magic block</> that is checked to guarantee compatibility. For
6835 this reason, non-PostgreSQL libraries cannot be loaded in this way. You
6836 might be able to use operating-system facilities such
6837 as <envar>LD_PRELOAD</envar> for that.
6841 In general, refer to the documentation of a specific module for the
6842 recommended way to load that module.
6846 <varlistentry id="guc-local-preload-libraries" xreflabel="local_preload_libraries">
6847 <term><varname>local_preload_libraries</varname> (<type>string</type>)
6849 <primary><varname>local_preload_libraries</> configuration parameter</primary>
6852 <primary><filename>$libdir/plugins</></primary>
6857 This variable specifies one or more shared libraries that are to be
6858 preloaded at connection start.
6859 The parameter value only takes effect at the start of the connection.
6860 Subsequent changes have no effect. If a specified library is not
6861 found, the connection attempt will fail.
6865 This option can be set by any user. Because of that, the libraries
6866 that can be loaded are restricted to those appearing in the
6867 <filename>plugins</> subdirectory of the installation's
6868 standard library directory. (It is the database administrator's
6869 responsibility to ensure that only <quote>safe</> libraries
6870 are installed there.) Entries in <varname>local_preload_libraries</>
6871 can specify this directory explicitly, for example
6872 <literal>$libdir/plugins/mylib</literal>, or just specify
6873 the library name — <literal>mylib</literal> would have
6874 the same effect as <literal>$libdir/plugins/mylib</literal>.
6878 The intent of this feature is to allow unprivileged users to load
6879 debugging or performance-measurement libraries into specific sessions
6880 without requiring an explicit <command>LOAD</> command. To that end,
6881 it would be typical to set this parameter using
6882 the <envar>PGOPTIONS</envar> environment variable on the client or by
6884 <command>ALTER ROLE SET</>.
6888 However, unless a module is specifically designed to be used in this way by
6889 non-superusers, this is usually not the right setting to use. Look
6890 at <xref linkend="guc-session-preload-libraries"> instead.
6896 <varlistentry id="guc-session-preload-libraries" xreflabel="session_preload_libraries">
6897 <term><varname>session_preload_libraries</varname> (<type>string</type>)
6899 <primary><varname>session_preload_libraries</> configuration parameter</primary>
6904 This variable specifies one or more shared libraries that are to be
6905 preloaded at connection start. Only superusers can change this setting.
6906 The parameter value only takes effect at the start of the connection.
6907 Subsequent changes have no effect. If a specified library is not
6908 found, the connection attempt will fail.
6912 The intent of this feature is to allow debugging or
6913 performance-measurement libraries to be loaded into specific sessions
6915 <command>LOAD</> command being given. For
6916 example, <xref linkend="auto-explain"> could be enabled for all
6917 sessions under a given user name by setting this parameter
6918 with <command>ALTER ROLE SET</>. Also, this parameter can be changed
6919 without restarting the server (but changes only take effect when a new
6920 session is started), so it is easier to add new modules this way, even
6921 if they should apply to all sessions.
6925 Unlike <xref linkend="guc-shared-preload-libraries">, there is no large
6926 performance advantage to loading a library at session start rather than
6927 when it is first used. There is some advantage, however, when
6928 connection pooling is used.
6933 <varlistentry id="guc-shared-preload-libraries" xreflabel="shared_preload_libraries">
6934 <term><varname>shared_preload_libraries</varname> (<type>string</type>)
6936 <primary><varname>shared_preload_libraries</> configuration parameter</primary>
6941 This variable specifies one or more shared libraries to be preloaded at
6942 server start. This parameter can only be set at server
6943 start. If a specified library is not found, the server will fail to
6948 Some libraries need to perform certain operations that can only take
6949 place at postmaster start, such as allocating shared memory, reserving
6950 light-weight locks, or starting background workers. Those libraries
6951 must be loaded at server start through this parameter. See the
6952 documentation of each library for details.
6956 Other libraries can also be preloaded. By preloading a shared library,
6957 the library startup time is avoided when the library is first used.
6958 However, the time to start each new server process might increase
6959 slightly, even if that process never uses the library. So this
6960 parameter is recommended only for libraries that will be used in most
6961 sessions. Also, changing this parameter requires a server restart, so
6962 this is not the right setting to use for short-term debugging tasks,
6963 say. Use <xref linkend="guc-session-preload-libraries"> for that
6969 On Windows hosts, preloading a library at server start will not reduce
6970 the time required to start each new server process; each server process
6971 will re-load all preload libraries. However, <varname>shared_preload_libraries
6972 </varname> is still useful on Windows hosts for libraries that need to
6973 perform operations at postmaster start time.
6981 <sect2 id="runtime-config-client-other">
6982 <title>Other Defaults</title>
6986 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
6987 <term><varname>dynamic_library_path</varname> (<type>string</type>)
6989 <primary><varname>dynamic_library_path</> configuration parameter</primary>
6991 <indexterm><primary>dynamic loading</></>
6995 If a dynamically loadable module needs to be opened and the
6996 file name specified in the <command>CREATE FUNCTION</command> or
6997 <command>LOAD</command> command
6998 does not have a directory component (i.e., the
6999 name does not contain a slash), the system will search this
7000 path for the required file.
7004 The value for <varname>dynamic_library_path</varname> must be a
7005 list of absolute directory paths separated by colons (or semi-colons
7006 on Windows). If a list element starts
7007 with the special string <literal>$libdir</literal>, the
7008 compiled-in <productname>PostgreSQL</productname> package
7009 library directory is substituted for <literal>$libdir</literal>; this
7010 is where the modules provided by the standard
7011 <productname>PostgreSQL</productname> distribution are installed.
7012 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
7013 this directory.) For example:
7015 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
7017 or, in a Windows environment:
7019 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
7024 The default value for this parameter is
7025 <literal>'$libdir'</literal>. If the value is set to an empty
7026 string, the automatic path search is turned off.
7030 This parameter can be changed at run time by superusers, but a
7031 setting done that way will only persist until the end of the
7032 client connection, so this method should be reserved for
7033 development purposes. The recommended way to set this parameter
7034 is in the <filename>postgresql.conf</filename> configuration
7040 <varlistentry id="guc-gin-fuzzy-search-limit" xreflabel="gin_fuzzy_search_limit">
7041 <term><varname>gin_fuzzy_search_limit</varname> (<type>integer</type>)
7043 <primary><varname>gin_fuzzy_search_limit</> configuration parameter</primary>
7048 Soft upper limit of the size of the set returned by GIN index scans. For more
7049 information see <xref linkend="gin-tips">.
7058 <sect1 id="runtime-config-locks">
7059 <title>Lock Management</title>
7063 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
7064 <term><varname>deadlock_timeout</varname> (<type>integer</type>)
7066 <primary>deadlock</primary>
7067 <secondary>timeout during</secondary>
7070 <primary>timeout</primary>
7071 <secondary>deadlock</secondary>
7074 <primary><varname>deadlock_timeout</> configuration parameter</primary>
7079 This is the amount of time, in milliseconds, to wait on a lock
7080 before checking to see if there is a deadlock condition. The
7081 check for deadlock is relatively expensive, so the server doesn't run
7082 it every time it waits for a lock. We optimistically assume
7083 that deadlocks are not common in production applications and
7084 just wait on the lock for a while before checking for a
7085 deadlock. Increasing this value reduces the amount of time
7086 wasted in needless deadlock checks, but slows down reporting of
7087 real deadlock errors. The default is one second (<literal>1s</>),
7088 which is probably about the smallest value you would want in
7089 practice. On a heavily loaded server you might want to raise it.
7090 Ideally the setting should exceed your typical transaction time,
7091 so as to improve the odds that a lock will be released before
7092 the waiter decides to check for deadlock. Only superusers can change
7097 When <xref linkend="guc-log-lock-waits"> is set,
7098 this parameter also determines the length of time to wait before
7099 a log message is issued about the lock wait. If you are trying
7100 to investigate locking delays you might want to set a shorter than
7101 normal <varname>deadlock_timeout</varname>.
7106 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
7107 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)
7109 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
7114 The shared lock table tracks locks on
7115 <varname>max_locks_per_transaction</varname> * (<xref
7116 linkend="guc-max-connections"> + <xref
7117 linkend="guc-max-prepared-transactions">) objects (e.g., tables);
7118 hence, no more than this many distinct objects can be locked at
7119 any one time. This parameter controls the average number of object
7120 locks allocated for each transaction; individual transactions
7121 can lock more objects as long as the locks of all transactions
7122 fit in the lock table. This is <emphasis>not</> the number of
7123 rows that can be locked; that value is unlimited. The default,
7124 64, has historically proven sufficient, but you might need to
7125 raise this value if you have queries that touch many different
7126 tables in a single transaction, e.g. query of a parent table with
7127 many children. This parameter can only be set at server start.
7131 When running a standby server, you must set this parameter to the
7132 same or higher value than on the master server. Otherwise, queries
7133 will not be allowed in the standby server.
7138 <varlistentry id="guc-max-pred-locks-per-transaction" xreflabel="max_pred_locks_per_transaction">
7139 <term><varname>max_pred_locks_per_transaction</varname> (<type>integer</type>)
7141 <primary><varname>max_pred_locks_per_transaction</> configuration parameter</primary>
7146 The shared predicate lock table tracks locks on
7147 <varname>max_pred_locks_per_transaction</varname> * (<xref
7148 linkend="guc-max-connections"> + <xref
7149 linkend="guc-max-prepared-transactions">) objects (e.g., tables);
7150 hence, no more than this many distinct objects can be locked at
7151 any one time. This parameter controls the average number of object
7152 locks allocated for each transaction; individual transactions
7153 can lock more objects as long as the locks of all transactions
7154 fit in the lock table. This is <emphasis>not</> the number of
7155 rows that can be locked; that value is unlimited. The default,
7156 64, has generally been sufficient in testing, but you might need to
7157 raise this value if you have clients that touch many different
7158 tables in a single serializable transaction. This parameter can
7159 only be set at server start.
7168 <sect1 id="runtime-config-compatible">
7169 <title>Version and Platform Compatibility</title>
7171 <sect2 id="runtime-config-compatible-version">
7172 <title>Previous PostgreSQL Versions</title>
7176 <varlistentry id="guc-array-nulls" xreflabel="array_nulls">
7177 <term><varname>array_nulls</varname> (<type>boolean</type>)
7179 <primary><varname>array_nulls</> configuration parameter</primary>
7184 This controls whether the array input parser recognizes
7185 unquoted <literal>NULL</> as specifying a null array element.
7186 By default, this is <literal>on</>, allowing array values containing
7187 null values to be entered. However, <productname>PostgreSQL</> versions
7188 before 8.2 did not support null values in arrays, and therefore would
7189 treat <literal>NULL</> as specifying a normal array element with
7190 the string value <quote>NULL</>. For backward compatibility with
7191 applications that require the old behavior, this variable can be
7192 turned <literal>off</>.
7196 Note that it is possible to create array values containing null values
7197 even when this variable is <literal>off</>.
7202 <varlistentry id="guc-backslash-quote" xreflabel="backslash_quote">
7203 <term><varname>backslash_quote</varname> (<type>enum</type>)
7204 <indexterm><primary>strings</><secondary>backslash quotes</></>
7206 <primary><varname>backslash_quote</> configuration parameter</primary>
7211 This controls whether a quote mark can be represented by
7212 <literal>\'</> in a string literal. The preferred, SQL-standard way
7213 to represent a quote mark is by doubling it (<literal>''</>) but
7214 <productname>PostgreSQL</> has historically also accepted
7215 <literal>\'</>. However, use of <literal>\'</> creates security risks
7216 because in some client character set encodings, there are multibyte
7217 characters in which the last byte is numerically equivalent to ASCII
7218 <literal>\</>. If client-side code does escaping incorrectly then a
7219 SQL-injection attack is possible. This risk can be prevented by
7220 making the server reject queries in which a quote mark appears to be
7221 escaped by a backslash.
7222 The allowed values of <varname>backslash_quote</> are
7223 <literal>on</> (allow <literal>\'</> always),
7224 <literal>off</> (reject always), and
7225 <literal>safe_encoding</> (allow only if client encoding does not
7226 allow ASCII <literal>\</> within a multibyte character).
7227 <literal>safe_encoding</> is the default setting.
7231 Note that in a standard-conforming string literal, <literal>\</> just
7232 means <literal>\</> anyway. This parameter only affects the handling of
7233 non-standard-conforming literals, including
7234 escape string syntax (<literal>E'...'</>).
7239 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
7240 <term><varname>default_with_oids</varname> (<type>boolean</type>)
7242 <primary><varname>default_with_oids</> configuration parameter</primary>
7247 This controls whether <command>CREATE TABLE</command> and
7248 <command>CREATE TABLE AS</command> include an OID column in
7249 newly-created tables, if neither <literal>WITH OIDS</literal>
7250 nor <literal>WITHOUT OIDS</literal> is specified. It also
7251 determines whether OIDs will be included in tables created by
7252 <command>SELECT INTO</command>. The parameter is <literal>off</>
7253 by default; in <productname>PostgreSQL</> 8.0 and earlier, it
7254 was <literal>on</> by default.
7258 The use of OIDs in user tables is considered deprecated, so
7259 most installations should leave this variable disabled.
7260 Applications that require OIDs for a particular table should
7261 specify <literal>WITH OIDS</literal> when creating the
7262 table. This variable can be enabled for compatibility with old
7263 applications that do not follow this behavior.
7268 <varlistentry id="guc-escape-string-warning" xreflabel="escape_string_warning">
7269 <term><varname>escape_string_warning</varname> (<type>boolean</type>)
7270 <indexterm><primary>strings</><secondary>escape warning</></>
7272 <primary><varname>escape_string_warning</> configuration parameter</primary>
7277 When on, a warning is issued if a backslash (<literal>\</>)
7278 appears in an ordinary string literal (<literal>'...'</>
7279 syntax) and <varname>standard_conforming_strings</varname> is off.
7280 The default is <literal>on</>.
7283 Applications that wish to use backslash as escape should be
7284 modified to use escape string syntax (<literal>E'...'</>),
7285 because the default behavior of ordinary strings is now to treat
7286 backslash as an ordinary character, per SQL standard. This variable
7287 can be enabled to help locate code that needs to be changed.
7292 <varlistentry id="guc-lo-compat-privileges" xreflabel="lo_compat_privileges">
7293 <term><varname>lo_compat_privileges</varname> (<type>boolean</type>)
7295 <primary><varname>lo_compat_privileges</> configuration parameter</primary>
7300 In <productname>PostgreSQL</> releases prior to 9.0, large objects
7301 did not have access privileges and were, therefore, always readable
7302 and writable by all users. Setting this variable to <literal>on</>
7303 disables the new privilege checks, for compatibility with prior
7304 releases. The default is <literal>off</>.
7305 Only superusers can change this setting.
7308 Setting this variable does not disable all security checks related to
7309 large objects — only those for which the default behavior has
7310 changed in <productname>PostgreSQL</> 9.0.
7311 For example, <literal>lo_import()</literal> and
7312 <literal>lo_export()</literal> need superuser privileges regardless
7318 <varlistentry id="guc-operator-precedence-warning" xreflabel="operator_precedence_warning">
7319 <term><varname>operator_precedence_warning</varname> (<type>boolean</type>)
7321 <primary><varname>operator_precedence_warning</> configuration parameter</primary>
7326 When on, the parser will emit a warning for any construct that might
7327 have changed meanings since <productname>PostgreSQL</> 9.4 as a result
7328 of changes in operator precedence. This is useful for auditing
7329 applications to see if precedence changes have broken anything; but it
7330 is not meant to be kept turned on in production, since it will warn
7331 about some perfectly valid, standard-compliant SQL code.
7332 The default is <literal>off</>.
7336 See <xref linkend="sql-precedence"> for more information.
7341 <varlistentry id="guc-quote-all-identifiers" xreflabel="quote-all-identifiers">
7342 <term><varname>quote_all_identifiers</varname> (<type>boolean</type>)
7344 <primary><varname>quote_all_identifiers</> configuration parameter</primary>
7349 When the database generates SQL, force all identifiers to be quoted,
7350 even if they are not (currently) keywords. This will affect the
7351 output of <command>EXPLAIN</> as well as the results of functions
7352 like <function>pg_get_viewdef</>. See also the
7353 <option>--quote-all-identifiers</option> option of
7354 <xref linkend="app-pgdump"> and <xref linkend="app-pg-dumpall">.
7359 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
7360 <term><varname>sql_inheritance</varname> (<type>boolean</type>)
7362 <primary><varname>sql_inheritance</> configuration parameter</primary>
7364 <indexterm><primary>inheritance</></>
7368 This setting controls whether undecorated table references are
7369 considered to include inheritance child tables. The default is
7370 <literal>on</>, which means child tables are included (thus,
7371 a <literal>*</> suffix is assumed by default). If turned
7372 <literal>off</>, child tables are not included (thus, an
7373 <literal>ONLY</literal> prefix is assumed). The SQL standard
7374 requires child tables to be included, so the <literal>off</> setting
7375 is not spec-compliant, but it is provided for compatibility with
7376 <productname>PostgreSQL</> releases prior to 7.1.
7377 See <xref linkend="ddl-inherit"> for more information.
7381 Turning <varname>sql_inheritance</> off is deprecated, because that
7382 behavior has been found to be error-prone as well as contrary to SQL
7383 standard. Discussions of inheritance behavior elsewhere in this
7384 manual generally assume that it is <literal>on</>.
7389 <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
7390 <term><varname>standard_conforming_strings</varname> (<type>boolean</type>)
7391 <indexterm><primary>strings</><secondary>standard conforming</></>
7393 <primary><varname>standard_conforming_strings</> configuration parameter</primary>
7398 This controls whether ordinary string literals
7399 (<literal>'...'</>) treat backslashes literally, as specified in
7401 Beginning in <productname>PostgreSQL</productname> 9.1, the default is
7402 <literal>on</> (prior releases defaulted to <literal>off</>).
7403 Applications can check this
7404 parameter to determine how string literals will be processed.
7405 The presence of this parameter can also be taken as an indication
7406 that the escape string syntax (<literal>E'...'</>) is supported.
7407 Escape string syntax (<xref linkend="sql-syntax-strings-escape">)
7408 should be used if an application desires
7409 backslashes to be treated as escape characters.
7414 <varlistentry id="guc-synchronize-seqscans" xreflabel="synchronize_seqscans">
7415 <term><varname>synchronize_seqscans</varname> (<type>boolean</type>)
7417 <primary><varname>synchronize_seqscans</> configuration parameter</primary>
7422 This allows sequential scans of large tables to synchronize with each
7423 other, so that concurrent scans read the same block at about the
7424 same time and hence share the I/O workload. When this is enabled,
7425 a scan might start in the middle of the table and then <quote>wrap
7426 around</> the end to cover all rows, so as to synchronize with the
7427 activity of scans already in progress. This can result in
7428 unpredictable changes in the row ordering returned by queries that
7429 have no <literal>ORDER BY</> clause. Setting this parameter to
7430 <literal>off</> ensures the pre-8.3 behavior in which a sequential
7431 scan always starts from the beginning of the table. The default
7440 <sect2 id="runtime-config-compatible-clients">
7441 <title>Platform and Client Compatibility</title>
7444 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
7445 <term><varname>transform_null_equals</varname> (<type>boolean</type>)
7446 <indexterm><primary>IS NULL</></>
7448 <primary><varname>transform_null_equals</> configuration parameter</primary>
7453 When on, expressions of the form <literal><replaceable>expr</> =
7454 NULL</literal> (or <literal>NULL =
7455 <replaceable>expr</></literal>) are treated as
7456 <literal><replaceable>expr</> IS NULL</literal>, that is, they
7457 return true if <replaceable>expr</> evaluates to the null value,
7458 and false otherwise. The correct SQL-spec-compliant behavior of
7459 <literal><replaceable>expr</> = NULL</literal> is to always
7460 return null (unknown). Therefore this parameter defaults to
7465 However, filtered forms in <productname>Microsoft
7466 Access</productname> generate queries that appear to use
7467 <literal><replaceable>expr</> = NULL</literal> to test for
7468 null values, so if you use that interface to access the database you
7469 might want to turn this option on. Since expressions of the
7470 form <literal><replaceable>expr</> = NULL</literal> always
7471 return the null value (using the SQL standard interpretation), they are not
7472 very useful and do not appear often in normal applications so
7473 this option does little harm in practice. But new users are
7474 frequently confused about the semantics of expressions
7475 involving null values, so this option is off by default.
7479 Note that this option only affects the exact form <literal>= NULL</>,
7480 not other comparison operators or other expressions
7481 that are computationally equivalent to some expression
7482 involving the equals operator (such as <literal>IN</literal>).
7483 Thus, this option is not a general fix for bad programming.
7487 Refer to <xref linkend="functions-comparison"> for related information.
7496 <sect1 id="runtime-config-error-handling">
7497 <title>Error Handling</title>
7501 <varlistentry id="guc-exit-on-error" xreflabel="exit_on_error">
7502 <term><varname>exit_on_error</varname> (<type>boolean</type>)
7504 <primary><varname>exit_on_error</> configuration parameter</primary>
7509 If true, any error will terminate the current session. By default,
7510 this is set to false, so that only FATAL errors will terminate the
7516 <varlistentry id="guc-restart-after-crash" xreflabel="restart_after_crash">
7517 <term><varname>restart_after_crash</varname> (<type>boolean</type>)
7519 <primary><varname>restart_after_crash</> configuration parameter</primary>
7524 When set to true, which is the default, <productname>PostgreSQL</>
7525 will automatically reinitialize after a backend crash. Leaving this
7526 value set to true is normally the best way to maximize the availability
7527 of the database. However, in some circumstances, such as when
7528 <productname>PostgreSQL</> is being invoked by clusterware, it may be
7529 useful to disable the restart so that the clusterware can gain
7530 control and take any actions it deems appropriate.
7539 <sect1 id="runtime-config-preset">
7540 <title>Preset Options</title>
7543 The following <quote>parameters</> are read-only, and are determined
7544 when <productname>PostgreSQL</productname> is compiled or when it is
7545 installed. As such, they have been excluded from the sample
7546 <filename>postgresql.conf</> file. These options report
7547 various aspects of <productname>PostgreSQL</productname> behavior
7548 that might be of interest to certain applications, particularly
7549 administrative front-ends.
7554 <varlistentry id="guc-block-size" xreflabel="block_size">
7555 <term><varname>block_size</varname> (<type>integer</type>)
7557 <primary><varname>block_size</> configuration parameter</primary>
7562 Reports the size of a disk block. It is determined by the value
7563 of <literal>BLCKSZ</> when building the server. The default
7564 value is 8192 bytes. The meaning of some configuration
7565 variables (such as <xref linkend="guc-shared-buffers">) is
7566 influenced by <varname>block_size</varname>. See <xref
7567 linkend="runtime-config-resource"> for information.
7572 <varlistentry id="guc-data-checksums" xreflabel="data_checksums">
7573 <term><varname>data_checksums</varname> (<type>boolean</type>)
7575 <primary><varname>data_checksums</> configuration parameter</primary>
7580 Reports whether data checksums are enabled for this cluster.
7581 See <xref linkend="app-initdb-data-checksums"> for more information.
7586 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
7587 <term><varname>debug_assertions</varname> (<type>boolean</type>)
7589 <primary><varname>debug_assertions</> configuration parameter</primary>
7594 Reports whether <productname>PostgreSQL</productname> has been built
7595 with assertions enabled. That is the case if the
7596 macro <symbol>USE_ASSERT_CHECKING</symbol> is defined
7597 when <productname>PostgreSQL</productname> is built (accomplished
7598 e.g. by the <command>configure</command> option
7599 <option>--enable-cassert</option>). By
7600 default <productname>PostgreSQL</productname> is built without
7606 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
7607 <term><varname>integer_datetimes</varname> (<type>boolean</type>)
7609 <primary><varname>integer_datetimes</> configuration parameter</primary>
7614 Reports whether <productname>PostgreSQL</> was built with
7615 support for 64-bit-integer dates and times. This can be
7616 disabled by configuring with <literal>--disable-integer-datetimes</>
7617 when building <productname>PostgreSQL</>. The default value is
7618 <literal>on</literal>.
7623 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
7624 <term><varname>lc_collate</varname> (<type>string</type>)
7626 <primary><varname>lc_collate</> configuration parameter</primary>
7631 Reports the locale in which sorting of textual data is done.
7632 See <xref linkend="locale"> for more information.
7633 This value is determined when a database is created.
7638 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
7639 <term><varname>lc_ctype</varname> (<type>string</type>)
7641 <primary><varname>lc_ctype</> configuration parameter</primary>
7646 Reports the locale that determines character classifications.
7647 See <xref linkend="locale"> for more information.
7648 This value is determined when a database is created.
7649 Ordinarily this will be the same as <varname>lc_collate</varname>,
7650 but for special applications it might be set differently.
7655 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
7656 <term><varname>max_function_args</varname> (<type>integer</type>)
7658 <primary><varname>max_function_args</> configuration parameter</primary>
7663 Reports the maximum number of function arguments. It is determined by
7664 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
7665 default value is 100 arguments.
7670 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
7671 <term><varname>max_identifier_length</varname> (<type>integer</type>)
7673 <primary><varname>max_identifier_length</> configuration parameter</primary>
7678 Reports the maximum identifier length. It is determined as one
7679 less than the value of <literal>NAMEDATALEN</> when building
7680 the server. The default value of <literal>NAMEDATALEN</> is
7681 64; therefore the default
7682 <varname>max_identifier_length</varname> is 63 bytes, which
7683 can be less than 63 characters when using multibyte encodings.
7688 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
7689 <term><varname>max_index_keys</varname> (<type>integer</type>)
7691 <primary><varname>max_index_keys</> configuration parameter</primary>
7696 Reports the maximum number of index keys. It is determined by
7697 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
7698 default value is 32 keys.
7703 <varlistentry id="guc-segment-size" xreflabel="segment_size">
7704 <term><varname>segment_size</varname> (<type>integer</type>)
7706 <primary><varname>segment_size</> configuration parameter</primary>
7711 Reports the number of blocks (pages) that can be stored within a file
7712 segment. It is determined by the value of <literal>RELSEG_SIZE</>
7713 when building the server. The maximum size of a segment file in bytes
7714 is equal to <varname>segment_size</> multiplied by
7715 <varname>block_size</>; by default this is 1GB.
7720 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
7721 <term><varname>server_encoding</varname> (<type>string</type>)
7723 <primary><varname>server_encoding</> configuration parameter</primary>
7725 <indexterm><primary>character set</></>
7729 Reports the database encoding (character set).
7730 It is determined when the database is created. Ordinarily,
7731 clients need only be concerned with the value of <xref
7732 linkend="guc-client-encoding">.
7737 <varlistentry id="guc-server-version" xreflabel="server_version">
7738 <term><varname>server_version</varname> (<type>string</type>)
7740 <primary><varname>server_version</> configuration parameter</primary>
7745 Reports the version number of the server. It is determined by the
7746 value of <literal>PG_VERSION</> when building the server.
7751 <varlistentry id="guc-server-version-num" xreflabel="server_version_num">
7752 <term><varname>server_version_num</varname> (<type>integer</type>)
7754 <primary><varname>server_version_num</> configuration parameter</primary>
7759 Reports the version number of the server as an integer. It is determined
7760 by the value of <literal>PG_VERSION_NUM</> when building the server.
7765 <varlistentry id="guc-wal-block-size" xreflabel="wal_block_size">
7766 <term><varname>wal_block_size</varname> (<type>integer</type>)
7768 <primary><varname>wal_block_size</> configuration parameter</primary>
7773 Reports the size of a WAL disk block. It is determined by the value
7774 of <literal>XLOG_BLCKSZ</> when building the server. The default value
7780 <varlistentry id="guc-wal-segment-size" xreflabel="wal_segment_size">
7781 <term><varname>wal_segment_size</varname> (<type>integer</type>)
7783 <primary><varname>wal_segment_size</> configuration parameter</primary>
7788 Reports the number of blocks (pages) in a WAL segment file.
7789 The total size of a WAL segment file in bytes is equal to
7790 <varname>wal_segment_size</> multiplied by <varname>wal_block_size</>;
7791 by default this is 16MB. See <xref linkend="wal-configuration"> for
7800 <sect1 id="runtime-config-custom">
7801 <title>Customized Options</title>
7804 This feature was designed to allow parameters not normally known to
7805 <productname>PostgreSQL</productname> to be added by add-on modules
7806 (such as procedural languages). This allows extension modules to be
7807 configured in the standard ways.
7811 Custom options have two-part names: an extension name, then a dot, then
7812 the parameter name proper, much like qualified names in SQL. An example
7813 is <literal>plpgsql.variable_conflict</>.
7817 Because custom options may need to be set in processes that have not
7818 loaded the relevant extension module, <productname>PostgreSQL</>
7819 will accept a setting for any two-part parameter name. Such variables
7820 are treated as placeholders and have no function until the module that
7821 defines them is loaded. When an extension module is loaded, it will add
7822 its variable definitions, convert any placeholder values according to
7823 those definitions, and issue warnings for any unrecognized placeholders
7824 that begin with its extension name.
7828 <sect1 id="runtime-config-developer">
7829 <title>Developer Options</title>
7832 The following parameters are intended for work on the
7833 <productname>PostgreSQL</productname> source code, and in some cases
7834 to assist with recovery of severely damaged databases. There
7835 should be no reason to use them on a production database.
7836 As such, they have been excluded from the sample
7837 <filename>postgresql.conf</> file. Note that many of these
7838 parameters require special source compilation flags to work at all.
7842 <varlistentry id="guc-allow-system-table-mods" xreflabel="allow_system_table_mods">
7843 <term><varname>allow_system_table_mods</varname> (<type>boolean</type>)
7845 <primary><varname>allow_system_table_mods</varname> configuration parameter</primary>
7850 Allows modification of the structure of system tables.
7851 This is used by <command>initdb</command>.
7852 This parameter can only be set at server start.
7857 <varlistentry id="guc-ignore-system-indexes" xreflabel="ignore_system_indexes">
7858 <term><varname>ignore_system_indexes</varname> (<type>boolean</type>)
7860 <primary><varname>ignore_system_indexes</varname> configuration parameter</primary>
7865 Ignore system indexes when reading system tables (but still
7866 update the indexes when modifying the tables). This is useful
7867 when recovering from damaged system indexes.
7868 This parameter cannot be changed after session start.
7873 <varlistentry id="guc-post-auth-delay" xreflabel="post_auth_delay">
7874 <term><varname>post_auth_delay</varname> (<type>integer</type>)
7876 <primary><varname>post_auth_delay</> configuration parameter</primary>
7881 If nonzero, a delay of this many seconds occurs when a new
7882 server process is started, after it conducts the
7883 authentication procedure. This is intended to give developers an
7884 opportunity to attach to the server process with a debugger.
7885 This parameter cannot be changed after session start.
7890 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
7891 <term><varname>pre_auth_delay</varname> (<type>integer</type>)
7893 <primary><varname>pre_auth_delay</> configuration parameter</primary>
7898 If nonzero, a delay of this many seconds occurs just after a
7899 new server process is forked, before it conducts the
7900 authentication procedure. This is intended to give developers an
7901 opportunity to attach to the server process with a debugger to
7902 trace down misbehavior in authentication.
7903 This parameter can only be set in the <filename>postgresql.conf</>
7904 file or on the server command line.
7909 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
7910 <term><varname>trace_notify</varname> (<type>boolean</type>)
7912 <primary><varname>trace_notify</> configuration parameter</primary>
7917 Generates a great amount of debugging output for the
7918 <command>LISTEN</command> and <command>NOTIFY</command>
7919 commands. <xref linkend="guc-client-min-messages"> or
7920 <xref linkend="guc-log-min-messages"> must be
7921 <literal>DEBUG1</literal> or lower to send this output to the
7922 client or server logs, respectively.
7927 <varlistentry id="guc-trace-recovery-messages" xreflabel="trace_recovery_messages">
7928 <term><varname>trace_recovery_messages</varname> (<type>enum</type>)
7930 <primary><varname>trace_recovery_messages</> configuration parameter</primary>
7935 Enables logging of recovery-related debugging output that otherwise
7936 would not be logged. This parameter allows the user to override the
7937 normal setting of <xref linkend="guc-log-min-messages">, but only for
7938 specific messages. This is intended for use in debugging Hot Standby.
7939 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
7940 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>, and
7941 <literal>LOG</>. The default, <literal>LOG</>, does not affect
7942 logging decisions at all. The other values cause recovery-related
7943 debug messages of that priority or higher to be logged as though they
7944 had <literal>LOG</> priority; for common settings of
7945 <varname>log_min_messages</> this results in unconditionally sending
7946 them to the server log.
7947 This parameter can only be set in the <filename>postgresql.conf</>
7948 file or on the server command line.
7953 <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
7954 <term><varname>trace_sort</varname> (<type>boolean</type>)
7956 <primary><varname>trace_sort</> configuration parameter</primary>
7961 If on, emit information about resource usage during sort operations.
7962 This parameter is only available if the <symbol>TRACE_SORT</symbol> macro
7963 was defined when <productname>PostgreSQL</productname> was compiled.
7964 (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
7970 <term><varname>trace_locks</varname> (<type>boolean</type>)
7972 <primary><varname>trace_locks</> configuration parameter</primary>
7977 If on, emit information about lock usage. Information dumped
7978 includes the type of lock operation, the type of lock and the unique
7979 identifier of the object being locked or unlocked. Also included
7980 are bit masks for the lock types already granted on this object as
7981 well as for the lock types awaited on this object. For each lock
7982 type a count of the number of granted locks and waiting locks is
7983 also dumped as well as the totals. An example of the log file output
7986 LOG: LockAcquire: new: lock(0xb7acd844) id(24688,24696,0,0,0,1)
7987 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
7988 wait(0) type(AccessShareLock)
7989 LOG: GrantLock: lock(0xb7acd844) id(24688,24696,0,0,0,1)
7990 grantMask(2) req(1,0,0,0,0,0,0)=1 grant(1,0,0,0,0,0,0)=1
7991 wait(0) type(AccessShareLock)
7992 LOG: UnGrantLock: updated: lock(0xb7acd844) id(24688,24696,0,0,0,1)
7993 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
7994 wait(0) type(AccessShareLock)
7995 LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
7996 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
7997 wait(0) type(INVALID)
7999 Details of the structure being dumped may be found in
8000 <filename>src/include/storage/lock.h</filename>.
8003 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
8004 macro was defined when <productname>PostgreSQL</productname> was
8011 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)
8013 <primary><varname>trace_lwlocks</> configuration parameter</primary>
8018 If on, emit information about lightweight lock usage. Lightweight
8019 locks are intended primarily to provide mutual exclusion of access
8020 to shared-memory data structures.
8023 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
8024 macro was defined when <productname>PostgreSQL</productname> was
8031 <term><varname>trace_userlocks</varname> (<type>boolean</type>)
8033 <primary><varname>trace_userlocks</> configuration parameter</primary>
8038 If on, emit information about user lock usage. Output is the same
8039 as for <symbol>trace_locks</symbol>, only for advisory locks.
8042 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
8043 macro was defined when <productname>PostgreSQL</productname> was
8050 <term><varname>trace_lock_oidmin</varname> (<type>integer</type>)
8052 <primary><varname>trace_lock_oidmin</> configuration parameter</primary>
8057 If set, do not trace locks for tables below this OID. (use to avoid
8058 output on system tables)
8061 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
8062 macro was defined when <productname>PostgreSQL</productname> was
8069 <term><varname>trace_lock_table</varname> (<type>integer</type>)
8071 <primary><varname>trace_lock_table</> configuration parameter</primary>
8076 Unconditionally trace locks on this table (OID).
8079 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
8080 macro was defined when <productname>PostgreSQL</productname> was
8087 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)
8089 <primary><varname>debug_deadlocks</> configuration parameter</primary>
8094 If set, dumps information about all current locks when a
8095 deadlock timeout occurs.
8098 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
8099 macro was defined when <productname>PostgreSQL</productname> was
8106 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)
8108 <primary><varname>log_btree_build_stats</> configuration parameter</primary>
8113 If set, logs system resource usage statistics (memory and CPU) on
8114 various B-tree operations.
8117 This parameter is only available if the <symbol>BTREE_BUILD_STATS</symbol>
8118 macro was defined when <productname>PostgreSQL</productname> was
8124 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
8125 <term><varname>wal_debug</varname> (<type>boolean</type>)
8127 <primary><varname>wal_debug</> configuration parameter</primary>
8132 If on, emit WAL-related debugging output. This parameter is
8133 only available if the <symbol>WAL_DEBUG</symbol> macro was
8134 defined when <productname>PostgreSQL</productname> was
8140 <varlistentry id="guc-ignore-checksum-failure" xreflabel="ignore_checksum_failure">
8141 <term><varname>ignore_checksum_failure</varname> (<type>boolean</type>)
8143 <primary><varname>ignore_checksum_failure</> configuration parameter</primary>
8148 Only has effect if <xref linkend="app-initdb-data-checksums"> are enabled.
8151 Detection of a checksum failure during a read normally causes
8152 <productname>PostgreSQL</> to report an error, aborting the current
8153 transaction. Setting <varname>ignore_checksum_failure</> to on causes
8154 the system to ignore the failure (but still report a warning), and
8155 continue processing. This behavior may <emphasis>cause crashes, propagate
8156 or hide corruption, or other serious problems</>. However, it may allow
8157 you to get past the error and retrieve undamaged tuples that might still be
8158 present in the table if the block header is still sane. If the header is
8159 corrupt an error will be reported even if this option is enabled. The
8160 default setting is <literal>off</>, and it can only be changed by a superuser.
8165 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
8166 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)
8168 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
8173 Detection of a damaged page header normally causes
8174 <productname>PostgreSQL</> to report an error, aborting the current
8175 transaction. Setting <varname>zero_damaged_pages</> to on causes
8176 the system to instead report a warning, zero out the damaged
8177 page in memory, and continue processing. This behavior <emphasis>will destroy data</>,
8178 namely all the rows on the damaged page. However, it does allow you to get
8179 past the error and retrieve rows from any undamaged pages that might
8180 be present in the table. It is useful for recovering data if
8181 corruption has occurred due to a hardware or software error. You should
8182 generally not set this on until you have given up hope of recovering
8183 data from the damaged pages of a table. Zeroed-out pages are not
8184 forced to disk so it is recommended to recreate the table or
8185 the index before turning this parameter off again. The
8186 default setting is <literal>off</>, and it can only be changed
8193 <sect1 id="runtime-config-short">
8194 <title>Short Options</title>
8197 For convenience there are also single letter command-line option
8198 switches available for some parameters. They are described in
8199 <xref linkend="runtime-config-short-table">. Some of these
8200 options exist for historical reasons, and their presence as a
8201 single-letter option does not necessarily indicate an endorsement
8202 to use the option heavily.
8205 <table id="runtime-config-short-table">
8206 <title>Short Option Key</title>
8210 <entry>Short Option</entry>
8211 <entry>Equivalent</entry>
8217 <entry><option>-B <replaceable>x</replaceable></option></entry>
8218 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
8221 <entry><option>-d <replaceable>x</replaceable></option></entry>
8222 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
8225 <entry><option>-e</option></entry>
8226 <entry><literal>datestyle = euro</></entry>
8230 <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
8231 <option>-fm</option>, <option>-fn</option>, <option>-fo</option>,
8232 <option>-fs</option>, <option>-ft</option>
8235 <literal>enable_bitmapscan = off</>,
8236 <literal>enable_hashjoin = off</>,
8237 <literal>enable_indexscan = off</>,
8238 <literal>enable_mergejoin = off</>,
8239 <literal>enable_nestloop = off</>,
8240 <literal>enable_indexonlyscan = off</>,
8241 <literal>enable_seqscan = off</>,
8242 <literal>enable_tidscan = off</>
8246 <entry><option>-F</option></entry>
8247 <entry><literal>fsync = off</></entry>
8250 <entry><option>-h <replaceable>x</replaceable></option></entry>
8251 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
8254 <entry><option>-i</option></entry>
8255 <entry><literal>listen_addresses = '*'</></entry>
8258 <entry><option>-k <replaceable>x</replaceable></option></entry>
8259 <entry><literal>unix_socket_directories = <replaceable>x</replaceable></></entry>
8262 <entry><option>-l</option></entry>
8263 <entry><literal>ssl = on</></entry>
8266 <entry><option>-N <replaceable>x</replaceable></option></entry>
8267 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
8270 <entry><option>-O</option></entry>
8271 <entry><literal>allow_system_table_mods = on</></entry>
8274 <entry><option>-p <replaceable>x</replaceable></option></entry>
8275 <entry><literal>port = <replaceable>x</replaceable></></entry>
8278 <entry><option>-P</option></entry>
8279 <entry><literal>ignore_system_indexes = on</></entry>
8282 <entry><option>-s</option></entry>
8283 <entry><literal>log_statement_stats = on</></entry>
8286 <entry><option>-S <replaceable>x</replaceable></option></entry>
8287 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
8290 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option></entry>
8291 <entry><literal>log_parser_stats = on</>,
8292 <literal>log_planner_stats = on</>,
8293 <literal>log_executor_stats = on</></entry>
8296 <entry><option>-W <replaceable>x</replaceable></option></entry>
8297 <entry><literal>post_auth_delay = <replaceable>x</replaceable></></entry>