17.5. Write Ahead Log

If this option 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 to a consistent state after an operating system or hardware crash.

However, using fsync results in a performance penalty: when a transaction is committed, PostgreSQL must wait for the operating system to flush the write-ahead log to disk. When fsync is disabled, the operating system is allowed to do its best in buffering, ordering, and delaying writes. This can result in significantly improved performance. However, if the system crashes, the results of the last few committed transactions may be lost in part or whole. In the worst case, unrecoverable data corruption may occur. (Crashes of the database software itself are not a risk factor here. Only an operating-system-level crash creates a risk of corruption.)

Due to the risks involved, there is no universally correct setting for fsync. Some administrators always disable fsync, while others only turn it off during initial bulk data loads, where there is a clear restart point if something goes wrong. Others always leave fsync enabled. The default is to enable fsync, for maximum reliability. If you trust your operating system, your hardware, and your utility company (or your battery backup), you can consider disabling fsync.

This option can be set at server start or in the postgresql.conf file. If you turn this option off, also consider turning off full_page_writes.

Method used for forcing WAL updates out to disk. If fsync is off then this setting is irrelevant, since 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)

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 that fdatasync is the default on Linux. This option can be set at server start or in the postgresql.conf file.

When this option 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 parameter is currently ignored (treated as always on) because turning it off can cause failure to recover from crashes even when no hardware or OS-level error occurred. This will be fixed in some future release, or else the parameter will be removed entirely.

Number of disk-page buffers allocated in shared memory for WAL data. The default is 8. The setting need only be large enough to hold the amount of WAL data generated by one typical transaction, since the data is written out to disk at every transaction commit. This option can only be set at server start.

Increasing this parameter may cause PostgreSQL to request more System V shared memory than your operating system's default configuration allows. See Section 16.4.1 for information on how to adjust those parameters, if necessary.

Time delay between writing a commit record to the WAL buffer and flushing the buffer out to disk, in microseconds. A nonzero delay can allow multiple transactions to be committed with only one fsync() system call, if system load is high enough that additional transactions become ready to commit within the given interval. But the delay is just wasted if no other transactions become ready to commit. Therefore, the delay is only performed if at least commit_siblings other transactions are active at the instant that a server process has written its commit record. The default is zero (no delay).

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.

Maximum distance between automatic WAL checkpoints, in log file segments (each segment is normally 16 megabytes). The default is three. This option can only be set at server start or in the postgresql.conf file.

Maximum time between automatic WAL checkpoints, in seconds. The default is 300 seconds. This option can only be set at server start or in the postgresql.conf file.

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 that checkpoint_segments ought to be raised). The default is 30 seconds. Zero disables the warning.

The shell command to execute to archive a completed segment of the WAL file series. If this is an empty string (the default), WAL archiving is disabled. Any %p in the string is replaced by the path name of the file to archive, and any %f is replaced by the file name only. (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. For more information see Section 23.3.1. This option can only be set at server start or in the postgresql.conf file.

It is important for the command to return a zero exit status if and only if it succeeds. Examples:

archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
archive_command = 'copy "%p" /mnt/server/archivedir/"%f"'  # Windows