Table of Contents

1. Why Migrate from MySQL to PostgreSQL?
2. Pre-Migration Checklist
3. Step 1: Export MySQL Schema
4. Step 2: Translate Schema to PostgreSQL
5. Step 3: Migrate Your Data
6. Step 4: Validate and Test
7. Common Type Mapping Issues
8. Migration Pitfalls to Avoid
9. Frequently Asked Questions

Why Migrate from MySQL to PostgreSQL?

Teams typically migrate from MySQL to PostgreSQL for these reasons:

• Advanced data types — PostgreSQL supports arrays, JSONB, UUID, INET, and custom types natively.
• Complex queries — Better CTE support, window functions, and recursive queries.
• Data integrity — Stricter constraint enforcement and transactional DDL.
• Extensions — PostGIS, pgvector, TimescaleDB, and hundreds of others.
• License — PostgreSQL's permissive license avoids Oracle's MySQL ownership concerns.

Pre-Migration Checklist

Step 1: Export MySQL Schema

Use mysqldump to export just the schema (no data) so you can review and translate it:

mysqldump --no-data --routines --events \
  -u username -p database_name > mysql_schema.sql

Review the exported file for MySQL-specific syntax:

• ENGINE=InnoDB — PostgreSQL doesn't use storage engines
• AUTO_INCREMENT — Will become SERIAL or GENERATED ALWAYS AS IDENTITY
• BACKTICK` quoting — PostgreSQL uses double quotes
• ENUM and SET types — Need special handling

Step 2: Translate Schema to PostgreSQL

This is where most migrations get stuck. MySQL and PostgreSQL have different:

• Data type names and behaviors
• Auto-increment syntax
• String quoting rules
• Index types (e.g., FULLTEXT doesn't exist in PostgreSQL)
• Constraint syntax

You have three options:

Option A: Automated Translation (Fastest)

Use the SQL Dialect Translator to convert your mysqldump output to PostgreSQL syntax. It handles type mapping, auto-increment conversion, and identifier quoting.

Option B: SchemaLens Diff (Recommended for Complex Schemas)

If you're redesigning the schema as part of migration, paste your MySQL schema and your target PostgreSQL schema into SchemaLens. It will show you exactly what's different and generate the migration SQL.

Option C: Manual Translation

For small schemas, you can manually rewrite the CREATE TABLE statements. Use the type mapping table below as a reference.

Step 3: Migrate Your Data

After translating the schema, create the tables in PostgreSQL and migrate the data.

Option 1: pgloader (Recommended)

pgloader is the gold standard for MySQL → PostgreSQL data migration. It handles type casting, index creation, and parallel loading.

pgloader mysql://user:pass@host/dbname postgresql://user:pass@host/dbname

Option 2: Custom ETL with Python

For complex transformations, use Python with SQLAlchemy and pandas:

import pandas as pd
from sqlalchemy import create_engine

mysql = create_engine('mysql+pymysql://user:pass@host/db')
postgres = create_engine('postgresql://user:pass@host/db')

df = pd.read_sql('SELECT * FROM users', mysql)
df.to_sql('users', postgres, if_exists='append', index=False)

Option 3: CSV Export/Import

For small datasets, export to CSV and use PostgreSQL's COPY command:

-- MySQL
SELECT * INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM users;

-- PostgreSQL
COPY users FROM '/tmp/users.csv' CSV HEADER;

Step 4: Validate and Test

Never skip validation. Here's a systematic approach:

Schema Validation

• Compare table counts: SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbname';
• Compare column counts per table
• Verify all indexes were recreated
• Check foreign key constraints

Data Validation

• Row count verification per table
• Checksum sampling on critical tables
• Verify NULL constraints are respected
• Test application queries against PostgreSQL

Use SchemaLens for Diff Validation

Export both schemas and diff them to catch anything you missed:

-- MySQL
mysqldump --no-data -u user -p mysql_db > mysql_schema.sql

-- PostgreSQL
pg_dump --schema-only -U user postgres_db > postgres_schema.sql

Then paste both into SchemaLens to see a visual diff of any remaining differences.

Common Type Mapping Issues

MySQL Type	PostgreSQL Type	Notes
INT AUTO_INCREMENT	SERIAL	Use BIGSERIAL for BIGINT
TINYINT(1)	BOOLEAN	MySQL often uses this for booleans
VARCHAR(n)	VARCHAR(n)	No change needed
TEXT	TEXT	No change needed
LONGTEXT	TEXT	PostgreSQL TEXT is unlimited
DATETIME	TIMESTAMP	Or TIMESTAMPTZ with timezone
JSON	JSONB	JSONB is faster and indexable
ENUM	VARCHAR + CHECK	PostgreSQL has no native ENUM in CREATE TABLE
SET	TEXT or array	No direct equivalent
BLOB	BYTEA	Binary data storage
FLOAT	REAL	Or DOUBLE PRECISION
DOUBLE	DOUBLE PRECISION	No change needed
DECIMAL(p,s)	NUMERIC(p,s)	No change needed
BIT	BOOLEAN	Or BIT if you need bit fields

Migration Pitfalls to Avoid

• Assuming IDENTICAL behavior — VARCHAR behaves similarly but not identically. PostgreSQL is stricter about trailing spaces.
• Forgetting to recreate indexes — pgloader recreates some, but custom or partial indexes may need manual work.
• Case sensitivity — PostgreSQL folds unquoted identifiers to lowercase. UserName becomes username unless quoted.
• Division behavior — SELECT 1/4 returns 0 in MySQL but 0.25 in PostgreSQL (integer vs numeric division).
• GROUP BY requirements — PostgreSQL requires all non-aggregated SELECT columns to be in GROUP BY. MySQL is more permissive.
• Full-text search — MySQL's MATCH ... AGAINST doesn't exist in PostgreSQL. Use tsvector and tsquery instead.
• String concatenation — CONCAT() works in both, but || is the standard in PostgreSQL.

Frequently Asked Questions

How long does a MySQL to PostgreSQL migration take?

For schemas under 50 tables with a few GB of data, plan for 4-8 hours of focused work. Large schemas (500+ tables) or TB-scale data can take days or weeks. The schema translation is usually the fastest part; data migration and testing take the most time.

Can I migrate without downtime?

Yes, using a dual-write pattern: write to both MySQL and PostgreSQL during a transition period, then switch reads to PostgreSQL once validated. Tools like Debezium can help with CDC (Change Data Capture).

What about stored procedures and triggers?

These must be manually rewritten. MySQL uses SQL/PSM syntax while PostgreSQL uses PL/pgSQL. The logic is often similar but function names and conventions differ.

Is pgloader free?

Yes, pgloader is open-source and free. It's the most popular tool for MySQL → PostgreSQL data migration and handles most edge cases automatically.

Do I need to change my application code?

Probably. Even with ORMs like Prisma or SQLAlchemy, you may need to update connection strings, query syntax, and handling of specific data types. Test thoroughly in a staging environment.