codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>20.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="20.5. Write Ahead Log" /><link rel="next" href="runtime-config-query.html" title="20.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">20.6. Replication</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-wal.html" title="20.5. Write Ahead Log">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 20. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-query.html" title="20.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">20.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">20.6.1. Sending Servers</a></span></dt><dt><span class="sect2"><a href="runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-PRIMARY">20.6.2. Primary Server</a></span></dt><dt><span class="sect2"><a href="runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-STANDBY">20.6.3. Standby Servers</a></span></dt><dt><span class="sect2"><a href="runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SUBSCRIBER">20.6.4. Subscribers</a></span></dt></dl></div><p>3 These settings control the behavior of the built-in4 <em class="firstterm">streaming replication</em> feature (see5 <a class="xref" href="warm-standby.html#STREAMING-REPLICATION" title="27.2.5. Streaming Replication">Section 27.2.5</a>), and the built-in6 <em class="firstterm">logical replication</em> feature (see7 <a class="xref" href="logical-replication.html" title="Chapter 31. Logical Replication">Chapter 31</a>).8 </p><p>9 For <span class="emphasis"><em>streaming replication</em></span>, servers will be either a10 primary or a standby server. Primaries can send data, while standbys11 are always receivers of replicated data. When cascading replication12 (see <a class="xref" href="warm-standby.html#CASCADING-REPLICATION" title="27.2.7. Cascading Replication">Section 27.2.7</a>) is used, standby servers13 can also be senders, as well as receivers.14 Parameters are mainly for sending and standby servers, though some15 parameters have meaning only on the primary server. Settings may vary16 across the cluster without problems if that is required.17 </p><p>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 details24 about logical replication configuration settings refer to25 <a class="xref" href="logical-replication-config.html" title="31.10. Configuration Settings">Section 31.10</a>.26 </p><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-SENDER"><div class="titlepage"><div><div><h3 class="title">20.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 is28 to send replication data to one or more standby servers.29 The primary is always a sending server, so these parameters must30 always be set on the primary.31 The role and meaning of these parameters does not change after a32 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.7.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 standby37 servers or streaming base backup clients (i.e., the maximum number of38 simultaneously running WAL sender processes). The default is39 <code class="literal">10</code>. The value <code class="literal">0</code> means40 replication is disabled. Abrupt disconnection of a streaming client might41 leave an orphaned connection slot behind until a timeout is reached,42 so this parameter should be set slightly higher than the maximum43 number of expected clients so disconnected clients can immediately44 reconnect. This parameter can only be set at server start. Also,45 <code class="varname">wal_level</code> must be set to46 <code class="literal">replica</code> or higher to allow connections from standby47 servers.48 </p><p>49 When running a standby server, you must set this parameter to the50 same or higher value than on the primary server. Otherwise, queries51 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.7.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 slots56 (see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="27.2.6. Replication Slots">Section 27.2.6</a>) that the server57 can support. The default is 10. This parameter can only be set at58 server start.59 Setting it to a lower value than the number of currently60 existing replication slots will prevent the server from starting.61 Also, <code class="varname">wal_level</code> must be set62 to <code class="literal">replica</code> or higher to allow replication slots to63 be used.64 </p><p>65 Note that this parameter also applies on the subscriber side, but with66 a different meaning.67 </p></dd><dt id="GUC-WAL-KEEP-SIZE"><span class="term"><code class="varname">wal_keep_size</code> (<code class="type">integer</code>)68 <a id="id-1.6.7.9.5.3.3.1.3" class="indexterm"></a>69 </span> <a href="#GUC-WAL-KEEP-SIZE" class="id_link">#</a></dt><dd><p>70 Specifies the minimum size of past WAL files kept in the71 <code class="filename">pg_wal</code>72 directory, in case a standby server needs to fetch them for streaming73 replication. If a standby74 server connected to the sending server falls behind by more than75 <code class="varname">wal_keep_size</code> megabytes, the sending server might76 remove a WAL segment still needed by the standby, in which case the77 replication connection will be terminated. Downstream connections78 will also eventually fail as a result. (However, the standby79 server can recover by fetching the segment from archive, if WAL80 archiving is in use.)81 </p><p>82 This sets only the minimum size of segments retained in83 <code class="filename">pg_wal</code>; the system might need to retain more segments84 for WAL archival or to recover from a checkpoint. If85 <code class="varname">wal_keep_size</code> is zero (the default), the system86 doesn't keep any extra segments for standby purposes, so the number87 of old WAL segments available to standby servers is a function of88 the location of the previous checkpoint and status of WAL89 archiving.90 If this value is specified without units, it is taken as megabytes.91 This parameter can only be set in the92 <code class="filename">postgresql.conf</code> file or on the server command line.93 </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>)94 <a id="id-1.6.7.9.5.3.4.1.3" class="indexterm"></a>95 </span> <a href="#GUC-MAX-SLOT-WAL-KEEP-SIZE" class="id_link">#</a></dt><dd><p>96 Specify the maximum size of WAL files97 that <a class="link" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="27.2.6. Replication Slots">replication98 slots</a> are allowed to retain in the <code class="filename">pg_wal</code>99 directory at checkpoint time.100 If <code class="varname">max_slot_wal_keep_size</code> is -1 (the default),101 replication slots may retain an unlimited amount of WAL files. Otherwise, if102 restart_lsn of a replication slot falls behind the current LSN by more103 than the given size, the standby using the slot may no longer be able104 to continue replication due to removal of required WAL files. You105 can see the WAL availability of replication slots106 in <a class="link" href="view-pg-replication-slots.html" title="54.19. pg_replication_slots">pg_replication_slots</a>.107 If this value is specified without units, it is taken as megabytes.108 This parameter can only be set in the <code class="filename">postgresql.conf</code>109 file or on the server command line.110 </p></dd><dt id="GUC-WAL-SENDER-TIMEOUT"><span class="term"><code class="varname">wal_sender_timeout</code> (<code class="type">integer</code>)111 <a id="id-1.6.7.9.5.3.5.1.3" class="indexterm"></a>112 </span> <a href="#GUC-WAL-SENDER-TIMEOUT" class="id_link">#</a></dt><dd><p>113 Terminate replication connections that are inactive for longer114 than this amount of time. This is useful for115 the sending server to detect a standby crash or network outage.116 If this value is specified without units, it is taken as milliseconds.117 The default value is 60 seconds.118 A value of zero disables the timeout mechanism.119 </p><p>120 With a cluster distributed across multiple geographic121 locations, using different values per location brings more flexibility122 in the cluster management. A smaller value is useful for faster123 failure detection with a standby having a low-latency network124 connection, and a larger value helps in judging better the health125 of a standby if located on a remote location, with a high-latency126 network connection.127 </p></dd><dt id="GUC-TRACK-COMMIT-TIMESTAMP"><span class="term"><code class="varname">track_commit_timestamp</code> (<code class="type">boolean</code>)128 <a id="id-1.6.7.9.5.3.6.1.3" class="indexterm"></a>129 </span> <a href="#GUC-TRACK-COMMIT-TIMESTAMP" class="id_link">#</a></dt><dd><p>130 Record commit time of transactions. This parameter131 can only be set in <code class="filename">postgresql.conf</code> file or on the server132 command line. The default value is <code class="literal">off</code>.133 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-PRIMARY"><div class="titlepage"><div><div><h3 class="title">20.6.2. Primary Server <a href="#RUNTIME-CONFIG-REPLICATION-PRIMARY" class="id_link">#</a></h3></div></div></div><p>134 These parameters can be set on the primary server that is135 to send replication data to one or more standby servers.136 Note that in addition to these parameters,137 <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> must be set appropriately on the primary138 server, and optionally WAL archiving can be enabled as139 well (see <a class="xref" href="runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING" title="20.5.3. Archiving">Section 20.5.3</a>).140 The values of these parameters on standby servers are irrelevant,141 although you may wish to set them there in preparation for the142 possibility of a standby becoming the primary.143 </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>)144 <a id="id-1.6.7.9.6.3.1.1.3" class="indexterm"></a>145 </span> <a href="#GUC-SYNCHRONOUS-STANDBY-NAMES" class="id_link">#</a></dt><dd><p>146 Specifies a list of standby servers that can support147 <em class="firstterm">synchronous replication</em>, as described in148 <a class="xref" href="warm-standby.html#SYNCHRONOUS-REPLICATION" title="27.2.8. Synchronous Replication">Section 27.2.8</a>.149 There will be one or more active synchronous standbys;150 transactions waiting for commit will be allowed to proceed after151 these standby servers confirm receipt of their data.152 The synchronous standbys will be those whose names appear153 in this list, and154 that are both currently connected and streaming data in real-time155 (as shown by a state of <code class="literal">streaming</code> in the156 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW" title="28.2.4. pg_stat_replication">157 <code class="structname">pg_stat_replication</code></a> view).158 Specifying more than one synchronous standby can allow for very high159 availability and protection against data loss.160 </p><p>161 The name of a standby server for this purpose is the162 <code class="varname">application_name</code> setting of the standby, as set in the163 standby's connection information. In case of a physical replication164 standby, this should be set in the <code class="varname">primary_conninfo</code>165 setting; the default is the setting of <a class="xref" href="runtime-config-logging.html#GUC-CLUSTER-NAME">cluster_name</a>166 if set, else <code class="literal">walreceiver</code>.167 For logical replication, this can be set in the connection168 information of the subscription, and it defaults to the169 subscription name. For other replication stream consumers,170 consult their documentation.171 </p><p>172 This parameter specifies a list of standby servers using173 either of the following syntaxes:174</p><pre class="synopsis">175[FIRST] <em class="replaceable"><code>num_sync</code></em> ( <em class="replaceable"><code>standby_name</code></em> [, ...] )176ANY <em class="replaceable"><code>num_sync</code></em> ( <em class="replaceable"><code>standby_name</code></em> [, ...] )177<em class="replaceable"><code>standby_name</code></em> [, ...]178</pre><p>179 where <em class="replaceable"><code>num_sync</code></em> is180 the number of synchronous standbys that transactions need to181 wait for replies from,182 and <em class="replaceable"><code>standby_name</code></em>183 is the name of a standby server.184 <code class="literal">FIRST</code> and <code class="literal">ANY</code> specify the method to choose185 synchronous standbys from the listed servers.186 </p><p>187 The keyword <code class="literal">FIRST</code>, coupled with188 <em class="replaceable"><code>num_sync</code></em>, specifies a189 priority-based synchronous replication and makes transaction commits190 wait until their WAL records are replicated to191 <em class="replaceable"><code>num_sync</code></em> synchronous192 standbys chosen based on their priorities. For example, a setting of193 <code class="literal">FIRST 3 (s1, s2, s3, s4)</code> will cause each commit to wait for194 replies from three higher-priority standbys chosen from standby servers195 <code class="literal">s1</code>, <code class="literal">s2</code>, <code class="literal">s3</code> and <code class="literal">s4</code>.196 The standbys whose names appear earlier in the list are given higher197 priority and will be considered as synchronous. Other standby servers198 appearing later in this list represent potential synchronous standbys.199 If any of the current synchronous standbys disconnects for whatever200 reason, it will be replaced immediately with the next-highest-priority201 standby. The keyword <code class="literal">FIRST</code> is optional.202 </p><p>203 The keyword <code class="literal">ANY</code>, coupled with204 <em class="replaceable"><code>num_sync</code></em>, specifies a205 quorum-based synchronous replication and makes transaction commits206 wait until their WAL records are replicated to <span class="emphasis"><em>at least</em></span>207 <em class="replaceable"><code>num_sync</code></em> listed standbys.208 For example, a setting of <code class="literal">ANY 3 (s1, s2, s3, s4)</code> will cause209 each commit to proceed as soon as at least any three standbys of210 <code class="literal">s1</code>, <code class="literal">s2</code>, <code class="literal">s3</code> and <code class="literal">s4</code>211 reply.212 </p><p>213 <code class="literal">FIRST</code> and <code class="literal">ANY</code> are case-insensitive. If these214 keywords are used as the name of a standby server,215 its <em class="replaceable"><code>standby_name</code></em> must216 be double-quoted.217 </p><p>218 The third syntax was used before <span class="productname">PostgreSQL</span>219 version 9.6 and is still supported. It's the same as the first syntax220 with <code class="literal">FIRST</code> and221 <em class="replaceable"><code>num_sync</code></em> equal to 1.222 For example, <code class="literal">FIRST 1 (s1, s2)</code> and <code class="literal">s1, s2</code> have223 the same meaning: either <code class="literal">s1</code> or <code class="literal">s2</code> is chosen224 as a synchronous standby.225 </p><p>226 The special entry <code class="literal">*</code> matches any standby name.227 </p><p>228 There is no mechanism to enforce uniqueness of standby names. In case229 of duplicates one of the matching standbys will be considered as230 higher priority, though exactly which one is indeterminate.231 </p><div class="note"><h3 class="title">Note</h3><p>232 Each <em class="replaceable"><code>standby_name</code></em>233 should have the form of a valid SQL identifier, unless it234 is <code class="literal">*</code>. You can use double-quoting if necessary. But note235 that <em class="replaceable"><code>standby_name</code></em>s are236 compared to standby application names case-insensitively, whether237 double-quoted or not.238 </p></div><p>239 If no synchronous standby names are specified here, then synchronous240 replication is not enabled and transaction commits will not wait for241 replication. This is the default configuration. Even when242 synchronous replication is enabled, individual transactions can be243 configured not to wait for replication by setting the244 <a class="xref" href="runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT">synchronous_commit</a> parameter to245 <code class="literal">local</code> or <code class="literal">off</code>.246 </p><p>247 This parameter can only be set in the <code class="filename">postgresql.conf</code>248 file or on the server command line.249 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-STANDBY"><div class="titlepage"><div><div><h3 class="title">20.6.3. Standby Servers <a href="#RUNTIME-CONFIG-REPLICATION-STANDBY" class="id_link">#</a></h3></div></div></div><p>250 These settings control the behavior of a251 <a class="link" href="warm-standby.html#STANDBY-SERVER-OPERATION" title="27.2.2. Standby Server Operation">standby server</a>252 that is253 to receive replication data. Their values on the primary server254 are irrelevant.255 </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>)256 <a id="id-1.6.7.9.7.3.1.1.3" class="indexterm"></a>257 </span> <a href="#GUC-PRIMARY-CONNINFO" class="id_link">#</a></dt><dd><p>258 Specifies a connection string to be used for the standby server259 to connect with a sending server. This string is in the format260 described in <a class="xref" href="libpq-connect.html#LIBPQ-CONNSTRING" title="34.1.1. Connection Strings">Section 34.1.1</a>. If any option is261 unspecified in this string, then the corresponding environment262 variable (see <a class="xref" href="libpq-envars.html" title="34.15. Environment Variables">Section 34.15</a>) is checked. If the263 environment variable is not set either, then264 defaults are used.265 </p><p>266 The connection string should specify the host name (or address)267 of the sending server, as well as the port number if it is not268 the same as the standby server's default.269 Also specify a user name corresponding to a suitably-privileged role270 on the sending server (see271 <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-AUTHENTICATION" title="27.2.5.1. Authentication">Section 27.2.5.1</a>).272 A password needs to be provided too, if the sender demands password273 authentication. It can be provided in the274 <code class="varname">primary_conninfo</code> string, or in a separate275 <code class="filename">~/.pgpass</code> file on the standby server (use276 <code class="literal">replication</code> as the database name).277 Do not specify a database name in the278 <code class="varname">primary_conninfo</code> string.279 </p><p>280 This parameter can only be set in the <code class="filename">postgresql.conf</code>281 file or on the server command line.282 If this parameter is changed while the WAL receiver process is283 running, that process is signaled to shut down and expected to284 restart with the new setting (except if <code class="varname">primary_conninfo</code>285 is an empty string).286 This setting has no effect if the server is not in standby mode.287 </p></dd><dt id="GUC-PRIMARY-SLOT-NAME"><span class="term"><code class="varname">primary_slot_name</code> (<code class="type">string</code>)288 <a id="id-1.6.7.9.7.3.2.1.3" class="indexterm"></a>289 </span> <a href="#GUC-PRIMARY-SLOT-NAME" class="id_link">#</a></dt><dd><p>290 Optionally specifies an existing replication slot to be used when291 connecting to the sending server via streaming replication to control292 resource removal on the upstream node293 (see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="27.2.6. Replication Slots">Section 27.2.6</a>).294 This parameter can only be set in the <code class="filename">postgresql.conf</code>295 file or on the server command line.296 If this parameter is changed while the WAL receiver process is running,297 that process is signaled to shut down and expected to restart with the298 new setting.299 This setting has no effect if <code class="varname">primary_conninfo</code> is not300 set or the server is not in standby mode.301 </p></dd><dt id="GUC-HOT-STANDBY"><span class="term"><code class="varname">hot_standby</code> (<code class="type">boolean</code>)302 <a id="id-1.6.7.9.7.3.3.1.3" class="indexterm"></a>303 </span> <a href="#GUC-HOT-STANDBY" class="id_link">#</a></dt><dd><p>304 Specifies whether or not you can connect and run queries during305 recovery, as described in <a class="xref" href="hot-standby.html" title="27.4. Hot Standby">Section 27.4</a>.306 The default value is <code class="literal">on</code>.307 This parameter can only be set at server start. It only has effect308 during archive recovery or in standby mode.309 </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>)310 <a id="id-1.6.7.9.7.3.4.1.3" class="indexterm"></a>311 </span> <a href="#GUC-MAX-STANDBY-ARCHIVE-DELAY" class="id_link">#</a></dt><dd><p>312 When hot standby is active, this parameter determines how long the313 standby server should wait before canceling standby queries that314 conflict with about-to-be-applied WAL entries, as described in315 <a class="xref" href="hot-standby.html#HOT-STANDBY-CONFLICT" title="27.4.2. Handling Query Conflicts">Section 27.4.2</a>.316 <code class="varname">max_standby_archive_delay</code> applies when WAL data is317 being read from WAL archive (and is therefore not current).318 If this value is specified without units, it is taken as milliseconds.319 The default is 30 seconds.320 A value of -1 allows the standby to wait forever for conflicting321 queries to complete.322 This parameter can only be set in the <code class="filename">postgresql.conf</code>323 file or on the server command line.324 </p><p>325 Note that <code class="varname">max_standby_archive_delay</code> is not the same as the326 maximum length of time a query can run before cancellation; rather it327 is the maximum total time allowed to apply any one WAL segment's data.328 Thus, if one query has resulted in significant delay earlier in the329 WAL segment, subsequent conflicting queries will have much less grace330 time.331 </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>)332 <a id="id-1.6.7.9.7.3.5.1.3" class="indexterm"></a>333 </span> <a href="#GUC-MAX-STANDBY-STREAMING-DELAY" class="id_link">#</a></dt><dd><p>334 When hot standby is active, this parameter determines how long the335 standby server should wait before canceling standby queries that336 conflict with about-to-be-applied WAL entries, as described in337 <a class="xref" href="hot-standby.html#HOT-STANDBY-CONFLICT" title="27.4.2. Handling Query Conflicts">Section 27.4.2</a>.338 <code class="varname">max_standby_streaming_delay</code> applies when WAL data is339 being received via streaming replication.340 If this value is specified without units, it is taken as milliseconds.341 The default is 30 seconds.342 A value of -1 allows the standby to wait forever for conflicting343 queries to complete.344 This parameter can only be set in the <code class="filename">postgresql.conf</code>345 file or on the server command line.346 </p><p>347 Note that <code class="varname">max_standby_streaming_delay</code> is not the same as348 the maximum length of time a query can run before cancellation; rather349 it is the maximum total time allowed to apply WAL data once it has350 been received from the primary server. Thus, if one query has351 resulted in significant delay, subsequent conflicting queries will352 have much less grace time until the standby server has caught up353 again.354 </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>)355 <a id="id-1.6.7.9.7.3.6.1.3" class="indexterm"></a>356 </span> <a href="#GUC-WAL-RECEIVER-CREATE-TEMP-SLOT" class="id_link">#</a></dt><dd><p>357 Specifies whether the WAL receiver process should create a temporary replication358 slot on the remote instance when no permanent replication slot to use359 has been configured (using <a class="xref" href="runtime-config-replication.html#GUC-PRIMARY-SLOT-NAME">primary_slot_name</a>).360 The default is off. This parameter can only be set in the361 <code class="filename">postgresql.conf</code> file or on the server command line.362 If this parameter is changed while the WAL receiver process is running,363 that process is signaled to shut down and expected to restart with364 the new setting.365 </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>)366 <a id="id-1.6.7.9.7.3.7.1.3" class="indexterm"></a>367 </span> <a href="#GUC-WAL-RECEIVER-STATUS-INTERVAL" class="id_link">#</a></dt><dd><p>368 Specifies the minimum frequency for the WAL receiver369 process on the standby to send information about replication progress370 to the primary or upstream standby, where it can be seen using the371 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW" title="28.2.4. pg_stat_replication">372 <code class="structname">pg_stat_replication</code></a>373 view. The standby will report374 the last write-ahead log location it has written, the last position it375 has flushed to disk, and the last position it has applied.376 This parameter's value is the maximum amount of time between reports.377 Updates are sent each time the write or flush positions change, or as378 often as specified by this parameter if set to a non-zero value.379 There are additional cases where updates are sent while ignoring this380 parameter; for example, when processing of the existing WAL completes381 or when <code class="varname">synchronous_commit</code> is set to382 <code class="literal">remote_apply</code>.383 Thus, the apply position may lag slightly behind the true position.384 If this value is specified without units, it is taken as seconds.385 The default value is 10 seconds. This parameter can only be set in386 the <code class="filename">postgresql.conf</code> file or on the server387 command line.388 </p></dd><dt id="GUC-HOT-STANDBY-FEEDBACK"><span class="term"><code class="varname">hot_standby_feedback</code> (<code class="type">boolean</code>)389 <a id="id-1.6.7.9.7.3.8.1.3" class="indexterm"></a>390 </span> <a href="#GUC-HOT-STANDBY-FEEDBACK" class="id_link">#</a></dt><dd><p>391 Specifies whether or not a hot standby will send feedback to the primary392 or upstream standby393 about queries currently executing on the standby. This parameter can394 be used to eliminate query cancels caused by cleanup records, but395 can cause database bloat on the primary for some workloads.396 Feedback messages will not be sent more frequently than once per397 <code class="varname">wal_receiver_status_interval</code>. The default value is398 <code class="literal">off</code>. This parameter can only be set in the399 <code class="filename">postgresql.conf</code> file or on the server command line.400 </p><p>401 If cascaded replication is in use the feedback is passed upstream402 until it eventually reaches the primary. Standbys make no other use403 of feedback they receive other than to pass upstream.404 </p><p>405 This setting does not override the behavior of406 <code class="varname">old_snapshot_threshold</code> on the primary; a snapshot on the407 standby which exceeds the primary's age threshold can become invalid,408 resulting in cancellation of transactions on the standby. This is409 because <code class="varname">old_snapshot_threshold</code> is intended to provide an410 absolute limit on the time which dead rows can contribute to bloat,411 which would otherwise be violated because of the configuration of a412 standby.413 </p></dd><dt id="GUC-WAL-RECEIVER-TIMEOUT"><span class="term"><code class="varname">wal_receiver_timeout</code> (<code class="type">integer</code>)414 <a id="id-1.6.7.9.7.3.9.1.3" class="indexterm"></a>415 </span> <a href="#GUC-WAL-RECEIVER-TIMEOUT" class="id_link">#</a></dt><dd><p>416 Terminate replication connections that are inactive for longer417 than this amount of time. This is useful for418 the receiving standby server to detect a primary node crash or network419 outage.420 If this value is specified without units, it is taken as milliseconds.421 The default value is 60 seconds.422 A value of zero disables the timeout mechanism.423 This parameter can only be set in424 the <code class="filename">postgresql.conf</code> file or on the server425 command line.426 </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>)427 <a id="id-1.6.7.9.7.3.10.1.3" class="indexterm"></a>428 </span> <a href="#GUC-WAL-RETRIEVE-RETRY-INTERVAL" class="id_link">#</a></dt><dd><p>429 Specifies how long the standby server should wait when WAL data is not430 available from any sources (streaming replication,431 local <code class="filename">pg_wal</code> or WAL archive) before trying432 again to retrieve WAL data.433 If this value is specified without units, it is taken as milliseconds.434 The default value is 5 seconds.435 This parameter can only be set in436 the <code class="filename">postgresql.conf</code> file or on the server437 command line.438 </p><p>439 This parameter is useful in configurations where a node in recovery440 needs to control the amount of time to wait for new WAL data to be441 available. For example, in archive recovery, it is possible to442 make the recovery more responsive in the detection of a new WAL443 file by reducing the value of this parameter. On a system with444 low WAL activity, increasing it reduces the amount of requests necessary445 to access WAL archives, something useful for example in cloud446 environments where the number of times an infrastructure is accessed447 is taken into account.448 </p><p>449 In logical replication, this parameter also limits how often a failing450 replication apply worker will be respawned.451 </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>)452 <a id="id-1.6.7.9.7.3.11.1.3" class="indexterm"></a>453 </span> <a href="#GUC-RECOVERY-MIN-APPLY-DELAY" class="id_link">#</a></dt><dd><p>454 By default, a standby server restores WAL records from the455 sending server as soon as possible. It may be useful to have a time-delayed456 copy of the data, offering opportunities to correct data loss errors.457 This parameter allows you to delay recovery by a specified amount458 of time. For example, if459 you set this parameter to <code class="literal">5min</code>, the standby will460 replay each transaction commit only when the system time on the standby461 is at least five minutes past the commit time reported by the primary.462 If this value is specified without units, it is taken as milliseconds.463 The default is zero, adding no delay.464 </p><p>465 It is possible that the replication delay between servers exceeds the466 value of this parameter, in which case no delay is added.467 Note that the delay is calculated between the WAL time stamp as written468 on primary and the current time on the standby. Delays in transfer469 because of network lag or cascading replication configurations470 may reduce the actual wait time significantly. If the system471 clocks on primary and standby are not synchronized, this may lead to472 recovery applying records earlier than expected; but that is not a473 major issue because useful settings of this parameter are much larger474 than typical time deviations between servers.475 </p><p>476 The delay occurs only on WAL records for transaction commits.477 Other records are replayed as quickly as possible, which478 is not a problem because MVCC visibility rules ensure their effects479 are not visible until the corresponding commit record is applied.480 </p><p>481 The delay occurs once the database in recovery has reached a consistent482 state, until the standby is promoted or triggered. After that the standby483 will end recovery without further waiting.484 </p><p>485 WAL records must be kept on the standby until they are ready to be486 applied. Therefore, longer delays will result in a greater accumulation487 of WAL files, increasing disk space requirements for the standby's488 <code class="filename">pg_wal</code> directory.489 </p><p>490 This parameter is intended for use with streaming replication deployments;491 however, if the parameter is specified it will be honored in all cases492 except crash recovery.493 494 <code class="varname">hot_standby_feedback</code> will be delayed by use of this feature495 which could lead to bloat on the primary; use both together with care.496 497 </p><div class="warning"><h3 class="title">Warning</h3><p>498 Synchronous replication is affected by this setting when <code class="varname">synchronous_commit</code>499 is set to <code class="literal">remote_apply</code>; every <code class="literal">COMMIT</code>500 will need to wait to be applied.501 </p></div><p>502 </p><p>503 This parameter can only be set in the <code class="filename">postgresql.conf</code>504 file or on the server command line.505 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-REPLICATION-SUBSCRIBER"><div class="titlepage"><div><div><h3 class="title">20.6.4. Subscribers <a href="#RUNTIME-CONFIG-REPLICATION-SUBSCRIBER" class="id_link">#</a></h3></div></div></div><p>506 These settings control the behavior of a logical replication subscriber.507 Their values on the publisher are irrelevant.508 See <a class="xref" href="logical-replication-config.html" title="31.10. Configuration Settings">Section 31.10</a> for more details.509 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-MAX-REPLICATION-SLOTS-SUBSCRIBER"><span class="term"><code class="varname">max_replication_slots</code> (<code class="type">integer</code>)510 <a id="id-1.6.7.9.8.3.1.1.3" class="indexterm"></a>511 </span> <a href="#GUC-MAX-REPLICATION-SLOTS-SUBSCRIBER" class="id_link">#</a></dt><dd><p>512 Specifies how many replication origins (see513 <a class="xref" href="replication-origins.html" title="Chapter 50. Replication Progress Tracking">Chapter 50</a>) can be tracked simultaneously,514 effectively limiting how many logical replication subscriptions can515 be created on the server. Setting it to a lower value than the current516 number of tracked replication origins (reflected in517 <a class="link" href="view-pg-replication-origin-status.html" title="54.18. pg_replication_origin_status">pg_replication_origin_status</a>)518 will prevent the server from starting.519 <code class="literal">max_replication_slots</code> must be set to at least the520 number of subscriptions that will be added to the subscriber, plus some521 reserve for table synchronization.522 </p><p>523 Note that this parameter also applies on a sending server, but with524 a different meaning.525 </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>)526 <a id="id-1.6.7.9.8.3.2.1.3" class="indexterm"></a>527 </span> <a href="#GUC-MAX-LOGICAL-REPLICATION-WORKERS" class="id_link">#</a></dt><dd><p>528 Specifies maximum number of logical replication workers. This includes529 leader apply workers, parallel apply workers, and table synchronization530 workers.531 </p><p>532 Logical replication workers are taken from the pool defined by533 <code class="varname">max_worker_processes</code>.534 </p><p>535 The default value is 4. This parameter can only be set at server536 start.537 </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>)538 <a id="id-1.6.7.9.8.3.3.1.3" class="indexterm"></a>539 </span> <a href="#GUC-MAX-SYNC-WORKERS-PER-SUBSCRIPTION" class="id_link">#</a></dt><dd><p>540 Maximum number of synchronization workers per subscription. This541 parameter controls the amount of parallelism of the initial data copy542 during the subscription initialization or when new tables are added.543 </p><p>544 Currently, there can be only one synchronization worker per table.545 </p><p>546 The synchronization workers are taken from the pool defined by547 <code class="varname">max_logical_replication_workers</code>.548 </p><p>549 The default value is 2. This parameter can only be set in the550 <code class="filename">postgresql.conf</code> file or on the server command551 line.552 </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>)553 <a id="id-1.6.7.9.8.3.4.1.3" class="indexterm"></a>554 </span> <a href="#GUC-MAX-PARALLEL-APPLY-WORKERS-PER-SUBSCRIPTION" class="id_link">#</a></dt><dd><p>555 Maximum number of parallel apply workers per subscription. This556 parameter controls the amount of parallelism for streaming of557 in-progress transactions with subscription parameter558 <code class="literal">streaming = parallel</code>.559 </p><p>560 The parallel apply workers are taken from the pool defined by561 <code class="varname">max_logical_replication_workers</code>.562 </p><p>563 The default value is 2. This parameter can only be set in the564 <code class="filename">postgresql.conf</code> file or on the server command565 line.566 </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="20.5. Write Ahead Log">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-query.html" title="20.7. Query Planning">Next</a></td></tr><tr><td width="40%" align="left" valign="top">20.5. Write Ahead Log </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.7. Query Planning</td></tr></table></div></body></html>