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