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.6. Replication</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-wal.html" title="19.5. Write Ahead Log" /><link rel="next" href="runtime-config-query.html" title="19.7. Query Planning" /></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.6. Replication</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-wal.html" title="19.5. Write Ahead Log">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-query.html" title="19.7. Query Planning">Next</a></td></tr></table><hr /></div><div class="sect1" id="RUNTIME-CONFIG-REPLICATION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.6. Replication <a href="#RUNTIME-CONFIG-REPLICATION" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER">19.6.1. Sending Servers</a></span></dt><dt><span class="sect2"><a href="runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-PRIMARY">19.6.2. Primary Server</a></span></dt><dt><span class="sect2"><a href="runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-STANDBY">19.6.3. Standby Servers</a></span></dt><dt><span class="sect2"><a href="runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SUBSCRIBER">19.6.4. Subscribers</a></span></dt></dl></div><p>
3 These settings control the behavior of the built-in
4 <em class="firstterm">streaming replication</em> feature (see
5 <a class="xref" href="warm-standby.html#STREAMING-REPLICATION" title="26.2.5. Streaming Replication">Section 26.2.5</a>), and the built-in
6 <em class="firstterm">logical replication</em> feature (see
7 <a class="xref" href="logical-replication.html" title="Chapter 29. Logical Replication">Chapter 29</a>).
9 For <span class="emphasis"><em>streaming replication</em></span>, servers will be either a
10 primary or a standby server. Primaries can send data, while standbys
11 are always receivers of replicated data. When cascading replication
12 (see <a class="xref" href="warm-standby.html#CASCADING-REPLICATION" title="26.2.7. Cascading Replication">Section 26.2.7</a>) is used, standby servers
13 can also be senders, as well as receivers.
14 Parameters are mainly for sending and standby servers, though some
15 parameters have meaning only on the primary server. Settings may vary
16 across the cluster without problems if that is required.
18 For <span class="emphasis"><em>logical replication</em></span>, <em class="firstterm">publishers</em>
19 (servers that do <a class="link" href="sql-createpublication.html" title="CREATE PUBLICATION"><code class="command">CREATE PUBLICATION</code></a>)
20 replicate data to <em class="firstterm">subscribers</em>
21 (servers that do <a class="link" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><code class="command">CREATE SUBSCRIPTION</code></a>).
22 Servers can also be publishers and subscribers at the same time. Note,
23 the following sections refer to publishers as "senders". For more details
24 about logical replication configuration settings refer to
25 <a class="xref" href="logical-replication-config.html" title="29.12. Configuration Settings">Section 29.12</a>.
26 </p><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-SENDER"><div class="titlepage"><div><div><h3 class="title">19.6.1. Sending Servers <a href="#RUNTIME-CONFIG-REPLICATION-SENDER" class="id_link">#</a></h3></div></div></div><p>
27 These parameters can be set on any server that is
28 to send replication data to one or more standby servers.
29 The primary is always a sending server, so these parameters must
30 always be set on the primary.
31 The role and meaning of these parameters does not change after a
32 standby becomes the primary.
33 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-MAX-WAL-SENDERS"><span class="term"><code class="varname">max_wal_senders</code> (<code class="type">integer</code>)
34 <a id="id-1.6.6.9.5.3.1.1.3" class="indexterm"></a>
35 </span> <a href="#GUC-MAX-WAL-SENDERS" class="id_link">#</a></dt><dd><p>
36 Specifies the maximum number of concurrent connections from standby
37 servers or streaming base backup clients (i.e., the maximum number of
38 simultaneously running WAL sender processes). The default is
39 <code class="literal">10</code>. The value <code class="literal">0</code> means
40 replication is disabled. Abrupt disconnection of a streaming client might
41 leave an orphaned connection slot behind until a timeout is reached,
42 so this parameter should be set slightly higher than the maximum
43 number of expected clients so disconnected clients can immediately
44 reconnect. This parameter can only be set at server start. Also,
45 <code class="varname">wal_level</code> must be set to
46 <code class="literal">replica</code> or higher to allow connections from standby
49 When running a standby server, you must set this parameter to the
50 same or higher value than on the primary server. Otherwise, queries
51 will not be allowed in the standby server.
52 </p></dd><dt id="GUC-MAX-REPLICATION-SLOTS"><span class="term"><code class="varname">max_replication_slots</code> (<code class="type">integer</code>)
53 <a id="id-1.6.6.9.5.3.2.1.3" class="indexterm"></a>
54 </span> <a href="#GUC-MAX-REPLICATION-SLOTS" class="id_link">#</a></dt><dd><p>
55 Specifies the maximum number of replication slots
56 (see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="26.2.6. Replication Slots">Section 26.2.6</a>) that the server
57 can support. The default is 10. This parameter can only be set at
59 Setting it to a lower value than the number of currently
60 existing replication slots will prevent the server from starting.
61 Also, <code class="varname">wal_level</code> must be set
62 to <code class="literal">replica</code> or higher to allow replication slots to
64 </p></dd><dt id="GUC-WAL-KEEP-SIZE"><span class="term"><code class="varname">wal_keep_size</code> (<code class="type">integer</code>)
65 <a id="id-1.6.6.9.5.3.3.1.3" class="indexterm"></a>
66 </span> <a href="#GUC-WAL-KEEP-SIZE" class="id_link">#</a></dt><dd><p>
67 Specifies the minimum size of past WAL files kept in the
68 <code class="filename">pg_wal</code>
69 directory, in case a standby server needs to fetch them for streaming
70 replication. If a standby
71 server connected to the sending server falls behind by more than
72 <code class="varname">wal_keep_size</code> megabytes, the sending server might
73 remove a WAL segment still needed by the standby, in which case the
74 replication connection will be terminated. Downstream connections
75 will also eventually fail as a result. (However, the standby
76 server can recover by fetching the segment from archive, if WAL
79 This sets only the minimum size of segments retained in
80 <code class="filename">pg_wal</code>; the system might need to retain more segments
81 for WAL archival or to recover from a checkpoint. If
82 <code class="varname">wal_keep_size</code> is zero (the default), the system
83 doesn't keep any extra segments for standby purposes, so the number
84 of old WAL segments available to standby servers is a function of
85 the location of the previous checkpoint and status of WAL
87 If this value is specified without units, it is taken as megabytes.
88 This parameter can only be set in the
89 <code class="filename">postgresql.conf</code> file or on the server command line.
90 </p></dd><dt id="GUC-MAX-SLOT-WAL-KEEP-SIZE"><span class="term"><code class="varname">max_slot_wal_keep_size</code> (<code class="type">integer</code>)
91 <a id="id-1.6.6.9.5.3.4.1.3" class="indexterm"></a>
92 </span> <a href="#GUC-MAX-SLOT-WAL-KEEP-SIZE" class="id_link">#</a></dt><dd><p>
93 Specify the maximum size of WAL files
94 that <a class="link" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="26.2.6. Replication Slots">replication
95 slots</a> are allowed to retain in the <code class="filename">pg_wal</code>
96 directory at checkpoint time.
97 If <code class="varname">max_slot_wal_keep_size</code> is -1 (the default),
98 replication slots may retain an unlimited amount of WAL files. Otherwise, if
99 restart_lsn of a replication slot falls behind the current LSN by more
100 than the given size, the standby using the slot may no longer be able
101 to continue replication due to removal of required WAL files. You
102 can see the WAL availability of replication slots
103 in <a class="link" href="view-pg-replication-slots.html" title="53.20. pg_replication_slots">pg_replication_slots</a>.
104 If this value is specified without units, it is taken as megabytes.
105 This parameter can only be set in the <code class="filename">postgresql.conf</code>
106 file or on the server command line.
107 </p></dd><dt id="GUC-IDLE-REPLICATION-SLOT-TIMEOUT"><span class="term"><code class="varname">idle_replication_slot_timeout</code> (<code class="type">integer</code>)
108 <a id="id-1.6.6.9.5.3.5.1.3" class="indexterm"></a>
109 </span> <a href="#GUC-IDLE-REPLICATION-SLOT-TIMEOUT" class="id_link">#</a></dt><dd><p>
110 Invalidate replication slots that have remained inactive (not used by
111 a <a class="link" href="protocol-replication.html" title="54.4. Streaming Replication Protocol">replication connection</a>)
112 for longer than this duration.
113 If this value is specified without units, it is taken as seconds.
114 A value of zero (the default) disables the idle timeout
115 invalidation mechanism. This parameter can only be set in the
116 <code class="filename">postgresql.conf</code> file or on the server command
119 Slot invalidation due to idle timeout occurs during checkpoint.
120 Because checkpoints happen at <code class="varname">checkpoint_timeout</code>
121 intervals, there can be some lag between when the
122 <code class="varname">idle_replication_slot_timeout</code> was exceeded and when
123 the slot invalidation is triggered at the next checkpoint.
124 To avoid such lags, users can force a checkpoint to promptly invalidate
125 inactive slots. The duration of slot inactivity is calculated using the
126 slot's <a class="link" href="view-pg-replication-slots.html" title="53.20. pg_replication_slots">pg_replication_slots</a>.<code class="structfield">inactive_since</code>
129 Note that the idle timeout invalidation mechanism is not applicable
130 for slots that do not reserve WAL or for slots on the standby server
131 that are being synced from the primary server (i.e., standby slots
132 having <a class="link" href="view-pg-replication-slots.html" title="53.20. pg_replication_slots">pg_replication_slots</a>.<code class="structfield">synced</code>
133 value <code class="literal">true</code>). Synced slots are always considered to
134 be inactive because they don't perform logical decoding to produce
136 </p></dd><dt id="GUC-WAL-SENDER-TIMEOUT"><span class="term"><code class="varname">wal_sender_timeout</code> (<code class="type">integer</code>)
137 <a id="id-1.6.6.9.5.3.6.1.3" class="indexterm"></a>
138 </span> <a href="#GUC-WAL-SENDER-TIMEOUT" class="id_link">#</a></dt><dd><p>
139 Terminate replication connections that are inactive for longer
140 than this amount of time. This is useful for
141 the sending server to detect a standby crash or network outage.
142 If this value is specified without units, it is taken as milliseconds.
143 The default value is 60 seconds.
144 A value of zero disables the timeout mechanism.
146 With a cluster distributed across multiple geographic
147 locations, using different values per location brings more flexibility
148 in the cluster management. A smaller value is useful for faster
149 failure detection with a standby having a low-latency network
150 connection, and a larger value helps in judging better the health
151 of a standby if located on a remote location, with a high-latency
153 </p></dd><dt id="GUC-TRACK-COMMIT-TIMESTAMP"><span class="term"><code class="varname">track_commit_timestamp</code> (<code class="type">boolean</code>)
154 <a id="id-1.6.6.9.5.3.7.1.3" class="indexterm"></a>
155 </span> <a href="#GUC-TRACK-COMMIT-TIMESTAMP" class="id_link">#</a></dt><dd><p>
156 Record commit time of transactions. This parameter
157 can only be set in <code class="filename">postgresql.conf</code> file or on the server
158 command line. The default value is <code class="literal">off</code>.
159 </p></dd><dt id="GUC-SYNCHRONIZED-STANDBY-SLOTS"><span class="term"><code class="varname">synchronized_standby_slots</code> (<code class="type">string</code>)
160 <a id="id-1.6.6.9.5.3.8.1.3" class="indexterm"></a>
161 </span> <a href="#GUC-SYNCHRONIZED-STANDBY-SLOTS" class="id_link">#</a></dt><dd><p>
162 A comma-separated list of streaming replication standby server slot names
163 that logical WAL sender processes will wait for. Logical WAL sender processes
164 will send decoded changes to plugins only after the specified replication
165 slots confirm receiving WAL. This guarantees that logical replication
166 failover slots do not consume changes until those changes are received
167 and flushed to corresponding physical standbys. If a
168 logical replication connection is meant to switch to a physical standby
169 after the standby is promoted, the physical replication slot for the
170 standby should be listed here. Note that logical replication will not
171 proceed if the slots specified in the
172 <code class="varname">synchronized_standby_slots</code> do not exist or are invalidated.
173 Additionally, the replication management functions
174 <a class="link" href="functions-admin.html#PG-REPLICATION-SLOT-ADVANCE">
175 <code class="function">pg_replication_slot_advance</code></a>,
176 <a class="link" href="functions-admin.html#PG-LOGICAL-SLOT-GET-CHANGES">
177 <code class="function">pg_logical_slot_get_changes</code></a>, and
178 <a class="link" href="functions-admin.html#PG-LOGICAL-SLOT-PEEK-CHANGES">
179 <code class="function">pg_logical_slot_peek_changes</code></a>,
180 when used with logical failover slots, will block until all
181 physical slots specified in <code class="varname">synchronized_standby_slots</code> have
182 confirmed WAL receipt.
184 The standbys corresponding to the physical replication slots in
185 <code class="varname">synchronized_standby_slots</code> must configure
186 <code class="literal">sync_replication_slots = true</code> so they can receive
187 logical failover slot changes from the primary.
188 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-PRIMARY"><div class="titlepage"><div><div><h3 class="title">19.6.2. Primary Server <a href="#RUNTIME-CONFIG-REPLICATION-PRIMARY" class="id_link">#</a></h3></div></div></div><p>
189 These parameters can be set on the primary server that is
190 to send replication data to one or more standby servers.
191 Note that in addition to these parameters,
192 <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> must be set appropriately on the primary
193 server, and optionally WAL archiving can be enabled as
194 well (see <a class="xref" href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING" title="19.5.3. Archiving">Section 19.5.3</a>).
195 The values of these parameters on standby servers are irrelevant,
196 although you may wish to set them there in preparation for the
197 possibility of a standby becoming the primary.
198 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-SYNCHRONOUS-STANDBY-NAMES"><span class="term"><code class="varname">synchronous_standby_names</code> (<code class="type">string</code>)
199 <a id="id-1.6.6.9.6.3.1.1.3" class="indexterm"></a>
200 </span> <a href="#GUC-SYNCHRONOUS-STANDBY-NAMES" class="id_link">#</a></dt><dd><p>
201 Specifies a list of standby servers that can support
202 <em class="firstterm">synchronous replication</em>, as described in
203 <a class="xref" href="warm-standby.html#SYNCHRONOUS-REPLICATION" title="26.2.8. Synchronous Replication">Section 26.2.8</a>.
204 There will be one or more active synchronous standbys;
205 transactions waiting for commit will be allowed to proceed after
206 these standby servers confirm receipt of their data.
207 The synchronous standbys will be those whose names appear
209 that are both currently connected and streaming data in real-time
210 (as shown by a state of <code class="literal">streaming</code> in the
211 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW" title="27.2.4. pg_stat_replication">
212 <code class="structname">pg_stat_replication</code></a> view).
213 Specifying more than one synchronous standby can allow for very high
214 availability and protection against data loss.
216 The name of a standby server for this purpose is the
217 <code class="varname">application_name</code> setting of the standby, as set in the
218 standby's connection information. In case of a physical replication
219 standby, this should be set in the <code class="varname">primary_conninfo</code>
220 setting; the default is the setting of <a class="xref" href="runtime-config-logging.html#GUC-CLUSTER-NAME">cluster_name</a>
221 if set, else <code class="literal">walreceiver</code>.
222 For logical replication, this can be set in the connection
223 information of the subscription, and it defaults to the
224 subscription name. For other replication stream consumers,
225 consult their documentation.
227 This parameter specifies a list of standby servers using
228 either of the following syntaxes:
229 </p><pre class="synopsis">
230 [FIRST] <em class="replaceable"><code>num_sync</code></em> ( <em class="replaceable"><code>standby_name</code></em> [, ...] )
231 ANY <em class="replaceable"><code>num_sync</code></em> ( <em class="replaceable"><code>standby_name</code></em> [, ...] )
232 <em class="replaceable"><code>standby_name</code></em> [, ...]
234 where <em class="replaceable"><code>num_sync</code></em> is
235 the number of synchronous standbys that transactions need to
236 wait for replies from,
237 and <em class="replaceable"><code>standby_name</code></em>
238 is the name of a standby server.
239 <em class="replaceable"><code>num_sync</code></em>
240 must be an integer value greater than zero.
241 <code class="literal">FIRST</code> and <code class="literal">ANY</code> specify the method to choose
242 synchronous standbys from the listed servers.
244 The keyword <code class="literal">FIRST</code>, coupled with
245 <em class="replaceable"><code>num_sync</code></em>, specifies a
246 priority-based synchronous replication and makes transaction commits
247 wait until their WAL records are replicated to
248 <em class="replaceable"><code>num_sync</code></em> synchronous
249 standbys chosen based on their priorities. For example, a setting of
250 <code class="literal">FIRST 3 (s1, s2, s3, s4)</code> will cause each commit to wait for
251 replies from three higher-priority standbys chosen from standby servers
252 <code class="literal">s1</code>, <code class="literal">s2</code>, <code class="literal">s3</code> and <code class="literal">s4</code>.
253 The standbys whose names appear earlier in the list are given higher
254 priority and will be considered as synchronous. Other standby servers
255 appearing later in this list represent potential synchronous standbys.
256 If any of the current synchronous standbys disconnects for whatever
257 reason, it will be replaced immediately with the next-highest-priority
258 standby. The keyword <code class="literal">FIRST</code> is optional.
260 The keyword <code class="literal">ANY</code>, coupled with
261 <em class="replaceable"><code>num_sync</code></em>, specifies a
262 quorum-based synchronous replication and makes transaction commits
263 wait until their WAL records are replicated to <span class="emphasis"><em>at least</em></span>
264 <em class="replaceable"><code>num_sync</code></em> listed standbys.
265 For example, a setting of <code class="literal">ANY 3 (s1, s2, s3, s4)</code> will cause
266 each commit to proceed as soon as at least any three standbys of
267 <code class="literal">s1</code>, <code class="literal">s2</code>, <code class="literal">s3</code> and <code class="literal">s4</code>
270 <code class="literal">FIRST</code> and <code class="literal">ANY</code> are case-insensitive. If these
271 keywords are used as the name of a standby server,
272 its <em class="replaceable"><code>standby_name</code></em> must
275 The third syntax was used before <span class="productname">PostgreSQL</span>
276 version 9.6 and is still supported. It's the same as the first syntax
277 with <code class="literal">FIRST</code> and
278 <em class="replaceable"><code>num_sync</code></em> equal to 1.
279 For example, <code class="literal">FIRST 1 (s1, s2)</code> and <code class="literal">s1, s2</code> have
280 the same meaning: either <code class="literal">s1</code> or <code class="literal">s2</code> is chosen
281 as a synchronous standby.
283 The special entry <code class="literal">*</code> matches any standby name.
285 There is no mechanism to enforce uniqueness of standby names. In case
286 of duplicates one of the matching standbys will be considered as
287 higher priority, though exactly which one is indeterminate.
288 </p><div class="note"><h3 class="title">Note</h3><p>
289 Each <em class="replaceable"><code>standby_name</code></em>
290 should have the form of a valid SQL identifier, unless it
291 is <code class="literal">*</code>. You can use double-quoting if necessary. But note
292 that <em class="replaceable"><code>standby_name</code></em>s are
293 compared to standby application names case-insensitively, whether
294 double-quoted or not.
296 If no synchronous standby names are specified here, then synchronous
297 replication is not enabled and transaction commits will not wait for
298 replication. This is the default configuration. Even when
299 synchronous replication is enabled, individual transactions can be
300 configured not to wait for replication by setting the
301 <a class="xref" href="runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT">synchronous_commit</a> parameter to
302 <code class="literal">local</code> or <code class="literal">off</code>.
304 This parameter can only be set in the <code class="filename">postgresql.conf</code>
305 file or on the server command line.
306 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-STANDBY"><div class="titlepage"><div><div><h3 class="title">19.6.3. Standby Servers <a href="#RUNTIME-CONFIG-REPLICATION-STANDBY" class="id_link">#</a></h3></div></div></div><p>
307 These settings control the behavior of a
308 <a class="link" href="warm-standby.html#STANDBY-SERVER-OPERATION" title="26.2.2. Standby Server Operation">standby server</a>
310 to receive replication data. Their values on the primary server
312 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-PRIMARY-CONNINFO"><span class="term"><code class="varname">primary_conninfo</code> (<code class="type">string</code>)
313 <a id="id-1.6.6.9.7.3.1.1.3" class="indexterm"></a>
314 </span> <a href="#GUC-PRIMARY-CONNINFO" class="id_link">#</a></dt><dd><p>
315 Specifies a connection string to be used for the standby server
316 to connect with a sending server. This string is in the format
317 described in <a class="xref" href="libpq-connect.html#LIBPQ-CONNSTRING" title="32.1.1. Connection Strings">Section 32.1.1</a>. If any option is
318 unspecified in this string, then the corresponding environment
319 variable (see <a class="xref" href="libpq-envars.html" title="32.15. Environment Variables">Section 32.15</a>) is checked. If the
320 environment variable is not set either, then
323 The connection string should specify the host name (or address)
324 of the sending server, as well as the port number if it is not
325 the same as the standby server's default.
326 Also specify a user name corresponding to a suitably-privileged role
327 on the sending server (see
328 <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-AUTHENTICATION" title="26.2.5.1. Authentication">Section 26.2.5.1</a>).
329 A password needs to be provided too, if the sender demands password
330 authentication. It can be provided in the
331 <code class="varname">primary_conninfo</code> string, or in a separate
332 <code class="filename">~/.pgpass</code> file on the standby server (use
333 <code class="literal">replication</code> as the database name).
335 For replication slot synchronization (see
336 <a class="xref" href="logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS-SYNCHRONIZATION" title="47.2.3. Replication Slot Synchronization">Section 47.2.3</a>),
337 it is also necessary to specify a valid <code class="literal">dbname</code>
338 in the <code class="varname">primary_conninfo</code> string. This will only be
339 used for slot synchronization. It is ignored for streaming.
341 This parameter can only be set in the <code class="filename">postgresql.conf</code>
342 file or on the server command line.
343 If this parameter is changed while the WAL receiver process is
344 running, that process is signaled to shut down and expected to
345 restart with the new setting (except if <code class="varname">primary_conninfo</code>
347 This setting has no effect if the server is not in standby mode.
348 </p></dd><dt id="GUC-PRIMARY-SLOT-NAME"><span class="term"><code class="varname">primary_slot_name</code> (<code class="type">string</code>)
349 <a id="id-1.6.6.9.7.3.2.1.3" class="indexterm"></a>
350 </span> <a href="#GUC-PRIMARY-SLOT-NAME" class="id_link">#</a></dt><dd><p>
351 Optionally specifies an existing replication slot to be used when
352 connecting to the sending server via streaming replication to control
353 resource removal on the upstream node
354 (see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="26.2.6. Replication Slots">Section 26.2.6</a>).
355 This parameter can only be set in the <code class="filename">postgresql.conf</code>
356 file or on the server command line.
357 If this parameter is changed while the WAL receiver process is running,
358 that process is signaled to shut down and expected to restart with the
360 This setting has no effect if <code class="varname">primary_conninfo</code> is not
361 set or the server is not in standby mode.
362 </p></dd><dt id="GUC-HOT-STANDBY"><span class="term"><code class="varname">hot_standby</code> (<code class="type">boolean</code>)
363 <a id="id-1.6.6.9.7.3.3.1.3" class="indexterm"></a>
364 </span> <a href="#GUC-HOT-STANDBY" class="id_link">#</a></dt><dd><p>
365 Specifies whether or not you can connect and run queries during
366 recovery, as described in <a class="xref" href="hot-standby.html" title="26.4. Hot Standby">Section 26.4</a>.
367 The default value is <code class="literal">on</code>.
368 This parameter can only be set at server start. It only has effect
369 during archive recovery or in standby mode.
370 </p></dd><dt id="GUC-MAX-STANDBY-ARCHIVE-DELAY"><span class="term"><code class="varname">max_standby_archive_delay</code> (<code class="type">integer</code>)
371 <a id="id-1.6.6.9.7.3.4.1.3" class="indexterm"></a>
372 </span> <a href="#GUC-MAX-STANDBY-ARCHIVE-DELAY" class="id_link">#</a></dt><dd><p>
373 When hot standby is active, this parameter determines how long the
374 standby server should wait before canceling standby queries that
375 conflict with about-to-be-applied WAL entries, as described in
376 <a class="xref" href="hot-standby.html#HOT-STANDBY-CONFLICT" title="26.4.2. Handling Query Conflicts">Section 26.4.2</a>.
377 <code class="varname">max_standby_archive_delay</code> applies when WAL data is
378 being read from WAL archive (and is therefore not current).
379 If this value is specified without units, it is taken as milliseconds.
380 The default is 30 seconds.
381 A value of -1 allows the standby to wait forever for conflicting
383 This parameter can only be set in the <code class="filename">postgresql.conf</code>
384 file or on the server command line.
386 Note that <code class="varname">max_standby_archive_delay</code> is not the same as the
387 maximum length of time a query can run before cancellation; rather it
388 is the maximum total time allowed to apply any one WAL segment's data.
389 Thus, if one query has resulted in significant delay earlier in the
390 WAL segment, subsequent conflicting queries will have much less grace
392 </p></dd><dt id="GUC-MAX-STANDBY-STREAMING-DELAY"><span class="term"><code class="varname">max_standby_streaming_delay</code> (<code class="type">integer</code>)
393 <a id="id-1.6.6.9.7.3.5.1.3" class="indexterm"></a>
394 </span> <a href="#GUC-MAX-STANDBY-STREAMING-DELAY" class="id_link">#</a></dt><dd><p>
395 When hot standby is active, this parameter determines how long the
396 standby server should wait before canceling standby queries that
397 conflict with about-to-be-applied WAL entries, as described in
398 <a class="xref" href="hot-standby.html#HOT-STANDBY-CONFLICT" title="26.4.2. Handling Query Conflicts">Section 26.4.2</a>.
399 <code class="varname">max_standby_streaming_delay</code> applies when WAL data is
400 being received via streaming replication.
401 If this value is specified without units, it is taken as milliseconds.
402 The default is 30 seconds.
403 A value of -1 allows the standby to wait forever for conflicting
405 This parameter can only be set in the <code class="filename">postgresql.conf</code>
406 file or on the server command line.
408 Note that <code class="varname">max_standby_streaming_delay</code> is not the same as
409 the maximum length of time a query can run before cancellation; rather
410 it is the maximum total time allowed to apply WAL data once it has
411 been received from the primary server. Thus, if one query has
412 resulted in significant delay, subsequent conflicting queries will
413 have much less grace time until the standby server has caught up
415 </p></dd><dt id="GUC-WAL-RECEIVER-CREATE-TEMP-SLOT"><span class="term"><code class="varname">wal_receiver_create_temp_slot</code> (<code class="type">boolean</code>)
416 <a id="id-1.6.6.9.7.3.6.1.3" class="indexterm"></a>
417 </span> <a href="#GUC-WAL-RECEIVER-CREATE-TEMP-SLOT" class="id_link">#</a></dt><dd><p>
418 Specifies whether the WAL receiver process should create a temporary replication
419 slot on the remote instance when no permanent replication slot to use
420 has been configured (using <a class="xref" href="runtime-config-replication.html#GUC-PRIMARY-SLOT-NAME">primary_slot_name</a>).
421 The default is off. This parameter can only be set in the
422 <code class="filename">postgresql.conf</code> file or on the server command line.
423 If this parameter is changed while the WAL receiver process is running,
424 that process is signaled to shut down and expected to restart with
426 </p></dd><dt id="GUC-WAL-RECEIVER-STATUS-INTERVAL"><span class="term"><code class="varname">wal_receiver_status_interval</code> (<code class="type">integer</code>)
427 <a id="id-1.6.6.9.7.3.7.1.3" class="indexterm"></a>
428 </span> <a href="#GUC-WAL-RECEIVER-STATUS-INTERVAL" class="id_link">#</a></dt><dd><p>
429 Specifies the minimum frequency for the WAL receiver
430 process on the standby to send information about replication progress
431 to the primary or upstream standby, where it can be seen using the
432 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW" title="27.2.4. pg_stat_replication">
433 <code class="structname">pg_stat_replication</code></a>
434 view. The standby will report
435 the last write-ahead log location it has written, the last position it
436 has flushed to disk, and the last position it has applied.
437 This parameter's value is the maximum amount of time between reports.
438 Updates are sent each time the write or flush positions change, or as
439 often as specified by this parameter if set to a non-zero value.
440 There are additional cases where updates are sent while ignoring this
441 parameter; for example, when processing of the existing WAL completes
442 or when <code class="varname">synchronous_commit</code> is set to
443 <code class="literal">remote_apply</code>.
444 Thus, the apply position may lag slightly behind the true position.
445 If this value is specified without units, it is taken as seconds.
446 The default value is 10 seconds. This parameter can only be set in
447 the <code class="filename">postgresql.conf</code> file or on the server
449 </p></dd><dt id="GUC-HOT-STANDBY-FEEDBACK"><span class="term"><code class="varname">hot_standby_feedback</code> (<code class="type">boolean</code>)
450 <a id="id-1.6.6.9.7.3.8.1.3" class="indexterm"></a>
451 </span> <a href="#GUC-HOT-STANDBY-FEEDBACK" class="id_link">#</a></dt><dd><p>
452 Specifies whether or not a hot standby will send feedback to the primary
454 about queries currently executing on the standby. This parameter can
455 be used to eliminate query cancels caused by cleanup records, but
456 can cause database bloat on the primary for some workloads.
457 Feedback messages will not be sent more frequently than once per
458 <code class="varname">wal_receiver_status_interval</code>. The default value is
459 <code class="literal">off</code>. This parameter can only be set in the
460 <code class="filename">postgresql.conf</code> file or on the server command line.
462 If cascaded replication is in use the feedback is passed upstream
463 until it eventually reaches the primary. Standbys make no other use
464 of feedback they receive other than to pass upstream.
466 Note that if the clock on standby is moved ahead or backward, the
467 feedback message might not be sent at the required interval. In
468 extreme cases, this can lead to a prolonged risk of not removing dead
469 rows on the primary for extended periods, as the feedback mechanism is
471 </p></dd><dt id="GUC-WAL-RECEIVER-TIMEOUT"><span class="term"><code class="varname">wal_receiver_timeout</code> (<code class="type">integer</code>)
472 <a id="id-1.6.6.9.7.3.9.1.3" class="indexterm"></a>
473 </span> <a href="#GUC-WAL-RECEIVER-TIMEOUT" class="id_link">#</a></dt><dd><p>
474 Terminate replication connections that are inactive for longer
475 than this amount of time. This is useful for
476 the receiving standby server to detect a primary node crash or network
478 If this value is specified without units, it is taken as milliseconds.
479 The default value is 60 seconds.
480 A value of zero disables the timeout mechanism.
481 This parameter can only be set in
482 the <code class="filename">postgresql.conf</code> file or on the server
484 </p></dd><dt id="GUC-WAL-RETRIEVE-RETRY-INTERVAL"><span class="term"><code class="varname">wal_retrieve_retry_interval</code> (<code class="type">integer</code>)
485 <a id="id-1.6.6.9.7.3.10.1.3" class="indexterm"></a>
486 </span> <a href="#GUC-WAL-RETRIEVE-RETRY-INTERVAL" class="id_link">#</a></dt><dd><p>
487 Specifies how long the standby server should wait when WAL data is not
488 available from any sources (streaming replication,
489 local <code class="filename">pg_wal</code> or WAL archive) before trying
490 again to retrieve WAL data.
491 If this value is specified without units, it is taken as milliseconds.
492 The default value is 5 seconds.
493 This parameter can only be set in
494 the <code class="filename">postgresql.conf</code> file or on the server
497 This parameter is useful in configurations where a node in recovery
498 needs to control the amount of time to wait for new WAL data to be
499 available. For example, in archive recovery, it is possible to
500 make the recovery more responsive in the detection of a new WAL
501 file by reducing the value of this parameter. On a system with
502 low WAL activity, increasing it reduces the amount of requests necessary
503 to access WAL archives, something useful for example in cloud
504 environments where the number of times an infrastructure is accessed
505 is taken into account.
507 In logical replication, this parameter also limits how often a failing
508 replication apply worker or table synchronization worker will be
510 </p></dd><dt id="GUC-RECOVERY-MIN-APPLY-DELAY"><span class="term"><code class="varname">recovery_min_apply_delay</code> (<code class="type">integer</code>)
511 <a id="id-1.6.6.9.7.3.11.1.3" class="indexterm"></a>
512 </span> <a href="#GUC-RECOVERY-MIN-APPLY-DELAY" class="id_link">#</a></dt><dd><p>
513 By default, a standby server restores WAL records from the
514 sending server as soon as possible. It may be useful to have a time-delayed
515 copy of the data, offering opportunities to correct data loss errors.
516 This parameter allows you to delay recovery by a specified amount
517 of time. For example, if
518 you set this parameter to <code class="literal">5min</code>, the standby will
519 replay each transaction commit only when the system time on the standby
520 is at least five minutes past the commit time reported by the primary.
521 If this value is specified without units, it is taken as milliseconds.
522 The default is zero, adding no delay.
524 It is possible that the replication delay between servers exceeds the
525 value of this parameter, in which case no delay is added.
526 Note that the delay is calculated between the WAL time stamp as written
527 on primary and the current time on the standby. Delays in transfer
528 because of network lag or cascading replication configurations
529 may reduce the actual wait time significantly. If the system
530 clocks on primary and standby are not synchronized, this may lead to
531 recovery applying records earlier than expected; but that is not a
532 major issue because useful settings of this parameter are much larger
533 than typical time deviations between servers.
535 The delay occurs only on WAL records for transaction commits.
536 Other records are replayed as quickly as possible, which
537 is not a problem because MVCC visibility rules ensure their effects
538 are not visible until the corresponding commit record is applied.
540 The delay occurs once the database in recovery has reached a consistent
541 state, until the standby is promoted or triggered. After that the standby
542 will end recovery without further waiting.
544 WAL records must be kept on the standby until they are ready to be
545 applied. Therefore, longer delays will result in a greater accumulation
546 of WAL files, increasing disk space requirements for the standby's
547 <code class="filename">pg_wal</code> directory.
549 This parameter is intended for use with streaming replication deployments;
550 however, if the parameter is specified it will be honored in all cases
551 except crash recovery.
553 <code class="varname">hot_standby_feedback</code> will be delayed by use of this feature
554 which could lead to bloat on the primary; use both together with care.
556 </p><div class="warning"><h3 class="title">Warning</h3><p>
557 Synchronous replication is affected by this setting when <code class="varname">synchronous_commit</code>
558 is set to <code class="literal">remote_apply</code>; every <code class="literal">COMMIT</code>
559 will need to wait to be applied.
562 This parameter can only be set in the <code class="filename">postgresql.conf</code>
563 file or on the server command line.
564 </p></dd><dt id="GUC-SYNC-REPLICATION-SLOTS"><span class="term"><code class="varname">sync_replication_slots</code> (<code class="type">boolean</code>)
565 <a id="id-1.6.6.9.7.3.12.1.3" class="indexterm"></a>
566 </span> <a href="#GUC-SYNC-REPLICATION-SLOTS" class="id_link">#</a></dt><dd><p>
567 It enables a physical standby to synchronize logical failover slots
568 from the primary server so that logical subscribers can resume
569 replication from the new primary server after failover.
571 It is disabled by default. This parameter can only be set in the
572 <code class="filename">postgresql.conf</code> file or on the server command line.
573 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-SUBSCRIBER"><div class="titlepage"><div><div><h3 class="title">19.6.4. Subscribers <a href="#RUNTIME-CONFIG-REPLICATION-SUBSCRIBER" class="id_link">#</a></h3></div></div></div><p>
574 These settings control the behavior of a logical replication subscriber.
575 Their values on the publisher are irrelevant.
576 See <a class="xref" href="logical-replication-config.html" title="29.12. Configuration Settings">Section 29.12</a> for more details.
577 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-MAX-ACTIVE-REPLICATION-ORIGINS"><span class="term"><code class="varname">max_active_replication_origins</code> (<code class="type">integer</code>)
578 <a id="id-1.6.6.9.8.3.1.1.3" class="indexterm"></a>
579 </span> <a href="#GUC-MAX-ACTIVE-REPLICATION-ORIGINS" class="id_link">#</a></dt><dd><p>
580 Specifies how many replication origins (see
581 <a class="xref" href="replication-origins.html" title="Chapter 48. Replication Progress Tracking">Chapter 48</a>) can be tracked simultaneously,
582 effectively limiting how many logical replication subscriptions can
583 be created on the server. Setting it to a lower value than the current
584 number of tracked replication origins (reflected in
585 <a class="link" href="view-pg-replication-origin-status.html" title="53.19. pg_replication_origin_status">pg_replication_origin_status</a>)
586 will prevent the server from starting. It defaults to 10. This parameter
587 can only be set at server start.
589 <code class="literal">max_active_replication_origins</code> must be set to at least the
590 number of subscriptions that will be added to the subscriber, plus some
591 reserve for table synchronization.
592 </p></dd><dt id="GUC-MAX-LOGICAL-REPLICATION-WORKERS"><span class="term"><code class="varname">max_logical_replication_workers</code> (<code class="type">integer</code>)
593 <a id="id-1.6.6.9.8.3.2.1.3" class="indexterm"></a>
594 </span> <a href="#GUC-MAX-LOGICAL-REPLICATION-WORKERS" class="id_link">#</a></dt><dd><p>
595 Specifies maximum number of logical replication workers. This includes
596 leader apply workers, parallel apply workers, and table synchronization
599 Logical replication workers are taken from the pool defined by
600 <code class="varname">max_worker_processes</code>.
602 The default value is 4. This parameter can only be set at server
604 </p></dd><dt id="GUC-MAX-SYNC-WORKERS-PER-SUBSCRIPTION"><span class="term"><code class="varname">max_sync_workers_per_subscription</code> (<code class="type">integer</code>)
605 <a id="id-1.6.6.9.8.3.3.1.3" class="indexterm"></a>
606 </span> <a href="#GUC-MAX-SYNC-WORKERS-PER-SUBSCRIPTION" class="id_link">#</a></dt><dd><p>
607 Maximum number of synchronization workers per subscription. This
608 parameter controls the amount of parallelism of the initial data copy
609 during the subscription initialization or when new tables are added.
611 Currently, there can be only one synchronization worker per table.
613 The synchronization workers are taken from the pool defined by
614 <code class="varname">max_logical_replication_workers</code>.
616 The default value is 2. This parameter can only be set in the
617 <code class="filename">postgresql.conf</code> file or on the server command
619 </p></dd><dt id="GUC-MAX-PARALLEL-APPLY-WORKERS-PER-SUBSCRIPTION"><span class="term"><code class="varname">max_parallel_apply_workers_per_subscription</code> (<code class="type">integer</code>)
620 <a id="id-1.6.6.9.8.3.4.1.3" class="indexterm"></a>
621 </span> <a href="#GUC-MAX-PARALLEL-APPLY-WORKERS-PER-SUBSCRIPTION" class="id_link">#</a></dt><dd><p>
622 Maximum number of parallel apply workers per subscription. This
623 parameter controls the amount of parallelism for streaming of
624 in-progress transactions with subscription parameter
625 <code class="literal">streaming = parallel</code>.
627 The parallel apply workers are taken from the pool defined by
628 <code class="varname">max_logical_replication_workers</code>.
630 The default value is 2. This parameter can only be set in the
631 <code class="filename">postgresql.conf</code> file or on the server command
633 </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-wal.html" title="19.5. Write Ahead Log">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-query.html" title="19.7. Query Planning">Next</a></td></tr><tr><td width="40%" align="left" valign="top">19.5. Write Ahead Log </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.7. Query Planning</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob