Re: Replicating changes

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