The user is provided with several ways to handle these conflicts. These conflicts are hard conflicts in the sense that queries might need to be canceled and, in some cases, sessions disconnected to resolve them. There are also additional types of conflict that can occur with Hot Standby. 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. As a result, there is potential for negative interactions or conflicts between them. Actions on the primary will have an effect on the standby. The primary and standby servers are in many ways loosely connected. 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. These allow you to write programs that are aware of the current state of the database. In addition, a set of functions ( Table 9-79) allow users to access information about the standby server. Users will be able to tell whether their session is read-only by issuing SHOW transaction_read_only. Once hot standby finishes, it will be possible to initiate read-write transactions (even from a session begun during hot standby). Sessions will remain connected while the server changes mode. If failover or switchover occurs, the database will switch to normal processing mode. But as long as no attempt is made to modify the database, connections during hot standby will act much like any other database connection. It is possible that some of these restrictions might be loosened in a future release.ĭuring hot standby, the parameter transaction_read_only is always true and may not be changed. In normal operation, "read-only" transactions are allowed to use LISTEN and NOTIFY, so Hot Standby sessions operate under slightly tighter restrictions than ordinary read-only sessions. 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). SET TRANSACTION READ WRITE, SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE Transaction management commands that explicitly set non-read-only state:īEGIN READ WRITE, START TRANSACTION READ WRITE LOCK in short default form, since it requests ACCESS EXCLUSIVE MODE. LOCK that explicitly requests a mode higher than ROW EXCLUSIVE MODE. Rules on SELECT statements that generate DML commands. FOR SHARE | UPDATE, because row locks cannot be taken without updating the underlying data files. This restriction applies even to temporary tables, because carrying out these operations would require updating the system catalog tables. This restriction applies even to temporary tables, because table rows cannot be read or written without assigning a transaction ID, which is currently not possible in a Hot Standby environment.ĭata Definition Language (DDL) - CREATE, DROP, ALTER, COMMENT. Note that there are no allowed actions that result in a trigger being executed during recovery. Therefore, the following actions will produce error messages:ĭata Manipulation Language (DML) - INSERT, UPDATE, DELETE, COPY FROM, TRUNCATE. Transactions started during hot standby will never be assigned a transaction ID and cannot write to the system write-ahead log. Plans and resources - PREPARE, EXECUTE, DEALLOCATE, DISCARD LOCK TABLE, though only when explicitly in one of these modes: ACCESS SHARE, ROW SHARE or ROW EXCLUSIVE. SAVEPOINT, RELEASE, ROLLBACK TO SAVEPOINTĮXCEPTION blocks and other internal subtransactions Transactions started during hot standby may issue the following commands: Snapshots may be taken at the start of each query or at the start of each transaction, depending on the current transaction isolation level. Once the commit record for a transaction is replayed on the standby, the changes made by that transaction will be visible to any new snapshots taken on the standby. We say that data on the standby is eventually consistent with the primary. Running the same query nearly simultaneously on both primary and standby might therefore return differing results. 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. All such connections are strictly read-only not even temporary tables may be written. When the hot_standby parameter is set to true on a standby server, it will begin accepting connections once the recovery has brought the system to a consistent state.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |