<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>19.5. Write Ahead Log</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="runtime-config-resource.html" title="19.4. Resource Consumption" /><link rel="next" href="runtime-config-replication.html" title="19.6. Replication" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">19.5. Write Ahead Log</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-resource.html" title="19.4. Resource Consumption">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 19. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-replication.html" title="19.6. Replication">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="RUNTIME-CONFIG-WAL"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.5. Write Ahead Log</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS">19.5.1. Settings</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS">19.5.2. Checkpoints</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING">19.5.3. Archiving</a></span></dt></dl></div><p>
For additional information on tuning these settings,
see <a class="xref" href="wal-configuration.html" title="30.4. WAL Configuration">Section 30.4</a>.
</p><div class="sect2" id="RUNTIME-CONFIG-WAL-SETTINGS"><div class="titlepage"><div><div><h3 class="title">19.5.1. Settings</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-WAL-LEVEL"><span class="term"><code class="varname">wal_level</code> (<code class="type">enum</code>)
<a id="id-1.6.6.8.3.2.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
<code class="varname">wal_level</code> determines how much information is written to
the WAL. The default value is <code class="literal">replica</code>, which writes enough
data to support WAL archiving and replication, including running
read-only queries on a standby server. <code class="literal">minimal</code> removes all
logging except the information required to recover from a crash or
immediate shutdown. Finally,
<code class="literal">logical</code> adds information necessary to support logical
decoding. Each level includes the information logged at all lower
levels. This parameter can only be set at server start.
</p><p>
In <code class="literal">minimal</code> level, WAL-logging of some bulk
operations can be safely skipped, which can make those
operations much faster (see <a class="xref" href="populate.html#POPULATE-PITR" title="14.4.7. Disable WAL Archival and Streaming Replication">Section 14.4.7</a>).
Operations in which this optimization can be applied include:
</p><table border="0" summary="Simple list" class="simplelist"><tr><td><code class="command">CREATE TABLE AS</code></td></tr><tr><td><code class="command">CREATE INDEX</code></td></tr><tr><td><code class="command">CLUSTER</code></td></tr><tr><td><code class="command">COPY</code> into tables that were created or truncated in the same
transaction</td></tr></table><p>
But minimal WAL does not contain enough information to reconstruct the
data from a base backup and the WAL logs, so <code class="literal">replica</code> or
higher must be used to enable WAL archiving
(<a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-MODE">archive_mode</a>) and streaming replication.
</p><p>
In <code class="literal">logical</code> level, the same information is logged as
with <code class="literal">replica</code>, plus information needed to allow
extracting logical change sets from the WAL. Using a level of
<code class="literal">logical</code> will increase the WAL volume, particularly if many
tables are configured for <code class="literal">REPLICA IDENTITY FULL</code> and
many <code class="command">UPDATE</code> and <code class="command">DELETE</code> statements are
executed.
</p><p>
In releases prior to 9.6, this parameter also allowed the
values <code class="literal">archive</code> and <code class="literal">hot_standby</code>.
These are still accepted but mapped to <code class="literal">replica</code>.
</p></dd><dt id="GUC-FSYNC"><span class="term"><code class="varname">fsync</code> (<code class="type">boolean</code>)
<a id="id-1.6.6.8.3.2.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
If this parameter is on, the <span class="productname">PostgreSQL</span> server
will try to make sure that updates are physically written to
disk, by issuing <code class="function">fsync()</code> system calls or various
equivalent methods (see <a class="xref" href="runtime-config-wal.html#GUC-WAL-SYNC-METHOD">wal_sync_method</a>).
This ensures that the database cluster can recover to a
consistent state after an operating system or hardware crash.
</p><p>
While turning off <code class="varname">fsync</code> 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 <code class="varname">fsync</code> if
you can easily recreate your entire database from external
data.
</p><p>
Examples of safe circumstances for turning off
<code class="varname">fsync</code> 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 <code class="varname">fsync</code>.
</p><p>
For reliable recovery when changing <code class="varname">fsync</code>
off to on, it is necessary to force all modified buffers in the
kernel to durable storage. This can be done while the cluster
is shutdown or while <code class="varname">fsync</code> is on by running <code class="command">initdb
--sync-only</code>, running <code class="command">sync</code>, unmounting the
file system, or rebooting the server.
</p><p>
In many situations, turning off <a class="xref" href="runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT">synchronous_commit</a>
for noncritical transactions can provide much of the potential
performance benefit of turning off <code class="varname">fsync</code>, without
the attendant risks of data corruption.
</p><p>
<code class="varname">fsync</code> can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
If you turn this parameter off, also consider turning off
<a class="xref" href="runtime-config-wal.html#GUC-FULL-PAGE-WRITES">full_page_writes</a>.
</p></dd><dt id="GUC-SYNCHRONOUS-COMMIT"><span class="term"><code class="varname">synchronous_commit</code> (<code class="type">enum</code>)
<a id="id-1.6.6.8.3.2.3.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies whether transaction commit will wait for WAL records
to be written to disk before the command returns a <span class="quote">“<span class="quote">success</span>”</span>
indication to the client. Valid values are <code class="literal">on</code>,
<code class="literal">remote_apply</code>, <code class="literal">remote_write</code>, <code class="literal">local</code>,
and <code class="literal">off</code>. The default, and safe, setting
is <code class="literal">on</code>. When <code class="literal">off</code>, 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 <a class="xref" href="runtime-config-wal.html#GUC-WAL-WRITER-DELAY">wal_writer_delay</a>.) Unlike
<a class="xref" href="runtime-config-wal.html#GUC-FSYNC">fsync</a>, setting this parameter to <code class="literal">off</code>
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 <code class="varname">synchronous_commit</code> off
can be a useful alternative when performance is more important than
exact certainty about the durability of a transaction. For more
discussion see <a class="xref" href="wal-async-commit.html" title="30.3. Asynchronous Commit">Section 30.3</a>.
</p><p>
If <a class="xref" href="runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES">synchronous_standby_names</a> is non-empty, this
parameter also controls whether or not transaction commits will wait
for their WAL records to be replicated to the standby server(s).
When set to <code class="literal">on</code>, commits will wait until replies
from the current synchronous standby(s) indicate they have received
the commit record of the transaction and flushed it to disk. This
ensures the transaction will not be lost unless both the primary and
all synchronous standbys suffer corruption of their database storage.
When set to <code class="literal">remote_apply</code>, commits will wait until replies
from the current synchronous standby(s) indicate they have received the
commit record of the transaction and applied it, so that it has become
visible to queries on the standby(s).
When set to <code class="literal">remote_write</code>, commits will wait until replies
from the current synchronous standby(s) indicate they have
received the commit record of the transaction and written it out to
their operating system. This setting is sufficient to
ensure data preservation even if a standby instance of
<span class="productname">PostgreSQL</span> were to crash, but not if the standby
suffers an operating-system-level crash, since the data has not
necessarily reached stable storage on the standby.
Finally, the setting <code class="literal">local</code> causes commits to wait for
local flush to disk, but not for replication. This is not usually
desirable when synchronous replication is in use, but is provided for
completeness.
</p><p>
If <code class="varname">synchronous_standby_names</code> is empty, the settings
<code class="literal">on</code>, <code class="literal">remote_apply</code>, <code class="literal">remote_write</code>
and <code class="literal">local</code> all provide the same synchronization level:
transaction commits only wait for local flush to disk.
</p><p>
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 <code class="command">SET
LOCAL synchronous_commit TO OFF</code> within the transaction.
</p></dd><dt id="GUC-WAL-SYNC-METHOD"><span class="term"><code class="varname">wal_sync_method</code> (<code class="type">enum</code>)
<a id="id-1.6.6.8.3.2.4.1.3" class="indexterm"></a>
</span></dt><dd><p>
Method used for forcing WAL updates out to disk.
If <code class="varname">fsync</code> is off then this setting is irrelevant,
since WAL file updates will not be forced out at all.
Possible values are:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
<code class="literal">open_datasync</code> (write WAL files with <code class="function">open()</code> option <code class="symbol">O_DSYNC</code>)
</p></li><li class="listitem"><p>
<code class="literal">fdatasync</code> (call <code class="function">fdatasync()</code> at each commit)
</p></li><li class="listitem"><p>
<code class="literal">fsync</code> (call <code class="function">fsync()</code> at each commit)
</p></li><li class="listitem"><p>
<code class="literal">fsync_writethrough</code> (call <code class="function">fsync()</code> at each commit, forcing write-through of any disk write cache)
</p></li><li class="listitem"><p>
<code class="literal">open_sync</code> (write WAL files with <code class="function">open()</code> option <code class="symbol">O_SYNC</code>)
</p></li></ul></div><p>
The <code class="literal">open_</code>* options also use <code class="literal">O_DIRECT</code> 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 <code class="literal">fdatasync</code> 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 <a class="xref" href="wal-reliability.html" title="30.1. Reliability">Section 30.1</a>.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-FULL-PAGE-WRITES"><span class="term"><code class="varname">full_page_writes</code> (<code class="type">boolean</code>)
<a id="id-1.6.6.8.3.2.5.1.3" class="indexterm"></a>
</span></dt><dd><p>
When this parameter is on, the <span class="productname">PostgreSQL</span> 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.)
</p><p>
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
<code class="varname">fsync</code>, though smaller, and it should be turned off
only based on the same circumstances recommended for that parameter.
</p><p>
Turning off this parameter does not affect use of
WAL archiving for point-in-time recovery (PITR)
(see <a class="xref" href="continuous-archiving.html" title="25.3. Continuous Archiving and Point-in-Time Recovery (PITR)">Section 25.3</a>).
</p><p>
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
The default is <code class="literal">on</code>.
</p></dd><dt id="GUC-WAL-LOG-HINTS"><span class="term"><code class="varname">wal_log_hints</code> (<code class="type">boolean</code>)
<a id="id-1.6.6.8.3.2.6.1.3" class="indexterm"></a>
</span></dt><dd><p>
When this parameter is <code class="literal">on</code>, the <span class="productname">PostgreSQL</span>
server writes the entire content of each disk page to WAL during the
first modification of that page after a checkpoint, even for
non-critical modifications of so-called hint bits.
</p><p>
If data checksums are enabled, hint bit updates are always WAL-logged
and this setting is ignored. You can use this setting to test how much
extra WAL-logging would occur if your database had data checksums
enabled.
</p><p>
This parameter can only be set at server start. The default value is <code class="literal">off</code>.
</p></dd><dt id="GUC-WAL-COMPRESSION"><span class="term"><code class="varname">wal_compression</code> (<code class="type">boolean</code>)
<a id="id-1.6.6.8.3.2.7.1.3" class="indexterm"></a>
</span></dt><dd><p>
When this parameter is <code class="literal">on</code>, the <span class="productname">PostgreSQL</span>
server compresses a full page image written to WAL when
<a class="xref" href="runtime-config-wal.html#GUC-FULL-PAGE-WRITES">full_page_writes</a> is on or during a base backup.
A compressed page image will be decompressed during WAL replay.
The default value is <code class="literal">off</code>.
Only superusers can change this setting.
</p><p>
Turning this parameter on can reduce the WAL volume without
increasing the risk of unrecoverable data corruption,
but at the cost of some extra CPU spent on the compression during
WAL logging and on the decompression during WAL replay.
</p></dd><dt id="GUC-WAL-BUFFERS"><span class="term"><code class="varname">wal_buffers</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.3.2.8.1.3" class="indexterm"></a>
</span></dt><dd><p>
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 <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but not less
than <code class="literal">64kB</code> nor more than the size of one WAL
segment, typically <code class="literal">16MB</code>. This value can be set
manually if the automatic choice is too large or too small,
but any positive value less than <code class="literal">32kB</code> will be
treated as <code class="literal">32kB</code>.
This parameter can only be set at server start.
</p><p>
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.
</p></dd><dt id="GUC-WAL-WRITER-DELAY"><span class="term"><code class="varname">wal_writer_delay</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.3.2.9.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies how often the WAL writer flushes WAL. After flushing WAL it
sleeps for <code class="varname">wal_writer_delay</code> milliseconds, unless woken up
by an asynchronously committing transaction. If the last flush
happened less than <code class="varname">wal_writer_delay</code> milliseconds ago and
less than <code class="varname">wal_writer_flush_after</code> bytes of WAL have been
produced since, then WAL is only written to the operating system, not
flushed to disk.
The default value is 200 milliseconds (<code class="literal">200ms</code>). Note that
on many systems, the effective resolution of sleep delays is 10
milliseconds; setting <code class="varname">wal_writer_delay</code> 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
<code class="filename">postgresql.conf</code> file or on the server command line.
</p></dd><dt id="GUC-WAL-WRITER-FLUSH-AFTER"><span class="term"><code class="varname">wal_writer_flush_after</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.3.2.10.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies how often the WAL writer flushes WAL. If the last flush
happened less than <code class="varname">wal_writer_delay</code> milliseconds ago and
less than <code class="varname">wal_writer_flush_after</code> bytes of WAL have been
produced since, then WAL is only written to the operating system, not
flushed to disk. If <code class="varname">wal_writer_flush_after</code> is set
to <code class="literal">0</code> then WAL data is flushed immediately. The default is
<code class="literal">1MB</code>. This parameter can only be set in the
<code class="filename">postgresql.conf</code> file or on the server command line.
</p></dd><dt id="GUC-COMMIT-DELAY"><span class="term"><code class="varname">commit_delay</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.3.2.11.1.3" class="indexterm"></a>
</span></dt><dd><p>
<code class="varname">commit_delay</code> adds a time delay, measured in
microseconds, before a WAL flush is initiated. This can improve
group commit throughput by allowing a larger number of transactions
to commit via a single WAL flush, if system load is high enough
that additional transactions become ready to commit within the
given interval. However, it also increases latency by up to
<code class="varname">commit_delay</code> microseconds for each WAL
flush. Because the delay is just wasted if no other transactions
become ready to commit, a delay is only performed if at least
<code class="varname">commit_siblings</code> other transactions are active
when a flush is about to be initiated. Also, no delays are
performed if <code class="varname">fsync</code> is disabled.
The default <code class="varname">commit_delay</code> is zero (no delay).
Only superusers can change this setting.
</p><p>
In <span class="productname">PostgreSQL</span> releases prior to 9.3,
<code class="varname">commit_delay</code> behaved differently and was much
less effective: it affected only commits, rather than all WAL flushes,
and waited for the entire configured delay even if the WAL flush
was completed sooner. Beginning in <span class="productname">PostgreSQL</span> 9.3,
the first process that becomes ready to flush waits for the configured
interval, while subsequent processes wait only until the leader
completes the flush operation.
</p></dd><dt id="GUC-COMMIT-SIBLINGS"><span class="term"><code class="varname">commit_siblings</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.3.2.12.1.3" class="indexterm"></a>
</span></dt><dd><p>
Minimum number of concurrent open transactions to require
before performing the <code class="varname">commit_delay</code> 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.
</p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-WAL-CHECKPOINTS"><div class="titlepage"><div><div><h3 class="title">19.5.2. Checkpoints</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-CHECKPOINT-TIMEOUT"><span class="term"><code class="varname">checkpoint_timeout</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.4.2.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
Maximum time between automatic WAL checkpoints, in seconds.
The valid range is between 30 seconds and one day.
The default is five minutes (<code class="literal">5min</code>).
Increasing this parameter can increase the amount of time needed
for crash recovery.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-CHECKPOINT-COMPLETION-TARGET"><span class="term"><code class="varname">checkpoint_completion_target</code> (<code class="type">floating point</code>)
<a id="id-1.6.6.8.4.2.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
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 <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-CHECKPOINT-FLUSH-AFTER"><span class="term"><code class="varname">checkpoint_flush_after</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.4.2.3.1.3" class="indexterm"></a>
</span></dt><dd><p>
Whenever more than <code class="varname">checkpoint_flush_after</code> bytes
have been written while performing a checkpoint, attempt to force the
OS to issue these writes to the underlying storage. Doing so will
limit the amount of dirty data in the kernel's page cache, reducing
the likelihood of stalls when an <code class="function">fsync</code> is issued at the end of the
checkpoint, or when the OS writes data back in larger batches in the
background. Often that will result in greatly reduced transaction
latency, but there also are some cases, especially with workloads
that are bigger than <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but smaller
than the OS's page cache, where performance might degrade. This
setting may have no effect on some platforms. The valid range is
between <code class="literal">0</code>, which disables forced writeback,
and <code class="literal">2MB</code>. The default is <code class="literal">256kB</code> on
Linux, <code class="literal">0</code> elsewhere. (If <code class="symbol">BLCKSZ</code> is not
8kB, the default and maximum values scale proportionally to it.)
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-CHECKPOINT-WARNING"><span class="term"><code class="varname">checkpoint_warning</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.4.2.4.1.3" class="indexterm"></a>
</span></dt><dd><p>
Write a message to the server log if checkpoints caused by
the filling of WAL segment files happen closer together
than this many seconds (which suggests that
<code class="varname">max_wal_size</code> ought to be raised). The default is
30 seconds (<code class="literal">30s</code>). Zero disables the warning.
No warnings will be generated if <code class="varname">checkpoint_timeout</code>
is less than <code class="varname">checkpoint_warning</code>.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-MAX-WAL-SIZE"><span class="term"><code class="varname">max_wal_size</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.4.2.5.1.3" class="indexterm"></a>
</span></dt><dd><p>
Maximum size to let the WAL grow to between automatic WAL
checkpoints. This is a soft limit; WAL size can exceed
<code class="varname">max_wal_size</code> under special circumstances, like
under heavy load, a failing <code class="varname">archive_command</code>, or a high
<code class="varname">wal_keep_segments</code> setting. The default is 1 GB.
Increasing this parameter can increase the amount of time needed for
crash recovery.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-MIN-WAL-SIZE"><span class="term"><code class="varname">min_wal_size</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.4.2.6.1.3" class="indexterm"></a>
</span></dt><dd><p>
As long as WAL disk usage stays below this setting, old WAL files are
always recycled for future use at a checkpoint, rather than removed.
This can be used to ensure that enough WAL space is reserved to
handle spikes in WAL usage, for example when running large batch
jobs. The default is 80 MB.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-WAL-ARCHIVING"><div class="titlepage"><div><div><h3 class="title">19.5.3. Archiving</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-ARCHIVE-MODE"><span class="term"><code class="varname">archive_mode</code> (<code class="type">enum</code>)
<a id="id-1.6.6.8.5.2.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
When <code class="varname">archive_mode</code> is enabled, completed WAL segments
are sent to archive storage by setting
<a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-COMMAND">archive_command</a>. In addition to <code class="literal">off</code>,
to disable, there are two modes: <code class="literal">on</code>, and
<code class="literal">always</code>. During normal operation, there is no
difference between the two modes, but when set to <code class="literal">always</code>
the WAL archiver is enabled also during archive recovery or standby
mode. In <code class="literal">always</code> mode, all files restored from the archive
or streamed with streaming replication will be archived (again). See
<a class="xref" href="warm-standby.html#CONTINUOUS-ARCHIVING-IN-STANDBY" title="26.2.9. Continuous archiving in standby">Section 26.2.9</a> for details.
</p><p>
<code class="varname">archive_mode</code> and <code class="varname">archive_command</code> are
separate variables so that <code class="varname">archive_command</code> can be
changed without leaving archiving mode.
This parameter can only be set at server start.
<code class="varname">archive_mode</code> cannot be enabled when
<code class="varname">wal_level</code> is set to <code class="literal">minimal</code>.
</p></dd><dt id="GUC-ARCHIVE-COMMAND"><span class="term"><code class="varname">archive_command</code> (<code class="type">string</code>)
<a id="id-1.6.6.8.5.2.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
The local shell command to execute to archive a completed WAL file
segment. Any <code class="literal">%p</code> in the string is
replaced by the path name of the file to archive, and any
<code class="literal">%f</code> 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 <code class="literal">%%</code> to embed an actual <code class="literal">%</code> character in the
command. It is important for the command to return a zero
exit status only if it succeeds. For more information see
<a class="xref" href="continuous-archiving.html#BACKUP-ARCHIVING-WAL" title="25.3.1. Setting Up WAL Archiving">Section 25.3.1</a>.
</p><p>
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line. It is ignored unless
<code class="varname">archive_mode</code> was enabled at server start.
If <code class="varname">archive_command</code> is an empty string (the default) while
<code class="varname">archive_mode</code> 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
<code class="varname">archive_command</code> to a command that does nothing but
return true, e.g. <code class="literal">/bin/true</code> (<code class="literal">REM</code> 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.
</p></dd><dt id="GUC-ARCHIVE-TIMEOUT"><span class="term"><code class="varname">archive_timeout</code> (<code class="type">integer</code>)
<a id="id-1.6.6.8.5.2.3.1.3" class="indexterm"></a>
</span></dt><dd><p>
The <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-COMMAND">archive_command</a> 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 <code class="varname">archive_timeout</code> 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 (checkpoints are skipped if there is
no database activity). 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
<code class="varname">archive_timeout</code> — it will bloat your archive
storage. <code class="varname">archive_timeout</code> 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
<code class="filename">postgresql.conf</code> file or on the server command line.
</p></dd></dl></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="runtime-config-resource.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-replication.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">19.4. Resource Consumption </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 19.6. Replication</td></tr></table></div></body></html>
