Re: Asynchronous replication of a PostgreSQL DB to a

On Thu, 2006-12-07 at 09:56 +0100, Markus Wollny wrote:
> 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

Wow, tough problem. You're trying to do two quite difficult things at
once: replicate schema changes and convert from PostgreSQL to MySQL.

I think your best bet might be to hack PgPool to do what you need.

First, do some analysis to figure out what kinds of schema changes are
pushed onto PostgreSQL. Since the schema changes are automated, you
should see a few types of queries that change the schema. Maybe all the
commands begin with "ALTER" or "CREATE".

Then, in PgPool, hack it to recognize any DDL statements, and put them
in a log in PostgreSQL (the query in raw text), in addition to passing
it to PostgreSQL like normal.

You then need to make a process that reads this log of DDL changes, and
can recognize the types of DDL statements they are, and convert them to
the MySQL equivalent, and put them in MySQL. In order for this to work,
the statements must match one of a few patterns so that you can pre-
define the translations necessary. This won't work for people issuing
arbitrary schema changes.

Then, for the data replication, do something clever with triggers and a
function that can use the MySQL protocol. Make sure that the DDL stuff
makes it to the MySQL database before your trigger tries to send the
data.

I have no idea whether that will work for you, but there isn't going to
be a simple solution. You should strongly reconsider your options;
you're asking for a lot of trouble.

Regards,
Jeff Davis