CODEWITHSUNDEEP
mysqldatabasepostgresqlreplicationdatabase-replication
Joshua
Joshua

Reputation: 442

Syncing/Streaming MySQL Table/Tables(Joined Tables) with PostgreSQL Table/Tables

  1. I am having one MySQL Server and one PostgreSQL server.

  2. Need to replicate or re-insert set of data from multiples tables of MySQL to be Streamed/Synced to the PostgreSQL table.

  3. This replication can be based on time(Sync) or event such as a new insert in the table(Stream).

    I tried using the below replication tools but all these tools will be able to sync table to table only.Its not allowing to choose the columns from different tables of the source database(MySQL) and insert in to different tables in the destination database(PostgreSQL).

    • Symmetricds
    • dbconvert
    • pgloader
    • Postgresql FDW

    Now I have to write an application to query the data from MySQL and insert in to PostgreSQL as a cron job . Its cumbersome and error prone to sync the data. This is not able to stream(Event based) the data for realtime replication.

    it would be great if some tools already solving this problem. Please let me know if there is opensource library or tool can do this for me.

    Thanks in advance.

Upvotes: 1

Views: 1323

Answers (2)

Federico Campoli
Federico Campoli

Reputation: 11

maybe this tool can help you. https://github.com/the4thdoctor/pg_chameleon

Pg_chameleon is a replication tool from MySQL to PostgreSQL developed in Python 2.7/3.5. The system relies on the mysql-replication library to pull the changes from MySQL and covert them into a jsonb object. A plpgsql function decodes the jsonb and replays the changes into the PostgreSQL database.

The tool can initialise the replica pulling out the data from MySQL but this requires the FLUSH TABLE WITH READ LOCK; to work properly.

The tool can pull the data from a cascading replica when the MySQL slave is configured with log-slave-updates.

Upvotes: 1

Boris Schegolev
Boris Schegolev

Reputation: 3701

To achieve a replication with one of tools you proposed you can do the following:

Create a separate schema in PostgreSQL and add views so that they completely copy the table structure of MySQL. You will then add rules or triggers to the views to handle inserts/updates/deletes and redirect them to the tables of your choice.

This way you have the complete freedom to transform your data during the replication, yet still use the common tools.

Upvotes: 1

Related Questions