MySQL to PostgreSQL Migration: Complete Step-by-Step Guide

Why Migrate from MySQL to PostgreSQL?

MySQL has been a reliable workhorse for web applications for over two decades. But as applications grow and requirements become more complex, teams increasingly move to PostgreSQL. This is not just a preference shift. PostgreSQL offers concrete technical advantages that matter at scale.

True open source. PostgreSQL is released under the PostgreSQL License, a permissive open-source license with no dual-licensing complications. MySQL is owned by Oracle and has a commercial edition alongside the open-source GPL version. Teams that want full open-source guarantees choose PostgreSQL.

JSONB and advanced data types. PostgreSQL's JSONB type stores JSON as binary, making it indexable and queryable with operators like @> (contains) and -> (extract). MySQL's JSON type is slower for complex queries. PostgreSQL also supports arrays, hstore (key-value), geometric types, UUID natively, and custom types. If your application stores semi-structured data, PostgreSQL handles it natively without workarounds.

Full-text search. PostgreSQL has a mature full-text search system with tsvector, tsquery, and GIN indexes. This is not a replacement for Elasticsearch in every case, but it eliminates the need for a separate search service for many applications.

SQL standards compliance. PostgreSQL follows the SQL standard more closely than MySQL. Features like window functions, CTEs with WITH, lateral joins, and strict GROUP BY behavior are handled correctly. MySQL's lenient GROUP BY (which allows non-aggregated columns that are not in the GROUP BY clause) can silently return wrong results and is considered a bug by many engineers.

MVCC and transactional DDL. PostgreSQL uses Multi-Version Concurrency Control for all operations. DDL statements like ALTER TABLE are transactional in PostgreSQL, meaning you can roll back a schema change. MySQL does not support transactional DDL.

The migration is not trivial, but it is manageable with a structured approach. This guide walks through every step from audit to production cutover.

Key Differences to Know Before You Start

Before touching a single file, understand the data type and syntax differences that will require changes. These are the most common sources of migration errors.

Data Type Mapping

MySQL Type	PostgreSQL Equivalent	Notes
INT AUTO_INCREMENT	SERIAL or GENERATED ALWAYS AS IDENTITY	IDENTITY is preferred in modern PostgreSQL
TINYINT(1)	BOOLEAN	MySQL uses 0/1 for booleans; PostgreSQL uses TRUE/FALSE
TINYINT	SMALLINT	PostgreSQL has no TINYINT type
DATETIME	TIMESTAMP	PostgreSQL TIMESTAMP is timezone-naive by default; use TIMESTAMPTZ for timezone-aware
UNSIGNED INT	INTEGER with CHECK constraint	PostgreSQL has no UNSIGNED modifier; use CHECK (col >= 0)
ENUM('a','b')	Custom TYPE or VARCHAR with CHECK	PostgreSQL ENUMs require a CREATE TYPE; VARCHAR + CHECK is more flexible
LONGTEXT / MEDIUMTEXT	TEXT	PostgreSQL TEXT has no length limit
FLOAT(M,D)	NUMERIC(M,D)	Use NUMERIC for exact precision; FLOAT for approximate

Identifier Quoting

MySQL uses backticks (`column_name`) to quote identifiers. PostgreSQL uses double quotes ("column_name"). Unquoted identifiers in PostgreSQL are lowercased, so a table created as MyTable is stored as mytable. If you mixed case in MySQL without quoting, results will differ in PostgreSQL.

String Quoting

MySQL allows both single and double quotes for string values. PostgreSQL only allows single quotes for strings. Double quotes are reserved for identifiers. Any MySQL query using "some string value" will fail in PostgreSQL.

Step 1: Audit Your MySQL Schema

Before converting anything, get a complete picture of what you have. Run the following queries against your MySQL database to inventory your objects:

-- List all tables and their row counts
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY DATA_LENGTH DESC;

-- List all columns with data types
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- List stored procedures and functions
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database_name';

-- List triggers
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database_name';

Flag anything that will need manual attention: AUTO_INCREMENT columns, ENUM types, stored procedures, triggers, and any UNSIGNED integer columns. Build a migration checklist before you proceed.

Step 2: Convert Your Schema

This is the most detail-oriented step. Here is a side-by-side comparison of common schema patterns and their PostgreSQL equivalents.

Auto-increment Primary Keys

-- MySQL
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT NOW(),
    PRIMARY KEY (id)
);

-- PostgreSQL
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

ENUM Columns

-- MySQL
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'cancelled') NOT NULL
);

-- PostgreSQL option 1: create a custom type
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'cancelled');

CREATE TABLE orders (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status order_status NOT NULL
);

-- PostgreSQL option 2: VARCHAR with CHECK constraint (more flexible)
CREATE TABLE orders (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'processing', 'shipped', 'cancelled'))
);

Indexes

-- MySQL
CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
CREATE FULLTEXT INDEX idx_posts_body ON posts (body);

-- PostgreSQL
CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
-- Full-text search in PostgreSQL uses GIN indexes
CREATE INDEX idx_posts_body ON posts USING GIN (to_tsvector('english', body));

Foreign Keys and Constraints

Foreign key syntax is nearly identical between MySQL and PostgreSQL. The main difference is that PostgreSQL enforces foreign keys immediately by default. MySQL with InnoDB does too, but MySQL with MyISAM silently ignores them. If you are migrating from MyISAM tables, expect foreign key errors on data import if referential integrity was violated.

Step 3: Export MySQL Data

Use mysqldump to export your data. Choose your options carefully to produce output that is easier to transform for PostgreSQL:

# Export schema only (no data) for review
mysqldump --no-data \
  --single-transaction \
  --routines \
  --triggers \
  -u your_user -p your_database > schema_only.sql

# Export data only (no CREATE TABLE statements)
mysqldump --no-create-info \
  --single-transaction \
  --skip-extended-insert \
  --complete-insert \
  -u your_user -p your_database > data_only.sql

# Full export with compatible options for transformation
mysqldump \
  --single-transaction \
  --skip-comments \
  --skip-extended-insert \
  --complete-insert \
  --default-character-set=utf8mb4 \
  -u your_user -p your_database > full_export.sql

The --skip-extended-insert flag generates one INSERT per row instead of multi-row inserts. This makes the output easier to inspect and transform. The --single-transaction flag ensures a consistent snapshot on InnoDB tables without locking the database during export.

Step 4: Transform SQL Syntax

The exported MySQL dump will not run directly in PostgreSQL. You need to transform the syntax. Here are the most common transformations required.

Backtick Identifiers to Double Quotes

-- MySQL (from mysqldump output)
INSERT INTO `users` (`id`, `email`, `created_at`) VALUES (1, 'alice@example.com', '2026-01-15 10:30:00');

-- PostgreSQL
INSERT INTO "users" ("id", "email", "created_at") VALUES (1, 'alice@example.com', '2026-01-15 10:30:00');

For large files, use sed to replace backticks. However, be careful: a simple global replace can break string literals that contain backticks. Use a proper SQL parser or pgLoader for reliable transformation.

LIMIT and OFFSET

-- MySQL
SELECT * FROM products ORDER BY created_at DESC LIMIT 10, 20;
-- MySQL syntax: LIMIT offset, count

-- PostgreSQL
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 10;
-- PostgreSQL syntax: LIMIT count OFFSET offset

GROUP BY Strictness

-- MySQL (this works due to lenient GROUP BY)
SELECT user_id, email, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;
-- email is not in GROUP BY and not aggregated — MySQL picks an arbitrary value

-- PostgreSQL (this fails with ERROR: column must appear in GROUP BY)
-- Correct version:
SELECT user_id, email, COUNT(*) AS order_count
FROM orders
JOIN users ON orders.user_id = users.id
GROUP BY user_id, email;

Boolean Values

-- MySQL (using TINYINT for booleans)
SELECT * FROM users WHERE is_active = 1;
INSERT INTO users (email, is_active) VALUES ('bob@example.com', 1);

-- PostgreSQL (using BOOLEAN type)
SELECT * FROM users WHERE is_active = TRUE;
INSERT INTO users (email, is_active) VALUES ('bob@example.com', TRUE);

Date and Time Functions

-- MySQL date functions
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month FROM orders;
SELECT DATEDIFF(end_date, start_date) FROM projects;
SELECT DATE_ADD(created_at, INTERVAL 7 DAY) FROM subscriptions;

-- PostgreSQL equivalents
SELECT TO_CHAR(created_at, 'YYYY-MM') AS month FROM orders;
SELECT (end_date - start_date) AS days FROM projects;
SELECT created_at + INTERVAL '7 days' FROM subscriptions;

String Concatenation

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- PostgreSQL (both work, but || is more idiomatic)
SELECT first_name || ' ' || last_name AS full_name FROM users;
-- CONCAT() also works in PostgreSQL 9.1+

Step 5: Import to PostgreSQL

Once the schema and data files are transformed, import them into your PostgreSQL database.

# Create the target database
createdb -U postgres your_database

# Import schema first
psql -U postgres -d your_database -f schema_converted.sql

# Import data
psql -U postgres -d your_database -f data_converted.sql

# If you have a pg_dump-format file (from pgLoader or AWS DMS)
pg_restore -U postgres -d your_database -F c backup.dump

# Monitor import progress for large files
psql -U postgres -d your_database \
  -c "SELECT schemaname, tablename, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;"

Import the schema before the data. Import data before adding indexes and foreign key constraints. This order is significantly faster because PostgreSQL does not need to maintain index integrity during bulk inserts. Add indexes and constraints after the data is loaded.

Step 6: Migrate Stored Procedures and Triggers

This is the most labor-intensive part of the migration. MySQL stored procedures use a syntax that does not translate directly to PostgreSQL's PL/pgSQL.

Stored Procedure Conversion

-- MySQL stored procedure
DELIMITER //
CREATE PROCEDURE get_user_orders(IN user_id_param INT)
BEGIN
    SELECT o.id, o.total_amount, o.created_at
    FROM orders o
    WHERE o.user_id = user_id_param
    ORDER BY o.created_at DESC;
END //
DELIMITER ;

-- PostgreSQL equivalent (PL/pgSQL function)
CREATE OR REPLACE FUNCTION get_user_orders(user_id_param INTEGER)
RETURNS TABLE(id INTEGER, total_amount NUMERIC, created_at TIMESTAMP)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, o.total_amount, o.created_at
    FROM orders o
    WHERE o.user_id = user_id_param
    ORDER BY o.created_at DESC;
END;
$$;

Trigger Conversion

-- MySQL trigger
CREATE TRIGGER update_modified_at
BEFORE UPDATE ON users
FOR EACH ROW
SET NEW.updated_at = NOW();

-- PostgreSQL trigger (requires a function first)
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;

CREATE TRIGGER update_modified_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Key differences: PostgreSQL triggers call a trigger function rather than containing inline code. The trigger function must return TRIGGER type and use RETURN NEW for row-level before triggers. MySQL's DELIMITER syntax does not exist in PostgreSQL.

Step 7: Update Your Application Connection String

After verifying the data import, update your application to point to PostgreSQL. The connection string format changes between drivers.

# MySQL connection strings
# Node.js (mysql2)
mysql://user:password@localhost:3306/database

# Python (PyMySQL / mysql-connector)
mysql+pymysql://user:password@localhost:3306/database

# PostgreSQL connection strings
# Node.js (pg / node-postgres)
postgresql://user:password@localhost:5432/database

# Python (psycopg2 / asyncpg)
postgresql+psycopg2://user:password@localhost:5432/database

# Environment variable format (works with most ORMs)
DATABASE_URL=postgresql://user:password@localhost:5432/database

If you use an ORM like Prisma, Sequelize, SQLAlchemy, or ActiveRecord, update the dialect or provider setting as well. In Prisma, change provider = "mysql" to provider = "postgresql" in your schema.prisma file. In SQLAlchemy, swap the database URL prefix. Most ORMs support both databases and handle the dialect differences internally once you update the connection string.

Common Migration Errors and Fixes

Even with careful preparation, you will hit errors during import. Here are the most common ones and how to fix them.

Duplicate Key Value Violates Unique Constraint

-- Error
ERROR: duplicate key value violates unique constraint "users_pkey"
DETAIL: Key (id)=(1) already exists.

-- Cause: sequence out of sync after bulk import
-- Fix: reset the sequence to the max existing value
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

Column Cannot Be Cast Automatically

-- Error
ERROR: column "is_active" is of type boolean but expression is of type integer

-- Fix: explicitly cast in your INSERT or UPDATE
-- Option 1: update the INSERT to use TRUE/FALSE
INSERT INTO users (email, is_active) VALUES ('test@example.com', TRUE);

-- Option 2: cast explicitly if updating from an old value
UPDATE users SET is_active = is_active::boolean;

Case Sensitivity on Table and Column Names

-- Error
ERROR: relation "Users" does not exist

-- Cause: MySQL is case-insensitive for identifiers; PostgreSQL lowercases unquoted identifiers
-- A table created as "Users" in MySQL becomes "users" in PostgreSQL

-- Fix option 1: query with lowercase (recommended)
SELECT * FROM users;

-- Fix option 2: create with double quotes if you need mixed case (not recommended)
SELECT * FROM "Users";

Null Handling Differences

-- MySQL: empty string and NULL are sometimes treated similarly
-- PostgreSQL: '' (empty string) and NULL are always distinct

-- If MySQL code treated '' as NULL, add explicit handling
UPDATE users SET middle_name = NULL WHERE middle_name = '';

-- PostgreSQL NULLIF function is useful for this
UPDATE users SET middle_name = NULLIF(middle_name, '');

Operator Does Not Exist for Mixed Types

-- Error
ERROR: operator does not exist: integer = text

-- Cause: PostgreSQL does not do implicit type casting the way MySQL does
-- MySQL: WHERE id = '42' works (casts string to integer)
-- PostgreSQL: strict type matching required

-- Fix: cast explicitly
SELECT * FROM users WHERE id = '42'::integer;
-- Or update the query to use the correct type
SELECT * FROM users WHERE id = 42;

Tools That Automate the Migration

Manual transformation works for small databases, but automated tools save significant time on larger migrations.

pgLoader

pgLoader is the most commonly used tool for MySQL to PostgreSQL migrations. It handles data type conversion, identifier quoting, and data streaming in one step.

-- pgLoader command file: migrate.load
LOAD DATABASE
    FROM mysql://user:password@localhost/source_db
    INTO postgresql://user:password@localhost/target_db

WITH include drop, create tables, create indexes,
     reset sequences, foreign keys

SET PostgreSQL PARAMETERS
    maintenance_work_mem to '128MB',
    work_mem to '64MB'

CAST type tinyint to boolean using tinyint-to-boolean,
     type datetime to timestamptz using zero-dates-to-null;

-- Run the migration
pgloader migrate.load

pgLoader handles the CAST rules automatically for common type mappings. The zero-dates-to-null cast handles MySQL's invalid zero dates (0000-00-00) which PostgreSQL rejects entirely.

AWS Database Migration Service (DMS)

AWS DMS supports MySQL to PostgreSQL migrations with ongoing replication using change data capture. It reads the MySQL binary log and applies changes to the PostgreSQL target in near real-time. This is the preferred approach for zero-downtime migrations in production environments. DMS requires MySQL binlog enabled with binlog_format=ROW on the source.

ora2pg

Despite the name, ora2pg also handles MySQL to PostgreSQL migrations. It is particularly useful for migrating stored procedures and triggers because it attempts to rewrite PL/SQL and MySQL procedure syntax into PL/pgSQL. It will not produce perfect output for complex procedures, but it gives you a starting point that is faster to fix than writing from scratch.

# ora2pg configuration for MySQL source
# in ora2pg.conf
ORACLE_DSN  dbi:mysql:database=mydb;host=localhost
ORACLE_USER myuser
ORACLE_PWD  mypassword

# Export procedure code for review
ora2pg -t PROCEDURE -o procedures.sql

How AI2SQL Helps During Migration

One of the most time-consuming parts of a MySQL to PostgreSQL migration is not the data transfer itself — it is finding and rewriting all the MySQL-specific queries in your application code. A typical application has SQL in ORM configurations, raw query files, API handlers, reporting scripts, and background jobs. Reviewing each one manually takes days.

AI2SQL accelerates this process in two ways.

Query translation. Paste any MySQL query into AI2SQL, specify PostgreSQL as the target dialect, and get a rewritten version that uses correct PostgreSQL syntax. Backtick identifiers become double quotes. DATE_FORMAT becomes TO_CHAR. GROUP_CONCAT becomes STRING_AGG. IFNULL becomes COALESCE. The translation covers syntax differences without you needing to memorize every mapping.

-- MySQL query you paste into AI2SQL
SELECT
    u.`user_id`,
    u.`email`,
    GROUP_CONCAT(t.`tag_name` ORDER BY t.`tag_name` SEPARATOR ', ') AS tags,
    IFNULL(SUM(o.`total`), 0) AS lifetime_value,
    DATE_FORMAT(u.`created_at`, '%Y-%m') AS signup_month
FROM `users` u
LEFT JOIN `orders` o ON u.`user_id` = o.`user_id`
LEFT JOIN `user_tags` ut ON u.`user_id` = ut.`user_id`
LEFT JOIN `tags` t ON ut.`tag_id` = t.`tag_id`
GROUP BY u.`user_id`, u.`email`, u.`created_at`
ORDER BY lifetime_value DESC;

-- AI2SQL output for PostgreSQL
SELECT
    u.user_id,
    u.email,
    STRING_AGG(t.tag_name, ', ' ORDER BY t.tag_name) AS tags,
    COALESCE(SUM(o.total), 0) AS lifetime_value,
    TO_CHAR(u.created_at, 'YYYY-MM') AS signup_month
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
LEFT JOIN user_tags ut ON u.user_id = ut.user_id
LEFT JOIN tags t ON ut.tag_id = t.tag_id
GROUP BY u.user_id, u.email, u.created_at
ORDER BY lifetime_value DESC;

Query explanation. Use AI2SQL's explain tool to understand what a complex MySQL stored procedure does before you rewrite it in PL/pgSQL. Paste the procedure body and ask for a plain-English explanation of each step. This is especially useful when migrating legacy code where the original author is no longer available.

During migration, AI2SQL works like a pair programmer who knows both MySQL and PostgreSQL syntax cold. You handle the infrastructure and testing; AI2SQL handles the syntax translation.

Frequently Asked Questions

How long does a MySQL to PostgreSQL migration take?

The time depends on database size and complexity. A small database (under 1 GB) with straightforward schemas can be migrated in a few hours using pgLoader or AWS DMS. Larger databases with many stored procedures, triggers, and complex data types may take days of planning and testing. Allow extra time for application-level query rewrites and validation testing in a staging environment before production cutover.

What is the biggest difference between MySQL and PostgreSQL SQL syntax?

The most impactful syntax differences are: backtick identifiers in MySQL become double-quoted identifiers in PostgreSQL; AUTO_INCREMENT becomes SERIAL or GENERATED ALWAYS AS IDENTITY; MySQL's lenient GROUP BY becomes strict in PostgreSQL; ENUM types behave differently; and boolean values TRUE/FALSE replace MySQL's 1/0 in many contexts. PostgreSQL is also much stricter about data type casting, so implicit conversions that work in MySQL will often throw errors in PostgreSQL.

Can I migrate from MySQL to PostgreSQL without downtime?

Yes, a near-zero-downtime migration is possible using change data capture (CDC) tools like AWS DMS or Debezium. The approach is: perform an initial bulk load of existing data to PostgreSQL, then set up CDC to stream ongoing changes from MySQL binlog to PostgreSQL, validate data consistency, update the application connection string, and cut over DNS or load balancer in seconds. This keeps the MySQL instance live as a fallback until you are confident in the migration.

Does AI2SQL help with MySQL to PostgreSQL query conversion?

Yes. AI2SQL can translate MySQL queries to PostgreSQL syntax instantly. You paste a MySQL query, specify the target dialect as PostgreSQL, and AI2SQL rewrites backtick identifiers, converts AUTO_INCREMENT to SERIAL, adjusts GROUP BY strictness, rewrites date functions like NOW() and DATE_FORMAT, and handles LIMIT/OFFSET correctly. This is especially useful when you have hundreds of queries in application code that need to be reviewed and updated during migration.