GoAdmio

mariadb2postgre

Rewrite a mariadb-dump SQL file in place for PostgreSQL: convert data types and AUTO_INCREMENT columns, strip MariaDB-only syntax and index definitions, and fix quoting and escapes

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:

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

What it does not handle

The script has no rules for the items below, so expect psql errors on dumps that use them:

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