Hot Standby

From PostgreSQL wiki

Revision as of 17:06, 18 May 2012 by Boshomi (Talk | contribs)

Jump to: navigation, search

Hot Standby is the name for the capability to run queries on a database that is currently performing archive recovery. Log Shipping replication allows you to create one or more standby nodes that are replicas of the primary node (or master node). Standby nodes can then be used for read-only query access.

Hot Standby has been developed for inclusion in PostgreSQL 9.0 by Simon Riggs of 2ndQuadrant via public sponsorship.

The feature is now committed to CVS and is included as of PostgreSQL 8.5 Alpha3, see Hot Standby docs for full details.

This feature will augment the capabilities of streaming replication that is also planned for inclusion in r9.0, though it does not rely on it and has minimal interaction with it.


Questions, Issues, Bugs

If you have a Question, an Issue or wish to report a Bug then you are welcome to do that via or, though please also copy on those mails.

Quick Start

There is also a more extensive Binary Replication Tutorial

Setting up Hot Standby is much the same as Warm Standby, with a few extra parameters.

The most basic set of instructions you'll need are as follows:

  1. Create the master database
  2. Configure the master for WAL archiving
  3. Create a backup of the master
  4. Start up the hot standby instance

1. Create the master database

Use initdb to create the master database.

2. Configure the master for WAL archiving

The most limited set of parameters you'll need to change are:

wal_level = hot_standby
archive_mode = on
archive_command = 'cp -i %p /path/to/archive/%f'

Start the master database.

3. Create a backup of the master

If you're setting this up for testing purposes on the same machine, a script as follows will probably work for you:


psql postgres -c "select pg_start_backup('backup')"
cp -pr hotstandby1/ hotstandby2 
psql postgres -c "select pg_stop_backup()"
rm hotstandby2/
rm -r hotstandby2/pg_xlog/*

echo 'hot_standby = on' >> hotstandby2/postgresql.conf
echo 'port = 5433' >> hotstandby2/postgresql.conf

echo "standby_mode = 'on'" >> hotstandby2/recovery.conf
echo "restore_command = 'cp -i /path/to/archive/%f %p'" >> hotstandby2/recovery.conf

YMMV with that - if you're shipping between two distinct servers or VMs, you'll need to configure your master server so that the WAL is shipped with something like RSYNC.

4. Start your standby server

Start up your standby server with pg_ctl or equivalent startup command for your operating system!

pg_ctl -D hotstandby2 -l hotstandby2.log start

Diagnosing problems

There are sometimes reasons why you cannot connect. Check the log for messages.

First, check that you are connected to the correct server and that the server is in recovery. You can check this by looking at "ps" output and looking for a "startup process". If you can't find one, you aren't in recovery.

If you can connect, check you are in recovery by running:

   SELECT pg_is_in_recovery();

If you think you should be able to see an object or some data, check that the standby has processed all outstanding WAL. It may just be the standby is lagging behind.

Look at the current snapshot using:

   SELECT txid_current_snapshot();

Look at pg_locks and pg_stat_activity.

Current TODO

Latest TODO

Personal tools