Convert PostgreSQL to MariaDB
Drop a plain dump from pg_dump or pg_dumpall, as .sql or .sql.gz, and download a script for MariaDB with the same tables, keys, indexes and data. Every value MariaDB can't hold as it is, and every object it doesn't carry, is listed in the report.
18 rows converted, 3 fixes, none rejected
Tables in shop_2024_postgres.sqlPostgreSQL 18.6 dump · written by pg_dump · utf8mb4
customers8 rows2
8 columns id int not null name text not null email varchar(768) not null country char(2) not null is_active tinyint(1) not null tags json not null signed_up date created_at datetime(6) not null orders10 rows1
10 columns id int not null customer_id int not null status enum('pending','paid','shipped','cancelled') not null total decimal(10,2) not null items smallint not null ordered_at datetime(6) not null shipped_at datetime(6) note text details json delivery varchar(100)
Not converted
- View paid_orders
Each database writes these in its own language. Rewrite them for MariaDB.
-- Written by IntoSQL from a PostgreSQL dump. Load it with: mariadb --default-character-set=utf8mb4 <database> < <file>
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO,ALLOW_INVALID_DATES';
CREATE TABLE `customers` (
`id` int NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`email` varchar(768) NOT NULL,
`country` char(2) NOT NULL DEFAULT 'US',
`is_active` tinyint(1) NOT NULL DEFAULT 1,
`tags` json NOT NULL,
`signed_up` date,
`created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
UNIQUE KEY `customers_email_key` (`email`),
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='People who bought at least once';
CREATE TABLE `orders` (
`id` int NOT NULL AUTO_INCREMENT,
`customer_id` int NOT NULL,
`status` enum('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
`total` decimal(10,2) NOT NULL,
`items` smallint NOT NULL DEFAULT 1,
`ordered_at` datetime(6) NOT NULL,
`shipped_at` datetime(6),
`note` text,
`details` json,
`delivery` varchar(100),
PRIMARY KEY (`id`),
KEY `idx_orders_customer` (`customer_id`),
KEY `idx_orders_status_date` (`status`,`ordered_at`),
CONSTRAINT `orders_total_check` CHECK (`total` >= (0)),
CONSTRAINT `orders_customer_id_fkey` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`tags`,`signed_up`,`created_at`) VALUES (1,'Ana Martínez','ana@example.com','MX',1,'["vip"]','2023-01-15','2023-01-15 10:24:00'),(2,'Zoë Müller','zoe@example.de','DE',1,'[]','2023-02-03','2023-02-03 08:11:42'),(3,'José Peña','jose@example.es','ES',0,'["wholesale","late payer"]','2023-03-21','2023-03-21 19:05:17'),(4,'李雷','lilei@example.cn','CN',1,'[]','2023-05-09','2023-05-09 02:40:00'),(5,'Noah O\'Brien','noah@example.ie','IE',1,'[]',NULL,'2023-06-30 14:00:00'),(6,'Amélie Laurent','amelie@example.fr','FR',1,'["vip"]','2024-01-02','2024-01-02 09:30:00'),(7,'Ravi Patel','ravi@example.in','IN',0,'[]','2024-02-29','2024-02-29 23:59:59'),(8,'Sofia Rossi 🌻','sofia@example.it','IT',1,'[]','2024-04-12','2024-04-12 12:12:12');
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`details`,`delivery`) VALUES (1,1,'shipped',129.90,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL,'{"gift": false}','1 day 06:30:00'),(2,2,'paid',54.00,1,'2024-03-02 11:15:00',NULL,'Gift wrap 🎁','{"gift": true, "wrap": "red"}',NULL),(3,1,'cancelled',12.50,1,'2024-03-03 09:45:00',NULL,'Customer changed their mind',NULL,NULL),(4,4,'shipped',980.00,12,'2024-03-05 02:10:00','2024-03-07 08:00:00','请小心轻放','{"fragile": true}','2 days 05:50:00'),(5,5,'pending',45.75,2,'2024-03-08 17:20:00',NULL,'Leave at the door; ring twice',NULL,NULL),(6,6,'shipped',310.40,5,'2024-03-10 13:00:00','2024-03-11 09:00:00','Fragile: "glass"','{"fragile": true}','20:00:00'),(7,8,'paid',76.20,2,'2024-03-11 18:45:00',NULL,NULL,NULL,NULL),(8,2,'shipped',19.99,1,'2024-03-12 08:00:00',NULL,'Shipped date set to infinity by an old import',NULL,NULL),(9,3,'shipped',64.00,2,'2024-03-14 15:30:00','2024-03-15 10:00:00','Two parcels','{"parcels": [1, 2]}','18:30:00'),(10,7,'paid',1499.00,1,'2024-03-15 21:05:00',NULL,'C:\\Users\\ravi\\invoice.pdf',NULL,NULL);
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
Fixes
- "created_at" of customers holds moments with a time zone; MariaDB has no type that keeps one, so they are stored in UTC as datetime(6).
- "tags" of customers is text[], which MariaDB doesn't have, so it is json with the same values.
- "delivery" of orders is interval, which MariaDB doesn't have, so it is varchar(100) with the same values.
Needs a look
- 1 view, trigger, routine or event isn't converted; the report lists each one. Recreate them by hand in MariaDB.
- The default of "tags" in customers can't be written for MariaDB (a value its type can't hold), so the column has no default. Add the default by hand if new rows need it.
- The index idx_orders_details on orders uses an expression that can't be written for MariaDB (neither MySQL 5.7 nor MariaDB index an expression), so it isn't created. Create the index by hand if queries need it.
- 1 value in "shipped_at" of orders is a date MariaDB can't store, such as infinity, so it is NULL. MariaDB stores years 1 to 9999 without infinity; replace those dates in the source if they matter.
Your file is converted on this computer and never uploaded.
Other samples:
What changes from PostgreSQL to MariaDB
Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.
boolean
tinyint(1)
MariaDB's BOOLEAN is TINYINT(1): true is 1 and false is 0.
varchar(n), char(n), text
varchar(n) up to 1024 characters, char(n) up to 255, text beyond
Text keeps every character as utf8mb4. A column in a key is varchar(768) at most, the longest a key holds.
numeric(p,s) past 65 digits or 30 decimals, and numeric values past what decimal(65,30) holds
varchar
MariaDB's decimal holds 65 digits with 30 decimals. Wider numbers are kept as text so every digit stays exact; numeric without a precision is decimal(65,30) when its values fit.
Report line: decimal_as_text
money
decimal(19,2)
MariaDB has no money type. The amount is kept as a number, without the currency symbol the server's locale added.
Report line: type_replaced
interval
varchar(100) with PostgreSQL's text
MariaDB's TIME holds durations up to 838 hours, but an interval has months and days; its text keeps every part.
Report line: type_replaced
arrays (integer[], text[]...)
json arrays
MariaDB has no arrays. A JSON array keeps every item and NULL, and JSON functions read it.
Report line: type_replaced
ranges, geometric types, tsvector, xml, hstore, composite and extension types
longtext with PostgreSQL's text
MariaDB has no equivalent type, so each value keeps the text PostgreSQL wrote.
Report line: type_replaced
json, jsonb
json
The same JSON values; jsonb's key order and spacing are the server's.
timestamp
datetime(p)
A timestamp without time zone is a wall-clock reading, like datetime.
timestamp with time zone
datetime(p) in UTC
MariaDB's TIMESTAMP only reaches 2038, so moments are stored as datetime in UTC, the same instant whatever offset the dump wrote.
Report line: moments_as_utc
time
time(p)
MariaDB's TIME holds every time of day, 24:00:00 included.
infinity, -infinity, BC dates and years past 9999
NULL
MariaDB stores years 1000 to 9999 without infinity. Those values become NULL and the report counts them.
Report line: date_out_of_range
NaN and Infinity in numeric, real and double precision
NULL
MariaDB's numbers have no NaN or Infinity, so those values become NULL and the report counts them.
Report line: invalid_value_null
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.
Report line: not_null_relaxed
enum types
enum(...) with the same labels
MariaDB's ENUM keeps the labels in their order.
uuid, inet, cidr
uuid as uuid, inet and cidr as varchar(43)
MariaDB has a uuid type; addresses keep their text, mask included.
bytea
blob, mediumblob or longblob; varbinary(3072) in a key
Bytes stay bytes.
bit(n)
bit(n) up to 64, text beyond
A bit string up to 64 bits is the number its bits make; a longer one keeps its digits.
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.
Report line: case_insensitive_collation
serial, identity
AUTO_INCREMENT, continuing after the highest id
The column that leads the primary key counts; the counter continues where the sequence was.
a second serial or identity column in a table
plain integer
MariaDB counts one column per table; any other keeps its values without a counter.
Report line: counter_dropped
sequences not owned by a column
CREATE SEQUENCE, at the value it hands out next
MariaDB sequences work the same way.
generated columns
generated columns, STORED as in the source
MariaDB computes them the same way.
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.
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.
Report line: 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.
CHECK constraints with functions MariaDB lacks
not created
A check that can't be written for MariaDB is left out and reported.
Report line: check_skipped
CHECK ... NOT VALID
not created
PostgreSQL never checked the rows that were there before, so they may break it.
Report line: 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.
Report line: 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.
foreign keys to tables the dump doesn't create
not created
A key can't name a table that isn't there.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: no_schema
expressions in INSERT values
NULL
A converter that doesn't run a database can't evaluate them.
Report line: expression_value
types IntoSQL doesn't know
longtext
Text holds any value; change the type after loading.
Report line: unknown_type
text that isn't valid in its encoding
the bad bytes as the replacement character
Text has to be valid utf8mb4.
Report line: invalid_utf8_replaced
text decoded twice (ñ)
copied as it is
The dump holds exactly these characters, so they are copied; the report points them out.
Report line: mojibake_suspected
| In PostgreSQL | In MariaDB | Report line |
|---|---|---|
| boolean | tinyint(1)MariaDB's BOOLEAN is TINYINT(1): true is 1 and false is 0. | |
| varchar(n), char(n), text | varchar(n) up to 1024 characters, char(n) up to 255, text beyondText keeps every character as utf8mb4. A column in a key is varchar(768) at most, the longest a key holds. | |
| numeric(p,s) past 65 digits or 30 decimals, and numeric values past what decimal(65,30) holds | varcharMariaDB's decimal holds 65 digits with 30 decimals. Wider numbers are kept as text so every digit stays exact; numeric without a precision is decimal(65,30) when its values fit. | decimal_as_text |
| money | decimal(19,2)MariaDB has no money type. The amount is kept as a number, without the currency symbol the server's locale added. | type_replaced |
| interval | varchar(100) with PostgreSQL's textMariaDB's TIME holds durations up to 838 hours, but an interval has months and days; its text keeps every part. | type_replaced |
| arrays (integer[], text[]...) | json arraysMariaDB has no arrays. A JSON array keeps every item and NULL, and JSON functions read it. | type_replaced |
| ranges, geometric types, tsvector, xml, hstore, composite and extension types | longtext with PostgreSQL's textMariaDB has no equivalent type, so each value keeps the text PostgreSQL wrote. | type_replaced |
| json, jsonb | jsonThe same JSON values; jsonb's key order and spacing are the server's. | |
| timestamp | datetime(p)A timestamp without time zone is a wall-clock reading, like datetime. | |
| timestamp with time zone | datetime(p) in UTCMariaDB's TIMESTAMP only reaches 2038, so moments are stored as datetime in UTC, the same instant whatever offset the dump wrote. | moments_as_utc |
| time | time(p)MariaDB's TIME holds every time of day, 24:00:00 included. | |
| infinity, -infinity, BC dates and years past 9999 | NULLMariaDB stores years 1000 to 9999 without infinity. Those values become NULL and the report counts them. | date_out_of_range |
| NaN and Infinity in numeric, real and double precision | NULLMariaDB's numbers have no NaN or Infinity, so those values become NULL and the report counts them. | invalid_value_null |
| NOT NULL columns with values that become NULL | NOT NULL droppedA column whose values had to become NULL can't keep NOT NULL, or the load would stop. | not_null_relaxed |
| enum types | enum(...) with the same labelsMariaDB's ENUM keeps the labels in their order. | |
| uuid, inet, cidr | uuid as uuid, inet and cidr as varchar(43)MariaDB has a uuid type; addresses keep their text, mask included. | |
| bytea | blob, mediumblob or longblob; varbinary(3072) in a keyBytes stay bytes. | |
| bit(n) | bit(n) up to 64, text beyondA bit string up to 64 bits is the number its bits make; a longer one keeps its digits. | |
| citext and nondeterministic collations | exact comparisonTables use utf8mb4_bin so text compares exactly, as PostgreSQL's does; columns that ignored case in the source lose that. | case_insensitive_collation |
| serial, identity | AUTO_INCREMENT, continuing after the highest idThe column that leads the primary key counts; the counter continues where the sequence was. | |
| a second serial or identity column in a table | plain integerMariaDB counts one column per table; any other keeps its values without a counter. | counter_dropped |
| sequences not owned by a column | CREATE SEQUENCE, at the value it hands out nextMariaDB sequences work the same way. | |
| generated columns | generated columns, STORED as in the sourceMariaDB computes them the same way. | |
| generated columns with functions MariaDB lacks | a plain columnA 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 defaultA default that can't be written for MariaDB is left out and reported. | default_not_translated |
| CHECK constraints | translated, inline in CREATE TABLEMySQL 8.0.16 and MariaDB enforce them; MySQL 5.7 keeps them without checking. | |
| CHECK constraints with functions MariaDB lacks | not createdA check that can't be written for MariaDB is left out and reported. | check_skipped |
| CHECK ... NOT VALID | not createdPostgreSQL 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 createdMariaDB 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=0The rows load in any order, and the keys check new rows afterwards. | |
| foreign keys to tables the dump doesn't create | not createdA key can't name a table that isn't there. | foreign_key_skipped |
| partitioned tables | one table with every partition's rowsThe rows are the same; partition the table again in MariaDB if it needs it. | partitions_dropped |
| schemas | one database, tables named after their schema firstMariaDB'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 reportTheir 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 valuesA data-only dump has only rows, so the types come from the values. | no_schema |
| expressions in INSERT values | NULLA converter that doesn't run a database can't evaluate them. | expression_value |
| types IntoSQL doesn't know | longtextText holds any value; change the type after loading. | unknown_type |
| text that isn't valid in its encoding | the bad bytes as the replacement characterText has to be valid utf8mb4. | invalid_utf8_replaced |
| text decoded twice (ñ) | copied as it isThe dump holds exactly these characters, so they are copied; the report points them out. | mojibake_suspected |
Load the script into MariaDB
The script creates the tables, copies the rows, then adds keys, indexes and foreign keys, so it loads fast and stops at the first error.
mariadb --default-character-set=utf8mb4 mydb < postgresql.mariadb.sqlForeign keys are created as the dump declares them, with FOREIGN_KEY_CHECKS=0 while the rows load, as mysqldump does.
For a large dump, run the client on the same machine as the server, and raise max_allowed_packet if a single row is very large.
IntoSQL Pro loads the file straight into MariaDB from the desktop app or the command line, without writing a script.
Dump PostgreSQL for the best result
The converter reads what the dump holds. These options keep more of your data in it:
- --format=plain, pg_dump's default, writes SQL. A custom or directory archive (-Fc, -Fd) becomes SQL with pg_restore -f dump.sql, without a database server.
- --no-owner --no-privileges leave out roles and grants, which the converter skips anyway, so the dump is smaller.
- pg_dumpall dumps every database in one file; each one's tables are named after it, so nothing collides.
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 name, never dropped silently, so you can rewrite them for MariaDB.
Questions about PostgreSQL to MariaDB
Not answered here? Write to us from the contact page and a person replies. Contact page
Is my dump uploaded?
No. The converter runs in your browser and reads the dump from your disk in pieces, so it never leaves your computer. The desktop app and the command line convert on your computer too.
How large can the dump be?
The converter never holds the whole dump in memory: it reads the file twice, by ranges, and writes the script as it goes. Multi-gigabyte dumps convert in the browser, in the desktop app and with the command line.
Does it read pg_dump -Fc archives?
Not directly: a custom archive is a binary format. pg_restore -f dump.sql dump.backup turns it into the plain SQL the converter reads, without connecting to a server. The converter says so when it gets an archive.
Which PostgreSQL versions can the dump come from?
Dumps written by pg_dump and pg_dumpall from PostgreSQL 10 on, with COPY or INSERT rows. Every test dump comes from PostgreSQL 13 and 18 and is compared with its source cell by cell.
Are serial and identity values kept?
Yes, for one column per table: the serial or identity column that leads the primary key becomes AUTO_INCREMENT and continues after the highest id. Sequences not owned by a column become MariaDB sequences at the value they had reached.
What happens to arrays, enums and other PostgreSQL types?
Arrays become JSON arrays, booleans tinyint(1), enums ENUM with the same labels, uuid UUID, and money a decimal. Ranges, geometric types, hstore and composite types keep PostgreSQL's text, and the report says which columns.
What about functions, triggers and views?
They are listed in the report by name and not converted, because each database writes them in its own language.
Which MariaDB versions does the script load on?
MariaDB 10.11 and later. Every test dump loads on 10.11 and 11.4, stopping at the first error if anything goes wrong.