>> I have been looking into how to ensure that synchronous replication, etc. >> could best be implimented. To date, I see only two options: incorporate >> the replication code into the database backend or have a separate "proxy" >> which handles the replication. > > There are many problems with a "proxy" solution. One is that you really > don't know if a statement does modify the database or not. A SELECT for > example can call a user defined function somewhere and that can do > whatever the programmer likes it to do. So you would have to "replicate" > all that too. Granted, you can exclude this type of database usage from > your supported list. > > Next you don't have control over sequence allocation. Every application > that uses sequence allocated ID's is in danger, because they are not > blocking, you cannot force the order of assignments and they don't roll > back either. > > And you get into deadlock problems if you don't guarantee that your > proxy uses the same order to access all databases. And you cannot > guarantee that if your proxy tries to do it parallel. So it has to do > the queries against all databases one by one, that doesn't scale well. > > The last thing (I mention for now) is that I cannot imagine any way that > such proxy code allows for a new member to join without stopping the > whole application, creating an identical copy of one member > (dump+restore) and continue. So it is impossible to build 24*7 support > that way. > > No, separate proxy code doesn't strike me as the superior solution.
I incorporate code (triggers/plpgsql) into the backend that creates a serialized audit trail of changes, then have an external proxy running which queries the audit trail and applies the changes. I don't have enough experience with the subject to debate it much, but I took this approach because: - It's much easier for the backend to keep straight the changes, for all the reasons mentioned above. - It seemed to me to be much easier to write the actual replication stuff as a C++ app on top of libpq than to try to stuff that into the backend. Note that I only need master/slave (take that LA County Commission) replication, and I don't really need to worry about scalability because I'll only have one or two slaves. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
