We have an unusual replication scenario and I am not sure which tool would be appropriate for that. Basically, we have a legacy non-normalized database that we want to refactor into a normalized database. Unfortunately, this has to be done over a long period of time (one or two years), hence the need for replication: legacy applications would use the old format until they are replaced by newer applications.
We would like to have three DBs:
Legacy -> Mirror -> New DB
Everything done on the legacy db would be replicated to a mirror, then using triggers and conversion scripts, we would transform the data into a normalized format. I'm not concerned about the conversion scripts (it's a different problem), but I'm not sure how to do the replication.
The load of the DB is mostly read with a few writes each day that need to be propagated in a matter of minutes (it is not acceptable to wait 15 minutes to see the result of an insertion on the mirror/new db).
We have around 50 tables divided in four databases on one server that we want to replicate.
The conversion scripts cannot be executed on the legacy db because we want to keep the replication cost to a minimum on the legacy db server.
I looked at the built-in replication in postgresql 9.1 but from my understanding, I cannot add a trigger on the slave that is not present on the master (both dbs must be identical copies).
Is there any way that I could use the built-in replication for our scenario? Otherwise, which replication strategy would be appropriate?