1 <!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.225 2009/09/08 17:08:36 tgl Exp $ -->
3 <chapter Id="runtime-config">
4 <title>Server Configuration</title>
7 <primary>configuration</primary>
8 <secondary>of the server</secondary>
12 There are many configuration parameters that affect the behavior of
13 the database system. In the first section of this chapter, we
14 describe how to set configuration parameters. The subsequent sections
15 discuss each parameter in detail.
18 <sect1 id="config-setting">
19 <title>Setting Parameters</title>
22 All parameter names are case-insensitive. Every parameter takes a
23 value of one of five types: Boolean, integer, floating point,
24 string or enum. Boolean values can be written as <literal>ON</literal>,
25 <literal>OFF</literal>, <literal>TRUE</literal>,
26 <literal>FALSE</literal>, <literal>YES</literal>,
27 <literal>NO</literal>, <literal>1</literal>, <literal>0</literal>
28 (all case-insensitive) or any unambiguous prefix of these.
32 Some settings specify a memory or time value. Each of these has an
33 implicit unit, which is either kilobytes, blocks (typically eight
34 kilobytes), milliseconds, seconds, or minutes. Default units can be
35 found by referencing <structname>pg_settings</>.<structfield>unit</>.
37 a different unit can also be specified explicitly. Valid memory units
38 are <literal>kB</literal> (kilobytes), <literal>MB</literal>
39 (megabytes), and <literal>GB</literal> (gigabytes); valid time units
40 are <literal>ms</literal> (milliseconds), <literal>s</literal>
41 (seconds), <literal>min</literal> (minutes), <literal>h</literal>
42 (hours), and <literal>d</literal> (days). Note that the multiplier
43 for memory units is 1024, not 1000.
47 Parameters of type <quote>enum</> are specified in the same way as string
48 parameters, but are restricted to a limited set of values. The allowed
50 from <structname>pg_settings</>.<structfield>enumvals</>.
51 Enum parameter values are case-insensitive.
55 One way to set these parameters is to edit the file
56 <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>,
57 which is normally kept in the data directory. (<application>initdb</>
58 installs a default copy there.) An example of what this file might look
63 log_destination = 'syslog'
64 search_path = '"$user", public'
65 shared_buffers = 128MB
67 One parameter is specified per line. The equal sign between name and
68 value is optional. Whitespace is insignificant and blank lines are
69 ignored. Hash marks (<literal>#</literal>) introduce comments
70 anywhere. Parameter values that are not simple identifiers or
71 numbers must be single-quoted. To embed a single quote in a parameter
72 value, write either two quotes (preferred) or backslash-quote.
77 <primary><literal>include</></primary>
78 <secondary>in configuration file</secondary>
80 In addition to parameter settings, the <filename>postgresql.conf</>
81 file can contain <firstterm>include directives</>, which specify
82 another file to read and process as if it were inserted into the
83 configuration file at this point. Include directives simply look like:
87 If the file name is not an absolute path, it is taken as relative to
88 the directory containing the referencing configuration file.
89 Inclusions can be nested.
94 <primary>SIGHUP</primary>
96 The configuration file is reread whenever the main server process receives a
97 <systemitem>SIGHUP</> signal (which is most easily sent by means
98 of <literal>pg_ctl reload</>). The main server process
99 also propagates this signal to all currently running server
100 processes so that existing sessions also get the new
101 value. Alternatively, you can send the signal to a single server
102 process directly. Some parameters can only be set at server start;
103 any changes to their entries in the configuration file will be ignored
104 until the server is restarted.
108 A second way to set these configuration parameters is to give them
109 as a command-line option to the <command>postgres</command> command, such as:
111 postgres -c log_connections=yes -c log_destination='syslog'
113 Command-line options override any conflicting settings in
114 <filename>postgresql.conf</filename>. Note that this means you won't
115 be able to change the value on-the-fly by editing
116 <filename>postgresql.conf</filename>, so while the command-line
117 method might be convenient, it can cost you flexibility later.
121 Occasionally it is useful to give a command line option to
122 one particular session only. The environment variable
123 <envar>PGOPTIONS</envar> can be used for this purpose on the
126 env PGOPTIONS='-c geqo=off' psql
128 (This works for any <application>libpq</>-based client application, not
129 just <application>psql</application>.) Note that this won't work for
130 parameters that are fixed when the server is started or that must be
131 specified in <filename>postgresql.conf</filename>.
135 Furthermore, it is possible to assign a set of parameter settings to
136 a user or a database. Whenever a session is started, the default
137 settings for the user and database involved are loaded. The
138 commands <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
139 and <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">,
140 respectively, are used to configure these settings. Per-database
141 settings override anything received from the
142 <command>postgres</command> command-line or the configuration
143 file, and in turn are overridden by per-user settings; both are
144 overridden by per-session settings.
148 Some parameters can be changed in individual <acronym>SQL</acronym>
149 sessions with the <xref linkend="SQL-SET" endterm="SQL-SET-title">
150 command, for example:
152 SET ENABLE_SEQSCAN TO OFF;
154 If <command>SET</> is allowed, it overrides all other sources of
155 values for the parameter. Some parameters cannot be changed via
156 <command>SET</command>: for example, if they control behavior that
157 cannot be changed without restarting the entire
158 <productname>PostgreSQL</productname> server. Also, some parameters can
159 be modified via <command>SET</command> or <command>ALTER</> by superusers,
160 but not by ordinary users.
164 The <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title">
165 command allows inspection of the current values of all parameters.
169 The virtual table <structname>pg_settings</structname>
170 (described in <xref linkend="view-pg-settings">) also allows
171 displaying and updating session run-time parameters. It is equivalent
172 to <command>SHOW</> and <command>SET</>, but can be more convenient
173 to use because it can be joined with other tables, or selected from using
174 any desired selection condition. It also contains more information about
175 what values are allowed for the parameters.
179 <sect1 id="runtime-config-file-locations">
180 <title>File Locations</title>
183 In addition to the <filename>postgresql.conf</filename> file
184 already mentioned, <productname>PostgreSQL</productname> uses
185 two other manually-edited configuration files, which control
186 client authentication (their use is discussed in <xref
187 linkend="client-authentication">). By default, all three
188 configuration files are stored in the database cluster's data
189 directory. The parameters described in this section allow the
190 configuration files to be placed elsewhere. (Doing so can ease
191 administration. In particular it is often easier to ensure that
192 the configuration files are properly backed-up when they are
197 <varlistentry id="guc-data-directory" xreflabel="data_directory">
198 <term><varname>data_directory</varname> (<type>string</type>)</term>
200 <primary><varname>data_directory</> configuration parameter</primary>
204 Specifies the directory to use for data storage.
205 This parameter can only be set at server start.
210 <varlistentry id="guc-config-file" xreflabel="config_file">
211 <term><varname>config_file</varname> (<type>string</type>)</term>
213 <primary><varname>config_file</> configuration parameter</primary>
217 Specifies the main server configuration file
218 (customarily called <filename>postgresql.conf</>).
219 This parameter can only be set on the <command>postgres</command> command line.
224 <varlistentry id="guc-hba-file" xreflabel="hba_file">
225 <term><varname>hba_file</varname> (<type>string</type>)</term>
227 <primary><varname>hba_file</> configuration parameter</primary>
231 Specifies the configuration file for host-based authentication
232 (customarily called <filename>pg_hba.conf</>).
233 This parameter can only be set at server start.
238 <varlistentry id="guc-ident-file" xreflabel="ident_file">
239 <term><varname>ident_file</varname> (<type>string</type>)</term>
241 <primary><varname>ident_file</> configuration parameter</primary>
245 Specifies the configuration file for
246 <application>ident</> authentication
247 (customarily called <filename>pg_ident.conf</>).
248 This parameter can only be set at server start.
253 <varlistentry id="guc-external-pid-file" xreflabel="external_pid_file">
254 <term><varname>external_pid_file</varname> (<type>string</type>)</term>
256 <primary><varname>external_pid_file</> configuration parameter</primary>
260 Specifies the name of an additional process-id (PID) file that the
261 server should create for use by server administration programs.
262 This parameter can only be set at server start.
269 In a default installation, none of the above parameters are set
270 explicitly. Instead, the
271 data directory is specified by the <option>-D</option> command-line
272 option or the <envar>PGDATA</envar> environment variable, and the
273 configuration files are all found within the data directory.
277 If you wish to keep the configuration files elsewhere than the
278 data directory, the <command>postgres</command> <option>-D</option>
279 command-line option or <envar>PGDATA</envar> environment variable
280 must point to the directory containing the configuration files,
281 and the <varname>data_directory</> parameter must be set in
282 <filename>postgresql.conf</filename> (or on the command line) to show
283 where the data directory is actually located. Notice that
284 <varname>data_directory</> overrides <option>-D</option> and
285 <envar>PGDATA</envar> for the location
286 of the data directory, but not for the location of the configuration
291 If you wish, you can specify the configuration file names and locations
292 individually using the parameters <varname>config_file</>,
293 <varname>hba_file</> and/or <varname>ident_file</>.
294 <varname>config_file</> can only be specified on the
295 <command>postgres</command> command line, but the others can be
296 set within the main configuration file. If all three parameters plus
297 <varname>data_directory</> are explicitly set, then it is not necessary
298 to specify <option>-D</option> or <envar>PGDATA</envar>.
302 When setting any of these parameters, a relative path will be interpreted
303 with respect to the directory in which <command>postgres</command>
308 <sect1 id="runtime-config-connection">
309 <title>Connections and Authentication</title>
311 <sect2 id="runtime-config-connection-settings">
312 <title>Connection Settings</title>
316 <varlistentry id="guc-listen-addresses" xreflabel="listen_addresses">
317 <term><varname>listen_addresses</varname> (<type>string</type>)</term>
319 <primary><varname>listen_addresses</> configuration parameter</primary>
323 Specifies the TCP/IP address(es) on which the server is
324 to listen for connections from client applications.
325 The value takes the form of a comma-separated list of host names
326 and/or numeric IP addresses. The special entry <literal>*</>
327 corresponds to all available IP interfaces.
328 If the list is empty, the server does not listen on any IP interface
329 at all, in which case only Unix-domain sockets can be used to connect
331 The default value is <systemitem class="systemname">localhost</>,
332 which allows only local <quote>loopback</> connections to be made.
333 This parameter can only be set at server start.
338 <varlistentry id="guc-port" xreflabel="port">
339 <term><varname>port</varname> (<type>integer</type>)</term>
341 <primary><varname>port</> configuration parameter</primary>
345 The TCP port the server listens on; 5432 by default. Note that the
346 same port number is used for all IP addresses the server listens on.
347 This parameter can only be set at server start.
352 <varlistentry id="guc-max-connections" xreflabel="max_connections">
353 <term><varname>max_connections</varname> (<type>integer</type>)</term>
355 <primary><varname>max_connections</> configuration parameter</primary>
359 Determines the maximum number of concurrent connections to the
360 database server. The default is typically 100 connections, but
361 might be less if your kernel settings will not support it (as
362 determined during <application>initdb</>). This parameter can
363 only be set at server start.
367 Increasing this parameter might cause <productname>PostgreSQL</>
368 to request more <systemitem class="osname">System V</> shared
369 memory or semaphores than your operating system's default configuration
370 allows. See <xref linkend="sysvipc"> for information on how to
371 adjust those parameters, if necessary.
376 <varlistentry id="guc-superuser-reserved-connections"
377 xreflabel="superuser_reserved_connections">
378 <term><varname>superuser_reserved_connections</varname>
379 (<type>integer</type>)</term>
381 <primary><varname>superuser_reserved_connections</> configuration parameter</primary>
385 Determines the number of connection <quote>slots</quote> that
386 are reserved for connections by <productname>PostgreSQL</>
387 superusers. At most <xref linkend="guc-max-connections">
388 connections can ever be active simultaneously. Whenever the
389 number of active concurrent connections is at least
390 <varname>max_connections</> minus
391 <varname>superuser_reserved_connections</varname>, new
392 connections will be accepted only for superusers.
396 The default value is three connections. The value must be less
397 than the value of <varname>max_connections</varname>. This
398 parameter can only be set at server start.
403 <varlistentry id="guc-unix-socket-directory" xreflabel="unix_socket_directory">
404 <term><varname>unix_socket_directory</varname> (<type>string</type>)</term>
406 <primary><varname>unix_socket_directory</> configuration parameter</primary>
410 Specifies the directory of the Unix-domain socket on which the
411 server is to listen for
412 connections from client applications. The default is normally
413 <filename>/tmp</filename>, but can be changed at build time.
414 This parameter can only be set at server start.
419 <varlistentry id="guc-unix-socket-group" xreflabel="unix_socket_group">
420 <term><varname>unix_socket_group</varname> (<type>string</type>)</term>
422 <primary><varname>unix_socket_group</> configuration parameter</primary>
426 Sets the owning group of the Unix-domain socket. (The owning
427 user of the socket is always the user that starts the
428 server.) In combination with the parameter
429 <varname>unix_socket_permissions</varname> this can be used as
430 an additional access control mechanism for Unix-domain connections.
431 By default this is the empty string, which selects the default
432 group for the current user. This parameter can only be set at
438 <varlistentry id="guc-unix-socket-permissions" xreflabel="unix_socket_permissions">
439 <term><varname>unix_socket_permissions</varname> (<type>integer</type>)</term>
441 <primary><varname>unix_socket_permissions</> configuration parameter</primary>
445 Sets the access permissions of the Unix-domain socket. Unix-domain
446 sockets use the usual Unix file system permission set.
447 The parameter value is expected to be a numeric mode
448 specification in the form accepted by the
449 <function>chmod</function> and <function>umask</function>
450 system calls. (To use the customary octal format the number
451 must start with a <literal>0</literal> (zero).)
455 The default permissions are <literal>0777</literal>, meaning
456 anyone can connect. Reasonable alternatives are
457 <literal>0770</literal> (only user and group, see also
458 <varname>unix_socket_group</varname>) and <literal>0700</literal>
459 (only user). (Note that for a Unix-domain socket, only write
460 permission matters and so there is no point in setting or revoking
461 read or execute permissions.)
465 This access control mechanism is independent of the one
466 described in <xref linkend="client-authentication">.
470 This parameter can only be set at server start.
475 <varlistentry id="guc-bonjour" xreflabel="bonjour">
476 <term><varname>bonjour</varname> (<type>boolean</type>)</term>
478 <primary><varname>bonjour</> configuration parameter</primary>
482 Enables advertising the server's existence via
483 <productname>Bonjour</productname>. The default is off.
484 This parameter can only be set at server start.
489 <varlistentry id="guc-bonjour-name" xreflabel="bonjour_name">
490 <term><varname>bonjour_name</varname> (<type>string</type>)</term>
492 <primary><varname>bonjour_name</> configuration parameter</primary>
496 Specifies the <productname>Bonjour</productname> service
497 name. The computer name is used if this parameter is set to the
498 empty string <literal>''</> (which is the default). This parameter is
499 ignored if the server was not compiled with
500 <productname>Bonjour</productname> support.
501 This parameter can only be set at server start.
506 <varlistentry id="guc-tcp-keepalives-idle" xreflabel="tcp_keepalives_idle">
507 <term><varname>tcp_keepalives_idle</varname> (<type>integer</type>)</term>
509 <primary><varname>tcp_keepalives_idle</> configuration parameter</primary>
513 On systems that support the <symbol>TCP_KEEPIDLE</symbol> socket option, specifies the
514 number of seconds between sending keepalives on an otherwise idle
515 connection. A value of zero uses the system default. If <symbol>TCP_KEEPIDLE</symbol> is
516 not supported, this parameter must be zero. This parameter is ignored for
517 connections made via a Unix-domain socket.
522 <varlistentry id="guc-tcp-keepalives-interval" xreflabel="tcp_keepalives_interval">
523 <term><varname>tcp_keepalives_interval</varname> (<type>integer</type>)</term>
525 <primary><varname>tcp_keepalives_interval</> configuration parameter</primary>
529 On systems that support the <symbol>TCP_KEEPINTVL</symbol> socket option, specifies how
530 long, in seconds, to wait for a response to a keepalive before
531 retransmitting. A value of zero uses the system default. If <symbol>TCP_KEEPINTVL</symbol>
532 is not supported, this parameter must be zero. This parameter is ignored
533 for connections made via a Unix-domain socket.
538 <varlistentry id="guc-tcp-keepalives-count" xreflabel="tcp_keepalives_count">
539 <term><varname>tcp_keepalives_count</varname> (<type>integer</type>)</term>
541 <primary><varname>tcp_keepalives_count</> configuration parameter</primary>
545 On systems that support the <symbol>TCP_KEEPCNT</symbol> socket option, specifies how
546 many keepalives can be lost before the connection is considered dead.
547 A value of zero uses the system default. If <symbol>TCP_KEEPCNT</symbol> is not
548 supported, this parameter must be zero. This parameter is ignored
549 for connections made via a Unix-domain socket.
556 <sect2 id="runtime-config-connection-security">
557 <title>Security and Authentication</title>
560 <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
561 <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
562 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
563 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
565 <primary><varname>authentication_timeout</> configuration parameter</primary>
570 Maximum time to complete client authentication, in seconds. If a
571 would-be client has not completed the authentication protocol in
572 this much time, the server breaks the connection. This prevents
573 hung clients from occupying a connection indefinitely.
574 The default is one minute (<literal>1m</>).
575 This parameter can only be set in the <filename>postgresql.conf</>
576 file or on the server command line.
581 <varlistentry id="guc-ssl" xreflabel="ssl">
582 <term><varname>ssl</varname> (<type>boolean</type>)</term>
584 <primary><varname>ssl</> configuration parameter</primary>
588 Enables <acronym>SSL</> connections. Please read
589 <xref linkend="ssl-tcp"> before using this. The default
590 is <literal>off</>. This parameter can only be set at server
591 start. <acronym>SSL</> communication is only possible with
597 <varlistentry id="guc-ssl-ciphers" xreflabel="ssl_ciphers">
598 <term><varname>ssl_ciphers</varname> (<type>string</type>)</term>
600 <primary><varname>ssl_ciphers</> configuration parameter</primary>
604 Specifies a list of <acronym>SSL</> ciphers that are allowed to be
605 used on secure connections. See the <application>openssl</>
606 manual page for a list of supported ciphers.
611 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
612 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
614 <primary><varname>password_encryption</> configuration parameter</primary>
618 When a password is specified in <xref
619 linkend="sql-createuser" endterm="sql-createuser-title"> or
620 <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
621 without writing either <literal>ENCRYPTED</> or
622 <literal>UNENCRYPTED</>, this parameter determines whether the
623 password is to be encrypted. The default is <literal>on</>
624 (encrypt the password).
629 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
630 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
632 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
636 Sets the location of the Kerberos server key file. See
637 <xref linkend="kerberos-auth"> or <xref linkend="gssapi-auth">
638 for details. This parameter can only be set in the
639 <filename>postgresql.conf</> file or on the server command line.
644 <varlistentry id="guc-krb-srvname" xreflabel="krb_srvname">
645 <term><varname>krb_srvname</varname> (<type>string</type>)</term>
647 <primary><varname>krb_srvname</> configuration parameter</primary>
651 Sets the Kerberos service name. See <xref linkend="kerberos-auth">
652 for details. This parameter can only be set in the
653 <filename>postgresql.conf</> file or on the server command line.
658 <varlistentry id="guc-krb-caseins-users" xreflabel="krb_caseins_users">
659 <term><varname>krb_caseins_users</varname> (<type>boolean</type>)</term>
661 <primary><varname>krb_caseins_users</varname> configuration parameter</primary>
665 Sets whether Kerberos and GSSAPI user names should be treated
667 The default is <literal>off</> (case sensitive). This parameter can only be
668 set in the <filename>postgresql.conf</> file or on the server command line.
673 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
674 <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
676 <primary><varname>db_user_namespace</> configuration parameter</primary>
680 This parameter enables per-database user names. It is off by default.
681 This parameter can only be set in the <filename>postgresql.conf</>
682 file or on the server command line.
686 If this is on, you should create users as <literal>username@dbname</>.
687 When <literal>username</> is passed by a connecting client,
688 <literal>@</> and the database name are appended to the user
689 name and that database-specific user name is looked up by the
690 server. Note that when you create users with names containing
691 <literal>@</> within the SQL environment, you will need to
696 With this parameter enabled, you can still create ordinary global
697 users. Simply append <literal>@</> when specifying the user
698 name in the client. The <literal>@</> will be stripped off
699 before the user name is looked up by the server.
703 <varname>db_user_namespace</> causes the client's and
704 server's user name representation to differ.
705 Authentication checks are always done with the server's user name
706 so authentication methods must be configured for the
707 server's user name, not the client's. Because
708 <literal>md5</> uses the user name as salt on both the
709 client and server, <literal>md5</> cannot be used with
710 <varname>db_user_namespace</>.
715 This feature is intended as a temporary measure until a
716 complete solution is found. At that time, this option will
727 <sect1 id="runtime-config-resource">
728 <title>Resource Consumption</title>
730 <sect2 id="runtime-config-resource-memory">
731 <title>Memory</title>
734 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
735 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
737 <primary><varname>shared_buffers</> configuration parameter</primary>
741 Sets the amount of memory the database server uses for shared
742 memory buffers. The default is typically 32 megabytes
743 (<literal>32MB</>), but might be less if your kernel settings will
744 not support it (as determined during <application>initdb</>).
745 This setting must be at least 128 kilobytes. (Non-default
746 values of <symbol>BLCKSZ</symbol> change the minimum.) However,
747 settings significantly higher than the minimum are usually needed
748 for good performance. Several tens of megabytes are recommended
749 for production installations. This parameter can only be set at
754 Increasing this parameter might cause <productname>PostgreSQL</>
755 to request more <systemitem class="osname">System V</> shared
756 memory than your operating system's default configuration
757 allows. See <xref linkend="sysvipc"> for information on how to
758 adjust those parameters, if necessary.
763 <varlistentry id="guc-temp-buffers" xreflabel="temp_buffers">
764 <term><varname>temp_buffers</varname> (<type>integer</type>)</term>
766 <primary><varname>temp_buffers</> configuration parameter</primary>
770 Sets the maximum number of temporary buffers used by each database
771 session. These are session-local buffers used only for access to
772 temporary tables. The default is eight megabytes
773 (<literal>8MB</>). The setting can be changed within individual
774 sessions, but only up until the first use of temporary tables
775 within a session; subsequent attempts to change the value will
776 have no effect on that session.
780 A session will allocate temporary buffers as needed up to the limit
781 given by <varname>temp_buffers</>. The cost of setting a large
782 value in sessions that do not actually need a lot of temporary
783 buffers is only a buffer descriptor, or about 64 bytes, per
784 increment in <varname>temp_buffers</>. However if a buffer is
785 actually used an additional 8192 bytes will be consumed for it
786 (or in general, <symbol>BLCKSZ</symbol> bytes).
791 <varlistentry id="guc-max-prepared-transactions" xreflabel="max_prepared_transactions">
792 <term><varname>max_prepared_transactions</varname> (<type>integer</type>)</term>
794 <primary><varname>max_prepared_transactions</> configuration parameter</primary>
798 Sets the maximum number of transactions that can be in the
799 <quote>prepared</> state simultaneously (see <xref
800 linkend="sql-prepare-transaction"
801 endterm="sql-prepare-transaction-title">).
802 Setting this parameter to zero (which is the default)
803 disables the prepared-transaction feature.
804 This parameter can only be set at server start.
808 If you are not planning to use prepared transactions, this parameter
809 should be set to zero to prevent accidental creation of prepared
810 transactions. If you are using prepared transactions, you will
811 probably want <varname>max_prepared_transactions</varname> to be at
812 least as large as <xref linkend="guc-max-connections">, so that every
813 session can have a prepared transaction pending.
817 Increasing this parameter might cause <productname>PostgreSQL</>
818 to request more <systemitem class="osname">System V</> shared
819 memory than your operating system's default configuration
820 allows. See <xref linkend="sysvipc"> for information on how to
821 adjust those parameters, if necessary.
826 <varlistentry id="guc-work-mem" xreflabel="work_mem">
827 <term><varname>work_mem</varname> (<type>integer</type>)</term>
829 <primary><varname>work_mem</> configuration parameter</primary>
833 Specifies the amount of memory to be used by internal sort operations
834 and hash tables before switching to temporary disk files. The value
835 defaults to one megabyte (<literal>1MB</>).
836 Note that for a complex query, several sort or hash operations might be
837 running in parallel; each one will be allowed to use as much memory
838 as this value specifies before it starts to put data into temporary
839 files. Also, several running sessions could be doing such operations
840 concurrently. So the total memory used could be many
841 times the value of <varname>work_mem</varname>; it is necessary to
842 keep this fact in mind when choosing the value. Sort operations are
843 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
845 Hash tables are used in hash joins, hash-based aggregation, and
846 hash-based processing of <literal>IN</> subqueries.
851 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
852 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
854 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
858 Specifies the maximum amount of memory to be used in maintenance
859 operations, such as <command>VACUUM</command>, <command>CREATE
860 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults
861 to 16 megabytes (<literal>16MB</>). Since only one of these
862 operations can be executed at a time by a database session, and
863 an installation normally doesn't have many of them running
864 concurrently, it's safe to set this value significantly larger
865 than <varname>work_mem</varname>. Larger settings might improve
866 performance for vacuuming and for restoring database dumps.
869 Note that when autovacuum runs, up to
870 <xref linkend="guc-autovacuum-max-workers"> times this memory may be
871 allocated, so be careful not to set the default value too high.
876 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
877 <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
879 <primary><varname>max_stack_depth</> configuration parameter</primary>
883 Specifies the maximum safe depth of the server's execution stack.
884 The ideal setting for this parameter is the actual stack size limit
885 enforced by the kernel (as set by <literal>ulimit -s</> or local
886 equivalent), less a safety margin of a megabyte or so. The safety
887 margin is needed because the stack depth is not checked in every
888 routine in the server, but only in key potentially-recursive routines
889 such as expression evaluation. The default setting is two
890 megabytes (<literal>2MB</>), which is conservatively small and
891 unlikely to risk crashes. However, it might be too small to allow
892 execution of complex functions. Only superusers can change this
897 Setting <varname>max_stack_depth</> higher than
898 the actual kernel limit will mean that a runaway recursive function
899 can crash an individual backend process. On platforms where
900 <productname>PostgreSQL</productname> can determine the kernel limit,
901 it will not let you set this variable to an unsafe value. However,
902 not all platforms provide the information, so caution is recommended
903 in selecting a value.
911 <sect2 id="runtime-config-resource-kernel">
912 <title>Kernel Resource Usage</title>
915 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
916 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
918 <primary><varname>max_files_per_process</> configuration parameter</primary>
922 Sets the maximum number of simultaneously open files allowed to each
923 server subprocess. The default is one thousand files. If the kernel is enforcing
924 a safe per-process limit, you don't need to worry about this setting.
925 But on some platforms (notably, most BSD systems), the kernel will
926 allow individual processes to open many more files than the system
927 can really support when a large number of processes all try to open
928 that many files. If you find yourself seeing <quote>Too many open
929 files</> failures, try reducing this setting.
930 This parameter can only be set at server start.
935 <varlistentry id="guc-shared-preload-libraries" xreflabel="shared_preload_libraries">
936 <term><varname>shared_preload_libraries</varname> (<type>string</type>)</term>
938 <primary><varname>shared_preload_libraries</> configuration parameter</primary>
942 This variable specifies one or more shared libraries that are
943 to be preloaded at server start. If more than one library is to be
944 loaded, separate their names with commas. For example,
945 <literal>'$libdir/mylib'</literal> would cause
946 <literal>mylib.so</> (or on some platforms,
947 <literal>mylib.sl</>) to be preloaded from the installation's
948 standard library directory.
949 This parameter can only be set at server start.
953 <productname>PostgreSQL</productname> procedural language
954 libraries can be preloaded in this way, typically by using the
955 syntax <literal>'$libdir/plXXX'</literal> where
956 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
957 <literal>tcl</>, or <literal>python</>.
961 By preloading a shared library, the library startup time is avoided
962 when the library is first used. However, the time to start each new
963 server process might increase slightly, even if that process never
964 uses the library. So this parameter is recommended only for
965 libraries that will be used in most sessions.
970 On Windows hosts, preloading a library at server start will not reduce
971 the time required to start each new server process; each server process
972 will re-load all preload libraries. However, <varname>shared_preload_libraries
973 </varname> is still useful on Windows hosts because some shared libraries may
974 need to perform certain operations that only take place at postmaster start
975 (for example, a shared library may need to reserve lightweight locks
976 or shared memory and you can't do that after the postmaster has started).
980 If a specified library is not found,
981 the server will fail to start.
985 Every PostgreSQL-supported library has a <quote>magic
986 block</> that is checked to guarantee compatibility.
987 For this reason, non-PostgreSQL libraries cannot be
996 <sect2 id="runtime-config-resource-vacuum-cost">
997 <title id="runtime-config-resource-vacuum-cost-title">
998 Cost-Based Vacuum Delay
1002 During the execution of <xref linkend="sql-vacuum"
1003 endterm="sql-vacuum-title"> and <xref linkend="sql-analyze"
1004 endterm="sql-analyze-title"> commands, the system maintains an
1005 internal counter that keeps track of the estimated cost of the
1006 various I/O operations that are performed. When the accumulated
1007 cost reaches a limit (specified by
1008 <varname>vacuum_cost_limit</varname>), the process performing
1009 the operation will sleep for a while (specified by
1010 <varname>vacuum_cost_delay</varname>). Then it will reset the
1011 counter and continue execution.
1015 The intent of this feature is to allow administrators to reduce
1016 the I/O impact of these commands on concurrent database
1017 activity. There are many situations in which it is not very
1018 important that maintenance commands like
1019 <command>VACUUM</command> and <command>ANALYZE</command> finish
1020 quickly; however, it is usually very important that these
1021 commands do not significantly interfere with the ability of the
1022 system to perform other database operations. Cost-based vacuum
1023 delay provides a way for administrators to achieve this.
1027 This feature is disabled by default for manually issued
1028 <command>VACUUM</command> commands. To enable it, set the
1029 <varname>vacuum_cost_delay</varname> variable to a nonzero
1034 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1035 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)</term>
1037 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1041 The length of time, in milliseconds, that the process will sleep
1042 when the cost limit has been exceeded.
1043 The default value is zero, which disables the cost-based vacuum
1044 delay feature. Positive values enable cost-based vacuuming.
1045 Note that on many systems, the effective resolution
1046 of sleep delays is 10 milliseconds; setting
1047 <varname>vacuum_cost_delay</varname> to a value that is
1048 not a multiple of 10 might have the same results as setting it
1049 to the next higher multiple of 10.
1053 When using cost-based vacuuming, appropriate values for
1054 <varname>vacuum_cost_delay</> are usually quite small, perhaps
1055 10 or 20 milliseconds. Adjusting vacuum's resource consumption
1056 is best done by changing the other vacuum cost parameters.
1061 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1062 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
1064 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1068 The estimated cost for vacuuming a buffer found in the shared buffer
1069 cache. It represents the cost to lock the buffer pool, lookup
1070 the shared hash table and scan the content of the page. The
1071 default value is one.
1076 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1077 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
1079 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1083 The estimated cost for vacuuming a buffer that has to be read from
1084 disk. This represents the effort to lock the buffer pool,
1085 lookup the shared hash table, read the desired block in from
1086 the disk and scan its content. The default value is 10.
1091 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1092 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
1094 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1098 The estimated cost charged when vacuum modifies a block that was
1099 previously clean. It represents the extra I/O required to
1100 flush the dirty block out to disk again. The default value is
1106 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1107 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
1109 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1113 The accumulated cost that will cause the vacuuming process to sleep.
1114 The default value is 200.
1122 There are certain operations that hold critical locks and should
1123 therefore complete as quickly as possible. Cost-based vacuum
1124 delays do not occur during such operations. Therefore it is
1125 possible that the cost accumulates far higher than the specified
1126 limit. To avoid uselessly long delays in such cases, the actual
1127 delay is calculated as <varname>vacuum_cost_delay</varname> *
1128 <varname>accumulated_balance</varname> /
1129 <varname>vacuum_cost_limit</varname> with a maximum of
1130 <varname>vacuum_cost_delay</varname> * 4.
1135 <sect2 id="runtime-config-resource-background-writer">
1136 <title>Background Writer</title>
1139 There is a separate server
1140 process called the <firstterm>background writer</>, whose function
1141 is to issue writes of <quote>dirty</> shared buffers. The intent is
1142 that server processes handling user queries should seldom or never have
1143 to wait for a write to occur, because the background writer will do it.
1144 However there is a net overall
1145 increase in I/O load, because a repeatedly-dirtied page might
1146 otherwise be written only once per checkpoint interval, but the
1147 background writer might write it several times in the same interval.
1148 The parameters discussed in this subsection can be used to
1149 tune the behavior for local needs.
1153 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1154 <term><varname>bgwriter_delay</varname> (<type>integer</type>)</term>
1156 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1160 Specifies the delay between activity rounds for the
1161 background writer. In each round the writer issues writes
1162 for some number of dirty buffers (controllable by the
1163 following parameters). It then sleeps for <varname>bgwriter_delay</>
1164 milliseconds, and repeats. The default value is 200 milliseconds
1165 (<literal>200ms</>). Note that on many systems, the effective
1166 resolution of sleep delays is 10 milliseconds; setting
1167 <varname>bgwriter_delay</> to a value that is not a multiple of
1168 10 might have the same results as setting it to the next higher
1169 multiple of 10. This parameter can only be set in the
1170 <filename>postgresql.conf</> file or on the server command line.
1175 <varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1176 <term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)</term>
1178 <primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1182 In each round, no more than this many buffers will be written
1183 by the background writer. Setting this to zero disables
1184 background writing (except for checkpoint activity).
1185 The default value is 100 buffers.
1186 This parameter can only be set in the <filename>postgresql.conf</>
1187 file or on the server command line.
1192 <varlistentry id="guc-bgwriter-lru-multiplier" xreflabel="bgwriter_lru_multiplier">
1193 <term><varname>bgwriter_lru_multiplier</varname> (<type>floating point</type>)</term>
1195 <primary><varname>bgwriter_lru_multiplier</> configuration parameter</primary>
1199 The number of dirty buffers written in each round is based on the
1200 number of new buffers that have been needed by server processes
1201 during recent rounds. The average recent need is multiplied by
1202 <varname>bgwriter_lru_multiplier</> to arrive at an estimate of the
1203 number of buffers that will be needed during the next round. Dirty
1204 buffers are written until there are that many clean, reusable buffers
1205 available. (However, no more than <varname>bgwriter_lru_maxpages</>
1206 buffers will be written per round.)
1207 Thus, a setting of 1.0 represents a <quote>just in time</> policy
1208 of writing exactly the number of buffers predicted to be needed.
1209 Larger values provide some cushion against spikes in demand,
1210 while smaller values intentionally leave writes to be done by
1213 This parameter can only be set in the <filename>postgresql.conf</>
1214 file or on the server command line.
1221 Smaller values of <varname>bgwriter_lru_maxpages</varname> and
1222 <varname>bgwriter_lru_multiplier</varname> reduce the extra I/O load
1223 caused by the background writer, but make it more likely that server
1224 processes will have to issue writes for themselves, delaying interactive
1229 <sect2 id="runtime-config-resource-async-behavior">
1230 <title>Asynchronous Behavior</title>
1233 <varlistentry id="guc-effective-io-concurrency" xreflabel="effective_io_concurrency">
1234 <term><varname>effective_io_concurrency</varname> (<type>integer</type>)</term>
1236 <primary><varname>effective_io_concurrency</> configuration parameter</primary>
1240 Sets the number of concurrent disk I/O operations that
1241 <productname>PostgreSQL</> expects can be executed
1242 simultaneously. Raising this value will increase the number of I/O
1243 operations that any individual <productname>PostgreSQL</> session
1244 attempts to initiate in parallel. The allowed range is 1 to 1000,
1245 or zero to disable issuance of asynchronous I/O requests.
1249 A good starting point for this setting is the number of separate
1250 drives comprising a RAID 0 stripe or RAID 1 mirror being used for the
1251 database. (For RAID 5 the parity drive should not be counted.)
1252 However, if the database is often busy with multiple queries issued in
1253 concurrent sessions, lower values may be sufficient to keep the disk
1254 array busy. A value higher than needed to keep the disks busy will
1255 only result in extra CPU overhead.
1259 For more exotic systems, such as memory-based storage or a RAID array
1260 that is limited by bus bandwidth, the correct value might be the
1261 number of I/O paths available. Some experimentation may be needed
1262 to find the best value.
1266 Asynchronous I/O depends on an effective <function>posix_fadvise</>
1267 function, which some operating systems lack. If the function is not
1268 present then setting this parameter to anything but zero will result
1269 in an error. On some operating systems (e.g., Solaris), the function
1270 is present but does not actually do anything.
1278 <sect1 id="runtime-config-wal">
1279 <title>Write Ahead Log</title>
1282 See also <xref linkend="wal-configuration"> for details on WAL
1283 and checkpoint tuning.
1286 <sect2 id="runtime-config-wal-settings">
1287 <title>Settings</title>
1290 <varlistentry id="guc-fsync" xreflabel="fsync">
1292 <primary><varname>fsync</> configuration parameter</primary>
1294 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1297 If this parameter is on, the <productname>PostgreSQL</> server
1298 will try to make sure that updates are physically written to
1299 disk, by issuing <function>fsync()</> system calls or various
1300 equivalent methods (see <xref linkend="guc-wal-sync-method">).
1301 This ensures that the database cluster can recover to a
1302 consistent state after an operating system or hardware crash.
1306 However, using <varname>fsync</varname> results in a
1307 performance penalty: when a transaction is committed,
1308 <productname>PostgreSQL</productname> must wait for the
1309 operating system to flush the write-ahead log to disk. When
1310 <varname>fsync</varname> is disabled, the operating system is
1311 allowed to do its best in buffering, ordering, and delaying
1312 writes. This can result in significantly improved performance.
1313 However, if the system crashes, the results of the last few
1314 committed transactions might be lost in part or whole. In the
1315 worst case, unrecoverable data corruption might occur.
1316 (Crashes of the database software itself are <emphasis>not</>
1317 a risk factor here. Only an operating-system-level crash
1318 creates a risk of corruption.)
1322 Due to the risks involved, there is no universally correct
1323 setting for <varname>fsync</varname>. Some administrators
1324 always disable <varname>fsync</varname>, while others only
1325 turn it off during initial bulk data loads, where there is a clear
1326 restart point if something goes wrong. Others
1327 always leave <varname>fsync</varname> enabled. The default is
1328 to enable <varname>fsync</varname>, for maximum reliability.
1329 If you trust your operating system, your hardware, and your
1330 utility company (or your battery backup), you can consider
1331 disabling <varname>fsync</varname>.
1335 In many situations, turning off <xref linkend="guc-synchronous-commit">
1336 for noncritical transactions can provide much of the potential
1337 performance benefit of turning off <varname>fsync</varname>, without
1338 the attendant risks of data corruption.
1342 This parameter can only be set in the <filename>postgresql.conf</>
1343 file or on the server command line.
1344 If you turn this parameter off, also consider turning off
1345 <xref linkend="guc-full-page-writes">.
1350 <varlistentry id="guc-synchronous-commit" xreflabel="synchronous_commit">
1351 <term><varname>synchronous_commit</varname> (<type>boolean</type>)</term>
1353 <primary><varname>synchronous_commit</> configuration parameter</primary>
1357 Specifies whether transaction commit will wait for WAL records
1358 to be written to disk before the command returns a <quote>success</>
1359 indication to the client. The default, and safe, setting is
1360 <literal>on</>. When <literal>off</>, there can be a delay between
1361 when success is reported to the client and when the transaction is
1362 really guaranteed to be safe against a server crash. (The maximum
1363 delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
1364 <xref linkend="guc-fsync">, setting this parameter to <literal>off</>
1365 does not create any risk of database inconsistency: a crash might
1366 result in some recent allegedly-committed transactions being lost, but
1367 the database state will be just the same as if those transactions had
1368 been aborted cleanly. So, turning <varname>synchronous_commit</> off
1369 can be a useful alternative when performance is more important than
1370 exact certainty about the durability of a transaction. For more
1371 discussion see <xref linkend="wal-async-commit">.
1374 This parameter can be changed at any time; the behavior for any
1375 one transaction is determined by the setting in effect when it
1376 commits. It is therefore possible, and useful, to have some
1377 transactions commit synchronously and others asynchronously.
1378 For example, to make a single multi-statement transaction commit
1379 asynchronously when the default is the opposite, issue <command>SET
1380 LOCAL synchronous_commit TO OFF</> within the transaction.
1385 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1386 <term><varname>wal_sync_method</varname> (<type>enum</type>)</term>
1388 <primary><varname>wal_sync_method</> configuration parameter</primary>
1392 Method used for forcing WAL updates out to disk.
1393 If <varname>fsync</varname> is off then this setting is irrelevant,
1394 since updates will not be forced out at all.
1395 Possible values are:
1400 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>)
1405 <literal>fdatasync</> (call <function>fdatasync()</> at each commit)
1410 <literal>fsync_writethrough</> (call <function>fsync()</> at each commit, forcing write-through of any disk write cache)
1415 <literal>fsync</> (call <function>fsync()</> at each commit)
1420 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>)
1425 Not all of these choices are available on all platforms.
1426 The default is the first method in the above list that is supported
1428 The <literal>open_</>* options also use <literal>O_DIRECT</> if available.
1429 This parameter can only be set in the <filename>postgresql.conf</>
1430 file or on the server command line.
1435 <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
1437 <primary><varname>full_page_writes</> configuration parameter</primary>
1439 <term><varname>full_page_writes</varname> (<type>boolean</type>)</term>
1442 When this parameter is on, the <productname>PostgreSQL</> server
1443 writes the entire content of each disk page to WAL during the
1444 first modification of that page after a checkpoint.
1445 This is needed because
1446 a page write that is in process during an operating system crash might
1447 be only partially completed, leading to an on-disk page
1448 that contains a mix of old and new data. The row-level change data
1449 normally stored in WAL will not be enough to completely restore
1450 such a page during post-crash recovery. Storing the full page image
1451 guarantees that the page can be correctly restored, but at a price
1452 in increasing the amount of data that must be written to WAL.
1453 (Because WAL replay always starts from a checkpoint, it is sufficient
1454 to do this during the first change of each page after a checkpoint.
1455 Therefore, one way to reduce the cost of full-page writes is to
1456 increase the checkpoint interval parameters.)
1460 Turning this parameter off speeds normal operation, but
1461 might lead to a corrupt database after an operating system crash
1462 or power failure. The risks are similar to turning off
1463 <varname>fsync</>, though smaller. It might be safe to turn off
1464 this parameter if you have hardware (such as a battery-backed disk
1465 controller) or file-system software that reduces
1466 the risk of partial page writes to an acceptably low level (e.g., ReiserFS 4).
1470 Turning off this parameter does not affect use of
1471 WAL archiving for point-in-time recovery (PITR)
1472 (see <xref linkend="continuous-archiving">).
1476 This parameter can only be set in the <filename>postgresql.conf</>
1477 file or on the server command line.
1478 The default is <literal>on</>.
1483 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1484 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1486 <primary><varname>wal_buffers</> configuration parameter</primary>
1490 The amount of memory used in shared memory for WAL data. The
1491 default is 64 kilobytes (<literal>64kB</>). The setting need only
1492 be large enough to hold the amount of WAL data generated by one
1493 typical transaction, since the data is written out to disk at
1494 every transaction commit. This parameter can only be set at server
1499 Increasing this parameter might cause <productname>PostgreSQL</>
1500 to request more <systemitem class="osname">System V</> shared
1501 memory than your operating system's default configuration
1502 allows. See <xref linkend="sysvipc"> for information on how to
1503 adjust those parameters, if necessary.
1508 <varlistentry id="guc-wal-writer-delay" xreflabel="wal_writer_delay">
1509 <term><varname>wal_writer_delay</varname> (<type>integer</type>)</term>
1511 <primary><varname>wal_writer_delay</> configuration parameter</primary>
1515 Specifies the delay between activity rounds for the WAL writer.
1516 In each round the writer will flush WAL to disk. It then sleeps for
1517 <varname>wal_writer_delay</> milliseconds, and repeats. The default
1518 value is 200 milliseconds (<literal>200ms</>). Note that on many
1519 systems, the effective resolution of sleep delays is 10 milliseconds;
1520 setting <varname>wal_writer_delay</> to a value that is not a multiple
1521 of 10 might have the same results as setting it to the next higher
1522 multiple of 10. This parameter can only be set in the
1523 <filename>postgresql.conf</> file or on the server command line.
1528 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
1529 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1531 <primary><varname>commit_delay</> configuration parameter</primary>
1535 Time delay between writing a commit record to the WAL buffer
1536 and flushing the buffer out to disk, in microseconds. A
1537 nonzero delay can allow multiple transactions to be committed
1538 with only one <function>fsync()</function> system call, if
1539 system load is high enough that additional transactions become
1540 ready to commit within the given interval. But the delay is
1541 just wasted if no other transactions become ready to
1542 commit. Therefore, the delay is only performed if at least
1543 <varname>commit_siblings</varname> other transactions are
1544 active at the instant that a server process has written its
1545 commit record. The default is zero (no delay).
1550 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
1551 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1553 <primary><varname>commit_siblings</> configuration parameter</primary>
1557 Minimum number of concurrent open transactions to require
1558 before performing the <varname>commit_delay</> delay. A larger
1559 value makes it more probable that at least one other
1560 transaction will become ready to commit during the delay
1561 interval. The default is five transactions.
1568 <sect2 id="runtime-config-wal-checkpoints">
1569 <title>Checkpoints</title>
1572 <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1573 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1575 <primary><varname>checkpoint_segments</> configuration parameter</primary>
1579 Maximum number of log file segments between automatic WAL
1580 checkpoints (each segment is normally 16 megabytes). The default
1581 is three segments. Increasing this parameter can increase the
1582 amount of time needed for crash recovery.
1583 This parameter can only be set in the <filename>postgresql.conf</>
1584 file or on the server command line.
1589 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1590 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1592 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
1596 Maximum time between automatic WAL checkpoints, in
1597 seconds. The default is five minutes (<literal>5min</>).
1598 Increasing this parameter can increase the amount of time needed
1600 This parameter can only be set in the <filename>postgresql.conf</>
1601 file or on the server command line.
1606 <varlistentry id="guc-checkpoint-completion-target" xreflabel="checkpoint_completion_target">
1607 <term><varname>checkpoint_completion_target</varname> (<type>floating point</type>)</term>
1609 <primary><varname>checkpoint_completion_target</> configuration parameter</primary>
1613 Specifies the target length of checkpoints, as a fraction of
1614 the checkpoint interval. The default is 0.5.
1616 This parameter can only be set in the <filename>postgresql.conf</>
1617 file or on the server command line.
1622 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1623 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1625 <primary><varname>checkpoint_warning</> configuration parameter</primary>
1629 Write a message to the server log if checkpoints caused by
1630 the filling of checkpoint segment files happen closer together
1631 than this many seconds (which suggests that
1632 <varname>checkpoint_segments</> ought to be raised). The default is
1633 30 seconds (<literal>30s</>). Zero disables the warning.
1634 This parameter can only be set in the <filename>postgresql.conf</>
1635 file or on the server command line.
1642 <sect2 id="runtime-config-wal-archiving">
1643 <title>Archiving</title>
1646 <varlistentry id="guc-archive-mode" xreflabel="archive_mode">
1647 <term><varname>archive_mode</varname> (<type>boolean</type>)</term>
1649 <primary><varname>archive_mode</> configuration parameter</primary>
1653 When <varname>archive_mode</> is enabled, completed WAL segments
1654 can be sent to archive storage by setting
1655 <xref linkend="guc-archive-command">.
1656 <varname>archive_mode</> and <varname>archive_command</> are
1657 separate variables so that <varname>archive_command</> can be
1658 changed without leaving archiving mode.
1659 This parameter can only be set at server start.
1664 <varlistentry id="guc-archive-command" xreflabel="archive_command">
1665 <term><varname>archive_command</varname> (<type>string</type>)</term>
1667 <primary><varname>archive_command</> configuration parameter</primary>
1671 The shell command to execute to archive a completed segment of
1672 the WAL file series. Any <literal>%p</> in the string is
1673 replaced by the path name of the file to archive, and any
1674 <literal>%f</> is replaced by the file name only.
1675 (The path name is relative to the working directory of the server,
1676 i.e., the cluster's data directory.)
1677 Use <literal>%%</> to embed an actual <literal>%</> character in the
1678 command. For more information see <xref
1679 linkend="backup-archiving-wal">.
1680 This parameter can only be set in the <filename>postgresql.conf</>
1681 file or on the server command line. It is ignored unless
1682 <varname>archive_mode</> was enabled at server start.
1683 If <varname>archive_command</> is an empty string (the default) while
1684 <varname>archive_mode</> is enabled, then WAL archiving is temporarily
1685 disabled, but the server continues to accumulate WAL segment files in
1686 the expectation that a command will soon be provided.
1689 It is important for the command to return a zero exit status if
1690 and only if it succeeds. Examples:
1692 archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
1693 archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows
1699 <varlistentry id="guc-archive-timeout" xreflabel="archive_timeout">
1700 <term><varname>archive_timeout</varname> (<type>integer</type>)</term>
1702 <primary><varname>archive_timeout</> configuration parameter</primary>
1706 The <xref linkend="guc-archive-command"> is only invoked on
1707 completed WAL segments. Hence, if your server generates little WAL
1708 traffic (or has slack periods where it does so), there could be a
1709 long delay between the completion of a transaction and its safe
1710 recording in archive storage. To put a limit on how old unarchived
1711 data can be, you can set <varname>archive_timeout</> to force the
1712 server to switch to a new WAL segment file periodically. When this
1713 parameter is greater than zero, the server will switch to a new
1714 segment file whenever this many seconds have elapsed since the last
1715 segment file switch. Note that archived files that are closed early
1716 due to a forced switch are still the same length as completely full
1717 files. Therefore, it is unwise to use a very short
1718 <varname>archive_timeout</> — it will bloat your archive
1719 storage. <varname>archive_timeout</> settings of a minute or so are
1720 usually reasonable. This parameter can only be set in the
1721 <filename>postgresql.conf</> file or on the server command line.
1730 <sect1 id="runtime-config-query">
1731 <title>Query Planning</title>
1733 <sect2 id="runtime-config-query-enable">
1734 <title>Planner Method Configuration</title>
1737 These configuration parameters provide a crude method of
1738 influencing the query plans chosen by the query optimizer. If
1739 the default plan chosen by the optimizer for a particular query
1740 is not optimal, a temporary solution can be found by using one
1741 of these configuration parameters to force the optimizer to
1742 choose a different plan. Turning one of these settings off
1743 permanently is seldom a good idea, however.
1744 Better ways to improve the quality of the
1745 plans chosen by the optimizer include adjusting the <xref
1746 linkend="runtime-config-query-constants"
1747 endterm="runtime-config-query-constants-title">, running <xref
1748 linkend="sql-analyze" endterm="sql-analyze-title"> more
1749 frequently, increasing the value of the <xref
1750 linkend="guc-default-statistics-target"> configuration parameter,
1751 and increasing the amount of statistics collected for
1752 specific columns using <command>ALTER TABLE SET
1753 STATISTICS</command>.
1757 <varlistentry id="guc-enable-bitmapscan" xreflabel="enable_bitmapscan">
1758 <term><varname>enable_bitmapscan</varname> (<type>boolean</type>)</term>
1760 <primary>bitmap scan</primary>
1763 <primary><varname>enable_bitmapscan</> configuration parameter</primary>
1767 Enables or disables the query planner's use of bitmap-scan plan
1768 types. The default is <literal>on</>.
1773 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
1774 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
1776 <primary><varname>enable_hashagg</> configuration parameter</primary>
1780 Enables or disables the query planner's use of hashed
1781 aggregation plan types. The default is <literal>on</>.
1786 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
1787 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
1789 <primary><varname>enable_hashjoin</> configuration parameter</primary>
1793 Enables or disables the query planner's use of hash-join plan
1794 types. The default is <literal>on</>.
1799 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
1800 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
1802 <primary>index scan</primary>
1805 <primary><varname>enable_indexscan</> configuration parameter</primary>
1809 Enables or disables the query planner's use of index-scan plan
1810 types. The default is <literal>on</>.
1815 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
1816 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
1818 <primary><varname>enable_mergejoin</> configuration parameter</primary>
1822 Enables or disables the query planner's use of merge-join plan
1823 types. The default is <literal>on</>.
1828 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
1829 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
1831 <primary><varname>enable_nestloop</> configuration parameter</primary>
1835 Enables or disables the query planner's use of nested-loop join
1836 plans. It's not possible to suppress nested-loop joins entirely,
1837 but turning this variable off discourages the planner from using
1838 one if there are other methods available. The default is
1844 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
1845 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
1847 <primary>sequential scan</primary>
1850 <primary><varname>enable_seqscan</> configuration parameter</primary>
1854 Enables or disables the query planner's use of sequential scan
1855 plan types. It's not possible to suppress sequential scans
1856 entirely, but turning this variable off discourages the planner
1857 from using one if there are other methods available. The
1858 default is <literal>on</>.
1863 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
1864 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
1866 <primary><varname>enable_sort</> configuration parameter</primary>
1870 Enables or disables the query planner's use of explicit sort
1871 steps. It's not possible to suppress explicit sorts entirely,
1872 but turning this variable off discourages the planner from
1873 using one if there are other methods available. The default
1879 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
1880 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
1882 <primary><varname>enable_tidscan</> configuration parameter</primary>
1886 Enables or disables the query planner's use of <acronym>TID</>
1887 scan plan types. The default is <literal>on</>.
1894 <sect2 id="runtime-config-query-constants">
1895 <title id="runtime-config-query-constants-title">
1896 Planner Cost Constants
1900 The <firstterm>cost</> variables described in this section are measured
1901 on an arbitrary scale. Only their relative values matter, hence
1902 scaling them all up or down by the same factor will result in no change
1903 in the planner's choices. Traditionally, these variables have been
1904 referenced to sequential page fetches as the unit of cost; that is,
1905 <varname>seq_page_cost</> is conventionally set to <literal>1.0</>
1906 and the other cost variables are set with reference to that. But
1907 you can use a different scale if you prefer, such as actual execution
1908 times in milliseconds on a particular machine.
1913 Unfortunately, there is no well-defined method for determining ideal
1914 values for the cost variables. They are best treated as averages over
1915 the entire mix of queries that a particular installation will get. This
1916 means that changing them on the basis of just a few experiments is very
1923 <varlistentry id="guc-seq-page-cost" xreflabel="seq_page_cost">
1924 <term><varname>seq_page_cost</varname> (<type>floating point</type>)</term>
1926 <primary><varname>seq_page_cost</> configuration parameter</primary>
1930 Sets the planner's estimate of the cost of a disk page fetch
1931 that is part of a series of sequential fetches. The default is 1.0.
1936 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
1937 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
1939 <primary><varname>random_page_cost</> configuration parameter</primary>
1943 Sets the planner's estimate of the cost of a
1944 non-sequentially-fetched disk page. The default is 4.0.
1945 Reducing this value relative to <varname>seq_page_cost</>
1946 will cause the system to prefer index scans; raising it will
1947 make index scans look relatively more expensive. You can raise
1948 or lower both values together to change the importance of disk I/O
1949 costs relative to CPU costs, which are described by the following
1955 Although the system will let you set <varname>random_page_cost</> to
1956 less than <varname>seq_page_cost</>, it is not physically sensible
1957 to do so. However, setting them equal makes sense if the database
1958 is entirely cached in RAM, since in that case there is no penalty
1959 for touching pages out of sequence. Also, in a heavily-cached
1960 database you should lower both values relative to the CPU parameters,
1961 since the cost of fetching a page already in RAM is much smaller
1962 than it would normally be.
1968 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
1969 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
1971 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
1975 Sets the planner's estimate of the cost of processing
1976 each row during a query.
1977 The default is 0.01.
1982 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
1983 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
1985 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
1989 Sets the planner's estimate of the cost of processing
1990 each index entry during an index scan.
1991 The default is 0.005.
1996 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
1997 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
1999 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
2003 Sets the planner's estimate of the cost of processing each
2004 operator or function executed during a query.
2005 The default is 0.0025.
2010 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
2011 <term><varname>effective_cache_size</varname> (<type>integer</type>)</term>
2013 <primary><varname>effective_cache_size</> configuration parameter</primary>
2017 Sets the planner's assumption about the effective size of the
2018 disk cache that is available to a single query. This is
2019 factored into estimates of the cost of using an index; a
2020 higher value makes it more likely index scans will be used, a
2021 lower value makes it more likely sequential scans will be
2022 used. When setting this parameter you should consider both
2023 <productname>PostgreSQL</productname>'s shared buffers and the
2024 portion of the kernel's disk cache that will be used for
2025 <productname>PostgreSQL</productname> data files. Also, take
2026 into account the expected number of concurrent queries on different
2027 tables, since they will have to share the available
2028 space. This parameter has no effect on the size of shared
2029 memory allocated by <productname>PostgreSQL</productname>, nor
2030 does it reserve kernel disk cache; it is used only for estimation
2031 purposes. The default is 128 megabytes (<literal>128MB</>).
2039 <sect2 id="runtime-config-query-geqo">
2040 <title>Genetic Query Optimizer</title>
2043 The genetic query optimizer (GEQO) is an algorithm that does query
2044 planning using heuristic searching. This reduces planning time for
2045 complex queries (those joining many relations), at the cost of producing
2046 plans that are sometimes inferior to those found by the normal
2047 exhaustive-search algorithm. Also, GEQO's searching is randomized and
2048 therefore its plans may vary nondeterministically.
2049 For more information see <xref linkend="geqo">.
2054 <varlistentry id="guc-geqo" xreflabel="geqo">
2056 <primary>genetic query optimization</primary>
2059 <primary>GEQO</primary>
2060 <see>genetic query optimization</see>
2063 <primary><varname>geqo</> configuration parameter</primary>
2065 <term><varname>geqo</varname> (<type>boolean</type>)</term>
2068 Enables or disables genetic query optimization.
2069 This is on by default. It is usually best not to turn it off in
2070 production; the <varname>geqo_threshold</varname> variable provides a
2071 more granular way to control use of GEQO.
2076 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
2077 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
2079 <primary><varname>geqo_threshold</> configuration parameter</primary>
2083 Use genetic query optimization to plan queries with at least
2084 this many <literal>FROM</> items involved. (Note that a
2085 <literal>FULL OUTER JOIN</> construct counts as only one <literal>FROM</>
2086 item.) The default is 12. For simpler queries it is usually best
2087 to use the deterministic, exhaustive planner, but for queries with
2088 many tables the deterministic planner takes too long.
2093 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
2094 <term><varname>geqo_effort</varname>
2095 (<type>integer</type>)</term>
2097 <primary><varname>geqo_effort</> configuration parameter</primary>
2101 Controls the trade-off between planning time and query plan
2102 quality in GEQO. This variable must be an integer in the
2103 range from 1 to 10. The default value is five. Larger values
2104 increase the time spent doing query planning, but also
2105 increase the likelihood that an efficient query plan will be
2110 <varname>geqo_effort</varname> doesn't actually do anything
2111 directly; it is only used to compute the default values for
2112 the other variables that influence GEQO behavior (described
2113 below). If you prefer, you can set the other parameters by
2119 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
2120 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
2122 <primary><varname>geqo_pool_size</> configuration parameter</primary>
2126 Controls the pool size used by GEQO, that is the
2127 number of individuals in the genetic population. It must be
2128 at least two, and useful values are typically 100 to 1000. If
2129 it is set to zero (the default setting) then a suitable
2130 value is chosen based on <varname>geqo_effort</varname> and
2131 the number of tables in the query.
2136 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
2137 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
2139 <primary><varname>geqo_generations</> configuration parameter</primary>
2143 Controls the number of generations used by GEQO, that is
2144 the number of iterations of the algorithm. It must
2145 be at least one, and useful values are in the same range as
2146 the pool size. If it is set to zero (the default setting)
2147 then a suitable value is chosen based on
2148 <varname>geqo_pool_size</varname>.
2153 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
2154 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
2156 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
2160 Controls the selection bias used by GEQO. The selection bias
2161 is the selective pressure within the population. Values can be
2162 from 1.50 to 2.00; the latter is the default.
2167 <varlistentry id="guc-geqo-seed" xreflabel="geqo_seed">
2168 <term><varname>geqo_seed</varname> (<type>floating point</type>)</term>
2170 <primary><varname>geqo_seed</> configuration parameter</primary>
2174 Controls the initial value of the random number generator used
2175 by GEQO to select random paths through the join order search space.
2176 The value can range from zero (the default) to one. Varying the
2177 value changes the set of join paths explored, and may result in a
2178 better or worse best path being found.
2185 <sect2 id="runtime-config-query-other">
2186 <title>Other Planner Options</title>
2190 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
2191 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
2193 <primary><varname>default_statistics_target</> configuration parameter</primary>
2197 Sets the default statistics target for table columns that have
2198 not had a column-specific target set via <command>ALTER TABLE
2199 SET STATISTICS</>. Larger values increase the time needed to
2200 do <command>ANALYZE</>, but might improve the quality of the
2201 planner's estimates. The default is 100. For more information
2202 on the use of statistics by the <productname>PostgreSQL</>
2203 query planner, refer to <xref linkend="planner-stats">.
2208 <varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
2209 <term><varname>constraint_exclusion</varname> (<type>enum</type>)</term>
2211 <primary>constraint exclusion</primary>
2214 <primary><varname>constraint_exclusion</> configuration parameter</primary>
2218 Controls the query planner's use of table constraints to
2220 The allowed values of <varname>constraint_exclusion</> are
2221 <literal>on</> (examine constraints for all tables),
2222 <literal>off</> (never examine constraints), and
2223 <literal>partition</> (examine constraints only for inheritance child
2224 tables and <literal>UNION ALL</> subqueries).
2225 <literal>partition</> is the default setting.
2229 When this parameter allows it for a particular table, the planner
2230 compares query conditions with the table's <literal>CHECK</>
2231 constraints, and omits scanning tables for which the conditions
2232 contradict the constraints. For example:
2235 CREATE TABLE parent(key integer, ...);
2236 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
2237 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
2239 SELECT * FROM parent WHERE key = 2400;
2242 With constraint exclusion enabled, this <command>SELECT</>
2243 will not scan <structname>child1000</> at all. This can
2244 improve performance when inheritance is used to build
2249 Currently, constraint exclusion is enabled by default
2250 only for cases that are often used to implement table partitioning.
2251 Turning it on for all tables imposes extra planning overhead that is
2252 quite noticeable on simple queries, and most often will yield no
2253 benefit for simple queries. If you have no partitioned tables
2254 you might prefer to turn it off entirely.
2258 Refer to <xref linkend="ddl-partitioning-constraint-exclusion"> for
2259 more information on using constraint exclusion and partitioning.
2264 <varlistentry id="guc-cursor-tuple-fraction" xreflabel="cursor_tuple_fraction">
2265 <term><varname>cursor_tuple_fraction</varname> (<type>floating point</type>)</term>
2267 <primary><varname>cursor_tuple_fraction</> configuration parameter</primary>
2271 Sets the planner's estimate of the fraction of a cursor's rows that
2272 will be retrieved. The default is 0.1. Smaller values of this
2273 setting bias the planner towards using <quote>fast start</> plans
2274 for cursors, which will retrieve the first few rows quickly while
2275 perhaps taking a long time to fetch all rows. Larger values
2276 put more emphasis on the total estimated time. At the maximum
2277 setting of 1.0, cursors are planned exactly like regular queries,
2278 considering only the total estimated time and not how soon the
2279 first rows might be delivered.
2284 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
2285 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
2287 <primary><varname>from_collapse_limit</> configuration parameter</primary>
2291 The planner will merge sub-queries into upper queries if the
2292 resulting <literal>FROM</literal> list would have no more than
2293 this many items. Smaller values reduce planning time but might
2294 yield inferior query plans. The default is eight.
2295 For more information see <xref linkend="explicit-joins">.
2299 Setting this value to <xref linkend="guc-geqo-threshold"> or more
2300 may trigger use of the GEQO planner, resulting in nondeterministic
2301 plans. See <xref linkend="runtime-config-query-geqo">.
2306 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
2307 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
2309 <primary><varname>join_collapse_limit</> configuration parameter</primary>
2313 The planner will rewrite explicit <literal>JOIN</>
2314 constructs (except <literal>FULL JOIN</>s) into lists of
2315 <literal>FROM</> items whenever a list of no more than this many items
2316 would result. Smaller values reduce planning time but might
2317 yield inferior query plans.
2321 By default, this variable is set the same as
2322 <varname>from_collapse_limit</varname>, which is appropriate
2323 for most uses. Setting it to 1 prevents any reordering of
2324 explicit <literal>JOIN</>s. Thus, the explicit join order
2325 specified in the query will be the actual order in which the
2326 relations are joined. The query planner does not always choose
2327 the optimal join order; advanced users can elect to
2328 temporarily set this variable to 1, and then specify the join
2329 order they desire explicitly.
2330 For more information see <xref linkend="explicit-joins">.
2334 Setting this value to <xref linkend="guc-geqo-threshold"> or more
2335 may trigger use of the GEQO planner, resulting in nondeterministic
2336 plans. See <xref linkend="runtime-config-query-geqo">.
2345 <sect1 id="runtime-config-logging">
2346 <title>Error Reporting and Logging</title>
2348 <indexterm zone="runtime-config-logging">
2349 <primary>server log</primary>
2352 <sect2 id="runtime-config-logging-where">
2353 <title>Where To Log</title>
2355 <indexterm zone="runtime-config-logging-where">
2356 <primary>where to log</primary>
2361 <varlistentry id="guc-log-destination" xreflabel="log_destination">
2362 <term><varname>log_destination</varname> (<type>string</type>)</term>
2364 <primary><varname>log_destination</> configuration parameter</primary>
2368 <productname>PostgreSQL</productname> supports several methods
2369 for logging server messages, including
2370 <systemitem>stderr</systemitem>, <systemitem>csvlog</systemitem> and
2371 <systemitem>syslog</systemitem>. On Windows,
2372 <systemitem>eventlog</systemitem> is also supported. Set this
2373 parameter to a list of desired log destinations separated by
2374 commas. The default is to log to <systemitem>stderr</systemitem>
2376 This parameter can only be set in the <filename>postgresql.conf</>
2377 file or on the server command line.
2380 If <systemitem>csvlog</> is included in <varname>log_destination</>,
2381 log entries are output in <quote>comma separated
2382 value</> format, which is convenient for loading them into programs.
2383 See <xref linkend="runtime-config-logging-csvlog"> for details.
2384 <varname>logging_collector</varname> must be enabled to generate
2385 CSV-format log output.
2390 On most Unix systems, you will need to alter the configuration of
2391 your system's <application>syslog</application> daemon in order
2392 to make use of the <systemitem>syslog</systemitem> option for
2393 <varname>log_destination</>. <productname>PostgreSQL</productname>
2394 can log to <application>syslog</application> facilities
2395 <literal>LOCAL0</> through <literal>LOCAL7</> (see <xref
2396 linkend="guc-syslog-facility">), but the default
2397 <application>syslog</application> configuration on most platforms
2398 will discard all such messages. You will need to add something like
2400 local0.* /var/log/postgresql
2402 to the <application>syslog</application> daemon's configuration file
2409 <varlistentry id="guc-logging-collector" xreflabel="logging_collector">
2410 <term><varname>logging_collector</varname> (<type>boolean</type>)</term>
2412 <primary><varname>logging_collector</> configuration parameter</primary>
2416 This parameter allows messages sent to <application>stderr</>,
2417 and CSV-format log output, to be
2418 captured and redirected into log files.
2419 This approach is often more useful than
2420 logging to <application>syslog</>, since some types of messages
2421 might not appear in <application>syslog</> output (a common example
2422 is dynamic-linker failure messages).
2423 This parameter can only be set at server start.
2428 <varlistentry id="guc-log-directory" xreflabel="log_directory">
2429 <term><varname>log_directory</varname> (<type>string</type>)</term>
2431 <primary><varname>log_directory</> configuration parameter</primary>
2435 When <varname>logging_collector</> is enabled,
2436 this parameter determines the directory in which log files will be created.
2437 It can be specified as an absolute path, or relative to the
2438 cluster data directory.
2439 This parameter can only be set in the <filename>postgresql.conf</>
2440 file or on the server command line.
2445 <varlistentry id="guc-log-filename" xreflabel="log_filename">
2446 <term><varname>log_filename</varname> (<type>string</type>)</term>
2448 <primary><varname>log_filename</> configuration parameter</primary>
2452 When <varname>logging_collector</varname> is enabled,
2453 this parameter sets the file names of the created log files. The value
2454 is treated as a <systemitem>strftime</systemitem> pattern,
2455 so <literal>%</literal>-escapes can be used to specify time-varying
2456 file names. (Note that if there are
2457 any time-zone-dependent <literal>%</literal>-escapes, the computation
2458 is done in the zone specified
2459 by <xref linkend="guc-log-timezone">.)
2460 Note that the system's <systemitem>strftime</systemitem> is not used
2461 directly, so platform-specific (nonstandard) extensions do not work.
2464 If you specify a file name without escapes, you should plan to
2465 use a log rotation utility to avoid eventually filling the
2466 entire disk. In releases prior to 8.4, if
2467 no <literal>%</literal> escapes were
2468 present, <productname>PostgreSQL</productname> would append
2469 the epoch of the new log file's creation time, but this is no
2473 If CSV-format output is enabled in <varname>log_destination</>,
2474 <literal>.csv</> will be appended to the timestamped
2475 log file name to create the file name for CSV-format output.
2476 (If <varname>log_filename</> ends in <literal>.log</>, the suffix is
2478 In the case of the example above, the CSV
2479 file name will be <literal>server_log.1093827753.csv</literal>.
2482 This parameter can only be set in the <filename>postgresql.conf</>
2483 file or on the server command line.
2488 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
2489 <term><varname>log_rotation_age</varname> (<type>integer</type>)</term>
2491 <primary><varname>log_rotation_age</> configuration parameter</primary>
2495 When <varname>logging_collector</varname> is enabled,
2496 this parameter determines the maximum lifetime of an individual log file.
2497 After this many minutes have elapsed, a new log file will
2498 be created. Set to zero to disable time-based creation of
2500 This parameter can only be set in the <filename>postgresql.conf</>
2501 file or on the server command line.
2506 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
2507 <term><varname>log_rotation_size</varname> (<type>integer</type>)</term>
2509 <primary><varname>log_rotation_size</> configuration parameter</primary>
2513 When <varname>logging_collector</varname> is enabled,
2514 this parameter determines the maximum size of an individual log file.
2515 After this many kilobytes have been emitted into a log file,
2516 a new log file will be created. Set to zero to disable size-based
2517 creation of new log files.
2518 This parameter can only be set in the <filename>postgresql.conf</>
2519 file or on the server command line.
2524 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
2525 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)</term>
2527 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
2531 When <varname>logging_collector</varname> is enabled,
2532 this parameter will cause <productname>PostgreSQL</productname> to truncate (overwrite),
2533 rather than append to, any existing log file of the same name.
2534 However, truncation will occur only when a new file is being opened
2535 due to time-based rotation, not during server startup or size-based
2536 rotation. When off, pre-existing files will be appended to in
2537 all cases. For example, using this setting in combination with
2538 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
2539 would result in generating twenty-four hourly log files and then
2540 cyclically overwriting them.
2541 This parameter can only be set in the <filename>postgresql.conf</>
2542 file or on the server command line.
2545 Example: To keep 7 days of logs, one log file per day named
2546 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
2547 etc, and automatically overwrite last week's log with this week's log,
2548 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
2549 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>, and
2550 <varname>log_rotation_age</varname> to <literal>1440</literal>.
2553 Example: To keep 24 hours of logs, one log file per hour, but
2554 also rotate sooner if the log file size exceeds 1GB, set
2555 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
2556 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>,
2557 <varname>log_rotation_age</varname> to <literal>60</literal>, and
2558 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
2559 Including <literal>%M</> in <varname>log_filename</varname> allows
2560 any size-driven rotations that might occur to select a file name
2561 different from the hour's initial file name.
2566 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
2567 <term><varname>syslog_facility</varname> (<type>enum</type>)</term>
2569 <primary><varname>syslog_facility</> configuration parameter</primary>
2573 When logging to <application>syslog</> is enabled, this parameter
2574 determines the <application>syslog</application>
2575 <quote>facility</quote> to be used. You can choose
2576 from <literal>LOCAL0</>, <literal>LOCAL1</>,
2577 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
2578 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
2579 the default is <literal>LOCAL0</>. See also the
2580 documentation of your system's
2581 <application>syslog</application> daemon.
2582 This parameter can only be set in the <filename>postgresql.conf</>
2583 file or on the server command line.
2588 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
2589 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
2591 <primary><varname>syslog_identity</> configuration parameter</primary>
2595 When logging to <application>syslog</> is enabled, this parameter
2596 determines the program name used to identify
2597 <productname>PostgreSQL</productname> messages in
2598 <application>syslog</application> logs. The default is
2599 <literal>postgres</literal>.
2600 This parameter can only be set in the <filename>postgresql.conf</>
2601 file or on the server command line.
2606 <varlistentry id="guc-silent-mode" xreflabel="silent_mode">
2607 <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
2609 <primary><varname>silent_mode</> configuration parameter</primary>
2613 Runs the server silently. If this parameter is set, the server
2614 will automatically run in background and disassociate from the
2615 controlling terminal.
2616 This parameter can only be set at server start.
2621 When this parameter is set,
2622 the server's standard output and standard error are redirected
2623 to the file <filename>postmaster.log</> within the data directory.
2624 There is no provision for rotating this file, so it will grow
2625 indefinitely unless server log output is redirected elsewhere
2626 by other settings. It is recommended that <varname>log_destination</>
2627 be set to <literal>syslog</> or that <varname>logging_collector</> be
2628 enabled when using this option. Even with those measures, errors
2629 reported early during startup may appear in
2630 <filename>postmaster.log</> rather than the normal log destination.
2638 <sect2 id="runtime-config-logging-when">
2639 <title>When To Log</title>
2643 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
2644 <term><varname>client_min_messages</varname> (<type>enum</type>)</term>
2646 <primary><varname>client_min_messages</> configuration parameter</primary>
2650 Controls which message levels are sent to the client.
2651 Valid values are <literal>DEBUG5</>,
2652 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
2653 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
2654 <literal>WARNING</>, <literal>ERROR</>, <literal>FATAL</>,
2655 and <literal>PANIC</>. Each level
2656 includes all the levels that follow it. The later the level,
2657 the fewer messages are sent. The default is
2658 <literal>NOTICE</>. Note that <literal>LOG</> has a different
2659 rank here than in <varname>log_min_messages</>.
2664 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
2665 <term><varname>log_min_messages</varname> (<type>enum</type>)</term>
2667 <primary><varname>log_min_messages</> configuration parameter</primary>
2671 Controls which message levels are written to the server log.
2672 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
2673 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
2674 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
2675 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
2676 <literal>PANIC</>. Each level includes all the levels that
2677 follow it. The later the level, the fewer messages are sent
2678 to the log. The default is <literal>WARNING</>. Note that
2679 <literal>LOG</> has a different rank here than in
2680 <varname>client_min_messages</>.
2681 Only superusers can change this setting.
2686 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
2687 <term><varname>log_error_verbosity</varname> (<type>enum</type>)</term>
2689 <primary><varname>log_error_verbosity</> configuration parameter</primary>
2693 Controls the amount of detail written in the server log for each
2694 message that is logged. Valid values are <literal>TERSE</>,
2695 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
2696 fields to displayed messages.
2697 Only superusers can change this setting.
2702 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
2703 <term><varname>log_min_error_statement</varname> (<type>enum</type>)</term>
2705 <primary><varname>log_min_error_statement</> configuration parameter</primary>
2709 Controls whether or not the SQL statement that causes an error
2710 condition will be recorded in the server log. The current
2711 SQL statement is included in the log entry for any message of
2712 the specified severity or higher.
2713 Valid values are <literal>DEBUG5</literal>,
2714 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
2715 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
2716 <literal>INFO</literal>, <literal>NOTICE</literal>,
2717 <literal>WARNING</literal>, <literal>ERROR</literal>,
2718 <literal>LOG</literal>,
2719 <literal>FATAL</literal>, and <literal>PANIC</literal>.
2720 The default is <literal>ERROR</literal>, which means statements
2721 causing errors, log messages, fatal errors, or panics will be logged.
2722 To effectively turn off logging of failing statements,
2723 set this parameter to <literal>PANIC</literal>.
2724 Only superusers can change this setting.
2729 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
2730 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
2732 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
2736 Causes the duration of each completed statement to be logged
2737 if the statement ran for at least the specified number of
2738 milliseconds. Setting this to zero prints all statement durations.
2739 Minus-one (the default) disables logging statement durations.
2740 For example, if you set it to <literal>250ms</literal>
2741 then all SQL statements that run 250ms or longer will be
2742 logged. Enabling this parameter can be helpful in tracking down
2743 unoptimized queries in your applications.
2744 Only superusers can change this setting.
2748 For clients using extended query protocol, durations of the Parse,
2749 Bind, and Execute steps are logged independently.
2754 When using this option together with
2755 <xref linkend="guc-log-statement">,
2756 the text of statements that are logged because of
2757 <varname>log_statement</> will not be repeated in the
2758 duration log message.
2759 If you are not using <application>syslog</>, it is recommended
2760 that you log the PID or session ID using
2761 <xref linkend="guc-log-line-prefix">
2762 so that you can link the statement message to the later
2763 duration message using the process ID or session ID.
2772 <xref linkend="runtime-config-severity-levels"> explains the message
2773 severity levels used by <productname>PostgreSQL</>. If logging output
2774 is sent to <systemitem>syslog</systemitem> or Windows'
2775 <systemitem>eventlog</systemitem>, the severity levels are translated
2776 as shown in the table.
2779 <table id="runtime-config-severity-levels">
2780 <title>Message severity levels</title>
2784 <entry>Severity</entry>
2785 <entry>Usage</entry>
2786 <entry><systemitem>syslog</></entry>
2787 <entry><systemitem>eventlog</></entry>
2793 <entry><literal>DEBUG1..DEBUG5</></entry>
2794 <entry>Provides successively-more-detailed information for use by
2796 <entry><literal>DEBUG</></entry>
2797 <entry><literal>INFORMATION</></entry>
2801 <entry><literal>INFO</></entry>
2802 <entry>Provides information implicitly requested by the user,
2803 e.g., output from <command>VACUUM VERBOSE</>.</entry>
2804 <entry><literal>INFO</></entry>
2805 <entry><literal>INFORMATION</></entry>
2809 <entry><literal>NOTICE</></entry>
2810 <entry>Provides information that might be helpful to users, e.g.,
2811 notice of truncation of long identifiers.</entry>
2812 <entry><literal>NOTICE</></entry>
2813 <entry><literal>INFORMATION</></entry>
2817 <entry><literal>WARNING</></entry>
2818 <entry>Provides warnings of likely problems, e.g., <command>COMMIT</>
2819 outside a transaction block.</entry>
2820 <entry><literal>NOTICE</></entry>
2821 <entry><literal>WARNING</></entry>
2825 <entry><literal>ERROR</></entry>
2826 <entry>Reports an error that caused the current command to
2828 <entry><literal>WARNING</></entry>
2829 <entry><literal>ERROR</></entry>
2833 <entry><literal>LOG</></entry>
2834 <entry>Reports information of interest to administrators, e.g.,
2835 checkpoint activity.</entry>
2836 <entry><literal>INFO</></entry>
2837 <entry><literal>INFORMATION</></entry>
2841 <entry><literal>FATAL</></entry>
2842 <entry>Reports an error that caused the current session to
2844 <entry><literal>ERR</></entry>
2845 <entry><literal>ERROR</></entry>
2849 <entry><literal>PANIC</></entry>
2850 <entry>Reports an error that caused all database sessions to abort.</entry>
2851 <entry><literal>CRIT</></entry>
2852 <entry><literal>ERROR</></entry>
2859 <sect2 id="runtime-config-logging-what">
2860 <title>What To Log</title>
2865 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
2866 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
2867 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
2869 <primary><varname>debug_print_parse</> configuration parameter</primary>
2872 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
2875 <primary><varname>debug_print_plan</> configuration parameter</primary>
2879 These parameters enable various debugging output to be emitted.
2880 When set, they print the resulting parse tree, the query rewriter
2881 output, or the execution plan for each executed query.
2882 These messages are emitted at <literal>LOG</> message level, so by
2883 default they will appear in the server log but will not be sent to the
2884 client. You can change that by adjusting
2885 <xref linkend="guc-client-min-messages"> and/or
2886 <xref linkend="guc-log-min-messages">.
2887 These parameters are off by default.
2893 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
2895 <primary><varname>debug_pretty_print</> configuration parameter</primary>
2899 When set, <varname>debug_pretty_print</varname> indents the messages
2900 produced by <varname>debug_print_parse</varname>,
2901 <varname>debug_print_rewritten</varname>, or
2902 <varname>debug_print_plan</varname>. This results in more readable
2903 but much longer output than the <quote>compact</> format used when
2904 it is off. It is on by default.
2909 <varlistentry id="guc-log-checkpoints" xreflabel="log_checkpoints">
2910 <term><varname>log_checkpoints</varname> (<type>boolean</type>)</term>
2912 <primary><varname>log_checkpoints</> configuration parameter</primary>
2916 Causes checkpoints to be logged in the server log. Some
2917 statistics about each checkpoint are included in the log messages,
2918 including the number of buffers written and the time spent writing
2920 This parameter can only be set in the <filename>postgresql.conf</>
2921 file or on the server command line. The default is off.
2926 <varlistentry id="guc-log-connections" xreflabel="log_connections">
2927 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
2929 <primary><varname>log_connections</> configuration parameter</primary>
2933 Causes each attempted connection to the server to be logged,
2934 as well as successful completion of client authentication.
2935 This parameter can only be set in the <filename>postgresql.conf</>
2936 file or on the server command line. The default is off.
2941 Some client programs, like <application>psql</>, attempt
2942 to connect twice while determining if a password is required, so
2943 duplicate <quote>connection received</> messages do not
2944 necessarily indicate a problem.
2950 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
2951 <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
2953 <primary><varname>log_disconnections</> configuration parameter</primary>
2957 This outputs a line in the server log similar to
2958 <varname>log_connections</varname> but at session termination,
2959 and includes the duration of the session. This is off by
2961 This parameter can only be set in the <filename>postgresql.conf</>
2962 file or on the server command line.
2968 <varlistentry id="guc-log-duration" xreflabel="log_duration">
2969 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
2971 <primary><varname>log_duration</> configuration parameter</primary>
2975 Causes the duration of every completed statement to be logged.
2976 The default is <literal>off</>.
2977 Only superusers can change this setting.
2981 For clients using extended query protocol, durations of the Parse,
2982 Bind, and Execute steps are logged independently.
2987 The difference between setting this option and setting
2988 <xref linkend="guc-log-min-duration-statement"> to zero is that
2989 exceeding <varname>log_min_duration_statement</> forces the text of
2990 the query to be logged, but this option doesn't. Thus, if
2991 <varname>log_duration</> is <literal>on</> and
2992 <varname>log_min_duration_statement</> has a positive value, all
2993 durations are logged but the query text is included only for
2994 statements exceeding the threshold. This behavior can be useful for
2995 gathering statistics in high-load installations.
3001 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
3002 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
3004 <primary><varname>log_hostname</> configuration parameter</primary>
3008 By default, connection log messages only show the IP address of the
3009 connecting host. Turning on this parameter causes logging of the
3010 host name as well. Note that depending on your host name resolution
3011 setup this might impose a non-negligible performance penalty.
3012 This parameter can only be set in the <filename>postgresql.conf</>
3013 file or on the server command line.
3018 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
3019 <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
3021 <primary><varname>log_line_prefix</> configuration parameter</primary>
3025 This is a <function>printf</>-style string that is output at the
3026 beginning of each log line.
3027 <literal>%</> characters begin <quote>escape sequences</>
3028 that are replaced with status information as outlined below.
3029 Unrecognized escapes are ignored. Other
3030 characters are copied straight to the log line. Some escapes are
3031 only recognized by session processes, and do not apply to
3032 background processes such as the main server process.
3033 This parameter can only be set in the <filename>postgresql.conf</>
3034 file or on the server command line. The default is an empty string.
3040 <entry>Escape</entry>
3041 <entry>Effect</entry>
3042 <entry>Session only</entry>
3047 <entry><literal>%u</literal></entry>
3048 <entry>User name</entry>
3052 <entry><literal>%d</literal></entry>
3053 <entry>Database name</entry>
3057 <entry><literal>%r</literal></entry>
3058 <entry>Remote host name or IP address, and remote port</entry>
3062 <entry><literal>%h</literal></entry>
3063 <entry>Remote host name or IP address</entry>
3067 <entry><literal>%p</literal></entry>
3068 <entry>Process ID</entry>
3072 <entry><literal>%t</literal></entry>
3073 <entry>Time stamp without milliseconds</entry>
3077 <entry><literal>%m</literal></entry>
3078 <entry>Time stamp with milliseconds</entry>
3082 <entry><literal>%i</literal></entry>
3083 <entry>Command tag: type of session's current command</entry>
3087 <entry><literal>%e</literal></entry>
3088 <entry>SQL state</entry>
3092 <entry><literal>%c</literal></entry>
3093 <entry>Session ID: see below</entry>
3097 <entry><literal>%l</literal></entry>
3098 <entry>Number of the log line for each session or process, starting at 1</entry>
3102 <entry><literal>%s</literal></entry>
3103 <entry>Process start time stamp</entry>
3107 <entry><literal>%v</literal></entry>
3108 <entry>Virtual transaction ID (backendID/localXID)</entry>
3112 <entry><literal>%x</literal></entry>
3113 <entry>Transaction ID (0 if none is assigned)</entry>
3117 <entry><literal>%q</literal></entry>
3118 <entry>Produces no output, but tells non-session
3119 processes to stop at this point in the string; ignored by
3120 session processes</entry>
3124 <entry><literal>%%</literal></entry>
3125 <entry>Literal <literal>%</></entry>
3132 The <literal>%c</> escape prints a quasi-unique session identifier,
3133 consisting of two 4-byte hexadecimal numbers (without leading zeros)
3134 separated by a dot. The numbers are the process start time and the
3135 process ID, so <literal>%c</> can also be used as a space saving way
3136 of printing those items. For example, to generate the session
3137 identifier from <literal>pg_stat_activity</>, use this query:
3139 SELECT to_hex(EXTRACT(EPOCH FROM backend_start)::integer) || '.' ||
3141 FROM pg_stat_activity;
3148 If you set a nonempty value for <varname>log_line_prefix</>,
3149 you should usually make its last character be a space, to provide
3150 visual separation from the rest of the log line. A punctuation
3151 character could be used too.
3157 <application>Syslog</> produces its own
3158 time stamp and process ID information, so you probably do not want to
3159 use those escapes if you are logging to <application>syslog</>.
3165 <varlistentry id="guc-log-lock-waits" xreflabel="log_lock_waits">
3166 <term><varname>log_lock_waits</varname> (<type>boolean</type>)</term>
3168 <primary><varname>log_lock_waits</> configuration parameter</primary>
3172 Controls whether a log message is produced when a session waits
3173 longer than <xref linkend="guc-deadlock-timeout"> to acquire a
3174 lock. This is useful in determining if lock waits are causing
3175 poor performance. The default is <literal>off</>.
3180 <varlistentry id="guc-log-statement" xreflabel="log_statement">
3181 <term><varname>log_statement</varname> (<type>enum</type>)</term>
3183 <primary><varname>log_statement</> configuration parameter</primary>
3187 Controls which SQL statements are logged. Valid values are
3188 <literal>none</>, <literal>ddl</>, <literal>mod</>, and
3189 <literal>all</>. <literal>ddl</> logs all data definition
3190 statements, such as <command>CREATE</>, <command>ALTER</>, and
3191 <command>DROP</> statements. <literal>mod</> logs all
3192 <literal>ddl</> statements, plus data-modifying statements
3193 such as <command>INSERT</>,
3194 <command>UPDATE</>, <command>DELETE</>, <command>TRUNCATE</>,
3195 and <command>COPY FROM</>.
3196 <command>PREPARE</>, <command>EXECUTE</>, and
3197 <command>EXPLAIN ANALYZE</> statements are also logged if their
3198 contained command is of an appropriate type. For clients using
3199 extended query protocol, logging occurs when an Execute message
3200 is received, and values of the Bind parameters are included
3201 (with any embedded single-quote marks doubled).
3205 The default is <literal>none</>. Only superusers can change this
3211 Statements that contain simple syntax errors are not logged
3212 even by the <varname>log_statement</> = <literal>all</> setting,
3213 because the log message is emitted only after basic parsing has
3214 been done to determine the statement type. In the case of extended
3215 query protocol, this setting likewise does not log statements that
3216 fail before the Execute phase (i.e., during parse analysis or
3217 planning). Set <varname>log_min_error_statement</> to
3218 <literal>ERROR</> (or lower) to log such statements.
3224 <varlistentry id="guc-log-temp-files" xreflabel="log_temp_files">
3225 <term><varname>log_temp_files</varname> (<type>integer</type>)</term>
3227 <primary><varname>log_temp_files</> configuration parameter</primary>
3231 Controls logging of use of temporary files.
3232 Temporary files can be
3233 created for sorts, hashes, and temporary query results.
3234 A log entry is made for each temporary file when it is deleted.
3235 A value of zero logs all temporary files, while positive
3236 values log only files whose size is greater than or equal to
3237 the specified number of kilobytes. The
3238 default setting is <literal>-1</>, which disables such logging.
3239 Only superusers can change this setting.
3244 <varlistentry id="guc-log-timezone" xreflabel="log_timezone">
3245 <term><varname>log_timezone</varname> (<type>string</type>)</term>
3247 <primary><varname>log_timezone</> configuration parameter</primary>
3251 Sets the time zone used for timestamps written in the log.
3252 Unlike <xref linkend="guc-timezone">, this value is cluster-wide,
3253 so that all sessions will report timestamps consistently.
3254 The default is <literal>unknown</>, which means to use whatever
3255 the system environment specifies as the time zone. See <xref
3256 linkend="datatype-timezones"> for more information.
3257 This parameter can only be set in the <filename>postgresql.conf</>
3258 file or on the server command line.
3265 <sect2 id="runtime-config-logging-csvlog">
3266 <title>Using CSV-Format Log Output</title>
3269 Including <literal>csvlog</> in the <varname>log_destination</> list
3270 provides a convenient way to import log files into a database table.
3271 This option emits log lines in comma-separated-value format,
3272 with these columns: timestamp with milliseconds, user name, database
3273 name, process ID, host:port number, session ID, per-session or -process line
3274 number, command tag, session start time, virtual transaction ID,
3275 regular transaction id, error severity, SQL state code, error message,
3276 error message detail, hint, internal query that led to the error (if
3277 any), character count of the error position thereof, error context,
3278 user query that led to the error (if any and enabled by
3279 <varname>log_min_error_statement</>), character count of the error
3280 position thereof, location of the error in the PostgreSQL source code
3281 (if <varname>log_error_verbosity</> is set to <literal>verbose</>).
3282 Here is a sample table definition for storing CSV-format log output:
3285 CREATE TABLE postgres_log
3287 log_time timestamp(3) with time zone,
3291 connection_from text,
3293 session_line_num bigint,
3295 session_start_time timestamp with time zone,
3296 virtual_transaction_id text,
3297 transaction_id bigint,
3298 error_severity text,
3299 sql_state_code text,
3303 internal_query text,
3304 internal_query_pos integer,
3309 PRIMARY KEY (session_id, session_line_num)
3315 To import a log file into this table, use the <command>COPY FROM</>
3319 COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
3324 There are a few things you need to do to simplify importing CSV log
3325 files easily and automatically:
3330 Set <varname>log_filename</varname> and
3331 <varname>log_rotation_age</> to provide a consistent,
3332 predictable naming scheme for your log files. This lets you
3333 predict what the file name will be and know when an individual log
3334 file is complete and therefore ready to be imported.
3340 Set <varname>log_rotation_size</varname> to 0 to disable
3341 size-based log rotation, as it makes the log file name difficult
3348 Set <varname>log_truncate_on_rotation</varname> to <literal>on</> so
3349 that old log data isn't mixed with the new in the same file.
3355 The table definition above includes a primary key specification.
3356 This is useful to protect against accidentally importing the same
3357 information twice. The <command>COPY</> command commits all of the
3358 data it imports at one time, so any error will cause the entire
3359 import to fail. If you import a partial log file and later import
3360 the file again when it is complete, the primary key violation will
3361 cause the import to fail. Wait until the log is complete and
3362 closed before importing. This procedure will also protect against
3363 accidentally importing a partial line that hasn't been completely
3364 written, which would also cause <command>COPY</> to fail.
3373 <sect1 id="runtime-config-statistics">
3374 <title>Run-Time Statistics</title>
3376 <sect2 id="runtime-config-statistics-collector">
3377 <title>Query and Index Statistics Collector</title>
3380 These parameters control server-wide statistics collection features.
3381 When statistics collection is enabled, the data that is produced can be
3382 accessed via the <structname>pg_stat</structname> and
3383 <structname>pg_statio</structname> family of system views.
3384 Refer to <xref linkend="monitoring"> for more information.
3389 <varlistentry id="guc-track-activities" xreflabel="track_activities">
3390 <term><varname>track_activities</varname> (<type>boolean</type>)</term>
3392 <primary><varname>track_activities</> configuration parameter</primary>
3396 Enables the collection of information on the currently
3397 executing command of each session, along with the time at
3398 which that command began execution. This parameter is on by
3399 default. Note that even when enabled, this information is not
3400 visible to all users, only to superusers and the user owning
3401 the session being reported on; so it should not represent a
3403 Only superusers can change this setting.
3408 <varlistentry id="guc-track-activity-query-size" xreflabel="track_activity_query_size">
3409 <term><varname>track_activity_query_size</varname> (<type>integer</type>)</term>
3411 <primary><varname>track_activity_query_size</> configuration parameter</primary>
3415 Specifies the number of bytes reserved to track the currently
3416 executing command for each active session, for the
3417 <structname>pg_stat_activity</>.<structfield>current_query</> field.
3418 The default value is 1024. This parameter can only be set at server
3424 <varlistentry id="guc-track-counts" xreflabel="track_counts">
3425 <term><varname>track_counts</varname> (<type>boolean</type>)</term>
3427 <primary><varname>track_counts</> configuration parameter</primary>
3431 Enables collection of statistics on database activity.
3432 This parameter is on by default, because the autovacuum
3433 daemon needs the collected information.
3434 Only superusers can change this setting.
3439 <varlistentry id="guc-track-functions" xreflabel="track_functions">
3440 <term><varname>track_functions</varname> (<type>enum</type>)</term>
3442 <primary><varname>track_functions</> configuration parameter</primary>
3446 Enables tracking of function call counts and time used. Specify
3447 <literal>pl</literal> to track only procedural-language functions,
3448 <literal>all</literal> to also track SQL and C language functions.
3449 The default is <literal>none</literal>, which disables function
3450 statistics tracking. Only superusers can change this setting.
3455 SQL-language functions that are simple enough to be <quote>inlined</>
3456 into the calling query will not be tracked, regardless of this
3463 <varlistentry id="guc-update-process-title" xreflabel="update_process_title">
3464 <term><varname>update_process_title</varname> (<type>boolean</type>)</term>
3466 <primary><varname>update_process_title</> configuration parameter</primary>
3470 Enables updating of the process title every time a new SQL command
3471 is received by the server. The process title is typically viewed
3472 by the <command>ps</> command,
3473 or in Windows by using the <application>Process Explorer</>.
3474 Only superusers can change this setting.
3479 <varlistentry id="guc-stats-temp-directory" xreflabel="stats_temp_directory">
3480 <term><varname>stats_temp_directory</varname> (<type>string</type>)</term>
3482 <primary><varname>stats_temp_directory</> configuration parameter</primary>
3486 Sets the directory to store temporary statistics data in. This can be
3487 a path relative to the data directory or an absolute path. The default
3488 is <filename>pg_stat_tmp</filename>. Pointing this at a RAM based
3489 filesystem will decrease physical I/O requirements and can lead to
3490 improved performance.
3491 This parameter can only be set in the <filename>postgresql.conf</>
3492 file or on the server command line.
3500 <sect2 id="runtime-config-statistics-monitor">
3501 <title>Statistics Monitoring</title>
3505 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
3506 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
3507 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
3508 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
3510 <primary><varname>log_statement_stats</> configuration parameter</primary>
3513 <primary><varname>log_parser_stats</> configuration parameter</primary>
3516 <primary><varname>log_planner_stats</> configuration parameter</primary>
3519 <primary><varname>log_executor_stats</> configuration parameter</primary>
3523 For each query, write performance statistics of the respective
3524 module to the server log. This is a crude profiling
3525 instrument. <varname>log_statement_stats</varname> reports total
3526 statement statistics, while the others report per-module statistics.
3527 <varname>log_statement_stats</varname> cannot be enabled together with
3528 any of the per-module options. All of these options are disabled by
3529 default. Only superusers can change these settings.
3539 <sect1 id="runtime-config-autovacuum">
3540 <title>Automatic Vacuuming</title>
3543 <primary>autovacuum</primary>
3544 <secondary>configuration parameters</secondary>
3548 These settings control the behavior of the <firstterm>autovacuum</>
3549 feature. Refer to <xref linkend="autovacuum"> for
3555 <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
3556 <term><varname>autovacuum</varname> (<type>boolean</type>)</term>
3558 <primary><varname>autovacuum</> configuration parameter</primary>
3562 Controls whether the server should run the
3563 autovacuum launcher daemon. This is on by default; however,
3564 <xref linkend="guc-track-counts"> must also be turned on for
3566 This parameter can only be set in the <filename>postgresql.conf</>
3567 file or on the server command line.
3570 Note that even when this parameter is disabled, the system
3571 will launch autovacuum processes if necessary to
3572 prevent transaction ID wraparound. See <xref
3573 linkend="vacuum-for-wraparound"> for more information.
3578 <varlistentry id="guc-log-autovacuum-min-duration" xreflabel="log_autovacuum_min_duration">
3579 <term><varname>log_autovacuum_min_duration</varname> (<type>integer</type>)</term>
3581 <primary><varname>log_autovacuum_min_duration</> configuration parameter</primary>
3585 Causes each action executed by autovacuum to be logged if it ran for at
3586 least the specified number of milliseconds. Setting this to zero logs
3587 all autovacuum actions. Minus-one (the default) disables logging
3588 autovacuum actions. For example, if you set this to
3589 <literal>250ms</literal> then all automatic vacuums and analyzes that run
3590 250ms or longer will be logged. Enabling this parameter can be helpful
3591 in tracking autovacuum activity. This setting can only be set in
3592 the <filename>postgresql.conf</> file or on the server command line.
3597 <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
3598 <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)</term>
3600 <primary><varname>autovacuum_max_workers</> configuration parameter</primary>
3604 Specifies the maximum number of autovacuum processes (other than the
3605 autovacuum launcher) which may be running at any one time. The default
3606 is three. This parameter can only be set in
3607 the <filename>postgresql.conf</> file or on the server command line.
3612 <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
3613 <term><varname>autovacuum_naptime</varname> (<type>integer</type>)</term>
3615 <primary><varname>autovacuum_naptime</> configuration parameter</primary>
3619 Specifies the minimum delay between autovacuum runs on any given
3620 database. In each round the daemon examines the
3621 database and issues <command>VACUUM</> and <command>ANALYZE</> commands
3622 as needed for tables in that database. The delay is measured
3623 in seconds, and the default is one minute (<literal>1m</>).
3624 This parameter can only be set in the <filename>postgresql.conf</>
3625 file or on the server command line.
3630 <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
3631 <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)</term>
3633 <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
3637 Specifies the minimum number of updated or deleted tuples needed
3638 to trigger a <command>VACUUM</> in any one table.
3639 The default is 50 tuples.
3640 This parameter can only be set in the <filename>postgresql.conf</>
3641 file or on the server command line.
3642 This setting can be overridden for individual tables by
3643 changing storage parameters.
3648 <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
3649 <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)</term>
3651 <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
3655 Specifies the minimum number of inserted, updated or deleted tuples
3656 needed to trigger an <command>ANALYZE</> in any one table.
3657 The default is 50 tuples.
3658 This parameter can only be set in the <filename>postgresql.conf</>
3659 file or on the server command line.
3660 This setting can be overridden for individual tables by
3661 changing storage parameters.
3666 <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
3667 <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)</term>
3669 <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
3673 Specifies a fraction of the table size to add to
3674 <varname>autovacuum_vacuum_threshold</varname>
3675 when deciding whether to trigger a <command>VACUUM</>.
3676 The default is 0.2 (20% of table size).
3677 This parameter can only be set in the <filename>postgresql.conf</>
3678 file or on the server command line.
3679 This setting can be overridden for individual tables by
3680 changing storage parameters.
3685 <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
3686 <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)</term>
3688 <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
3692 Specifies a fraction of the table size to add to
3693 <varname>autovacuum_analyze_threshold</varname>
3694 when deciding whether to trigger an <command>ANALYZE</>.
3695 The default is 0.1 (10% of table size).
3696 This parameter can only be set in the <filename>postgresql.conf</>
3697 file or on the server command line.
3698 This setting can be overridden for individual tables by
3699 changing storage parameters.
3704 <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
3705 <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)</term>
3707 <primary><varname>autovacuum_freeze_max_age</> configuration parameter</primary>
3711 Specifies the maximum age (in transactions) that a table's
3712 <structname>pg_class</>.<structfield>relfrozenxid</> field can
3713 attain before a <command>VACUUM</> operation is forced to prevent
3714 transaction ID wraparound within the table. Note that the system
3715 will launch autovacuum processes to prevent wraparound even when
3716 autovacuum is otherwise disabled.
3717 The default is 200 million transactions.
3718 This parameter can only be set at server start, but the setting
3719 can be reduced for individual tables by
3720 changing storage parameters.
3721 For more information see <xref linkend="vacuum-for-wraparound">.
3726 <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
3727 <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
3729 <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
3733 Specifies the cost delay value that will be used in automatic
3734 <command>VACUUM</> operations. If <literal>-1</> is
3735 specified, the regular
3736 <xref linkend="guc-vacuum-cost-delay"> value will be used.
3737 The default value is 20 milliseconds.
3738 This parameter can only be set in the <filename>postgresql.conf</>
3739 file or on the server command line.
3740 This setting can be overridden for individual tables by
3741 changing storage parameters.
3746 <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
3747 <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
3749 <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
3753 Specifies the cost limit value that will be used in automatic
3754 <command>VACUUM</> operations. If <literal>-1</> is specified (which is the
3755 default), the regular
3756 <xref linkend="guc-vacuum-cost-limit"> value will be used. Note that
3757 the value is distributed proportionally among the running autovacuum
3758 workers, if there is more than one, so that the sum of the limits of
3759 each worker never exceeds the limit on this variable.
3760 This parameter can only be set in the <filename>postgresql.conf</>
3761 file or on the server command line.
3762 This setting can be overridden for individual tables by
3763 changing storage parameters.
3771 <sect1 id="runtime-config-client">
3772 <title>Client Connection Defaults</title>
3774 <sect2 id="runtime-config-client-statement">
3775 <title>Statement Behavior</title>
3778 <varlistentry id="guc-search-path" xreflabel="search_path">
3779 <term><varname>search_path</varname> (<type>string</type>)</term>
3781 <primary><varname>search_path</> configuration parameter</primary>
3783 <indexterm><primary>path</><secondary>for schemas</></>
3786 This variable specifies the order in which schemas are searched
3787 when an object (table, data type, function, etc.) is referenced by a
3788 simple name with no schema component. When there are objects of
3789 identical names in different schemas, the one found first
3790 in the search path is used. An object that is not in any of the
3791 schemas in the search path can only be referenced by specifying
3792 its containing schema with a qualified (dotted) name.
3796 The value for <varname>search_path</varname> has to be a comma-separated
3797 list of schema names. If one of the list items is
3798 the special value <literal>$user</literal>, then the schema
3799 having the name returned by <function>SESSION_USER</> is substituted, if there
3800 is such a schema. (If not, <literal>$user</literal> is ignored.)
3804 The system catalog schema, <literal>pg_catalog</>, is always
3805 searched, whether it is mentioned in the path or not. If it is
3806 mentioned in the path then it will be searched in the specified
3807 order. If <literal>pg_catalog</> is not in the path then it will
3808 be searched <emphasis>before</> searching any of the path items.
3812 Likewise, the current session's temporary-table schema,
3813 <literal>pg_temp_<replaceable>nnn</></>, is always searched if it
3814 exists. It can be explicitly listed in the path by using the
3815 alias <literal>pg_temp</>. If it is not listed in the path then
3816 it is searched first (before even <literal>pg_catalog</>). However,
3817 the temporary schema is only searched for relation (table, view,
3818 sequence, etc) and data type names. It will never be searched for
3819 function or operator names.
3823 When objects are created without specifying a particular target
3824 schema, they will be placed in the first schema listed
3825 in the search path. An error is reported if the search path is
3830 The default value for this parameter is
3831 <literal>'"$user", public'</literal> (where the second part will be
3832 ignored if there is no schema named <literal>public</>).
3833 This supports shared use of a database (where no users
3834 have private schemas, and all share use of <literal>public</>),
3835 private per-user schemas, and combinations of these. Other
3836 effects can be obtained by altering the default search path
3837 setting, either globally or per-user.
3841 The current effective value of the search path can be examined
3842 via the <acronym>SQL</acronym> function
3843 <function>current_schemas()</>. This is not quite the same as
3844 examining the value of <varname>search_path</varname>, since
3845 <function>current_schemas()</> shows how the requests
3846 appearing in <varname>search_path</varname> were resolved.
3850 For more information on schema handling, see <xref linkend="ddl-schemas">.
3855 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
3856 <term><varname>default_tablespace</varname> (<type>string</type>)</term>
3858 <primary><varname>default_tablespace</> configuration parameter</primary>
3860 <indexterm><primary>tablespace</><secondary>default</></>
3863 This variable specifies the default tablespace in which to create
3864 objects (tables and indexes) when a <command>CREATE</> command does
3865 not explicitly specify a tablespace.
3869 The value is either the name of a tablespace, or an empty string
3870 to specify using the default tablespace of the current database.
3871 If the value does not match the name of any existing tablespace,
3872 <productname>PostgreSQL</> will automatically use the default
3873 tablespace of the current database. If a nondefault tablespace
3874 is specified, the user must have <literal>CREATE</> privilege
3875 for it, or creation attempts will fail.
3879 This variable is not used for temporary tables; for them,
3880 <xref linkend="guc-temp-tablespaces"> is consulted instead.
3884 For more information on tablespaces,
3885 see <xref linkend="manage-ag-tablespaces">.
3890 <varlistentry id="guc-temp-tablespaces" xreflabel="temp_tablespaces">
3891 <term><varname>temp_tablespaces</varname> (<type>string</type>)</term>
3893 <primary><varname>temp_tablespaces</> configuration parameter</primary>
3895 <indexterm><primary>tablespace</><secondary>temporary</></>
3898 This variable specifies tablespace(s) in which to create temporary
3899 objects (temp tables and indexes on temp tables) when a
3900 <command>CREATE</> command does not explicitly specify a tablespace.
3901 Temporary files for purposes such as sorting large data sets
3902 are also created in these tablespace(s).
3906 The value is a list of names of tablespaces. When there is more than
3907 one name in the list, <productname>PostgreSQL</> chooses a random
3908 member of the list each time a temporary object is to be created;
3909 except that within a transaction, successively created temporary
3910 objects are placed in successive tablespaces from the list.
3911 If the selected element of the list is an empty string,
3912 <productname>PostgreSQL</> will automatically use the default
3913 tablespace of the current database instead.
3917 When <varname>temp_tablespaces</> is set interactively, specifying a
3918 nonexistent tablespace is an error, as is specifying a tablespace for
3919 which the user does not have <literal>CREATE</> privilege. However,
3920 when using a previously set value, nonexistent tablespaces are
3921 ignored, as are tablespaces for which the user lacks
3922 <literal>CREATE</> privilege. In particular, this rule applies when
3923 using a value set in <filename>postgresql.conf</>.
3927 The default value is an empty string, which results in all temporary
3928 objects being created in the default tablespace of the current
3933 See also <xref linkend="guc-default-tablespace">.
3938 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
3939 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
3941 <primary><varname>check_function_bodies</> configuration parameter</primary>
3945 This parameter is normally on. When set to <literal>off</>, it
3946 disables validation of the function body string during <xref
3947 linkend="sql-createfunction"
3948 endterm="sql-createfunction-title">. Disabling validation is
3949 occasionally useful to avoid problems such as forward references
3950 when restoring function definitions from a dump.
3955 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
3957 <primary>transaction isolation level</primary>
3960 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
3962 <term><varname>default_transaction_isolation</varname> (<type>enum</type>)</term>
3965 Each SQL transaction has an isolation level, which can be
3966 either <quote>read uncommitted</quote>, <quote>read
3967 committed</quote>, <quote>repeatable read</quote>, or
3968 <quote>serializable</quote>. This parameter controls the
3969 default isolation level of each new transaction. The default
3970 is <quote>read committed</quote>.
3974 Consult <xref linkend="mvcc"> and <xref
3975 linkend="sql-set-transaction"
3976 endterm="sql-set-transaction-title"> for more information.
3981 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
3983 <primary>read-only transaction</primary>
3986 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
3989 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
3992 A read-only SQL transaction cannot alter non-temporary tables.
3993 This parameter controls the default read-only status of each new
3994 transaction. The default is <literal>off</> (read/write).
3998 Consult <xref linkend="sql-set-transaction"
3999 endterm="sql-set-transaction-title"> for more information.
4004 <varlistentry id="guc-session-replication-role" xreflabel="session_replication_role">
4005 <term><varname>session_replication_role</varname> (<type>enum</type>)</term>
4007 <primary><varname>session_replication_role</> configuration parameter</primary>
4011 Controls firing of replication-related triggers and rules for the
4012 current session. Setting this variable requires
4013 superuser privilege and results in discarding any previously cached
4014 query plans. Possible values are <literal>origin</> (the default),
4015 <literal>replica</> and <literal>local</>.
4016 See <xref linkend="sql-altertable" endterm="sql-altertable-title"> for
4022 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
4023 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
4025 <primary><varname>statement_timeout</> configuration parameter</primary>
4029 Abort any statement that takes over the specified number of
4030 milliseconds, starting from the time the command arrives at the server
4031 from the client. If <varname>log_min_error_statement</> is set to
4032 <literal>ERROR</> or lower, the statement that timed out will also be
4033 logged. A value of zero (the default) turns off the
4038 Setting <varname>statement_timeout</> in
4039 <filename>postgresql.conf</> is not recommended because it
4040 affects all sessions.
4045 <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
4046 <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)</term>
4048 <primary><varname>vacuum_freeze_table_age</> configuration parameter</primary>
4052 <command>VACUUM</> performs a whole-table scan if the table's
4053 <structname>pg_class</>.<structfield>relfrozenxid</> field has reached
4054 the age specified by this setting. The default is 150 million
4055 transactions. Although users can set this value anywhere from zero to
4056 one billion, <command>VACUUM</> will silently limit the effective value
4057 to 95% of <xref linkend="guc-autovacuum-freeze-max-age">, so that a
4058 periodical manual <command>VACUUM</> has a chance to run before an
4059 anti-wraparound autovacuum is launched for the table. For more
4061 <xref linkend="vacuum-for-wraparound">.
4066 <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
4067 <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)</term>
4069 <primary><varname>vacuum_freeze_min_age</> configuration parameter</primary>
4073 Specifies the cutoff age (in transactions) that <command>VACUUM</>
4074 should use to decide whether to replace transaction IDs with
4075 <literal>FrozenXID</> while scanning a table.
4076 The default is 50 million transactions. Although
4077 users can set this value anywhere from zero to one billion,
4078 <command>VACUUM</> will silently limit the effective value to half
4079 the value of <xref linkend="guc-autovacuum-freeze-max-age">, so
4080 that there is not an unreasonably short time between forced
4081 autovacuums. For more information see <xref
4082 linkend="vacuum-for-wraparound">.
4087 <varlistentry id="guc-bytea-output" xreflabel="bytea_output">
4088 <term><varname>bytea_output</varname> (<type>enum</type>)</term>
4090 <primary><varname>bytea_output</> configuration parameter</primary>
4094 Sets the output format for values of type <type>bytea</type>.
4095 Valid values are <literal>hex</literal> (the default)
4096 and <literal>escape</literal> (the traditional PostgreSQL
4097 format). See <xref linkend="datatype-binary"> for more
4098 information. The <type>bytea</type> type always
4099 accepts both formats on input, regardless of this setting.
4104 <varlistentry id="guc-xmlbinary" xreflabel="xmlbinary">
4105 <term><varname>xmlbinary</varname> (<type>enum</type>)</term>
4107 <primary><varname>xmlbinary</> configuration parameter</primary>
4111 Sets how binary values are to be encoded in XML. This applies
4112 for example when <type>bytea</type> values are converted to
4113 XML by the functions <function>xmlelement</function> or
4114 <function>xmlforest</function>. Possible values are
4115 <literal>base64</literal> and <literal>hex</literal>, which
4116 are both defined in the XML Schema standard. The default is
4117 <literal>base64</literal>. For further information about
4118 XML-related functions, see <xref linkend="functions-xml">.
4122 The actual choice here is mostly a matter of taste,
4123 constrained only by possible restrictions in client
4124 applications. Both methods support all possible values,
4125 although the hex encoding will be somewhat larger than the
4131 <varlistentry id="guc-xmloption" xreflabel="xmloption">
4132 <term><varname>xmloption</varname> (<type>enum</type>)</term>
4134 <primary><varname>xmloption</> configuration parameter</primary>
4137 <primary><varname>SET XML OPTION</></primary>
4140 <primary>XML option</primary>
4144 Sets whether <literal>DOCUMENT</literal> or
4145 <literal>CONTENT</literal> is implicit when converting between
4146 XML and character string values. See <xref
4147 linkend="datatype-xml"> for a description of this. Valid
4148 values are <literal>DOCUMENT</literal> and
4149 <literal>CONTENT</literal>. The default is
4150 <literal>CONTENT</literal>.
4154 According to the SQL standard, the command to set this option is
4156 SET XML OPTION { DOCUMENT | CONTENT };
4158 This syntax is also available in PostgreSQL.
4165 <sect2 id="runtime-config-client-format">
4166 <title>Locale and Formatting</title>
4170 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
4171 <term><varname>DateStyle</varname> (<type>string</type>)</term>
4173 <primary><varname>DateStyle</> configuration parameter</primary>
4177 Sets the display format for date and time values, as well as the
4178 rules for interpreting ambiguous date input values. For
4179 historical reasons, this variable contains two independent
4180 components: the output format specification (<literal>ISO</>,
4181 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
4182 and the input/output specification for year/month/day ordering
4183 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
4184 can be set separately or together. The keywords <literal>Euro</>
4185 and <literal>European</> are synonyms for <literal>DMY</>; the
4186 keywords <literal>US</>, <literal>NonEuro</>, and
4187 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
4188 <xref linkend="datatype-datetime"> for more information. The
4189 built-in default is <literal>ISO, MDY</>, but
4190 <application>initdb</application> will initialize the
4191 configuration file with a setting that corresponds to the
4192 behavior of the chosen <varname>lc_time</varname> locale.
4197 <varlistentry id="guc-intervalstyle" xreflabel="IntervalStyle">
4198 <term><varname>IntervalStyle</varname> (<type>enum</type>)</term>
4200 <primary><varname>IntervalStyle</> configuration parameter</primary>
4204 Sets the display format for interval values.
4205 The value <literal>sql_standard</> will produce
4206 output matching <acronym>SQL</acronym> standard interval literals.
4207 The value <literal>postgres</> (which is the default) will produce
4208 output matching <productname>PostgreSQL</> releases prior to 8.4
4209 when the <xref linkend="guc-datestyle">
4210 parameter was set to <literal>ISO</>.
4211 The value <literal>postgres_verbose</> will produce output
4212 matching <productname>PostgreSQL</> releases prior to 8.4
4213 when the <varname>DateStyle</>
4214 parameter was set to non-<literal>ISO</> output.
4215 The value <literal>iso_8601</> will produce output matching the time
4216 interval <quote>format with designators</> defined in section
4217 4.4.3.2 of ISO 8601.
4220 The <varname>IntervalStyle</> parameter also affects the
4221 interpretation of ambiguous interval input. See
4222 <xref linkend="datatype-interval-input"> for more information.
4227 <varlistentry id="guc-timezone" xreflabel="timezone">
4228 <term><varname>timezone</varname> (<type>string</type>)</term>
4230 <primary><varname>timezone</> configuration parameter</primary>
4232 <indexterm><primary>time zone</></>
4235 Sets the time zone for displaying and interpreting time stamps.
4236 The default is <literal>unknown</>, which means to use whatever
4237 the system environment specifies as the time zone. See <xref
4238 linkend="datatype-timezones"> for more
4244 <varlistentry id="guc-timezone-abbreviations" xreflabel="timezone_abbreviations">
4245 <term><varname>timezone_abbreviations</varname> (<type>string</type>)</term>
4247 <primary><varname>timezone_abbreviations</> configuration parameter</primary>
4249 <indexterm><primary>time zone names</></>
4252 Sets the collection of time zone abbreviations that will be accepted
4253 by the server for datetime input. The default is <literal>'Default'</>,
4254 which is a collection that works in most of the world; there are
4255 also 'Australia' and 'India', and other collections can be defined
4256 for a particular installation. See <xref
4257 linkend="datetime-appendix"> for more information.
4262 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
4264 <primary>significant digits</primary>
4267 <primary>floating-point</primary>
4268 <secondary>display</secondary>
4271 <primary><varname>extra_float_digits</> configuration parameter</primary>
4274 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
4277 This parameter adjusts the number of digits displayed for
4278 floating-point values, including <type>float4</>, <type>float8</>,
4279 and geometric data types. The parameter value is added to the
4280 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
4281 as appropriate). The value can be set as high as 2, to include
4282 partially-significant digits; this is especially useful for dumping
4283 float data that needs to be restored exactly. Or it can be set
4284 negative to suppress unwanted digits.
4289 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
4290 <term><varname>client_encoding</varname> (<type>string</type>)</term>
4292 <primary><varname>client_encoding</> configuration parameter</primary>
4294 <indexterm><primary>character set</></>
4297 Sets the client-side encoding (character set).
4298 The default is to use the database encoding.
4303 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
4304 <term><varname>lc_messages</varname> (<type>string</type>)</term>
4306 <primary><varname>lc_messages</> configuration parameter</primary>
4310 Sets the language in which messages are displayed. Acceptable
4311 values are system-dependent; see <xref linkend="locale"> for
4312 more information. If this variable is set to the empty string
4313 (which is the default) then the value is inherited from the
4314 execution environment of the server in a system-dependent way.
4318 On some systems, this locale category does not exist. Setting
4319 this variable will still work, but there will be no effect.
4320 Also, there is a chance that no translated messages for the
4321 desired language exist. In that case you will continue to see
4322 the English messages.
4326 Only superusers can change this setting, because it affects the
4327 messages sent to the server log as well as to the client.
4332 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
4333 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
4335 <primary><varname>lc_monetary</> configuration parameter</primary>
4339 Sets the locale to use for formatting monetary amounts, for
4340 example with the <function>to_char</function> family of
4341 functions. Acceptable values are system-dependent; see <xref
4342 linkend="locale"> for more information. If this variable is
4343 set to the empty string (which is the default) then the value
4344 is inherited from the execution environment of the server in a
4345 system-dependent way.
4350 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
4351 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
4353 <primary><varname>lc_numeric</> configuration parameter</primary>
4357 Sets the locale to use for formatting numbers, for example
4358 with the <function>to_char</function> family of
4359 functions. Acceptable values are system-dependent; see <xref
4360 linkend="locale"> for more information. If this variable is
4361 set to the empty string (which is the default) then the value
4362 is inherited from the execution environment of the server in a
4363 system-dependent way.
4368 <varlistentry id="guc-lc-time" xreflabel="lc_time">
4369 <term><varname>lc_time</varname> (<type>string</type>)</term>
4371 <primary><varname>lc_time</> configuration parameter</primary>
4375 Sets the locale to use for formatting dates and times, for example
4376 with the <function>to_char</function> family of
4377 functions. Acceptable values are system-dependent; see <xref
4378 linkend="locale"> for more information. If this variable is
4379 set to the empty string (which is the default) then the value
4380 is inherited from the execution environment of the server in a
4381 system-dependent way.
4386 <varlistentry id="guc-default-text-search-config" xreflabel="default_text_search_config">
4387 <term><varname>default_text_search_config</varname> (<type>string</type>)</term>
4389 <primary><varname>default_text_search_config</> configuration parameter</primary>
4393 Selects the text search configuration that is used by those variants
4394 of the text search functions that do not have an explicit argument
4395 specifying the configuration.
4396 See <xref linkend="textsearch"> for further information.
4397 The built-in default is <literal>pg_catalog.simple</>, but
4398 <application>initdb</application> will initialize the
4399 configuration file with a setting that corresponds to the
4400 chosen <varname>lc_ctype</varname> locale, if a configuration
4401 matching that locale can be identified.
4409 <sect2 id="runtime-config-client-other">
4410 <title>Other Defaults</title>
4414 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
4415 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
4417 <primary><varname>dynamic_library_path</> configuration parameter</primary>
4419 <indexterm><primary>dynamic loading</></>
4422 If a dynamically loadable module needs to be opened and the
4423 file name specified in the <command>CREATE FUNCTION</command> or
4424 <command>LOAD</command> command
4425 does not have a directory component (i.e., the
4426 name does not contain a slash), the system will search this
4427 path for the required file.
4431 The value for <varname>dynamic_library_path</varname> has to be a
4432 list of absolute directory paths separated by colons (or semi-colons
4433 on Windows). If a list element starts
4434 with the special string <literal>$libdir</literal>, the
4435 compiled-in <productname>PostgreSQL</productname> package
4436 library directory is substituted for <literal>$libdir</literal>. This
4437 is where the modules provided by the standard
4438 <productname>PostgreSQL</productname> distribution are installed.
4439 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
4440 this directory.) For example:
4442 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
4444 or, in a Windows environment:
4446 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
4451 The default value for this parameter is
4452 <literal>'$libdir'</literal>. If the value is set to an empty
4453 string, the automatic path search is turned off.
4457 This parameter can be changed at run time by superusers, but a
4458 setting done that way will only persist until the end of the
4459 client connection, so this method should be reserved for
4460 development purposes. The recommended way to set this parameter
4461 is in the <filename>postgresql.conf</filename> configuration
4467 <varlistentry id="guc-gin-fuzzy-search-limit" xreflabel="gin_fuzzy_search_limit">
4468 <term><varname>gin_fuzzy_search_limit</varname> (<type>integer</type>)</term>
4470 <primary><varname>gin_fuzzy_search_limit</> configuration parameter</primary>
4474 Soft upper limit of the size of the set returned by GIN index. For more
4475 information see <xref linkend="gin-tips">.
4480 <varlistentry id="guc-local-preload-libraries" xreflabel="local_preload_libraries">
4481 <term><varname>local_preload_libraries</varname> (<type>string</type>)</term>
4483 <primary><varname>local_preload_libraries</> configuration parameter</primary>
4486 <primary><filename>$libdir/plugins</></primary>
4490 This variable specifies one or more shared libraries that are
4491 to be preloaded at connection start. If more than one library
4492 is to be loaded, separate their names with commas.
4493 This parameter cannot be changed after the start of a particular
4498 Because this is not a superuser-only option, the libraries
4499 that can be loaded are restricted to those appearing in the
4500 <filename>plugins</> subdirectory of the installation's
4501 standard library directory. (It is the database administrator's
4502 responsibility to ensure that only <quote>safe</> libraries
4503 are installed there.) Entries in <varname>local_preload_libraries</>
4504 can specify this directory explicitly, for example
4505 <literal>$libdir/plugins/mylib</literal>, or just specify
4506 the library name — <literal>mylib</literal> would have
4507 the same effect as <literal>$libdir/plugins/mylib</literal>.
4511 There is no performance advantage to loading a library at session
4512 start rather than when it is first used. Rather, the intent of
4513 this feature is to allow debugging or performance-measurement
4514 libraries to be loaded into specific sessions without an explicit
4515 <command>LOAD</> command being given. For example, debugging could
4516 be enabled for all sessions under a given user name by setting
4517 this parameter with <command>ALTER USER SET</>.
4521 If a specified library is not found,
4522 the connection attempt will fail.
4526 Every PostgreSQL-supported library has a <quote>magic
4527 block</> that is checked to guarantee compatibility.
4528 For this reason, non-PostgreSQL libraries cannot be
4538 <sect1 id="runtime-config-locks">
4539 <title>Lock Management</title>
4543 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
4545 <primary>deadlock</primary>
4546 <secondary>timeout during</secondary>
4549 <primary>timeout</primary>
4550 <secondary>deadlock</secondary>
4553 <primary><varname>deadlock_timeout</> configuration parameter</primary>
4556 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
4559 This is the amount of time, in milliseconds, to wait on a lock
4560 before checking to see if there is a deadlock condition. The
4561 check for deadlock is relatively slow, so the server doesn't run
4562 it every time it waits for a lock. We optimistically assume
4563 that deadlocks are not common in production applications and
4564 just wait on the lock for a while before starting the check for a
4565 deadlock. Increasing this value reduces the amount of time
4566 wasted in needless deadlock checks, but slows down reporting of
4567 real deadlock errors. The default is one second (<literal>1s</>),
4568 which is probably about the smallest value you would want in
4569 practice. On a heavily loaded server you might want to raise it.
4570 Ideally the setting should exceed your typical transaction time,
4571 so as to improve the odds that a lock will be released before
4572 the waiter decides to check for deadlock.
4576 When <xref linkend="guc-log-lock-waits"> is set,
4577 this parameter also determines the length of time to wait before
4578 a log message is issued about the lock wait. If you are trying
4579 to investigate locking delays you might want to set a shorter than
4580 normal <varname>deadlock_timeout</varname>.
4585 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
4586 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
4588 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
4592 The shared lock table is created to track locks on
4593 <varname>max_locks_per_transaction</varname> * (<xref
4594 linkend="guc-max-connections"> + <xref
4595 linkend="guc-max-prepared-transactions">) objects (e.g., tables);
4596 hence, no more than this many distinct objects can be locked at
4597 any one time. This parameter controls the average number of object
4598 locks allocated for each transaction; individual transactions
4599 can lock more objects as long as the locks of all transactions
4600 fit in the lock table. This is <emphasis>not</> the number of
4601 rows that can be locked; that value is unlimited. The default,
4602 64, has historically proven sufficient, but you might need to
4603 raise this value if you have clients that touch many different
4604 tables in a single transaction. This parameter can only be set at
4609 Increasing this parameter might cause <productname>PostgreSQL</>
4610 to request more <systemitem class="osname">System V</> shared
4611 memory than your operating system's default configuration
4612 allows. See <xref linkend="sysvipc"> for information on how to
4613 adjust those parameters, if necessary.
4621 <sect1 id="runtime-config-compatible">
4622 <title>Version and Platform Compatibility</title>
4624 <sect2 id="runtime-config-compatible-version">
4625 <title>Previous PostgreSQL Versions</title>
4629 <varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
4630 <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
4631 <indexterm><primary>FROM</><secondary>missing</></>
4633 <primary><varname>add_missing_from</> configuration parameter</primary>
4637 When on, tables that are referenced by a query will be
4638 automatically added to the <literal>FROM</> clause if not
4639 already present. This behavior does not comply with the SQL
4640 standard and many people dislike it because it can mask mistakes
4641 (such as referencing a table where you should have referenced
4642 its alias). The default is <literal>off</>. This variable can be
4643 enabled for compatibility with releases of
4644 <productname>PostgreSQL</> prior to 8.1, where this behavior was
4649 Note that even when this variable is enabled, a warning
4650 message will be emitted for each implicit <literal>FROM</>
4651 entry referenced by a query. Users are encouraged to update
4652 their applications to not rely on this behavior, by adding all
4653 tables referenced by a query to the query's <literal>FROM</>
4654 clause (or its <literal>USING</> clause in the case of
4655 <command>DELETE</>).
4660 <varlistentry id="guc-array-nulls" xreflabel="array_nulls">
4661 <term><varname>array_nulls</varname> (<type>boolean</type>)</term>
4663 <primary><varname>array_nulls</> configuration parameter</primary>
4667 This controls whether the array input parser recognizes
4668 unquoted <literal>NULL</> as specifying a null array element.
4669 By default, this is <literal>on</>, allowing array values containing
4670 null values to be entered. However, <productname>PostgreSQL</> versions
4671 before 8.2 did not support null values in arrays, and therefore would
4672 treat <literal>NULL</> as specifying a normal array element with
4673 the string value <quote>NULL</>. For backwards compatibility with
4674 applications that require the old behavior, this variable can be
4675 turned <literal>off</>.
4679 Note that it is possible to create array values containing null values
4680 even when this variable is <literal>off</>.
4685 <varlistentry id="guc-backslash-quote" xreflabel="backslash_quote">
4686 <term><varname>backslash_quote</varname> (<type>enum</type>)</term>
4687 <indexterm><primary>strings</><secondary>backslash quotes</></>
4689 <primary><varname>backslash_quote</> configuration parameter</primary>
4693 This controls whether a quote mark can be represented by
4694 <literal>\'</> in a string literal. The preferred, SQL-standard way
4695 to represent a quote mark is by doubling it (<literal>''</>) but
4696 <productname>PostgreSQL</> has historically also accepted
4697 <literal>\'</>. However, use of <literal>\'</> creates security risks
4698 because in some client character set encodings, there are multibyte
4699 characters in which the last byte is numerically equivalent to ASCII
4700 <literal>\</>. If client-side code does escaping incorrectly then a
4701 SQL-injection attack is possible. This risk can be prevented by
4702 making the server reject queries in which a quote mark appears to be
4703 escaped by a backslash.
4704 The allowed values of <varname>backslash_quote</> are
4705 <literal>on</> (allow <literal>\'</> always),
4706 <literal>off</> (reject always), and
4707 <literal>safe_encoding</> (allow only if client encoding does not
4708 allow ASCII <literal>\</> within a multibyte character).
4709 <literal>safe_encoding</> is the default setting.
4713 Note that in a standard-conforming string literal, <literal>\</> just
4714 means <literal>\</> anyway. This parameter affects the handling of
4715 non-standard-conforming literals, including
4716 escape string syntax (<literal>E'...'</>).
4721 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
4722 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
4724 <primary><varname>default_with_oids</> configuration parameter</primary>
4728 This controls whether <command>CREATE TABLE</command> and
4729 <command>CREATE TABLE AS</command> include an OID column in
4730 newly-created tables, if neither <literal>WITH OIDS</literal>
4731 nor <literal>WITHOUT OIDS</literal> is specified. It also
4732 determines whether OIDs will be included in tables created by
4733 <command>SELECT INTO</command>. In <productname>PostgreSQL</>
4734 8.1 <varname>default_with_oids</> is <literal>off</> by default; in
4735 prior versions of <productname>PostgreSQL</productname>, it
4740 The use of OIDs in user tables is considered deprecated, so
4741 most installations should leave this variable disabled.
4742 Applications that require OIDs for a particular table should
4743 specify <literal>WITH OIDS</literal> when creating the
4744 table. This variable can be enabled for compatibility with old
4745 applications that do not follow this behavior.
4750 <varlistentry id="guc-escape-string-warning" xreflabel="escape_string_warning">
4751 <term><varname>escape_string_warning</varname> (<type>boolean</type>)</term>
4752 <indexterm><primary>strings</><secondary>escape warning</></>
4754 <primary><varname>escape_string_warning</> configuration parameter</primary>
4758 When on, a warning is issued if a backslash (<literal>\</>)
4759 appears in an ordinary string literal (<literal>'...'</>
4760 syntax) and <varname>standard_conforming_strings</varname> is off.
4761 The default is <literal>on</>.
4764 Applications that wish to use backslash as escape should be
4765 modified to use escape string syntax (<literal>E'...'</>),
4766 because the default behavior of ordinary strings will change
4767 in a future release for SQL compatibility. This variable can
4768 be enabled to help detect applications that will break.
4773 <varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
4774 <term><varname>regex_flavor</varname> (<type>enum</type>)</term>
4775 <indexterm><primary>regular expressions</></>
4777 <primary><varname>regex_flavor</> configuration parameter</primary>
4781 The regular expression <quote>flavor</> can be set to
4782 <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
4783 The default is <literal>advanced</>. The <literal>extended</>
4784 setting might be useful for exact backwards compatibility with
4785 pre-7.4 releases of <productname>PostgreSQL</>. See
4786 <xref linkend="posix-syntax-details"> for details.
4791 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
4792 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
4794 <primary><varname>sql_inheritance</> configuration parameter</primary>
4796 <indexterm><primary>inheritance</></>
4799 This controls the inheritance semantics. If turned <literal>off</>,
4800 subtables are not included by various commands by default; basically
4801 an implied <literal>ONLY</literal> key word. This was added for
4802 compatibility with releases prior to 7.1. See
4803 <xref linkend="ddl-inherit"> for more information.
4808 <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
4809 <term><varname>standard_conforming_strings</varname> (<type>boolean</type>)</term>
4810 <indexterm><primary>strings</><secondary>standard conforming</></>
4812 <primary><varname>standard_conforming_strings</> configuration parameter</primary>
4816 This controls whether ordinary string literals
4817 (<literal>'...'</>) treat backslashes literally, as specified in
4819 The default is currently <literal>off</>, causing
4820 <productname>PostgreSQL</productname> to have its historical
4821 behavior of treating backslashes as escape characters.
4822 The default will change to <literal>on</> in a future release
4823 to improve compatibility with the standard.
4824 Applications can check this
4825 parameter to determine how string literals will be processed.
4826 The presence of this parameter can also be taken as an indication
4827 that the escape string syntax (<literal>E'...'</>) is supported.
4828 Escape string syntax should be used if an application desires
4829 backslashes to be treated as escape characters.
4834 <varlistentry id="guc-synchronize-seqscans" xreflabel="synchronize_seqscans">
4835 <term><varname>synchronize_seqscans</varname> (<type>boolean</type>)</term>
4837 <primary><varname>synchronize_seqscans</> configuration parameter</primary>
4841 This allows sequential scans of large tables to synchronize with each
4842 other, so that concurrent scans read the same block at about the
4843 same time and hence share the I/O workload. When this is enabled,
4844 a scan might start in the middle of the table and then <quote>wrap
4845 around</> the end to cover all rows, so as to synchronize with the
4846 activity of scans already in progress. This can result in
4847 unpredictable changes in the row ordering returned by queries that
4848 have no <literal>ORDER BY</> clause. Setting this parameter to
4849 <literal>off</> ensures the pre-8.3 behavior in which a sequential
4850 scan always starts from the beginning of the table. The default
4859 <sect2 id="runtime-config-compatible-clients">
4860 <title>Platform and Client Compatibility</title>
4863 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
4864 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
4865 <indexterm><primary>IS NULL</></>
4867 <primary><varname>transform_null_equals</> configuration parameter</primary>
4871 When on, expressions of the form <literal><replaceable>expr</> =
4872 NULL</literal> (or <literal>NULL =
4873 <replaceable>expr</></literal>) are treated as
4874 <literal><replaceable>expr</> IS NULL</literal>, that is, they
4875 return true if <replaceable>expr</> evaluates to the null value,
4876 and false otherwise. The correct SQL-spec-compliant behavior of
4877 <literal><replaceable>expr</> = NULL</literal> is to always
4878 return null (unknown). Therefore this parameter defaults to
4883 However, filtered forms in <productname>Microsoft
4884 Access</productname> generate queries that appear to use
4885 <literal><replaceable>expr</> = NULL</literal> to test for
4886 null values, so if you use that interface to access the database you
4887 might want to turn this option on. Since expressions of the
4888 form <literal><replaceable>expr</> = NULL</literal> always
4889 return the null value (using the correct interpretation) they are not
4890 very useful and do not appear often in normal applications, so
4891 this option does little harm in practice. But new users are
4892 frequently confused about the semantics of expressions
4893 involving null values, so this option is not on by default.
4897 Note that this option only affects the exact form <literal>= NULL</>,
4898 not other comparison operators or other expressions
4899 that are computationally equivalent to some expression
4900 involving the equals operator (such as <literal>IN</literal>).
4901 Thus, this option is not a general fix for bad programming.
4905 Refer to <xref linkend="functions-comparison"> for related information.
4914 <sect1 id="runtime-config-preset">
4915 <title>Preset Options</title>
4918 The following <quote>parameters</> are read-only, and are determined
4919 when <productname>PostgreSQL</productname> is compiled or when it is
4920 installed. As such, they have been excluded from the sample
4921 <filename>postgresql.conf</> file. These options report
4922 various aspects of <productname>PostgreSQL</productname> behavior
4923 that might be of interest to certain applications, particularly
4924 administrative front-ends.
4929 <varlistentry id="guc-block-size" xreflabel="block_size">
4930 <term><varname>block_size</varname> (<type>integer</type>)</term>
4932 <primary><varname>block_size</> configuration parameter</primary>
4936 Reports the size of a disk block. It is determined by the value
4937 of <literal>BLCKSZ</> when building the server. The default
4938 value is 8192 bytes. The meaning of some configuration
4939 variables (such as <xref linkend="guc-shared-buffers">) is
4940 influenced by <varname>block_size</varname>. See <xref
4941 linkend="runtime-config-resource"> for information.
4946 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
4947 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
4949 <primary><varname>integer_datetimes</> configuration parameter</primary>
4953 Reports whether <productname>PostgreSQL</> was built with
4954 support for 64-bit-integer dates and times. This can be
4955 disabled by configuring with <literal>--disable-integer-datetimes</>
4956 when building <productname>PostgreSQL</>. The default value is
4957 <literal>on</literal>.
4962 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
4963 <term><varname>lc_collate</varname> (<type>string</type>)</term>
4965 <primary><varname>lc_collate</> configuration parameter</primary>
4969 Reports the locale in which sorting of textual data is done.
4970 See <xref linkend="locale"> for more information.
4971 This value is determined when a database is created.
4976 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
4977 <term><varname>lc_ctype</varname> (<type>string</type>)</term>
4979 <primary><varname>lc_ctype</> configuration parameter</primary>
4983 Reports the locale that determines character classifications.
4984 See <xref linkend="locale"> for more information.
4985 This value is determined when a database is created.
4986 Ordinarily this will be the same as <varname>lc_collate</varname>,
4987 but for special applications it might be set differently.
4992 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
4993 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
4995 <primary><varname>max_function_args</> configuration parameter</primary>
4999 Reports the maximum number of function arguments. It is determined by
5000 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
5001 default value is 100 arguments.
5006 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
5007 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
5009 <primary><varname>max_identifier_length</> configuration parameter</primary>
5013 Reports the maximum identifier length. It is determined as one
5014 less than the value of <literal>NAMEDATALEN</> when building
5015 the server. The default value of <literal>NAMEDATALEN</> is
5016 64; therefore the default
5017 <varname>max_identifier_length</varname> is 63 bytes.
5022 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
5023 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
5025 <primary><varname>max_index_keys</> configuration parameter</primary>
5029 Reports the maximum number of index keys. It is determined by
5030 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
5031 default value is 32 keys.
5036 <varlistentry id="guc-segment-size" xreflabel="segment_size">
5037 <term><varname>segment_size</varname> (<type>integer</type>)</term>
5039 <primary><varname>segment_size</> configuration parameter</primary>
5043 Reports the number of blocks (pages) that can be stored within a file
5044 segment. It is determined by the value of <literal>RELSEG_SIZE</>
5045 when building the server. The maximum size of a segment file in bytes
5046 is equal to <varname>segment_size</> multiplied by
5047 <varname>block_size</>; by default this is 1GB.
5052 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
5053 <term><varname>server_encoding</varname> (<type>string</type>)</term>
5055 <primary><varname>server_encoding</> configuration parameter</primary>
5057 <indexterm><primary>character set</></>
5060 Reports the database encoding (character set).
5061 It is determined when the database is created. Ordinarily,
5062 clients need only be concerned with the value of <xref
5063 linkend="guc-client-encoding">.
5068 <varlistentry id="guc-server-version" xreflabel="server_version">
5069 <term><varname>server_version</varname> (<type>string</type>)</term>
5071 <primary><varname>server_version</> configuration parameter</primary>
5075 Reports the version number of the server. It is determined by the
5076 value of <literal>PG_VERSION</> when building the server.
5081 <varlistentry id="guc-server-version-num" xreflabel="server_version_num">
5082 <term><varname>server_version_num</varname> (<type>integer</type>)</term>
5084 <primary><varname>server_version_num</> configuration parameter</primary>
5088 Reports the version number of the server as an integer. It is determined
5089 by the value of <literal>PG_VERSION_NUM</> when building the server.
5094 <varlistentry id="guc-wal-block-size" xreflabel="wal_block_size">
5095 <term><varname>wal_block_size</varname> (<type>integer</type>)</term>
5097 <primary><varname>wal_block_size</> configuration parameter</primary>
5101 Reports the size of a WAL disk block. It is determined by the value
5102 of <literal>XLOG_BLCKSZ</> when building the server. The default value
5108 <varlistentry id="guc-wal-segment-size" xreflabel="wal_segment_size">
5109 <term><varname>wal_segment_size</varname> (<type>integer</type>)</term>
5111 <primary><varname>wal_segment_size</> configuration parameter</primary>
5115 Reports the number of blocks (pages) in a WAL segment file.
5116 The total size of a WAL segment file in bytes is equal to
5117 <varname>wal_segment_size</> multiplied by <varname>wal_block_size</>;
5118 by default this is 16MB. See <xref linkend="wal-configuration"> for
5127 <sect1 id="runtime-config-custom">
5128 <title>Customized Options</title>
5131 This feature was designed to allow parameters not normally known to
5132 <productname>PostgreSQL</productname> to be added by add-on modules
5133 (such as procedural languages). This allows add-on modules to be
5134 configured in the standard ways.
5139 <varlistentry id="guc-custom-variable-classes" xreflabel="custom_variable_classes">
5140 <term><varname>custom_variable_classes</varname> (<type>string</type>)</term>
5142 <primary><varname>custom_variable_classes</> configuration parameter</primary>
5146 This variable specifies one or several class names to be used for
5147 custom variables, in the form of a comma-separated list. A custom
5148 variable is a variable not normally known
5149 to <productname>PostgreSQL</productname> proper but used by some
5150 add-on module. Such variables must have names consisting of a class
5151 name, a dot, and a variable name. <varname>custom_variable_classes</>
5152 specifies all the class names in use in a particular installation.
5153 This parameter can only be set in the <filename>postgresql.conf</>
5154 file or on the server command line.
5162 The difficulty with setting custom variables in
5163 <filename>postgresql.conf</> is that the file must be read before add-on
5164 modules have been loaded, and so custom variables would ordinarily be
5165 rejected as unknown. When <varname>custom_variable_classes</> is set,
5166 the server will accept definitions of arbitrary variables within each
5167 specified class. These variables will be treated as placeholders and
5168 will have no function until the module that defines them is loaded. When a
5169 module for a specific class is loaded, it will add the proper variable
5170 definitions for its class name, convert any placeholder
5171 values according to those definitions, and issue warnings for any
5172 placeholders of its class that remain (which presumably would be
5173 misspelled configuration variables).
5177 Here is an example of what <filename>postgresql.conf</> might contain
5178 when using custom variables:
5181 custom_variable_classes = 'plr,plperl'
5182 plr.path = '/usr/lib/R'
5183 plperl.use_strict = true
5184 plruby.use_strict = true # generates error: unknown class name
5189 <sect1 id="runtime-config-developer">
5190 <title>Developer Options</title>
5193 The following parameters are intended for work on the
5194 <productname>PostgreSQL</productname> source, and in some cases
5195 to assist with recovery of severely damaged databases. There
5196 should be no reason to use them in a production database setup.
5197 As such, they have been excluded from the sample
5198 <filename>postgresql.conf</> file. Note that many of these
5199 parameters require special source compilation flags to work at all.
5203 <varlistentry id="guc-allow-system-table-mods" xreflabel="allow_system_table_mods">
5204 <term><varname>allow_system_table_mods</varname> (<type>boolean</type>)</term>
5206 <primary><varname>allow_system_table_mods</varname> configuration parameter</primary>
5210 Allows modification of the structure of system tables.
5211 This is used by <command>initdb</command>.
5212 This parameter can only be set at server start.
5217 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
5218 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
5220 <primary><varname>debug_assertions</> configuration parameter</primary>
5224 Turns on various assertion checks. This is a debugging aid. If
5225 you are experiencing strange problems or crashes you might want
5226 to turn this on, as it might expose programming mistakes. To use
5227 this parameter, the macro <symbol>USE_ASSERT_CHECKING</symbol>
5228 must be defined when <productname>PostgreSQL</productname> is
5229 built (accomplished by the <command>configure</command> option
5230 <option>--enable-cassert</option>). Note that
5231 <varname>debug_assertions</varname> defaults to <literal>on</>
5232 if <productname>PostgreSQL</productname> has been built with
5238 <varlistentry id="guc-ignore-system-indexes" xreflabel="ignore_system_indexes">
5239 <term><varname>ignore_system_indexes</varname> (<type>boolean</type>)</term>
5241 <primary><varname>ignore_system_indexes</varname> configuration parameter</primary>
5245 Ignore system indexes when reading system tables (but still
5246 update the indexes when modifying the tables). This is useful
5247 when recovering from damaged system indexes.
5248 This parameter cannot be changed after session start.
5253 <varlistentry id="guc-post-auth-delay" xreflabel="post_auth_delay">
5254 <term><varname>post_auth_delay</varname> (<type>integer</type>)</term>
5256 <primary><varname>post_auth_delay</> configuration parameter</primary>
5260 If nonzero, a delay of this many seconds occurs when a new
5261 server process is started, after it conducts the
5262 authentication procedure. This is intended to give an
5263 opportunity to attach to the server process with a debugger.
5264 This parameter cannot be changed after session start.
5269 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
5270 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
5272 <primary><varname>pre_auth_delay</> configuration parameter</primary>
5276 If nonzero, a delay of this many seconds occurs just after a
5277 new server process is forked, before it conducts the
5278 authentication procedure. This is intended to give an
5279 opportunity to attach to the server process with a debugger to
5280 trace down misbehavior in authentication.
5281 This parameter can only be set in the <filename>postgresql.conf</>
5282 file or on the server command line.
5287 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
5288 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
5290 <primary><varname>trace_notify</> configuration parameter</primary>
5294 Generates a great amount of debugging output for the
5295 <command>LISTEN</command> and <command>NOTIFY</command>
5296 commands. <xref linkend="guc-client-min-messages"> or
5297 <xref linkend="guc-log-min-messages"> must be
5298 <literal>DEBUG1</literal> or lower to send this output to the
5299 client or server log, respectively.
5304 <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
5305 <term><varname>trace_sort</varname> (<type>boolean</type>)</term>
5307 <primary><varname>trace_sort</> configuration parameter</primary>
5311 If on, emit information about resource usage during sort operations.
5312 This parameter is only available if the <symbol>TRACE_SORT</symbol> macro
5313 was defined when <productname>PostgreSQL</productname> was compiled.
5314 (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
5320 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
5322 <primary><varname>trace_locks</> configuration parameter</primary>
5326 If on, emit information about lock usage. Information dumped
5327 includes the type of lock operation, the type of lock and the unique
5328 identifier of the object being locked or unlocked. Also included
5329 are bitmasks for the lock types already granted on this object as
5330 well as for the lock types awaited on this object. For each lock
5331 type a count of the number of granted locks and waiting locks is
5332 also dumped as well as the totals. An example of the log file output
5336 LOG: LockAcquire: new: lock(0xb7acd844) id(24688,24696,0,0,0,1)
5337 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
5338 wait(0) type(AccessShareLock)
5341 LOG: GrantLock: lock(0xb7acd844) id(24688,24696,0,0,0,1)
5342 grantMask(2) req(1,0,0,0,0,0,0)=1 grant(1,0,0,0,0,0,0)=1
5343 wait(0) type(AccessShareLock)
5347 LOG: UnGrantLock: updated: lock(0xb7acd844) id(24688,24696,0,0,0,1)
5348 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
5349 wait(0) type(AccessShareLock)
5352 LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
5353 grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
5354 wait(0) type(INVALID)
5357 Details of the structure being dumped may be found in
5358 src/include/storage/lock.h
5361 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5362 macro was defined when <productname>PostgreSQL</productname> was
5369 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
5371 <primary><varname>trace_lwlocks</> configuration parameter</primary>
5375 If on, emit information about lightweight lock usage. Lightweight
5376 locks are intended primarily to provide mutual exclusion of access
5377 to shared-memory data structures.
5380 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5381 macro was defined when <productname>PostgreSQL</productname> was
5388 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
5390 <primary><varname>trace_userlocks</> configuration parameter</primary>
5394 If on, emit information about user lock usage. Output is the same
5395 as for <symbol>trace_locks</symbol>, only for user locks.
5398 User locks were removed as of PostgreSQL version 8.2. This option
5399 currently has no effect.
5402 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5403 macro was defined when <productname>PostgreSQL</productname> was
5410 <term><varname>trace_lock_oidmin</varname> (<type>integer</type>)</term>
5412 <primary><varname>trace_lock_oidmin</> configuration parameter</primary>
5416 If set, do not trace locks for tables below this OID. (use to avoid
5417 output on system tables)
5420 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5421 macro was defined when <productname>PostgreSQL</productname> was
5428 <term><varname>trace_lock_table</varname> (<type>integer</type>)</term>
5430 <primary><varname>trace_lock_table</> configuration parameter</primary>
5434 Unconditionally trace locks on this table (OID).
5437 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5438 macro was defined when <productname>PostgreSQL</productname> was
5445 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
5447 <primary><varname>debug_deadlocks</> configuration parameter</primary>
5451 If set, dumps information about all current locks when a
5452 DeadLockTimeout occurs.
5455 This parameter is only available if the <symbol>LOCK_DEBUG</symbol>
5456 macro was defined when <productname>PostgreSQL</productname> was
5463 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
5465 <primary><varname>log_btree_build_stats</> configuration parameter</primary>
5469 If set, logs system resource usage statistics (memory and CPU) on
5470 various btree operations.
5473 This parameter is only available if the <symbol>BTREE_BUILD_STATS</symbol>
5474 macro was defined when <productname>PostgreSQL</productname> was
5480 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
5481 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
5483 <primary><varname>wal_debug</> configuration parameter</primary>
5487 If on, emit WAL-related debugging output. This parameter is
5488 only available if the <symbol>WAL_DEBUG</symbol> macro was
5489 defined when <productname>PostgreSQL</productname> was
5495 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
5496 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
5498 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
5502 Detection of a damaged page header normally causes
5503 <productname>PostgreSQL</> to report an error, aborting the current
5504 command. Setting <varname>zero_damaged_pages</> to on causes
5505 the system to instead report a warning, zero out the damaged page,
5506 and continue processing. This behavior <emphasis>will destroy data</>,
5507 namely all the rows on the damaged page. But it allows you to get
5508 past the error and retrieve rows from any undamaged pages that might
5509 be present in the table. So it is useful for recovering data if
5510 corruption has occurred due to hardware or software error. You should
5511 generally not set this on until you have given up hope of recovering
5512 data from the damaged page(s) of a table. The
5513 default setting is <literal>off</>, and it can only be changed
5520 <sect1 id="runtime-config-short">
5521 <title>Short Options</title>
5524 For convenience there are also single letter command-line option
5525 switches available for some parameters. They are described in
5526 <xref linkend="runtime-config-short-table">. Some of these
5527 options exist for historical reasons, and their presence as a
5528 single-letter option does not necessarily indicate an endorsement
5529 to use the option heavily.
5532 <table id="runtime-config-short-table">
5533 <title>Short option key</title>
5537 <entry>Short option</entry>
5538 <entry>Equivalent</entry>
5544 <entry><option>-A <replaceable>x</replaceable></option></entry>
5545 <entry><literal>debug_assertions = <replaceable>x</replaceable></></entry>
5548 <entry><option>-B <replaceable>x</replaceable></option></entry>
5549 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
5552 <entry><option>-d <replaceable>x</replaceable></option></entry>
5553 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
5556 <entry><option>-e</option></entry>
5557 <entry><literal>datestyle = euro</></entry>
5561 <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
5562 <option>-fm</option>, <option>-fn</option>,
5563 <option>-fs</option>, <option>-ft</option>
5566 <literal>enable_bitmapscan = off</>,
5567 <literal>enable_hashjoin = off</>,
5568 <literal>enable_indexscan = off</>,
5569 <literal>enable_mergejoin = off</>,
5570 <literal>enable_nestloop = off</>,
5571 <literal>enable_seqscan = off</>,
5572 <literal>enable_tidscan = off</>
5576 <entry><option>-F</option></entry>
5577 <entry><literal>fsync = off</></entry>
5580 <entry><option>-h <replaceable>x</replaceable></option></entry>
5581 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
5584 <entry><option>-i</option></entry>
5585 <entry><literal>listen_addresses = '*'</></entry>
5588 <entry><option>-k <replaceable>x</replaceable></option></entry>
5589 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
5592 <entry><option>-l</option></entry>
5593 <entry><literal>ssl = on</></entry>
5596 <entry><option>-N <replaceable>x</replaceable></option></entry>
5597 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
5600 <entry><option>-O</option></entry>
5601 <entry><literal>allow_system_table_mods = on</></entry>
5604 <entry><option>-p <replaceable>x</replaceable></option></entry>
5605 <entry><literal>port = <replaceable>x</replaceable></></entry>
5608 <entry><option>-P</option></entry>
5609 <entry><literal>ignore_system_indexes = on</></entry>
5612 <entry><option>-s</option></entry>
5613 <entry><literal>log_statement_stats = on</></entry>
5616 <entry><option>-S <replaceable>x</replaceable></option></entry>
5617 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
5620 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option></entry>
5621 <entry><literal>log_parser_stats = on</>,
5622 <literal>log_planner_stats = on</>,
5623 <literal>log_executor_stats = on</></entry>
5626 <entry><option>-W <replaceable>x</replaceable></option></entry>
5627 <entry><literal>post_auth_delay = <replaceable>x</replaceable></></entry>