How to Migrate from MySQL to PostgreSQL

Schema Assessment and Inventory

Export your MySQL schema and catalog all tables, views, stored procedures, triggers, and foreign key relationships. Identify MySQL-specific features that need PostgreSQL equivalents.

mysqldump --no-data --routines --triggers mydb > schema.sql

Set Up Target PostgreSQL Instance

Install PostgreSQL, create the target database, and configure connection parameters. Install pgloader or your chosen migration tool.

createdb target_db
psql target_db

Convert Schema

Convert the MySQL DDL to PostgreSQL-compatible DDL. Replace data types, AUTO_INCREMENT with SERIAL, ENUM types, backticks with double quotes, and MySQL-specific options.

-- MySQL:
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

-- PostgreSQL:
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

Migrate Data

Use pgloader for automated, efficient data migration. It handles type conversions automatically and provides detailed error reports.

-- pgloader command file:
LOAD DATABASE
  FROM mysql://user:pass@localhost/source_db
  INTO postgresql://user:pass@localhost/target_db
SET work_mem to '16MB',
    maintenance_work_mem to '512MB';

Migrate Stored Procedures and Functions

MySQL stored procedures use a different syntax than PostgreSQL PL/pgSQL. Each procedure must be manually rewritten for PostgreSQL.

-- MySQL Procedure
CREATE PROCEDURE GetUser(IN userId INT)
BEGIN
  SELECT * FROM users WHERE id = userId;
END;

-- PostgreSQL Function
CREATE OR REPLACE FUNCTION get_user(p_user_id INT)
RETURNS TABLE(id INT, email VARCHAR) AS $$
BEGIN
  RETURN QUERY SELECT u.id, u.email FROM users u WHERE u.id = p_user_id;
END;
$$ LANGUAGE plpgsql;

Update Application Queries

Update application code: replace backtick quoting with double quotes or no quotes, replace MySQL-specific functions with PostgreSQL equivalents, fix GROUP BY strictness issues, and update UPSERT syntax.

Validate Data Integrity

Compare row counts between source MySQL and target PostgreSQL for all tables. Spot-check critical business records. Run application integration tests against the PostgreSQL database.

-- Run in both MySQL and PostgreSQL, compare results:
SELECT table_name, COUNT(*)
FROM information_schema.tables
JOIN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'mydb') t USING(table_name)
GROUP BY table_name;

Performance Testing and Index Optimization

Run EXPLAIN ANALYZE on your most critical queries. PostgreSQL's query planner may choose different strategies than MySQL. Add or adjust indexes as needed. Run ANALYZE to update table statistics.

ANALYZE;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Cutover

Plan the cutover: take the MySQL database read-only, run a final incremental sync to PostgreSQL, update application connection strings, switch DNS/load balancer, and monitor closely for 24-48 hours.