29

I am running Ubuntu 16. I have installed Postgresql. Postgresql used to work, but then I rebooted.

nmap commands show port 5432 is open. Postgres seems to be working correctly:

service postgresql status

postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Sat 2017-07-29 18:42:59 EDT; 1min 4s ago Process: 201 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 201 (code=exited, status=0/SUCCESS)
Memory: 0B CGroup: /system.slice/postgresql.service

I ran this: psql

But I got this:

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

The file listed above does not seem to exist.

How do I get into Postgresql? Normally I'd run psql or sudo -i -u postgres then psql. But these commands are not working. I keep getting an error about "could not connect to server." Several reboots have not helped.

Update:

I ran this command: dpkg -l | grep postgres

rc postgresql-9.5                9.5.6-0ubuntu0.16.04                   amd64        object-relational SQL database, version 9.5 server
ii postgresql-client             9.5+173                                all          front-end programs for PostgreSQL (supported version)
ii postgresql-client-9.5         9.5.7-0ubuntu0.16.04                   amd64        front-end programs for PostgreSQL 9.5
ii postgresql-client-common      173                                    all          manager for multiple PostgreSQL client versions
ii postgresql-common             173                                    all          PostgreSQL database-cluster manager
Improve this question
8
  • What is the result of ps aux | grep '[b]in/postgres'?
    – Abelisto
    Jul 29, 2017 at 23:37
  • I get no output at all from that command.
    – Victor
    Jul 29, 2017 at 23:53
  • 2
    Show the output of pg_lsclusters, please. Also try using psql -h 127.0.0.1. It may be a configuration issue (disabled Unix sockets).
    – ypercubeᵀᴹ
    Jul 30, 2017 at 9:41
  • 5
    The dpkg status of postgresql-9.5 shoud be ii, not rc, which means it has been uninstalled. The configuration files and data should still be there, so simply doing apt-get install postgresql-9.5 has a good chance to work.
    – Daniel Vérité
    Jul 30, 2017 at 10:29
  • 1
    To those who arrived here with the same error: Have you checked your postgresql.conf? This can happen when bind ip is not 127.0.0.1
    – nurettin
    Feb 1, 2022 at 15:14

10 Answers 10

Reset to default
14

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

This error generally means that the server is not running. Based on dpkg -l output and the thread of comments, it was due to the postgresql-9.5 main package being somehow uninstalled. Since the uninstall hasn't been called with the --purge option to dpkg, the data and configuration files are still there, so apt-get install postgresql-9.5 can fix the problem.

Improve this answer
2
  • I have been working through this issue for half a day (though Im using postgresql-10). There are so many reasons for why someone could get the error in the OP. Its very misleading when the problem is that the packages were uninstalled (somehow?) but the system lists them as installed. Are you able to explain how this could happen? Im really confused, and wouldnt think to just apt-get install package-that-appears-installed. Also I am baffled as to how a simple apt-get update/upgrade could cause this, as its all I did this morning before encountering this problem. Pls halp understands?
    – Todd
    Oct 29, 2018 at 20:35
  • Also, tysm for this solution, totally saved me :-)
    – Todd
    Oct 29, 2018 at 20:50
26

If your Postgres service is up and running without any error or there is no error in starting the Postgres service and still you are getting the mentioned error, follow these steps

Step1: 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 . Try restarting Postgres clusters and service

Step 2: Restart the 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 an error

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) 

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

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 "ssl-cert" group. Run the below code to fix the user group issue and 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

sudo service postgresql restart
Improve this answer
1
  • 1
    This must be the correct answer, But after step 2, conditions may be different for someone else, for example, my problem was "invalid value for parameter "lc_monetary:... " and remains startUp steps for Postgres affected by... So, edit it to something like: How to figure out whats is you Postgres startup problem Not the title, of course, but the content
    – Mamrezo
    Mar 27, 2020 at 2:10
17

You probably have multiple PostgreSQL versions installed. If so, the other version probably defaults to unix_socket_directories = '/tmp/' but the libpq your psql is linked to probably defaults to /var/run/postgresql/.

Try

psql -h /tmp

If that works, the above is the problem. You can add export PGHOST=/tmp to your .bashrc to change the default locally for your user.

If that doesn't work, make sure PostgreSQL is actually running

ps aux |grep postgres

and if not, start it. How depends on how you installed it, but it'll be via the service or systemctl command(s) if you installed using packages.

Improve this answer
3
  • 1
    psql -h /tmp psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? So that did not work. I then tried this: # ps aux | grep postgres root 3627 0.0 0.0 11224 876 pts/0 S+ 22:37 0:00 grep --color=auto postgres I may have multiple versions installed. I am not sure what is wrong.
    – Victor
    Jul 30, 2017 at 2:38
  • 2
    Ok, so PostgreSQL isn't running (no postgres processes). You have to start it. It should usually autostart on boot so either the way you installed it won't autostart, you might have to start it with pg_ctl. Otherwise, check the logs. If you don't know what you installed or where, try find / -name postgres 2>/dev/null and/or rpm -qa|grep postgres or dpkg -l |grep postgres
    – Craig Ringer
    Jul 30, 2017 at 4:01
  • "service postgresql start" goes to the next line. There is no error. But no postgres daemon starts. "pg_ctl" is a "command not found. The command "find / -name postgres 2>/dev/null" returns /root/.linuxbrew/Library/Aliases/postgres.
    – Victor
    Jul 30, 2017 at 4:32
2

I was getting same error on ubuntu 20.04 for connecting through libpq.

could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I edited the config file of postgres

sudo nano /etc/postgresql/12/main/postgresql.conf

and changed

unix_socket_directories = '/var/run/postgresql/'

to

unix_socket_directories = '/tmp/'

then did a restart using

sudo service postgresql restart

and it started working

Improve this answer
2
  • Yes, but it made the postgresql listening sockets into /tmp. I think it is not a good solution from a security sense. They should be in /var/run/postgresql. The psql client connects to a bad location, not the postgresql server listens on a bad location.
    – peterh
    Dec 11, 2020 at 18:03
  • 1
    Thank you for this answer. I edit the same file and set the port to 5432 (from 5435). I believe the port was changed to 5435 b/c I had postgres 9 running when installing postgres 12.
    – Charles L.
    Aug 15, 2021 at 18:45
1

You're under Ubuntu. You can use Debian wrappers. Try pg_ctlcluster 9.6 main start (you can change main for your cluster's name.

To check if another PostgreSQL version is running, try ps -ef | grep postgres.

If it' won't start with pg_ctlcluster. Look at the logs. For Debian-based, default logging is in /var/log/postgresql.

Improve this answer
2
  • 1
    You're assuming that they're using Ubuntu's packages, or those like the apt.postgresql.org ones that integrate with pg_wrapper. Not necessarily a safe assumption. Many people don't know to use them and install from EDB or some other binary installer distro.
    – Craig Ringer
    Jul 30, 2017 at 11:12
  • Oh, I didn't think people would use the more complicated way, but I suppose "complicated" isn't objective.
    – Arkhena
    Jul 31, 2017 at 7:46
0

I solved this problem with a few checks

root@biber-OptiPlex-380:/home/biber/kailas/RoR/demo# su - postgres
postgres@biber-OptiPlex-380:~$ 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"?
postgres@biber-OptiPlex-380:~$ service postgresql status
9.5/main (port 5432): down
postgres@biber-OptiPlex-380:~$ service postgresql start
 * Starting PostgreSQL 9.5 database server                                                                                                        [ OK ] 
postgres@biber-OptiPlex-380:~$ service postgresql status
9.5/main (port 5432): online
postgres@biber-OptiPlex-380:~$ psql
psql (9.5.19)
Type "help" for help.

postgres=#
Improve this answer
1
  • 2
    Welcome to Database Administrators. Please, check the Markdown help to make your answer easier to read.
    – Ronaldo
    Dec 20, 2019 at 13:29
0

I know this is an old track, but I got the same exact issue and I solved it using some of the information I found here. so I would like to share this with you. I'm using Kali linux (Linux kali 6.0.0-kali6-amd64) 2022-12-19 running on VMWare VM. When I run Metasploit database manager I get this:

    └─$ sudo msfdb reinit

[i] Database already started psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? [+] Deleting configuration file /usr/share/metasploit-framework/config/database.yml [+] Stopping database [+] Starting database psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? [+] Creating database user 'msf' createuser: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? [+] Creating databases 'msf' createdb: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? [+] Creating databases 'msf_test' createdb: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? [+] Creating configuration file '/usr/share/metasploit-framework/config/database.yml' [+] Creating initial database schema rake aborted! ActiveRecord::ConnectionNotEstablished: connection to server at "::1", port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections? connection to server at "127.0.0.1", port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections? /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:83:in rescue in new_client' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:77:in new_client' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:37:in postgresql_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:882:in public_send' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:882:in new_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:926:in checkout_new_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:905:in try_to_checkout_new_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:866:in acquire_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:588:in checkout' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:428:in connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:1128:in retrieve_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_handling.rb:327:in retrieve_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_handling.rb:283:in connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/tasks/database_tasks.rb:237:in migrate' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:92:in block (3 levels) in <top (required)>' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:90:in each' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:90:in block (2 levels) in <top (required)>' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/rake-13.0.6/exe/rake:27:in <top (required)>'

Caused by: PG::ConnectionBad: connection to server at "::1", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections? connection to server at "127.0.0.1", port 5432 failed:

Connection refused Is the server running on that host and accepting TCP/IP connections? /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/pg-1.4.5/lib/pg/connection.rb:632:in async_connect_or_reset' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/pg-1.4.5/lib/pg/connection.rb:760:in connect_to_hosts' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/pg-1.4.5/lib/pg/connection.rb:695:in new' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/pg-1.4.5/lib/pg.rb:69:in connect' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:78:in new_client' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:37:in postgresql_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:882:in public_send' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:882:in new_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:926:in checkout_new_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:905:in try_to_checkout_new_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:866:in acquire_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:588:in checkout' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:428:in connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:1128:in retrieve_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_handling.rb:327:in retrieve_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_handling.rb:283:in connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/tasks/database_tasks.rb:237:in migrate' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:92:in block (3 levels) in <top (required)>' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:90:in each' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:90:in block (2 levels) in <top (required)>' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/rake-13.0.6/exe/rake:27:in `<top (required)>' Tasks: TOP => db:migrate (See full trace by running task with --trace)

I searched every where and used every thing I could find, but nothing work. However, folowing some hints from this track. I was able to solve the issue.

When I typed :

$ pg_lsclusters

I got :

Ver Cluster Port Status Owner Data directory
Log file 14 main 5432 down, postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log 15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

This means that the older version "14" which uses the port "5432" required by "msfbd" is down whilst version "15" which is "online" uses "5433". so simply changes the port using :

sudo PGPORT=5433 msfdb init

And voilà, it's working just fine.

$ msfconsole

Call trans opt: received. 2-19-98 13:24:18 REC:Loc

 Trace program: running                                                                                                                                                                                                                 
                                                                                                                                                                                                                                        
       wake up, Neo...                                                                                                                                                                                                                  
    the matrix has you                                                                                                                                                                                                                  
  follow the white rabbit.

      knock, knock, Neo.

                    (`.         ,-,
                    ` `.    ,;' /
                     `.  ,'/ .'
                      `. X /.'
            .-;--''--.._` ` (
          .'            /   `
         ,           ` '   Q '
         ,         ,   `._    \
      ,.|         '     `-.;_'
      :  . `  ;    `  ` --,.._;
       ' `    ,   )   .'
          `._ ,  '   /_
             ; ,''-,;' ``-
              ``-..__``--`

                         https://metasploit.com

   =[ metasploit v6.2.31-dev                          ]
  • -- --=[ 2274 exploits - 1192 auxiliary - 405 post ]
  • -- --=[ 951 payloads - 45 encoders - 11 nops ]
  • -- --=[ 9 evasion ]

Metasploit tip: Tired of setting RHOSTS for modules? Try globally setting it with setg RHOSTS x.x.x.x Metasploit Documentation: https://docs.metasploit.com/

msf6 >

I guess that the problem occurred after a system update.

Improve this answer
0

If you installed a different version of PostgreSQL and later uninstalled one or more formerly installed versions, then you also will see this error:

psql: error: 
    connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed

This happens because port 5432 was taken by the old PostgreSQL version when you installed the new one, so a different port was chosen to run the newly installed PostgreSQL server version (5433, 5434 etc.). Since you uninstalled the formerly installed PostgreSQL server version, no server is running on port 5433 anymore, so psql cannot connect. One might expect that the PostgreSQL client psql would be configured on install to connect to the server of the same version, at its different port, but that is not the case. Call it a bug if you like.

To fix this:

  1. Edit file /etc/postgresql/{version}/main/postgresql.conf and change line port = … to:

    port = 5432

  2. Restart the PostgreSQL server:

    sudo service postgresql restart

Source: A comment by @Charles L on another answer here. I just wanted to make this gem of knowledge more visible 😋

Improve this answer
-1

Try psql -h localhost -U postgres that cured the error message for me with Postgres running docker locally on Ubuntu 18.04.

Improve this answer
-3

Remove the database and user access
goto this path and open pg_hba.conf and remove the user below the "Database administrative login by Unix domain socket" other than postgres database.

/etc/postgresql/10/main$ sudo nano pg_hba.conf or sudo nano /etc/postgresql/10/main/pg_hba.conf

after removing the user we got like this:

after removing the user we got like this

Improve this answer
1
  • That is unrelated to the error message in the question.
    – Laurenz Albe
    Mar 3, 2020 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.