'Cannot connect to Postgres server running through brew services

I've been looking for a solution for this and could not find a working solution.

I've installed postgres using brew (brew install postgres) in my MacBook and I am currently running it using brew services (brew services list displays postgres as a running service). However, when I try to run psql I get following error.

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

Anyone has already solved similar problem?

macospostgresqlhomebrew


Solution 1:[1]

I had the same error and I fixed it by removing the process pid file:

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

Solution 2:[2]

I ran into this problem today. postgres stopped accepting connections though homebrew thought it was running.

To fix it I ran,

brew services restart -vvv postgresql

Output from this command,

==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
==> Generated plist for postgresql:
   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
   <plist version="1.0">
   <dict>
     <key>KeepAlive</key>
     <true/>
     <key>Label</key>
     <string>homebrew.mxcl.postgresql</string>
     <key>ProgramArguments</key>
     <array>
       <string>/usr/local/opt/postgresql/bin/postgres</string>
       <string>-D</string>
       <string>/usr/local/var/postgres</string>
     </array>
     <key>RunAtLoad</key>
     <true/>
     <key>WorkingDirectory</key>
     <string>/usr/local</string>
     <key>StandardErrorPath</key>
     <string>/usr/local/var/log/postgres.log</string>
   </dict>
   </plist>

Then I thought, hmm maybe there's something in that log file,

tail -n 10 /usr/local/var/log/postgres.log

Sure enough,

[4826] FATAL:  lock file "postmaster.pid" already exists
[4826] HINT:  Is another postmaster (PID 1489) running in data directory "/usr/local/var/postgres"?

So, I removed that file

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

And everything started working again.

Solution 3:[3]

In my case the postmaster.pid file wasn't even there. Got it working by upgrading postgres.

brew update
brew upgrade

Then, because I upgraded the major version from 10 to 11, I also had to run this:

brew postgresql-upgrade-database

(source https://github.com/facebook/react-native/issues/18760#issuecomment-410533581)

Solution 4:[4]

I would combine the two answers from Wilson and Grasshopper here.

You can check the plist file for the postgres service using brew services list to find the location of the file and just opening it up in you favourite editor.

You should see the value of StandardErrorPath listed as: 

<key>StandardErrorPath</key>
<string>/usr/local/var/log/postgres.log</string>

And then you should tail the end of the log file using tail -n 100 /usr/local/var/log/postgres.log

In my case the error was the following:

2017-12-06 11:51:16.078 GMT [85476] FATAL: lock file "postmaster.pid" already exists 2017-12-06 11:51:16.078 GMT [85476] HINT: Is another postmaster (PID 601) running in data directory "/usr/local/var/postgres"?

This was because I had to hard shutdown my Mac and postgres didn't get a chance to cleanup the PID file. Just remove the PID file rm /usr/local/var/postgres/postmaster.pid and start postgres brew services start postgresql

A word of warning: do not delete this PID file unless you are sure that postgres is not running. You can do this by running brew services stop postgresql and then waiting for the result of brew services list to show posgres is in the stopped state.

Solution 5:[5]

Problem - Postgres not working after macOS upgrade

I just upgraded to the latest macOS (Big Sur).

My node application that uses a local instance of postgresql as a database was unable to start due to this error: Error: connect ECONNREFUSED 127.0.0.1:5432

Clearly postgresql is no longer running properly on my machine.

Running brew services list shows an error for postgresql:

Name          Status  User  Plist    
postgresql    error   ***** /Users/*****/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Solution - Upgrade Postgres

To resolve the problem I upgraded postgresql with the following commands:

$ brew services stop postgresql
$ brew postgresql-upgrade-database # may need to `rm-rf /usr/local/var/postgres.old` first
$ brew services start postgresql

Solution 6:[6]

In case you have multiple versions of postgresql installed. Below mentioned commands will do the job of removing process id(pid) and restarting it. Just make sure you select the correct version.

rm -f /usr/local/var/[email protected]/postmaster.pid

brew services restart [email protected]

Please also refer to Eric Corner's answer to confirm if the issue is about "lock file postmaster.pid already exists" in log file.

Solution 7:[7]

What worked for me was removing the /usr/local/var/postgres/ folder and then uninstalling and installing postgres again

Solution 8:[8]

I got the same error installing postgresql93 from the versions tap. Inspecting the .plist file indicated in the output of brew services list(~/Library/LaunchAgents/homebrew.mxcl.postgresql93.plist) I found the following message:

FATAL: data directory "/usr/local/var/postgres" has group or world access
DETAIL: Permissions should be u=rwx (0700).

Which led me to this answer: data directory "/usr/local/var/postgres" has wrong ownership

After running sudo chmod -R 700 /usr/local/var/postgres I got a different error:

FATAL: could not open directory "pg_tblspc": No such file or directory

Which then led me to: `pg_tblspc` missing after installation of latest version of OS X (Yosemite or El Capitan)

After running mkdir /usr/local/var/postgres/pg_tblspc/ the cluster started successfully.

Solution 9:[9]

In my case after updating to MacOS Monterey it was need to make

$ brew postgresql-upgrade-database

as well.

Solution 10:[10]

In case all the other answers don't work...

I was seeing this same message and the accepted answer, running

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

didn't work. Neither did

rm -f /usr/local/var/[email protected]/postmaster.pid after I remembered I had a specific version installed.

I did a cat /usr/local/var/log/[email protected] and saw:

FATAL:  lock file "postmaster.pid" already exists
HINT:  Is another postmaster (PID 1231) running in data directory "/usr/local/var/[email protected]"?

> ps aux|grep 1231
[...] /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneOS.platform/Library/Developer/CoreSimulator/Profiles/Runtimes/iOS.simruntime/Contents/Resources/RuntimeRoot/usr/libexec/mobileassetd

**thinking face**

Closed the iOS Simulator. Postgres restarted on its own.

This is on a desktop and we had the power go out yesterday, so based on the other answers I suspect the root cause is the hard shutdown without giving things a chance to clean up.

Solution 11:[11]

If your postgres.log file contains "image not found error" then you may need to reinstall postgresql:

brew reinstall postgresql

Solution 12:[12]

update it by using command 

  brew postgresql-upgrade-database

if you have following error Command 'brew' not found, but can be installed with: sudo apt install linuxbrew-wrapper

then install it by using command 

 sudo apt install linuxbrew-wrapper

Solution 13:[13]

In my case (postgres installed by homebrew),

  • tailing the log to see what the error was tail -n 50 /usr/local/var/log/postgres.log

    2022-04-28 16:38:10.509 PDT [84751] FATAL: lock file "postmaster.pid" already exists 2022-04-28 16:38:10.509 PDT [84751] HINT: Is another postmaster (PID 969) running in data directory "/usr/local/var/postgres"?

  • kill the pid based on above kill -9 <pid>

  • restart the service via homebrew brew services restart postgresql && brew services list

Solution 14:[14]

In my own case, the postgres.log file contains this HINT: Is another postmaster already running on port 5432? If not, remove socket file "/tmp/.s.PGSQL.5432" and retry. After removing the socket file and restarting, everything worked fine. Note that this is a hidden file and won't be visible through the window browser. Removal should be done via the command line.

Solution 15:[15]

You might be seeing a conflict of libssl/libcrypto versions.

brew services start postgres logs gave me nada, but manually starting pg_ctl -D /usr/local/var/postgres start pointed at:

missing definition _RAND_cleanup in /.../openssl/lib/libcrypto.1.0.0.dylib

Have a shot at:

brew tap-new $USER/old-openssl
brew extract --version=1.0.2t openssl $USER/old-openssl
brew install [email protected]

# and then either brew link [email protected] or symlink the required files to current openssl
ln -s /usr/local/Cellar/openssl/[email protected]/lib/libcrypto.1.0.0.dylib /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib
ln -s /usr/local/Cellar/openssl/[email protected]/lib/libssl.1.0.0.dylib /usr/local/opt/openssl/lib/libssl.1.0.0.dylib

Solution 16:[16]

My problem was an open connection to my database.

Solution 17:[17]

In my case the problem was that I was attempting to start the brew service in an SSH session on a Mac, where my user was not currently logged in. Simply logging into the Mac, through the MacOS login GUI solved the issue.

A more detailed error message included the hint that the problem was gui-related:

Error: Failure while executing; `/bin/launchctl enable gui/501/homebrew.mxcl.postgresql` exited with 64.

Solution 18:[18]

If someone scrolls to the bottom after trying everything out and it's not working, this is what worked for me. I decided not to use brew for this eventually.

I installed Postgres app directly from the website following this link https://postgresapp.com, initialised a new server and created the databases and tables as usual.

I was also getting port occupied related errors and this answer https://stackoverflow.com/a/43825665 worked for me.

Related Questions