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.
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?
3.) Somehow directly monitoring the WAL files, interpreting and detecting changes. So far we haven't found any documentation on how WAL files are stored, so we're not sure this is feasible.
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.
