Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes14kdownloads
hot-standby.html567 linesDownload Raw Back to html
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>
codekingpro/portable-devtools · Team Ai