Dev Guide on migrating from Postgres to MySQL

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.

Migrating a Laravel E-Diary App from Heroku to Self-Hosted: PostgreSQL to MySQL

Back in 2020, I built an e-diary app with Laravel — encrypted entries, privacy features, PWA support, the works. It lived comfortably on Heroku’s free tier with a Postgres database. Then in 2022, Heroku killed its free dynos and free Postgres, which finally forced my hand: time to migrate to my own hosting.

Cloning the codebase was trivial. The real challenge was the database.

The Stack (Then and Now)

Migrating PostgreSQL to MySQL

I tested several automated migration tools, but ran into technical and cost constraints with each one. I eventually landed on a TablePlus + manual cleanup approach that worked cleanly.

Step 1 — Recreate the Schema with Artisan

Because the app was built with Laravel migrations, recreating the schema on MySQL was a one-liner:

php artisan migrate

This means you only need to move records, not the schema — a much smaller and cleaner export.

Step 2 — Export Records from TablePlus

Connect to your Heroku Postgres database in TablePlus, then export each table’s data as SQL (data-only, no schema). Choose SQL format for relational data or CSV if you prefer more control.

Step 3 — Clean the Exported SQL

PostgreSQL exports include syntax that MySQL doesn’t understand. Open the file in VS Code or any editor and make these changes:

• Remove all public. prefixes (e.g., public.users → users)
• Remove all double-quote identifiers (e.g., "id" → id)
• Remove or comment out any CREATE SEQUENCE and SEQUENCE references
• Replace SERIAL / BIGSERIAL with INT AUTO_INCREMENT if present
• Replace TRUE/FALSE booleans with 1/0 if needed for older MySQL versions
• Check for any ::text, ::integer Postgres-style type casts and remove them

A quick find-and-replace handles most of this in under a minute.

Step 4 — Test Locally Before Touching Production

Import the cleaned SQL into a local MySQL instance first:

mysql -u root -p your_database < exported_data.sql

Boot the app locally, run through your key flows, and verify data integrity before touching production.

Step 5 — Export in Chunks, Not All at Once

Don’t dump the entire database as one file. Export and import in this order:

1. Schema — handled by artisan migrate, skip this
2. Records — table by table
3. Constraints and indexes — last

This way, if something breaks, you can roll back cleanly to the last successful step.

Quick Reference: Common Postgres → MySQL Replacements

Key Takeaways

• Use framework migrations — Laravel and Django migrations let you recreate schemas anywhere with a single command. Always write them.
• Separate schema from data — Migrating records independently from schema gives you far more control.
• Clean before importing — PostgreSQL SQL exports are not MySQL-compatible out of the box. A quick manual review saves hours of cryptic errors.
• Go local first — Never import directly to production. Validate on a local or staging server first.

The live app is at ediary.shakiltech.com. The full source is open on GitHub if you want to dig into the Laravel setup.

Please leave this field empty

Oh hi there 👋
It’s nice to meet you.

Sign up to receive awesome content in your inbox.

We don’t spam! Read our privacy policy for more info.

Check your inbox or spam folder to confirm your subscription.