The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 274 pages ISBN 9781906966072 RRP £9.95 ($14.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 3 Buy the book here! >>> only $14.95

11.5.1 User's Overview

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. 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 - SELECT, COPY TO
• Cursor commands - DECLARE, FETCH, CLOSE
• Parameters - SHOW, SET, RESET
• Transaction management commands
  • BEGIN, END, ABORT, START TRANSACTION
  • SAVEPOINT, RELEASE, ROLLBACK TO SAVEPOINT
  • EXCEPTION blocks and other internal subtransactions
• LOCK TABLE, though only when explicitly in one of these modes: ACCESS SHARE, ROW SHARE or ROW EXCLUSIVE.
• Plans and resources - PREPARE, EXECUTE, DEALLOCATE, DISCARD
• Plugins and extensions - LOAD

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) - INSERT, UPDATE, DELETE, COPY FROM, 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) - CREATE, DROP, ALTER, 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.
• Rules on SELECT statements that generate DML commands.
• LOCK that explicitly requests a mode higher than ROW EXCLUSIVE MODE.
• LOCK in 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 - 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).
• Sequence updates - nextval(), setval()
• LISTEN, UNLISTEN, NOTIFY

In normal operation, “read-only” transactions are allowed to update sequences and to use LISTEN, UNLISTEN, and 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 hot standby).

Users will be able to tell whether their session is read-only by issuing 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.