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 Section 14.4.7).
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 (archive_mode) 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
hot_standby 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 wal_sync_method).
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 synchronous_commit
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
full_page_writes.
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 wal_writer_delay.) Unlike
fsync, 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)
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. 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 in Section 29.1.
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 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 off
fsync, 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)
(see Section 24.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 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%) of shared_buffers, but not less
than 64kB nor more than the size of one WAL
segment, typically 16MB. This value can be set
manually if the automatic choice is too large or too small,
but any positive value less than 32kB will be
treated as 32kB.
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.
Increasing this parameter might cause PostgreSQL
to request more System V shared
memory than your operating system's default configuration
allows. See Section 17.4.1 for information on how to
adjust those parameters, if necessary.
wal_writer_delay (integer)
Specifies the delay between activity rounds for the WAL writer.
In each round the writer will flush WAL to disk. It then sleeps for
wal_writer_delay milliseconds, and repeats. The default
value is 200 milliseconds (200ms). Note that on many
systems, the effective resolution of sleep delays is 10 milliseconds;
setting wal_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 the
postgresql.conf file or on the server command line.
commit_delay (integer)
When the commit data for a transaction is flushed to disk, any
additional commits ready at that time are also flushed out.
commit_delay adds a time delay, set in
microseconds, before a transaction attempts to
flush the WAL buffer out to disk. A nonzero delay can allow more
transactions to be committed with only one flush operation, 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 commit_delay is zero (no delay).
Since all pending commit data will be written at every flush
regardless of this setting, it is rare that adding delay
by increasing this parameter will actually improve performance.
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 number of log file segments between automatic WAL
checkpoints (each segment is normally 16 megabytes). The default
is three segments. Increasing this parameter can increase the
amount of time needed for crash recovery.
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).
Increasing this parameter can increase the amount of time needed
for crash recovery.
This parameter can only be set in the postgresql.conf
file or on the server command line.
checkpoint_completion_target (floating point)
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 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.
When archive_mode is enabled, completed WAL segments
are sent to archive storage by setting
archive_command.
archive_mode and archive_command are
separate variables so that archive_command can be
changed without leaving archiving mode.
This parameter can only be set at server start.
archive_mode cannot be enabled when
wal_level is set to minimal.
archive_command (string)
The 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 see
Section 24.3.1.
This parameter can only be set in the postgresql.conf
file or on the server command line. It is ignored unless
archive_mode was enabled at server start.
If archive_command is an empty string (the default) while
archive_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. Setting
archive_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.
archive_timeout (integer)
The archive_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 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, and there has been any database activity,
including a single checkpoint. (Increasing
checkpoint_timeout will reduce unnecessary
checkpoints on an idle system.)
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. 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 the
postgresql.conf file or on the server command line.