Yatendra Khandelwal

Setting up replication in Postgres 9.0

 (Migrated from old wordpress blog post dated 12/22/2010)

For picksie I had to set up streaming replication in Postgres 9.0. We have a master server which is used for read/write and a hot standby where we want the data to updated using streaming replication. Following is a summary of what I did

1. Install postgres on both master and standby

2. Allow standby server to connect to the master by editing pg_hba.conf

host replication postgres 192.168.0.20/22 trust

3. Setup streaming on master by updating postgresql.conf

wal_level = hot_standby

max_wal_senders = 5

wal_keep_segments = 32

archive_mode = on

archive_command = ‘cp %p /path_to/archive/%f’

4. Start postgres on master

5. Make base backup on master

$ psql -c “SELECT pg_start_backup(‘label’, true)”

$ rsync -a ${PGDATA}/ standby:/srv/pgsql/standby/ —exclude postmaster.pid

$ psql -c “SELECT pg_stop_backup()”

6. Do authentication and streaming related configuration in postgresql.conf and pg_hba.conf in standby server so that when needed it can be promoted as master with less effort.

7. Set standby server as a hot standby by updating postgresql.conf

hot_standby = on

8. Create recovery.conf with following configuration on standby server in same folder as postgresql.conf

standby_mode = ‘on’

primary_conninfo = ‘host=192.168.0.10 port=5432 user=postgres’

trigger_file = ‘/path_to/trigger’

restore_command = ‘cp /path_to/archive/%f “%p”’

Note: trigger file is the file, presence of which will make the standby server to stop replication and failover

9. Start postgres service on standby and it will start replication

Reference: http://wiki.postgresql.org/wiki/Streaming_Replication

$ $EDITOR postgresql.conf

listen_addresses = '192.168.0.10'

$ $EDITOR pg_hba.conf

# The standby server must have superuser access privileges.
host  replication  postgres  192.168.0.20/22  trust