Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

migrated from stackoverflow.com Dec 16 '11 at 18:24

3 Answers

up vote 2 down vote accepted

You are right, PostgreSQL's built-in replication (aka Hot Standby) replicates whole cluster - so it's not suitable in your case.

You will need some trigger-based solution. For example,

  • Slony-I - most mature, written in C, flexible, good for complex setups
  • SkyTools pgq+londiste - C+Python, more lighweight than Slony-I
  • RubyRep - Ruby/JRuby, simple, easy to setup, not so mature

Please note that triggers always cause some extra load, and also initial sync will be equivalent of dumping all replicated tables.

Hope this helps, I'd propose to come back with more detailed questions if you have problems.

Good luck!

share|improve this answer
Thank you for your answer. I wanted to double check about Hot Standby before looking at other solutions. I am now looking into Bucardo, which seems simpler than Slony and mature. – Barthelemy Dec 17 '11 at 14:50
up vote 0 down vote

I think it's worth exploring the idea that replicating the data may not be the right answer at all. Instead, you may want to refactor your app to just write the data to two places, the old and new systems. This was you can test your app simultaneously, and do a more graceful switch between the two systems. Once everything works on the new system, you change the app to stop writing to the old system, and then you can shut the legacy db off.

share|improve this answer
Actually, our team is only developing the app that reads the data and we have no power on the app that writes the data (we don't have the resources to take over these writing apps too now). – Barthelemy Dec 23 '11 at 1:21
up vote 0 down vote

If you really have only "a few writes each day that need to be propagated", you should consider putting triggers on those to write a queue of things to be processed on the Legacy node. Then you can use the built-in replication for Legacy->Mirror, poll the queue on the Mirror using Hot Standby, convert them and push over to New DB, and then push an update back to Legacy removing them from the queue. You'll end up needing to connect to two databases either in your app or with dblink for these steps, and making the whole thing atomic will be fun. Would be even more difficult without the Legacy node helping you keep track of replication state though. Without a queue on the Legacy server, you're in for a fun time resyncronizing the first time there's a hiccup between Legacy and Mirror or between Mirror and New DB.

That will add a bit of overhead to the Legacy server, but not the full conversion overhead. Anything else you might do instead is going to end up spending a lot of time polling every table you care about processing on the Mirror system. That's not only really inefficient, it's hard to get right. Truly "minimal" overhead on the master is no help if you can't trust the result, which is why I highlight concerns over recovering from connection failures between nodes.

And none of the statement-based replication solutions are going to help with this problem unless you wander this same route. They also all assume you've identified changes to be replicated by having triggers flag them on the master. If you've accepted that triggers on Legacy going into a replication queue are worthwhile, the queue handling replication mechanisms in Londiste (pgq) are probably the easiest to re-purpose for what you're trying to do.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.