patternsqlMinor
Replication chaining with PostgreSQL
Viewed 0 times
withpostgresqlchainingreplication
Problem
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?
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?
Solution
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,
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!
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!
Context
StackExchange Database Administrators Q#9291, answer score: 2
Revisions (0)
No revisions yet.