MariaDB to PostgreSQL Migration: A Schema-Level Guide

By /

Planning a MariaDB to PostgreSQL migration? The two databases speak similar SQL on the surface, but the schema-level differences — sequences, storage engines, type mapping, and identifier casing — are where migrations quietly break. This guide walks through the divergences that matter and how to generate a safe, reviewable migration script instead of hand-translating DDL.

Why teams move off MariaDB

MariaDB forked from MySQL in 2009 and has since diverged in its own direction: native SEQUENCE objects, a different optimizer, and a storage-engine model (InnoDB, Aria, ColumnStore) that doesn't map cleanly onto anything else. Teams typically move to PostgreSQL for its richer type system, better support for JSON and array columns, native window functions, and a single storage engine with fewer surprises. The catch is that "MySQL-compatible" doesn't mean "PostgreSQL-compatible," and a straight dump-and-restore usually fails on the first schema that uses engine-specific features.

Sequences: the one place MariaDB is easier, not harder

Unlike MySQL, MariaDB (10.3+) supports real SEQUENCE objects alongside the classic AUTO_INCREMENT column attribute. That actually simplifies this particular migration compared to a MySQL source: PostgreSQL is sequence-native too, so a MariaDB sequence maps far more directly onto a PostgreSQL SEQUENCE or an identity column than an AUTO_INCREMENT column does. If your schema still uses AUTO_INCREMENT rather than explicit sequences, the target needs a backing SERIAL or GENERATED ALWAYS AS IDENTITY column with the sequence created and owned before the table gets its default. Get this step wrong and inserts either fail on missing defaults or silently restart numbering at 1. We cover the general pattern in more depth in our piece on sequences, identity columns, and auto-increment across databases.

Type mapping and storage-engine quirks

A handful of MariaDB types need explicit handling rather than a literal rename:

  • TINYINT(1) is MariaDB's conventional boolean — map it to PostgreSQL boolean, not smallint, or every application-layer check breaks.
  • Unsigned integer types (INT UNSIGNED, BIGINT UNSIGNED) have no PostgreSQL equivalent; widen to the next signed type or add a CHECK (col >= 0) constraint.
  • ENUM columns should become a PostgreSQL ENUM type (or a check-constrained text column) — a straight text copy loses the validation MariaDB enforced at write time.
  • MariaDB's JSON type is really a LONGTEXT alias with validation; PostgreSQL's native jsonb is a strict superset and the better target.
  • Storage engine annotations (ENGINE=InnoDB, ROW_FORMAT) have no PostgreSQL analog and should simply be dropped, not translated.

Case sensitivity and identifier casing

MariaDB table names are case-sensitive on Linux and case-insensitive on Windows and macOS by default — a source of bugs even within MariaDB-only shops. PostgreSQL identifiers are case-insensitive unless quoted, and folds unquoted names to lowercase. If your MariaDB schema mixes case in table or column names, decide up front whether the target should quote every identifier to preserve exact casing, or normalize everything to lowercase (the more idiomatic PostgreSQL convention). Pick one and apply it consistently across the whole migration, not table by table.

Views, triggers, and stored routines need a human

Tables, columns, indexes, and foreign keys translate mechanically. Views, triggers, and stored procedures/functions do not — MariaDB's procedural SQL dialect and PostgreSQL's PL/pgSQL are different languages, not different syntaxes for the same one. Any migration tool that claims to auto-translate a stored procedure is guessing. The safer approach is to flag these objects for manual rewrite rather than emit SQL that looks plausible but fails at runtime, or worse, runs and does the wrong thing silently.

Generating the migration instead of writing it by hand

FoxSchema connects to your MariaDB source and PostgreSQL target, compares the two schemas, and generates the migration SQL for the differences — applying sequence creation order, type mapping, and casing rules automatically. Its readiness panel separates what translates cleanly (tables, columns, keys, indexes) from what needs manual review (views, enums, stored routines), so nothing procedural slips through as if it were mechanical. Every migration runs in dry-run mode first, with a pre-migration snapshot, so you can review the generated SQL before anything touches the target.

Try it against your own MariaDB and PostgreSQL instances — self-host FoxSchema in Docker in a few minutes, or read the documentation for the full CLI and web workflow.

Scroll to Top