I have been using MySQL for probably as long as I could remember. For Bloglation, search capability is an important feature since it’s hard to browse each post one by one. I will probably implement tagging functionality, but even so, it’s important to be able to search the contents with a keyword(s). While Ultrasphinx works well, Heroku only supports WebSolr… I was using acts_as_ferret using /tmp for index files, but the problem using the /tmp directory is that ferret index files most likely to disappear at some point.

Then, I found out that Postgres supports full-text search and since Heroku uses Postgres, I could use other plug-ins like acts_as_tsearch or texticle for free. Free is important to me, since it’s not making any money.

Searching online, there are various ways to do it like Pivotal Labs’ script or Heroku’s Taps gem, but I wanted to do it in an old way like AEdifice to check everything is going alright at each step.

1. First thing to do is to backup MySQL

For me, it was important to backup preserving encoding, since it had many different languages. First I pulled db from Heroku thinking that I needed to convert the db at Heroku to Postgres since my local db was MySQL. However, it wasn’t necessary at all, since db at Heroku is already Postgres even though my local db is MySQL.

mysql -u root -p --no-create-info --compatible=postgresql --complete-insert --default-character-set=utf8 [db_name] > [whatever].sql

option is necessary to preserve unicode encoding, but it didn’t work for me. It didn’t matter since I don’t need to touch the db at Heroku, but I probably need to revisit it since I will have to move db at some point. This exercise is really for development and testing.

2. Prepare the SQL dump file for Postgres

You can follow AEdifice‘s blog post to prepare for Postgres until importing dump part.

3. Install and configure Postgres

Since I use Fedora, I followed the instruction described here, and it was just a breeze. Make sure you create a user, assign password and grant correct rights. It’s probably easy to create the same root user as MySQL.

4. Change database.yml file

development:
  adapter: postgresql
  encoding: utf8
  database: openT_development
  username: [username]
  password: [password]
  template: template0 # Required for UTF8 encoding

test:
  adapter: postgresql
  encoding: utf8
  database: openT_test
  username: [username]
  password: [password]
  template: template0 # Required for UTF8 encoding

production:
  adapter: postgresql
  encoding: utf8
  database: openT_production
  username: [username]
  password: [password]
  template: template0 # Required for UTF8 encoding

5. Run

to create db’s in Postgres

One caveat here is that Postgres does not allow option for “text” data type. In my schema.rb, sessions table’s data field had an option, so I had to delete the option for the tables to be created correctly.

6. Then you can continue AEdifice‘s blog post from importing the dump file

After importing is done, you should connect to your database and check to see if everything had been imported correctly.

7. Then you can follow Aaron’s blog post to install texticle gem or acts_as_tsearch to have full-text search in your rails app using Postgres db.