Migrate MariaDB to PostgreSQL

Beta — live-tested path

Migrating from MariaDB (the community-driven MySQL fork) to PostgreSQL (the most advanced open-source relational database) means every table, column type, key, index and row has to survive two engines' different opinions about data. DBShifts converts the schema with deterministic rules, transfers data in parallel batches, and validates the result with per-table row counts and type-aware checksums.

Start MariaDB → PostgreSQL Migration

Free tier · no credit card

MariaDB vs PostgreSQL

MariaDB

the community-driven MySQL fork.

PostgreSQL

the most advanced open-source relational database.

How the migration works

Connect

Point DBShifts at your source and target. Credentials stay encrypted; SSH tunnels supported for private databases.

Analyze

Automatic schema introspection produces a migration plan: what converts automatically, what migrates with warnings, what needs human review.

Migrate

Schema is created on the target, data transfers in parallel batches with constraints deferred, indexes rebuilt after load.

Validate

Per-table row counts, type-aware checksums on both sides, FK integrity — plus a fidelity report listing anything lossy.

MariaDB to PostgreSQL data type mapping

Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.

MariaDB	PostgreSQL	Note
INT	INTEGER
BIGINT	BIGINT
SMALLINT	SMALLINT
TINYINT(1)	BOOLEAN	MySQL and MariaDB store booleans here. Plain TINYINT is numeric — the width is the only thing distinguishing them.
DECIMAL(10,2)	NUMERIC(10,2)
FLOAT	REAL	Binary floating point, same rounding caveat as DOUBLE.
DOUBLE	DOUBLE PRECISION	Binary floating point. Money stored in a DOUBLE will round; use DECIMAL end to end.
VARCHAR(255)	VARCHAR(255)
TEXT	TEXT
LONGTEXT	TEXT
CHAR(10)	CHAR(10)
DATE	DATE
DATETIME	TIMESTAMP
TIMESTAMP	TIMESTAMPTZ	Timezone behaviour differs per engine — a naive target shifts the instant rather than failing.
TIME	TIME	MySQL TIME is a duration spanning -838:59:59 to 838:59:59, not a clock time. Targets with a 24-hour TIME cannot hold the full range.
YEAR	SMALLINT	MySQL-only type. Widened on every other engine.
BLOB	BYTEA
JSON	JSONB	JSON is stored and indexed very differently per engine; queries against it will need rewriting.
ENUM('a','b')	TEXT	Value list is kept as a CHECK constraint where the target supports one.
SET('a','b')	TEXT[]	No equivalent outside MySQL and MariaDB. The value list is preserved but the constraint is not.

Example: MariaDB to PostgreSQL schema conversion

MariaDB

CREATE TABLE orders (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  customer_id   INT NOT NULL,
  status        ENUM('new','paid','shipped') NOT NULL DEFAULT 'new',
  total         DECIMAL(10,2) NOT NULL,
  is_gift       TINYINT(1) NOT NULL DEFAULT 0,
  notes         TEXT,
  metadata      JSON,
  placed_at     DATETIME NOT NULL
);

PostgreSQL

CREATE TABLE IF NOT EXISTS "orders" (
    "id" INTEGER NOT NULL,
    "customer_id" INTEGER NOT NULL,
    "status" "orders_status_enum",
    "total" NUMERIC(10,2) NOT NULL,
    "is_gift" BOOLEAN,
    "notes" TEXT,
    "metadata" JSONB,
    "placed_at" TIMESTAMP NOT NULL,
    PRIMARY KEY ("id")
);

The most common relational migration, and its most common mistake

MariaDB to PostgreSQL is a well-worn route, which means the pitfalls are known rather than absent. The one that catches most teams is not a type or a query: it is case. MariaDB's default collation compares case-insensitively, PostgreSQL compares case-sensitively, and a login lookup written as WHERE email = ? stops matching addresses stored with different capitalisation the moment it lands.

The fix is a decision, not a setting. Either normalise the data and the writes to lowercase, or use citext, or index on lower(email) and change the queries to match. Deciding after the cutover means deciding while people cannot sign in.

TINYINT(1), and the boolean that is not always a boolean

MariaDB has no boolean type, so it stores booleans as TINYINT(1). The width is the only signal, and it is not a reliable one: plenty of schemas use TINYINT(1) for a small integer, and plenty use TINYINT(4) for a flag. Converting every TINYINT to boolean corrupts counters and ratings; converting none of them leaves the application comparing to 0 and 1 forever.

Reviewing these columns individually is worth the time, because the conversion is one-way in practice. A rating column that becomes boolean has lost the values 2 through 5 and no checksum will report it, since both sides agree on what was written.

Unsigned integers, ENUM, and zero dates

PostgreSQL has no unsigned integer types. An INT UNSIGNED column has to widen to BIGINT to keep values above the signed maximum, and a BIGINT UNSIGNED near its ceiling needs NUMERIC. Leaving the width unchanged is the version of this that fails only for your largest customers.

MariaDB ENUM has three plausible destinations in PostgreSQL: a native enum type, a text column with a CHECK constraint, or a lookup table. The native type keeps enforcement and is awkward to alter later; a CHECK constraint is easy to change; a lookup table is the most flexible and the most work. The right answer depends on how often the value list changes.

Zero dates are legal in MariaDB and impossible in PostgreSQL. 0000-00-00 has to become NULL or a real date before the load, and that is a decision about meaning: a zero date usually stood for unknown, which is what NULL means.

MariaDB → PostgreSQL conversion notes

Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.

MariaDB's JSON columns (LONGTEXT + json_valid CHECK) are detected and mapped to real JSON types on targets that have them.
Engine-specific CHECK constraints like json_valid() are filtered on targets that don't support them — data still migrates.
Display-width unsigned types (bigint(20) unsigned) are recognized and widened correctly.
Bulk loads use sequence reseeding (setval to MAX) so SERIAL columns keep working after migration.
ENUMs from MySQL become native CREATE TYPE … AS ENUM — validation semantics preserved.
Foreign keys and indexes are applied after data load for dramatically faster transfers.

Frequently asked questions

Is MariaDB to PostgreSQL migration production-ready in DBShifts?

MariaDB to PostgreSQL is a live-verified beta pair: it passed the same certification suite as every other pair — a real migration with edge-case data validated by row counts and checksums — and ships with a post-migration fidelity report you can audit before cutover.

How does DBShifts verify the MariaDB to PostgreSQL migration was correct?

Three layers: per-table row counts on both sides, an order-independent type-aware checksum of row data (canonicalized so engine representation differences don't false-alarm), and FK integrity checks on the target. Rows that fail to insert are quarantined with the exact error — never silently dropped — and can be fixed and retried from the UI.

What happens to MariaDB types that PostgreSQL doesn't have?

They convert by deterministic rules with a recorded decision: a native equivalent where one exists, a portable fallback (TEXT/JSON/DECIMAL) where one doesn't. Every lossy conversion appears in the migration plan before you run and in the fidelity report after. You can override any mapping per column.

Can I keep MariaDB and PostgreSQL in sync after the initial migration?

Yes. DBShifts supports incremental sync (UPSERT-based delta transfers on a sync key) and, for supported sources, real-time change data capture so the target stays current until you cut over.

Do I need to write any SQL or scripts for MariaDB to PostgreSQL?

No. Connect both databases, review the generated migration plan (what's automatic, what migrates with warnings, what needs manual review — typically triggers and stored procedures), and run. Procedural code is transpiled best-effort and queued for human review rather than auto-applied.

What's the hardest part of moving MariaDB to PostgreSQL?

Type-system mismatches that fail silently rather than loudly — MariaDB's edge-case types (3 engine-specific rules apply) converting into PostgreSQL's nearest equivalent. DBShifts's deterministic mapping records every downgrade in the fidelity report, so a value that clips, rounds, or widens is visible before cutover, not after.

Which MariaDB data types change when moving to PostgreSQL?

9 of the mapped types convert to something that cannot represent exactly the same range or constraint. The clearest cases are TINYINT(1) to BOOLEAN, FLOAT to REAL, DOUBLE to DOUBLE PRECISION. The full table above lists every mapping the converter performs, and analysis flags each one on your actual schema before any data moves.

Go deeper

How We Live-Certified All 49 Migration Paths

The certification harness behind this page's claims.

Why Row Counts Aren't Enough

How type-aware checksum validation works.

Related migration paths

MySQLPostgreSQL MariaDBMySQL MariaDBMariaDB MariaDBSQL Server MariaDBOracle MariaDBSQLite

Ready to move MariaDB to PostgreSQL?

Set up in under two minutes. Validation and rollback included.

Start Free Migration