MySQL 8.0 → PostgreSQL

📚 Overview & Scope

This SOP covers a full copy (schema + data) from a MySQL 8.0 source into a PostgreSQL 14+ target using Endrias Bridge's cross-engine row-copy mode. Cross-engine migration requires more type-conversion awareness than a same-engine copy — key differences include ENUM handling, AUTO_INCREMENT sequences, zero-date behavior, unsigned integers, and case sensitivity of identifiers.

✓ Prerequisites — Source (MySQL 8.0)

✓ Prerequisites — Target (PostgreSQL 14+)

Endrias Bridge connects directly via postgresql+psycopg2:// — it does not create the database, only tables inside it. Run the following once on the PostgreSQL server (as a superuser, e.g. psql -U postgres) before Step 1:

-- 1. Create the target database
CREATE DATABASE endriasbridge_target;

-- 2. Create the migration user
CREATE USER MigrationUser WITH PASSWORD 'your-password';

-- 3. Grant ownership so CREATE/DROP/INSERT work freely
--    (avoids "permission denied for schema public" on PostgreSQL 15+)
ALTER DATABASE endriasbridge_target OWNER TO MigrationUser;
\connect endriasbridge_target
ALTER SCHEMA public OWNER TO MigrationUser;

Open Endrias Bridge, navigate to Migration Projects → New Project, and fill in both connection blocks. Select MySQL as source type and PostgreSQL as target type.

Switch to the Assessment tab and click Run Assessment. For cross-engine migrations the assessment panel shows a richer compatibility report — all type conversions that will be applied are listed per column.

Switch to the Migration tab. Click Load Tables from Source, confirm all tables are checked, then configure migration options.

Click Start Migration. The Migration Log updates in real time. Type conversion lines are printed first, then schema creation, then row-by-row copy progress per table.

1. Click Verify Data Integrity — compares source vs. target row counts for every migrated table and logs any mismatches.
2. Review zero-date warnings: check the Migration Log for WARNING: zero-date converted to NULL lines, then query the target to confirm NULLs are expected.
3. Click Export Report (enabled once migration finishes) to save an HTML/text summary of tables, row counts, type conversions, and all warnings.

After a successful full copy the following tasks are required before switching application traffic to the PostgreSQL target.

1. Verify ENUM columns — values are stored as strings in VARCHAR(n) columns. No PostgreSQL ENUM type is created.

-- Spot-check a former ENUM column
SELECT DISTINCT type FROM categories ORDER BY type;

2. Reset sequences — PostgreSQL SERIAL sequences start at 1 after schema creation; they are not seeded from the migrated MAX(id) value. Reset each auto-increment column:

-- Run for each table with an AUTO_INCREMENT / SERIAL primary key
SELECT setval(
    pg_get_serial_sequence('users', 'id'),
    (SELECT MAX(id) FROM users)
);

SELECT setval(
    pg_get_serial_sequence('orders', 'id'),
    (SELECT MAX(id) FROM orders)
);
-- Repeat for every SERIAL column in the schema

3. Zero-date check

-- Confirm expected NULLs where MySQL stored 0000-00-00
SELECT COUNT(*) AS zero_date_rows
FROM orders
WHERE created_at IS NULL;

4. Case sensitivity — PostgreSQL identifiers are case-sensitive. All MySQL identifiers were lowercased during migration. Update any application queries that reference mixed-case table or column names.

5. Full-text indexes — MySQL FULLTEXT indexes are not migrated. Recreate them as GIN indexes with tsvector on PostgreSQL:

-- Example: recreate full-text search index on products.description
ALTER TABLE products ADD COLUMN tsv tsvector
    GENERATED ALWAYS AS (to_tsvector('english', description)) STORED;

CREATE INDEX idx_products_tsv ON products USING GIN (tsv);

⚠ Known Issues

📊 Type Conversions (MySQL → PostgreSQL)