MySQL to Azure Database for PostgreSQL Migration¶

When and how to switch engines: MySQL/MariaDB to Azure Database for PostgreSQL Flexible Server. Decision criteria, schema conversion, SQL syntax differences, stored procedure conversion, and tooling.

Migrating from MySQL to PostgreSQL is a real engine change, not a version upgrade. SQL syntax, stored procedure language, data types, and tooling all change. This guide is for organizations that have decided the benefits of PostgreSQL (superior JSON support, PostGIS, Citus scale-out, community governance, extension ecosystem) justify the conversion effort. If minimizing migration risk is the priority, stay on MySQL and use Azure MySQL Flexible Server.

1. When to choose PostgreSQL over MySQL¶

1.1 Strong indicators for PostgreSQL¶

1.2 Stay on MySQL when¶

2. Schema conversion tools¶

2.1 pgloader¶

pgloader is the primary tool for MySQL-to-PostgreSQL migration. It handles schema conversion and data loading in a single pass.

# Install pgloader
sudo apt-get install pgloader  # Debian/Ubuntu
# or
brew install pgloader          # macOS

# Basic migration command
pgloader mysql://user:password@source-host/source_db \
         postgresql://user:password@target-host/target_db

# With configuration file for fine-grained control
pgloader migration.load

pgloader configuration file (migration.load):

LOAD DATABASE
    FROM mysql://root:password@source-mysql:3306/myapp
    INTO postgresql://admin:password@target-pg.postgres.database.azure.com:5432/myapp

WITH include drop, create tables, create indexes,
     reset sequences, downcase identifiers,
     uniquify index names

SET maintenance_work_mem to '512MB',
    work_mem to '128MB'

CAST type int with extra auto_increment
          to serial
     type bigint with extra auto_increment
          to bigserial
     type tinyint to smallint
     type mediumint to integer
     type float to real
     type double to double precision
     type tinytext to text
     type mediumtext to text
     type longtext to text
     type tinyblob to bytea
     type mediumblob to bytea
     type longblob to bytea
     type blob to bytea
     type binary to bytea
     type varbinary to bytea
     type datetime to timestamp
     type year to smallint
     type bit to boolean using pgloader.transforms::integer-to-boolean
     type enum to varchar

BEFORE LOAD DO
     $$ CREATE SCHEMA IF NOT EXISTS myapp; $$

AFTER LOAD DO
     $$ ALTER DATABASE myapp SET search_path TO myapp, public; $$
;

2.2 MySQL Workbench migration wizard¶

MySQL Workbench includes a migration wizard that can generate PostgreSQL DDL from MySQL schemas. It does not migrate data but is useful for reviewing schema differences.

2.3 AWS Schema Conversion Tool (SCT)¶

Though an AWS tool, SCT can convert MySQL schemas to PostgreSQL DDL and identify conversion issues. Use it as a supplementary assessment tool.

2.4 ora2pg (for MariaDB)¶

ora2pg supports MariaDB as a source and can generate PostgreSQL DDL with data type mapping.

# Install ora2pg
sudo apt-get install ora2pg

# Run assessment
ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg_mysql.conf

# Generate schema
ora2pg -t TABLE -c ora2pg_mysql.conf -o tables.sql
ora2pg -t VIEW -c ora2pg_mysql.conf -o views.sql
ora2pg -t PROCEDURE -c ora2pg_mysql.conf -o procedures.sql
ora2pg -t FUNCTION -c ora2pg_mysql.conf -o functions.sql
ora2pg -t TRIGGER -c ora2pg_mysql.conf -o triggers.sql

3. SQL syntax differences¶

3.1 Core syntax mapping¶

3.2 String differences¶

3.3 Date and time differences¶

4. Stored procedure conversion¶

4.1 MySQL to PL/pgSQL conversion¶

MySQL stored procedures and functions must be rewritten in PL/pgSQL, PostgreSQL's procedural language.

MySQL stored procedure:

DELIMITER //
CREATE PROCEDURE get_customer_orders(IN p_customer_id INT, OUT p_total DECIMAL(10,2))
BEGIN
    DECLARE v_count INT DEFAULT 0;

    SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO v_count, p_total
    FROM orders
    WHERE customer_id = p_customer_id
      AND status = 'completed';

    IF v_count = 0 THEN
        SET p_total = 0.00;
    END IF;
END //
DELIMITER ;

PostgreSQL equivalent:

CREATE OR REPLACE FUNCTION get_customer_orders(
    p_customer_id INTEGER,
    OUT p_total NUMERIC(10,2)
)
RETURNS NUMERIC AS $$
DECLARE
    v_count INTEGER := 0;
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO v_count, p_total
    FROM orders
    WHERE customer_id = p_customer_id
      AND status = 'completed';

    IF v_count = 0 THEN
        p_total := 0.00;
    END IF;
END;
$$ LANGUAGE plpgsql;

4.2 Key conversion patterns¶

4.3 Trigger conversion¶

MySQL trigger:

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END;

PostgreSQL equivalent:

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Key difference: PostgreSQL triggers call a function (EXECUTE FUNCTION), while MySQL embeds the logic directly in the trigger body.

5. Data migration for engine switch¶

5.1 Using pgloader (recommended)¶

pgloader handles both schema conversion and data loading in a single operation:

# Full migration with pgloader
pgloader mysql://root:pass@mysql-host:3306/mydb \
         postgresql://admin:pass@pg-server.postgres.database.azure.com:5432/mydb

# pgloader automatically:
# - Maps MySQL data types to PostgreSQL types
# - Converts AUTO_INCREMENT to sequences
# - Handles character set conversion
# - Creates indexes and constraints
# - Loads data in parallel

5.2 Schema-first, then data¶

For more control, migrate schema and data separately:

# 1. Extract MySQL schema
mysqldump --no-data --routines --triggers mydb > schema.sql

# 2. Convert schema to PostgreSQL (manual or tool-assisted)
# Review and fix: data types, AUTO_INCREMENT, backticks, etc.

# 3. Create schema in PostgreSQL
psql -h pg-server.postgres.database.azure.com -U admin -d mydb -f schema_pg.sql

# 4. Migrate data with pgloader (data only)
pgloader --with "data only" \
  mysql://root:pass@mysql-host/mydb \
  postgresql://admin:pass@pg-server.postgres.database.azure.com/mydb

# 5. Reset sequences after data load
# pgloader handles this automatically, but verify
SELECT setval(pg_get_serial_sequence('tablename', 'id'),
              (SELECT MAX(id) FROM tablename));

5.3 Using Azure Data Factory for data movement¶

ADF can read from MySQL and write to PostgreSQL using the MySQL connector as source and PostgreSQL connector as sink. This is useful for incremental migrations or when pgloader cannot be used.

{
    "name": "MySQL_to_PostgreSQL_Pipeline",
    "properties": {
        "activities": [
            {
                "name": "CopyFromMySQL",
                "type": "Copy",
                "inputs": [
                    {
                        "referenceName": "MySQLSource",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "PostgreSQLSink",
                        "type": "DatasetReference"
                    }
                ],
                "typeProperties": {
                    "source": {
                        "type": "MySqlSource",
                        "query": "SELECT * FROM customers"
                    },
                    "sink": {
                        "type": "PostgreSqlV2Sink",
                        "writeBatchSize": 10000
                    }
                }
            }
        ]
    }
}

6. Application code changes¶

6.1 Connection string changes¶

6.2 ORM configuration changes¶

6.3 Common code changes¶

Most ORMs abstract SQL differences, but raw SQL queries need updates:

• Replace backtick quoting with double-quote quoting
• Replace LIMIT offset, count with LIMIT count OFFSET offset
• Replace IFNULL() with COALESCE()
• Replace GROUP_CONCAT() with STRING_AGG()
• Replace RAND() with RANDOM()
• Replace NOW() with NOW() (same, but verify timezone handling)
• Replace LAST_INSERT_ID() with RETURNING id clause or currval()

7. Testing strategy¶

7.1 Validation checklist¶

• All tables created with correct data types
• Row counts match between MySQL and PostgreSQL for every table
• Stored procedures / functions return identical results for test inputs
• Triggers fire correctly on INSERT, UPDATE, DELETE
• Indexes exist and are used by query planner (EXPLAIN ANALYZE)
• Character encoding preserved (especially multi-byte characters)
• NULL handling consistent between source and target
• Date/time values preserved (timezone awareness)
• Decimal precision preserved
• Application test suite passes against PostgreSQL backend
• Performance within acceptable range (compare EXPLAIN plans)
• Connection pooling configured (PgBouncer on Azure PostgreSQL)

7.2 Performance comparison¶

After migration, compare query performance:

-- PostgreSQL: Enable timing
\timing

-- Run representative queries and compare execution times
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01'
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 20;

Next: Schema Migration | Data Migration | Feature Mapping

Maintainers: csa-inabox core team Last updated: 2026-04-30