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 git@github.com: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
This work but a point to note is that I faced the issue when working with
python3
. It only worked when usingpython2
which I verified multiple times in a docker container