The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 274 pages ISBN 9781906966072 RRP £9.95 ($14.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 3 Buy the book here! >>> support free software

11.2.4 Setting Up a Standby Server

To set up the standby server, restore the base backup taken from primary server (see section 10.3.3 Recovering using a Continuous Archive Backup). Create a recovery command file ‘recovery.conf’ in the standby's cluster data directory, and turn on standby_mode. Set restore_command to a simple command to copy files from the WAL archive.

Note: Do not use pg_standby or similar tools with the built-in standby mode described here. restore_command should return immediately if the file does not exist; the server will retry the command again if necessary. See section 11.4 Alternative method for log shipping for using tools like pg_standby.

If you want to use streaming replication, fill in primary_conninfo with a libpq connection string, including the host name (or IP address) and any additional details needed to connect to the primary server. If the primary needs a password for authentication, the password needs to be specified in primary_conninfo as well.

You can use archive_cleanup_command to prune the archive of files no longer needed by the standby.

If you're setting up the standby server for high availability purposes, set up WAL archiving, connections and authentication like the primary server, because the standby server will work as a primary server after failover. You will also need to set trigger_file to make it possible to fail over. If you're setting up the standby server for reporting purposes, with no plans to fail over to it, trigger_file is not required.

A simple example of a ‘recovery.conf’ is:

standby_mode = 'on'
primary_conninfo = 'host=192.168.1.50 port=5432 user=foo 
  password=foopass'
restore_command = 'cp /path/to/archive/%f %p'
trigger_file = '/path/to/trigger_file'

You can have any number of standby servers, but if you use streaming replication, make sure you set max_wal_senders high enough in the primary to allow them to be connected simultaneously.

If you're using a WAL archive, its size can be minimized using the archive_cleanup_command option to remove files that are no longer required by the standby server. Note however, that if you're using the archive for backup purposes, you need to retain files needed to recover from at least the latest base backup, even if they're no longer needed by the standby.