PostgreSQL 9.0 Streaming Replication on Windows

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Comments are closed.