How to Migrate from MySQL to PostgreSQL

MySQL to PostgreSQL Migration: A Practical Guide for Developers Who've Been There

💡 Mindset Shift: Ready to ditch MySQL for PostgreSQL? Read our high-level overview: [So You Want to Ditch MySQL for PostgreSQL. Let's Talk](/blog/ditch-mysql-for-postgresql).

So you've decided to migrate from MySQL to PostgreSQL. Maybe your team finally hit that wall where MySQL just isn't cutting it anymore — complex queries slowing down, missing features you desperately need, or you're simply following the industry trend toward Postgres. Whatever brought you here, you're making a solid choice. But let's be honest: migrations are messy, stressful, and full of surprises.

This guide won't sugarcoat things. We'll walk through the whole process — the what, the why, the how, and the "oh no, why is this breaking?" moments — in a way that actually makes sense.

Why Bother Switching at All?

Before diving into the *how*, let's talk about the *why*. You shouldn't migrate just because it's trendy. Here are some genuinely good reasons people make the switch:

PostgreSQL handles complex queries better. Window functions, CTEs (Common Table Expressions), recursive queries — Postgres was built for analytical workloads that make MySQL sweat.

Stricter SQL standards compliance. MySQL has historically been lenient (some would say *too* lenient) about data integrity. PostgreSQL enforces the rules more closely, which means fewer silent data corruption bugs down the road.

Rich data types. Native JSON/JSONB support, arrays, hstore, UUID, geometric types — Postgres gives you a lot of tools out of the box that MySQL simply doesn't have.

Better concurrency model. PostgreSQL uses MVCC (Multi-Version Concurrency Control) more aggressively, which means reads don't block writes and vice versa in most scenarios.

Open source with no "community vs enterprise" split. Unlike MySQL (owned by Oracle), PostgreSQL is truly community-driven. What you see is what you get — no feature paywalls.

What's Actually Different Between MySQL and PostgreSQL?

This is where most guides gloss over the painful details. Let's not do that.

1. Case Sensitivity in Identifiers

MySQL is case-insensitive for table and column names by default (on most platforms). PostgreSQL lowercases everything unless you quote it.

-- MySQL: This works fine
SELECT * FROM Users;
SELECT * FROM users; -- same table

-- PostgreSQL: These are different!
SELECT * FROM "Users";  -- specific table named Users
SELECT * FROM users;    -- different table

Practical tip: Stick to lowercase identifiers everywhere and you'll avoid this headache entirely.

2. AUTO_INCREMENT vs SERIAL / IDENTITY

MySQL uses AUTO_INCREMENT. PostgreSQL uses SERIAL (older style) or GENERATED ALWAYS AS IDENTITY (modern, SQL-standard way).

-- MySQL
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255)
);

-- PostgreSQL (modern way)
CREATE TABLE products (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255)
);

3. String Quoting

MySQL lets you use double quotes for string values. PostgreSQL strictly uses single quotes for strings and double quotes for identifiers (column/table names).

-- MySQL (works)
SELECT * FROM users WHERE name = "Alice";

-- PostgreSQL (WRONG — double quotes mean identifier)
SELECT * FROM users WHERE name = "Alice";  -- error or wrong behavior

-- PostgreSQL (correct)
SELECT * FROM users WHERE name = 'Alice';

4. LIMIT with OFFSET

Both support LIMIT and OFFSET, but the syntax differs slightly in edge cases. You're mostly fine here, but watch out when migrating raw SQL strings.

5. Boolean Values

MySQL uses TINYINT(1) to fake booleans. PostgreSQL has a real BOOLEAN type.

-- MySQL
is_active TINYINT(1) DEFAULT 1

-- PostgreSQL
is_active BOOLEAN DEFAULT TRUE

6. Date and Time Handling

PostgreSQL is stricter about date formats and timezone handling. MySQL tends to silently accept garbage dates (like 0000-00-00). Postgres will throw an error.

7. Full-Text Search Syntax

Both databases support full-text search, but the syntax is completely different. If you're using MySQL's MATCH ... AGAINST, you'll need to rewrite those queries for PostgreSQL's tsvector and tsquery approach.

Planning the Migration: Don't Skip This Part

Rushing into a migration without a plan is how you end up with 3 AM war room calls. Here's what you should nail down before touching a single production database.

Audit Your Current MySQL Setup

Identify the Hard Parts Early

Run a search through your codebase for MySQL-specific syntax:

Decide on Your Migration Approach

There are three main strategies:

Big Bang Migration — Take the system offline, migrate everything, bring it back up on PostgreSQL. Simple but risky for large systems with zero downtime requirements.

Parallel Run — Run both databases simultaneously. Write to both, read from MySQL until Postgres is verified, then cut over. Complex but safe.

Incremental Migration — Migrate table by table or feature by feature, using something like a dual-write proxy. Best for large, complex systems.

For most small-to-medium projects, a well-planned Big Bang migration with a tested rollback plan works fine.

The Actual Migration Process

Step 1: Schema Migration

Start with the schema — no data yet. You want to translate your MySQL DDL (Data Definition Language) into PostgreSQL-compatible DDL.

Do it manually for small databases. It's tedious but gives you full control and helps you catch issues early.

Use tools for larger databases:

Using pgLoader:

pgloader mysql://user:password@localhost/mydb \
         postgresql://user:password@localhost/mydb

It handles a lot of type conversions automatically, but always review the output.

Step 2: Data Migration

Once schema is in place, move the data. pgLoader can do both at once, which is convenient.

Watch out for:

If you're doing it manually with dumps:

# Export from MySQL
mysqldump -u root -p --compatible=postgresql mydb > dump.sql

# This won't work perfectly — you'll need to clean up the dump

Honestly, stick with pgLoader or a purpose-built ETL pipeline for anything beyond a toy database.

Step 3: Update Application Code

This is where the real work is. Update your:

ORM configuration: Change the database adapter. In Python (SQLAlchemy), swap mysql+pymysql:// for postgresql://. In Node.js (Sequelize), change the dialect from mysql to postgres.

Raw SQL queries: Hunt down every raw SQL string and fix MySQL-specific syntax.

Stored procedures and triggers: Rewrite in PL/pgSQL instead of MySQL's stored procedure syntax.

Connection pooling: PostgreSQL handles connections differently. Tools like PgBouncer are commonly used to manage connection pools.

Step 4: Test Everything

Don't skip this. Run your full test suite. Then run it again.

Pay special attention to:

Step 5: Performance Tuning

Your PostgreSQL instance won't be perfectly tuned out of the box. Key things to look at:

Common Errors You'll Hit (And How to Fix Them)

"column must appear in the GROUP BY clause"

You had a loose GROUP BY in MySQL. In Postgres, if you select a column, it must either be in GROUP BY or be wrapped in an aggregate function.

-- MySQL allows this, Postgres doesn't
SELECT name, MAX(salary), department FROM employees GROUP BY department;

-- Fix: add name to GROUP BY or use an aggregate
SELECT MAX(name), MAX(salary), department FROM employees GROUP BY department;

"operator does not exist: integer = text"

Postgres is strict about type matching. MySQL would silently cast. You need to be explicit.

-- Fix: cast explicitly
WHERE id = '123'::integer
-- or better, fix the application code to send the right type

"invalid input syntax for type boolean"

You're sending 1 or 0 where Postgres expects true/false.

"syntax error at or near 'LIMIT'"

Postgres doesn't support LIMIT without SELECT in some edge cases. Also check if you have raw MySQL syntax like LIMIT 10, 5 (offset first) — Postgres uses LIMIT 5 OFFSET 10.

Should You Use a Migration Tool or Do It By Hand?

Use a tool if:

Go manual if:

In practice, most teams use pgLoader for the heavy lifting and then fix things up manually. It's a good balance.

Post-Migration Checklist

Before calling it done, go through this:

The Honest Truth About Timelines

Small databases (< 10GB, simple schema): 1–3 days of focused effort.

Medium databases (10–100GB, moderate complexity): 1–3 weeks, including testing.

Large or complex databases (100GB+, lots of stored procedures, strict zero-downtime requirements): Plan for months, not days.

Don't let anyone pressure you into an unrealistic timeline. A botched migration is far more expensive than a careful, slow one.

Wrapping Up

Migrating from MySQL to PostgreSQL isn't the kind of task you knock out in an afternoon, but it's absolutely doable — and for most teams, worth it. PostgreSQL is a genuinely powerful database that will serve you well for years.

The key is to respect the differences between the two systems, plan thoroughly, test obsessively, and have a rollback plan ready. Most migrations that go wrong aren't because of technical impossibilities — they're because someone rushed.

Take your time, lean on tools like pgLoader, fix your type mismatches and GROUP BY issues, and tune your Postgres config before going live. Follow those steps and you'll come out the other side with a faster, more reliable database and a story to tell at your next team retrospective.

Good luck — you've got this.

*Have questions or ran into a migration issue not covered here? Drop a comment below or reach out through the [SQLMarrow community](https://sqlmarrow.com). We're always happy to dig into the details.*