Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
보기 1
좋아요 0
위키 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
브렌치: master
브랜치 태그
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/hot-standby.html

539 lines
39KB
Raw 고유링크 Blame 히스토리 

  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>26.5. 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 V1.79.1" /><link rel="prev" href="log-shipping-alternative.html" title="26.4. Alternative Method for Log Shipping" /><link rel="next" href="monitoring.html" title="Chapter 27. Monitoring Database Activity" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">26.5. Hot Standby</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="log-shipping-alternative.html" title="26.4. Alternative Method for Log Shipping">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="high-availability.html" title="Chapter 26. High Availability, Load Balancing, and Replication">Up</a></td><th width="60%" align="center">Chapter 26. High Availability, Load Balancing, and Replication</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="monitoring.html" title="Chapter 27. Monitoring Database Activity">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="HOT-STANDBY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">26.5. Hot Standby</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-USERS">26.5.1. User's Overview</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-CONFLICT">26.5.2. Handling Query Conflicts</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-ADMIN">26.5.3. Administrator's Overview</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-PARAMETERS">26.5.4. Hot Standby Parameter Reference</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-CAVEATS">26.5.5. Caveats</a></span></dt></dl></div><a id="id-1.6.13.19.2" class="indexterm"></a><p>

  3.     Hot Standby is the term used to describe the ability to connect to

  4.     the server and run read-only queries while the server is in archive

  5.     recovery or standby mode. This

  6.     is useful both for replication purposes and for restoring a backup

  7.     to a desired state with great precision.

  8.     The term Hot Standby also refers to the ability of the server to move

  9.     from recovery through to normal operation while users continue running

  10.     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 differences

  14.     explained below.

  15.    </p><div class="sect2" id="HOT-STANDBY-USERS"><div class="titlepage"><div><div><h3 class="title">26.5.1. User's Overview</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 a

  17.     standby server, it will begin accepting connections once the recovery has

  18.     brought the system to a consistent state.  All such connections are

  19.     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 server

  22.     so there will be a measurable delay between primary and standby. Running the

  23.     same query nearly simultaneously on both primary and standby might therefore

  24.     return differing results. We say that data on the standby is

  25.     <em class="firstterm">eventually consistent</em> with the primary.  Once the

  26.     commit record for a transaction is replayed on the standby, the changes

  27.     made by that transaction will be visible to any new snapshots taken on

  28.     the standby.  Snapshots may be taken at the start of each query or at the

  29.     start of each transaction, depending on the current transaction isolation

  30.     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.        Parameters - <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 subtransactions

  48.           </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 a

  62.     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>, <code class="command">COPY FROM</code>,

  68.        <code class="command">TRUNCATE</code>.

  69.        Note that there are no allowed actions that result in a trigger

  70.        being executed during recovery.  This restriction applies even to

  71.        temporary tables, because table rows cannot be read or written without

  72.        assigning a transaction ID, which is currently not possible in a

  73.        Hot Standby environment.

  74.       </p></li><li class="listitem"><p>

  75.        Data Definition Language (DDL) - <code class="command">CREATE</code>,

  76.        <code class="command">DROP</code>, <code class="command">ALTER</code>, <code class="command">COMMENT</code>.

  77.        This restriction applies even to temporary tables, because carrying

  78.        out these operations would require updating the system catalog tables.

  79.       </p></li><li class="listitem"><p>

  80.        <code class="command">SELECT ... FOR SHARE | UPDATE</code>, because row locks cannot be

  81.        taken without updating the underlying data files.

  82.       </p></li><li class="listitem"><p>

  83.        Rules on <code class="command">SELECT</code> statements that generate DML commands.

  84.       </p></li><li class="listitem"><p>

  85.        <code class="command">LOCK</code> that explicitly requests a mode higher than <code class="literal">ROW EXCLUSIVE MODE</code>.

  86.       </p></li><li class="listitem"><p>

  87.        <code class="command">LOCK</code> in short default form, since it requests <code class="literal">ACCESS EXCLUSIVE MODE</code>.

  88.       </p></li><li class="listitem"><p>

  89.        Transaction management commands that explicitly set non-read-only state:

  90.         </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>

  91.             <code class="command">BEGIN READ WRITE</code>,

  92.             <code class="command">START TRANSACTION READ WRITE</code>

  93.           </p></li><li class="listitem"><p>

  94.             <code class="command">SET TRANSACTION READ WRITE</code>,

  95.             <code class="command">SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE</code>

  96.           </p></li><li class="listitem"><p>

  97.            <code class="command">SET transaction_read_only = off</code>

  98.           </p></li></ul></div><p>

  99.       </p></li><li class="listitem"><p>

  100.        Two-phase commit commands - <code class="command">PREPARE TRANSACTION</code>,

  101.        <code class="command">COMMIT PREPARED</code>, <code class="command">ROLLBACK PREPARED</code>

  102.        because even read-only transactions need to write WAL in the

  103.        prepare phase (the first phase of two phase commit).

  104.       </p></li><li class="listitem"><p>

  105.        Sequence updates - <code class="function">nextval()</code>, <code class="function">setval()</code>

  106.       </p></li><li class="listitem"><p>

  107.        <code class="command">LISTEN</code>, <code class="command">NOTIFY</code>

  108.       </p></li></ul></div><p>

  109.    </p><p>

  110.     In normal operation, <span class="quote">“<span class="quote">read-only</span>”</span> transactions are allowed to

  111.     use <code class="command">LISTEN</code> and <code class="command">NOTIFY</code>,

  112.     so Hot Standby sessions operate under slightly tighter

  113.     restrictions than ordinary read-only sessions.  It is possible that some

  114.     of these restrictions might be loosened in a future release.

  115.    </p><p>

  116.     During hot standby, the parameter <code class="varname">transaction_read_only</code> is always

  117.     true and may not be changed.  But as long as no attempt is made to modify

  118.     the database, connections during hot standby will act much like any other

  119.     database connection.  If failover or switchover occurs, the database will

  120.     switch to normal processing mode.  Sessions will remain connected while the

  121.     server changes mode.  Once hot standby finishes, it will be possible to

  122.     initiate read-write transactions (even from a session begun during

  123.     hot standby).

  124.    </p><p>

  125.     Users will be able to tell whether their session is read-only by

  126.     issuing <code class="command">SHOW transaction_read_only</code>.  In addition, a set of

  127.     functions (<a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE" title="Table 9.85. Recovery Information Functions">Table 9.85</a>) allow users to

  128.     access information about the standby server. These allow you to write

  129.     programs that are aware of the current state of the database. These

  130.     can be used to monitor the progress of recovery, or to allow you to

  131.     write complex programs that restore the database to particular states.

  132.    </p></div><div class="sect2" id="HOT-STANDBY-CONFLICT"><div class="titlepage"><div><div><h3 class="title">26.5.2. Handling Query Conflicts</h3></div></div></div><p>

  133.     The primary and standby servers are in many ways loosely connected. Actions

  134.     on the primary will have an effect on the standby. As a result, there is

  135.     potential for negative interactions or conflicts between them. The easiest

  136.     conflict to understand is performance: if a huge data load is taking place

  137.     on the primary then this will generate a similar stream of WAL records on the

  138.     standby, so standby queries may contend for system resources, such as I/O.

  139.    </p><p>

  140.     There are also additional types of conflict that can occur with Hot Standby.

  141.     These conflicts are <span class="emphasis"><em>hard conflicts</em></span> in the sense that queries

  142.     might need to be canceled and, in some cases, sessions disconnected to resolve them.

  143.     The user is provided with several ways to handle these

  144.     conflicts. Conflict cases include:

  145. 

  146.       </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  147.          Access Exclusive locks taken on the primary server, including both

  148.          explicit <code class="command">LOCK</code> commands and various <acronym class="acronym">DDL</acronym>

  149.          actions, conflict with table accesses in standby queries.

  150.         </p></li><li class="listitem"><p>

  151.          Dropping a tablespace on the primary conflicts with standby queries

  152.          using that tablespace for temporary work files.

  153.         </p></li><li class="listitem"><p>

  154.          Dropping a database on the primary conflicts with sessions connected

  155.          to that database on the standby.

  156.         </p></li><li class="listitem"><p>

  157.          Application of a vacuum cleanup record from WAL conflicts with

  158.          standby transactions whose snapshots can still <span class="quote">“<span class="quote">see</span>”</span> any of

  159.          the rows to be removed.

  160.         </p></li><li class="listitem"><p>

  161.          Application of a vacuum cleanup record from WAL conflicts with

  162.          queries accessing the target page on the standby, whether or not

  163.          the data to be removed is visible.

  164.         </p></li></ul></div><p>

  165.    </p><p>

  166.     On the primary server, these cases simply result in waiting; and the

  167.     user might choose to cancel either of the conflicting actions.  However,

  168.     on the standby there is no choice: the WAL-logged action already occurred

  169.     on the primary so the standby must not fail to apply it.  Furthermore,

  170.     allowing WAL application to wait indefinitely may be very undesirable,

  171.     because the standby's state will become increasingly far behind the

  172.     primary's.  Therefore, a mechanism is provided to forcibly cancel standby

  173.     queries that conflict with to-be-applied WAL records.

  174.    </p><p>

  175.     An example of the problem situation is an administrator on the primary

  176.     server running <code class="command">DROP TABLE</code> on a table that is currently being

  177.     queried on the standby server.  Clearly the standby query cannot continue

  178.     if the <code class="command">DROP TABLE</code> is applied on the standby. If this situation

  179.     occurred on the primary, the <code class="command">DROP TABLE</code> would wait until the

  180.     other query had finished. But when <code class="command">DROP TABLE</code> is run on the

  181.     primary, the primary doesn't have information about what queries are

  182.     running on the standby, so it will not wait for any such standby

  183.     queries. The WAL change records come through to the standby while the

  184.     standby query is still running, causing a conflict.  The standby server

  185.     must either delay application of the WAL records (and everything after

  186.     them, too) or else cancel the conflicting query so that the <code class="command">DROP

  187.     TABLE</code> can be applied.

  188.    </p><p>

  189.     When a conflicting query is short, it's typically desirable to allow it to

  190.     complete by delaying WAL application for a little bit; but a long delay in

  191.     WAL application is usually not desirable.  So the cancel mechanism has

  192.     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 maximum

  193.     allowed delay in WAL application.  Conflicting queries will be canceled

  194.     once it has taken longer than the relevant delay setting to apply any

  195.     newly-received WAL data.  There are two parameters so that different delay

  196.     values can be specified for the case of reading WAL data from an archive

  197.     (i.e., initial recovery from a base backup or <span class="quote">“<span class="quote">catching up</span>”</span> a

  198.     standby server that has fallen far behind) versus reading WAL data via

  199.     streaming replication.

  200.    </p><p>

  201.     In a standby server that exists primarily for high availability, it's

  202.     best to set the delay parameters relatively short, so that the server

  203.     cannot fall far behind the primary due to delays caused by standby

  204.     queries.  However, if the standby server is meant for executing

  205.     long-running queries, then a high or even infinite delay value may be

  206.     preferable.  Keep in mind however that a long-running query could

  207.     cause other sessions on the standby server to not see recent changes

  208.     on the primary, if it delays application of WAL records.

  209.    </p><p>

  210.     Once the delay specified by <code class="varname">max_standby_archive_delay</code> or

  211.     <code class="varname">max_standby_streaming_delay</code> has been exceeded, conflicting

  212.     queries will be canceled.  This usually results just in a cancellation

  213.     error, although in the case of replaying a <code class="command">DROP DATABASE</code>

  214.     the entire conflicting session will be terminated.  Also, if the conflict

  215.     is over a lock held by an idle transaction, the conflicting session is

  216.     terminated (this behavior might change in the future).

  217.    </p><p>

  218.     Canceled queries may be retried immediately (after beginning a new

  219.     transaction, of course).  Since query cancellation depends on

  220.     the nature of the WAL records being replayed, a query that was

  221.     canceled may well succeed if it is executed again.

  222.    </p><p>

  223.     Keep in mind that the delay parameters are compared to the elapsed time

  224.     since the WAL data was received by the standby server.  Thus, the grace

  225.     period allowed to any one query on the standby is never more than the

  226.     delay parameter, and could be considerably less if the standby has already

  227.     fallen behind as a result of waiting for previous queries to complete, or

  228.     as a result of being unable to keep up with a heavy update load.

  229.    </p><p>

  230.     The most common reason for conflict between standby queries and WAL replay

  231.     is <span class="quote">“<span class="quote">early cleanup</span>”</span>.  Normally, <span class="productname">PostgreSQL</span> allows

  232.     cleanup of old row versions when there are no transactions that need to

  233.     see them to ensure correct visibility of data according to MVCC rules.

  234.     However, this rule can only be applied for transactions executing on the

  235.     master.  So it is possible that cleanup on the master will remove row

  236.     versions that are still visible to a transaction on the standby.

  237.    </p><p>

  238.     Experienced users should note that both row version cleanup and row version

  239.     freezing will potentially conflict with standby queries. Running a manual

  240.     <code class="command">VACUUM FREEZE</code> is likely to cause conflicts even on tables with

  241.     no updated or deleted rows.

  242.    </p><p>

  243.     Users should be clear that tables that are regularly and heavily updated

  244.     on the primary server will quickly cause cancellation of longer running

  245.     queries on the standby. In such cases the setting of a finite value for

  246.     <code class="varname">max_standby_archive_delay</code> or

  247.     <code class="varname">max_standby_streaming_delay</code> can be considered similar to

  248.     setting <code class="varname">statement_timeout</code>.

  249.    </p><p>

  250.     Remedial possibilities exist if the number of standby-query cancellations

  251.     is found to be unacceptable.  The first option is to set the parameter

  252.     <code class="varname">hot_standby_feedback</code>, which prevents <code class="command">VACUUM</code> from

  253.     removing recently-dead rows and so cleanup conflicts do not occur.

  254.     If you do this, you

  255.     should note that this will delay cleanup of dead rows on the primary,

  256.     which may result in undesirable table bloat. However, the cleanup

  257.     situation will be no worse than if the standby queries were running

  258.     directly on the primary server, and you are still getting the benefit of

  259.     off-loading execution onto the standby.

  260.     If standby servers connect and disconnect frequently, you

  261.     might want to make adjustments to handle the period when

  262.     <code class="varname">hot_standby_feedback</code> feedback is not being provided.

  263.     For example, consider increasing <code class="varname">max_standby_archive_delay</code>

  264.     so that queries are not rapidly canceled by conflicts in WAL archive

  265.     files during disconnected periods.  You should also consider increasing

  266.     <code class="varname">max_standby_streaming_delay</code> to avoid rapid cancellations

  267.     by newly-arrived streaming WAL entries after reconnection.

  268.    </p><p>

  269.     Another option is to increase <a class="xref" href="runtime-config-replication.html#GUC-VACUUM-DEFER-CLEANUP-AGE">vacuum_defer_cleanup_age</a>

  270.     on the primary server, so that dead rows will not be cleaned up as quickly

  271.     as they normally would be.  This will allow more time for queries to

  272.     execute before they are canceled on the standby, without having to set

  273.     a high <code class="varname">max_standby_streaming_delay</code>.  However it is

  274.     difficult to guarantee any specific execution-time window with this

  275.     approach, since <code class="varname">vacuum_defer_cleanup_age</code> is measured in

  276.     transactions executed on the primary server.

  277.    </p><p>

  278.     The number of query cancels and the reason for them can be viewed using

  279.     the <code class="structname">pg_stat_database_conflicts</code> system view on the standby

  280.     server. The <code class="structname">pg_stat_database</code> system view also contains

  281.     summary information.

  282.    </p></div><div class="sect2" id="HOT-STANDBY-ADMIN"><div class="titlepage"><div><div><h3 class="title">26.5.3. Administrator's Overview</h3></div></div></div><p>

  283.     If <code class="varname">hot_standby</code> is <code class="literal">on</code> in <code class="filename">postgresql.conf</code>

  284.     (the default value) and there is a <code class="filename">standby.signal</code>

  285.     file present, the server will run in Hot Standby mode.

  286.     However, it may take some time for Hot Standby connections to be allowed,

  287.     because the server will not accept connections until it has completed

  288.     sufficient recovery to provide a consistent state against which queries

  289.     can run.  During this period,

  290.     clients that attempt to connect will be refused with an error message.

  291.     To confirm the server has come up, either loop trying to connect from

  292.     the application, or look for these messages in the server logs:

  293. 

  294. </p><pre class="programlisting">

  295. LOG:  entering standby mode

  296. 

  297. ... then some time later ...

  298. 

  299. LOG:  consistent recovery state reached

  300. LOG:  database system is ready to accept read only connections

  301. </pre><p>

  302. 

  303.     Consistency information is recorded once per checkpoint on the primary.

  304.     It is not possible to enable hot standby when reading WAL

  305.     written during a period when <code class="varname">wal_level</code> was not set to

  306.     <code class="literal">replica</code> or <code class="literal">logical</code> on the primary.  Reaching

  307.     a consistent state can also be delayed in the presence of both of these

  308.     conditions:

  309. 

  310.       </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  311.          A write transaction has more than 64 subtransactions

  312.         </p></li><li class="listitem"><p>

  313.          Very long-lived write transactions

  314.         </p></li></ul></div><p>

  315. 

  316.     If you are running file-based log shipping ("warm standby"), you might need

  317.     to wait until the next WAL file arrives, which could be as long as the

  318.     <code class="varname">archive_timeout</code> setting on the primary.

  319.    </p><p>

  320.     The setting of some parameters on the standby will need reconfiguration

  321.     if they have been changed on the primary. For these parameters,

  322.     the value on the standby must

  323.     be equal to or greater than the value on the primary.

  324.     Therefore, if you want to increase these values, you should do so on all

  325.     standby servers first, before applying the changes to the primary server.

  326.     Conversely, if you want to decrease these values, you should do so on the

  327.     primary server first, before applying the changes to all standby servers.

  328.     If these parameters

  329.     are not set high enough then the standby will refuse to start.

  330.     Higher values can then be supplied and the server

  331.     restarted to begin recovery again.  These parameters are:

  332. 

  333.       </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  334.          <code class="varname">max_connections</code>

  335.         </p></li><li class="listitem"><p>

  336.          <code class="varname">max_prepared_transactions</code>

  337.         </p></li><li class="listitem"><p>

  338.          <code class="varname">max_locks_per_transaction</code>

  339.         </p></li><li class="listitem"><p>

  340.          <code class="varname">max_wal_senders</code>

  341.         </p></li><li class="listitem"><p>

  342.          <code class="varname">max_worker_processes</code>

  343.         </p></li></ul></div><p>

  344.    </p><p>

  345.     It is important that the administrator select appropriate settings for

  346.     <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 vary

  347.     depending on business priorities.  For example if the server is primarily

  348.     tasked as a High Availability server, then you will want low delay

  349.     settings, perhaps even zero, though that is a very aggressive setting. If

  350.     the standby server is tasked as an additional server for decision support

  351.     queries then it might be acceptable to set the maximum delay values to

  352.     many hours, or even -1 which means wait forever for queries to complete.

  353.    </p><p>

  354.     Transaction status "hint bits" written on the primary are not WAL-logged,

  355.     so data on the standby will likely re-write the hints again on the standby.

  356.     Thus, the standby server will still perform disk writes even though

  357.     all users are read-only; no changes occur to the data values

  358.     themselves.  Users will still write large sort temporary files and

  359.     re-generate relcache info files, so no part of the database

  360.     is truly read-only during hot standby mode.

  361.     Note also that writes to remote databases using

  362.     <span class="application">dblink</span> module, and other operations outside the

  363.     database using PL functions will still be possible, even though the

  364.     transaction is read-only locally.

  365.    </p><p>

  366.     The following types of administration commands are not accepted

  367.     during recovery mode:

  368. 

  369.       </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  370.          Data Definition Language (DDL) - e.g. <code class="command">CREATE INDEX</code>

  371.         </p></li><li class="listitem"><p>

  372.          Privilege and Ownership - <code class="command">GRANT</code>, <code class="command">REVOKE</code>,

  373.          <code class="command">REASSIGN</code>

  374.         </p></li><li class="listitem"><p>

  375.          Maintenance commands - <code class="command">ANALYZE</code>, <code class="command">VACUUM</code>,

  376.          <code class="command">CLUSTER</code>, <code class="command">REINDEX</code>

  377.         </p></li></ul></div><p>

  378.    </p><p>

  379.     Again, note that some of these commands are actually allowed during

  380.     "read only" mode transactions on the primary.

  381.    </p><p>

  382.     As a result, you cannot create additional indexes that exist solely

  383.     on the standby, nor statistics that exist solely on the standby.

  384.     If these administration commands are needed, they should be executed

  385.     on the primary, and eventually those changes will propagate to the

  386.     standby.

  387.    </p><p>

  388.     <code class="function">pg_cancel_backend()</code>

  389.     and <code class="function">pg_terminate_backend()</code> will work on user backends,

  390.     but not the Startup process, which performs

  391.     recovery. <code class="structname">pg_stat_activity</code> does not show

  392.     recovering transactions as active. As a result,

  393.     <code class="structname">pg_prepared_xacts</code> is always empty during

  394.     recovery. If you wish to resolve in-doubt prepared transactions, view

  395.     <code class="literal">pg_prepared_xacts</code> on the primary and issue commands to

  396.     resolve transactions there or resolve them after the end of recovery.

  397.    </p><p>

  398.     <code class="structname">pg_locks</code> will show locks held by backends,

  399.     as normal. <code class="structname">pg_locks</code> also shows

  400.     a virtual transaction managed by the Startup process that owns all

  401.     <code class="literal">AccessExclusiveLocks</code> held by transactions being replayed by recovery.

  402.     Note that the Startup process does not acquire locks to

  403.     make database changes, and thus locks other than <code class="literal">AccessExclusiveLocks</code>

  404.     do not show in <code class="structname">pg_locks</code> for the Startup

  405.     process; they are just presumed to exist.

  406.    </p><p>

  407.     The <span class="productname">Nagios</span> plugin <span class="productname">check_pgsql</span> will

  408.     work, because the simple information it checks for exists.

  409.     The <span class="productname">check_postgres</span> monitoring script will also work,

  410.     though some reported values could give different or confusing results.

  411.     For example, last vacuum time will not be maintained, since no

  412.     vacuum occurs on the standby.  Vacuums running on the primary

  413.     do still send their changes to the standby.

  414.    </p><p>

  415.     WAL file control commands will not work during recovery,

  416.     e.g. <code class="function">pg_start_backup</code>, <code class="function">pg_switch_wal</code> etc.

  417.    </p><p>

  418.     Dynamically loadable modules work, including <code class="structname">pg_stat_statements</code>.

  419.    </p><p>

  420.     Advisory locks work normally in recovery, including deadlock detection.

  421.     Note that advisory locks are never WAL logged, so it is impossible for

  422.     an advisory lock on either the primary or the standby to conflict with WAL

  423.     replay. Nor is it possible to acquire an advisory lock on the primary

  424.     and have it initiate a similar advisory lock on the standby. Advisory

  425.     locks relate only to the server on which they are acquired.

  426.    </p><p>

  427.     Trigger-based replication systems such as <span class="productname">Slony</span>,

  428.     <span class="productname">Londiste</span> and <span class="productname">Bucardo</span> won't run on the

  429.     standby at all, though they will run happily on the primary server as

  430.     long as the changes are not sent to standby servers to be applied.

  431.     WAL replay is not trigger-based so you cannot relay from the

  432.     standby to any system that requires additional database writes or

  433.     relies on the use of triggers.

  434.    </p><p>

  435.     New OIDs cannot be assigned, though some <acronym class="acronym">UUID</acronym> generators may still

  436.     work as long as they do not rely on writing new status to the database.

  437.    </p><p>

  438.     Currently, temporary table creation is not allowed during read only

  439.     transactions, so in some cases existing scripts will not run correctly.

  440.     This restriction might be relaxed in a later release. This is

  441.     both a SQL Standard compliance issue and a technical issue.

  442.    </p><p>

  443.     <code class="command">DROP TABLESPACE</code> can only succeed if the tablespace is empty.

  444.     Some standby users may be actively using the tablespace via their

  445.     <code class="varname">temp_tablespaces</code> parameter. If there are temporary files in the

  446.     tablespace, all active queries are canceled to ensure that temporary

  447.     files are removed, so the tablespace can be removed and WAL replay

  448.     can continue.

  449.    </p><p>

  450.     Running <code class="command">DROP DATABASE</code> or <code class="command">ALTER DATABASE ... SET

  451.     TABLESPACE</code> on the primary

  452.     will generate a WAL entry that will cause all users connected to that

  453.     database on the standby to be forcibly disconnected. This action occurs

  454.     immediately, whatever the setting of

  455.     <code class="varname">max_standby_streaming_delay</code>. Note that

  456.     <code class="command">ALTER DATABASE ... RENAME</code> does not disconnect users, which

  457.     in most cases will go unnoticed, though might in some cases cause a

  458.     program confusion if it depends in some way upon database name.

  459.    </p><p>

  460.     In normal (non-recovery) mode, if you issue <code class="command">DROP USER</code> or <code class="command">DROP ROLE</code>

  461.     for a role with login capability while that user is still connected then

  462.     nothing happens to the connected user - they remain connected. The user cannot

  463.     reconnect however. This behavior applies in recovery also, so a

  464.     <code class="command">DROP USER</code> on the primary does not disconnect that user on the standby.

  465.    </p><p>

  466.     The statistics collector is active during recovery. All scans, reads, blocks,

  467.     index usage, etc., will be recorded normally on the standby. Replayed

  468.     actions will not duplicate their effects on primary, so replaying an

  469.     insert will not increment the Inserts column of pg_stat_user_tables.

  470.     The stats file is deleted at the start of recovery, so stats from primary

  471.     and standby will differ; this is considered a feature, not a bug.

  472.    </p><p>

  473.     Autovacuum is not active during recovery.  It will start normally at the

  474.     end of recovery.

  475.    </p><p>

  476.     The background writer is active during recovery and will perform

  477.     restartpoints (similar to checkpoints on the primary) and normal block

  478.     cleaning activities. This can include updates of the hint bit

  479.     information stored on the standby server.

  480.     The <code class="command">CHECKPOINT</code> command is accepted during recovery,

  481.     though it performs a restartpoint rather than a new checkpoint.

  482.    </p></div><div class="sect2" id="HOT-STANDBY-PARAMETERS"><div class="titlepage"><div><div><h3 class="title">26.5.4. Hot Standby Parameter Reference</h3></div></div></div><p>

  483.     Various parameters have been mentioned above in

  484.     <a class="xref" href="hot-standby.html#HOT-STANDBY-CONFLICT" title="26.5.2. Handling Query Conflicts">Section 26.5.2</a> and

  485.     <a class="xref" href="hot-standby.html#HOT-STANDBY-ADMIN" title="26.5.3. Administrator's Overview">Section 26.5.3</a>.

  486.    </p><p>

  487.     On the primary, parameters <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> and

  488.     <a class="xref" href="runtime-config-replication.html#GUC-VACUUM-DEFER-CLEANUP-AGE">vacuum_defer_cleanup_age</a> can be used.

  489.     <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY">max_standby_archive_delay</a> and

  490.     <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY">max_standby_streaming_delay</a> have no effect if set on

  491.     the primary.

  492.    </p><p>

  493.     On the standby, parameters <a class="xref" href="runtime-config-replication.html#GUC-HOT-STANDBY">hot_standby</a>,

  494.     <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY">max_standby_archive_delay</a> and

  495.     <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY">max_standby_streaming_delay</a> can be used.

  496.     <a class="xref" href="runtime-config-replication.html#GUC-VACUUM-DEFER-CLEANUP-AGE">vacuum_defer_cleanup_age</a> has no effect

  497.     as long as the server remains in standby mode, though it will

  498.     become relevant if the standby becomes primary.

  499.    </p></div><div class="sect2" id="HOT-STANDBY-CAVEATS"><div class="titlepage"><div><div><h3 class="title">26.5.5. Caveats</h3></div></div></div><p>

  500.     There are several limitations of Hot Standby.

  501.     These can and probably will be fixed in future releases:

  502. 

  503.   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  504.      Full knowledge of running transactions is required before snapshots

  505.      can be taken. Transactions that use large numbers of subtransactions

  506.      (currently greater than 64) will delay the start of read only

  507.      connections until the completion of the longest running write transaction.

  508.      If this situation occurs, explanatory messages will be sent to the server log.

  509.     </p></li><li class="listitem"><p>

  510.      Valid starting points for standby queries are generated at each

  511.      checkpoint on the master. If the standby is shut down while the master

  512.      is in a shutdown state, it might not be possible to re-enter Hot Standby

  513.      until the primary is started up, so that it generates further starting

  514.      points in the WAL logs.  This situation isn't a problem in the most

  515.      common situations where it might happen. Generally, if the primary is

  516.      shut down and not available anymore, that's likely due to a serious

  517.      failure that requires the standby being converted to operate as

  518.      the new primary anyway.  And in situations where the primary is

  519.      being intentionally taken down, coordinating to make sure the standby

  520.      becomes the new primary smoothly is also standard procedure.

  521.     </p></li><li class="listitem"><p>

  522.      At the end of recovery, <code class="literal">AccessExclusiveLocks</code> held by prepared transactions

  523.      will require twice the normal number of lock table entries. If you plan

  524.      on running either a large number of concurrent prepared transactions

  525.      that normally take <code class="literal">AccessExclusiveLocks</code>, or you plan on having one

  526.      large transaction that takes many <code class="literal">AccessExclusiveLocks</code>, you are

  527.      advised to select a larger value of <code class="varname">max_locks_per_transaction</code>,

  528.      perhaps as much as twice the value of the parameter on

  529.      the primary server. You need not consider this at all if

  530.      your setting of <code class="varname">max_prepared_transactions</code> is 0.

  531.     </p></li><li class="listitem"><p>

  532.      The Serializable transaction isolation level is not yet available in hot

  533.      standby.  (See <a class="xref" href="transaction-iso.html#XACT-SERIALIZABLE" title="13.2.3. Serializable Isolation Level">Section 13.2.3</a> and

  534.      <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.)

  535.      An attempt to set a transaction to the serializable isolation level in

  536.      hot standby mode will generate an error.

  537.     </p></li></ul></div><p>

  538. 

  539.    </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="log-shipping-alternative.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="high-availability.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="monitoring.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">26.4. Alternative Method for Log Shipping </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 27. Monitoring Database Activity</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1