mariadb2postgre
Rewrite a
mariadb-dumpSQL file in place for PostgreSQL: convert data types andAUTO_INCREMENTcolumns, strip MariaDB-only syntax and index definitions, and fix quoting and escapes
- Command:
/go/mariadb2postgre - Category: Databases
- Required option:
<file>(path to the SQL dump) - Output: None. The file is changed in place.
- Last updated: 2026-11-20
On this page: Overview · Quick start · Syntax · Options · Output · Examples · Troubleshooting · Details · See also
Overview
/go/mariadb2postgre takes a SQL dump made by mariadb-dump and rewrites it with a series of text substitutions, so that PostgreSQL can load it with psql. It:
- deletes MariaDB-specific comment, locking, and transaction lines
- converts common data types (
int(11),tinyint(1),datetime,varchar, and others) - turns
AUTO_INCREMENTcolumns into PostgreSQL identity columns - removes
ENGINE=...table options and all index and key definitions - fixes quoting and escape sequences
- renames the table
UsertoUsers, becauseuseris a reserved word in PostgreSQL
It is a quick text conversion, not a full migration tool. It changes the file in place, without making a backup, and it also changes some values inside the data rows. Read What it does not handle before you rely on the result.
Quick start
# 1. Dump the database
mariadb-dump mydb > mydb.sql
# 2. Keep the original, convert a copy
cp mydb.sql mydb-pg.sql
/go/mariadb2postgre mydb-pg.sql
# 3. Load the copy into PostgreSQL (create the database first)
sudo su - postgres -c "psql -d mydb -f /tmp/mydb-pg.sql"
Syntax
/go/mariadb2postgre <file>
| Form | Behavior |
|---|---|
/go/mariadb2postgre <file> |
Rewrites <file> in place. |
Pass the path of an existing file. The path is not quoted inside the script, so a path with spaces will not work.
The command does not check that the file exists or that it is a dump. It also does not dump the database for you.
Options
This command has no options. It takes only the file path.
| Argument | Required | Default | Notes |
|---|---|---|---|
<file> |
Yes | — | The SQL dump to convert. It is overwritten. |
Output
The command prints nothing on success. It only writes the line below to /var/log/go/mariadb2postgre.log, so you can see when it ran and on which file.
[Nov 20 10:15:42]# mariadb2postgre mydb-pg.sql
Check the result by opening the converted file, or by loading it and reading the psql errors.
Security
[!WARNING] The original file is overwritten and there is no undo. Copy the dump first, as in Quick start.
[!WARNING] The substitutions run on the whole file, including the data rows, not only on the table definitions. Some values are changed or lose characters. See Changes to your data.
Examples
# Convert a copy of a dump
cp shop.sql shop-pg.sql
/go/mariadb2postgre shop-pg.sql
Before:
CREATE TABLE `Orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
After:
CREATE TABLE Orders (
id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name character varying(100) DEFAULT NULL,
created TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00');
The PRIMARY KEY line and the KEY line were removed. The identity column carries the primary key instead, and the idx_name index is gone.
# After loading, reset each identity sequence to the highest existing id
# (replace Orders and id with your table and column)
sudo su - postgres -c "psql -d shop -c \"SELECT setval(pg_get_serial_sequence('orders','id'), (SELECT MAX(id) FROM Orders));\""
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Command seems to hang | No file name was given, so the final step waits for keyboard input. | Press Ctrl+C and run it again with a file path. |
sed: can't read <file>: No such file or directory |
Wrong path. | Check the path. |
| Permission errors on the last step | The command was run as a normal user and the file belongs to root. | Run it as root. |
psql error at longtext, smallint(6), enum(...), or datetime(6) |
These types are not converted. | Edit them by hand. See What it does not handle. |
psql syntax error at current_timestamp() or ON UPDATE |
MariaDB-only syntax is not removed. | Edit the file, for example change current_timestamp() to current_timestamp, and delete ON UPDATE .... |
Text values lost their line breaks and contain stray n or r characters |
All backslashes are removed, including \n and \r. |
Convert with a tool that handles escapes, such as pgloader, or fix the affected rows. |
Characters from \uXXXX sequences are missing |
These escapes are deleted. | Fix the affected rows after the import. |
| Duplicate key errors when adding new rows | Identity sequences still start at 1. | Reset each sequence (see Examples). |
Application can't find the table User |
The table was renamed Users. |
Change the application, or rename the table back after the import. |
| Foreign key errors | Primary and unique keys were removed, so the referenced columns may no longer be unique. | Re-create the keys, then the foreign keys. |
| The original dump is gone | The file was converted in place. | Dump the database again. |
Details
What it changes
The substitutions run in this order.
| Step | Change |
|---|---|
| 1 | Deletes lines starting with /* (MariaDB version comments such as /*!40101 ... */). |
| 2 | Deletes lines starting with -- (comments). |
| 3 | Deletes LOCK TABLES and UNLOCK TABLES lines. |
| 4 | Deletes set autocommit and commit; lines (any letter case). |
| 5 | Replaces AUTO_INCREMENT with GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY. |
| 6 | Replaces char(0) with char(255). |
| 7 | Replaces the date 0000-00-00 with 2000-01-01. |
| 8 | Replaces varchar( with character varying(, and char( with character(. |
| 9 | Converts tinyint(N) to smallint, bigint(N) to bigint, int(N) and mediumint(N) to integer. |
| 10 | Converts mediumtext to text, DOUBLE to DOUBLE PRECISION, and DATETIME to TIMESTAMP. |
| 11 | Removes unsigned. |
| 12 | Removes all backticks. |
| 13 | Removes the ENGINE=... table options, up to and including the ; on the same line. |
| 14 | Deletes the lines that define a PRIMARY KEY (, UNIQUE KEY, or KEY name (. |
| 15 | Renames the table User to Users in DROP TABLE, CREATE TABLE, and INSERT INTO statements. |
| 16 | Turns \' into '', deletes \uXXXX sequences, and then deletes every remaining backslash. |
| 17 | Removes the trailing comma before the closing ); of a CREATE TABLE. |
Type names are matched with a space on both sides (for example int(11)), which is how mariadb-dump writes them. Matching ignores letter case, except for the key-line deletions in step 14 and the LOCK TABLES and UNLOCK TABLES lines in step 3.
The script contains one rule that never matches: the second bigint(N) line uses a pattern that doesn't work as written. It does no harm, because step 9 already handles bigint(N).
Changes to your data
Because the substitutions run on every line, not just on CREATE TABLE statements, they also affect row values:
| Change | Effect on data |
|---|---|
0000-00-00 becomes 2000-01-01 |
Zero dates (which PostgreSQL rejects) become 1 January 2000. Real text containing this string is changed too. |
| All backslashes are deleted | \n and \r in text become the letters n and r, and \\ disappears. Line breaks are lost. |
\uXXXX sequences are deleted |
For example, escaped Unicode inside JSON text is removed. |
| Backticks are deleted | Any backtick in a text value is removed. |
unsigned is deleted |
Unsigned columns become signed. Values above the signed range won't fit. |
| Matches inside values | Text such as varchar( or DATETIME inside a value is also rewritten. |
Primary keys, indexes, and sequences
- Only columns that were
AUTO_INCREMENTkeep a primary key (the identity column getsPRIMARY KEY). - Every other primary key, such as a composite key, and every
UNIQUE KEYandKEYline is deleted. All secondary indexes and unique constraints are lost. Re-create them after the import. FOREIGN KEYconstraints are left unchanged, so they can fail if the referenced column no longer has a key.- Identity columns start at 1 after the import, even when rows were loaded with larger ids, so reset the sequences before adding rows (see Examples).
- Key lines whose names contain characters other than letters, digits, or
_(for example a hyphen) are not matched and stay in the file.
What it does not handle
The script has no rules for the items below, so expect psql errors on dumps that use them:
- other types:
smallint(N),longtext,tinytext,blobtypes,enum,set,year, and types with a precision such asdatetime(6) - MariaDB-only column clauses:
ON UPDATE ..., column-levelCHARACTER SETorCOLLATE,COMMENT '...', and defaults such ascurrent_timestamp() - stored procedures, functions, triggers, events, and views (
DELIMITERblocks) - a table named
Useris renamed, but other reserved words used as names are not - table options that span more than one line
For a dump that uses these, run the script first, then fix the remaining errors by hand, or use pgloader, which converts directly from a live MariaDB database.
Loading the result
The script does not import anything. The comments at the end of the script show the next step:
sudo su - postgres -c "psql -d <database> -f <file>"
Create the database and user first with postgre. Because psql runs as the postgres user, put the converted file somewhere that user can read it (for example /tmp), not in /root.
See also
| Command | Description |
|---|---|
pgloader |
Install PGLoader |
postgre |
Create, edit, or delete a PostgreSQL database and user |
postgresql |
Install and start PostgreSQL |
postgresync |
Copy PostgreSQL data to a remote server |
import |
Import a SQL dump into an existing MySQL/MariaDB database, pre-creating tables from the dump's INSERT statements |