Hi!
I'd like to export schema and data from a PostgreSQL database to a
remote MySQL database; any changes to the PG-master should be reflected
on the MySQL target in a matter of a few minutes to one hour max.
Has anybody done something like this before?
Here's some more background: We've got an Oracle database as our backend
and a couple of PostgreSQL-DBs as our frontend databases; the schema of
the backend DB is stable. There are so called "publishing jobs" running
every few minutes; these jobs not only update the frontend databases
with any changes in the backend, they also make changes to the frontend
dbs schemas whenever the backend says so - the frontend schemas differ
from the backend's, the DDL of the frontend dbs is partly defined by
data in the backend.
The logical thing to do would be to create another set of publishing
jobs for the MySQL databases; however our current network layout makes
this quite difficult, so I'd rather try and keep the MySQL db and one of
the PostgreSQL dbs in near sync.
My first problem is that the PostgreSQLs schema is not stable, so if I
simply write a couple of jobs to transport the data, I need to alter
these jobs and the MySQL schema whenever there are changes to the PG
schema. The second problem lies in PostgreSQL-specifics such as tsearch2
- I actually do not need nor want to replicate such metadata. Custom
datatypes and functions should also be exempt from this kind of
replication.
My hopes aren't all too high that there's an easy way to accomplish what
I wish to do, so any advice would be very much welcome - even a "can't
be done that way" by somebody who has tried to travel that path before
:)
Kind regards
Markus