Migrate MariaDB to PostgreSQL
Certified — live-tested pathThe 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.
Free tier · no credit card
MariaDB vs PostgreSQL
MariaDB
the community-driven MySQL fork.
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.
| MariaDB | PostgreSQL |
|---|---|
| INT | INTEGER |
| BIGINT | BIGINT |
| SMALLINT | SMALLINT |
| TINYINT(1) | BOOLEAN |
| DECIMAL(10,2) | NUMERIC(10,2) |
| FLOAT | REAL |
| DOUBLE | DOUBLE PRECISION |
| VARCHAR(255) | VARCHAR(255) |
| TEXT | TEXT |
| LONGTEXT | TEXT |
| CHAR(10) | CHAR(10) |
| DATE | DATE |
| DATETIME | TIMESTAMP |
| TIMESTAMP | TIMESTAMPTZ |
| TIME | TIME |
| YEAR | SMALLINT |
| BLOB | BYTEA |
| JSON | JSONB |
| 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
How We Live-Certified All 49 Migration Paths
The certification harness behind this page's claims.
The Hidden Data-Loss Traps in Cross-Engine Migration
Five silent bugs the certification harness caught, from clipped TINYINTs to rounded NUMERICs.
Reference
Compared with other tools
Ready to move MariaDB to PostgreSQL?
Set up in under two minutes. Validation and rollback included.
Start Free Migration