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>19.5. Write Ahead Log</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="runtime-config-resource.html" title="19.4. Resource Consumption" /><link rel="next" href="runtime-config-replication.html" title="19.6. Replication" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">19.5. Write Ahead Log</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-resource.html" title="19.4. Resource Consumption">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 19. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-replication.html" title="19.6. Replication">Next</a></td></tr></table><hr /></div><div class="sect1" id="RUNTIME-CONFIG-WAL"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.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">19.5.1. Settings</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS">19.5.2. Checkpoints</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING">19.5.3. Archiving</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY">19.5.4. Recovery</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY">19.5.5. Archive Recovery</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET">19.5.6. Recovery Target</a></span></dt><dt><span class="sect2"><a href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-SUMMARIZATION">19.5.7. WAL Summarization</a></span></dt></dl></div><p>
3 For additional information on tuning these settings,
4 see <a class="xref" href="wal-configuration.html" title="28.5. WAL Configuration">Section 28.5</a>.
5 </p><div class="sect2" id="RUNTIME-CONFIG-WAL-SETTINGS"><div class="titlepage"><div><div><h3 class="title">19.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.6.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 to
9 the WAL. The default value is <code class="literal">replica</code>, which writes enough
10 data to support WAL archiving and replication, including running
11 read-only queries on a standby server. <code class="literal">minimal</code> removes all
12 logging except the information required to recover from a crash or
13 immediate shutdown. Finally,
14 <code class="literal">logical</code> adds information necessary to support logical
15 decoding. Each level includes the information logged at all lower
16 levels. This parameter can only be set at server start.
18 The <code class="literal">minimal</code> level generates the least WAL
19 volume. It logs no row information for permanent relations
20 in transactions that create or
21 rewrite them. This can make operations much faster (see
22 <a class="xref" href="populate.html#POPULATE-PITR" title="14.4.7. Disable WAL Archival and Streaming Replication">Section 14.4.7</a>). Operations that initiate this
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 for
27 point-in-time recovery, so <code class="literal">replica</code> or
28 higher must be used to enable continuous archiving
29 (<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 if
31 <code class="varname">max_wal_senders</code> is non-zero.
32 Note that changing <code class="varname">wal_level</code> to
33 <code class="literal">minimal</code> makes previous base backups unusable
34 for point-in-time recovery and standby servers.
36 In <code class="literal">logical</code> level, the same information is logged as
37 with <code class="literal">replica</code>, plus information needed to
38 extract logical change sets from the WAL. Using a level of
39 <code class="literal">logical</code> will increase the WAL volume, particularly if many
40 tables are configured for <code class="literal">REPLICA IDENTITY FULL</code> and
41 many <code class="command">UPDATE</code> and <code class="command">DELETE</code> statements are
44 In releases prior to 9.6, this parameter also allowed the
45 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.6.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> server
51 will try to make sure that updates are physically written to
52 disk, by issuing <code class="function">fsync()</code> system calls or various
53 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 a
55 consistent state after an operating system or hardware crash.
57 While turning off <code class="varname">fsync</code> is often a performance
58 benefit, this can result in unrecoverable data corruption in
59 the event of a power failure or system crash. Thus it
60 is only advisable to turn off <code class="varname">fsync</code> if
61 you can easily recreate your entire database from external
64 Examples of safe circumstances for turning off
65 <code class="varname">fsync</code> include the initial loading of a new
66 database cluster from a backup file, using a database cluster
67 for processing a batch of data after which the database
68 will be thrown away and recreated,
69 or for a read-only database clone which
70 gets recreated frequently and is not used for failover. High
71 quality hardware alone is not a sufficient justification for
72 turning off <code class="varname">fsync</code>.
74 For reliable recovery when changing <code class="varname">fsync</code>
75 off to on, it is necessary to force all modified buffers in the
76 kernel to durable storage. This can be done while the cluster
77 is shutdown or while <code class="varname">fsync</code> is on by running <code class="command">initdb
78 --sync-only</code>, running <code class="command">sync</code>, unmounting the
79 file system, or rebooting the server.
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 potential
83 performance benefit of turning off <code class="varname">fsync</code>, without
84 the attendant risks of data corruption.
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 off
89 <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.6.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 before
94 the database server returns a <span class="quote">“<span class="quote">success</span>”</span>
95 indication to the client. Valid values are
96 <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>.
100 If <code class="varname">synchronous_standby_names</code> is empty,
101 the only meaningful settings are <code class="literal">on</code> and
102 <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 level
105 as <code class="literal">on</code>. The local behavior of all
106 non-<code class="literal">off</code> modes is to wait for local flush of WAL
107 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 the
109 client and when the transaction is later guaranteed to be safe
110 against a server crash. (The maximum
111 delay is three times <a class="xref" href="runtime-config-wal.html#GUC-WAL-WRITER-DELAY">wal_writer_delay</a>.) Unlike
112 <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 operating
114 system or database crash might
115 result in some recent allegedly-committed transactions being lost, but
116 the database state will be just the same as if those transactions had
117 been aborted cleanly. So, turning <code class="varname">synchronous_commit</code> off
118 can be a useful alternative when performance is more important than
119 exact certainty about the durability of a transaction. For more
120 discussion see <a class="xref" href="wal-async-commit.html" title="28.4. Asynchronous Commit">Section 28.4</a>.
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 whether
124 transaction commits will wait for their WAL records to be
125 processed on the standby server(s).
127 When set to <code class="literal">remote_apply</code>, commits will wait
128 until replies from the current synchronous standby(s) indicate they
129 have received the commit record of the transaction and applied
130 it, so that it has become visible to queries on the standby(s),
131 and also written to durable storage on the standbys. This will
132 cause much larger commit delays than previous settings since
133 it waits for WAL replay. When set to <code class="literal">on</code>,
134 commits wait until replies
135 from the current synchronous standby(s) indicate they have received
136 the commit record of the transaction and flushed it to durable storage. This
137 ensures the transaction will not be lost unless both the primary and
138 all synchronous standbys suffer corruption of their database storage.
139 When set to <code class="literal">remote_write</code>, commits will wait until replies
140 from the current synchronous standby(s) indicate they have
141 received the commit record of the transaction and written it to
142 their file systems. This setting ensures data preservation if a standby instance of
143 <span class="productname">PostgreSQL</span> crashes, but not if the standby
144 suffers an operating-system-level crash because the data has not
145 necessarily reached durable storage on the standby.
146 The setting <code class="literal">local</code> causes commits to wait for
147 local flush to disk, but not for replication. This is usually not
148 desirable when synchronous replication is in use, but is provided for
151 This parameter can be changed at any time; the behavior for any
152 one transaction is determined by the setting in effect when it
153 commits. It is therefore possible, and useful, to have some
154 transactions commit synchronously and others asynchronously.
155 For example, to make a single multistatement transaction commit
156 asynchronously when the default is the opposite, issue <code class="command">SET
157 LOCAL synchronous_commit TO OFF</code> within the transaction.
159 <a class="xref" href="runtime-config-wal.html#SYNCHRONOUS-COMMIT-MATRIX" title="Table 19.1. synchronous_commit Modes">Table 19.1</a> summarizes the
160 capabilities of the <code class="varname">synchronous_commit</code> settings.
161 </p><div class="table" id="SYNCHRONOUS-COMMIT-MATRIX"><p class="title"><strong>Table 19.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.6.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.
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 supported
181 by the platform, except that <code class="literal">fdatasync</code> is the default on
182 Linux and FreeBSD. The default is not necessarily ideal; it might be
183 necessary to change this setting or other aspects of your system
184 configuration in order to create a crash-safe configuration or
185 achieve optimal performance.
186 These aspects are discussed in <a class="xref" href="wal-reliability.html" title="28.1. Reliability">Section 28.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.6.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> server
193 writes the entire content of each disk page to WAL during the
194 first modification of that page after a checkpoint.
195 This is needed because
196 a page write that is in process during an operating system crash might
197 be only partially completed, leading to an on-disk page
198 that contains a mix of old and new data. The row-level change data
199 normally stored in WAL will not be enough to completely restore
200 such a page during post-crash recovery. Storing the full page image
201 guarantees that the page can be correctly restored, but at the price
202 of increasing the amount of data that must be written to WAL.
203 (Because WAL replay always starts from a checkpoint, it is sufficient
204 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 to
206 increase the checkpoint interval parameters.)
208 Turning this parameter off speeds normal operation, but
209 might lead to either unrecoverable data corruption, or silent
210 data corruption, after a system failure. The risks are similar to turning off
211 <code class="varname">fsync</code>, though smaller, and it should be turned off
212 only based on the same circumstances recommended for that parameter.
214 Turning off this parameter does not affect use of
215 WAL archiving for point-in-time recovery (PITR)
216 (see <a class="xref" href="continuous-archiving.html" title="25.3. Continuous Archiving and Point-in-Time Recovery (PITR)">Section 25.3</a>).
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.6.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 the
226 first modification of that page after a checkpoint, even for
227 non-critical modifications of so-called hint bits.
229 If data checksums are enabled, hint bit updates are always WAL-logged
230 and this setting is ignored. You can use this setting to test how much
231 extra WAL-logging would occur if your database had data checksums
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.6.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 specified
240 When enabled, the <span class="productname">PostgreSQL</span>
241 server compresses full page images written to WAL (e.g. when
242 <a class="xref" href="runtime-config-wal.html#GUC-FULL-PAGE-WRITES">full_page_writes</a> is on, during a base backup,
244 A compressed page image will be decompressed during WAL replay.
245 The supported methods are <code class="literal">pglz</code>,
246 <code class="literal">lz4</code> (if <span class="productname">PostgreSQL</span>
247 was compiled with <code class="option">--with-lz4</code>) and
248 <code class="literal">zstd</code> (if <span class="productname">PostgreSQL</span>
249 was compiled with <code class="option">--with-zstd</code>).
250 The default value is <code class="literal">off</code>.
251 Only superusers and users with the appropriate <code class="literal">SET</code>
252 privilege can change this setting.
254 Enabling compression can reduce the WAL volume without
255 increasing the risk of unrecoverable data corruption,
256 but at the cost of some extra CPU spent on the compression during
257 WAL logging and on the decompression during WAL replay.
258 </p></dd><dt id="GUC-WAL-INIT-ZERO"><span class="term"><code class="varname">wal_init_zero</code> (<code class="type">boolean</code>)
259 <a id="id-1.6.6.8.3.2.8.1.3" class="indexterm"></a>
260 </span> <a href="#GUC-WAL-INIT-ZERO" class="id_link">#</a></dt><dd><p>
261 If set to <code class="literal">on</code> (the default), this option causes new
262 WAL files to be filled with zeroes. On some file systems, this ensures
263 that space is allocated before we need to write WAL records. However,
264 <em class="firstterm">Copy-On-Write</em> (COW) file systems may not benefit
265 from this technique, so the option is given to skip the unnecessary
266 work. If set to <code class="literal">off</code>, only the final byte is written
267 when the file is created so that it has the expected size.
268 </p></dd><dt id="GUC-WAL-RECYCLE"><span class="term"><code class="varname">wal_recycle</code> (<code class="type">boolean</code>)
269 <a id="id-1.6.6.8.3.2.9.1.3" class="indexterm"></a>
270 </span> <a href="#GUC-WAL-RECYCLE" class="id_link">#</a></dt><dd><p>
271 If set to <code class="literal">on</code> (the default), this option causes WAL
272 files to be recycled by renaming them, avoiding the need to create new
273 ones. On COW file systems, it may be faster to create new ones, so the
274 option is given to disable this behavior.
275 </p></dd><dt id="GUC-WAL-BUFFERS"><span class="term"><code class="varname">wal_buffers</code> (<code class="type">integer</code>)
276 <a id="id-1.6.6.8.3.2.10.1.3" class="indexterm"></a>
277 </span> <a href="#GUC-WAL-BUFFERS" class="id_link">#</a></dt><dd><p>
278 The amount of shared memory used for WAL data that has not yet been
279 written to disk. The default setting of -1 selects a size equal to
280 1/32nd (about 3%) of <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but not less
281 than <code class="literal">64kB</code> nor more than the size of one WAL
282 segment, typically <code class="literal">16MB</code>. This value can be set
283 manually if the automatic choice is too large or too small,
284 but any positive value less than <code class="literal">32kB</code> will be
285 treated as <code class="literal">32kB</code>.
286 If this value is specified without units, it is taken as WAL blocks,
287 that is <code class="symbol">XLOG_BLCKSZ</code> bytes, typically 8kB.
288 This parameter can only be set at server start.
290 The contents of the WAL buffers are written out to disk at every
291 transaction commit, so extremely large values are unlikely to
292 provide a significant benefit. However, setting this value to at
293 least a few megabytes can improve write performance on a busy
294 server where many clients are committing at once. The auto-tuning
295 selected by the default setting of -1 should give reasonable
296 results in most cases.
297 </p></dd><dt id="GUC-WAL-WRITER-DELAY"><span class="term"><code class="varname">wal_writer_delay</code> (<code class="type">integer</code>)
298 <a id="id-1.6.6.8.3.2.11.1.3" class="indexterm"></a>
299 </span> <a href="#GUC-WAL-WRITER-DELAY" class="id_link">#</a></dt><dd><p>
300 Specifies how often the WAL writer flushes WAL, in time terms.
301 After flushing WAL the writer sleeps for the length of time given
302 by <code class="varname">wal_writer_delay</code>, unless woken up sooner
303 by an asynchronously committing transaction. If the last flush
304 happened less than <code class="varname">wal_writer_delay</code> ago and less
305 than <code class="varname">wal_writer_flush_after</code> worth of WAL has been
306 produced since, then WAL is only written to the operating system, not
308 If this value is specified without units, it is taken as milliseconds.
309 The default value is 200 milliseconds (<code class="literal">200ms</code>). Note that
310 on some systems, the effective resolution of sleep delays is 10
311 milliseconds; setting <code class="varname">wal_writer_delay</code> to a value that is
312 not a multiple of 10 might have the same results as setting it to the
313 next higher multiple of 10. This parameter can only be set in the
314 <code class="filename">postgresql.conf</code> file or on the server command line.
315 </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>)
316 <a id="id-1.6.6.8.3.2.12.1.3" class="indexterm"></a>
317 </span> <a href="#GUC-WAL-WRITER-FLUSH-AFTER" class="id_link">#</a></dt><dd><p>
318 Specifies how often the WAL writer flushes WAL, in volume terms.
319 If the last flush happened less
320 than <code class="varname">wal_writer_delay</code> ago and less
321 than <code class="varname">wal_writer_flush_after</code> worth of WAL has been
322 produced since, then WAL is only written to the operating system, not
323 flushed to disk. If <code class="varname">wal_writer_flush_after</code> is set
324 to <code class="literal">0</code> then WAL data is always flushed immediately.
325 If this value is specified without units, it is taken as WAL blocks,
326 that is <code class="symbol">XLOG_BLCKSZ</code> bytes, typically 8kB.
327 The default is <code class="literal">1MB</code>.
328 This parameter can only be set in the
329 <code class="filename">postgresql.conf</code> file or on the server command line.
330 </p></dd><dt id="GUC-WAL-SKIP-THRESHOLD"><span class="term"><code class="varname">wal_skip_threshold</code> (<code class="type">integer</code>)
331 <a id="id-1.6.6.8.3.2.13.1.3" class="indexterm"></a>
332 </span> <a href="#GUC-WAL-SKIP-THRESHOLD" class="id_link">#</a></dt><dd><p>
333 When <code class="varname">wal_level</code> is <code class="literal">minimal</code> and a
334 transaction commits after creating or rewriting a permanent relation,
335 this setting determines how to persist the new data. If the data is
336 smaller than this setting, write it to the WAL log; otherwise, use an
337 fsync of affected files. Depending on the properties of your storage,
338 raising or lowering this value might help if such commits are slowing
339 concurrent transactions. If this value is specified without units, it
340 is taken as kilobytes. The default is two megabytes
341 (<code class="literal">2MB</code>).
342 </p></dd><dt id="GUC-COMMIT-DELAY"><span class="term"><code class="varname">commit_delay</code> (<code class="type">integer</code>)
343 <a id="id-1.6.6.8.3.2.14.1.3" class="indexterm"></a>
344 </span> <a href="#GUC-COMMIT-DELAY" class="id_link">#</a></dt><dd><p>
345 Setting <code class="varname">commit_delay</code> adds a time delay
346 before a WAL flush is initiated. This can improve
347 group commit throughput by allowing a larger number of transactions
348 to commit via a single WAL flush, if system load is high enough
349 that additional transactions become ready to commit within the
350 given interval. However, it also increases latency by up to the
351 <code class="varname">commit_delay</code> for each WAL
352 flush. Because the delay is just wasted if no other transactions
353 become ready to commit, a delay is only performed if at least
354 <code class="varname">commit_siblings</code> other transactions are active
355 when a flush is about to be initiated. Also, no delays are
356 performed if <code class="varname">fsync</code> is disabled.
357 If this value is specified without units, it is taken as microseconds.
358 The default <code class="varname">commit_delay</code> is zero (no delay).
359 Only superusers and users with the appropriate <code class="literal">SET</code>
360 privilege can change this setting.
362 In <span class="productname">PostgreSQL</span> releases prior to 9.3,
363 <code class="varname">commit_delay</code> behaved differently and was much
364 less effective: it affected only commits, rather than all WAL flushes,
365 and waited for the entire configured delay even if the WAL flush
366 was completed sooner. Beginning in <span class="productname">PostgreSQL</span> 9.3,
367 the first process that becomes ready to flush waits for the configured
368 interval, while subsequent processes wait only until the leader
369 completes the flush operation.
370 </p></dd><dt id="GUC-COMMIT-SIBLINGS"><span class="term"><code class="varname">commit_siblings</code> (<code class="type">integer</code>)
371 <a id="id-1.6.6.8.3.2.15.1.3" class="indexterm"></a>
372 </span> <a href="#GUC-COMMIT-SIBLINGS" class="id_link">#</a></dt><dd><p>
373 Minimum number of concurrent open transactions to require
374 before performing the <code class="varname">commit_delay</code> delay. A larger
375 value makes it more probable that at least one other
376 transaction will become ready to commit during the delay
377 interval. The default is five transactions.
378 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-WAL-CHECKPOINTS"><div class="titlepage"><div><div><h3 class="title">19.5.2. Checkpoints <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>)
379 <a id="id-1.6.6.8.4.2.1.1.3" class="indexterm"></a>
380 </span> <a href="#GUC-CHECKPOINT-TIMEOUT" class="id_link">#</a></dt><dd><p>
381 Maximum time between automatic WAL checkpoints.
382 If this value is specified without units, it is taken as seconds.
383 The valid range is between 30 seconds and one day.
384 The default is five minutes (<code class="literal">5min</code>).
385 Increasing this parameter can increase the amount of time needed
387 This parameter can only be set in the <code class="filename">postgresql.conf</code>
388 file or on the server command line.
389 </p></dd><dt id="GUC-CHECKPOINT-COMPLETION-TARGET"><span class="term"><code class="varname">checkpoint_completion_target</code> (<code class="type">floating point</code>)
390 <a id="id-1.6.6.8.4.2.2.1.3" class="indexterm"></a>
391 </span> <a href="#GUC-CHECKPOINT-COMPLETION-TARGET" class="id_link">#</a></dt><dd><p>
392 Specifies the target of checkpoint completion, as a fraction of
393 total time between checkpoints. The default is 0.9, which spreads the
394 checkpoint across almost all of the available interval, providing fairly
395 consistent I/O load while also leaving some time for checkpoint
396 completion overhead. Reducing this parameter is not recommended because
397 it causes the checkpoint to complete faster. This results in a higher
398 rate of I/O during the checkpoint followed by a period of less I/O between
399 the checkpoint completion and the next scheduled checkpoint. This
400 parameter can only be set in the <code class="filename">postgresql.conf</code> file
401 or on the server command line.
402 </p></dd><dt id="GUC-CHECKPOINT-FLUSH-AFTER"><span class="term"><code class="varname">checkpoint_flush_after</code> (<code class="type">integer</code>)
403 <a id="id-1.6.6.8.4.2.3.1.3" class="indexterm"></a>
404 </span> <a href="#GUC-CHECKPOINT-FLUSH-AFTER" class="id_link">#</a></dt><dd><p>
405 Whenever more than this amount of data has been
406 written while performing a checkpoint, attempt to force the
407 OS to issue these writes to the underlying storage. Doing so will
408 limit the amount of dirty data in the kernel's page cache, reducing
409 the likelihood of stalls when an <code class="function">fsync</code> is issued at the end of the
410 checkpoint, or when the OS writes data back in larger batches in the
411 background. Often that will result in greatly reduced transaction
412 latency, but there also are some cases, especially with workloads
413 that are bigger than <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but smaller
414 than the OS's page cache, where performance might degrade. This
415 setting may have no effect on some platforms.
416 If this value is specified without units, it is taken as blocks,
417 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
419 between <code class="literal">0</code>, which disables forced writeback,
420 and <code class="literal">2MB</code>. The default is <code class="literal">256kB</code> on
421 Linux, <code class="literal">0</code> elsewhere. (If <code class="symbol">BLCKSZ</code> is not
422 8kB, the default and maximum values scale proportionally to it.)
423 This parameter can only be set in the <code class="filename">postgresql.conf</code>
424 file or on the server command line.
425 </p></dd><dt id="GUC-CHECKPOINT-WARNING"><span class="term"><code class="varname">checkpoint_warning</code> (<code class="type">integer</code>)
426 <a id="id-1.6.6.8.4.2.4.1.3" class="indexterm"></a>
427 </span> <a href="#GUC-CHECKPOINT-WARNING" class="id_link">#</a></dt><dd><p>
428 Write a message to the server log if checkpoints caused by
429 the filling of WAL segment files happen closer together
430 than this amount of time (which suggests that
431 <code class="varname">max_wal_size</code> ought to be raised).
432 If this value is specified without units, it is taken as seconds.
433 The default is 30 seconds (<code class="literal">30s</code>).
434 Zero disables the warning.
435 No warnings will be generated if <code class="varname">checkpoint_timeout</code>
436 is less than <code class="varname">checkpoint_warning</code>.
437 This parameter can only be set in the <code class="filename">postgresql.conf</code>
438 file or on the server command line.
439 </p></dd><dt id="GUC-MAX-WAL-SIZE"><span class="term"><code class="varname">max_wal_size</code> (<code class="type">integer</code>)
440 <a id="id-1.6.6.8.4.2.5.1.3" class="indexterm"></a>
441 </span> <a href="#GUC-MAX-WAL-SIZE" class="id_link">#</a></dt><dd><p>
442 Maximum size to let the WAL grow during automatic
443 checkpoints. This is a soft limit; WAL size can exceed
444 <code class="varname">max_wal_size</code> under special circumstances, such as
445 heavy load, a failing <code class="varname">archive_command</code> or <code class="varname">archive_library</code>, or a high
446 <code class="varname">wal_keep_size</code> setting.
447 If this value is specified without units, it is taken as megabytes.
449 Increasing this parameter can increase the amount of time needed for
451 This parameter can only be set in the <code class="filename">postgresql.conf</code>
452 file or on the server command line.
453 </p></dd><dt id="GUC-MIN-WAL-SIZE"><span class="term"><code class="varname">min_wal_size</code> (<code class="type">integer</code>)
454 <a id="id-1.6.6.8.4.2.6.1.3" class="indexterm"></a>
455 </span> <a href="#GUC-MIN-WAL-SIZE" class="id_link">#</a></dt><dd><p>
456 As long as WAL disk usage stays below this setting, old WAL files are
457 always recycled for future use at a checkpoint, rather than removed.
458 This can be used to ensure that enough WAL space is reserved to
459 handle spikes in WAL usage, for example when running large batch
461 If this value is specified without units, it is taken as megabytes.
462 The default is 80 MB.
463 This parameter can only be set in the <code class="filename">postgresql.conf</code>
464 file or on the server command line.
465 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-WAL-ARCHIVING"><div class="titlepage"><div><div><h3 class="title">19.5.3. Archiving <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>)
466 <a id="id-1.6.6.8.5.2.1.1.3" class="indexterm"></a>
467 </span> <a href="#GUC-ARCHIVE-MODE" class="id_link">#</a></dt><dd><p>
468 When <code class="varname">archive_mode</code> is enabled, completed WAL segments
469 are sent to archive storage by setting
470 <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-COMMAND">archive_command</a> or
471 <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-LIBRARY">archive_library</a>. In addition to <code class="literal">off</code>,
472 to disable, there are two modes: <code class="literal">on</code>, and
473 <code class="literal">always</code>. During normal operation, there is no
474 difference between the two modes, but when set to <code class="literal">always</code>
475 the WAL archiver is enabled also during archive recovery or standby
476 mode. In <code class="literal">always</code> mode, all files restored from the archive
477 or streamed with streaming replication will be archived (again). See
478 <a class="xref" href="warm-standby.html#CONTINUOUS-ARCHIVING-IN-STANDBY" title="26.2.9. Continuous Archiving in Standby">Section 26.2.9</a> for details.
480 <code class="varname">archive_mode</code> is a separate setting from
481 <code class="varname">archive_command</code> and
482 <code class="varname">archive_library</code> so that
483 <code class="varname">archive_command</code> and
484 <code class="varname">archive_library</code> can be changed without leaving
486 This parameter can only be set at server start.
487 <code class="varname">archive_mode</code> cannot be enabled when
488 <code class="varname">wal_level</code> is set to <code class="literal">minimal</code>.
489 </p></dd><dt id="GUC-ARCHIVE-COMMAND"><span class="term"><code class="varname">archive_command</code> (<code class="type">string</code>)
490 <a id="id-1.6.6.8.5.2.2.1.3" class="indexterm"></a>
491 </span> <a href="#GUC-ARCHIVE-COMMAND" class="id_link">#</a></dt><dd><p>
492 The local shell command to execute to archive a completed WAL file
493 segment. Any <code class="literal">%p</code> in the string is
494 replaced by the path name of the file to archive, and any
495 <code class="literal">%f</code> is replaced by only the file name.
496 (The path name is relative to the working directory of the server,
497 i.e., the cluster's data directory.)
498 Use <code class="literal">%%</code> to embed an actual <code class="literal">%</code> character in the
499 command. It is important for the command to return a zero
500 exit status only if it succeeds. For more information see
501 <a class="xref" href="continuous-archiving.html#BACKUP-ARCHIVING-WAL" title="25.3.1. Setting Up WAL Archiving">Section 25.3.1</a>.
503 This parameter can only be set in the <code class="filename">postgresql.conf</code>
504 file or on the server command line. It is only used if
505 <code class="varname">archive_mode</code> was enabled at server start and
506 <code class="varname">archive_library</code> is set to an empty string. If both
507 <code class="varname">archive_command</code> and <code class="varname">archive_library</code>
508 are set, an error will be raised.
509 If <code class="varname">archive_command</code> is an empty string (the default) while
510 <code class="varname">archive_mode</code> is enabled (and <code class="varname">archive_library</code>
511 is set to an empty string), WAL archiving is temporarily
512 disabled, but the server continues to accumulate WAL segment files in
513 the expectation that a command will soon be provided. Setting
514 <code class="varname">archive_command</code> to a command that does nothing but
515 return true, e.g., <code class="literal">/bin/true</code> (<code class="literal">REM</code> on
516 Windows), effectively disables
517 archiving, but also breaks the chain of WAL files needed for
518 archive recovery, so it should only be used in unusual circumstances.
519 </p></dd><dt id="GUC-ARCHIVE-LIBRARY"><span class="term"><code class="varname">archive_library</code> (<code class="type">string</code>)
520 <a id="id-1.6.6.8.5.2.3.1.3" class="indexterm"></a>
521 </span> <a href="#GUC-ARCHIVE-LIBRARY" class="id_link">#</a></dt><dd><p>
522 The library to use for archiving completed WAL file segments. If set to
523 an empty string (the default), archiving via shell is enabled, and
524 <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-COMMAND">archive_command</a> is used. If both
525 <code class="varname">archive_command</code> and <code class="varname">archive_library</code>
526 are set, an error will be raised. Otherwise, the specified
527 shared library is used for archiving. The WAL archiver process is
528 restarted by the postmaster when this parameter changes. For more
529 information, see <a class="xref" href="continuous-archiving.html#BACKUP-ARCHIVING-WAL" title="25.3.1. Setting Up WAL Archiving">Section 25.3.1</a> and
530 <a class="xref" href="archive-modules.html" title="Chapter 49. Archive Modules">Chapter 49</a>.
532 This parameter can only be set in the
533 <code class="filename">postgresql.conf</code> file or on the server command line.
534 </p></dd><dt id="GUC-ARCHIVE-TIMEOUT"><span class="term"><code class="varname">archive_timeout</code> (<code class="type">integer</code>)
535 <a id="id-1.6.6.8.5.2.4.1.3" class="indexterm"></a>
536 </span> <a href="#GUC-ARCHIVE-TIMEOUT" class="id_link">#</a></dt><dd><p>
537 The <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-COMMAND">archive_command</a> or <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-LIBRARY">archive_library</a> is only invoked for
538 completed WAL segments. Hence, if your server generates little WAL
539 traffic (or has slack periods where it does so), there could be a
540 long delay between the completion of a transaction and its safe
541 recording in archive storage. To limit how old unarchived
542 data can be, you can set <code class="varname">archive_timeout</code> to force the
543 server to switch to a new WAL segment file periodically. When this
544 parameter is greater than zero, the server will switch to a new
545 segment file whenever this amount of time has elapsed since the last
546 segment file switch, and there has been any database activity,
547 including a single checkpoint (checkpoints are skipped if there is
548 no database activity). Note that archived files that are closed
549 early due to a forced switch are still the same length as completely
550 full files. Therefore, it is unwise to use a very short
551 <code class="varname">archive_timeout</code> — it will bloat your archive
552 storage. <code class="varname">archive_timeout</code> settings of a minute or so are
553 usually reasonable. You should consider using streaming replication,
554 instead of archiving, if you want data to be copied off the primary
555 server more quickly than that.
556 If this value is specified without units, it is taken as seconds.
557 This parameter can only be set in the
558 <code class="filename">postgresql.conf</code> file or on the server command line.
559 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-WAL-RECOVERY"><div class="titlepage"><div><div><h3 class="title">19.5.4. Recovery <a href="#RUNTIME-CONFIG-WAL-RECOVERY" class="id_link">#</a></h3></div></div></div><a id="id-1.6.6.8.6.2" class="indexterm"></a><p>
560 This section describes the settings that apply to recovery in general,
561 affecting crash recovery, streaming replication and archive-based
563 </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>)
564 <a id="id-1.6.6.8.6.4.1.1.3" class="indexterm"></a>
565 </span> <a href="#GUC-RECOVERY-PREFETCH" class="id_link">#</a></dt><dd><p>
566 Whether to try to prefetch blocks that are referenced in the WAL that
567 are not yet in the buffer pool, during recovery. Valid values are
568 <code class="literal">off</code>, <code class="literal">on</code> and
569 <code class="literal">try</code> (the default). The setting
570 <code class="literal">try</code> enables
571 prefetching only if the operating system provides support for issuing
574 Prefetching blocks that will soon be needed can reduce I/O wait times
575 during recovery with some workloads.
576 See also the <a class="xref" href="runtime-config-wal.html#GUC-WAL-DECODE-BUFFER-SIZE">wal_decode_buffer_size</a> and
577 <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-IO-CONCURRENCY">maintenance_io_concurrency</a> settings, which limit
578 prefetching activity.
579 </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>)
580 <a id="id-1.6.6.8.6.4.2.1.3" class="indexterm"></a>
581 </span> <a href="#GUC-WAL-DECODE-BUFFER-SIZE" class="id_link">#</a></dt><dd><p>
582 A limit on how far ahead the server can look in the WAL, to find
583 blocks to prefetch. If this value is specified without units, it is
585 The default is 512kB.
586 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY"><div class="titlepage"><div><div><h3 class="title">19.5.5. Archive Recovery <a href="#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY" class="id_link">#</a></h3></div></div></div><a id="id-1.6.6.8.7.2" class="indexterm"></a><p>
587 This section describes the settings that apply only for the duration of
588 the recovery. They must be reset for any subsequent recovery you wish to
591 <span class="quote">“<span class="quote">Recovery</span>”</span> covers using the server as a standby or for
592 executing a targeted recovery. Typically, standby mode would be used to
593 provide high availability and/or read scalability, whereas a targeted
594 recovery is used to recover from data loss.
596 To start the server in standby mode, create a file called
597 <code class="filename">standby.signal</code><a id="id-1.6.6.8.7.5.2" class="indexterm"></a>
598 in the data directory. The server will enter recovery and will not stop
599 recovery when the end of archived WAL is reached, but will keep trying to
600 continue recovery by connecting to the sending server as specified by the
601 <code class="varname">primary_conninfo</code> setting and/or by fetching new WAL
602 segments using <code class="varname">restore_command</code>. For this mode, the
603 parameters from this section and <a class="xref" href="runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-STANDBY" title="19.6.3. Standby Servers">Section 19.6.3</a> are of interest.
604 Parameters from <a class="xref" href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET" title="19.5.6. Recovery Target">Section 19.5.6</a> will
605 also be applied but are typically not useful in this mode.
607 To start the server in targeted recovery mode, create a file called
608 <code class="filename">recovery.signal</code><a id="id-1.6.6.8.7.6.2" class="indexterm"></a>
609 in the data directory. If both <code class="filename">standby.signal</code> and
610 <code class="filename">recovery.signal</code> files are created, standby mode
611 takes precedence. Targeted recovery mode ends when the archived WAL is
612 fully replayed, or when <code class="varname">recovery_target</code> is reached.
613 In this mode, the parameters from both this section and <a class="xref" href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET" title="19.5.6. Recovery Target">Section 19.5.6</a> will be used.
614 </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>)
615 <a id="id-1.6.6.8.7.7.1.1.3" class="indexterm"></a>
616 </span> <a href="#GUC-RESTORE-COMMAND" class="id_link">#</a></dt><dd><p>
617 The local shell command to execute to retrieve an archived segment of
618 the WAL file series. This parameter is required for archive recovery,
619 but optional for streaming replication.
620 Any <code class="literal">%f</code> in the string is
621 replaced by the name of the file to retrieve from the archive,
622 and any <code class="literal">%p</code> is replaced by the copy destination path name
624 (The path name is relative to the current working directory,
625 i.e., the cluster's data directory.)
626 Any <code class="literal">%r</code> is replaced by the name of the file containing the
627 last valid restart point. That is the earliest file that must be kept
628 to allow a restore to be restartable, so this information can be used
629 to truncate the archive to just the minimum required to support
630 restarting from the current restore. <code class="literal">%r</code> is typically only
631 used by warm-standby configurations
632 (see <a class="xref" href="warm-standby.html" title="26.2. Log-Shipping Standby Servers">Section 26.2</a>).
633 Write <code class="literal">%%</code> to embed an actual <code class="literal">%</code> character.
635 It is important for the command to return a zero exit status
636 only if it succeeds. The command <span class="emphasis"><em>will</em></span> be asked for file
637 names that are not present in the archive; it must return nonzero
638 when so asked. Examples:
639 </p><pre class="programlisting">
640 restore_command = 'cp /mnt/server/archivedir/%f "%p"'
641 restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows
643 An exception is that if the command was terminated by a signal (other
644 than <span class="systemitem">SIGTERM</span>, which is used as part of a
645 database server shutdown) or an error by the shell (such as command
646 not found), then recovery will abort and the server will not start up.
648 This parameter can only be set in the <code class="filename">postgresql.conf</code>
649 file or on the server command line.
650 </p></dd><dt id="GUC-ARCHIVE-CLEANUP-COMMAND"><span class="term"><code class="varname">archive_cleanup_command</code> (<code class="type">string</code>)
651 <a id="id-1.6.6.8.7.7.2.1.3" class="indexterm"></a>
652 </span> <a href="#GUC-ARCHIVE-CLEANUP-COMMAND" class="id_link">#</a></dt><dd><p>
653 This optional parameter specifies a shell command that will be executed
654 at every restartpoint. The purpose of
655 <code class="varname">archive_cleanup_command</code> is to provide a mechanism for
656 cleaning up old archived WAL files that are no longer needed by the
658 Any <code class="literal">%r</code> is replaced by the name of the file containing the
659 last valid restart point.
660 That is the earliest file that must be <span class="emphasis"><em>kept</em></span> to allow a
661 restore to be restartable, and so all files earlier than <code class="literal">%r</code>
662 may be safely removed.
663 This information can be used to truncate the archive to just the
664 minimum required to support restart from the current restore.
665 The <a class="xref" href="pgarchivecleanup.html" title="pg_archivecleanup"><span class="refentrytitle"><span class="application">pg_archivecleanup</span></span></a> module
666 is often used in <code class="varname">archive_cleanup_command</code> for
667 single-standby configurations, for example:
668 </p><pre class="programlisting">archive_cleanup_command = 'pg_archivecleanup /mnt/server/archivedir %r'</pre><p>
669 Note however that if multiple standby servers are restoring from the
670 same archive directory, you will need to ensure that you do not delete
671 WAL files until they are no longer needed by any of the servers.
672 <code class="varname">archive_cleanup_command</code> would typically be used in a
673 warm-standby configuration (see <a class="xref" href="warm-standby.html" title="26.2. Log-Shipping Standby Servers">Section 26.2</a>).
674 Write <code class="literal">%%</code> to embed an actual <code class="literal">%</code> character in the
677 If the command returns a nonzero exit status then a warning log
678 message will be written. An exception is that if the command was
679 terminated by a signal or an error by the shell (such as command not
680 found), a fatal error will be raised.
682 This parameter can only be set in the <code class="filename">postgresql.conf</code>
683 file or on the server command line.
684 </p></dd><dt id="GUC-RECOVERY-END-COMMAND"><span class="term"><code class="varname">recovery_end_command</code> (<code class="type">string</code>)
685 <a id="id-1.6.6.8.7.7.3.1.3" class="indexterm"></a>
686 </span> <a href="#GUC-RECOVERY-END-COMMAND" class="id_link">#</a></dt><dd><p>
687 This parameter specifies a shell command that will be executed once only
688 at the end of recovery. This parameter is optional. The purpose of the
689 <code class="varname">recovery_end_command</code> is to provide a mechanism for cleanup
690 following replication or recovery.
691 Any <code class="literal">%r</code> is replaced by the name of the file containing the
692 last valid restart point, like in <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-CLEANUP-COMMAND">archive_cleanup_command</a>.
694 If the command returns a nonzero exit status then a warning log
695 message will be written and the database will proceed to start up
696 anyway. An exception is that if the command was terminated by a
697 signal or an error by the shell (such as command not found), the
698 database will not proceed with startup.
700 This parameter can only be set in the <code class="filename">postgresql.conf</code>
701 file or on the server command line.
702 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-WAL-RECOVERY-TARGET"><div class="titlepage"><div><div><h3 class="title">19.5.6. Recovery Target <a href="#RUNTIME-CONFIG-WAL-RECOVERY-TARGET" class="id_link">#</a></h3></div></div></div><p>
703 By default, recovery will recover to the end of the WAL log. The
704 following parameters can be used to specify an earlier stopping point.
705 At most one of <code class="varname">recovery_target</code>,
706 <code class="varname">recovery_target_lsn</code>, <code class="varname">recovery_target_name</code>,
707 <code class="varname">recovery_target_time</code>, or <code class="varname">recovery_target_xid</code>
708 can be used; if more than one of these is specified in the configuration
709 file, an error will be raised.
710 These parameters can only be set at server start.
711 </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>
712 <a id="id-1.6.6.8.8.3.1.1.3" class="indexterm"></a>
713 </span> <a href="#GUC-RECOVERY-TARGET" class="id_link">#</a></dt><dd><p>
714 This parameter specifies that recovery should end as soon as a
715 consistent state is reached, i.e., as early as possible. When restoring
716 from an online backup, this means the point where taking the backup
719 Technically, this is a string parameter, but <code class="literal">'immediate'</code>
720 is currently the only allowed value.
721 </p></dd><dt id="GUC-RECOVERY-TARGET-NAME"><span class="term"><code class="varname">recovery_target_name</code> (<code class="type">string</code>)
722 <a id="id-1.6.6.8.8.3.2.1.3" class="indexterm"></a>
723 </span> <a href="#GUC-RECOVERY-TARGET-NAME" class="id_link">#</a></dt><dd><p>
724 This parameter specifies the named restore point (created with
725 <code class="function">pg_create_restore_point()</code>) to which recovery will proceed.
726 </p></dd><dt id="GUC-RECOVERY-TARGET-TIME"><span class="term"><code class="varname">recovery_target_time</code> (<code class="type">timestamp</code>)
727 <a id="id-1.6.6.8.8.3.3.1.3" class="indexterm"></a>
728 </span> <a href="#GUC-RECOVERY-TARGET-TIME" class="id_link">#</a></dt><dd><p>
729 This parameter specifies the time stamp up to which recovery
731 The precise stopping point is also influenced by
732 <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-INCLUSIVE">recovery_target_inclusive</a>.
734 The value of this parameter is a time stamp in the same format
735 accepted by the <code class="type">timestamp with time zone</code> data type,
736 except that you cannot use a time zone abbreviation (unless the
737 <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE-ABBREVIATIONS">timezone_abbreviations</a> variable has been set
738 earlier in the configuration file). Preferred style is to use a
739 numeric offset from UTC, or you can write a full time zone name,
740 e.g., <code class="literal">Europe/Helsinki</code> not <code class="literal">EEST</code>.
741 </p></dd><dt id="GUC-RECOVERY-TARGET-XID"><span class="term"><code class="varname">recovery_target_xid</code> (<code class="type">string</code>)
742 <a id="id-1.6.6.8.8.3.4.1.3" class="indexterm"></a>
743 </span> <a href="#GUC-RECOVERY-TARGET-XID" class="id_link">#</a></dt><dd><p>
744 This parameter specifies the transaction ID up to which recovery
745 will proceed. Keep in mind
746 that while transaction IDs are assigned sequentially at transaction
747 start, transactions can complete in a different numeric order.
748 The transactions that will be recovered are those that committed
749 before (and optionally including) the specified one.
750 The precise stopping point is also influenced by
751 <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-INCLUSIVE">recovery_target_inclusive</a>.
752 </p></dd><dt id="GUC-RECOVERY-TARGET-LSN"><span class="term"><code class="varname">recovery_target_lsn</code> (<code class="type">pg_lsn</code>)
753 <a id="id-1.6.6.8.8.3.5.1.3" class="indexterm"></a>
754 </span> <a href="#GUC-RECOVERY-TARGET-LSN" class="id_link">#</a></dt><dd><p>
755 This parameter specifies the LSN of the write-ahead log location up
756 to which recovery will proceed. The precise stopping point is also
757 influenced by <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-INCLUSIVE">recovery_target_inclusive</a>. This
758 parameter is parsed using the system data type
759 <a class="link" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type"><code class="type">pg_lsn</code></a>.
760 </p></dd></dl></div><p>
761 The following options further specify the recovery target, and affect
762 what happens when the target is reached:
763 </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>)
764 <a id="id-1.6.6.8.8.5.1.1.3" class="indexterm"></a>
765 </span> <a href="#GUC-RECOVERY-TARGET-INCLUSIVE" class="id_link">#</a></dt><dd><p>
766 Specifies whether to stop just after the specified recovery target
767 (<code class="literal">on</code>), or just before the recovery target
768 (<code class="literal">off</code>).
769 Applies when <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-LSN">recovery_target_lsn</a>,
770 <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-TIME">recovery_target_time</a>, or
771 <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-XID">recovery_target_xid</a> is specified.
772 This setting controls whether transactions
773 having exactly the target WAL location (LSN), commit time, or transaction ID, respectively, will
774 be included in the recovery. Default is <code class="literal">on</code>.
775 </p></dd><dt id="GUC-RECOVERY-TARGET-TIMELINE"><span class="term"><code class="varname">recovery_target_timeline</code> (<code class="type">string</code>)
776 <a id="id-1.6.6.8.8.5.2.1.3" class="indexterm"></a>
777 </span> <a href="#GUC-RECOVERY-TARGET-TIMELINE" class="id_link">#</a></dt><dd><p>
778 Specifies recovering into a particular timeline. The value can be a
779 numeric timeline ID or a special value. The value
780 <code class="literal">current</code> recovers along the same timeline that was
781 current when the base backup was taken. The
782 value <code class="literal">latest</code> recovers
783 to the latest timeline found in the archive, which is useful in
784 a standby server. <code class="literal">latest</code> is the default.
786 To specify a timeline ID in hexadecimal (for example, if extracted
787 from a WAL file name or history file), prefix it with a
788 <code class="literal">0x</code>. For instance, if the WAL file name is
789 <code class="filename">00000011000000A10000004F</code>, then the timeline ID is
790 <code class="literal">0x11</code> (or 17 decimal).
792 You usually only need to set this parameter
793 in complex re-recovery situations, where you need to return to
794 a state that itself was reached after a point-in-time recovery.
795 See <a class="xref" href="continuous-archiving.html#BACKUP-TIMELINES" title="25.3.6. Timelines">Section 25.3.6</a> for discussion.
796 </p></dd><dt id="GUC-RECOVERY-TARGET-ACTION"><span class="term"><code class="varname">recovery_target_action</code> (<code class="type">enum</code>)
797 <a id="id-1.6.6.8.8.5.3.1.3" class="indexterm"></a>
798 </span> <a href="#GUC-RECOVERY-TARGET-ACTION" class="id_link">#</a></dt><dd><p>
799 Specifies what action the server should take once the recovery target is
800 reached. The default is <code class="literal">pause</code>, which means recovery will
801 be paused. <code class="literal">promote</code> means the recovery process will finish
802 and the server will start to accept connections.
803 Finally <code class="literal">shutdown</code> will stop the server after reaching the
806 The intended use of the <code class="literal">pause</code> setting is to allow queries
807 to be executed against the database to check if this recovery target
808 is the most desirable point for recovery.
809 The paused state can be resumed by
810 using <code class="function">pg_wal_replay_resume()</code> (see
811 <a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE" title="Table 9.99. Recovery Control Functions">Table 9.99</a>), which then
812 causes recovery to end. If this recovery target is not the
813 desired stopping point, then shut down the server, change the
814 recovery target settings to a later target and restart to
817 The <code class="literal">shutdown</code> setting is useful to have the instance ready
818 at the exact replay point desired. The instance will still be able to
819 replay more WAL records (and in fact will have to replay WAL records
820 since the last checkpoint next time it is started).
822 Note that because <code class="filename">recovery.signal</code> will not be
823 removed when <code class="varname">recovery_target_action</code> is set to <code class="literal">shutdown</code>,
824 any subsequent start will end with immediate shutdown unless the
825 configuration is changed or the <code class="filename">recovery.signal</code>
826 file is removed manually.
828 This setting has no effect if no recovery target is set.
829 If <a class="xref" href="runtime-config-replication.html#GUC-HOT-STANDBY">hot_standby</a> is not enabled, a setting of
830 <code class="literal">pause</code> will act the same as <code class="literal">shutdown</code>.
831 If the recovery target is reached while a promotion is ongoing,
832 a setting of <code class="literal">pause</code> will act the same as
833 <code class="literal">promote</code>.
835 In any case, if a recovery target is configured but the archive
836 recovery ends before the target is reached, the server will shut down
838 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-WAL-SUMMARIZATION"><div class="titlepage"><div><div><h3 class="title">19.5.7. WAL Summarization <a href="#RUNTIME-CONFIG-WAL-SUMMARIZATION" class="id_link">#</a></h3></div></div></div><p>
839 These settings control WAL summarization, a feature which must be
840 enabled in order to perform an
841 <a class="link" href="continuous-archiving.html#BACKUP-INCREMENTAL-BACKUP" title="25.3.3. Making an Incremental Backup">incremental backup</a>.
842 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-SUMMARIZE-WAL"><span class="term"><code class="varname">summarize_wal</code> (<code class="type">boolean</code>)
843 <a id="id-1.6.6.8.9.3.1.1.3" class="indexterm"></a>
844 </span> <a href="#GUC-SUMMARIZE-WAL" class="id_link">#</a></dt><dd><p>
845 Enables the WAL summarizer process. Note that WAL summarization can
846 be enabled either on a primary or on a standby. This parameter can only
847 be set in the <code class="filename">postgresql.conf</code> file or on the server
848 command line. The default is <code class="literal">off</code>.
850 The server cannot be started with <code class="literal">summarize_wal=on</code>
851 if <code class="literal">wal_level</code> is set to <code class="literal">minimal</code>. If
852 <code class="literal">summarize_wal=on</code> is configured after server startup
853 while <code class="literal">wal_level=minimal</code>, the summarizer will run
854 but refuse to generate summary files for any WAL generated with
855 <code class="literal">wal_level=minimal</code>.
856 </p></dd><dt id="GUC-WAL-SUMMARY-KEEP-TIME"><span class="term"><code class="varname">wal_summary_keep_time</code> (<code class="type">integer</code>)
857 <a id="id-1.6.6.8.9.3.2.1.3" class="indexterm"></a>
858 </span> <a href="#GUC-WAL-SUMMARY-KEEP-TIME" class="id_link">#</a></dt><dd><p>
859 Configures the amount of time after which the WAL summarizer
860 automatically removes old WAL summaries. The file timestamp is used to
861 determine which files are old enough to remove. Typically, you should set
862 this comfortably higher than the time that could pass between a backup
863 and a later incremental backup that depends on it. WAL summaries must
864 be available for the entire range of WAL records between the preceding
865 backup and the new one being taken; if not, the incremental backup will
866 fail. If this parameter is set to zero, WAL summaries will not be
867 automatically deleted, but it is safe to manually remove files that you
868 know will not be required for future incremental backups.
869 This parameter can only be set in the
870 <code class="filename">postgresql.conf</code> file or on the server command line.
871 If this value is specified without units, it is taken as minutes.
872 The default is 10 days. If <code class="literal">summarize_wal = off</code>,
873 existing WAL summaries will not be removed regardless of the value of
874 this parameter, because the WAL summarizer will not run.
875 </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="19.4. Resource Consumption">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-replication.html" title="19.6. Replication">Next</a></td></tr><tr><td width="40%" align="left" valign="top">19.4. Resource Consumption </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 19.6. Replication</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob