MySQL to PostgreSQL
MySQL to PostgreSQL migration
Last updated:
Datalobe assesses your MySQL source and flags decisions such as zero dates, TINYINT(1) flags and UNSIGNED integers, converts stored code and compiles it on PostgreSQL before deploy, moves data with change data capture (CDC), and validates every table with 17 checks.
Source
MySQL
- Code
- Stored routines
Target
PostgreSQL
- Code
- PL/pgSQL
Conversion
What converts automatically, and what needs review?
Stored procedures, functions, triggers and views convert to PL/pgSQL, and tables, types and keys follow fixed rules.
AI converts stored code, every AI conversion is checked, and anything that cannot be proven goes to a named person.
Every converted statement is compile-checked on PostgreSQL before deploy: Datalobe replays it inside a transaction that is rolled back.
Assessment
Which differences does the assessment look for?
Where a choice is needed, such as zero dates, a named approver decides before the run.
| MySQL | Common PostgreSQL equivalent |
|---|---|
| AUTO_INCREMENT | Identity column (GENERATED AS IDENTITY) |
| TINYINT(1) used as a flag | BOOLEAN, by decision |
| UNSIGNED integer types | A larger signed type or a check constraint |
| ENUM | An enum type or a check constraint |
| DATETIME | TIMESTAMP |
| Zero dates such as '0000-00-00' | Not valid; NULL or a chosen date, by decision |
| ON UPDATE CURRENT_TIMESTAMP | A trigger |
| Backtick-quoted identifiers | Double-quoted or unquoted identifiers |
| Case-insensitive default collation | Case-sensitive comparison by default |
Validation
What is validated, and how?
17 checks on every table in six groups, and code computes one verdict: Passed, Passed with warnings, Not verified or Failed.
| Group | What it checks on a MySQL to PostgreSQL migration |
|---|---|
| Completeness | Row counts match on every table, and every object is present |
| Accuracy | Checksums by chunk on all rows, and sample rows compared value by value |
| Structure | Columns, types and defaults match the plan, and AUTO_INCREMENT and identity values are reset |
| Integrity | Primary key, foreign key, unique and check constraints hold on PostgreSQL |
| Code objects | Converted routines and views are present and compiled, deterministic read-only functions return the same results on both sides, and triggers are present and enabled |
| Performance readiness (advisory) | Indexes are built and statistics analysed. Advisory, not part of the verdict |
Questions
Frequently asked questions
What happens to MySQL zero dates?
PostgreSQL does not accept dates such as '0000-00-00'. The assessment counts the affected columns and an approver decides the mapping, for example NULL, before the data moves.
Do MySQL stored procedures convert to PostgreSQL?
Yes. AI converts stored code, every AI conversion is checked, and anything that cannot be proven goes to a named person. Each one must compile on PostgreSQL before deploy.
How are TINYINT(1) flags handled?
They can become BOOLEAN. Because some systems store values other than 0 and 1 in these columns, it is a decision, not a silent conversion.
How much downtime does the migration need?
With keep-in-sync (CDC), downtime is limited to the cut-over window. CDC keeps PostgreSQL in sync while MySQL stays in use, and data copies in checkpointed batches that resume after an interruption.
Sources (25)
- PostgreSQL 16 Documentation: 43.1 Overview (PL/pgSQL), checked
- PostgreSQL 16 Documentation: Chapter 42 Procedural Languages, checked
- PostgreSQL 16 Documentation: CREATE TABLE, checked
- MySQL 8.0 Reference Manual: 5.6.9 Using AUTO_INCREMENT, checked
- MySQL 8.0 Reference Manual: 13.1.1 Numeric Data Type Syntax, checked
- MySQL 8.0 Reference Manual: 13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT, checked
- PostgreSQL 16 Documentation: 8.6 Boolean Type, checked
- PostgreSQL 16 Documentation: 8.1 Numeric Types, checked
- PostgreSQL 16 Documentation: 5.4 Constraints, checked
- MySQL 8.0 Reference Manual: 13.3.5 The ENUM Type, checked
- PostgreSQL 16 Documentation: 8.7 Enumerated Types, checked
- MySQL 8.0 Reference Manual: 13.2.2 The DATE, DATETIME, and TIMESTAMP Types, checked
- MySQL 8.0 Reference Manual: 7.1.11 Server SQL Modes, checked
- PostgreSQL 16 Documentation: 8.5 Date/Time Types, checked
- MySQL 8.0 Reference Manual: 13.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME, checked
- PostgreSQL 16 Documentation: 43.10 Trigger Functions, checked
- MySQL 8.0 Reference Manual: 11.2 Schema Object Names, checked
- PostgreSQL 16 Documentation: 4.1 Lexical Structure, checked
- MySQL 8.0 Reference Manual: 12.2 Character Sets and Collations in MySQL, checked
- MySQL 8.0 Reference Manual: B.3.4.1 Case Sensitivity in String Searches, checked
- PostgreSQL 16 Documentation: 24.2 Collation Support, checked
- PostgreSQL 16 Documentation: CREATE COLLATION, checked
- PostgreSQL 16 Documentation: BEGIN, checked
- PostgreSQL 16 Documentation: ROLLBACK, checked
- PostgreSQL wiki: Transactional DDL in PostgreSQL: A Competitive Analysis, checked
Next step
Assess your MySQL database
Send a read-only connection or a schema export and get an Assessment Report.