Migrating (transferring) data from MySQL to PostgreSQL

I was working on rails project and I faced this problem, my development environment DB is MySQL while production environment DB is PostgreSQL, and I wanted to move some data. I found the following 2 ways :

1. Using native database options (mysqldump and psql)

First, run the following command to dump the data/backup from Mysql

mysqldump -u USERNAME -p --no-create-info --no-create-db --compact --default-character-set=utf8 --compatible=postgresql DBNAME > dbname.sql

Then, the following code to set escape characters with correct value for PostgreSQL

sed -i "s/\\\'/\'\'/g" dbname.sql

And finally, import it to PostgreSQL

psql -U USERNAME -f dbname.sql

This way should works fine. Actually, it worked fine with me to migrate/transfer 60k rows out of 110k rows but I’d a problem that I couldn’t indentify that’s why I moved to the 2nd way.

2. Using the AWESOME python tool “py-mysql2pgsql”

Here’s the link to download and documentation :

http://packages.python.org/py-mysql2pgsql/

Leave a comment

 

WP-SpamFree by Pole Position Marketing