Databases

Rails and Postgresql on Mac Snow Leopard, Heroku

I was looking to use Sphinx which is a search plugin for Rails but requires Postgresql as the database containing the data to search. Since I have been using Sqlite3 on my development setup, this meant making a shift into the Postgres world. Installing it was pretty straightforward on the Mac.

1. Install Mac ports. This utility is similar to ‘apt’ on Debian or ‘yum’ on RedHat. After installing, run ‘which port’ to see that it was successful.

$ which port
/opt/local/bin/port

2. Using Mac ports, install postgresql 8.4 using the following:

sudo port install postgresql84 postgresql84-server

3. Make a location for a default database

$ sudo mkdir -p /opt/local/var/db/postgresql84/defaultdb

4. Make its owner as user ‘postgres’

$ sudo chown postgres:postgres /opt/local/var/db/postgresql84/defaultdb

5. Run ‘initdb’ to initialize it once

$ sudo su postgres -c '/opt/local/lib/postgresql84/bin/initdb -D \
/opt/local/var/db/postgresql84/defaultdb'

6. To make it run always, enter

sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql84-server.plist

7. Start the db server using:

sudo launchctl start org.macports.postgresql84-server

8. Create a user called ‘postgres’ using your system admin username:

createuser --superuser <admin_username> -U postgres

9. Now try creating and removing a db to test if everything worked:

createdb temp
psql temp - this should take you into a db sql prompt
dropdb temp

10. Now install the ‘pg’ gem which acts as an adapter between your Rails app and the postgres db. The best way to install is to include it in your Rails app’s Gemfile and run ‘bundle install’.

11. Next, create a new app temporarily in order to know the format of your new database.yml file for the postgresql db that will be created.

rails new temp_app -d postgresql

Open and view the ‘config/database.yml’ file. Take a back up of your sqlite3 database.yml and copy this newly created file to your Rails app into ‘config/’. Change the username and database names accordingly as it would be set to ‘temp_app’ and ‘temp_app_development’. Other values can stay the same.

12. Create the database with the name you set in the above database.yml file

createdb myapp_development

13. Now run ‘rake db:migrate’ to create the tables according to the schema.rb file. This will create the same tables as your sqlite3 database in the new postgres database.

14. If your sqlite3 db has data, get its dump using:

sqlite3 db/development.sqlite3 .dump > sqlite3-db.dump

15. I read somewhere that one should be able to import this dump into the postgres database but that did not work for me. A simple script should be able to run the ‘INSERT’ commands listed in the dump file into your new postgres db (psql into the db, run INSERT commands). Make sure that you use single quotes around any numbers that are present in the sqlite3 dump data, such as IDs. You might also have to make sure your IDs are unique.

16. Restart your app and now your new data should be getting inserted into the Postgres database.

17. If you use Heroku, then push in all the changes to the files. Then, run the following:

:myapp$ heroku rake db:migrate

Install the gem ‘taps’ that will allow pushing your local data to the Heroku server (which will now also use Postgresql!)

:myapp$ heroku db:push

Done!

PS: If you see a segmentation fault issue upon access to the Postgres database, then set the following env. variable:

export RUBYOPT='-r openssl'