Movatterモバイル変換

wal_level determines how much information is written to the WAL. The default value isreplica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server.minimal removes all logging except the information required to recover from a crash or immediate shutdown. Finally,logical adds information necessary to support logical decoding. Each level includes the information logged at all lower levels. This parameter can only be set at server start.

Inminimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (seeSection 14.4.7). Operations in which this optimization can be applied include:

But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, soreplica or higher must be used to enable WAL archiving (archive_mode) and streaming replication.

Inlogical level, the same information is logged as withreplica, plus information needed to allow extracting logical change sets from the WAL. Using a level oflogical will increase the WAL volume, particularly if many tables are configured forREPLICA IDENTITY FULL and manyUPDATE andDELETE statements are executed.

In releases prior to 9.6, this parameter also allowed the valuesarchive andhot_standby. These are still accepted but mapped toreplica.

If this parameter is on, thePostgreSQL server will try to make sure that updates are physically written to disk, by issuingfsync() system calls or various equivalent methods (seewal_sync_method). This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash.

While turning offfsync is often a performance benefit, this can result in unrecoverable data corruption in the event of a power failure or system crash. Thus it is only advisable to turn offfsync if you can easily recreate your entire database from external data.

Examples of safe circumstances for turning offfsync include the initial loading of a new database cluster from a backup file, using a database cluster for processing a batch of data after which the database will be thrown away and recreated, or for a read-only database clone which gets recreated frequently and is not used for failover. High quality hardware alone is not a sufficient justification for turning offfsync.

For reliable recovery when changingfsync off to on, it is necessary to force all modified buffers in the kernel to durable storage. This can be done while the cluster is shutdown or whilefsync is on by runninginitdb --sync-only, runningsync, unmounting the file system, or rebooting the server.

In many situations, turning offsynchronous_commit for noncritical transactions can provide much of the potential performance benefit of turning offfsync, without the attendant risks of data corruption.

fsync can only be set in thepostgresql.conf file or on the server command line. If you turn this parameter off, also consider turning offfull_page_writes.

Specifies how much WAL processing must complete before the database server returns a“success” indication to the client. Valid values areremote_apply,on (the default),remote_write,local, andoff.

Ifsynchronous_standby_names is empty, the only meaningful settings areon andoff;remote_apply,remote_write andlocal all provide the same local synchronization level ason. The local behavior of all non-off modes is to wait for local flush of WAL to disk. Inoff mode, there is no waiting, so there can be a delay between when success is reported to the client and when the transaction is later guaranteed to be safe against a server crash. (The maximum delay is three timeswal_writer_delay.) Unlikefsync, setting this parameter tooff does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turningsynchronous_commit off can be a useful alternative when performance is more important than exact certainty about the durability of a transaction. For more discussion seeSection 30.3.

Ifsynchronous_standby_names is non-empty,synchronous_commit also controls whether transaction commits will wait for their WAL records to be processed on the standby server(s).

When set toremote_apply, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and applied it, so that it has become visible to queries on the standby(s), and also written to durable storage on the standbys. This will cause much larger commit delays than previous settings since it waits for WAL replay. When set toon, commits wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and flushed it to durable storage. This ensures the transaction will not be lost unless both the primary and all synchronous standbys suffer corruption of their database storage. When set toremote_write, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and written it to their file systems. This setting ensures data preservation if a standby instance ofPostgreSQL crashes, but not if the standby suffers an operating-system-level crash because the data has not necessarily reached durable storage on the standby. The settinglocal causes commits to wait for local flush to disk, but not for replication. This is usually not desirable when synchronous replication is in use, but is provided for completeness.

This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits. It is therefore possible, and useful, to have some transactions commit synchronously and others asynchronously. For example, to make a single multistatement transaction commit asynchronously when the default is the opposite, issueSET LOCAL synchronous_commit TO OFF within the transaction.

Table 19.1 summarizes the capabilities of thesynchronous_commit settings.

Method used for forcing WAL updates out to disk. Iffsync is off then this setting is irrelevant, since WAL file updates will not be forced out at all. Possible values are:

Theopen_* options also useO_DIRECT if available. Not all of these choices are available on all platforms. The default is the first method in the above list that is supported by the platform, except thatfdatasync is the default on Linux and FreeBSD. The default is not necessarily ideal; it might be necessary to change this setting or other aspects of your system configuration in order to create a crash-safe configuration or achieve optimal performance. These aspects are discussed inSection 30.1. This parameter can only be set in thepostgresql.conf file or on the server command line.

When this parameter is on, thePostgreSQL 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 crash might be only partially completed, leading to an on-disk page that contains a mix of old and new data. The row-level change data normally stored in WAL will not be enough to completely restore such a page during post-crash recovery. Storing the full page image guarantees that the page can be correctly restored, but at the price of increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the checkpoint interval parameters.)

Turning this parameter off speeds normal operation, but might lead to either unrecoverable data corruption, or silent data corruption, after a system failure. The risks are similar to turning offfsync, though smaller, and it should be turned off only based on the same circumstances recommended for that parameter.

Turning off this parameter does not affect use of WAL archiving for point-in-time recovery (PITR) (seeSection 25.3).

This parameter can only be set in thepostgresql.conf file or on the server command line. The default ison.

When this parameter ison, thePostgreSQL 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 enabled, hint bit updates are always WAL-logged and this setting is ignored. You can use this setting to test how much extra WAL-logging would occur if your database had data checksums enabled.

This parameter can only be set at server start. The default value isoff.

When this parameter ison, thePostgreSQL server compresses a full page image written to WAL whenfull_page_writes is on or during a base backup. A compressed page image will be decompressed during WAL replay. The default value isoff. Only superusers can change this setting.

Turning this parameter on can reduce the WAL volume without increasing the risk of unrecoverable data corruption, but at the cost of some extra CPU spent on the compression during WAL logging and on the decompression during WAL replay.

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%) ofshared_buffers, but not less than64kB nor more than the size of one WAL segment, typically16MB. This value can be set manually if the automatic choice is too large or too small, but any positive value less than32kB will be treated as32kB. This parameter can only be set at server start.

The contents of the WAL buffers are written out to disk at every transaction commit, so extremely large values are unlikely to provide a significant benefit. However, setting this value to at least a few megabytes can improve write performance on a busy server where many clients are committing at once. The auto-tuning selected by the default setting of -1 should give reasonable results in most cases.

Specifies how often the WAL writer flushes WAL. After flushing WAL it sleeps forwal_writer_delay milliseconds, unless woken up by an asynchronously committing transaction. If the last flush happened less thanwal_writer_delay milliseconds ago and less thanwal_writer_flush_after bytes of WAL have been produced since, then WAL is only written to the operating system, not flushed to disk. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; settingwal_writer_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10. This parameter can only be set in thepostgresql.conf file or on the server command line.

Specifies how often the WAL writer flushes WAL. If the last flush happened less thanwal_writer_delay milliseconds ago and less thanwal_writer_flush_after bytes of WAL have been produced since, then WAL is only written to the operating system, not flushed to disk. Ifwal_writer_flush_after is set to0 then WAL data is flushed immediately. The default is1MB. This parameter can only be set in thepostgresql.conf file or on the server command line.

commit_delay adds a time delay, measured in microseconds, 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 become ready to commit within the given interval. However, it also increases latency by up tocommit_delay microseconds for each WAL flush. Because the delay is just wasted if no other transactions become ready to commit, a delay is only performed if at leastcommit_siblings other transactions are active when a flush is about to be initiated. Also, no delays are performed iffsync is disabled. The defaultcommit_delay is zero (no delay). Only superusers can change this setting.

InPostgreSQL releases prior to 9.3,commit_delay behaved differently and was much less effective: it affected only commits, rather than all WAL flushes, and waited for the entire configured delay even if the WAL flush was completed sooner. Beginning inPostgreSQL 9.3, the first process that becomes ready to flush waits for the configured interval, while subsequent processes wait only until the leader completes the flush operation.

Minimum number of concurrent open transactions to require before performing thecommit_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 transactions.

Maximum time between automatic WAL checkpoints, in seconds. The valid range is between 30 seconds and one day. The default is five minutes (5min). Increasing this parameter can increase the amount of time needed for crash recovery. This parameter can only be set in thepostgresql.conf file or on the server command line.

Specifies the target of checkpoint completion, as a fraction of total time between checkpoints. The default is 0.5. This parameter can only be set in thepostgresql.conf file or on the server command line.

Whenever more thancheckpoint_flush_after bytes have 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 likelihood of stalls when anfsync is issued at the end of the checkpoint, or when the OS writes data back in larger batches in the background. Often that will result in greatly reduced transaction latency, but there also are some cases, especially with workloads that are bigger thanshared_buffers, but smaller than the OS's page cache, where performance might degrade. This setting may have no effect on some platforms. The valid range is between0, which disables forced writeback, and2MB. The default is256kB on Linux,0 elsewhere. (IfBLCKSZ is not 8kB, the default and maximum values scale proportionally to it.) This parameter can only be set in thepostgresql.conf file or on the server command line.

Write a message to the server log if checkpoints caused by the filling of checkpoint segment files happen closer together than this many seconds (which suggests thatmax_wal_size ought to be raised). The default is 30 seconds (30s). Zero disables the warning. No warnings will be generated ifcheckpoint_timeout is less thancheckpoint_warning. This parameter can only be set in thepostgresql.conf file or on the server command line.

Maximum size to let the WAL grow during automatic checkpoints. This is a soft limit; WAL size can exceedmax_wal_size under special circumstances, like under heavy load, a failingarchive_command, or a highwal_keep_segments setting. The default is 1 GB. Increasing this parameter can increase the amount of time needed for crash recovery. This parameter can only be set in thepostgresql.conf file or on the server command line.

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 running large batch jobs. The default is 80 MB. This parameter can only be set in thepostgresql.conf file or on the server command line.

Whenarchive_mode is enabled, completed WAL segments are sent to archive storage by settingarchive_command. In addition tooff, to disable, there are two modes:on, andalways. During normal operation, there is no difference between the two modes, but when set toalways the WAL archiver is enabled also during archive recovery or standby mode. Inalways mode, all files restored from the archive or streamed with streaming replication will be archived (again). SeeSection 26.2.9 for details.

archive_mode andarchive_command are separate variables so thatarchive_command can be changed without leaving archiving mode. This parameter can only be set at server start.archive_mode cannot be enabled whenwal_level is set tominimal.

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 of the server, i.e., the cluster's data directory.) Use%% to embed an actual% character in the command. It is important for the command to return a zero exit status only if it succeeds. For more information seeSection 25.3.1.

This parameter can only be set in thepostgresql.conf file or on the server command line. It is ignored unlessarchive_mode was enabled at server start. Ifarchive_command is an empty string (the default) whilearchive_mode is enabled, WAL archiving is temporarily disabled, but the server continues to accumulate WAL segment files in the expectation that a command will soon be provided. Settingarchive_command to a command that does nothing but return true, e.g.,/bin/true (REM on Windows), effectively disables archiving, but also breaks the chain of WAL files needed for archive recovery, so it should only be used in unusual circumstances.

Thearchive_command 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 its safe recording in archive storage. To limit how old unarchived data can be, you can setarchive_timeout to force the server to switch to a new WAL segment file periodically. When this parameter is greater than zero, the server will switch to a new segment file whenever this many seconds have elapsed since the last segment file switch, and there has been any database activity, including a single checkpoint (checkpoints are skipped if there is no database activity). Note that archived files that are closed early due to a forced switch are still the same length as completely full files. Therefore, it is unwise to use a very shortarchive_timeout — it will bloat your archive storage.archive_timeout settings of a minute or so are usually reasonable. You should consider using streaming replication, instead of archiving, if you want data to be copied off the master server more quickly than that. This parameter can only be set in thepostgresql.conf file or on the server command line.