2

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 :)

warren
  • 17,829
  • 23
  • 82
  • 134

1 Answers1

2

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.

sleske
  • 9,851
  • 4
  • 33
  • 44
  • Good answer. I'd write a custom solution (which doesn't have to be complicated). –  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 04 '09 at 11:15