Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
runtime-config-wal.html839 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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>3    For additional information on tuning these settings,4    see <a class="xref" href="wal-configuration.html" title="30.5. WAL Configuration">Section 30.5</a>.5   </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>)6      <a id="id-1.6.7.8.3.2.1.1.3" class="indexterm"></a>7      </span> <a href="#GUC-WAL-LEVEL" class="id_link">#</a></dt><dd><p>8        <code class="varname">wal_level</code> determines how much information is written to9        the WAL. The default value is <code class="literal">replica</code>, which writes enough10        data to support WAL archiving and replication, including running11        read-only queries on a standby server. <code class="literal">minimal</code> removes all12        logging except the information required to recover from a crash or13        immediate shutdown.  Finally,14        <code class="literal">logical</code> adds information necessary to support logical15        decoding.  Each level includes the information logged at all lower16        levels.  This parameter can only be set at server start.17       </p><p>18        The <code class="literal">minimal</code> level generates the least WAL19        volume.  It logs no row information for permanent relations20        in transactions that create or21        rewrite them.  This can make operations much faster (see22        <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 this23        optimization include:24        </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>25         (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>26        However, minimal WAL does not contain sufficient information for27        point-in-time recovery, so <code class="literal">replica</code> or28        higher must be used to enable continuous archiving29        (<a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-MODE">archive_mode</a>) and streaming binary replication.30        In fact, the server will not even start in this mode if31        <code class="varname">max_wal_senders</code> is non-zero.32        Note that changing <code class="varname">wal_level</code> to33        <code class="literal">minimal</code> makes previous base backups unusable34        for point-in-time recovery and standby servers.35       </p><p>36        In <code class="literal">logical</code> level, the same information is logged as37        with <code class="literal">replica</code>, plus information needed to38        extract logical change sets from the WAL. Using a level of39        <code class="literal">logical</code> will increase the WAL volume, particularly if many40        tables are configured for <code class="literal">REPLICA IDENTITY FULL</code> and41        many <code class="command">UPDATE</code> and <code class="command">DELETE</code> statements are42        executed.43       </p><p>44        In releases prior to 9.6, this parameter also allowed the45        values <code class="literal">archive</code> and <code class="literal">hot_standby</code>.46        These are still accepted but mapped to <code class="literal">replica</code>.47       </p></dd><dt id="GUC-FSYNC"><span class="term"><code class="varname">fsync</code> (<code class="type">boolean</code>)48      <a id="id-1.6.7.8.3.2.2.1.3" class="indexterm"></a>49      </span> <a href="#GUC-FSYNC" class="id_link">#</a></dt><dd><p>50        If this parameter is on, the <span class="productname">PostgreSQL</span> server51        will try to make sure that updates are physically written to52        disk, by issuing <code class="function">fsync()</code> system calls or various53        equivalent methods (see <a class="xref" href="runtime-config-wal.html#GUC-WAL-SYNC-METHOD">wal_sync_method</a>).54        This ensures that the database cluster can recover to a55        consistent state after an operating system or hardware crash.56       </p><p>57        While turning off <code class="varname">fsync</code> is often a performance58        benefit, this can result in unrecoverable data corruption in59        the event of a power failure or system crash.  Thus it60        is only advisable to turn off <code class="varname">fsync</code> if61        you can easily recreate your entire database from external62        data.63       </p><p>64        Examples of safe circumstances for turning off65        <code class="varname">fsync</code> include the initial loading of a new66        database cluster from a backup file, using a database cluster67        for processing a batch of data after which the database68        will be thrown away and recreated,69        or for a read-only database clone which70        gets recreated frequently and is not used for failover.  High71        quality hardware alone is not a sufficient justification for72        turning off <code class="varname">fsync</code>.73       </p><p>74        For reliable recovery when changing <code class="varname">fsync</code>75        off to on, it is necessary to force all modified buffers in the76        kernel to durable storage.  This can be done while the cluster77        is shutdown or while <code class="varname">fsync</code> is on by running <code class="command">initdb78        --sync-only</code>, running <code class="command">sync</code>, unmounting the79        file system, or rebooting the server.80       </p><p>81        In many situations, turning off <a class="xref" href="runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT">synchronous_commit</a>82        for noncritical transactions can provide much of the potential83        performance benefit of turning off <code class="varname">fsync</code>, without84        the attendant risks of data corruption.85       </p><p>86        <code class="varname">fsync</code> can only be set in the <code class="filename">postgresql.conf</code>87        file or on the server command line.88        If you turn this parameter off, also consider turning off89        <a class="xref" href="runtime-config-wal.html#GUC-FULL-PAGE-WRITES">full_page_writes</a>.90       </p></dd><dt id="GUC-SYNCHRONOUS-COMMIT"><span class="term"><code class="varname">synchronous_commit</code> (<code class="type">enum</code>)91      <a id="id-1.6.7.8.3.2.3.1.3" class="indexterm"></a>92      </span> <a href="#GUC-SYNCHRONOUS-COMMIT" class="id_link">#</a></dt><dd><p>93        Specifies how much WAL processing must complete before94        the database server returns a <span class="quote">“<span class="quote">success</span>”</span>95        indication to the client.  Valid values are96        <code class="literal">remote_apply</code>, <code class="literal">on</code>97        (the default), <code class="literal">remote_write</code>,98        <code class="literal">local</code>, and <code class="literal">off</code>.99       </p><p>100        If <code class="varname">synchronous_standby_names</code> is empty,101        the only meaningful settings are <code class="literal">on</code> and102        <code class="literal">off</code>;  <code class="literal">remote_apply</code>,103        <code class="literal">remote_write</code> and <code class="literal">local</code>104        all provide the same local synchronization level105        as <code class="literal">on</code>.  The local behavior of all106        non-<code class="literal">off</code> modes is to wait for local flush of WAL107        to disk.  In <code class="literal">off</code> mode, there is no waiting,108        so there can be a delay between when success is reported to the109        client and when the transaction is later guaranteed to be safe110        against a server crash.  (The maximum111        delay is three times <a class="xref" href="runtime-config-wal.html#GUC-WAL-WRITER-DELAY">wal_writer_delay</a>.)  Unlike112        <a class="xref" href="runtime-config-wal.html#GUC-FSYNC">fsync</a>, setting this parameter to <code class="literal">off</code>113        does not create any risk of database inconsistency: an operating114        system or database crash might115        result in some recent allegedly-committed transactions being lost, but116        the database state will be just the same as if those transactions had117        been aborted cleanly.  So, turning <code class="varname">synchronous_commit</code> off118        can be a useful alternative when performance is more important than119        exact certainty about the durability of a transaction.  For more120        discussion see <a class="xref" href="wal-async-commit.html" title="30.4. Asynchronous Commit">Section 30.4</a>.121       </p><p>122        If <a class="xref" href="runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES">synchronous_standby_names</a> is non-empty,123        <code class="varname">synchronous_commit</code> also controls whether124        transaction commits will wait for their WAL records to be125        processed on the standby server(s).126       </p><p>127        When set to <code class="literal">remote_apply</code>, commits will wait128        until replies from the current synchronous standby(s) indicate they129        have received the commit record of the transaction and applied130        it, so that it has become visible to queries on the standby(s),131        and also written to durable storage on the standbys.  This will132        cause much larger commit delays than previous settings since133        it waits for WAL replay.  When set to <code class="literal">on</code>,134        commits wait until replies135        from the current synchronous standby(s) indicate they have received136        the commit record of the transaction and flushed it to durable storage.  This137        ensures the transaction will not be lost unless both the primary and138        all synchronous standbys suffer corruption of their database storage.139        When set to <code class="literal">remote_write</code>, commits will wait until replies140        from the current synchronous standby(s) indicate they have141        received the commit record of the transaction and written it to142        their file systems. This setting ensures data preservation if a standby instance of143        <span class="productname">PostgreSQL</span> crashes, but not if the standby144        suffers an operating-system-level crash because the data has not145        necessarily reached durable storage on the standby.146        The setting <code class="literal">local</code> causes commits to wait for147        local flush to disk, but not for replication.  This is usually not148        desirable when synchronous replication is in use, but is provided for149        completeness.150       </p><p>151        This parameter can be changed at any time; the behavior for any152        one transaction is determined by the setting in effect when it153        commits.  It is therefore possible, and useful, to have some154        transactions commit synchronously and others asynchronously.155        For example, to make a single multistatement transaction commit156        asynchronously when the default is the opposite, issue <code class="command">SET157        LOCAL synchronous_commit TO OFF</code> within the transaction.158       </p><p>159        <a class="xref" href="runtime-config-wal.html#SYNCHRONOUS-COMMIT-MATRIX" title="Table 20.1. synchronous_commit Modes">Table 20.1</a> summarizes the160        capabilities of the <code class="varname">synchronous_commit</code> settings.161       </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>)162      <a id="id-1.6.7.8.3.2.4.1.3" class="indexterm"></a>163      </span> <a href="#GUC-WAL-SYNC-METHOD" class="id_link">#</a></dt><dd><p>164        Method used for forcing WAL updates out to disk.165        If <code class="varname">fsync</code> is off then this setting is irrelevant,166        since WAL file updates will not be forced out at all.167        Possible values are:168       </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>169         <code class="literal">open_datasync</code> (write WAL files with <code class="function">open()</code> option <code class="symbol">O_DSYNC</code>)170        </p></li><li class="listitem"><p>171         <code class="literal">fdatasync</code> (call <code class="function">fdatasync()</code> at each commit)172        </p></li><li class="listitem"><p>173         <code class="literal">fsync</code> (call <code class="function">fsync()</code> at each commit)174        </p></li><li class="listitem"><p>175         <code class="literal">fsync_writethrough</code> (call <code class="function">fsync()</code> at each commit, forcing write-through of any disk write cache)176        </p></li><li class="listitem"><p>177         <code class="literal">open_sync</code> (write WAL files with <code class="function">open()</code> option <code class="symbol">O_SYNC</code>)178        </p></li></ul></div><p>179        Not all of these choices are available on all platforms.180        The default is the first method in the above list that is supported181        by the platform, except that <code class="literal">fdatasync</code> is the default on182        Linux and FreeBSD.  The default is not necessarily ideal; it might be183        necessary to change this setting or other aspects of your system184        configuration in order to create a crash-safe configuration or185        achieve optimal performance.186        These aspects are discussed in <a class="xref" href="wal-reliability.html" title="30.1. Reliability">Section 30.1</a>.187        This parameter can only be set in the <code class="filename">postgresql.conf</code>188        file or on the server command line.189       </p></dd><dt id="GUC-FULL-PAGE-WRITES"><span class="term"><code class="varname">full_page_writes</code> (<code class="type">boolean</code>)190      <a id="id-1.6.7.8.3.2.5.1.3" class="indexterm"></a>191      </span> <a href="#GUC-FULL-PAGE-WRITES" class="id_link">#</a></dt><dd><p>192        When this parameter is on, the <span class="productname">PostgreSQL</span> server193        writes the entire content of each disk page to WAL during the194        first modification of that page after a checkpoint.195        This is needed because196        a page write that is in process during an operating system crash might197        be only partially completed, leading to an on-disk page198        that contains a mix of old and new data.  The row-level change data199        normally stored in WAL will not be enough to completely restore200        such a page during post-crash recovery.  Storing the full page image201        guarantees that the page can be correctly restored, but at the price202        of increasing the amount of data that must be written to WAL.203        (Because WAL replay always starts from a checkpoint, it is sufficient204        to do this during the first change of each page after a checkpoint.205        Therefore, one way to reduce the cost of full-page writes is to206        increase the checkpoint interval parameters.)207       </p><p>208        Turning this parameter off speeds normal operation, but209        might lead to either unrecoverable data corruption, or silent210        data corruption, after a system failure. The risks are similar to turning off211        <code class="varname">fsync</code>, though smaller, and it should be turned off212        only based on the same circumstances recommended for that parameter.213       </p><p>214        Turning off this parameter does not affect use of215        WAL archiving for point-in-time recovery (PITR)216        (see <a class="xref" href="continuous-archiving.html" title="26.3. Continuous Archiving and Point-in-Time Recovery (PITR)">Section 26.3</a>).217       </p><p>218        This parameter can only be set in the <code class="filename">postgresql.conf</code>219        file or on the server command line.220        The default is <code class="literal">on</code>.221       </p></dd><dt id="GUC-WAL-LOG-HINTS"><span class="term"><code class="varname">wal_log_hints</code> (<code class="type">boolean</code>)222      <a id="id-1.6.7.8.3.2.6.1.3" class="indexterm"></a>223      </span> <a href="#GUC-WAL-LOG-HINTS" class="id_link">#</a></dt><dd><p>224        When this parameter is <code class="literal">on</code>, the <span class="productname">PostgreSQL</span>225        server writes the entire content of each disk page to WAL during the226        first modification of that page after a checkpoint, even for227        non-critical modifications of so-called hint bits.228       </p><p>229        If data checksums are enabled, hint bit updates are always WAL-logged230        and this setting is ignored. You can use this setting to test how much231        extra WAL-logging would occur if your database had data checksums232        enabled.233       </p><p>234        This parameter can only be set at server start. The default value is <code class="literal">off</code>.235       </p></dd><dt id="GUC-WAL-COMPRESSION"><span class="term"><code class="varname">wal_compression</code> (<code class="type">enum</code>)236      <a id="id-1.6.7.8.3.2.7.1.3" class="indexterm"></a>237      </span> <a href="#GUC-WAL-COMPRESSION" class="id_link">#</a></dt><dd><p>238        This parameter enables compression of WAL using the specified239        compression method.240        When enabled, the <span class="productname">PostgreSQL</span>241        server compresses full page images written to WAL when242        <a class="xref" href="runtime-config-wal.html#GUC-FULL-PAGE-WRITES">full_page_writes</a> is on or during a base backup.243        A compressed page image will be decompressed during WAL replay.244        The supported methods are <code class="literal">pglz</code>,245        <code class="literal">lz4</code> (if <span class="productname">PostgreSQL</span>246        was compiled with <code class="option">--with-lz4</code>) and247        <code class="literal">zstd</code> (if <span class="productname">PostgreSQL</span>248        was compiled with <code class="option">--with-zstd</code>).249        The default value is <code class="literal">off</code>.250        Only superusers and users with the appropriate <code class="literal">SET</code>251        privilege can change this setting.252       </p><p>253        Enabling compression can reduce the WAL volume without254        increasing the risk of unrecoverable data corruption,255        but at the cost of some extra CPU spent on the compression during256        WAL logging and on the decompression during WAL replay.257       </p></dd><dt id="GUC-WAL-INIT-ZERO"><span class="term"><code class="varname">wal_init_zero</code> (<code class="type">boolean</code>)258      <a id="id-1.6.7.8.3.2.8.1.3" class="indexterm"></a>259      </span> <a href="#GUC-WAL-INIT-ZERO" class="id_link">#</a></dt><dd><p>260        If set to <code class="literal">on</code> (the default), this option causes new261        WAL files to be filled with zeroes.  On some file systems, this ensures262        that space is allocated before we need to write WAL records.  However,263        <em class="firstterm">Copy-On-Write</em> (COW) file systems may not benefit264        from this technique, so the option is given to skip the unnecessary265        work.  If set to <code class="literal">off</code>, only the final byte is written266        when the file is created so that it has the expected size.267       </p></dd><dt id="GUC-WAL-RECYCLE"><span class="term"><code class="varname">wal_recycle</code> (<code class="type">boolean</code>)268      <a id="id-1.6.7.8.3.2.9.1.3" class="indexterm"></a>269      </span> <a href="#GUC-WAL-RECYCLE" class="id_link">#</a></dt><dd><p>270        If set to <code class="literal">on</code> (the default), this option causes WAL271        files to be recycled by renaming them, avoiding the need to create new272        ones.  On COW file systems, it may be faster to create new ones, so the273        option is given to disable this behavior.274       </p></dd><dt id="GUC-WAL-BUFFERS"><span class="term"><code class="varname">wal_buffers</code> (<code class="type">integer</code>)275      <a id="id-1.6.7.8.3.2.10.1.3" class="indexterm"></a>276      </span> <a href="#GUC-WAL-BUFFERS" class="id_link">#</a></dt><dd><p>277        The amount of shared memory used for WAL data that has not yet been278        written to disk.  The default setting of -1 selects a size equal to279        1/32nd (about 3%) of <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but not less280        than <code class="literal">64kB</code> nor more than the size of one WAL281        segment, typically <code class="literal">16MB</code>.  This value can be set282        manually if the automatic choice is too large or too small,283        but any positive value less than <code class="literal">32kB</code> will be284        treated as <code class="literal">32kB</code>.285        If this value is specified without units, it is taken as WAL blocks,286        that is <code class="symbol">XLOG_BLCKSZ</code> bytes, typically 8kB.287        This parameter can only be set at server start.288       </p><p>289        The contents of the WAL buffers are written out to disk at every290        transaction commit, so extremely large values are unlikely to291        provide a significant benefit.  However, setting this value to at292        least a few megabytes can improve write performance on a busy293        server where many clients are committing at once.  The auto-tuning294        selected by the default setting of -1 should give reasonable295        results in most cases.296       </p></dd><dt id="GUC-WAL-WRITER-DELAY"><span class="term"><code class="varname">wal_writer_delay</code> (<code class="type">integer</code>)297      <a id="id-1.6.7.8.3.2.11.1.3" class="indexterm"></a>298      </span> <a href="#GUC-WAL-WRITER-DELAY" class="id_link">#</a></dt><dd><p>299        Specifies how often the WAL writer flushes WAL, in time terms.300        After flushing WAL the writer sleeps for the length of time given301        by <code class="varname">wal_writer_delay</code>, unless woken up sooner302        by an asynchronously committing transaction. If the last flush303        happened less than <code class="varname">wal_writer_delay</code> ago and less304        than <code class="varname">wal_writer_flush_after</code> worth of WAL has been305        produced since, then WAL is only written to the operating system, not306        flushed to disk.307        If this value is specified without units, it is taken as milliseconds.308        The default value is 200 milliseconds (<code class="literal">200ms</code>).  Note that309        on many systems, the effective resolution of sleep delays is 10310        milliseconds; setting <code class="varname">wal_writer_delay</code> to a value that is311        not a multiple of 10 might have the same results as setting it to the312        next higher multiple of 10. This parameter can only be set in the313        <code class="filename">postgresql.conf</code> file or on the server command line.314       </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>)315      <a id="id-1.6.7.8.3.2.12.1.3" class="indexterm"></a>316      </span> <a href="#GUC-WAL-WRITER-FLUSH-AFTER" class="id_link">#</a></dt><dd><p>317        Specifies how often the WAL writer flushes WAL, in volume terms.318        If the last flush happened less319        than <code class="varname">wal_writer_delay</code> ago and less320        than <code class="varname">wal_writer_flush_after</code> worth of WAL has been321        produced since, then WAL is only written to the operating system, not322        flushed to disk.  If <code class="varname">wal_writer_flush_after</code> is set323        to <code class="literal">0</code> then WAL data is always flushed immediately.324        If this value is specified without units, it is taken as WAL blocks,325        that is <code class="symbol">XLOG_BLCKSZ</code> bytes, typically 8kB.326        The default is <code class="literal">1MB</code>.327        This parameter can only be set in the328        <code class="filename">postgresql.conf</code> file or on the server command line.329       </p></dd><dt id="GUC-WAL-SKIP-THRESHOLD"><span class="term"><code class="varname">wal_skip_threshold</code> (<code class="type">integer</code>)330      <a id="id-1.6.7.8.3.2.13.1.3" class="indexterm"></a>331      </span> <a href="#GUC-WAL-SKIP-THRESHOLD" class="id_link">#</a></dt><dd><p>332        When <code class="varname">wal_level</code> is <code class="literal">minimal</code> and a333        transaction commits after creating or rewriting a permanent relation,334        this setting determines how to persist the new data.  If the data is335        smaller than this setting, write it to the WAL log; otherwise, use an336        fsync of affected files.  Depending on the properties of your storage,337        raising or lowering this value might help if such commits are slowing338        concurrent transactions.  If this value is specified without units, it339        is taken as kilobytes.  The default is two megabytes340        (<code class="literal">2MB</code>).341       </p></dd><dt id="GUC-COMMIT-DELAY"><span class="term"><code class="varname">commit_delay</code> (<code class="type">integer</code>)342      <a id="id-1.6.7.8.3.2.14.1.3" class="indexterm"></a>343      </span> <a href="#GUC-COMMIT-DELAY" class="id_link">#</a></dt><dd><p>344        Setting <code class="varname">commit_delay</code> adds a time delay345        before a WAL flush is initiated.  This can improve346        group commit throughput by allowing a larger number of transactions347        to commit via a single WAL flush, if system load is high enough348        that additional transactions become ready to commit within the349        given interval.  However, it also increases latency by up to the350        <code class="varname">commit_delay</code> for each WAL351        flush.  Because the delay is just wasted if no other transactions352        become ready to commit, a delay is only performed if at least353        <code class="varname">commit_siblings</code> other transactions are active354        when a flush is about to be initiated.  Also, no delays are355        performed if <code class="varname">fsync</code> is disabled.356        If this value is specified without units, it is taken as microseconds.357        The default <code class="varname">commit_delay</code> is zero (no delay).358        Only superusers and users with the appropriate <code class="literal">SET</code>359        privilege can change this setting.360       </p><p>361        In <span class="productname">PostgreSQL</span> releases prior to 9.3,362        <code class="varname">commit_delay</code> behaved differently and was much363        less effective: it affected only commits, rather than all WAL flushes,364        and waited for the entire configured delay even if the WAL flush365        was completed sooner.  Beginning in <span class="productname">PostgreSQL</span> 9.3,366        the first process that becomes ready to flush waits for the configured367        interval, while subsequent processes wait only until the leader368        completes the flush operation.369       </p></dd><dt id="GUC-COMMIT-SIBLINGS"><span class="term"><code class="varname">commit_siblings</code> (<code class="type">integer</code>)370      <a id="id-1.6.7.8.3.2.15.1.3" class="indexterm"></a>371      </span> <a href="#GUC-COMMIT-SIBLINGS" class="id_link">#</a></dt><dd><p>372        Minimum number of concurrent open transactions to require373        before performing the <code class="varname">commit_delay</code> delay. A larger374        value makes it more probable that at least one other375        transaction will become ready to commit during the delay376        interval. The default is five transactions.377       </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>)378      <a id="id-1.6.7.8.4.2.1.1.3" class="indexterm"></a>379      </span> <a href="#GUC-CHECKPOINT-TIMEOUT" class="id_link">#</a></dt><dd><p>380        Maximum time between automatic WAL checkpoints.381        If this value is specified without units, it is taken as seconds.382        The valid range is between 30 seconds and one day.383        The default is five minutes (<code class="literal">5min</code>).384        Increasing this parameter can increase the amount of time needed385        for crash recovery.386        This parameter can only be set in the <code class="filename">postgresql.conf</code>387        file or on the server command line.388       </p></dd><dt id="GUC-CHECKPOINT-COMPLETION-TARGET"><span class="term"><code class="varname">checkpoint_completion_target</code> (<code class="type">floating point</code>)389      <a id="id-1.6.7.8.4.2.2.1.3" class="indexterm"></a>390      </span> <a href="#GUC-CHECKPOINT-COMPLETION-TARGET" class="id_link">#</a></dt><dd><p>391        Specifies the target of checkpoint completion, as a fraction of392        total time between checkpoints. The default is 0.9, which spreads the393        checkpoint across almost all of the available interval, providing fairly394        consistent I/O load while also leaving some time for checkpoint395        completion overhead.  Reducing this parameter is not recommended because396        it causes the checkpoint to complete faster.  This results in a higher397        rate of I/O during the checkpoint followed by a period of less I/O between398        the checkpoint completion and the next scheduled checkpoint.  This399        parameter can only be set in the <code class="filename">postgresql.conf</code> file400        or on the server command line.401       </p></dd><dt id="GUC-CHECKPOINT-FLUSH-AFTER"><span class="term"><code class="varname">checkpoint_flush_after</code> (<code class="type">integer</code>)402      <a id="id-1.6.7.8.4.2.3.1.3" class="indexterm"></a>403      </span> <a href="#GUC-CHECKPOINT-FLUSH-AFTER" class="id_link">#</a></dt><dd><p>404        Whenever more than this amount of data has been405        written while performing a checkpoint, attempt to force the406        OS to issue these writes to the underlying storage.  Doing so will407        limit the amount of dirty data in the kernel's page cache, reducing408        the likelihood of stalls when an <code class="function">fsync</code> is issued at the end of the409        checkpoint, or when the OS writes data back in larger batches in the410        background.  Often that will result in greatly reduced transaction411        latency, but there also are some cases, especially with workloads412        that are bigger than <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but smaller413        than the OS's page cache, where performance might degrade.  This414        setting may have no effect on some platforms.415        If this value is specified without units, it is taken as blocks,416        that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.417        The valid range is418        between <code class="literal">0</code>, which disables forced writeback,419        and <code class="literal">2MB</code>.  The default is <code class="literal">256kB</code> on420        Linux, <code class="literal">0</code> elsewhere.  (If <code class="symbol">BLCKSZ</code> is not421        8kB, the default and maximum values scale proportionally to it.)422        This parameter can only be set in the <code class="filename">postgresql.conf</code>423        file or on the server command line.424       </p></dd><dt id="GUC-CHECKPOINT-WARNING"><span class="term"><code class="varname">checkpoint_warning</code> (<code class="type">integer</code>)425      <a id="id-1.6.7.8.4.2.4.1.3" class="indexterm"></a>426      </span> <a href="#GUC-CHECKPOINT-WARNING" class="id_link">#</a></dt><dd><p>427        Write a message to the server log if checkpoints caused by428        the filling of WAL segment files happen closer together429        than this amount of time (which suggests that430        <code class="varname">max_wal_size</code> ought to be raised).431        If this value is specified without units, it is taken as seconds.432        The default is 30 seconds (<code class="literal">30s</code>).433        Zero disables the warning.434        No warnings will be generated if <code class="varname">checkpoint_timeout</code>435        is less than <code class="varname">checkpoint_warning</code>.436        This parameter can only be set in the <code class="filename">postgresql.conf</code>437        file or on the server command line.438       </p></dd><dt id="GUC-MAX-WAL-SIZE"><span class="term"><code class="varname">max_wal_size</code> (<code class="type">integer</code>)439      <a id="id-1.6.7.8.4.2.5.1.3" class="indexterm"></a>440      </span> <a href="#GUC-MAX-WAL-SIZE" class="id_link">#</a></dt><dd><p>441        Maximum size to let the WAL grow during automatic442        checkpoints. This is a soft limit; WAL size can exceed443        <code class="varname">max_wal_size</code> under special circumstances, such as444        heavy load, a failing <code class="varname">archive_command</code> or <code class="varname">archive_library</code>, or a high445        <code class="varname">wal_keep_size</code> setting.446        If this value is specified without units, it is taken as megabytes.447        The default is 1 GB.448        Increasing this parameter can increase the amount of time needed for449        crash recovery.450        This parameter can only be set in the <code class="filename">postgresql.conf</code>451        file or on the server command line.452       </p></dd><dt id="GUC-MIN-WAL-SIZE"><span class="term"><code class="varname">min_wal_size</code> (<code class="type">integer</code>)453      <a id="id-1.6.7.8.4.2.6.1.3" class="indexterm"></a>454      </span> <a href="#GUC-MIN-WAL-SIZE" class="id_link">#</a></dt><dd><p>455        As long as WAL disk usage stays below this setting, old WAL files are456        always recycled for future use at a checkpoint, rather than removed.457        This can be used to ensure that enough WAL space is reserved to458        handle spikes in WAL usage, for example when running large batch459        jobs.460        If this value is specified without units, it is taken as megabytes.461        The default is 80 MB.462        This parameter can only be set in the <code class="filename">postgresql.conf</code>463        file or on the server command line.464       </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>)465      <a id="id-1.6.7.8.5.2.1.1.3" class="indexterm"></a>466      </span> <a href="#GUC-ARCHIVE-MODE" class="id_link">#</a></dt><dd><p>467        When <code class="varname">archive_mode</code> is enabled, completed WAL segments468        are sent to archive storage by setting469        <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-COMMAND">archive_command</a> or470        <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-LIBRARY">archive_library</a>. In addition to <code class="literal">off</code>,471        to disable, there are two modes: <code class="literal">on</code>, and472        <code class="literal">always</code>. During normal operation, there is no473        difference between the two modes, but when set to <code class="literal">always</code>474        the WAL archiver is enabled also during archive recovery or standby475        mode. In <code class="literal">always</code> mode, all files restored from the archive476        or streamed with streaming replication will be archived (again). See477        <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.478       </p><p>479        <code class="varname">archive_mode</code> is a separate setting from480        <code class="varname">archive_command</code> and481        <code class="varname">archive_library</code> so that482        <code class="varname">archive_command</code> and483        <code class="varname">archive_library</code> can be changed without leaving484        archiving mode.485        This parameter can only be set at server start.486        <code class="varname">archive_mode</code> cannot be enabled when487        <code class="varname">wal_level</code> is set to <code class="literal">minimal</code>.488       </p></dd><dt id="GUC-ARCHIVE-COMMAND"><span class="term"><code class="varname">archive_command</code> (<code class="type">string</code>)489      <a id="id-1.6.7.8.5.2.2.1.3" class="indexterm"></a>490      </span> <a href="#GUC-ARCHIVE-COMMAND" class="id_link">#</a></dt><dd><p>491        The local shell command to execute to archive a completed WAL file492        segment.  Any <code class="literal">%p</code> in the string is493        replaced by the path name of the file to archive, and any494        <code class="literal">%f</code> is replaced by only the file name.495        (The path name is relative to the working directory of the server,496        i.e., the cluster's data directory.)497        Use <code class="literal">%%</code> to embed an actual <code class="literal">%</code> character in the498        command.  It is important for the command to return a zero499        exit status only if it succeeds. For more information see500        <a class="xref" href="continuous-archiving.html#BACKUP-ARCHIVING-WAL" title="26.3.1. Setting Up WAL Archiving">Section 26.3.1</a>.501       </p><p>502        This parameter can only be set in the <code class="filename">postgresql.conf</code>503        file or on the server command line.  It is only used if504        <code class="varname">archive_mode</code> was enabled at server start and505        <code class="varname">archive_library</code> is set to an empty string.  If both506        <code class="varname">archive_command</code> and <code class="varname">archive_library</code>507        are set, an error will be raised.508        If <code class="varname">archive_command</code> is an empty string (the default) while509        <code class="varname">archive_mode</code> is enabled (and <code class="varname">archive_library</code>510        is set to an empty string), WAL archiving is temporarily511        disabled, but the server continues to accumulate WAL segment files in512        the expectation that a command will soon be provided.  Setting513        <code class="varname">archive_command</code> to a command that does nothing but514        return true, e.g., <code class="literal">/bin/true</code> (<code class="literal">REM</code> on515        Windows), effectively disables516        archiving, but also breaks the chain of WAL files needed for517        archive recovery, so it should only be used in unusual circumstances.518       </p></dd><dt id="GUC-ARCHIVE-LIBRARY"><span class="term"><code class="varname">archive_library</code> (<code class="type">string</code>)519      <a id="id-1.6.7.8.5.2.3.1.3" class="indexterm"></a>520      </span> <a href="#GUC-ARCHIVE-LIBRARY" class="id_link">#</a></dt><dd><p>521        The library to use for archiving completed WAL file segments.  If set to522        an empty string (the default), archiving via shell is enabled, and523        <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-COMMAND">archive_command</a> is used.  If both524        <code class="varname">archive_command</code> and <code class="varname">archive_library</code>525        are set, an error will be raised.  Otherwise, the specified526        shared library is used for archiving. The WAL archiver process is527        restarted by the postmaster when this parameter changes. For more528        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> and529        <a class="xref" href="archive-modules.html" title="Chapter 51. Archive Modules">Chapter 51</a>.530       </p><p>531        This parameter can only be set in the532        <code class="filename">postgresql.conf</code> file or on the server command line.533       </p></dd><dt id="GUC-ARCHIVE-TIMEOUT"><span class="term"><code class="varname">archive_timeout</code> (<code class="type">integer</code>)534      <a id="id-1.6.7.8.5.2.4.1.3" class="indexterm"></a>535      </span> <a href="#GUC-ARCHIVE-TIMEOUT" class="id_link">#</a></dt><dd><p>536        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 for537        completed WAL segments. Hence, if your server generates little WAL538        traffic (or has slack periods where it does so), there could be a539        long delay between the completion of a transaction and its safe540        recording in archive storage.  To limit how old unarchived541        data can be, you can set <code class="varname">archive_timeout</code> to force the542        server to switch to a new WAL segment file periodically.  When this543        parameter is greater than zero, the server will switch to a new544        segment file whenever this amount of time has elapsed since the last545        segment file switch, and there has been any database activity,546        including a single checkpoint (checkpoints are skipped if there is547        no database activity).  Note that archived files that are closed548        early due to a forced switch are still the same length as completely549        full files.  Therefore, it is unwise to use a very short550        <code class="varname">archive_timeout</code> — it will bloat your archive551        storage.  <code class="varname">archive_timeout</code> settings of a minute or so are552        usually reasonable.  You should consider using streaming replication,553        instead of archiving, if you want data to be copied off the primary554        server more quickly than that.555        If this value is specified without units, it is taken as seconds.556        This parameter can only be set in the557        <code class="filename">postgresql.conf</code> file or on the server command line.558       </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>559     This section describes the settings that apply to recovery in general,560     affecting crash recovery, streaming replication and archive-based561     replication.562    </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>)563      <a id="id-1.6.7.8.6.4.1.1.3" class="indexterm"></a>564      </span> <a href="#GUC-RECOVERY-PREFETCH" class="id_link">#</a></dt><dd><p>565        Whether to try to prefetch blocks that are referenced in the WAL that566        are not yet in the buffer pool, during recovery.  Valid values are567        <code class="literal">off</code>, <code class="literal">on</code> and568        <code class="literal">try</code> (the default).  The setting569        <code class="literal">try</code> enables570        prefetching only if the operating system provides the571        <code class="function">posix_fadvise</code> function, which is currently used572        to implement prefetching.  Note that some operating systems provide the573        function, but it doesn't do anything.574       </p><p>575        Prefetching blocks that will soon be needed can reduce I/O wait times576        during recovery with some workloads.577        See also the <a class="xref" href="runtime-config-wal.html#GUC-WAL-DECODE-BUFFER-SIZE">wal_decode_buffer_size</a> and578        <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-IO-CONCURRENCY">maintenance_io_concurrency</a> settings, which limit579        prefetching activity.580       </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>)581      <a id="id-1.6.7.8.6.4.2.1.3" class="indexterm"></a>582      </span> <a href="#GUC-WAL-DECODE-BUFFER-SIZE" class="id_link">#</a></dt><dd><p>583        A limit on how far ahead the server can look in the WAL, to find584        blocks to prefetch.  If this value is specified without units, it is585        taken as bytes.586        The default is 512kB.587       </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>588     This section describes the settings that apply only for the duration of589     the recovery.  They must be reset for any subsequent recovery you wish to590     perform.591    </p><p>592     <span class="quote">“<span class="quote">Recovery</span>”</span> covers using the server as a standby or for593     executing a targeted recovery.  Typically, standby mode would be used to594     provide high availability and/or read scalability, whereas a targeted595     recovery is used to recover from data loss.596    </p><p>597     To start the server in standby mode, create a file called598     <code class="filename">standby.signal</code><a id="id-1.6.7.8.7.5.2" class="indexterm"></a>599     in the data directory.  The server will enter recovery and will not stop600     recovery when the end of archived WAL is reached, but will keep trying to601     continue recovery by connecting to the sending server as specified by the602     <code class="varname">primary_conninfo</code> setting and/or by fetching new WAL603     segments using <code class="varname">restore_command</code>.  For this mode, the604     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.605     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> will606     also be applied but are typically not useful in this mode.607    </p><p>608     To start the server in targeted recovery mode, create a file called609     <code class="filename">recovery.signal</code><a id="id-1.6.7.8.7.6.2" class="indexterm"></a>610     in the data directory.  If both <code class="filename">standby.signal</code> and611     <code class="filename">recovery.signal</code> files are created, standby mode612     takes precedence.  Targeted recovery mode ends when the archived WAL is613     fully replayed, or when <code class="varname">recovery_target</code> is reached.614     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.615    </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>)616      <a id="id-1.6.7.8.7.7.1.1.3" class="indexterm"></a>617      </span> <a href="#GUC-RESTORE-COMMAND" class="id_link">#</a></dt><dd><p>618        The local shell command to execute to retrieve an archived segment of619        the WAL file series. This parameter is required for archive recovery,620        but optional for streaming replication.621        Any <code class="literal">%f</code> in the string is622        replaced by the name of the file to retrieve from the archive,623        and any <code class="literal">%p</code> is replaced by the copy destination path name624        on the server.625        (The path name is relative to the current working directory,626        i.e., the cluster's data directory.)627        Any <code class="literal">%r</code> is replaced by the name of the file containing the628        last valid restart point. That is the earliest file that must be kept629        to allow a restore to be restartable, so this information can be used630        to truncate the archive to just the minimum required to support631        restarting from the current restore. <code class="literal">%r</code> is typically only632        used by warm-standby configurations633        (see <a class="xref" href="warm-standby.html" title="27.2. Log-Shipping Standby Servers">Section 27.2</a>).634        Write <code class="literal">%%</code> to embed an actual <code class="literal">%</code> character.635       </p><p>636        It is important for the command to return a zero exit status637        only if it succeeds.  The command <span class="emphasis"><em>will</em></span> be asked for file638        names that are not present in the archive; it must return nonzero639        when so asked.  Examples:640</p><pre class="programlisting">641restore_command = 'cp /mnt/server/archivedir/%f "%p"'642restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"'  # Windows643</pre><p>644        An exception is that if the command was terminated by a signal (other645        than <span class="systemitem">SIGTERM</span>, which is used as part of a646        database server shutdown) or an error by the shell (such as command647        not found), then recovery will abort and the server will not start up.648       </p><p>649        This parameter can only be set in the <code class="filename">postgresql.conf</code>650        file or on the server command line.651       </p></dd><dt id="GUC-ARCHIVE-CLEANUP-COMMAND"><span class="term"><code class="varname">archive_cleanup_command</code> (<code class="type">string</code>)652      <a id="id-1.6.7.8.7.7.2.1.3" class="indexterm"></a>653      </span> <a href="#GUC-ARCHIVE-CLEANUP-COMMAND" class="id_link">#</a></dt><dd><p>654        This optional parameter specifies a shell command that will be executed655        at every restartpoint.  The purpose of656        <code class="varname">archive_cleanup_command</code> is to provide a mechanism for657        cleaning up old archived WAL files that are no longer needed by the658        standby server.659        Any <code class="literal">%r</code> is replaced by the name of the file containing the660        last valid restart point.661        That is the earliest file that must be <span class="emphasis"><em>kept</em></span> to allow a662        restore to be restartable, and so all files earlier than <code class="literal">%r</code>663        may be safely removed.664        This information can be used to truncate the archive to just the665        minimum required to support restart from the current restore.666        The <a class="xref" href="pgarchivecleanup.html" title="pg_archivecleanup"><span class="refentrytitle"><span class="application">pg_archivecleanup</span></span></a> module667        is often used in <code class="varname">archive_cleanup_command</code> for668        single-standby configurations, for example:669</p><pre class="programlisting">archive_cleanup_command = 'pg_archivecleanup /mnt/server/archivedir %r'</pre><p>670        Note however that if multiple standby servers are restoring from the671        same archive directory, you will need to ensure that you do not delete672        WAL files until they are no longer needed by any of the servers.673        <code class="varname">archive_cleanup_command</code> would typically be used in a674        warm-standby configuration (see <a class="xref" href="warm-standby.html" title="27.2. Log-Shipping Standby Servers">Section 27.2</a>).675        Write <code class="literal">%%</code> to embed an actual <code class="literal">%</code> character in the676        command.677       </p><p>678        If the command returns a nonzero exit status then a warning log679        message will be written.  An exception is that if the command was680        terminated by a signal or an error by the shell (such as command not681        found), a fatal error will be raised.682       </p><p>683        This parameter can only be set in the <code class="filename">postgresql.conf</code>684        file or on the server command line.685       </p></dd><dt id="GUC-RECOVERY-END-COMMAND"><span class="term"><code class="varname">recovery_end_command</code> (<code class="type">string</code>)686      <a id="id-1.6.7.8.7.7.3.1.3" class="indexterm"></a>687      </span> <a href="#GUC-RECOVERY-END-COMMAND" class="id_link">#</a></dt><dd><p>688        This parameter specifies a shell command that will be executed once only689        at the end of recovery. This parameter is optional. The purpose of the690        <code class="varname">recovery_end_command</code> is to provide a mechanism for cleanup691        following replication or recovery.692        Any <code class="literal">%r</code> is replaced by the name of the file containing the693        last valid restart point, like in <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-CLEANUP-COMMAND">archive_cleanup_command</a>.694       </p><p>695        If the command returns a nonzero exit status then a warning log696        message will be written and the database will proceed to start up697        anyway.  An exception is that if the command was terminated by a698        signal or an error by the shell (such as command not found), the699        database will not proceed with startup.700       </p><p>701        This parameter can only be set in the <code class="filename">postgresql.conf</code>702        file or on the server command line.703       </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>704      By default, recovery will recover to the end of the WAL log. The705      following parameters can be used to specify an earlier stopping point.706      At most one of <code class="varname">recovery_target</code>,707      <code class="varname">recovery_target_lsn</code>, <code class="varname">recovery_target_name</code>,708      <code class="varname">recovery_target_time</code>, or <code class="varname">recovery_target_xid</code>709      can be used; if more than one of these is specified in the configuration710      file, an error will be raised.711      These parameters can only be set at server start.712     </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>713      <a id="id-1.6.7.8.8.3.1.1.3" class="indexterm"></a>714      </span> <a href="#GUC-RECOVERY-TARGET" class="id_link">#</a></dt><dd><p>715        This parameter specifies that recovery should end as soon as a716        consistent state is reached, i.e., as early as possible. When restoring717        from an online backup, this means the point where taking the backup718        ended.719       </p><p>720        Technically, this is a string parameter, but <code class="literal">'immediate'</code>721        is currently the only allowed value.722       </p></dd><dt id="GUC-RECOVERY-TARGET-NAME"><span class="term"><code class="varname">recovery_target_name</code> (<code class="type">string</code>)723      <a id="id-1.6.7.8.8.3.2.1.3" class="indexterm"></a>724      </span> <a href="#GUC-RECOVERY-TARGET-NAME" class="id_link">#</a></dt><dd><p>725        This parameter specifies the named restore point (created with726        <code class="function">pg_create_restore_point()</code>) to which recovery will proceed.727       </p></dd><dt id="GUC-RECOVERY-TARGET-TIME"><span class="term"><code class="varname">recovery_target_time</code> (<code class="type">timestamp</code>)728      <a id="id-1.6.7.8.8.3.3.1.3" class="indexterm"></a>729      </span> <a href="#GUC-RECOVERY-TARGET-TIME" class="id_link">#</a></dt><dd><p>730        This parameter specifies the time stamp up to which recovery731        will proceed.732        The precise stopping point is also influenced by733        <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-INCLUSIVE">recovery_target_inclusive</a>.734       </p><p>735        The value of this parameter is a time stamp in the same format736        accepted by the <code class="type">timestamp with time zone</code> data type,737        except that you cannot use a time zone abbreviation (unless the738        <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE-ABBREVIATIONS">timezone_abbreviations</a> variable has been set739        earlier in the configuration file).  Preferred style is to use a740        numeric offset from UTC, or you can write a full time zone name,741        e.g., <code class="literal">Europe/Helsinki</code> not <code class="literal">EEST</code>.742       </p></dd><dt id="GUC-RECOVERY-TARGET-XID"><span class="term"><code class="varname">recovery_target_xid</code> (<code class="type">string</code>)743      <a id="id-1.6.7.8.8.3.4.1.3" class="indexterm"></a>744      </span> <a href="#GUC-RECOVERY-TARGET-XID" class="id_link">#</a></dt><dd><p>745        This parameter specifies the transaction ID up to which recovery746        will proceed. Keep in mind747        that while transaction IDs are assigned sequentially at transaction748        start, transactions can complete in a different numeric order.749        The transactions that will be recovered are those that committed750        before (and optionally including) the specified one.751        The precise stopping point is also influenced by752        <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-INCLUSIVE">recovery_target_inclusive</a>.753       </p></dd><dt id="GUC-RECOVERY-TARGET-LSN"><span class="term"><code class="varname">recovery_target_lsn</code> (<code class="type">pg_lsn</code>)754      <a id="id-1.6.7.8.8.3.5.1.3" class="indexterm"></a>755      </span> <a href="#GUC-RECOVERY-TARGET-LSN" class="id_link">#</a></dt><dd><p>756        This parameter specifies the LSN of the write-ahead log location up757        to which recovery will proceed. The precise stopping point is also758        influenced by <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-INCLUSIVE">recovery_target_inclusive</a>. This759        parameter is parsed using the system data type760        <a class="link" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type"><code class="type">pg_lsn</code></a>.761       </p></dd></dl></div><p>762       The following options further specify the recovery target, and affect763       what happens when the target is reached:764     </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>)765      <a id="id-1.6.7.8.8.5.1.1.3" class="indexterm"></a>766      </span> <a href="#GUC-RECOVERY-TARGET-INCLUSIVE" class="id_link">#</a></dt><dd><p>767        Specifies whether to stop just after the specified recovery target768        (<code class="literal">on</code>), or just before the recovery target769        (<code class="literal">off</code>).770        Applies when <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-LSN">recovery_target_lsn</a>,771        <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-TIME">recovery_target_time</a>, or772        <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-XID">recovery_target_xid</a> is specified.773        This setting controls whether transactions774        having exactly the target WAL location (LSN), commit time, or transaction ID, respectively, will775        be included in the recovery.  Default is <code class="literal">on</code>.776       </p></dd><dt id="GUC-RECOVERY-TARGET-TIMELINE"><span class="term"><code class="varname">recovery_target_timeline</code> (<code class="type">string</code>)777      <a id="id-1.6.7.8.8.5.2.1.3" class="indexterm"></a>778      </span> <a href="#GUC-RECOVERY-TARGET-TIMELINE" class="id_link">#</a></dt><dd><p>779        Specifies recovering into a particular timeline.  The value can be a780        numeric timeline ID or a special value.  The value781        <code class="literal">current</code> recovers along the same timeline that was782        current when the base backup was taken.  The783        value <code class="literal">latest</code> recovers784        to the latest timeline found in the archive, which is useful in785        a standby server.  <code class="literal">latest</code> is the default.786       </p><p>787        To specify a timeline ID in hexadecimal (for example, if extracted788        from a WAL file name or history file), prefix it with a789        <code class="literal">0x</code>.  For instance, if the WAL file name is790        <code class="filename">00000011000000A10000004F</code>, then the timeline ID is791        <code class="literal">0x11</code> (or 17 decimal).792       </p><p>793        You usually only need to set this parameter794        in complex re-recovery situations, where you need to return to795        a state that itself was reached after a point-in-time recovery.796        See <a class="xref" href="continuous-archiving.html#BACKUP-TIMELINES" title="26.3.5. Timelines">Section 26.3.5</a> for discussion.797       </p></dd><dt id="GUC-RECOVERY-TARGET-ACTION"><span class="term"><code class="varname">recovery_target_action</code> (<code class="type">enum</code>)798      <a id="id-1.6.7.8.8.5.3.1.3" class="indexterm"></a>799      </span> <a href="#GUC-RECOVERY-TARGET-ACTION" class="id_link">#</a></dt><dd><p>800        Specifies what action the server should take once the recovery target is801        reached. The default is <code class="literal">pause</code>, which means recovery will802        be paused. <code class="literal">promote</code> means the recovery process will finish803        and the server will start to accept connections.804        Finally <code class="literal">shutdown</code> will stop the server after reaching the805        recovery target.806       </p><p>807        The intended use of the <code class="literal">pause</code> setting is to allow queries808        to be executed against the database to check if this recovery target809        is the most desirable point for recovery.810        The paused state can be resumed by811        using <code class="function">pg_wal_replay_resume()</code> (see812        <a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE" title="Table 9.93. Recovery Control Functions">Table 9.93</a>), which then813        causes recovery to end. If this recovery target is not the814        desired stopping point, then shut down the server, change the815        recovery target settings to a later target and restart to816        continue recovery.817       </p><p>818        The <code class="literal">shutdown</code> setting is useful to have the instance ready819        at the exact replay point desired.  The instance will still be able to820        replay more WAL records (and in fact will have to replay WAL records821        since the last checkpoint next time it is started).822       </p><p>823        Note that because <code class="filename">recovery.signal</code> will not be824        removed when <code class="varname">recovery_target_action</code> is set to <code class="literal">shutdown</code>,825        any subsequent start will end with immediate shutdown unless the826        configuration is changed or the <code class="filename">recovery.signal</code>827        file is removed manually.828       </p><p>829        This setting has no effect if no recovery target is set.830        If <a class="xref" href="runtime-config-replication.html#GUC-HOT-STANDBY">hot_standby</a> is not enabled, a setting of831        <code class="literal">pause</code> will act the same as <code class="literal">shutdown</code>.832        If the recovery target is reached while a promotion is ongoing,833        a setting of <code class="literal">pause</code> will act the same as834        <code class="literal">promote</code>.835       </p><p>836        In any case, if a recovery target is configured but the archive837        recovery ends before the target is reached, the server will shut down838        with a fatal error.839       </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.6. Replication</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai