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 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.
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:
- Create the master database
- Configure the master for WAL archiving
- Create a backup of the master
- 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:
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
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.