MariaDB to PostgreSQL
MariaDB to PostgreSQL migration
Last updated:
Datalobe treats MariaDB as its own source, not as MySQL: it assesses the source, converts stored procedures, functions, triggers and views, compiles each on PostgreSQL before deploy, moves data with change data capture (CDC) until cut-over, and validates every table with 17 checks.
Source
MariaDB
- 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.
| MariaDB | 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 |
| Zero dates such as '0000-00-00' | Not valid; NULL or a chosen date, by decision |
| ON UPDATE CURRENT_TIMESTAMP | A trigger |
| 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 MariaDB 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
Is migrating MariaDB the same as migrating MySQL?
Mostly. MariaDB shares much of its history with MySQL but the two have drifted apart, and some MariaDB features need their own handling, so Datalobe treats MariaDB as its own source.
Do MariaDB 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 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 MariaDB stays in use, and data copies in checkpointed batches that resume after an interruption.
Sources (26)
- MariaDB Documentation: MariaDB versus MySQL - Compatibility, checked
- MariaDB Knowledge Base: InnoDB Versions, checked
- MariaDB Documentation: Incompatibilities and Feature Differences Between MariaDB 10.11 and MySQL 8.0, checked
- MariaDB Documentation: MariaDB versus MySQL - Features, checked
- PostgreSQL 16 Documentation: 43.1 Overview (PL/pgSQL), checked
- PostgreSQL 16 Documentation: Chapter 42 Procedural Languages, checked
- MariaDB Documentation: AUTO_INCREMENT, checked
- PostgreSQL 16 Documentation: CREATE TABLE, checked
- MariaDB Documentation: BOOLEAN, checked
- PostgreSQL 16 Documentation: 8.6 Boolean Type, checked
- MariaDB Documentation: INT, checked
- PostgreSQL 16 Documentation: 8.1 Numeric Types, checked
- MariaDB Documentation: ENUM, checked
- PostgreSQL 16 Documentation: 8.7 Enumerated Types, checked
- MariaDB Documentation: DATE, checked
- MariaDB Documentation: SQL_MODE, checked
- PostgreSQL 16 Documentation: 8.5 Date/Time Types, checked
- MariaDB Documentation: TIMESTAMP, checked
- PostgreSQL 16 Documentation: 43.10 Trigger Functions, checked
- MariaDB Documentation: Character Set and Collation Overview, checked
- MariaDB Documentation: Setting Character Sets and Collations, 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 MariaDB database
Send a read-only connection or a schema export and get an Assessment Report.