Psql could not connect to server: No such file or directory, 5432 error?

Question

I'm trying to run psql on my Vagrant machine, but I get this 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"?

Note: Vagrant 1.9.2 Box: ubuntu/trusty64, https://atlas.hashicorp.com/ubuntu/boxes/trusty64

EDIT Commands I've used in order to install and run postgres:

• sudo apt-get update
• sudo apt-get install postgresql
• sudo su postgres
• psql -d postgres -U postgres

Crankpin

This was the answer that worked for me: askubuntu.com/a/824325/733901 – Cence

Answer 1

I've had this same issue, related to the configuration of my pg_hba.conf file (located in /etc/postgresql/9.6/main). Please note that 9.6 is the postgresql version I am using.

The error itself is related to a misconfiguration of postgresql, which causes the server to crash before it starts.

I would suggest following these instructions:

1. Certify that postgresql service is running, using sudo service postgresql start
2. Run pg_lsclusters from your terminal

3. Check what is the cluster you are running, the output should be something like:

   Version - Cluster Port Status Owner Data directory

   9.6 ------- main -- 5432 online postgres /var/lib/postgresql/9.6/main

   Disregard the '---' signs, as they are being used there only for alignment. The important information are the version and the cluster. You can also check whether the server is running or not in the status column.

4. Copy the info from the version and the cluster, and use like so: pg_ctlcluster <version> <cluster> start, so in my case, using version 9.6 and cluster 'main', it would be pg_ctlcluster 9.6 main start
5. If something is wrong, then postgresql will generate a log, that can be accessed on /var/log/postgresql/postgresql-<version>-main.log, so in my case, the full command would be sudo nano /var/log/postgresql/postgresql-9.6-main.log.
6. The output should show what is the error.

   2017-07-13 16:53:04 BRT [32176-1] LOG: invalid authentication method "all"
   2017-07-13 16:53:04 BRT [32176-2] CONTEXT: line 90 of configuration file "/etc/postgresql/9.5/main/pg_hba.conf"
   2017-07-13 16:53:04 BRT [32176-3] FATAL: could not load pg_hba.conf

7. Fix the errors and restart postgresql service through sudo service postgresql restart and it should be fine.

I have searched a lot to find this, credit goes to this post.

Best of luck!

Porphyritic

Excellent guide. Full of comprehensive explanations ! – Winson

For step 4, I had to do sudo systemctl start [email protected] instead of pg_ctlcluster 9.5 main start. Thank you for your awesome explanation! – Gendarmerie

on mine, I can not run this: "pg_ctlcluster 10 main start" instead this work for me: "/usr/lib/postgresql/10/bin/pg_ctl restart -D /var/lib/postgresql/10/main" of course you should point to your own postgre version and path. I forgot where did I get this solution, but it's on my terminal's history. May be someone need it. thank you for this answer, this lead me to the right direction. – Maybe

Running pg_ctlcluster as the postgres user gave me the error message I needed directly. Better than sudo pg_ctlcluster for me. But I am running Postgres 12 or 13. – Luann

Answer 2

I had the same issue but non of the answers here helped.

How I fixed it (mac)

• Try to start postgresql with pg_ctl -D /usr/local/var/postgres start
• Look for the Error Message that says something like FATAL: could not open directory "pg_tblspc": No such file or directory.
• Create that missing directory mkdir /usr/local/var/postgres/pg_tblspc
• Repeat from step one until you created all missing directories
• When done and then trying to start postgresql again it might say FATAL: lock file "postmaster.pid" already exists
• Delete postmaster.pid: rm /usr/local/var/postgres/postmaster.pid
• Start postgres with: pg_ctl -D /usr/local/var/postgres start
• Done ✨

Lunarian

Answer 3

These two steps solved it for me on Mac:

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

For M1 Macs:

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

In case you face this issue (reported by @luckyguy73): psql: FATAL: database "postgresql" does not exist

You can run

brew postgresql-upgrade-database

to fix it.

Alate

thanks man, i went from a can't connect to a fatal by following your instructions: "psql: FATAL: database "postgresql" does not exist" – Dunning

Huh weird, this always works for me. Did you manage to solve it? – Alate

no worries, it actually turned out ok. i just ran 'brew postgresql-upgrade-database' and that did the trick, thanks – Dunning

Glad you fixed it! If you don't mind I'll add that to the answer too, in case someone else faces the same issue. Thanks – Alate

After a power outage, I always have this problem on my dev Mac. Removing the .pid file always worked for me, in my case it's: rm /usr/local/var/[email protected]/postmaster.pid – Forestry

This was it for me - how to recover after a crash. – Protection

I've had the same problem as mentioned @balazs630. After a power outage, I had to rm /usr/local/var/[email protected]/postmaster.pid. Thanks a million @balaz630! – Melly

Answer 4

I am just posting this for anyone who is feeling lost and hopeless as I did when I found this question. It seems that sometimes by editing some psotgresql-related config files, one can accidentally change the permissions of the file:

Note how pg_hba.conf belongs to root, and users cannot even read it. This causes postgres to not be able to open this file and therefore not be able to start the server, throwing the error seen in the original question.

By running

sudo chmod +r pg_hba.conf

I was able to make this file once again accessible to the postgres user and then after running

sudo service postgresql start

Was able to get the server running again.

Bootstrap

Yes, this was my problem, and this was not pointed out in logs (or if it was I didn't understood as it). – Merwyn

Not exactly what happened to me, but for some reason NETWORK SERVICE lost write privileges to the entire directory structure. This answer pointed me in the right direction hence the upvote. – Liquidity

Answer 5

WARNING: This will remove the database

Use command:

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

Weston

Doesn't this remove entire database? I think that a word of warning would be in order. – Joerg

Thanks @Tucker Watts and @Gaurav Verma. I solved my problem by trying both solutions and added this command pg_ctl -D /usr/local/var/postgres -l logfile start. – Trevor

Answer 6

WARNING: This will remove the database

Within zsh:

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

This is the only thing that worked for me after countless hours trouble shooting.

Boeschen

Answer 7

This works for me:

pg_ctl -D /usr/local/var/[email protected] stop;
brew services stop [email protected];
brew services start [email protected];

Pursuer

Answer 8

Does the /etc/postgresql/9.6/main/postgresql.conf show that port being assigned? On my default Xubuntu Linux install, mine showed port = 5433 for some reason as best as I can remember, but I did comment out the line in that same file that said listen_addresses = 'localhost' and uncommented the line listen_addresses = '*'. So maybe start and check there. Hope that helps.

Lovellalovelock

Answer 9

I was able to solve the issue by running:

sudo systemctl start [email protected]

Launceston

sudo systemctl start postgresql@12-main worked for me – Parhelion

Answer 10

In my case Postgres was managed through Homebrew Services (i.e. started via brew services start postgresql@10 Terminal command for the Postgres 10 that I use), and for that setup I had to discover a couple of essential steps to do before I could apply any advice in this thread. So I want to share just that piece as it may help someone who has the same setup.

NOTE: all the commands below are to be run in Terminal.

To give a quick background: After upgrading to macOS Big Sur I discovered that Postgres wasn't working and running psql results in the error mentioned in the original question above. I tried to start Postgres (via the brew services start postgresql@10 command), this resulted in a message Service postgresql@10 already started. If I tried to restart it (via the brew services restart postgresql@10) I got a message that it was stopped and then started successfully. But! This was a misleading message, and I spent quite some time searching for config issues etc. before discovering that the service was not started successfully in reality.

So, the way to investigate this is:

1. Make sure the service is started by running the brew services start postgresql@10 (the latter argument may be different depending on what your Homebrew package name is e.g. postgresql@12 or plain postgresql).
2. Run brew services list. This is the command that gives you the true state of the service. In my case it said that Postgres' status is error:

Name Status User Plist postgresql@10 error Denis /Users/Denis/Library/LaunchAgents/[email protected] redis started Denis /Users/Denis/Library/LaunchAgents/homebrew.mxcl.redis.plist

3. To investigate further open the config shown in the same command output in Plist column (I used nano /Users/Denis/Library/LaunchAgents/[email protected] to check it).
4. In the config look for the StandardErrorPath key, and open the file located in the value of that key, i.e. in the <string> tag following the key. In my case it was /usr/local/var/log/[email protected].
5. Open that log and check the latest error (I used nano /usr/local/var/log/[email protected] and then Alt+/ to go to the end of the file).
6. Voila. That is the real error to investigate, which you can then look for in the previous answers or google for. I'm not covering the rest here, as the goal of this answer is to show how to find the real error if you use Homebrew Services to run Postgres. (In my case it was the lock file "postmaster.pid" already exists already covered in the previous answers, plus the path to check right in the error message, in my case /usr/local/var/postgresql@10).

Omphalos

Answer 11

I couldn't connect using the psql command and kept getting the error Cannot connect to Server: No such file or directory.

Step 1: Check the status of the Postgres cluster

$ pg_lsclusters

Step 2: Restart the Postgres cluster

$ sudo pg_ctlcluster 12 main start

Make sure to replace 12 with your version of Postgres

Step 3: Check again and connect

$ pg_lsclusters

$ sudo -i -u postgres

$ psql

Prescience

Answer 12

In my case it was the lockfile postmaster.id that was not deleted properly during the last system crash that caused the issue. Deleting it with sudo rm /usr/local/var/postgres/postmaster.pid and restarting Postgres solved the problem.

Damages

This comment was so helpful to tracking the issue down. Thank you so much. – Karmakarmadharaya

Answer 13

just reinstall your pgsql with direct version sudo apt-get install postgresql-9.5 (u must remove the package before install new one)

Heathheathberry

Answer 14

I recommend you should clarify port that postgres. In my case I didn't know which port postgres was running on.

lsof -i | grep 'post'

then you can know which port is listening.

psql -U postgres -p "port_in_use"

with port option, might be answer. you can use psql.

Guanine

Answer 15

I occasionally have the same issue but mostly after macOS upgrades. Shutting down and migrating to the new version usually fixes it for me(make changes according to your version). So first upgrade your postgresql

brew services stop postgresql@12
brew services start postgresql@12
brew postgresql-upgrade-database

This is mostly a temporary fix but since I couldn't find a better solution this works for me.

Update: If the issue says that another postmaster is running then try removing it from that location(your postmaster.pid location will be displayed to you)

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

Secondclass

brew postgresql-upgrade-database is what finally worked for me after i tried everything else – Dunning

hmn. this works for me as well although I don't have any db installed from the previous version. weird. – Have

Answer 16

If non of the above answers are not working for you, then please try this one,

Many people have mentioned many solutions to this problem! But all of them forgot that, the same problem will arise when your disk don't have enough space or the space you are assigned for postgres is full

Check your system storage, if its full free up some space! then restart your postgres by sudo service postgresql restart or do a stop and start sudo service posgresql stop then sudo service postgresql start

This will solve the issue, it solved for me

Reins

Answer 17

Ubuntu 20

This Problem happened to me, as ubuntu pre-installed version of Postgresql-9.6 server was always down and after trying all the above answers it didn't start.

Solution:

1. I installed another version of Postgresql which is postgresql-13, using this command: sudo apt install postgresql it will install the latest version of postgresql.

2. I see if the server is online or down using this command: pg_lsclusters if the new version of postgresql is online, we will proceed to remove the old version of postgresql.

3. we will see all packages that are installed related to postgresql, using this command: dpkg -l | grep postgresql

4. Remove the old version, which is here postgresql-9.6. Using this command: sudo apt-get --purge remove postgresql-9.6 postgresql-client-9.6 replace 9.6 with your old version number. Final remaining packages related to the latest Version 13:

5. Restart your postgresql latest version server, which is here postgresql-13. Using this command: sudo systemctl restart postgresql@13-main replace 13 in the command with your latest version number.

6. Now, if you try psql command you will get an error related to your user, as in the image:

7. To Remove the above error, The installation procedure created a user account called postgres that is associated with the default Postgres role, to switch over to the postgres account use this command: sudo -u postgres psql this command will log you into the interactive Postgres session. You can also set your password for this user using this command \password postgres.

8. Then change the Port to the deafult port of postgresql, which is 5432 as all application will try to connect to postgresql using this port by default, using this command: sudo nano /etc/postgresql/13/main/postgresql.conf, it will open postgresql configuration file, then search for port and change it to 5432. After that you need to restart the server using this command sudo systemctl restart postgresql@13-main. Note, Replace 13 in the command with your latest version.

If you want to create your own User/Role, use this command: sudo -u postgres createuser --interactive. The script will prompt you with some choices, as in the image and based on your responses, it will execute the correct Postgres commands to create a user to your specifications.

Tutorial: For more information on postgresql related commands

Physoclistous

Failed to restart [email protected]: Unit [email protected] not found – Wicket

@Wicket you may removed something that's related to postgres-12, please remove all things that have -oldVersionNumber in it only. I updated my answer and put an image in step 4 to the available packages after you remove the old packages related to old postgresql installation. – Physoclistous

I honestly didn't need any of this except your last step. As well as making sure the config files were owned by postgres chmod postgres:postgres on the config files. Normal service restart and everything worked. – Wicket

@Wicket if the answer solved a part from your problem, please upvote the answer. – Physoclistous

Answer 18

Open your database manager and execute this script

update pg_database set datallowconn = 'true' where datname = 'your_database_name';

Antedate

Answer 19

I had the same error when I create the SQL db in a VM. I had changed the default value of /etc/postgresql/9.3/main/postgresql.conf shared_buffers = 200MB to 75% of my total RAM. Well, I forgot to actually allocate that RAM in the VM. When I gave the command to make a new database, I received the same error.

Powered off, gave the baby its bottle (RAM) and presto, it worked.

Kob

Answer 20

The same thing happened to me as I had changed something in the /etc/hosts file. After changing it back to 127.0.0.1 localhost it worked for me.

Scepter

Answer 21

I got this error when I restored my database from last pg_basebackup backup file. After that when I tried to connect database(psql), I was getting the same error. The error was resolved, when I updated pg_hba.conf file and wherever "peer" authentication was there I replaced that with "md5" and then restarted postgres services. After that, the problem was resolved.

Flagrant

Answer 22

This error happened to me after my mac mini got un-plugged (so forced shutdown), and all I had to do to fix it was restart

Avesta

Answer 23

I have the same issue with postgres 11 on my mac. I get this error every time after restart

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

As a temporary fix I do

brew services stop postgresql@11
brew services start postgresql@11

Symposiarch

Answer 24

My problem happened after a brew update so I've ran

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

and I've got this result:

FATAL: database files are incompatible with server 2021-07-07 13:27:21.692 CEST [70896] DETAIL: The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 13.2. stopped waiting

I've ran

brew postgresql-upgrade-database

Inviolate

Answer 25

FATAL:  could not load server certificate file "/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory
LOG:  database system is shut down
pg_ctl: could not start server

I have a missing ssl-cert-snakeoil.pem file so i created it using make-ssl-cert generate-default-snakeoil --force-overwrite And it worked fine.

Natch

Answer 26

In my case, I had to run journalctl -xe, and it showed that my disk was full. I then deleted some .gz items from /var/log and I could again restart the postgresql.

Sher

Answer 27

I'm on Kali Linux. I had to remove the brew version of postgresql with

brew uninstall postgresql

sudo -u postgres psql got me into root postgres

Elsi

Answer 28

Simply running these commands from the installation steps in the official PostgreSQL docs worked for me (I'm on Fedora 33):

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13

RHEL Installation link

Endogamy

Answer 29

kali users pls do this

sudo service postgresql restart

Caravaggio

Please add further details to expand on your answer, such as working code or documentation citations. – Mccreery

your postgresql service is off, use the command to start or restart the service port – Caravaggio

Answer 30

Faced the same issue.

I wasn't able to use psql also, as the service was down.
pg_lsclusters showed the cluster is down and I couldn't start it with pg_ctlcluster <version> <cluster_name> start, because "postgres was the owner".

Solved my issue with sudo -i -u postgres service postgresql start
(Ubuntu 20.04)

Lole

Answer 31

I had a similar issue on Ubuntu 14.04 Postgres 9.4. Where when I try to start the Postgres service it was showing the below error

* Starting PostgreSQL 9.4 database server
* Failed to issue method call: Unit [email protected] failed to load: No such file or directory. See system logs and 'systemctl status [email protected]' for details.

I checked every answer mentioned in this thread but nothing seems working for me.

In the end, I just switch my user to postgres and then tried to start the service, all the postgres services started without any error.

Hope it might help someone.

Oaken

Answer 32

I faced the same issue.

So i checked for the logs

sudo tail -f /var/log/postgresql/postgresql-14-main.log (14 -> version of installed)

output as below

Examine the log output.
2023-04-05 19:43:16.884 +0530 [7097] FATAL:  could not open log file "/var/log/postgresql/logs/postgresql-2023-04-05.log": Permission denied
2023-04-05 19:43:16.886 +0530 [7097] LOG:  database system is shut down
pg_ctl: could not start server

after i checked for the log file permission

ls -ld /var/log/postgresql/logs
drw-r----- 2 postgres postgres 4096 Jan 29 09:18 /var/log/postgresql/logs

I excuted below commands

sudo chown postgres:postgres /var/log/postgresql/logs
sudo chmod 750 /var/log/postgresql/logs
sudo systemctl restart postgresql
sudo -u postgres psql

I was able fix the issue.

Newsome