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..

Does adding `host: localhost` to the bottom of each environment fix the problem? — jake, Aug 14 '12 at 12:46
There is an answer to that in here: http://stackoverflow.com/questions/9987171/rails-3-2-fatal-peer-authentication-failed-for-user-pgerror — davidrac, Aug 14 '12 at 12:47
@dezso Can you tell me a command to check if those DB's exist. I checked out lot many links but dint get an appropriate solution. — Swati Aggarwal, Aug 14 '12 at 12:55
Try this: `psql -U postgres` and then `\c mydb_production` for example. — dezso, Aug 14 '12 at 12:58
I think there is indentation of 2 in YAML? no? odd that you should get yaml parsing error. — Aditya Sanghi, Aug 14 '12 at 14:00

score 1 · Answer 1 · edited May 23 '17 at 12:14

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

score 0 · Answer 2 · answered Aug 14 '12 at 12:46

0

Please add host to your yml as localhost

answered Aug 14 '12 at 12:46

Shreyas Agarwal

1,128
8
10

I did as you said but i again got an error. I edited my question and mentioned the error. – Swati Aggarwal Aug 14 '12 at 12:50
Add 127.0.0.1 in your host and please remove the {} in your yaml file – Shreyas Agarwal Aug 14 '12 at 12:57
{} is not present in yaml file. I jst wrote it to specify the line number. – Swati Aggarwal Aug 14 '12 at 13:11
Then please check spaces in your YAML file. YAML is not able to read your yaml file properly I guess. – Shreyas Agarwal Aug 14 '12 at 13:49

score 0 · Answer 3 · answered Aug 14 '12 at 13:57

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.

Rails Connect with Postgresql database in application ubuntu

3 Answers3