1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>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 V1.79.1" /><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 xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 13.4 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></hr></div><div class="sect1" id="RUNTIME-CONFIG-REPLICATION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.6. Replication</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-MASTER">19.6.2. Master 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>
These settings control the behavior of the built-in
<em class="firstterm">streaming replication</em> feature (see
<a class="xref" href="warm-standby.html#STREAMING-REPLICATION" title="26.2.5. Streaming Replication">Section 26.2.5</a>). Servers will be either a
master or a standby server. Masters can send data, while standbys
are always receivers of replicated data. When cascading replication
(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
can also be senders, as well as receivers.
Parameters are mainly for sending and standby servers, though some
parameters have meaning only on the master server. Settings may vary
across the cluster without problems if that is required.
</p><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-SENDER"><div class="titlepage"><div><div><h3 class="title">19.6.1. Sending Servers</h3></div></div></div><p>
These parameters can be set on any server that is
to send replication data to one or more standby servers.
The master is always a sending server, so these parameters must
always be set on the master.
The role and meaning of these parameters does not change after a
standby becomes the master.
</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>)
<a id="id-1.6.6.9.3.3.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies the maximum number of concurrent connections from standby
servers or streaming base backup clients (i.e., the maximum number of
simultaneously running WAL sender processes). The default is
<code class="literal">10</code>. The value <code class="literal">0</code> means
replication is disabled. Abrupt disconnection of a streaming client might
leave an orphaned connection slot behind until a timeout is reached,
so this parameter should be set slightly higher than the maximum
number of expected clients so disconnected clients can immediately
reconnect. This parameter can only be set at server start. Also,
<code class="varname">wal_level</code> must be set to
<code class="literal">replica</code> or higher to allow connections from standby
servers.
</p><p>
When running a standby server, you must set this parameter to the
same or higher value than on the master server. Otherwise, queries
will not be allowed in the standby server.
</p></dd><dt id="GUC-MAX-REPLICATION-SLOTS"><span class="term"><code class="varname">max_replication_slots</code> (<code class="type">integer</code>)
<a id="id-1.6.6.9.3.3.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies the maximum number of replication slots
(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
can support. The default is 10. This parameter can only be set at
server start.
Setting it to a lower value than the number of currently
existing replication slots will prevent the server from starting.
Also, <code class="varname">wal_level</code> must be set
to <code class="literal">replica</code> or higher to allow replication slots to
be used.
</p><p>
On the subscriber side, specifies how many replication origins (see
<a class="xref" href="replication-origins.html" title="Chapter 49. Replication Progress Tracking">Chapter 49</a>) can be tracked simultaneously,
effectively limiting how many logical replication subscriptions can
be created on the server. Setting it a lower value than the current
number of tracked replication origins (reflected in
<a class="link" href="view-pg-replication-origin-status.html" title="51.79. pg_replication_origin_status">pg_replication_origin_status</a>,
not <a class="link" href="catalog-pg-replication-origin.html" title="51.42. pg_replication_origin">pg_replication_origin</a>)
will prevent the server from starting.
</p></dd><dt id="GUC-WAL-KEEP-SIZE"><span class="term"><code class="varname">wal_keep_size</code> (<code class="type">integer</code>)
<a id="id-1.6.6.9.3.3.3.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies the minimum size of past log file segments kept in the
<code class="filename">pg_wal</code>
directory, in case a standby server needs to fetch them for streaming
replication. If a standby
server connected to the sending server falls behind by more than
<code class="varname">wal_keep_size</code> megabytes, the sending server might
remove a WAL segment still needed by the standby, in which case the
replication connection will be terminated. Downstream connections
will also eventually fail as a result. (However, the standby
server can recover by fetching the segment from archive, if WAL
archiving is in use.)
</p><p>
This sets only the minimum size of segments retained in
<code class="filename">pg_wal</code>; the system might need to retain more segments
for WAL archival or to recover from a checkpoint. If
<code class="varname">wal_keep_size</code> is zero (the default), the system
doesn't keep any extra segments for standby purposes, so the number
of old WAL segments available to standby servers is a function of
the location of the previous checkpoint and status of WAL
archiving.
If this value is specified without units, it is taken as megabytes.
This parameter can only be set in the
<code class="filename">postgresql.conf</code> file or on the server command line.
</p></dd><dt id="GUC-MAX-SLOT-WAL-KEEP-SIZE"><span class="term"><code class="varname">max_slot_wal_keep_size</code> (<code class="type">integer</code>)
<a id="id-1.6.6.9.3.3.4.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specify the maximum size of WAL files
that <a class="link" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="26.2.6. Replication Slots">replication
slots</a> are allowed to retain in the <code class="filename">pg_wal</code>
directory at checkpoint time.
If <code class="varname">max_slot_wal_keep_size</code> is -1 (the default),
replication slots may retain an unlimited amount of WAL files. Otherwise, if
restart_lsn of a replication slot falls behind the current LSN by more
than the given size, the standby using the slot may no longer be able
to continue replication due to removal of required WAL files. You
can see the WAL availability of replication slots
in <a class="link" href="view-pg-replication-slots.html" title="51.80. pg_replication_slots">pg_replication_slots</a>.
</p></dd><dt id="GUC-WAL-SENDER-TIMEOUT"><span class="term"><code class="varname">wal_sender_timeout</code> (<code class="type">integer</code>)
<a id="id-1.6.6.9.3.3.5.1.3" class="indexterm"></a>
</span></dt><dd><p>
Terminate replication connections that are inactive for longer
than this amount of time. This is useful for
the sending server to detect a standby crash or network outage.
If this value is specified without units, it is taken as milliseconds.
The default value is 60 seconds.
A value of zero disables the timeout mechanism.
</p><p>
With a cluster distributed across multiple geographic
locations, using different values per location brings more flexibility
in the cluster management. A smaller value is useful for faster
failure detection with a standby having a low-latency network
connection, and a larger value helps in judging better the health
of a standby if located on a remote location, with a high-latency
network connection.
</p></dd><dt id="GUC-TRACK-COMMIT-TIMESTAMP"><span class="term"><code class="varname">track_commit_timestamp</code> (<code class="type">boolean</code>)
<a id="id-1.6.6.9.3.3.6.1.3" class="indexterm"></a>
</span></dt><dd><p>
Record commit time of transactions. This parameter
can only be set in <code class="filename">postgresql.conf</code> file or on the server
command line. The default value is <code class="literal">off</code>.
</p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-MASTER"><div class="titlepage"><div><div><h3 class="title">19.6.2. Master Server</h3></div></div></div><p>
These parameters can be set on the master/primary server that is
to send replication data to one or more standby servers.
Note that in addition to these parameters,
<a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> must be set appropriately on the master
server, and optionally WAL archiving can be enabled as
well (see <a class="xref" href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING" title="19.5.3. Archiving">Section 19.5.3</a>).
The values of these parameters on standby servers are irrelevant,
although you may wish to set them there in preparation for the
possibility of a standby becoming the master.
</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>)
<a id="id-1.6.6.9.4.3.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies a list of standby servers that can support
<em class="firstterm">synchronous replication</em>, as described in
<a class="xref" href="warm-standby.html#SYNCHRONOUS-REPLICATION" title="26.2.8. Synchronous Replication">Section 26.2.8</a>.
There will be one or more active synchronous standbys;
transactions waiting for commit will be allowed to proceed after
these standby servers confirm receipt of their data.
The synchronous standbys will be those whose names appear
in this list, and
that are both currently connected and streaming data in real-time
(as shown by a state of <code class="literal">streaming</code> in the
<a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW" title="27.2.4. pg_stat_replication">
<code class="structname">pg_stat_replication</code></a> view).
Specifying more than one synchronous standby can allow for very high
availability and protection against data loss.
</p><p>
The name of a standby server for this purpose is the
<code class="varname">application_name</code> setting of the standby, as set in the
standby's connection information. In case of a physical replication
standby, this should be set in the <code class="varname">primary_conninfo</code>
setting; the default is the setting of <a class="xref" href="runtime-config-logging.html#GUC-CLUSTER-NAME">cluster_name</a>
if set, else <code class="literal">walreceiver</code>.
For logical replication, this can be set in the connection
information of the subscription, and it defaults to the
subscription name. For other replication stream consumers,
consult their documentation.
</p><p>
This parameter specifies a list of standby servers using
either of the following syntaxes:
</p><pre class="synopsis">
[FIRST] <em class="replaceable"><code>num_sync</code></em> ( <em class="replaceable"><code>standby_name</code></em> [, ...] )
ANY <em class="replaceable"><code>num_sync</code></em> ( <em class="replaceable"><code>standby_name</code></em> [, ...] )
<em class="replaceable"><code>standby_name</code></em> [, ...]
</pre><p>
where <em class="replaceable"><code>num_sync</code></em> is
the number of synchronous standbys that transactions need to
wait for replies from,
and <em class="replaceable"><code>standby_name</code></em>
is the name of a standby server.
<code class="literal">FIRST</code> and <code class="literal">ANY</code> specify the method to choose
synchronous standbys from the listed servers.
</p><p>
The keyword <code class="literal">FIRST</code>, coupled with
<em class="replaceable"><code>num_sync</code></em>, specifies a
priority-based synchronous replication and makes transaction commits
wait until their WAL records are replicated to
<em class="replaceable"><code>num_sync</code></em> synchronous
standbys chosen based on their priorities. For example, a setting of
<code class="literal">FIRST 3 (s1, s2, s3, s4)</code> will cause each commit to wait for
replies from three higher-priority standbys chosen from standby servers
<code class="literal">s1</code>, <code class="literal">s2</code>, <code class="literal">s3</code> and <code class="literal">s4</code>.
The standbys whose names appear earlier in the list are given higher
priority and will be considered as synchronous. Other standby servers
appearing later in this list represent potential synchronous standbys.
If any of the current synchronous standbys disconnects for whatever
reason, it will be replaced immediately with the next-highest-priority
standby. The keyword <code class="literal">FIRST</code> is optional.
</p><p>
The keyword <code class="literal">ANY</code>, coupled with
<em class="replaceable"><code>num_sync</code></em>, specifies a
quorum-based synchronous replication and makes transaction commits
wait until their WAL records are replicated to <span class="emphasis"><em>at least</em></span>
<em class="replaceable"><code>num_sync</code></em> listed standbys.
For example, a setting of <code class="literal">ANY 3 (s1, s2, s3, s4)</code> will cause
each commit to proceed as soon as at least any three standbys of
<code class="literal">s1</code>, <code class="literal">s2</code>, <code class="literal">s3</code> and <code class="literal">s4</code>
reply.
</p><p>
<code class="literal">FIRST</code> and <code class="literal">ANY</code> are case-insensitive. If these
keywords are used as the name of a standby server,
its <em class="replaceable"><code>standby_name</code></em> must
be double-quoted.
</p><p>
The third syntax was used before <span class="productname">PostgreSQL</span>
version 9.6 and is still supported. It's the same as the first syntax
with <code class="literal">FIRST</code> and
<em class="replaceable"><code>num_sync</code></em> equal to 1.
For example, <code class="literal">FIRST 1 (s1, s2)</code> and <code class="literal">s1, s2</code> have
the same meaning: either <code class="literal">s1</code> or <code class="literal">s2</code> is chosen
as a synchronous standby.
</p><p>
The special entry <code class="literal">*</code> matches any standby name.
</p><p>
There is no mechanism to enforce uniqueness of standby names. In case
of duplicates one of the matching standbys will be considered as
higher priority, though exactly which one is indeterminate.
</p><div class="note"><h3 class="title">Note</h3><p>
Each <em class="replaceable"><code>standby_name</code></em>
should have the form of a valid SQL identifier, unless it
is <code class="literal">*</code>. You can use double-quoting if necessary. But note
that <em class="replaceable"><code>standby_name</code></em>s are
compared to standby application names case-insensitively, whether
double-quoted or not.
</p></div><p>
If no synchronous standby names are specified here, then synchronous
replication is not enabled and transaction commits will not wait for
replication. This is the default configuration. Even when
synchronous replication is enabled, individual transactions can be
configured not to wait for replication by setting the
<a class="xref" href="runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT">synchronous_commit</a> parameter to
<code class="literal">local</code> or <code class="literal">off</code>.
</p><p>
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-VACUUM-DEFER-CLEANUP-AGE"><span class="term"><code class="varname">vacuum_defer_cleanup_age</code> (<code class="type">integer</code>)
<a id="id-1.6.6.9.4.3.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies the number of transactions by which <code class="command">VACUUM</code> and
<acronym class="acronym">HOT</acronym> updates will defer cleanup of dead row versions. The
default is zero transactions, meaning that dead row versions can be
removed as soon as possible, that is, as soon as they are no longer
visible to any open transaction. You may wish to set this to a
non-zero value on a primary server that is supporting hot standby
servers, as described in <a class="xref" href="hot-standby.html" title="26.5. Hot Standby">Section 26.5</a>. This allows
more time for queries on the standby to complete without incurring
conflicts due to early cleanup of rows. However, since the value
is measured in terms of number of write transactions occurring on the
primary server, it is difficult to predict just how much additional
grace time will be made available to standby queries.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p><p>
You should also consider setting <code class="varname">hot_standby_feedback</code>
on standby server(s) as an alternative to using this parameter.
</p><p>
This does not prevent cleanup of dead rows which have reached the age
specified by <code class="varname">old_snapshot_threshold</code>.
</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</h3></div></div></div><p>
These settings control the behavior of a
<a class="link" href="warm-standby.html#STANDBY-SERVER-OPERATION" title="26.2.2. Standby Server Operation">standby server</a>
that is to receive replication data. Their values on the master server
are irrelevant.
</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>)
<a id="id-1.6.6.9.5.3.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies a connection string to be used for the standby server
to connect with a sending server. This string is in the format
described in <a class="xref" href="libpq-connect.html#LIBPQ-CONNSTRING" title="33.1.1. Connection Strings">Section 33.1.1</a>. If any option is
unspecified in this string, then the corresponding environment
variable (see <a class="xref" href="libpq-envars.html" title="33.14. Environment Variables">Section 33.14</a>) is checked. If the
environment variable is not set either, then
defaults are used.
</p><p>
The connection string should specify the host name (or address)
of the sending server, as well as the port number if it is not
the same as the standby server's default.
Also specify a user name corresponding to a suitably-privileged role
on the sending server (see
<a class="xref" href="warm-standby.html#STREAMING-REPLICATION-AUTHENTICATION" title="26.2.5.1. Authentication">Section 26.2.5.1</a>).
A password needs to be provided too, if the sender demands password
authentication. It can be provided in the
<code class="varname">primary_conninfo</code> string, or in a separate
<code class="filename">~/.pgpass</code> file on the standby server (use
<code class="literal">replication</code> as the database name).
Do not specify a database name in the
<code class="varname">primary_conninfo</code> string.
</p><p>
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
If this parameter is changed while the WAL receiver process is
running, that process is signaled to shut down and expected to
restart with the new setting (except if <code class="varname">primary_conninfo</code>
is an empty string).
This setting has no effect if the server is not in standby mode.
</p></dd><dt id="GUC-PRIMARY-SLOT-NAME"><span class="term"><code class="varname">primary_slot_name</code> (<code class="type">string</code>)
<a id="id-1.6.6.9.5.3.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
Optionally specifies an existing replication slot to be used when
connecting to the sending server via streaming replication to control
resource removal on the upstream node
(see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="26.2.6. Replication Slots">Section 26.2.6</a>).
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
If this parameter is changed while the WAL receiver process is running,
that process is signaled to shut down and expected to restart with the
new setting.
This setting has no effect if <code class="varname">primary_conninfo</code> is not
set or the server is not in standby mode.
</p></dd><dt id="GUC-PROMOTE-TRIGGER-FILE"><span class="term"><code class="varname">promote_trigger_file</code> (<code class="type">string</code>)
<a id="id-1.6.6.9.5.3.3.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies a trigger file whose presence ends recovery in the
standby. Even if this value is not set, you can still promote
the standby using <code class="command">pg_ctl promote</code> or calling
<code class="function">pg_promote()</code>.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-HOT-STANDBY"><span class="term"><code class="varname">hot_standby</code> (<code class="type">boolean</code>)
<a id="id-1.6.6.9.5.3.4.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies whether or not you can connect and run queries during
recovery, as described in <a class="xref" href="hot-standby.html" title="26.5. Hot Standby">Section 26.5</a>.
The default value is <code class="literal">on</code>.
This parameter can only be set at server start. It only has effect
during archive recovery or in standby mode.
</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>)
<a id="id-1.6.6.9.5.3.5.1.3" class="indexterm"></a>
</span></dt><dd><p>
When Hot Standby is active, this parameter determines how long the
standby server should wait before canceling standby queries that
conflict with about-to-be-applied WAL entries, as described in
<a class="xref" href="hot-standby.html#HOT-STANDBY-CONFLICT" title="26.5.2. Handling Query Conflicts">Section 26.5.2</a>.
<code class="varname">max_standby_archive_delay</code> applies when WAL data is
being read from WAL archive (and is therefore not current).
If this value is specified without units, it is taken as milliseconds.
The default is 30 seconds.
A value of -1 allows the standby to wait forever for conflicting
queries to complete.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p><p>
Note that <code class="varname">max_standby_archive_delay</code> is not the same as the
maximum length of time a query can run before cancellation; rather it
is the maximum total time allowed to apply any one WAL segment's data.
Thus, if one query has resulted in significant delay earlier in the
WAL segment, subsequent conflicting queries will have much less grace
time.
</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>)
<a id="id-1.6.6.9.5.3.6.1.3" class="indexterm"></a>
</span></dt><dd><p>
When Hot Standby is active, this parameter determines how long the
standby server should wait before canceling standby queries that
conflict with about-to-be-applied WAL entries, as described in
<a class="xref" href="hot-standby.html#HOT-STANDBY-CONFLICT" title="26.5.2. Handling Query Conflicts">Section 26.5.2</a>.
<code class="varname">max_standby_streaming_delay</code> applies when WAL data is
being received via streaming replication.
If this value is specified without units, it is taken as milliseconds.
The default is 30 seconds.
A value of -1 allows the standby to wait forever for conflicting
queries to complete.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p><p>
Note that <code class="varname">max_standby_streaming_delay</code> is not the same as
the maximum length of time a query can run before cancellation; rather
it is the maximum total time allowed to apply WAL data once it has
been received from the primary server. Thus, if one query has
resulted in significant delay, subsequent conflicting queries will
have much less grace time until the standby server has caught up
again.
</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>)
<a id="id-1.6.6.9.5.3.7.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies whether the WAL receiver process should create a temporary replication
slot on the remote instance when no permanent replication slot to use
has been configured (using <a class="xref" href="runtime-config-replication.html#GUC-PRIMARY-SLOT-NAME">primary_slot_name</a>).
The default is off. This parameter can only be set in the
<code class="filename">postgresql.conf</code> file or on the server command line.
If this parameter is changed while the WAL receiver process is running,
that process is signaled to shut down and expected to restart with
the new setting.
</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>)
<a id="id-1.6.6.9.5.3.8.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies the minimum frequency for the WAL receiver
process on the standby to send information about replication progress
to the primary or upstream standby, where it can be seen using the
<a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW" title="27.2.4. pg_stat_replication">
<code class="structname">pg_stat_replication</code></a>
view. The standby will report
the last write-ahead log location it has written, the last position it
has flushed to disk, and the last position it has applied.
This parameter's
value is the maximum amount of time between reports. Updates are
sent each time the write or flush positions change, or at least as
often as specified by this parameter. Thus, the apply position may
lag slightly behind the true position.
If this value is specified without units, it is taken as seconds.
The default value is 10 seconds.
Setting this parameter to zero disables status updates completely.
This parameter can only be set in
the <code class="filename">postgresql.conf</code> file or on the server
command line.
</p></dd><dt id="GUC-HOT-STANDBY-FEEDBACK"><span class="term"><code class="varname">hot_standby_feedback</code> (<code class="type">boolean</code>)
<a id="id-1.6.6.9.5.3.9.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies whether or not a hot standby will send feedback to the primary
or upstream standby
about queries currently executing on the standby. This parameter can
be used to eliminate query cancels caused by cleanup records, but
can cause database bloat on the primary for some workloads.
Feedback messages will not be sent more frequently than once per
<code class="varname">wal_receiver_status_interval</code>. The default value is
<code class="literal">off</code>. This parameter can only be set in the
<code class="filename">postgresql.conf</code> file or on the server command line.
</p><p>
If cascaded replication is in use the feedback is passed upstream
until it eventually reaches the primary. Standbys make no other use
of feedback they receive other than to pass upstream.
</p><p>
This setting does not override the behavior of
<code class="varname">old_snapshot_threshold</code> on the primary; a snapshot on the
standby which exceeds the primary's age threshold can become invalid,
resulting in cancellation of transactions on the standby. This is
because <code class="varname">old_snapshot_threshold</code> is intended to provide an
absolute limit on the time which dead rows can contribute to bloat,
which would otherwise be violated because of the configuration of a
standby.
</p></dd><dt id="GUC-WAL-RECEIVER-TIMEOUT"><span class="term"><code class="varname">wal_receiver_timeout</code> (<code class="type">integer</code>)
<a id="id-1.6.6.9.5.3.10.1.3" class="indexterm"></a>
</span></dt><dd><p>
Terminate replication connections that are inactive for longer
than this amount of time. This is useful for
the receiving standby server to detect a primary node crash or network
outage.
If this value is specified without units, it is taken as milliseconds.
The default value is 60 seconds.
A value of zero disables the timeout mechanism.
This parameter can only be set in
the <code class="filename">postgresql.conf</code> file or on the server
command line.
</p></dd><dt id="GUC-WAL-RETRIEVE-RETRY-INTERVAL"><span class="term"><code class="varname">wal_retrieve_retry_interval</code> (<code class="type">integer</code>)
<a id="id-1.6.6.9.5.3.11.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies how long the standby server should wait when WAL data is not
available from any sources (streaming replication,
local <code class="filename">pg_wal</code> or WAL archive) before trying
again to retrieve WAL data.
If this value is specified without units, it is taken as milliseconds.
The default value is 5 seconds.
This parameter can only be set in
the <code class="filename">postgresql.conf</code> file or on the server
command line.
</p><p>
This parameter is useful in configurations where a node in recovery
needs to control the amount of time to wait for new WAL data to be
available. For example, in archive recovery, it is possible to
make the recovery more responsive in the detection of a new WAL
log file by reducing the value of this parameter. On a system with
low WAL activity, increasing it reduces the amount of requests necessary
to access WAL archives, something useful for example in cloud
environments where the amount of times an infrastructure is accessed
is taken into account.
</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>)
<a id="id-1.6.6.9.5.3.12.1.3" class="indexterm"></a>
</span></dt><dd><p>
By default, a standby server restores WAL records from the
sending server as soon as possible. It may be useful to have a time-delayed
copy of the data, offering opportunities to correct data loss errors.
This parameter allows you to delay recovery by a specified amount
of time. For example, if
you set this parameter to <code class="literal">5min</code>, the standby will
replay each transaction commit only when the system time on the standby
is at least five minutes past the commit time reported by the master.
If this value is specified without units, it is taken as milliseconds.
The default is zero, adding no delay.
</p><p>
It is possible that the replication delay between servers exceeds the
value of this parameter, in which case no delay is added.
Note that the delay is calculated between the WAL time stamp as written
on master and the current time on the standby. Delays in transfer
because of network lag or cascading replication configurations
may reduce the actual wait time significantly. If the system
clocks on master and standby are not synchronized, this may lead to
recovery applying records earlier than expected; but that is not a
major issue because useful settings of this parameter are much larger
than typical time deviations between servers.
</p><p>
The delay occurs only on WAL records for transaction commits.
Other records are replayed as quickly as possible, which
is not a problem because MVCC visibility rules ensure their effects
are not visible until the corresponding commit record is applied.
</p><p>
The delay occurs once the database in recovery has reached a consistent
state, until the standby is promoted or triggered. After that the standby
will end recovery without further waiting.
</p><p>
This parameter is intended for use with streaming replication deployments;
however, if the parameter is specified it will be honored in all cases
except crash recovery.
<code class="varname">hot_standby_feedback</code> will be delayed by use of this feature
which could lead to bloat on the master; use both together with care.
</p><div class="warning"><h3 class="title">Warning</h3><p>
Synchronous replication is affected by this setting when <code class="varname">synchronous_commit</code>
is set to <code class="literal">remote_apply</code>; every <code class="literal">COMMIT</code>
will need to wait to be applied.
</p></div><p>
</p><p>
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-SUBSCRIBER"><div class="titlepage"><div><div><h3 class="title">19.6.4. Subscribers</h3></div></div></div><p>
These settings control the behavior of a logical replication subscriber.
Their values on the publisher are irrelevant.
</p><p>
Note that <code class="varname">wal_receiver_timeout</code>,
<code class="varname">wal_receiver_status_interval</code> and
<code class="varname">wal_retrieve_retry_interval</code> configuration parameters
affect the logical replication workers as well.
</p><div class="variablelist"><dl class="variablelist"><dt id="GUC-MAX-LOGICAL-REPLICATION-WORKERS"><span class="term"><code class="varname">max_logical_replication_workers</code> (<code class="type">int</code>)
<a id="id-1.6.6.9.6.4.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies maximum number of logical replication workers. This includes
both apply workers and table synchronization workers.
</p><p>
Logical replication workers are taken from the pool defined by
<code class="varname">max_worker_processes</code>.
</p><p>
The default value is 4. This parameter can only be set at server
start.
</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>)
<a id="id-1.6.6.9.6.4.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
Maximum number of synchronization workers per subscription. This
parameter controls the amount of parallelism of the initial data copy
during the subscription initialization or when new tables are added.
</p><p>
Currently, there can be only one synchronization worker per table.
</p><p>
The synchronization workers are taken from the pool defined by
<code class="varname">max_logical_replication_workers</code>.
</p><p>
The default value is 2. This parameter can only be set in the
<code class="filename">postgresql.conf</code> file or on the server command
line.
</p></dd></dl></div></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></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 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 19.7. Query Planning</td></tr></table></div></body></html>
|
