Sunday, May 1, 2011

Setup a Rails 3 project with PostgreSQL on Ubuntu 11.04

So I've been working on a cool web application using Ruby on Rails for all the latest innovations in security, solid modelling and also for the extremely useful MVC concepts in use with Rails.

Yesterday I wanted to set up my Rails project to use a PostgreSQL database, so I searched on Google to find out how. After looking to about 10 different pages, I finally figured out how to do it, so here goes. This guide is written for Ubuntu 11.04, but later I'll probably post for openSUSE 11.4 since I've got to do this on there as well.

Step 1 - Install PostgreSQL
If you've already installed it on your machine, you may skip this step.

sudo apt-get install postgresql postgresql-contrib libpq-dev


We need libpq-dev to be able to install the Ruby pg gem. After the setup completes, then run the following commands:

sudo -u postgres createuser --superuser $USER
sudo -u postgres psql postgres


In the first command, using $USER will automatically add your user to PostgreSQL. If you want another user, just replace $USER with the correct user name. The second command will bring you directly into the psql console, and will open up the postgres database. It's just the database corresponding to the postgres user, which for some reason is necessary when installing PostgreSQL.

Once in the psql prompt, type the following command:

postgres=# \passsword <user>


Replace <user> with your actual user name. So anyway, type whatever password you want for your user IN the database twice, then once it returns you to the prompt, type "\q" (without the quotes!) to exit the psql prompt.

Now you'll be back in your terminal, and you can then proceed to create a database for your
user. If you really don't care to have a database for your user, just skip this step.

createdb $USER


Remember to replace $USER with your ACTUAL user name.

Also, you need to change the postgresql.conf file to make PostgreSQL listen on at least localhost. If you have a setup where you want to listen on an external IP or something, change this line to either the IP or just '*' or something.

/etc/postgresql/8.4/main/postgresql.conf:
listen_addresses = 'localhost'


Then, in pg_hba.conf, make sure you've got this:

/etc/postgresql/8.4/main/pg_hba.conf:
# "local" is for Unix domain socket connections only
local   all         all                               md5


Otherwise you'll probably get an error when trying to login from your Rails app. After this, all you've got to do is simply start PostgreSQL using this command:

sudo /etc/init.d/postgresql start


And you're set to go!



Step 2 - Install Ruby Gems
This assumes that you have installed Ruby, Rails, and Bundler on your machine. Also, this is for Rails 3 only, so all you Rails 2 lovers, go away!

Type this command:

gem install pg


or

place this in your Gemfile:

gem 'pg'


and type "bundle install" to install the gem.


Step 3 - Configure Your Rails 3 Project
To create a new Rails project that uses the PostgreSQL database, use the following command:

rails new <project_name> -d postgresql


Replace <project_name> with the name of the project you want to create.

Alternatively, if you have an existing project that you want to switch over to PostgreSQL, just modify your config/database.yml file as follows:

config/database.yml:
# PostgreSQL v0.8.x
#   gem install pg
development:
  adapter: postgresql
  encoding: unicode
  host: localhost
  database: <project_name>_development
  pool: 5
  username: <user>
  password: <password>

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: postgresql
  encoding: unicode
  host: localhost
  database: <project_name>_test
  pool: 5
  username: <user>
  password: <password>

production:
  adapter: postgresql
  encoding: unicode
  host: localhost
  database: <project_name>_production
  pool: 5
  username: <user>
  password: <password>


Replace <project_name> with name of your project. Keep in mind that you DON'T need to have the naming like I have above; you can put whatever you really want, as long as the names are not the same. Anyway, also change <user> and <password> to the username (probably the name you chose when setting up PostgreSQL in Step 1) and the password you chose for that user.

A nice thing about the database.yml file is that you can specify even a different server (in the host: field) for each level of development, so you can have more advanced setups if you like, even though that's really all up to you. If you want to go crazy and have a server for development only, one for test only, and one just for production, you can! Not a bad idea.....

You will need to add the development and test databases above using the createdb command.

createdb <project_name>_development
createdb <project_name>_test


Replace <project_name> with the name of your Rails app, as you did in the database.yml file.

Step 4 - Install Extra Apps (Optional)There are 2 applications that help alot when you want to administer you PostgreSQL database. The first is pgadmin3. It's a GUI frontend for administrating your PostgreSQL database. I recommend this if you have install your database on your development machine, since you don't need web access and you can save on the apache server.

To install pgadmin, type the following command:

sudo apt-get install pgadmin3

The second application is called phppgadmin. It does the same stuff as pgadmin3, but it is web-based and lets you administer remote database servers easily. It's the PostgreSQL counterpart of phpmyadmin, and it seems to work great.

To install phppgadmin, use this command:

sudo apt-get install phppgadmin

Once the installation has completed, start the web server:

sudo /etc/init.d/apache2 start

Or replace "start" with "restart" if you already are running your apache2 web server. You can then access phppgadmin by going to the following web address:

http://localhost/phppgadmin

You'll need to login using your username and password, and you're set to go!

7 comments:

  1. Thanks so much for this post! I found you by googling "install postgresql rails ubuntu 11.04". Your instructions worked great. I decided to create a new app with the "-d postgresql" option, just to see if it was different from the config you had posted. The only important difference was that it didn't include the "localhost" line... which I thought was great so I added it in (as I use this locally for development). Thanks again for this helpful post.
    Cheers,
    Graham

    ReplyDelete
  2. Thanks very much for this - it was the missing link for me, and I can't find anywhere with as good information.

    I'm going to do a post on my blog in a couple of days on the process from start to finish for rails + postgresql.

    ReplyDelete
  3. Thanks, Jesse, for what I think is the easiest post to follow to set up postgresql on Ubuntu. I used it today for 12.04. However, I think we can do slightly better when it comes to setting up a Rails app to work with postgresql.

    From config/database.yml, we can leave out the username and password lines. Then create the db using the standard "rake db:create" command, and it will take the $USER as the db owner for the _xxx dbs.

    Or is there some scenario where explicitly providing the username and password makes sense (in dev and test environments)?

    ReplyDelete
  4. God thank you. This was the least painful ubuntu tutorial I have ever been through.

    ReplyDelete
  5. Awesome tutorial! thank you for your help! was looking for this the whole day

    ReplyDelete
  6. Not so far I have found new cool tool to work with PostgreSQL on ubuntu — Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview

    ReplyDelete