Most people face problems during Database Migration. I was having a Rails Application of Mysql database, later at a point of time I need to change the database from MySql to PostgreSql. I search in the Internet and havn’t found any methods that easily convert a 4.2 GB MySql dump into a Postgres dump.

After that I found Lanyrd’s MySQL to PostgreSQL conversion script. This will create a postgres dump for you, simply by trying some commands in the command prompt. By this blog I will help you to create a psql dump of your mysql database and also we will discuss the difficulties that you may face while using the mysql-postgresql-converter script.

 Mysql-Postgresql-Converter

The link that is provided below is the tool that I am going to introduce now. We can go through it step by step.

https://github.com/lanyrd/mysql-postgresql-converter

Step 1

First thing that you want to do is, go to the above link and clone it using the following command.

git clone ‘https://github.com/lanyrd/mysql-postgresql-converter.git ‘

Step 2

Go to that folder in the command prompt by,

cd mysql-postgresql-converter

and try the following command in the command prompt.

mysqldump – -compatible=postgresql – -default-character-set=utf8  -r MyMysqlDump.mysql -u root -p MyMysql

By providing the password of your mysql database, it will create a mysql dump called MyMysqlDump.mysql in the current folder and MyMysql is the database name.

Step 3

After that execute the following command in the command prompt

python db_converter.py MyMysqlDump.mysql MyPsqlDump.psql

Where MyMysqlDump.mysql is the dump that we create some time before and MyPsqlDump is the name of new psql dump that we are going to create. This command will take some time if your database is bigger in size.

It will display to you the number of tables and the number of inserts to that database and also the time required to create the dump.

Step 4

Next thing that we have to do is, load the psql dump into the new psql database. For that we have to create a new database in psql.

For that, execute the following command in command prompt:

sudo -u postgres createdb MyPsql

This will create a psql database called MyPsql. Then execute the following command to load the dump into the database.

psql -h localhost -d MyPsql -U postgres -f MyPsqlDump.psql

The execution of the above command, will display the status of migration as shown below.

Now you migrated your database from MySql to PostgreSql. Congrats you are done

Reference

• https://github.com/lanyrd/mysql-postgresql-converter