Getting PostgreSQL into Rails3!

2010-09-14T23:28:00.000-07:00

Now, since Rails3 is released, many projects come to be on their phase of migration, but this time I'll show you how I got my Rails environment to work with PostgreSQL.

It's very important to carefully decide which database will be used for your application and what you really care about: Performance, Security, Availability; so in this case we'll just state that we need Security and Availability regardless of Performance, which better approaches us to PostgreSQL. It's important to say that I did this in Ubuntu 10.04 - Lucid Lynx distro.

OK so, let's begin with our brief trip:

I'd personally recommend using RVM (Ruby Version Manager) to manage Ruby versions since it's pretty easy to switch, install or remove any version you'd like to test, so let's run:

$ rvm install 1.8.7

and switch to that ruby version by running:

$ rvm 1.8.7

When we're ready to use ruby, lets install Rails, if you want, you could install it in any gemset defined in rvm by executing 'rvm gemset create <gemset_name>' and 'rvm gemset use <gemset_name>', so after this we do:

$ gem install rails --version 3.0.0

Once we have it on the rails, let's proceed to the main business... Installing PostgreSQL:

$ sudo apt-get install postgresql postgresql-client postgresql-contrib libpq-dev libpgsql-ruby libgda-4.0-postgres libpq-dev

Aditionally you can install 'pgadmin' visual management tool to make queries, add users or manage any other information from your databases with:

$ sudo apt-get install pgadmin3 postgresql-doc

OK, so at this point we have PostgreSQL installed in our system, so... what's next?? Go to sleep? Don't despair! we're near...
As you can see, we have Rails3 and PostgreSQL installed separately; all we need is an adapter to communicate each other, how do we do it? Easily, just add pg gem to your Gemfile:

gem 'pg'

And run the bundler:

$ bundle install

And in your 'config/database.yml' file use 'postgresql' as your adapter for each environment.

You could use 'postgres' username but I recommend using a different one. To create it you'll first have to connect to PostgreSQL server by doing:

$ sudo -u postgres psql template1

This will let you into PostgreSQL command line where you can tell the DBMS what you want to do, lets tell it we didn't like that name and create our own:

CREATE USER <user_name> WITH CREATEDB CREATEUSER ENCRYPTED PASSWORD '<password>';

I put some words in capital so you can identify the reserved words that PostgreSQL uses and in <> tags you can put your own information.
So this will create a user with database and user creation permissions with a defined password.

But if you opted to keep using 'postgres' user, you can always change the password with:

ALTER USER postgres WITH ENCRYPTED PASSWORD '<new_password>';

Note: Don't forget to put ';' (semicollon) at the end of each instruction in PostgreSQL console

And finally, we have to put that information into our 'config/database.yml':

development:

adapter: postgresql

database: <development_database_name>

username: <user_name>

password: <password>

host: 127.0.0.1

test:

adapter: postgresql

database: <test_database_name>

username: <user_name>

password: <password>

host: 127.0.0.1

production:

adapter: postgresql

database: <production_database_name>

username: <user_name>

password: <password>

host: 127.0.0.1

And that's it! We can test our connection by creating a model and putting some data in it, but don't forget to follow BDD with Cucumber and RSpec cycle! That's very important!

This is my first post so I'll appreciate any feedback, if there is any problem with the steps or additional help I may give you... Thank you in advance!

Helping the World Change..

Getting PostgreSQL into Rails3!