PostgreSQL 9.0 Streaming Replication on Windows

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.
        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.

42 Replies to “PostgreSQL 9.0 Streaming Replication on Windows”

1. 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(&#39label&#39, true)"$ rsync -a ${PGDATA}/ standby:/srv/pgsql/standby/ –exclude postmaster.pid$ psql -c "SELECT pg_stop_backup()"————Thank you, Ivan Orosco

2. 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.

3. @ShyamIts 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.

4. Thank you for the opportunity. Currently I am engaged with many projects where its difficult to take out time on this project. Am sorry.

5. 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&#39s you can use xcopy/robocopy to copy directory to standby server. –Raghav

6. 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

7. 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.

8. 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

9. 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.aspxThank you, Alan Jelden.

10. Hi,I am Llewellyn F. Rozario, a Technical Editor with Packt Publishing.There&#39s work begun to develop a title on ‘PostgreSQL Replication’ and I am nowlooking for an author to write this book.Please contact me if you are interested in writing this book.Thanks,Llewellyn F. RozarioTechnical Editor[Packt Publishing ]Email: [email protected]: http://www.packtpub.com

11. 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 = &#39cp -i %p $HOME/bin/postgres/archive/%f&#39restore_command = &#39cp -i $HOME/bin/postgres/archive/%f %p&#39How should we replace the cp in the above parameters for windows, my shared WAL Archive directory is E:PostgresWAL_Archive, I tried both xcopy and robocopy they dont work, in my case how the above two parameters be [email protected]

12. 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.5Operating System -Windows Server 2008 R2

13. You need mention xcopy/robocopy in restore_command of recovery.conf file. –Raghav

14. 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.3Server 2- 192.168.65.5when 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

15. 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 meThanks,Shyam

16. Hi, I followed all the steps properly. But postgres service on slave node is failing to start.

17. Hi, Now my standby connected to master,I and I have hot_standby = on in slave configuration file,StillI am not able perform an insert query on my master db, taking huge time to return back

18. 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.

19. 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.

20. Hi,
    I followed all the steps properly.
    But postgres service on slave node is failing to start.

21. 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

22. @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.

23. 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

24. 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

25. 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.

26. Thank you for the opportunity.
    Currently I am engaged with many projects where its difficult to take out time on this project. Am sorry.

27. 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: [email protected]
    Web: http://www.packtpub.com

28. 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

29. 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:PostgresWAL_Archive, I tried both xcopy and robocopy they dont work, in my case how the above two parameters be set.

    Thanks
    Terry
    [email protected]

30. You need mention xcopy/robocopy in restore_command of recovery.conf file.

    –Raghav

31. 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.

32. 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

33. 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

34. 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

35. 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?

36. 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&#39s and restore_command to apply WAL&#39s on Standby.

    Get back if you have any further questions.

    –Raghav

37. 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?

38. 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

39. 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

40. Alan,

    Yes, you are right..

41. 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.

42. Alan,

    Yes, you are right..

Comments are closed.