PostgreSQL 9.0 Streaming Replication on Windows

Published on Mon, May 30, 2011

A major milestone in PostgreSQL 9.0 is Streaming Replication(including DDL). Many of you all used configuring SR on Linux, but I would be presenting SR on Windows Platform. PostgreSQL wiki is the best guide for setting up the Streaming Replication.

For setting up SR on Windows, I would recommend to follow the PostgreSQL wiki steps with minor changes what needed for Windows Platform. I would like to show only the changes what you have to look for on Windows Platform in my blog.
http://wiki.postgresql.org/wiki/Streaming_Replication

Step 1. (Before configuring SR, add the port)

On primary, you need to configure the accepting port. Below link will guide for adding port.
http://support.microsoft.com/kb/842242

Note: Adding the port differ’s from different Windows Platforms.

Step 2. (Before configuring SR, Create common mount point for Archives)

Create one common mount point where Primary and Standby write/read the Archives. Mount point should own the Postgres user permissions. My common mount point: ‘10.10.101.111’

Step 3.

On Primary, changes in PostgreSQL.conf.

wal_level = hot_standby 
archive_mode = on 
archive_command = 'copy %p  \\\\10.10.101.111\\pg\\WAL_Archive\\%f'
max_wal_senders = 5
wal_keep_segments = 32

Step 4.

On Standby,

1) Edit the postgresql.conf file and change the below parameters.
        listen_address='*'
        hot_standby = on

2) Add the primary server entry in pg_hba.conf
        host    replication   postgres  primary.IP.address/22  trust

3) Create recovery.conf
        standby_mode = 'on'
        primary_conninfo = 'host=10.10.101.111 port=5432 user=postgres'
        trigger_file = 'C:\\stopreplication\\standby.txt'
        restore_command = 'copy \\\\10.10.101.111\\pg\\WAL_Archive\\%f %p'
     
Note: Create the recovery.conf file by copying any of the .conf files from the /data_directory.

Mentioned steps are the only changes you need to take care when setting up SR on Windows, rest all follow the procedure on PostgreSQL Wiki.

Regards
Raghav