Блог

Со вчерашнего дня у меня возникает ошибка при запуске psql в Ubuntu 20.04 — PostgreSQL 12. Вот ошибка:

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

Я уже видел много ответов на этот вопрос в Интернете, но никто не работал…

Это произошло, когда я перезапустил postgresql после установки phppgadmin, вот последние журналы :

 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.
 

и вот последние 35 строк sudo journalctl -xe , когда я запускаю sudo systemctl start postgresql@12-main.service :
https://mystb.in/TillDimensionIntellectual.yaml

/etc/init.d/postgresql вывод: https://mystb.in/AmountsAlexanderExtreme.bash

Я также отключил ufw

Если я непреднамеренно установлю postgresql, потеряю ли я свои базы данных?

Расположение (или обработка) файла блокировки, похоже, изменилось (между версиями?). Я исправил это, отредактировав startupfile (который выполняется с помощью 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 amp;amp; 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' amp;"
        su - $PGUSER -c "$DAEMON -D '$PGDATA' amp;" >>$PGLOG 2>amp;1
        echo "ok"
        ;;
  stop)
 

Кстати: unix-domain-socket иногда тоже находится в этом каталоге. (раньше был /tmp/ )

BTW2: я поместил его в сценарий запуска, потому /var/run/ что при перезагрузке он стирается.

BTW3: используйте на свой страх и риск!