Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am migrating my site from PHP to Rails.

At the same time I want to migrate my database from MySQL to PostgreSQL. However, the schema I have in the MySQL database is poor. Therefore, I want to implement a new schema in the PostgreSQL database.

Basically, I want take the data from the MySQL database and I want to fit it to the new schema in the PostgreSQL database. The new tables in the PostgreSQL database consisted of joined views from the MySQL database.

I am new to this sort of thing and I don't really know to start.

share|improve this question
 
You're going to have a lot more problems that just migrating your data. PostgreSQL is, thankfully, quite a bit stricter than MySQL: your GROUP BYs will probably break, your strings won't be silently truncated, etc. –  mu is too short Apr 19 '11 at 0:54
add comment

2 Answers

up vote 0 down vote accepted

I had to to this in the past - your answer is a called "taps":

http://adam.heroku.com/past/2009/2/11/taps_for_easy_database_transfers/

It's basically a middle-man between mysql and postgres and will be able to handle all the differences between them.

share|improve this answer
add comment
up vote 0 down vote

If your schemas are radically different, you are going to have to write a script to do the necessary transformations. You can use a Database Abstraction Layer to handle the differences between MySQL and PostgreSQL, but for the most part you're on your own.

share|improve this answer
 
How do you transform the data? Do I alter the table structures? What do you mean by this? –  Peter Apr 19 '11 at 1:17
 
I mean, write a script (in PHP or Ruby. Your choice.) to fetch the data out of the MySQL database, make the alterations, then insert it into the PostgreSQL database. –  Sean Apr 19 '11 at 1:22
 
I am new to Ruby, so how would you suggest that I write this script? How do you select from a database using Ruby? –  Peter Apr 19 '11 at 1:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.