Hot Standby

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 was developed for inclusion in PostgreSQL 9.0 by Simon Riggs of 2ndQuadrant via public sponsorship.

The feature was committed in PostgreSQL 8.5 Alpha3 and is available in PostgreSQL 9.0 and above. See the Hot Standby docs for full details.

This feature augments streaming replication feature that was introduced in PostgreSQL 9.1, though it does not rely on it and has minimal interaction with it.

Questions, issues and bugs

If you have a question/issue or wish to report a bug then you are welcome to do that via the pgsql-bugs or pgsql-hackers mailing lists. Please also copy simon@2ndQuadrant.com on hot standby related mail.

Quick Start

There is also a more extensive Binary Replication Tutorial, but your best starting point is the PostgreSQL documentation on hot standby.

Setting up Hot Standby is much the same as Warm Standby, with a few extra parameters. It is strongly recommended that you read the PostgreSQL documentation on hot standby for your PostgreSQL version rather than relying on this quick-start guide.

The most basic set of instructions you'll need to try out hot standby 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

Create the master database

Use initdb to create the master database if you don't already have a suitable PostgreSQL master you wish to use.

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'

Since you'll likely want to use pg_basebackup you should also set max_wal_senders to at least 2 or 3:

max_wal_senders = 3

(More settings to control WAL retention, feedback, apply latency, etc are recommended for production deployments. This is just a quick-start guide.)

Now start the master database.

Create a backup of the master

Use pg_basebackup to copy the master (9.1+)

The best option for creating the base backup is to use the standard pg_basebackup tool that comes with PostgreSQL 9.1 and above. If you intend to use 9.0 you will need to use your own scripts using pg_start_backup, pg_stop_backup, rsync or similar, and WAL archiving. pg_basebackup is strongly recommended instead.

A simple example might be, as the postgres user who wants to put a trial hot standby in the current directory, to run:

pg_basebackup -D hotstandby2 -w -R --xlog-method=stream --dbname="host=master user=postgres"

A more typical location to put the standby would be under /var/lib/pgsql or /var/lib/postgresql, depending on your install.

Windows and Mac users can use pg_basebackup, it isn't limited to unix/linux. It doesn't have to be run as the postgres user, that's just convention on unix/linux systems.

Script the copy of master to replica

If you're setting this up for testing purposes on the same machine and have WAL archiving enabled on the master, a script as follows will probably work for you instead of using pg_basebackup:

#!/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/*

Configure the copy of the master to run as a hot standby

echo 'hot_standby = on' >> hotstandby2/postgresql.conf
echo 'port = 5433' >> hotstandby2/postgresql.conf
echo "standby_mode = 'on'" >> hotstandby2/recovery.conf
# If you're using WAL archiving:
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.

Start your standby server

Start up your standby server with pg_ctl or equivalent startup command for your operating system, running as the same user you made the pg_basebackup with:

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