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:password@169.254.0.1: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." `