postgresql 为什么psql无法连接到服务器？

Why psql can't connect to server?

提问by hidemyname

I typed psqland I get this:

我打字psql，我得到了这个：

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

I used sudo netstat -nlp | grep 5432to see the status but nothing showed. And I searched online, somebody told me to modify pg_hba.confbut I can't locatethis file. And I also tried this commandsudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432. It can't work.

我曾经sudo netstat -nlp | grep 5432看到状态，但什么也没显示。我在网上搜索，有人告诉我要修改，pg_hba.conf但我不能修改locate这个文件。我也试过这个命令sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432。它不能工作。

采纳答案by Haleemur Ali

The error states that the psql utility can't find the socket to connect to your database server. Either you don't have the database service running in the background, or the socket is located elsewhere, or perhaps the pg_hba.confneeds to be fixed.

该错误指出 psql 实用程序找不到连接到数据库服务器的套接字。要么您没有在后台运行数据库服务，要么套接字位于其他地方，或者可能pg_hba.conf需要修复。

Step 1: Verify that the database is running

步骤 1：验证数据库是否正在运行

The command may vary depending on your operating system. But on most *ix systems the following would work, it will search for postgres among all running processes

该命令可能因您的操作系统而异。但是在大多数 *ix 系统上，以下内容会起作用，它将在所有正在运行的进程中搜索 postgres

ps -ef | grep postgres

On my system, mac osx, this spits out

在我的系统 mac osx 上，这会吐出来

501   408     1   0  2Jul15 ??         0:21.63 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log

The last column shows the command used to start the server, and the options.

最后一列显示用于启动服务器的命令和选项。

You can look at all the options available to start the postgres server using the following.

您可以使用以下命令查看可用于启动 postgres 服务器的所有选项。

man postgres

From there, you'd see that the options -Dand -rare respectively the datadir& the logfilename.

从那里，您会看到 options-D和-r分别是datadir& logfilename。

Step 2: If the postgres service is running

第 2 步：如果 postgres 服务正在运行

Use findto search for the location of the socket, which should be somewhere in the /tmp

使用find搜索插座的位置，这应该是在某处/tmp

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

If postgres is running and accepting socket connections, the above should tell you the location of the socket. On my machine, it turned out to be:

如果 postgres 正在运行并接受套接字连接，上面应该告诉你套接字的位置。在我的机器上，结果是：

/tmp/.s.PGSQL.5432

Then, try connecting via psql using this file's location explicitly, eg.

然后，尝试通过 psql 显式使用此文件的位置进行连接，例如。

psql -h /tmp/ dbname

Step 3: If the service is running but you don't see a socket

第 3 步：如果服务正在运行但您没有看到套接字

If you can't find the socket, but see that the service is running, Verify that the pg_hba.conf file allows local sockets.

如果找不到套接字，但看到服务正在运行，请验证 pg_hba.conf 文件是否允许本地套接字。

Browse to the datadirand you should find the pg_hba.conffile.

浏览到datadir，您应该会找到该pg_hba.conf文件。

By default, near the bottom of the file you should see the following lines:

默认情况下，在文件底部附近，您应该会看到以下几行：

# "local" is for Unix domain socket connections only
local       all       all       trust

If you don't see it, you can modify the file, and restart the postgres service.

如果没有看到，可以修改文件，重启postgres服务。

回答by Noushad

If there is no error in starting the postgres service, follow these steps

如果启动postgres服务没有报错，请按照以下步骤操作

Step 1: Running pg_lsclusterswill list all the postgres clusters running on your device

第 1 步：运行pg_lsclusters将列出您设备上运行的所有 postgres 集群

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 postgresql service

在您的情况和 postgresql 服务中，状态很可能会下降

Step 2: Restart the pg_ctlcluster

第二步：重启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

第 3 步：第 2 步失败并抛出错误

If this process is not successfull it will throw the error. My error was(You can see the error log on /var/log/postgresql/postgresql-9.6-main.log)

如果此过程不成功，它将抛出错误。我的错误是（你可以看到错误日志/var/log/postgresql/postgresql-9.6-main.log）

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

第 4 步：检查 postgres 的所有权

Make sure that postgresis the owner of /var/lib/postgresql/version_no/maineg: sudo chown postgres -R /var/lib/postgresql/9.6/main/

确保这postgres是/var/lib/postgresql/version_no/main例如的所有者：sudo chown postgres -R /var/lib/postgresql/9.6/main/

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

第五步：检查postgres用户是否属于ssl-cert用户组

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

这发生在我身上，结果我错误地从“ssl-cert”组中删除了 Postgres 用户。运行以下代码以修复用户组问题并修复权限

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

# Fixed 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

回答by Faith Nassiwa

I have encountered a similar issue a couple of times. Normally I just do a fresh installation of PostgreSQL following this tutorialand that solves the problem at the expense of losing data.

我遇到过几次类似的问题。通常我只是按照本教程重新安装 PostgreSQL ，以丢失数据为代价来解决问题。

I was determined on getting real fix today. Restarting PostgreSQL resolved it on ubuntu. sudo /etc/init.d/postgresql restart

我决定今天得到真正的修复。重启 PostgreSQL 在 ubuntu 上解决了这个问题。sudo /etc/init.d/postgresql restart

回答by hidemyname

Solved it! Although I don't know what happened, but I just deleted all the stuff and reinstalled it. This is the command I used to delete it sudo apt-get --purge remove postgresql\*and dpkg -l | grep postgres. The latter one is to find all the packets in case it is not clean.

解决了！虽然我不知道发生了什么，但我只是删除了所有的东西并重新安装了它。这是我用来删除它sudo apt-get --purge remove postgresql\*和dpkg -l | grep postgres. 后一种是查找所有数据包，以防不洁。

回答by Aashutosh Rathi

I was facing same problem and

我面临同样的问题

sudo su - postgres
initdb --locale en_US.UTF-8 -D /var/lib/postgres/data
exit
sudo systemctl start postgresql
sudo systemctl status postgresql

This worked for me.

这对我有用。

回答by qrtLs

quick howto on debian to remotely access postgres database on server from the psql client: (the changed config is doc'd in the files):

在 debian 上从 psql 客户端远程访问服务器上的 postgres 数据库的快速方法：（更改的配置在文件中记录）：

1. edit /etc/postgresql/10/main/postgresql.confwith listen_address *
2. edit /etc/postgresql/10/main/pg_hba.confand add line in the end with host all all 0/0 md5
3. create login role postgres=# CREATE ROLE remoteuser LOGIN WITH PASSWORD 'foo'

4. sudo /etc/init.d/postgresql restartchanges take effect

5. login from clientside with psql --host=ipofserver --port=5432 --username=remoteuser --password --dbname=mydb

6. the password is interactivly asked which in this case is foo

1. /etc/postgresql/10/main/postgresql.conf使用 listen_address编辑*
2. 最后编辑/etc/postgresql/10/main/pg_hba.conf并添加行host all all 0/0 md5
3. 创建登录角色 postgres=# CREATE ROLE remoteuser LOGIN WITH PASSWORD 'foo'

4. sudo /etc/init.d/postgresql restart更改生效

5. 从客户端登录 psql --host=ipofserver --port=5432 --username=remoteuser --password --dbname=mydb

6. 以交互方式询问密码，在这种情况下哪个是 foo

回答by sbandra

The error means that the Postgres server is not running. Try starting it:

该错误意味着 Postgres 服务器没有运行。尝试启动它：

sudo systemctl start postgresql

Make sure that the server starts on boot:

确保服务器在引导时启动：

sudo systemctl enable postgresql

回答by Markus

I had the same issue on Devuan ascii (maybe Debian, too?). The config file /etc/postgresql/9.6/main/postgresql.confcontains a directive unix_socket_directorieswhich points to /var/run/postgresqlby default. Changing it to /tmp, where most clients look by default, fixed it for me.

我在 Devuan ascii 上遇到了同样的问题（也许是 Debian？）。该配置文件/etc/postgresql/9.6/main/postgresql.conf包含一个指令unix_socket_directories指向/var/run/postgresql默认。将其更改为/tmp，大多数客户默认查看的位置，为我修复了它。

回答by Hafeez Oguntola

I resolved this problem by checking my file system the disk was completely full, and so database could not start up

我通过检查我的文件系统磁盘已满，因此数据库无法启动来解决此问题

connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432" ?

Unix 域套接字上的连接 "/var/run/postgresql/.s.PGSQL.5432" ?

I tried series of troubleshooting, until when i checked my disk usage and found that it was full, 100% usage,

我尝试了一系列的故障排除，直到我检查了我的磁盘使用情况，发现它已满，100% 使用率，

df -h
cd /var/log/odoo/
cat /dev/null > odoo-server.log
reboot

回答by Fide

My issue with this error message was in wrong permissions on key and pem certificates, which I have manipulated. What helped me a lot was: /var/log/postgresql/postgresql-9.5-main.log where are all the errors.

我对这个错误消息的问题是我操纵的密钥和 pem 证书的权限错误。对我有很大帮助的是：/var/log/postgresql/postgresql-9.5-main.log 所有错误在哪里。