Convert MySQL dump to PostgreSQL dump

convert_mysql_dump_to_postgresql_dump.md

Prepare database dump using mysqldump:

mysqldump -u [username] -p [databaseName] --compatible=postgresql --default-character-set=utf8 > database.sql

Clone mysql-postgresql-converter project:

git clone [email protected]:lanyrd/mysql-postgresql-converter.git
cd mysql-postgresql-converter

Assuming that your mysql/mariadb dump is named database.sql, place that file into the mysql-postgresql-converter directory.

Convert mysql dump to postgresql dump:

python db_converter.py database.mysql database.psql

Replace datetime field with timestamp field.

sed -i 's/datetime(6)/timestamp with time zone/' database.psql

Import dump into postgres:

psql -U dbname -f database.pgsql

Line 25 (of 2738: 0.91%) [0 tables] [0 inserts] [ETA: 0 min 0 sec]Traceback (most recent call last):
File "db_converter.py", line 225, in
parse(sys.argv[1], sys.argv[2])
File "db_converter.py", line 81, in parse
current_table = line.split('"')[1]
IndexError: list index out of range

@sajid-nltsf:

It used to work back then. Raise an issue in the lanyrd/mysql-postgresql-converter.

This work but a point to note is that I faced the issue when working with python3. It only worked when using python2 which I verified multiple times in a docker container