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 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 parameter 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 full_page_writes.

wal_sync_method (string)

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)

The open_* options also use
O_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 that fdatasync is the default on Linux. This
parameter can only be set in the postgresql.conf file or on the server
command line.

full_page_writes (boolean)

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 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 a price in 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 a corrupt database after an operating
system crash or power failure. The risks are similar to
turning off fsync, though
smaller. It may be safe to turn off this parameter if you
have hardware (such as a battery-backed disk controller)
or file-system software that reduces the risk of partial
page writes to an acceptably low level (e.g., ReiserFS
4).

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

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

wal_buffers
(integer)

The amount of memory used in shared memory for WAL
data. The default is 64 kilobytes (64kB). 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 parameter 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.

commit_delay (integer)

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).

commit_siblings (integer)

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 transactions.

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

checkpoint_timeout (integer)

Maximum time between automatic WAL checkpoints, in
seconds. The default is five minutes (5min). This parameter can only be set in
the postgresql.conf file or on
the server command line.

checkpoint_warning (integer)

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 (30s). Zero disables the warning. This
parameter can only be set in the postgresql.conf file or on the server
command line.

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 parameter can only be set in the
postgresql.conf file or on the
server command line.

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

The archive_command
is only invoked on 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 put a limit on how old unarchived
data can be, you can set archive_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. 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 short
archive_timeout — it will bloat
your archive storage. archive_timeout settings of a minute or so
are usually reasonable. This parameter can only be set in
the postgresql.conf file or on
the server command line.

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.