Cannot connect to Postgres server running through brew services

Question

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?

Both the question and the answers here are very helpful.

Jendorski Labs
– Jendorski Labs

2023-12-09 15:49:58 +00:00
Commented Dec 9, 2023 at 15:49 — Jendorski Labs
– Jendorski Labs, Commented Dec 9, 2023 at 15:49

Abel O'Ryan · Accepted Answer · 2024-02-19 19:44:19Z

550

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

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

or for a specific version:

rm -f /usr/local/var/postgresql@16/postmaster.pid

[Updated Answer For Arm-based Chips (Apple M1)]

When you use brew to install postgresql on Apple M1 computers, the postmaster.pid will be located in: /opt/homebrew/var/postgresql/postmaster.pid

Follow following three steps:

# 1. Stop PostgreSQL 
brew services stop postgresql@16

# 2. Delete the postmaster.pid
rm -f /opt/homebrew/var/postgresql@16/postmaster.pid

# 3. Start the PostgreSQL again
brew services start postgresql@16

After the above, you can also check the status of the service by brew services info postgresql

brew services info postgresql@16
postgresql@16 (homebrew.mxcl.postgresql)
Running: ✔
Loaded: ✔
Schedulable: ✘
User: root
PID: 34884

edited Feb 19, 2024 at 19:44

Abel O'Ryan

4,27236 silver badges34 bronze badges

answered Jan 23, 2017 at 10:45

Wilson Silva

11.6k6 gold badges29 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

rap-2-h Over a year ago

Thanks, it works! But why? How? Do I have run this command after every restart?

Wilson Silva Over a year ago

@rap-2-h It seems that Postgres isn't being shut down properly. I get this problem when I have to shutdown my Mac by holding the power button.

Vjatseslav Gedrovits Over a year ago

If you use keg of specific version (example: [email protected]) make sure to use correct folder instead of "/postgres/"

edan Over a year ago

If this doesn't work, try tail /usr/local/var/log/postgres.log. This may give you more information about exactly why postgres is failing. You can usually post the error message from that log in google, and find another fix on StackOverflow :-D

Gerry Shaw Over a year ago

For Apple Silicone Macs use rm /opt/homebrew/var/postgres/postmaster.pid as the brew folder is by default in a /opt/homebrew instead of /usr/local.

|

Eric Conner · Accepted Answer · 2020-08-19 20:34:31Z

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.

The most useful command for me was tail -n 10 /usr/local/var/log/postgres.log
Very helpful step-by-step @Eric Conner! I repeated the approach, and it led me to a different root cause in my case: 2023-09-26 17:41:41.047 EDT [6620] FATAL: data directory "/opt/homebrew/var/postgresql@15" has invalid permissions 2023-09-26 17:41:41.047 EDT [6620] DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750). I did sudo chmod -R 0700 /opt/homebrew/var/postgresql@15 and resolved the issue.

Nelu · Accepted Answer · 2020-04-30 22:03:30Z

63

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)

edited Apr 30, 2020 at 22:03

answered Feb 1, 2019 at 3:15

Nelu

19k10 gold badges88 silver badges94 bronze badges

3 Comments

dijonkitchen Over a year ago

In these cases, the /usr/local/var/log/postgres.log file may have this as the last few logs: FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 11, which is not compatible with this version 12.2.

FooF Over a year ago

I got this exact problem. The upgrade from postgresql 11 to 12 failed, but uninstalling and installing again helped through.

Alex Over a year ago

brew postgresql-upgrade-database is what I was looking for

real_ate · Accepted Answer · 2017-12-06 11:56:44Z

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.

Vijay Shaaruck · Accepted Answer · 2022-07-07 05:58:50Z

17

I had the same error when I forced the restart of my mac.

When I did:

brew services list

I got this error:

postgresql        error  256 ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Steps followed to resolve this issue:

Open the terminal and type the following command.

cd /opt/homebrew/var/postgres
rm postmaster.pid

answered Jul 7, 2022 at 5:58

Vijay Shaaruck

7028 silver badges5 bronze badges

Comments

Derek Soike · Accepted Answer · 2021-02-19 18:16:41Z

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

this did the trick brew postgresql-upgrade-database thanks
I was able to remove the postgres.old directory by using rm -r /usr/local/var/postgres.old

Dave · Accepted Answer · 2020-12-07 06:20:51Z

10

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.

edited Dec 7, 2020 at 6:20

answered Jul 21, 2020 at 17:14

Dave

4,4363 gold badges27 silver badges38 bronze badges

1 Comment

risingStark Over a year ago

This is the one that worked for me. I was trying endlessly for 2hr+ but it was saying no such file found. Then I looked at this and specified the version as well. And voila! it worked

António Ferreira · Accepted Answer · 2018-12-14 10:30:48Z

7

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

answered Dec 14, 2018 at 10:30

António Ferreira

1112 silver badges3 bronze badges

2 Comments

Jean Over a year ago

You should try other responses before doing this.

Sean Coley Over a year ago

Will this not delete the entire database?

Denis Popov · Accepted Answer · 2021-11-11 08:24:52Z

7

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

$ brew postgresql-upgrade-database

as well.

answered Nov 11, 2021 at 8:24

Denis Popov

711 silver badge4 bronze badges

1 Comment

Joe Over a year ago

Crazy, but that worked.

Community · Accepted Answer · 2017-05-23 12:18:19Z

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.

Caio Abe · Accepted Answer · 2023-10-24 17:48:22Z

If using homebrew on macos, you can check the logs:

# change @15 by the version you have installed
# run this if you want to check what you have
# `brew list | grep postgres`
tail -n 100 /opt/homebrew/var/log/[email protected]

In my case I saw errors like this:

2023-10-24 14:43:01.793 -03 [54662] FATAL:  lock file "postmaster.pid" already exists
2023-10-24 14:43:01.793 -03 [54662] HINT:  Is another postmaster (PID 10344) running in data directory "/opt/homebrew/var/postgresql@15"?

Removing the lock did the trick: rm /opt/homebrew/var/postgresql@15/postmaster.pid

Then running psql to assert it works: psql -d postgres

Depending on your macOS version this could be another possible location for your postmaster.pid /usr/local/var/postgresql@12/postmaster.pid

Allen · Accepted Answer · 2022-04-28 23:44:34Z

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

Ilya Kushlianski · Accepted Answer · 2024-07-13 00:48:52Z

4

I installed postgresql@14 with brew but the DB won't start.

brew services info postgresql showed

Running: ✘
Loaded: ✔
Schedulable: ✘

Turned out a database data folder was missing. So I ran the following:

initdb postgres-data

in the folder where I wanted my data to reside.

Then

pg_ctl -D postgres-data -l logfile start

I was then able to connect to the DB on localhost:5432.

answered Jul 13, 2024 at 0:48

Ilya Kushlianski

96811 silver badges17 bronze badges

Comments

Nadeem Qasmi · Accepted Answer · 2019-10-09 18:09:33Z

2

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

answered Oct 9, 2019 at 18:09

Nadeem Qasmi

2,49724 silver badges17 bronze badges

Comments

apb · Accepted Answer · 2021-01-07 18:38:39Z

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.

voxobscuro · Accepted Answer · 2021-06-08 05:53:19Z

1

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

brew reinstall postgresql

edited Jun 8, 2021 at 5:53

voxobscuro

2,1891 gold badge22 silver badges46 bronze badges

answered Jun 7, 2021 at 6:38

Imran

752 bronze badges

Comments

Yashika Sharma · Accepted Answer · 2022-03-17 12:48:28Z

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.

dazonic · Accepted Answer · 2023-03-15 11:59:44Z

1

For me the issue was a non-versioned Postgres directory after I assume a version 14 dot upgrade.

brew info postgresql

gives a warning about the non-versioned directory and says to run:

brew services stop postgresql
mv -v "/opt/homebrew/var/postgres" "/opt/homebrew/var/postgresql@14"

answered Mar 15, 2023 at 11:59

dazonic

1,26113 silver badges12 bronze badges

Comments

The RealDuke · Accepted Answer · 2019-12-31 19:34:32Z

0

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.

answered Dec 31, 2019 at 19:34

The RealDuke

31 silver badge6 bronze badges

Comments

Asdrubal · Accepted Answer · 2021-08-06 19:51:01Z

0

My problem was an open connection to my database.

answered Aug 6, 2021 at 19:51

Asdrubal

3585 silver badges10 bronze badges

Comments

fkotsian · Accepted Answer · 2020-11-29 04:11:44Z

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

postrational · Accepted Answer · 2021-12-05 21:46:43Z

-1

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.

answered Dec 5, 2021 at 21:46

postrational

6,3763 gold badges25 silver badges27 bronze badges

Comments

Hubix vx · Accepted Answer · 2023-08-18 14:05:54Z

-1

My problem was that another postgres was running in the background I don't know why, you can check it with the command: sudo lsof -i -P | grep LISTEN | grep :5432 and then kill this process if you don't need it

answered Aug 18, 2023 at 14:05

Hubix vx

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

mutoe · Accepted Answer · 2023-08-21 03:02:43Z

-1

I didn't find the correct log location in the answer above. Probably because the brew installation path of the M1 chip is different from inter.

My log is located in /opt/homebrew/var/log/postgresql/postgresql.log

answered Aug 21, 2023 at 3:02

mutoe

5827 silver badges16 bronze badges

Collectives™ on Stack Overflow

Cannot connect to Postgres server running through brew services

24 Answers 24

16 Comments

5 Comments

3 Comments

Comments

Comments

2 Comments

1 Comment

2 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

24 Answers 24

16 Comments

5 Comments

3 Comments

Comments

Comments

2 Comments

1 Comment

2 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Linked

Related