2

I have a MySQL database running behind strict firewalls, and only listening on localhost - not even 127.0.0.1.

I have a remote PostgreSQL which I would like to have a synced copy of a table from the MySQL database.

The server running the MySQL database is able to connect to the PostgreSQL database, as outgoing connections are allowed.

Is it possible to have the MySQL database sync via push a table to the remote PostgreSQL and keeping it in sync in near-realtime?

I've looked at the foreign data wrappers for PostgreSQL but they sort of work the other way around, and doesn't store a replica locally.

Cannot find if there's any foreign data wrapper for MySQL which can insert/update/delete into a PostgreSQL?

Improve this question

2 Answers 2

Reset to default
1

Not a sysadmin solution/answer, but if only for one table, I would suggest building a small service that sync from the mysql to the postgree db that table.

I did that for two mysql database in the past, but I had to add a table field to know if the field was sync'ed.

Improve this answer
2
  • My challenge is that fields can be updated, so I need to keep track of which field has been updated, created, deleted etc. to know what to sync. And also need to do it almost realtime? Commented Mar 27, 2019 at 14:04
  • Like I wrote, you need a field in the table that keep track if the record need to be synced, but it's a homemade solution.. can't use mysql on both side ? dev.mysql.com/doc/refman/8.0/en/replication.html Commented Mar 27, 2019 at 14:08
1

You haven't mentioned the OS but assuming a Linux system, you could create SSH tunnel, something like: ssh root@<HOSTNAME> -L 5432:localhost:5432 and try pg_chameleon:

pg_chameleon is a MySQL to PostgreSQL replica system written in Python 3. The tool can connect to the mysql replication protocol and replicate the data changes in PostgreSQL. Whether the user needs to setup a permanent replica between MySQL and PostgreSQL or perform an engine migration, pg_chamaleon is the perfect tool for the job.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.