Wiki / GUC Parameters
GUC Parameters
Compare versionsConfiguration parameters, documented behavior, and measured default history.
Reading PG 18current·24 versions documented, 7.4 to 20
-
If set to on, ACST, CST, EST, and SAT are interpreted as Australian time zones rather than as North/South American time zones and Saturday.
-
Sets the output format for values of type bytea. Valid values are hex (the default) and escape (the traditional PostgreSQL format). See Section 8.4 for more information. The bytea type always accepts both formats on input, regardless of this setting.
-
This parameter is normally on. When set to off, it disables validation of the routine body string during CREATE FUNCTION and CREATE PROCEDURE. Disabling validation avoids side effects of the validation process, in particular preventing false positiv…
-
Sets the client-side encoding (character set). The default is to use the database encoding. The character sets supported by the PostgreSQL server are described in Section 23.3.1.
-
Controls which message levels are sent to the client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, and ERROR. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. The …
-
If a user who has CREATEROLE but not SUPERUSER creates a role, and if this is set to a non-empty value, the newly-created role will be granted to the creating user with the options specified. The value must be set, inherit, or a comma-separated list…
-
Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or G…
-
This parameter specifies the default table access method to use when creating tables or materialized views if the CREATE command does not explicitly specify an access method, or when SELECT ... INTO is used, which does not allow specifying a table a…
-
This variable specifies the default tablespace in which to create objects (tables and indexes) when a CREATE command does not explicitly specify a tablespace. The value is either the name of a tablespace, or an empty string to specify using the defa…
-
Selects the text search configuration that is used by those variants of the text search functions that do not have an explicit argument specifying the configuration. See Chapter 12 for further information. The built-in default is pg_catalog.simple, …
-
This variable sets the default TOAST compression method for values of compressible columns. (This can be overridden for individual columns by setting the COMPRESSION column option in CREATE TABLE or ALTER TABLE.) The supported compression methods ar…
-
When running at the serializable isolation level, a deferrable read-only SQL transaction may be delayed before it is allowed to proceed. However, once it begins executing it does not incur any of the overhead required to ensure serializability; so s…
-
Each SQL transaction has an isolation level, which can be either “read uncommitted”, “read committed”, “repeatable read”, or “serializable”. This parameter controls the default isolation level of each new transaction. The default is “read committed”…
-
A read-only SQL transaction cannot alter non-temporary tables. This parameter controls the default read-only status of each new transaction. The default is off (read/write). Consult SET TRANSACTION for more information.
-
If a dynamically loadable module needs to be opened and the file name specified in the CREATE FUNCTION or LOAD command does not have a directory component (i.e., the name does not contain a slash), the system will search this path for the required f…
-
Allow temporarily disabling execution of event triggers in order to troubleshoot and repair faulty event triggers. All event triggers will be disabled by setting it to false. Setting the value to true allows all event triggers to fire, this is the d…
-
Determines whether EXPLAIN VERBOSE uses the indented or non-indented format for displaying detailed query-tree dumps.
-
A path to search for extensions, specifically extension control files (name.control). The remaining extension script and secondary control files are then loaded from the same directory where the primary control file was found. See Section 36.17.1 fo…
-
This parameter adjusts the number of digits used for textual output of floating-point values, including float4, float8, and geometric data types. If the value is 1 (the default) or above, float values are output in shortest-precise format; see Secti…
-
Soft upper limit of the size of the set returned by GIN index scans. For more information see Section 65.4.5.
-
Sets the maximum size of a GIN index's pending list, which is used when fastupdate is enabled. If the list grows larger than this maximum size, it is cleaned up by moving the entries in it to the index's main GIN data structure in bulk. If this valu…
-
When ICU locale validation problems are encountered, controls which message level is used to report the problem. Valid values are DISABLED, DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, and LOG. If set to DISABLED, does not r…
-
Terminate any session that has been idle (that is, waiting for a client query) within an open transaction for longer than the specified amount of time. If this value is specified without units, it is taken as milliseconds. A value of zero (the defau…
-
Terminate any session that has been idle (that is, waiting for a client query), but not within an open transaction, for longer than the specified amount of time. If this value is specified without units, it is taken as milliseconds. A value of zero …
-
Sets the display format for interval values. The value sql_standard will produce output matching SQL standard interval literals. The value postgres (which is the default) will produce output matching PostgreSQL releases prior to 8.4 when the DateSty…
-
This variable is the name of the JIT provider library to be used (see Section 30.4.2). The default is llvmjit. This parameter can only be set at server start. If set to a non-existent library, JIT will not be available, but no error will be raised. …
-
Sets the language in which messages are displayed. Acceptable values are system-dependent; see Section 23.1 for more information. If this variable is set to the empty string (which is the default) then the value is inherited from the execution envir…
-
Sets the locale to use for formatting monetary amounts, for example with the to_char family of functions. Acceptable values are system-dependent; see Section 23.1 for more information. If this variable is set to the empty string (which is the defaul…
-
Sets the locale to use for formatting numbers, for example with the to_char family of functions. Acceptable values are system-dependent; see Section 23.1 for more information. If this variable is set to the empty string (which is the default) then t…
-
Sets the locale to use for formatting dates and times, for example with the to_char family of functions. Acceptable values are system-dependent; see Section 23.1 for more information. If this variable is set to the empty string (which is the default…
-
This variable specifies one or more shared libraries that are to be preloaded at connection start. It contains a comma-separated list of library names, where each name is interpreted as for the LOAD command. Whitespace between entries is ignored; su…
-
Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object. The time limit applies separately to each lock acquisition attempt. The limit applies both t…
-
Sets the maximum expression nesting depth of the parser.
-
Set relation kinds for which access to non-system relations is prohibited. The value takes the form of a comma-separated list of relation kinds. Currently, the supported relation kinds are view and foreign-table.
-
This variable controls whether to raise an error in lieu of applying a row security policy. When set to on, policies apply normally. When set to off, queries fail which would otherwise apply at least one policy. The default is on. Change to off wher…
-
This variable specifies the order in which schemas are searched when an object (table, data type, function, etc.) is referenced by a simple name with no schema specified. When there are objects of identical names in different schemas, the one found …
-
This variable specifies one or more shared libraries that are to be preloaded at connection start. It contains a comma-separated list of library names, where each name is interpreted as for the LOAD command. Whitespace between entries is ignored; su…
-
Controls firing of replication-related triggers and rules for the current session. Possible values are origin (the default), replica and local. Setting this parameter results in discarding any previously cached query plans. Only superusers and users…
-
This variable specifies one or more shared libraries to be preloaded at server start. It contains a comma-separated list of library names, where each name is interpreted as for the LOAD command. Whitespace between entries is ignored; surround a libr…
-
Abort any statement that takes more than the specified amount of time. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. If this value is specified without units, it is taken as milliseconds. A va…
-
This variable specifies tablespaces in which to create temporary objects (temp tables and indexes on temp tables) when a CREATE command does not explicitly specify a tablespace. Temporary files for purposes such as sorting large data sets are also c…
-
Sets the time zone for displaying and interpreting time stamps. The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 for more…
-
Sets the collection of additional time zone abbreviations that will be accepted by the server for datetime input (beyond any abbreviations defined by the current TimeZone setting). The default is 'Default', which is a collection that works in most o…
-
This parameter reflects the current transaction's deferrability status. At the beginning of each transaction, it is set to the current value of default_transaction_deferrable. Any subsequent attempt to change it is equivalent to a SET TRANSACTION co…
-
This parameter reflects the current transaction's isolation level. At the beginning of each transaction, it is set to the current value of default_transaction_isolation. Any subsequent attempt to change it is equivalent to a SET TRANSACTION command.
-
This parameter reflects the current transaction's read-only status. At the beginning of each transaction, it is set to the current value of default_transaction_read_only. Any subsequent attempt to change it is equivalent to a SET TRANSACTION command.
-
Terminate any session that spans longer than the specified amount of time in a transaction. The limit applies both to explicit transactions (started with BEGIN) and to an implicitly started transaction corresponding to a single statement. If this va…
-
Specifies the fraction of the total number of heap tuples counted in the previous statistics collection that can be inserted without incurring an index scan at the VACUUM cleanup stage.
-
Sets how binary values are to be encoded in XML. This applies for example when bytea values are converted to XML by the functions xmlelement or xmlforest. Possible values are base64 and hex, which are both defined in the XML Schema standard. The def…
-
Sets whether DOCUMENT or CONTENT is implicit when converting between XML and character string values. See Section 8.13 for a description of this. Valid values are DOCUMENT and CONTENT. The default is CONTENT. According to the SQL standard, the comma…
-
Maximum amount of time allowed to complete client authentication. If a would-be client has not completed the authentication protocol in this much time, the server closes the connection. This prevents hung clients from occupying a connection indefini…
-
Enables advertising the server's existence via Bonjour. The default is off. This parameter can only be set at server start.
-
Specifies the Bonjour service name. The computer name is used if this parameter is set to the empty string '' (which is the default). This parameter is ignored if the server was not compiled with Bonjour support. This parameter can only be set at se…
-
Sets the time interval between optional checks that the client is still connected, while running queries. The check is performed by polling the socket, and allows long running queries to be aborted sooner if the kernel reports that the connection is…
-
This parameter enables per-database user names.
-
Sets whether GSSAPI delegation should be accepted from the client. The default is off meaning credentials from the client will not be accepted. Changing this to on will make the server accept credentials delegated to it from the client. This paramet…
-
Sets whether GSSAPI user names should be treated case-insensitively. The default is off (case sensitive). This parameter can only be set in the postgresql.conf file or on the server command line.
-
Sets the realm to match Kerberos, GSSAPI and SSPI user names against.
-
Sets the host name part of the service principal.
-
Sets the location of the server's Kerberos key file. The default is FILE:/usr/local/pgsql/etc/krb5.keytab (where the directory part is whatever was specified as sysconfdir at build time; use pg_config --sysconfdir to determine that). If this paramet…
-
Sets the Kerberos service name.
-
Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all availabl…
-
Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at …
-
Controls whether a WARNING about MD5 password deprecation is produced when a CREATE ROLE or ALTER ROLE statement sets an MD5-encrypted password. The default value is on.
-
The library/libraries to use for validating OAuth connection tokens. If only one validator library is provided, it will be used by default for any OAuth connections; otherwise, all oauth HBA entries must explicitly set a validator chosen from this l…
-
When a password is specified in CREATE ROLE or ALTER ROLE, this parameter determines the algorithm to use to encrypt the password. Possible values are scram-sha-256, which will encrypt the password with SCRAM-SHA-256, and md5, which stores the passw…
-
When this parameter is greater than zero, the server will emit a WARNING upon successful password authentication if less than this amount of time remains until the authenticated role's password expires.
-
The TCP port the server listens on; 5432 by default. Note that the same port number is used for all IP addresses the server listens on. This parameter can only be set at server start.
-
Specifies the Rendezvous broadcast name.
-
Determines the number of connection “slots” that are reserved for connections by roles with privileges of the pg_use_reserved_connections role. Whenever the number of free connection slots is greater than superuser_reserved_connections but less than…
-
The number of computational iterations to be performed when encrypting a password using SCRAM-SHA-256. The default is 4096. A higher number of iterations provides additional protection against brute-force attacks on stored passwords, but makes authe…
-
Enables SSL connections. This parameter can only be set in the postgresql.conf file or on the server command line. The default is off.
-
Specifies the name of the file containing the SSL server certificate authority (CA). Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is empty, m…
-
Specifies the name of the file containing the SSL server certificate. Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is server.crt.
-
Specifies a list of SSL ciphers that are allowed by connections using TLS version 1.2 and lower, see ssl_tls13_ciphers for TLS version 1.3 connections. See the ciphers manual page in the OpenSSL package for the syntax of this setting and a list of s…
-
Specifies the name of the directory containing the SSL client certificate revocation list (CRL). Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default…
-
Specifies the name of the file containing the SSL client certificate revocation list (CRL). Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is e…
-
Specifies the name of the file containing Diffie-Hellman parameters used for so-called ephemeral DH family of SSL ciphers. The default is empty, in which case compiled-in default DH parameters used. Using custom DH parameters reduces the exposure if…
-
Specifies the name of the curve to use in ECDH key exchange.
-
Specifies the named group to use for TLS key exchange. It needs to be supported by all clients that connect. Multiple groups can be specified by using a colon-separated list. It does not need to match the key type used by the server certificate. Thi…
-
Specifies the name of the file containing the SSL server private key. Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is server.key.
-
Sets the maximum SSL/TLS protocol version to use. Valid values are as for ssl_min_protocol_version, with addition of an empty string, which allows any protocol version. The default is to allow any version. Setting the maximum protocol version is mai…
-
Sets the minimum SSL/TLS protocol version to use. Valid values are currently: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3. Older versions of the OpenSSL library do not support all values; an error will be raised if an unsupported setting is chosen. Protocol ve…
-
Sets an external command to be invoked when a passphrase for decrypting an SSL file such as a private key needs to be obtained. By default, this parameter is empty, which means the built-in prompting mechanism is used. The command must print the pas…
-
This parameter determines whether the passphrase command set by ssl_passphrase_command will also be called during a configuration reload if a key file needs a passphrase. If this parameter is off (the default), then ssl_passphrase_command will be ig…
-
Specifies whether to use the server's SSL cipher preferences, rather than the client's. This parameter can only be set in the postgresql.conf file or on the server command line. The default is on. PostgreSQL versions before 9.4 do not have this sett…
-
Specifies how much data can flow over an SSL-encrypted connection before renegotiation of the session keys will take place.
-
Enables SNI configuration for SSL connections.
-
Specifies a list of cipher suites that are allowed by connections using TLS version 1.3. Multiple cipher suites can be specified by using a colon separated list. If left blank, the default set of cipher suites in OpenSSL will be used. This parameter…
-
Determines the number of connection “slots” that are reserved for connections by PostgreSQL superusers. At most max_connections connections can ever be active simultaneously. Whenever the number of active concurrent connections is at least max_conne…
-
Specifies the number of TCP keepalive messages that can be lost before the server's connection to the client is considered dead. A value of 0 (the default) selects the operating system's default. This parameter is supported only on systems that supp…
-
Specifies the amount of time with no network activity after which the operating system should send a TCP keepalive message to the client. If this value is specified without units, it is taken as seconds. A value of 0 (the default) selects the operat…
-
Specifies the amount of time after which a TCP keepalive message that has not been acknowledged by the client should be retransmitted. If this value is specified without units, it is taken as seconds. A value of 0 (the default) selects the operating…
-
Specifies the amount of time that transmitted data may remain unacknowledged before the TCP connection is forcibly closed. If this value is specified without units, it is taken as milliseconds. A value of 0 (the default) selects the operating system…
-
If this is true, then the server will accept TCP/IP connections.
-
Specifies the directory of the Unix-domain socket(s) on which the server is to listen for connections from client applications. Multiple sockets can be created by listing multiple directories separated by commas. Whitespace between entries is ignore…
-
Specifies the directory of the Unix-domain socket on which the server is to listen for connections from client applications.
-
Sets the owning group of the Unix-domain socket(s). (The owning user of the sockets is always the user that starts the server.) In combination with the parameter unix_socket_permissions this can be used as an additional access control mechanism for …
-
Sets the access permissions of the Unix-domain socket(s). Unix-domain sockets use the usual Unix file system permission set. The parameter value is expected to be a numeric mode specified in the format accepted by the chmod and umask system calls. (…
-
Specifies the host name or IP address on which the server is to listen for connections from client applications.
-
This variable specifies one or several class names to be used for custom variables, in the form of a comma-separated list.
-
Allows tablespaces to be created as directories inside pg_tblspc, when an empty location string is provided to the CREATE TABLESPACE command. This is intended to allow testing replication scenarios where primary and standby servers are running on th…
-
Allows modification of the structure of system tables as well as certain other risky actions on system tables. This is otherwise not allowed even for superusers. Ill-advised use of this setting can cause irretrievable data loss or seriously corrupt …
-
This parameter contains a comma-separated list of C function names. If an error is raised and the name of the internal C function where the error happens matches a value in the list, then a backtrace is written to the server log together with the er…
-
Enabling this forces all parse and plan trees to be passed through copyObject(), to facilitate catching errors and omissions in copyObject(). The default is off. This parameter is only available when DEBUG_NODE_TESTS_ENABLED was defined at compile t…
-
If set, dumps information about all current locks when a deadlock timeout occurs. This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
-
When set to 1, each system catalog cache entry is invalidated at the first possible opportunity, whether or not anything that would render it invalid really occurred. Caching of system catalogs is effectively disabled as a result, so the server will…
-
Ask the kernel to minimize caching effects for relation data and WAL files using O_DIRECT (most Unix-like systems), F_NOCACHE (macOS) or FILE_FLAG_NO_BUFFERING (Windows). May be set to an empty string (the default) to disable use of direct I/O, or a…
-
The allowed values are buffered and immediate. The default is buffered. This parameter is intended to be used to test logical decoding and replication of large transactions. The effect of debug_logical_replication_streaming is different for the publ…
-
Allows the use of parallel queries for testing purposes even in cases where no performance benefit is expected. The allowed values of debug_parallel_query are off (use parallel mode only when it is expected to improve performance), on (force paralle…
-
Enabling this forces all raw parse trees for DML statements to be scanned by raw_expression_tree_walker(), to facilitate catching errors and omissions in that function. The default is off. This parameter is only available when DEBUG_NODE_TESTS_ENABL…
-
Number of seconds between buffer freelist reports.
-
Enabling this forces all parse and plan trees to be passed through outfuncs.c/readfuncs.c, to facilitate catching errors and omissions in those modules. The default is off. This parameter is only available when DEBUG_NODE_TESTS_ENABLED was defined a…
-
Allows the use of parallel queries for testing purposes even in cases where no performance benefit is expected.
-
Only has effect if data checksums are enabled. Detection of a checksum failure during a read normally causes PostgreSQL to report an error, aborting the current transaction. Setting ignore_checksum_failure to on causes the system to ignore the failu…
-
If set to off (the default), detection of WAL records having references to invalid pages during recovery causes PostgreSQL to raise a PANIC-level error, aborting the recovery. Setting ignore_invalid_pages to on causes the system to ignore invalid pa…
-
Ignore system indexes when reading system tables (but still update the indexes when modifying the tables). This is useful when recovering from damaged system indexes. This parameter cannot be changed after session start.
-
If LLVM has the required functionality, register generated functions with GDB. This makes debugging easier. The default setting is off. Only superusers and users with the appropriate SET privilege can change this parameter at session start, and it c…
-
Writes the generated LLVM IR out to the file system, inside data_directory. This is only useful for working on the internals of the JIT implementation. The default setting is off. Only superusers and users with the appropriate SET privilege can chan…
-
Determines whether expressions are JIT compiled, when JIT compilation is activated (see Section 30.2). The default is on.
-
If LLVM has the required functionality, emit the data needed to allow perf to profile functions generated by JIT. This writes out files to ~/.debug/jit/; the user is responsible for performing cleanup when desired. The default setting is off. Only s…
-
Determines whether tuple deforming is JIT compiled, when JIT compilation is activated (see Section 30.2). The default is on.
-
If set, logs system resource usage statistics (memory and CPU) on various B-tree operations. This parameter is only available if the BTREE_BUILD_STATS macro was defined when PostgreSQL was compiled.
-
The amount of time to delay when a new server process is started, after it conducts the authentication procedure. This is intended to give developers an opportunity to attach to the server process with a debugger. If this value is specified without …
-
The amount of time to delay just after a new server process is forked, before it conducts the authentication procedure. This is intended to give developers an opportunity to attach to the server process with a debugger to trace down misbehavior in a…
-
When set to on, which is the default, PostgreSQL will automatically remove temporary files after a backend crash. If disabled, the files will be retained and may be used for debugging, for example. Repeated crashes may however result in accumulation…
-
By default, after a backend crash the postmaster will stop remaining child processes by sending them SIGQUIT signals, which permits them to exit more-or-less gracefully. When this option is set to on, SIGABRT is sent instead. That normally results i…
-
By default, after attempting to stop a child process with SIGQUIT, the postmaster will wait five seconds and then send SIGKILL to force immediate termination. When this option is set to on, SIGABRT is sent instead of SIGKILL. That normally results i…
-
Logs details of pre-authentication connection handshake.
-
If set, do not trace locks for tables below this OID (used to avoid output on system tables). This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
-
Unconditionally trace locks on this table (OID). This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
-
If on, emit information about lock usage. Information dumped includes the type of lock operation, the type of lock and the unique identifier of the object being locked or unlocked. Also included are bit masks for the lock types already granted on th…
-
If on, emit information about lightweight lock usage. Lightweight locks are intended primarily to provide mutual exclusion of access to shared-memory data structures. This parameter is only available if the LOCK_DEBUG macro was defined when PostgreS…
-
Generates a great amount of debugging output for the LISTEN and NOTIFY commands. client_min_messages or log_min_messages must be DEBUG1 or lower to send this output to the client or server logs, respectively.
-
Enables logging of recovery-related debugging output that otherwise would not be logged.
-
If on, emit information about resource usage during sort operations.
-
If on, emit information about user lock usage. Output is the same as for trace_locks, only for advisory locks. This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
-
This parameter is intended to be used to check for bugs in the WAL redo routines. When enabled, full-page images of any buffers modified in conjunction with the WAL record are added to the record. If the record is subsequently replayed, the system w…
-
If on, emit WAL-related debugging output. This parameter is only available if the WAL_DEBUG macro was defined when PostgreSQL was compiled.
-
Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting zero_damaged_pages to on causes the system to instead report a warning, zero out the damaged page in memory, and continue pro…
-
When set to off, which is the default, PostgreSQL will raise a PANIC-level error on failure to flush modified data files to the file system. This causes the database server to crash. This parameter can only be set at server start. On some operating …
-
If on, any error will terminate the current session. By default, this is set to off, so that only FATAL errors will terminate the session.
-
When set to fsync, which is the default, PostgreSQL will recursively open and synchronize all files in the data directory before crash recovery begins. The search for files will follow symbolic links for the WAL directory and each configured tablesp…
-
When set to on, which is the default, PostgreSQL will automatically reinitialize after a backend crash. Leaving this value set to on is normally the best way to maximize the availability of the database. However, in some circumstances, such as when …
-
Specifies the main server configuration file (customarily called postgresql.conf). This parameter can only be set on the postgres command line.
-
Specifies the directory to use for data storage. This parameter can only be set at server start.
-
Path to prepend for extension loading.
-
Specifies the name of an additional process-ID (PID) file that the server should create for use by server administration programs. This parameter can only be set at server start.
-
Specifies the configuration file for host-based authentication (customarily called pg_hba.conf). This parameter can only be set at server start.
-
Specifies the configuration file for host-based SSL configuration (customarily called pg_hosts.conf).
-
Specifies the configuration file for user name mapping (customarily called pg_ident.conf). This parameter can only be set at server start. See also Section 20.2.
-
This is the amount of time to wait on a lock before checking to see if there is a deadlock condition. The check for deadlock is relatively expensive, so the server doesn't run it every time it waits for a lock. We optimistically assume that deadlock…
-
The shared lock table has space for max_locks_per_transaction objects (e.g., tables) per server process or prepared transaction; hence, no more than this many distinct objects can be locked at any one time. This parameter limits the average number o…
-
This controls how many rows on a single page can be predicate-locked before the lock is promoted to covering the whole page. The default is 2. This parameter can only be set in the postgresql.conf file or on the server command line.
-
This controls how many pages or tuples of a single relation can be predicate-locked before the lock is promoted to covering the whole relation. Values greater than or equal to zero mean an absolute limit, while negative values mean max_pred_locks_pe…
-
The shared predicate lock table has space for max_pred_locks_per_transaction objects (e.g., tables) per server process or prepared transaction; hence, no more than this many distinct objects can be locked at any one time. This parameter limits the a…
-
Reports the size of a disk block. It is determined by the value of BLCKSZ when building the server. The default value is 8192 bytes. The meaning of some configuration variables (such as shared_buffers) is influenced by block_size. See Section 19.4 f…
-
Reports whether data checksums are enabled for this cluster. See -k for more information.
-
On Unix systems this parameter reports the permissions the data directory (defined by data_directory) had at server startup. (On Microsoft Windows this parameter will always display 0700.) See the initdb -g option for more information.
-
Reports whether PostgreSQL has been built with assertions enabled. That is the case if the macro USE_ASSERT_CHECKING is defined when PostgreSQL is built (accomplished e.g., by the configure option --enable-cassert). By default PostgreSQL is built wi…
-
Reports whether PostgreSQL has been built with EXEC_BACKEND enabled.
-
Reports the actual WAL logging level currently in effect in the system.
-
Reports the state of huge pages in the current instance: on, off, or unknown (if displayed with postgres -C). This parameter is useful to determine whether allocation of huge pages was successful under huge_pages=try. See huge_pages for more informa…
-
Reports whether the server is currently in hot standby mode. When this is on, all transactions are forced to be read-only. Within a session, this can change only if the server is promoted to be primary. See Section 26.4 for more information.
-
Reports whether PostgreSQL was built with support for 64-bit-integer dates and times. As of PostgreSQL 10, this is always on.
-
Reports the locale in which sorting of textual data is done.
-
Reports the locale that determines character classifications.
-
Reports the maximum number of function arguments. It is determined by the value of FUNC_MAX_ARGS when building the server. The default value is 100 arguments.
-
Reports the maximum identifier length. It is determined as one less than the value of NAMEDATALEN when building the server. The default value of NAMEDATALEN is 64; therefore the default max_identifier_length is 63 bytes, which can be less than 63 ch…
-
Reports the maximum number of index keys. It is determined by the value of INDEX_MAX_KEYS when building the server. The default value is 32 keys.
-
Reports the number of semaphores that are needed for the server based on the configured number of allowed connections (max_connections), allowed autovacuum worker processes (autovacuum_max_workers), allowed WAL sender processes (max_wal_senders), al…
-
Reports the number of blocks (pages) that can be stored within a file segment. It is determined by the value of RELSEG_SIZE when building the server. The maximum size of a segment file in bytes is equal to segment_size multiplied by block_size; by d…
-
Reports the database encoding (character set). It is determined when the database is created. Ordinarily, clients need only be concerned with the value of client_encoding.
-
Reports the version number of the server. It is determined by the value of PG_VERSION when building the server.
-
Reports the version number of the server as an integer. It is determined by the value of PG_VERSION_NUM when building the server.
-
Reports the size of the main shared memory area, rounded up to the nearest megabyte.
-
Reports the number of huge pages that are needed for the main shared memory area based on the specified huge_page_size. If huge pages are not supported, this will be -1. This setting is supported only on Linux. It is always set to -1 on other platfo…
-
Reports the name of the SSL library that this PostgreSQL server was built with (even if SSL is not currently configured or in use on this instance), for example OpenSSL, or an empty string if none.
-
Reports the size of a WAL disk block. It is determined by the value of XLOG_BLCKSZ when building the server. The default value is 8192 bytes.
-
Reports the size of write ahead log segments. The default value is 16MB. See Section 28.5 for more information.
-
Controls the query planner's use of table constraints to optimize queries. The allowed values of constraint_exclusion are on (examine constraints for all tables), off (never examine constraints), and partition (examine constraints only for inheritan…
-
Sets the planner's estimate of the cost of processing each index entry during an index scan. The default is 0.005.
-
Sets the planner's estimate of the cost of processing each operator or function executed during a query. The default is 0.0025.
-
Sets the planner's estimate of the cost of processing each row during a query. The default is 0.01.
-
Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved. The default is 0.1. Smaller values of this setting bias the planner towards using “fast start” plans for cursors, which will retrieve the first few rows quickly w…
-
Sets the default statistics target for table columns without a column-specific target set via ALTER TABLE SET STATISTICS. Larger values increase the time needed to do ANALYZE, but might improve the quality of the planner's estimates. The default is …
-
Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value …
-
Enables or disables the query planner's use of async-aware append plan types. The default is on.
-
Enables or disables the query planner's use of bitmap-scan plan types. The default is on.
-
Enables or disables the query planner's ability to reorder DISTINCT keys to match the input path's pathkeys. The default is on.
-
Enables or disables the query planner's ability to partially push aggregation past a join, and finalize it once all the relations are joined.
-
Enables or disables the query planner's use of gather merge plan types. The default is on.
-
Controls if the query planner will produce a plan which will provide GROUP BY keys sorted in the order of keys of a child node of the plan, such as an index scan. When disabled, the query planner will produce a plan with GROUP BY keys only sorted to…
-
Enables or disables the query planner's use of sort-based grouping and aggregation plan types.
-
Enables or disables the query planner's use of hashed aggregation plan types. The default is on.
-
Enables or disables the query planner's use of hash-join plan types. The default is on.
-
Enables or disables the query planner's use of incremental sort steps. The default is on.
-
Enables or disables the query planner's use of index-only-scan plan types (see Section 11.9). The default is on. The enable_indexscan setting must also be enabled to have the query planner consider index-only-scans.
-
Enables or disables the query planner's use of index-scan and index-only-scan plan types. The default is on. Also see enable_indexonlyscan.
-
Enables or disables the query planner's use of materialization. It is impossible to suppress materialization entirely, but turning this variable off prevents the planner from inserting materialize nodes except in cases where it is required for corre…
-
Enables or disables the query planner's use of memoize plans for caching results from parameterized scans inside nested-loop joins. This plan type allows scans to the underlying plans to be skipped when the results for the current parameters are alr…
-
Enables or disables the query planner's use of merge-join plan types. The default is on.
-
Enables or disables the query planner's use of nested-loop join plans. It is impossible to suppress nested-loop joins entirely, but turning this variable off discourages the planner from using one if there are other methods available. The default is…
-
Enables or disables the query planner's use of parallel-aware append plan types. The default is on.
-
Enables or disables the query planner's use of hash-join plan types with parallel hash. Has no effect if hash-join plans are not also enabled. The default is on.
-
Enables or disables the query planner's ability to eliminate a partitioned table's partitions from query plans. This also controls the planner's ability to generate query plans which allow the query executor to remove (ignore) partitions during quer…
-
Enables or disables the query planner's use of partitionwise grouping or aggregation, which allows grouping or aggregation on partitioned tables to be performed separately for each partition. If the GROUP BY clause does not include the partition key…
-
Enables or disables the query planner's use of partitionwise join, which allows a join between partitioned tables to be performed by joining the matching partitions. Partitionwise join currently applies only when the join conditions include all the …
-
Controls if the query planner will produce a plan which will provide rows which are presorted in the order required for the query's ORDER BY / DISTINCT aggregate functions. When disabled, the query planner will produce a plan which will always requi…
-
Enables or disables the query planner's optimization which analyses the query tree and replaces self joins with semantically equivalent single scans. Takes into consideration only plain tables. The default is on.
-
Enables or disables the query planner's use of sequential scan plan types. It is impossible to suppress sequential scans entirely, but turning this variable off discourages the planner from using one if there are other methods available. The default…
-
Enables or disables the query planner's use of explicit sort steps. It is impossible to suppress explicit sorts entirely, but turning this variable off discourages the planner from using one if there are other methods available. The default is on.
-
Enables or disables the query planner's use of TID scan plan types. The default is on.
-
The planner will merge sub-queries into upper queries if the resulting FROM list would have no more than this many items. Smaller values reduce planning time but might yield inferior query plans. The default is eight. For more information see Sectio…
-
Enables or disables genetic query optimization. This is on by default. It is usually best not to turn it off in production; the geqo_threshold variable provides more granular control of GEQO.
-
Controls the trade-off between planning time and query plan quality in GEQO. This variable must be an integer in the range from 1 to 10. The default value is five. Larger values increase the time spent doing query planning, but also increase the lik…
-
Controls the number of generations used by GEQO, that is the number of iterations of the algorithm. It must be at least one, and useful values are in the same range as the pool size. If it is set to zero (the default setting) then a suitable value i…
-
Controls the pool size used by GEQO, that is the number of individuals in the genetic population. It must be at least two, and useful values are typically 100 to 1000. If it is set to zero (the default setting) then a suitable value is chosen based …
-
Controls the initial value of the random number generator used by GEQO to select random paths through the join order search space. The value can range from zero (the default) to one. Varying the value changes the set of join paths explored, and may …
-
Controls the selection bias used by GEQO. The selection bias is the selective pressure within the population. Values can be from 1.50 to 2.00; the latter is the default.
-
Use genetic query optimization to plan queries with at least this many FROM items involved. (Note that a FULL OUTER JOIN construct counts as only one FROM item.) The default is 12. For simpler queries it is usually best to use the regular, exhaustiv…
-
Determines whether JIT compilation may be used by PostgreSQL, if available (see Chapter 30). The default is on.
-
Sets the query cost above which JIT compilation is activated, if enabled (see Chapter 30). Performing JIT costs planning time but can accelerate query execution. Setting this to -1 disables JIT compilation. The default is 100000.
-
Sets the query cost above which JIT compilation attempts to inline functions and operators. Inlining adds planning time, but can improve execution speed. It is not meaningful to set this to less than jit_above_cost. Setting this to -1 disables inlin…
-
Sets the query cost above which JIT compilation applies expensive optimizations. Such optimization adds planning time, but can improve execution speed. It is not meaningful to set this to less than jit_above_cost, and it is unlikely to be beneficial…
-
The planner will rewrite explicit JOIN constructs (except FULL JOINs) into lists of FROM items whenever a list of no more than this many items would result. Smaller values reduce planning time but might yield inferior query plans. By default, this v…
-
Sets the minimum average group size required to consider applying eager aggregation.
-
Sets the minimum amount of index data that must be scanned in order for a parallel scan to be considered. Note that a parallel index scan typically won't touch the entire index; it is the number of pages which the planner believes will actually be t…
-
Sets the minimum size of relations to be considered for parallel scan.
-
Sets the minimum amount of table data that must be scanned in order for a parallel scan to be considered. For a parallel sequential scan, the amount of table data scanned is always equal to the size of the table, but when indexes are used the amount…
-
Sets the planner's estimate of the cost of launching parallel worker processes. The default is 1000.
-
Sets the planner's estimate of the cost of transferring one tuple from a parallel worker process to another process. The default is 0.1.
-
Prepared statements (either explicitly prepared or implicitly generated, for example by PL/pgSQL) can be executed using custom or generic plans. Custom plans are made afresh for each execution using its specific set of parameter values, while generi…
-
Sets the planner's estimate of the cost of a non-sequentially-fetched disk page. The default is 4.0. This value can be overridden for tables and indexes in a particular tablespace by setting the tablespace parameter of the same name (see ALTER TABLE…
-
Sets the planner's estimate of the average size of the working table of a recursive query, as a multiple of the estimated size of the initial non-recursive term of the query. This helps the planner choose the most appropriate method for joining the …
-
Sets the planner's estimate of the cost of a disk page fetch that is part of a series of sequential fetches. The default is 1.0. This value can be overridden for tables and indexes in a particular tablespace by setting the tablespace parameter of th…
-
Specifies whether or not you can connect and run queries during recovery, as described in Section 26.4. The default value is on. This parameter can only be set at server start. It only has effect during archive recovery or in standby mode.
-
Specifies whether or not a hot standby will send feedback to the primary or upstream standby about queries currently executing on the standby. This parameter can be used to eliminate query cancels caused by cleanup records, but can cause database bl…
-
Invalidate replication slots that have remained inactive (not used by a replication connection) for longer than this duration. If this value is specified without units, it is taken as seconds. A value of zero (the default) disables the idle timeout …
-
Specifies how many replication origins (see Chapter 48) can be tracked simultaneously, effectively limiting how many logical replication subscriptions can be created on the server. Setting it to a lower value than the current number of tracked repli…
-
Specifies maximum number of logical replication workers. This includes leader apply workers, parallel apply workers, and table synchronization workers. Logical replication workers are taken from the pool defined by max_worker_processes. The default …
-
Maximum number of parallel apply workers per subscription. This parameter controls the amount of parallelism for streaming of in-progress transactions with subscription parameter streaming = parallel. The parallel apply workers are taken from the po…
-
Specifies the maximum number of replication slots for use of the REPACK command.
-
Specifies the maximum number of replication slots (see Section 26.2.6) that the server can support. The default is 10. This parameter can only be set at server start. Setting it to a lower value than the number of currently existing replication slot…
-
Specify the maximum size of WAL files that replication slots are allowed to retain in the pg_wal directory at checkpoint time. If max_slot_wal_keep_size is -1 (the default), replication slots may retain an unlimited amount of WAL files. Otherwise, i…
-
When hot standby is active, this parameter determines how long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries, as described in Section 26.4.2. max_standby_archive_delay applies when…
-
When hot standby is active, this parameter determines how long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries, as described in Section 26.4.2. max_standby_streaming_delay applies wh…
-
Maximum number of synchronization workers per subscription. This parameter controls the amount of parallelism of the initial data copy during the subscription initialization or when new tables are added. Currently, there can be only one synchronizat…
-
Specifies the maximum number of concurrent connections from standby servers or streaming base backup clients (i.e., the maximum number of simultaneously running WAL sender processes). The default is 10. The value 0 means replication is disabled. Abr…
-
Lists the libraries installed in dynamic_library_path that are also trusted for use as logical output plugins by replication clients. Any logical decoding or replication requests for other libraries will be refused. All users are subject to this res…
-
Specifies a connection string to be used for the standby server to connect with a sending server. This string is in the format described in Section 32.1.1. If any option is unspecified in this string, then the corresponding environment variable (see…
-
Optionally specifies an existing replication slot to be used when connecting to the sending server via streaming replication to control resource removal on the upstream node (see Section 26.2.6). This parameter can only be set in the postgresql.conf…
-
Specifies a trigger file whose presence ends recovery in the standby.
-
By default, a standby server restores WAL records from the sending server as soon as possible. It may be useful to have a time-delayed copy of the data, offering opportunities to correct data loss errors. This parameter allows you to delay recovery …
-
Terminate replication connections that are inactive longer than the specified number of milliseconds.
-
It enables a physical standby to synchronize logical failover slots from the primary server so that logical subscribers can resume replication from the new primary server after failover. It is disabled by default. This parameter can only be set in t…
-
A comma-separated list of streaming replication standby server slot names that logical WAL sender processes will wait for. Logical WAL sender processes will send decoded changes to plugins only after the specified replication slots confirm receiving…
-
Specifies a list of standby servers that can support synchronous replication, as described in Section 26.2.8. There will be one or more active synchronous standbys; transactions waiting for commit will be allowed to proceed after these standby serve…
-
Record commit time of transactions. This parameter can only be set at server start. The default value is off.
-
Specifies the number of transactions by which VACUUM and HOT updates will defer cleanup of dead row versions.
-
Specifies the minimum number of past log file segments kept in the pg_wal directory, in case a standby server needs to fetch them for streaming replication.
-
Specifies the minimum size of past WAL files kept in the pg_wal directory, in case a standby server needs to fetch them for streaming replication. If a standby server connected to the sending server falls behind by more than wal_keep_size megabytes,…
-
Specifies whether the WAL receiver process should create a temporary replication slot on the remote instance when no permanent replication slot to use has been configured (using primary_slot_name). The default is off. This parameter can only be set …
-
Specifies the minimum frequency for the WAL receiver process on the standby to send information about replication progress to the primary or upstream standby, where it can be seen using the pg_stat_replication view. The standby will report the last …
-
Terminate replication connections that are inactive for longer than this amount of time. This is useful for the receiving standby server to detect a primary node crash or network outage. If this value is specified without units, it is taken as milli…
-
Specifies how long the standby server should wait when WAL data is not available from any sources (streaming replication, local pg_wal or WAL archive) before trying again to retrieve WAL data. If this value is specified without units, it is taken as…
-
Specifies the delay between activity rounds for WAL sender processes.
-
Specifies the maximum time the server waits during shutdown for all WAL data to be replicated to the receiver.
-
Terminate replication connections that are inactive for longer than this amount of time. This is useful for the sending server to detect a standby crash or network outage. If this value is specified without units, it is taken as milliseconds. The de…
-
The application_name can be any string of less than NAMEDATALEN characters (64 characters in a standard build). It is typically set by an application upon connection to the server. The name will be displayed in the pg_stat_activity view and included…
-
Sets a name that identifies this database cluster (instance) for various purposes. The cluster name appears in the process title for all server processes in this cluster. Moreover, it is the default application name for a standby connection (see syn…
-
When set, debug_pretty_print indents the messages produced by debug_print_parse, debug_print_rewritten, or debug_print_plan. This results in more readable but much longer output than the “compact” format used when it is off. It is on by default.
-
These parameters enable various debugging output to be emitted. When set, they print the resulting parse tree, the query rewriter output, or the execution plan for each executed query. These messages are emitted at LOG message level, so by default t…
-
These parameters enable various debugging output to be emitted. When set, they print the resulting parse tree, the query rewriter output, or the execution plan for each executed query. These messages are emitted at LOG message level, so by default t…
-
These parameters enable various debugging output to be emitted.
-
These parameters enable various debugging output to be emitted. When set, they print the resulting parse tree, the query rewriter output, or the execution plan for each executed query. These messages are emitted at LOG message level, so by default t…
-
When logging to event log is enabled, this parameter determines the program name used to identify PostgreSQL messages in the log. The default is PostgreSQL. This parameter can only be set at server start.
-
Causes analyze action executed by autovacuum to be logged if it ran for at least the specified amount of time.
-
Causes each action executed by autovacuum to be logged if it ran for at least the specified amount of time. Setting this to zero logs all autovacuum actions. -1 disables logging autovacuum actions. If this value is specified without units, it is tak…
-
Causes checkpoints and restartpoints to be logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them. This parameter can only be set in the postgresql.conf fil…
-
Causes aspects of each connection to the server to be logged. The default is the empty string, '', which disables all connection logging. The following options may be specified alone or in a comma-separated list: Table 19.3. Log Connection Options N…
-
PostgreSQL supports several methods for logging server messages, including stderr, csvlog, jsonlog, and syslog. On Windows, eventlog is also supported. Set this parameter to a list of desired log destinations separated by commas. The default is to l…
-
When logging_collector is enabled, this parameter determines the directory in which log files will be created. It can be specified as an absolute path, or relative to the cluster data directory. This parameter can only be set in the postgresql.conf …
-
Causes session terminations to be logged. The log output provides information similar to log_connections, plus the duration of the session. Only superusers and users with the appropriate SET privilege can change this parameter at session start, and …
-
Causes the duration of every completed statement to be logged. The default is off. Only superusers and users with the appropriate SET privilege can change this setting. For clients using extended query protocol, durations of the Parse, Bind, and Exe…
-
Controls the amount of detail written in the server log for each message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE, each adding more fields to displayed messages. TERSE excludes the logging of DETAIL, HINT, QUERY, and CONTEXT erro…
-
On Unix systems this parameter sets the permissions for log files when logging_collector is enabled. (On Microsoft Windows this parameter is ignored.) The parameter value is expected to be a numeric mode specified in the format accepted by the chmod…
-
When logging_collector is enabled, this parameter sets the file names of the created log files. The value is treated as a strftime pattern, so %-escapes can be used to specify time-varying file names. (Note that if there are any time-zone-dependent …
-
By default, connection log messages only show the IP address of the connecting host. Turning this parameter on causes logging of the host name as well. Note that depending on your host name resolution setup this might impose a non-negligible perform…
-
This is a printf-style string that is output at the beginning of each log line. % characters begin “escape sequences” that are replaced with status information as outlined below. Unrecognized escapes are ignored. Other characters are copied straight…
-
Controls whether a detailed log message is produced when a lock acquisition fails. This is useful for analyzing the causes of lock failures. Currently, only lock failures due to SELECT NOWAIT is supported. The default is off. Only superusers and use…
-
Controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance. The default is off. Only superusers and users with the appropri…
-
Allows sampling the duration of completed statements that ran for at least the specified amount of time. This produces the same kind of log entries as log_min_duration_statement, but only for a subset of the executed statements, with sample rate con…
-
Causes the duration of each completed statement to be logged if the statement ran for at least the specified amount of time. For example, if you set it to 250ms then all SQL statements that run 250ms or longer will be logged. Enabling this parameter…
-
Controls which SQL statements that cause an error condition are recorded in the server log. The current SQL statement is included in the log entry for any message of the specified severity or higher. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, …
-
Controls which message levels are written to the server log. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. Each level includes all the levels that follow it. The later the level, the fe…
-
If greater than zero, each bind parameter value logged with a non-error statement-logging message is trimmed to this many bytes. Zero disables logging of bind parameters for non-error statement logs. -1 (the default) allows bind parameters to be log…
-
If greater than zero, each bind parameter value reported in error messages is trimmed to this many bytes. Zero (the default) disables including bind parameters in error messages. -1 allows bind parameters to be printed in full. If this value is spec…
-
Prefixes each message in the server log file with the process ID of the server process.
-
Controls whether a log message is produced when the startup process waits longer than deadlock_timeout for recovery conflicts. This is useful in determining if recovery conflicts prevent the recovery from applying WAL. The default is off. This param…
-
Causes each replication command and walsender process's replication slot acquisition/release to be logged in the server log. See Section 54.4 for more information about replication command. The default value is off. Only superusers and users with th…
-
When logging_collector is enabled, this parameter determines the maximum amount of time to use an individual log file, after which a new log file will be created. If this value is specified without units, it is taken as minutes. The default is 24 ho…
-
When logging_collector is enabled, this parameter determines the maximum size of an individual log file. After this amount of data has been emitted into a log file, a new log file will be created. If this value is specified without units, it is take…
-
Shows the outgoing port number of the connecting host in the connection log messages.
-
Sets the amount of time after which the startup process will log a message about a long-running operation that is still in progress, as well as the interval between further progress messages for that operation. The default is 10 seconds. A setting o…
-
Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). ddl logs all data definition statements, such as CREATE, ALTER, and DROP statements. mod logs all ddl statements, plus data-modifying statemen…
-
If greater than zero, each statement logged by log_statement, log_min_duration_statement, log_min_duration_sample, or log_transaction_sample_rate has its statement text truncated to at most this many bytes.
-
Determines the fraction of statements with duration exceeding log_min_duration_sample that will be logged. Sampling is stochastic, for example 0.5 means there is statistically one chance in two that any given statement will be logged. The default is…
-
Controls logging of temporary file names and sizes. Temporary files can be created for sorts, hashes, and temporary query results. If enabled by this setting, a log entry is emitted for each temporary file, with the file size specified in bytes, whe…
-
Prefixes each server log message with a time stamp.
-
Sets the time zone used for timestamps written in the server log. Unlike TimeZone, this value is cluster-wide, so that all sessions will report timestamps consistently. The built-in default is GMT, but that is typically overridden in postgresql.conf…
-
Sets the fraction of transactions whose statements are all logged, in addition to statements logged for other reasons. It applies to each new transaction regardless of its statements' durations. Sampling is stochastic, for example 0.1 means there is…
-
When logging_collector is enabled, this parameter will cause PostgreSQL to truncate (overwrite), rather than append to, any existing log file of the same name. However, truncation will occur only when a new file is being opened due to time-based rot…
-
This parameter enables the logging collector, which is a background process that captures log messages sent to stderr and redirects them into log files. This approach is often more useful than logging to syslog, since some types of messages might no…
-
This parameter allows messages sent to stderr to be captured and redirected into log files.
-
Runs the server silently.
-
PostgreSQL allows the use of syslog for logging.
-
When logging to syslog is enabled, this parameter determines the syslog “facility” to be used. You can choose from LOCAL0, LOCAL1, LOCAL2, LOCAL3, LOCAL4, LOCAL5, LOCAL6, LOCAL7; the default is LOCAL0. See also the documentation of your system's sys…
-
When logging to syslog is enabled, this parameter determines the program name used to identify PostgreSQL messages in syslog logs. The default is postgres. This parameter can only be set in the postgresql.conf file or on the server command line.
-
When logging to syslog and this is on (the default), then each message will be prefixed by an increasing sequence number (such as [2]). This circumvents the “--- last message repeated N times ---” suppression that many syslog implementations perform…
-
When logging to syslog is enabled, this parameter determines how messages are delivered to syslog. When on (the default), messages are split by lines, and long lines are split so that they will fit into 1024 bytes, which is a typical size limit for …
-
Enables updating of the process title every time a new SQL command is received by the server. This setting defaults to on on most platforms, but it defaults to off on Windows due to that platform's larger overhead for updating the process title. Onl…
-
Specifies the maximum amount of memory to be used by each autovacuum worker process. If this value is specified without units, it is taken as kilobytes. It defaults to -1, indicating that the value of maintenance_work_mem should be used instead. The…
-
Whenever more than this amount of data has been written by a single backend, attempt to force the OS to issue these writes to the underlying storage. Doing so will limit the amount of dirty data in the kernel's page cache, reducing the likelihood of…
-
In each round, no more than this many buffers will be written as a result of the scan of the entire buffer pool.
-
To reduce the amount of work that will be needed at checkpoint time, the background writer also does a circular scan through the entire buffer pool, writing buffers that are found to be dirty.
-
Specifies the delay between activity rounds for the background writer. In each round the writer issues writes for some number of dirty buffers (controllable by the following parameters). It then sleeps for the length of bgwriter_delay, and repeats. …
-
Whenever more than this amount of data has been written by the background writer, attempt to force the OS to issue these writes to the underlying storage. Doing so will limit the amount of dirty data in the kernel's page cache, reducing the likeliho…
-
In each round, no more than this many buffers will be written by the background writer. Setting this to zero disables background writing. (Note that checkpoints, which are managed by a separate, dedicated auxiliary process, are unaffected.) The defa…
-
The number of dirty buffers written in each round is based on the number of new buffers that have been needed by server processes during recent rounds. The average recent need is multiplied by bgwriter_lru_multiplier to arrive at an estimate of the …
-
To reduce the probability that server processes will need to issue their own writes, the background writer tries to write buffers that are likely to be recycled soon.
-
In each round, no more than this many dirty buffers will be written.
-
In each round, no more than this percentage of the currently dirty buffers will be written (rounding up any fraction to the next whole number of buffers).
-
Specifies the amount of memory to use to cache the contents of pg_commit_ts (see Table 66.1). If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default value is 0, which requests shared_buffers…
-
Specifies the dynamic shared memory implementation that the server should use. Possible values are posix (for POSIX shared memory allocated using shm_open), sysv (for System V shared memory allocated via shmget), windows (for Windows shared memory),…
-
Sets the number of concurrent storage I/O operations that PostgreSQL expects can be executed simultaneously. Raising this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel. The a…
-
Specifies the method used to copy files. Possible values are COPY (default) and CLONE (if operating support is available). This parameter affects: CREATE DATABASE ... STRATEGY=FILE_COPY ALTER DATABASE ... SET TABLESPACE ... CLONE uses the copy_file_…
-
Specifies the method used to extend data files during bulk operations such as COPY. The first available option is used as the default, depending on the operating system: posix_fallocate (Unix) uses the standard POSIX interface for allocating disk sp…
-
Used to compute the maximum amount of memory that hash-based operations can use. The final limit is determined by multiplying work_mem by hash_mem_multiplier. The default value is 2.0, which makes hash-based operations use twice the usual work_mem b…
-
Controls the size of huge pages, when they are enabled with huge_pages. The default is zero (0). When set to 0, the default huge page size on the system will be used. This parameter can only be set at server start. Some commonly available page sizes…
-
Controls whether huge pages are requested for the main shared memory area. Valid values are try (the default), on, and off. This parameter can only be set at server start. With huge_pages set to try, the server will try to request huge pages, but fa…
-
Controls the largest I/O size in operations that combine I/O. If set higher than the io_max_combine_limit parameter, the lower value will silently be used instead, so both may need to be raised to increase the I/O size. If this value is specified wi…
-
Controls the largest I/O size in operations that combine I/O, and silently limits the user-settable parameter io_combine_limit. This parameter can only be set at server start. If this value is specified without units, it is taken as blocks, that is …
-
Controls the maximum number of I/O operations that one process can execute simultaneously. The default setting of -1 selects a number based on shared_buffers and the maximum number of processes (max_connections, autovacuum_worker_slots, max_worker_p…
-
Sets the maximum number of I/O worker processes.
-
Selects the method for executing asynchronous I/O. Possible values are: worker (execute asynchronous I/O using worker processes) io_uring (execute asynchronous I/O using io_uring, requires a build with --with-liburing / -Dliburing) sync (execute asy…
-
Sets the minimum number of I/O worker processes.
-
Sets the time after which entirely idle I/O worker processes exit, reducing the size of pool to match demand.
-
Sets the minimum time before another I/O worker can be launched.
-
Selects the number of I/O worker processes to use. The default is 3. This parameter can only be set in the postgresql.conf file or on the server command line. Only has an effect if io_method is set to worker.
-
Specifies the maximum amount of memory to be used by logical decoding, before some of the decoded changes are written to local disk. This limits the amount of memory used by logical streaming replication connections. It defaults to 64 megabytes (64M…
-
Similar to effective_io_concurrency, but used for maintenance work that is done on behalf of many client sessions. The default is 16. This value can be overridden for tables in a particular tablespace by setting the tablespace parameter of the same …
-
Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. If this value is specified without units, it is taken as kilobytes. It defaults to 64 megabytes (64MB). Since…
-
Sets the maximum number of open files each server subprocess is allowed to open simultaneously; files already opened in the postmaster are not counted toward this limit. The default is one thousand files. If the kernel is enforcing a safe per-proces…
-
Sets the maximum number of disk pages for which free space will be tracked in the shared free-space map.
-
Sets the maximum number of relations (tables and indexes) for which free space will be tracked in the shared free-space map.
-
Specifies the maximum amount of allocated pages for NOTIFY / LISTEN queue. The default value is 1048576. For 8 KB pages it allows to consume up to 8 GB of disk space. This parameter can only be set at server start.
-
Sets the maximum number of parallel workers that can be started by a single utility command. Currently, the parallel utility commands that support the use of parallel workers are CREATE INDEX when building a B-tree, GIN, or BRIN index, and VACUUM wi…
-
Sets the maximum number of workers that the cluster can support for parallel operations. The default value is 8. When increasing or decreasing this value, consider also adjusting max_parallel_maintenance_workers and max_parallel_workers_per_gather. …
-
Sets the maximum number of workers that can be started by a single Gather or Gather Merge node. Parallel workers are taken from the pool of processes established by max_worker_processes, limited by max_parallel_workers. Note that the requested numbe…
-
Sets the maximum number of transactions that can be in the “prepared” state simultaneously (see PREPARE TRANSACTION). Setting this parameter to zero (which is the default) disables the prepared-transaction feature. This parameter can only be set at …
-
Specifies the maximum safe depth of the server's execution stack. The ideal setting for this parameter is the actual stack size limit enforced by the kernel (as set by ulimit -s or local equivalent), less a safety margin of a megabyte or so. The saf…
-
Sets the maximum number of background processes that the cluster can support. This parameter can only be set at server start. The default is 8. When running a standby server, you must set this parameter to the same or higher value than on the primar…
-
Specifies the amount of memory that should be allocated at server startup for use by parallel queries. When this memory region is insufficient or exhausted by concurrent queries, new parallel queries try to allocate extra shared memory temporarily f…
-
Specifies the amount of shared memory to use to cache the contents of pg_multixact/members (see Table 66.1). If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default value is 32. This paramete…
-
Specifies the amount of shared memory to use to cache the contents of pg_multixact/offsets (see Table 66.1). If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default value is 16. This paramete…
-
Specifies the amount of shared memory to use to cache the contents of pg_notify (see Table 66.1). If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default value is 16. This parameter can only …
-
Sets the minimum amount of time that a query snapshot can be used without risk of a “snapshot too old” error occurring when using the snapshot.
-
Allows the leader process to execute the query plan under Gather and Gather Merge nodes instead of waiting for worker processes. The default is on. Setting this value to off reduces the likelihood that workers will become blocked because the leader …
-
This variable specifies one or more shared libraries that are to be preloaded at server start.
-
When the number of tuples to be sorted is smaller than this number, a sort will produce its first output run using replacement selection rather than quicksort.
-
Specifies the amount of shared memory to use to cache the contents of pg_serial (see Table 66.1). If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default value is 32. This parameter can only …
-
Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 megabytes (128MB), but might be less if your kernel settings will not support it (as determined during initdb). This setting must be at least …
-
Specifies the shared memory implementation that the server should use for the main shared memory region that holds PostgreSQL's shared buffers and other shared data. Possible values are mmap (for anonymous shared memory allocated using mmap), sysv (…
-
Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files.
-
Specifies the amount of shared memory to use to cache the contents of pg_subtrans (see Table 66.1). If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default value is 0, which requests shared_b…
-
Sets the maximum amount of memory used for temporary buffers within each database session. These are session-local buffers used only for access to temporary tables. If this value is specified without units, it is taken as blocks, that is BLCKSZ byte…
-
Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor. A transaction attempting to exceed this limit will be canceled. If this value is spe…
-
Selects the method for making timing measurements using the OS or specialized CPU instructions.
-
Specifies the amount of shared memory to use to cache the contents of pg_xact (see Table 66.1). If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default value is 0, which requests shared_buffe…
-
Specifies the size of the Buffer Access Strategy used by the VACUUM and ANALYZE commands. A setting of 0 will allow the operation to use any number of shared_buffers. Otherwise valid sizes range from 128 kB to 16 GB. If the specified size would exce…
-
Specifies the maximum amount of memory to be used by VACUUM to keep track of to-be-reclaimed rows.
-
Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. If this value is specified without units, it is taken as kilobytes. The default value is four megabytes (4M…
-
Enables in-core computation of a query identifier. Query identifiers can be displayed in the pg_stat_activity view, using EXPLAIN, or emitted in the log if configured via the log_line_prefix parameter. The pg_stat_statements extension also requires …
-
For each query, output performance statistics of the respective module to the server log. This is a crude profiling instrument, similar to the Unix getrusage() operating system facility. log_statement_stats reports total statement statistics, while …
-
For each query, output performance statistics of the respective module to the server log. This is a crude profiling instrument, similar to the Unix getrusage() operating system facility. log_statement_stats reports total statement statistics, while …
-
For each query, output performance statistics of the respective module to the server log. This is a crude profiling instrument, similar to the Unix getrusage() operating system facility. log_statement_stats reports total statement statistics, while …
-
For each query, output performance statistics of the respective module to the server log. This is a crude profiling instrument, similar to the Unix getrusage() operating system facility. log_statement_stats reports total statement statistics, while …
-
Enables the collection of block-level statistics on database activity.
-
Enables the collection of information on the currently executing command of each session, along with the time at which that command began execution.
-
Determines the behavior when cumulative statistics are accessed multiple times within a transaction. When set to none, each access re-fetches counters from shared memory. When set to cache, the first access to statistics for an object caches those s…
-
If on, collected block-level and row-level statistics are zeroed out whenever the server is restarted.
-
Enables the collection of row-level statistics on database activity.
-
Controls whether the server should start the statistics-collection subprocess.
-
Sets the directory to store temporary statistics data in.
-
Enables the collection of information on the currently executing command of each session, along with its identifier and the time when that command began execution. This parameter is on by default. Note that even when enabled, this information is onl…
-
Specifies the amount of memory reserved to store the text of the currently executing command for each active session, for the pg_stat_activity.query field. If this value is specified without units, it is taken as bytes. The default value is 1024 byt…
-
Enables timing of cost-based vacuum delay (see Section 19.10.2). This parameter is off by default, as it will repeatedly query the operating system for the current time, which may cause significant overhead on some platforms. You can use the pg_test…
-
Enables collection of statistics on database activity. This parameter is on by default, because the autovacuum daemon needs the collected information. Only superusers and users with the appropriate SET privilege can change this setting.
-
Enables tracking of function call counts and time used. Specify pl to track only procedural-language functions, all to also track SQL and C language functions. The default is none, which disables function statistics tracking. Only superusers and use…
-
Enables timing of database I/O waits. This parameter is off by default, as it will repeatedly query the operating system for the current time, which may cause significant overhead on some platforms. You can use the pg_test_timing tool to measure the…
-
Enables timing of WAL I/O waits. This parameter is off by default, as it will repeatedly query the operating system for the current time, which may cause significant overhead on some platforms. You can use the pg_test_timing tool to measure the over…
-
Controls whether the server should run the autovacuum launcher daemon. This is on by default; however, track_counts must also be enabled for autovacuum to work. This parameter can only be set in the postgresql.conf file or on the server command line…
-
Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE. The default is 0.1 (10% of table size). This parameter can only be set in the postgresql.conf file or on the server command li…
-
Specifies the scaling factor of the analyze threshold component of the score used by autovacuum for prioritization purposes.
-
Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting ca…
-
Specifies the maximum age (in transactions) that a table's pg_class.relfrozenxid field can attain before a VACUUM operation is forced to prevent transaction ID wraparound within the table. Note that the system will launch autovacuum processes to pre…
-
Specifies the scaling factor of the transaction ID age component of the score used by autovacuum for prioritization purposes.
-
Sets the maximum number of parallel workers that can be used by a single autovacuum worker to process indexes.
-
Specifies the maximum number of autovacuum processes (other than the autovacuum launcher) that may be running at any one time. The default is 3. This parameter can only be set in the postgresql.conf file or on the server command line. Note that a se…
-
Specifies the maximum age (in multixacts) that a table's pg_class.relminmxid field can attain before a VACUUM operation is forced to prevent multixact ID wraparound within the table. Note that the system will launch autovacuum processes to prevent w…
-
Specifies the scaling factor of the multixact ID age component of the score used by autovacuum for prioritization purposes.
-
Specifies the minimum delay between autovacuum runs on any given database. In each round the daemon examines the database and issues VACUUM and ANALYZE commands as needed for tables in that database. If this value is specified without units, it is t…
-
Specifies the cost delay value that will be used in automatic VACUUM operations. If -1 is specified, the regular vacuum_cost_delay value will be used. If this value is specified without units, it is taken as milliseconds. The default value is 2 mill…
-
Specifies the cost limit value that will be used in automatic VACUUM operations. If -1 is specified (which is the default), the regular vacuum_cost_limit value will be used. Note that the value is distributed proportionally among the running autovac…
-
Specifies a fraction of the unfrozen pages in the table to add to autovacuum_vacuum_insert_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of unfrozen pages in table). This parameter can only be set in the postgresql.con…
-
Specifies the scaling factor of the vacuum insert threshold component of the score used by autovacuum for prioritization purposes.
-
Specifies the number of inserted tuples needed to trigger a VACUUM in any one table. The default is 1000 tuples. If -1 is specified, autovacuum will not trigger a VACUUM operation on any tables based on the number of inserts. This parameter can only…
-
Specifies the maximum number of updated or deleted tuples needed to trigger a VACUUM in any one table, i.e., a limit on the value calculated with autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor. The default is 100,000,000 tuples. If -…
-
Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size). This parameter can only be set in the postgresql.conf file or on the server command line;…
-
Specifies the scaling factor of the vacuum threshold component of the score used by autovacuum for prioritization purposes.
-
Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overrid…
-
Specifies the number of backend slots to reserve for autovacuum worker processes. The default is typically 16 slots, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at serve…
-
The amount of time that the process will sleep when the cost limit has been exceeded. If this value is specified without units, it is taken as milliseconds. The default value is 0, which disables the cost-based vacuum delay feature. Positive values …
-
This is the accumulated cost that will cause the vacuuming process to sleep for vacuum_cost_delay. The default is 200.
-
The estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again. The default value is 20.
-
The estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table and scan the content of the page. The default value is 1.
-
The estimated cost for vacuuming a buffer that has to be read from disk. This represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk and scan its content. The default value is 2.
-
Specifies the maximum age (in transactions) that a table's pg_class.relfrozenxid field can attain before VACUUM takes extraordinary measures to avoid system-wide transaction ID wraparound failure. This is VACUUM's strategy of last resort. The failsa…
-
Specifies the cutoff age (in transactions) that VACUUM should use to decide whether to trigger freezing of pages that have an older XID. The default is 50 million transactions. Although users can set this value anywhere from zero to one billion, VAC…
-
VACUUM performs an aggressive scan if the table's pg_class.relfrozenxid field has reached the age specified by this setting. An aggressive scan differs from a regular VACUUM in that it visits every page that might contain unfrozen XIDs or MXIDs, not…
-
Specifies the maximum number of pages (as a fraction of total pages in the relation) that VACUUM may scan and fail to set all-frozen in the visibility map before disabling eager scanning. A value of 0 disables eager scanning altogether. The default …
-
Specifies the maximum age (in multixacts) that a table's pg_class.relminmxid field can attain before VACUUM takes extraordinary measures to avoid system-wide multixact ID wraparound failure. This is VACUUM's strategy of last resort. The failsafe typ…
-
Specifies the cutoff age (in multixacts) that VACUUM should use to decide whether to trigger freezing of pages with an older multixact ID. The default is 5 million multixacts. Although users can set this value anywhere from zero to one billion, VACU…
-
VACUUM performs an aggressive scan if the table's pg_class.relminmxid field has reached the age specified by this setting. An aggressive scan differs from a regular VACUUM in that it visits every page that might contain unfrozen XIDs or MXIDs, not j…
-
Enables or disables vacuum to try to truncate off any empty pages at the end of the table. The default value is true. If true, VACUUM and autovacuum do the truncation and the disk space for the truncated pages is returned to the operating system. No…
-
When on, tables that are referenced by a query will be automatically added to the FROM clause if not already present.
-
When allow_alter_system is set to off, an error is returned if the ALTER SYSTEM command is executed. This parameter can only be set in the postgresql.conf file or on the server command line. The default value is on. Note that this setting must not b…
-
This controls whether the array input parser recognizes unquoted NULL as specifying a null array element. By default, this is on, allowing array values containing null values to be entered. However, PostgreSQL versions before 8.2 did not support nul…
-
This controls whether a quote mark can be represented by \' in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted \'. However, use of \' creates security r…
-
This controls whether CREATE TABLE and CREATE TABLE AS include an OID column in newly-created tables, if neither WITH OIDS nor WITHOUT OIDS is specified.
-
When on, a warning is issued if a backslash (\) appears in an ordinary string literal ('...' syntax) and standard_conforming_strings is off. The default is on. Applications that wish to use backslash as escape should be modified to use escape string…
-
In PostgreSQL releases prior to 9.0, large objects did not have access privileges and were, therefore, always readable and writable by all users. Setting this variable to on disables the new privilege checks, for compatibility with prior releases. T…
-
When on, the parser will emit a warning for any construct that might have changed meanings since PostgreSQL 9.4 as a result of changes in operator precedence.
-
When the database generates SQL, force all identifiers to be quoted, even if they are not (currently) keywords. This will affect the output of EXPLAIN as well as the results of functions like pg_get_viewdef. See also the --quote-all-identifiers opti…
-
The regular expression "flavor" can be set to advanced, extended, or basic.
-
This setting controls whether undecorated table references are considered to include inheritance child tables.
-
This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. Beginning in PostgreSQL 9.1, the default is on (prior releases defaulted to off). Applications can check this parameter to determin…
-
This allows sequential scans of large tables to synchronize with each other, so that concurrent scans read the same block at about the same time and hence share the I/O workload. When this is enabled, a scan might start in the middle of the table an…
-
When on, expressions of the form expr = NULL (or NULL = expr) are treated as expr IS NULL, that is, they return true if expr evaluates to the null value, and false otherwise. The correct SQL-spec-compliant behavior of expr = NULL is to always return…
-
This optional parameter specifies a shell command that will be executed at every restartpoint. The purpose of archive_cleanup_command is to provide a mechanism for cleaning up old archived WAL files that are no longer needed by the standby server. A…
-
The local shell command to execute to archive a completed WAL file segment. Any %p in the string is replaced by the path name of the file to archive, and any %f is replaced by only the file name. (The path name is relative to the working directory o…
-
The library to use for archiving completed WAL file segments. If set to an empty string (the default), archiving via shell is enabled, and archive_command is used. If both archive_command and archive_library are set, an error will be raised. Otherwi…
-
When archive_mode is enabled, completed WAL segments are sent to archive storage by setting archive_command or archive_library. In addition to off, to disable, there are two modes: on, and always. During normal operation, there is no difference betw…
-
The archive_command or archive_library is only invoked for completed WAL segments. Hence, if your server generates little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and it…
-
Specifies the target of checkpoint completion, as a fraction of total time between checkpoints. The default is 0.9, which spreads the checkpoint across almost all of the available interval, providing fairly consistent I/O load while also leaving som…
-
Whenever more than this amount of data has been written while performing a checkpoint, attempt to force the OS to issue these writes to the underlying storage. Doing so will limit the amount of dirty data in the kernel's page cache, reducing the lik…
-
Maximum number of log file segments between automatic WAL checkpoints (each segment is normally 16 megabytes).
-
Maximum time between automatic WAL checkpoints. If this value is specified without units, it is taken as seconds. The valid range is between 30 seconds and one day. The default is five minutes (5min). Increasing this parameter can increase the amoun…
-
Write a message to the server log if checkpoints caused by the filling of WAL segment files happen closer together than this amount of time (which suggests that max_wal_size ought to be raised). If this value is specified without units, it is taken …
-
Setting commit_delay adds a time delay before a WAL flush is initiated. This can improve group commit throughput by allowing a larger number of transactions to commit via a single WAL flush, if system load is high enough that additional transactions…
-
Minimum number of concurrent open transactions to require before performing the commit_delay delay. A larger value makes it more probable that at least one other transaction will become ready to commit during the delay interval. The default is five …
-
If this parameter is on, the PostgreSQL server will try to make sure that updates are physically written to disk, by issuing fsync() system calls or various equivalent methods (see wal_sync_method). This ensures that the database cluster can recover…
-
When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint. This is needed because a page write that is in process during an operating system cras…
-
Maximum size to let the WAL grow during automatic checkpoints. This is a soft limit; WAL size can exceed max_wal_size under special circumstances, such as heavy load, a failing archive_command or archive_library, or a high wal_keep_size setting. If …
-
As long as WAL disk usage stays below this setting, old WAL files are always recycled for future use at a checkpoint, rather than removed. This can be used to ensure that enough WAL space is reserved to handle spikes in WAL usage, for example when r…
-
This parameter specifies a shell command that will be executed once only at the end of recovery. This parameter is optional. The purpose of the recovery_end_command is to provide a mechanism for cleanup following replication or recovery. Any %r is r…
-
Whether to try to prefetch blocks that are referenced in the WAL that are not yet in the buffer pool, during recovery. Valid values are off, on and try (the default). The setting try enables prefetching only if the operating system provides support …
-
This parameter specifies that recovery should end as soon as a consistent state is reached, i.e., as early as possible. When restoring from an online backup, this means the point where taking the backup ended. Technically, this is a string parameter…
-
Specifies what action the server should take once the recovery target is reached. The default is pause, which means recovery will be paused. promote means the recovery process will finish and the server will start to accept connections. Finally shut…
-
Specifies whether to stop just after the specified recovery target (on), or just before the recovery target (off). Applies when recovery_target_lsn, recovery_target_time, or recovery_target_xid is specified. This setting controls whether transaction…
-
This parameter specifies the LSN of the write-ahead log location up to which recovery will proceed. The precise stopping point is also influenced by recovery_target_inclusive. This parameter is parsed using the system data type pg_lsn.
-
This parameter specifies the named restore point (created with pg_create_restore_point()) to which recovery will proceed.
-
This parameter specifies the time stamp up to which recovery will proceed. The precise stopping point is also influenced by recovery_target_inclusive. The value of this parameter is a time stamp in the same format accepted by the timestamp with time…
-
Specifies recovering into a particular timeline. The value can be a numeric timeline ID or a special value. The value current recovers along the same timeline that was current when the base backup was taken. The value latest recovers to the latest t…
-
This parameter specifies the transaction ID up to which recovery will proceed. Keep in mind that while transaction IDs are assigned sequentially at transaction start, transactions can complete in a different numeric order. The transactions that will…
-
The local shell command to execute to retrieve an archived segment of the WAL file series. This parameter is required for archive recovery, but optional for streaming replication. Any %f in the string is replaced by the name of the file to retrieve …
-
Enables the WAL summarizer process. Note that WAL summarization can be enabled either on a primary or on a standby. This parameter can only be set in the postgresql.conf file or on the server command line. The default is off. The server cannot be st…
-
Specifies how much WAL processing must complete before the database server returns a “success” indication to the client. Valid values are remote_apply, on (the default), remote_write, local, and off. If synchronous_standby_names is empty, the only m…
-
The amount of shared memory used for WAL data that has not yet been written to disk. The default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers, but not less than 64kB nor more than the size of one WAL segment, typically 1…
-
This parameter enables compression of WAL using the specified compression method. When enabled, the PostgreSQL server compresses full page images written to WAL (e.g. when full_page_writes is on, during a base backup, etc.). A compressed page image …
-
A limit on how far ahead the server can look in the WAL, to find blocks to prefetch. If this value is specified without units, it is taken as bytes. The default is 512kB. This parameter can only be set at server start.
-
If set to on (the default), this option causes new WAL files to be filled with zeroes. On some file systems, this ensures that space is allocated before we need to write WAL records. However, Copy-On-Write (COW) file systems may not benefit from thi…
-
wal_level determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. minimal removes all logging ex…
-
When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint, even for non-critical modifications of so-called hint bits. If data checksums are enab…
-
If set to on (the default), this option causes WAL files to be recycled by renaming them, avoiding the need to create new ones. On COW file systems, it may be faster to create new ones, so the option is given to disable this behavior.
-
When wal_level is minimal and a transaction commits after creating or rewriting a permanent relation, this setting determines how to persist the new data. If the data is smaller than this setting, write it to the WAL log; otherwise, use an fsync of …
-
Configures the amount of time after which the WAL summarizer automatically removes old WAL summaries. The file timestamp is used to determine which files are old enough to remove. Typically, you should set this comfortably higher than the time that …
-
Method used for forcing WAL updates out to disk. If fsync is off then this setting is irrelevant, since WAL file updates will not be forced out at all. Possible values are: open_datasync (write WAL files with open() option O_DSYNC) fdatasync (call f…
-
Specifies how often the WAL writer flushes WAL, in time terms. After flushing WAL the writer sleeps for the length of time given by wal_writer_delay, unless woken up sooner by an asynchronously committing transaction. If the last flush happened less…
-
Specifies how often the WAL writer flushes WAL, in volume terms. If the last flush happened less than wal_writer_delay ago and less than wal_writer_flush_after worth of WAL has been produced since, then WAL is only written to the operating system, n…