Migrate PostgreSQL to MariaDB
Certified — live-tested pathThe converter maps 18 PostgreSQL column types to MariaDB. One of them changes kind on the way (uuid becomes CHAR(36)); 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
PostgreSQL vs MariaDB
PostgreSQL
the most advanced open-source relational database.
MariaDB
the community-driven MySQL fork.
PostgreSQL to MariaDB data type mapping
Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.
| PostgreSQL | MariaDB |
|---|---|
| integer | int |
| bigint | bigint |
| smallint | smallint |
| boolean | boolean |
| numeric(10,2) | decimal(10,2) |
| real | float |
| double precision | double |
| varchar(255) | varchar(255) |
| text | LONGTEXT |
| char(10) | char(10) |
| date | date |
| timestamp | datetime(6) |
| timestamptz | timestamp(6) |
| time | time(6) |
| bytea | LONGBLOB |
| json | json |
| jsonb | json |
| uuid | CHAR(36) |
Example: PostgreSQL to MariaDB schema conversion
PostgreSQL
CREATE TABLE orders ( id bigserial PRIMARY KEY, customer_id integer NOT NULL, total numeric(10,2) NOT NULL, is_gift boolean NOT NULL DEFAULT false, notes text, metadata jsonb, placed_at timestamptz NOT NULL );
MariaDB
CREATE TABLE IF NOT EXISTS `orders` (
`id` bigint NOT NULL AUTO_INCREMENT,
`customer_id` int NOT NULL,
`total` decimal(10,2) NOT NULL,
`is_gift` boolean NOT NULL DEFAULT '0',
`notes` LONGTEXT,
`metadata` json,
`placed_at` timestamp(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Unique text columns can collide on the way in
MariaDB's default utf8mb4 collations compare text case-insensitively. PostgreSQL does not, so two rows that differ only by case, Alice and alice in a unique username column, are two valid rows in PostgreSQL and a duplicate key in MariaDB. The load stops at the second one.
Check each unique text column first with SELECT lower(col), count(*) FROM t GROUP BY 1 HAVING count(*) > 1. Where the distinction is real, declare the column with a binary collation such as utf8mb4_bin.
jsonb becomes text-backed JSON
jsonb becomes MariaDB json, which is LONGTEXT with a validity check rather than a binary format. The documents arrive intact, but PostgreSQL's operators do not: ->, ->> and @> have no MariaDB form, so metadata->>'plan' becomes JSON_VALUE(metadata, '$.plan').
There is no GIN index to carry over. A MariaDB virtual column over JSON_VALUE(), with an index on it, covers the lookups that matter; every other path is now a scan.
uuid lands as CHAR(36)
uuid becomes CHAR(36), which works on every MariaDB version. MariaDB 10.7 and later also has a native UUID type that stores sixteen bytes, so on a recent server it is worth switching to after the load.
The switch is an ALTER TABLE per column, plus the same change on every column that references it, so plan it with the foreign keys in view.
PostgreSQL → MariaDB conversion notes
Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.
- Arrays (integer[], text[]) are preserved natively on PostgreSQL targets and serialized to JSON elsewhere.
- JSONB, UUID, INET and other rich types fall back to portable representations with every downgrade recorded in the fidelity report.
- SERIAL/identity sequences are recreated and reseeded on the target.
- Literal 'NULL' defaults reported by MariaDB's information schema are cleaned instead of becoming DEFAULT 'NULL' strings.
- AUTO_INCREMENT reseed after bulk load, same as MySQL.
Frequently asked questions
Is PostgreSQL to MariaDB migration production-ready in DBShifts?
Yes. PostgreSQL to MariaDB is a certified pair: it passes the live certification suite on real PostgreSQL and MariaDB servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.
What happens to PostgreSQL types that MariaDB doesn't have?
Each gets the nearest MariaDB type the converter treats as safe: uuid becomes CHAR(36). Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.
Can I keep PostgreSQL and MariaDB in sync after the initial migration?
Yes, until you cut over. DBShifts uses logical decoding through a replication slot, which needs wal_level = logical and the wal2json plugin on the server. With zero-downtime mode on, the slot is created before the first row is copied, so it holds every change made during the copy.
Which PostgreSQL data types need a second look when moving to MariaDB?
The table above carries a note on 3 of them: timestamptz (timestamp(6)), bytea (LONGBLOB), jsonb (json). 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 PostgreSQL to MariaDB?
Set up in under two minutes. Validation and rollback included.
Start Free Migration