Import mysql dump file to postgres

[oferdinho]

Hey!

I am trying to import a mysql dump file (that was dumped with --compatible=postgresql) using pgloader into a running postgres db. I saw in the documentation an example for doing so with sqlite, is it not supported for mysql as well? I tried doing so with compiling from sources and also with the pgloader docker container. I am trying to avoid importing the mysql dump file to a mysql db and then run a pgloader migration.

Many thanks in advanced.

• [ ] pgloader --version sources: pgloader version "3.6.48d8ed0" compiled with SBCL 1.4.5.debian docker: pgloader version "3.6.3~devel" compiled with SBCL 1.4.16.debian

This is the load.conf:

LOAD DATABASE FROM mysql://./dump/mysqldump INTO postgresql://postgres:[email protected]:5432/my_db

[cristianburca]

is this working?

[denizzzka]

@cristianburca no

[am0z]

lol, wasted two hours of my life before finding this

[thexperiments]

it would really be great to have this, currently struggling with this as I'm trying to migrate a database which is in a container that is not accessible from anywhere outside the docker network

[BrianJM]

it would really be great to have this, currently struggling with this as I'm trying to migrate a database which is in a container that is not accessible from anywhere outside the docker network

Same here.

[fredrickwampamba]

This will be of help

`#!/bin/bash

Define input and output files

MYSQL_DUMP_FILE="kwaugsms.sql" POSTGRES_DUMP_FILE="postgres_dump.sql"

Convert MySQL SQL dump to PostgreSQL equivalent

sed -e 's/`/"/g'
-e 's/IF NOT EXISTS//g'
-e 's/AUTO_INCREMENT//g'
-e 's/UNSIGNED//g'
-e 's/ENGINE=InnoDB//g'
-e 's/DEFAULT CHARSET=utf8//g'
-e 's/CHARACTER SET utf8 COLLATE utf8_unicode_ci//g'
-e 's/utf8_unicode_ci/citext/g'
-e 's/ON UPDATE CURRENT_TIMESTAMP//g'
-e 's/TIMESTAMP/datetime/g'
-e 's/mediumint/int/g'
-e 's/mediumtext/text/g'
-e 's/timestamp(6)/timestamp/g'
-e 's/UNIQUE KEY/UNIQUE/g'
-e 's/SET character_set_client=utf8//g'
-e 's/SET collation_connection=utf8_unicode_ci//g'
-e 's/SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"//g'
-e 's/ENGINE=MyISAM//g'
-e 's/ROW_FORMAT=DYNAMIC//g'
-e 's/ROW_FORMAT=COMPRESSED//g'
-e 's/COMMENT=[^;]//g'
-e 's/ENUM([^)])/text/g'
-e 's/BINARY/varbinary/g'
-e 's/ZEROFILL//g'
-e 's/UNSIGNED//g'
-e 's/USING btree//g'
-e 's/USING BTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/USING HASH//g'
-e 's/USING RTREE//g'
-e 's/INT(([0-9]))/integer/g'
-e 's/DROP INDEX/COMMENT ON INDEX/g'
-e 's/,\sCONSTRAINT \S+ FOREIGN KEY ([^()]) REFERENCES ([^ ]) ([^,])//g'
-e 's/,\sCONSTRAINT \S+ FOREIGN KEY ([^()]) REFERENCES ([^ ]) ([^,])//g'
-e 's/CONSTRAINT \S+ FOREIGN KEY ([^()]) REFERENCES ([^ ]) ([^,])//g'
-e 's/CONSTRAINT \S+ FOREIGN KEY ([^()]) REFERENCES ([^ ]) ([^,])//g'
-e 's/unsigned//g'
-e 's/PRIMARY KEY ([^)])/PRIMARY KEY/g'
-e 's/,\sPRIMARY KEY ([^)])//g'
-e 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g'
-e 's/,\sUNIQUE KEY [^,]//g'
-e 's/,\sKEY [^,](?:([^)]))?//g'
-e 's/,CONSTRAINT [^,](?: FOREIGN KEY ([^)]))?//g'
-e 's/,CHECK (([^)]))//g'
-e 's/ AUTO_INCREMENT=[0-9]//g'
-e 's/UNSIGNED//g'
-e 's/REFERENCES ([^ ])/REFERENCES "\1"/g'
-e 's/INT([0-9])/INT/g'
-e 's/TEXT([0-9])/TEXT/g'
-e 's/DATETIME/ TIMESTAMP/g'
-e 's/bool /boolean /g'
-e 's/DEFAULT NULL//g'
-e 's/DEFAULT CURRENT_TIMESTAMP//g'
-e 's/ ON UPDATE CURRENT_TIMESTAMP//g'
-e 's/datetime/timestamp/g'
-e 's/ENGINE=MEMORY//g'
-e 's/SET storage_engine=MEMORY//g'
-e 's/ALTER TABLE/COMMENT ON TABLE/g'
-e 's/latin1/utf8/g'
-e 's/ mediumtext/ text/g'
-e 's/ tinyint([0-9])/ smallint/g'
-e 's/ mediumint([0-9])/ integer/g'
-e 's/ int([0-9])/ integer/g'
-e 's/ unsigned//g'
-e 's/ auto_increment//g'
-e 's/character set latin1/character set utf8/g'
-e 's/enum(/text check (check_column in (/g'
-e 's/enum(/text check (check_column in (/g'
-e 's/enum(/text check (check_column in (/g'
-e 's/enum(/text check (check_column in (/g'
-e 's/enum(/text check (check_column in (/g'
-e 's/enum(/text check (check_column in (/g'
-e 's/enum(/text check (check_column in (/g'
-e 's/enum(/text check (check_column in (/g'
-e 's/enum(/text check (check_column in (/g'
-e 's/enum(/text check (check_column in (/g'
-e 's/enum(/text check (check_column in (/g'
-e '/^SET/d'
-e '/^DROP/d'
-e '/^LOCK/d'
-e '/^UNLOCK/d'
-e '/^INSERT/d'
-e '/^/*/d'
-e '/^--/d'
-e '/^/*!/,/^SET/d'
-e '/^/*[^!]/d'
-e '/^\s$/d'
"$MYSQL_DUMP_FILE" > "$POSTGRES_DUMP_FILE"

echo "MySQL SQL dump converted to PostgreSQL equivalent successfully." `