Rails Connect with Postgresql database in application ubuntu

Question

I am using ubuntu 12.04 and rails 3.2. I am creating a rails application in which I'm using PostgreSQL databse. I installed postgresql using the following command:

sudo apt-get install postgresql

for reference i checked out https://help.ubuntu.com/community/PostgreSQL. Later I created the user postgres and set the password postgres using the following command

sudo -u postgres psql postgres
\password postgres

Next I created the database using:

 sudo -u postgres createdb mydb

I tried to connect with Postgresql with the username postgres and password postgres and got successfully connected with the following command:

psql -U postgres -h localhost
Password for user postgres:
psql (9.1.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
postgres=#

In my rails app my database.yml has the following code:

development:
 adapter: postgresql
 encoding: unicode
 database: mydb_development
 pool: 5
 username: postgres
 password: postgres

test:
 adapter: postgresql
 encoding: unicode
 database: mydb_test
 pool: 5
 username: postgres
 password: postgres

production:
 adapter: postgresql
 encoding: unicode
 database: mydb_production
 pool: 5
 username: postgres
 password: postgres

Now when I run the command rake db:migrate i get the following error:

rake aborted!
FATAL:  Peer authentication failed for user "postgres"

I tried adding host: localhost to database.yml for each environment and i get the following error:

rake aborted!
couldn't parse YAML at line 28 column 0

The line 28 is

development:
 adapter: postgresql
 encoding: unicode
 database: hackathonio_development
 pool: 5
 username: postgres
 password: testing
 host: localhost {This is line 28}

Please help me figure out a solution for this..

Answer 1

I think you may have 2 problems. First, the host not being set as Shreyas pointed out. But I believe the second problem is that when you set the hostname Rails is trying to connect to PostgreSQL via a tcp socket and not a local ruby socket. My guess is you need to modify your pg_hba.conf file to allow postgres to login via localhost. Below are a few SO questions with answers that may help.

Rails can't login to postgresql - PG::Error - password - Correct info

What's the difference between "local" and "localhost" connection types in pg_hba.conf?

Can't use postgres user in new database for rails 3 on ubuntu 10.04 server

Answer 2

My recommendation:

Step 1

Create a new, different user by running

$ createuser <username>

on the command line. You'll be prompted for password, etc. (Examples/docs: http://www.postgresql.org/docs/8.1/static/app-createuser.html)

Step 2

Update database.yml with the new users's username/password. Forget the first user you created, at least for now.

Step 2

$ rake db:create

Step 3

$ rake db:migrate

I think those steps are more likely to work that what you're trying.

Answer 3

Please add host to your yml as localhost