logo标签列表

PostgreSQL: 'psql：错误：无法连接到服务器：没有这个文件或目录'.s.PGSQL.5432

databasepostgresqlubuntuunixpsql
3
自从昨天在Ubuntu 20.04 - PostgreSQL 12上运行psql时，我遇到了一个错误。 以下是错误信息：
psql: 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"?

我已经在互联网上看到了很多关于这个问题的答案，但是没有一个能够解决我的问题...

这个问题发生在我安装phppgadmin后重新启动postgresql时，以下是最新的日志：

2021-01-01 21:37:27.981 UTC [1071608] LOG:  received fast shutdown request
2021-01-01 21:37:27.982 UTC [1071608] LOG:  aborting any active transactions
2021-01-01 21:37:27.982 UTC [434049] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.982 UTC [1514704] thegabdoosan@ephedia_web FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.984 UTC [1231171] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.986 UTC [1231170] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.988 UTC [899543] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.990 UTC [899542] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.992 UTC [899541] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.994 UTC [899540] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.996 UTC [899539] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.998 UTC [899538] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.999 UTC [899537] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:28.001 UTC [899536] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:28.009 UTC [1071608] LOG:  background worker "logical replication launcher" (PID 1071615) exited with exit code 1
2021-01-01 21:37:28.010 UTC [1071610] LOG:  shutting down
2021-01-01 21:37:28.030 UTC [1071608] LOG:  database system is shut down

我看不到任何奇怪的东西

  • pg_hba.conf：
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.106/24        md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
  • postgresql.conf
# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories

当我尝试运行psql -h localhost时，出现了另一个错误:
psql: error: could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

当我运行 sudo systemctl status postgresql 命令时：
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2021-01-02 09:10:59 UTC; 18min ago
    Process: 1750585 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1750585 (code=exited, status=0/SUCCESS)

janv. 02 09:10:59 vps-d989390a systemd[1]: Starting PostgreSQL RDBMS...
janv. 02 09:10:59 vps-d989390a systemd[1]: Finished PostgreSQL RDBMS.

当我运行 ls /var/run/postgresql/ -a 命令时：
0 drwxrwsr-x  3 postgres postgres   80 janv.  1 22:53 .
0 drwxr-xr-x 32 root     root     1060 janv.  2 09:09 ..
0 drwxr-s---  2 postgres postgres   40 janv.  1 21:37 12-main.pg_stat_tmp
0 lrwxrwxrwx  1 root     postgres   18 janv.  1 22:53 .s.PGSQL.5432 -> /tmp/.s.PGSQL.5432

当我运行 sudo pg_ctlcluster 12 main start 命令时：
Job for postgresql@12-main.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@12-main.service" and "journalctl -xe" for details.

并且pg_lsclusters：

Ver Cluster Port Status Owner     Data directory              Log file
12  main    5432 down   <unknown> /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

当我运行 sudo systemctl status postgresql@12-main.service 命令时：
● postgresql@12-main.service - PostgreSQL Cluster 12-main
     Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled; vendor preset: enabled)
     Active: failed (Result: protocol) since Sat 2021-01-02 13:21:05 UTC; 3h 50min ago
    Process: 705 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 12-main start (code=exited, status=1/FAILURE)

Jan 02 13:21:04 vps-d989390a systemd[1]: Starting PostgreSQL Cluster 12-main...
Jan 02 13:21:05 vps-d989390a postgresql@12-main[723]: Error: Could not open logfile /var/log/postgresql/postgresql-12-main.log
Jan 02 13:21:05 vps-d989390a postgresql@12-main[705]: Error: /usr/lib/postgresql/12/bin/pg_ctl /usr/lib/postgresql/12/bin/pg_ctl start -D /var/lib/postgresql/12/main -l /var/log/postgresql/postgresql-12>
Jan 02 13:21:05 vps-d989390a systemd[1]: postgresql@12-main.service: Can't open PID file /run/postgresql/12-main.pid (yet?) after start: Operation not permitted
Jan 02 13:21:05 vps-d989390a systemd[1]: postgresql@12-main.service: Failed with result 'protocol'.
Jan 02 13:21:05 vps-d989390a systemd[1]: Failed to start PostgreSQL Cluster 12-main.

以下是当我运行 sudo systemctl start postgresql@12-main.service 时，sudo journalctl -xe 的最后35行日志记录：https://mystb.in/TillDimensionIntellectual.yaml

/etc/init.d/postgresql 输出：https://mystb.in/AmountsAlexanderExtreme.bash

我还关闭了ufw。

如果我卸载并重新安装postgresql，会丢失我的数据库吗？

ls /etc/init.d/postgresql 返回 /etc/init.d/postgresql（https://imgur.com/02Y4jDm.png） - TheGabDooSan
1
我想要获取/etc/init.d/postgresql文件的内容。 - Adrian Klaver
我有这个：https://mystb.in/AmountsAlexanderExtreme.bash - TheGabDooSan
从日志信息中可以明显看出，服务器无法启动，因为它无法访问某些目录（例如日志，可能是Unix域套接字）。尝试手动创建这些目录，并赋予正确的模式和所有者。然后再次尝试。 - wildplasser
“/var/log/postgresql”和“/var/run/postgresql”的权限是什么？如果您正在考虑重新安装（我不建议立即这样做），请将“/var/lib/postgresql/12/main/”复制到其他地方。它包含服务器的数据目录。 - Adrian Klaver
显示剩余15条评论
1个回答
1
锁文件的位置（或处理方式）似乎已更改（版本之间？）。我通过编辑启动文件（该文件以setuid root执行）来修复它： sudo vi /etc/init.d/postgresql 
# Parse command line parameters.

case $1 in
  start)
        echo -n "Starting PostgreSQL: "
        test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj

        #################################
        # FIX: Directory Lockfile must be writable by postgres
        mkdir -p /var/run/postgresql
        chown postgres.postgres /var/run/postgresql
        ##################################

        #echo su - $PGUSER -c "$DAEMON -D '$PGDATA' &"
        su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
        echo "ok"
        ;;
  stop)

顺便提一下：unix-domain-socket有时也存在那个目录中。（曾经是/tmp/）

顺便说一下2：我把它放在启动脚本中，因为似乎在重新启动时/var/run/会被清除。

顺便说一下3：使用需谨慎！

很确定这不是它。我和@TheGabDooSan有相同的设置，我可以启动Postgres 12。另外上面的不是随Ubuntu软件包一起提供的postgresql初始化文件。 - Adrian Klaver
@AdrianKlaver 是的，你说得对。这是一个较旧的版本。（我从源代码安装） - wildplasser
不幸的是，@TheGabDooSan的设置并非如此，因此不适用。 - Adrian Klaver
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接