PostgreSQL to MariaDB migration guide

IntoSQL reads a plain PostgreSQL dump and writes one MariaDB script with the same tables, columns, keys, indexes, foreign keys, defaults, counters, sequences, comments and rows. It reads the dump itself, without a database server, and lists in the report every value MariaDB can't hold as it is and every object it doesn't carry. The script loads on MariaDB 10.11 and later.

Convert in the browser: PostgreSQL to MariaDB opens the converter set up for this pair.

Dump for the best result#

Any plain dump from pg_dump or pg_dumpall from PostgreSQL 10 on works, as .sql or .sql.gz, with COPY rows (the default) or --inserts:

pg_dump --no-owner --no-privileges shop | gzip > shop.sql.gz

• --no-owner --no-privileges leave out roles and grants, which the converter skips anyway.
• A custom or directory archive (-Fc, -Fd) is binary: pg_restore -f shop.sql shop.backup turns it into plain SQL without a server. The converter says so when it gets an archive (dump_archive).
• pg_dumpall writes every database in one file; each database's tables are named after it, so nothing collides.

A dump made with --data-only still converts: the types are read from the values, and the report says the schema was missing (no_schema). Partitioned tables arrive as one table with every partition's rows (partitions_dropped).

Convert#

In the browser, open PostgreSQL to MariaDB or the converter on the home page, drop the dump and choose MariaDB. It reads the file from your disk in pieces, twice, and writes the script as it goes, so the dump is never uploaded and its size isn't limited by memory. The desktop app does the same with the file on disk.

With the command line:

intosql inspect shop.sql.gz
intosql convert shop.sql.gz --to mariadb --output shop.mariadb.sql --report shop.report.json

inspect lists the tables, their rows and what won't be converted, without writing anything. convert writes the script and, with --report, the report as JSON.

Through the API, a dump converts like any other file: open a conversion with its name ending in .sql or .sql.gz and "output": {"dialect": "mariadb"}.

Load into MariaDB#

mariadb -e "CREATE DATABASE shop"
mariadb --default-character-set=utf8mb4 shop < shop.mariadb.sql

The script sets the character set and a binary collation, so text compares exactly as in PostgreSQL, then creates each table with its keys and copies the rows with foreign key and unique checks off. Schemas other than public become a prefix on their tables' names (app.orders is app_orders). The serial or identity column that leads each primary key becomes AUTO_INCREMENT and continues after the highest id, and sequences not owned by a column become MariaDB sequences at the value they had reached.

Or load it straight in#

intosql load and the desktop app load the dump into MariaDB without a script, with the same tables, keys and indexes the script creates:

intosql load shop.sql.gz --into mariadb://ana@localhost/shop

It stops before writing anything when one of the dump's tables already exists. MariaDB can't roll back a table, so a failed load drops the tables it created. Loading into a database server is part of IntoSQL Pro, with a 14-day trial. See Loading.

What isn't converted#

Views, materialized views, functions, procedures, triggers, rules, row security policies, extensions and types are written in PostgreSQL's own language. They are listed in the report by kind and name, never dropped silently, so you can recreate them in MariaDB.

Data types#

PostgreSQL to MariaDB data types lists each type that changes in the script: what it becomes, why, and the report line.

Every other rule#

Generated from the engine, where each rule is decided.