Psql ошибка подключиться к серверу через сокет var run postgresql s pgsql 5432 не удалось - Исправление ошибок и поиск оптимальных решений проблем

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.

Источник

    └─$ 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.

Источник

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

6695 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,3321 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

8628 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,3321 gold badge10 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,3245 gold badges46 silver badges50 bronze badges

answered Jun 15, 2018 at 21:15

wfgeowfgeo

2,4294 gold badges27 silver badges48 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.1k10 gold badges68 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.1k10 gold badges68 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 badges23 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,0501 gold badge21 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

811 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

3544 silver badges7 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

6,7534 gold badges36 silver badges58 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

5101 gold badge8 silver badges27 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,7713 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

87811 silver badges13 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

3453 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 had similar problems just a while ago. After trying more than 5 suggestions I decided to go back to the basics and start from the beginning. Which meant removing my postgresql installation and following this guide upon re-installing postgresql. https://help.ubuntu.com/lts/serverguide/postgresql.html

answered Sep 30, 2019 at 5:10

sylverysylvery

1561 silver badge7 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

6701 gold badge7 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,9322 gold badges25 silver badges34 bronze badges

answered Feb 11, 2022 at 9:39

Shankar ARULShankar ARUL

12.1k11 gold badges67 silver badges69 bronze badges

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

67417 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

4221 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

39.1k9 gold badges73 silver badges99 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,2572 gold badges25 silver badges39 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,3161 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

Источник

This issue comes from installing the postgres package without a version number. Although postgres will be installed and it will be the correct version, the script to setup the cluster will not run correctly; it’s a packaging issue.

If you’re comfortable with postgres there is a script you can run to create this cluster and get postgres running. However, there’s an easier way.

First purge the old postgres install, which will remove everything of the old installation, including databases, so back up your databases first.. The issue currently lies with 9.1 so I will assume that’s what you have installed

sudo apt-get remove --purge postgresql-9.1

Now simply reinstall

sudo apt-get install postgresql-9.1

Note the package name with the version number. HTH.

Thomas Ward♦

70.4k29 gold badges173 silver badges236 bronze badges

answered Aug 19, 2013 at 23:54

StewartStewart

1,1581 gold badge7 silver badges7 bronze badges

The error message refers to a Unix-domain socket, so you need to tweak your netstat invocation to not exclude them. So try it without the option -t:

netstat -nlp | grep 5432

I would guess that the server is actually listening on the socket /tmp/.s.PGSQL.5432 rather than the /var/run/postgresql/.s.PGSQL.5432 that your client is attempting to connect to. This is a typical problem when using hand-compiled or third-party PostgreSQL packages on Debian or Ubuntu, because the source default for the Unix-domain socket directory is /tmp but the Debian packaging changes it to /var/run/postgresql.

Possible workarounds:

Use the clients supplied by your third-party package (call /opt/djangostack-1.3-0/postgresql/bin/psql). Possibly uninstall the Ubuntu-supplied packages altogether (might be difficult because of other reverse dependencies).
Fix the socket directory of the third-party package to be compatible with Debian/Ubuntu.
Use -H localhost to connect via TCP/IP instead.
Use -h /tmp or equivalent PGHOST setting to point to the right directory.
Don’t use third-party packages.

answered Jun 27, 2011 at 17:41

This works for me:

Edit: postgresql.conf

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

Enable or add:

listen_addresses = '*'

Restart the database engine:

sudo service postgresql restart

Also, you can check the file pg_hba.conf

sudo nano /etc/postgresql/9.3/main/pg_hba.conf

And add your network or host address:

host    all             all             192.168.1.0/24          md5

Zanna♦

68.3k55 gold badges210 silver badges320 bronze badges

answered Oct 9, 2014 at 13:17

angelousangelous

3813 silver badges2 bronze badges

You can use psql -U postgres -h localhost to force the connection to happen over TCP instead of UNIX domain sockets; your netstat output shows that the PostgreSQL server is listening on localhost’s port 5432.

You can find out which local UNIX socket is used by the PostgrSQL server by using a different invocavtion of netstat:

netstat -lp --protocol=unix | grep postgres

At any rate, the interfaces on which the PostgreSQL server listens to are configured in postgresql.conf.

answered Jun 26, 2011 at 12:51

Riccardo MurriRiccardo Murri

16.2k7 gold badges52 silver badges51 bronze badges

Just create a softlink like this :

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

answered Jul 9, 2012 at 1:35

UrielUriel

2012 silver badges2 bronze badges

I make it work by doing this:

dpkg-reconfigure locales

Choose your preferred locales then run

pg_createcluster 9.5 main --start

(9.5 is my version of postgresql)

/etc/init.d/postgresql start

and then it works!

sudo su - postgres
psql

Zanna♦

68.3k55 gold badges210 silver badges320 bronze badges

answered Sep 13, 2016 at 9:05

mymusisemymusise

1911 silver badge2 bronze badges

I had to compile PostgreSQL 8.1 on Debian Squeeze because I am using Project Open, which is based on OpenACS and will not run on more recent versions of PostgreSQL.

The default compile configuration puts the unix_socket in /tmp, but Project Open, which relies on PostgreSQL, would not work because it looks for the unix_socket at /var/run/postgresql.

There is a setting in postgresql.conf to set the location of the socket. My problem was that either I could set for /tmp and psql worked, but not project open, or I could set it for /var/run/postgresql and psql would not work but project open did.

One resolution to the issue is to set the socket for /var/run/postgresql and then run psql, based on Peter’s suggestion, as:

psql -h /var/run/postgresql

This runs locally using local permissions. The only drawback is that it is more typing than simply «psql».

The other suggestion that someone made was to create a symbolic link between the two locations. This also worked, but, the link disappeared upon reboot. It maybe easier to just use the -h argument, however, I created the symbolic link from within the PostgreSQL script in /etc/init.d. I placed the symbolic link create command in the «start» section. Of course, when I issue a stop and start or restart command, it will try to recreate an existing symbolic link, but other than warning message, there is probably no harm in that.

In my case, instead of:

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

I have

ln -s /var/run/postgresql/.s.PGSQL.5432 /tmp/.s.PGSQL.5432

and have explicitly set the unix_socket to /var/run/postgresql/.s.PGSQL.5432 in postgresql.conf.

Peachy

6,98710 gold badges36 silver badges45 bronze badges

answered Nov 6, 2012 at 3:22

JoeJoe

611 silver badge1 bronze badge

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 and postgres 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 error

If this process is not successful it will throw an error. You can see the error log on /var/log/postgresql/postgresql-9.6-main.log

My error was:

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

If not, run

sudo chown postgres -R /var/lib/postgresql/9.6/main/

Step 5: Check postgres user belongs to ssl-cert user group

It turned out that I had erroneously removed the Postgres user from the ssl-cert group. Run the below code to fix the user group issue and fix the permissions

#set user to group back with
sudo gpasswd -a postgres ssl-cert

# Fix 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

answered Apr 16, 2018 at 14:44

NoushadNoushad

1711 silver badge5 bronze badges

I found uninstalling Postgres sounds unconvincing.
This helps to solve my problem:

Start the postgres server:
```
sudo systemctl start postgresql
```
Make sure that the server starts on boot:
```
sudo systemctl enable postgresql
```

Detail information can be found on DigitalOcean site Here.

answered Feb 1, 2018 at 15:45

parladparlad

2294 silver badges11 bronze badges

Solution:

Do this

export LC_ALL="en_US.UTF-8"

and this. (9.3 is my current PostgreSQL version. Write your version!)

sudo pg_createcluster 9.3 main --start

answered Feb 23, 2016 at 21:47

In my case it was caused by a typo I made while editing /etc/postgresql/9.5/main/pg_hba.conf

I changed:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

to:

# Database administrative login by Unix domain socket
local   all             postgres                                MD5

But MD5 had to be lowercase md5:

# Database administrative login by Unix domain socket
local   all             postgres                                md5

answered Nov 29, 2016 at 10:15

I failed to solve this problem with my postgres-9.5 server. After 3 days of zero progress trying every permutation of fix on this and other sites I decided to re-install the server and lose 5 days worth of work. But, I did replicate the issue on the new instance. This might provide some perspective on how to fix it before you take the catastrophic approach I did.

First, disable all logging settings in postgresql.conf. This is the section:

# ERROR REPORTING AND LOGGING

Comment out everything in that section. Then restart the service.

When restarting, use /etc/init.d/postgresql start or restart
I found it helpful to be in superuser mode while restarting. I had a x-window open just for that operation. You can establish that superuser mode with sudo -i.

Verify that the server can be reached with this simple command: psql -l -U postgres

If that doesn’t fix it, then consider this:

I was changing the ownership on many folders while trying to find a solution. I knew that I’d probably be trying to revert those folder ownerships and chmods for 2 more days. If you have already messed with those folder ownerships and don’t want to completely purge your server, then start tracking the settings for all impacted folders to bring them back to the original state. You might want to try to do a parallel install on another system and systematically check the ownership and settings of all folders. Tedious, but you may be able to get access to your data.

Once you do gain access, systematically change each relevant line in the # ERROR REPORTING AND LOGGING section of the postgresql.conf file. Restart and test. I found that the default folder for the logs was causing a failure. I specifically commented out log_directory. The default folder the system drops the logs into is then /var/log/postgresql.

Zanna♦

68.3k55 gold badges210 silver badges320 bronze badges

answered Jan 19, 2017 at 1:34

Possibly it could have happened because you changed the permissions of the /var/lib/postgresql/9.3/main folder.

Try changing it to 700 using the command below:

sudo chmod 700 main

Zanna♦

68.3k55 gold badges210 silver badges320 bronze badges

answered Nov 6, 2014 at 13:01

This is not exactly related to the question since I’m using Flask, but this was the exact error I was getting and this was the most relevant thread to get ideas.

My setup: Windows Subsystem for Linux, Docker-compose w/ makefile w/ dockerfile, Flask, Postgresql (using a schema consisting of tables)

To connect to postgres, setup your connection string like this:

from flask import Flask
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql+psycopg2://<user>:<password>@<container_name_in_docker-compose.yml>/<database_name>"

NOTE: I never got any IP (e.g. localhost, 127.0.0.1) to work using any method in this thread. Idea for using the container name instead of localhost came from here: https://github.com/docker-library/postgres/issues/297

Set your schema:

from sqlalchemy import MetaData
db = SQLAlchemy(app, metadata=MetaData(schema="<schema_name>"))

Set your search path for your functions when you setup your session:

db.session.execute("SET search_path TO <schema_name>")

answered Mar 29, 2019 at 21:30

The most upvoted answer isn’t even remotely correct because you can see in the question the server is running on the expected port (he shows this with netstat).

While the OP did not mark the other answer as chosen, they commented that the other answer (which makes sense and works) was sufficient,

But for these reasons that solution is poor and insecure even if it the server wasn’t running on port 5432:

What you’re doing here when you say --purge is you’re deleting the configuration file for PostgreSQL ((as well as all of the data with the database. You or may not even see a warning about this, but here is the warning just to show you now,

Removing the PostgreSQL server package will leave existing database clusters intact, i.e. their configuration, data, and log directories will not be removed. On purging the package, the directories can optionally be removed. Remove PostgreSQL directories when package is purged? [prompt for yes or no]

When you add it again PostgreSQL is reinstalling it to a port number that’s not taken (which may be the port number you expect). Before you even try this solution, you need to answer a few questions along the same line:

Do I want multiple versions of PostgreSQL on my machine?
Do I want an older version of PostgreSQL?
What do I want to happen when I dist-upgrade and there is a newer version?

Currently when you dist-upgrade on Ubuntu (and any Debian variant), the new version of PostgreSQL is installed alongside the old copy and the port number on the new copy is the port number of the old copy + 1. So you can just start it up, increment the port number in your client and you’ve got a new install! And you have the old install to fall back on — it’s safe!

However, if you only one want version of PostgreSQL purging to change the configuration is still not the right option because it will destroy your database. The only time this could even be acceptable is you want to destroy everything related to PostgreSQL. You’re better off ensuring your database is correct and then merely editing the configuration file so the new install runs on the old port number

#!/bin/bash

# We can find the version number of the newest PostgreSQL with this
VERSION=$(dpkg-query -W -f "${Version}" 'postgresql' | sed -e's/+.*//')
PGCONF="/etc/postgresql/${VERSION}/main/postgresql.conf"

# Then we can update the port.
sudo sed -ie '/port = /s/.*/port = 5432/' "$PGCONF"

sudo systemctl restart postgresql

Do not install a specific version of PostgreSQL. Only ever install postgresql. If you install a specific version then when you dist-upgrade your version will simply remain on your computer forever without upgrades. The repo will no longer have the security patches for the old version (which they don’t support). This must always be suboptimal to getting a newer version that they do support, running on a different port number.

answered Aug 4, 2021 at 18:30

Evan CarrollEvan Carroll

7,49915 gold badges53 silver badges86 bronze badges

I had the exact same problem Peter Eisentraut described. Using the netstat -nlp | grep 5432 command, I could see the server was listening on socket /tmp/.s.PGSQL.5432.

To fix this, just edit your postgresql.conf file and change the following lines:

listen_addresses = '*'
unix_socket_directories = '/var/run/postgresql'

Now run service postgresql-9.4 restart (Replace 9-4 with your version), and remote connections should be working now.

Now to allow local connections, simply create a symbolic link to the /var/run/postgresql directory.

ln -s /var/run/postgresql/.s.PGSQL.5432 /tmp/.s.PGSQL.5432

Don’t forget to make sure your pg_hba.conf is correctly configured too.

answered Nov 20, 2015 at 16:44

In my case, all i had to do was this:

sudo service postgresql restart

and then

sudo -u postgres psql

This worked just fine.
Hope it helps.
Cheers .

answered Jun 29, 2017 at 17:21

Find your file:

sudo find /tmp/ -name .s.PGSQL.5432

Result:

/tmp/.s.PGSQL.5432

su postgres
psql -h /tmp/ yourdatabase

Zanna♦

68.3k55 gold badges210 silver badges320 bronze badges

answered Jan 30, 2017 at 15:18

I had the same problem (on Ubuntu 15.10 (wily)). sudo find / -name 'pg_hba.conf' -print or sudo find / -name 'postgresql.conf' -print turned up empty. Before that it seemed that multiple instances of postgresql were installed.

You might have similar when you see as installed, or dependency problems listing

.../postgresql
.../postgresql-9.x

and so on.

In that case you must sudo apt-get autoremove each package 1 by 1.

Then following this to the letter and you will be fine. Especially when it comes to key importing and adding to source list FIRST

sudo apt-get update && sudo apt-get -y install python-software-properties && wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

If not using wily, replace wily with your release, i.e with the output of lsb_release -cs

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'
sudo apt-get update && sudo apt-get install postgresql-9.3 pgadmin3

And then you should be fine and be able to connect and create users.

Expected output:

Creating new cluster 9.3/main ...
config /etc/postgresql/9.3/main
data   /var/lib/postgresql/9.3/main
locale en_US.UTF-8
socket /var/run/postgresql
port   5432

Source of my solutions (credits)

Zanna♦

68.3k55 gold badges210 silver badges320 bronze badges

answered Feb 5, 2016 at 16:43

While having the same issue I tried something different:

Starting the postgresql daemon manually I got:

FATAL:  could not create shared memory segment ...
   To reduce the request size (currently 57237504 bytes), reduce PostgreSQL's
   shared memory usage, perhaps by reducing shared_buffers or max_connections.

So what I did was to set a lower limit for shared_buffers and max_connections into postgresql.conf and restart the service.

This fixed the problem!

Here’s the full error log:

$ sudo service postgresql start
 * Starting PostgreSQL 9.1 database server                                                                                                                                                               * The PostgreSQL server failed to start. Please check the log output:
2013-06-26 15:05:11 CEST FATAL:  could not create shared memory segment: Invalid argument
2013-06-26 15:05:11 CEST DETAIL:  Failed system call was shmget(key=5432001, size=57237504, 03600).
2013-06-26 15:05:11 CEST HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMMAX.  To reduce the request size (currently 57237504 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
    If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
    The PostgreSQL documentation contains more information about shared memory configuration.

Zanna♦

68.3k55 gold badges210 silver badges320 bronze badges

answered Jun 26, 2013 at 13:29

After many exhausting attempts, I found the solution based on other posts!

dpkg -l | grep postgres
apt-get --purge remove <package-founded-1> <package-founded-2>
whereis postgres
whereis postgresql
sudo rm -rf <paths-founded>
sudo userdel -f postgres

Kevin Bowen

19.2k55 gold badges75 silver badges81 bronze badges

answered Apr 22, 2019 at 23:46

Check the status of postgresql:
```
service postgresql status
```
If it shows online, proceed to step no 3 else execute step no 2.
To make postgresql online, execute the following command:
```
sudo service postgresql start
```
Now check the status by running the command of the previous step. It should show online.
To start psql session, execute the following command:
```
sudo su postreg
```
Finally, check if it’s working or not by executing:
```
psql
```

answered May 29, 2021 at 12:21

Restart postgresql by using the command

sudo /opt/bitnami/ctlscript.sh restart postgresql

answered Apr 26, 2022 at 9:41

This error could mean a lot of different things.

In my case, I was running PostgreSQL 12 on a virtual machine.

I had changed the shared_buffer config and apparently, the system administrator edited the memory config for the virtual machine reducing the RAM allocation from where it was to below what I had set for the shared_buffer.

I figured that out by looking at the log in

/var/log/postgresql/postgresql-12-main.log

and after that I restarted the service using

sudo systemctl restart postgresql.service

that’s how it worked

answered Jun 30, 2022 at 9:47

Create postgresql directory inside run and then run the following command.

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

answered Apr 26, 2019 at 5:35

Simply add /tmp unix_socket_directories

postgresql.conf

unix_socket_directories = '/var/run/postgresql,/tmp'

answered Jun 17, 2019 at 1:00

I had this problem with another port. The problem was, that I had a system variable in /etc/environments with the following value:

PGPORT=54420

As I removed it (and restarted), psql was able to connect.

answered Jul 18, 2021 at 14:46

BevorBevor

4908 silver badges17 bronze badges

Источник

Before anyone can access the database, you must start the database server. The database server program is called postgres.

If you are using a pre-packaged version of PostgreSQL, it almost certainly includes provisions for running the server as a background task according to the conventions of your operating system. Using the package’s infrastructure to start the server will be much less work than figuring out how to do this yourself. Consult the package-level documentation for details.

The bare-bones way to start the server manually is just to invoke postgres directly, specifying the location of the data directory with the -D option, for example:

$ postgres -D /usr/local/pgsql/data

which will leave the server running in the foreground. This must be done while logged into the PostgreSQL user account. Without -D, the server will try to use the data directory named by the environment variable PGDATA. If that variable is not provided either, it will fail.

Normally it is better to start postgres in the background. For this, use the usual Unix shell syntax:

$ postgres -D /usr/local/pgsql/data >logfile 2>&1 &

It is important to store the server’s stdout and stderr output somewhere, as shown above. It will help for auditing purposes and to diagnose problems. (See Section 25.3 for a more thorough discussion of log file handling.)

The postgres program also takes a number of other command-line options. For more information, see the postgres reference page and Chapter 20 below.

This shell syntax can get tedious quickly. Therefore the wrapper program pg_ctl is provided to simplify some tasks. For example:

pg_ctl start -l logfile

will start the server in the background and put the output into the named log file. The -D option has the same meaning here as for postgres. pg_ctl is also capable of stopping the server.

Normally, you will want to start the database server when the computer boots. Autostart scripts are operating-system-specific. There are a few example scripts distributed with PostgreSQL in the contrib/start-scripts directory. Installing one will require root privileges.

Different systems have different conventions for starting up daemons at boot time. Many systems have a file /etc/rc.local or /etc/rc.d/rc.local. Others use init.d or rc.d directories. Whatever you do, the server must be run by the PostgreSQL user account and not by root or any other user. Therefore you probably should form your commands using su postgres -c '...'. For example:

su postgres -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog'

Here are a few more operating-system-specific suggestions. (In each case be sure to use the proper installation directory and user name where we show generic values.)

For FreeBSD, look at the file contrib/start-scripts/freebsd in the PostgreSQL source distribution.

On OpenBSD, add the following lines to the file /etc/rc.local:

if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postgres ]; then
    su -l postgres -c '/usr/local/pgsql/bin/pg_ctl start -s -l /var/postgresql/log -D /usr/local/pgsql/data'
    echo -n ' postgresql'
fi

On Linux systems either add
```
/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
```
to /etc/rc.d/rc.local or /etc/rc.local or look at the file contrib/start-scripts/linux in the PostgreSQL source distribution.

When using systemd, you can use the following service unit file (e.g., at /etc/systemd/system/postgresql.service):
```
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=infinity

[Install]
WantedBy=multi-user.target
```
Using Type=notify requires that the server binary was built with configure --with-systemd.

Consider carefully the timeout setting. systemd has a default timeout of 90 seconds as of this writing and will kill a process that does not report readiness within that time. But a PostgreSQL server that might have to perform crash recovery at startup could take much longer to become ready. The suggested value of infinity disables the timeout logic.
On NetBSD, use either the FreeBSD or Linux start scripts, depending on preference.
On Solaris, create a file called /etc/init.d/postgresql that contains the following line:
```
su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
```
Then, create a symbolic link to it in /etc/rc3.d as S99postgresql.

While the server is running, its PID is stored in the file postmaster.pid in the data directory. This is used to prevent multiple server instances from running in the same data directory and can also be used for shutting down the server.

19.3.1. Server Start-up Failures

There are several common reasons the server might fail to start. Check the server’s log file, or start it by hand (without redirecting standard output or standard error) and see what error messages appear. Below we explain some of the most common error messages in more detail.

LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
FATAL:  could not create any TCP/IP sockets

This usually means just what it suggests: you tried to start another server on the same port where one is already running. However, if the kernel error message is not Address already in use or some variant of that, there might be a different problem. For example, trying to start a server on a reserved port number might draw something like:

$ postgres -p 666
LOG:  could not bind IPv4 address "127.0.0.1": Permission denied
HINT:  Is another postmaster already running on port 666? If not, wait a few seconds and retry.
FATAL:  could not create any TCP/IP sockets

A message like:

FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=5440001, size=4011376640, 03600).

probably means your kernel’s limit on the size of shared memory is smaller than the work area PostgreSQL is trying to create (4011376640 bytes in this example). This is only likely to happen if you have set shared_memory_type to sysv. In that case, you can try starting the server with a smaller-than-normal number of buffers (shared_buffers), or reconfigure your kernel to increase the allowed shared memory size. You might also see this message when trying to start multiple servers on the same machine, if their total space requested exceeds the kernel limit.

An error like:

FATAL:  could not create semaphores: No space left on device
DETAIL:  Failed system call was semget(5440126, 17, 03600).

does not mean you’ve run out of disk space. It means your kernel’s limit on the number of System V semaphores is smaller than the number PostgreSQL wants to create. As above, you might be able to work around the problem by starting the server with a reduced number of allowed connections (max_connections), but you’ll eventually want to increase the kernel limit.

Details about configuring System V IPC facilities are given in Section 19.4.1.

19.3.2. Client Connection Problems

Although the error conditions possible on the client side are quite varied and application-dependent, a few of them might be directly related to how the server was started. Conditions other than those shown below should be documented with the respective client application.

psql: error: connection to server at "server.joe.com" (123.123.123.123), port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

This is the generic “I couldn’t find a server to talk to” failure. It looks like the above when TCP/IP communication is attempted. A common mistake is to forget to configure the server to allow TCP/IP connections.

Alternatively, you might get this when attempting Unix-domain socket communication to a local server:

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

If the server is indeed running, check that the client’s idea of the socket path (here /tmp) agrees with the server’s unix_socket_directories setting.

A connection failure message always shows the server address or socket path name, which is useful in verifying that the client is trying to connect to the right place. If there is in fact no server listening there, the kernel error message will typically be either Connection refused or No such file or directory, as illustrated. (It is important to realize that Connection refused in this context does not mean that the server got your connection request and rejected it. That case will produce a different message, as shown in Section 21.15.) Other error messages such as Connection timed out might indicate more fundamental problems, like lack of network connectivity, or a firewall blocking the connection.

Источник

I installed the Bitnami Django stack which included PostgreSQL 8.4.

When I run psql -U postgres I get the following 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"?

PG is definitely running and the pg_hba.conf file looks like this:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

What gives?

«Proof» that pg is running:

root@assaf-desktop:/home/assaf# ps axf | grep postgres
14338 ?        S      0:00 /opt/djangostack-1.3-0/postgresql/bin/postgres -D /opt/djangostack-1.3-0/postgresql/data -p 5432
14347 ?        Ss     0:00  _ postgres: writer process                                                                        
14348 ?        Ss     0:00  _ postgres: wal writer process                                                                    
14349 ?        Ss     0:00  _ postgres: autovacuum launcher process                                                           
14350 ?        Ss     0:00  _ postgres: stats collector process                                                               
15139 pts/1    S+     0:00              _ grep --color=auto postgres
root@assaf-desktop:/home/assaf# netstat -nltp | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      14338/postgres  
tcp6       0      0 ::1:5432                :::*                    LISTEN      14338/postgres  
root@assaf-desktop:/home/assaf#

asked Jun 26, 2011 at 12:28

I use following workaround so both clients should be happy:

sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

answered Nov 15, 2011 at 14:25

JaakLJaakL

3112 silver badges6 bronze badges

At a guess you’re using the system version of the psql command, which will look for the postgres unix domain sockets in /var/run/postgresql and the third party postgres you are using has been configured to put them somewhere else.

The easiest solution is probably to use /opt/djangostack-1.3-0/postgresql/bin/psql instead, assuming that there is one, as it will presumably look in the correct place for the unix sockets.

Otherwise, you need to look at the unix_socket_directory setting in postgresql.conf but it’s quite likely that will be commented out and it is using a compiled in default.

answered Jun 26, 2011 at 14:42

TomHTomH

1,2907 silver badges9 bronze badges

The error message refers to a Unix-domain socket, so you need to tweak your netstat invocation to not exclude them. So try it without the option -t:

netstat -nlp | grep 5432

Possible workarounds:

Use the clients supplied by your third-party package (call /opt/djangostack-1.3-0/postgresql/bin/psql). Possibly deinstall the Ubuntu-supplied packages altogether (might be difficult because of other reverse dependencies).
Fix the socket directory of the third-party package to be compatible with Debian/Ubuntu.
Use -h localhost to connect via TCP/IP instead.
Use -h /tmp or equivalent PGHOST setting to point to the right directory.
Don’t use third-party packages.

answered Jun 27, 2011 at 17:39

Peter EisentrautPeter Eisentraut

3,6351 gold badge23 silver badges21 bronze badges

Restart postgresql by using the command

sudo /opt/bitnami/ctlscript.sh restart postgresql

This worked for me

answered Apr 26, 2022 at 9:44

Источник

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

How I fixed it (mac)

Step1: Running pg_lsclusters will list all the postgres clusters running on your device

Step 2: Restart the pg_ctlcluster

Step 3: Step 2 failed and threw error

Step 4: check ownership of postgres

Step 5: Check postgres user belongs to ssl-cert user group

19.3.1. Server Start-up Failures

19.3.2. Client Connection Problems

Step1: Running `pg_lsclusters` will list all the postgres clusters running on your device