See also Section 27.3, “WAL Configuration” for details on WAL tuning.
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 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_writethrough
(call fsync()
at each commit, forcing write-through of any disk write cache)
fsync
(call fsync()
at each commit)
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.
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, “Continuous Archiving and Point-In-Time Recovery (PITR)”).
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, “Shared Memory and Semaphores” 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.
checkpoint_segments
(integer
)
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.
archive_command
(string
)
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, “Setting up WAL archiving”.
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:
archive_command = 'cp "%p" /mnt/server/archivedir/"%f"' archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
archive_timeout
(integer
)
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.