0

Does anyone have a drop in prod-ready solution to move all tables from MySQL to Postgres?

Debezium looks like a great tool, but I'm facing a fairly steep learning curve to get it in prod mode. Many tutorial seem skipping over important features needed to be production ready and even if I can hack something up, it'd be great avoiding blind spots when operating in prod.

e.g. https://medium.com/swlh/sync-mysql-to-postgresql-using-debezium-and-kafkaconnect-d6612489fd64

  • This handles only one table, what is the easiest way to do multiple tables?
  • It uses old debezium syntax (property and not JSON), use of keys such as table.whitelist instead table.include.list
  • doesn't use properties such as database.history.kafka.recovery.attempts or tasks.max, include.schema.changes` --> still requires a decent amount of work and to cover blind spots that I'm not even aware of
  • does it need any kind of transform?

For the Sink I was trying to create a config that:

  • creates the tables
  • evolve the schema change (is it even possible for MySQL to Postgres?)
  • uses PKs for kafka partitioning easily
  • can run in recovery mode (so if it crashes, go back and replay in a idempotent fashion)
M4rk
  • 2,172
  • 5
  • 36
  • 70
  • I have not done this, and I don't know of an existing solution, but FWIW I doubt that schema changes are possible to replicate. MySQL logs schema changes in statement mode even if your binary log is in ROW format. I don't know of an automatic translation between MySQL and PostgreSQL DDL that is integrated with Debezium. You might have to just make a rule for your site: no schema changes allowed while the transition is in progress. – Bill Karwin Apr 23 '22 at 18:04
  • Excluding the Schema Evolution part, do you believe the rest is achievable? – M4rk Apr 23 '22 at 20:24
  • I don't think there's a general-purpose solution that can replicate _any_ MySQL database to PostgreSQL. For example, MySQL's `ENUM` and `SET` types have no equivalent in PostgreSQL. Fulltext and spatial indexes are totally different. Semantics of views, JSON support, and other things are different. That's just off the top of my head. Probably this is why there is (as far as I know) not any open-source converter available. A solution that handles 80% of MySQL features might work for _your_ needs, but it wouldn't go far as a horizontal technology. – Bill Karwin Apr 23 '22 at 20:33
  • I get there are some specific corner cases for type conversions, we try to engineer for the 99%. Then some special cases can be handled independently (with some transformations, or refactoring). I believe the main concern for most engineers is having a good Debezium source-sink configuration that looks like it could be used in production in majority of cases. – M4rk Apr 23 '22 at 20:50
  • Yeah, I think you will have to develop that sink. – Bill Karwin Apr 23 '22 at 21:25

0 Answers0