diff options
Diffstat (limited to 'doc/src/sgml/html/runtime-config-wal.html')
| -rw-r--r-- | doc/src/sgml/html/runtime-config-wal.html | 839 |
1 files changed, 839 insertions, 0 deletions
diff --git a/doc/src/sgml/html/runtime-config-wal.html b/doc/src/sgml/html/runtime-config-wal.html new file mode 100644 index 0000000..f67aa89 --- /dev/null +++ b/doc/src/sgml/html/runtime-config-wal.html @@ -0,0 +1,839 @@ +<?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>20.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="20.4. Resource Consumption" /><link rel="next" href="runtime-config-replication.html" title="20.6. Replication" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">20.5. Write Ahead Log</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-resource.html" title="20.4. Resource Consumption">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 20. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-replication.html" title="20.6. Replication">Next</a></td></tr></table><hr /></div><div class="sect1" id="RUNTIME-CONFIG-WAL"><div class="titlepage"><div><div><h2 class="title" style="clear: both">20.5. Write Ahead Log <a href="#RUNTIME-CONFIG-WAL" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS">20.5.1. Settings</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS">20.5.2. Checkpoints</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING">20.5.3. Archiving</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY">20.5.4. Recovery</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY">20.5.5. Archive Recovery</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET">20.5.6. Recovery Target</a></span></dt></dl></div><p> + For additional information on tuning these settings, + see <a class="xref" href="wal-configuration.html" title="30.5. WAL Configuration">Section 30.5</a>. + </p><div class="sect2" id="RUNTIME-CONFIG-WAL-SETTINGS"><div class="titlepage"><div><div><h3 class="title">20.5.1. Settings <a href="#RUNTIME-CONFIG-WAL-SETTINGS" class="id_link">#</a></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.7.8.3.2.1.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-LEVEL" class="id_link">#</a></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> + The <code class="literal">minimal</code> level generates the least WAL + volume. It logs no row information for permanent relations + in transactions that create or + rewrite them. This can make 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 that initiate this + optimization include: + </p><table border="0" summary="Simple list" class="simplelist"><tr><td><code class="command">ALTER ... SET TABLESPACE</code></td></tr><tr><td><code class="command">CLUSTER</code></td></tr><tr><td><code class="command">CREATE TABLE</code></td></tr><tr><td><code class="command">REFRESH MATERIALIZED VIEW</code> + (without <code class="option">CONCURRENTLY</code>)</td></tr><tr><td><code class="command">REINDEX</code></td></tr><tr><td><code class="command">TRUNCATE</code></td></tr></table><p> + However, minimal WAL does not contain sufficient information for + point-in-time recovery, so <code class="literal">replica</code> or + higher must be used to enable continuous archiving + (<a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-MODE">archive_mode</a>) and streaming binary replication. + In fact, the server will not even start in this mode if + <code class="varname">max_wal_senders</code> is non-zero. + Note that changing <code class="varname">wal_level</code> to + <code class="literal">minimal</code> makes previous base backups unusable + for point-in-time recovery and standby servers. + </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 + extract 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.7.8.3.2.2.1.3" class="indexterm"></a> + </span> <a href="#GUC-FSYNC" class="id_link">#</a></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.7.8.3.2.3.1.3" class="indexterm"></a> + </span> <a href="#GUC-SYNCHRONOUS-COMMIT" class="id_link">#</a></dt><dd><p> + Specifies how much WAL processing must complete before + the database server returns a <span class="quote">“<span class="quote">success</span>”</span> + indication to the client. Valid values are + <code class="literal">remote_apply</code>, <code class="literal">on</code> + (the default), <code class="literal">remote_write</code>, + <code class="literal">local</code>, and <code class="literal">off</code>. + </p><p> + If <code class="varname">synchronous_standby_names</code> is empty, + the only meaningful settings are <code class="literal">on</code> and + <code class="literal">off</code>; <code class="literal">remote_apply</code>, + <code class="literal">remote_write</code> and <code class="literal">local</code> + all provide the same local synchronization level + as <code class="literal">on</code>. The local behavior of all + non-<code class="literal">off</code> modes is to wait for local flush of WAL + to disk. In <code class="literal">off</code> mode, there is no waiting, + so there can be a delay between when success is reported to the + client and when the transaction is later 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.4. Asynchronous Commit">Section 30.4</a>. + </p><p> + If <a class="xref" href="runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES">synchronous_standby_names</a> is non-empty, + <code class="varname">synchronous_commit</code> also controls whether + transaction commits will wait for their WAL records to be + processed on the standby server(s). + </p><p> + 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), + and also written to durable storage on the standbys. This will + cause much larger commit delays than previous settings since + it waits for WAL replay. When set to <code class="literal">on</code>, + commits wait until replies + from the current synchronous standby(s) indicate they have received + the commit record of the transaction and flushed it to durable storage. 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_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 to + their file systems. This setting ensures data preservation if a standby instance of + <span class="productname">PostgreSQL</span> crashes, but not if the standby + suffers an operating-system-level crash because the data has not + necessarily reached durable storage on the standby. + The setting <code class="literal">local</code> causes commits to wait for + local flush to disk, but not for replication. This is usually not + desirable when synchronous replication is in use, but is provided for + completeness. + </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><p> + <a class="xref" href="runtime-config-wal.html#SYNCHRONOUS-COMMIT-MATRIX" title="Table 20.1. synchronous_commit Modes">Table 20.1</a> summarizes the + capabilities of the <code class="varname">synchronous_commit</code> settings. + </p><div class="table" id="SYNCHRONOUS-COMMIT-MATRIX"><p class="title"><strong>Table 20.1. synchronous_commit Modes</strong></p><div class="table-contents"><table class="table" summary="synchronous_commit Modes" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /><col class="col4" /><col class="col5" /></colgroup><thead><tr><th>synchronous_commit setting</th><th>local durable commit</th><th>standby durable commit after PG crash</th><th>standby durable commit after OS crash</th><th>standby query consistency</th></tr></thead><tbody><tr><td>remote_apply</td><td align="center">•</td><td align="center">•</td><td align="center">•</td><td align="center">•</td></tr><tr><td>on</td><td align="center">•</td><td align="center">•</td><td align="center">•</td><td align="center"> </td></tr><tr><td>remote_write</td><td align="center">•</td><td align="center">•</td><td align="center"> </td><td align="center"> </td></tr><tr><td>local</td><td align="center">•</td><td align="center"> </td><td align="center"> </td><td align="center"> </td></tr><tr><td>off</td><td align="center"> </td><td align="center"> </td><td align="center"> </td><td align="center"> </td></tr></tbody></table></div></div><br class="table-break" /></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.7.8.3.2.4.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-SYNC-METHOD" class="id_link">#</a></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> + 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 and FreeBSD. 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.7.8.3.2.5.1.3" class="indexterm"></a> + </span> <a href="#GUC-FULL-PAGE-WRITES" class="id_link">#</a></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="26.3. Continuous Archiving and Point-in-Time Recovery (PITR)">Section 26.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.7.8.3.2.6.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-LOG-HINTS" class="id_link">#</a></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">enum</code>) + <a id="id-1.6.7.8.3.2.7.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-COMPRESSION" class="id_link">#</a></dt><dd><p> + This parameter enables compression of WAL using the specified + compression method. + When enabled, the <span class="productname">PostgreSQL</span> + server compresses full page images 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 supported methods are <code class="literal">pglz</code>, + <code class="literal">lz4</code> (if <span class="productname">PostgreSQL</span> + was compiled with <code class="option">--with-lz4</code>) and + <code class="literal">zstd</code> (if <span class="productname">PostgreSQL</span> + was compiled with <code class="option">--with-zstd</code>). + The default value is <code class="literal">off</code>. + Only superusers and users with the appropriate <code class="literal">SET</code> + privilege can change this setting. + </p><p> + Enabling compression 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-INIT-ZERO"><span class="term"><code class="varname">wal_init_zero</code> (<code class="type">boolean</code>) + <a id="id-1.6.7.8.3.2.8.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-INIT-ZERO" class="id_link">#</a></dt><dd><p> + If set to <code class="literal">on</code> (the default), this option causes new + WAL files to be filled with zeroes. On some file systems, this ensures + that space is allocated before we need to write WAL records. However, + <em class="firstterm">Copy-On-Write</em> (COW) file systems may not benefit + from this technique, so the option is given to skip the unnecessary + work. If set to <code class="literal">off</code>, only the final byte is written + when the file is created so that it has the expected size. + </p></dd><dt id="GUC-WAL-RECYCLE"><span class="term"><code class="varname">wal_recycle</code> (<code class="type">boolean</code>) + <a id="id-1.6.7.8.3.2.9.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-RECYCLE" class="id_link">#</a></dt><dd><p> + If set to <code class="literal">on</code> (the default), this option causes WAL + files to be recycled by renaming them, avoiding the need to create new + ones. On COW file systems, it may be faster to create new ones, so the + option is given to disable this behavior. + </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.7.8.3.2.10.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-BUFFERS" class="id_link">#</a></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>. + If this value is specified without units, it is taken as WAL blocks, + that is <code class="symbol">XLOG_BLCKSZ</code> bytes, typically 8kB. + 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.7.8.3.2.11.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-WRITER-DELAY" class="id_link">#</a></dt><dd><p> + Specifies how often the WAL writer flushes WAL, in time terms. + After flushing WAL the writer sleeps for the length of time given + by <code class="varname">wal_writer_delay</code>, unless woken up sooner + by an asynchronously committing transaction. If the last flush + happened less than <code class="varname">wal_writer_delay</code> ago and less + than <code class="varname">wal_writer_flush_after</code> worth of WAL has been + produced since, then WAL is only written to the operating system, not + flushed to disk. + If this value is specified without units, it is taken as milliseconds. + 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.7.8.3.2.12.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-WRITER-FLUSH-AFTER" class="id_link">#</a></dt><dd><p> + Specifies how often the WAL writer flushes WAL, in volume terms. + If the last flush happened less + than <code class="varname">wal_writer_delay</code> ago and less + than <code class="varname">wal_writer_flush_after</code> worth of WAL has 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 always flushed immediately. + If this value is specified without units, it is taken as WAL blocks, + that is <code class="symbol">XLOG_BLCKSZ</code> bytes, typically 8kB. + 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-WAL-SKIP-THRESHOLD"><span class="term"><code class="varname">wal_skip_threshold</code> (<code class="type">integer</code>) + <a id="id-1.6.7.8.3.2.13.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-SKIP-THRESHOLD" class="id_link">#</a></dt><dd><p> + When <code class="varname">wal_level</code> is <code class="literal">minimal</code> and a + transaction commits after creating or rewriting a permanent relation, + this setting determines how to persist the new data. If the data is + smaller than this setting, write it to the WAL log; otherwise, use an + fsync of affected files. Depending on the properties of your storage, + raising or lowering this value might help if such commits are slowing + concurrent transactions. If this value is specified without units, it + is taken as kilobytes. The default is two megabytes + (<code class="literal">2MB</code>). + </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.7.8.3.2.14.1.3" class="indexterm"></a> + </span> <a href="#GUC-COMMIT-DELAY" class="id_link">#</a></dt><dd><p> + Setting <code class="varname">commit_delay</code> adds a time delay + 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 the + <code class="varname">commit_delay</code> 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. + If this value is specified without units, it is taken as microseconds. + The default <code class="varname">commit_delay</code> is zero (no delay). + Only superusers and users with the appropriate <code class="literal">SET</code> + privilege 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.7.8.3.2.15.1.3" class="indexterm"></a> + </span> <a href="#GUC-COMMIT-SIBLINGS" class="id_link">#</a></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">20.5.2. Checkpoints <a href="#RUNTIME-CONFIG-WAL-CHECKPOINTS" class="id_link">#</a></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.7.8.4.2.1.1.3" class="indexterm"></a> + </span> <a href="#GUC-CHECKPOINT-TIMEOUT" class="id_link">#</a></dt><dd><p> + Maximum time between automatic WAL checkpoints. + If this value is specified without units, it is taken as 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.7.8.4.2.2.1.3" class="indexterm"></a> + </span> <a href="#GUC-CHECKPOINT-COMPLETION-TARGET" class="id_link">#</a></dt><dd><p> + Specifies the target of checkpoint completion, as a fraction of + total time between checkpoints. The default is 0.9, which spreads the + checkpoint across almost all of the available interval, providing fairly + consistent I/O load while also leaving some time for checkpoint + completion overhead. Reducing this parameter is not recommended because + it causes the checkpoint to complete faster. This results in a higher + rate of I/O during the checkpoint followed by a period of less I/O between + the checkpoint completion and the next scheduled checkpoint. 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.7.8.4.2.3.1.3" class="indexterm"></a> + </span> <a href="#GUC-CHECKPOINT-FLUSH-AFTER" class="id_link">#</a></dt><dd><p> + Whenever more than this amount of data has 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. + If this value is specified without units, it is taken as blocks, + that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. + 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.7.8.4.2.4.1.3" class="indexterm"></a> + </span> <a href="#GUC-CHECKPOINT-WARNING" class="id_link">#</a></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 amount of time (which suggests that + <code class="varname">max_wal_size</code> ought to be raised). + If this value is specified without units, it is taken as seconds. + 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.7.8.4.2.5.1.3" class="indexterm"></a> + </span> <a href="#GUC-MAX-WAL-SIZE" class="id_link">#</a></dt><dd><p> + Maximum size to let the WAL grow during automatic + checkpoints. This is a soft limit; WAL size can exceed + <code class="varname">max_wal_size</code> under special circumstances, such as + heavy load, a failing <code class="varname">archive_command</code> or <code class="varname">archive_library</code>, or a high + <code class="varname">wal_keep_size</code> setting. + If this value is specified without units, it is taken as megabytes. + 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.7.8.4.2.6.1.3" class="indexterm"></a> + </span> <a href="#GUC-MIN-WAL-SIZE" class="id_link">#</a></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. + If this value is specified without units, it is taken as megabytes. + 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">20.5.3. Archiving <a href="#RUNTIME-CONFIG-WAL-ARCHIVING" class="id_link">#</a></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.7.8.5.2.1.1.3" class="indexterm"></a> + </span> <a href="#GUC-ARCHIVE-MODE" class="id_link">#</a></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> or + <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-LIBRARY">archive_library</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="27.2.9. Continuous Archiving in Standby">Section 27.2.9</a> for details. + </p><p> + <code class="varname">archive_mode</code> is a separate setting from + <code class="varname">archive_command</code> and + <code class="varname">archive_library</code> so that + <code class="varname">archive_command</code> and + <code class="varname">archive_library</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.7.8.5.2.2.1.3" class="indexterm"></a> + </span> <a href="#GUC-ARCHIVE-COMMAND" class="id_link">#</a></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="26.3.1. Setting Up WAL Archiving">Section 26.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 only used if + <code class="varname">archive_mode</code> was enabled at server start and + <code class="varname">archive_library</code> is set to an empty string. If both + <code class="varname">archive_command</code> and <code class="varname">archive_library</code> + are set, an error will be raised. + If <code class="varname">archive_command</code> is an empty string (the default) while + <code class="varname">archive_mode</code> is enabled (and <code class="varname">archive_library</code> + is set to an empty string), 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-LIBRARY"><span class="term"><code class="varname">archive_library</code> (<code class="type">string</code>) + <a id="id-1.6.7.8.5.2.3.1.3" class="indexterm"></a> + </span> <a href="#GUC-ARCHIVE-LIBRARY" class="id_link">#</a></dt><dd><p> + The library to use for archiving completed WAL file segments. If set to + an empty string (the default), archiving via shell is enabled, and + <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-COMMAND">archive_command</a> is used. If both + <code class="varname">archive_command</code> and <code class="varname">archive_library</code> + are set, an error will be raised. Otherwise, the specified + shared library is used for archiving. The WAL archiver process is + restarted by the postmaster when this parameter changes. For more + information, see <a class="xref" href="continuous-archiving.html#BACKUP-ARCHIVING-WAL" title="26.3.1. Setting Up WAL Archiving">Section 26.3.1</a> and + <a class="xref" href="archive-modules.html" title="Chapter 51. Archive Modules">Chapter 51</a>. + </p><p> + 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-ARCHIVE-TIMEOUT"><span class="term"><code class="varname">archive_timeout</code> (<code class="type">integer</code>) + <a id="id-1.6.7.8.5.2.4.1.3" class="indexterm"></a> + </span> <a href="#GUC-ARCHIVE-TIMEOUT" class="id_link">#</a></dt><dd><p> + The <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-COMMAND">archive_command</a> or <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-LIBRARY">archive_library</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 amount of time has 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 primary + server more quickly than that. + If this value is specified without units, it is taken as seconds. + 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-RECOVERY"><div class="titlepage"><div><div><h3 class="title">20.5.4. Recovery <a href="#RUNTIME-CONFIG-WAL-RECOVERY" class="id_link">#</a></h3></div></div></div><a id="id-1.6.7.8.6.2" class="indexterm"></a><p> + This section describes the settings that apply to recovery in general, + affecting crash recovery, streaming replication and archive-based + replication. + </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-RECOVERY-PREFETCH"><span class="term"><code class="varname">recovery_prefetch</code> (<code class="type">enum</code>) + <a id="id-1.6.7.8.6.4.1.1.3" class="indexterm"></a> + </span> <a href="#GUC-RECOVERY-PREFETCH" class="id_link">#</a></dt><dd><p> + Whether to try to prefetch blocks that are referenced in the WAL that + are not yet in the buffer pool, during recovery. Valid values are + <code class="literal">off</code>, <code class="literal">on</code> and + <code class="literal">try</code> (the default). The setting + <code class="literal">try</code> enables + prefetching only if the operating system provides the + <code class="function">posix_fadvise</code> function, which is currently used + to implement prefetching. Note that some operating systems provide the + function, but it doesn't do anything. + </p><p> + Prefetching blocks that will soon be needed can reduce I/O wait times + during recovery with some workloads. + See also the <a class="xref" href="runtime-config-wal.html#GUC-WAL-DECODE-BUFFER-SIZE">wal_decode_buffer_size</a> and + <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-IO-CONCURRENCY">maintenance_io_concurrency</a> settings, which limit + prefetching activity. + </p></dd><dt id="GUC-WAL-DECODE-BUFFER-SIZE"><span class="term"><code class="varname">wal_decode_buffer_size</code> (<code class="type">integer</code>) + <a id="id-1.6.7.8.6.4.2.1.3" class="indexterm"></a> + </span> <a href="#GUC-WAL-DECODE-BUFFER-SIZE" class="id_link">#</a></dt><dd><p> + A limit on how far ahead the server can look in the WAL, to find + blocks to prefetch. If this value is specified without units, it is + taken as bytes. + The default is 512kB. + </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY"><div class="titlepage"><div><div><h3 class="title">20.5.5. Archive Recovery <a href="#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY" class="id_link">#</a></h3></div></div></div><a id="id-1.6.7.8.7.2" class="indexterm"></a><p> + This section describes the settings that apply only for the duration of + the recovery. They must be reset for any subsequent recovery you wish to + perform. + </p><p> + <span class="quote">“<span class="quote">Recovery</span>”</span> covers using the server as a standby or for + executing a targeted recovery. Typically, standby mode would be used to + provide high availability and/or read scalability, whereas a targeted + recovery is used to recover from data loss. + </p><p> + To start the server in standby mode, create a file called + <code class="filename">standby.signal</code><a id="id-1.6.7.8.7.5.2" class="indexterm"></a> + in the data directory. The server will enter recovery and will not stop + recovery when the end of archived WAL is reached, but will keep trying to + continue recovery by connecting to the sending server as specified by the + <code class="varname">primary_conninfo</code> setting and/or by fetching new WAL + segments using <code class="varname">restore_command</code>. For this mode, the + parameters from this section and <a class="xref" href="runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-STANDBY" title="20.6.3. Standby Servers">Section 20.6.3</a> are of interest. + Parameters from <a class="xref" href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET" title="20.5.6. Recovery Target">Section 20.5.6</a> will + also be applied but are typically not useful in this mode. + </p><p> + To start the server in targeted recovery mode, create a file called + <code class="filename">recovery.signal</code><a id="id-1.6.7.8.7.6.2" class="indexterm"></a> + in the data directory. If both <code class="filename">standby.signal</code> and + <code class="filename">recovery.signal</code> files are created, standby mode + takes precedence. Targeted recovery mode ends when the archived WAL is + fully replayed, or when <code class="varname">recovery_target</code> is reached. + In this mode, the parameters from both this section and <a class="xref" href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET" title="20.5.6. Recovery Target">Section 20.5.6</a> will be used. + </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-RESTORE-COMMAND"><span class="term"><code class="varname">restore_command</code> (<code class="type">string</code>) + <a id="id-1.6.7.8.7.7.1.1.3" class="indexterm"></a> + </span> <a href="#GUC-RESTORE-COMMAND" class="id_link">#</a></dt><dd><p> + The local shell command to execute to retrieve an archived segment of + the WAL file series. This parameter is required for archive recovery, + but optional for streaming replication. + Any <code class="literal">%f</code> in the string is + replaced by the name of the file to retrieve from the archive, + and any <code class="literal">%p</code> is replaced by the copy destination path name + on the server. + (The path name is relative to the current working directory, + i.e., the cluster's data directory.) + Any <code class="literal">%r</code> is replaced by the name of the file containing the + last valid restart point. That is the earliest file that must be kept + to allow a restore to be restartable, so this information can be used + to truncate the archive to just the minimum required to support + restarting from the current restore. <code class="literal">%r</code> is typically only + used by warm-standby configurations + (see <a class="xref" href="warm-standby.html" title="27.2. Log-Shipping Standby Servers">Section 27.2</a>). + Write <code class="literal">%%</code> to embed an actual <code class="literal">%</code> character. + </p><p> + It is important for the command to return a zero exit status + only if it succeeds. The command <span class="emphasis"><em>will</em></span> be asked for file + names that are not present in the archive; it must return nonzero + when so asked. Examples: +</p><pre class="programlisting"> +restore_command = 'cp /mnt/server/archivedir/%f "%p"' +restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows +</pre><p> + An exception is that if the command was terminated by a signal (other + than <span class="systemitem">SIGTERM</span>, which is used as part of a + database server shutdown) or an error by the shell (such as command + not found), then recovery will abort and the server will not start up. + </p><p> + 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-ARCHIVE-CLEANUP-COMMAND"><span class="term"><code class="varname">archive_cleanup_command</code> (<code class="type">string</code>) + <a id="id-1.6.7.8.7.7.2.1.3" class="indexterm"></a> + </span> <a href="#GUC-ARCHIVE-CLEANUP-COMMAND" class="id_link">#</a></dt><dd><p> + This optional parameter specifies a shell command that will be executed + at every restartpoint. The purpose of + <code class="varname">archive_cleanup_command</code> is to provide a mechanism for + cleaning up old archived WAL files that are no longer needed by the + standby server. + Any <code class="literal">%r</code> is replaced by the name of the file containing the + last valid restart point. + That is the earliest file that must be <span class="emphasis"><em>kept</em></span> to allow a + restore to be restartable, and so all files earlier than <code class="literal">%r</code> + may be safely removed. + This information can be used to truncate the archive to just the + minimum required to support restart from the current restore. + The <a class="xref" href="pgarchivecleanup.html" title="pg_archivecleanup"><span class="refentrytitle"><span class="application">pg_archivecleanup</span></span></a> module + is often used in <code class="varname">archive_cleanup_command</code> for + single-standby configurations, for example: +</p><pre class="programlisting">archive_cleanup_command = 'pg_archivecleanup /mnt/server/archivedir %r'</pre><p> + Note however that if multiple standby servers are restoring from the + same archive directory, you will need to ensure that you do not delete + WAL files until they are no longer needed by any of the servers. + <code class="varname">archive_cleanup_command</code> would typically be used in a + warm-standby configuration (see <a class="xref" href="warm-standby.html" title="27.2. Log-Shipping Standby Servers">Section 27.2</a>). + Write <code class="literal">%%</code> to embed an actual <code class="literal">%</code> character in the + command. + </p><p> + If the command returns a nonzero exit status then a warning log + message will be written. An exception is that if the command was + terminated by a signal or an error by the shell (such as command not + found), a fatal error will be raised. + </p><p> + 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-RECOVERY-END-COMMAND"><span class="term"><code class="varname">recovery_end_command</code> (<code class="type">string</code>) + <a id="id-1.6.7.8.7.7.3.1.3" class="indexterm"></a> + </span> <a href="#GUC-RECOVERY-END-COMMAND" class="id_link">#</a></dt><dd><p> + This parameter specifies a shell command that will be executed once only + at the end of recovery. This parameter is optional. The purpose of the + <code class="varname">recovery_end_command</code> is to provide a mechanism for cleanup + following replication or recovery. + Any <code class="literal">%r</code> is replaced by the name of the file containing the + last valid restart point, like in <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-CLEANUP-COMMAND">archive_cleanup_command</a>. + </p><p> + If the command returns a nonzero exit status then a warning log + message will be written and the database will proceed to start up + anyway. An exception is that if the command was terminated by a + signal or an error by the shell (such as command not found), the + database will not proceed with startup. + </p><p> + 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-RECOVERY-TARGET"><div class="titlepage"><div><div><h3 class="title">20.5.6. Recovery Target <a href="#RUNTIME-CONFIG-WAL-RECOVERY-TARGET" class="id_link">#</a></h3></div></div></div><p> + By default, recovery will recover to the end of the WAL log. The + following parameters can be used to specify an earlier stopping point. + At most one of <code class="varname">recovery_target</code>, + <code class="varname">recovery_target_lsn</code>, <code class="varname">recovery_target_name</code>, + <code class="varname">recovery_target_time</code>, or <code class="varname">recovery_target_xid</code> + can be used; if more than one of these is specified in the configuration + file, an error will be raised. + These parameters can only be set at server start. + </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-RECOVERY-TARGET"><span class="term"><code class="varname">recovery_target</code><code class="literal"> = 'immediate'</code> + <a id="id-1.6.7.8.8.3.1.1.3" class="indexterm"></a> + </span> <a href="#GUC-RECOVERY-TARGET" class="id_link">#</a></dt><dd><p> + This parameter specifies that recovery should end as soon as a + consistent state is reached, i.e., as early as possible. When restoring + from an online backup, this means the point where taking the backup + ended. + </p><p> + Technically, this is a string parameter, but <code class="literal">'immediate'</code> + is currently the only allowed value. + </p></dd><dt id="GUC-RECOVERY-TARGET-NAME"><span class="term"><code class="varname">recovery_target_name</code> (<code class="type">string</code>) + <a id="id-1.6.7.8.8.3.2.1.3" class="indexterm"></a> + </span> <a href="#GUC-RECOVERY-TARGET-NAME" class="id_link">#</a></dt><dd><p> + This parameter specifies the named restore point (created with + <code class="function">pg_create_restore_point()</code>) to which recovery will proceed. + </p></dd><dt id="GUC-RECOVERY-TARGET-TIME"><span class="term"><code class="varname">recovery_target_time</code> (<code class="type">timestamp</code>) + <a id="id-1.6.7.8.8.3.3.1.3" class="indexterm"></a> + </span> <a href="#GUC-RECOVERY-TARGET-TIME" class="id_link">#</a></dt><dd><p> + This parameter specifies the time stamp up to which recovery + will proceed. + The precise stopping point is also influenced by + <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-INCLUSIVE">recovery_target_inclusive</a>. + </p><p> + The value of this parameter is a time stamp in the same format + accepted by the <code class="type">timestamp with time zone</code> data type, + except that you cannot use a time zone abbreviation (unless the + <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE-ABBREVIATIONS">timezone_abbreviations</a> variable has been set + earlier in the configuration file). Preferred style is to use a + numeric offset from UTC, or you can write a full time zone name, + e.g., <code class="literal">Europe/Helsinki</code> not <code class="literal">EEST</code>. + </p></dd><dt id="GUC-RECOVERY-TARGET-XID"><span class="term"><code class="varname">recovery_target_xid</code> (<code class="type">string</code>) + <a id="id-1.6.7.8.8.3.4.1.3" class="indexterm"></a> + </span> <a href="#GUC-RECOVERY-TARGET-XID" class="id_link">#</a></dt><dd><p> + This parameter specifies the transaction ID up to which recovery + will proceed. Keep in mind + that while transaction IDs are assigned sequentially at transaction + start, transactions can complete in a different numeric order. + The transactions that will be recovered are those that committed + before (and optionally including) the specified one. + The precise stopping point is also influenced by + <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-INCLUSIVE">recovery_target_inclusive</a>. + </p></dd><dt id="GUC-RECOVERY-TARGET-LSN"><span class="term"><code class="varname">recovery_target_lsn</code> (<code class="type">pg_lsn</code>) + <a id="id-1.6.7.8.8.3.5.1.3" class="indexterm"></a> + </span> <a href="#GUC-RECOVERY-TARGET-LSN" class="id_link">#</a></dt><dd><p> + This parameter specifies the LSN of the write-ahead log location up + to which recovery will proceed. The precise stopping point is also + influenced by <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-INCLUSIVE">recovery_target_inclusive</a>. This + parameter is parsed using the system data type + <a class="link" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type"><code class="type">pg_lsn</code></a>. + </p></dd></dl></div><p> + The following options further specify the recovery target, and affect + what happens when the target is reached: + </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-RECOVERY-TARGET-INCLUSIVE"><span class="term"><code class="varname">recovery_target_inclusive</code> (<code class="type">boolean</code>) + <a id="id-1.6.7.8.8.5.1.1.3" class="indexterm"></a> + </span> <a href="#GUC-RECOVERY-TARGET-INCLUSIVE" class="id_link">#</a></dt><dd><p> + Specifies whether to stop just after the specified recovery target + (<code class="literal">on</code>), or just before the recovery target + (<code class="literal">off</code>). + Applies when <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-LSN">recovery_target_lsn</a>, + <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-TIME">recovery_target_time</a>, or + <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-XID">recovery_target_xid</a> is specified. + This setting controls whether transactions + having exactly the target WAL location (LSN), commit time, or transaction ID, respectively, will + be included in the recovery. Default is <code class="literal">on</code>. + </p></dd><dt id="GUC-RECOVERY-TARGET-TIMELINE"><span class="term"><code class="varname">recovery_target_timeline</code> (<code class="type">string</code>) + <a id="id-1.6.7.8.8.5.2.1.3" class="indexterm"></a> + </span> <a href="#GUC-RECOVERY-TARGET-TIMELINE" class="id_link">#</a></dt><dd><p> + Specifies recovering into a particular timeline. The value can be a + numeric timeline ID or a special value. The value + <code class="literal">current</code> recovers along the same timeline that was + current when the base backup was taken. The + value <code class="literal">latest</code> recovers + to the latest timeline found in the archive, which is useful in + a standby server. <code class="literal">latest</code> is the default. + </p><p> + To specify a timeline ID in hexadecimal (for example, if extracted + from a WAL file name or history file), prefix it with a + <code class="literal">0x</code>. For instance, if the WAL file name is + <code class="filename">00000011000000A10000004F</code>, then the timeline ID is + <code class="literal">0x11</code> (or 17 decimal). + </p><p> + You usually only need to set this parameter + in complex re-recovery situations, where you need to return to + a state that itself was reached after a point-in-time recovery. + See <a class="xref" href="continuous-archiving.html#BACKUP-TIMELINES" title="26.3.5. Timelines">Section 26.3.5</a> for discussion. + </p></dd><dt id="GUC-RECOVERY-TARGET-ACTION"><span class="term"><code class="varname">recovery_target_action</code> (<code class="type">enum</code>) + <a id="id-1.6.7.8.8.5.3.1.3" class="indexterm"></a> + </span> <a href="#GUC-RECOVERY-TARGET-ACTION" class="id_link">#</a></dt><dd><p> + Specifies what action the server should take once the recovery target is + reached. The default is <code class="literal">pause</code>, which means recovery will + be paused. <code class="literal">promote</code> means the recovery process will finish + and the server will start to accept connections. + Finally <code class="literal">shutdown</code> will stop the server after reaching the + recovery target. + </p><p> + The intended use of the <code class="literal">pause</code> setting is to allow queries + to be executed against the database to check if this recovery target + is the most desirable point for recovery. + The paused state can be resumed by + using <code class="function">pg_wal_replay_resume()</code> (see + <a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE" title="Table 9.93. Recovery Control Functions">Table 9.93</a>), which then + causes recovery to end. If this recovery target is not the + desired stopping point, then shut down the server, change the + recovery target settings to a later target and restart to + continue recovery. + </p><p> + The <code class="literal">shutdown</code> setting is useful to have the instance ready + at the exact replay point desired. The instance will still be able to + replay more WAL records (and in fact will have to replay WAL records + since the last checkpoint next time it is started). + </p><p> + Note that because <code class="filename">recovery.signal</code> will not be + removed when <code class="varname">recovery_target_action</code> is set to <code class="literal">shutdown</code>, + any subsequent start will end with immediate shutdown unless the + configuration is changed or the <code class="filename">recovery.signal</code> + file is removed manually. + </p><p> + This setting has no effect if no recovery target is set. + If <a class="xref" href="runtime-config-replication.html#GUC-HOT-STANDBY">hot_standby</a> is not enabled, a setting of + <code class="literal">pause</code> will act the same as <code class="literal">shutdown</code>. + If the recovery target is reached while a promotion is ongoing, + a setting of <code class="literal">pause</code> will act the same as + <code class="literal">promote</code>. + </p><p> + In any case, if a recovery target is configured but the archive + recovery ends before the target is reached, the server will shut down + with a fatal error. + </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" title="20.4. Resource Consumption">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-replication.html" title="20.6. Replication">Next</a></td></tr><tr><td width="40%" align="left" valign="top">20.4. Resource Consumption </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.6. Replication</td></tr></table></div></body></html>
\ No newline at end of file |