Dev Guide on migrating from Postgres to MySQL
These tips will help you in the process of exporting the Postgres database to the MySQL server.
Back in 2020, when I was learning Laravel, I created an e-diary app that saves diary entries in encrypted form in the database and has multiple privacy and progressive web app features. I deployed the app on Heroku free dynos with Postgres Database Server.
Later, I purchased the hosting but never migrated the e-diary app from Heroku to a personal hosting server for multiple reasons. Now you may think why are you telling me this and how it is helpful to me.
So, Recently Heroku announced that starting November 28th, 2022, free Heroku Dynos, free Heroku Postgres, and free Heroku Data for Redis® will no longer be available. And I have to migrate it to my hosting.
The whole code is open-sourced on GitHub, so migrating the code was just a quick clone command and some basic requirement installations. But I got some real complications when tried to migrate the database and here’s how I achieved it.
How to migrate a database from Postgres to MySQL
I tried some tools but none worked because of multiple technical and monetary constraints. Then I think to go with TablePlus.
The app was developed in Laravel so running the command php artisan migrate created the table with the correct schema in the database.
But copying the database was a real task. I connected the Postgres database in TablePlus and exported the data from the server in MYSQL but I keep getting the syntax error. Then I decided to go with the developer’s brain.
I opened the exported SQL file and remove all “public” and also remove all double quotes(“) and saved it and tried to import it and it worked like a charm. All my database records have been migrated to MySQL and it lives on http://ediary.shakiltech.com
Tip on migrating the database from Postgres to MySQL
These tips will help you in the process of exporting the Postgres database to the MySQL server.
Stick to the guideline: Framework like Laravel and Django has migration that stores the schema of tables in code. If you are working with any of these. Please create migration files so later we can generate table schema from these schemas and we only have to migrate the records.
Export from TablePlus in a suitable form: Connect with your database with TablePlus and export the records in SQL or CSV format according to your need. Always verify the database before going to production with migrated data.
Review SQL export before exporting: Open the SQL in the code editor and review the code and do the necessary changes such as removing the public. and “(double quote) and SEQUENCE creation command.
Export in chunk: Don’t try to export the whole database at once. I would recommend exporting the table in two parts. Firstly add schema and then records and then table constraints. So If something broke. You can fix it asap and roll back to the last successful steps.
Go local first (Staging): Import the database on the local MySQL server first and once you’re happy with the result. Export it from the local MYSQL server to production.
You may have to read the Postgres to MySQL migration guide and replacements for multiple Postgres commands replacements in SQL. Try to fix one problem at a time and use basic developer instinct and you are good to go.
Thanks for reading. I hope you found it useful. If you have any thoughts or suggestions about the article. feel free to comment down below.
