On Friday 27 October 2006 09:59, Alban Hertroys wrote: > Hello list, > > We're investigating a ways to replicate changes to the database to > several "outside" systems. > > Some filtering will need to take place, as not all changes are allowed > to go to all systems. Whether the system receiving the changes stores > them in a database or not isn't particularly relevant to us. We're only > interested in getting the data out in a specified format. > > The changes in the data that needs to be replicated can come in bursts > of several thousands of records. Performance is important, but > reliability as well. We need to get those changes to a number of 3rd > parties, in a format that we're allowed to specify. > > So far we've looked into a few solutions: > 1.) Adding triggers to the relevant tables that send a NOTIFY and store > the changes in a local table. probably some meta-data will be required, > like for example a time stamp. > If I had to guess, this is what your going to end up doing given the granularity you need to control data changes. > 2.) Use one of the existing replication systems. We're currently under > the impression that (we've looked at Slony-I particularly) the slave > system is supposed to be another (postgresql?) database. This wouldn't > fit our needs, but maybe we're overlooking something? > For Slony this is correct... but there are some 3rd party replication solutions that will do cross database replication (I think Continuent's will do this, but I'm not sure). > 3.) Somehow directly monitoring the WAL fil > detecting changes. So far we haven't found any documentation on how WAL > files are stored, so we're not sure this is feasible. > In theory this would seem possible; you can do WAL log shipping to send changes between PostgreSQL servers; but sending those changes to a different database server will mean having to transform it into some form the other database can read, which sounds rather tricky (when compared to doing this at a higher level anyway). I think your best bet for learning how WAL files work is to read the backend code and maybe take a look at http://pgfoundry.org/projects/xlogviewer/ > This is a call for advice, as we're surely not the first who are trying > to accomplish this. If any clarification is needed, please ask. We want > to get this right. -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
