Or, for something far easier, try http://pgfoundry.org/projects/pgcluster/ which provides syncronous multi-master clustering. On Wed, Aug 24, 2005 at 12:53:34PM -0700, Jeff Davis wrote: > Chris Browne wrote: > > > > Slony-I is a master/slave asynchronous replication system; if you > > already considered it unsuitable, then I see little likelihood of > > other systems with the same sorts of properties being suitable. > > > > What could conceivably be of use to you would be a *multimaster* > > asynchronous replication system. Unfortunately, multimaster > > *anything* is a really tough nut to crack. > > > > In general that's a difficult problem, but in practice there may be a > solution. > > For instance, perhaps the following configuration would be helpful: > > Make a database for each physical server, called db1 ... dbN. Let your > logical tables in each database be table1 ... tableM. Now, for each > logical tableX (where 1 <= X <= M), make N physical tables, tableX_1 ... > tableX_N. Now, make a view called tableX that is the UNION of tableX_1 > ... tableX_N (tableX is not a real table, it's just a logical table). > > Now, use Slony-I. For each dbY (where 1 <= Y <= N), make dbY a master > for tableX_Y (for all X where 1 <= X <= M) and a slave for tableX_Z (for > all X,Z where 1 <= X <= M, Z != Y). > > Now, use a rule that replaces all INSERTs to tableX (where 1 <= X <= M) > on dbY (where 1 <= Y <= N) with INSERTs to tableX_Y. > > That was my attempt at being unambiguous. In general what I mean is that > each database is master of one piece of a table, and slave to all the > other pieces of that table, and then you have a view which is the union > of those pieces. That view is the logical table. Then have a RULE which > makes INSERTs go to the physical table for which that database is master. > > The advantages: if one machine goes down, the rest keep going, and > merely miss the updates from that one site to that table. If one machine > makes an insert to the table, it quickly propogates to the other > machines and transparently becomes a part of the logical tables on those > machines. > > The disadvantages: UPDATEs are difficult, and might end up with a > complicated set of rules/procedures/triggers. You may have to program > the application defensively in case the database is unable to update a > remote database for various reasons (if the record to be updated is a > part of a table for which another database is master). Also, since the > solution is asynchronous, the databases may provide different results to > the same query. > > In general, this solution does not account for all kinds of data > constraints. The conflict resolution is very simplified because it's > basically just the union of data. If that union could cause a constraint > violation itself, this solution might not be right for you. For > instance, let's say you're tracking video rentals, and store policy says > that you only rent one video per person. However, maybe they go to store > 1 and rent a video, and run to store 2 and rent a video before store 1 > sends the INSERT record over to store 2. Now, when they finally do > attempt to UNION the data for the view, you have an inconsistant state. > > Many applications can get by just fine by UNIONing the data like that, > and if not, perhaps work around it. > > I hope this is helpful. Let me know if there's some reason my plan won't > work. > > Regards, > Jeff Davis > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
