codekingpro/portable-devtools
114k
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>27.4. Hot Standby</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="warm-standby-failover.html" title="27.3. Failover" /><link rel="next" href="monitoring.html" title="Chapter 28. Monitoring Database Activity" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">27.4. Hot Standby</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="warm-standby-failover.html" title="27.3. Failover">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="high-availability.html" title="Chapter 27. High Availability, Load Balancing, and Replication">Up</a></td><th width="60%" align="center">Chapter 27. High Availability, Load Balancing, and Replication</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="monitoring.html" title="Chapter 28. Monitoring Database Activity">Next</a></td></tr></table><hr /></div><div class="sect1" id="HOT-STANDBY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">27.4. Hot Standby <a href="#HOT-STANDBY" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-USERS">27.4.1. User's Overview</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-CONFLICT">27.4.2. Handling Query Conflicts</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-ADMIN">27.4.3. Administrator's Overview</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-PARAMETERS">27.4.4. Hot Standby Parameter Reference</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-CAVEATS">27.4.5. Caveats</a></span></dt></dl></div><a id="id-1.6.14.18.2" class="indexterm"></a><p>3 Hot standby is the term used to describe the ability to connect to4 the server and run read-only queries while the server is in archive5 recovery or standby mode. This6 is useful both for replication purposes and for restoring a backup7 to a desired state with great precision.8 The term hot standby also refers to the ability of the server to move9 from recovery through to normal operation while users continue running10 queries and/or keep their connections open.11 </p><p>12 Running queries in hot standby mode is similar to normal query operation,13 though there are several usage and administrative differences14 explained below.15 </p><div class="sect2" id="HOT-STANDBY-USERS"><div class="titlepage"><div><div><h3 class="title">27.4.1. User's Overview <a href="#HOT-STANDBY-USERS" class="id_link">#</a></h3></div></div></div><p>16 When the <a class="xref" href="runtime-config-replication.html#GUC-HOT-STANDBY">hot_standby</a> parameter is set to true on a17 standby server, it will begin accepting connections once the recovery has18 brought the system to a consistent state. All such connections are19 strictly read-only; not even temporary tables may be written.20 </p><p>21 The data on the standby takes some time to arrive from the primary server22 so there will be a measurable delay between primary and standby. Running the23 same query nearly simultaneously on both primary and standby might therefore24 return differing results. We say that data on the standby is25 <em class="firstterm">eventually consistent</em> with the primary. Once the26 commit record for a transaction is replayed on the standby, the changes27 made by that transaction will be visible to any new snapshots taken on28 the standby. Snapshots may be taken at the start of each query or at the29 start of each transaction, depending on the current transaction isolation30 level. For more details, see <a class="xref" href="transaction-iso.html" title="13.2. Transaction Isolation">Section 13.2</a>.31 </p><p>32 Transactions started during hot standby may issue the following commands:33 34 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>35 Query access: <code class="command">SELECT</code>, <code class="command">COPY TO</code>36 </p></li><li class="listitem"><p>37 Cursor commands: <code class="command">DECLARE</code>, <code class="command">FETCH</code>, <code class="command">CLOSE</code>38 </p></li><li class="listitem"><p>39 Settings: <code class="command">SHOW</code>, <code class="command">SET</code>, <code class="command">RESET</code>40 </p></li><li class="listitem"><p>41 Transaction management commands:42 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>43 <code class="command">BEGIN</code>, <code class="command">END</code>, <code class="command">ABORT</code>, <code class="command">START TRANSACTION</code>44 </p></li><li class="listitem"><p>45 <code class="command">SAVEPOINT</code>, <code class="command">RELEASE</code>, <code class="command">ROLLBACK TO SAVEPOINT</code>46 </p></li><li class="listitem"><p>47 <code class="command">EXCEPTION</code> blocks and other internal subtransactions48 </p></li></ul></div><p>49 </p></li><li class="listitem"><p>50 <code class="command">LOCK TABLE</code>, though only when explicitly in one of these modes:51 <code class="literal">ACCESS SHARE</code>, <code class="literal">ROW SHARE</code> or <code class="literal">ROW EXCLUSIVE</code>.52 </p></li><li class="listitem"><p>53 Plans and resources: <code class="command">PREPARE</code>, <code class="command">EXECUTE</code>,54 <code class="command">DEALLOCATE</code>, <code class="command">DISCARD</code>55 </p></li><li class="listitem"><p>56 Plugins and extensions: <code class="command">LOAD</code>57 </p></li><li class="listitem"><p>58 <code class="command">UNLISTEN</code>59 </p></li></ul></div><p>60 </p><p>61 Transactions started during hot standby will never be assigned a62 transaction ID and cannot write to the system write-ahead log.63 Therefore, the following actions will produce error messages:64 65 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>66 Data Manipulation Language (DML): <code class="command">INSERT</code>,67 <code class="command">UPDATE</code>, <code class="command">DELETE</code>,68 <code class="command">MERGE</code>, <code class="command">COPY FROM</code>,69 <code class="command">TRUNCATE</code>.70 Note that there are no allowed actions that result in a trigger71 being executed during recovery. This restriction applies even to72 temporary tables, because table rows cannot be read or written without73 assigning a transaction ID, which is currently not possible in a74 hot standby environment.75 </p></li><li class="listitem"><p>76 Data Definition Language (DDL): <code class="command">CREATE</code>,77 <code class="command">DROP</code>, <code class="command">ALTER</code>, <code class="command">COMMENT</code>.78 This restriction applies even to temporary tables, because carrying79 out these operations would require updating the system catalog tables.80 </p></li><li class="listitem"><p>81 <code class="command">SELECT ... FOR SHARE | UPDATE</code>, because row locks cannot be82 taken without updating the underlying data files.83 </p></li><li class="listitem"><p>84 Rules on <code class="command">SELECT</code> statements that generate DML commands.85 </p></li><li class="listitem"><p>86 <code class="command">LOCK</code> that explicitly requests a mode higher than <code class="literal">ROW EXCLUSIVE MODE</code>.87 </p></li><li class="listitem"><p>88 <code class="command">LOCK</code> in short default form, since it requests <code class="literal">ACCESS EXCLUSIVE MODE</code>.89 </p></li><li class="listitem"><p>90 Transaction management commands that explicitly set non-read-only state:91 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>92 <code class="command">BEGIN READ WRITE</code>,93 <code class="command">START TRANSACTION READ WRITE</code>94 </p></li><li class="listitem"><p>95 <code class="command">SET TRANSACTION READ WRITE</code>,96 <code class="command">SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE</code>97 </p></li><li class="listitem"><p>98 <code class="command">SET transaction_read_only = off</code>99 </p></li></ul></div><p>100 </p></li><li class="listitem"><p>101 Two-phase commit commands: <code class="command">PREPARE TRANSACTION</code>,102 <code class="command">COMMIT PREPARED</code>, <code class="command">ROLLBACK PREPARED</code>103 because even read-only transactions need to write WAL in the104 prepare phase (the first phase of two phase commit).105 </p></li><li class="listitem"><p>106 Sequence updates: <code class="function">nextval()</code>, <code class="function">setval()</code>107 </p></li><li class="listitem"><p>108 <code class="command">LISTEN</code>, <code class="command">NOTIFY</code>109 </p></li></ul></div><p>110 </p><p>111 In normal operation, <span class="quote">“<span class="quote">read-only</span>”</span> transactions are allowed to112 use <code class="command">LISTEN</code> and <code class="command">NOTIFY</code>,113 so hot standby sessions operate under slightly tighter114 restrictions than ordinary read-only sessions. It is possible that some115 of these restrictions might be loosened in a future release.116 </p><p>117 During hot standby, the parameter <code class="varname">transaction_read_only</code> is always118 true and may not be changed. But as long as no attempt is made to modify119 the database, connections during hot standby will act much like any other120 database connection. If failover or switchover occurs, the database will121 switch to normal processing mode. Sessions will remain connected while the122 server changes mode. Once hot standby finishes, it will be possible to123 initiate read-write transactions (even from a session begun during124 hot standby).125 </p><p>126 Users can determine whether hot standby is currently active for their127 session by issuing <code class="command">SHOW in_hot_standby</code>.128 (In server versions before 14, the <code class="varname">in_hot_standby</code>129 parameter did not exist; a workable substitute method for older servers130 is <code class="command">SHOW transaction_read_only</code>.) In addition, a set of131 functions (<a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE" title="Table 9.92. Recovery Information Functions">Table 9.92</a>) allow users to132 access information about the standby server. These allow you to write133 programs that are aware of the current state of the database. These134 can be used to monitor the progress of recovery, or to allow you to135 write complex programs that restore the database to particular states.136 </p></div><div class="sect2" id="HOT-STANDBY-CONFLICT"><div class="titlepage"><div><div><h3 class="title">27.4.2. Handling Query Conflicts <a href="#HOT-STANDBY-CONFLICT" class="id_link">#</a></h3></div></div></div><p>137 The primary and standby servers are in many ways loosely connected. Actions138 on the primary will have an effect on the standby. As a result, there is139 potential for negative interactions or conflicts between them. The easiest140 conflict to understand is performance: if a huge data load is taking place141 on the primary then this will generate a similar stream of WAL records on the142 standby, so standby queries may contend for system resources, such as I/O.143 </p><p>144 There are also additional types of conflict that can occur with hot standby.145 These conflicts are <span class="emphasis"><em>hard conflicts</em></span> in the sense that queries146 might need to be canceled and, in some cases, sessions disconnected to resolve them.147 The user is provided with several ways to handle these148 conflicts. Conflict cases include:149 150 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>151 Access Exclusive locks taken on the primary server, including both152 explicit <code class="command">LOCK</code> commands and various <acronym class="acronym">DDL</acronym>153 actions, conflict with table accesses in standby queries.154 </p></li><li class="listitem"><p>155 Dropping a tablespace on the primary conflicts with standby queries156 using that tablespace for temporary work files.157 </p></li><li class="listitem"><p>158 Dropping a database on the primary conflicts with sessions connected159 to that database on the standby.160 </p></li><li class="listitem"><p>161 Application of a vacuum cleanup record from WAL conflicts with162 standby transactions whose snapshots can still <span class="quote">“<span class="quote">see</span>”</span> any of163 the rows to be removed.164 </p></li><li class="listitem"><p>165 Application of a vacuum cleanup record from WAL conflicts with166 queries accessing the target page on the standby, whether or not167 the data to be removed is visible.168 </p></li></ul></div><p>169 </p><p>170 On the primary server, these cases simply result in waiting; and the171 user might choose to cancel either of the conflicting actions. However,172 on the standby there is no choice: the WAL-logged action already occurred173 on the primary so the standby must not fail to apply it. Furthermore,174 allowing WAL application to wait indefinitely may be very undesirable,175 because the standby's state will become increasingly far behind the176 primary's. Therefore, a mechanism is provided to forcibly cancel standby177 queries that conflict with to-be-applied WAL records.178 </p><p>179 An example of the problem situation is an administrator on the primary180 server running <code class="command">DROP TABLE</code> on a table that is currently being181 queried on the standby server. Clearly the standby query cannot continue182 if the <code class="command">DROP TABLE</code> is applied on the standby. If this situation183 occurred on the primary, the <code class="command">DROP TABLE</code> would wait until the184 other query had finished. But when <code class="command">DROP TABLE</code> is run on the185 primary, the primary doesn't have information about what queries are186 running on the standby, so it will not wait for any such standby187 queries. The WAL change records come through to the standby while the188 standby query is still running, causing a conflict. The standby server189 must either delay application of the WAL records (and everything after190 them, too) or else cancel the conflicting query so that the <code class="command">DROP191 TABLE</code> can be applied.192 </p><p>193 When a conflicting query is short, it's typically desirable to allow it to194 complete by delaying WAL application for a little bit; but a long delay in195 WAL application is usually not desirable. So the cancel mechanism has196 parameters, <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY">max_standby_archive_delay</a> and <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY">max_standby_streaming_delay</a>, that define the maximum197 allowed delay in WAL application. Conflicting queries will be canceled198 once it has taken longer than the relevant delay setting to apply any199 newly-received WAL data. There are two parameters so that different delay200 values can be specified for the case of reading WAL data from an archive201 (i.e., initial recovery from a base backup or <span class="quote">“<span class="quote">catching up</span>”</span> a202 standby server that has fallen far behind) versus reading WAL data via203 streaming replication.204 </p><p>205 In a standby server that exists primarily for high availability, it's206 best to set the delay parameters relatively short, so that the server207 cannot fall far behind the primary due to delays caused by standby208 queries. However, if the standby server is meant for executing209 long-running queries, then a high or even infinite delay value may be210 preferable. Keep in mind however that a long-running query could211 cause other sessions on the standby server to not see recent changes212 on the primary, if it delays application of WAL records.213 </p><p>214 Once the delay specified by <code class="varname">max_standby_archive_delay</code> or215 <code class="varname">max_standby_streaming_delay</code> has been exceeded, conflicting216 queries will be canceled. This usually results just in a cancellation217 error, although in the case of replaying a <code class="command">DROP DATABASE</code>218 the entire conflicting session will be terminated. Also, if the conflict219 is over a lock held by an idle transaction, the conflicting session is220 terminated (this behavior might change in the future).221 </p><p>222 Canceled queries may be retried immediately (after beginning a new223 transaction, of course). Since query cancellation depends on224 the nature of the WAL records being replayed, a query that was225 canceled may well succeed if it is executed again.226 </p><p>227 Keep in mind that the delay parameters are compared to the elapsed time228 since the WAL data was received by the standby server. Thus, the grace229 period allowed to any one query on the standby is never more than the230 delay parameter, and could be considerably less if the standby has already231 fallen behind as a result of waiting for previous queries to complete, or232 as a result of being unable to keep up with a heavy update load.233 </p><p>234 The most common reason for conflict between standby queries and WAL replay235 is <span class="quote">“<span class="quote">early cleanup</span>”</span>. Normally, <span class="productname">PostgreSQL</span> allows236 cleanup of old row versions when there are no transactions that need to237 see them to ensure correct visibility of data according to MVCC rules.238 However, this rule can only be applied for transactions executing on the239 primary. So it is possible that cleanup on the primary will remove row240 versions that are still visible to a transaction on the standby.241 </p><p>242 Row version cleanup isn't the only potential cause of conflicts with243 standby queries. All index-only scans (including those that run on244 standbys) must use an <acronym class="acronym">MVCC</acronym> snapshot that245 <span class="quote">“<span class="quote">agrees</span>”</span> with the visibility map. Conflicts are therefore246 required whenever <code class="command">VACUUM</code> <a class="link" href="routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP" title="25.1.4. Updating the Visibility Map">sets a page as all-visible in the247 visibility map</a> containing one or more rows248 <span class="emphasis"><em>not</em></span> visible to all standby queries. So even running249 <code class="command">VACUUM</code> against a table with no updated or deleted rows250 requiring cleanup might lead to conflicts.251 </p><p>252 Users should be clear that tables that are regularly and heavily updated253 on the primary server will quickly cause cancellation of longer running254 queries on the standby. In such cases the setting of a finite value for255 <code class="varname">max_standby_archive_delay</code> or256 <code class="varname">max_standby_streaming_delay</code> can be considered similar to257 setting <code class="varname">statement_timeout</code>.258 </p><p>259 Remedial possibilities exist if the number of standby-query cancellations260 is found to be unacceptable. The first option is to set the parameter261 <code class="varname">hot_standby_feedback</code>, which prevents <code class="command">VACUUM</code> from262 removing recently-dead rows and so cleanup conflicts do not occur.263 If you do this, you264 should note that this will delay cleanup of dead rows on the primary,265 which may result in undesirable table bloat. However, the cleanup266 situation will be no worse than if the standby queries were running267 directly on the primary server, and you are still getting the benefit of268 off-loading execution onto the standby.269 If standby servers connect and disconnect frequently, you270 might want to make adjustments to handle the period when271 <code class="varname">hot_standby_feedback</code> feedback is not being provided.272 For example, consider increasing <code class="varname">max_standby_archive_delay</code>273 so that queries are not rapidly canceled by conflicts in WAL archive274 files during disconnected periods. You should also consider increasing275 <code class="varname">max_standby_streaming_delay</code> to avoid rapid cancellations276 by newly-arrived streaming WAL entries after reconnection.277 </p><p>278 The number of query cancels and the reason for them can be viewed using279 the <code class="structname">pg_stat_database_conflicts</code> system view on the standby280 server. The <code class="structname">pg_stat_database</code> system view also contains281 summary information.282 </p><p>283 Users can control whether a log message is produced when WAL replay is waiting284 longer than <code class="varname">deadlock_timeout</code> for conflicts. This285 is controlled by the <a class="xref" href="runtime-config-logging.html#GUC-LOG-RECOVERY-CONFLICT-WAITS">log_recovery_conflict_waits</a> parameter.286 </p></div><div class="sect2" id="HOT-STANDBY-ADMIN"><div class="titlepage"><div><div><h3 class="title">27.4.3. Administrator's Overview <a href="#HOT-STANDBY-ADMIN" class="id_link">#</a></h3></div></div></div><p>287 If <code class="varname">hot_standby</code> is <code class="literal">on</code> in <code class="filename">postgresql.conf</code>288 (the default value) and there is a289 <a class="link" href="warm-standby.html#FILE-STANDBY-SIGNAL"><code class="filename">standby.signal</code></a><a id="id-1.6.14.18.7.2.5" class="indexterm"></a>290 file present, the server will run in hot standby mode.291 However, it may take some time for hot standby connections to be allowed,292 because the server will not accept connections until it has completed293 sufficient recovery to provide a consistent state against which queries294 can run. During this period,295 clients that attempt to connect will be refused with an error message.296 To confirm the server has come up, either loop trying to connect from297 the application, or look for these messages in the server logs:298 299</p><pre class="programlisting">300LOG: entering standby mode301 302... then some time later ...303 304LOG: consistent recovery state reached305LOG: database system is ready to accept read-only connections306</pre><p>307 308 Consistency information is recorded once per checkpoint on the primary.309 It is not possible to enable hot standby when reading WAL310 written during a period when <code class="varname">wal_level</code> was not set to311 <code class="literal">replica</code> or <code class="literal">logical</code> on the primary. Reaching312 a consistent state can also be delayed in the presence of both of these313 conditions:314 315 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>316 A write transaction has more than 64 subtransactions317 </p></li><li class="listitem"><p>318 Very long-lived write transactions319 </p></li></ul></div><p>320 321 If you are running file-based log shipping ("warm standby"), you might need322 to wait until the next WAL file arrives, which could be as long as the323 <code class="varname">archive_timeout</code> setting on the primary.324 </p><p>325 The settings of some parameters determine the size of shared memory for326 tracking transaction IDs, locks, and prepared transactions. These shared327 memory structures must be no smaller on a standby than on the primary in328 order to ensure that the standby does not run out of shared memory during329 recovery. For example, if the primary had used a prepared transaction but330 the standby had not allocated any shared memory for tracking prepared331 transactions, then recovery could not continue until the standby's332 configuration is changed. The parameters affected are:333 334 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>335 <code class="varname">max_connections</code>336 </p></li><li class="listitem"><p>337 <code class="varname">max_prepared_transactions</code>338 </p></li><li class="listitem"><p>339 <code class="varname">max_locks_per_transaction</code>340 </p></li><li class="listitem"><p>341 <code class="varname">max_wal_senders</code>342 </p></li><li class="listitem"><p>343 <code class="varname">max_worker_processes</code>344 </p></li></ul></div><p>345 346 The easiest way to ensure this does not become a problem is to have these347 parameters set on the standbys to values equal to or greater than on the348 primary. Therefore, if you want to increase these values, you should do349 so on all standby servers first, before applying the changes to the350 primary server. Conversely, if you want to decrease these values, you351 should do so on the primary server first, before applying the changes to352 all standby servers. Keep in mind that when a standby is promoted, it353 becomes the new reference for the required parameter settings for the354 standbys that follow it. Therefore, to avoid this becoming a problem355 during a switchover or failover, it is recommended to keep these settings356 the same on all standby servers.357 </p><p>358 The WAL tracks changes to these parameters on the359 primary. If a hot standby processes WAL that indicates that the current360 value on the primary is higher than its own value, it will log a warning361 and pause recovery, for example:362</p><pre class="screen">363WARNING: hot standby is not possible because of insufficient parameter settings364DETAIL: max_connections = 80 is a lower setting than on the primary server, where its value was 100.365LOG: recovery has paused366DETAIL: If recovery is unpaused, the server will shut down.367HINT: You can then restart the server after making the necessary configuration changes.368</pre><p>369 At that point, the settings on the standby need to be updated and the370 instance restarted before recovery can continue. If the standby is not a371 hot standby, then when it encounters the incompatible parameter change, it372 will shut down immediately without pausing, since there is then no value373 in keeping it up.374 </p><p>375 It is important that the administrator select appropriate settings for376 <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY">max_standby_archive_delay</a> and <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY">max_standby_streaming_delay</a>. The best choices vary377 depending on business priorities. For example if the server is primarily378 tasked as a High Availability server, then you will want low delay379 settings, perhaps even zero, though that is a very aggressive setting. If380 the standby server is tasked as an additional server for decision support381 queries then it might be acceptable to set the maximum delay values to382 many hours, or even -1 which means wait forever for queries to complete.383 </p><p>384 Transaction status "hint bits" written on the primary are not WAL-logged,385 so data on the standby will likely re-write the hints again on the standby.386 Thus, the standby server will still perform disk writes even though387 all users are read-only; no changes occur to the data values388 themselves. Users will still write large sort temporary files and389 re-generate relcache info files, so no part of the database390 is truly read-only during hot standby mode.391 Note also that writes to remote databases using392 <span class="application">dblink</span> module, and other operations outside the393 database using PL functions will still be possible, even though the394 transaction is read-only locally.395 </p><p>396 The following types of administration commands are not accepted397 during recovery mode:398 399 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>400 Data Definition Language (DDL): e.g., <code class="command">CREATE INDEX</code>401 </p></li><li class="listitem"><p>402 Privilege and Ownership: <code class="command">GRANT</code>, <code class="command">REVOKE</code>,403 <code class="command">REASSIGN</code>404 </p></li><li class="listitem"><p>405 Maintenance commands: <code class="command">ANALYZE</code>, <code class="command">VACUUM</code>,406 <code class="command">CLUSTER</code>, <code class="command">REINDEX</code>407 </p></li></ul></div><p>408 </p><p>409 Again, note that some of these commands are actually allowed during410 "read only" mode transactions on the primary.411 </p><p>412 As a result, you cannot create additional indexes that exist solely413 on the standby, nor statistics that exist solely on the standby.414 If these administration commands are needed, they should be executed415 on the primary, and eventually those changes will propagate to the416 standby.417 </p><p>418 <code class="function">pg_cancel_backend()</code>419 and <code class="function">pg_terminate_backend()</code> will work on user backends,420 but not the startup process, which performs421 recovery. <code class="structname">pg_stat_activity</code> does not show422 recovering transactions as active. As a result,423 <code class="structname">pg_prepared_xacts</code> is always empty during424 recovery. If you wish to resolve in-doubt prepared transactions, view425 <code class="literal">pg_prepared_xacts</code> on the primary and issue commands to426 resolve transactions there or resolve them after the end of recovery.427 </p><p>428 <code class="structname">pg_locks</code> will show locks held by backends,429 as normal. <code class="structname">pg_locks</code> also shows430 a virtual transaction managed by the startup process that owns all431 <code class="literal">AccessExclusiveLocks</code> held by transactions being replayed by recovery.432 Note that the startup process does not acquire locks to433 make database changes, and thus locks other than <code class="literal">AccessExclusiveLocks</code>434 do not show in <code class="structname">pg_locks</code> for the Startup435 process; they are just presumed to exist.436 </p><p>437 The <span class="productname">Nagios</span> plugin <span class="productname">check_pgsql</span> will438 work, because the simple information it checks for exists.439 The <span class="productname">check_postgres</span> monitoring script will also work,440 though some reported values could give different or confusing results.441 For example, last vacuum time will not be maintained, since no442 vacuum occurs on the standby. Vacuums running on the primary443 do still send their changes to the standby.444 </p><p>445 WAL file control commands will not work during recovery,446 e.g., <code class="function">pg_backup_start</code>, <code class="function">pg_switch_wal</code> etc.447 </p><p>448 Dynamically loadable modules work, including <code class="structname">pg_stat_statements</code>.449 </p><p>450 Advisory locks work normally in recovery, including deadlock detection.451 Note that advisory locks are never WAL logged, so it is impossible for452 an advisory lock on either the primary or the standby to conflict with WAL453 replay. Nor is it possible to acquire an advisory lock on the primary454 and have it initiate a similar advisory lock on the standby. Advisory455 locks relate only to the server on which they are acquired.456 </p><p>457 Trigger-based replication systems such as <span class="productname">Slony</span>,458 <span class="productname">Londiste</span> and <span class="productname">Bucardo</span> won't run on the459 standby at all, though they will run happily on the primary server as460 long as the changes are not sent to standby servers to be applied.461 WAL replay is not trigger-based so you cannot relay from the462 standby to any system that requires additional database writes or463 relies on the use of triggers.464 </p><p>465 New OIDs cannot be assigned, though some <acronym class="acronym">UUID</acronym> generators may still466 work as long as they do not rely on writing new status to the database.467 </p><p>468 Currently, temporary table creation is not allowed during read-only469 transactions, so in some cases existing scripts will not run correctly.470 This restriction might be relaxed in a later release. This is471 both an SQL standard compliance issue and a technical issue.472 </p><p>473 <code class="command">DROP TABLESPACE</code> can only succeed if the tablespace is empty.474 Some standby users may be actively using the tablespace via their475 <code class="varname">temp_tablespaces</code> parameter. If there are temporary files in the476 tablespace, all active queries are canceled to ensure that temporary477 files are removed, so the tablespace can be removed and WAL replay478 can continue.479 </p><p>480 Running <code class="command">DROP DATABASE</code> or <code class="command">ALTER DATABASE ... SET481 TABLESPACE</code> on the primary482 will generate a WAL entry that will cause all users connected to that483 database on the standby to be forcibly disconnected. This action occurs484 immediately, whatever the setting of485 <code class="varname">max_standby_streaming_delay</code>. Note that486 <code class="command">ALTER DATABASE ... RENAME</code> does not disconnect users, which487 in most cases will go unnoticed, though might in some cases cause a488 program confusion if it depends in some way upon database name.489 </p><p>490 In normal (non-recovery) mode, if you issue <code class="command">DROP USER</code> or <code class="command">DROP ROLE</code>491 for a role with login capability while that user is still connected then492 nothing happens to the connected user — they remain connected. The user cannot493 reconnect however. This behavior applies in recovery also, so a494 <code class="command">DROP USER</code> on the primary does not disconnect that user on the standby.495 </p><p>496 The cumulative statistics system is active during recovery. All scans,497 reads, blocks, index usage, etc., will be recorded normally on the498 standby. However, WAL replay will not increment relation and database499 specific counters. I.e. replay will not increment pg_stat_all_tables500 columns (like n_tup_ins), nor will reads or writes performed by the501 startup process be tracked in the pg_statio views, nor will associated502 pg_stat_database columns be incremented.503 </p><p>504 Autovacuum is not active during recovery. It will start normally at the505 end of recovery.506 </p><p>507 The checkpointer process and the background writer process are active during508 recovery. The checkpointer process will perform restartpoints (similar to509 checkpoints on the primary) and the background writer process will perform510 normal block cleaning activities. This can include updates of the hint bit511 information stored on the standby server.512 The <code class="command">CHECKPOINT</code> command is accepted during recovery,513 though it performs a restartpoint rather than a new checkpoint.514 </p></div><div class="sect2" id="HOT-STANDBY-PARAMETERS"><div class="titlepage"><div><div><h3 class="title">27.4.4. Hot Standby Parameter Reference <a href="#HOT-STANDBY-PARAMETERS" class="id_link">#</a></h3></div></div></div><p>515 Various parameters have been mentioned above in516 <a class="xref" href="hot-standby.html#HOT-STANDBY-CONFLICT" title="27.4.2. Handling Query Conflicts">Section 27.4.2</a> and517 <a class="xref" href="hot-standby.html#HOT-STANDBY-ADMIN" title="27.4.3. Administrator's Overview">Section 27.4.3</a>.518 </p><p>519 On the primary, the <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> parameter can be used.520 <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY">max_standby_archive_delay</a> and521 <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY">max_standby_streaming_delay</a> have no effect if set on522 the primary.523 </p><p>524 On the standby, parameters <a class="xref" href="runtime-config-replication.html#GUC-HOT-STANDBY">hot_standby</a>,525 <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY">max_standby_archive_delay</a> and526 <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY">max_standby_streaming_delay</a> can be used.527 </p></div><div class="sect2" id="HOT-STANDBY-CAVEATS"><div class="titlepage"><div><div><h3 class="title">27.4.5. Caveats <a href="#HOT-STANDBY-CAVEATS" class="id_link">#</a></h3></div></div></div><p>528 There are several limitations of hot standby.529 These can and probably will be fixed in future releases:530 531 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>532 Full knowledge of running transactions is required before snapshots533 can be taken. Transactions that use large numbers of subtransactions534 (currently greater than 64) will delay the start of read-only535 connections until the completion of the longest running write transaction.536 If this situation occurs, explanatory messages will be sent to the server log.537 </p></li><li class="listitem"><p>538 Valid starting points for standby queries are generated at each539 checkpoint on the primary. If the standby is shut down while the primary540 is in a shutdown state, it might not be possible to re-enter hot standby541 until the primary is started up, so that it generates further starting542 points in the WAL logs. This situation isn't a problem in the most543 common situations where it might happen. Generally, if the primary is544 shut down and not available anymore, that's likely due to a serious545 failure that requires the standby being converted to operate as546 the new primary anyway. And in situations where the primary is547 being intentionally taken down, coordinating to make sure the standby548 becomes the new primary smoothly is also standard procedure.549 </p></li><li class="listitem"><p>550 At the end of recovery, <code class="literal">AccessExclusiveLocks</code> held by prepared transactions551 will require twice the normal number of lock table entries. If you plan552 on running either a large number of concurrent prepared transactions553 that normally take <code class="literal">AccessExclusiveLocks</code>, or you plan on having one554 large transaction that takes many <code class="literal">AccessExclusiveLocks</code>, you are555 advised to select a larger value of <code class="varname">max_locks_per_transaction</code>,556 perhaps as much as twice the value of the parameter on557 the primary server. You need not consider this at all if558 your setting of <code class="varname">max_prepared_transactions</code> is 0.559 </p></li><li class="listitem"><p>560 The Serializable transaction isolation level is not yet available in hot561 standby. (See <a class="xref" href="transaction-iso.html#XACT-SERIALIZABLE" title="13.2.3. Serializable Isolation Level">Section 13.2.3</a> and562 <a class="xref" href="applevel-consistency.html#SERIALIZABLE-CONSISTENCY" title="13.4.1. Enforcing Consistency with Serializable Transactions">Section 13.4.1</a> for details.)563 An attempt to set a transaction to the serializable isolation level in564 hot standby mode will generate an error.565 </p></li></ul></div><p>566 567 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="warm-standby-failover.html" title="27.3. Failover">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="high-availability.html" title="Chapter 27. High Availability, Load Balancing, and Replication">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="monitoring.html" title="Chapter 28. Monitoring Database Activity">Next</a></td></tr><tr><td width="40%" align="left" valign="top">27.3. Failover </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"> Chapter 28. Monitoring Database Activity</td></tr></table></div></body></html>