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

# MariaDB to PostgreSQL migration guide

IntoSQL reads a MariaDB dump and writes one PostgreSQL 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 PostgreSQL can't hold as it is and every object it doesn't carry. The script loads on PostgreSQL 13 and later.

**Convert in the browser:** [MariaDB to PostgreSQL](/mariadb-to-postgresql) opens the converter set up for this pair.

## Dump for the best result

Any dump from mariadb-dump, mysqldump, phpMyAdmin or Adminer from MariaDB 10.3 on works, as `.sql` or `.sql.gz`. These options keep more of your data:

```shell
mariadb-dump --default-character-set=utf8mb4 --hex-blob \
  --routines --triggers --events --single-transaction \
  shop | gzip > shop.sql.gz
```

- `--default-character-set=utf8mb4` keeps emoji and other 4-byte characters. A dump made with `utf8mb3` has already lost them, and the report says so (`dump_lost_four_byte_chars`).
- `--hex-blob` writes binary columns and `VECTOR` values as hexadecimal, which survives copying between systems.
- `--routines --triggers --events` add those objects to the dump, so the report can list them by name.

A dump made with `--no-create-info` still converts: the types are read from the values, and the report says the schema was missing (`no_schema`). MariaDB's `JSON` columns, which the dump writes as `longtext` with a `json_valid` check, are read as JSON again.

## Convert

In the browser, open [MariaDB to PostgreSQL](/mariadb-to-postgresql) or the converter on the home page, drop the dump and choose PostgreSQL. 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 postgresql --output shop.postgresql.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": "postgresql"}`.

## Load into PostgreSQL

```shell
createdb shop
psql -v ON_ERROR_STOP=1 -d shop -f shop.postgresql.sql
```

The script creates the tables without keys, copies the rows with `COPY`, then adds keys, indexes, foreign keys and identities, so it loads fast and stops at the first error. Foreign keys are added as `NOT VALID` and then validated: a row that points to a missing row is named by its constraint instead of failing the load half way. Each identity continues after the highest id copied, or after the table's `AUTO_INCREMENT` when that is higher.

### Or load it straight in

`intosql load` and the [desktop app](/docs/desktop) load the dump into PostgreSQL without a script, with the same tables, keys and indexes the script creates:

```shell
intosql load shop.sql.gz --into postgres://ana@localhost/shop
```

It stops before writing anything when one of the dump's tables already exists. It loads in one transaction, so a failed load leaves nothing. 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, triggers, stored procedures, functions and events are written in MariaDB's own language. They are listed in the report by kind and name, never dropped silently, so you can recreate them in PostgreSQL.


## Data types

[MariaDB to PostgreSQL data types](/docs/types/mariadb-to-postgresql) 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 MariaDB | In PostgreSQL | 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` |
| text decoded twice (ñ) | copied as it is | The dump holds exactly these characters, so they are copied; the report points them out. | `mojibake_suspected` |
| ON UPDATE CURRENT_TIMESTAMP | a BEFORE UPDATE trigger | The trigger sets the column to the current time when a row changes and the update didn't set it, as MySQL does. | `on_update_trigger` |
| generated columns | STORED | PostgreSQL 13 to 17 only store generated columns; the formula is the same. | `virtual_as_stored` |
| generated columns with functions PostgreSQL lacks | a plain column | A formula that can't be written for PostgreSQL leaves a plain column and a report line. | `generated_not_translated` |
| INVISIBLE columns | regular columns | PostgreSQL has no invisible columns. | `invisible_column` |
| CHECK constraints | translated, inline in CREATE TABLE | Inline checks are tested as each row loads instead of in a scan per check afterwards. | None |
| CHECK constraints with functions PostgreSQL lacks | not created | A check that can't be written for PostgreSQL is left out and reported. | `check_skipped` |
| CHECK ... NOT ENFORCED | not created | MySQL keeps these checks without applying them, so the rows may break them. | `check_not_enforced` |
| defaults with functions PostgreSQL lacks | no default | A default that can't be written for PostgreSQL is left out and reported. | `default_not_translated` |
| keys and indexes on a prefix, c(20) | an index on left(c, 20) | PostgreSQL indexes expressions, so the same prefix is indexed. | `prefix_unique` |
| FULLTEXT indexes | GIN on to_tsvector('simple', ...) | Full-text search works with @@ instead of MATCH ... AGAINST. | `fulltext_as_gin` |
| indexes on values over 2,000 bytes | hash | A btree entry can't hold values that long; a hash index still finds equal values. | `index_on_wide_values` |
| SPATIAL indexes | not created without PostGIS | Spatial indexes need PostGIS. | `spatial_index_skipped` |
| functional indexes PostgreSQL can't write | not created | An index on an expression that can't be written for PostgreSQL is left out and reported. | `index_skipped` |
| foreign keys | after the data, NOT VALID, then validated with a warning on orphans | Dumps load with FOREIGN_KEY_CHECKS=0 and MyISAM never checks, so rows without a parent are common. The key is created first and validated last; orphans leave it NOT VALID with a warning instead of stopping the load. | None |
| foreign keys to columns without a unique key | not created | PostgreSQL needs a unique key on the referenced columns; InnoDB doesn't. | `foreign_key_skipped` |
| table and column names | lowercase when that stays unique | Lowercase names can be written without quotes in PostgreSQL. | `identifier_renamed` |
| names over 63 bytes | shortened with a hash | PostgreSQL names hold 63 bytes; the hash keeps shortened names apart. | `name_shortened` |
| PARTITION BY | one table | The rows are the same; partition the table again in PostgreSQL if it needs it. | `partitions_dropped` |
| WITH SYSTEM VERSIONING (MariaDB) | a table with the current rows | A dump holds the current rows only. | `system_versioning_dropped` |
| MyISAM, MEMORY and other engines | a regular table | PostgreSQL has one table engine. | `engine_dropped` |
| views, triggers, procedures, functions and events | listed in the report | Their code is written in MySQL's language, so they are listed for you to recreate. | `not_converted_objects` |
| dumps without CREATE TABLE | types read from the values | A dump made with --no-create-info only has rows, so the types come from the values. | `no_schema` |
| _ci collations | exact comparison | PostgreSQL compares text exactly unless told otherwise. | `case_insensitive_collation` |
| SET NAMES utf8 from utf8mb4 tables | converted, with a warning | MySQL 5.x mysqldump writes 3-byte utf8 by default and replaces every emoji with a question mark before any converter sees the file. | `dump_lost_four_byte_chars` |
| dumps without SET NAMES whose text isn't UTF-8 | read as Latin-1 (Windows-1252) | MySQL's latin1 is Windows-1252, the usual encoding of such dumps. | `charset_guessed` |
| sjis, cp932, gbk, big5 dumps | read as UTF-8, with a warning | These encodings are not decoded yet. | `unsupported_charset` |
| UNHEX('..'), CONVERT(b'..', UNSIGNED), CONVERT(".." USING x), ST_GeomFromText('..') in rows | evaluated | Some dump tools write values as these calls; they are read as the values they produce, with geographic WKT turned to longitude first. | None |
| other expressions in rows | NULL | A converter that doesn't run a database can't evaluate them. | `expression_value` |
| DEFAULT in VALUES | the column's default value | A literal default is copied into the row. | `default_keyword_filled` |
| DEFAULT in VALUES without a literal default | NULL | There is no value to copy. | `default_keyword_null` |
| REPLACE and INSERT IGNORE | plain rows | A dump has one row per key, so the rows are the same. | `replace_as_insert` |
| CREATE SEQUENCE (MariaDB) | CREATE SEQUENCE with its current value | PostgreSQL sequences take the same options. | None |