Rails: rake db:create:all fails to connect to PostgreSQL database

Question

I am trying to create a Rails app that uses PostgreSQL. Here is a description of what I did.

---

PostgreSQL setup:
I installed PostgreSQL 9.1.3 via the ppa:pitti/postgresql maintained by Martin Pitt. There was PostgreSQL 8.4 installed before; I am not sure if it is still installed or gone.

• I added a database user with superuser rights to the database that has the same name as my Ubuntu account.
• I start the database daemon with sudo service postgresql start.
• I installed pgadmin3, Version 1.14.0 Beta 1 via ppa:rhonda/pgadmin3 maintained by Gerfried Fuchs.
• I can connect via pgadmin3 using my user account and password and port 5433.

My postgres configuration in pg_hba.conf is as follows (removed comments for readability).

[...]
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

---

Rails setup:
Now I want to create a Rails application that uses PostgreSQL.

• I installed Ruby 1.9.3-p125 via RVM.
• I installed Rails 3.2.3 into the Gemset ruby-1.9.3-p125@global.
• I created a .rvmrc and Gemset for the application.
• I created a Rails application via rails new my_test_app -d postgresql.
• I configured the user name and password in config/database.yml for development and test and removed production.
• I configured host: localhost and port: 5433 in config/database.yml.

Here is the content of my config/database.yml (removed comments for readability).

development:
  adapter: postgresql
  encoding: unicode
  database: my_test_app_development
  pool: 5
  username: johndoe
  password: password    
  host: localhost
  port: 5433

test:
  adapter: postgresql
  encoding: unicode
  database: my_test_app_test
  pool: 5
  username: johndoe
  password: password

---

Problem:
However, when I run bundle exec rake db:create:all I receive the following error message.

could not connect to server: No such file or directory
Is the server running locally and accepting connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?
[...]
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode",
"database"=>"my_test_app_test", "pool"=>5, "username"=>"johndoe",
"password"=>"password"}

---

Question:
Why is the port different to the one I use when I successfully connect via pgadmin3?

Solution 1

@Riateche: Finally, I saw that the database configuration for test environment misses the explicit settings for host and port. After I added the settings to the test environment, I was able to run the command bundle exec rake db:create:all successfully.
I must say, I do not like that they suggest those settings for the development enviroment, but did not add them for the other environments. That makes it very likely to miss them, as I proofed.

test:
  adapter: postgresql
  encoding: unicode
  database: my_test_app_test
  pool: 5
  username: johndoe
  password: password
  host: localhost
  port: 5433

Solution 2

If any psql client session is accessing template1 ( for example psql or pgAdmin ), rake db:migrate fails. Close any sessions before doing rake db:migrate .

Solution 3

you can change your postgresql configuration in pg_hba.conf to trust.

[...]
local   all             postgres                                peer
local   all             all                                     trust
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Comments

[shoujo_sm]

is the port number is same to all pc? if no,how can I know my port number?

[JJD]

@AmySukumunu You can configure the port in the configuration file which you can find here: /etc/postgresql/<VERSION>/main/postgresql.conf. More infos here. The default port is 5432 as far as I know.

[Andrei]

in my case I needed adding just host

[BC2]

Hi you guys are using Linux OS ? What if i'm doing my rails project in windows base ? where can i find that etc directory

[Katarzyna]

If you configure it through /etc/postgresql/<VERSION>/main/postgresql.conf you are not able to maintain multiple postresql databases in your computer.

[JJD]

@Katarzyna Where would I apply the configuration per database instead?

[Katarzyna]

You can find it in gist: gist.github.com/siwka/64c26f60dfe577a0fe2d5a384cbfe8fe