Migrating Rails Application Data from sqlite3 to PostgreSQL

While i was trying to deploy a rails app into heroku, I realized that i had to change my database to use PostgreSQL in production because that's the database used on heroku. I had never used pg before so this was a new experience to me but it wasn't as difficult as i had thought it would be, so here is how i did it.

Firstly though, if i was to give advice, it would be to just use the same database in development and production. I know a new rails app uses SQLite as the default database engine, but you can override that when running a new rails app command by just passing the -d "database".  

rails new app_name -d mysql

SQLite is fine and works perfectly however having to migrate data from it into a separate database in production is not something you want to be doing on a regular basis.

Most of the time, especially when you are just starting in Rails, you will just run the new command without giving a shit about the database that you are using, So you will first need to change a few things in your app, you need to change the gemfile add pg as your database engine in production and change the database.yml file and specify your database there as well so here is what you do. In your gem file add this line

group :production do 
gem 'pg' 
end

There's another line that looks like this gem 'sqlite3', change it to be like this 

group :development do 
gem 'sqlite3' 
end

What this piece of code does is instruct your app to use postgres in production environment  and sqlite in development, if however you want to use PostgreSQL throughout just add gem 'pg' but you also need to remove SQLite, just comment or remove it.

After you save your gemfile, just run bundle install to install the postgres adapter and its dependencies.

Now lets move to the database.yml file and add postgres there as well, remove everything and add this piece of code

development:
  adapter: sqlite3
  encoding: unicode
  database: databasename
  pool: 5
  username: name
  password: secret

test:
  adapter: sqlite3
  encoding: unicode
  database: testdatabasename
  pool: 5
  username: name
  password: secret

production:
  adapter: postgresql
  encoding: unicode
  database: productiondatabasename
  pool: 5
  username: name
  password: secret

I am assuming that you have already installed PostgreSQL in your system and that you have a user there called pgusername whose password is pgpassword, you can specify these credentials when you are installing postgres (http://www.postgresql.org/download/).

Now once you have saved your database.yml file just run the rake command to create your database

rake db:create:all

If the command runs successfully you should be able to see the three databases for development, production and test created.

Now its time we move all our data that we had created in sqlite into postgres, If you created your app by just running rails new appname and populated it with data, you should have a file called development.sqlite3 in the db folder of your app, so what we do is run this command

What this command does is take all the data in the development.sqlite3 and load to development_db database which is in postgres, restart your rails server and all your data has been moved to postres.

sequel -C sqlite://db/development.sqlite3 postgres://pgusername@localhost/development_db

Few things to note:

If in one of your models you had a method for searching objects, and you used the keyword LIKE in your search query, change it to be @@, postgres will somehow not return any result if you use LIKE

Example

Abc.find(:all, :conditions => ['name LIKE ? OR profile LIKE ?', keyword, keyword],:include => [:profiles])

Should now be

Abc.find(:all, :conditions => ['name @@ ? OR profile @@ ?', keyword, keyword],:include => [:profiles])

The Power of Postgres

PostgeSQL is a very powerful database engine, i seriously can't explain why its not as popular as its inferior competitor MySQL, one of the coolest feature in Postgres that i have come to like is hstore, it provides you with the capability to use hashlike data in your database (you know hashes are the bread and butter of any rails app), try it and i don't think you will use MySQL again.