How to set up Postgres database for local Rails project?

You follow this link http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/

to create a postgres user and replace the credentials in database.yml

firstly, install postgresql

sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update

#now install postgresql
sudo apt-get install postgresql-9.1 libpq-dev

create a new user in psql

sudo su postgres
createuser user_name #Shall the new role be a superuser? (y/n) y

Gemfile

gem 'pg'

bundle install

development.yml

development:
  adapter: postgresql
  database: app_development
  pool: 5
  username: user_name
  password:

I came across your question when looking for the same answer. I attempted to follow the instructions @prasad.surase gave you. The problem I found is the ppa repository is going to depreciate soon on 12.04 LTS. Instead I found this link and it really helped.

PostgreSQL setup for Rails development in Ubuntu 12.04

1. Install postgresql and admin tools through the package manager

   sudo apt-get install postgresql libpq-dev phppgadmin pgadmin3
   

2. Login to postgresql prompt as the postgres user

   sudo su postgres -c psql 
   

3. Create a postgresql user for your project

   create user username with password 'password';
   

4. Setup your postgres user with the same name and password as your Ubuntu user and make him a postgres superuser

   alter user username superuser; 
   

5. Create the development and test databases

   create database projectname_development;
   create database projectname_test; 
   

6. Give permissions to the user on the databases

   grant all privileges on database projectname_development to username;
   grant all privileges on database projectname_test to username; 
   

To end the postgresql session type \q

Update password for the user

alter user username with password ‘new password’;