From PostgreSQL wiki
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 email@example.com or firstname.lastname@example.org, though please also copy simon@2ndQuadrant.com on those mails.
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:
- Create the master database
- Configure the master for WAL archiving
- Create a backup of the master
- 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:
#!/bin/sh psql postgres -c "select pg_start_backup('backup')" cp -pr hotstandby1/ hotstandby2 psql postgres -c "select pg_stop_backup()" rm hotstandby2/postmaster.pid 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
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:
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:
Look at pg_locks and pg_stat_activity.