Replication & Upgrades - PostgreSQL 9 Administration

Database Reference

In-Depth Information

If you attempt to run a non-read only query, then you will receive an error marked with

SQLSTATE 25006 READ ONLY TRANSACTION . That might be used to re-direct SQL to the

Master, where it can execute successfully.

How it works...

On the Standby, node changes from the Master are read from the transaction log and

applied to the Standby database. Hot Standby works by emulating running transactions from

the Master, so that queries on the Standby have the visibility information they need to fully

respect MVCC. This makes Hot Standby mode particularly suitable for serving a large workload

of short/fast SELECT queries. If the workload is consistently short, then few conflicts will delay

the Standby, and the server will run smoothly.

When running longer selects in reporting mode, then you will probably need to play with

the configuration settings to suit you, or use a utility, such as the repmgr, to minimize query

conflicts from cleanup records.

There's more...

Changes made by a transaction on the Master will not be visible until the commit is applied

onto the Standby. So, for example, we have a Master and a Standby with a replication delay of

four seconds between them. A long-running transaction may take one hour to make changes

on the Master. How long does it take before those changes are visible on the Standby? With

Hot Standby, the answer is four seconds after the commit on the Master. This is because the

changes made during the transaction on the Master have been streamed while the transaction

is still in progress, and in most cases already applied on the Standby when the commit record

arrives. Note that this is a very different situation for trigger-based replication, such as Slony

and Londiste, where the data does not start transferring until after a transaction commits on

the Master. So, with trigger-based replication, the data would likely only become visible many

minutes after the commit on the Master. Which means that with trigger-based replication the

effective apply delay also depends upon, transaction duration on the Master.

Hot Standby can also be used when running a Point - in - Time Recovery , so the WAL records

applied to the database need not be arriving immediately from a live database server. We

would just use file-based recovery in that case, not streaming replication.

Also note that a Standby node can be shutdown and restarted normally using the commands

already described in earlier chapters.