mariadb
postgresql

Migrate MariaDB to PostgreSQL

Certified — live-tested path

The converter maps 20 MariaDB column types to PostgreSQL. 2 of them change kind on the way (YEAR becomes SMALLINT, SET('a','b') becomes TEXT[]); the table below lists every one. DBShifts converts the schema, copies the rows in parallel batches and checks the result with row counts and checksums.

Start MariaDB → PostgreSQL Migration

Free tier · no credit card

MariaDB vs PostgreSQL

mariadb

MariaDB

the community-driven MySQL fork.

postgresql

PostgreSQL

the most advanced open-source relational database.

MariaDB to PostgreSQL data type mapping

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

MariaDBPostgreSQL
INTINTEGER
BIGINTBIGINT
SMALLINTSMALLINT
TINYINT(1)BOOLEAN
DECIMAL(10,2)NUMERIC(10,2)
FLOATREAL
DOUBLEDOUBLE PRECISION
VARCHAR(255)VARCHAR(255)
TEXTTEXT
LONGTEXTTEXT
CHAR(10)CHAR(10)
DATEDATE
DATETIMETIMESTAMP
TIMESTAMPTIMESTAMPTZ
TIMETIME
YEARSMALLINT
BLOBBYTEA
JSONJSONB
ENUM('a','b')native ENUM (CREATE TYPE … AS ENUM)
SET('a','b')TEXT[]

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" SERIAL,
    "customer_id" INTEGER NOT NULL,
    "status" "orders_status_enum" NOT NULL DEFAULT 'new',
    "total" NUMERIC(10,2) NOT NULL,
    "is_gift" BOOLEAN NOT NULL DEFAULT FALSE,
    "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?

Yes. MariaDB to PostgreSQL is a certified pair: it passes the live certification suite on real MariaDB and PostgreSQL servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.

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

Each gets the nearest PostgreSQL type the converter treats as safe: YEAR becomes SMALLINT, SET('a','b') becomes TEXT[]. Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.

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

Yes, until you cut over. DBShifts reads the row-based binary log (binlog_format = ROW). With zero-downtime mode on, the log position is captured before the first row is copied, so writes made during the copy are replayed afterwards.

Which MariaDB data types need a second look when moving to PostgreSQL?

The table above carries a note on 9 of them: TINYINT(1) (BOOLEAN), FLOAT (REAL), DOUBLE (DOUBLE PRECISION), TIMESTAMP (TIMESTAMPTZ) and more. Analysis flags the same columns on your own schema before any data moves.

Go deeper

Ready to move MariaDB to PostgreSQL?

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

Start Free Migration