Copy data from Mysql to Postgres Part 2

  Tuesday, November 1, 2011 » mysql postgres

I showed you in my last post how to move data from mysql to postgres by using sqlalchemy. But that technique requires sqlalchemy metadata. What if you don’t have them?

You can fall-back to the Wiki I mentioned and get overwhelmed by the many tools available (some open source, some not). Two of the tools I tried didn’t work well. But one certainly did: mysql2postgres. I successfully migrated a Drupal and a phpBB installation using it. Although I had to create a few indexes by hand on the Drupal installation as it didn’t pick up all of them right.

Its usage is plain simple. After installation via gem:

gem install mysql2pgsql

You can invoke it once to generate a sample mysql2psql.yml file which you can then modify to set up the database connections. Invoke mysql2pgsql again and it will happily do its work.