Unanswered: Which database replication for PostgreSQL on Windows?
I have been asked to look into open source database replication and failover solutions for some software that my place of work is releasing. Before a week ago I knew nothing about this. I did a lot of research and I found several possibilities (slony, rubyrep, bucardo etc). Our databases are PostgreSQL 8.3 and will always be on windows machines, typically XP. I have only tested RubyRep, as it is the only one I could get to work, and it creates some conflicts when it adds tables to the same schema we use. It seems like all of the other decent solutions are for linux machines. Or if there is some way to get slony, for example, working on a windows machine. It looks possible but the documentation is very limited. I am very new to this replication business, and would appreciate any suggestions.
What should the "second" server do?
Is that only for failover (high availability)?
Or do you want to run read-only queries on that?
Or do you need to both (all) servers to be able to handle read and write access?
If it's only for failover or the second one should only be used for read-only access, I'd suggest you look at version 9.0 which has hot standby and streaming replication out of the box and runs fine on Windows.
I should have been more explicit with our needs. I apologize.
Pretty much the only concern here is failover. From what I understand, the system would be a single master/(one or more) slave setup, not requiring read on the slaves (i.e. only the master will be interacting outside the replication cluster).
Sensitive data is collected at unknown intervals and a switchover is needed if the master database fails.
Thanks for the suggestion. I'm open to others, but in the meantime I'll take a look at verson 9.0 and see if they wouldnt mind upgrading.
You can have a "warm" standby with 8.3 as well.
The main difference between streaming replication in 9.0 and the WAL shipping in 8.x is that the potential data loss "window" is a bit bigger with 8.x WAL shipping. This is well documented in the manual: