2

I generated my rails app with the default SQLite database, but after creating a few models and migrating a few times, I want to change it to Postgresql.

I added the postgres gem to my Gemfile, bundle install, then I replaced all my database.yml code from

development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 5
  timeout: 5000

to 

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  username: postgres
  password: mypass

development:
  <<: *default
  database: sample_app_development

test:
  <<: *default
  database: sample_app_test

production:
  <<: *default
  database: sample_app_production

I get a FATAL: password authentication failed for user "postgres" error even though the password is correct. Is it because I am missing a step? Am I supposed to tell PG using pg Admin III that I want to add this app to my server? Am I supposed to create a new role/connection?

I have run into this problem a few times and don't seem to be able to find an answer for this specific problem.

it gives me this when I try to run rake db:drop :

Couldn't drop sample_app_development : #<PGError: FATAL:  role "postgres" does not exist
>
Couldn't drop sample_app_test : #<PGError: FATAL:  role "postgres" does not exist
>

=========

Edmunds-MacBook-Pro:sample_app edmundmai$ createuser foo
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
Password: 
createuser: could not connect to database postgres: FATAL:  password authentication failed for user "edmundmai"
bigpotato
  • 26,262
  • 56
  • 178
  • 334
  • I think you need at least to set the `host` and `port` parameters; see http://stackoverflow.com/questions/10263821/rails-rake-dbcreateall-fails-to-connect-to-postgresql-database – Baldrick Dec 24 '12 at 18:25
  • after adding that I still get the error that the role doesn't exist. what exactly is a role and how do i create new roles? – bigpotato Dec 24 '12 at 18:33
  • @Edmund - For the simple version, just think of a role as a user. There's a lengthier discussion in [the docs](http://www.postgresql.org/docs/9.2/static/user-manag.html) if you really want to dig into it. – Xavier Holt Dec 24 '12 at 18:47

3 Answers3

2

Postgres user authentication is a bit weird. The default is to use the same authentication as the OS (at least in Linux). So to get to the Postgres prompt from the command line, you have to do something like this:

sudo -u postgres psql

Note that there's no password - and because the OS takes care of the authentication, there's no need for one (the OS'll ask for your sudo password, though, if required).

So option one is to just strip the password option out of your Rails config file and hope everything works out. Failing that, set up Postgres to accept password-based authentication by editing the pg_hba.conf file (mine's at /etc/postgresql/9.2/main/pg_hba.conf). Here's an example from my local server; the user "postgres" uses the OS's authentication ("peer"), but the user "opengeo" uses a password ("md5"):

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             opengeo                                 md5

Hope that helps!

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
Xavier Holt
  • 14,471
  • 4
  • 43
  • 56
  • hey xavier. when i type that sudo command i get: `sudo: unknown user: postgres`. I remember playing around with the pg_hba file before in the past, but I don't remember how to find it. Can you show me how I could find that file on my mac/ – bigpotato Dec 24 '12 at 18:46
  • @Edmund - Hmm. Sounds like you may have changed the default user name, then? And I wouldn't know where to find that file on a Mac, but it's a pretty distinctive filename - should pop up if you search for it. – Xavier Holt Dec 24 '12 at 18:50
1

To convert your database to postgresql first create a user as below:

$ createuser foo
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

To create a db:

CREATE DATABASE foo_db ENCODING 'UTF8' OWNER foo;

make sure your database.yml looks as below:

development:
  adapter: postgresql
  encoding: unicode
  database: foo_db
  pool: 5
  username: foo
  password:

test:
  adapter: postgresql
  encoding: unicode
  database: foo_test
  pool: 5
  username: foo
  password:
tokhi
  • 21,044
  • 23
  • 95
  • 105
  • hey stsd, I think you're on to something! However I get an authentication error when I try. I updated my answer with the error on the bottom. Please take a look and tell me what's wrong! – bigpotato Dec 24 '12 at 18:48
  • @Edmund, try this: `sudo -u postgres createuser -s foo` – tokhi Dec 24 '12 at 18:56
  • what OS are you using? If you are on Debian/ubuntu then you should be able to login to postgresql using this: `sudo -u postgres psql -U postgres` then you can issue any command e.g; `createuser` – tokhi Dec 24 '12 at 19:21
  • if the above comment didn't work out, then you can try: `sudo -u postgres psql` – tokhi Dec 24 '12 at 19:27
  • same error... Edmunds-MacBook-Pro:sample_app edmundmai$ sudo -u postgres psql sudo: unknown user: postgres – bigpotato Dec 24 '12 at 19:29
  • my login was working on the other apps i made a few weeks ago... now my pg admin iii even says the connection doesn't work. – bigpotato Dec 24 '12 at 19:31
0
development:
  adapter: postgresql
  database: postgres
  username: postgres
  password: ;ernakulam
  pool: 5
  timeout: 5000

test:
  adapter: postgresql
  database: postgres
  pool: 5
  timeout: 5000

production:
  adapter: postgresql
  database: postgres
  pool: 5
  timeout: 5000`
kleopatra
  • 51,061
  • 28
  • 99
  • 211
SNEH PANDYA
  • 797
  • 7
  • 22