--- # === IDENTITY === id: software/migrations/mysql-to-postgresql/2026 canonical_question: "How do I migrate from MySQL to PostgreSQL?" aliases: - "MySQL to PostgreSQL migration" - "convert MySQL to Postgres" - "MySQL to Postgres guide" - "switch from MySQL to PostgreSQL" - "migrate MySQL database to PostgreSQL" - "MySQL to PostgreSQL data type mapping" - "pgloader MySQL migration" - "MySQL to Postgres schema conversion" entity_type: software_reference domain: software > migrations > mysql_to_postgresql region: global jurisdiction: global temporal_scope: 2020-2026 # === VERIFICATION === last_verified: 2026-05-17 confidence: 0.93 freshness: stable version: 2.1 first_published: 2026-02-17 # === TEMPORAL VALIDITY === temporal_validity: status: stable last_breaking_change: "PostgreSQL 18 (2025-09-25)" next_review: 2026-11-13 change_sensitivity: low # === CONSTRAINTS === constraints: - "pgloader does NOT migrate stored procedures, triggers, event schedulers, or views with logic — these must be rewritten manually in PL/pgSQL" - "MySQL zero-date values ('0000-00-00') must be cleaned or cast to NULL before migration — PostgreSQL rejects them outright" - "Never run pgloader with 'include drop' in production against a database that already contains data you want to keep — it drops and recreates all target tables" - "AWS DMS does not migrate secondary indexes, foreign keys, or constraints during initial full load — apply these after full load completes" - "MySQL ENUM columns are migrated to VARCHAR by pgloader — create PostgreSQL native ENUM types manually if needed" - "Always reset sequences after bulk data load — failing to do so causes duplicate key errors on the next INSERT" # === SKIP CONDITIONS === skip_this_unit_if: - condition: "User wants to migrate SQL Server to PostgreSQL" use_instead: "software/migrations/sqlserver-to-postgresql/2026" - condition: "User wants to migrate Oracle to PostgreSQL" use_instead: "software/migrations/oracle-to-postgresql/2026" - condition: "User wants to migrate MongoDB to PostgreSQL" use_instead: "software/migrations/mongodb-to-postgresql/2026" - condition: "User is debugging slow queries on an existing PostgreSQL database (not migrating)" use_instead: "software/debugging/postgresql-slow-queries/2026" # === AGENT HINTS === inputs_needed: - key: database_size question: "What is the approximate size of the MySQL database?" type: choice options: ["Small (<10 GB)", "Medium (10-100 GB)", "Large (>100 GB)"] - key: downtime_tolerance question: "Is planned downtime acceptable during migration?" type: choice options: ["Yes — maintenance window available", "No — need near-zero downtime (CDC)"] - key: stored_procedures question: "Does the database use stored procedures, triggers, or complex views?" type: choice options: ["Yes — significant procedural logic", "No — mostly schema and data", "Not sure"] # === DISTRIBUTION === canonical_source: "https://knowledgelib.io/software/migrations/mysql-to-postgresql/2026" suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-05-17)" # === RELATED UNITS === related_kos: related_to: - id: "software/migrations/sqlserver-to-postgresql/2026" label: "SQL Server to PostgreSQL Migration" - id: "software/migrations/oracle-to-postgresql/2026" label: "Oracle to PostgreSQL Migration" - id: "software/migrations/mongodb-to-postgresql/2026" label: "MongoDB to PostgreSQL Migration" - id: "software/debugging/postgresql-slow-queries/2026" label: "PostgreSQL Slow Query Debugging" - id: "software/debugging/postgresql-index-bloat/2026" label: "PostgreSQL Index Bloat Diagnostic Guide" - id: "software/debugging/postgresql-connection-pool/2026" label: "PostgreSQL Connection Pool Exhaustion" alternative_to: - id: "software/migrations/sqlserver-to-postgresql/2026" label: "SQL Server to PostgreSQL (different source engine)" often_confused_with: - id: "software/debugging/mysql-deadlocks/2026" label: "MySQL Deadlock Debugging (not a migration topic)" # === SOURCES (9 authoritative sources) === # Types: official_docs, technical_blog, rfc_spec, academic_paper, community_resource, industry_report # Reliability: high, moderate_high, moderate, moderate_low, low, authoritative sources: - id: src1 title: "MySQL to Postgres — pgloader 3.6.9 documentation" author: pgloader url: https://pgloader.readthedocs.io/en/latest/ref/mysql.html type: official_docs published: 2024-06-01 reliability: authoritative - id: src2 title: "Converting from other Databases to PostgreSQL" author: PostgreSQL Wiki url: https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL type: official_docs published: 2024-01-15 reliability: authoritative - id: src3 title: "Migrating from MySQL to PostgreSQL Using pgloader: A Practical Guide" author: Robert Bernier (Percona) url: https://www.percona.com/blog/migrating-from-mysql-to-postgresql-using-pgloader/ type: technical_blog published: 2025-03-18 reliability: high - id: src4 title: "Using a PostgreSQL database as a target for AWS Database Migration Service" author: AWS url: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html type: official_docs published: 2025-01-01 reliability: authoritative - id: src5 title: "MySQL to PostgreSQL Migration: The Ultimate Guide (2026)" author: Skyvia url: https://blog.skyvia.com/mysql-to-postgresql/ type: technical_blog published: 2025-06-15 reliability: high - id: src6 title: "How to make a proper migration from MySQL to PostgreSQL" author: PostgreSQL Wiki url: https://wiki.postgresql.org/wiki/How_to_make_a_proper_migration_from_MySQL_to_PostgreSQL type: official_docs published: 2024-01-15 reliability: authoritative - id: src7 title: "From MySQL to Postgres: A Successful Database Migration using AWS DMS" author: Ippon Technologies url: https://blog.ippon.tech/from-mysql-to-postgres-a-successful-database-migration-using-aws-dms type: technical_blog published: 2024-09-10 reliability: high - id: src8 title: "AWS DMS Serverless — Automatically Provisions and Scales Capacity" author: AWS url: https://aws.amazon.com/blogs/aws/new-aws-dms-serverless-automatically-provisions-and-scales-capacity-for-migration-and-data-replication/ type: official_docs published: 2025-06-01 reliability: authoritative - id: src9 title: "PostgreSQL 18 Released" author: PostgreSQL Global Development Group url: https://www.postgresql.org/about/news/postgresql-18-released-3142/ type: official_docs published: 2025-09-25 reliability: authoritative --- # How to Migrate from MySQL to PostgreSQL ## TL;DR - **Bottom line**: Use pgloader for a single-command migration that handles schema conversion, data type mapping, and data transfer — or AWS DMS (including DMS Serverless) for managed cloud migrations with CDC (change data capture) for near-zero downtime. - **Key tool/command**: `pgloader mysql://user:pass@mysql-host/dbname postgresql://user:pass@pg-host/dbname` - **Watch out for**: MySQL's `TINYINT(1)` to PostgreSQL `BOOLEAN` conversion, `AUTO_INCREMENT` to `SERIAL`/`IDENTITY` mapping, zero-date values (`'0000-00-00'`), and case-sensitivity differences in string comparisons. - **Works with**: MySQL 5.7+/8.x, PostgreSQL 14–18, pgloader 3.6.x, AWS DMS 3.6.1 (including Serverless), Azure DMS. PostgreSQL 18 (released 2025-09-25) is now the preferred target — adds async I/O with up to 3× read performance, UUIDv7, virtual generated columns, and statistics retention across major upgrades. [src9] ## Constraints - pgloader does NOT migrate stored procedures, triggers, event schedulers, or views with logic — these must be rewritten manually in PL/pgSQL. [src1, src2] - MySQL zero-date values ('0000-00-00' and '0000-00-00 00:00:00') must be cleaned or cast to NULL before/during migration — PostgreSQL rejects them outright. Use pgloader's `zero-dates-to-null` casting function. [src1] - Never run pgloader with `include drop` against a production PostgreSQL database that already contains data you want to keep — it drops and recreates all target tables. [src1] - AWS DMS does not migrate secondary indexes, foreign keys, or constraints during the initial full load phase — apply these after full load completes. [src4] - MySQL `ENUM` columns are migrated to `VARCHAR` by pgloader — create PostgreSQL native ENUM types manually and alter columns after migration if needed. [src1, src6] - Always reset sequences after bulk data load (use `reset sequences` in pgloader or manual `setval()`) — failing to do so causes duplicate key errors on the next INSERT without explicit ID. [src1] ## Quick Reference | MySQL Syntax | PostgreSQL Equivalent | Example | |---|---|---| | `AUTO_INCREMENT` | `SERIAL` / `GENERATED ALWAYS AS IDENTITY` | `id SERIAL PRIMARY KEY` or `id INT GENERATED ALWAYS AS IDENTITY` | | `TINYINT(1)` | `BOOLEAN` | `is_active BOOLEAN DEFAULT true` | | `DATETIME` | `TIMESTAMP` / `TIMESTAMPTZ` | `created_at TIMESTAMPTZ DEFAULT NOW()` | | `INT UNSIGNED` | `BIGINT` (no unsigned in PG) | `counter BIGINT CHECK (counter >= 0)` | | `DOUBLE` | `DOUBLE PRECISION` | `price DOUBLE PRECISION` | | `BLOB` / `MEDIUMBLOB` / `LONGBLOB` | `BYTEA` | `file_data BYTEA` | | `TEXT` / `MEDIUMTEXT` / `LONGTEXT` | `TEXT` (no size limit in PG) | `content TEXT` | | `ENUM('a','b','c')` | Custom TYPE or CHECK | `CREATE TYPE status AS ENUM ('a','b','c')` | | `SET('a','b','c')` | `TEXT[]` (array) or junction table | `tags TEXT[] DEFAULT '{}'` | | `LIMIT 5, 10` | `LIMIT 10 OFFSET 5` | `SELECT * FROM t LIMIT 10 OFFSET 5` | | `IFNULL(a, b)` | `COALESCE(a, b)` | `SELECT COALESCE(name, 'Unknown')` | | `IF(cond, a, b)` | `CASE WHEN cond THEN a ELSE b END` | `SELECT CASE WHEN x>0 THEN 'pos' ELSE 'neg' END` | | `NOW()` | `NOW()` (identical) | `SELECT NOW()` | | `DATE_ADD(d, INTERVAL 1 DAY)` | `d + INTERVAL '1 day'` | `SELECT created_at + INTERVAL '7 days'` | | `GROUP_CONCAT(col)` | `STRING_AGG(col, ',')` | `SELECT STRING_AGG(name, ', ') FROM t` | | `SHOW TABLES` | `\dt` or `SELECT * FROM information_schema.tables` | `SELECT table_name FROM information_schema.tables WHERE table_schema='public'` | | backtick quoting `` `column` `` | double-quote quoting `"column"` | `SELECT "Column" FROM "Table"` | | `REGEXP '^pattern'` | `~ '^pattern'` | `SELECT * FROM t WHERE col ~ '^A'` | ## Decision Tree ``` START ├── Database size < 10 GB and acceptable downtime window? │ ├── YES → Use pgloader direct migration (single command) │ └── NO ↓ ├── Need near-zero downtime (CDC/replication)? │ ├── YES → Use AWS DMS with CDC (or DMS Serverless for auto-scaling) │ └── NO ↓ ├── Complex stored procedures, triggers, or views? │ ├── YES → Use AWS SCT (with AI-assisted conversion) for schema + pgloader for data │ └── NO ↓ ├── Running on AWS infrastructure? │ ├── YES → Use AWS DMS Serverless + Schema Conversion Tool │ └── NO ↓ ├── Running on Azure infrastructure? │ ├── YES → Use Azure Database Migration Service │ └── NO ↓ ├── Want a no-code/low-code approach? │ ├── YES → Use Skyvia or similar SaaS migration tool │ └── NO ↓ └── DEFAULT → pgloader with a .load configuration file for custom type mappings ``` ## Step-by-Step Guide ### 1. Audit the MySQL source database Inventory your database schema, data volume, stored procedures, triggers, and views. This determines your migration approach and identifies potential issues early. [src3, src5] ```sql -- Count tables, views, procedures, and triggers SELECT 'Tables' AS object_type, COUNT(*) AS count FROM information_schema.tables WHERE table_schema = 'mydb' AND table_type = 'BASE TABLE' UNION ALL SELECT 'Views', COUNT(*) FROM information_schema.views WHERE table_schema = 'mydb' UNION ALL SELECT 'Procedures', COUNT(*) FROM information_schema.routines WHERE routine_schema = 'mydb' UNION ALL SELECT 'Triggers', COUNT(*) FROM information_schema.triggers WHERE trigger_schema = 'mydb'; -- Check total data size SELECT table_schema AS db, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables WHERE table_schema = 'mydb' GROUP BY table_schema; -- List columns using MySQL-specific types that need attention SELECT table_name, column_name, column_type FROM information_schema.columns WHERE table_schema = 'mydb' AND (column_type LIKE '%unsigned%' OR column_type LIKE 'enum%' OR column_type LIKE 'set%' OR column_type = 'tinyint(1)'); ``` **Verify**: You should have a clear inventory — number of tables, total data size, and a list of columns requiring special type handling. ### 2. Install pgloader and prepare the target PostgreSQL database Install pgloader and create an empty target database in PostgreSQL. [src1] ```bash # Ubuntu/Debian sudo apt-get install pgloader # macOS brew install pgloader # Docker (recommended for consistent versioning) docker pull dimitri/pgloader:latest # Create the target PostgreSQL database psql -U postgres -c "CREATE DATABASE mydb_pg ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8';" ``` **Verify**: `pgloader --version` returns `3.6.x` and `psql -U postgres -d mydb_pg -c "SELECT 1"` connects successfully. ### 3. Run a schema-only dry run first Start with a dry run to surface type and constraint mismatches before moving data. Iterate on CAST rules until the dry run completes without rejects. [src1, src3] ```bash # Dry run — shows what pgloader would do without executing pgloader --dry-run mysql://mysql_user:password@mysql-host:3306/mydb \ postgresql://pg_user:password@pg-host:5432/mydb_pg # Or simple one-line test migration pgloader mysql://mysql_user:password@mysql-host:3306/mydb \ postgresql://pg_user:password@pg-host:5432/mydb_pg # Or with Docker docker run --rm -it dimitri/pgloader:latest \ pgloader mysql://mysql_user:password@mysql-host:3306/mydb \ postgresql://pg_user:password@pg-host:5432/mydb_pg ``` **Verify**: pgloader prints a summary table showing rows loaded per table. Check for errors in the output: `grep -i 'error\|warning' pgloader.log`. ### 4. Create a pgloader configuration file for production For production migrations, use a `.load` file with explicit CAST rules to control type mapping precisely. [src1] ``` -- migration.load LOAD DATABASE FROM mysql://mysql_user:password@mysql-host:3306/mydb INTO postgresql://pg_user:password@pg-host:5432/mydb_pg WITH include drop, create tables, create indexes, reset sequences, workers = 8, concurrency = 4, multiple readers per thread, rows per range = 50000 SET PostgreSQL PARAMETERS maintenance_work_mem to '512MB', work_mem to '64MB' CAST type tinyint to boolean using tinyint-to-boolean, type int when unsigned to bigint drop typemod, type bigint with extra auto_increment to bigserial, type int with extra auto_increment to serial, type datetime to timestamptz drop default using zero-dates-to-null, type date drop default using zero-dates-to-null BEFORE LOAD DO $$ CREATE SCHEMA IF NOT EXISTS mydb; $$; ``` **Verify**: `pgloader migration.load` completes with zero errors. Compare row counts: `SELECT schemaname, tablename, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;` ### 5. Validate data integrity after migration Compare row counts, check key data types, and verify constraints were created correctly. [src5, src6] ```sql -- PostgreSQL: Check row counts per table SELECT schemaname, relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC; -- Verify sequences are in sync (critical for AUTO_INCREMENT columns) SELECT sequencename, last_value FROM pg_sequences WHERE schemaname = 'public'; -- Check that indexes were created SELECT indexname, tablename, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename; -- Verify foreign keys SELECT conname, conrelid::regclass, confrelid::regclass FROM pg_constraint WHERE contype = 'f'; ``` **Verify**: Row counts match between MySQL (`SELECT COUNT(*) FROM table_name`) and PostgreSQL. Sequence `last_value` >= max ID in each table. ### 6. Migrate stored procedures and triggers manually pgloader does not migrate stored procedures, triggers, or views. These must be rewritten in PL/pgSQL. AWS DMS Schema Conversion Tool now offers AI-assisted conversion for complex stored procedures. [src2, src4, src8] ```sql -- MySQL stored procedure DELIMITER // CREATE PROCEDURE get_active_users() BEGIN SELECT * FROM users WHERE is_active = 1; END // DELIMITER ; -- PostgreSQL equivalent function CREATE OR REPLACE FUNCTION get_active_users() RETURNS SETOF users AS $$ BEGIN RETURN QUERY SELECT * FROM users WHERE is_active = true; END; $$ LANGUAGE plpgsql; -- MySQL trigger CREATE TRIGGER before_user_update BEFORE UPDATE ON users FOR EACH ROW SET NEW.updated_at = NOW(); -- PostgreSQL equivalent (requires function + trigger) CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER before_user_update BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_timestamp(); ``` **Verify**: `SELECT proname FROM pg_proc WHERE pronamespace = 'public'::regnamespace;` lists your functions. Test each procedure manually. ### 7. Update application connection strings and SQL queries Switch your application to use PostgreSQL-compatible SQL. Key syntax changes are in the Quick Reference table above. [src5, src6] ```python # BEFORE: MySQL connection (Python) import mysql.connector conn = mysql.connector.connect(host='mysql-host', database='mydb', user='user', password='pass') # AFTER: PostgreSQL connection (Python) import psycopg2 # or psycopg[binary] for psycopg 3 conn = psycopg2.connect(host='pg-host', dbname='mydb_pg', user='user', password='pass') # Key SQL changes in application code: # MySQL: SELECT * FROM users LIMIT 5, 10 # PG: SELECT * FROM users LIMIT 10 OFFSET 5 # MySQL: INSERT INTO t SET col1='a', col2='b' # PG: INSERT INTO t (col1, col2) VALUES ('a', 'b') # MySQL: SELECT IFNULL(name, 'N/A') FROM users # PG: SELECT COALESCE(name, 'N/A') FROM users ``` **Verify**: Run your application test suite. Check for SQL errors in logs: `grep -i 'error\|syntax' app.log`. ## Code Examples ### pgloader: Production migration with custom CAST rules > Full script: [pgloader-production-migration-with-custom-cast-rul.txt](scripts/pgloader-production-migration-with-custom-cast-rul.txt) (34 lines) ``` -- Input: MySQL database with AUTO_INCREMENT, TINYINT(1) booleans, ENUMs, unsigned ints -- Output: Fully migrated PostgreSQL database with correct types LOAD DATABASE FROM mysql://root:secret@mysql.example.com:3306/production INTO postgresql://pgadmin:secret@pg.example.com:5432/production_pg # ... (see full script) ``` ### Python: Automated migration validation script > Full script: [python-automated-migration-validation-script.py](scripts/python-automated-migration-validation-script.py) (46 lines) ```python # Input: MySQL and PostgreSQL connection details # Output: Comparison report of row counts and schema differences import mysql.connector import psycopg2 def validate_migration(mysql_config: dict, pg_config: dict) -> dict: # ... (see full script) ``` ### Bash: End-to-end migration script with validation > Full script: [bash-end-to-end-migration-script-with-validation.sh](scripts/bash-end-to-end-migration-script-with-validation.sh) (31 lines) ```bash #!/usr/bin/env bash # Input: MySQL source and PostgreSQL target connection details # Output: Migrated and validated PostgreSQL database set -euo pipefail MYSQL_URI="mysql://root:password@mysql-host:3306/mydb" # ... (see full script) ``` ## Anti-Patterns ### Wrong: Migrating without handling zero-dates ```sql -- BAD — MySQL allows '0000-00-00' dates, PostgreSQL rejects them -- This INSERT works in MySQL but crashes in PostgreSQL INSERT INTO events (event_date) VALUES ('0000-00-00'); -- ERROR: date/time field value out of range: "0000-00-00" ``` ### Correct: Convert zero-dates to NULL before or during migration ```sql -- GOOD — Clean up in MySQL before migration UPDATE events SET event_date = NULL WHERE event_date = '0000-00-00'; -- Or use pgloader CAST rule to handle it automatically -- CAST type datetime to timestamptz using zero-dates-to-null -- CAST type date drop default using zero-dates-to-null ``` ### Wrong: Assuming case-insensitive string comparison in PostgreSQL ```sql -- BAD — This works in MySQL (case-insensitive by default) but fails in PostgreSQL SELECT * FROM users WHERE username = 'JohnDoe'; -- PostgreSQL returns no rows if stored as 'johndoe' ``` ### Correct: Use ILIKE or citext extension for case-insensitive queries ```sql -- GOOD — Explicit case-insensitive comparison in PostgreSQL SELECT * FROM users WHERE username ILIKE 'JohnDoe'; -- Or use the citext extension for the column type CREATE EXTENSION IF NOT EXISTS citext; ALTER TABLE users ALTER COLUMN username TYPE citext; -- Now = comparisons are case-insensitive for this column ``` ### Wrong: Using MySQL-specific SQL syntax after migration ```sql -- BAD — MySQL-only syntax that breaks in PostgreSQL SELECT * FROM orders LIMIT 10, 20; -- MySQL offset,limit INSERT INTO users SET name='Alice', age=30; -- MySQL SET syntax SELECT IFNULL(email, 'none') FROM users; -- MySQL function SELECT * FROM users WHERE name REGEXP '^A'; -- MySQL regex ``` ### Correct: Use PostgreSQL-standard SQL equivalents ```sql -- GOOD — PostgreSQL-compatible syntax SELECT * FROM orders LIMIT 20 OFFSET 10; -- Standard LIMIT/OFFSET INSERT INTO users (name, age) VALUES ('Alice', 30); -- Standard INSERT SELECT COALESCE(email, 'none') FROM users; -- Standard COALESCE SELECT * FROM users WHERE name ~ '^A'; -- PostgreSQL regex (~) ``` ### Wrong: Not resetting sequences after data migration ```sql -- BAD — Sequences not updated after bulk data load -- pgloader may load data but sequences still at 1 INSERT INTO users (name) VALUES ('New User'); -- ERROR: duplicate key value violates unique constraint "users_pkey" -- (because sequence returns 1, which already exists) ``` ### Correct: Reset sequences to match max ID values > Full script: [correct-reset-sequences-to-match-max-id-values.sql](scripts/correct-reset-sequences-to-match-max-id-values.sql) (25 lines) ```sql -- GOOD — Reset all sequences after migration -- pgloader does this with `reset sequences` option -- Manual reset for a specific table: SELECT setval( pg_get_serial_sequence('users', 'id'), # ... (see full script) ``` ### Wrong: Running pgloader with include drop on a populated production database ```sql -- BAD — 'include drop' drops existing tables in the target database -- If the target already has data you want to keep, it's gone LOAD DATABASE FROM mysql://user:pass@src/mydb INTO postgresql://user:pass@dst/production_pg WITH include drop -- THIS WILL DROP ALL MATCHING TABLES ``` ### Correct: Use create tables without include drop, or migrate to a fresh database ```sql -- GOOD — Use a fresh target database or omit 'include drop' LOAD DATABASE FROM mysql://user:pass@src/mydb INTO postgresql://user:pass@dst/mydb_pg_staging WITH create tables, create indexes, reset sequences -- Migrate to a staging database, validate, then swap ``` ## Common Pitfalls - **TINYINT(1) not converting to BOOLEAN**: pgloader converts `TINYINT(1)` to `BOOLEAN` by default, but `TINYINT(2)`, `TINYINT(3)`, etc. become `SMALLINT`. If your MySQL app uses `TINYINT(4)` for booleans, add explicit CAST rule: `CAST column table.col to boolean using tinyint-to-boolean`. [src1] - **Unsigned integers have no PostgreSQL equivalent**: MySQL's `INT UNSIGNED` (0 to 4,294,967,295) does not exist in PostgreSQL. Use `BIGINT` and add a `CHECK (col >= 0)` constraint if non-negative values are required. [src5, src6] - **Character encoding mismatches**: MySQL databases may use `latin1` or `utf8` (which is actually `utf8mb3`, 3-byte). PostgreSQL uses proper UTF-8 (`utf8mb4` equivalent). Convert MySQL to `utf8mb4` before migration, or use pgloader's encoding handling. Fix: `ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;` [src3] - **Zero-date values ('0000-00-00')**: MySQL allows invalid dates like `'0000-00-00'` and `'0000-00-00 00:00:00'`. PostgreSQL rejects these outright. Fix: Use pgloader's `zero-dates-to-null` casting function or clean data before migration. [src1] - **GROUP BY strictness**: PostgreSQL requires all non-aggregated columns in the SELECT list to appear in GROUP BY. MySQL's `ONLY_FULL_GROUP_BY` mode (default since 5.7.5) enforces this, but older apps may rely on lenient mode. Fix: Add missing columns to GROUP BY or use aggregate functions. [src6] - **Backtick vs double-quote identifier quoting**: MySQL uses backticks for identifier quoting, PostgreSQL uses double quotes. Update all SQL queries in application code. Fix: Use pgloader's `downcase identifiers` option and avoid quoted identifiers where possible. [src2] - **Stored procedures require full rewrite**: pgloader and AWS DMS do not migrate stored procedures, triggers, or views with logic. MySQL procedures use different syntax (`DELIMITER`, `BEGIN...END`, `SET` variables) than PostgreSQL's PL/pgSQL. Fix: Manually rewrite each procedure, or use AWS DMS Schema Conversion Tool with AI-assisted conversion for complex objects. [src2, src4, src8] - **Sequence values out of sync after INSERT with explicit IDs**: When data is loaded with explicit ID values, PostgreSQL sequences do not auto-advance. The next `INSERT` without an ID causes a duplicate key error. Fix: Always use `reset sequences` in pgloader, or manually run `setval()` after migration. [src1] - **MySQL SET type has no direct equivalent**: MySQL's `SET` data type (multiple values from a defined list) has no PostgreSQL equivalent. Fix: Use PostgreSQL arrays (`TEXT[]`) or a junction/pivot table. [src6] ## Diagnostic Commands ```bash # Check pgloader version pgloader --version # Dry run — show what pgloader would do without executing pgloader --dry-run migration.load # Verify PostgreSQL connection psql -U postgres -h pg-host -d mydb_pg -c "SELECT version();" # Compare table counts between MySQL and PostgreSQL mysql -u root -p -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='mydb' ORDER BY table_rows DESC;" psql -U postgres -d mydb_pg -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;" # Check for sequences out of sync (potential duplicate key errors) psql -U postgres -d mydb_pg -c "SELECT sequencename, last_value FROM pg_sequences WHERE schemaname='public';" # Verify all constraints migrated psql -U postgres -d mydb_pg -c "SELECT conname, contype, conrelid::regclass FROM pg_constraint WHERE connamespace='public'::regnamespace;" # Check encoding psql -U postgres -d mydb_pg -c "SHOW server_encoding;" # Check PostgreSQL version psql -U postgres -d mydb_pg -c "SELECT version();" # Run ANALYZE after migration for query planner accuracy psql -U postgres -d mydb_pg -c "ANALYZE VERBOSE;" ``` ## Version History & Compatibility | Version | Status | Key Features | Migration Notes | |---|---|---|---| | PostgreSQL 18 (2025-09-25) | Current | Async I/O (up to 3× read perf), skip-scan B-tree, UUIDv7, virtual generated columns, temporal constraints, OAuth 2.0, statistics retention on pg_upgrade | Preferred target — pgloader 3.6.x supports it via standard CAST rules | | PostgreSQL 17 (2024) | Stable | JSON_TABLE, incremental backup, identity columns improvements | Fully supported by pgloader 3.6.x | | PostgreSQL 16 (2023) | Stable | Logical replication improvements, pg_stat_io | Fully supported by pgloader 3.6.x | | PostgreSQL 15 (2022) | Stable | MERGE command, JSON logging, public schema changes | Good target — MERGE simplifies app logic vs MySQL | | PostgreSQL 14 (2021) | Maintenance | Multirange types, LZ4 compression | Supported, but consider upgrading target | | PostgreSQL 13 (2020) | EOL Nov 2025 | Incremental sort, deduplication | Avoid as migration target — end of life | | MySQL 8.4 LTS (2024) | Current LTS | Long-term support, deprecates mysql_native_password | Latest MySQL source — fully supported | | MySQL 8.0 (2018) | Maintenance | Window functions, CTEs, JSON improvements | Most common source version | | pgloader 3.6.9 | Current | MySQL 8.x support, improved CAST | Recommended version — works against PG 18 | | AWS DMS 3.6.1 (2025-05-15) | Current | CDC, multi-AZ, DMS Serverless, AI-assisted SCT, IAM auth, PG read-replica CDC source, SQL Server Binary(16) → PostgreSQL UUID | Use for managed cloud migrations | | Azure DMS | Current | Online and offline migration modes | Use for Azure-hosted targets | ## When to Use / When Not to Use | Use When | Don't Use When | Use Instead | |---|---|---| | Need advanced features: JSONB, arrays, CTEs, window functions | Simple CRUD app working fine on MySQL | Stay on MySQL | | Scaling beyond MySQL replication limits | Database < 1 GB with no complex queries | `mysqldump` + manual conversion | | Moving to AWS Aurora PostgreSQL or Supabase | Need MySQL-specific features (e.g., spatial with MySQL GIS) | MySQL 8.x with InnoDB Cluster | | Application already uses standard SQL | Team has deep MySQL expertise, no PG experience | Invest in MySQL tuning first | | Cost optimization (PostgreSQL is fully open source) | Tight timeline with zero tolerance for downtime | Run both in parallel with CDC first | | Need PostGIS for geospatial data | Only need basic geospatial queries | MySQL 8.x with spatial indexes | ## Important Caveats - pgloader does NOT migrate stored procedures, triggers, event schedulers, or views with complex logic. These must be rewritten manually in PL/pgSQL. AWS DMS Schema Conversion Tool (SCT) now offers AI-assisted conversion for complex procedures, but results still need manual review. [src4, src8] - MySQL's `utf8` charset is actually 3-byte (`utf8mb3`). PostgreSQL's UTF-8 is 4-byte. Ensure MySQL source uses `utf8mb4` before migration to avoid data loss with emoji and CJK characters. - PostgreSQL is case-sensitive for string comparisons by default. If your MySQL application relies on case-insensitive `=` comparisons, use `ILIKE`, the `citext` extension, or add `COLLATE "und-x-icu"` rules. - `AUTO_INCREMENT` gaps in MySQL are preserved as-is. PostgreSQL sequences continue from the max existing value after `reset sequences`, not from the MySQL auto_increment counter value. - AWS DMS does not migrate secondary indexes, foreign keys, or constraints during the initial full load phase. These must be applied after the full load completes. AWS DMS Fleet Advisor will be discontinued on 2026-05-20 — plan accordingly. - MySQL `ENUM` columns are migrated to `VARCHAR` by pgloader. If you need PostgreSQL native ENUMs, create the types manually and alter columns after migration. - AWS DMS Serverless auto-scales capacity using Data Change Units (DCUs) and bills on consumption — consider this for variable-load migrations to avoid over-provisioning. - **PostgreSQL 18 (released 2025-09-25) is the preferred target as of 2026.** Key migration-relevant additions: async I/O delivers up to 3× faster reads (set `io_method = worker | io_uring` after migration), `uuidv7()` produces timestamp-ordered UUIDs that index better than random UUIDv4 (consider using it for new primary keys instead of MySQL `AUTO_INCREMENT`/`SERIAL`), virtual generated columns compute at query time (use to mirror MySQL computed columns without storage cost), and statistics now survive `pg_upgrade` — no more post-upgrade query-plan regressions. MD5 password authentication is deprecated; use SCRAM. [src9] - **AWS DMS homogeneous migration is NOT applicable to MySQL→PostgreSQL** — homogeneous mode only supports same-engine migrations (MySQL→MySQL, PostgreSQL→PostgreSQL, etc.). For MySQL→PostgreSQL stick with heterogeneous DMS tasks plus the AWS Schema Conversion Tool. [src8] - AWS DMS Fleet Advisor is scheduled for discontinuation on 2026-05-20. Plan migration-assessment work accordingly and export any Fleet Advisor data before that date. [src4] ## Related Units - [SQL Server to PostgreSQL Migration](/software/migrations/sqlserver-to-postgresql/2026) - [Oracle to PostgreSQL Migration](/software/migrations/oracle-to-postgresql/2026) - [MongoDB to PostgreSQL Migration](/software/migrations/mongodb-to-postgresql/2026) - [PostgreSQL Slow Query Debugging](/software/debugging/postgresql-slow-queries/2026) - [PostgreSQL Index Bloat Diagnostic Guide](/software/debugging/postgresql-index-bloat/2026) - [PostgreSQL Connection Pool Exhaustion](/software/debugging/postgresql-connection-pool/2026)