Re: Asynchronous replication of a PostgreSQL DB to

I think Sequoia (open source) and Continuent (proprietary) do this.

---------------------------------------------------------------------------

Markus Wollny wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +