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.
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
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.