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
21 comments :
Thanks, Raghav.
Question: When you say "Create one common mount point" are you referring to the steps shown in this link? http://technet.microsoft.com/en-us/library/cc753321.aspx
Thank you, Alan Jelden.
Alan,
Yes, you are right..
Hi Raghav, please help-me...
The mount point that you refer is just a shared folder on the primary server to write to the slave right?
How to do step 6 of the postgre manual if in the windows server does not have the rsync command?
------------
$ psql -c "SELECT pg_start_backup('label', true)"
$ rsync -a ${PGDATA}/ standby:/srv/pgsql/standby/ --exclude postmaster.pid
$ psql -c "SELECT pg_stop_backup()"
------------
Thank you, Ivan Orosco
Hi Ivan Orosco,
"The mount point that you refer is just a shared folder on the primary server to write to the slave right?"
Yes.. its for WAL-archive sharing.
In step 6, it says to take backup of the Data directory, which mean, in window's you can use xcopy/robocopy to copy directory to standby server.
--Raghav
you use the IP Address 10.10.101.111 in all of your examples as does the PostgrSQL wiki.
I am replicating ACROSS servers. They have TWO DIFFERENT IP addresses.
Master = 192.168.1.100
Slave = 192.168.1.111
Which IP of my addresses replace which 10.10.101.111 IP Addresses in yours and the wiki examples?
How does the archive_command and the restore_command copy across the network?
To make concept clear, if you want to replicate across servers, first you need to create one shared mount point where Master and Slave can do read/write operation on that location.
After creating the mount point, Master will send WALs and Slave will apply WALs from the shared mount point.
Now to your question, you need to replace the mount residing IP in Archive_command for Master writing WAL's and restore_command to apply WAL's on Standby.
Get back if you have any further questions.
--Raghav
Hi Raghavendra,
We have 2 windows 2008 server, and we we want to create postgre SQL 9.0 replication, below is IP of the server and they both belongs to same windows domain,
Server 1- 192.168.65.3
Server 2- 192.168.65.5
when you say mount point does it mean to have a shared folder on primary server and both server should have read/write access on it?
please guide us, thanks
+91-7204232661
Yes, you need to have a common mount point (shared folder with r/w access on it) on Primary to write WAL files to feed Standby. Standby also must have complete access on this Shared folder.
--Raghav
Hi Raghav,
Please help me in making streaming replication. After creating recovery.conf where to use xcopy/robocopy in windows server 2008 because rsync script is not present in windows server 2008 according to postgres/wiki/tutorial.after creating recovery.conf when i am trying to start standby server it is not starting.
You need mention xcopy/robocopy in restore_command of recovery.conf file.
--Raghav
I am trying to set up replication on windows, both master and slave dbs are running on the same machine, I have the setps to set it up too, the problem I am running into has to do with the "cp" command that we have to use in the config files,parameters like restore_command and archive_command, there is no cp command in windows there is no copy command either,
archive_command = 'cp -i %p $HOME/bin/postgres/archive/%f'
restore_command = 'cp -i $HOME/bin/postgres/archive/%f %p'
How should we replace the cp in the above parameters for windows, my shared WAL Archive directory is E:\Postgres\WAL_Archive, I tried both xcopy and robocopy they dont work, in my case how the above two parameters be set.
Thanks
Terry
terrykhatri531@gmail.com
Am pretty sure, windows comes with COPY command in-built and you can use COPY command in both the parameters. Please follow the below link , point 24.3.1.
http://www.postgresql.org/docs/9.1/static/continuous-archiving.html
--Raghav
I am following all the necessary steps of the document . But the slave database is not starting.
Can you please provide some important informations on this.
Hi,
I am Llewellyn F. Rozario, a Technical Editor with Packt Publishing.
There's work begun to develop a title on ‘PostgreSQL Replication’ and I am now
looking for an author to write this book.
Please contact me if you are interested in writing this book.
Thanks,
Llewellyn F. Rozario
Technical Editor
[Packt Publishing ]
Email: llewellynr@packtpub.com
Web: www.packtpub.com
Thank you for the opportunity.
Currently I am engaged with many projects where its difficult to take out time on this project. Am sorry.
Hi,
I have configured the primary and standby servers
,and WAL files are generating at the shared folder.but stand by server is not getting updated.
pg_current_xlog_location on the primary returns data,but pg_last_xlog_receive_location on the standby returns empty.Please help me
Thanks,
Shyam
Hi,
Now my standby connected to master,I and I have
hot_standby = on in slave configuration file,Still
I am not able perform an insert query on my master db, taking huge time to return back
@Shyam
Its tough to guess why its happening with less information. My suspect, to see if archives have complete Postgres user permission. Also see what log writing on this behavior.
Since its a blog, I recommend to post the same on Postgresql mailing list to take it forward.
Hi,
I followed all the steps properly.
But postgres service on slave node is failing to start.
Hi,
I have followed same steps mentioned as per document. However, I have same issue as mentioned above by @ashutosh.
Postgres service on slave machine is not able to start.
Could you please let me know the reason behind this if possible ?
My setup -
Postgres Advanced Server 9.5
Operating System -Windows Server 2008 R2
hi Raghavendra..
i m using PostgreSQL9.4 and trying to write trigger file for replication but had not idea how to write.
could you please help me to write trigger file. you may send me a demo file or syntax to write.
Post a Comment