postgresql
mariadb

Migrate PostgreSQL to MariaDB

Certified — live-tested path

The 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.

Start PostgreSQL → MariaDB Migration

Free tier · no credit card

PostgreSQL vs MariaDB

postgresql

PostgreSQL

the most advanced open-source relational database.

mariadb

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.

PostgreSQLMariaDB
integerint
bigintbigint
smallintsmallint
booleanboolean
numeric(10,2)decimal(10,2)
realfloat
double precisiondouble
varchar(255)varchar(255)
textLONGTEXT
char(10)char(10)
datedate
timestampdatetime(6)
timestamptztimestamp(6)
timetime(6)
byteaLONGBLOB
jsonjson
jsonbjson
uuidCHAR(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

Ready to move PostgreSQL to MariaDB?

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

Start Free Migration