fousa blog

I never thought I had to do this but I'm migrating my MySQL database to PostgreSQL for my Rails application! The reason why is that I want to use the same type of database in development as in production.

The reason for using PostgreSQL as a production DB is thanks to Heroku. This is a great hosting company!

I had a few problems while migrating!

INSTALLATION POSTGRESQL

First off all I had to install postgresql on my Mac... I found a great blog post concerning this subject! Read it here.

DATEBASE CONFIGURATION

Configuring your Rails project to use PostgreSQL is very easy. Just modify your database.yml file so that it looks like this:

development: adapter: postgresql database: <database> username: <username> password: <password> charset: utf8

IMPORT/EXPORT

I wanted to import the data from my MySQL DB and import it into my PostgreSQL DB. For that I created a dump from my old DB like this:

mysqldump –-user <username> –-password=<password> <databasename> > <dumpfile>

Next thing was to import them into my PostgreSQL DB.

psql -d <databasename> -a -f <dumpfile>

QUERY PROBLEM

Next I had one minor issue with a custom made MySQL query I performed in my Rails app.

SELECT * FROM users WHERE YEAR(birtdate) = 1984

This caused a casting issue in PostgreSQL. So I had to change the above query to:

SELECT * FROM users WHERE EXTRACT(YEAR FROM birtdate) = 1984