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.
1055 Explanation of the default value:
1058 <term><literal>HIGH</literal></term>
1061 Cipher suites that use ciphers from <literal>HIGH</> group (e.g.,
1062 AES, Camellia, 3DES)
1068 <term><literal>MEDIUM</literal></term>
1071 Cipher suites that use ciphers from <literal>MEDIUM</> group
1078 <term><literal>+3DES</literal></term>
1081 The OpenSSL default order for <literal>HIGH</> is problematic
1082 because it orders 3DES higher than AES128. This is wrong because
1083 3DES offers less security than AES128, and it is also much
1084 slower. <literal>+3DES</> reorders it after all other
1085 <literal>HIGH</> and <literal>MEDIUM</> ciphers.
1091 <term><literal>!aNULL</literal></term>
1094 Disables anonymous cipher suites that do no authentication. Such
1095 cipher suites are vulnerable to man-in-the-middle attacks and
1096 therefore should not be used.
1104 Available cipher suite details will vary across OpenSSL versions. Use
1106 <literal>openssl ciphers -v 'HIGH:MEDIUM:+3DES:!aNULL'</literal> to
1107 see actual details for the currently installed <application>OpenSSL</>
1108 version. Note that this list is filtered at run time based on the
1114 <varlistentry id="guc-ssl-prefer-server-ciphers" xreflabel="ssl_prefer_server_ciphers">
1115 <term><varname>ssl_prefer_server_ciphers</varname> (<type>bool</type>)
1117 <primary><varname>ssl_prefer_server_ciphers</> configuration parameter</primary>
1122 Specifies whether to use the server's SSL cipher preferences, rather
1123 than the client's. The default is true.
1127 Older PostgreSQL versions do not have this setting and always use the
1128 client's preferences. This setting is mainly for backward
1129 compatibility with those versions. Using the server's preferences is
1130 usually better because it is more likely that the server is appropriately
1136 <varlistentry id="guc-ssl-ecdh-curve" xreflabel="ssl_ecdh_curve">
1137 <term><varname>ssl_ecdh_curve</varname> (<type>string</type>)
1139 <primary><varname>ssl_ecdh_curve</> configuration parameter</primary>
1144 Specifies the name of the curve to use in <acronym>ECDH</> key
1145 exchange. It needs to be supported by all clients that connect.
1146 It does not need to be same curve as used by server's Elliptic
1147 Curve key. The default is <literal>prime256v1</>.
1151 OpenSSL names for most common curves:
1152 <literal>prime256v1</> (NIST P-256),
1153 <literal>secp384r1</> (NIST P-384),
1154 <literal>secp521r1</> (NIST P-521).
1158 The full list of available curves can be shown with the command
1159 <command>openssl ecparam -list_curves</command>. Not all of them
1160 are usable in <acronym>TLS</> though.
1165 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
1166 <term><varname>password_encryption</varname> (<type>boolean</type>)
1168 <primary><varname>password_encryption</> configuration parameter</primary>
1173 When a password is specified in <xref
1174 linkend="sql-createuser"> or
1175 <xref linkend="sql-alterrole">
1176 without writing either <literal>ENCRYPTED</> or
1177 <literal>UNENCRYPTED</>, this parameter determines whether the
1178 password is to be encrypted. The default is <literal>on</>
1179 (encrypt the password).
1184 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
1185 <term><varname>krb_server_keyfile</varname> (<type>string</type>)
1187 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
1192 Sets the location of the Kerberos server key file. See
1193 <xref linkend="gssapi-auth">
1194 for details. This parameter can only be set in the
1195 <filename>postgresql.conf</> file or on the server command line.
1200 <varlistentry id="guc-krb-caseins-users" xreflabel="krb_caseins_users">
1201 <term><varname>krb_caseins_users</varname> (<type>boolean</type>)
1203 <primary><varname>krb_caseins_users</varname> configuration parameter</primary>
1208 Sets whether GSSAPI user names should be treated
1210 The default is <literal>off</> (case sensitive). This parameter can only be
1211 set in the <filename>postgresql.conf</> file or on the server command line.
1216 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
1217 <term><varname>db_user_namespace</varname> (<type>boolean</type>)
1219 <primary><varname>db_user_namespace</> configuration parameter</primary>
1224 This parameter enables per-database user names. It is off by default.
1225 This parameter can only be set in the <filename>postgresql.conf</>
1226 file or on the server command line.
1230 If this is on, you should create users as <literal>username@dbname</>.
1231 When <literal>username</> is passed by a connecting client,
1232 <literal>@</> and the database name are appended to the user
1233 name and that database-specific user name is looked up by the
1234 server. Note that when you create users with names containing
1235 <literal>@</> within the SQL environment, you will need to
1236 quote the user name.
1240 With this parameter enabled, you can still create ordinary global
1241 users. Simply append <literal>@</> when specifying the user
1242 name in the client, e.g. <literal>joe@</>. The <literal>@</>
1243 will be stripped off before the user name is looked up by the
1248 <varname>db_user_namespace</> causes the client's and
1249 server's user name representation to differ.
1250 Authentication checks are always done with the server's user name
1251 so authentication methods must be configured for the
1252 server's user name, not the client's. Because
1253 <literal>md5</> uses the user name as salt on both the
1254 client and server, <literal>md5</> cannot be used with
1255 <varname>db_user_namespace</>.
1260 This feature is intended as a temporary measure until a
1261 complete solution is found. At that time, this option will
1272 <sect1 id="runtime-config-resource">
1273 <title>Resource Consumption</title>
1275 <sect2 id="runtime-config-resource-memory">
1276 <title>Memory</title>
1279 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
1280 <term><varname>shared_buffers</varname> (<type>integer</type>)
1282 <primary><varname>shared_buffers</> configuration parameter</primary>
1287 Sets the amount of memory the database server uses for shared
1288 memory buffers. The default is typically 128 megabytes
1289 (<literal>128MB</>), but might be less if your kernel settings will
1290 not support it (as determined during <application>initdb</>).
1291 This setting must be at least 128 kilobytes. (Non-default
1292 values of <symbol>BLCKSZ</symbol> change the minimum.) However,
1293 settings significantly higher than the minimum are usually needed
1294 for good performance. This parameter can only be set at server start.
1298 If you have a dedicated database server with 1GB or more of RAM, a
1299 reasonable starting value for <varname>shared_buffers</varname> is 25%
1300 of the memory in your system. There are some workloads where even
1301 large settings for <varname>shared_buffers</varname> are effective, but
1302 because <productname>PostgreSQL</productname> also relies on the
1303 operating system cache, it is unlikely that an allocation of more than
1304 40% of RAM to <varname>shared_buffers</varname> will work better than a
1305 smaller amount. Larger settings for <varname>shared_buffers</varname>
1306 usually require a corresponding increase in
1307 <varname>max_wal_size</varname>, in order to spread out the
1308 process of writing large quantities of new or changed data over a
1309 longer period of time.
1313 On systems with less than 1GB of RAM, a smaller percentage of RAM is
1314 appropriate, so as to leave adequate space for the operating system.
1315 Also, on Windows, large values for <varname>shared_buffers</varname>
1316 aren't as effective. You may find better results keeping the setting
1317 relatively low and using the operating system cache more instead. The
1318 useful range for <varname>shared_buffers</varname> on Windows systems
1319 is generally from 64MB to 512MB.
1325 <varlistentry id="guc-huge-pages" xreflabel="huge_pages">
1326 <term><varname>huge_pages</varname> (<type>enum</type>)
1328 <primary><varname>huge_pages</> configuration parameter</primary>
1333 Enables/disables the use of huge memory pages. Valid values are
1334 <literal>try</literal> (the default), <literal>on</literal>,
1335 and <literal>off</literal>.
1339 At present, this feature is supported only on Linux. The setting is
1340 ignored on other systems when set to <literal>try</literal>.
1344 The use of huge pages results in smaller page tables and less CPU time
1345 spent on memory management, increasing performance. For more details,
1346 see <xref linkend="linux-huge-pages">.
1350 With <varname>huge_pages</varname> set to <literal>try</literal>,
1351 the server will try to use huge pages, but fall back to using
1352 normal allocation if that fails. With <literal>on</literal>, failure
1353 to use huge pages will prevent the server from starting up. With
1354 <literal>off</literal>, huge pages will not be used.
1359 <varlistentry id="guc-temp-buffers" xreflabel="temp_buffers">
1360 <term><varname>temp_buffers</varname> (<type>integer</type>)
1362 <primary><varname>temp_buffers</> configuration parameter</primary>
1367 Sets the maximum number of temporary buffers used by each database
1368 session. These are session-local buffers used only for access to
1369 temporary tables. The default is eight megabytes
1370 (<literal>8MB</>). The setting can be changed within individual
1371 sessions, but only before the first use of temporary tables
1372 within the session; subsequent attempts to change the value will
1373 have no effect on that session.
1377 A session will allocate temporary buffers as needed up to the limit
1378 given by <varname>temp_buffers</>. The cost of setting a large
1379 value in sessions that do not actually need many temporary
1380 buffers is only a buffer descriptor, or about 64 bytes, per
1381 increment in <varname>temp_buffers</>. However if a buffer is
1382 actually used an additional 8192 bytes will be consumed for it
1383 (or in general, <symbol>BLCKSZ</symbol> bytes).
1388 <varlistentry id="guc-max-prepared-transactions" xreflabel="max_prepared_transactions">
1389 <term><varname>max_prepared_transactions</varname> (<type>integer</type>)
1391 <primary><varname>max_prepared_transactions</> configuration parameter</primary>
1396 Sets the maximum number of transactions that can be in the
1397 <quote>prepared</> state simultaneously (see <xref
1398 linkend="sql-prepare-transaction">).
1399 Setting this parameter to zero (which is the default)
1400 disables the prepared-transaction feature.
1401 This parameter can only be set at server start.
1405 If you are not planning to use prepared transactions, this parameter
1406 should be set to zero to prevent accidental creation of prepared
1407 transactions. If you are using prepared transactions, you will
1408 probably want <varname>max_prepared_transactions</varname> to be at
1409 least as large as <xref linkend="guc-max-connections">, so that every
1410 session can have a prepared transaction pending.
1414 When running a standby server, you must set this parameter to the
1415 same or higher value than on the master server. Otherwise, queries
1416 will not be allowed in the standby server.
1421 <varlistentry id="guc-work-mem" xreflabel="work_mem">
1422 <term><varname>work_mem</varname> (<type>integer</type>)
1424 <primary><varname>work_mem</> configuration parameter</primary>
1429 Specifies the amount of memory to be used by internal sort operations
1430 and hash tables before writing to temporary disk files. The value
1431 defaults to four megabytes (<literal>4MB</>).
1432 Note that for a complex query, several sort or hash operations might be
1433 running in parallel; each operation will be allowed to use as much memory
1434 as this value specifies before it starts to write data into temporary
1435 files. Also, several running sessions could be doing such operations
1436 concurrently. Therefore, the total memory used could be many
1437 times the value of <varname>work_mem</varname>; it is necessary to
1438 keep this fact in mind when choosing the value. Sort operations are
1439 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
1441 Hash tables are used in hash joins, hash-based aggregation, and
1442 hash-based processing of <literal>IN</> subqueries.
1447 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
1448 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)
1450 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
1455 Specifies the maximum amount of memory to be used by maintenance
1456 operations, such as <command>VACUUM</command>, <command>CREATE
1457 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults
1458 to 64 megabytes (<literal>64MB</>). Since only one of these
1459 operations can be executed at a time by a database session, and
1460 an installation normally doesn't have many of them running
1461 concurrently, it's safe to set this value significantly larger
1462 than <varname>work_mem</varname>. Larger settings might improve
1463 performance for vacuuming and for restoring database dumps.
1466 Note that when autovacuum runs, up to
1467 <xref linkend="guc-autovacuum-max-workers"> times this memory
1468 may be allocated, so be careful not to set the default value
1469 too high. It may be useful to control for this by separately
1470 setting <xref linkend="guc-autovacuum-work-mem">.
1475 <varlistentry id="guc-autovacuum-work-mem" xreflabel="autovacuum_work_mem">
1476 <term><varname>autovacuum_work_mem</varname> (<type>integer</type>)
1478 <primary><varname>autovacuum_work_mem</> configuration parameter</primary>
1483 Specifies the maximum amount of memory to be used by each
1484 autovacuum worker process. It defaults to -1, indicating that
1485 the value of <xref linkend="guc-maintenance-work-mem"> should
1486 be used instead. The setting has no effect on the behavior of
1487 <command>VACUUM</command> when run in other contexts.
1492 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
1493 <term><varname>max_stack_depth</varname> (<type>integer</type>)
1495 <primary><varname>max_stack_depth</> configuration parameter</primary>
1500 Specifies the maximum safe depth of the server's execution stack.
1501 The ideal setting for this parameter is the actual stack size limit
1502 enforced by the kernel (as set by <literal>ulimit -s</> or local
1503 equivalent), less a safety margin of a megabyte or so. The safety
1504 margin is needed because the stack depth is not checked in every
1505 routine in the server, but only in key potentially-recursive routines
1506 such as expression evaluation. The default setting is two
1507 megabytes (<literal>2MB</>), which is conservatively small and
1508 unlikely to risk crashes. However, it might be too small to allow
1509 execution of complex functions. Only superusers can change this
1514 Setting <varname>max_stack_depth</> higher than
1515 the actual kernel limit will mean that a runaway recursive function
1516 can crash an individual backend process. On platforms where
1517 <productname>PostgreSQL</productname> can determine the kernel limit,
1518 the server will not allow this variable to be set to an unsafe
1519 value. However, not all platforms provide the information,
1520 so caution is recommended in selecting a value.
1525 <varlistentry id="guc-dynamic-shared-memory-type" xreflabel="dynamic_shared_memory_type">
1526 <term><varname>dynamic_shared_memory_type</varname> (<type>enum</type>)
1528 <primary><varname>dynamic_shared_memory_type</> configuration parameter</primary>
1533 Specifies the dynamic shared memory implementation that the server
1534 should use. Possible values are <literal>posix</> (for POSIX shared
1535 memory allocated using <literal>shm_open</>), <literal>sysv</literal>
1536 (for System V shared memory allocated via <literal>shmget</>),
1537 <literal>windows</> (for Windows shared memory), <literal>mmap</>
1538 (to simulate shared memory using memory-mapped files stored in the
1539 data directory), and <literal>none</> (to disable this feature).
1540 Not all values are supported on all platforms; the first supported
1541 option is the default for that platform. The use of the
1542 <literal>mmap</> option, which is not the default on any platform,
1543 is generally discouraged because the operating system may write
1544 modified pages back to disk repeatedly, increasing system I/O load;
1545 however, it may be useful for debugging, when the
1546 <literal>pg_dynshmem</> directory is stored on a RAM disk, or when
1547 other shared memory facilities are not available.
1555 <sect2 id="runtime-config-resource-disk">
1559 <varlistentry id="guc-temp-file-limit" xreflabel="temp_file_limit">
1560 <term><varname>temp_file_limit</varname> (<type>integer</type>)
1562 <primary><varname>temp_file_limit</> configuration parameter</primary>
1567 Specifies the maximum amount of disk space that a session can use
1568 for temporary files, such as sort and hash temporary files, or the
1569 storage file for a held cursor. A transaction attempting to exceed
1570 this limit will be canceled.
1571 The value is specified in kilobytes, and <literal>-1</> (the
1572 default) means no limit.
1573 Only superusers can change this setting.
1576 This setting constrains the total space used at any instant by all
1577 temporary files used by a given <productname>PostgreSQL</> session.
1578 It should be noted that disk space used for explicit temporary
1579 tables, as opposed to temporary files used behind-the-scenes in query
1580 execution, does <emphasis>not</emphasis> count against this limit.
1588 <sect2 id="runtime-config-resource-kernel">
1589 <title>Kernel Resource Usage</title>
1592 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
1593 <term><varname>max_files_per_process</varname> (<type>integer</type>)
1595 <primary><varname>max_files_per_process</> configuration parameter</primary>
1600 Sets the maximum number of simultaneously open files allowed to each
1601 server subprocess. The default is one thousand files. If the kernel is enforcing
1602 a safe per-process limit, you don't need to worry about this setting.
1603 But on some platforms (notably, most BSD systems), the kernel will
1604 allow individual processes to open many more files than the system
1605 can actually support if many processes all try to open
1606 that many files. If you find yourself seeing <quote>Too many open
1607 files</> failures, try reducing this setting.
1608 This parameter can only be set at server start.
1615 <sect2 id="runtime-config-resource-vacuum-cost">
1616 <title>Cost-based Vacuum Delay</title>
1619 During the execution of <xref linkend="sql-vacuum">
1620 and <xref linkend="sql-analyze">
1621 commands, the system maintains an
1622 internal counter that keeps track of the estimated cost of the
1623 various I/O operations that are performed. When the accumulated
1624 cost reaches a limit (specified by
1625 <varname>vacuum_cost_limit</varname>), the process performing
1626 the operation will sleep for a short period of time, as specified by
1627 <varname>vacuum_cost_delay</varname>. Then it will reset the
1628 counter and continue execution.
1632 The intent of this feature is to allow administrators to reduce
1633 the I/O impact of these commands on concurrent database
1634 activity. There are many situations where it is not
1635 important that maintenance commands like
1636 <command>VACUUM</command> and <command>ANALYZE</command> finish
1637 quickly; however, it is usually very important that these
1638 commands do not significantly interfere with the ability of the
1639 system to perform other database operations. Cost-based vacuum
1640 delay provides a way for administrators to achieve this.
1644 This feature is disabled by default for manually issued
1645 <command>VACUUM</command> commands. To enable it, set the
1646 <varname>vacuum_cost_delay</varname> variable to a nonzero
1651 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1652 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)
1654 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1659 The length of time, in milliseconds, that the process will sleep
1660 when the cost limit has been exceeded.
1661 The default value is zero, which disables the cost-based vacuum
1662 delay feature. Positive values enable cost-based vacuuming.
1663 Note that on many systems, the effective resolution
1664 of sleep delays is 10 milliseconds; setting
1665 <varname>vacuum_cost_delay</varname> to a value that is
1666 not a multiple of 10 might have the same results as setting it
1667 to the next higher multiple of 10.
1671 When using cost-based vacuuming, appropriate values for
1672 <varname>vacuum_cost_delay</> are usually quite small, perhaps
1673 10 or 20 milliseconds. Adjusting vacuum's resource consumption
1674 is best done by changing the other vacuum cost parameters.
1679 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1680 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)
1682 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1687 The estimated cost for vacuuming a buffer found in the shared buffer
1688 cache. It represents the cost to lock the buffer pool, lookup
1689 the shared hash table and scan the content of the page. The
1690 default value is one.
1695 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1696 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)
1698 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1703 The estimated cost for vacuuming a buffer that has to be read from
1704 disk. This represents the effort to lock the buffer pool,
1705 lookup the shared hash table, read the desired block in from
1706 the disk and scan its content. The default value is 10.
1711 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1712 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)
1714 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1719 The estimated cost charged when vacuum modifies a block that was
1720 previously clean. It represents the extra I/O required to
1721 flush the dirty block out to disk again. The default value is
1727 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1728 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)
1730 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1735 The accumulated cost that will cause the vacuuming process to sleep.
1736 The default value is 200.
1744 There are certain operations that hold critical locks and should
1745 therefore complete as quickly as possible. Cost-based vacuum
1746 delays do not occur during such operations. Therefore it is
1747 possible that the cost accumulates far higher than the specified
1748 limit. To avoid uselessly long delays in such cases, the actual
1749 delay is calculated as <varname>vacuum_cost_delay</varname> *
1750 <varname>accumulated_balance</varname> /
1751 <varname>vacuum_cost_limit</varname> with a maximum of
1752 <varname>vacuum_cost_delay</varname> * 4.
1757 <sect2 id="runtime-config-resource-background-writer">
1758 <title>Background Writer</title>
1761 There is a separate server
1762 process called the <firstterm>background writer</>, whose function
1763 is to issue writes of <quote>dirty</> (new or modified) shared
1764 buffers. It writes shared buffers so server processes handling
1765 user queries seldom or never need to wait for a write to occur.
1766 However, the background writer does cause a net overall
1767 increase in I/O load, because while a repeatedly-dirtied page might
1768 otherwise be written only once per checkpoint interval, the
1769 background writer might write it several times as it is dirtied
1770 in the same interval. The parameters discussed in this subsection
1771 can be used to tune the behavior for local needs.
1775 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1776 <term><varname>bgwriter_delay</varname> (<type>integer</type>)
1778 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1783 Specifies the delay between activity rounds for the
1784 background writer. In each round the writer issues writes
1785 for some number of dirty buffers (controllable by the
1786 following parameters). It then sleeps for <varname>bgwriter_delay</>
1787 milliseconds, and repeats. When there are no dirty buffers in the
1788 buffer pool, though, it goes into a longer sleep regardless of
1789 <varname>bgwriter_delay</>. The default value is 200
1790 milliseconds (<literal>200ms</>). Note that on many systems, the
1791 effective resolution of sleep delays is 10 milliseconds; setting
1792 <varname>bgwriter_delay</> to a value that is not a multiple of 10
1793 might have the same results as setting it to the next higher multiple
1794 of 10. This parameter can only be set in the
1795 <filename>postgresql.conf</> file or on the server command line.
1800 <varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1801 <term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)
1803 <primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1808 In each round, no more than this many buffers will be written
1809 by the background writer. Setting this to zero disables
1810 background writing. (Note that checkpoints, which are managed by
1811 a separate, dedicated auxiliary process, are unaffected.)
1812 The default value is 100 buffers.
1813 This parameter can only be set in the <filename>postgresql.conf</>
1814 file or on the server command line.
1819 <varlistentry id="guc-bgwriter-lru-multiplier" xreflabel="bgwriter_lru_multiplier">
1820 <term><varname>bgwriter_lru_multiplier</varname> (<type>floating point</type>)
1822 <primary><varname>bgwriter_lru_multiplier</> configuration parameter</primary>
1827 The number of dirty buffers written in each round is based on the
1828 number of new buffers that have been needed by server processes
1829 during recent rounds. The average recent need is multiplied by
1830 <varname>bgwriter_lru_multiplier</> to arrive at an estimate of the
1831 number of buffers that will be needed during the next round. Dirty
1832 buffers are written until there are that many clean, reusable buffers
1833 available. (However, no more than <varname>bgwriter_lru_maxpages</>
1834 buffers will be written per round.)
1835 Thus, a setting of 1.0 represents a <quote>just in time</> policy
1836 of writing exactly the number of buffers predicted to be needed.
1837 Larger values provide some cushion against spikes in demand,
1838 while smaller values intentionally leave writes to be done by
1841 This parameter can only be set in the <filename>postgresql.conf</>
1842 file or on the server command line.
1849 Smaller values of <varname>bgwriter_lru_maxpages</varname> and
1850 <varname>bgwriter_lru_multiplier</varname> reduce the extra I/O load
1851 caused by the background writer, but make it more likely that server
1852 processes will have to issue writes for themselves, delaying interactive
1857 <sect2 id="runtime-config-resource-async-behavior">
1858 <title>Asynchronous Behavior</title>
1861 <varlistentry id="guc-effective-io-concurrency" xreflabel="effective_io_concurrency">
1862 <term><varname>effective_io_concurrency</varname> (<type>integer</type>)
1864 <primary><varname>effective_io_concurrency</> configuration parameter</primary>
1869 Sets the number of concurrent disk I/O operations that
1870 <productname>PostgreSQL</> expects can be executed
1871 simultaneously. Raising this value will increase the number of I/O
1872 operations that any individual <productname>PostgreSQL</> session
1873 attempts to initiate in parallel. The allowed range is 1 to 1000,
1874 or zero to disable issuance of asynchronous I/O requests. Currently,
1875 this setting only affects bitmap heap scans.
1879 A good starting point for this setting is the number of separate
1880 drives comprising a RAID 0 stripe or RAID 1 mirror being used for the
1881 database. (For RAID 5 the parity drive should not be counted.)
1882 However, if the database is often busy with multiple queries issued in
1883 concurrent sessions, lower values may be sufficient to keep the disk
1884 array busy. A value higher than needed to keep the disks busy will
1885 only result in extra CPU overhead.
1889 For more exotic systems, such as memory-based storage or a RAID array
1890 that is limited by bus bandwidth, the correct value might be the
1891 number of I/O paths available. Some experimentation may be needed
1892 to find the best value.
1896 Asynchronous I/O depends on an effective <function>posix_fadvise</>
1897 function, which some operating systems lack. If the function is not
1898 present then setting this parameter to anything but zero will result
1899 in an error. On some operating systems (e.g., Solaris), the function
1900 is present but does not actually do anything.
1904 The default is 1 on supported systems, otherwise 0. This value can
1905 be overridden for tables in a particular tablespace by setting the
1906 tablespace parameter of the same name (see
1907 <xref linkend="sql-altertablespace">).
1912 <varlistentry id="guc-max-worker-processes" xreflabel="max_worker_processes">
1913 <term><varname>max_worker_processes</varname> (<type>integer</type>)
1915 <primary><varname>max_worker_processes</> configuration parameter</primary>
1920 Sets the maximum number of background processes that the system
1921 can support. This parameter can only be set at server start.
1925 When running a standby server, you must set this parameter to the
1926 same or higher value than on the master server. Otherwise, queries
1927 will not be allowed in the standby server.
1932 <varlistentry id="guc-max-parallel-degree" xreflabel="max_parallel_degree">
1933 <term><varname>max_parallel_degree</varname> (<type>integer</type>)
1935 <primary><varname>max_parallel_degree</> configuration parameter</primary>
1940 Sets the maximum degree of parallelism for an individual parallel
1941 operation. Note that the requested number of workers may not actually
1942 be available at runtime. Parallel workers are taken from the pool
1943 of processes established by <xref linkend="guc-max-worker-processes">.
1951 <sect1 id="runtime-config-wal">
1952 <title>Write Ahead Log</title>
1955 For additional information on tuning these settings,
1956 see <xref linkend="wal-configuration">.
1959 <sect2 id="runtime-config-wal-settings">
1960 <title>Settings</title>
1963 <varlistentry id="guc-wal-level" xreflabel="wal_level">
1964 <term><varname>wal_level</varname> (<type>enum</type>)
1966 <primary><varname>wal_level</> configuration parameter</primary>
1971 <varname>wal_level</> determines how much information is written
1972 to the WAL. The default value is <literal>minimal</>, which writes
1973 only the information needed to recover from a crash or immediate
1974 shutdown. <literal>archive</> adds logging required for WAL archiving;
1975 <literal>hot_standby</> further adds information required to run
1976 read-only queries on a standby server; and, finally
1977 <literal>logical</> adds information necessary to support logical
1978 decoding. Each level includes the information logged at all lower
1979 levels. This parameter can only be set at server start.
1982 In <literal>minimal</> level, WAL-logging of some bulk
1983 operations can be safely skipped, which can make those
1984 operations much faster (see <xref linkend="populate-pitr">).
1985 Operations in which this optimization can be applied include:
1987 <member><command>CREATE TABLE AS</></member>
1988 <member><command>CREATE INDEX</></member>
1989 <member><command>CLUSTER</></member>
1990 <member><command>COPY</> into tables that were created or truncated in the same
1991 transaction</member>
1993 But minimal WAL does not contain enough information to reconstruct the
1994 data from a base backup and the WAL logs, so <literal>archive</> or
1995 higher must be used to enable WAL archiving
1996 (<xref linkend="guc-archive-mode">) and streaming replication.
1999 In <literal>hot_standby</> level, the same information is logged as
2000 with <literal>archive</>, plus information needed to reconstruct
2001 the status of running transactions from the WAL. To enable read-only
2002 queries on a standby server, <varname>wal_level</> must be set to
2003 <literal>hot_standby</> or higher on the primary, and
2004 <xref linkend="guc-hot-standby"> must be enabled in the standby. It is
2005 thought that there is little measurable difference in performance
2006 between using <literal>hot_standby</> and <literal>archive</> levels,
2007 so feedback is welcome if any production impacts are noticeable.
2010 In <literal>logical</> level, the same information is logged as
2011 with <literal>hot_standby</>, plus information needed to allow
2012 extracting logical change sets from the WAL. Using a level of
2013 <literal>logical</> will increase the WAL volume, particularly if many
2014 tables are configured for <literal>REPLICA IDENTITY FULL</literal> and
2015 many <command>UPDATE</> and <command>DELETE</> statements are
2021 <varlistentry id="guc-fsync" xreflabel="fsync">
2022 <term><varname>fsync</varname> (<type>boolean</type>)
2024 <primary><varname>fsync</> configuration parameter</primary>
2029 If this parameter is on, the <productname>PostgreSQL</> server
2030 will try to make sure that updates are physically written to
2031 disk, by issuing <function>fsync()</> system calls or various
2032 equivalent methods (see <xref linkend="guc-wal-sync-method">).
2033 This ensures that the database cluster can recover to a
2034 consistent state after an operating system or hardware crash.
2038 While turning off <varname>fsync</varname> is often a performance
2039 benefit, this can result in unrecoverable data corruption in
2040 the event of a power failure or system crash. Thus it
2041 is only advisable to turn off <varname>fsync</varname> if
2042 you can easily recreate your entire database from external
2047 Examples of safe circumstances for turning off
2048 <varname>fsync</varname> include the initial loading of a new
2049 database cluster from a backup file, using a database cluster
2050 for processing a batch of data after which the database
2051 will be thrown away and recreated,
2052 or for a read-only database clone which
2053 gets recreated frequently and is not used for failover. High
2054 quality hardware alone is not a sufficient justification for
2055 turning off <varname>fsync</varname>.
2059 For reliable recovery when changing <varname>fsync</varname>
2060 off to on, it is necessary to force all modified buffers in the
2061 kernel to durable storage. This can be done while the cluster
2062 is shutdown or while fsync is on by running <command>initdb
2063 --sync-only</command>, running <command>sync</>, unmounting the
2064 file system, or rebooting the server.
2068 In many situations, turning off <xref linkend="guc-synchronous-commit">
2069 for noncritical transactions can provide much of the potential
2070 performance benefit of turning off <varname>fsync</varname>, without
2071 the attendant risks of data corruption.
2075 <varname>fsync</varname> can only be set in the <filename>postgresql.conf</>
2076 file or on the server command line.
2077 If you turn this parameter off, also consider turning off
2078 <xref linkend="guc-full-page-writes">.
2083 <varlistentry id="guc-synchronous-commit" xreflabel="synchronous_commit">
2084 <term><varname>synchronous_commit</varname> (<type>enum</type>)
2086 <primary><varname>synchronous_commit</> configuration parameter</primary>
2091 Specifies whether transaction commit will wait for WAL records
2092 to be written to disk before the command returns a <quote>success</>
2093 indication to the client. Valid values are <literal>on</>,
2094 <literal>remote_write</>, <literal>local</>, and <literal>off</>.
2095 The default, and safe, setting
2096 is <literal>on</>. When <literal>off</>, there can be a delay between
2097 when success is reported to the client and when the transaction is
2098 really guaranteed to be safe against a server crash. (The maximum
2099 delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
2100 <xref linkend="guc-fsync">, setting this parameter to <literal>off</>
2101 does not create any risk of database inconsistency: an operating
2102 system or database crash might
2103 result in some recent allegedly-committed transactions being lost, but
2104 the database state will be just the same as if those transactions had
2105 been aborted cleanly. So, turning <varname>synchronous_commit</> off
2106 can be a useful alternative when performance is more important than
2107 exact certainty about the durability of a transaction. For more
2108 discussion see <xref linkend="wal-async-commit">.
2111 If <xref linkend="guc-synchronous-standby-names"> is set, this
2112 parameter also controls whether or not transaction commits will wait
2113 for the transaction's WAL records to be replicated to the standby
2115 When set to <literal>on</>, commits will wait until a reply
2116 from the current synchronous standby indicates it has received
2117 the commit record of the transaction and flushed it to disk. This
2118 ensures the transaction will not be lost unless both primary and
2119 standby suffer corruption of their database storage.
2120 When set to <literal>remote_write</>, commits will wait
2121 until a reply from the current synchronous standby indicates it has
2122 received the commit record of the transaction and written it out to
2123 the standby's operating system, but the data has not necessarily
2124 reached stable storage on the standby. This setting is sufficient to
2125 ensure data preservation even if the standby instance of
2126 <productname>PostgreSQL</> were to crash, but not if the standby
2127 suffers an operating-system-level crash.
2131 replication is in use, it will normally be sensible either to wait
2132 for both local flush to disk and replication of WAL records, or
2133 to allow the transaction to commit asynchronously. However, the
2134 setting <literal>local</> is available for transactions that
2135 wish to wait for local flush to disk, but not synchronous replication.
2136 If <varname>synchronous_standby_names</> is not set, the settings
2137 <literal>on</>, <literal>remote_write</> and <literal>local</> all
2138 provide the same synchronization level: transaction commits only wait
2139 for local flush to disk.
2142 This parameter can be changed at any time; the behavior for any
2143 one transaction is determined by the setting in effect when it
2144 commits. It is therefore possible, and useful, to have some
2145 transactions commit synchronously and others asynchronously.
2146 For example, to make a single multistatement transaction commit
2147 asynchronously when the default is the opposite, issue <command>SET
2148 LOCAL synchronous_commit TO OFF</> within the transaction.
2153 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
2154 <term><varname>wal_sync_method</varname> (<type>enum</type>)
2156 <primary><varname>wal_sync_method</> configuration parameter</primary>
2161 Method used for forcing WAL updates out to disk.
2162 If <varname>fsync</varname> is off then this setting is irrelevant,
2163 since WAL file updates will not be forced out at all.
2164 Possible values are:
2169 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>)
2174 <literal>fdatasync</> (call <function>fdatasync()</> at each commit)
2179 <literal>fsync</> (call <function>fsync()</> at each commit)
2184 <literal>fsync_writethrough</> (call <function>fsync()</> at each commit, forcing write-through of any disk write cache)
2189 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>)
2194 The <literal>open_</>* options also use <literal>O_DIRECT</> if available.
2195 Not all of these choices are available on all platforms.
2196 The default is the first method in the above list that is supported
2197 by the platform, except that <literal>fdatasync</> is the default on
2198 Linux. The default is not necessarily ideal; it might be
2199 necessary to change this setting or other aspects of your system
2200 configuration in order to create a crash-safe configuration or
2201 achieve optimal performance.
2202 These aspects are discussed in <xref linkend="wal-reliability">.
2203 This parameter can only be set in the <filename>postgresql.conf</>
2204 file or on the server command line.
2209 <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
2210 <term><varname>full_page_writes</varname> (<type>boolean</type>)
2212 <primary><varname>full_page_writes</> configuration parameter</primary>
2217 When this parameter is on, the <productname>PostgreSQL</> server
2218 writes the entire content of each disk page to WAL during the
2219 first modification of that page after a checkpoint.
2220 This is needed because
2221 a page write that is in process during an operating system crash might
2222 be only partially completed, leading to an on-disk page
2223 that contains a mix of old and new data. The row-level change data
2224 normally stored in WAL will not be enough to completely restore
2225 such a page during post-crash recovery. Storing the full page image
2226 guarantees that the page can be correctly restored, but at the price
2227 of increasing the amount of data that must be written to WAL.
2228 (Because WAL replay always starts from a checkpoint, it is sufficient
2229 to do this during the first change of each page after a checkpoint.
2230 Therefore, one way to reduce the cost of full-page writes is to
2231 increase the checkpoint interval parameters.)
2235 Turning this parameter off speeds normal operation, but
2236 might lead to either unrecoverable data corruption, or silent
2237 data corruption, after a system failure. The risks are similar to turning off
2238 <varname>fsync</varname>, though smaller, and it should be turned off
2239 only based on the same circumstances recommended for that parameter.
2243 Turning off this parameter does not affect use of
2244 WAL archiving for point-in-time recovery (PITR)
2245 (see <xref linkend="continuous-archiving">).
2249 This parameter can only be set in the <filename>postgresql.conf</>
2250 file or on the server command line.
2251 The default is <literal>on</>.
2256 <varlistentry id="guc-wal-log-hints" xreflabel="wal_log_hints">
2257 <term><varname>wal_log_hints</varname> (<type>boolean</type>)
2259 <primary><varname>wal_log_hints</> configuration parameter</primary>
2264 When this parameter is <literal>on</>, the <productname>PostgreSQL</>
2265 server writes the entire content of each disk page to WAL during the
2266 first modification of that page after a checkpoint, even for
2267 non-critical modifications of so-called hint bits.
2271 If data checksums are enabled, hint bit updates are always WAL-logged
2272 and this setting is ignored. You can use this setting to test how much
2273 extra WAL-logging would occur if your database had data checksums
2278 This parameter can only be set at server start. The default value is <literal>off</>.
2283 <varlistentry id="guc-wal-compression" xreflabel="wal_compression">
2284 <term><varname>wal_compression</varname> (<type>boolean</type>)
2286 <primary><varname>wal_compression</> configuration parameter</primary>
2291 When this parameter is <literal>on</>, the <productname>PostgreSQL</>
2292 server compresses a full page image written to WAL when
2293 <xref linkend="guc-full-page-writes"> is on or during a base backup.
2294 A compressed page image will be decompressed during WAL replay.
2295 The default value is <literal>off</>.
2296 Only superusers can change this setting.
2300 Turning this parameter on can reduce the WAL volume without
2301 increasing the risk of unrecoverable data corruption,
2302 but at the cost of some extra CPU spent on the compression during
2303 WAL logging and on the decompression during WAL replay.
2308 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
2309 <term><varname>wal_buffers</varname> (<type>integer</type>)
2311 <primary><varname>wal_buffers</> configuration parameter</primary>
2316 The amount of shared memory used for WAL data that has not yet been
2317 written to disk. The default setting of -1 selects a size equal to
2318 1/32nd (about 3%) of <xref linkend="guc-shared-buffers">, but not less
2319 than <literal>64kB</literal> nor more than the size of one WAL
2320 segment, typically <literal>16MB</literal>. This value can be set
2321 manually if the automatic choice is too large or too small,
2322 but any positive value less than <literal>32kB</literal> will be
2323 treated as <literal>32kB</literal>.
2324 This parameter can only be set at server start.
2328 The contents of the WAL buffers are written out to disk at every
2329 transaction commit, so extremely large values are unlikely to
2330 provide a significant benefit. However, setting this value to at
2331 least a few megabytes can improve write performance on a busy
2332 server where many clients are committing at once. The auto-tuning
2333 selected by the default setting of -1 should give reasonable
2334 results in most cases.
2340 <varlistentry id="guc-wal-writer-delay" xreflabel="wal_writer_delay">
2341 <term><varname>wal_writer_delay</varname> (<type>integer</type>)
2343 <primary><varname>wal_writer_delay</> configuration parameter</primary>
2348 Specifies the delay between activity rounds for the WAL writer.
2349 In each round the writer will flush WAL to disk. It then sleeps for
2350 <varname>wal_writer_delay</> milliseconds, and repeats. The default
2351 value is 200 milliseconds (<literal>200ms</>). Note that on many
2352 systems, the effective resolution of sleep delays is 10 milliseconds;
2353 setting <varname>wal_writer_delay</> to a value that is not a multiple
2354 of 10 might have the same results as setting it to the next higher
2355 multiple of 10. This parameter can only be set in the
2356 <filename>postgresql.conf</> file or on the server command line.
2361 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
2362 <term><varname>commit_delay</varname> (<type>integer</type>)
2364 <primary><varname>commit_delay</> configuration parameter</primary>
2369 <varname>commit_delay</varname> adds a time delay, measured in
2370 microseconds, before a WAL flush is initiated. This can improve
2371 group commit throughput by allowing a larger number of transactions
2372 to commit via a single WAL flush, if system load is high enough
2373 that additional transactions become ready to commit within the
2374 given interval. However, it also increases latency by up to
2375 <varname>commit_delay</varname> microseconds for each WAL
2376 flush. Because the delay is just wasted if no other transactions
2377 become ready to commit, a delay is only performed if at least
2378 <varname>commit_siblings</varname> other transactions are active
2379 when a flush is about to be initiated. Also, no delays are
2380 performed if <varname>fsync</varname> is disabled.
2381 The default <varname>commit_delay</> is zero (no delay).
2382 Only superusers can change this setting.
2385 In <productname>PostgreSQL</> releases prior to 9.3,
2386 <varname>commit_delay</varname> behaved differently and was much
2387 less effective: it affected only commits, rather than all WAL flushes,
2388 and waited for the entire configured delay even if the WAL flush
2389 was completed sooner. Beginning in <productname>PostgreSQL</> 9.3,
2390 the first process that becomes ready to flush waits for the configured
2391 interval, while subsequent processes wait only until the leader
2392 completes the flush operation.
2397 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
2398 <term><varname>commit_siblings</varname> (<type>integer</type>)
2400 <primary><varname>commit_siblings</> configuration parameter</primary>
2405 Minimum number of concurrent open transactions to require
2406 before performing the <varname>commit_delay</> delay. A larger
2407 value makes it more probable that at least one other
2408 transaction will become ready to commit during the delay
2409 interval. The default is five transactions.
2416 <sect2 id="runtime-config-wal-checkpoints">
2417 <title>Checkpoints</title>
2420 <varlistentry id="guc-max-wal-size" xreflabel="max_wal_size">
2421 <term><varname>max_wal_size</varname> (<type>integer</type>)
2423 <primary><varname>max_wal_size</> configuration parameter</primary>
2428 Maximum size to let the WAL grow to between automatic WAL
2429 checkpoints. This is a soft limit; WAL size can exceed
2430 <varname>max_wal_size</> under special circumstances, like
2431 under heavy load, a failing <varname>archive_command</>, or a high
2432 <varname>wal_keep_segments</> setting. The default is 1 GB.
2433 Increasing this parameter can increase the amount of time needed for
2435 This parameter can only be set in the <filename>postgresql.conf</>
2436 file or on the server command line.
2441 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
2442 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)
2444 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
2449 Maximum time between automatic WAL checkpoints, in seconds.
2450 The valid range is between 30 seconds and one hour.
2451 The default is five minutes (<literal>5min</>).
2452 Increasing this parameter can increase the amount of time needed
2454 This parameter can only be set in the <filename>postgresql.conf</>
2455 file or on the server command line.
2460 <varlistentry id="guc-checkpoint-completion-target" xreflabel="checkpoint_completion_target">
2461 <term><varname>checkpoint_completion_target</varname> (<type>floating point</type>)
2463 <primary><varname>checkpoint_completion_target</> configuration parameter</primary>
2468 Specifies the target of checkpoint completion, as a fraction of
2469 total time between checkpoints. The default is 0.5.
2470 This parameter can only be set in the <filename>postgresql.conf</>
2471 file or on the server command line.
2476 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
2477 <term><varname>checkpoint_warning</varname> (<type>integer</type>)
2479 <primary><varname>checkpoint_warning</> configuration parameter</primary>
2484 Write a message to the server log if checkpoints caused by
2485 the filling of checkpoint segment files happen closer together
2486 than this many seconds (which suggests that
2487 <varname>max_wal_size</> ought to be raised). The default is
2488 30 seconds (<literal>30s</>). Zero disables the warning.
2489 No warnings will be generated if <varname>checkpoint_timeout</varname>
2490 is less than <varname>checkpoint_warning</varname>.
2491 This parameter can only be set in the <filename>postgresql.conf</>
2492 file or on the server command line.
2497 <varlistentry id="guc-min-wal-size" xreflabel="min_wal_size">
2498 <term><varname>min_wal_size</varname> (<type>integer</type>)
2500 <primary><varname>min_wal_size</> configuration parameter</primary>
2505 As long as WAL disk usage stays below this setting, old WAL files are
2506 always recycled for future use at a checkpoint, rather than removed.
2507 This can be used to ensure that enough WAL space is reserved to
2508 handle spikes in WAL usage, for example when running large batch
2509 jobs. The default is 80 MB.
2510 This parameter can only be set in the <filename>postgresql.conf</>
2511 file or on the server command line.
2518 <sect2 id="runtime-config-wal-archiving">
2519 <title>Archiving</title>
2522 <varlistentry id="guc-archive-mode" xreflabel="archive_mode">
2523 <term><varname>archive_mode</varname> (<type>enum</type>)
2525 <primary><varname>archive_mode</> configuration parameter</primary>
2530 When <varname>archive_mode</> is enabled, completed WAL segments
2531 are sent to archive storage by setting
2532 <xref linkend="guc-archive-command">. In addition to <literal>off</>,
2533 to disable, there are two modes: <literal>on</>, and
2534 <literal>always</>. During normal operation, there is no
2535 difference between the two modes, but when set to <literal>always</>
2536 the WAL archiver is enabled also during archive recovery or standby
2537 mode. In <literal>always</> mode, all files restored from the archive
2538 or streamed with streaming replication will be archived (again). See
2539 <xref linkend="continuous-archiving-in-standby"> for details.
2542 <varname>archive_mode</> and <varname>archive_command</> are
2543 separate variables so that <varname>archive_command</> can be
2544 changed without leaving archiving mode.
2545 This parameter can only be set at server start.
2546 <varname>archive_mode</> cannot be enabled when
2547 <varname>wal_level</> is set to <literal>minimal</>.
2552 <varlistentry id="guc-archive-command" xreflabel="archive_command">
2553 <term><varname>archive_command</varname> (<type>string</type>)
2555 <primary><varname>archive_command</> configuration parameter</primary>
2560 The local shell command to execute to archive a completed WAL file
2561 segment. Any <literal>%p</> in the string is
2562 replaced by the path name of the file to archive, and any
2563 <literal>%f</> is replaced by only the file name.
2564 (The path name is relative to the working directory of the server,
2565 i.e., the cluster's data directory.)
2566 Use <literal>%%</> to embed an actual <literal>%</> character in the
2567 command. It is important for the command to return a zero
2568 exit status only if it succeeds. For more information see
2569 <xref linkend="backup-archiving-wal">.
2572 This parameter can only be set in the <filename>postgresql.conf</>
2573 file or on the server command line. It is ignored unless
2574 <varname>archive_mode</> was enabled at server start.
2575 If <varname>archive_command</> is an empty string (the default) while
2576 <varname>archive_mode</> is enabled, WAL archiving is temporarily
2577 disabled, but the server continues to accumulate WAL segment files in
2578 the expectation that a command will soon be provided. Setting
2579 <varname>archive_command</> to a command that does nothing but
2580 return true, e.g. <literal>/bin/true</> (<literal>REM</> on
2581 Windows), effectively disables
2582 archiving, but also breaks the chain of WAL files needed for
2583 archive recovery, so it should only be used in unusual circumstances.
2588 <varlistentry id="guc-archive-timeout" xreflabel="archive_timeout">
2589 <term><varname>archive_timeout</varname> (<type>integer</type>)
2591 <primary><varname>archive_timeout</> configuration parameter</primary>
2596 The <xref linkend="guc-archive-command"> is only invoked for
2597 completed WAL segments. Hence, if your server generates little WAL
2598 traffic (or has slack periods where it does so), there could be a
2599 long delay between the completion of a transaction and its safe
2600 recording in archive storage. To limit how old unarchived
2601 data can be, you can set <varname>archive_timeout</> to force the
2602 server to switch to a new WAL segment file periodically. When this
2603 parameter is greater than zero, the server will switch to a new
2604 segment file whenever this many seconds have elapsed since the last
2605 segment file switch, and there has been any database activity,
2606 including a single checkpoint. (Increasing
2607 <varname>checkpoint_timeout</> will reduce unnecessary
2608 checkpoints on an idle system.)
2609 Note that archived files that are closed early
2610 due to a forced switch are still the same length as completely full
2611 files. Therefore, it is unwise to use a very short
2612 <varname>archive_timeout</> — it will bloat your archive
2613 storage. <varname>archive_timeout</> settings of a minute or so are
2614 usually reasonable. You should consider using streaming replication,
2615 instead of archiving, if you want data to be copied off the master
2616 server more quickly than that.
2617 This parameter can only be set in the
2618 <filename>postgresql.conf</> file or on the server command line.
2628 <sect1 id="runtime-config-replication">
2629 <title>Replication</title>
2632 These settings control the behavior of the built-in
2633 <firstterm>streaming replication</> feature (see
2634 <xref linkend="streaming-replication">). Servers will be either a
2635 Master or a Standby server. Masters can send data, while Standby(s)
2636 are always receivers of replicated data. When cascading replication
2637 (see <xref linkend="cascading-replication">) is used, Standby server(s)
2638 can also be senders, as well as receivers.
2639 Parameters are mainly for Sending and Standby servers, though some
2640 parameters have meaning only on the Master server. Settings may vary
2641 across the cluster without problems if that is required.
2644 <sect2 id="runtime-config-replication-sender">
2645 <title>Sending Server(s)</title>
2648 These parameters can be set on any server that is
2649 to send replication data to one or more standby servers.
2650 The master is always a sending server, so these parameters must
2651 always be set on the master.
2652 The role and meaning of these parameters does not change after a
2653 standby becomes the master.
2657 <varlistentry id="guc-max-wal-senders" xreflabel="max_wal_senders">
2658 <term><varname>max_wal_senders</varname> (<type>integer</type>)
2660 <primary><varname>max_wal_senders</> configuration parameter</primary>
2665 Specifies the maximum number of concurrent connections from
2666 standby servers or streaming base backup clients (i.e., the
2667 maximum number of simultaneously running WAL sender
2668 processes). The default is zero, meaning replication is
2669 disabled. WAL sender processes count towards the total number
2670 of connections, so the parameter cannot be set higher than
2671 <xref linkend="guc-max-connections">. Abrupt streaming client
2672 disconnection might cause an orphaned connection slot until
2673 a timeout is reached, so this parameter should be set slightly
2674 higher than the maximum number of expected clients so disconnected
2675 clients can immediately reconnect. This parameter can only
2676 be set at server start. <varname>wal_level</> must be set to
2677 <literal>archive</> or higher to allow connections from standby
2683 <varlistentry id="guc-max-replication-slots" xreflabel="max_replication_slots">
2684 <term><varname>max_replication_slots</varname> (<type>integer</type>)
2686 <primary><varname>max_replication_slots</> configuration parameter</primary>
2691 Specifies the maximum number of replication slots
2692 (see <xref linkend="streaming-replication-slots">) that the server
2693 can support. The default is zero. This parameter can only be set at
2695 <varname>wal_level</varname> must be set
2696 to <literal>archive</literal> or higher to allow replication slots to
2697 be used. Setting it to a lower value than the number of currently
2698 existing replication slots will prevent the server from starting.
2703 <varlistentry id="guc-wal-keep-segments" xreflabel="wal_keep_segments">
2704 <term><varname>wal_keep_segments</varname> (<type>integer</type>)
2706 <primary><varname>wal_keep_segments</> configuration parameter</primary>
2711 Specifies the minimum number of past log file segments kept in the
2712 <filename>pg_xlog</>
2713 directory, in case a standby server needs to fetch them for streaming
2714 replication. Each segment is normally 16 megabytes. If a standby
2715 server connected to the sending server falls behind by more than
2716 <varname>wal_keep_segments</> segments, the sending server might remove
2717 a WAL segment still needed by the standby, in which case the
2718 replication connection will be terminated. Downstream connections
2719 will also eventually fail as a result. (However, the standby
2720 server can recover by fetching the segment from archive, if WAL
2721 archiving is in use.)
2725 This sets only the minimum number of segments retained in
2726 <filename>pg_xlog</>; the system might need to retain more segments
2727 for WAL archival or to recover from a checkpoint. If
2728 <varname>wal_keep_segments</> is zero (the default), the system
2729 doesn't keep any extra segments for standby purposes, so the number
2730 of old WAL segments available to standby servers is a function of
2731 the location of the previous checkpoint and status of WAL
2733 This parameter can only be set in the
2734 <filename>postgresql.conf</> file or on the server command line.
2739 <varlistentry id="guc-wal-sender-timeout" xreflabel="wal_sender_timeout">
2740 <term><varname>wal_sender_timeout</varname> (<type>integer</type>)
2742 <primary><varname>wal_sender_timeout</> configuration parameter</primary>
2747 Terminate replication connections that are inactive longer
2748 than the specified number of milliseconds. This is useful for
2749 the sending server to detect a standby crash or network outage.
2750 A value of zero disables the timeout mechanism. This parameter
2752 the <filename>postgresql.conf</> file or on the server command line.
2753 The default value is 60 seconds.
2758 <varlistentry id="guc-track-commit-timestamp" xreflabel="track_commit_timestamp">
2759 <term><varname>track_commit_timestamp</varname> (<type>bool</type>)
2761 <primary><varname>track_commit_timestamp</> configuration parameter</primary>
2766 Record commit time of transactions. This parameter
2767 can only be set in <filename>postgresql.conf</> file or on the server
2768 command line. The default value is <literal>off</literal>.
2776 <sect2 id="runtime-config-replication-master">
2777 <title>Master Server</title>
2780 These parameters can be set on the master/primary server that is
2781 to send replication data to one or more standby servers.
2782 Note that in addition to these parameters,
2783 <xref linkend="guc-wal-level"> must be set appropriately on the master
2784 server, and optionally WAL archiving can be enabled as
2785 well (see <xref linkend="runtime-config-wal-archiving">).
2786 The values of these parameters on standby servers are irrelevant,
2787 although you may wish to set them there in preparation for the
2788 possibility of a standby becoming the master.
2793 <varlistentry id="guc-synchronous-standby-names" xreflabel="synchronous_standby_names">
2794 <term><varname>synchronous_standby_names</varname> (<type>string</type>)
2796 <primary><varname>synchronous_standby_names</> configuration parameter</primary>
2801 Specifies a comma-separated list of standby names that can support
2802 <firstterm>synchronous replication</>, as described in
2803 <xref linkend="synchronous-replication">.
2804 At any one time there will be at most one active synchronous standby;
2805 transactions waiting for commit will be allowed to proceed after
2806 this standby server confirms receipt of their data.
2807 The synchronous standby will be the first standby named in this list
2808 that is both currently connected and streaming data in real-time
2809 (as shown by a state of <literal>streaming</literal> in the
2810 <link linkend="monitoring-stats-views-table">
2811 <literal>pg_stat_replication</></link> view).
2812 Other standby servers appearing later in this list represent potential
2813 synchronous standbys.
2814 If the current synchronous standby disconnects for whatever reason,
2815 it will be replaced immediately with the next-highest-priority standby.
2816 Specifying more than one standby name can allow very high availability.
2819 The name of a standby server for this purpose is the
2820 <varname>application_name</> setting of the standby, as set in the
2821 <varname>primary_conninfo</> of the standby's WAL receiver. There is
2822 no mechanism to enforce uniqueness. In case of duplicates one of the
2823 matching standbys will be chosen to be the synchronous standby, though
2824 exactly which one is indeterminate.
2825 The special entry <literal>*</> matches any
2826 <varname>application_name</>, including the default application name
2827 of <literal>walreceiver</>.
2830 If no synchronous standby names are specified here, then synchronous
2831 replication is not enabled and transaction commits will not wait for
2832 replication. This is the default configuration. Even when
2833 synchronous replication is enabled, individual transactions can be
2834 configured not to wait for replication by setting the
2835 <xref linkend="guc-synchronous-commit"> parameter to
2836 <literal>local</> or <literal>off</>.
2839 This parameter can only be set in the <filename>postgresql.conf</>
2840 file or on the server command line.
2845 <varlistentry id="guc-vacuum-defer-cleanup-age" xreflabel="vacuum_defer_cleanup_age">
2846 <term><varname>vacuum_defer_cleanup_age</varname> (<type>integer</type>)
2848 <primary><varname>vacuum_defer_cleanup_age</> configuration parameter</primary>
2853 Specifies the number of transactions by which <command>VACUUM</> and
2854 <acronym>HOT</> updates will defer cleanup of dead row versions. The
2855 default is zero transactions, meaning that dead row versions can be
2856 removed as soon as possible, that is, as soon as they are no longer
2857 visible to any open transaction. You may wish to set this to a
2858 non-zero value on a primary server that is supporting hot standby
2859 servers, as described in <xref linkend="hot-standby">. This allows
2860 more time for queries on the standby to complete without incurring
2861 conflicts due to early cleanup of rows. However, since the value
2862 is measured in terms of number of write transactions occurring on the
2863 primary server, it is difficult to predict just how much additional
2864 grace time will be made available to standby queries.
2865 This parameter can only be set in the <filename>postgresql.conf</>
2866 file or on the server command line.
2869 You should also consider setting <varname>hot_standby_feedback</>
2870 on standby server(s) as an alternative to using this parameter.
2878 <sect2 id="runtime-config-replication-standby">
2879 <title>Standby Servers</title>
2882 These settings control the behavior of a standby server that is
2883 to receive replication data. Their values on the master server
2889 <varlistentry id="guc-hot-standby" xreflabel="hot_standby">
2890 <term><varname>hot_standby</varname> (<type>boolean</type>)
2892 <primary><varname>hot_standby</> configuration parameter</primary>
2897 Specifies whether or not you can connect and run queries during
2898 recovery, as described in <xref linkend="hot-standby">.
2899 The default value is <literal>off</literal>.
2900 This parameter can only be set at server start. It only has effect
2901 during archive recovery or in standby mode.
2906 <varlistentry id="guc-max-standby-archive-delay" xreflabel="max_standby_archive_delay">
2907 <term><varname>max_standby_archive_delay</varname> (<type>integer</type>)
2909 <primary><varname>max_standby_archive_delay</> configuration parameter</primary>
2914 When Hot Standby is active, this parameter determines how long the
2915 standby server should wait before canceling standby queries that
2916 conflict with about-to-be-applied WAL entries, as described in
2917 <xref linkend="hot-standby-conflict">.
2918 <varname>max_standby_archive_delay</> applies when WAL data is
2919 being read from WAL archive (and is therefore not current).
2920 The default is 30 seconds. Units are milliseconds if not specified.
2921 A value of -1 allows the standby to wait forever for conflicting
2922 queries to complete.
2923 This parameter can only be set in the <filename>postgresql.conf</>
2924 file or on the server command line.
2927 Note that <varname>max_standby_archive_delay</> is not the same as the
2928 maximum length of time a query can run before cancellation; rather it
2929 is the maximum total time allowed to apply any one WAL segment's data.
2930 Thus, if one query has resulted in significant delay earlier in the
2931 WAL segment, subsequent conflicting queries will have much less grace
2937 <varlistentry id="guc-max-standby-streaming-delay" xreflabel="max_standby_streaming_delay">
2938 <term><varname>max_standby_streaming_delay</varname> (<type>integer</type>)
2940 <primary><varname>max_standby_streaming_delay</> configuration parameter</primary>
2945 When Hot Standby is active, this parameter determines how long the
2946 standby server should wait before canceling standby queries that
2947 conflict with about-to-be-applied WAL entries, as described in
2948 <xref linkend="hot-standby-conflict">.
2949 <varname>max_standby_streaming_delay</> applies when WAL data is
2950 being received via streaming replication.
2951 The default is 30 seconds. Units are milliseconds if not specified.
2952 A value of -1 allows the standby to wait forever for conflicting
2953 queries to complete.
2954 This parameter can only be set in the <filename>postgresql.conf</>
2955 file or on the server command line.
2958 Note that <varname>max_standby_streaming_delay</> is not the same as
2959 the maximum length of time a query can run before cancellation; rather
2960 it is the maximum total time allowed to apply WAL data once it has
2961 been received from the primary server. Thus, if one query has
2962 resulted in significant delay, subsequent conflicting queries will
2963 have much less grace time until the standby server has caught up
2969 <varlistentry id="guc-wal-receiver-status-interval" xreflabel="wal_receiver_status_interval">
2970 <term><varname>wal_receiver_status_interval</varname> (<type>integer</type>)
2972 <primary><varname>wal_receiver_status_interval</> configuration parameter</primary>
2977 Specifies the minimum frequency for the WAL receiver
2978 process on the standby to send information about replication progress
2979 to the primary or upstream standby, where it can be seen using the
2980 <link linkend="monitoring-stats-views-table">
2981 <literal>pg_stat_replication</></link> view. The standby will report
2982 the last transaction log position it has written, the last position it
2983 has flushed to disk, and the last position it has applied.
2985 value is the maximum interval, in seconds, between reports. Updates are
2986 sent each time the write or flush positions change, or at least as
2987 often as specified by this parameter. Thus, the apply position may
2988 lag slightly behind the true position. Setting this parameter to zero
2989 disables status updates completely. This parameter can only be set in
2990 the <filename>postgresql.conf</> file or on the server command line.
2991 The default value is 10 seconds.
2996 <varlistentry id="guc-hot-standby-feedback" xreflabel="hot_standby_feedback">
2997 <term><varname>hot_standby_feedback</varname> (<type>boolean</type>)
2999 <primary><varname>hot_standby_feedback</> configuration parameter</primary>
3004 Specifies whether or not a hot standby will send feedback to the primary
3006 about queries currently executing on the standby. This parameter can
3007 be used to eliminate query cancels caused by cleanup records, but
3008 can cause database bloat on the primary for some workloads.
3009 Feedback messages will not be sent more frequently than once per
3010 <varname>wal_receiver_status_interval</>. The default value is
3011 <literal>off</literal>. This parameter can only be set in the
3012 <filename>postgresql.conf</> file or on the server command line.
3015 If cascaded replication is in use the feedback is passed upstream
3016 until it eventually reaches the primary. Standbys make no other use
3017 of feedback they receive other than to pass upstream.
3022 <varlistentry id="guc-wal-receiver-timeout" xreflabel="wal_receiver_timeout">
3023 <term><varname>wal_receiver_timeout</varname> (<type>integer</type>)
3025 <primary><varname>wal_receiver_timeout</> configuration parameter</primary>
3030 Terminate replication connections that are inactive longer
3031 than the specified number of milliseconds. This is useful for
3032 the receiving standby server to detect a primary node crash or network
3034 A value of zero disables the timeout mechanism. This parameter
3036 the <filename>postgresql.conf</> file or on the server command line.
3037 The default value is 60 seconds.
3042 <varlistentry id="guc-wal-retrieve-retry-interval" xreflabel="wal_retrieve_retry_interval">
3043 <term><varname>wal_retrieve_retry_interval</varname> (<type>integer</type>)
3045 <primary><varname>wal_retrieve_retry_interval</> configuration parameter</primary>
3050 Specify how long the standby server should wait when WAL data is not
3051 available from any sources (streaming replication,
3052 local <filename>pg_xlog</> or WAL archive) before retrying to
3053 retrieve WAL data. This parameter can only be set in the
3054 <filename>postgresql.conf</> file or on the server command line.
3055 The default value is 5 seconds. Units are milliseconds if not specified.
3064 <sect1 id="runtime-config-query">
3065 <title>Query Planning</title>
3067 <sect2 id="runtime-config-query-enable">
3068 <title>Planner Method Configuration</title>
3071 These configuration parameters provide a crude method of
3072 influencing the query plans chosen by the query optimizer. If
3073 the default plan chosen by the optimizer for a particular query
3074 is not optimal, a <emphasis>temporary</> solution is to use one
3075 of these configuration parameters to force the optimizer to
3076 choose a different plan.
3077 Better ways to improve the quality of the
3078 plans chosen by the optimizer include adjusting the planer cost
3079 constants (see <xref linkend="runtime-config-query-constants">),
3080 running <xref linkend="sql-analyze"> manually, increasing
3081 the value of the <xref
3082 linkend="guc-default-statistics-target"> configuration parameter,
3083 and increasing the amount of statistics collected for
3084 specific columns using <command>ALTER TABLE SET
3085 STATISTICS</command>.
3089 <varlistentry id="guc-enable-bitmapscan" xreflabel="enable_bitmapscan">
3090 <term><varname>enable_bitmapscan</varname> (<type>boolean</type>)
3092 <primary>bitmap scan</primary>
3095 <primary><varname>enable_bitmapscan</> configuration parameter</primary>
3100 Enables or disables the query planner's use of bitmap-scan plan
3101 types. The default is <literal>on</>.
3106 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
3107 <term><varname>enable_hashagg</varname> (<type>boolean</type>)
3109 <primary><varname>enable_hashagg</> configuration parameter</primary>
3114 Enables or disables the query planner's use of hashed
3115 aggregation plan types. The default is <literal>on</>.
3120 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
3121 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)
3123 <primary><varname>enable_hashjoin</> configuration parameter</primary>
3128 Enables or disables the query planner's use of hash-join plan
3129 types. The default is <literal>on</>.
3134 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
3135 <term><varname>enable_indexscan</varname> (<type>boolean</type>)
3137 <primary>index scan</primary>
3140 <primary><varname>enable_indexscan</> configuration parameter</primary>
3145 Enables or disables the query planner's use of index-scan plan
3146 types. The default is <literal>on</>.
3151 <varlistentry id="guc-enable-indexonlyscan" xreflabel="enable_indexonlyscan">
3152 <term><varname>enable_indexonlyscan</varname> (<type>boolean</type>)
3154 <primary>index-only scan</primary>
3157 <primary><varname>enable_indexonlyscan</> configuration parameter</primary>
3162 Enables or disables the query planner's use of index-only-scan plan
3163 types. The default is <literal>on</>.
3168 <varlistentry id="guc-enable-material" xreflabel="enable_material">
3169 <term><varname>enable_material</varname> (<type>boolean</type>)
3171 <primary><varname>enable_material</> configuration parameter</primary>
3176 Enables or disables the query planner's use of materialization.
3177 It is impossible to suppress materialization entirely,
3178 but turning this variable off prevents the planner from inserting
3179 materialize nodes except in cases where it is required for correctness.
3180 The default is <literal>on</>.
3185 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
3186 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)
3188 <primary><varname>enable_mergejoin</> configuration parameter</primary>
3193 Enables or disables the query planner's use of merge-join plan
3194 types. The default is <literal>on</>.
3199 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
3200 <term><varname>enable_nestloop</varname> (<type>boolean</type>)
3202 <primary><varname>enable_nestloop</> configuration parameter</primary>
3207 Enables or disables the query planner's use of nested-loop join
3208 plans. It is impossible to suppress nested-loop joins entirely,
3209 but turning this variable off discourages the planner from using
3210 one if there are other methods available. The default is
3216 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
3217 <term><varname>enable_seqscan</varname> (<type>boolean</type>)
3219 <primary>sequential scan</primary>
3222 <primary><varname>enable_seqscan</> configuration parameter</primary>
3227 Enables or disables the query planner's use of sequential scan
3228 plan types. It is impossible to suppress sequential scans
3229 entirely, but turning this variable off discourages the planner
3230 from using one if there are other methods available. The
3231 default is <literal>on</>.
3236 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
3237 <term><varname>enable_sort</varname> (<type>boolean</type>)
3239 <primary><varname>enable_sort</> configuration parameter</primary>
3244 Enables or disables the query planner's use of explicit sort
3245 steps. It is impossible to suppress explicit sorts entirely,
3246 but turning this variable off discourages the planner from
3247 using one if there are other methods available. The default
3253 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
3254 <term><varname>enable_tidscan</varname> (<type>boolean</type>)
3256 <primary><varname>enable_tidscan</> configuration parameter</primary>
3261 Enables or disables the query planner's use of <acronym>TID</>
3262 scan plan types. The default is <literal>on</>.
3269 <sect2 id="runtime-config-query-constants">
3270 <title>Planner Cost Constants</title>
3273 The <firstterm>cost</> variables described in this section are measured
3274 on an arbitrary scale. Only their relative values matter, hence
3275 scaling them all up or down by the same factor will result in no change
3276 in the planner's choices. By default, these cost variables are based on
3277 the cost of sequential page fetches; that is,
3278 <varname>seq_page_cost</> is conventionally set to <literal>1.0</>
3279 and the other cost variables are set with reference to that. But
3280 you can use a different scale if you prefer, such as actual execution
3281 times in milliseconds on a particular machine.
3286 Unfortunately, there is no well-defined method for determining ideal
3287 values for the cost variables. They are best treated as averages over
3288 the entire mix of queries that a particular installation will receive. This
3289 means that changing them on the basis of just a few experiments is very
3296 <varlistentry id="guc-seq-page-cost" xreflabel="seq_page_cost">
3297 <term><varname>seq_page_cost</varname> (<type>floating point</type>)
3299 <primary><varname>seq_page_cost</> configuration parameter</primary>
3304 Sets the planner's estimate of the cost of a disk page fetch
3305 that is part of a series of sequential fetches. The default is 1.0.
3306 This value can be overridden for tables and indexes in a particular
3307 tablespace by setting the tablespace parameter of the same name
3308 (see <xref linkend="sql-altertablespace">).
3313 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
3314 <term><varname>random_page_cost</varname> (<type>floating point</type>)
3316 <primary><varname>random_page_cost</> configuration parameter</primary>
3321 Sets the planner's estimate of the cost of a
3322 non-sequentially-fetched disk page. The default is 4.0.
3323 This value can be overridden for tables and indexes in a particular
3324 tablespace by setting the tablespace parameter of the same name
3325 (see <xref linkend="sql-altertablespace">).
3329 Reducing this value relative to <varname>seq_page_cost</>
3330 will cause the system to prefer index scans; raising it will
3331 make index scans look relatively more expensive. You can raise
3332 or lower both values together to change the importance of disk I/O
3333 costs relative to CPU costs, which are described by the following
3338 Random access to mechanical disk storage is normally much more expensive
3339 than four times sequential access. However, a lower default is used
3340 (4.0) because the majority of random accesses to disk, such as indexed
3341 reads, are assumed to be in cache. The default value can be thought of
3342 as modeling random access as 40 times slower than sequential, while
3343 expecting 90% of random reads to be cached.
3347 If you believe a 90% cache rate is an incorrect assumption
3348 for your workload, you can increase random_page_cost to better
3349 reflect the true cost of random storage reads. Correspondingly,
3350 if your data is likely to be completely in cache, such as when
3351 the database is smaller than the total server memory, decreasing
3352 random_page_cost can be appropriate. Storage that has a low random
3353 read cost relative to sequential, e.g. solid-state drives, might
3354 also be better modeled with a lower value for random_page_cost.
3359 Although the system will let you set <varname>random_page_cost</> to
3360 less than <varname>seq_page_cost</>, it is not physically sensible
3361 to do so. However, setting them equal makes sense if the database
3362 is entirely cached in RAM, since in that case there is no penalty
3363 for touching pages out of sequence. Also, in a heavily-cached
3364 database you should lower both values relative to the CPU parameters,
3365 since the cost of fetching a page already in RAM is much smaller
3366 than it would normally be.
3372 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
3373 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)
3375 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
3380 Sets the planner's estimate of the cost of processing
3381 each row during a query.
3382 The default is 0.01.
3387 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
3388 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)
3390 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
3395 Sets the planner's estimate of the cost of processing
3396 each index entry during an index scan.
3397 The default is 0.005.
3402 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
3403 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)
3405 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
3410 Sets the planner's estimate of the cost of processing each
3411 operator or function executed during a query.
3412 The default is 0.0025.
3417 <varlistentry id="parallel-tuple-cost" xreflabel="parallel_tuple_cost">
3418 <term><varname>parallel_tuple_cost</varname> (<type>floating point</type>)
3420 <primary><varname>parallel_tuple_cost</> configuration parameter</primary>
3425 Sets the planner's estimate of the cost of transferring a tuple
3426 from a parallel worker process to another process.
3432 <varlistentry id="parallel-setup-cost" xreflabel="parallel_setup_cost">
3433 <term><varname>parallel_setup_cost</varname> (<type>floating point</type>)
3435 <primary><varname>parallel_setup_cost</> configuration parameter</primary>
3440 Sets the planner's estimate of the cost of launching parallel worker
3442 The default is 1000.
3447 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
3448 <term><varname>effective_cache_size</varname> (<type>integer</type>)
3450 <primary><varname>effective_cache_size</> configuration parameter</primary>
3455 Sets the planner's assumption about the effective size of the
3456 disk cache that is available to a single query. This is
3457 factored into estimates of the cost of using an index; a
3458 higher value makes it more likely index scans will be used, a
3459 lower value makes it more likely sequential scans will be
3460 used. When setting this parameter you should consider both
3461 <productname>PostgreSQL</productname>'s shared buffers and the
3462 portion of the kernel's disk cache that will be used for
3463 <productname>PostgreSQL</productname> data files. Also, take
3464 into account the expected number of concurrent queries on different
3465 tables, since they will have to share the available
3466 space. This parameter has no effect on the size of shared
3467 memory allocated by <productname>PostgreSQL</productname>, nor
3468 does it reserve kernel disk cache; it is used only for estimation
3469 purposes. The system also does not assume data remains in
3470 the disk cache between queries. The default is 4 gigabytes
3479 <sect2 id="runtime-config-query-geqo">
3480 <title>Genetic Query Optimizer</title>
3483 The genetic query optimizer (GEQO) is an algorithm that does query
3484 planning using heuristic searching. This reduces planning time for
3485 complex queries (those joining many relations), at the cost of producing
3486 plans that are sometimes inferior to those found by the normal
3487 exhaustive-search algorithm.
3488 For more information see <xref linkend="geqo">.
3493 <varlistentry id="guc-geqo" xreflabel="geqo">
3494 <term><varname>geqo</varname> (<type>boolean</type>)
3496 <primary>genetic query optimization</primary>
3499 <primary>GEQO</primary>
3500 <see>genetic query optimization</see>
3503 <primary><varname>geqo</> configuration parameter</primary>
3508 Enables or disables genetic query optimization.
3509 This is on by default. It is usually best not to turn it off in
3510 production; the <varname>geqo_threshold</varname> variable provides
3511 more granular control of GEQO.
3516 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
3517 <term><varname>geqo_threshold</varname> (<type>integer</type>)
3519 <primary><varname>geqo_threshold</> configuration parameter</primary>
3524 Use genetic query optimization to plan queries with at least
3525 this many <literal>FROM</> items involved. (Note that a
3526 <literal>FULL OUTER JOIN</> construct counts as only one <literal>FROM</>
3527 item.) The default is 12. For simpler queries it is usually best
3528 to use the regular, exhaustive-search planner, but for queries with
3529 many tables the exhaustive search takes too long, often
3530 longer than the penalty of executing a suboptimal plan. Thus,
3531 a threshold on the size of the query is a convenient way to manage
3537 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
3538 <term><varname>geqo_effort</varname> (<type>integer</type>)
3540 <primary><varname>geqo_effort</> configuration parameter</primary>
3545 Controls the trade-off between planning time and query plan
3546 quality in GEQO. This variable must be an integer in the
3547 range from 1 to 10. The default value is five. Larger values
3548 increase the time spent doing query planning, but also
3549 increase the likelihood that an efficient query plan will be
3554 <varname>geqo_effort</varname> doesn't actually do anything
3555 directly; it is only used to compute the default values for
3556 the other variables that influence GEQO behavior (described
3557 below). If you prefer, you can set the other parameters by
3563 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
3564 <term><varname>geqo_pool_size</varname> (<type>integer</type>)
3566 <primary><varname>geqo_pool_size</> configuration parameter</primary>
3571 Controls the pool size used by GEQO, that is the
3572 number of individuals in the genetic population. It must be
3573 at least two, and useful values are typically 100 to 1000. If
3574 it is set to zero (the default setting) then a suitable
3575 value is chosen based on <varname>geqo_effort</varname> and
3576 the number of tables in the query.
3581 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
3582 <term><varname>geqo_generations</varname> (<type>integer</type>)
3584 <primary><varname>geqo_generations</> configuration parameter</primary>
3589 Controls the number of generations used by GEQO, that is
3590 the number of iterations of the algorithm. It must
3591 be at least one, and useful values are in the same range as
3592 the pool size. If it is set to zero (the default setting)
3593 then a suitable value is chosen based on
3594 <varname>geqo_pool_size</varname>.
3599 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
3600 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)
3602 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
3607 Controls the selection bias used by GEQO. The selection bias
3608 is the selective pressure within the population. Values can be
3609 from 1.50 to 2.00; the latter is the default.
3614 <varlistentry id="guc-geqo-seed" xreflabel="geqo_seed">
3615 <term><varname>geqo_seed</varname> (<type>floating point</type>)
3617 <primary><varname>geqo_seed</> configuration parameter</primary>
3622 Controls the initial value of the random number generator used
3623 by GEQO to select random paths through the join order search space.
3624 The value can range from zero (the default) to one. Varying the
3625 value changes the set of join paths explored, and may result in a
3626 better or worse best path being found.
3633 <sect2 id="runtime-config-query-other">
3634 <title>Other Planner Options</title>
3638 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
3639 <term><varname>default_statistics_target</varname> (<type>integer</type>)
3641 <primary><varname>default_statistics_target</> configuration parameter</primary>
3646 Sets the default statistics target for table columns without
3647 a column-specific target set via <command>ALTER TABLE
3648 SET STATISTICS</>. Larger values increase the time needed to
3649 do <command>ANALYZE</>, but might improve the quality of the
3650 planner's estimates. The default is 100. For more information
3651 on the use of statistics by the <productname>PostgreSQL</>
3652 query planner, refer to <xref linkend="planner-stats">.
3657 <varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
3658 <term><varname>constraint_exclusion</varname> (<type>enum</type>)
3660 <primary>constraint exclusion</primary>
3663 <primary><varname>constraint_exclusion</> configuration parameter</primary>
3668 Controls the query planner's use of table constraints to
3670 The allowed values of <varname>constraint_exclusion</> are
3671 <literal>on</> (examine constraints for all tables),
3672 <literal>off</> (never examine constraints), and
3673 <literal>partition</> (examine constraints only for inheritance child
3674 tables and <literal>UNION ALL</> subqueries).
3675 <literal>partition</> is the default setting.
3676 It is often used with inheritance and partitioned tables to
3677 improve performance.
3681 When this parameter allows it for a particular table, the planner
3682 compares query conditions with the table's <literal>CHECK</>
3683 constraints, and omits scanning tables for which the conditions
3684 contradict the constraints. For example:
3687 CREATE TABLE parent(key integer, ...);
3688 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
3689 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
3691 SELECT * FROM parent WHERE key = 2400;
3694 With constraint exclusion enabled, this <command>SELECT</>
3695 will not scan <structname>child1000</> at all, improving performance.
3699 Currently, constraint exclusion is enabled by default
3700 only for cases that are often used to implement table partitioning.
3701 Turning it on for all tables imposes extra planning overhead that is
3702 quite noticeable on simple queries, and most often will yield no
3703 benefit for simple queries. If you have no partitioned tables
3704 you might prefer to turn it off entirely.
3708 Refer to <xref linkend="ddl-partitioning-constraint-exclusion"> for
3709 more information on using constraint exclusion and partitioning.
3714 <varlistentry id="guc-cursor-tuple-fraction" xreflabel="cursor_tuple_fraction">
3715 <term><varname>cursor_tuple_fraction</varname> (<type>floating point</type>)
3717 <primary><varname>cursor_tuple_fraction</> configuration parameter</primary>
3722 Sets the planner's estimate of the fraction of a cursor's rows that
3723 will be retrieved. The default is 0.1. Smaller values of this
3724 setting bias the planner towards using <quote>fast start</> plans
3725 for cursors, which will retrieve the first few rows quickly while
3726 perhaps taking a long time to fetch all rows. Larger values
3727 put more emphasis on the total estimated time. At the maximum
3728 setting of 1.0, cursors are planned exactly like regular queries,
3729 considering only the total estimated time and not how soon the
3730 first rows might be delivered.
3735 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
3736 <term><varname>from_collapse_limit</varname> (<type>integer</type>)
3738 <primary><varname>from_collapse_limit</> configuration parameter</primary>
3743 The planner will merge sub-queries into upper queries if the
3744 resulting <literal>FROM</literal> list would have no more than
3745 this many items. Smaller values reduce planning time but might
3746 yield inferior query plans. The default is eight.
3747 For more information see <xref linkend="explicit-joins">.
3751 Setting this value to <xref linkend="guc-geqo-threshold"> or more
3752 may trigger use of the GEQO planner, resulting in non-optimal
3753 plans. See <xref linkend="runtime-config-query-geqo">.
3758 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
3759 <term><varname>join_collapse_limit</varname> (<type>integer</type>)
3761 <primary><varname>join_collapse_limit</> configuration parameter</primary>
3766 The planner will rewrite explicit <literal>JOIN</>
3767 constructs (except <literal>FULL JOIN</>s) into lists of
3768 <literal>FROM</> items whenever a list of no more than this many items
3769 would result. Smaller values reduce planning time but might
3770 yield inferior query plans.
3774 By default, this variable is set the same as
3775 <varname>from_collapse_limit</varname>, which is appropriate
3776 for most uses. Setting it to 1 prevents any reordering of
3777 explicit <literal>JOIN</>s. Thus, the explicit join order
3778 specified in the query will be the actual order in which the
3779 relations are joined. Because the query planner does not always choose
3780 the optimal join order, advanced users can elect to
3781 temporarily set this variable to 1, and then specify the join
3782 order they desire explicitly.
3783 For more information see <xref linkend="explicit-joins">.
3787 Setting this value to <xref linkend="guc-geqo-threshold"> or more
3788 may trigger use of the GEQO planner, resulting in non-optimal
3789 plans. See <xref linkend="runtime-config-query-geqo">.
3798 <sect1 id="runtime-config-logging">
3799 <title>Error Reporting and Logging</title>
3801 <indexterm zone="runtime-config-logging">
3802 <primary>server log</primary>
3805 <sect2 id="runtime-config-logging-where">
3806 <title>Where To Log</title>
3808 <indexterm zone="runtime-config-logging-where">
3809 <primary>where to log</primary>
3814 <varlistentry id="guc-log-destination" xreflabel="log_destination">
3815 <term><varname>log_destination</varname> (<type>string</type>)
3817 <primary><varname>log_destination</> configuration parameter</primary>
3822 <productname>PostgreSQL</productname> supports several methods
3823 for logging server messages, including
3824 <systemitem>stderr</systemitem>, <systemitem>csvlog</systemitem> and
3825 <systemitem>syslog</systemitem>. On Windows,
3826 <systemitem>eventlog</systemitem> is also supported. Set this
3827 parameter to a list of desired log destinations separated by
3828 commas. The default is to log to <systemitem>stderr</systemitem>
3830 This parameter can only be set in the <filename>postgresql.conf</>
3831 file or on the server command line.
3834 If <systemitem>csvlog</> is included in <varname>log_destination</>,
3835 log entries are output in <quote>comma separated
3836 value</> (<acronym>CSV</>) format, which is convenient for
3837 loading logs into programs.
3838 See <xref linkend="runtime-config-logging-csvlog"> for details.
3839 <xref linkend="guc-logging-collector"> must be enabled to generate
3840 CSV-format log output.
3845 On most Unix systems, you will need to alter the configuration of
3846 your system's <application>syslog</application> daemon in order
3847 to make use of the <systemitem>syslog</systemitem> option for
3848 <varname>log_destination</>. <productname>PostgreSQL</productname>
3849 can log to <application>syslog</application> facilities
3850 <literal>LOCAL0</> through <literal>LOCAL7</> (see <xref
3851 linkend="guc-syslog-facility">), but the default
3852 <application>syslog</application> configuration on most platforms
3853 will discard all such messages. You will need to add something like:
3855 local0.* /var/log/postgresql
3857 to the <application>syslog</application> daemon's configuration file
3861 On Windows, when you use the <literal>eventlog</literal>
3862 option for <varname>log_destination</>, you should
3863 register an event source and its library with the operating
3864 system so that the Windows Event Viewer can display event
3865 log messages cleanly.
3866 See <xref linkend="event-log-registration"> for details.
3872 <varlistentry id="guc-logging-collector" xreflabel="logging_collector">
3873 <term><varname>logging_collector</varname> (<type>boolean</type>)
3875 <primary><varname>logging_collector</> configuration parameter</primary>
3880 This parameter enables the <firstterm>logging collector</>, which
3881 is a background process that captures log messages
3882 sent to <systemitem>stderr</> and redirects them into log files.
3883 This approach is often more useful than
3884 logging to <application>syslog</>, since some types of messages
3885 might not appear in <application>syslog</> output. (One common
3886 example is dynamic-linker failure messages; another is error messages
3887 produced by scripts such as <varname>archive_command</>.)
3888 This parameter can only be set at server start.
3893 It is possible to log to <systemitem>stderr</> without using the
3894 logging collector; the log messages will just go to wherever the
3895 server's <systemitem>stderr</> is directed. However, that method is
3896 only suitable for low log volumes, since it provides no convenient
3897 way to rotate log files. Also, on some platforms not using the
3898 logging collector can result in lost or garbled log output, because
3899 multiple processes writing concurrently to the same log file can
3900 overwrite each other's output.
3906 The logging collector is designed to never lose messages. This means
3907 that in case of extremely high load, server processes could be
3908 blocked while trying to send additional log messages when the
3909 collector has fallen behind. In contrast, <application>syslog</>
3910 prefers to drop messages if it cannot write them, which means it
3911 may fail to log some messages in such cases but it will not block
3912 the rest of the system.
3919 <varlistentry id="guc-log-directory" xreflabel="log_directory">
3920 <term><varname>log_directory</varname> (<type>string</type>)
3922 <primary><varname>log_directory</> configuration parameter</primary>
3927 When <varname>logging_collector</> is enabled,
3928 this parameter determines the directory in which log files will be created.
3929 It can be specified as an absolute path, or relative to the
3930 cluster data directory.
3931 This parameter can only be set in the <filename>postgresql.conf</>
3932 file or on the server command line.
3933 The default is <literal>pg_log</literal>.
3938 <varlistentry id="guc-log-filename" xreflabel="log_filename">
3939 <term><varname>log_filename</varname> (<type>string</type>)
3941 <primary><varname>log_filename</> configuration parameter</primary>
3946 When <varname>logging_collector</varname> is enabled,
3947 this parameter sets the file names of the created log files. The value
3948 is treated as a <function>strftime</function> pattern,
3949 so <literal>%</literal>-escapes can be used to specify time-varying
3950 file names. (Note that if there are
3951 any time-zone-dependent <literal>%</literal>-escapes, the computation
3952 is done in the zone specified
3953 by <xref linkend="guc-log-timezone">.)
3954 The supported <literal>%</literal>-escapes are similar to those
3955 listed in the Open Group's <ulink
3956 url="http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html">strftime
3957 </ulink> specification.
3958 Note that the system's <function>strftime</function> is not used
3959 directly, so platform-specific (nonstandard) extensions do not work.
3960 The default is <literal>postgresql-%Y-%m-%d_%H%M%S.log</literal>.
3963 If you specify a file name without escapes, you should plan to
3964 use a log rotation utility to avoid eventually filling the
3965 entire disk. In releases prior to 8.4, if
3966 no <literal>%</literal> escapes were
3967 present, <productname>PostgreSQL</productname> would append
3968 the epoch of the new log file's creation time, but this is no
3972 If CSV-format output is enabled in <varname>log_destination</>,
3973 <literal>.csv</> will be appended to the timestamped
3974 log file name to create the file name for CSV-format output.
3975 (If <varname>log_filename</> ends in <literal>.log</>, the suffix is
3979 This parameter can only be set in the <filename>postgresql.conf</>
3980 file or on the server command line.
3985 <varlistentry id="guc-log-file-mode" xreflabel="log_file_mode">
3986 <term><varname>log_file_mode</varname> (<type>integer</type>)
3988 <primary><varname>log_file_mode</> configuration parameter</primary>
3993 On Unix systems this parameter sets the permissions for log files
3994 when <varname>logging_collector</varname> is enabled. (On Microsoft
3995 Windows this parameter is ignored.)
3996 The parameter value is expected to be a numeric mode
3997 specified in the format accepted by the
3998 <function>chmod</function> and <function>umask</function>
3999 system calls. (To use the customary octal format the number
4000 must start with a <literal>0</literal> (zero).)
4003 The default permissions are <literal>0600</>, meaning only the
4004 server owner can read or write the log files. The other commonly
4005 useful setting is <literal>0640</>, allowing members of the owner's
4006 group to read the files. Note however that to make use of such a
4007 setting, you'll need to alter <xref linkend="guc-log-directory"> to
4008 store the files somewhere outside the cluster data directory. In
4009 any case, it's unwise to make the log files world-readable, since
4010 they might contain sensitive data.
4013 This parameter can only be set in the <filename>postgresql.conf</>
4014 file or on the server command line.
4019 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
4020 <term><varname>log_rotation_age</varname> (<type>integer</type>)
4022 <primary><varname>log_rotation_age</> configuration parameter</primary>
4027 When <varname>logging_collector</varname> is enabled,
4028 this parameter determines the maximum lifetime of an individual log file.
4029 After this many minutes have elapsed, a new log file will
4030 be created. Set to zero to disable time-based creation of
4032 This parameter can only be set in the <filename>postgresql.conf</>
4033 file or on the server command line.
4038 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
4039 <term><varname>log_rotation_size</varname> (<type>integer</type>)
4041 <primary><varname>log_rotation_size</> configuration parameter</primary>
4046 When <varname>logging_collector</varname> is enabled,
4047 this parameter determines the maximum size of an individual log file.
4048 After this many kilobytes have been emitted into a log file,
4049 a new log file will be created. Set to zero to disable size-based
4050 creation of new log files.
4051 This parameter can only be set in the <filename>postgresql.conf</>
4052 file or on the server command line.
4057 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
4058 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)
4060 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
4065 When <varname>logging_collector</varname> is enabled,
4066 this parameter will cause <productname>PostgreSQL</productname> to truncate (overwrite),
4067 rather than append to, any existing log file of the same name.
4068 However, truncation will occur only when a new file is being opened
4069 due to time-based rotation, not during server startup or size-based
4070 rotation. When off, pre-existing files will be appended to in
4071 all cases. For example, using this setting in combination with
4072 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
4073 would result in generating twenty-four hourly log files and then
4074 cyclically overwriting them.
4075 This parameter can only be set in the <filename>postgresql.conf</>
4076 file or on the server command line.
4079 Example: To keep 7 days of logs, one log file per day named
4080 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
4081 etc, and automatically overwrite last week's log with this week's log,
4082 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
4083 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>, and
4084 <varname>log_rotation_age</varname> to <literal>1440</literal>.
4087 Example: To keep 24 hours of logs, one log file per hour, but
4088 also rotate sooner if the log file size exceeds 1GB, set
4089 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
4090 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>,
4091 <varname>log_rotation_age</varname> to <literal>60</literal>, and
4092 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
4093 Including <literal>%M</> in <varname>log_filename</varname> allows
4094 any size-driven rotations that might occur to select a file name
4095 different from the hour's initial file name.
4100 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
4101 <term><varname>syslog_facility</varname> (<type>enum</type>)
4103 <primary><varname>syslog_facility</> configuration parameter</primary>
4108 When logging to <application>syslog</> is enabled, this parameter
4109 determines the <application>syslog</application>
4110 <quote>facility</quote> to be used. You can choose
4111 from <literal>LOCAL0</>, <literal>LOCAL1</>,
4112 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
4113 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
4114 the default is <literal>LOCAL0</>. See also the
4115 documentation of your system's
4116 <application>syslog</application> daemon.
4117 This parameter can only be set in the <filename>postgresql.conf</>
4118 file or on the server command line.
4123 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
4124 <term><varname>syslog_ident</varname> (<type>string</type>)
4126 <primary><varname>syslog_ident</> configuration parameter</primary>
4131 When logging to <application>syslog</> is enabled, this parameter
4132 determines the program name used to identify
4133 <productname>PostgreSQL</productname> messages in
4134 <application>syslog</application> logs. The default is
4135 <literal>postgres</literal>.
4136 This parameter can only be set in the <filename>postgresql.conf</>
4137 file or on the server command line.
4142 <varlistentry id="guc-event-source" xreflabel="event_source">
4143 <term><varname>event_source</varname> (<type>string</type>)
4145 <primary><varname>event_source</> configuration parameter</primary>
4150 When logging to <application>event log</> is enabled, this parameter
4151 determines the program name used to identify
4152 <productname>PostgreSQL</productname> messages in
4153 the log. The default is <literal>PostgreSQL</literal>.
4154 This parameter can only be set in the <filename>postgresql.conf</>
4155 file or on the server command line.
4162 <sect2 id="runtime-config-logging-when">
4163 <title>When To Log</title>
4167 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
4168 <term><varname>client_min_messages</varname> (<type>enum</type>)
4170 <primary><varname>client_min_messages</> configuration parameter</primary>
4175 Controls which message levels are sent to the client.
4176 Valid values are <literal>DEBUG5</>,
4177 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
4178 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
4179 <literal>WARNING</>, <literal>ERROR</>, <literal>FATAL</>,
4180 and <literal>PANIC</>. Each level
4181 includes all the levels that follow it. The later the level,
4182 the fewer messages are sent. The default is
4183 <literal>NOTICE</>. Note that <literal>LOG</> has a different
4184 rank here than in <varname>log_min_messages</>.
4189 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
4190 <term><varname>log_min_messages</varname> (<type>enum</type>)
4192 <primary><varname>log_min_messages</> configuration parameter</primary>
4197 Controls which message levels are written to the server log.
4198 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
4199 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
4200 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
4201 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
4202 <literal>PANIC</>. Each level includes all the levels that
4203 follow it. The later the level, the fewer messages are sent
4204 to the log. The default is <literal>WARNING</>. Note that
4205 <literal>LOG</> has a different rank here than in
4206 <varname>client_min_messages</>.
4207 Only superusers can change this setting.
4212 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
4213 <term><varname>log_min_error_statement</varname> (<type>enum</type>)
4215 <primary><varname>log_min_error_statement</> configuration parameter</primary>
4220 Controls which SQL statements that cause an error
4221 condition are recorded in the server log. The current
4222 SQL statement is included in the log entry for any message of
4223 the specified severity or higher.
4224 Valid values are <literal>DEBUG5</literal>,
4225 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
4226 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
4227 <literal>INFO</literal>, <literal>NOTICE</literal>,
4228 <literal>WARNING</literal>, <literal>ERROR</literal>,
4229 <literal>LOG</literal>,
4230 <literal>FATAL</literal>, and <literal>PANIC</literal>.
4231 The default is <literal>ERROR</literal>, which means statements
4232 causing errors, log messages, fatal errors, or panics will be logged.
4233 To effectively turn off logging of failing statements,
4234 set this parameter to <literal>PANIC</literal>.
4235 Only superusers can change this setting.
4240 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
4241 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)
4243 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
4248 Causes the duration of each completed statement to be logged
4249 if the statement ran for at least the specified number of
4250 milliseconds. Setting this to zero prints all statement durations.
4251 Minus-one (the default) disables logging statement durations.
4252 For example, if you set it to <literal>250ms</literal>
4253 then all SQL statements that run 250ms or longer will be
4254 logged. Enabling this parameter can be helpful in tracking down
4255 unoptimized queries in your applications.
4256 Only superusers can change this setting.
4260 For clients using extended query protocol, durations of the Parse,
4261 Bind, and Execute steps are logged independently.
4266 When using this option together with
4267 <xref linkend="guc-log-statement">,
4268 the text of statements that are logged because of
4269 <varname>log_statement</> will not be repeated in the
4270 duration log message.
4271 If you are not using <application>syslog</>, it is recommended
4272 that you log the PID or session ID using
4273 <xref linkend="guc-log-line-prefix">
4274 so that you can link the statement message to the later
4275 duration message using the process ID or session ID.
4284 <xref linkend="runtime-config-severity-levels"> explains the message
4285 severity levels used by <productname>PostgreSQL</>. If logging output
4286 is sent to <systemitem>syslog</systemitem> or Windows'
4287 <systemitem>eventlog</systemitem>, the severity levels are translated
4288 as shown in the table.
4291 <table id="runtime-config-severity-levels">
4292 <title>Message Severity Levels</title>
4296 <entry>Severity</entry>
4297 <entry>Usage</entry>
4298 <entry><systemitem>syslog</></entry>
4299 <entry><systemitem>eventlog</></entry>
4305 <entry><literal>DEBUG1..DEBUG5</></entry>
4306 <entry>Provides successively-more-detailed information for use by
4308 <entry><literal>DEBUG</></entry>
4309 <entry><literal>INFORMATION</></entry>
4313 <entry><literal>INFO</></entry>
4314 <entry>Provides information implicitly requested by the user,
4315 e.g., output from <command>VACUUM VERBOSE</>.</entry>
4316 <entry><literal>INFO</></entry>
4317 <entry><literal>INFORMATION</></entry>
4321 <entry><literal>NOTICE</></entry>
4322 <entry>Provides information that might be helpful to users, e.g.,
4323 notice of truncation of long identifiers.</entry>
4324 <entry><literal>NOTICE</></entry>
4325 <entry><literal>INFORMATION</></entry>
4329 <entry><literal>WARNING</></entry>
4330 <entry>Provides warnings of likely problems, e.g., <command>COMMIT</>
4331 outside a transaction block.</entry>
4332 <entry><literal>NOTICE</></entry>
4333 <entry><literal>WARNING</></entry>
4337 <entry><literal>ERROR</></entry>
4338 <entry>Reports an error that caused the current command to
4340 <entry><literal>WARNING</></entry>
4341 <entry><literal>ERROR</></entry>
4345 <entry><literal>LOG</></entry>
4346 <entry>Reports information of interest to administrators, e.g.,
4347 checkpoint activity.</entry>
4348 <entry><literal>INFO</></entry>
4349 <entry><literal>INFORMATION</></entry>
4353 <entry><literal>FATAL</></entry>
4354 <entry>Reports an error that caused the current session to
4356 <entry><literal>ERR</></entry>
4357 <entry><literal>ERROR</></entry>
4361 <entry><literal>PANIC</></entry>
4362 <entry>Reports an error that caused all database sessions to abort.</entry>
4363 <entry><literal>CRIT</></entry>
4364 <entry><literal>ERROR</></entry>
4371 <sect2 id="runtime-config-logging-what">
4372 <title>What To Log</title>
4376 <varlistentry id="guc-application-name" xreflabel="application_name">
4377 <term><varname>application_name</varname> (<type>string</type>)
4379 <primary><varname>application_name</> configuration parameter</primary>
4384 The <varname>application_name</varname> can be any string of less than
4385 <symbol>NAMEDATALEN</> characters (64 characters in a standard build).
4386 It is typically set by an application upon connection to the server.
4387 The name will be displayed in the <structname>pg_stat_activity</> view
4388 and included in CSV log entries. It can also be included in regular
4389 log entries via the <xref linkend="guc-log-line-prefix"> parameter.
4390 Only printable ASCII characters may be used in the
4391 <varname>application_name</varname> value. Other characters will be
4392 replaced with question marks (<literal>?</literal>).
4397 <varlistentry id="guc-cluster-name" xreflabel="cluster_name">
4398 <term><varname>cluster_name</varname> (<type>string</type>)
4400 <primary><varname>cluster_name</> configuration parameter</primary>
4405 Sets the cluster name that appears in the process title for all
4406 processes in this cluster. The name can be any string of less than
4407 <symbol>NAMEDATALEN</> characters (64 characters in a standard
4408 build). Only printable ASCII characters may be used in the
4409 <varname>cluster_name</varname> value. Other characters will be
4410 replaced with question marks (<literal>?</literal>). No name is shown
4411 if this parameter is set to the empty string <literal>''</> (which is
4412 the default). This parameter can only be set at server start.
4415 The process title is typically viewed using programs like
4416 <application>ps</> or, on Windows, <application>Process Explorer</>.
4422 <term><varname>debug_print_parse</varname> (<type>boolean</type>)
4424 <primary><varname>debug_print_parse</> configuration parameter</primary>
4427 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)
4429 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
4432 <term><varname>debug_print_plan</varname> (<type>boolean</type>)
4434 <primary><varname>debug_print_plan</> configuration parameter</primary>
4439 These parameters enable various debugging output to be emitted.
4440 When set, they print the resulting parse tree, the query rewriter
4441 output, or the execution plan for each executed query.
4442 These messages are emitted at <literal>LOG</> message level, so by
4443 default they will appear in the server log but will not be sent to the
4444 client. You can change that by adjusting
4445 <xref linkend="guc-client-min-messages"> and/or
4446 <xref linkend="guc-log-min-messages">.
4447 These parameters are off by default.
4453 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)
4455 <primary><varname>debug_pretty_print</> configuration parameter</primary>
4460 When set, <varname>debug_pretty_print</varname> indents the messages
4461 produced by <varname>debug_print_parse</varname>,
4462 <varname>debug_print_rewritten</varname>, or
4463 <varname>debug_print_plan</varname>. This results in more readable
4464 but much longer output than the <quote>compact</> format used when
4465 it is off. It is on by default.
4470 <varlistentry id="guc-log-checkpoints" xreflabel="log_checkpoints">
4471 <term><varname>log_checkpoints</varname> (<type>boolean</type>)
4473 <primary><varname>log_checkpoints</> configuration parameter</primary>
4478 Causes checkpoints and restartpoints to be logged in the server log.
4479 Some statistics are included in the log messages, including the number
4480 of buffers written and the time spent writing them.
4481 This parameter can only be set in the <filename>postgresql.conf</>
4482 file or on the server command line. The default is off.
4487 <varlistentry id="guc-log-connections" xreflabel="log_connections">
4488 <term><varname>log_connections</varname> (<type>boolean</type>)
4490 <primary><varname>log_connections</> configuration parameter</primary>
4495 Causes each attempted connection to the server to be logged,
4496 as well as successful completion of client authentication.
4497 Only superusers can change this parameter at session start,
4498 and it cannot be changed at all within a session.
4499 The default is <literal>off</>.
4504 Some client programs, like <application>psql</>, attempt
4505 to connect twice while determining if a password is required, so
4506 duplicate <quote>connection received</> messages do not
4507 necessarily indicate a problem.
4513 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
4514 <term><varname>log_disconnections</varname> (<type>boolean</type>)
4516 <primary><varname>log_disconnections</> configuration parameter</primary>
4521 Causes session terminations to be logged. The log output
4522 provides information similar to <varname>log_connections</varname>,
4523 plus the duration of the session.
4524 Only superusers can change this parameter at session start,
4525 and it cannot be changed at all within a session.
4526 The default is <literal>off</>.
4532 <varlistentry id="guc-log-duration" xreflabel="log_duration">
4533 <term><varname>log_duration</varname> (<type>boolean</type>)
4535 <primary><varname>log_duration</> configuration parameter</primary>
4540 Causes the duration of every completed statement to be logged.
4541 The default is <literal>off</>.
4542 Only superusers can change this setting.
4546 For clients using extended query protocol, durations of the Parse,
4547 Bind, and Execute steps are logged independently.
4552 The difference between setting this option and setting
4553 <xref linkend="guc-log-min-duration-statement"> to zero is that
4554 exceeding <varname>log_min_duration_statement</> forces the text of
4555 the query to be logged, but this option doesn't. Thus, if
4556 <varname>log_duration</> is <literal>on</> and
4557 <varname>log_min_duration_statement</> has a positive value, all
4558 durations are logged but the query text is included only for
4559 statements exceeding the threshold. This behavior can be useful for
4560 gathering statistics in high-load installations.
4566 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
4567 <term><varname>log_error_verbosity</varname> (<type>enum</type>)
4569 <primary><varname>log_error_verbosity</> configuration parameter</primary>
4574 Controls the amount of detail written in the server log for each
4575 message that is logged. Valid values are <literal>TERSE</>,
4576 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
4577 fields to displayed messages. <literal>TERSE</> excludes
4578 the logging of <literal>DETAIL</>, <literal>HINT</>,
4579 <literal>QUERY</>, and <literal>CONTEXT</> error information.
4580 <literal>VERBOSE</> output includes the <symbol>SQLSTATE</> error
4581 code (see also <xref linkend="errcodes-appendix">) and the source code file name, function name,
4582 and line number that generated the error.
4583 Only superusers can change this setting.
4588 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
4589 <term><varname>log_hostname</varname> (<type>boolean</type>)
4591 <primary><varname>log_hostname</> configuration parameter</primary>
4596 By default, connection log messages only show the IP address of the
4597 connecting host. Turning this parameter on causes logging of the
4598 host name as well. Note that depending on your host name resolution
4599 setup this might impose a non-negligible performance penalty.
4600 This parameter can only be set in the <filename>postgresql.conf</>
4601 file or on the server command line.
4606 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
4607 <term><varname>log_line_prefix</varname> (<type>string</type>)
4609 <primary><varname>log_line_prefix</> configuration parameter</primary>
4614 This is a <function>printf</>-style string that is output at the
4615 beginning of each log line.
4616 <literal>%</> characters begin <quote>escape sequences</>
4617 that are replaced with status information as outlined below.
4618 Unrecognized escapes are ignored. Other
4619 characters are copied straight to the log line. Some escapes are
4620 only recognized by session processes, and will be treated as empty by
4621 background processes such as the main server process. Status
4622 information may be aligned either left or right by specifying a
4623 numeric literal after the % and before the option. A negative
4624 value will cause the status information to be padded on the
4625 right with spaces to give it a minimum width, whereas a positive
4626 value will pad on the left. Padding can be useful to aid human
4627 readability in log files.
4628 This parameter can only be set in the <filename>postgresql.conf</>
4629 file or on the server command line. The default is an empty string.
4635 <entry>Escape</entry>
4636 <entry>Effect</entry>
4637 <entry>Session only</entry>
4642 <entry><literal>%a</literal></entry>
4643 <entry>Application name</entry>
4647 <entry><literal>%u</literal></entry>
4648 <entry>User name</entry>
4652 <entry><literal>%d</literal></entry>
4653 <entry>Database name</entry>
4657 <entry><literal>%r</literal></entry>
4658 <entry>Remote host name or IP address, and remote port</entry>
4662 <entry><literal>%h</literal></entry>
4663 <entry>Remote host name or IP address</entry>
4667 <entry><literal>%p</literal></entry>
4668 <entry>Process ID</entry>
4672 <entry><literal>%t</literal></entry>
4673 <entry>Time stamp without milliseconds</entry>
4677 <entry><literal>%m</literal></entry>
4678 <entry>Time stamp with milliseconds</entry>
4682 <entry><literal>%n</literal></entry>
4683 <entry>Time stamp with milliseconds (as a Unix epoch)</entry>
4687 <entry><literal>%i</literal></entry>
4688 <entry>Command tag: type of session's current command</entry>
4692 <entry><literal>%e</literal></entry>
4693 <entry>SQLSTATE error code</entry>
4697 <entry><literal>%c</literal></entry>
4698 <entry>Session ID: see below</entry>
4702 <entry><literal>%l</literal></entry>
4703 <entry>Number of the log line for each session or process, starting at 1</entry>
4707 <entry><literal>%s</literal></entry>
4708 <entry>Process start time stamp</entry>
4712 <entry><literal>%v</literal></entry>
4713 <entry>Virtual transaction ID (backendID/localXID)</entry>
4717 <entry><literal>%x</literal></entry>
4718 <entry>Transaction ID (0 if none is assigned)</entry>
4722 <entry><literal>%q</literal></entry>
4723 <entry>Produces no output, but tells non-session
4724 processes to stop at this point in the string; ignored by
4725 session processes</entry>
4729 <entry><literal>%%</literal></entry>
4730 <entry>Literal <literal>%</></entry>
4737 The <literal>%c</> escape prints a quasi-unique session identifier,
4738 consisting of two 4-byte hexadecimal numbers (without leading zeros)
4739 separated by a dot. The numbers are the process start time and the
4740 process ID, so <literal>%c</> can also be used as a space saving way
4741 of printing those items. For example, to generate the session
4742 identifier from <literal>pg_stat_activity</>, use this query:
4744 SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
4746 FROM pg_stat_activity;
4753 If you set a nonempty value for <varname>log_line_prefix</>,
4754 you should usually make its last character be a space, to provide
4755 visual separation from the rest of the log line. A punctuation
4756 character can be used too.
4762 <application>Syslog</> produces its own
4763 time stamp and process ID information, so you probably do not want to
4764 include those escapes if you are logging to <application>syslog</>.
4770 <varlistentry id="guc-log-lock-waits" xreflabel="log_lock_waits">
4771 <term><varname>log_lock_waits</varname> (<type>boolean</type>)
4773 <primary><varname>log_lock_waits</> configuration parameter</primary>
4778 Controls whether a log message is produced when a session waits
4779 longer than <xref linkend="guc-deadlock-timeout"> to acquire a
4780 lock. This is useful in determining if lock waits are causing
4781 poor performance. The default is <literal>off</>.
4786 <varlistentry id="guc-log-statement" xreflabel="log_statement">
4787 <term><varname>log_statement</varname> (<type>enum</type>)
4789 <primary><varname>log_statement</> configuration parameter</primary>
4794 Controls which SQL statements are logged. Valid values are
4795 <literal>none</> (off), <literal>ddl</>, <literal>mod</>, and
4796 <literal>all</> (all statements). <literal>ddl</> logs all data definition
4797 statements, such as <command>CREATE</>, <command>ALTER</>, and
4798 <command>DROP</> statements. <literal>mod</> logs all
4799 <literal>ddl</> statements, plus data-modifying statements
4800 such as <command>INSERT</>,
4801 <command>UPDATE</>, <command>DELETE</>, <command>TRUNCATE</>,
4802 and <command>COPY FROM</>.
4803 <command>PREPARE</>, <command>EXECUTE</>, and
4804 <command>EXPLAIN ANALYZE</> statements are also logged if their
4805 contained command is of an appropriate type. For clients using
4806 extended query protocol, logging occurs when an Execute message
4807 is received, and values of the Bind parameters are included
4808 (with any embedded single-quote marks doubled).
4812 The default is <literal>none</>. Only superusers can change this
4818 Statements that contain simple syntax errors are not logged
4819 even by the <varname>log_statement</> = <literal>all</> setting,
4820 because the log message is emitted only after basic parsing has
4821 been done to determine the statement type. In the case of extended
4822 query protocol, this setting likewise does not log statements that
4823 fail before the Execute phase (i.e., during parse analysis or
4824 planning). Set <varname>log_min_error_statement</> to
4825 <literal>ERROR</> (or lower) to log such statements.
4831 <varlistentry id="guc-log-replication-commands" xreflabel="log_replication_commands">
4832 <term><varname>log_replication_commands</varname> (<type>boolean</type>)
4834 <primary><varname>log_replication_commands</> configuration parameter</primary>
4839 Causes each replication command to be logged in the server log.
4840 See <xref linkend="protocol-replication"> for more information about
4841 replication command. The default value is <literal>off</>.
4842 Only superusers can change this setting.
4847 <varlistentry id="guc-log-temp-files" xreflabel="log_temp_files">
4848 <term><varname>log_temp_files</varname> (<type>integer</type>)
4850 <primary><varname>log_temp_files</> configuration parameter</primary>
4855 Controls logging of temporary file names and sizes.
4856 Temporary files can be
4857 created for sorts, hashes, and temporary query results.
4858 A log entry is made for each temporary file when it is deleted.
4859 A value of zero logs all temporary file information, while positive
4860 values log only files whose size is greater than or equal to
4861 the specified number of kilobytes. The
4862 default setting is -1, which disables such logging.
4863 Only superusers can change this setting.
4868 <varlistentry id="guc-log-timezone" xreflabel="log_timezone">
4869 <term><varname>log_timezone</varname> (<type>string</type>)
4871 <primary><varname>log_timezone</> configuration parameter</primary>
4876 Sets the time zone used for timestamps written in the server log.
4877 Unlike <xref linkend="guc-timezone">, this value is cluster-wide,
4878 so that all sessions will report timestamps consistently.
4879 The built-in default is <literal>GMT</>, but that is typically
4880 overridden in <filename>postgresql.conf</>; <application>initdb</>
4881 will install a setting there corresponding to its system environment.
4882 See <xref linkend="datatype-timezones"> for more information.
4883 This parameter can only be set in the <filename>postgresql.conf</>
4884 file or on the server command line.
4891 <sect2 id="runtime-config-logging-csvlog">
4892 <title>Using CSV-Format Log Output</title>
4895 Including <literal>csvlog</> in the <varname>log_destination</> list
4896 provides a convenient way to import log files into a database table.
4897 This option emits log lines in comma-separated-values
4898 (<acronym>CSV</>) format,
4900 time stamp with milliseconds,
4904 client host:port number,
4906 per-session line number,
4909 virtual transaction ID,
4910 regular transaction ID,
4914 error message detail,
4916 internal query that led to the error (if any),
4917 character count of the error position therein,
4919 user query that led to the error (if any and enabled by
4920 <varname>log_min_error_statement</>),
4921 character count of the error position therein,
4922 location of the error in the PostgreSQL source code
4923 (if <varname>log_error_verbosity</> is set to <literal>verbose</>),
4924 and application name.
4925 Here is a sample table definition for storing CSV-format log output:
4928 CREATE TABLE postgres_log
4930 log_time timestamp(3) with time zone,
4934 connection_from text,
4936 session_line_num bigint,
4938 session_start_time timestamp with time zone,
4939 virtual_transaction_id text,
4940 transaction_id bigint,
4941 error_severity text,
4942 sql_state_code text,
4946 internal_query text,
4947 internal_query_pos integer,
4952 application_name text,
4953 PRIMARY KEY (session_id, session_line_num)
4959 To import a log file into this table, use the <command>COPY FROM</>
4963 COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
4968 There are a few things you need to do to simplify importing CSV log
4974 Set <varname>log_filename</varname> and
4975 <varname>log_rotation_age</> to provide a consistent,
4976 predictable naming scheme for your log files. This lets you
4977 predict what the file name will be and know when an individual log
4978 file is complete and therefore ready to be imported.
4984 Set <varname>log_rotation_size</varname> to 0 to disable
4985 size-based log rotation, as it makes the log file name difficult
4992 Set <varname>log_truncate_on_rotation</varname> to <literal>on</> so
4993 that old log data isn't mixed with the new in the same file.
4999 The table definition above includes a primary key specification.
5000 This is useful to protect against accidentally importing the same
5001 information twice. The <command>COPY</> command commits all of the
5002 data it imports at one time, so any error will cause the entire
5003 import to fail. If you import a partial log file and later import
5004 the file again when it is complete, the primary key violation will
5005 cause the import to fail. Wait until the log is complete and
5006 closed before importing. This procedure will also protect against
5007 accidentally importing a partial line that hasn't been completely
5008 written, which would also cause <command>COPY</> to fail.
5017 <sect1 id="runtime-config-statistics">
5018 <title>Run-time Statistics</title>
5020 <sect2 id="runtime-config-statistics-collector">
5021 <title>Query and Index Statistics Collector</title>
5024 These parameters control server-wide statistics collection features.
5025 When statistics collection is enabled, the data that is produced can be
5026 accessed via the <structname>pg_stat</structname> and
5027 <structname>pg_statio</structname> family of system views.
5028 Refer to <xref linkend="monitoring"> for more information.
5033 <varlistentry id="guc-track-activities" xreflabel="track_activities">
5034 <term><varname>track_activities</varname> (<type>boolean</type>)
5036 <primary><varname>track_activities</> configuration parameter</primary>
5041 Enables the collection of information on the currently
5042 executing command of each session, along with the time when
5043 that command began execution. This parameter is on by
5044 default. Note that even when enabled, this information is not
5045 visible to all users, only to superusers and the user owning
5046 the session being reported on, so it should not represent a
5048 Only superusers can change this setting.
5053 <varlistentry id="guc-track-activity-query-size" xreflabel="track_activity_query_size">
5054 <term><varname>track_activity_query_size</varname> (<type>integer</type>)
5056 <primary><varname>track_activity_query_size</> configuration parameter</primary>
5061 Specifies the number of bytes reserved to track the currently
5062 executing command for each active session, for the
5063 <structname>pg_stat_activity</>.<structfield>query</> field.
5064 The default value is 1024. This parameter can only be set at server
5070 <varlistentry id="guc-track-counts" xreflabel="track_counts">
5071 <term><varname>track_counts</varname> (<type>boolean</type>)
5073 <primary><varname>track_counts</> configuration parameter</primary>
5078 Enables collection of statistics on database activity.
5079 This parameter is on by default, because the autovacuum
5080 daemon needs the collected information.
5081 Only superusers can change this setting.
5086 <varlistentry id="guc-track-io-timing" xreflabel="track_io_timing">
5087 <term><varname>track_io_timing</varname> (<type>boolean</type>)
5089 <primary><varname>track_io_timing</> configuration parameter</primary>
5094 Enables timing of database I/O calls. This parameter is off by
5095 default, because it will repeatedly query the operating system for
5096 the current time, which may cause significant overhead on some
5097 platforms. You can use the <xref linkend="pgtesttiming"> tool to
5098 measure the overhead of timing on your system.
5099 I/O timing information is
5100 displayed in <xref linkend="pg-stat-database-view">, in the output of
5101 <xref linkend="sql-explain"> when the <literal>BUFFERS</> option is
5102 used, and by <xref linkend="pgstatstatements">. Only superusers can
5103 change this setting.
5108 <varlistentry id="guc-track-functions" xreflabel="track_functions">
5109 <term><varname>track_functions</varname> (<type>enum</type>)
5111 <primary><varname>track_functions</> configuration parameter</primary>
5116 Enables tracking of function call counts and time used. Specify
5117 <literal>pl</literal> to track only procedural-language functions,
5118 <literal>all</literal> to also track SQL and C language functions.
5119 The default is <literal>none</literal>, which disables function
5120 statistics tracking. Only superusers can change this setting.
5125 SQL-language functions that are simple enough to be <quote>inlined</>
5126 into the calling query will not be tracked, regardless of this
5133 <varlistentry id="guc-update-process-title" xreflabel="update_process_title">
5134 <term><varname>update_process_title</varname> (<type>boolean</type>)
5136 <primary><varname>update_process_title</> configuration parameter</primary>
5141 Enables updating of the process title every time a new SQL command
5142 is received by the server. The process title is typically viewed
5143 by the <command>ps</> command,
5144 or in Windows by using the <application>Process Explorer</>.
5145 Only superusers can change this setting.
5150 <varlistentry id="guc-stats-temp-directory" xreflabel="stats_temp_directory">
5151 <term><varname>stats_temp_directory</varname> (<type>string</type>)
5153 <primary><varname>stats_temp_directory</> configuration parameter</primary>
5158 Sets the directory to store temporary statistics data in. This can be
5159 a path relative to the data directory or an absolute path. The default
5160 is <filename>pg_stat_tmp</filename>. Pointing this at a RAM-based
5161 file system will decrease physical I/O requirements and can lead to
5162 improved performance.
5163 This parameter can only be set in the <filename>postgresql.conf</>
5164 file or on the server command line.
5172 <sect2 id="runtime-config-statistics-monitor">
5173 <title>Statistics Monitoring</title>
5177 <term><varname>log_statement_stats</varname> (<type>boolean</type>)
5179 <primary><varname>log_statement_stats</> configuration parameter</primary>
5182 <term><varname>log_parser_stats</varname> (<type>boolean</type>)
5184 <primary><varname>log_parser_stats</> configuration parameter</primary>
5187 <term><varname>log_planner_stats</varname> (<type>boolean</type>)
5189 <primary><varname>log_planner_stats</> configuration parameter</primary>
5192 <term><varname>log_executor_stats</varname> (<type>boolean</type>)
5194 <primary><varname>log_executor_stats</> configuration parameter</primary>
5199 For each query, output performance statistics of the respective
5200 module to the server log. This is a crude profiling
5201 instrument, similar to the Unix <function>getrusage()</> operating
5202 system facility. <varname>log_statement_stats</varname> reports total
5203 statement statistics, while the others report per-module statistics.
5204 <varname>log_statement_stats</varname> cannot be enabled together with
5205 any of the per-module options. All of these options are disabled by
5206 default. Only superusers can change these settings.
5216 <sect1 id="runtime-config-autovacuum">
5217 <title>Automatic Vacuuming</title>
5220 <primary>autovacuum</primary>
5221 <secondary>configuration parameters</secondary>
5225 These settings control the behavior of the <firstterm>autovacuum</>
5226 feature. Refer to <xref linkend="autovacuum"> for
5232 <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
5233 <term><varname>autovacuum</varname> (<type>boolean</type>)
5235 <primary><varname>autovacuum</> configuration parameter</primary>
5240 Controls whether the server should run the
5241 autovacuum launcher daemon. This is on by default; however,
5242 <xref linkend="guc-track-counts"> must also be enabled for
5244 This parameter can only be set in the <filename>postgresql.conf</>
5245 file or on the server command line.
5248 Note that even when this parameter is disabled, the system
5249 will launch autovacuum processes if necessary to
5250 prevent transaction ID wraparound. See <xref
5251 linkend="vacuum-for-wraparound"> for more information.
5256 <varlistentry id="guc-log-autovacuum-min-duration" xreflabel="log_autovacuum_min_duration">
5257 <term><varname>log_autovacuum_min_duration</varname> (<type>integer</type>)
5259 <primary><varname>log_autovacuum_min_duration</> configuration parameter</primary>
5264 Causes each action executed by autovacuum to be logged if it ran for at
5265 least the specified number of milliseconds. Setting this to zero logs
5266 all autovacuum actions. Minus-one (the default) disables logging
5267 autovacuum actions. For example, if you set this to
5268 <literal>250ms</literal> then all automatic vacuums and analyzes that run
5269 250ms or longer will be logged. In addition, when this parameter is
5270 set to any value other than <literal>-1</literal>, a message will be
5271 logged if an autovacuum action is skipped due to the existence of a
5272 conflicting lock. Enabling this parameter can be helpful
5273 in tracking autovacuum activity. This setting can only be set in
5274 the <filename>postgresql.conf</> file or on the server command line.
5279 <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
5280 <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)
5282 <primary><varname>autovacuum_max_workers</> configuration parameter</primary>
5287 Specifies the maximum number of autovacuum processes (other than the
5288 autovacuum launcher) which may be running at any one time. The default
5289 is three. This parameter can only be set at server start.
5294 <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
5295 <term><varname>autovacuum_naptime</varname> (<type>integer</type>)
5297 <primary><varname>autovacuum_naptime</> configuration parameter</primary>
5302 Specifies the minimum delay between autovacuum runs on any given
5303 database. In each round the daemon examines the
5304 database and issues <command>VACUUM</> and <command>ANALYZE</> commands
5305 as needed for tables in that database. The delay is measured
5306 in seconds, and the default is one minute (<literal>1min</>).
5307 This parameter can only be set in the <filename>postgresql.conf</>
5308 file or on the server command line.
5313 <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
5314 <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)
5316 <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
5321 Specifies the minimum number of updated or deleted tuples needed
5322 to trigger a <command>VACUUM</> in any one table.
5323 The default is 50 tuples.
5324 This parameter can only be set in the <filename>postgresql.conf</>
5325 file or on the server command line.
5326 This setting can be overridden for individual tables by
5327 changing storage parameters.
5332 <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
5333 <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)
5335 <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
5340 Specifies the minimum number of inserted, updated or deleted tuples
5341 needed to trigger an <command>ANALYZE</> in any one table.
5342 The default is 50 tuples.
5343 This parameter can only be set in the <filename>postgresql.conf</>
5344 file or on the server command line.
5345 This setting can be overridden for individual tables by
5346 changing storage parameters.
5351 <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
5352 <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)
5354 <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
5359 Specifies a fraction of the table size to add to
5360 <varname>autovacuum_vacuum_threshold</varname>
5361 when deciding whether to trigger a <command>VACUUM</>.
5362 The default is 0.2 (20% of table size).
5363 This parameter can only be set in the <filename>postgresql.conf</>
5364 file or on the server command line.
5365 This setting can be overridden for individual tables by
5366 changing storage parameters.
5371 <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
5372 <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)
5374 <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
5379 Specifies a fraction of the table size to add to
5380 <varname>autovacuum_analyze_threshold</varname>
5381 when deciding whether to trigger an <command>ANALYZE</>.
5382 The default is 0.1 (10% of table size).
5383 This parameter can only be set in the <filename>postgresql.conf</>
5384 file or on the server command line.
5385 This setting can be overridden for individual tables by
5386 changing storage parameters.
5391 <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
5392 <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)
5394 <primary><varname>autovacuum_freeze_max_age</> configuration parameter</primary>
5399 Specifies the maximum age (in transactions) that a table's
5400 <structname>pg_class</>.<structfield>relfrozenxid</> field can
5401 attain before a <command>VACUUM</> operation is forced
5402 to prevent transaction ID wraparound within the table.
5403 Note that the system will launch autovacuum processes to
5404 prevent wraparound even when autovacuum is otherwise disabled.
5408 Vacuum also allows removal of old files from the
5409 <filename>pg_clog</> subdirectory, which is why the default
5410 is a relatively low 200 million transactions.
5411 This parameter can only be set at server start, but the setting
5412 can be reduced for individual tables by
5413 changing storage parameters.
5414 For more information see <xref linkend="vacuum-for-wraparound">.
5419 <varlistentry id="guc-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
5420 <term><varname>autovacuum_multixact_freeze_max_age</varname> (<type>integer</type>)
5422 <primary><varname>autovacuum_multixact_freeze_max_age</varname> configuration parameter</primary>
5427 Specifies the maximum age (in multixacts) that a table's
5428 <structname>pg_class</>.<structfield>relminmxid</> field can
5429 attain before a <command>VACUUM</> operation is forced to
5430 prevent multixact ID wraparound within the table.
5431 Note that the system will launch autovacuum processes to
5432 prevent wraparound even when autovacuum is otherwise disabled.
5436 Vacuuming multixacts also allows removal of old files from the
5437 <filename>pg_multixact/members</> and <filename>pg_multixact/offsets</>
5438 subdirectories, which is why the default is a relatively low
5439 400 million multixacts.
5440 This parameter can only be set at server start, but the setting
5441 can be reduced for individual tables by changing storage parameters.
5442 For more information see <xref linkend="vacuum-for-multixact-wraparound">.
5447 <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
5448 <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)
5450 <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
5455 Specifies the cost delay value that will be used in automatic
5456 <command>VACUUM</> operations. If -1 is specified, the regular
5457 <xref linkend="guc-vacuum-cost-delay"> value will be used.
5458 The default value is 20 milliseconds.
5459 This parameter can only be set in the <filename>postgresql.conf</>
5460 file or on the server command line.
5461 This setting can be overridden for individual tables by
5462 changing storage parameters.
5467 <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
5468 <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)
5470 <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
5475 Specifies the cost limit value that will be used in automatic
5476 <command>VACUUM</> operations. If -1 is specified (which is the
5477 default), the regular
5478 <xref linkend="guc-vacuum-cost-limit"> value will be used. Note that
5479 the value is distributed proportionally among the running autovacuum
5480 workers, if there is more than one, so that the sum of the limits of
5481 each worker never exceeds the limit on this variable.
5482 This parameter can only be set in the <filename>postgresql.conf</>
5483 file or on the server command line.
5484 This setting can be overridden for individual tables by
5485 changing storage parameters.
5493 <sect1 id="runtime-config-client">
5494 <title>Client Connection Defaults</title>
5496 <sect2 id="runtime-config-client-statement">
5497 <title>Statement Behavior</title>
5500 <varlistentry id="guc-search-path" xreflabel="search_path">
5501 <term><varname>search_path</varname> (<type>string</type>)
5503 <primary><varname>search_path</> configuration parameter</primary>
5505 <indexterm><primary>path</><secondary>for schemas</></>
5509 This variable specifies the order in which schemas are searched
5510 when an object (table, data type, function, etc.) is referenced by a
5511 simple name with no schema specified. When there are objects of
5512 identical names in different schemas, the one found first
5513 in the search path is used. An object that is not in any of the
5514 schemas in the search path can only be referenced by specifying
5515 its containing schema with a qualified (dotted) name.
5519 The value for <varname>search_path</varname> must be a comma-separated
5520 list of schema names. Any name that is not an existing schema, or is
5521 a schema for which the user does not have <literal>USAGE</>
5522 permission, is silently ignored.
5526 If one of the list items is the special name
5527 <literal>$user</literal>, then the schema having the name returned by
5528 <function>SESSION_USER</> is substituted, if there is such a schema
5529 and the user has <literal>USAGE</> permission for it.
5530 (If not, <literal>$user</literal> is ignored.)
5534 The system catalog schema, <literal>pg_catalog</>, is always
5535 searched, whether it is mentioned in the path or not. If it is
5536 mentioned in the path then it will be searched in the specified
5537 order. If <literal>pg_catalog</> is not in the path then it will
5538 be searched <emphasis>before</> searching any of the path items.
5542 Likewise, the current session's temporary-table schema,
5543 <literal>pg_temp_<replaceable>nnn</></>, is always searched if it
5544 exists. It can be explicitly listed in the path by using the
5545 alias <literal>pg_temp</><indexterm><primary>pg_temp</></>. If it is not listed in the path then
5546 it is searched first (even before <literal>pg_catalog</>). However,
5547 the temporary schema is only searched for relation (table, view,
5548 sequence, etc) and data type names. It is never searched for
5549 function or operator names.
5553 When objects are created without specifying a particular target
5554 schema, they will be placed in the first valid schema named in
5555 <varname>search_path</varname>. An error is reported if the search
5560 The default value for this parameter is
5561 <literal>"$user", public</literal>.
5562 This setting supports shared use of a database (where no users
5563 have private schemas, and all share use of <literal>public</>),
5564 private per-user schemas, and combinations of these. Other
5565 effects can be obtained by altering the default search path
5566 setting, either globally or per-user.
5570 The current effective value of the search path can be examined
5571 via the <acronym>SQL</acronym> function
5572 <function>current_schemas</>
5573 (see <xref linkend="functions-info">).
5574 This is not quite the same as
5575 examining the value of <varname>search_path</varname>, since
5576 <function>current_schemas</> shows how the items
5577 appearing in <varname>search_path</varname> were resolved.
5581 For more information on schema handling, see <xref linkend="ddl-schemas">.
5586 <varlistentry id="guc-row-security" xreflabel="row_security">
5587 <term><varname>row_security</varname> (<type>enum</type>)
5589 <primary><varname>row_security</> configuration parameter</primary>
5594 This variable controls if row security policies are to be applied
5595 to queries which are run against tables that have row security enabled.
5596 The default is <literal>on</>. When set to <literal>on</>, all users,
5597 except superusers and the owner of the table, will have the row
5598 policies for the table applied to their queries. When set to
5599 <literal>off</>, queries will bypass row policies for the table, if
5600 possible, and error if not.
5604 For a user who is not a superuser and not the table owner to bypass
5605 row policies for the table, they must have the <literal>BYPASSRLS</>
5606 role attribute. If this is set to <literal>off</> and the user queries
5607 a table which has row policies enabled and the user does not have the
5608 right to bypass row policies then a permission denied error will be
5613 For more information on row security policies,
5614 see <xref linkend="SQL-CREATEPOLICY">.
5619 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
5620 <term><varname>default_tablespace</varname> (<type>string</type>)
5622 <primary><varname>default_tablespace</> configuration parameter</primary>
5624 <indexterm><primary>tablespace</><secondary>default</></>
5628 This variable specifies the default tablespace in which to create
5629 objects (tables and indexes) when a <command>CREATE</> command does
5630 not explicitly specify a tablespace.
5634 The value is either the name of a tablespace, or an empty string
5635 to specify using the default tablespace of the current database.
5636 If the value does not match the name of any existing tablespace,
5637 <productname>PostgreSQL</> will automatically use the default
5638 tablespace of the current database. If a nondefault tablespace
5639 is specified, the user must have <literal>CREATE</> privilege
5640 for it, or creation attempts will fail.
5644 This variable is not used for temporary tables; for them,
5645 <xref linkend="guc-temp-tablespaces"> is consulted instead.
5649 This variable is also not used when creating databases.
5650 By default, a new database inherits its tablespace setting from
5651 the template database it is copied from.
5655 For more information on tablespaces,
5656 see <xref linkend="manage-ag-tablespaces">.
5661 <varlistentry id="guc-temp-tablespaces" xreflabel="temp_tablespaces">
5662 <term><varname>temp_tablespaces</varname> (<type>string</type>)
5664 <primary><varname>temp_tablespaces</> configuration parameter</primary>
5666 <indexterm><primary>tablespace</><secondary>temporary</></>
5670 This variable specifies tablespaces in which to create temporary
5671 objects (temp tables and indexes on temp tables) when a
5672 <command>CREATE</> command does not explicitly specify a tablespace.
5673 Temporary files for purposes such as sorting large data sets
5674 are also created in these tablespaces.
5678 The value is a list of names of tablespaces. When there is more than
5679 one name in the list, <productname>PostgreSQL</> chooses a random
5680 member of the list each time a temporary object is to be created;
5681 except that within a transaction, successively created temporary
5682 objects are placed in successive tablespaces from the list.
5683 If the selected element of the list is an empty string,
5684 <productname>PostgreSQL</> will automatically use the default
5685 tablespace of the current database instead.
5689 When <varname>temp_tablespaces</> is set interactively, specifying a
5690 nonexistent tablespace is an error, as is specifying a tablespace for
5691 which the user does not have <literal>CREATE</> privilege. However,
5692 when using a previously set value, nonexistent tablespaces are
5693 ignored, as are tablespaces for which the user lacks
5694 <literal>CREATE</> privilege. In particular, this rule applies when
5695 using a value set in <filename>postgresql.conf</>.
5699 The default value is an empty string, which results in all temporary
5700 objects being created in the default tablespace of the current
5705 See also <xref linkend="guc-default-tablespace">.
5710 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
5711 <term><varname>check_function_bodies</varname> (<type>boolean</type>)
5713 <primary><varname>check_function_bodies</> configuration parameter</primary>
5718 This parameter is normally on. When set to <literal>off</>, it
5719 disables validation of the function body string during <xref
5720 linkend="sql-createfunction">. Disabling validation avoids side
5721 effects of the validation process and avoids false positives due
5722 to problems such as forward references. Set this parameter
5723 to <literal>off</> before loading functions on behalf of other
5724 users; <application>pg_dump</> does so automatically.
5729 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
5730 <term><varname>default_transaction_isolation</varname> (<type>enum</type>)
5732 <primary>transaction isolation level</primary>
5733 <secondary>setting default</secondary>
5736 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
5741 Each SQL transaction has an isolation level, which can be
5742 either <quote>read uncommitted</quote>, <quote>read
5743 committed</quote>, <quote>repeatable read</quote>, or
5744 <quote>serializable</quote>. This parameter controls the
5745 default isolation level of each new transaction. The default
5746 is <quote>read committed</quote>.
5750 Consult <xref linkend="mvcc"> and <xref
5751 linkend="sql-set-transaction"> for more information.
5756 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
5757 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)
5759 <primary>read-only transaction</primary>
5760 <secondary>setting default</secondary>
5763 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
5768 A read-only SQL transaction cannot alter non-temporary tables.
5769 This parameter controls the default read-only status of each new
5770 transaction. The default is <literal>off</> (read/write).
5774 Consult <xref linkend="sql-set-transaction"> for more information.
5779 <varlistentry id="guc-default-transaction-deferrable" xreflabel="default_transaction_deferrable">
5780 <term><varname>default_transaction_deferrable</varname> (<type>boolean</type>)
5782 <primary>deferrable transaction</primary>
5783 <secondary>setting default</secondary>
5786 <primary><varname>default_transaction_deferrable</> configuration parameter</primary>
5791 When running at the <literal>serializable</> isolation level,
5792 a deferrable read-only SQL transaction may be delayed before
5793 it is allowed to proceed. However, once it begins executing
5794 it does not incur any of the overhead required to ensure
5795 serializability; so serialization code will have no reason to
5796 force it to abort because of concurrent updates, making this
5797 option suitable for long-running read-only transactions.
5801 This parameter controls the default deferrable status of each
5802 new transaction. It currently has no effect on read-write
5803 transactions or those operating at isolation levels lower
5804 than <literal>serializable</>. The default is <literal>off</>.
5808 Consult <xref linkend="sql-set-transaction"> for more information.
5814 <varlistentry id="guc-session-replication-role" xreflabel="session_replication_role">
5815 <term><varname>session_replication_role</varname> (<type>enum</type>)
5817 <primary><varname>session_replication_role</> configuration parameter</primary>
5822 Controls firing of replication-related triggers and rules for the
5823 current session. Setting this variable requires
5824 superuser privilege and results in discarding any previously cached
5825 query plans. Possible values are <literal>origin</> (the default),
5826 <literal>replica</> and <literal>local</>.
5827 See <xref linkend="sql-altertable"> for
5833 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
5834 <term><varname>statement_timeout</varname> (<type>integer</type>)
5836 <primary><varname>statement_timeout</> configuration parameter</primary>
5841 Abort any statement that takes more than the specified number of
5842 milliseconds, starting from the time the command arrives at the server
5843 from the client. If <varname>log_min_error_statement</> is set to
5844 <literal>ERROR</> or lower, the statement that timed out will also be
5845 logged. A value of zero (the default) turns this off.
5849 Setting <varname>statement_timeout</> in
5850 <filename>postgresql.conf</> is not recommended because it would
5851 affect all sessions.
5856 <varlistentry id="guc-lock-timeout" xreflabel="lock_timeout">
5857 <term><varname>lock_timeout</varname> (<type>integer</type>)
5859 <primary><varname>lock_timeout</> configuration parameter</primary>
5864 Abort any statement that waits longer than the specified number of
5865 milliseconds while attempting to acquire a lock on a table, index,
5866 row, or other database object. The time limit applies separately to
5867 each lock acquisition attempt. The limit applies both to explicit
5868 locking requests (such as <command>LOCK TABLE</>, or <command>SELECT
5869 FOR UPDATE</> without <literal>NOWAIT</>) and to implicitly-acquired
5870 locks. If <varname>log_min_error_statement</> is set to
5871 <literal>ERROR</> or lower, the statement that timed out will be
5872 logged. A value of zero (the default) turns this off.
5876 Unlike <varname>statement_timeout</>, this timeout can only occur
5877 while waiting for locks. Note that if <varname>statement_timeout</>
5878 is nonzero, it is rather pointless to set <varname>lock_timeout</> to
5879 the same or larger value, since the statement timeout would always
5884 Setting <varname>lock_timeout</> in
5885 <filename>postgresql.conf</> is not recommended because it would
5886 affect all sessions.
5891 <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
5892 <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)
5894 <primary><varname>vacuum_freeze_table_age</> configuration parameter</primary>
5899 <command>VACUUM</> performs a whole-table scan if the table's
5900 <structname>pg_class</>.<structfield>relfrozenxid</> field has reached
5901 the age specified by this setting. The default is 150 million
5902 transactions. Although users can set this value anywhere from zero to
5903 two billions, <command>VACUUM</> will silently limit the effective value
5904 to 95% of <xref linkend="guc-autovacuum-freeze-max-age">, so that a
5905 periodical manual <command>VACUUM</> has a chance to run before an
5906 anti-wraparound autovacuum is launched for the table. For more
5908 <xref linkend="vacuum-for-wraparound">.
5913 <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
5914 <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)
5916 <primary><varname>vacuum_freeze_min_age</> configuration parameter</primary>
5921 Specifies the cutoff age (in transactions) that <command>VACUUM</>
5922 should use to decide whether to freeze row versions
5923 while scanning a table.
5924 The default is 50 million transactions. Although
5925 users can set this value anywhere from zero to one billion,
5926 <command>VACUUM</> will silently limit the effective value to half
5927 the value of <xref linkend="guc-autovacuum-freeze-max-age">, so
5928 that there is not an unreasonably short time between forced
5929 autovacuums. For more information see <xref
5930 linkend="vacuum-for-wraparound">.
5935 <varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
5936 <term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)
5938 <primary><varname>vacuum_multixact_freeze_table_age</> configuration parameter</primary>
5943 <command>VACUUM</> performs a whole-table scan if the table's
5944 <structname>pg_class</>.<structfield>relminmxid</> field has reached
5945 the age specified by this setting. The default is 150 million multixacts.
5946 Although users can set this value anywhere from zero to two billions,
5947 <command>VACUUM</> will silently limit the effective value to 95% of
5948 <xref linkend="guc-autovacuum-multixact-freeze-max-age">, so that a
5949 periodical manual <command>VACUUM</> has a chance to run before an
5950 anti-wraparound is launched for the table.
5951 For more information see <xref linkend="vacuum-for-multixact-wraparound">.
5956 <varlistentry id="guc-vacuum-multixact-freeze-min-age" xreflabel="vacuum_multixact_freeze_min_age">
5957 <term><varname>vacuum_multixact_freeze_min_age</varname> (<type>integer</type>)
5959 <primary><varname>vacuum_multixact_freeze_min_age</> configuration parameter</primary>
5964 Specifies the cutoff age (in multixacts) that <command>VACUUM</>
5965 should use to decide whether to replace multixact IDs with a newer
5966 transaction ID or multixact ID while scanning a table. The default
5967 is 5 million multixacts.
5968 Although users can set this value anywhere from zero to one billion,
5969 <command>VACUUM</> will silently limit the effective value to half
5970 the value of <xref linkend="guc-autovacuum-multixact-freeze-max-age">,
5971 so that there is not an unreasonably short time between forced
5973 For more information see <xref linkend="vacuum-for-multixact-wraparound">.
5978 <varlistentry id="guc-bytea-output" xreflabel="bytea_output">
5979 <term><varname>bytea_output</varname> (<type>enum</type>)
5981 <primary><varname>bytea_output</> configuration parameter</primary>
5986 Sets the output format for values of type <type>bytea</type>.
5987 Valid values are <literal>hex</literal> (the default)
5988 and <literal>escape</literal> (the traditional PostgreSQL
5989 format). See <xref linkend="datatype-binary"> for more
5990 information. The <type>bytea</type> type always
5991 accepts both formats on input, regardless of this setting.
5996 <varlistentry id="guc-xmlbinary" xreflabel="xmlbinary">
5997 <term><varname>xmlbinary</varname> (<type>enum</type>)
5999 <primary><varname>xmlbinary</> configuration parameter</primary>
6004 Sets how binary values are to be encoded in XML. This applies
6005 for example when <type>bytea</type> values are converted to
6006 XML by the functions <function>xmlelement</function> or
6007 <function>xmlforest</function>. Possible values are
6008 <literal>base64</literal> and <literal>hex</literal>, which
6009 are both defined in the XML Schema standard. The default is
6010 <literal>base64</literal>. For further information about
6011 XML-related functions, see <xref linkend="functions-xml">.
6015 The actual choice here is mostly a matter of taste,
6016 constrained only by possible restrictions in client
6017 applications. Both methods support all possible values,
6018 although the hex encoding will be somewhat larger than the
6024 <varlistentry id="guc-xmloption" xreflabel="xmloption">
6025 <term><varname>xmloption</varname> (<type>enum</type>)
6027 <primary><varname>xmloption</> configuration parameter</primary>
6030 <primary><varname>SET XML OPTION</></primary>
6033 <primary>XML option</primary>
6038 Sets whether <literal>DOCUMENT</literal> or
6039 <literal>CONTENT</literal> is implicit when converting between
6040 XML and character string values. See <xref
6041 linkend="datatype-xml"> for a description of this. Valid
6042 values are <literal>DOCUMENT</literal> and
6043 <literal>CONTENT</literal>. The default is
6044 <literal>CONTENT</literal>.
6048 According to the SQL standard, the command to set this option is
6050 SET XML OPTION { DOCUMENT | CONTENT };
6052 This syntax is also available in PostgreSQL.
6057 <varlistentry id="guc-gin-pending-list-limit" xreflabel="gin_pending_list_limit">
6058 <term><varname>gin_pending_list_limit</varname> (<type>integer</type>)
6060 <primary><varname>gin_pending_list_limit</> configuration parameter</primary>
6065 Sets the maximum size of the GIN pending list which is used
6066 when <literal>fastupdate</> is enabled. If the list grows
6067 larger than this maximum size, it is cleaned up by moving
6068 the entries in it to the main GIN data structure in bulk.
6069 The default is four megabytes (<literal>4MB</>). This setting
6070 can be overridden for individual GIN indexes by changing
6072 See <xref linkend="gin-fast-update"> and <xref linkend="gin-tips">
6073 for more information.
6080 <sect2 id="runtime-config-client-format">
6081 <title>Locale and Formatting</title>
6085 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
6086 <term><varname>DateStyle</varname> (<type>string</type>)
6088 <primary><varname>DateStyle</> configuration parameter</primary>
6093 Sets the display format for date and time values, as well as the
6094 rules for interpreting ambiguous date input values. For
6095 historical reasons, this variable contains two independent
6096 components: the output format specification (<literal>ISO</>,
6097 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
6098 and the input/output specification for year/month/day ordering
6099 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
6100 can be set separately or together. The keywords <literal>Euro</>
6101 and <literal>European</> are synonyms for <literal>DMY</>; the
6102 keywords <literal>US</>, <literal>NonEuro</>, and
6103 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
6104 <xref linkend="datatype-datetime"> for more information. The
6105 built-in default is <literal>ISO, MDY</>, but
6106 <application>initdb</application> will initialize the
6107 configuration file with a setting that corresponds to the
6108 behavior of the chosen <varname>lc_time</varname> locale.
6113 <varlistentry id="guc-intervalstyle" xreflabel="IntervalStyle">
6114 <term><varname>IntervalStyle</varname> (<type>enum</type>)
6116 <primary><varname>IntervalStyle</> configuration parameter</primary>
6121 Sets the display format for interval values.
6122 The value <literal>sql_standard</> will produce
6123 output matching <acronym>SQL</acronym> standard interval literals.
6124 The value <literal>postgres</> (which is the default) will produce
6125 output matching <productname>PostgreSQL</> releases prior to 8.4
6126 when the <xref linkend="guc-datestyle">
6127 parameter was set to <literal>ISO</>.
6128 The value <literal>postgres_verbose</> will produce output
6129 matching <productname>PostgreSQL</> releases prior to 8.4
6130 when the <varname>DateStyle</>
6131 parameter was set to non-<literal>ISO</> output.
6132 The value <literal>iso_8601</> will produce output matching the time
6133 interval <quote>format with designators</> defined in section
6134 4.4.3.2 of ISO 8601.
6137 The <varname>IntervalStyle</> parameter also affects the
6138 interpretation of ambiguous interval input. See
6139 <xref linkend="datatype-interval-input"> for more information.
6144 <varlistentry id="guc-timezone" xreflabel="TimeZone">
6145 <term><varname>TimeZone</varname> (<type>string</type>)
6147 <primary><varname>TimeZone</> configuration parameter</primary>
6149 <indexterm><primary>time zone</></>
6153 Sets the time zone for displaying and interpreting time stamps.
6154 The built-in default is <literal>GMT</>, but that is typically
6155 overridden in <filename>postgresql.conf</>; <application>initdb</>
6156 will install a setting there corresponding to its system environment.
6157 See <xref linkend="datatype-timezones"> for more information.
6162 <varlistentry id="guc-timezone-abbreviations" xreflabel="timezone_abbreviations">
6163 <term><varname>timezone_abbreviations</varname> (<type>string</type>)
6165 <primary><varname>timezone_abbreviations</> configuration parameter</primary>
6167 <indexterm><primary>time zone names</></>
6171 Sets the collection of time zone abbreviations that will be accepted
6172 by the server for datetime input. The default is <literal>'Default'</>,
6173 which is a collection that works in most of the world; there are
6174 also <literal>'Australia'</literal> and <literal>'India'</literal>,
6175 and other collections can be defined for a particular installation.
6176 See <xref linkend="datetime-config-files"> for more information.
6181 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
6182 <term><varname>extra_float_digits</varname> (<type>integer</type>)
6184 <primary>significant digits</primary>
6187 <primary>floating-point</primary>
6188 <secondary>display</secondary>
6191 <primary><varname>extra_float_digits</> configuration parameter</primary>
6196 This parameter adjusts the number of digits displayed for
6197 floating-point values, including <type>float4</>, <type>float8</>,
6198 and geometric data types. The parameter value is added to the
6199 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
6200 as appropriate). The value can be set as high as 3, to include
6201 partially-significant digits; this is especially useful for dumping
6202 float data that needs to be restored exactly. Or it can be set
6203 negative to suppress unwanted digits.
6204 See also <xref linkend="datatype-float">.
6209 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
6210 <term><varname>client_encoding</varname> (<type>string</type>)
6212 <primary><varname>client_encoding</> configuration parameter</primary>
6214 <indexterm><primary>character set</></>
6218 Sets the client-side encoding (character set).
6219 The default is to use the database encoding.
6220 The character sets supported by the <productname>PostgreSQL</productname>
6221 server are described in <xref linkend="multibyte-charset-supported">.
6226 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
6227 <term><varname>lc_messages</varname> (<type>string</type>)
6229 <primary><varname>lc_messages</> configuration parameter</primary>
6234 Sets the language in which messages are displayed. Acceptable
6235 values are system-dependent; see <xref linkend="locale"> for
6236 more information. If this variable is set to the empty string
6237 (which is the default) then the value is inherited from the
6238 execution environment of the server in a system-dependent way.
6242 On some systems, this locale category does not exist. Setting
6243 this variable will still work, but there will be no effect.
6244 Also, there is a chance that no translated messages for the
6245 desired language exist. In that case you will continue to see
6246 the English messages.
6250 Only superusers can change this setting, because it affects the
6251 messages sent to the server log as well as to the client, and
6252 an improper value might obscure the readability of the server
6258 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
6259 <term><varname>lc_monetary</varname> (<type>string</type>)
6261 <primary><varname>lc_monetary</> configuration parameter</primary>
6266 Sets the locale to use for formatting monetary amounts, for
6267 example with the <function>to_char</function> family of
6268 functions. Acceptable values are system-dependent; see <xref
6269 linkend="locale"> for more information. If this variable is
6270 set to the empty string (which is the default) then the value
6271 is inherited from the execution environment of the server in a
6272 system-dependent way.
6277 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
6278 <term><varname>lc_numeric</varname> (<type>string</type>)
6280 <primary><varname>lc_numeric</> configuration parameter</primary>
6285 Sets the locale to use for formatting numbers, for example
6286 with the <function>to_char</function> family of
6287 functions. Acceptable values are system-dependent; see <xref
6288 linkend="locale"> for more information. If this variable is
6289 set to the empty string (which is the default) then the value
6290 is inherited from the execution environment of the server in a
6291 system-dependent way.
6296 <varlistentry id="guc-lc-time" xreflabel="lc_time">
6297 <term><varname>lc_time</varname> (<type>string</type>)
6299 <primary><varname>lc_time</> configuration parameter</primary>
6304 Sets the locale to use for formatting dates and times, for example
6305 with the <function>to_char</function> family of
6306 functions. Acceptable values are system-dependent; see <xref
6307 linkend="locale"> for more information. If this variable is
6308 set to the empty string (which is the default) then the value
6309 is inherited from the execution environment of the server in a
6310 system-dependent way.
6315 <varlistentry id="guc-default-text-search-config" xreflabel="default_text_search_config">
6316 <term><varname>default_text_search_config</varname> (<type>string</type>)
6318 <primary><varname>default_text_search_config</> configuration parameter</primary>
6323 Selects the text search configuration that is used by those variants
6324 of the text search functions that do not have an explicit argument
6325 specifying the configuration.
6326 See <xref linkend="textsearch"> for further information.
6327 The built-in default is <literal>pg_catalog.simple</>, but
6328 <application>initdb</application> will initialize the
6329 configuration file with a setting that corresponds to the
6330 chosen <varname>lc_ctype</varname> locale, if a configuration
6331 matching that locale can be identified.
6340 <sect2 id="runtime-config-client-preload">
6341 <title>Shared Library Preloading</title>
6344 Several settings are available for preloading shared libraries into the
6345 server, in order to load additional functionality or achieve performance
6346 benefits. For example, a setting of
6347 <literal>'$libdir/mylib'</literal> would cause
6348 <literal>mylib.so</> (or on some platforms,
6349 <literal>mylib.sl</>) to be preloaded from the installation's standard
6350 library directory. The differences between the settings are when they
6351 take effect and what privileges are required to change them.
6355 <productname>PostgreSQL</productname> procedural language libraries can
6356 be preloaded in this way, typically by using the
6357 syntax <literal>'$libdir/plXXX'</literal> where
6358 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
6359 <literal>tcl</>, or <literal>python</>.
6363 For each parameter, if more than one library is to be loaded, separate
6364 their names with commas. All library names are converted to lower case
6365 unless double-quoted.
6369 Only shared libraries specifically intended to be used with PostgreSQL
6370 can be loaded this way. Every PostgreSQL-supported library has
6371 a <quote>magic block</> that is checked to guarantee compatibility. For
6372 this reason, non-PostgreSQL libraries cannot be loaded in this way. You
6373 might be able to use operating-system facilities such
6374 as <envar>LD_PRELOAD</envar> for that.
6378 In general, refer to the documentation of a specific module for the
6379 recommended way to load that module.
6383 <varlistentry id="guc-local-preload-libraries" xreflabel="local_preload_libraries">
6384 <term><varname>local_preload_libraries</varname> (<type>string</type>)
6386 <primary><varname>local_preload_libraries</> configuration parameter</primary>
6389 <primary><filename>$libdir/plugins</></primary>
6394 This variable specifies one or more shared libraries that are to be
6395 preloaded at connection start.
6396 The parameter value only takes effect at the start of the connection.
6397 Subsequent changes have no effect. If a specified library is not
6398 found, the connection attempt will fail.
6402 This option can be set by any user. Because of that, the libraries
6403 that can be loaded are restricted to those appearing in the
6404 <filename>plugins</> subdirectory of the installation's
6405 standard library directory. (It is the database administrator's
6406 responsibility to ensure that only <quote>safe</> libraries
6407 are installed there.) Entries in <varname>local_preload_libraries</>
6408 can specify this directory explicitly, for example
6409 <literal>$libdir/plugins/mylib</literal>, or just specify
6410 the library name — <literal>mylib</literal> would have
6411 the same effect as <literal>$libdir/plugins/mylib</literal>.
6415 The intent of this feature is to allow unprivileged users to load
6416 debugging or performance-measurement libraries into specific sessions
6417 without requiring an explicit <command>LOAD</> command. To that end,
6418 it would be typical to set this parameter using
6419 the <envar>PGOPTIONS</envar> environment variable on the client or by
6421 <command>ALTER ROLE SET</>.
6425 However, unless a module is specifically designed to be used in this way by
6426 non-superusers, this is usually not the right setting to use. Look
6427 at <xref linkend="guc-session-preload-libraries"> instead.
6433 <varlistentry id="guc-session-preload-libraries" xreflabel="session_preload_libraries">
6434 <term><varname>session_preload_libraries</varname> (<type>string</type>)
6436 <primary><varname>session_preload_libraries</> configuration parameter</primary>
6441 This variable specifies one or more shared libraries that are to be
6442 preloaded at connection start. Only superusers can change this setting.
6443 The parameter value only takes effect at the start of the connection.
6444 Subsequent changes have no effect. If a specified library is not
6445 found, the connection attempt will fail.
6449 The intent of this feature is to allow debugging or
6450 performance-measurement libraries to be loaded into specific sessions
6452 <command>LOAD</> command being given. For
6453 example, <xref linkend="auto-explain"> could be enabled for all
6454 sessions under a given user name by setting this parameter
6455 with <command>ALTER ROLE SET</>. Also, this parameter can be changed
6456 without restarting the server (but changes only take effect when a new
6457 session is started), so it is easier to add new modules this way, even
6458 if they should apply to all sessions.
6462 Unlike <xref linkend="guc-shared-preload-libraries">, there is no large
6463 performance advantage to loading a library at session start rather than
6464 when it is first used. There is some advantage, however, when
6465 connection pooling is used.
6470 <varlistentry id="guc-shared-preload-libraries" xreflabel="shared_preload_libraries">
6471 <term><varname>shared_preload_libraries</varname> (<type>string</type>)
6473 <primary><varname>shared_preload_libraries</> configuration parameter</primary>
6478 This variable specifies one or more shared libraries to be preloaded at
6479 server start. This parameter can only be set at server
6480 start. If a specified library is not found, the server will fail to
6485 Some libraries need to perform certain operations that can only take
6486 place at postmaster start, such as allocating shared memory, reserving
6487 light-weight locks, or starting background workers. Those libraries
6488 must be loaded at server start through this parameter. See the
6489 documentation of each library for details.
6493 Other libraries can also be preloaded. By preloading a shared library,
6494 the library startup time is avoided when the library is first used.
6495 However, the time to start each new server process might increase
6496 slightly, even if that process never uses the library. So this
6497 parameter is recommended only for libraries that will be used in most
6498 sessions. Also, changing this parameter requires a server restart, so
6499 this is not the right setting to use for short-term debugging tasks,
6500 say. Use <xref linkend="guc-session-preload-libraries"> for that
6506 On Windows hosts, preloading a library at server start will not reduce
6507 the time required to start each new server process; each server process
6508 will re-load all preload libraries. However, <varname>shared_preload_libraries
6509 </varname> is still useful on Windows hosts for libraries that need to
6510 perform operations at postmaster start time.
6518 <sect2 id="runtime-config-client-other">
6519 <title>Other Defaults</title>
6523 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
6524 <term><varname>dynamic_library_path</varname> (<type>string</type>)
6526 <primary><varname>dynamic_library_path</> configuration parameter</primary>
6528 <indexterm><primary>dynamic loading</></>
6532 If a dynamically loadable module needs to be opened and the
6533 file name specified in the <command>CREATE FUNCTION</command> or
6534 <command>LOAD</command> command
6535 does not have a directory component (i.e., the
6536 name does not contain a slash), the system will search this
6537 path for the required file.
6541 The value for <varname>dynamic_library_path</varname> must be a
6542 list of absolute directory paths separated by colons (or semi-colons
6543 on Windows). If a list element starts
6544 with the special string <literal>$libdir</literal>, the
6545 compiled-in <productname>PostgreSQL</productname> package
6546 library directory is substituted for <literal>$libdir</literal>; this
6547 is where the modules provided by the standard
6548 <productname>PostgreSQL</productname> distribution are installed.
6549 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
6550 this directory.) For example:
6552 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
6554 or, in a Windows environment:
6556 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
6561 The default value for this parameter is
6562 <literal>'$libdir'</literal>. If the value is set to an empty
6563 string, the automatic path search is turned off.
6567 This parameter can be changed at run time by superusers, but a
6568 setting done that way will only persist until the end of the
6569 client connection, so this method should be reserved for
6570 development purposes. The recommended way to set this parameter
6571 is in the <filename>postgresql.conf</filename> configuration
6577 <varlistentry id="guc-gin-fuzzy-search-limit" xreflabel="gin_fuzzy_search_limit">
6578 <term><varname>gin_fuzzy_search_limit</varname> (<type>integer</type>)
6580 <primary><varname>gin_fuzzy_search_limit</> configuration parameter</primary>
6585 Soft upper limit of the size of the set returned by GIN index scans. For more
6586 information see <xref linkend="gin-tips">.
6595 <sect1 id="runtime-config-locks">
6596 <title>Lock Management</title>
6600 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
6601 <term><varname>deadlock_timeout</varname> (<type>integer</type>)
6603 <primary>deadlock</primary>
6604 <secondary>timeout during</secondary>
6607 <primary>timeout</primary>
6608 <secondary>deadlock</secondary>
6611 <primary><varname>deadlock_timeout</> configuration parameter</primary>
6616 This is the amount of time, in milliseconds, to wait on a lock
6617 before checking to see if there is a deadlock condition. The
6618 check for deadlock is relatively expensive, so the server doesn't run
6619 it every time it waits for a lock. We optimistically assume
6620 that deadlocks are not common in production applications and
6621 just wait on the lock for a while before checking for a
6622 deadlock. Increasing this value reduces the amount of time
6623 wasted in needless deadlock checks, but slows down reporting of
6624 real deadlock errors. The default is one second (<literal>1s</>),
6625 which is probably about the smallest value you would want in
6626 practice. On a heavily loaded server you might want to raise it.
6627 Ideally the setting should exceed your typical transaction time,
6628 so as to improve the odds that a lock will be released before
6629 the waiter decides to check for deadlock. Only superusers can change
6634 When <xref linkend="guc-log-lock-waits"> is set,
6635 this parameter also determines the length of time to wait before
6636 a log message is issued about the lock wait. If you are trying
6637 to investigate locking delays you might want to set a shorter than
6638 normal <varname>deadlock_timeout</varname>.
6643 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
6644 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)
6646 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
6651 The shared lock table tracks locks on
6652 <varname>max_locks_per_transaction</varname> * (<xref
6653 linkend="guc-max-connections"> + <xref
6654 linkend="guc-max-prepared-transactions">) objects (e.g., tables);
6655 hence, no more than this many distinct objects can be locked at
6656 any one time. This parameter controls the average number of object
6657 locks allocated for each transaction; individual transactions
6658 can lock more objects as long as the locks of all transactions
6659 fit in the lock table. This is <emphasis>not</> the number of
6660 rows that can be locked; that value is unlimited. The default,
6661 64, has historically proven sufficient, but you might need to
6662 raise this value if you have queries that touch many different
6663 tables in a single transaction, e.g. query of a parent table with
6664 many children. This parameter can only be set at server start.
6668 When running a standby server, you must set this parameter to the
6669 same or higher value than on the master server. Otherwise, queries
6670 will not be allowed in the standby server.
6675 <varlistentry id="guc-max-pred-locks-per-transaction" xreflabel="max_pred_locks_per_transaction">
6676 <term><varname>max_pred_locks_per_transaction</varname> (<type>integer</type>)
6678 <primary><varname>max_pred_locks_per_transaction</> configuration parameter</primary>
6683 The shared predicate lock table tracks locks on
6684 <varname>max_pred_locks_per_transaction</varname> * (<xref
6685 linkend="guc-max-connections"> + <xref
6686 linkend="guc-max-prepared-transactions">) objects (e.g., tables);
6687 hence, no more than this many distinct objects can be locked at
6688 any one time. This parameter controls the average number of object
6689 locks allocated for each transaction; individual transactions
6690 can lock more objects as long as the locks of all transactions
6691 fit in the lock table. This is <emphasis>not</> the number of
6692 rows that can be locked; that value is unlimited. The default,
6693 64, has generally been sufficient in testing, but you might need to
6694 raise this value if you have clients that touch many different
6695 tables in a single serializable transaction. This parameter can
6696 only be set at server start.
6705 <sect1 id="runtime-config-compatible">
6706 <title>Version and Platform Compatibility</title>
6708 <sect2 id="runtime-config-compatible-version">
6709 <title>Previous PostgreSQL Versions</title>
6713 <varlistentry id="guc-array-nulls" xreflabel="array_nulls">
6714 <term><varname>array_nulls</varname> (<type>boolean</type>)
6716 <primary><varname>array_nulls</> configuration parameter</primary>
6721 This controls whether the array input parser recognizes
6722 unquoted <literal>NULL</> as specifying a null array element.
6723 By default, this is <literal>on</>, allowing array values containing
6724 null values to be entered. However, <productname>PostgreSQL</> versions
6725 before 8.2 did not support null values in arrays, and therefore would
6726 treat <literal>NULL</> as specifying a normal array element with
6727 the string value <quote>NULL</>. For backward compatibility with
6728 applications that require the old behavior, this variable can be
6729 turned <literal>off</>.
6733 Note that it is possible to create array values containing null values
6734 even when this variable is <literal>off</>.
6739 <varlistentry id="guc-backslash-quote" xreflabel="backslash_quote">
6740 <term><varname>backslash_quote</varname> (<type>enum</type>)
6741 <indexterm><primary>strings</><secondary>backslash quotes</></>
6743 <primary><varname>backslash_quote</> configuration parameter</primary>
6748 This controls whether a quote mark can be represented by
6749 <literal>\'</> in a string literal. The preferred, SQL-standard way
6750 to represent a quote mark is by doubling it (<literal>''</>) but
6751 <productname>PostgreSQL</> has historically also accepted
6752 <literal>\'</>. However, use of <literal>\'</> creates security risks
6753 because in some client character set encodings, there are multibyte
6754 characters in which the last byte is numerically equivalent to ASCII
6755 <literal>\</>. If client-side code does escaping incorrectly then a
6756 SQL-injection attack is possible. This risk can be prevented by
6757 making the server reject queries in which a quote mark appears to be
6758 escaped by a backslash.
6759 The allowed values of <varname>backslash_quote</> are
6760 <literal>on</> (allow <literal>\'</> always),
6761 <literal>off</> (reject always), and
6762 <literal>safe_encoding</> (allow only if client encoding does not
6763 allow ASCII <literal>\</> within a multibyte character).
6764 <literal>safe_encoding</> is the default setting.
6768 Note that in a standard-conforming string literal, <literal>\</> just
6769 means <literal>\</> anyway. This parameter only affects the handling of
6770 non-standard-conforming literals, including
6771 escape string syntax (<literal>E'...'</>).
6776 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
6777 <term><varname>default_with_oids</varname> (<type>boolean</type>)
6779 <primary><varname>default_with_oids</> configuration parameter</primary>
6784 This controls whether <command>CREATE TABLE</command> and
6785 <command>CREATE TABLE AS</command> include an OID column in
6786 newly-created tables, if neither <literal>WITH OIDS</literal>
6787 nor <literal>WITHOUT OIDS</literal> is specified. It also
6788 determines whether OIDs will be included in tables created by
6789 <command>SELECT INTO</command>. The parameter is <literal>off</>
6790 by default; in <productname>PostgreSQL</> 8.0 and earlier, it
6791 was <literal>on</> by default.
6795 The use of OIDs in user tables is considered deprecated, so
6796 most installations should leave this variable disabled.
6797 Applications that require OIDs for a particular table should
6798 specify <literal>WITH OIDS</literal> when creating the
6799 table. This variable can be enabled for compatibility with old
6800 applications that do not follow this behavior.
6805 <varlistentry id="guc-escape-string-warning" xreflabel="escape_string_warning">
6806 <term><varname>escape_string_warning</varname> (<type>boolean</type>)
6807 <indexterm><primary>strings</><secondary>escape warning</></>
6809 <primary><varname>escape_string_warning</> configuration parameter</primary>
6814 When on, a warning is issued if a backslash (<literal>\</>)
6815 appears in an ordinary string literal (<literal>'...'</>
6816 syntax) and <varname>standard_conforming_strings</varname> is off.
6817 The default is <literal>on</>.
6820 Applications that wish to use backslash as escape should be
6821 modified to use escape string syntax (<literal>E'...'</>),
6822 because the default behavior of ordinary strings is now to treat
6823 backslash as an ordinary character, per SQL standard. This variable
6824 can be enabled to help locate code that needs to be changed.
6829 <varlistentry id="guc-lo-compat-privileges" xreflabel="lo_compat_privileges">
6830 <term><varname>lo_compat_privileges</varname> (<type>boolean</type>)
6832 <primary><varname>lo_compat_privileges</> configuration parameter</primary>
6837 In <productname>PostgreSQL</> releases prior to 9.0, large objects
6838 did not have access privileges and were, therefore, always readable
6839 and writable by all users. Setting this variable to <literal>on</>
6840 disables the new privilege checks, for compatibility with prior
6841 releases. The default is <literal>off</>.
6842 Only superusers can change this setting.
6845 Setting this variable does not disable all security checks related to
6846 large objects — only those for which the default behavior has
6847 changed in <productname>PostgreSQL</> 9.0.
6848 For example, <literal>lo_import()</literal> and
6849 <literal>lo_export()</literal> need superuser privileges regardless
6855 <varlistentry id="guc-operator-precedence-warning" xreflabel="operator_precedence_warning">
6856 <term><varname>operator_precedence_warning</varname> (<type>boolean</type>)
6858 <primary><varname>operator_precedence_warning</> configuration parameter</primary>
6863 When on, the parser will emit a warning for any construct that might
6864 have changed meanings since <productname>PostgreSQL</> 9.4 as a result
6865 of changes in operator precedence. This is useful for auditing
6866 applications to see if precedence changes have broken anything; but it
6867 is not meant to be kept turned on in production, since it will warn
6868 about some perfectly valid, standard-compliant SQL code.
6869 The default is <literal>off</>.
6873 See <xref linkend="sql-precedence"> for more information.
6878 <varlistentry id="guc-quote-all-identifiers" xreflabel="quote-all-identifiers">
6879 <term><varname>quote_all_identifiers</varname> (<type>boolean</type>)
6881 <primary><varname>quote_all_identifiers</> configuration parameter</primary>
6886 When the database generates SQL, force all identifiers to be quoted,
6887 even if they are not (currently) keywords. This will affect the
6888 output of <command>EXPLAIN</> as well as the results of functions
6889 like <function>pg_get_viewdef</>. See also the
6890 <option>--quote-all-identifiers</option> option of
6891 <xref linkend="app-pgdump"> and <xref linkend="app-pg-dumpall">.
6896 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
6897 <term><varname>sql_inheritance</varname> (<type>boolean</type>)
6899 <primary><varname>sql_inheritance</> configuration parameter</primary>
6901 <indexterm><primary>inheritance</></>
6905 This setting controls whether undecorated table references are
6906 considered to include inheritance child tables. The default is
6907 <literal>on</>, which means child tables are included (thus,
6908 a <literal>*</> suffix is assumed by default). If turned
6909 <literal>off</>, child tables are not included (thus, an
6910 <literal>ONLY</literal> prefix is assumed). The SQL standard
6911 requires child tables to be included, so the <literal>off</> setting
6912 is not spec-compliant, but it is provided for compatibility with
6913 <productname>PostgreSQL</> releases prior to 7.1.
6914 See <xref linkend="ddl-inherit"> for more information.
6918 Turning <varname>sql_inheritance</> off is deprecated, because that
6919 behavior has been found to be error-prone as well as contrary to SQL
6920 standard. Discussions of inheritance behavior elsewhere in this
6921 manual generally assume that it is <literal>on</>.
6926 <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
6927 <term><varname>standard_conforming_strings</varname> (<type>boolean</type>)
6928 <indexterm><primary>strings</><secondary>standard conforming</></>
6930 <primary><varname>standard_conforming_strings</> configuration parameter</primary>
6935 This controls whether ordinary string literals
6936 (<literal>'...'</>) treat backslashes literally, as specified in
6938 Beginning in <productname>PostgreSQL</productname> 9.1, the default is
6939 <literal>on</> (prior releases defaulted to <literal>off</>).
6940 Applications can check this
6941 parameter to determine how string literals will be processed.
6942 The presence of this parameter can also be taken as an indication
6943 that the escape string syntax (<literal>E'...'</>) is supported.
6944 Escape string syntax (<xref linkend="sql-syntax-strings-escape">)
6945 should be used if an application desires
6946 backslashes to be treated as escape characters.
6951 <varlistentry id="guc-synchronize-seqscans" xreflabel="synchronize_seqscans">
6952 <term><varname>synchronize_seqscans</varname> (<type>boolean</type>)
6954 <primary><varname>synchronize_seqscans</> configuration parameter</primary>
6959 This allows sequential scans of large tables to synchronize with each
6960 other, so that concurrent scans read the same block at about the
6961 same time and hence share the I/O workload. When this is enabled,
6962 a scan might start in the middle of the table and then <quote>wrap
6963 around</> the end to cover all rows, so as to synchronize with the
6964 activity of scans already in progress. This can result in
6965 unpredictable changes in the row ordering returned by queries that
6966 have no <literal>ORDER BY</> clause. Setting this parameter to
6967 <literal>off</> ensures the pre-8.3 behavior in which a sequential
6968 scan always starts from the beginning of the table. The default
6977 <sect2 id="runtime-config-compatible-clients">
6978 <title>Platform and Client Compatibility</title>
6981 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
6982 <term><varname>transform_null_equals</varname> (<type>boolean</type>)
6983 <indexterm><primary>IS NULL</></>
6985 <primary><varname>transform_null_equals</> configuration parameter</primary>
6990 When on, expressions of the form <literal><replaceable>expr</> =
6991 NULL</literal> (or <literal>NULL =
6992 <replaceable>expr</></literal>) are treated as
6993 <literal><replaceable>expr</> IS NULL</literal>, that is, they
6994 return true if <replaceable>expr</> evaluates to the null value,
6995 and false otherwise. The correct SQL-spec-compliant behavior of
6996 <literal><replaceable>expr</> = NULL</literal> is to always
6997 return null (unknown). Therefore this parameter defaults to
7002 However, filtered forms in <productname>Microsoft
7003 Access</productname> generate queries that appear to use
7004 <literal><replaceable>expr</> = NULL</literal> to test for
7005 null values, so if you use that interface to access the database you
7006 might want to turn this option on. Since expressions of the
7007 form <literal><replaceable>expr</> = NULL</literal> always
7008 return the null value (using the SQL standard interpretation), they are not
7009 very useful and do not appear often in normal applications so
7010 this option does little harm in practice. But new users are
7011 frequently confused about the semantics of expressions
7012 involving null values, so this option is off by default.
7016 Note that this option only affects the exact form <literal>= NULL</>,
7017 not other comparison operators or other expressions
7018 that are computationally equivalent to some expression
7019 involving the equals operator (such as <literal>IN</literal>).
7020 Thus, this option is not a general fix for bad programming.
7024 Refer to <xref linkend="functions-comparison"> for related information.
7033 <sect1 id="runtime-config-error-handling">
7034 <title>Error Handling</title>
7038 <varlistentry id="guc-exit-on-error" xreflabel="exit_on_error">
7039 <term><varname>exit_on_error</varname> (<type>boolean</type>)
7041 <primary><varname>exit_on_error</> configuration parameter</primary>
7046 If true, any error will terminate the current session. By default,
7047 this is set to false, so that only FATAL errors will terminate the
7053 <varlistentry id="guc-restart-after-crash" xreflabel="restart_after_crash">
7054 <term><varname>restart_after_crash</varname> (<type>boolean</type>)
7056 <primary><varname>restart_after_crash</> configuration parameter</primary>
7061 When set to true, which is the default, <productname>PostgreSQL</>
7062 will automatically reinitialize after a backend crash. Leaving this
7063 value set to true is normally the best way to maximize the availability
7064 of the database. However, in some circumstances, such as when
7065 <productname>PostgreSQL</> is being invoked by clusterware, it may be
7066 useful to disable the restart so that the clusterware can gain
7067 control and take any actions it deems appropriate.
7076 <sect1 id="runtime-config-preset">
7077 <title>Preset Options</title>
7080 The following <quote>parameters</> are read-only, and are determined
7081 when <productname>PostgreSQL</productname> is compiled or when it is
7082 installed. As such, they have been excluded from the sample
7083 <filename>postgresql.conf</> file. These options report
7084 various aspects of <productname>PostgreSQL</productname> behavior
7085 that might be of interest to certain applications, particularly
7086 administrative front-ends.
7091 <varlistentry id="guc-block-size" xreflabel="block_size">
7092 <term><varname>block_size</varname> (<type>integer</type>)
7094 <primary><varname>block_size</> configuration parameter</primary>
7099 Reports the size of a disk block. It is determined by the value
7100 of <literal>BLCKSZ</> when building the server. The default
7101 value is 8192 bytes. The meaning of some configuration
7102 variables (such as <xref linkend="guc-shared-buffers">) is
7103 influenced by <varname>block_size</varname>. See <xref
7104 linkend="runtime-config-resource"> for information.
7109 <varlistentry id="guc-data-checksums" xreflabel="data_checksums">
7110 <term><varname>data_checksums</varname> (<type>boolean</type>)
7112 <primary><varname>data_checksums</> configuration parameter</primary>
7117 Reports whether data checksums are enabled for this cluster.
7118 See <xref linkend="app-initdb-data-checksums"> for more information.
7123 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
7124 <term><varname>debug_assertions</varname> (<type>boolean</type>)
7126 <primary><varname>debug_assertions</> configuration parameter</primary>
7131 Reports whether <productname>PostgreSQL</productname> has been built
7132 with assertions enabled. That is the case if the
7133 macro <symbol>USE_ASSERT_CHECKING</symbol> is defined
7134 when <productname>PostgreSQL</productname> is built (accomplished
7135 e.g. by the <command>configure</command> option
7136 <option>--enable-cassert</option>). By
7137 default <productname>PostgreSQL</productname> is built without
7143 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
7144 <term><varname>integer_datetimes</varname> (<type>boolean</type>)
7146 <primary><varname>integer_datetimes</> configuration parameter</primary>
7151 Reports whether <productname>PostgreSQL</> was built with
7152 support for 64-bit-integer dates and times. This can be
7153 disabled by configuring with <literal>--disable-integer-datetimes</>
7154 when building <productname>PostgreSQL</>. The default value is
7155 <literal>on</literal>.
7160 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
7161 <term><varname>lc_collate</varname> (<type>string</type>)
7163 <primary><varname>lc_collate</> configuration parameter</primary>
7168 Reports the locale in which sorting of textual data is done.
7169 See <xref linkend="locale"> for more information.
7170 This value is determined when a database is created.
7175 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
7176 <term><varname>lc_ctype</varname> (<type>string</type>)
7178 <primary><varname>lc_ctype</> configuration parameter</primary>
7183 Reports the locale that determines character classifications.
7184 See <xref linkend="locale"> for more information.
7185 This value is determined when a database is created.
7186 Ordinarily this will be the same as <varname>lc_collate</varname>,
7187 but for special applications it might be set differently.
7192 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
7193 <term><varname>max_function_args</varname> (<type>integer</type>)
7195 <primary><varname>max_function_args</> configuration parameter</primary>
7200 Reports the maximum number of function arguments. It is determined by
7201 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
7202 default value is 100 arguments.
7207 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
7208 <term><varname>max_identifier_length</varname> (<type>integer</type>)
7210 <primary><varname>max_identifier_length</> configuration parameter</primary>
7215 Reports the maximum identifier length. It is determined as one
7216 less than the value of <literal>NAMEDATALEN</> when building
7217 the server. The default value of <literal>NAMEDATALEN</> is
7218 64; therefore the default
7219 <varname>max_identifier_length</varname> is 63 bytes, which
7220 can be less than 63 characters when using multibyte encodings.
7225 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
7226 <term><varname>max_index_keys</varname> (<type>integer</type>)
7228 <primary><varname>max_index_keys</> configuration parameter</primary>
7233 Reports the maximum number of index keys. It is determined by
7234 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
7235 default value is 32 keys.
7240 <varlistentry id="guc-segment-size" xreflabel="segment_size">
7241 <term><varname>segment_size</varname> (<type>integer</type>)
7243 <primary><varname>segment_size</> configuration parameter</primary>
7248 Reports the number of blocks (pages) that can be stored within a file
7249 segment. It is determined by the value of <literal>RELSEG_SIZE</>
7250 when building the server. The maximum size of a segment file in bytes
7251 is equal to <varname>segment_size</> multiplied by
7252 <varname>block_size</>; by default this is 1GB.
7257 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
7258 <term><varname>server_encoding</varname> (<type>string</type>)
7260 <primary><varname>server_encoding</> configuration parameter</primary>
7262 <indexterm><primary>character set</></>
7266 Reports the database encoding (character set).
7267 It is determined when the database is created. Ordinarily,
7268 clients need only be concerned with the value of <xref
7269 linkend="guc-client-encoding">.
7274 <varlistentry id="guc-server-version" xreflabel="server_version">
7275 <term><varname>server_version</varname> (<type>string</type>)
7277 <primary><varname>server_version</> configuration parameter</primary>
7282 Reports the version number of the server. It is determined by the
7283 value of <literal>PG_VERSION</> when building the server.
7288 <varlistentry id="guc-server-version-num" xreflabel="server_version_num">
7289 <term><varname>server_version_num</varname> (<type>integer</type>)
7291 <primary><varname>server_version_num</> configuration parameter</primary>
7296 Reports the version number of the server as an integer. It is determined
7297 by the value of <literal>PG_VERSION_NUM</> when building the server.
7302 <varlistentry id="guc-wal-block-size" xreflabel="wal_block_size">
7303 <term><varname>wal_block_size</varname> (<type>integer</type>)
7305 <primary><varname>wal_block_size</> configuration parameter</primary>
7310 Reports the size of a WAL disk block. It is determined by the value
7311 of <literal>XLOG_BLCKSZ</> when building the server. The default value
7317 <varlistentry id="guc-wal-segment-size" xreflabel="wal_segment_size">
7318 <term><varname>wal_segment_size</varname> (<type>integer</type>)
7320 <primary><varname>wal_segment_size</> configuration parameter</primary>
7325 Reports the number of blocks (pages) in a WAL segment file.
7326 The total size of a WAL segment file in bytes is equal to
7327 <varname>wal_segment_size</> multiplied by <varname>wal_block_size</>;
7328 by default this is 16MB. See <xref linkend="wal-configuration"> for
7337 <sect1 id="runtime-config-custom">
7338 <title>Customized Options</title>
7341 This feature was designed to allow parameters not normally known to
7342 <productname>PostgreSQL</productname> to be added by add-on modules
7343 (such as procedural languages). This allows extension modules to be
7344 configured in the standard ways.
7348 Custom options have two-part names: an extension name, then a dot, then
7349 the parameter name proper, much like qualified names in SQL. An example
7350 is <literal>plpgsql.variable_conflict</>.
7354 Because custom options may need to be set in processes that have not
7355 loaded the relevant extension module, <productname>PostgreSQL</>
7356 will accept a setting for any two-part parameter name. Such variables
7357 are treated as placeholders and have no function until the module that
7358 defines them is loaded. When an extension module is loaded, it will add
7359 its variable definitions, convert any placeholder values according to
7360 those definitions, and issue warnings for any unrecognized placeholders
7361 that begin with its extension name.
7365 <sect1 id="runtime-config-developer">
7366 <title>Developer Options</title>
7369 The following parameters are intended for work on the
7370 <productname>PostgreSQL</productname> source code, and in some cases
7371 to assist with recovery of severely damaged databases. There
7372 should be no reason to use them on a production database.
7373 As such, they have been excluded from the sample
7374 <filename>postgresql.conf</> file. Note that many of these
7375 parameters require special source compilation flags to work at all.
7379 <varlistentry id="guc-allow-system-table-mods" xreflabel="allow_system_table_mods">
7380 <term><varname>allow_system_table_mods</varname> (<type>boolean</type>)
7382 <primary><varname>allow_system_table_mods</varname> configuration parameter</primary>
7387 Allows modification of the structure of system tables.
7388 This is used by <command>initdb</command>.
7389 This parameter can only be set at server start.
7394 <varlistentry id="guc-ignore-system-indexes" xreflabel="ignore_system_indexes">
7395 <term><varname>ignore_system_indexes</varname> (<type>boolean</type>)
7397 <primary><varname>ignore_system_indexes</varname> configuration parameter</primary>
7402 Ignore system indexes when reading system tables (but still
7403 update the indexes when modifying the tables). This is useful
7404 when recovering from damaged system indexes.
7405 This parameter cannot be changed after session start.
7410 <varlistentry id="guc-post-auth-delay" xreflabel="post_auth_delay">
7411 <term><varname>post_auth_delay</varname> (<type>integer</type>)
7413 <primary><varname>post_auth_delay</> configuration parameter</primary>
7418 If nonzero, a delay of this many seconds occurs when a new
7419 server process is started, after it conducts the
7420 authentication procedure. This is intended to give developers an
7421 opportunity to attach to the server process with a debugger.
7422 This parameter cannot be changed after session start.
7427 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
7428 <term><varname>pre_auth_delay</varname> (<type>integer</type>)
7430 <primary><varname>pre_auth_delay</> configuration parameter</primary>
7435 If nonzero, a delay of this many seconds occurs just after a
7436 new server process is forked, before it conducts the
7437 authentication procedure. This is intended to give developers an
7438 opportunity to attach to the server process with a debugger to
7439 trace down misbehavior in authentication.
7440 This parameter can only be set in the <filename>postgresql.conf</>
7441 file or on the server command line.
7446 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
7447 <term><varname>trace_notify</varname> (<type>boolean</type>)
7449 <primary><varname>trace_notify</> configuration parameter</primary>
7454 Generates a great amount of debugging output for the
7455 <command>LISTEN</command> and <command>NOTIFY</command>
7456 commands. <xref linkend="guc-client-min-messages"> or
7457 <xref linkend="guc-log-min-messages"> must be
7458 <literal>DEBUG1</literal> or lower to send this output to the
7459 client or server logs, respectively.
7464 <varlistentry id="guc-trace-recovery-messages" xreflabel="trace_recovery_messages">
7465 <term><varname>trace_recovery_messages</varname> (<type>enum</type>)
7467 <primary><varname>trace_recovery_messages</> configuration parameter</primary>
7472 Enables logging of recovery-related debugging output that otherwise
7473 would not be logged. This parameter allows the user to override the
7474 normal setting of <xref linkend="guc-log-min-messages">, but only for
7475 specific messages. This is intended for use in debugging Hot Standby.
7476 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
7477 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>, and
7478 <literal>LOG</>. The default, <literal>LOG</>, does not affect
7479 logging decisions at all. The other values cause recovery-related
7480 debug messages of that priority or higher to be logged as though they
7481 had <literal>LOG</> priority; for common settings of
7482 <varname>log_min_messages</> this results in unconditionally sending
7483 them to the server log.
7484 This parameter can only be set in the <filename>postgresql.conf</>
7485 file or on the server command line.
7490 <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
7491 <term><varname>trace_sort</varname> (<type>boolean</type>)
7493 <primary><varname>trace_sort</> configuration parameter</primary>
7498 If on, emit information about resource usage during sort operations.
7499 This parameter is only available if the <symbol>TRACE_SORT</symbol> macro
7500 was defined when <productname>PostgreSQL</productname> was compiled.
7501 (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
7507 <term><varname>trace_locks</varname> (<type>boolean</type>)
7509 <primary><varname>trace_locks</> configuration parameter</primary>
7514 If on, emit information about lock usage. Information dumped
7515 includes the type of lock operation, the type of lock and the unique
7516 identifier of the object being locked or unlocked. Also included
7517 are bit masks for the lock types already granted on this object as
7518 well as for the lock types awaited on this object. For each lock
7519 type a count of the number of granted locks and waiting locks is
7520 also dumped as well as the totals. An example of the log file output
7523 LOG: LockAcquire: new: lock(0xb7acd844) id(24688,24696,0,0,0,1)
7524 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
7525 wait(0) type(AccessShareLock)
7526 LOG: GrantLock: lock(0xb7acd844) id(24688,24696,0,0,0,1)
7527 grantMask(2) req(1,0,0,0,0,0,0)=1 grant(1,0,0,0,0,0,0)=1
7528 wait(0) type(AccessShareLock)
7529 LOG: UnGrantLock: updated: lock(0xb7acd844) id(24688,24696,0,0,0,1)
7530 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
7531 wait(0) type(AccessShareLock)
7532 LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
7533 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
7534 wait(0) type(INVALID)
7536 Details of the structure being dumped may be found in
7537 <filename>src/include/storage/lock.h</filename>.
7540 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
7541 macro was defined when <productname>PostgreSQL</productname> was
7548 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)
7550 <primary><varname>trace_lwlocks</> configuration parameter</primary>
7555 If on, emit information about lightweight lock usage. Lightweight
7556 locks are intended primarily to provide mutual exclusion of access
7557 to shared-memory data structures.
7560 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
7561 macro was defined when <productname>PostgreSQL</productname> was
7568 <term><varname>trace_userlocks</varname> (<type>boolean</type>)
7570 <primary><varname>trace_userlocks</> configuration parameter</primary>
7575 If on, emit information about user lock usage. Output is the same
7576 as for <symbol>trace_locks</symbol>, only for advisory locks.
7579 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
7580 macro was defined when <productname>PostgreSQL</productname> was
7587 <term><varname>trace_lock_oidmin</varname> (<type>integer</type>)
7589 <primary><varname>trace_lock_oidmin</> configuration parameter</primary>
7594 If set, do not trace locks for tables below this OID. (use to avoid
7595 output on system tables)
7598 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
7599 macro was defined when <productname>PostgreSQL</productname> was
7606 <term><varname>trace_lock_table</varname> (<type>integer</type>)
7608 <primary><varname>trace_lock_table</> configuration parameter</primary>
7613 Unconditionally trace locks on this table (OID).
7616 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
7617 macro was defined when <productname>PostgreSQL</productname> was
7624 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)
7626 <primary><varname>debug_deadlocks</> configuration parameter</primary>
7631 If set, dumps information about all current locks when a
7632 deadlock timeout occurs.
7635 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
7636 macro was defined when <productname>PostgreSQL</productname> was
7643 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)
7645 <primary><varname>log_btree_build_stats</> configuration parameter</primary>
7650 If set, logs system resource usage statistics (memory and CPU) on
7651 various B-tree operations.
7654 This parameter is only available if the <symbol>BTREE_BUILD_STATS</symbol>
7655 macro was defined when <productname>PostgreSQL</productname> was
7661 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
7662 <term><varname>wal_debug</varname> (<type>boolean</type>)
7664 <primary><varname>wal_debug</> configuration parameter</primary>
7669 If on, emit WAL-related debugging output. This parameter is
7670 only available if the <symbol>WAL_DEBUG</symbol> macro was
7671 defined when <productname>PostgreSQL</productname> was
7677 <varlistentry id="guc-ignore-checksum-failure" xreflabel="ignore_checksum_failure">
7678 <term><varname>ignore_checksum_failure</varname> (<type>boolean</type>)
7680 <primary><varname>ignore_checksum_failure</> configuration parameter</primary>
7685 Only has effect if <xref linkend="app-initdb-data-checksums"> are enabled.
7688 Detection of a checksum failure during a read normally causes
7689 <productname>PostgreSQL</> to report an error, aborting the current
7690 transaction. Setting <varname>ignore_checksum_failure</> to on causes
7691 the system to ignore the failure (but still report a warning), and
7692 continue processing. This behavior may <emphasis>cause crashes, propagate
7693 or hide corruption, or other serious problems</>. However, it may allow
7694 you to get past the error and retrieve undamaged tuples that might still be
7695 present in the table if the block header is still sane. If the header is
7696 corrupt an error will be reported even if this option is enabled. The
7697 default setting is <literal>off</>, and it can only be changed by a superuser.
7702 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
7703 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)
7705 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
7710 Detection of a damaged page header normally causes
7711 <productname>PostgreSQL</> to report an error, aborting the current
7712 transaction. Setting <varname>zero_damaged_pages</> to on causes
7713 the system to instead report a warning, zero out the damaged
7714 page in memory, and continue processing. This behavior <emphasis>will destroy data</>,
7715 namely all the rows on the damaged page. However, it does allow you to get
7716 past the error and retrieve rows from any undamaged pages that might
7717 be present in the table. It is useful for recovering data if
7718 corruption has occurred due to a hardware or software error. You should
7719 generally not set this on until you have given up hope of recovering
7720 data from the damaged pages of a table. Zeroed-out pages are not
7721 forced to disk so it is recommended to recreate the table or
7722 the index before turning this parameter off again. The
7723 default setting is <literal>off</>, and it can only be changed
7730 <sect1 id="runtime-config-short">
7731 <title>Short Options</title>
7734 For convenience there are also single letter command-line option
7735 switches available for some parameters. They are described in
7736 <xref linkend="runtime-config-short-table">. Some of these
7737 options exist for historical reasons, and their presence as a
7738 single-letter option does not necessarily indicate an endorsement
7739 to use the option heavily.
7742 <table id="runtime-config-short-table">
7743 <title>Short Option Key</title>
7747 <entry>Short Option</entry>
7748 <entry>Equivalent</entry>
7754 <entry><option>-B <replaceable>x</replaceable></option></entry>
7755 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
7758 <entry><option>-d <replaceable>x</replaceable></option></entry>
7759 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
7762 <entry><option>-e</option></entry>
7763 <entry><literal>datestyle = euro</></entry>
7767 <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
7768 <option>-fm</option>, <option>-fn</option>, <option>-fo</option>,
7769 <option>-fs</option>, <option>-ft</option>
7772 <literal>enable_bitmapscan = off</>,
7773 <literal>enable_hashjoin = off</>,
7774 <literal>enable_indexscan = off</>,
7775 <literal>enable_mergejoin = off</>,
7776 <literal>enable_nestloop = off</>,
7777 <literal>enable_indexonlyscan = off</>,
7778 <literal>enable_seqscan = off</>,
7779 <literal>enable_tidscan = off</>
7783 <entry><option>-F</option></entry>
7784 <entry><literal>fsync = off</></entry>
7787 <entry><option>-h <replaceable>x</replaceable></option></entry>
7788 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
7791 <entry><option>-i</option></entry>
7792 <entry><literal>listen_addresses = '*'</></entry>
7795 <entry><option>-k <replaceable>x</replaceable></option></entry>
7796 <entry><literal>unix_socket_directories = <replaceable>x</replaceable></></entry>
7799 <entry><option>-l</option></entry>
7800 <entry><literal>ssl = on</></entry>
7803 <entry><option>-N <replaceable>x</replaceable></option></entry>
7804 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
7807 <entry><option>-O</option></entry>
7808 <entry><literal>allow_system_table_mods = on</></entry>
7811 <entry><option>-p <replaceable>x</replaceable></option></entry>
7812 <entry><literal>port = <replaceable>x</replaceable></></entry>
7815 <entry><option>-P</option></entry>
7816 <entry><literal>ignore_system_indexes = on</></entry>
7819 <entry><option>-s</option></entry>
7820 <entry><literal>log_statement_stats = on</></entry>
7823 <entry><option>-S <replaceable>x</replaceable></option></entry>
7824 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
7827 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option></entry>
7828 <entry><literal>log_parser_stats = on</>,
7829 <literal>log_planner_stats = on</>,
7830 <literal>log_executor_stats = on</></entry>
7833 <entry><option>-W <replaceable>x</replaceable></option></entry>
7834 <entry><literal>post_auth_delay = <replaceable>x</replaceable></></entry>