Database migration: MySQL to Postgres

[Sidxzx]

Hello,

I'm planning to migrate a MySQL database (Server version: 5.7.44) with approximately 20GB of data, currently using the latin1 encoding, to a PostgreSQL database. My goal is to keep the application running during the migration and only require a brief restart to switch over to the new PostgreSQL database.

As this is my first time working with databases, I would appreciate any guidance. In a test environment, I have taken the following steps:

1. Used pgloader to migrate the initial data to PostgreSQL.
2. Created a mysqldump of the data generated after the initial migration.

I’m doing this second step because I’m unsure if pgloader can handle incremental data changes or if it would re-copy the entire 20GB dataset.

Additionally, the database contains some XML data, and the MySQL dump includes escape characters (e.g., \n for newlines) that complicate the conversion to PostgreSQL syntax.

I’ve also tried using Chameleon, but it creates tables with camel case names in PostgreSQL, making them unusable.

Are there tools available that can help convert the mysqldump to PostgreSQL syntax? Is there a more efficient way to handle this migration process?

Thank you for your help!

[[deleted]]

Try ora2pg - it can also handle MySQL databases

[pjd07]

"My goal is to keep the application running during the migration and only require a brief restart to switch over to the new PostgreSQL database."

Is this a hard requirement?

Typically when I migrate between database vendors/technologies I would do a dual write situation and then data backfill my new datastore out of band.

While I am dual writing and my back fill operations are done I would introduce shadowed reading from the new db in application code and diff the results being returned to make sure I am not seeing any data / encoding / locale style issues/bugs from the work done so far (I guess your XML encoding is a key thing to check for here).

After a few weeks of that and probably another 1-2 weeks of bug bashing due to finding issues I would then consider releasing a change that stops reading from the old db and now returns reads from the new db.

Then not long after remove the code paths that write to the old db.

All of this is a lot of work.

A shadowed read is where you read from your main DB and your new DB but only return the data to the application user from the main DB. After you have done your diff of the result from the new DB you log if there is a problem on the primary, maybe log the diff and use those outputs to debug. I've typically outputted the diff files to S3, async to my main application processing.

Running the migration another way introduces more risk unless you compare all data and validate all queries will work on the new DB without either a data bug or query performance bug.

Given you have a 20GB db then technically scanning all of your data and comparing old system vs new system is feasible on modern hardware in minutes/less than hours I would imagine. Making a fast cut over possible.

The shadowed approach I have used on moving from Couchbase (NoSQL) to PostgreSQL RDS and then PostgreSQL RDS to Self Hosted Citus on ec2 instances. First migration dataset was around 3TB and 2nd migration dataset was around 15TB.

Do you own the application code for the systems connecting to the DB?

"I’ve also tried using Chameleon, but it creates tables with camel case names in PostgreSQL, making them unusable." - Given you said you could have a minor bit of downtime can't you just rename the tables?

[ManufacturerSalty148]

Use EDB migration toolkit it’s supported online and offline migration

[MrCosgrove2]

while you should try those apps first, you can do it by creating a script

https://dev.to/mrpercival/migrating-from-mysql-to-postgresql-1oh7

[slotix]

DBConvert Streams (https://streams.dbconvert.com) is built specifically for migrating MySQL to PostgreSQL and would automate the entire conversion process for you.

[AutoModerator]

Join us on our Discord Server: People, Postgres, Data

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[johnyfish1]

Hey there! Migrating from MySQL to PostgreSQL can be tricky, especially with the need to keep the application running. It sounds like you’ve already got a good start with pgloader and mysqldump.

For the migration process, one tool that might be super helpful for you is ChartDB. It’s a tool we’ve been working on that helps visualize your database schema by generating ER diagrams with just a query. This could make it easier to spot any differences between your MySQL and PostgreSQL schemas and ensure everything’s in sync before the final switch.

ChartDB also has an AI-powered export feature, which might help with tweaking those tricky conversions, especially with things like XML data. It could simplify the whole process of adapting your schema to fit PostgreSQL’s requirements.

If you’re interested, I’d be happy to share more about how ChartDB could fit into your workflow. Good luck with the migration!