Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a project currently based on PostgreSQL 8.1. However, some of the data processing for it needs to happen on a server for which 7.4 is the latest version (for now, only official packages via yum can be used there).

What I would like to be able to do is split the processing out so that data for which MySQL works better can be operated-on talking to a MySQL database, while data for which PostgreSQL is better can be operated-on there.

I also want to have a fully-replicated environment across the two different RDBMS environments for redundancy.

I found a couple articles on how to do clustering or shadowing inside MySQL (such as here), and I've read the MySQL replication chapter.

I'm wondering if there is a way (preferably a "good" one!) of "subscribing" to the MySQL db from PostgreSQL, and to the PostgreSQL db from MySQL.

Update -
I'm not averse to writing the interfacing mechanism myself, and will if that's what needs to be done; just hoping something already exists that would handle this for me :)

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

While most DBMS have some kind of native replication support (as you found for MySQL and PostgreSQL), these are, AFAIK, always for replication to other instances of the same DBMS.

I am not aware of any DBMS whose replication support can replicate to other DBMSes. The reason probably is that replicating across different DBMSes is a significantly different problem from replicating to the same DBMS: Many concepts (triggers, constraints, table spaces etc.) will be different, and for each of these, there must be some decision as to if/how these things will be treated on replication.

So I believe your best option will be to use some kind of data migration / data transport tool and script some kind of replication. This will probably involve temporarily shutting down the databases for the transfers. Also it will probably be quite difficult to do a "bidirectional" sync, so you might want to consider whether you can do without that.

Alternatively you could try to create some custom solution, e.g. only replicate some tables, such that for a given table there is only one "master" DB.

Sorry to disappoint you, but I fear there is no general replication solution along the lines of what you are looking for, so you'll have to take a different approach.

share|improve this answer
    
Good answer. I'd write a custom solution (which doesn't have to be complicated). –  user3914 Sep 30 '09 at 10:53
    
I'm guessing you're right, but am hoping there's something like this already - I guess I'm envisioning some kind of "authenticated" rss feed for a db - publish all items added/changed/whatever since the last time an authenticated client hit the feed, allow the client (the other db) to grab those changes, and then start the feed afresh –  warren Sep 30 '09 at 11:01
    
Yes, would be cool if something like this existed, but a general solution is probably not possible, as I tried to outline. Would love to be proven wrong of course :-). –  sleske Sep 30 '09 at 11:21
    
I'm sure you're right, still hoping you're not though :D ..otoh, it could make a great project/product to write :) –  warren Sep 30 '09 at 15:24
    
I'm going to accept this after the week of no one else's ideas. Guess I'm off to write my own tool :) –  warren Oct 4 '09 at 11:15
add comment

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.