<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Hot Standby</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.5alpha3 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Backup and Restore" HREF="backup.html"><LINK REL="PREVIOUS" TITLE="Warm Standby Servers for High Availability" HREF="warm-standby.html"><LINK REL="NEXT" TITLE="Migration Between Releases" HREF="migration.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2010-04-15T09:31:23"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" ><A HREF="index.html" >PostgreSQL 8.5alpha3 Documentation</A ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="Warm Standby Servers for High Availability" HREF="warm-standby.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="Backup and Restore" HREF="backup.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 24. Backup and Restore</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A TITLE="Backup and Restore" HREF="backup.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A TITLE="Migration Between Releases" HREF="migration.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="HOT-STANDBY" >24.5. Hot Standby</A ></H1 ><P > Hot Standby is the term used to describe the ability to connect to the server and run queries while the server is in archive recovery. This is useful for both log shipping replication and for restoring a backup to an exact state with great precision. The term Hot Standby also refers to the ability of the server to move from recovery through to normal running while users continue running queries and/or continue their connections. </P ><P > Running queries in recovery is in many ways the same as normal running though there are a large number of usage and administrative points to note. </P ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="HOT-STANDBY-USERS" >24.5.1. User's Overview</A ></H2 ><P > Users can connect to the database while the server is in recovery and perform read-only queries. Read-only access to catalogs and views will also occur as normal. </P ><P > The data on the standby takes some time to arrive from the primary server so there will be a measurable delay between primary and standby. Running the same query nearly simultaneously on both primary and standby might therefore return differing results. We say that data on the standby is eventually consistent with the primary. Queries executed on the standby will be correct with regard to the transactions that had been recovered at the start of the query, or start of first statement, in the case of serializable transactions. In comparison with the primary, the standby returns query results that could have been obtained on the primary at some exact moment in the past. </P ><P > When a transaction is started in recovery, the parameter <TT CLASS="VARNAME" >transaction_read_only</TT > will be forced to be true, regardless of the <TT CLASS="VARNAME" >default_transaction_read_only</TT > setting in <TT CLASS="FILENAME" >postgresql.conf</TT >. It can't be manually set to false either. As a result, all transactions started during recovery will be limited to read-only actions only. In all other ways, connected sessions will appear identical to sessions initiated during normal processing mode. There are no special commands required to initiate a connection at this time, so all interfaces work normally without change. After recovery finishes, the session will allow normal read-write transactions at the start of the next transaction, if these are requested. </P ><P > Read-only here means "no writes to the permanent database tables". There are no problems with queries that make use of transient sort and work files. </P ><P > The following actions are allowed <P ></P ></P><UL ><LI ><P > Query access - SELECT, COPY TO including views and SELECT RULEs </P ></LI ><LI ><P > Cursor commands - DECLARE, FETCH, CLOSE, </P ></LI ><LI ><P > Parameters - SHOW, SET, RESET </P ></LI ><LI ><P > Transaction management commands <P ></P ></P><UL ><LI ><P > BEGIN, END, ABORT, START TRANSACTION </P ></LI ><LI ><P > SAVEPOINT, RELEASE, ROLLBACK TO SAVEPOINT </P ></LI ><LI ><P > EXCEPTION blocks and other internal subtransactions </P ></LI ></UL ><P> </P ></LI ><LI ><P > LOCK TABLE, though only when explicitly in one of these modes: ACCESS SHARE, ROW SHARE or ROW EXCLUSIVE. </P ></LI ><LI ><P > Plans and resources - PREPARE, EXECUTE, DEALLOCATE, DISCARD </P ></LI ><LI ><P > Plugins and extensions - LOAD </P ></LI ></UL ><P> </P ><P > These actions produce error messages <P ></P ></P><UL ><LI ><P > Data Manipulation Language (DML) - INSERT, UPDATE, DELETE, COPY FROM, TRUNCATE. Note that there are no allowed actions that result in a trigger being executed during recovery. </P ></LI ><LI ><P > Data Definition Language (DDL) - CREATE, DROP, ALTER, COMMENT. This also applies to temporary tables currently because currently their definition causes writes to catalog tables. </P ></LI ><LI ><P > SELECT ... FOR SHARE | UPDATE which cause row locks to be written </P ></LI ><LI ><P > RULEs on SELECT statements that generate DML commands. </P ></LI ><LI ><P > LOCK TABLE, in short default form, since it requests ACCESS EXCLUSIVE MODE. LOCK TABLE that explicitly requests a mode higher than ROW EXCLUSIVE MODE. </P ></LI ><LI ><P > Transaction management commands that explicitly set non-read only state <P ></P ></P><UL ><LI ><P > BEGIN READ WRITE, START TRANSACTION READ WRITE </P ></LI ><LI ><P > SET TRANSACTION READ WRITE, SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE </P ></LI ><LI ><P > SET transaction_read_only = off </P ></LI ></UL ><P> </P ></LI ><LI ><P > Two-phase commit commands - PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED because even read-only transactions need to write WAL in the prepare phase (the first phase of two phase commit). </P ></LI ><LI ><P > sequence update - nextval() </P ></LI ><LI ><P > LISTEN, UNLISTEN, NOTIFY since they currently write to system tables </P ></LI ></UL ><P> </P ><P > Note that current behaviour of read only transactions when not in recovery is to allow the last two actions, so there are small and subtle differences in behaviour between read-only transactions run on standby and during normal running. It is possible that the restrictions on LISTEN, UNLISTEN, NOTIFY and temporary tables may be lifted in a future release, if their internal implementation is altered to make this possible. </P ><P > If failover or switchover occurs the database will switch to normal processing mode. Sessions will remain connected while the server changes mode. Current transactions will continue, though will remain read-only. After recovery is complete, it will be possible to initiate read-write transactions. </P ><P > Users will be able to tell whether their session is read-only by issuing SHOW transaction_read_only. In addition a set of functions <A HREF="functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE" >Table 9-57</A > allow users to access information about Hot Standby. These allow you to write functions that are aware of the current state of the database. These can be used to monitor the progress of recovery, or to allow you to write complex programs that restore the database to particular states. </P ><P > In recovery, transactions will not be permitted to take any table lock higher than RowExclusiveLock. In addition, transactions may never assign a TransactionId and may never write WAL. Any <TT CLASS="COMMAND" >LOCK TABLE</TT > command that runs on the standby and requests a specific lock mode higher than ROW EXCLUSIVE MODE will be rejected. </P ><P > In general queries will not experience lock conflicts with the database changes made by recovery. This is becase recovery follows normal concurrency control mechanisms, known as <ACRONYM CLASS="ACRONYM" >MVCC</ACRONYM >. There are some types of change that will cause conflicts, covered in the following section. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="HOT-STANDBY-CONFLICT" >24.5.2. Handling query conflicts</A ></H2 ><P > The primary and standby nodes are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them. The easiest conflict to understand is performance: if a huge data load is taking place on the primary then this will generate a similar stream of WAL records on the standby, so standby queries may contend for system resources, such as I/O. </P ><P > There are also additional types of conflict that can occur with Hot Standby. These conflicts are <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >hard conflicts</I ></SPAN > in the sense that we may need to cancel queries and in some cases disconnect sessions to resolve them. The user is provided with a number of optional ways to handle these conflicts, though we must first understand the possible reasons behind a conflict. <P ></P ></P><UL ><LI ><P > Access Exclusive Locks from primary node, including both explicit LOCK commands and various kinds of DDL action </P ></LI ><LI ><P > Dropping tablespaces on the primary while standby queries are using those tablespaces for temporary work files (work_mem overflow) </P ></LI ><LI ><P > Dropping databases on the primary while that role is connected on standby. </P ></LI ><LI ><P > Waiting to acquire buffer cleanup locks (for which there is no time out) </P ></LI ><LI ><P > Early cleanup of data still visible to the current query's snapshot </P ></LI ></UL ><P> </P ><P > Some WAL redo actions will be for DDL actions. These DDL actions are repeating actions that have already committed on the primary node, so they must not fail on the standby node. These DDL locks take priority and will automatically *cancel* any read-only transactions that get in their way, after a grace period. This is similar to the possibility of being canceled by the deadlock detector, but in this case the standby process always wins, since the replayed actions must not fail. This also ensures that replication doesn't fall behind while we wait for a query to complete. Again, we assume that the standby is there for high availability purposes primarily. </P ><P > An example of the above would be an Administrator on Primary server runs a <TT CLASS="COMMAND" >DROP TABLE</TT > on a table that's currently being queried in the standby server. Clearly the query cannot continue if we let the <TT CLASS="COMMAND" >DROP TABLE</TT > proceed. If this situation occurred on the primary, the <TT CLASS="COMMAND" >DROP TABLE</TT > would wait until the query has finished. When the query is on the standby and the <TT CLASS="COMMAND" >DROP TABLE</TT > is on the primary, the primary doesn't have information about which queries are running on the standby and so the query does not wait on the primary. The WAL change records come through to the standby while the standby query is still running, causing a conflict. </P ><P > The most common reason for conflict between standby queries and WAL redo is "early cleanup". Normally, <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > allows cleanup of old row versions when there are no users who may need to see them to ensure correct visibility of data (the heart of MVCC). If there is a standby query that has been running for longer than any query on the primary then it is possible for old row versions to be removed by either a vacuum or HOT. This will then generate WAL records that, if applied, would remove data on the standby that might *potentially* be required by the standby query. In more technical language, the primary's xmin horizon is later than the standby's xmin horizon, allowing dead rows to be removed. </P ><P > Experienced users should note that both row version cleanup and row version freezing will potentially conflict with recovery queries. Running a manual <TT CLASS="COMMAND" >VACUUM FREEZE</TT > is likely to cause conflicts even on tables with no updated or deleted rows. </P ><P > We have a number of choices for resolving query conflicts. The default is that we wait and hope the query completes. The server will wait automatically until the lag between primary and standby is at most <TT CLASS="VARNAME" >max_standby_delay</TT > seconds. Once that grace period expires, we take one of the following actions: <P ></P ></P><UL ><LI ><P > If the conflict is caused by a lock, we cancel the conflicting standby transaction immediately. If the transaction is idle-in-transaction then currently we abort the session instead, though this may change in the future. </P ></LI ><LI ><P > If the conflict is caused by cleanup records we tell the standby query that a conflict has occurred and that it must cancel itself to avoid the risk that it silently fails to read relevant data because that data has been removed. (This is regrettably very similar to the much feared and iconic error message "snapshot too old"). Some cleanup records only cause conflict with older queries, though some types of cleanup record affect all queries. </P ><P > If cancellation does occur, the query and/or transaction can always be re-executed. The error is dynamic and will not necessarily occur the same way if the query is executed again. </P ></LI ></UL ><P> </P ><P > <TT CLASS="VARNAME" >max_standby_delay</TT > is set in <TT CLASS="FILENAME" >postgresql.conf</TT >. The parameter applies to the server as a whole so if the delay is all used up by a single query then there may be little or no waiting for queries that follow immediately, though they will have benefited equally from the initial waiting period. The server may take time to catch up again before the grace period is available again, though if there is a heavy and constant stream of conflicts it may seldom catch up fully. </P ><P > Users should be clear that tables that are regularly and heavily updated on primary server will quickly cause cancellation of longer running queries on the standby. In those cases <TT CLASS="VARNAME" >max_standby_delay</TT > can be considered somewhat but not exactly the same as setting <TT CLASS="VARNAME" >statement_timeout</TT >. </P ><P > Other remedial actions exist if the number of cancellations is unacceptable. The first option is to connect to primary server and keep a query active for as long as we need to run queries on the standby. This guarantees that a WAL cleanup record is never generated and we don't ever get query conflicts as described above. This could be done using contrib/dblink and pg_sleep(), or via other mechanisms. If you do this, you should note that this will delay cleanup of dead rows by vacuum or HOT and many people may find this undesirable. However, we should remember that primary and standby nodes are linked via the WAL, so this situation is no different to the case where we ran the query on the primary node itself except we have the benefit of off-loading the execution onto the standby. </P ><P > It is also possible to set <TT CLASS="VARNAME" >vacuum_defer_cleanup_age</TT > on the primary to defer the cleanup of records by autovacuum, vacuum and HOT. This may allow more time for queries to execute before they are cancelled on the standby, without the need for setting a high <TT CLASS="VARNAME" >max_standby_delay</TT >. </P ><P > Three-way deadlocks are possible between AccessExclusiveLocks arriving from the primary, cleanup WAL records that require buffer cleanup locks and user requests that are waiting behind replayed AccessExclusiveLocks. Deadlocks are currently resolved by the cancellation of user processes that would need to wait on a lock. This is heavy-handed and generates more query cancellations than we need to, though does remove the possibility of deadlock. This behaviour is expected to improve substantially for the main release version of 8.5. </P ><P > Dropping tablespaces or databases is discussed in the administrator's section since they are not typical user situations. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="HOT-STANDBY-ADMIN" >24.5.3. Administrator's Overview</A ></H2 ><P > If there is a <TT CLASS="FILENAME" >recovery.conf</TT > file present the server will start in Hot Standby mode by default, though <TT CLASS="VARNAME" >recovery_connections</TT > can be disabled via <TT CLASS="FILENAME" >postgresql.conf</TT >, if required. The server may take some time to enable recovery connections since the server must first complete sufficient recovery to provide a consistent state against which queries can run before enabling read only connections. Look for these messages in the server logs </P><PRE CLASS="PROGRAMLISTING" >LOG: initializing recovery connections ... then some time later ... LOG: consistent recovery state reached LOG: database system is ready to accept read only connections</PRE ><P> Consistency information is recorded once per checkpoint on the primary, as long as <TT CLASS="VARNAME" >recovery_connections</TT > is enabled (on the primary). If this parameter is disabled, it will not be possible to enable recovery connections on the standby. The consistent state can also be delayed in the presence of both of these conditions <P ></P ></P><UL ><LI ><P > a write transaction has more than 64 subtransactions </P ></LI ><LI ><P > very long-lived write transactions </P ></LI ></UL ><P> If you are running file-based log shipping ("warm standby"), you may need to wait until the next WAL file arrives, which could be as long as the <TT CLASS="VARNAME" >archive_timeout</TT > setting on the primary. </P ><P > The setting of some parameters on the standby will need reconfiguration if they have been changed on the primary. The value on the standby must be equal to or greater than the value on the primary. If these parameters are not set high enough then the standby will not be able to track work correctly from recovering transactions. If these values are set too low the the server will halt. Higher values can then be supplied and the server restarted to begin recovery again. <P ></P ></P><UL ><LI ><P > <TT CLASS="VARNAME" >max_connections</TT > </P ></LI ><LI ><P > <TT CLASS="VARNAME" >max_prepared_transactions</TT > </P ></LI ><LI ><P > <TT CLASS="VARNAME" >max_locks_per_transaction</TT > </P ></LI ></UL ><P> </P ><P > It is important that the administrator consider the appropriate setting of <TT CLASS="VARNAME" >max_standby_delay</TT >, set in <TT CLASS="FILENAME" >postgresql.conf</TT >. There is no optimal setting and should be set according to business priorities. For example if the server is primarily tasked as a High Availability server, then you may wish to lower <TT CLASS="VARNAME" >max_standby_delay</TT > or even set it to zero, though that is a very aggressive setting. If the standby server is tasked as an additional server for decision support queries then it may be acceptable to set this to a value of many hours (in seconds). It is also possible to set <TT CLASS="VARNAME" >max_standby_delay</TT > to -1 which means wait forever for queries to complete, if there are conflicts; this will be useful when performing an archive recovery from a backup. </P ><P > Transaction status "hint bits" written on primary are not WAL-logged, so data on standby will likely re-write the hints again on the standby. Thus the main database blocks will produce write I/Os even though all users are read-only; no changes have occurred to the data values themselves. Users will be able to write large sort temp files and re-generate relcache info files, so there is no part of the database that is truly read-only during hot standby mode. There is no restriction on the use of set returning functions, or other users of tuplestore/tuplesort code. Note also that writes to remote databases will still be possible, even though the transaction is read-only locally. </P ><P > The following types of administrator command are not accepted during recovery mode <P ></P ></P><UL ><LI ><P > Data Definition Language (DDL) - e.g. CREATE INDEX </P ></LI ><LI ><P > Privilege and Ownership - GRANT, REVOKE, REASSIGN </P ></LI ><LI ><P > Maintenance commands - ANALYZE, VACUUM, CLUSTER, REINDEX </P ></LI ></UL ><P> </P ><P > Note again that some of these commands are actually allowed during "read only" mode transactions on the primary. </P ><P > As a result, you cannot create additional indexes that exist solely on the standby, nor can statistics that exist solely on the standby. If these administrator commands are needed they should be executed on the primary so that the changes will propagate through to the standby. </P ><P > <CODE CLASS="FUNCTION" >pg_cancel_backend()</CODE > will work on user backends, but not the Startup process, which performs recovery. pg_stat_activity does not show an entry for the Startup process, nor do recovering transactions show as active. As a result, pg_prepared_xacts is always empty during recovery. If you wish to resolve in-doubt prepared transactions then look at pg_prepared_xacts on the primary and issue commands to resolve those transactions there. </P ><P > pg_locks will show locks held by backends as normal. pg_locks also shows a virtual transaction managed by the Startup process that owns all AccessExclusiveLocks held by transactions being replayed by recovery. Note that Startup process does not acquire locks to make database changes and thus locks other than AccessExclusiveLocks do not show in pg_locks for the Startup process, they are just presumed to exist. </P ><P > <SPAN CLASS="PRODUCTNAME" >check_pgsql</SPAN > will work, but it is very simple. <SPAN CLASS="PRODUCTNAME" >check_postgres</SPAN > will also work, though many some actions could give different or confusing results. e.g. last vacuum time will not be maintained for example, since no vacuum occurs on the standby (though vacuums running on the primary do send their changes to the standby). </P ><P > WAL file control commands will not work during recovery e.g. <CODE CLASS="FUNCTION" >pg_start_backup</CODE >, <CODE CLASS="FUNCTION" >pg_switch_xlog</CODE > etc.. </P ><P > Dynamically loadable modules work, including pg_stat_statements. </P ><P > Advisory locks work normally in recovery, including deadlock detection. Note that advisory locks are never WAL logged, so it is not possible for an advisory lock on either the primary or the standby to conflict with WAL replay. Nor is it possible to acquire an advisory lock on the primary and have it initiate a similar advisory lock on the standby. Advisory locks relate only to a single server on which they are acquired. </P ><P > Trigger-based replication systems such as <SPAN CLASS="PRODUCTNAME" >Slony</SPAN >, <SPAN CLASS="PRODUCTNAME" >Londiste</SPAN > and <SPAN CLASS="PRODUCTNAME" >Bucardo</SPAN > won't run on the standby at all, though they will run happily on the primary server as long as the changes are not sent to standby servers to be applied. WAL replay is not trigger-based so you cannot relay from the standby to any system that requires additional database writes or relies on the use of triggers. </P ><P > New oids cannot be assigned, though some <ACRONYM CLASS="ACRONYM" >UUID</ACRONYM > generators may still work as long as they do not rely on writing new status to the database. </P ><P > Currently, temp table creation is not allowed during read only transactions, so in some cases existing scripts will not run correctly. It is possible we may relax that restriction in a later release. This is both a SQL Standard compliance issue and a technical issue. </P ><P > <TT CLASS="COMMAND" >DROP TABLESPACE</TT > can only succeed if the tablespace is empty. Some standby users may be actively using the tablespace via their <TT CLASS="VARNAME" >temp_tablespaces</TT > parameter. If there are temp files in the tablespace we currently cancel all active queries to ensure that temp files are removed, so that we can remove the tablespace and continue with WAL replay. </P ><P > Running <TT CLASS="COMMAND" >DROP DATABASE</TT >, <TT CLASS="COMMAND" >ALTER DATABASE ... SET TABLESPACE</TT >, or <TT CLASS="COMMAND" >ALTER DATABASE ... RENAME</TT > on primary will generate a log message that will cause all users connected to that database on the standby to be forcibly disconnected, once <TT CLASS="VARNAME" >max_standby_delay</TT > has been reached. </P ><P > In normal running, if you issue <TT CLASS="COMMAND" >DROP USER</TT > or <TT CLASS="COMMAND" >DROP ROLE</TT > for a role with login capability while that user is still connected then nothing happens to the connected user - they remain connected. The user cannot reconnect however. This behaviour applies in recovery also, so a <TT CLASS="COMMAND" >DROP USER</TT > on the primary does not disconnect that user on the standby. </P ><P > Stats collector is active during recovery. All scans, reads, blocks, index usage etc will all be recorded normally on the standby. Replayed actions will not duplicate their effects on primary, so replaying an insert will not increment the Inserts column of pg_stat_user_tables. The stats file is deleted at start of recovery, so stats from primary and standby will differ; this is considered a feature not a bug. </P ><P > Autovacuum is not active during recovery, though will start normally at the end of recovery. </P ><P > Background writer is active during recovery and will perform restartpoints (similar to checkpoints on primary) and normal block cleaning activities. The <TT CLASS="COMMAND" >CHECKPOINT</TT > command is accepted during recovery, though performs a restartpoint rather than a new checkpoint. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="HOT-STANDBY-PARAMETERS" >24.5.4. Hot Standby Parameter Reference</A ></H2 ><P > Various parameters have been mentioned above in the <A HREF="hot-standby.html#HOT-STANDBY-ADMIN" >Section 24.5.3</A > and <A HREF="hot-standby.html#HOT-STANDBY-CONFLICT" >Section 24.5.2</A > sections. </P ><P > On the primary, parameters <TT CLASS="VARNAME" >recovery_connections</TT > and <TT CLASS="VARNAME" >vacuum_defer_cleanup_age</TT > can be used to enable and control the primary server to assist the successful configuration of Hot Standby servers. <TT CLASS="VARNAME" >max_standby_delay</TT > has no effect if set on the primary. </P ><P > On the standby, parameters <TT CLASS="VARNAME" >recovery_connections</TT > and <TT CLASS="VARNAME" >max_standby_delay</TT > can be used to enable and control Hot Standby. standby server to assist the successful configuration of Hot Standby servers. <TT CLASS="VARNAME" >vacuum_defer_cleanup_age</TT > has no effect during recovery. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="HOT-STANDBY-CAVEATS" >24.5.5. Caveats</A ></H2 ><P > At this writing, there are several limitations of Hot Standby. These can and probably will be fixed in future releases: <P ></P ></P><UL ><LI ><P > Operations on hash indexes are not presently WAL-logged, so replay will not update these indexes. Hash indexes will not be used for query plans during recovery. </P ></LI ><LI ><P > Full knowledge of running transactions is required before snapshots may be taken. Transactions that take use large numbers of subtransactions (currently greater than 64) will delay the start of read only connections until the completion of the longest running write transaction. If this situation occurs explanatory messages will be sent to server log. </P ></LI ><LI ><P > Valid starting points for recovery connections are generated at each checkpoint on the master. If the standby is shutdown while the master is in a shutdown state it may not be possible to re-enter Hot Standby until the primary is started up so that it generates further starting points in the WAL logs. This is not considered a serious issue because the standby is usually switched into the primary role while the first node is taken down. </P ></LI ><LI ><P > At the end of recovery, AccessExclusiveLocks held by prepared transactions will require twice the normal number of lock table entries. If you plan on running either a large number of concurrent prepared transactions that normally take AccessExclusiveLocks, or you plan on having one large transaction that takes many AccessExclusiveLocks then you are advised to select a larger value of <TT CLASS="VARNAME" >max_locks_per_transaction</TT >, up to, but never more than twice the value of the parameter setting on the primary server in rare extremes. You need not consider this at all if your setting of <TT CLASS="VARNAME" >max_prepared_transactions</TT > is <TT CLASS="LITERAL" >0</TT >. </P ></LI ></UL ><P> </P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="warm-standby.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="migration.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Warm Standby Servers for High Availability</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="backup.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Migration Between Releases</TD ></TR ></TABLE ></DIV ></BODY ></HTML >