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.
Step 1. (Before configuring SR, add the port)
On primary, you need to configure the accepting port. Below link will
guide for adding port.
Note: Adding the port differ’s from different Windows Platforms.
Step 2. (Before configuring SR, Create common mount point for
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’
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
1) Edit the postgresql.conf file and change the below parameters.
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.