---
title: "PostgreSQL to MariaDB migration guide"
description: "Convert a PostgreSQL dump into a MariaDB script with the same tables, keys, indexes and data: how to dump, convert, load, and every rule the converter applies."
canonical: https://intosql.com/docs/migrations/postgresql-to-mariadb
last_updated: 2026-10-15
---

# 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](/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`:

```shell
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](/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](/docs/desktop) does the same with the file on disk.

With the [command line](/docs/cli):

```shell
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](/docs/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

```shell
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](/docs/desktop) load the dump into MariaDB without a script, with the same tables, keys and indexes the script creates:

```shell
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](/docs/loading#database-dumps).

## 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](/docs/types/postgresql-to-mariadb) 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.

| In PostgreSQL | In MariaDB | Why | Report line |
| --- | --- | --- | --- |
| NOT NULL columns with values that become NULL | NOT NULL dropped | A column whose values had to become NULL can't keep NOT NULL, or the load would stop. | `not_null_relaxed` |
| citext and nondeterministic collations | exact comparison | Tables use utf8mb4_bin so text compares exactly, as PostgreSQL's does; columns that ignored case in the source lose that. | `case_insensitive_collation` |
| sequences not owned by a column | CREATE SEQUENCE, at the value it hands out next | MariaDB sequences work the same way. | None |
| generated columns | generated columns, STORED as in the source | MariaDB computes them the same way. | None |
| generated columns with functions MariaDB lacks | a plain column | A formula that can't be written for MariaDB leaves a plain column and a report line. | `generated_not_translated` |
| defaults with functions MariaDB lacks, and defaults from a sequence | no default | A default that can't be written for MariaDB is left out and reported. | `default_not_translated` |
| CHECK constraints | translated, inline in CREATE TABLE | MySQL 8.0.16 and MariaDB enforce them; MySQL 5.7 keeps them without checking. | None |
| CHECK constraints with functions MariaDB lacks | not created | A check that can't be written for MariaDB is left out and reported. | `check_skipped` |
| CHECK ... NOT VALID | not created | PostgreSQL never checked the rows that were there before, so they may break it. | `check_not_valid` |
| gin, gist, brin and hash indexes, partial and expression indexes | not created | MariaDB has b-tree, full-text and spatial indexes only, on whole columns, without a WHERE. | `index_skipped` |
| foreign keys | foreign keys, loaded with FOREIGN_KEY_CHECKS=0 | The rows load in any order, and the keys check new rows afterwards. | None |
| foreign keys to tables the dump doesn't create | not created | A key can't name a table that isn't there. | `foreign_key_skipped` |
| partitioned tables | one table with every partition's rows | The rows are the same; partition the table again in MariaDB if it needs it. | `partitions_dropped` |
| schemas | one database, tables named after their schema first | MariaDB's schemas are databases, which a script loaded into one database can't create; tables in public keep their names. | `schemas_merged` |
| views, functions, procedures, triggers, rules, policies, extensions and types | listed in the report | Their code is written in PostgreSQL's language, so they are listed for you to recreate. | `not_converted_objects` |
| dumps without CREATE TABLE (--data-only) | types read from the values | A data-only dump has only rows, so the types come from the values. | `no_schema` |
| expressions in INSERT values | NULL | A converter that doesn't run a database can't evaluate them. | `expression_value` |
| text decoded twice (ñ) | copied as it is | The dump holds exactly these characters, so they are copied; the report points them out. | `mojibake_suspected` |