PostgreSQL Why psql can t connect to server

Question

I typed psql and I get this  psql  could not connect to server  No such file or directory     Is the server running locally and accepting     connections on Unix domain socket  quot  var run postgresql  s PGSQL 5432 quot    I used sudo netstat -nlp   grep 5432 to see the status but nothing showed  And I searched online  somebody told me to modify pg hba conf but I can t locate this file  And I also tried this command sudo ln -s  tmp  s PGSQL 5432  var run postgresql  s PGSQL 5432  It can t work

User · Answer

In my case I had this error   var run postgresql  s PGSQL 5433  note  one number up from the file it was looking for   s PGSQL 5432  was present  Tried the instructions at the top of this page but nothing worked  Turns out there was an old directory for PostGreSQL 12 config files in  etc postgresql 12  which I deleted  which solved the issue

User · Answer

During fresh installation of postgresql  By default  user name and password is assigned as  postgres   The feature this RDBMS provides is to add role for new user and create database  If you are getting such errors    login in by default username   root kalilinux    sudo -i -u postgres ype psql for interactive prompt  postgres kalilinux    psql To quit from prompt use   q To create new user role  postgres kalilinux    createuser --interactive   Now you are in interacive psql shell  Enjoy  Dont forget to login in from your username and type psql for shell

User · Answer

In my case  the service was running but the cluster was down and psql wouldn t start   My configuration files looked perfect but it kept throwing configuration errors and seemed to ignore the changes I was making   It turns out that whenever you use ALTER SYSTEM SET     syntax  PostgreSQL writes to a file called postgresql auto conf   That file is read in addition to the regular postgresql conf and pg hba conf files   In my distribution of Ubuntu  18 04   they are in different folders     - pg hba conf and postgresql conf are both in  etc postgresql 12 main - The auto-generated file is  var lib postgresql 12 main postgresql auto conf    I had tried to change the configuration using ALTER SYSTEM SET listen addresses    lt my-ip gt   but had made a mistake and that created a broken  ghost  configuration that I couldn t find   As soon as I erased the offending line in postgresql auto conf  it fixed everything

User · Answer

quick howto on debian to remotely access postgres database on server from the psql client   the changed config is doc d in the files     edit  etc postgresql 10 main postgresql conf with listen address   edit  etc postgresql 10 main pg hba conf and add line in the end with host all all 0 0 md5 create login role postgres   CREATE ROLE remoteuser LOGIN WITH PASSWORD  foo  sudo  etc init d postgresql restart changes take effect  login from clientside with psql --host ipofserver --port 5432 --username remoteuser --password --dbname mydb the password is interactivly asked which in this case is foo

User · Answer

I could resolve this by setting the right permissions to datadir  It should be   chmod 700  var lib postgresql 10 main chown postgres postgres  var lib postgresql 10 main

User · Answer

Verify that Postgres is running using   ps -ef   grep postgres   root 959dca34cc6d  var lib edb  ps -ef grep postgres enterpr     476  1  0 06 38          00 00 00  usr lib edb-as 11 bin edb-postgres -D  var lib edb-as 11 main2 -c config file  etc edb-as 11 main2 postgresql conf   Check for data directory and postgresql conf   In my case data directory in -D was different than that in postgresql conf  So I changed the data directory in postgresql conf and it worked

User · Answer

If there is no error in starting the Postgres service  follow these steps Step 1  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  If not restart PostgreSQL service Step 2  Restart the pg ctlcluster  format is pg ctlcluster  lt version gt   lt cluster gt   lt action gt  sudo pg ctlcluster 9 6 main start   restart PostgreSQL service sudo service postgresql restart  Step 3  Step 2 failed and threw an error If restarting pg lsclusters was not successful  it will throw an  error  My error was You can see the errors in the logs  var log postgresql postgresql-9 6-main log  FATAL  could not access private key file  quot  etc ssl private ssl-cert-snakeoil key quot   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 eg  sudo chown postgres -R  var lib postgresql 9 6 main  Step 5  Check postgres user belongs to ssl-cert user group It happened to me and it turned out that I removed erroneously the Postgres user from  quot ssl-cert quot  group  Run the below code to fix the user group issue and for fixing the permissions  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

User · Answer

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     I tried series of troubleshooting  until when i checked my disk usage and found that it was full  100  usage   df -h cd  var log odoo  cat  dev null  gt  odoo-server log reboot

User · Answer

The error means that the Postgres server is not running  Try starting it   sudo systemctl start postgresql   Make sure that the server starts on boot   sudo systemctl enable postgresql

User · Answer

So for me and my pals working on a Node js app  with Postgres and Sequelize   we had to    brew install postgresql  one of us was missing postgres  one of us was not  and yet we were getting the same error msg as listed above  brew services start postgresql       utilize Homebrew to start postgres  createdb  lt name of database in config json file gt  node modules  bin sequelize db migrate npm start

User · Answer

I had the same issue on Devuan ascii  maybe Debian  too    The config file  etc postgresql 9 6 main postgresql conf contains a directive unix socket directories which points to  var run postgresql by default  Changing it to  tmp  where most clients look by default  fixed it for me

User · Answer

if you are using windows subsystem for linux and Ruby on Rails  then check  your postgres is running in which port using this command sudo nano  etc postgresql 12 main postgresql conf if it is in port 5433 then go to database yml file and add port 5433 in there and then run command sudo service postgresql start i have resolved my issue like this

User · Answer

Just want to make a small addition  if your instance is complaining on a socket  you can also check unix socket directories at  data postgresql conf file which could have been set to  tmp  for example  if you have used a 3rd party distribution  You can change it to  var run postgresql and restart the service  That may also require creating a postgresql dir at  var run and subsys postgresql-9 6 at  var lock if those doesn t already exist  worked for me with postgresql 9 6

User · Answer

I have encountered a similar issue a couple of times  Normally I just do a fresh installation of PostgreSQL following this tutorial and that solves the problem at the expense of losing data    I was determined on getting real fix today  Restarting PostgreSQL resolved it on ubuntu  sudo  etc init d postgresql restart

User · Answer

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

User · Answer

In my case I saw this error and postgres was not running  The problem was that the instalation failed to create the required cluster  The solution was to create the folder  etc postgres  postgresql-version  main and then create the cluster with  pg createcluster  postgresql-version  main  After that wiht just restarting the postgresql service everything should work

User · Answer

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

User · Answer

I had the same problem  It seems that there is no socket when there is no cluster   The default cluster creation failed during the installation because no default locale was set

User · Answer

It can cause anything for example  my issue was caused for typo error on configuration files  Some of people says caused by certificate files  another group says caused by unmatched locals   If you cant find any solution about your issue  remove postgres and reinstall it This is the best solution

User · Answer

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

User · Answer

I had the similar issue and the problem was in the config file pg hba conf  I had earlier made some changes which was causing the server to error out while trying to start it  Commenting out the extra additions solved the problem

User · Answer

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  quot  var run postgresql  s PGSQL 5432 quot    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  quot systemctl status postgresql 10-main service quot  and  quot journalctl -xe quot  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  quot  var lib postgresql 10 main quot  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

User · Answer

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 conf needs to be fixed   Step 1  Verify that the database is running  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  ps -ef   grep postgres   On my system  mac osx  this spits out  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   man postgres   From there  you d see that the options -D and -r are respectively the datadir  amp  the logfilename    Step 2  If the postgres service is running  Use find to search for the location of the socket  which should be somewhere in the  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     tmp  s PGSQL 5432   Then  try connecting via psql using this file s location explicitly  eg   psql -h  tmp  dbname   Step 3  If the service is running but you don t see a socket  If you can t find the socket  but see that the service is running  Verify that the pg hba conf file allows local sockets   Browse to the datadir and you should find the pg hba conf file   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

User · Answer

If your service is not secure  this may be the reason vi  etc postgresql 11 main pg hba conf   open hba config file  this config file usualy located in the etc directory   host    all   all    localhost trust   md5   you can remove the trust keyword  save pg hba conf  sudo service postgresql restart

User · Answer

Restarting the instance worked for me  Also  as mentioned in some other post psql -h   tmp  worked as well before the restart  But post restart psql  directly started working   So  probably some file ownership issues that got reset with the restart is what I am thinking

[postgresql] PostgreSQL: Why psql can't connect to server?

Examples related to postgresql

Examples related to psql