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

I experienced this issue when working with PostgreSQL on Ubuntu 18.04.

I checked my PostgreSQL status and realized that it was running fine using:

sudo systemctl status postgresql

I also tried restarting the PotgreSQL server on the machine using:

sudo systemctl restart postgresql

but the issue persisted:

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

Following Noushad’ answer I did the following:

List all the Postgres clusters running on your device:

pg_lsclusters

this gave me this output in red colour, showing that they were all down and the status also showed down:

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5433 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5434 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

Restart the pg_ctlcluster for one of the server clusters. For me I restarted PG 10:

sudo pg_ctlcluster 10 main start

It however threw the error below, and the same error occurred when I tried restarting other PG clusters:

Job for postgresql@10-main.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@10-main.service" and "journalctl -xe" for details.

Check the log for errors, in this case mine is PG 10:

sudo nano /var/log/postgresql/postgresql-10-main.log

I saw the following error:

2020-09-29 02:27:06.445 WAT [25041] FATAL:  data directory "/var/lib/postgresql/10/main" has group or world access
2020-09-29 02:27:06.445 WAT [25041] DETAIL:  Permissions should be u=rwx (0700).
pg_ctl: could not start server
Examine the log output.

This was caused because I made changes to the file permissions for the PostgreSQL data directory.

I fixed it by running the command below. I ran the command for the 3 PG clusters on my machine:

sudo chmod -R 0700 /var/lib/postgresql/10/main
sudo chmod -R 0700 /var/lib/postgresql/11/main
sudo chmod -R 0700 /var/lib/postgresql/12/main

Afterwhich I restarted each of the PG clusters:

sudo pg_ctlcluster 10 main start
sudo pg_ctlcluster 11 main start
sudo pg_ctlcluster 12 main start

And then finally I checked the health of clusters again:

pg_lsclusters

this time around everything was fine again as the status showed online:

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5434 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

That’s all.

I hope this helps

Источник

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

oxfist

7176 silver badges21 bronze badges

asked Mar 7, 2017 at 16:43

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:

Certify that postgresql service is running, using sudo service postgresql start
Run pg_lsclusters from your terminal
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.
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
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.
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
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!

answered Jul 13, 2017 at 20:05

malvadaomalvadao

3,3521 gold badge19 silver badges22 bronze badges

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 ✨

answered Jun 23, 2018 at 11:35

martinlasekmartinlasek

8721 gold badge8 silver badges9 bronze badges

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.

answered Jul 8, 2020 at 23:03

nicodpnicodp

2,3521 gold badge11 silver badges20 bronze badges

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.

Arnab Nandy

6,4425 gold badges43 silver badges50 bronze badges

answered Jun 15, 2018 at 21:15

wfgeowfgeo

2,5964 gold badges29 silver badges51 bronze badges

WARNING: This will remove the database

Use command:

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

Igor Parra

10.2k10 gold badges69 silver badges99 bronze badges

answered Jan 10, 2019 at 18:24

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.

Igor Parra

10.2k10 gold badges69 silver badges99 bronze badges

answered Apr 16, 2019 at 14:20

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.

answered Mar 7, 2017 at 17:52

J2112OJ2112O

6362 gold badges11 silver badges24 bronze badges

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

MevlütÖzdemirMevlütÖzdemir

3,1401 gold badge23 silver badges28 bronze badges

I was able to solve the issue by running:

sudo systemctl start postgresql@9.5-main

answered Jun 11, 2019 at 20:58

Eugene LemEugene Lem

911 silver badge2 bronze badges

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:

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).
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

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).
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.
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).
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

Denis PDenis P

4306 silver badges10 bronze badges

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.

answered Feb 5, 2020 at 8:56

xjixji

7,1034 gold badges38 silver badges60 bronze badges

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

horoyoi ohoroyoi o

5401 gold badge8 silver badges28 bronze badges

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

Sushin PvSushin Pv

1,8163 gold badges22 silver badges36 bronze badges

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

answered Jun 19, 2020 at 3:16

NishithNishith

8789 silver badges13 bronze badges

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:

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.
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.
we will see all packages that are installed related to postgresql, using this command: dpkg -l | grep postgresql
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:
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.
Now, if you try psql command you will get an error related to your user, as in the image:
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.
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

answered Mar 8, 2021 at 20:43

Abdel-RaoufAbdel-Raouf

7001 gold badge8 silver badges20 bronze badges

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

dda

5,9552 gold badges25 silver badges34 bronze badges

answered Feb 11, 2022 at 9:39

Shankar ARULShankar ARUL

12.4k11 gold badges68 silver badges69 bronze badges

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

CENTURIONCENTURION

3553 silver badges11 bronze badges

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.

answered Jan 31, 2018 at 7:29

ranvirranvir

961 silver badge7 bronze badges

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

answered Sep 14, 2018 at 7:53

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.

answered Sep 17, 2019 at 13:08

ChannaChanna

72217 silver badges27 bronze badges

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

answered Jan 5, 2020 at 15:35

muceyMucemuceyMuce

1191 gold badge2 silver badges8 bronze badges

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

umunBeingumunBeing

4841 gold badge5 silver badges15 bronze badges

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.

Arghya Sadhu

40.2k9 gold badges77 silver badges107 bronze badges

answered Sep 4, 2020 at 17:56

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.

answered Nov 11, 2020 at 20:49

DharmaDharma

2,3753 gold badges26 silver badges40 bronze badges

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

ThomasThomas

2,5011 gold badge9 silver badges16 bronze badges

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

kali users pls do this

sudo service postgresql restart

answered Aug 28, 2021 at 23:32

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

Источник

Сейчас столкнулся с тем же самым и стал гуглить, нашел этот вопрос. Потом вспомнил, что часом ранее редактировал файл start.conf, который находится в /etc/postgresql/дальше сами найдете)
В общем, я хотел, чтобы постгрес не запускался при старте машины, чтобы я мог запускать его через сервис пострес старт и поменял в том файле auto на manual, перезагрузил, смотрю — пострес не запустился, отлично. Но потом случился сабж. Потом поменял обратно на авто, опять перезагрузка, и проблема решилась. Возможно, кроме запуска сервиса нужно еще что-то запустить, чтобы все хорошо работало, этого уж я не знаю, не лютый линуксоид)

Обновление 17.07.2019
После перезагрузки компа столкнулся с тем же самым. Залез в логи, там прямым текстом написано

2019-07-17 15:12:07.950 MSK [547] ВАЖНО: для каталога данных «/var/lib/postgresql/11/main» установлены неправильные права доступа
2019-07-17 15:12:07.950 MSK [547] ПОДРОБНОСТИ: Маска прав должна быть u=rwx (0700) или u=rwx,g=rx (0750).
pg_ctl: не удалось запустить сервер
Изучите протокол выполнения.

Зачмодил каталог, и все заработало.

Источник

Actually, I installed PostgreSQL 9.4 (with postGIS extension), and when installed, everything used to work fine.

As said on many tuts, I’ve set the /data folder, checked configuration files, and so on. Worked on other projects so I did not work on psql for a while But when installation was done, it used to work correctly, I made a test database, initialized postgres user, etc.

Now, I try to start psql (with «default» postgres user) and cannot connect! Start/stop/restart service do not change anything…

Result of «psql» command (with postgres user) :

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

When I check service status, I get this :

postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
Active: active (exited) since tue 2016-05-24 09:24:13 CEST; 3s ago
Process: 5658 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 5658 (code=exited, status=0/SUCCESS)

Starting/Stopping/Restarting service with command

sudo service postgresql start (or restart or stop)

Does not change anything to actual system behaviour..

Log says:

DETAIL:  Permissions should be u=rwx (0700).
FATAL:  data directory "/var/lib/postgresql/9.4/main" has group or world access

Источник

#database #postgresql #ubuntu #unix #psql

#База данных #postgresql #ubuntu #unix #psql

Вопрос:

Со вчерашнего дня у меня возникает ошибка при запуске 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, потеряю ли я свои базы данных?

1. Что это systemctl status postgresql@12-main.service показывает? А также journalctl -xe после попытки запуска? Добавьте эту информацию в свой вопрос.

2. Я добавил это! 👌

3. Похоже, это какая-то комбинация ошибок разрешений ( Could not open logfile /var/log/postgresql/postgresql-12-main.log ) и неправильного каталога ( Can't open PID file /run/postgresql/12-main.pid ). Последнее должно быть /var/run/postgresql/12-main.pid . Как вы устанавливали пакеты? У вас есть более одного типа установки на компьютере?

4. Я установил пакеты с помощью apt-get. И я думаю, что у меня не более одного типа установки на компьютере:/ Если это ошибка разрешений, не могу ли я исправить это с помощью (а) командной строки (ов)?

5. Я хотел получить содержимое (то, что находится внутри) /etc/init.d/postgresql .

Ответ №1:

Расположение (или обработка) файла блокировки, похоже, изменилось (между версиями?). Я исправил это, отредактировав 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: используйте на свой страх и риск!

1. Уверен, что это не так. У меня те же настройки, что и у @TheGabDooSan, и я могу запустить Postgres 12. Также приведенный выше не postgresql является файлом инициализации, который поставляется с пакетами Ubuntu.

2. @AdrianKlaver Это правильно. Это более старая версия. (Я устанавливаю из исходного кода)

3. К сожалению, это не так, как настройка @TheGabDooSan, и поэтому она не применяется.

Источник

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

How I fixed it (mac)

Вопрос:

Комментарии:

Ответ №1:

Комментарии: