Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory

Not really sure what caused this but most likely exiting the terminal while my rails server which was connected to PostgreSQL database was closed (not a good practice I know but lesson learned!)

I've already tried the following:

Below was the workaround if you prefer to stay in v13.3, if you are on 14.2 you can simply change the port postgresql is listening to. Read their docs about it here .

Note that this solution works for the setup that I have (take a look at the original post for my setup details ie. OS & how what I'd use to install Postgres)

vscode

sudo code . /usr/local/var/postgres/postgresql.conf
sudo vi /usr/local/var/postgres/postgresql.conf
change port to 5432 and restart your machine.
v13.3 solution
it appears that the upgrade really messed up my postgres since per Nagev's answer it's listening to port 5433 instead of 5432. I downgraded to v13.3 to fix this issue.
brew uninstall postgresql
brew install postgresql@13
brew services start postgresql@13
brew link postgresql@13 --force
                To check if the port is not 5432 you can: brew services info --all -> find the PID for postgres in the output -> then lsof -P -sTCP:LISTEN -i TCP -a -p <THE PID>.  This will give you output which at the end should indicate the port, e.g. localhost:5433 (LISTEN)
– tgf
                Mar 6 at 2:26
if you are using macOS and not M1
rm /usr/local/var/postgres/postmaster.pid
brew services restart postgresql
detail
try brew info postgresql get execute command
try /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres
error: FATAL:  lock file "postmaster.pid" already exists
goto /usr/local/var/postgres directory, delete postmaster.pid file
restart, works
pgrep -l postgres
If it doesn't give you any result then let's try starting it using brew services.
brew services start postgresql
pgrep -l postgres
Still not running? Check the logs
tail /usr/local/var/log/postgres.log
You may see an error about a file named postmaster.pid:
FATAL:  lock file "postmaster.pid" already exists
This indicates that Postgres had not been shut down cleanly, for any number of reasons. Remove the stale PID lock then start the server again
rm /usr/local/var/postgres/postmaster.pid
brew services start postgresql
pgrep -l postgres
If you install the Postgress via home-brew then first create the postgres user.
/opt/homebrew/Cellar/postgresql@14/14.7/bin/createuser -s postgres
Now you can log into it via following command:
psql -U postgres
If you use MacOS Homebrew, follow the instructions at https://wiki.postgresql.org/wiki/Homebrew
In short, try
psql postgres
For Mac with M1 use this:
brew services stop postgresql
rm /opt/homebrew/var/postgresql@14/postmaster.pid
brew services start postgresql
In my case I received this issue after upgrading my Mac (pg 14.2 was the version).  I uninstalled it and reinstalled it a few times but it wouldn't start.  Even when it said it started, it didn't which was confusing.  I ended up needing to run the command below to get everything to work again.
brew postgresql-upgrade-database
Specifically I ran
brew install postgresql
brew postgresql-upgrade-database
It seems to me that the DB needed to be upgraded before it could run, once I did that it all came back.
                Looks like it is 14.2 now, I thought is was 13 when I started this but it must have updated with all this.
– Rockwell Rice
                Apr 4, 2022 at 15:46
                I tried doing the same but I'm getting "Error: postgresql data already upgraded!", on 14.2 as well.
– Lea Tinoso
                Apr 22, 2022 at 0:43
                This is the right answer if you have some old databases before your new postgres installation.
– qichao_he
                Jul 30, 2022 at 3:44
Resetting PostgreSQL
My original answer only included the troubleshooting steps below, and a workaround. I now decided to properly fix it via brute force by removing all clusters and reinstalling, since I didn't have any data there to keep. It was something along these lines, on my Ubuntu 21.04 system:
sudo pg_dropcluster --stop 12 main
sudo pg_dropcluster --stop 14 main
sudo apt remove postgresql-14
sudo apt purge postgresql*
sudo apt install postgresql-14
Now I have:
$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
14  main    5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
And sudo -u postgres psql works fine. The service was started automatically but it can be done manually with sudo systemctl start postgresql.
Incidentally, I can recommend the PostgreSQL docker image, which eliminates the need to bother with a local installation.
Troubleshooting
Although I cannot provide an answer to your specific problem, I thought I'd share my troubleshooting steps, hoping that it might be of some help. It seems that you are on Mac, whereas I am running Ubuntu 21.04, so expect things to be different.
This is a client connection problem, as noted by section 19.3.2 in the docs.
The directory in my error message is different:
$ sudo su postgres -c "psql"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
I checked what unix sockets I had in that directory:
$ ls -lah /var/run/postgresql/
total 8.0K
drwxrwsr-x  4 postgres postgres  160 Oct 29 16:40 .
drwxr-xr-x 36 root     root     1.1K Oct 29 14:08 ..
drwxr-s---  2 postgres postgres   40 Oct 29 14:33 12-main.pg_stat_tmp
drwxr-s---  2 postgres postgres  120 Oct 29 16:59 14-main.pg_stat_tmp
-rw-r--r--  1 postgres postgres    6 Oct 29 16:36 14-main.pid
srwxrwxrwx  1 postgres postgres    0 Oct 29 16:36 .s.PGSQL.5433
-rw-------  1 postgres postgres   70 Oct 29 16:36 .s.PGSQL.5433.lock
Makes sense, there is a socket for 5433 not 5432. I confirmed this by running:
$ pg_lsclusters
Ver Cluster Port Status                Owner    Data directory              Log file
12  main    5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
14  main    5433 online                postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
This explains how it got into this mess on my system. The default port is 5432, but after I upgraded from version 12 to 14, the server was setup to listen to 5433, presumably because it considered 5432 as already taken. Two alternatives here, get the server to listen on 5432 which is the client's default, or get the client to use 5433.
Let's try it by changing the client's parameters:
$ sudo su postgres -c "psql --port=5433"
psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
Type "help" for help.
postgres=#
It worked! Now, to make it permanent I'm supposed to put this setting on a psqlrc or ~/.psqlrc file. The thin documentation on this (under "Files") was not helpful to me as I was not sure on the syntax and my attempts did not change the client's default, so I moved on.
To change the server I looked for the postgresql.conf mentioned in the documentation but could not find the file. I did however see /var/lib/postgresql/14/main/postgresql.auto.conf so I created it on the same directory with the content:
port = 5432
Restarted the server:  sudo systemctl restart postgresql
But the error persisted because, as the logs confirmed, the port did not change:
$ tail /var/log/postgresql/postgresql-14-main.log
2021-10-29 16:36:12.195 UTC [25236] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-10-29 16:36:12.198 UTC [25236] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2021-10-29 16:36:12.204 UTC [25237] LOG:  database system was shut down at 2021-10-29 16:36:12 UTC
2021-10-29 16:36:12.210 UTC [25236] LOG:  database system is ready to accept connections
After other attempts did not succeed, I eventually decided to use a workaround: to redirect the client's requests on 5432 to 5433:
ln -s /var/run/postgresql/.s.PGSQL.5433 /var/run/postgresql/.s.PGSQL.5432
This is what I have now:
$ ls -lah /var/run/postgresql/
total 8.0K
drwxrwsr-x  4 postgres postgres  160 Oct 29 16:40 .
drwxr-xr-x 36 root     root     1.1K Oct 29 14:08 ..
drwxr-s---  2 postgres postgres   40 Oct 29 14:33 12-main.pg_stat_tmp
drwxr-s---  2 postgres postgres  120 Oct 29 16:59 14-main.pg_stat_tmp
-rw-r--r--  1 postgres postgres    6 Oct 29 16:36 14-main.pid
lrwxrwxrwx  1 postgres postgres   33 Oct 29 16:40 .s.PGSQL.5432 -> /var/run/postgresql/.s.PGSQL.5433
srwxrwxrwx  1 postgres postgres    0 Oct 29 16:36 .s.PGSQL.5433
-rw-------  1 postgres postgres   70 Oct 29 16:36 .s.PGSQL.5433.lock
This means I can now just run psql without having to explicitly set the port to 5433. Now, this is a hack and I would not recommend it. But in my development system I am happy with it for now, because I don't have more time to spend on this. This is why I shared the steps and the links so that you can find a proper solution for your case.
The top answer on this post is what solved the issue for me. Basically run createdb in your terminal. This will create database for your logged in user. Then run psql -h localhost and you will be in!
Not saying this is the solve for all issues, but it solved mine.
Worked on Mac OSX M2 and installing postgresql@15 using brew,
Installation steps that worked for me:
brew doctor
brew cleanup
brew update
brew install postgresql@15
Check installation path in the logs of this command  brew install postgresql@15, it might look like /opt/homebrew/opt/postgresql@15/bin
Add to environment:
vim ~/.zshrc
Append this line to the end of the file
export PATH=$PATH:/opt/homebrew/opt/postgresql@15/bin
Check this command, brew services start postgresql@15 (Showed already started) and verify it's status, sometimes it will say its running but in the below command, it will show error.
brew services list
If status is running
 Name          Status  User  File
postgresql@15 started username ~/Library/LaunchAgents/homebrew.mxcl.postgresql@15.plist
brew link postgresql@15 --force
cd /opt/homebrew/var/postgresql@15
sudo vi postgresql.conf 
Either uncomment or add in postgresql.conf
port = 5432                             # (change requires restart)
(From: https://stackoverflow.com/a/71792068/7276869)
rm /usr/local/var/postgres/postmaster.pid
brew services restart postgresql
Try to start:
psql -d postgres -U username
postgres=# \l
(You have entered postgres shell!)
If you use lunchy with homebrew, resolving this may be as simple as running three commands:
# Instead of uninstalling it, simply stop Postgres
lunchy stop postgres
# Remove the PID file that was left behind from improper shutdown
rm /usr/local/var/postgres/postmaster.pid
# Restart Postgres
lunchy start postgres
Most likely the issue is you have multiple clusters running so first check what the clusters are running by command:
pg_lsclusters
Then stop the unused cluster by command:
sudo pg_dropcluster --stop 11 main
sudo pg_dropcluster --stop 12 main
It could be that your pg gem compiled to an old or different version of postgresql. Reinstalling the pg gem fixed it for me...
gem uninstall pg
bundle install
For me, I previously brew unlink postgresql@14 && brew link libpq --force
to get around this.
So I have to reverse it by brew unlink libpq && brew link postgresql@14 .
I figure this out, bc I got same error in this post when I execute initdb  (use locate to find the one used by current postgres installation)
$ /usr/local/Cellar/postgresql@14/14.6/bin/initdb --locale=C -E UTF-8 /usr/local/var/postgres
The files belonging to this database system will be owned by user "XXX".
This user must also own the server process.
initdb: error: file "/usr/local/share/postgresql@14/postgres.bki" does not exist
This normally indicates that the server is not running on the default socket.
You should check that postgresql server is active. On Ubuntu you can run
service postgresql status
psql is command-line client used to interact with the database engine. If you do not have any use you should invoke it as a postgres user which is the database administrator. This can be achieved with sudo:
sudo -u postgres psql
In my case, I restored files onto a recovered and upgraded laptop. On Big Sur using postgres version 14, my postgres data directory had been
/usr/local/var/postgres
After brew installing postgres@14, it was looking and failing to find the data directory at
/usr/local/var/postgresql@14
The command brew services restart postgres@14 confidently informed me that Postgres was running, but psql -l still returned the error in the post title.
What solved this for me was super simple:
cd /usr/local/var
ln -s postgres postgresql@14
Posting here in case this saves anyone the same trouble I had.
I had this error on an M1 Macbook Pro Ventura 13.0
I probably got this error when I accidentally closed my vscode or pgAdmin application while the server ran.
My error:
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:
Here is how I fixed it:
Check if postgres is running in the background using the pgrep command: pgrep -l postgres
Kill the running processes in the background using: sudo pkill -u postgres
Make sure that all the postgres processes are killed by repeating steps 1-2 until no processes are shown.
Run the psql command again.
            redprogrammer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.        
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.