PostgreSQL: Documentation: 9.1: Write Ahead Log

PostgreSQL: Documentation: 9.1: Write Ahead Log

时间:2015-07-10 13:54来源:网络整理 作者:KKWL 点击:
18.5. Write Ahead Log See also Section 29.4 for details on WAL and checkpoint tuning. 18.5.1. Settings wal_level(enum) wal_level determines how muchinformation is written to the WAL. The default value isminimal, which writes only theinforma

18.5. Write Ahead Log

See also Section 29.4 for details on WAL and checkpoint tuning.

18.5.1. Settings

wal_level (enum)

wal_level determines how much information is written to the WAL. The default value is minimal, which writes only the information needed to recover from a crash or immediate shutdown. archive adds logging required for WAL archiving, and hot_standby further adds information required to run read-only queries on a standby server. This parameter can only be set at server start.

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

CREATE TABLE AS

CREATE INDEX

CLUSTER

COPY into tables that were created or truncated in the same transaction

But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so either archive or hot_standby level must be used to enable WAL archiving () and streaming replication.

In hot_standby level, the same information is logged as with archive, plus information needed to reconstruct the status of running transactions from the WAL. To enable read-only queries on a standby server, wal_level must be set to hot_standby on the primary, and must be enabled in the standby. It is thought that there is little measurable difference in performance between using hot_standby and archive levels, so feedback is welcome if any production impacts are noticeable.

fsync (boolean)

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 ). This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash.

While turning off fsync 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 off fsync if you can easily recreate your entire database from external data.

Examples of safe circumstances for turning off fsync 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 off fsync.

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

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

synchronous_commit (enum)

Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a "success" indication to the client. Valid values are on, local, and off. The default, and safe, value is on. When off, there can be a delay between when success is reported to the client and when the transaction is really guaranteed to be safe against a server crash. (The maximum delay is three times .) Unlike , setting this parameter to off 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, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty about the durability of a transaction. For more discussion see Section 29.3.

If synchronous_standby_names is set, this parameter also controls whether or not transaction commit will wait for the transaction's WAL records to be flushed to disk and replicated to the standby server. The commit wait will last until a reply from the current synchronous standby indicates it has written the commit record of the transaction to durable storage. If synchronous replication is in use, it will normally be sensible either to wait both for WAL records to reach both the local and remote disks, or to allow the transaction to commit asynchronously. However, the special value local is available for transactions that wish to wait for local flush to disk, but not synchronous replication.

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, issue SET LOCAL synchronous_commit TO OFF within the transaction.

wal_sync_method (enum)

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 fdatasync() at each commit)

  • fsync (call fsync() at each commit)

  • fsync_writethrough (call fsync() at each commit, forcing write-through of any disk write cache)

  • open_sync (write WAL files with open() option O_SYNC)

  • ------分隔线----------------------------