Replication & Upgrades - PostgreSQL 9 Administration - page 304

7. Adjust hot Standby parameters if required (see later recipe)
8. Take a base backup, very similar to the process for taking a physical backup as
described in the backup chapter.
a. Start the backup
psql -c "select pg_start_backup('base backup for streaming
rep')"
b. Copy the data files (excluding the pg_xlog directory)
rsync -cva --inplace --exclude=*pg_xlog* \
${PGDATA}/ $STANDBYNODE:$PGDATA
c. Stop the backup
psql -c "select pg_stop_backup(), current_timestamp"
9. Set the recovery.conf parameters on the Standby. Note that the primary_
conninfo must not specify a database name, though can contain any other
PostgreSQL connection option. Note also that all options in recovery.conf are
enclosed in quotes, whereas postgresql.conf parameters need not be.
Standby_mode = 'on'
primary_conninfo = 'host=192.168.0.1 user=repuser'
trigger_file = '/tmp/postgresql.trigger.5432'
10. Start Standby server
11. Carefully monitor replication delay until the catchup period is over. During the initial
catchup period, the replication delay will be much higher than we would normally
expect it to be. You are advised to set hot_Standby = off for the initial period only.
How it works...
Multiple Standby nodes can connect to a single Master. If you use multiple Standbys, be
sure to set max_wal_senders correctly. You may wish to set up an individual user for each
Standby node, though it may be sufficient just to set the application_name parameter in
the primary_conninfo .
Standby nodes cannot connect to other Standby nodes, only to the current Master. A Standby
that connects to a different Master will receive an error message.
The architecture for streaming replication is that on the Master, one WALSender process is
created for each Standby that connects for streaming replication. On the Standby node, a
WALReceiver process is created to work co-operatively with the Master. Data transfer has
been designed and measured to be very efficient—data is typically sent in 8192-byte chunks
without additional buffering at the network layer.