Like a lot of developers, I’ve been watching the movement of MySQL since it has been acquired by Oracle. Since the acquisition, a lot of MySQL forks have been created. Think of MariaDB and Drizzle.
I’ve also been looking at PostgreSQL. Since long, I’ve wanted to start personal project using PostgreSQL. So I decided to migrate an existing project from MySQL to PostgreSQL. Since PostgreSQL is completely different from MySQL, you can’t just dump your MySQL data and import it into Postgres.
First off, I needed to extract the data from the MySQL database so it could be imported again in Postgres. Using the standard sql dump is not something I could do, due to the fact that data types are not always matching 1-on-1 between MySQL and Postgres ( ex. Booleans ).
So I came across yaml_db. YamlDb creates a database-independent format of your data. This way you can dump your data and import it into another database engine.
The latest versions of this gem only work on Rails 3. For Rails 2 projects, you’ll need to clone the rails-2 branch ( not covered here ).
Usage
Add the gem to your Gemfile and run bundle
gem 'yaml_db'
To test if everything is configured properly, check if the yaml_db rake tasks show up in the list:
$ bunlde exec rake -T
In the list you should find:
rake db:data:dump # Dump contents of database to db/data.extension (defaults to yaml) rake db:data:dump_dir # Dump contents of database to curr_dir_name/tablename.extension (defaults to yaml rake db:data:load # Load contents of db/data.extension (defaults to yaml) into database rake db:data:load_dir
Now run the dump command. This will create a yml file called data.yml in your db directory. Keep in mind to check your data first. You never know that some data might get malformed.
Once that is done, open up your config/database.yml file and change the settings so it will match your PostgreSQL database ( also don’t forget to change the adapter setting to “postgresql” ).
Make sure you have the right gems installed as well.
Since we start from a clean database, you’ll have to migrate again.
$ bundle exec rake db:migrate:all
The only thing left to do is run the load task:
$ bundle exec rake db:data:load
All your data should be stored in your newly created PostgreSQL database. Of course, this could be applied to other database engines, or the opposite action, migrating from PostgreSQL to MySQL.