Migrating data from Rails on MySQL DB to Postgres SQL for use with Heroku

Question

I have a RAILS 3.x application developed locally using MySQL which has data which needs migrating. Now I want to deploy to Heroku, which uses Postgresql and also transfer the data.

The issue is in dealing with the columns with NULL data which the import process doesn't like.

I've attempted using a number of different strategies such as

yaml_db gem - fails to import at all
rails-backup-migrate gem - doesn't like the encoded NULL element;
taps - fails on import without much detail

Has anyone tried any other strategies, gems or methods? Should I just start over with a Postgresl local DB instead ?

thanks in advance grant

not well versed in the problem, is there some way to use an intermediary like sqlite locally before pushing to heroku? — ZMorek
– ZMorek, Commented Nov 1, 2011 at 22:23
Yes - i've run the migrations to create the tables and this has the data populated from another legacy system. But just in case will re-check. — Grant Sayer
– Grant Sayer, Commented Nov 1, 2011 at 23:06
Yes - tried the heroku db:push which chugs along and then fails without too much help. — Grant Sayer
– Grant Sayer, Commented Nov 2, 2011 at 3:05
Heroku actually supports (by that I mean provides support for) taps. They wrote it. There may be a way to get more debug info out which they can help you with. We've done similar migrations successfully via taps. — Tom Lianza
– Tom Lianza, Commented Dec 24, 2011 at 6:21

Jared Beck · Accepted Answer · 2011-12-24 04:57:24Z

2

Should I just start over with a Postgresl local DB instead ?

Heroku recommends that you use Postgres for local development.

Your production and development environment should be as close to identical as possible. This prevents difficult to diagnose bugs introduced by subtle differences between your environments. Every application on Heroku comes with a PostgreSQL database as the default SQL database. Therefore you should use PostgreSQL for your local development database as well. ( http://devcenter.heroku.com/articles/rails3 )

A quick search turned up this:

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL

Good luck!

answered Dec 24, 2011 at 4:57

Jared Beck

17.6k10 gold badges76 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Richard Peck · Accepted Answer · 2013-10-13 12:37:23Z

0

I had this problem too and found this gem of a blog post.

It says you can use a gem called Valkyrie to migrate the data from a MySQL database to a PostgreSQL database. You just have to install the gem using gem install valkyrie and then use this command:

valkyrie mysql://datachomp@localhost/seppuku?password=QuickAndPainless postgres://[email protected]/seppuku

answered Oct 13, 2013 at 12:37

Richard Peck

76.8k9 gold badges98 silver badges148 bronze badges

Collectives™ on Stack Overflow

Migrating data from Rails on MySQL DB to Postgres SQL for use with Heroku

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related