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?

Rails app tries to connect through UNIX socket instead of TCP socket localhost:5433. It mustn't behave so if you setup database.yml properly. Check contents of database.yml and ensure that rails does read this file. Please provide contents of database.yml. You also can try to use different adapter (sqlite, for example) to test if your settings applies. — Pavel Strakhov, Apr 21 '12 at 23:18
Does that mean I should not use port 5433 but 5432 which was in *database.yml* as a uncommented line? — JJD, Apr 21 '12 at 23:59
For are those who install PG via Homebrew and are having problems, I found another answer. Just uninstall pg gem and reinstall with Homebrew configs. See the answer at http://stackoverflow.com/a/19609228/1072058. — zquintana, Oct 26 '13 at 16:58

JJD · Accepted Answer · 2012-04-22T00:02:51.850

20

@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

edited Apr 22 '12 at 00:02

answered Apr 21 '12 at 23:57

JJD

50,076
60
203
339

is the port number is same to all pc? if no,how can I know my port number? – shoujo_sm May 07 '12 at 12:50
2

@AmySukumunu You can configure the **port** in the configuration file which you can find here: `/etc/postgresql//main/postgresql.conf`. More infos [here](http://ubuntumanual.org/posts/505/how-to-install-postgresql-on-ubuntu). The default port is 5432 as far as I know. – JJD May 08 '12 at 21:51
in my case I needed adding just `host` – Andrei Aug 10 '12 at 07:09
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 – BC2 Nov 06 '13 at 16:05
If you configure it through ```/etc/postgresql//main/postgresql.conf``` you are not able to maintain multiple postresql databases in your computer. – Katarzyna Apr 18 '18 at 23:43
@Katarzyna Where would I apply the configuration **per database** instead? – JJD Apr 19 '18 at 16:01
1

You can find it in gist: https://gist.github.com/siwka/64c26f60dfe577a0fe2d5a384cbfe8fe – Katarzyna Apr 22 '18 at 04:47

score 1 · Answer 2 · edited Nov 07 '13 at 09:49

1

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 .

edited Nov 07 '13 at 09:49

JJD

50,076
60
203
339

answered Aug 22 '12 at 18:01

Anish

1,490
1
13
13

score 1 · Answer 3 · answered Apr 21 '16 at 04:44

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

score 0 · Answer 4 · answered Nov 20 '20 at 21:32

0

I had the same issue and I solve it by running this solution

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

answered Nov 20 '20 at 21:32

nourza

2,215
2
16
42

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

4 Answers4

Linked