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
  • 19,297

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
  • 10,234