2 $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.51 2006/03/07 02:54:23 momjian Exp $
4 <chapter Id="runtime-config">
5 <title>Server Configuration</title>
8 <primary>configuration</primary>
9 <secondary>of the server</secondary>
13 There are many configuration parameters that affect the behavior of
14 the database system. In the first section of this chapter, we
15 describe how to set configuration parameters. The subsequent sections
16 discuss each parameter in detail.
19 <sect1 id="config-setting">
20 <title>Setting Parameters</title>
23 All parameter names are case-insensitive. Every parameter takes a
24 value of one of four types: Boolean, integer, floating point,
25 or string. Boolean values may be written as <literal>ON</literal>,
26 <literal>OFF</literal>, <literal>TRUE</literal>,
27 <literal>FALSE</literal>, <literal>YES</literal>,
28 <literal>NO</literal>, <literal>1</literal>, <literal>0</literal>
29 (all case-insensitive) or any unambiguous prefix of these.
33 One way to set these parameters is to edit the file
34 <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>,
35 which is normally kept in the data directory. (<application>initdb</>
36 installs a default copy there.) An example of what this file might look
41 log_destination = 'syslog'
42 search_path = '"$user", public'
44 One parameter is specified per line. The equal sign between name and
45 value is optional. Whitespace is insignificant and blank lines are
46 ignored. Hash marks (<literal>#</literal>) introduce comments
47 anywhere. Parameter values that are not simple identifiers or
48 numbers must be single-quoted. To embed a single quote in a parameter
49 value, write either two quotes (preferred) or backslash-quote.
54 <primary><literal>include</></primary>
55 <secondary>in configuration file</secondary>
57 In addition to parameter settings, the <filename>postgresql.conf</>
58 file can contain <firstterm>include directives</>, which specify
59 another file to read and process as if it were inserted into the
60 configuration file at this point. Include directives simply look like
64 If the filename is not an absolute path, it is taken as relative to
65 the directory containing the referencing configuration file.
66 Inclusions can be nested.
71 <primary>SIGHUP</primary>
73 The configuration file is reread whenever the
74 <command>postmaster</command> process receives a
75 <systemitem>SIGHUP</> signal (which is most easily sent by means
76 of <literal>pg_ctl reload</>). The <command>postmaster</command>
77 also propagates this signal to all currently running server
78 processes so that existing sessions also get the new
79 value. Alternatively, you can send the signal to a single server
80 process directly. Some parameters can only be set at server start;
81 any changes to their entries in the configuration file will be ignored
82 until the server is restarted.
86 A second way to set these configuration parameters is to give them
87 as a command line option to the <command>postmaster</command>, such as:
89 postmaster -c log_connections=yes -c log_destination='syslog'
91 Command-line options override any conflicting settings in
92 <filename>postgresql.conf</filename>. Note that this means you won't
93 be able to change the value on-the-fly by editing
94 <filename>postgresql.conf</filename>, so while the command-line
95 method may be convenient, it can cost you flexibility later.
99 Occasionally it is useful to give a command line option to
100 one particular session only. The environment variable
101 <envar>PGOPTIONS</envar> can be used for this purpose on the
104 env PGOPTIONS='-c geqo=off' psql
106 (This works for any <application>libpq</>-based client application, not
107 just <application>psql</application>.) Note that this won't work for
108 parameters that are fixed when the server is started or that must be
109 specified in <filename>postgresql.conf</filename>.
113 Furthermore, it is possible to assign a set of parameter settings to
114 a user or a database. Whenever a session is started, the default
115 settings for the user and database involved are loaded. The
116 commands <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
117 and <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">,
118 respectively, are used to configure these settings. Per-database
119 settings override anything received from the
120 <command>postmaster</command> command-line or the configuration
121 file, and in turn are overridden by per-user settings; both are
122 overridden by per-session settings.
126 Some parameters can be changed in individual <acronym>SQL</acronym>
127 sessions with the <xref linkend="SQL-SET" endterm="SQL-SET-title">
128 command, for example:
130 SET ENABLE_SEQSCAN TO OFF;
132 If <command>SET</> is allowed, it overrides all other sources of
133 values for the parameter. Some parameters cannot be changed via
134 <command>SET</command>: for example, if they control behavior that
135 cannot be changed without restarting the entire
136 <productname>PostgreSQL</productname> server. Also, some parameters can
137 be modified via <command>SET</command> or <command>ALTER</> by superusers,
138 but not by ordinary users.
142 The <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title">
143 command allows inspection of the current values of all parameters.
147 The virtual table <structname>pg_settings</structname>
148 (described in <xref linkend="view-pg-settings">) also allows
149 displaying and updating session run-time parameters. It is equivalent
150 to <command>SHOW</> and <command>SET</>, but can be more convenient
151 to use because it can be joined with other tables, or selected from using
152 any desired selection condition.
156 <sect1 id="runtime-config-file-locations">
157 <title>File Locations</title>
160 In addition to the <filename>postgresql.conf</filename> file
161 already mentioned, <productname>PostgreSQL</productname> uses
162 two other manually-edited configuration files, which control
163 client authentication (their use is discussed in <xref
164 linkend="client-authentication">). By default, all three
165 configuration files are stored in the database cluster's data
166 directory. The parameters described in this section allow the
167 configuration files to be placed elsewhere. (Doing so can ease
168 administration. In particular it is often easier to ensure that
169 the configuration files are properly backed-up when they are
174 <varlistentry id="guc-data-directory" xreflabel="data_directory">
175 <term><varname>data_directory</varname> (<type>string</type>)</term>
177 <primary><varname>data_directory</> configuration parameter</primary>
181 Specifies the directory to use for data storage.
182 This parameter can only be set at server start.
187 <varlistentry id="guc-config-file" xreflabel="config_file">
188 <term><varname>config_file</varname> (<type>string</type>)</term>
190 <primary><varname>config_file</> configuration parameter</primary>
194 Specifies the main server configuration file
195 (customarily called <filename>postgresql.conf</>).
196 This parameter can only be set on the postmaster command line.
201 <varlistentry id="guc-hba-file" xreflabel="hba_file">
202 <term><varname>hba_file</varname> (<type>string</type>)</term>
204 <primary><varname>hba_file</> configuration parameter</primary>
208 Specifies the configuration file for host-based authentication
209 (customarily called <filename>pg_hba.conf</>).
210 This parameter can only be set at server start.
215 <varlistentry id="guc-ident-file" xreflabel="ident_file">
216 <term><varname>ident_file</varname> (<type>string</type>)</term>
218 <primary><varname>ident_file</> configuration parameter</primary>
222 Specifies the configuration file for
223 <application>ident</> authentication
224 (customarily called <filename>pg_ident.conf</>).
225 This parameter can only be set at server start.
230 <varlistentry id="guc-external-pid-file" xreflabel="external_pid_file">
231 <term><varname>external_pid_file</varname> (<type>string</type>)</term>
233 <primary><varname>external_pid_file</> configuration parameter</primary>
237 Specifies the name of an additional process-id (PID) file that the
238 <application>postmaster</> should create for use by server
239 administration programs.
240 This parameter can only be set at server start.
247 In a default installation, none of the above parameters are set
248 explicitly. Instead, the
249 data directory is specified by the <option>-D</option> command-line
250 option or the <envar>PGDATA</envar> environment variable, and the
251 configuration files are all found within the data directory.
255 If you wish to keep the configuration files elsewhere than the
256 data directory, the postmaster's <option>-D</option>
257 command-line option or <envar>PGDATA</envar> environment variable
258 must point to the directory containing the configuration files,
259 and the <varname>data_directory</> parameter must be set in
260 <filename>postgresql.conf</filename> (or on the command line) to show
261 where the data directory is actually located. Notice that
262 <varname>data_directory</> overrides <option>-D</option> and
263 <envar>PGDATA</envar> for the location
264 of the data directory, but not for the location of the configuration
269 If you wish, you can specify the configuration file names and locations
270 individually using the parameters <varname>config_file</>,
271 <varname>hba_file</> and/or <varname>ident_file</>.
272 <varname>config_file</> can only be specified on the
273 <command>postmaster</command> command line, but the others can be
274 set within the main configuration file. If all three parameters plus
275 <varname>data_directory</> are explicitly set, then it is not necessary
276 to specify <option>-D</option> or <envar>PGDATA</envar>.
280 When setting any of these parameters, a relative path will be interpreted
281 with respect to the directory in which the <command>postmaster</command>
286 <sect1 id="runtime-config-connection">
287 <title>Connections and Authentication</title>
289 <sect2 id="runtime-config-connection-settings">
290 <title>Connection Settings</title>
294 <varlistentry id="guc-listen-addresses" xreflabel="listen_addresses">
295 <term><varname>listen_addresses</varname> (<type>string</type>)</term>
297 <primary><varname>listen_addresses</> configuration parameter</primary>
301 Specifies the TCP/IP address(es) on which the server is
302 to listen for connections from client applications.
303 The value takes the form of a comma-separated list of host names
304 and/or numeric IP addresses. The special entry <literal>*</>
305 corresponds to all available IP interfaces.
306 If the list is empty, the server does not listen on any IP interface
307 at all, in which case only Unix-domain sockets can be used to connect
309 The default value is <systemitem class="systemname">localhost</>,
310 which allows only local <quote>loopback</> connections to be made.
311 This parameter can only be set at server start.
316 <varlistentry id="guc-port" xreflabel="port">
317 <term><varname>port</varname> (<type>integer</type>)</term>
319 <primary><varname>port</> configuration parameter</primary>
323 The TCP port the server listens on; 5432 by default. Note that the
324 same port number is used for all IP addresses the server listens on.
325 This parameter can only be set at server start.
330 <varlistentry id="guc-max-connections" xreflabel="max_connections">
331 <term><varname>max_connections</varname> (<type>integer</type>)</term>
333 <primary><varname>max_connections</> configuration parameter</primary>
337 Determines the maximum number of concurrent connections to the
338 database server. The default is typically 100, but may be less
339 if your kernel settings will not support it (as determined
340 during <application>initdb</>). This parameter can only be
345 Increasing this parameter may cause <productname>PostgreSQL</>
346 to request more <systemitem class="osname">System V</> shared
347 memory or semaphores than your operating system's default configuration
348 allows. See <xref linkend="sysvipc"> for information on how to
349 adjust those parameters, if necessary.
354 <varlistentry id="guc-superuser-reserved-connections"
355 xreflabel="superuser_reserved_connections">
356 <term><varname>superuser_reserved_connections</varname>
357 (<type>integer</type>)</term>
359 <primary><varname>superuser_reserved_connections</> configuration parameter</primary>
363 Determines the number of connection <quote>slots</quote> that
364 are reserved for connections by <productname>PostgreSQL</>
365 superusers. At most <xref linkend="guc-max-connections">
366 connections can ever be active simultaneously. Whenever the
367 number of active concurrent connections is at least
368 <varname>max_connections</> minus
369 <varname>superuser_reserved_connections</varname>, new
370 connections will be accepted only for superusers.
374 The default value is 2. The value must be less than the value of
375 <varname>max_connections</varname>. This parameter can only be
381 <varlistentry id="guc-unix-socket-directory" xreflabel="unix_socket_directory">
382 <term><varname>unix_socket_directory</varname> (<type>string</type>)</term>
384 <primary><varname>unix_socket_directory</> configuration parameter</primary>
388 Specifies the directory of the Unix-domain socket on which the
389 server is to listen for
390 connections from client applications. The default is normally
391 <filename>/tmp</filename>, but can be changed at build time.
392 This parameter can only be set at server start.
397 <varlistentry id="guc-unix-socket-group" xreflabel="unix_socket_group">
398 <term><varname>unix_socket_group</varname> (<type>string</type>)</term>
400 <primary><varname>unix_socket_group</> configuration parameter</primary>
404 Sets the owning group of the Unix-domain socket. (The owning
405 user of the socket is always the user that starts the
406 server.) In combination with the parameter
407 <varname>unix_socket_permissions</varname> this can be used as
408 an additional access control mechanism for Unix-domain connections.
409 By default this is the empty string, which selects the default
410 group for the current user. This parameter can only be set at
416 <varlistentry id="guc-unix-socket-permissions" xreflabel="unix_socket_permissions">
417 <term><varname>unix_socket_permissions</varname> (<type>integer</type>)</term>
419 <primary><varname>unix_socket_permissions</> configuration parameter</primary>
423 Sets the access permissions of the Unix-domain socket. Unix-domain
424 sockets use the usual Unix file system permission set.
425 The parameter value is expected to be a numeric mode
426 specification in the form accepted by the
427 <function>chmod</function> and <function>umask</function>
428 system calls. (To use the customary octal format the number
429 must start with a <literal>0</literal> (zero).)
433 The default permissions are <literal>0777</literal>, meaning
434 anyone can connect. Reasonable alternatives are
435 <literal>0770</literal> (only user and group, see also
436 <varname>unix_socket_group</varname>) and <literal>0700</literal>
437 (only user). (Note that for a Unix-domain socket, only write
438 permission matters and so there is no point in setting or revoking
439 read or execute permissions.)
443 This access control mechanism is independent of the one
444 described in <xref linkend="client-authentication">.
448 This parameter can only be set at server start.
453 <varlistentry id="guc-bonjour-name" xreflabel="bonjour_name">
454 <term><varname>bonjour_name</varname> (<type>string</type>)</term>
456 <primary><varname>bonjour_name</> configuration parameter</primary>
460 Specifies the <productname>Bonjour</productname> broadcast
461 name. The computer name is used if this parameter is set to the
462 empty string <literal>''</> (which is the default). This parameter is
463 ignored if the server was not compiled with
464 <productname>Bonjour</productname> support.
465 This parameter can only be set at server start.
470 <varlistentry id="guc-tcp-keepalives-idle" xreflabel="tcp_keepalives_idle">
471 <term><varname>tcp_keepalives_idle</varname> (<type>integer</type>)</term>
473 <primary><varname>tcp_keepalives_idle</> configuration parameter</primary>
477 On systems that support the <symbol>TCP_KEEPIDLE</symbol> socket option, specifies the
478 number of seconds between sending keepalives on an otherwise idle
479 connection. A value of 0 uses the system default. If <symbol>TCP_KEEPIDLE</symbol> is
480 not supported, this parameter must be 0. This parameter is ignored for
481 connections made via a Unix-domain socket.
486 <varlistentry id="guc-tcp-keepalives-interval" xreflabel="tcp_keepalives_interval">
487 <term><varname>tcp_keepalives_interval</varname> (<type>integer</type>)</term>
489 <primary><varname>tcp_keepalives_interval</> configuration parameter</primary>
493 On systems that support the <symbol>TCP_KEEPINTVL</symbol> socket option, specifies how
494 long, in seconds, to wait for a response to a keepalive before
495 retransmitting. A value of 0 uses the system default. If <symbol>TCP_KEEPINTVL</symbol>
496 is not supported, this parameter must be 0. This parameter is ignored
497 for connections made via a Unix-domain socket.
502 <varlistentry id="guc-tcp-keepalives-count" xreflabel="tcp_keepalives_count">
503 <term><varname>tcp_keepalives_count</varname> (<type>integer</type>)</term>
505 <primary><varname>tcp_keepalives_count</> configuration parameter</primary>
509 On systems that support the <symbol>TCP_KEEPCNT</symbol> socket option, specifies how
510 many keepalives may be lost before the connection is considered dead.
511 A value of 0 uses the system default. If <symbol>TCP_KEEPCNT</symbol> is not
512 supported, this parameter must be 0. This parameter is ignored
513 for connections made via a Unix-domain socket.
520 <sect2 id="runtime-config-connection-security">
521 <title>Security and Authentication</title>
524 <varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
525 <term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
526 <indexterm><primary>timeout</><secondary>client authentication</></indexterm>
527 <indexterm><primary>client authentication</><secondary>timeout during</></indexterm>
529 <primary><varname>authentication_timeout</> configuration parameter</primary>
534 Maximum time to complete client authentication, in seconds. If a
535 would-be client has not completed the authentication protocol in
536 this much time, the server breaks the connection. This prevents
537 hung clients from occupying a connection indefinitely.
539 This parameter can only be set in the <filename>postgresql.conf</>
540 file or on the server command line.
545 <varlistentry id="guc-ssl" xreflabel="ssl">
546 <term><varname>ssl</varname> (<type>boolean</type>)</term>
548 <primary><varname>ssl</> configuration parameter</primary>
552 Enables <acronym>SSL</> connections. Please read
553 <xref linkend="ssl-tcp"> before using this. The default
554 is <literal>off</>. This parameter can only be set at server
560 <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
561 <term><varname>password_encryption</varname> (<type>boolean</type>)</term>
563 <primary><varname>password_encryption</> configuration parameter</primary>
567 When a password is specified in <xref
568 linkend="sql-createuser" endterm="sql-createuser-title"> or
569 <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
570 without writing either <literal>ENCRYPTED</> or
571 <literal>UNENCRYPTED</>, this parameter determines whether the
572 password is to be encrypted. The default is <literal>on</>
573 (encrypt the password).
578 <varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
579 <term><varname>krb_server_keyfile</varname> (<type>string</type>)</term>
581 <primary><varname>krb_server_keyfile</> configuration parameter</primary>
585 Sets the location of the Kerberos server key file. See
586 <xref linkend="kerberos-auth"> for details. This parameter
587 can only be set at server start.
592 <varlistentry id="guc-krb-srvname" xreflabel="krb_srvname">
593 <term><varname>krb_srvname</varname> (<type>string</type>)</term>
595 <primary><varname>krb_srvname</> configuration parameter</primary>
599 Sets the Kerberos service name. See <xref linkend="kerberos-auth">
600 for details. This parameter can only be set at server start.
605 <varlistentry id="guc-krb-server-hostname" xreflabel="krb_server_hostname">
606 <term><varname>krb_server_hostname</varname> (<type>string</type>)</term>
608 <primary><varname>krb_server_hostname</> configuration parameter</primary>
612 Sets the host name part of the service principal.
613 This, combined with <varname>krb_srvname</>, is used to generate
614 the complete service principal, that is
615 <varname>krb_srvname</><literal>/</><varname>krb_server_hostname</><literal>@</>REALM.
616 If not set, the default is the server host name. See <xref linkend="kerberos-auth">
617 for details. This parameter can only be set at server start.
622 <varlistentry id="guc-krb-caseins-users" xreflabel="krb_caseins_users">
623 <term><varname>krb_caseins_users</varname> (<type>boolean</type>)</term>
625 <primary><varname>krb_caseins_users</varname> configuration parameter</primary>
629 Sets whether Kerberos user names should be treated case-insensitively.
630 The default is <literal>off</> (case sensitive). This parameter
631 can only be set at server start.
636 <varlistentry id="guc-db-user-namespace" xreflabel="db_user_namespace">
637 <term><varname>db_user_namespace</varname> (<type>boolean</type>)</term>
639 <primary><varname>db_user_namespace</> configuration parameter</primary>
643 This parameter enables per-database user names. It is off by default.
644 This parameter can only be set in the <filename>postgresql.conf</>
645 file or on the server command line.
649 If this is on, you should create users as <literal>username@dbname</>.
650 When <literal>username</> is passed by a connecting client,
651 <literal>@</> and the database name are appended to the user
652 name and that database-specific user name is looked up by the
653 server. Note that when you create users with names containing
654 <literal>@</> within the SQL environment, you will need to
659 With this parameter enabled, you can still create ordinary global
660 users. Simply append <literal>@</> when specifying the user
661 name in the client. The <literal>@</> will be stripped off
662 before the user name is looked up by the server.
667 This feature is intended as a temporary measure until a
668 complete solution is found. At that time, this option will
679 <sect1 id="runtime-config-resource">
680 <title>Resource Consumption</title>
682 <sect2 id="runtime-config-resource-memory">
683 <title>Memory</title>
686 <varlistentry id="guc-shared-buffers" xreflabel="shared_buffers">
687 <term><varname>shared_buffers</varname> (<type>integer</type>)</term>
689 <primary><varname>shared_buffers</> configuration parameter</primary>
693 Sets the number of shared memory buffers used by the database
694 server. The default is typically 4000, but may be less if your
695 kernel settings will not support it (as determined during
696 <application>initdb</>). Each buffer is 8192 bytes, unless a
697 different value of <symbol>BLCKSZ</symbol> was chosen when building
698 the server. This setting must be at least 16, as well as at
699 least twice the value of <xref linkend="guc-max-connections">;
700 however, settings significantly higher than the minimum are
701 usually needed for good performance. Values of a few thousand
702 are recommended for production installations. This parameter can
703 only be set at server start.
707 Increasing this parameter may cause <productname>PostgreSQL</>
708 to request more <systemitem class="osname">System V</> shared
709 memory than your operating system's default configuration
710 allows. See <xref linkend="sysvipc"> for information on how to
711 adjust those parameters, if necessary.
716 <varlistentry id="guc-temp-buffers" xreflabel="temp_buffers">
717 <term><varname>temp_buffers</varname> (<type>integer</type>)</term>
719 <primary><varname>temp_buffers</> configuration parameter</primary>
723 Sets the maximum number of temporary buffers used by each database
724 session. These are session-local buffers used only for access
725 to temporary tables. The default is 1000. The setting can
726 be changed within individual sessions, but only up until the
727 first use of temporary tables within a session; subsequent
728 attempts to change the value will have no effect on that session.
732 A session will allocate temporary buffers as needed up to the limit
733 given by <varname>temp_buffers</>. The cost of setting a large
734 value in sessions that do not actually need a lot of temporary
735 buffers is only a buffer descriptor, or about 64 bytes, per
736 increment in <varname>temp_buffers</>. However if a buffer is
737 actually used an additional 8192 bytes will be consumed for it
738 (or in general, <symbol>BLCKSZ</symbol> bytes).
743 <varlistentry id="guc-max-prepared-transactions" xreflabel="max_prepared_transactions">
744 <term><varname>max_prepared_transactions</varname> (<type>integer</type>)</term>
746 <primary><varname>max_prepared_transactions</> configuration parameter</primary>
750 Sets the maximum number of transactions that can be in the
751 <quote>prepared</> state simultaneously (see <xref
752 linkend="sql-prepare-transaction"
753 endterm="sql-prepare-transaction-title">).
754 Setting this parameter to zero disables the prepared-transaction
757 This parameter can only be set at server start.
761 If you are not using prepared transactions, this parameter may as
762 well be set to zero. If you are using them, you will probably
763 want <varname>max_prepared_transactions</varname> to be at least
764 as large as <xref linkend="guc-max-connections">, to avoid unwanted
765 failures at the prepare step.
769 Increasing this parameter may cause <productname>PostgreSQL</>
770 to request more <systemitem class="osname">System V</> shared
771 memory than your operating system's default configuration
772 allows. See <xref linkend="sysvipc"> for information on how to
773 adjust those parameters, if necessary.
778 <varlistentry id="guc-work-mem" xreflabel="work_mem">
779 <term><varname>work_mem</varname> (<type>integer</type>)</term>
781 <primary><varname>work_mem</> configuration parameter</primary>
785 Specifies the amount of memory to be used by internal sort operations
786 and hash tables before switching to temporary disk files. The value is
787 specified in kilobytes, and defaults to 1024 kilobytes (1 MB).
788 Note that for a complex query, several sort or hash operations might be
789 running in parallel; each one will be allowed to use as much memory
790 as this value specifies before it starts to put data into temporary
791 files. Also, several running sessions could be doing such operations
792 concurrently. So the total memory used could be many
793 times the value of <varname>work_mem</varname>; it is necessary to
794 keep this fact in mind when choosing the value. Sort operations are
795 used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
797 Hash tables are used in hash joins, hash-based aggregation, and
798 hash-based processing of <literal>IN</> subqueries.
803 <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
804 <term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
806 <primary><varname>maintenance_work_mem</> configuration parameter</primary>
810 Specifies the maximum amount of memory to be used in maintenance
811 operations, such as <command>VACUUM</command>, <command>CREATE
812 INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>.
813 The value is specified in kilobytes, and defaults to 16384 kilobytes
814 (16 MB). Since only one of these operations can be executed at
815 a time by a database session, and an installation normally doesn't
816 have very many of them happening concurrently, it's safe to set this
817 value significantly larger than <varname>work_mem</varname>. Larger
818 settings may improve performance for vacuuming and for restoring
824 <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
825 <term><varname>max_stack_depth</varname> (<type>integer</type>)</term>
827 <primary><varname>max_stack_depth</> configuration parameter</primary>
831 Specifies the maximum safe depth of the server's execution stack.
832 The ideal setting for this parameter is the actual stack size limit
833 enforced by the kernel (as set by <literal>ulimit -s</> or local
834 equivalent), less a safety margin of a megabyte or so. The safety
835 margin is needed because the stack depth is not checked in every
836 routine in the server, but only in key potentially-recursive routines
837 such as expression evaluation. Setting the parameter higher than
838 the actual kernel limit will mean that a runaway recursive function
839 can crash an individual backend process. The default setting is
840 2048 KB (two megabytes), which is conservatively small and unlikely
841 to risk crashes. However, it may be too small to allow execution
842 of complex functions.
843 Only superusers can change this setting.
850 <sect2 id="runtime-config-resource-fsm">
851 <title>Free Space Map</title>
854 <primary>free space map</primary>
858 These parameters control the size of the shared <firstterm>free space
859 map</>, which tracks the locations of unused space in the database.
860 An undersized free space map may cause the database to consume
861 increasing amounts of disk space over time, because free space that
862 is not in the map cannot be re-used; instead <productname>PostgreSQL</>
863 will request more disk space from the operating system when it needs
865 The last few lines displayed by a database-wide <command>VACUUM VERBOSE</>
866 command can help in determining if the current settings are adequate.
867 A <literal>NOTICE</> message is also printed during such an operation
868 if the current settings are too low.
872 Increasing these parameters may cause <productname>PostgreSQL</>
873 to request more <systemitem class="osname">System V</> shared
874 memory than your operating system's default configuration
875 allows. See <xref linkend="sysvipc"> for information on how to
876 adjust those parameters, if necessary.
880 <varlistentry id="guc-max-fsm-pages" xreflabel="max_fsm_pages">
881 <term><varname>max_fsm_pages</varname> (<type>integer</type>)</term>
883 <primary><varname>max_fsm_pages</> configuration parameter</primary>
887 Sets the maximum number of disk pages for which free space will
888 be tracked in the shared free-space map. Six bytes of shared memory
889 are consumed for each page slot. This setting must be more than
890 16 * <varname>max_fsm_relations</varname>. The default is chosen
891 by <application>initdb</> depending on the amount of available memory,
892 and can range from 20000 to 200000.
893 This parameter can only be set at server start.
898 <varlistentry id="guc-max-fsm-relations" xreflabel="max_fsm_relations">
899 <term><varname>max_fsm_relations</varname> (<type>integer</type>)</term>
901 <primary><varname>max_fsm_relations</> configuration parameter</primary>
905 Sets the maximum number of relations (tables and indexes) for which
906 free space will be tracked in the shared free-space map. Roughly
907 seventy bytes of shared memory are consumed for each slot.
909 This parameter can only be set at server start.
916 <sect2 id="runtime-config-resource-kernel">
917 <title>Kernel Resource Usage</title>
920 <varlistentry id="guc-max-files-per-process" xreflabel="max_files_per_process">
921 <term><varname>max_files_per_process</varname> (<type>integer</type>)</term>
923 <primary><varname>max_files_per_process</> configuration parameter</primary>
927 Sets the maximum number of simultaneously open files allowed to each
928 server subprocess. The default is 1000. If the kernel is enforcing
929 a safe per-process limit, you don't need to worry about this setting.
930 But on some platforms (notably, most BSD systems), the kernel will
931 allow individual processes to open many more files than the system
932 can really support when a large number of processes all try to open
933 that many files. If you find yourself seeing <quote>Too many open
934 files</> failures, try reducing this setting.
935 This parameter can only be set at server start.
940 <varlistentry id="guc-preload-libraries" xreflabel="preload_libraries">
941 <term><varname>preload_libraries</varname> (<type>string</type>)</term>
943 <primary><varname>preload_libraries</> configuration parameter</primary>
947 This variable specifies one or more shared libraries that are
948 to be preloaded at server start. A parameterless
949 initialization function can optionally be called for each
950 library. To specify that, add a colon and the name of the
951 initialization function after the library name. For example
952 <literal>'$libdir/mylib:mylib_init'</literal> would cause
953 <literal>mylib</> to be preloaded and <literal>mylib_init</>
954 to be executed. If more than one library is to be loaded,
955 separate their names with commas.
959 If a specified library or initialization function is not found,
960 the server will fail to start.
964 <productname>PostgreSQL</productname> procedural language
965 libraries may be preloaded in this way, typically by using the
966 syntax <literal>'$libdir/plXXX:plXXX_init'</literal> where
967 <literal>XXX</literal> is <literal>pgsql</>, <literal>perl</>,
968 <literal>tcl</>, or <literal>python</>.
972 By preloading a shared library (and initializing it if
973 applicable), the library startup time is avoided when the
974 library is first used. However, the time to start each new
975 server process may increase slightly, even if that process never
976 uses the library. So this parameter is recommended only for
977 libraries that will be used in most sessions.
985 <sect2 id="runtime-config-resource-vacuum-cost">
986 <title id="runtime-config-resource-vacuum-cost-title">
987 Cost-Based Vacuum Delay
991 During the execution of <xref linkend="sql-vacuum"
992 endterm="sql-vacuum-title"> and <xref linkend="sql-analyze"
993 endterm="sql-analyze-title"> commands, the system maintains an
994 internal counter that keeps track of the estimated cost of the
995 various I/O operations that are performed. When the accumulated
996 cost reaches a limit (specified by
997 <varname>vacuum_cost_limit</varname>), the process performing
998 the operation will sleep for a while (specified by
999 <varname>vacuum_cost_delay</varname>). Then it will reset the
1000 counter and continue execution.
1004 The intent of this feature is to allow administrators to reduce
1005 the I/O impact of these commands on concurrent database
1006 activity. There are many situations in which it is not very
1007 important that maintenance commands like
1008 <command>VACUUM</command> and <command>ANALYZE</command> finish
1009 quickly; however, it is usually very important that these
1010 commands do not significantly interfere with the ability of the
1011 system to perform other database operations. Cost-based vacuum
1012 delay provides a way for administrators to achieve this.
1016 This feature is disabled by default. To enable it, set the
1017 <varname>vacuum_cost_delay</varname> variable to a nonzero
1022 <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
1023 <term><varname>vacuum_cost_delay</varname> (<type>integer</type>)</term>
1025 <primary><varname>vacuum_cost_delay</> configuration parameter</primary>
1029 The length of time, in milliseconds, that the process will sleep
1030 when the cost limit has been exceeded.
1031 The default value is 0, which disables the cost-based vacuum
1032 delay feature. Positive values enable cost-based vacuuming.
1033 Note that on many systems, the effective resolution
1034 of sleep delays is 10 milliseconds; setting
1035 <varname>vacuum_cost_delay</varname> to a value that is
1036 not a multiple of 10 may have the same results as setting it
1037 to the next higher multiple of 10.
1042 <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
1043 <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>
1045 <primary><varname>vacuum_cost_page_hit</> configuration parameter</primary>
1049 The estimated cost for vacuuming a buffer found in the shared buffer
1050 cache. It represents the cost to lock the buffer pool, lookup
1051 the shared hash table and scan the content of the page. The
1057 <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
1058 <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>
1060 <primary><varname>vacuum_cost_page_miss</> configuration parameter</primary>
1064 The estimated cost for vacuuming a buffer that has to be read from
1065 disk. This represents the effort to lock the buffer pool,
1066 lookup the shared hash table, read the desired block in from
1067 the disk and scan its content. The default value is 10.
1072 <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
1073 <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>
1075 <primary><varname>vacuum_cost_page_dirty</> configuration parameter</primary>
1079 The estimated cost charged when vacuum modifies a block that was
1080 previously clean. It represents the extra I/O required to
1081 flush the dirty block out to disk again. The default value is
1087 <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
1088 <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>
1090 <primary><varname>vacuum_cost_limit</> configuration parameter</primary>
1094 The accumulated cost that will cause the vacuuming process to sleep.
1095 The default value is 200.
1103 There are certain operations that hold critical locks and should
1104 therefore complete as quickly as possible. Cost-based vacuum
1105 delays do not occur during such operations. Therefore it is
1106 possible that the cost accumulates far higher than the specified
1107 limit. To avoid uselessly long delays in such cases, the actual
1108 delay is calculated as <varname>vacuum_cost_delay</varname> *
1109 <varname>accumulated_balance</varname> /
1110 <varname>vacuum_cost_limit</varname> with a maximum of
1111 <varname>vacuum_cost_delay</varname> * 4.
1116 <sect2 id="runtime-config-resource-background-writer">
1117 <title>Background Writer</title>
1120 Beginning in <productname>PostgreSQL</> 8.0, there is a separate server
1121 process called the <firstterm>background writer</>, whose sole function
1122 is to issue writes of <quote>dirty</> shared buffers. The intent is
1123 that server processes handling user queries should seldom or never have
1124 to wait for a write to occur, because the background writer will do it.
1125 This arrangement also reduces the performance penalty associated with
1126 checkpoints. The background writer will continuously trickle out dirty
1127 pages to disk, so that only a few pages will need to be forced out when
1128 checkpoint time arrives, instead of the storm of dirty-buffer writes that
1129 formerly occurred at each checkpoint. However there is a net overall
1130 increase in I/O load, because where a repeatedly-dirtied page might
1131 before have been written only once per checkpoint interval, the
1132 background writer might write it several times in the same interval.
1133 In most situations a continuous low load is preferable to periodic
1134 spikes, but the parameters discussed in this subsection can be used to tune
1135 the behavior for local needs.
1139 <varlistentry id="guc-bgwriter-delay" xreflabel="bgwriter_delay">
1140 <term><varname>bgwriter_delay</varname> (<type>integer</type>)</term>
1142 <primary><varname>bgwriter_delay</> configuration parameter</primary>
1146 Specifies the delay between activity rounds for the
1147 background writer. In each round the writer issues writes
1148 for some number of dirty buffers (controllable by the
1149 following parameters). It then sleeps for <varname>bgwriter_delay</>
1150 milliseconds, and repeats. The default value is 200. Note
1151 that on many systems, the effective resolution of sleep
1152 delays is 10 milliseconds; setting <varname>bgwriter_delay</>
1153 to a value that is not a multiple of 10 may have the same
1154 results as setting it to the next higher multiple of 10.
1155 This parameter can only be set in the <filename>postgresql.conf</>
1156 file or on the server command line.
1161 <varlistentry id="guc-bgwriter-lru-percent" xreflabel="bgwriter_lru_percent">
1162 <term><varname>bgwriter_lru_percent</varname> (<type>floating point</type>)</term>
1164 <primary><varname>bgwriter_lru_percent</> configuration parameter</primary>
1168 To reduce the probability that server processes will need to issue
1169 their own writes, the background writer tries to write buffers that
1170 are likely to be recycled soon. In each round, it examines up to
1171 <varname>bgwriter_lru_percent</> of the buffers that are nearest to
1172 being recycled, and writes any that are dirty.
1173 The default value is 1.0 (this is a percentage of the total number
1175 This parameter can only be set in the <filename>postgresql.conf</>
1176 file or on the server command line.
1181 <varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1182 <term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)</term>
1184 <primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1188 In each round, no more than this many buffers will be written
1189 as a result of scanning soon-to-be-recycled buffers.
1190 The default value is 5.
1191 This parameter can only be set in the <filename>postgresql.conf</>
1192 file or on the server command line.
1197 <varlistentry id="guc-bgwriter-all-percent" xreflabel="bgwriter_all_percent">
1198 <term><varname>bgwriter_all_percent</varname> (<type>floating point</type>)</term>
1200 <primary><varname>bgwriter_all_percent</> configuration parameter</primary>
1204 To reduce the amount of work that will be needed at checkpoint time,
1205 the background writer also does a circular scan through the entire
1206 buffer pool, writing buffers that are found to be dirty.
1207 In each round, it examines up to
1208 <varname>bgwriter_all_percent</> of the buffers for this purpose.
1209 The default value is 0.333 (this is a percentage of the total number
1210 of shared buffers). With the default <varname>bgwriter_delay</>
1211 setting, this will allow the entire shared buffer pool to be scanned
1212 about once per minute.
1213 This parameter can only be set in the <filename>postgresql.conf</>
1214 file or on the server command line.
1219 <varlistentry id="guc-bgwriter-all-maxpages" xreflabel="bgwriter_all_maxpages">
1220 <term><varname>bgwriter_all_maxpages</varname> (<type>integer</type>)</term>
1222 <primary><varname>bgwriter_all_maxpages</> configuration parameter</primary>
1226 In each round, no more than this many buffers will be written
1227 as a result of the scan of the entire buffer pool. (If this
1228 limit is reached, the scan stops, and resumes at the next buffer
1229 during the next round.)
1230 The default value is 5.
1231 This parameter can only be set in the <filename>postgresql.conf</>
1232 file or on the server command line.
1239 Smaller values of <varname>bgwriter_all_percent</varname> and
1240 <varname>bgwriter_all_maxpages</varname> reduce the extra I/O load
1241 caused by the background writer, but leave more work to be done
1242 at checkpoint time. To reduce load spikes at checkpoints,
1243 increase these two values.
1244 Similarly, smaller values of <varname>bgwriter_lru_percent</varname> and
1245 <varname>bgwriter_lru_maxpages</varname> reduce the extra I/O load
1246 caused by the background writer, but make it more likely that server
1247 processes will have to issue writes for themselves, delaying interactive
1249 To disable background writing entirely,
1250 set both <varname>maxpages</varname> values and/or both
1251 <varname>percent</varname> values to zero.
1256 <sect1 id="runtime-config-wal">
1257 <title>Write Ahead Log</title>
1260 See also <xref linkend="wal-configuration"> for details on WAL
1264 <sect2 id="runtime-config-wal-settings">
1265 <title>Settings</title>
1268 <varlistentry id="guc-fsync" xreflabel="fsync">
1270 <primary><varname>fsync</> configuration parameter</primary>
1272 <term><varname>fsync</varname> (<type>boolean</type>)</term>
1275 If this parameter is on, the <productname>PostgreSQL</> server
1276 will try to make sure that updates are physically written to
1277 disk, by issuing <function>fsync()</> system calls or various
1278 equivalent methods (see <xref linkend="guc-wal-sync-method">).
1279 This ensures that the database cluster can recover to a
1280 consistent state after an operating system or hardware crash.
1284 However, using <varname>fsync</varname> results in a
1285 performance penalty: when a transaction is committed,
1286 <productname>PostgreSQL</productname> must wait for the
1287 operating system to flush the write-ahead log to disk. When
1288 <varname>fsync</varname> is disabled, the operating system is
1289 allowed to do its best in buffering, ordering, and delaying
1290 writes. This can result in significantly improved performance.
1291 However, if the system crashes, the results of the last few
1292 committed transactions may be lost in part or whole. In the
1293 worst case, unrecoverable data corruption may occur.
1294 (Crashes of the database software itself are <emphasis>not</>
1295 a risk factor here. Only an operating-system-level crash
1296 creates a risk of corruption.)
1300 Due to the risks involved, there is no universally correct
1301 setting for <varname>fsync</varname>. Some administrators
1302 always disable <varname>fsync</varname>, while others only
1303 turn it off during initial bulk data loads, where there is a clear
1304 restart point if something goes wrong. Others
1305 always leave <varname>fsync</varname> enabled. The default is
1306 to enable <varname>fsync</varname>, for maximum reliability.
1307 If you trust your operating system, your hardware, and your
1308 utility company (or your battery backup), you can consider
1309 disabling <varname>fsync</varname>.
1313 This parameter can only be set in the <filename>postgresql.conf</>
1314 file or on the server command line.
1315 If you turn this parameter off, also consider turning off
1316 <xref linkend="guc-full-page-writes">.
1321 <varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
1322 <term><varname>wal_sync_method</varname> (<type>string</type>)</term>
1324 <primary><varname>wal_sync_method</> configuration parameter</primary>
1328 Method used for forcing WAL updates out to disk.
1329 If <varname>fsync</varname> is off then this setting is irrelevant,
1330 since updates will not be forced out at all.
1331 Possible values are:
1336 <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>)
1341 <literal>fdatasync</> (call <function>fdatasync()</> at each commit)
1346 <literal>fsync_writethrough</> (call <function>fsync()</> at each commit, forcing write-through of any disk write cache)
1351 <literal>fsync</> (call <function>fsync()</> at each commit)
1356 <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>)
1361 Not all of these choices are available on all platforms.
1362 The default is the first method in the above list that is supported
1364 This parameter can only be set in the <filename>postgresql.conf</>
1365 file or on the server command line.
1370 <varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
1372 <primary><varname>full_page_writes</> configuration parameter</primary>
1374 <term><varname>full_page_writes</varname> (<type>boolean</type>)</term>
1377 When this parameter is on, the <productname>PostgreSQL</> server
1378 writes the entire content of each disk page to WAL during the
1379 first modification of that page after a checkpoint.
1380 This is needed because
1381 a page write that is in process during an operating system crash might
1382 be only partially completed, leading to an on-disk page
1383 that contains a mix of old and new data. The row-level change data
1384 normally stored in WAL will not be enough to completely restore
1385 such a page during post-crash recovery. Storing the full page image
1386 guarantees that the page can be correctly restored, but at a price
1387 in increasing the amount of data that must be written to WAL.
1388 (Because WAL replay always starts from a checkpoint, it is sufficient
1389 to do this during the first change of each page after a checkpoint.
1390 Therefore, one way to reduce the cost of full-page writes is to
1391 increase the checkpoint interval parameters.)
1395 Turning this parameter off speeds normal operation, but
1396 might lead to a corrupt database after an operating system crash
1397 or power failure. The risks are similar to turning off
1398 <varname>fsync</>, though smaller. It may be safe to turn off
1399 this parameter if you have hardware (such as a battery-backed disk
1400 controller) or filesystem software (e.g., Reiser4) that reduces
1401 the risk of partial page writes to an acceptably low level.
1405 Turning off this parameter does not affect use of
1406 WAL archiving for point-in-time recovery (PITR)
1407 (see <xref linkend="continuous-archiving">).
1411 This parameter can only be set in the <filename>postgresql.conf</>
1412 file or on the server command line.
1413 The default is <literal>on</>.
1418 <varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
1419 <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
1421 <primary><varname>wal_buffers</> configuration parameter</primary>
1425 Number of disk-page buffers allocated in shared memory for WAL data.
1426 The default is 8. The setting need only be large enough to hold
1427 the amount of WAL data generated by one typical transaction, since
1428 the data is written out to disk at every transaction commit.
1429 This parameter can only be set at server start.
1433 Increasing this parameter may cause <productname>PostgreSQL</>
1434 to request more <systemitem class="osname">System V</> shared
1435 memory than your operating system's default configuration
1436 allows. See <xref linkend="sysvipc"> for information on how to
1437 adjust those parameters, if necessary.
1442 <varlistentry id="guc-commit-delay" xreflabel="commit_delay">
1443 <term><varname>commit_delay</varname> (<type>integer</type>)</term>
1445 <primary><varname>commit_delay</> configuration parameter</primary>
1449 Time delay between writing a commit record to the WAL buffer
1450 and flushing the buffer out to disk, in microseconds. A
1451 nonzero delay can allow multiple transactions to be committed
1452 with only one <function>fsync()</function> system call, if
1453 system load is high enough that additional transactions become
1454 ready to commit within the given interval. But the delay is
1455 just wasted if no other transactions become ready to
1456 commit. Therefore, the delay is only performed if at least
1457 <varname>commit_siblings</varname> other transactions are
1458 active at the instant that a server process has written its
1459 commit record. The default is zero (no delay).
1464 <varlistentry id="guc-commit-siblings" xreflabel="commit_siblings">
1465 <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
1467 <primary><varname>commit_siblings</> configuration parameter</primary>
1471 Minimum number of concurrent open transactions to require
1472 before performing the <varname>commit_delay</> delay. A larger
1473 value makes it more probable that at least one other
1474 transaction will become ready to commit during the delay
1475 interval. The default is five.
1482 <sect2 id="runtime-config-wal-checkpoints">
1483 <title>Checkpoints</title>
1486 <varlistentry id="guc-checkpoint-segments" xreflabel="checkpoint_segments">
1487 <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
1489 <primary><varname>checkpoint_segments</> configuration parameter</primary>
1493 Maximum distance between automatic WAL checkpoints, in log
1494 file segments (each segment is normally 16 megabytes). The
1496 This parameter can only be set in the <filename>postgresql.conf</>
1497 file or on the server command line.
1502 <varlistentry id="guc-checkpoint-timeout" xreflabel="checkpoint_timeout">
1503 <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
1505 <primary><varname>checkpoint_timeout</> configuration parameter</primary>
1509 Maximum time between automatic WAL checkpoints, in
1510 seconds. The default is 300 seconds.
1511 This parameter can only be set in the <filename>postgresql.conf</>
1512 file or on the server command line.
1517 <varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
1518 <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
1520 <primary><varname>checkpoint_warning</> configuration parameter</primary>
1524 Write a message to the server log if checkpoints caused by
1525 the filling of checkpoint segment files happen closer together
1526 than this many seconds (which suggests that
1527 <varname>checkpoint_segments</> ought to be raised). The default is
1528 30 seconds. Zero disables the warning.
1529 This parameter can only be set in the <filename>postgresql.conf</>
1530 file or on the server command line.
1537 <sect2 id="runtime-config-wal-archiving">
1538 <title>Archiving</title>
1541 <varlistentry id="guc-archive-command" xreflabel="archive_command">
1542 <term><varname>archive_command</varname> (<type>string</type>)</term>
1544 <primary><varname>archive_command</> configuration parameter</primary>
1548 The shell command to execute to archive a completed segment of
1549 the WAL file series. If this is an empty string (the default),
1550 WAL archiving is disabled. Any <literal>%p</> in the string is
1551 replaced by the absolute path of the file to archive, and any
1552 <literal>%f</> is replaced by the file name only. Use
1553 <literal>%%</> to embed an actual <literal>%</> character in the
1554 command. For more information see <xref
1555 linkend="backup-archiving-wal">.
1556 This parameter can only be set in the <filename>postgresql.conf</>
1557 file or on the server command line.
1560 It is important for the command to return a zero exit status if
1561 and only if it succeeds. Examples:
1563 archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
1564 archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
1574 <sect1 id="runtime-config-query">
1575 <title>Query Planning</title>
1577 <sect2 id="runtime-config-query-enable">
1578 <title>Planner Method Configuration</title>
1581 These configuration parameters provide a crude method of
1582 influencing the query plans chosen by the query optimizer. If
1583 the default plan chosen by the optimizer for a particular query
1584 is not optimal, a temporary solution may be found by using one
1585 of these configuration parameters to force the optimizer to
1586 choose a different plan. Turning one of these settings off
1587 permanently is seldom a good idea, however.
1588 Better ways to improve the quality of the
1589 plans chosen by the optimizer include adjusting the <xref
1590 linkend="runtime-config-query-constants"
1591 endterm="runtime-config-query-constants-title">, running <xref
1592 linkend="sql-analyze" endterm="sql-analyze-title"> more
1593 frequently, increasing the value of the <xref
1594 linkend="guc-default-statistics-target"> configuration parameter,
1595 and increasing the amount of statistics collected for
1596 specific columns using <command>ALTER TABLE SET
1597 STATISTICS</command>.
1601 <varlistentry id="guc-enable-bitmapscan" xreflabel="enable_bitmapscan">
1602 <term><varname>enable_bitmapscan</varname> (<type>boolean</type>)</term>
1604 <primary>bitmap scan</primary>
1607 <primary><varname>enable_bitmapscan</> configuration parameter</primary>
1611 Enables or disables the query planner's use of bitmap-scan plan
1612 types. The default is <literal>on</>.
1617 <varlistentry id="guc-enable-hashagg" xreflabel="enable_hashagg">
1618 <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
1620 <primary><varname>enable_hashagg</> configuration parameter</primary>
1624 Enables or disables the query planner's use of hashed
1625 aggregation plan types. The default is <literal>on</>.
1630 <varlistentry id="guc-enable-hashjoin" xreflabel="enable_hashjoin">
1631 <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
1633 <primary><varname>enable_hashjoin</> configuration parameter</primary>
1637 Enables or disables the query planner's use of hash-join plan
1638 types. The default is <literal>on</>.
1643 <varlistentry id="guc-enable-indexscan" xreflabel="enable_indexscan">
1644 <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
1646 <primary>index scan</primary>
1649 <primary><varname>enable_indexscan</> configuration parameter</primary>
1653 Enables or disables the query planner's use of index-scan plan
1654 types. The default is <literal>on</>.
1659 <varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
1660 <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
1662 <primary><varname>enable_mergejoin</> configuration parameter</primary>
1666 Enables or disables the query planner's use of merge-join plan
1667 types. The default is <literal>on</>.
1672 <varlistentry id="guc-enable-nestloop" xreflabel="enable_nestloop">
1673 <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
1675 <primary><varname>enable_nestloop</> configuration parameter</primary>
1679 Enables or disables the query planner's use of nested-loop join
1680 plans. It's not possible to suppress nested-loop joins entirely,
1681 but turning this variable off discourages the planner from using
1682 one if there are other methods available. The default is
1688 <varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
1689 <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
1691 <primary>sequential scan</primary>
1694 <primary><varname>enable_seqscan</> configuration parameter</primary>
1698 Enables or disables the query planner's use of sequential scan
1699 plan types. It's not possible to suppress sequential scans
1700 entirely, but turning this variable off discourages the planner
1701 from using one if there are other methods available. The
1702 default is <literal>on</>.
1707 <varlistentry id="guc-enable-sort" xreflabel="enable_sort">
1708 <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
1710 <primary><varname>enable_sort</> configuration parameter</primary>
1714 Enables or disables the query planner's use of explicit sort
1715 steps. It's not possible to suppress explicit sorts entirely,
1716 but turning this variable off discourages the planner from
1717 using one if there are other methods available. The default
1723 <varlistentry id="guc-enable-tidscan" xreflabel="enable_tidscan">
1724 <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
1726 <primary><varname>enable_tidscan</> configuration parameter</primary>
1730 Enables or disables the query planner's use of <acronym>TID</>
1731 scan plan types. The default is <literal>on</>.
1738 <sect2 id="runtime-config-query-constants">
1739 <title id="runtime-config-query-constants-title">
1740 Planner Cost Constants
1745 Unfortunately, there is no well-defined method for determining
1746 ideal values for the family of <quote>cost</quote> variables that
1747 appear below. You are encouraged to experiment and share
1754 <varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
1755 <term><varname>effective_cache_size</varname> (<type>floating point</type>)</term>
1757 <primary><varname>effective_cache_size</> configuration parameter</primary>
1761 Sets the planner's assumption about the effective size of the
1762 disk cache that is available to a single index scan. This is
1763 factored into estimates of the cost of using an index; a
1764 higher value makes it more likely index scans will be used, a
1765 lower value makes it more likely sequential scans will be
1766 used. When setting this parameter you should consider both
1767 <productname>PostgreSQL</productname>'s shared buffers and the
1768 portion of the kernel's disk cache that will be used for
1769 <productname>PostgreSQL</productname> data files. Also, take
1770 into account the expected number of concurrent queries using
1771 different indexes, since they will have to share the available
1772 space. This parameter has no effect on the size of shared
1773 memory allocated by <productname>PostgreSQL</productname>, nor
1774 does it reserve kernel disk cache; it is used only for
1775 estimation purposes. The value is measured in disk pages,
1776 which are normally 8192 bytes each. The default is 1000.
1781 <varlistentry id="guc-random-page-cost" xreflabel="random_page_cost">
1782 <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
1784 <primary><varname>random_page_cost</> configuration parameter</primary>
1788 Sets the planner's estimate of the cost of a
1789 nonsequentially fetched disk page. This is measured as a
1790 multiple of the cost of a sequential page fetch. A higher
1791 value makes it more likely a sequential scan will be used, a
1792 lower value makes it more likely an index scan will be
1793 used. The default is four.
1798 <varlistentry id="guc-cpu-tuple-cost" xreflabel="cpu_tuple_cost">
1799 <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
1801 <primary><varname>cpu_tuple_cost</> configuration parameter</primary>
1805 Sets the planner's estimate of the cost of processing
1806 each row during a query. This is measured as a fraction of
1807 the cost of a sequential page fetch. The default is 0.01.
1812 <varlistentry id="guc-cpu-index-tuple-cost" xreflabel="cpu_index_tuple_cost">
1813 <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
1815 <primary><varname>cpu_index_tuple_cost</> configuration parameter</primary>
1819 Sets the planner's estimate of the cost of processing
1820 each index row during an index scan. This is measured as a
1821 fraction of the cost of a sequential page fetch. The default
1827 <varlistentry id="guc-cpu-operator-cost" xreflabel="cpu_operator_cost">
1828 <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
1830 <primary><varname>cpu_operator_cost</> configuration parameter</primary>
1834 Sets the planner's estimate of the cost of processing each
1835 operator in a <literal>WHERE</> clause. This is measured as a fraction of
1836 the cost of a sequential page fetch. The default is 0.0025.
1844 <sect2 id="runtime-config-query-geqo">
1845 <title>Genetic Query Optimizer</title>
1849 <varlistentry id="guc-geqo" xreflabel="geqo">
1851 <primary>genetic query optimization</primary>
1854 <primary>GEQO</primary>
1855 <see>genetic query optimization</see>
1858 <primary><varname>geqo</> configuration parameter</primary>
1860 <term><varname>geqo</varname> (<type>boolean</type>)</term>
1863 Enables or disables genetic query optimization, which is an
1864 algorithm that attempts to do query planning without
1865 exhaustive searching. This is on by default. The
1866 <varname>geqo_threshold</varname> variable provides a more
1867 granular way to disable GEQO for certain classes of queries.
1872 <varlistentry id="guc-geqo-threshold" xreflabel="geqo_threshold">
1873 <term><varname>geqo_threshold</varname> (<type>integer</type>)</term>
1875 <primary><varname>geqo_threshold</> configuration parameter</primary>
1879 Use genetic query optimization to plan queries with at least
1880 this many <literal>FROM</> items involved. (Note that a
1881 <literal>FULL OUTER JOIN</> construct counts as only one <literal>FROM</>
1882 item.) The default is 12. For simpler queries it is usually best
1883 to use the deterministic, exhaustive planner, but for queries with
1884 many tables the deterministic planner takes too long.
1889 <varlistentry id="guc-geqo-effort" xreflabel="geqo_effort">
1890 <term><varname>geqo_effort</varname>
1891 (<type>integer</type>)</term>
1893 <primary><varname>geqo_effort</> configuration parameter</primary>
1897 Controls the trade off between planning time and query plan
1898 efficiency in GEQO. This variable must be an integer in the
1899 range from 1 to 10. The default value is 5. Larger values
1900 increase the time spent doing query planning, but also
1901 increase the likelihood that an efficient query plan will be
1906 <varname>geqo_effort</varname> doesn't actually do anything
1907 directly; it is only used to compute the default values for
1908 the other variables that influence GEQO behavior (described
1909 below). If you prefer, you can set the other parameters by
1915 <varlistentry id="guc-geqo-pool-size" xreflabel="geqo_pool_size">
1916 <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term>
1918 <primary><varname>geqo_pool_size</> configuration parameter</primary>
1922 Controls the pool size used by GEQO. The pool size is the
1923 number of individuals in the genetic population. It must be
1924 at least two, and useful values are typically 100 to 1000. If
1925 it is set to zero (the default setting) then a suitable
1926 default is chosen based on <varname>geqo_effort</varname> and
1927 the number of tables in the query.
1932 <varlistentry id="guc-geqo-generations" xreflabel="geqo_generations">
1933 <term><varname>geqo_generations</varname> (<type>integer</type>)</term>
1935 <primary><varname>geqo_generations</> configuration parameter</primary>
1939 Controls the number of generations used by GEQO. Generations
1940 specifies the number of iterations of the algorithm. It must
1941 be at least one, and useful values are in the same range as
1942 the pool size. If it is set to zero (the default setting)
1943 then a suitable default is chosen based on
1944 <varname>geqo_pool_size</varname>.
1949 <varlistentry id="guc-geqo-selection-bias" xreflabel="geqo_selection_bias">
1950 <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term>
1952 <primary><varname>geqo_selection_bias</> configuration parameter</primary>
1956 Controls the selection bias used by GEQO. The selection bias
1957 is the selective pressure within the population. Values can be
1958 from 1.50 to 2.00; the latter is the default.
1965 <sect2 id="runtime-config-query-other">
1966 <title>Other Planner Options</title>
1970 <varlistentry id="guc-default-statistics-target" xreflabel="default_statistics_target">
1971 <term><varname>default_statistics_target</varname> (<type>integer</type>)</term>
1973 <primary><varname>default_statistics_target</> configuration parameter</primary>
1977 Sets the default statistics target for table columns that have
1978 not had a column-specific target set via <command>ALTER TABLE
1979 SET STATISTICS</>. Larger values increase the time needed to
1980 do <command>ANALYZE</>, but may improve the quality of the
1981 planner's estimates. The default is 10. For more information
1982 on the use of statistics by the <productname>PostgreSQL</>
1983 query planner, refer to <xref linkend="planner-stats">.
1988 <varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
1989 <term><varname>constraint_exclusion</varname> (<type>boolean</type>)</term>
1991 <primary>constraint exclusion</primary>
1994 <primary><varname>constraint_exclusion</> configuration parameter</primary>
1998 Enables or disables the query planner's use of table constraints to
1999 optimize queries. The default is <literal>off</>.
2003 When this parameter is <literal>on</>, the planner compares
2004 query conditions with table <literal>CHECK</> constraints, and
2005 omits scanning tables for which the conditions contradict the
2006 constraints. For example:
2009 CREATE TABLE parent(key integer, ...);
2010 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
2011 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
2013 SELECT * FROM parent WHERE key = 2400;
2016 With constraint exclusion enabled, this <command>SELECT</>
2017 will not scan <structname>child1000</> at all. This can
2018 improve performance when inheritance is used to build
2023 Currently, <varname>constraint_exclusion</> is disabled by
2024 default because it risks incorrect results if query plans are
2025 cached — if a table constraint is changed or dropped,
2026 the previously generated plan might now be wrong, and there is
2027 no built-in mechanism to force re-planning. (This deficiency
2028 will probably be addressed in a future
2029 <productname>PostgreSQL</> release.) Another reason for
2030 keeping it off is that the constraint checks are relatively
2031 expensive, and in many circumstances will yield no savings.
2032 It is recommended to turn this on only if you are actually
2033 using partitioned tables designed to take advantage of the
2038 Refer to <xref linkend="ddl-partitioning"> for more information
2039 on using constraint exclusion and partitioning.
2044 <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
2045 <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
2047 <primary><varname>from_collapse_limit</> configuration parameter</primary>
2051 The planner will merge sub-queries into upper queries if the
2052 resulting <literal>FROM</literal> list would have no more than
2053 this many items. Smaller values reduce planning time but may
2054 yield inferior query plans. The default is 8. It is usually
2055 wise to keep this less than <xref linkend="guc-geqo-threshold">.
2056 For more information see <xref linkend="explicit-joins">.
2061 <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
2062 <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
2064 <primary><varname>join_collapse_limit</> configuration parameter</primary>
2068 The planner will rewrite explicit <literal>JOIN</>
2069 constructs (except <literal>FULL JOIN</>s) into lists of
2070 <literal>FROM</> items whenever a list of no more than this many items
2071 would result. Smaller values reduce planning time but may
2072 yield inferior query plans.
2076 By default, this variable is set the same as
2077 <varname>from_collapse_limit</varname>, which is appropriate
2078 for most uses. Setting it to 1 prevents any reordering of
2079 explicit <literal>JOIN</>s. Thus, the explicit join order
2080 specified in the query will be the actual order in which the
2081 relations are joined. The query planner does not always choose
2082 the optimal join order; advanced users may elect to
2083 temporarily set this variable to 1, and then specify the join
2084 order they desire explicitly.
2085 For more information see <xref linkend="explicit-joins">.
2094 <sect1 id="runtime-config-logging">
2095 <title>Error Reporting and Logging</title>
2097 <indexterm zone="runtime-config-logging">
2098 <primary>server log</primary>
2101 <sect2 id="runtime-config-logging-where">
2102 <title>Where To Log</title>
2104 <indexterm zone="runtime-config-logging-where">
2105 <primary>where to log</primary>
2110 <varlistentry id="guc-log-destination" xreflabel="log_destination">
2111 <term><varname>log_destination</varname> (<type>string</type>)</term>
2113 <primary><varname>log_destination</> configuration parameter</primary>
2117 <productname>PostgreSQL</productname> supports several methods
2118 for logging server messages, including
2119 <systemitem>stderr</systemitem> and
2120 <systemitem>syslog</systemitem>. On Windows,
2121 <systemitem>eventlog</systemitem> is also supported. Set this
2122 parameter to a list of desired log destinations separated by
2123 commas. The default is to log to <systemitem>stderr</systemitem>
2125 This parameter can only be set in the <filename>postgresql.conf</>
2126 file or on the server command line.
2131 <varlistentry id="guc-redirect-stderr" xreflabel="redirect_stderr">
2132 <term><varname>redirect_stderr</varname> (<type>boolean</type>)</term>
2134 <primary><varname>redirect_stderr</> configuration parameter</primary>
2138 This parameter allows messages sent to <application>stderr</> to be
2139 captured and redirected into log files.
2140 This method, in combination with logging to <application>stderr</>,
2141 is often more useful than
2142 logging to <application>syslog</>, since some types of messages
2143 may not appear in <application>syslog</> output (a common example
2144 is dynamic-linker failure messages).
2145 This parameter can only be set at server start.
2150 <varlistentry id="guc-log-directory" xreflabel="log_directory">
2151 <term><varname>log_directory</varname> (<type>string</type>)</term>
2153 <primary><varname>log_directory</> configuration parameter</primary>
2157 When <varname>redirect_stderr</> is enabled, this parameter
2158 determines the directory in which log files will be created.
2159 It may be specified as an absolute path, or relative to the
2160 cluster data directory.
2161 This parameter can only be set in the <filename>postgresql.conf</>
2162 file or on the server command line.
2167 <varlistentry id="guc-log-filename" xreflabel="log_filename">
2168 <term><varname>log_filename</varname> (<type>string</type>)</term>
2170 <primary><varname>log_filename</> configuration parameter</primary>
2174 When <varname>redirect_stderr</varname> is enabled, this parameter
2175 sets the file names of the created log files. The value
2176 is treated as a <systemitem>strftime</systemitem> pattern,
2177 so <literal>%</literal>-escapes
2178 can be used to specify time-varying file names.
2179 If no <literal>%</literal>-escapes are present,
2180 <productname>PostgreSQL</productname> will
2181 append the epoch of the new log file's open time. For example,
2182 if <varname>log_filename</varname> were <literal>server_log</literal>, then the
2183 chosen file name would be <literal>server_log.1093827753</literal>
2184 for a log starting at Sun Aug 29 19:02:33 2004 MST.
2185 This parameter can only be set in the <filename>postgresql.conf</>
2186 file or on the server command line.
2191 <varlistentry id="guc-log-rotation-age" xreflabel="log_rotation_age">
2192 <term><varname>log_rotation_age</varname> (<type>integer</type>)</term>
2194 <primary><varname>log_rotation_age</> configuration parameter</primary>
2198 When <varname>redirect_stderr</varname> is enabled, this parameter
2199 determines the maximum lifetime of an individual log file.
2200 After this many minutes have elapsed, a new log file will
2201 be created. Set to zero to disable time-based creation of
2203 This parameter can only be set in the <filename>postgresql.conf</>
2204 file or on the server command line.
2209 <varlistentry id="guc-log-rotation-size" xreflabel="log_rotation_size">
2210 <term><varname>log_rotation_size</varname> (<type>integer</type>)</term>
2212 <primary><varname>log_rotation_size</> configuration parameter</primary>
2216 When <varname>redirect_stderr</varname> is enabled, this parameter
2217 determines the maximum size of an individual log file.
2218 After this many kilobytes have been emitted into a log file,
2219 a new log file will be created. Set to zero to disable size-based
2220 creation of new log files.
2221 This parameter can only be set in the <filename>postgresql.conf</>
2222 file or on the server command line.
2227 <varlistentry id="guc-log-truncate-on-rotation" xreflabel="log_truncate_on_rotation">
2228 <term><varname>log_truncate_on_rotation</varname> (<type>boolean</type>)</term>
2230 <primary><varname>log_truncate_on_rotation</> configuration parameter</primary>
2234 When <varname>redirect_stderr</varname> is enabled, this parameter will cause
2235 <productname>PostgreSQL</productname> to truncate (overwrite),
2236 rather than append to, any existing log file of the same name.
2237 However, truncation will occur only when a new file is being opened
2238 due to time-based rotation, not during server startup or size-based
2239 rotation. When off, pre-existing files will be appended to in
2240 all cases. For example, using this setting in combination with
2241 a <varname>log_filename</varname> like <literal>postgresql-%H.log</literal>
2242 would result in generating twenty-four hourly log files and then
2243 cyclically overwriting them.
2244 This parameter can only be set in the <filename>postgresql.conf</>
2245 file or on the server command line.
2248 Example: To keep 7 days of logs, one log file per day named
2249 <literal>server_log.Mon</literal>, <literal>server_log.Tue</literal>,
2250 etc, and automatically overwrite last week's log with this week's log,
2251 set <varname>log_filename</varname> to <literal>server_log.%a</literal>,
2252 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>, and
2253 <varname>log_rotation_age</varname> to <literal>1440</literal>.
2256 Example: To keep 24 hours of logs, one log file per hour, but
2257 also rotate sooner if the log file size exceeds 1GB, set
2258 <varname>log_filename</varname> to <literal>server_log.%H%M</literal>,
2259 <varname>log_truncate_on_rotation</varname> to <literal>on</literal>,
2260 <varname>log_rotation_age</varname> to <literal>60</literal>, and
2261 <varname>log_rotation_size</varname> to <literal>1000000</literal>.
2262 Including <literal>%M</> in <varname>log_filename</varname> allows
2263 any size-driven rotations that may occur to select a file name
2264 different from the hour's initial file name.
2269 <varlistentry id="guc-syslog-facility" xreflabel="syslog_facility">
2270 <term><varname>syslog_facility</varname> (<type>string</type>)</term>
2272 <primary><varname>syslog_facility</> configuration parameter</primary>
2276 When logging to <application>syslog</> is enabled, this parameter
2277 determines the <application>syslog</application>
2278 <quote>facility</quote> to be used. You may choose
2279 from <literal>LOCAL0</>, <literal>LOCAL1</>,
2280 <literal>LOCAL2</>, <literal>LOCAL3</>, <literal>LOCAL4</>,
2281 <literal>LOCAL5</>, <literal>LOCAL6</>, <literal>LOCAL7</>;
2282 the default is <literal>LOCAL0</>. See also the
2283 documentation of your system's
2284 <application>syslog</application> daemon.
2285 This parameter can only be set in the <filename>postgresql.conf</>
2286 file or on the server command line.
2291 <varlistentry id="guc-syslog-ident" xreflabel="syslog_ident">
2292 <term><varname>syslog_ident</varname> (<type>string</type>)</term>
2294 <primary><varname>syslog_identity</> configuration parameter</primary>
2298 When logging to <application>syslog</> is enabled, this parameter
2299 determines the program name used to identify
2300 <productname>PostgreSQL</productname> messages in
2301 <application>syslog</application> logs. The default is
2302 <literal>postgres</literal>.
2303 This parameter can only be set in the <filename>postgresql.conf</>
2304 file or on the server command line.
2311 <sect2 id="runtime-config-logging-when">
2312 <title>When To Log</title>
2316 <varlistentry id="guc-client-min-messages" xreflabel="client_min_messages">
2317 <term><varname>client_min_messages</varname> (<type>string</type>)</term>
2319 <primary><varname>client_min_messages</> configuration parameter</primary>
2323 Controls which message levels are sent to the client.
2324 Valid values are <literal>DEBUG5</>,
2325 <literal>DEBUG4</>, <literal>DEBUG3</>, <literal>DEBUG2</>,
2326 <literal>DEBUG1</>, <literal>LOG</>, <literal>NOTICE</>,
2327 <literal>WARNING</>, <literal>ERROR</>, <literal>FATAL</>,
2328 and <literal>PANIC</>. Each level
2329 includes all the levels that follow it. The later the level,
2330 the fewer messages are sent. The default is
2331 <literal>NOTICE</>. Note that <literal>LOG</> has a different
2332 rank here than in <varname>log_min_messages</>.
2337 <varlistentry id="guc-log-min-messages" xreflabel="log_min_messages">
2338 <term><varname>log_min_messages</varname> (<type>string</type>)</term>
2340 <primary><varname>log_min_messages</> configuration parameter</primary>
2344 Controls which message levels are written to the server log.
2345 Valid values are <literal>DEBUG5</>, <literal>DEBUG4</>,
2346 <literal>DEBUG3</>, <literal>DEBUG2</>, <literal>DEBUG1</>,
2347 <literal>INFO</>, <literal>NOTICE</>, <literal>WARNING</>,
2348 <literal>ERROR</>, <literal>LOG</>, <literal>FATAL</>, and
2349 <literal>PANIC</>. Each level includes all the levels that
2350 follow it. The later the level, the fewer messages are sent
2351 to the log. The default is <literal>NOTICE</>. Note that
2352 <literal>LOG</> has a different rank here than in
2353 <varname>client_min_messages</>.
2354 Only superusers can change this setting.
2359 <varlistentry id="guc-log-error-verbosity" xreflabel="log_error_verbosity">
2360 <term><varname>log_error_verbosity</varname> (<type>string</type>)</term>
2362 <primary><varname>log_error_verbosity</> configuration parameter</primary>
2366 Controls the amount of detail written in the server log for each
2367 message that is logged. Valid values are <literal>TERSE</>,
2368 <literal>DEFAULT</>, and <literal>VERBOSE</>, each adding more
2369 fields to displayed messages.
2370 Only superusers can change this setting.
2375 <varlistentry id="guc-log-min-error-statement" xreflabel="log_min_error_statement">
2376 <term><varname>log_min_error_statement</varname> (<type>string</type>)</term>
2378 <primary><varname>log_min_error_statement</> configuration parameter</primary>
2382 Controls whether or not the SQL statement that causes an error
2383 condition will also be recorded in the server log. All SQL
2384 statements that cause an error of the specified level or
2385 higher are logged. The default is
2386 <literal>PANIC</literal> (effectively turning this feature
2387 off for normal use). Valid values are <literal>DEBUG5</literal>,
2388 <literal>DEBUG4</literal>, <literal>DEBUG3</literal>,
2389 <literal>DEBUG2</literal>, <literal>DEBUG1</literal>,
2390 <literal>INFO</literal>, <literal>NOTICE</literal>,
2391 <literal>WARNING</literal>, <literal>ERROR</literal>,
2392 <literal>FATAL</literal>, and <literal>PANIC</literal>. For
2393 example, if you set this to <literal>ERROR</literal> then all
2394 SQL statements causing errors, fatal errors, or panics will be
2395 logged. Enabling this parameter can be helpful in tracking down
2396 the source of any errors that appear in the server log.
2397 Only superusers can change this setting.
2402 <varlistentry id="guc-log-min-duration-statement" xreflabel="log_min_duration_statement">
2403 <term><varname>log_min_duration_statement</varname> (<type>integer</type>)</term>
2405 <primary><varname>log_min_duration_statement</> configuration parameter</primary>
2409 Logs the statement and its duration on a single log line if its
2410 duration is greater than or equal to the specified number of
2411 milliseconds. Setting this to zero will print all statements
2412 and their durations. Minus-one (the default) disables the
2413 feature. For example, if you set it to <literal>250</literal>
2414 then all SQL statements that run 250ms or longer will be
2415 logged. Enabling this parameter can be useful in tracking down
2416 unoptimized queries in your applications. This setting is
2417 independent of <varname>log_statement</varname> and
2418 <varname>log_duration</varname>. Only superusers can change
2424 <varlistentry id="guc-silent-mode" xreflabel="silent_mode">
2425 <term><varname>silent_mode</varname> (<type>boolean</type>)</term>
2427 <primary><varname>silent_mode</> configuration parameter</primary>
2431 Runs the server silently. If this parameter is set, the server
2432 will automatically run in background and any controlling
2433 terminals are disassociated.
2434 The server's standard output and standard error are redirected
2435 to <literal>/dev/null</>, so any messages sent to them will be lost.
2436 Unless <application>syslog</> logging is selected or
2437 <varname>redirect_stderr</> is enabled, using this parameter
2438 is discouraged because it makes it impossible to see error messages.
2439 This parameter can only be set at server start.
2447 Here is a list of the various message severity levels used in
2451 <term><literal>DEBUG[1-5]</literal></term>
2454 Provides information for use by developers.
2460 <term><literal>INFO</literal></term>
2463 Provides information implicitly requested by the user,
2464 e.g., during <command>VACUUM VERBOSE</>.
2470 <term><literal>NOTICE</literal></term>
2473 Provides information that may be helpful to users, e.g.,
2474 truncation of long identifiers and the creation of indexes as part
2481 <term><literal>WARNING</literal></term>
2484 Provides warnings to the user, e.g., <command>COMMIT</>
2485 outside a transaction block.
2491 <term><literal>ERROR</literal></term>
2494 Reports an error that caused the current command to abort.
2500 <term><literal>LOG</literal></term>
2503 Reports information of interest to administrators, e.g.,
2504 checkpoint activity.
2510 <term><literal>FATAL</literal></term>
2513 Reports an error that caused the current session to abort.
2519 <term><literal>PANIC</literal></term>
2522 Reports an error that caused all sessions to abort.
2530 <sect2 id="runtime-config-logging-what">
2531 <title>What To Log</title>
2536 <term><varname>debug_print_parse</varname> (<type>boolean</type>)</term>
2537 <term><varname>debug_print_rewritten</varname> (<type>boolean</type>)</term>
2538 <term><varname>debug_print_plan</varname> (<type>boolean</type>)</term>
2539 <term><varname>debug_pretty_print</varname> (<type>boolean</type>)</term>
2541 <primary><varname>debug_print_parse</> configuration parameter</primary>
2544 <primary><varname>debug_print_rewritten</> configuration parameter</primary>
2547 <primary><varname>debug_print_plan</> configuration parameter</primary>
2550 <primary><varname>debug_pretty_print</> configuration parameter</primary>
2554 These parameters enable various debugging output to be emitted.
2555 For each executed query, they print
2556 the resulting parse tree, the query rewriter output, or the
2557 execution plan. <varname>debug_pretty_print</varname> indents
2558 these displays to produce a more readable but much longer
2559 output format. <varname>client_min_messages</varname> or
2560 <varname>log_min_messages</varname> must be
2561 <literal>DEBUG1</literal> or lower to actually send this output
2562 to the client or the server log, respectively.
2563 These parameters are off by default.
2568 <varlistentry id="guc-log-connections" xreflabel="log_connections">
2569 <term><varname>log_connections</varname> (<type>boolean</type>)</term>
2571 <primary><varname>log_connections</> configuration parameter</primary>
2575 This outputs a line to the server log detailing each successful
2576 connection. This is off by default, although it is probably very
2577 useful. Some client programs, like <application>psql</>, attempt
2578 to connect twice while determining if a password is required, so
2579 duplicate <quote>connection received</> messages do not
2580 necessarily indicate a problem.
2581 This parameter can only be set in the <filename>postgresql.conf</>
2582 file or on the server command line.
2587 <varlistentry id="guc-log-disconnections" xreflabel="log_disconnections">
2588 <term><varname>log_disconnections</varname> (<type>boolean</type>)</term>
2590 <primary><varname>log_disconnections</> configuration parameter</primary>
2594 This outputs a line in the server log similar to
2595 <varname>log_connections</varname> but at session termination,
2596 and includes the duration of the session. This is off by
2598 This parameter can only be set in the <filename>postgresql.conf</>
2599 file or on the server command line.
2605 <varlistentry id="guc-log-duration" xreflabel="log_duration">
2606 <term><varname>log_duration</varname> (<type>boolean</type>)</term>
2608 <primary><varname>log_duration</> configuration parameter</primary>
2612 Causes the duration of every completed statement which satisfies
2613 <varname>log_statement</> to be logged. When using this option,
2614 if you are not using <application>syslog</>, it is recommended
2615 that you log the PID or session ID using <varname>log_line_prefix</>
2616 so that you can link the statement message to the later
2617 duration message using the process ID or session ID. The default is
2618 <literal>off</>. Only superusers can change this setting.
2623 <varlistentry id="guc-log-line-prefix" xreflabel="log_line_prefix">
2624 <term><varname>log_line_prefix</varname> (<type>string</type>)</term>
2626 <primary><varname>log_line_prefix</> configuration parameter</primary>
2630 This is a <function>printf</>-style string that is output at the
2631 beginning of each log line. The default is an empty string.
2632 Each recognized escape is replaced as outlined
2633 below - anything else that looks like an escape is ignored. Other
2634 characters are copied straight to the log line. Some escapes are
2635 only recognized by session processes, and do not apply to
2636 background processes such as the postmaster. <application>Syslog</>
2638 time stamp and process ID information, so you probably do not want to
2639 use those escapes if you are using <application>syslog</>.
2640 This parameter can only be set in the <filename>postgresql.conf</>
2641 file or on the server command line.
2647 <entry>Escape</entry>
2648 <entry>Effect</entry>
2649 <entry>Session only</entry>
2654 <entry><literal>%u</literal></entry>
2655 <entry>User name</entry>
2659 <entry><literal>%d</literal></entry>
2660 <entry>Database name</entry>
2664 <entry><literal>%r</literal></entry>
2665 <entry>Remote host name or IP address, and remote port</entry>
2669 <entry><literal>%h</literal></entry>
2670 <entry>Remote host name or IP address</entry>
2674 <entry><literal>%p</literal></entry>
2675 <entry>Process ID</entry>
2679 <entry><literal>%t</literal></entry>
2680 <entry>Time stamp (no milliseconds)</entry>
2684 <entry><literal>%m</literal></entry>
2685 <entry>Time stamp with milliseconds</entry>
2689 <entry><literal>%i</literal></entry>
2690 <entry>Command tag: This is the command that generated the log line.</entry>
2694 <entry><literal>%c</literal></entry>
2695 <entry>Session ID: A unique identifier for each session.
2696 It is 2 4-byte hexadecimal numbers (without leading zeros)
2697 separated by a dot. The numbers
2698 are the session start time and the process ID, so this can also
2699 be used as a space saving way of printing these items.</entry>
2703 <entry><literal>%l</literal></entry>
2704 <entry>Number of the log line for each process, starting at 1</entry>
2708 <entry><literal>%s</literal></entry>
2709 <entry>Session start time stamp</entry>
2713 <entry><literal>%x</literal></entry>
2714 <entry>Transaction ID</entry>
2718 <entry><literal>%q</literal></entry>
2719 <entry>Does not produce any output, but tells non-session
2720 processes to stop at this point in the string. Ignored by
2721 session processes.</entry>
2725 <entry><literal>%%</literal></entry>
2726 <entry>Literal <literal>%</></entry>
2736 <varlistentry id="guc-log-statement" xreflabel="log_statement">
2737 <term><varname>log_statement</varname> (<type>string</type>)</term>
2739 <primary><varname>log_statement</> configuration parameter</primary>
2743 Controls which SQL statements are logged. Valid values are
2744 <literal>none</>, <literal>ddl</>, <literal>mod</>, and
2745 <literal>all</>. <literal>ddl</> logs all data definition
2746 commands like <literal>CREATE</>, <literal>ALTER</>, and
2747 <literal>DROP</> commands. <literal>mod</> logs all
2748 <literal>ddl</> statements, plus <literal>INSERT</>,
2749 <literal>UPDATE</>, <literal>DELETE</>, <literal>TRUNCATE</>,
2750 and <literal>COPY FROM</>. <literal>PREPARE</> and
2751 <literal>EXPLAIN ANALYZE</> statements are also logged if their
2752 contained command is of an appropriate type.
2755 The default is <literal>none</>. Only superusers can change this
2761 The <command>EXECUTE</command> statement is not considered a
2762 <literal>ddl</> or <literal>mod</> statement. When it is logged,
2763 only the name of the prepared statement is reported, not the
2764 actual prepared statement.
2768 When a function is defined in the
2769 <application>PL/pgSQL</application>server-side language, any queries
2770 executed by the function will only be logged the first time that the
2771 function is invoked in a particular session. This is because
2772 <application>PL/pgSQL</application> keeps a cache of the
2773 query plans produced for the SQL statements in the function.
2779 <varlistentry id="guc-log-hostname" xreflabel="log_hostname">
2780 <term><varname>log_hostname</varname> (<type>boolean</type>)</term>
2782 <primary><varname>log_hostname</> configuration parameter</primary>
2786 By default, connection log messages only show the IP address of the
2787 connecting host. Turning on this parameter causes logging of the
2788 host name as well. Note that depending on your host name resolution
2789 setup this might impose a non-negligible performance penalty.
2790 This parameter can only be set in the <filename>postgresql.conf</>
2791 file or on the server command line.
2800 <sect1 id="runtime-config-statistics">
2801 <title>Run-Time Statistics</title>
2803 <sect2 id="runtime-config-statistics-monitor">
2804 <title>Statistics Monitoring</title>
2808 <term><varname>log_statement_stats</varname> (<type>boolean</type>)</term>
2809 <term><varname>log_parser_stats</varname> (<type>boolean</type>)</term>
2810 <term><varname>log_planner_stats</varname> (<type>boolean</type>)</term>
2811 <term><varname>log_executor_stats</varname> (<type>boolean</type>)</term>
2813 <primary><varname>log_statement_stats</> configuration parameter</primary>
2816 <primary><varname>log_parser_stats</> configuration parameter</primary>
2819 <primary><varname>log_planner_stats</> configuration parameter</primary>
2822 <primary><varname>log_executor_stats</> configuration parameter</primary>
2826 For each query, write performance statistics of the respective
2827 module to the server log. This is a crude profiling
2828 instrument. <varname>log_statement_stats</varname> reports total
2829 statement statistics, while the others report per-module statistics.
2830 <varname>log_statement_stats</varname> cannot be enabled together with
2831 any of the per-module options. All of these options are disabled by
2832 default. Only superusers can change these settings.
2840 <sect2 id="runtime-config-statistics-collector">
2841 <title>Query and Index Statistics Collector</title>
2844 These parameters control a server-wide statistics collection feature.
2845 When statistics collection is enabled, the data that is produced can be
2846 accessed via the <structname>pg_stat</structname> and
2847 <structname>pg_statio</structname> family of system views.
2848 Refer to <xref linkend="monitoring"> for more information.
2853 <varlistentry id="guc-stats-start-collector" xreflabel="stats_start_collector">
2854 <term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>
2856 <primary><varname>stats_start_collector</> configuration parameter</primary>
2860 Controls whether the server should start the
2861 statistics-collection subprocess. This is on by default, but
2862 may be turned off if you know you have no interest in
2863 collecting statistics or running autovacuum.
2864 This parameter can only be set at server start, because the collection
2865 subprocess cannot be started or stopped on-the-fly. (However, the
2866 extent to which statistics are actually gathered can be changed while
2867 the server is running, so long as the subprocess exists.)
2872 <varlistentry id="guc-stats-command-string" xreflabel="stats_command_string">
2873 <term><varname>stats_command_string</varname> (<type>boolean</type>)</term>
2875 <primary><varname>stats_command_string</> configuration parameter</primary>
2879 Enables the collection of statistics on the currently
2880 executing command of each session, along with the time at
2881 which that command began execution. This parameter is off by
2882 default. Note that even when enabled, this information is not
2883 visible to all users, only to superusers and the user owning
2884 the session being reported on; so it should not represent a
2886 Only superusers can change this setting.
2891 <varlistentry id="guc-stats-block-level" xreflabel="stats_block_level">
2892 <term><varname>stats_block_level</varname> (<type>boolean</type>)</term>
2894 <primary><varname>stats_block_level</> configuration parameter</primary>
2898 Enables the collection of block-level statistics on database
2899 activity. This parameter is off by default.
2900 Only superusers can change this setting.
2905 <varlistentry id="guc-stats-row-level" xreflabel="stats_row_level">
2906 <term><varname>stats_row_level</varname> (<type>boolean</type>)</term>
2908 <primary><varname>stats_row_level</> configuration parameter</primary>
2912 Enables the collection of row-level statistics on database
2913 activity. This parameter is off by default.
2914 Only superusers can change this setting.
2919 <varlistentry id="guc-stats-reset-on-server-start" xreflabel="stats_reset_on_server_start">
2920 <term><varname>stats_reset_on_server_start</varname> (<type>boolean</type>)</term>
2922 <primary><varname>stats_reset_on_server_start</> configuration parameter</primary>
2926 If on, collected statistics are zeroed out whenever the server
2927 is restarted. If off, statistics are accumulated across server
2928 restarts. The default is <literal>off</>. This parameter can only
2929 be set at server start.
2938 <sect1 id="runtime-config-autovacuum">
2939 <title>Automatic Vacuuming</title>
2942 <primary>autovacuum</primary>
2943 <secondary>configuration parameters</secondary>
2947 These settings control the behavior of the <firstterm>autovacuum</>
2948 feature. Refer to <xref linkend="autovacuum"> for
2954 <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
2955 <term><varname>autovacuum</varname> (<type>boolean</type>)</term>
2957 <primary><varname>autovacuum</> configuration parameter</primary>
2961 Controls whether the server should run the
2962 autovacuum daemon. This is off by default.
2963 <varname>stats_start_collector</> and <varname>stats_row_level</>
2964 must also be turned on for autovacuum to work.
2965 This parameter can only be set in the <filename>postgresql.conf</>
2966 file or on the server command line.
2971 <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
2972 <term><varname>autovacuum_naptime</varname> (<type>integer</type>)</term>
2974 <primary><varname>autovacuum_naptime</> configuration parameter</primary>
2978 Specifies the delay between activity rounds for the autovacuum
2979 daemon. In each round the daemon examines one database
2980 and issues <command>VACUUM</> and <command>ANALYZE</> commands
2981 as needed for tables in that database. The delay is measured
2982 in seconds, and the default is 60.
2983 This parameter can only be set in the <filename>postgresql.conf</>
2984 file or on the server command line.
2989 <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
2990 <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)</term>
2992 <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
2996 Specifies the minimum number of updated or deleted tuples needed
2997 to trigger a <command>VACUUM</> in any one table.
2998 The default is 1000.
2999 This parameter can only be set in the <filename>postgresql.conf</>
3000 file or on the server command line.
3001 This setting can be overridden for individual tables by entries in
3002 <structname>pg_autovacuum</>.
3007 <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
3008 <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)</term>
3010 <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
3014 Specifies the minimum number of inserted, updated or deleted tuples
3015 needed to trigger an <command>ANALYZE</> in any one table.
3017 This parameter can only be set in the <filename>postgresql.conf</>
3018 file or on the server command line.
3019 This setting can be overridden for individual tables by entries in
3020 <structname>pg_autovacuum</>.
3025 <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
3026 <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)</term>
3028 <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
3032 Specifies a fraction of the table size to add to
3033 <varname>autovacuum_vacuum_threshold</varname>
3034 when deciding whether to trigger a <command>VACUUM</>.
3036 This parameter can only be set in the <filename>postgresql.conf</>
3037 file or on the server command line.
3038 This setting can be overridden for individual tables by entries in
3039 <structname>pg_autovacuum</>.
3044 <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
3045 <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)</term>
3047 <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
3051 Specifies a fraction of the table size to add to
3052 <varname>autovacuum_analyze_threshold</varname>
3053 when deciding whether to trigger an <command>ANALYZE</>.
3055 This parameter can only be set in the <filename>postgresql.conf</>
3056 file or on the server command line.
3057 This setting can be overridden for individual tables by entries in
3058 <structname>pg_autovacuum</>.
3063 <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
3064 <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
3066 <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
3070 Specifies the cost delay value that will be used in automatic
3071 <command>VACUUM</> operations. If -1 is specified (which is the
3072 default), the regular
3073 <xref linkend="guc-vacuum-cost-delay"> value will be used.
3074 This parameter can only be set in the <filename>postgresql.conf</>
3075 file or on the server command line.
3076 This setting can be overridden for individual tables by entries in
3077 <structname>pg_autovacuum</>.
3082 <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
3083 <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
3085 <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
3089 Specifies the cost limit value that will be used in automatic
3090 <command>VACUUM</> operations. If -1 is specified (which is the
3091 default), the regular
3092 <xref linkend="guc-vacuum-cost-limit"> value will be used.
3093 This parameter can only be set in the <filename>postgresql.conf</>
3094 file or on the server command line.
3095 This setting can be overridden for individual tables by entries in
3096 <structname>pg_autovacuum</>.
3104 <sect1 id="runtime-config-client">
3105 <title>Client Connection Defaults</title>
3107 <sect2 id="runtime-config-client-statement">
3108 <title>Statement Behavior</title>
3111 <varlistentry id="guc-search-path" xreflabel="search_path">
3112 <term><varname>search_path</varname> (<type>string</type>)</term>
3114 <primary><varname>search_path</> configuration parameter</primary>
3116 <indexterm><primary>path</><secondary>for schemas</></>
3119 This variable specifies the order in which schemas are searched
3120 when an object (table, data type, function, etc.) is referenced by a
3121 simple name with no schema component. When there are objects of
3122 identical names in different schemas, the one found first
3123 in the search path is used. An object that is not in any of the
3124 schemas in the search path can only be referenced by specifying
3125 its containing schema with a qualified (dotted) name.
3129 The value for <varname>search_path</varname> has to be a comma-separated
3130 list of schema names. If one of the list items is
3131 the special value <literal>$user</literal>, then the schema
3132 having the name returned by <function>SESSION_USER</> is substituted, if there
3133 is such a schema. (If not, <literal>$user</literal> is ignored.)
3137 The system catalog schema, <literal>pg_catalog</>, is always
3138 searched, whether it is mentioned in the path or not. If it is
3139 mentioned in the path then it will be searched in the specified
3140 order. If <literal>pg_catalog</> is not in the path then it will
3141 be searched <emphasis>before</> searching any of the path items.
3142 It should also be noted that the temporary-table schema,
3143 <literal>pg_temp_<replaceable>nnn</></>, is implicitly searched before any of
3148 When objects are created without specifying a particular target
3149 schema, they will be placed in the first schema listed
3150 in the search path. An error is reported if the search path is
3155 The default value for this parameter is
3156 <literal>'"$user", public'</literal> (where the second part will be
3157 ignored if there is no schema named <literal>public</>).
3158 This supports shared use of a database (where no users
3159 have private schemas, and all share use of <literal>public</>),
3160 private per-user schemas, and combinations of these. Other
3161 effects can be obtained by altering the default search path
3162 setting, either globally or per-user.
3166 The current effective value of the search path can be examined
3167 via the <acronym>SQL</acronym> function
3168 <function>current_schemas()</>. This is not quite the same as
3169 examining the value of <varname>search_path</varname>, since
3170 <function>current_schemas()</> shows how the requests
3171 appearing in <varname>search_path</varname> were resolved.
3175 For more information on schema handling, see <xref linkend="ddl-schemas">.
3180 <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
3181 <term><varname>default_tablespace</varname> (<type>string</type>)</term>
3183 <primary><varname>default_tablespace</> configuration parameter</primary>
3185 <indexterm><primary>tablespace</><secondary>default</></>
3188 This variable specifies the default tablespace in which to create
3189 objects (tables and indexes) when a <command>CREATE</> command does
3190 not explicitly specify a tablespace.
3194 The value is either the name of a tablespace, or an empty string
3195 to specify using the default tablespace of the current database.
3196 If the value does not match the name of any existing tablespace,
3197 <productname>PostgreSQL</> will automatically use the default
3198 tablespace of the current database.
3202 For more information on tablespaces,
3203 see <xref linkend="manage-ag-tablespaces">.
3208 <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
3209 <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
3211 <primary><varname>check_function_bodies</> configuration parameter</primary>
3215 This parameter is normally on. When set to <literal>off</>, it
3216 disables validation of the function body string during <xref
3217 linkend="sql-createfunction"
3218 endterm="sql-createfunction-title">. Disabling validation is
3219 occasionally useful to avoid problems such as forward references
3220 when restoring function definitions from a dump.
3225 <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
3227 <primary>transaction isolation level</primary>
3230 <primary><varname>default_transaction_isolation</> configuration parameter</primary>
3232 <term><varname>default_transaction_isolation</varname> (<type>string</type>)</term>
3235 Each SQL transaction has an isolation level, which can be
3236 either <quote>read uncommitted</quote>, <quote>read
3237 committed</quote>, <quote>repeatable read</quote>, or
3238 <quote>serializable</quote>. This parameter controls the
3239 default isolation level of each new transaction. The default
3240 is <quote>read committed</quote>.
3244 Consult <xref linkend="mvcc"> and <xref
3245 linkend="sql-set-transaction"
3246 endterm="sql-set-transaction-title"> for more information.
3251 <varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
3253 <primary>read-only transaction</primary>
3256 <primary><varname>default_transaction_read_only</> configuration parameter</primary>
3259 <term><varname>default_transaction_read_only</varname> (<type>boolean</type>)</term>
3262 A read-only SQL transaction cannot alter non-temporary tables.
3263 This parameter controls the default read-only status of each new
3264 transaction. The default is <literal>off</> (read/write).
3268 Consult <xref linkend="sql-set-transaction"
3269 endterm="sql-set-transaction-title"> for more information.
3274 <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
3275 <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
3277 <primary><varname>statement_timeout</> configuration parameter</primary>
3281 Abort any statement that takes over the specified number of
3282 milliseconds. If <varname>log_min_error_statement</> is set to
3283 <literal>ERROR</> or lower, the statement that timed out will also be
3284 logged. A value of zero (the default) turns off the
3292 <sect2 id="runtime-config-client-format">
3293 <title>Locale and Formatting</title>
3297 <varlistentry id="guc-datestyle" xreflabel="DateStyle">
3298 <term><varname>DateStyle</varname> (<type>string</type>)</term>
3300 <primary><varname>DateStyle</> configuration parameter</primary>
3304 Sets the display format for date and time values, as well as the
3305 rules for interpreting ambiguous date input values. For
3306 historical reasons, this variable contains two independent
3307 components: the output format specification (<literal>ISO</>,
3308 <literal>Postgres</>, <literal>SQL</>, or <literal>German</>)
3309 and the input/output specification for year/month/day ordering
3310 (<literal>DMY</>, <literal>MDY</>, or <literal>YMD</>). These
3311 can be set separately or together. The keywords <literal>Euro</>
3312 and <literal>European</> are synonyms for <literal>DMY</>; the
3313 keywords <literal>US</>, <literal>NonEuro</>, and
3314 <literal>NonEuropean</> are synonyms for <literal>MDY</>. See
3315 <xref linkend="datatype-datetime"> for more information. The
3316 built-in default is <literal>ISO, MDY</>, but
3317 <application>initdb</application> will initialize the
3318 configuration file with a setting that corresponds to the
3319 behavior of the chosen <varname>lc_time</varname> locale.
3324 <varlistentry id="guc-timezone" xreflabel="timezone">
3325 <term><varname>timezone</varname> (<type>string</type>)</term>
3327 <primary><varname>timezone</> configuration parameter</primary>
3329 <indexterm><primary>time zone</></>
3332 Sets the time zone for displaying and interpreting time
3333 stamps. The default is 'unknown', which means to use whatever
3334 the system environment specifies as the time zone. See <xref
3335 linkend="datatype-datetime"> for more information.
3340 <varlistentry id="guc-australian-timezones" xreflabel="australian_timezones">
3341 <term><varname>australian_timezones</varname> (<type>boolean</type>)</term>
3343 <primary><varname>australian_timezones</> configuration parameter</primary>
3345 <indexterm><primary>time zone</><secondary>Australian</></>
3348 If set to on, <literal>ACST</literal>,
3349 <literal>CST</literal>, <literal>EST</literal>, and
3350 <literal>SAT</literal> are interpreted as Australian time
3351 zones rather than as North/South American time zones and
3352 Saturday. The default is <literal>off</>.
3357 <varlistentry id="guc-extra-float-digits" xreflabel="extra_float_digits">
3359 <primary>significant digits</primary>
3362 <primary>floating-point</primary>
3363 <secondary>display</secondary>
3366 <primary><varname>extra_float_digits</> configuration parameter</primary>
3369 <term><varname>extra_float_digits</varname> (<type>integer</type>)</term>
3372 This parameter adjusts the number of digits displayed for
3373 floating-point values, including <type>float4</>, <type>float8</>,
3374 and geometric data types. The parameter value is added to the
3375 standard number of digits (<literal>FLT_DIG</> or <literal>DBL_DIG</>
3376 as appropriate). The value can be set as high as 2, to include
3377 partially-significant digits; this is especially useful for dumping
3378 float data that needs to be restored exactly. Or it can be set
3379 negative to suppress unwanted digits.
3384 <varlistentry id="guc-client-encoding" xreflabel="client_encoding">
3385 <term><varname>client_encoding</varname> (<type>string</type>)</term>
3387 <primary><varname>client_encoding</> configuration parameter</primary>
3389 <indexterm><primary>character set</></>
3392 Sets the client-side encoding (character set).
3393 The default is to use the database encoding.
3398 <varlistentry id="guc-lc-messages" xreflabel="lc_messages">
3399 <term><varname>lc_messages</varname> (<type>string</type>)</term>
3401 <primary><varname>lc_messages</> configuration parameter</primary>
3405 Sets the language in which messages are displayed. Acceptable
3406 values are system-dependent; see <xref linkend="locale"> for
3407 more information. If this variable is set to the empty string
3408 (which is the default) then the value is inherited from the
3409 execution environment of the server in a system-dependent way.
3413 On some systems, this locale category does not exist. Setting
3414 this variable will still work, but there will be no effect.
3415 Also, there is a chance that no translated messages for the
3416 desired language exist. In that case you will continue to see
3417 the English messages.
3421 Only superusers can change this setting, because it affects the
3422 messages sent to the postmaster log as well as to the client.
3427 <varlistentry id="guc-lc-monetary" xreflabel="lc_monetary">
3428 <term><varname>lc_monetary</varname> (<type>string</type>)</term>
3430 <primary><varname>lc_monetary</> configuration parameter</primary>
3434 Sets the locale to use for formatting monetary amounts, for
3435 example with the <function>to_char</function> family of
3436 functions. Acceptable values are system-dependent; see <xref
3437 linkend="locale"> for more information. If this variable is
3438 set to the empty string (which is the default) then the value
3439 is inherited from the execution environment of the server in a
3440 system-dependent way.
3445 <varlistentry id="guc-lc-numeric" xreflabel="lc_numeric">
3446 <term><varname>lc_numeric</varname> (<type>string</type>)</term>
3448 <primary><varname>lc_numeric</> configuration parameter</primary>
3452 Sets the locale to use for formatting numbers, for example
3453 with the <function>to_char</function> family of
3454 functions. Acceptable values are system-dependent; see <xref
3455 linkend="locale"> for more information. If this variable is
3456 set to the empty string (which is the default) then the value
3457 is inherited from the execution environment of the server in a
3458 system-dependent way.
3463 <varlistentry id="guc-lc-time" xreflabel="lc_time">
3464 <term><varname>lc_time</varname> (<type>string</type>)</term>
3466 <primary><varname>lc_time</> configuration parameter</primary>
3470 Sets the locale to use for formatting date and time values.
3471 (Currently, this setting does nothing, but it may in the
3472 future.) Acceptable values are system-dependent; see <xref
3473 linkend="locale"> for more information. If this variable is
3474 set to the empty string (which is the default) then the value
3475 is inherited from the execution environment of the server in a
3476 system-dependent way.
3484 <sect2 id="runtime-config-client-other">
3485 <title>Other Defaults</title>
3489 <varlistentry id="guc-explain-pretty-print" xreflabel="explain_pretty_print">
3490 <term><varname>explain_pretty_print</varname> (<type>boolean</type>)</term>
3492 <primary><varname>explain_pretty_print</> configuration parameter</primary>
3496 Determines whether <command>EXPLAIN VERBOSE</> uses the
3497 indented or non-indented format for displaying detailed
3498 query-tree dumps. The default is <literal>on</>.
3503 <varlistentry id="guc-dynamic-library-path" xreflabel="dynamic_library_path">
3504 <term><varname>dynamic_library_path</varname> (<type>string</type>)</term>
3506 <primary><varname>dynamic_library_path</> configuration parameter</primary>
3508 <indexterm><primary>dynamic loading</></>
3511 If a dynamically loadable module needs to be opened and the
3512 file name specified in the <command>CREATE FUNCTION</command> or
3513 <command>LOAD</command> command
3514 does not have a directory component (i.e. the
3515 name does not contain a slash), the system will search this
3516 path for the required file.
3520 The value for <varname>dynamic_library_path</varname> has to be a
3521 list of absolute directory paths separated by colons (or semi-colons
3522 on Windows). If a list element starts
3523 with the special string <literal>$libdir</literal>, the
3524 compiled-in <productname>PostgreSQL</productname> package
3525 library directory is substituted for <literal>$libdir</literal>. This
3526 is where the modules provided by the standard
3527 <productname>PostgreSQL</productname> distribution are installed.
3528 (Use <literal>pg_config --pkglibdir</literal> to find out the name of
3529 this directory.) For example:
3531 dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
3533 or, in a Windows environment:
3535 dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
3540 The default value for this parameter is
3541 <literal>'$libdir'</literal>. If the value is set to an empty
3542 string, the automatic path search is turned off.
3546 This parameter can be changed at run time by superusers, but a
3547 setting done that way will only persist until the end of the
3548 client connection, so this method should be reserved for
3549 development purposes. The recommended way to set this parameter
3550 is in the <filename>postgresql.conf</filename> configuration
3560 <sect1 id="runtime-config-locks">
3561 <title>Lock Management</title>
3565 <varlistentry id="guc-deadlock-timeout" xreflabel="deadlock_timeout">
3567 <primary>deadlock</primary>
3568 <secondary>timeout during</secondary>
3571 <primary>timeout</primary>
3572 <secondary>deadlock</secondary>
3575 <primary><varname>deadlock_timeout</> configuration parameter</primary>
3578 <term><varname>deadlock_timeout</varname> (<type>integer</type>)</term>
3581 This is the amount of time, in milliseconds, to wait on a lock
3582 before checking to see if there is a deadlock condition. The
3583 check for deadlock is relatively slow, so the server doesn't run
3584 it every time it waits for a lock. We (optimistically?) assume
3585 that deadlocks are not common in production applications and
3586 just wait on the lock for a while before starting the check for a
3587 deadlock. Increasing this value reduces the amount of time
3588 wasted in needless deadlock checks, but slows down reporting of
3589 real deadlock errors. The default is 1000 (i.e., one second),
3590 which is probably about the smallest value you would want in
3591 practice. On a heavily loaded server you might want to raise it.
3592 Ideally the setting should exceed your typical transaction time,
3593 so as to improve the odds that a lock will be released before
3594 the waiter decides to check for deadlock.
3599 <varlistentry id="guc-max-locks-per-transaction" xreflabel="max_locks_per_transaction">
3600 <term><varname>max_locks_per_transaction</varname> (<type>integer</type>)</term>
3602 <primary><varname>max_locks_per_transaction</> configuration parameter</primary>
3606 The shared lock table is created with room to describe locks on
3607 <varname>max_locks_per_transaction</varname> *
3608 (<xref linkend="guc-max-connections"> +
3609 <xref linkend="guc-max-prepared-transactions">) objects;
3610 hence, no more than this many distinct objects can
3611 be locked at any one time. (Thus, this parameter's name may be
3612 confusing: it is not a hard limit on the number of locks taken
3613 by any one transaction, but rather a maximum average value.)
3614 The default, 64, has historically
3615 proven sufficient, but you might need to raise this value if you
3616 have clients that touch many different tables in a single
3617 transaction. This parameter can only be set at server start.
3621 Increasing this parameter may cause <productname>PostgreSQL</>
3622 to request more <systemitem class="osname">System V</> shared
3623 memory than your operating system's default configuration
3624 allows. See <xref linkend="sysvipc"> for information on how to
3625 adjust those parameters, if necessary.
3633 <sect1 id="runtime-config-compatible">
3634 <title>Version and Platform Compatibility</title>
3636 <sect2 id="runtime-config-compatible-version">
3637 <title>Previous PostgreSQL Versions</title>
3641 <varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
3642 <term><varname>add_missing_from</varname> (<type>boolean</type>)</term>
3643 <indexterm><primary>FROM</><secondary>missing</></>
3645 <primary><varname>add_missing_from</> configuration parameter</primary>
3649 When on, tables that are referenced by a query will be
3650 automatically added to the <literal>FROM</> clause if not
3651 already present. This behavior does not comply with the SQL
3652 standard and many people dislike it because it can mask mistakes
3653 (such as referencing a table where you should have referenced
3654 its alias). The default is <literal>off</>. This variable can be
3655 enabled for compatibility with releases of
3656 <productname>PostgreSQL</> prior to 8.1, where this behavior was
3661 Note that even when this variable is enabled, a warning
3662 message will be emitted for each implicit <literal>FROM</>
3663 entry referenced by a query. Users are encouraged to update
3664 their applications to not rely on this behavior, by adding all
3665 tables referenced by a query to the query's <literal>FROM</>
3666 clause (or its <literal>USING</> clause in the case of
3667 <command>DELETE</>).
3672 <varlistentry id="guc-array-nulls" xreflabel="array_nulls">
3673 <term><varname>array_nulls</varname> (<type>boolean</type>)</term>
3675 <primary><varname>array_nulls</> configuration parameter</primary>
3679 This controls whether the array input parser recognizes
3680 unquoted <literal>NULL</> as specifying a NULL array element.
3681 By default, this is <literal>on</>, allowing array values containing
3682 NULLs to be entered. However, <productname>PostgreSQL</> versions
3683 before 8.2 did not support NULLs in arrays, and therefore would
3684 treat <literal>NULL</> as specifying a normal array element with
3685 the string value <quote>NULL</>. For backwards compatibility with
3686 applications that require the old behavior, this variable can be
3687 turned <literal>off</>.
3691 Note that it is possible to create array values containing NULLs
3692 even when this variable is <literal>off</>.
3697 <varlistentry id="guc-default-with-oids" xreflabel="default_with_oids">
3698 <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
3700 <primary><varname>default_with_oids</> configuration parameter</primary>
3704 This controls whether <command>CREATE TABLE</command> and
3705 <command>CREATE TABLE AS</command> include an OID column in
3706 newly-created tables, if neither <literal>WITH OIDS</literal>
3707 nor <literal>WITHOUT OIDS</literal> is specified. It also
3708 determines whether OIDs will be included in tables created by
3709 <command>SELECT INTO</command>. In <productname>PostgreSQL</>
3710 8.1 <varname>default_with_oids</> is disabled by default; in
3711 prior versions of <productname>PostgreSQL</productname>, it
3716 The use of OIDs in user tables is considered deprecated, so
3717 most installations should leave this variable disabled.
3718 Applications that require OIDs for a particular table should
3719 specify <literal>WITH OIDS</literal> when creating the
3720 table. This variable can be enabled for compatibility with old
3721 applications that do not follow this behavior.
3726 <varlistentry id="guc-escape-string-warning" xreflabel="escape_string_warning">
3727 <term><varname>escape_string_warning</varname> (<type>boolean</type>)</term>
3728 <indexterm><primary>strings</><secondary>escape</></>
3730 <primary><varname>escape_string_warning</> configuration parameter</primary>
3734 When on, a warning is issued if a backslash (<literal>\</>)
3735 appears in an ordinary string literal (<literal>'...'</>
3736 syntax). The default is <literal>on</>.
3739 Escape string syntax (<literal>E'...'</>) should be used for
3740 backslash escape sequences, because ordinary strings have
3741 the standard-conforming behavior of treating backslashes
3742 literally when the <literal>standard-conforming-strings</>
3743 option is set <literal>on</>.
3748 <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
3749 <term><varname>standard_conforming_strings</varname> (<type>boolean</type>)</term>
3750 <indexterm><primary>strings</><secondary>escape</></>
3752 <primary><varname>standard_conforming_strings</> configuration parameter</primary>
3756 Controls whether ordinary string literals
3757 (<literal>'...'</>) treat backslashes literally, as specified in
3758 the SQL standard. Applications may check this
3759 parameter to determine how string literals will be processed.
3760 The presence of this parameter can also be taken as an indication
3761 that the escape string syntax (<literal>E'...'</>) is supported.
3766 <varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
3767 <term><varname>regex_flavor</varname> (<type>string</type>)</term>
3768 <indexterm><primary>regular expressions</></>
3770 <primary><varname>regex_flavor</> configuration parameter</primary>
3774 The regular expression <quote>flavor</> can be set to
3775 <literal>advanced</>, <literal>extended</>, or <literal>basic</>.
3776 The default is <literal>advanced</>. The <literal>extended</>
3777 setting may be useful for exact backwards compatibility with
3778 pre-7.4 releases of <productname>PostgreSQL</>. See
3779 <xref linkend="posix-syntax-details"> for details.
3784 <varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
3785 <term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
3787 <primary><varname>sql_inheritance</> configuration parameter</primary>
3789 <indexterm><primary>inheritance</></>
3792 This controls the inheritance semantics, in particular whether
3793 subtables are included by various commands by default. They were
3794 not included in versions prior to 7.1. If you need the old
3795 behavior you can set this variable to <literal>off</>, but in
3796 the long run you are encouraged to change your applications to
3797 use the <literal>ONLY</literal> key word to exclude subtables.
3798 See <xref linkend="ddl-inherit"> for more information about
3807 <sect2 id="runtime-config-compatible-clients">
3808 <title>Platform and Client Compatibility</title>
3811 <varlistentry id="guc-transform-null-equals" xreflabel="transform_null_equals">
3812 <term><varname>transform_null_equals</varname> (<type>boolean</type>)</term>
3813 <indexterm><primary>IS NULL</></>
3815 <primary><varname>transform_null_equals</> configuration parameter</primary>
3819 When on, expressions of the form <literal><replaceable>expr</> =
3820 NULL</literal> (or <literal>NULL =
3821 <replaceable>expr</></literal>) are treated as
3822 <literal><replaceable>expr</> IS NULL</literal>, that is, they
3823 return true if <replaceable>expr</> evaluates to the null value,
3824 and false otherwise. The correct SQL-spec-compliant behavior of
3825 <literal><replaceable>expr</> = NULL</literal> is to always
3826 return null (unknown). Therefore this parameter defaults to
3831 However, filtered forms in <productname>Microsoft
3832 Access</productname> generate queries that appear to use
3833 <literal><replaceable>expr</> = NULL</literal> to test for
3834 null values, so if you use that interface to access the database you
3835 might want to turn this option on. Since expressions of the
3836 form <literal><replaceable>expr</> = NULL</literal> always
3837 return the null value (using the correct interpretation) they are not
3838 very useful and do not appear often in normal applications, so
3839 this option does little harm in practice. But new users are
3840 frequently confused about the semantics of expressions
3841 involving null values, so this option is not on by default.
3845 Note that this option only affects the exact form <literal>= NULL</>,
3846 not other comparison operators or other expressions
3847 that are computationally equivalent to some expression
3848 involving the equals operator (such as <literal>IN</literal>).
3849 Thus, this option is not a general fix for bad programming.
3853 Refer to <xref linkend="functions-comparison"> for related information.
3862 <sect1 id="runtime-config-preset">
3863 <title>Preset Options</title>
3866 The following <quote>parameters</> are read-only, and are determined
3867 when <productname>PostgreSQL</productname> is compiled or when it is
3868 installed. As such, they have been excluded from the sample
3869 <filename>postgresql.conf</> file. These options report
3870 various aspects of <productname>PostgreSQL</productname> behavior
3871 that may be of interest to certain applications, particularly
3872 administrative front-ends.
3877 <varlistentry id="guc-block-size" xreflabel="block_size">
3878 <term><varname>block_size</varname> (<type>integer</type>)</term>
3880 <primary><varname>block_size</> configuration parameter</primary>
3884 Reports the size of a disk block. It is determined by the value
3885 of <literal>BLCKSZ</> when building the server. The default
3886 value is 8192 bytes. The meaning of some configuration
3887 variables (such as <xref linkend="guc-shared-buffers">) is
3888 influenced by <varname>block_size</varname>. See <xref
3889 linkend="runtime-config-resource"> for information.
3894 <varlistentry id="guc-integer-datetimes" xreflabel="integer_datetimes">
3895 <term><varname>integer_datetimes</varname> (<type>boolean</type>)</term>
3897 <primary><varname>integer_datetimes</> configuration parameter</primary>
3901 Reports whether <productname>PostgreSQL</productname> was built
3902 with support for 64-bit-integer dates and times. It is set by
3903 configuring with <literal>--enable-integer-datetimes</literal>
3904 when building <productname>PostgreSQL</productname>. The
3905 default value is <literal>off</literal>.
3910 <varlistentry id="guc-lc-collate" xreflabel="lc_collate">
3911 <term><varname>lc_collate</varname> (<type>string</type>)</term>
3913 <primary><varname>lc_collate</> configuration parameter</primary>
3917 Reports the locale in which sorting of textual data is done.
3918 See <xref linkend="locale"> for more information.
3919 The value is determined when the database cluster is initialized.
3924 <varlistentry id="guc-lc-ctype" xreflabel="lc_ctype">
3925 <term><varname>lc_ctype</varname> (<type>string</type>)</term>
3927 <primary><varname>lc_ctype</> configuration parameter</primary>
3931 Reports the locale that determines character classifications.
3932 See <xref linkend="locale"> for more information.
3933 The value is determined when the database cluster is initialized.
3934 Ordinarily this will be the same as <varname>lc_collate</varname>,
3935 but for special applications it might be set differently.
3940 <varlistentry id="guc-max-function-args" xreflabel="max_function_args">
3941 <term><varname>max_function_args</varname> (<type>integer</type>)</term>
3943 <primary><varname>max_function_args</> configuration parameter</primary>
3947 Reports the maximum number of function arguments. It is determined by
3948 the value of <literal>FUNC_MAX_ARGS</> when building the server. The
3949 default value is 100.
3954 <varlistentry id="guc-max-identifier-length" xreflabel="max_identifier_length">
3955 <term><varname>max_identifier_length</varname> (<type>integer</type>)</term>
3957 <primary><varname>max_identifier_length</> configuration parameter</primary>
3961 Reports the maximum identifier length. It is determined as one
3962 less than the value of <literal>NAMEDATALEN</> when building
3963 the server. The default value of <literal>NAMEDATALEN</> is
3964 64; therefore the default
3965 <varname>max_identifier_length</varname> is 63.
3970 <varlistentry id="guc-max-index-keys" xreflabel="max_index_keys">
3971 <term><varname>max_index_keys</varname> (<type>integer</type>)</term>
3973 <primary><varname>max_index_keys</> configuration parameter</primary>
3977 Reports the maximum number of index keys. It is determined by
3978 the value of <literal>INDEX_MAX_KEYS</> when building the server. The
3979 default value is 32.
3984 <varlistentry id="guc-server-encoding" xreflabel="server_encoding">
3985 <term><varname>server_encoding</varname> (<type>string</type>)</term>
3987 <primary><varname>server_encoding</> configuration parameter</primary>
3989 <indexterm><primary>character set</></>
3992 Reports the database encoding (character set).
3993 It is determined when the database is created. Ordinarily,
3994 clients need only be concerned with the value of <xref
3995 linkend="guc-client-encoding">.
4000 <varlistentry id="guc-server-version" xreflabel="server_version">
4001 <term><varname>server_version</varname> (<type>string</type>)</term>
4003 <primary><varname>server_version</> configuration parameter</primary>
4007 Reports the version number of the server. It is determined by the
4008 value of <literal>PG_VERSION</> when building the server.
4016 <sect1 id="runtime-config-custom">
4017 <title>Customized Options</title>
4020 This feature was designed to allow parameters not normally known to
4021 <productname>PostgreSQL</productname> to be added by add-on modules
4022 (such as procedural languages). This allows add-on modules to be
4023 configured in the standard ways.
4028 <varlistentry id="guc-custom-variable-classes" xreflabel="custom_variable_classes">
4029 <term><varname>custom_variable_classes</varname> (<type>string</type>)</term>
4031 <primary><varname>custom_variable_classes</> configuration parameter</primary>
4035 This variable specifies one or several class names to be used for
4036 custom variables, in the form of a comma-separated list. A custom
4037 variable is a variable not normally known
4038 to <productname>PostgreSQL</productname> proper but used by some
4039 add-on module. Such variables must have names consisting of a class
4040 name, a dot, and a variable name. <varname>custom_variable_classes</>
4041 specifies all the class names in use in a particular installation.
4042 This parameter can only be set in the <filename>postgresql.conf</>
4043 file or on the server command line.
4051 The difficulty with setting custom variables in
4052 <filename>postgresql.conf</> is that the file must be read before add-on
4053 modules have been loaded, and so custom variables would ordinarily be
4054 rejected as unknown. When <varname>custom_variable_classes</> is set,
4055 the server will accept definitions of arbitrary variables within each
4056 specified class. These variables will be treated as placeholders and
4057 will have no function until the module that defines them is loaded. When a
4058 module for a specific class is loaded, it will add the proper variable
4059 definitions for its class name, convert any placeholder
4060 values according to those definitions, and issue warnings for any
4061 placeholders of its class that remain (which presumably would be
4062 misspelled configuration variables).
4066 Here is an example of what <filename>postgresql.conf</> might contain
4067 when using custom variables:
4070 custom_variable_classes = 'plr,plperl'
4071 plr.path = '/usr/lib/R'
4072 plperl.use_strict = true
4073 plruby.use_strict = true # generates error: unknown class name
4078 <sect1 id="runtime-config-developer">
4079 <title>Developer Options</title>
4082 The following parameters are intended for work on the
4083 <productname>PostgreSQL</productname> source, and in some cases
4084 to assist with recovery of severely damaged databases. There
4085 should be no reason to use them in a production database setup.
4086 As such, they have been excluded from the sample
4087 <filename>postgresql.conf</> file. Note that many of these
4088 parameters require special source compilation flags to work at all.
4092 <varlistentry id="guc-allow-system-table-mods" xreflabel="allow_system_table_mods">
4093 <term><varname>allow_system_table_mods</varname> (<type>boolean</type>)</term>
4095 <primary><varname>allow_system_table_mods</varname> configuration parameter</primary>
4099 Allows modification of the structure of system tables.
4100 This is used by <command>initdb</command>.
4101 This parameter can only be set at server start.
4106 <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
4107 <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
4109 <primary><varname>debug_assertions</> configuration parameter</primary>
4113 Turns on various assertion checks. This is a debugging aid. If
4114 you are experiencing strange problems or crashes you might want
4115 to turn this on, as it might expose programming mistakes. To use
4116 this parameter, the macro <symbol>USE_ASSERT_CHECKING</symbol>
4117 must be defined when <productname>PostgreSQL</productname> is
4118 built (accomplished by the <command>configure</command> option
4119 <option>--enable-cassert</option>). Note that
4120 <varname>debug_assertions</varname> defaults to <literal>on</>
4121 if <productname>PostgreSQL</productname> has been built with
4127 <varlistentry id="guc-ignore-system-indexes" xreflabel="ignore_system_indexes">
4128 <term><varname>ignore_system_indexes</varname> (<type>boolean</type>)</term>
4130 <primary><varname>ignore_system_indexes</varname> configuration parameter</primary>
4134 Ignore system indexes when reading system tables (but still
4135 update the indexes when modifying the tables). This is useful
4136 when recovering from damaged system indexes.
4137 This parameter cannot be changed after session start.
4142 <varlistentry id="guc-post-auth-delay" xreflabel="post_auth_delay">
4143 <term><varname>post_auth_delay</varname> (<type>integer</type>)</term>
4145 <primary><varname>post_auth_delay</> configuration parameter</primary>
4149 If nonzero, a delay of this many seconds occurs when a new
4150 server process is started, after it conducts the
4151 authentication procedure. This is intended to give an
4152 opportunity to attach to the server process with a debugger.
4153 This parameter cannot be changed after session start.
4158 <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
4159 <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
4161 <primary><varname>pre_auth_delay</> configuration parameter</primary>
4165 If nonzero, a delay of this many seconds occurs just after a
4166 new server process is forked, before it conducts the
4167 authentication procedure. This is intended to give an
4168 opportunity to attach to the server process with a debugger to
4169 trace down misbehavior in authentication.
4170 This parameter can only be set in the <filename>postgresql.conf</>
4171 file or on the server command line.
4176 <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
4177 <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
4179 <primary><varname>trace_notify</> configuration parameter</primary>
4183 Generates a great amount of debugging output for the
4184 <command>LISTEN</command> and <command>NOTIFY</command>
4185 commands. <xref linkend="guc-client-min-messages"> or
4186 <xref linkend="guc-log-min-messages"> must be
4187 <literal>DEBUG1</literal> or lower to send this output to the
4188 client or server log, respectively.
4193 <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
4194 <term><varname>trace_sort</varname> (<type>boolean</type>)</term>
4196 <primary><varname>trace_sort</> configuration parameter</primary>
4200 If on, emit information about resource usage during sort operations.
4201 This parameter is only available if the <symbol>TRACE_SORT</symbol> macro
4202 was defined when <productname>PostgreSQL</productname> was compiled.
4203 (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
4209 <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
4210 <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
4211 <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
4212 <term><varname>trace_lock_oidmin</varname> (<type>boolean</type>)</term>
4213 <term><varname>trace_lock_table</varname> (<type>boolean</type>)</term>
4214 <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
4215 <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
4218 Various other code tracing and debugging options.
4223 <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
4224 <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
4226 <primary><varname>wal_debug</> configuration parameter</primary>
4230 If on, emit WAL-related debugging output. This parameter is
4231 only available if the <symbol>WAL_DEBUG</symbol> macro was
4232 defined when <productname>PostgreSQL</productname> was
4238 <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
4239 <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
4241 <primary><varname>zero_damaged_pages</> configuration parameter</primary>
4245 Detection of a damaged page header normally causes
4246 <productname>PostgreSQL</> to report an error, aborting the current
4247 command. Setting <varname>zero_damaged_pages</> to on causes
4248 the system to instead report a warning, zero out the damaged page,
4249 and continue processing. This behavior <emphasis>will destroy data</>,
4250 namely all the rows on the damaged page. But it allows you to get
4251 past the error and retrieve rows from any undamaged pages that may
4252 be present in the table. So it is useful for recovering data if
4253 corruption has occurred due to hardware or software error. You should
4254 generally not set this on until you have given up hope of recovering
4255 data from the damaged page(s) of a table. The
4256 default setting is <literal>off</>, and it can only be changed
4263 <sect1 id="runtime-config-short">
4264 <title>Short Options</title>
4267 For convenience there are also single letter command-line option
4268 switches available for some parameters. They are described in
4269 <xref linkend="runtime-config-short-table">. Some of these
4270 options exist for historical reasons, and their presence as a
4271 single-letter option does not necessarily indicate an endorsement
4272 to use the option heavily.
4275 <table id="runtime-config-short-table">
4276 <title>Short option key</title>
4280 <entry>Short option</entry>
4281 <entry>Equivalent</entry>
4287 <entry><option>-A <replaceable>x</replaceable></option></entry>
4288 <entry><literal>debug_assertions = <replaceable>x</replaceable></></entry>
4291 <entry><option>-B <replaceable>x</replaceable></option></entry>
4292 <entry><literal>shared_buffers = <replaceable>x</replaceable></></entry>
4295 <entry><option>-d <replaceable>x</replaceable></option></entry>
4296 <entry><literal>log_min_messages = DEBUG<replaceable>x</replaceable></></entry>
4299 <entry><option>-e</option></entry>
4300 <entry><literal>datestyle = euro</></entry>
4304 <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
4305 <option>-fm</option>, <option>-fn</option>,
4306 <option>-fs</option>, <option>-ft</option>
4309 <literal>enable_bitmapscan = off</>,
4310 <literal>enable_hashjoin = off</>,
4311 <literal>enable_indexscan = off</>,
4312 <literal>enable_mergejoin = off</>,
4313 <literal>enable_nestloop = off</>,
4314 <literal>enable_seqscan = off</>,
4315 <literal>enable_tidscan = off</>
4319 <entry><option>-F</option></entry>
4320 <entry><literal>fsync = off</></entry>
4323 <entry><option>-h <replaceable>x</replaceable></option></entry>
4324 <entry><literal>listen_addresses = <replaceable>x</replaceable></></entry>
4327 <entry><option>-i</option></entry>
4328 <entry><literal>listen_addresses = '*'</></entry>
4331 <entry><option>-k <replaceable>x</replaceable></option></entry>
4332 <entry><literal>unix_socket_directory = <replaceable>x</replaceable></></entry>
4335 <entry><option>-l</option></entry>
4336 <entry><literal>ssl = on</></entry>
4339 <entry><option>-N <replaceable>x</replaceable></option></entry>
4340 <entry><literal>max_connections = <replaceable>x</replaceable></></entry>
4343 <entry><option>-O</option></entry>
4344 <entry><literal>allow_system_table_mods = on</></entry>
4347 <entry><option>-p <replaceable>x</replaceable></option></entry>
4348 <entry><literal>port = <replaceable>x</replaceable></></entry>
4351 <entry><option>-P</option></entry>
4352 <entry><literal>ignore_system_indexes = on</></entry>
4355 <entry><option>-s</option></entry>
4356 <entry><literal>log_statement_stats = on</></entry>
4359 <entry><option>-S <replaceable>x</replaceable></option></entry>
4360 <entry><literal>work_mem = <replaceable>x</replaceable></></entry>
4363 <entry><option>-tpa</option>, <option>-tpl</option>, <option>-te</option></entry>
4364 <entry><literal>log_parser_stats = on</>,
4365 <literal>log_planner_stats = on</>,
4366 <literal>log_executor_stats = on</></entry>
4369 <entry><option>-W <replaceable>x</replaceable></option></entry>
4370 <entry><literal>post_auth_delay = <replaceable>x</replaceable></></entry>
4379 <!-- Keep this comment at the end of the file
4384 sgml-minimize-attributes:nil
4385 sgml-always-quote-attributes:t
4388 sgml-parent-document:nil
4389 sgml-default-dtd-file:"./reference.ced"
4390 sgml-exposed-tags:nil
4391 sgml-local-catalogs:("/usr/lib/sgml/catalog")
4392 sgml-local-ecat-files:nil