codekingpro/portable-devtools
115k
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>