Postgresql adapter (pg): could not connect to server

Question

I get this error every this I run my Rails app (It cannot connect to my local Postgresql)

/Users/leonardo/.rvm/gems/ruby-1.9.3-p362/gems/activerecord-3.2.11/lib/
active_record/connection_adapters/postgresql_adapter.rb:1208:in `initialize': 
could not connect to server: No such file or directory (PG::Error)
   Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

I'm using Postgres.app that it's correctly running.

If I run

$ psql

I can login properly to Postgresql console.

$ which psql
 /Applications/Postgres.app/Contents/MacOS/bin/psql

Gemfile

source 'https://rubygems.org'
ruby "1.9.3"

gem 'rails', '3.2.11'
gem "pg"

database.yml

development:
  adapter: postgresql
  encoding: unicode
  username: leonardo
  password: 
  database: zapping
  port: 5432  

Postgresql (Console)

$ psql
leonardo=# \l

Hettiehetty

can you try add host: localhost to your database.yml ? (based on that answer : https://mcmap.net/q/48938/-postgres-permission-denied-on-mac-os-x) – Unblinking

If you add it in "answers" I will marked as accepted – Hettiehetty

Answer 1

Try adding host: localhost to your database.yml. (Based on: https://mcmap.net/q/48938/-postgres-permission-denied-on-mac-os-x)

Unblinking

Answer 2

Your Pg gem was compiled against the PostgreSQL libpq pre-installed in Mac OS X and you're using the psql that you installed in a newer version, or vice versa.

This can be worked around by specifying a TCP/IP connection, by adding localhost to database.yml, but it's better to compile the Pg gem against the libpq for the server you're actually running. To do that, you should be able to set the PATH environment variable to the folder with the correct pg_config in it before compiling. In your case that'll be somewhere within Postgres.app.

Ensor

Craig, can you please elaborate this one? Where and which env. variable should I set exactly? – Ronna

Thanks, this answer helped me fix it. The steps I took were simple: 1) gem uninstall pg, 2) bundle install, done. – Monahon

Perfect answer, and although updating database.yml works too, I think this is probably the best and most correct solution for this problem. If you've, say, installed Postgresql via Homebrew after installing pg, you might see this error. Then follow @haslo's instructions above. – Patriotism

Answer 3

If it does not work even after adding host: localhost, remove the postmaster.pid

rm /usr/local/var/postgres/postmaster.pid

Obtund

This solved the problem that I had. Even though running brew services start postgresql said it was successful, running ps x | grep postgres showed no server running. I followed the steps here (which include removing the postmaster.pid) and it solved the problem for me: coderwall.com/p/zf-fww/… – Gennygeno

This works if your computer crashes and you do a forced shutdown but postgres wasn't properly terminated, sometimes the pid file will get stuck there... removing it helps get it going again. – Prompter

No no no do NOT do this! It's only OK if you verify that no postgres processes are still running. It should rarely if ever be necessary; it can only arise if some other process happens to get assigned the pid the postmaster had. – Ensor

Should be okay for local postgres connections. – Obtund

This was my problem, I don't understand the reply above from @CraigRinger what is the harm in this? It was immediately rebuilt for me by the service when it finally started. – Eyra

@DavidGreco postmaster.pid serves as an interlock that stops one postgres from starting while another is still writing to the database file. If you remove it, you might have two (or more) postgres processes writing to the same database without coordinating with each other, resulting in massive database corruption. So do not do it unless you have made absolutely sure no other postgres processes are surviving and accessing the same data directory. – Ensor

Answer 4

you should add host: localhost to your db config...

Statesmanship

Im not sure why i get downvoted, I answered 5 minutes before! the accepted answer (which has the same solution like my post)? – Statesmanship

Thanks. In my case, I had to add host: db. – Leibowitz

Answer 5

I had this issue. One of the comments here helped me fix the issue.

Thanks, this answer helped me fix it. The steps I took were simple: 1) gem uninstall pg, 2) bundle install, done. – haslo Dec 3 '13 at 20:27

gem uninstall pg
bundle install

Pseudohemophilia

This fixed it for me. I had uninstalled/reinstall postgres and the gem needed to be rebuilt. – Chutzpah

Answer 6

I had the same problem on a Mac. Turns out that the psql I had on my path wasn't working correctly. Try launching psql by typing:

~/projects/some_project/project-rails$ psql
  psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

If you see this, it indicates the psql binary on your path is attempting to connect using socket and unable to do so (for whatever reason). Since I had already downloaded pgadmin and it was connecting fine, I knew it wasn't an issue with the server.

Fixed it by adding the right version of pgsql to my PATH:

export PATH=/Applications/Postgres.app/Contents/MacOS/bin:$PATH

Now psql (and rails) are happy!

Perretta

re-installation of the pg gem were also required – Nagel

Answer 7

I had this same issue. You have to actually run / start postgres. Postgres must have stopped running on my computer recently, so I had to make sure it's running by starting the postgres server

postgres -D /usr/local/var/postgres

Then the following commands (which were causing me the same error you had) all worked:

bundle exec rake db:create db:migrate
bundle exec rspec

Risinger

Answer 8

Since This was the first post that appeared in my search result, I have decided to post the updated fix for this. Since none of the above suggestion worked for me.

brew postgresql-upgrade-database

It upgraded postgresql data and moved my old version. I was on 9.6 instead of 10.4

Found the solution here

Aldehyde

Answer 9

find / -name 'postgresql.conf'

netstat -an | grep 5432 # => /tmp/.s.PGSQL.5432

vi /Users/admin/Library/Application\ Support/Postgres93/var/postgresql.conf

FROM: unix_socket_directories = '/tmp'

TO: unix_socket_directories = '/var/pgsql_socket'

sudo mkdir /var/pgsql_socket

sudo chmod 777 /var/pgsql_socket

Slugabed

This solution, ultimately in my case, would have fixed my issue with Rails not being able to start. I went a little different approach on the way to realizing this. I actually created a symlink as described in This Post – Adalia

Answer 10

For heroku this is all you need.

heroku addons:create heroku-postgresql

production:
 adapter: postgresql
 encoding: unicode
 host: localhost
 # For details on connection pooling, see rails configuration guide
 # http://guides.rubyonrails.org/configuring.html#database-pooling
 pool: 5

Maryrose

Answer 11

I just had the problem that the postgres app wasn't running on my mac...

Shih

Answer 12

On Mac, I had different versions of postgresql. This problem has been solved when I tried brew switch postgresql 9.5.5 (I don't think version number matters at this point)

$ brew switch postgresql 9.5.5
Cleaning /usr/local/Cellar/postgresql/9.6.1
Cleaning /usr/local/Cellar/postgresql/9.6.5
Cleaning /usr/local/Cellar/postgresql/10.2
Cleaning /usr/local/Cellar/postgresql/10.3
Cleaning /usr/local/Cellar/postgresql/9.5.5
Cleaning /usr/local/Cellar/postgresql/9.5.4

All data will be gone away, by the way. I guess there's a brew command for doing this without switching version, which I couldn't find.

Epigram

Answer 13

I had the same problem on OS High sierra 10.13 I followed the instructions from this website I downloaded version 10.4 ref: https://postgresapp.com/

Then I added this to bash profile: export PATH=$PATH:/Library/PostgreSQL/10.4/bin:$PATH

re start terminal.

Open new window terminal and then type: psql -U postgres

after that you will see this message: psql (10.4) Type "help" for help.

postgres=#

Hadj

Answer 14

My problem was that the /etc/hosts file changed and didn't have this crucial entry: 127.0.0.1 localhost

Toulon

Answer 15

I had a similar issue - when I ran psql command in terminal I still got the same error, so that told me that it wasn't a problem with the pg gem and I should check the postgres setup itself. So I checked the postgres logs - in my case they were located at /usr/local/var/log/postgres.log and I saw the following error:

dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.66.dylib
  Referenced from: /usr/local/opt/postgresql/bin/postgres

So knowing the exact error helped me resolve the issue, in my case based on this answer I ran

brew switch icu4c 66.1

which resolved the issue after restarting postgres:

brew services restart postgresql

Luxor

Answer 16

I'm having a problem connecting if I set the hostname to localhost or 127.0.0.1. The PostgreSQL client library seems to treat those as special cases, looking for the UNIX socket instead of the TCP port.

I was able to solve my problem by setting the hostname to localhost.. The trailing period makes it not hit the special case, and makes it work for me.

Okoka

Adding this because I've run into it twice myself, and I couldn't remember the original answer the second time. I couldn't find any other pertinent question to attach it to. – Okoka