Ошибка не удалось подключиться к серверу postgresql

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

answered Mar 7, 2017 at 17:52

This works for me:

pg_ctl -D /usr/local/var/postgresql@9.6 stop;
brew services stop postgresql@9.6;
brew services start postgresql@9.6;

answered Jul 5, 2019 at 7:20

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/homebrew.mxcl.postgresql@10.plist
redis started Denis /Users/Denis/Library/LaunchAgents/homebrew.mxcl.redis.plist

1. To investigate further open the config shown in the same command output in Plist column (I used nano /Users/Denis/Library/LaunchAgents/homebrew.mxcl.postgresql@10.plist to check it).
2. 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/postgresql@10.log.
3. Open that log and check the latest error (I used nano /usr/local/var/log/postgresql@10.log and then Alt+/ to go to the end of the file).
4. 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).

answered Apr 2, 2021 at 12:54

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.

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.

answered Jan 23, 2020 at 5:38

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

answered Jul 17, 2020 at 10:54

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

Open your database manager and execute this script

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

answered Aug 5, 2017 at 7:57

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.

answered Jan 29, 2018 at 1:58

answered Jan 31, 2018 at 7:29

answered Sep 14, 2018 at 7:53

answered Sep 17, 2019 at 13:08

answered Jan 5, 2020 at 15:35

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

answered Jun 8, 2020 at 7:32

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

answered Jul 7, 2021 at 11:37

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.

answered Nov 11, 2020 at 20:49

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

answered Nov 28, 2020 at 20:29

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

answered Apr 3, 2021 at 14:19

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)

answered Sep 9, 2021 at 22:57

This issue comes from installing the postgres package without a version number. Although postgres will be installed and it will be the correct version, the script to setup the cluster will not run correctly; it’s a packaging issue.

If you’re comfortable with postgres there is a script you can run to create this cluster and get postgres running. However, there’s an easier way.

First purge the old postgres install, which will remove everything of the old installation, including databases, so back up your databases first.. The issue currently lies with 9.1 so I will assume that’s what you have installed

sudo apt-get remove --purge postgresql-9.1

Now simply reinstall

sudo apt-get install postgresql-9.1

Note the package name with the version number. HTH.

The error message refers to a Unix-domain socket, so you need to tweak your netstat invocation to not exclude them. So try it without the option -t:

netstat -nlp | grep 5432

I would guess that the server is actually listening on the socket /tmp/.s.PGSQL.5432 rather than the /var/run/postgresql/.s.PGSQL.5432 that your client is attempting to connect to. This is a typical problem when using hand-compiled or third-party PostgreSQL packages on Debian or Ubuntu, because the source default for the Unix-domain socket directory is /tmp but the Debian packaging changes it to /var/run/postgresql.

Possible workarounds:

• Use the clients supplied by your third-party package (call /opt/djangostack-1.3-0/postgresql/bin/psql). Possibly uninstall the Ubuntu-supplied packages altogether (might be difficult because of other reverse dependencies).
• Fix the socket directory of the third-party package to be compatible with Debian/Ubuntu.
• Use -H localhost to connect via TCP/IP instead.
• Use -h /tmp or equivalent PGHOST setting to point to the right directory.
• Don’t use third-party packages.

This works for me:

Edit: postgresql.conf

sudo nano /etc/postgresql/9.3/main/postgresql.conf

Enable or add:

listen_addresses = '*'

Restart the database engine:

sudo service postgresql restart

Also, you can check the file pg_hba.conf

sudo nano /etc/postgresql/9.3/main/pg_hba.conf

And add your network or host address:

host    all             all             192.168.1.0/24          md5

You can use psql -U postgres -h localhost to force the connection to happen over TCP instead of UNIX domain sockets; your netstat output shows that the PostgreSQL server is listening on localhost’s port 5432.

You can find out which local UNIX socket is used by the PostgrSQL server by using a different invocavtion of netstat:

netstat -lp --protocol=unix | grep postgres

At any rate, the interfaces on which the PostgreSQL server listens to are configured in postgresql.conf.

answered Jun 26, 2011 at 12:51

Just create a softlink like this :

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

answered Jul 9, 2012 at 1:35

I make it work by doing this:

dpkg-reconfigure locales

Choose your preferred locales then run

pg_createcluster 9.5 main --start

(9.5 is my version of postgresql)

/etc/init.d/postgresql start

and then it works!

sudo su - postgres
psql

I had to compile PostgreSQL 8.1 on Debian Squeeze because I am using Project Open, which is based on OpenACS and will not run on more recent versions of PostgreSQL.

The default compile configuration puts the unix_socket in /tmp, but Project Open, which relies on PostgreSQL, would not work because it looks for the unix_socket at /var/run/postgresql.

There is a setting in postgresql.conf to set the location of the socket. My problem was that either I could set for /tmp and psql worked, but not project open, or I could set it for /var/run/postgresql and psql would not work but project open did.

One resolution to the issue is to set the socket for /var/run/postgresql and then run psql, based on Peter’s suggestion, as:

psql -h /var/run/postgresql

This runs locally using local permissions. The only drawback is that it is more typing than simply «psql».

The other suggestion that someone made was to create a symbolic link between the two locations. This also worked, but, the link disappeared upon reboot. It maybe easier to just use the -h argument, however, I created the symbolic link from within the PostgreSQL script in /etc/init.d. I placed the symbolic link create command in the «start» section. Of course, when I issue a stop and start or restart command, it will try to recreate an existing symbolic link, but other than warning message, there is probably no harm in that.

In my case, instead of:

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

I have

ln -s /var/run/postgresql/.s.PGSQL.5432 /tmp/.s.PGSQL.5432

and have explicitly set the unix_socket to /var/run/postgresql/.s.PGSQL.5432 in postgresql.conf.

If your Postgres service is up and running without any error or there is no error in starting the Postgres service and still you are getting the mentioned error, follow these steps

Step1: Running pg_lsclusters will list all the postgres clusters running on your device

eg:

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

most probably the status will be down in your case and postgres service

Step 2: Restart the pg_ctlcluster

#format is pg_ctlcluster <version> <cluster> <action>
sudo pg_ctlcluster 9.6 main start

#restart postgresql service
sudo service postgresql restart

Step 3: Step 2 failed and threw error

If this process is not successful it will throw an error. You can see the error log on /var/log/postgresql/postgresql-9.6-main.log

My error was:

FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
Try adding `postgres` user to the group `ssl-cert`

Step 4: check ownership of postgres

Make sure that postgres is the owner of /var/lib/postgresql/version_no/main

If not, run

sudo chown postgres -R /var/lib/postgresql/9.6/main/

Step 5: Check postgres user belongs to ssl-cert user group

It turned out that I had erroneously removed the Postgres user from the ssl-cert group. Run the below code to fix the user group issue and fix the permissions

#set user to group back with
sudo gpasswd -a postgres ssl-cert

# Fix ownership and mode
sudo chown root:ssl-cert  /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 740 /etc/ssl/private/ssl-cert-snakeoil.key

# now postgresql starts! (and install command doesn't fail anymore)
sudo service postgresql restart

answered Feb 1, 2018 at 15:45

Solution:

Do this

export LC_ALL="en_US.UTF-8"

and this. (9.3 is my current PostgreSQL version. Write your version!)

sudo pg_createcluster 9.3 main --start

answered Feb 23, 2016 at 21:47

In my case it was caused by a typo I made while editing /etc/postgresql/9.5/main/pg_hba.conf

I changed:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

to:

# Database administrative login by Unix domain socket
local   all             postgres                                MD5

But MD5 had to be lowercase md5:

# Database administrative login by Unix domain socket
local   all             postgres                                md5

answered Nov 29, 2016 at 10:15

answered Jan 19, 2017 at 1:34

answered Nov 6, 2014 at 13:01

Evan CarrollEvan Carroll

7,30615 gold badges53 silver badges87 bronze badges

answered Jan 30, 2017 at 15:18

answered Feb 5, 2016 at 16:43

answered Jun 26, 2013 at 13:29

After many exhausting attempts, I found the solution based on other posts!

dpkg -l | grep postgres
apt-get --purge remove <package-founded-1> <package-founded-2>
whereis postgres
whereis postgresql
sudo rm -rf <paths-founded>
sudo userdel -f postgres

answered May 29, 2021 at 12:21

Create postgresql directory inside run and then run the following command.

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

answered Apr 26, 2019 at 5:35

Simply add /tmp unix_socket_directories

postgresql.conf

unix_socket_directories = '/var/run/postgresql,/tmp'

answered Jun 17, 2019 at 1:00

I had this problem with another port. The problem was, that I had a system variable in /etc/environments with the following value:

PGPORT=54420

As I removed it (and restarted), psql was able to connect.

answered Jul 18, 2021 at 14:46