|The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 274 pages
RRP £9.95 ($14.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
11.5.1 User's Overview
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. All such connections are
strictly read-only; not even temporary tables may be written.
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. 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. Snapshots may be taken at the start of each query or at the start of each transaction, depending on the current transaction isolation level. For more details, see Volume 1A: 11.2 Transaction Isolation.
Transactions started during hot standby may issue the following commands:
Query access -
Cursor commands -
Transaction management commands
ROLLBACK TO SAVEPOINT
EXCEPTIONblocks and other internal subtransactions
LOCK TABLE, though only when explicitly in one of these modes:
Plans and resources -
Plugins and extensions -
Transactions started during hot standby will never be assigned a transaction ID and cannot write to the system write-ahead log. Therefore, the following actions will produce error messages:
Data Manipulation Language (DML) -
TRUNCATE. Note that there are no allowed actions that result in a trigger being executed during recovery. 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.
Data Definition Language (DDL) -
COMMENT. This restriction applies even to temporary tables, because carrying out these operations would require updating the system catalog tables.
SELECT ... FOR SHARE | UPDATE, because row locks cannot be taken without updating the underlying data files.
SELECTstatements that generate DML commands.
LOCKthat explicitly requests a mode higher than
ROW EXCLUSIVE MODE.
LOCKin short default form, since it requests
ACCESS EXCLUSIVE MODE.
Transaction management commands that explicitly set non-read-only state:
BEGIN READ WRITE,
START TRANSACTION READ WRITE
SET TRANSACTION READ WRITE,
SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE
SET transaction_read_only = off
Two-phase commit commands -
ROLLBACK PREPAREDbecause even read-only transactions need to write WAL in the prepare phase (the first phase of two phase commit).
Sequence updates -
In normal operation, “read-only” transactions are allowed to
update sequences and to use
NOTIFY, so Hot Standby sessions operate under slightly tighter
restrictions than ordinary read-only sessions. It is possible that some
of these restrictions might be loosened in a future release.
During hot standby, the parameter
transaction_read_only is always
true and may not be changed. But as long as no attempt is made to modify
the database, connections during hot standby will act much like any other
database connection. If failover or switchover occurs, the database will
switch to normal processing mode. Sessions will remain connected while the
server changes mode. Once hot standby finishes, it will be possible to
initiate read-write transactions (even from a session begun during
Users will be able to tell whether their session is read-only by
SHOW transaction_read_only. In addition, a set of
functions ( Volume 1A: Table 7-57) allow users to
access information about the standby server. These allow you to write
programs 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.
|ISBN 9781906966072||The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide||See the print edition|