Postgres could not connect to server

Question

After I did brew update and brew upgrade, my postgres got some problem. I tried to uninstall postgres and install it again, but it didn't work as well.

This is the error message. (I also got this error message when I try to do rake db:migrate)

$ psql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

How can I solve it?

Mac version: Mountain lion.

homebrew version: 0.9.3

postgres version: psql (PostgreSQL) 9.2.1

And this is what I did:

$ brew uninstall postgresql
Uninstalling /usr/local/Cellar/postgresql/9.2.1...
$ brew uninstall postgresql
Uninstalling /usr/local/Cellar/postgresql/9.1.4...
$ psql --version
bash: /usr/local/bin/psql: No such file or directory
$ brew install postgresql
==> Downloading http://ftp.postgresql.org/pub/source/v9.2.1/postgresql-9.2.1.tar.bz2
Already downloaded: /Library/Caches/Homebrew/postgresql-9.2.1.tar.bz2
......
......
==> Summary
/usr/local/Cellar/postgresql/9.2.1: 2814 files, 38M, built in 2.7 minutes
$ initdb /usr/local/var/postgres -E utf8
The files belonging to this database system will be owned by user "laigary".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

initdb: directory "/usr/local/var/postgres" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/usr/local/var/postgres" or run initdb
with an argument other than "/usr/local/var/postgres".
$ mkdir -p ~/Library/LaunchAgents
$ cp /usr/local/Cellar/postgresql/9.2.1/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
$ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
homebrew.mxcl.postgresql: Already loaded
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
server starting
$ env ARCHFLAGS="-arch x86_64" gem install pg
Building native extensions.  This could take a while...
Successfully installed pg-0.14.1
1 gem installed
$ psql --version
psql (PostgreSQL) 9.2.1
$ psql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Now, after I reinstalled homebrew, when I use $ psql, it doesn't show any error message.

But I run rake db:migrate in my Rails app, it shows:

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"?

/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:1213:in `initialize'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:1213:in `new'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:1213:in `connect'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:329:in `initialize'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:28:in `new'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:28:in `postgresql_connection'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:309:in `new_connection'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:319:in `checkout_new_connection'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:241:in `block (2 levels) in checkout'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:236:in `loop'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:236:in `block in checkout'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:233:in `checkout'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:96:in `block in connection'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:95:in `connection'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:404:in `retrieve_connection'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_specification.rb:170:in `retrieve_connection'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_specification.rb:144:in `connection'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:107:in `rescue in create_database'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:51:in `create_database'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:40:in `block (3 levels) in <top (required)>'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:40:in `each'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:40:in `block (2 levels) in <top (required)>'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:205:in `call'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:205:in `block in execute'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:200:in `each'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:200:in `execute'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:158:in `block in invoke_with_call_chain'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:151:in `invoke_with_call_chain'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:144:in `invoke'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:116:in `invoke_task'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:94:in `block (2 levels) in top_level'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:94:in `each'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:94:in `block in top_level'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:133:in `standard_exception_handling'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:88:in `top_level'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:66:in `block in run'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:133:in `standard_exception_handling'
/usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:63:in `run'
/usr/local/bin/rake:32:in `<main>'
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"riy_development", "pool"=>5, "username"=>nil, "password"=>nil}

Finally I've found a solution.

sudo mkdir /var/pgsql_socket/
sudo ln -s /private/tmp/.s.PGSQL.5432 /var/pgsql_socket/

This solution is a little tricky, but it works. Hope anyone has a better solution

Update

This works for me as well.

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

Solution 1

Had a similar problem; a pid file was blocking postgres from starting up. To fix it:

$ rm /usr/local/var/postgres/postmaster.pid
$ brew services restart postgresql

and then all is well.

---

UPDATE:

For Apple M1 (Big Sur) users, do this instead:

$ rm /opt/homebrew/var/postgres/postmaster.pid
$ brew services restart postgresql

Solution 2

This sometimes happens when brew does a postgres upgrade, causing the data files to become incompatible with the new server.

In my case, it happened when upgrading from 9.3 to 9.4.

OS X/Homebrew:

Try running postgres -D /usr/local/var/postgres -- it will give you a much more verbose output if postgres fails to start. Or open the log file at /usr/local/var/log/postgres.log (/opt/homebrew/var/log/postgres.log on Mac M1) and look for the line "FATAL: database files are incompatible with server"

In my case, running rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8 removed my old databases and then reinitialized the postgres db schema. (THIS WILL DESTROY YOUR DATA)

Thanks to https://github.com/Homebrew/homebrew/issues/35240 for that solution. Full instructions for a thorough re-install can be found here: How to completely uninstall and reinstall Homebrew Postgres - Test Double Blog (Again, if you're on an M1 Mac then substitute the /opt/homebrew/var path wherever it says /usr/local/var)

After regenerating my databases (with rake db:create) everything worked fine again.

Finally, links in the comments point to this possible solution that preserves your data, but I haven't tried it: How to upgrade PostgreSQL from version 9.6 to version 10.1 without losing data - Stack Overflow

Solution 3

Found a solution that worked for me here:

https://dba.stackexchange.com/questions/75214/psql-could-not-connect-to-server-no-such-file-or-directory

You basically run the following command to manually start the server:

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

Solution 4

If installing and uninstalling postgres with brew doesn't work for you, look at the logs of your postgresql installation or:

postgres -D /usr/local/var/postgres

if you see this kind of output:

LOG:  skipping missing configuration file "/usr/local/var/postgres/postgresql.auto.conf"
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.4, which is not compatible with this version 9.6.1.

Then try the following:

rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8

Then start the server:

pg_ctl -D /usr/local/var/postgres -l logfile start

Source

Solution 5

Upgrading the database works for me

brew postgresql-upgrade-database

View more solutions

Comments

[ajbraus]

I am having your exact same problem, but your solution did not work for me. I think this is a permissions problem but I don't have the knowledge of file permissions to fix it.

[zquintana]

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 stackoverflow.com/a/19609228/1072058.

[VelLes]

similar answer here - stackoverflow.com/questions/13573204/…

[atw]

Start postgres.

[Admin]

Upgrade your existing to the newer version with the following command brew postgresql-upgrade-database

[SamB]

What worked for me: brew services stop postgres && initdb && createdb $(whoami) && createuser -s postgres && brew services start postgres

[Gary Lai]

This is my computer response. $ ls -l /tmp/.s.PGSQL.5432 ls: /tmp/.s.PGSQL.5432: No such file or directory $ grep unix_socket /usr/local/var/postgres/postgresql.conf #unix_socket_directory = '' # (change requires restart) #unix_socket_group = '' # (change requires restart) #unix_socket_permissions = 0777 # begin with 0 to use octal notation

[Gary Lai]

Finally I find the solution. $ mkdir /var/pgsql_socket/ $ sudo mkdir /var/pgsql_socket/ $ ln -s /private/tmp/.s.PGSQL.5432 /var/pgsql_socket/ This solution is little tricky, but it works. Hope anyone have a better solution

[jrwren]

nice job @GaryLai. Very strange that I did not have to do the same with my homebrew postgresql

[AdamT]

this may help some folks out there: github.com/mxcl/homebrew/issues/14527

[eprothro]

This worked for me on OSX Mavericks after seeing this issue upon a update/restart.

[VelLes]

Thanks, this worked for me too. My PostgreSQL from Homebrew broke after I did a minor Maverick update to 10.9.2, maybe it was just a metter of cleaning the old .pid file.

[Brett Bim]

Doing this changes the connection from a socket to a TCP connection which may have negative implications. See stackoverflow.com/questions/6770649/…

[David Winiecki]

Hmm, actually, if you downvote... Maybe don't say why? meta.stackoverflow.com/questions/252297/… Or maybe say why, but log out first so you're anonymous.

[betoharres]

Using Yosemite it didn't work. Still the same error and the file no longer exists. But it used to work on OSX versions for me though... any help? "could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?"

[jstafford]

@BetuUuUu see this post for a solution on Yosemite. It's not pretty, but it works. stackoverflow.com/questions/25970132/…

[betoharres]

@jstafford thanks, I already solved!:) The solution was to uninstall via homebew and then restart my computer right away, so then I would be able to reinstall postgres. The old version was running in the background, creating files and conflicts every time I tried to reinstall postgres.

[Brian Dear]

I think the down vote is because the original question wasn't for the Postgres.app but Homebrew. So while the answer might be useful somewhere, it isn't answering the question.

[cpursley]

This worked for me with a homebrew installed postgres on Yosemite.

[sscirrus]

This didn't work for me on Mavericks: rm: /usr/local/var/postgres/postmaster.pid: No such file or directory

[Ricardo Castañeda]

This worked for me, after erasing I had to start postgres. # pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

[Nathan Long]

If running postgres -D /usr/local/var/postgres tells you that the data directory was initialized with an older, incompatible version of Postgres, and you don't want to lose your local data, you might try this thread

[Josh Bodah]

I recommend tailing the server log to make sure this is actually the issue: tail /usr/local/var/postgres/server.log

[Kmeixner]

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

[Adolfo]

After Yosemite crashed and restarted, cannot connect to Postgres. your answer worked perfect. :)

[Brian Dear]

The question was specifically referring to a homebrew installation and not a Postgres.app install. While this answer is helpful, it doesn't answer the question. It would be like saying "Use MySQL instead."

[Admin]

After the above command was run, I ran: pg_ctl -D /usr/local/var/postgres -l logfile start and then my server started in the background

[Mike Andrianov]

Use brew services start postgres on OSX to start postgres on background instead of postgres -D /usr/local/var/postgres

[stujo]

Just a head's up that this deletes your databases, which may not be what you want (or you might not care if this is all dev all the time)

[ea0723]

Mac Yosemite 10.10.5 after battery died, rebooted, and xCode finished it's update - your answer still works beautifully ... thanks!

[Patrick_870206]

Out of all the answers this is the only one that worked for me. Using El Capitan

[Simon1901]

Worked for me. I lost all my local databases but it fix it so it works. This i fine by me since this has locally.

[coffekid]

FATAL: database files are incompatible with server was the reason. I don't know how to fix that yet, but at least I know what's going on. Thanks!

[svelandiag]

This does not work for mac users, check my answer below stackoverflow.com/a/42127625/3700798

[Abe Petrillo]

Thanks, this worked for me. Removing the postmaster.pid in the accepted answer wasn't actually enough.

[Jan Hettich]

These instructions are also at collectiveidea.com/blog/archives/2016/01/08/… , and still worked for me today when upgrading from 9.4.0 to 9.6.3. I didn't need to do anything to the .pid file manually.

[sixty4bit]

The link doesn't seem to go anywhere specific (and searching "localhost" on the past has no results) but this just fixed the issue for me after an hour of searching.

[zulkarnain shah]

I don't see that pid file . I am using PostGreSQL version 9.6

[tomascharad]

Remember to restart the service after deleting the .pid

[Charlie]

I just accidentally upgraded my postgresql from 9.5 to 10.1 and improperly shutdown the pc. For the record, here's the log: postgres -D /usr/local/var/postgres \n `2018-02-12 11:31:23.676 +08 [2526] FATAL: database files are incompatible with server 2018-02-12 11:31:23.676 +08 [2526] DETAIL: The data directory was initialized by PostgreSQL version 9.5, which is not compatible with this version 10.1.

[craig1410]

Just wanted to say thanks. I just upgraded PostgresApp on macOS and although I could connect using psql, I couldn't connect via rails. uninstalling and reinstalling the pg gem was just the ticket! :)

[Micah Walter]

@coffekid I had the same issue, and fixed it with the instructions here: stackoverflow.com/questions/24379373/…

[Jerome]

for those whose the command does not work, ensure first you can see invisible files, then check path /usr/local/var/ You may have multiple folders there for different versions of postgresql. Each could contain the postmaster.pid file that needs to be removed

[Chris Wendt]

This followed by brew services restart postgresql worked for me

[aronmoshe_m]

This worked for me after an accidental restart (battery died and I had to reboot). Thank you!

[Falk Schuetzenmeister]

That works when the reason is different from the accepted answer, i.e. the database files are not compatible with the current version. Since exactly that has been my problem, I upvote.

[Mark A]

I get connection refused

[hooknc]

Ok. Is your database running on your localhost?

[Mark A]

yes. Trying to configure basic connection with localhost

[hooknc]

Is your server running on port 5432?

[Mark A]

yes.. I checked and the different versions were 10 and 11. I had to run brew postgresql-upgrade-database..

[hooknc]

You might need to make sure that the security setting are set correctly in the pg_hba.conf file.

[Harshit Trivedi]

I have tried with that cmd but not working for me!!

[Nuha]

WOW! After trying everything, this rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8 finally worked for me. Thank you!

[Rob Bednark]

👍✅⭐️🙏Thank you Tomer! This worked for me 6/2/20 upgrading from 9.4 to 12 on macOS Mojave 10.14.6

[Yaseen Ahmad]

This is worked but i recommend if rm /usr/local/var/postgres/postmaster.pid on mac os not worked then use this command.

[Fábio BC Souza]

should be rm -rf ~/usr/local/var/postgres/postmaster.pid

[ruohola]

@FábioBCSouza No it should not!

[Fábio BC Souza]

@ruohola, without recursive force I could not make it work. Using rm -rf the pid was deleted

[Francisco Quintero]

After changing Ruby version, I didn't noticed but my Postgresql installation changed versions and only with this command could have it running again.

[MIA]

Some forget about the version, so they will get No such file or directory. They should be more specific: rm /usr/local/var/postgresql\@9.5/postmaster.pid then brew services restart [email protected]

[theKid]

This should be the accepted answer, works like a charm!

[Heimson]

Thanks ! For me I just needed to run the second command: 'brew services restart postgresql'

[Sachin]

this worked for me also. 2 days back I updated homebrew which caused my postgres but after updating postgressql , its working fine. Thanks again

[nayiaw]

God bless you. Other answers are not working for me and there's no way I could see a postmaster.pid. I have other error but thank god for hinting me on the log file to inspect :. /usr/local/var/log/postgres.log.

[Patrick]

If you installed a specific version of postgres using homebrew, the paths above might be different. If you run brew list | grep postgres and see postgresql@11 for example, then you would need to run $ rm /usr/local/var/postgresql@11/postmaster.pid $ brew services restart postgresql@11

[Gustavo Matias]

I just wanna say this is the worse errors I get every single time there's an OS update, Postgres update, computer restart or anything in between. It's incredible that I need to go seeking for the same solution over and over. Had to vent somewhere.

[Aqeeb Imtiaz Harun]

Thank you! Directly restarting for me didn't work, had to remove the pid file.

[David Aldridge]

As @MIA says, sometimes you need to be version specific. I needed rm /opt/homebrew/var/postgresql@12/postmaster.pid and postgresql@12 (macOS Big Sur on M1)

[Ivan Hanák]

Works on Catalina as well!

[PirateApp]

double this!!! on big sur i struggled to get this to run until i landed on your answer

[shah]

No such file or directory @ rb_sysopen - /usr/local/var/postgres/PG_VERSIO

[Salvador Nava]

Command rm -rf /usr/local/var/postgres throw me an error "folder doesn't exists" but then I ran initdb /usr/local/var/postgres it worked for me! Thanks

[Abdul Basit Mangat]

worked for me as well!

[Ansh]

Thank you, you saved my day

[Peter]

This worked on my mac, thanks!

[Zargold]

This seemed to work well for big sur. note that depending on your specific version of postgresql from brew, and OS --any of the solutions involving deleting files from specific paths may not have the right path. In my case even when I found the right path it still failed--but this worked.

[phoenix]

Worked for Postgres 13 -> 14 upgrade on macOS 10.15 in which other listed answers did not.

[Rick Westera]

Note that after this you may need to recreate the postgres role /usr/local/opt/postgres/bin/createuser -s postgres and your personal database psql -U postgres then create database with your role name.

[Fernando]

I just had to brew services restart postgresql

[Vikas Pal]

Thank you so much! 🥳🥳🥳 This worked for me 👍👍👍 I think brew automatically updated lot of packages when I installed some other package.

[Theodory]

This one worked for me in mac os big sur.

[Onur Karadeniz]

You saved my life!! :) Thank you! haha

[DaveR]

My postmaster.pid file was in /usr/local/var/postgresql@13/postmaster.pid rather than the directory above - so you might need to do find / -name postmaster.pid to get the right directory.

[Mike Williamson]

This was also my issue; my data directory was of an older version than the server, due to brew updating it. You can see if this is the problem by looking at the logs: postgres -D /usr/local/var/postgres. If you need to upgrade, you'll see something like, The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.2.

[Pshemski]

Thank you Tomer! This worked for me too, upgrading from 13 to 14 on Mac Monterey 12.3.1. Don't you love one line solutions? 😊

[Bradley]

Worked for me. nice one @saeed