psql: could not connect to server: No such file or directory (Mac OS X)

Question

Upon restarting my Mac I got the dreaded Postgres error:

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

The reason this happened is because my macbook froze completely due to an unrelated issue and I had to do a hard reboot using the power button. After rebooting I couldn't start Postgres because of this error.

Mercenary

As a sanity check, make sure you have PG running on your machine – Maduro

When you leave the answer please try to explain WHY this is happening, or how the Postgres system work so we're not all just trying random stuff. – Chukker

brew reinstall postgresql https://mcmap.net/q/48518/-library-not-loaded-usr-local-opt-readline-lib-libreadline-6-2-dylib worked for me – Rubious

brew reinstall postgresql this solved my issue – Berrios

Answer 1

WARNING: If you delete postmaster.pid without making sure there are really no postgres processes running you, could permanently corrupt your database. (PostgreSQL should delete it automatically if the postmaster has exited.).

SOLUTION: This fixed the issue--I deleted this file, and then everything worked!

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

--

and here is how I figured out why this needed to be deleted.

1. I used the following command to see if there were any PG processes running. for me there were none, I couldn't even start the PG server:

   ps auxw | grep post
   

2. I searched for the file .s.PGSQL.5432 that was in the error message above. i used the following command:

   sudo find / -name .s.PGSQL.5432 -ls
   

this didn't show anything after searching my whole computer so the file didn't exist, but obviously psql "wanted it to" or "thought it was there".

3. I took a look at my server logs and saw the following error:

   cat /usr/local/var/postgres/server.log
   

at the end of the server log I see the following error:

    FATAL:  pre-existing shared memory block (key 5432001, ID 65538) is still in use
    HINT:  If you're sure there are no old server processes still running, remove the shared memory block or just delete the file "postmaster.pid".

4. Following the advice in the error message, I deleted the postmaster.pid file in the same directory as server.log. This resolved the issue and I was able to restart.

So, it seems that my macbook freezing and being hard-rebooted caused Postgres to think that it's processes were still running even after reboot. Deleting this file resolved. Lots of people have similar issues but most the answers had to do with file permissions, whereas in my case things were different.

Mercenary

hopefully this answer ends up helping someone else out, and thank you to the person that upvoted it. despite the moderator who marked this question as a duplicate it is unique and useful. thanks stack overflow for the awesome site and the experts on here for sharing! – Mercenary

Just FWIW, deleting postmaster.pid is dangerous, you've got to be really sure there are no postgres processes running before you do it. – Capacitate

Great answer. Thanks for posting your entire process rather than just the potentially destructive command! – Nephogram

Agreed. The answer about postmaster.pid is good, but each step of the process of figuring out if/where postgres is running, is invaluable. Thank you! – Farrah

My postgres log on macos was at /usr/local/var/log/postgres.log. – Tempura

Before deleting anything, try brew reinstall postgresql. That fixed it for me. – Rubious

Answer 2

None of the above worked for me. I had to reinstall Postgres the following way :

• Uninstall postgresql with brew : brew uninstall postgresql

• brew doctor (fix whatever is here)

• brew cleanup

• Remove all Postgres folders :

• rm -r /usr/local/var/postgres

• rm -r ~/Library/Application\ Support/Postgres

• Reinstall postgresql with brew : brew install postgresql

• Start server : brew services start postgresql

• You should now have to create your databases... (createdb)

Engedi

Worked for me, yet I did not have to remove an actual database and didn't have a folder in Application Support. I think the point was that some brew elements had broken symlinks while installing postgres. Fixing them and installing postgres again solved the issue. Thanks! – Saarinen

Neither this nor top answer worked for me, until I also ran brew postgresql-upgrade-database – Stair

These worked for me, except I didn't have to delete the old data. – Maitland

To confirm: this deletes all your databases, correct? I was able to get by with just a brew reinstall postgres, confirming that the latest Homebrew Postgres major version number was the same as my current (broken) one. – Siloxane

@JacobFord are you saying that brew reinstall postgresql won't wipe your dbs? – Palocz

Thanks! it is Old but gold! – Jule

The above provided solution worked on MacOS Monterey 12.1 – Snorter

Only change I added was also uninstalling libpq after uninstalling postgres: brew uninstall libpq – Belittle

Answer 3

If you're on macOS and installed postgres via homebrew, try restarting it with

brew services restart postgresql

If you're on Ubuntu, you can restart it with either one of these commands

sudo service postgresql restart

sudo /etc/init.d/postgresql restart

Teresetereshkova

Answer 4

Maybe this is unrelated but a similar error appears when you upgrade postgres to a major version using brew; using brew info postgresql found out this that helped:

To migrate existing data from a previous major version of PostgreSQL run:
brew postgresql-upgrade-database

Critic

This worked for me on going from 11 to 12 on Catalina – Venipuncture

Worked for me as well – Retributive

Answer 5

Here is my way:

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
rm /usr/local/var/postgres/postmaster.pid
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Lingcod

This worked for me when no other stopping and starting/restarting/reinstalling of Postgres using homebrew was done. Thanks @smartworld-dm – Cattish

This is the soultion I always come back to. I might add a alias that when I run fix-my-postgresql it will execute this :) – Sop

Any idea why I have to do this every time I reboot my PC? – Cardew

Similar but different :) ... brew services stop postgresql; rm /usr/local/var/postgres/postmaster.pid; brew services start postgresql – Bisayas

Answer 6

The following commands helped me out. The issue was with the PostgreSQL data version. Once upgraded, it started working fine for me.

brew upgrade postgresql
brew postgresql-upgrade-database
brew services restart postgresql

Brittle

Surprised !!! I just install postgresql on my MAC OS than on connect, connection fail. postgresql not running in background when I start as service, but after these commands. All works perfectly. – Austroasiatic

^^^ this sequence did the trick for me, working solution for macOS as of June 2021. Thanks for documenting @satendra-rawat! If this doesn't work, it could potentially be a stale PID and then I'd try @smartworld-dm solution above – Stonecrop

Just brew upgrade postgresql@VERSION did it for me, VERSION=11, etc. – Disbranch

Answer 7

if your postmaster.pid is gone and you can't restart or anything, do this:

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

as explained here initially

Radioactive

I worked for me! After deleting postmaster.pid and restarting postgres it was not created. I run this command, the file was recreated and the I can connect to DB again. Thank you! – Unstoppable

Answer 8

For me, the solution was simply restart my computer. I first tried restarting with Brew services and when that didn't work, restarting seemed like the next best option to try before looking into some of the more involved solutions. Everything worked as it should after.

Hendrix

Answer 9

Another class of reasons why this can happen is due to Postgres version updates.

You can confirm this is a problem by looking at the postgres logs:

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

and seeing entries like:

DETAIL:  The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 13.0.

In this case (assuming you are on Mac and using brew), just run:

brew postgresql-upgrade-database

(Oddly, it failed on run 1 and worked on run 2, so try it twice before giving up)

Sauncho

👍🏻 This worked for me and I had to run it twice as well - first time it failed with the original error: connection to database failed: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/usr/local/var/log/.s.PGSQL.50432"? but running it a second time worked ¯\＿(ツ)＿/¯ – Thereupon

Answer 10

Hello world :)
The best but strange way for me was to do next things.

1) Download postgres93.app or other version. Add this app into /Applications/ folder.

2) Add a row (command) into the file .bash_profile (which is in my home directory):

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

It's a PATH to psql from Postgres93.app. The row (command) runs every time console is started.

3) Launch Postgres93.app from /Applications/ folder. It starts a local server (port is "5432" and host is "localhost").

4) After all of this manipulations I was glad to run $ createuser -SRDP user_name and other commands and to see that it worked! Postgres93.app can be made to run every time your system starts.

5) Also if you wanna see your databases graphically you should install PG Commander.app. It's good way to see your postgres DB as pretty data-tables

Of, course, it's helpful only for local server. I will be glad if this instructions help others who has faced with this problem.

Gressorial

The key here is adding Postgres to your path (step 2). That helped me. If you installed Postgres using Homebrew, you can add it to your path by adding the following line to your .bash_profile: export PATH='/usr/local/Cellar/postgresql/9.4.1/bin/':$PATH – Keep in mind your version number may be different. – Ambrogio

Answer 11

This problema has many sources, and thus many answers. I've experienced each one of them.

1) If you have a crash of some sort, removing the /usr/local/var/postgres/postmaster.pid file is probably required as postgres may not have handled it properly. But ensure that no process is running.

2) Craig Ringer has pointed out in other posts that Apple's bundling of postgreSQL leads to pg gem installation issues Setting the PATH environment variable is a solution.

3) Another solution, is to uninstall and reinstall the gem. A brew update may be necessary as well.

If you stumble upon this post, if you can pinpoint one of the sources, you'll save time...

Staphylorrhaphy

I'm glad I posted this. Another crash & the answer can be retreived rather quickly... – Staphylorrhaphy

Answer 12

I was facing a similar issue here I solved this issue as below.

Actually the postgres process is dead, to see the status of postgres run the following command

sudo /etc/init.d/postgres status

It will says the process is dead`just start the process

sudo /etc/init.d/postgres start

Teresetereshkova

Thankyou for the answer, your answer helped me solve the same issue with some modifications: sudo /etc/init.d/postgresql start – Bigham

Answer 13

This happened to me after my Mac (High Sierra) froze and I had to manually restart it (press and hold the power button). All I had to do to fix it was do a clean restart.

Atelier

Answer 14

I had the same issue. Most of the times, the problem is the fact that there's a leftover file

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

which works for most people, but my case was different - I tried googling this issue for last 3 hours, uninstalled postresql on OSX through brew, purged the database, nothing worked.

Finally, I noticed that I had an issue with brew that whenever I tried to install anything, it popped:

Error: Permission denied @ rb_sysopen - /private/tmp/github_api_....

or something like it at the end of an install.

I simply did sudo chmod -R 777 /private/tmp and it finally works!

I'm writing this down because this might be a solution for someone else

Dulciedulcify

Answer 15

I faced the same problem for psql (PostgreSQL) 9.6.11.

what worked for me -

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

restart postgres -- brew services restart [email protected]

If postmaster.pid doesn't exist or the above process doesn't work then run --

sudo chmod 700 /usr/local/var/[email protected]

Jam

This solution helped me with my regular issue with postgresql@11. I face this often issue whenever my laptop gets discharged and forced to restart. – Mariann

Answer 16

For those running into this issue on M1 macs, try deleting this file and then restarting the brew service:

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

Rosaceous

in my case (Intel Mac OS Catalina) this file was here: /usr/local/var/postgresql@12/postmaster.pid – Lantana

Answer 17

My problem ended up being that I was using Gas Mask (a hosts file manager for Mac), and I didn't have an entry for localhost in the hosts file I was using.

I added:

127.0.0.1 localhost

And that resolved my problem.

Backing

Answer 18

I'm not entirely sure why, but my Postgres installation got a little bit screwed and some files were deleted resulting in the error OP is showing.

Despite the fact that I am able to run commands like brew service retart postgres and see the proper messages, this error persisted.

I went through the postgres documentation and found that my file /usr/local/var/postgres was totally empty. So I ran the following:

initdb /usr/local/var/postgres

It seems some configurations took place with that command.

Then it asked me to run this:

postgres -D /usr/local/var/postgres

And that told me a postmaster.pid file already exists.

I just needed to know if brew would be able to pick up the configs I just ran, so I tested it out.

ls /usr/local/var/postgres

That showed me a postmaster.pid file. I then did brew services stop postgresql, and the postmaster.pid file disappeared. Then I did brew services start postgresql, and VIOLA, the file reappeared.

Then I went ahead and ran my app, which did in fact find the server, however my databases seem to be gone.

Although I know that they may not be gone at all - the new initialization I did may have created a new data_area, and the old one isn't being pointed to. I'd have to look at where that's at and point it back over or just create my databases again.

Hope this helps! Reading the postgres docs helped me a lot. I hate reading answers that are like "Paste this in it works!" because I don't know what the hell is happening and why.

Chukker

Answer 19

I had the same issue and it was due to an incompatible version after upgrading from version 11 to 13.2

Checking error log at:

/usr/local/var/log/postgres.log

Showed me:

DETAIL:  The data directory was initialized by PostgreSQL version 11, which is not compatible with this version 13.2.

To fix I ran:

brew postgresql-upgrade-database

And then started postresql with brew:

brew services start postgresql

Leeanneleeboard

Thanks for pointing me towards the log files. Discovered that there was a lock on the database and this other SO answer helped me. https://mcmap.net/q/48521/-fatal-error-lock-file-quot-postmaster-pid-quot-already-exists – Carper

Answer 20

The causes of this error are many so first locate your log file and check it for clues. It might be at /usr/local/var/log/postgres.log or /usr/local/var/postgres/server.log or possibly elsewhere. If you installed with Homebrew you can find the location in ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist.

Kathrynkathryne

Answer 21

I had a similar error.

All of this is done from the command line (no sudo calls at all)

1. I verified I had PostgreSQL installed psql -V (note that's a capital "V")
2. I attempted to connect to the server: psql postgres

THIS IS WHERE I EXPERIENCED THE ERROR OF THIS STACK OVERFLOW QUESTION

After doing some research about possible fixes, I obviously had PostgreSQL installed, but I didn't have a default server in place.

What I had to do was create a Custom Data Directory

As far as I can tell, creating the custom data directory is the same as having a default server in place.

Since this is a new machine (MacBook Pro 2021 using apple m1 chip), I wanted to find the easiest solution possible, and I believe this Custom Data Directory is just that. The remaining steps to fix this issue are as follows:

3. From the home directory, I created an empty directory mkdir myData
4. From the home directory, Initialized a server: initdb myData (throws a bunch of files into the myData directory)
5. pg_ctl -D myData -l logfile start (starts the server)
6. psql postgres (connects to the server)

So, as someone fairly new to PostgreSQL and databases and SQL in general, couple notes:

• It is possible to "quit" the connection to the server, using \q (while connected to the server, it's also possible to type "help")
• It is also possible to "stop" the server, as well with pg_ctl -D myData stop

At this point I now am certain I have PostgreSQL installed, have a server I can start and stop, and have the ability to connect to/disconnect from, that server.

Tarnopol

Answer 22

I had this same concern when connecting trying to start a PostgreSQL database server on MacOS Monterey.

When I run the command below to restart the PostgreSQL database server:

brew services restart PostgreSQL

It restarts but when I try to check the status of the PostgreSQL database server using the command below, I get an error:

Name       Status     User           File
mysql      started    promisepreston ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
nginx      started    promisepreston ~/Library/LaunchAgents/homebrew.mxcl.nginx.plist
postgresql error  256 root           ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Here's what worked for me:

First, I checked the log file for the PostgreSQL database server to what was the cause of the error using the command below:

cat /usr/local/var/log/postgres.log

OR

nano /usr/local/var/log/postgres.log 

The logs showed the following errors:

"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise.  See the documentation for
more information on how to properly start the server.
2022-01-25 19:01:06.051 WAT [29053] FATAL:  database files are incompatible with server
2022-01-25 19:01:06.051 WAT [29053] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.

For the root execution error I had to run the following command to fix file permissions that when messed us when I ran brew services with the sudo command prefix. Replace your-username with your MacOS username (in my case my username was promisepreston:

# Stop postgresql
sudo brew services stop PostgreSQL

# In case service file is copied to ~/Library/LaunchAgents as well
brew services stop postgresql

# Fix permission of homebrew files
sudo chown -R your-username:admin $(brew --prefix)/*

For the database files are incompatible with server I had to simply upgrade the existing PostgreSQL data files which were created using version 13 to the latest PostgreSQL version on my computer which was 14.1 by running the following command below:

brew postgresql-upgrade-database

Afterwhich, I restarted the PostgreSQL database server:

brew services restart PostgreSQL

And then checked the status using the command below:

brew services list

Then I got the output below showing that everything was working fine:

Name          Status  User           File
mysql         started promisepreston ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
nginx         started promisepreston ~/Library/LaunchAgents/homebrew.mxcl.nginx.plist
postgresql    started promisepreston ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

References: Brew PostgreSQL Starts But Process Is Not Running

Diabetes

Answer 23

Go to /var/log/ and run cat postgres.log Here you will find the reason for the failure of postgres. If it is a smart shut down then probably your icu4c version (C++ library for Unicode) is not proper which is linked with postgres. So run the following commands.

brew upgrade brew cleanup This should work ;)

Carl

May have to run brew style afterwards if brew cleanup breaks Homebrew. – Southwestward

Answer 24

I've had to look up this post several times to solve this issue. There's another fix, which will also applies to similar issues with other running programs.

I just discovered you can use the following two commands.

$top

This will show all the currently running actions with their pid numbers. When you find postgres and the associated pid

$kill pid_number

Caye

Similar to ps aux | grep \[p\]ostgres | awk '{ print $2 }' to return the pid, or kill $(ps aux | grep \[p\]ostgres | awk '{ print $2 }') (on zsh [] must be escaped as \[\], but not on bash) – Republicanize

Answer 25

I just got the same issue as I have put my machine(ubuntu) for update and got below error:

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

After completing the updating process when I restart my system error gone. And its work like charm as before.. I guess this was happened as pg was updating and another process started.

Munition

Answer 26

SUPER NEWBIE ALERT: I'm just learning web development and the particular tutorial I was following mentioned I have to install Postgres but didn't actually mention I have to run it as well... Once I opened the Postgres application everything was fine and dandy.

Devinna

Answer 27

@Jagdish Barabari's answer gave me the clue I needed to resolve this. Turns out there were two versions of postgresql installed while only one was running. Purging all postgresql files and reinstalling the latest version resolved this issue for me.

Soliz

Answer 28

I removed /usr/lib from the LD_LIBRARY_PATH and it worked. I was working in dockerfile postgres:alpine.

Duo

Answer 29

This answer worked for me: https://mcmap.net/q/48523/-postgresql-could-not-connect-to-server-no-such-file-or-directory on my ubuntu system.

Command: sudo service postgresql restart.

Janik

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Slub