Migrating from PostgreSQL to MySQL: Step-by-Step Guide

Understanding PostgreSQL to MySQL Migration

Database migration is the process of transferring data, schema definitions, stored procedures, triggers, and other database objects from one database management system to another. Migrating from PostgreSQL to MySQL involves converting PostgreSQL-specific data types, functions, SQL syntax, and procedural logic into their MySQL-compatible equivalents while preserving the integrity, relationships, and meaning of the original data.

This migration path is commonly undertaken when organizations standardize on MySQL for cost reasons, hosting simplicity, or integration with applications built around the MySQL ecosystem. While both PostgreSQL and MySQL are robust relational database systems, they differ significantly in their feature sets, default behaviors, and extension mechanisms, making a direct lift-and-shift approach rarely feasible without careful planning.

Why Migration from PostgreSQL to MySQL Matters

🚀 Deploy your AI agent in 10 minutes

Managed Hermes hosting. Zero DevOps. 100M tokens/mo included.

Try it free →

Several business and technical factors drive organizations to migrate from PostgreSQL to MySQL:

Hosting and managed service availability: Many cloud providers and shared hosting platforms offer MySQL as a default or lower-cost option, with mature managed services like Amazon RDS for MySQL, Google Cloud SQL for MySQL, and Azure Database for MySQL.
Application framework compatibility: Popular content management systems, e-commerce platforms, and PHP-based frameworks often ship with native MySQL drivers and optimized query patterns for MySQL's storage engines.
Operational familiarity: Development teams may have deeper MySQL expertise, making ongoing maintenance, backup, and performance tuning more efficient.
Replication and scaling models: MySQL's replication topology options, including group replication and InnoDB Cluster, appeal to teams seeking specific high-availability architectures.
License alignment: While PostgreSQL uses the permissive PostgreSQL license, MySQL's dual-licensing under GPL and commercial terms may align better with certain organizational compliance requirements.

Pre-Migration Assessment and Planning

Cataloging PostgreSQL-Specific Features

Before writing any migration scripts, you must inventory every PostgreSQL feature your application depends on. Create a comprehensive checklist covering:

Custom data types (JSONB, arrays, ENUMs defined with CREATE TYPE, custom domains)
PostgreSQL extensions (PostGIS, pgcrypto, uuid-ossp, hstore, pg_trgm)
Stored procedures and functions written in PL/pgSQL or other procedural languages
Triggers and trigger functions with PostgreSQL-specific timing options (INSTEAD OF, DEFERRED)
Sequence objects and SERIAL/BIGSERIAL pseudo-types
Window functions, CTEs with recursive modifiers, and LATERAL joins
Full-text search configurations using tsvector and tsquery
Row-level security policies
Partitioning strategies (declarative partitioning by range, list, or hash)
Schemas as namespace containers (MySQL uses databases as equivalent namespaces)

Compatibility Matrix Development

Build a mapping document that pairs each PostgreSQL feature with its MySQL equivalent or workaround. For features without direct equivalents — such as JSONB with full indexing support, array columns, or custom ENUM types — you will need to plan schema redesigns or application-level adaptations.

Step-by-Step Migration Process

Step 1: Export the PostgreSQL Schema

Begin by extracting the complete DDL (Data Definition Language) from your PostgreSQL database. Use pg_dump with schema-only flags to avoid exporting data prematurely:

# Export schema only, excluding owner and privilege statements
pg_dump --host=localhost --port=5432 --username=postgres \
  --dbname=source_db --schema-only --no-owner --no-privileges \
  --file=postgres_schema.sql

This generates a SQL file containing CREATE TABLE, CREATE INDEX, CREATE FUNCTION, CREATE TRIGGER, CREATE SEQUENCE, and other DDL statements exactly as PostgreSQL expects them. Review this file carefully — it will serve as your primary reference throughout the conversion.

Step 2: Analyze and Categorize Schema Objects

Open the exported schema file and categorize every statement into conversion tiers:

Tier 1 — Directly convertible: Standard SQL types like INTEGER, VARCHAR, TEXT, DATE, TIMESTAMP that map cleanly to MySQL equivalents.
Tier 2 — Requires type remapping: SERIAL → INT AUTO_INCREMENT, BIGSERIAL → BIGINT AUTO_INCREMENT, BOOLEAN → TINYINT(1), UUID → CHAR(36) or VARCHAR(36).
Tier 3 — Requires schema redesign: JSONB columns with GIN indexes, array types (INTEGER[]), custom ENUMs, composite types.
Tier 4 — Requires application-level reimplementation: Row-level security policies, triggers using DEFERRED constraints, recursive CTEs with complex logic, PostGIS spatial functions.

Step 3: Convert Data Types Systematically

Work through each table definition, converting PostgreSQL data types to MySQL-compatible equivalents. Below is a practical conversion reference for common types:

-- PostgreSQL original
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email TEXT,
    is_active BOOLEAN DEFAULT true,
    preferences JSONB,
    tags TEXT[],
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- MySQL equivalent
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email TEXT,
    is_active TINYINT(1) DEFAULT 1,
    preferences JSON,
    tags TEXT COMMENT 'Array stored as comma-separated or JSON array',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Pay special attention to SERIAL and BIGSERIAL. PostgreSQL implements these as auto-incrementing integer columns backed by sequences. MySQL uses AUTO_INCREMENT on the column itself, with only one auto-increment column permitted per table. For tables with multiple SERIAL columns, you must decide which becomes the physical auto-increment and handle others through application-generated values or BEFORE INSERT triggers.

Step 4: Handle ENUM Types and Check Constraints

PostgreSQL supports both ENUM types created with CREATE TYPE and inline CHECK constraints. MySQL offers native ENUM columns, but migration requires careful mapping:

-- PostgreSQL custom ENUM
CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

-- MySQL conversion using native ENUM
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'confirmed', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending'
) ENGINE=InnoDB;

If the PostgreSQL source uses CHECK constraints instead of ENUM types, MySQL 8.0+ supports CHECK constraints natively (though enforced only since MySQL 8.0.16). For earlier MySQL versions, you may need ENUM columns or application-level validation.

Step 5: Convert Indexes Including PostgreSQL-Specific Index Types

PostgreSQL offers index types that MySQL handles differently or lacks entirely. Convert each index type explicitly:

-- PostgreSQL GIN index on JSONB (for full indexing of JSON keys/values)
CREATE INDEX idx_preferences ON users USING GIN (preferences jsonb_path_ops);

-- MySQL: No GIN equivalent; use generated columns with regular indexes
ALTER TABLE users 
  ADD COLUMN pref_keys TEXT GENERATED ALWAYS AS (JSON_KEYS(preferences)) STORED,
  ADD INDEX idx_pref_keys (pref_keys);

-- PostgreSQL partial index
CREATE INDEX idx_active_users ON users (email) WHERE is_active = true;

-- MySQL: Partial indexes not supported; use full index or application filtering
-- Workaround: Create indexed view alternative or indexed generated column
ALTER TABLE users
  ADD COLUMN active_email VARCHAR(255) 
    GENERATED ALWAYS AS (IF(is_active = 1, email, NULL)) STORED,
  ADD INDEX idx_active_email (active_email);

For standard B-tree indexes, the syntax translates cleanly. For PostgreSQL BRIN, GiST, SP-GiST, or GIN indexes, you must either use MySQL's spatial indexes (for geometric data), FULLTEXT indexes (for text search), or restructure the schema with generated columns and standard B-tree indexes.

Step 6: Migrate Functions and Stored Procedures

PostgreSQL functions written in PL/pgSQL require full rewriting to MySQL's procedural SQL syntax. The languages differ in variable declaration, exception handling, cursor management, and return semantics:

-- PostgreSQL PL/pgSQL function
CREATE OR REPLACE FUNCTION calculate_order_total(p_order_id INT)
RETURNS NUMERIC AS $$
DECLARE
    v_total NUMERIC := 0;
    v_item_record RECORD;
BEGIN
    FOR v_item_record IN
        SELECT quantity, unit_price FROM order_items WHERE order_id = p_order_id
    LOOP
        v_total := v_total + (v_item_record.quantity * v_item_record.unit_price);
    END LOOP;
    
    -- Apply discount if total exceeds threshold
    IF v_total > 1000 THEN
        v_total := v_total * 0.9;
    END IF;
    
    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- MySQL stored function equivalent
DELIMITER $$

CREATE FUNCTION calculate_order_total(p_order_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_total DECIMAL(10,2) DEFAULT 0;
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_qty INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE cur CURSOR FOR 
        SELECT quantity, unit_price FROM order_items WHERE order_id = p_order_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_qty, v_price;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        SET v_total = v_total + (v_qty * v_price);
    END LOOP;
    CLOSE cur;
    
    IF v_total > 1000 THEN
        SET v_total = v_total * 0.9;
    END IF;
    
    RETURN v_total;
END$$

DELIMITER ;

Key differences to address include: explicit cursor handling with handler declarations, DELIMITER changes for statement termination, DETERMINISTIC or NOT DETERMINISTIC classification, READS SQL DATA or MODIFIES SQL DATA annotations, and the absence of RECORD type iteration — requiring explicit cursor FETCH loops.

Step 7: Convert Triggers and Trigger Functions

PostgreSQL allows trigger functions to be defined separately and reused across multiple triggers. MySQL requires the trigger body to be inline. Additionally, PostgreSQL supports statement-level triggers and INSTEAD OF triggers for views, which MySQL lacks:

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

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

-- MySQL equivalent: inline trigger body
DELIMITER $$

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END$$

DELIMITER ;

For PostgreSQL's INSTEAD OF triggers on views, you must redesign the application layer to handle updates directly against base tables, or create updatable views in MySQL using the merge algorithm with simple one-to-one mappings.

Step 8: Address Schema Namespaces

PostgreSQL organizes objects within schemas inside a single database. MySQL treats databases as the equivalent namespace container. A PostgreSQL setup with schemas like auth, billing, and reporting within one database translates to separate MySQL databases (auth, billing, reporting) or a flattened structure with naming conventions:

-- PostgreSQL cross-schema reference
SELECT * FROM auth.users JOIN billing.invoices ON auth.users.id = billing.invoices.user_id;

-- MySQL equivalent with separate databases
SELECT * FROM auth.users JOIN billing.invoices ON auth.users.id = billing.invoices.user_id;

-- MySQL equivalent with flattened single database
-- Tables renamed: auth_users, billing_invoices, etc.
SELECT * FROM auth_users JOIN billing_invoices ON auth_users.id = billing_invoices.user_id;

Step 9: Export and Transform Data

Once the target MySQL schema is created and validated, extract PostgreSQL data in a format suitable for MySQL import. Use pg_dump with data-only flags and the --inserts option to produce standard INSERT statements:

# Export data as standard SQL INSERT statements, avoiding COPY format
pg_dump --host=localhost --port=5432 --username=postgres \
  --dbname=source_db --data-only --inserts --no-owner \
  --file=postgres_data.sql

Before importing, you must transform data that uses PostgreSQL-specific syntax. Common transformations include:

Converting TRUE/FALSE literals to 1/0 for boolean columns mapped to TINYINT(1)
Escaping or converting array literals like '{apple,banana,cherry}'::text[] into JSON arrays or comma-separated strings
Converting uuid_generate_v4() default expressions to pre-generated UUID strings
Replacing PostgreSQL bytea hex escapes with MySQL-compatible binary representations

A sed-based transformation pipeline can handle many replacements automatically:

# Example transformation script for data SQL file
sed -i \
  -e "s/'t'/1/g" \
  -e "s/'f'/0/g" \
  -e "s/TRUE/1/gi" \
  -e "s/FALSE/0/gi" \
  -e "s/::text\[\]//g" \
  -e "s/NULL::character varying/NULL/g" \
  postgres_data.sql

After transformation, import the data into MySQL:

# Import transformed data into MySQL
mysql --host=localhost --port=3306 --user=root --password \
  --database=target_db --default-character-set=utf8mb4 \
  < postgres_data.sql

Step 10: Recreate Sequences and Auto-Increment Values

After data import, MySQL auto-increment columns may start from incorrect values if sequences were not preserved. Reset each table's auto-increment pointer to match the highest existing ID:

-- For each table with an auto-increment column, reset the counter
ALTER TABLE users AUTO_INCREMENT = (
    SELECT COALESCE(MAX(id), 0) + 1 FROM users
);

ALTER TABLE orders AUTO_INCREMENT = (
    SELECT COALESCE(MAX(id), 0) + 1 FROM orders
);

You can generate these statements dynamically using MySQL's information_schema or a scripting language to iterate through all tables containing auto-increment columns.

Step 11: Validate Data Integrity

After migration, run comprehensive validation queries comparing row counts, checksums, and sample data between the PostgreSQL source and MySQL target. A systematic approach includes:

-- Row count validation for each table
-- Run on PostgreSQL source:
SELECT 'users' AS table_name, COUNT(*) AS row_count FROM users
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;

-- Run identical query on MySQL target and compare results

-- Checksum validation (numeric columns)
-- PostgreSQL:
SELECT 'users', SUM(COALESCE(id, 0)) FROM users;

-- MySQL:
SELECT 'users', SUM(COALESCE(id, 0)) FROM users;

For large datasets, consider checksumming text columns with hashing functions or exporting both systems' data to CSV and using external diff tools for byte-level comparison.

Best Practices for PostgreSQL to MySQL Migration

1. Never Migrate Without a Full Backup

Always create a complete, verified backup of your PostgreSQL source database before initiating any migration activities. Use pg_dump with both schema and data, plus a binary backup via pg_basebackup if possible. The migration process should never modify the source database, ensuring a reliable rollback path.

2. Use a Staging Environment First

Execute the entire migration process in an isolated staging environment before touching production systems. This allows you to catch conversion errors, performance regressions, and application compatibility issues without impacting live users. Run your full application test suite against the staged MySQL database to validate functional equivalence.

3. Break Migration into Iterative Phases

For large databases with hundreds of tables, migrate in logical chunks — perhaps by schema/domain module — rather than attempting a single monolithic cutover. This reduces risk, simplifies debugging, and allows incremental validation. Start with reference/lookup tables, proceed through transaction tables, and finish with reporting or analytical structures.

4. Maintain Dual Database Access During Transition

Implement an abstraction layer or feature flag system in your application that allows reading from or writing to both PostgreSQL and MySQL during a transition period. This pattern supports gradual traffic shifting, A/B testing of query performance, and immediate rollback if MySQL exhibits unexpected behavior under production load.

5. Profile Query Performance Proactively

MySQL's query optimizer behaves differently from PostgreSQL's planner. Queries that performed well on PostgreSQL may require index adjustments, query rewriting, or hint usage on MySQL. Enable MySQL's slow query log, use EXPLAIN extensively on migrated queries, and compare execution plans against PostgreSQL EXPLAIN ANALYZE output to identify regressions early.

6. Handle Transaction Isolation Differences

PostgreSQL defaults to READ COMMITTED isolation with a non-blocking MVCC implementation. MySQL's InnoDB also uses MVCC, but behavior around implicit commits, DDL transactions, and lock escalation differs. Review your application's transaction boundaries — especially any code relying on PostgreSQL's ability to roll back DDL within transactions, which MySQL does not support.

7. Plan for Extension Functionality Gaps

If your PostgreSQL application uses extensions like PostGIS for geospatial queries, pgcrypto for cryptographic functions, or pg_trgm for trigram-based similarity search, you must plan equivalent functionality on MySQL. MySQL offers spatial indexes and functions (differing from PostGIS), built-in AES encryption functions, and FULLTEXT indexes with n-gram parsers — but the SQL interfaces and performance characteristics differ significantly.

8. Automate Conversion Where Possible

Manual conversion of hundreds of DDL statements is error-prone. Use schema conversion tools — such as pgloader, mysql-workbench migration utilities, or custom scripting with SQL parsers — to automate the mechanical aspects of type mapping and syntax translation. Always review automated output manually for semantic correctness, as tools cannot reason about business logic implications.

9. Document Every Conversion Decision

Maintain a migration runbook that records every non-trivial conversion choice: why a particular JSONB column became a generated column plus index, why a recursive CTE was reimplemented as a stored procedure loop, or why a specific trigger was moved to application code. This documentation is essential for future maintainers who may wonder why the MySQL schema diverges from the original PostgreSQL design.

10. Test Edge Cases Extensively

Focus testing on areas where PostgreSQL and MySQL diverge most: character set handling (UTF-8 normalization differences), timezone-aware timestamp semantics, NULL ordering in indexes (PostgreSQL NULLS FIRST/LAST), empty string versus NULL distinctions, and floating-point precision in aggregate calculations. These subtle differences often surface only under specific data conditions.

Conclusion

Migrating from PostgreSQL to MySQL is a significant engineering undertaking that requires methodical planning, systematic type and syntax conversion, and rigorous validation. Success depends on thorough pre-migration assessment — cataloging every PostgreSQL-specific feature your application uses — and a phased approach that respects the semantic differences between the two systems. By following the step-by-step process outlined above, converting data types carefully, rewriting procedural logic with attention to MySQL's syntax, and applying the best practices of staging, iterative migration, and comprehensive testing, you can achieve a reliable migration that preserves data integrity and application functionality. Remember that the goal is not merely to move data but to ensure your application operates correctly and efficiently on MySQL, which often requires rethinking schema designs and query patterns rather than pursuing literal one-to-one translation.