7

I have an error with PostgreSQL:

SQLSTATE[08006] [7] could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

The following command doesn't show 5432 port is open:

sudo netstat -plunt |grep postgres

I guess that the problem is 5432 port so as I don't see it at netstat.
How to open port for PostgreSQL?

  1. The postgresql.conf and pg_hba.conf files are okay (see below).
  2. postgres server is running
  3. My postgresql.conf file:
listen_addresses = '*'
port = 5432
  1. My pg_hba.conf file:
local   all             postgres                                md5
local   all             all                                     md5
host    all             all             0.0.0.0/0               trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
  1. I ran the command: $ netstat -atu
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 localhost:6379          0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:http            0.0.0.0:*               LISTEN
tcp        0      0 localhost:domain        0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:ssh             0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:https           0.0.0.0:*               LISTEN
tcp        0      0 30secondboomer.com:ssh  109-252-90-59.nat:11807 ESTABLISHED
tcp        0      0 30secondboomer.com:ssh  109-252-90-59.nat:11258 ESTABLISHED
tcp        0      0 30secondboomer.com:ssh  109-252-90-59.nat:11797 ESTABLISHED
tcp6       0      0 localhost:6379          [::]:*                  LISTEN
tcp6       0      0 [::]:http               [::]:*                  LISTEN
tcp6       0      0 [::]:ssh                [::]:*                  LISTEN
tcp6       0      0 [::]:https              [::]:*                  LISTEN
udp        0      0 localhost:domain        0.0.0.0:*
udp        0      0 30secondboomer.c:bootpc 0.0.0.0:*
cokeman19
  • 2,405
  • 1
  • 25
  • 40
Андрей Писарев
  • 151
  • 1
  • 2
  • 9
  • 3
    Please check the logs. The PostgreSQL server obviously (netstat output) is not running. – Ancoron Mar 24 '19 at 18:06
  • I use command "sudo service postgresql status" ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Sun 2019-03-24 21:01:12 MSK; 6min ago Process: 24974 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 24974 (code=exited, status=0/SUCCESS) Mar 24 21:01:12 cs451508 systemd[1]: Starting PostgreSQL RDBMS... Mar 24 21:01:12 cs451508 systemd[1]: Started PostgreSQL RDBMS. – Андрей Писарев Mar 24 '19 at 18:08
  • 1
    Localhost should be open to itself. Are you trying to jump between servers on that port? Or are you connecting on same server to the database? –  Mar 24 '19 at 18:13
  • On the same server – Андрей Писарев Mar 24 '19 at 18:15
  • It seems that server is not running. Below is PostgreSQL log: FATAL: pg_hba.conf rejects connection for host "109.252.90.59" LOG: received fast shutdown request LOG: aborting any active transactions LOG: background worker "logical replication launcher" (PID 421) exited with exit code 1 LOG: shutting down – Андрей Писарев Mar 24 '19 at 18:27
  • LOG: database system is shut down pg_ctl: another server might be running; trying to start server anyway FATAL: could not remove old lock file "postmaster.pid": Permission denied HINT: The file seems accidentally left over, but it could not be removed. Please remove the file by ha$ pg_ctl: could not start server – Андрей Писарев Mar 24 '19 at 18:27
  • 1
    @АндрейПисарев: `postgresql.service` is an umbrella service whose status does not prove anything. The actual service is probably `postgresql@11-main`. You may have multiple instances of postgres managed by systemd depending on your OS. – Daniel Vérité Mar 24 '19 at 18:34
  • I have only one instance and log above from postgresql-11-main.log – Андрей Писарев Mar 24 '19 at 18:37
  • I run command "service postgresql@11-main status" and got ● postgresql@11-main.service - PostgreSQL Cluster 11-main Loaded: loaded (/lib/systemd/system/postgresql@.service; indirect; vendor preset: enabled) Active: failed (Result: protocol) since Sun 2019-03-24 21:23:14 MSK; 16min ago Process: 329 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 11-main start (code=exited, status=1/FAILURE) Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable. – Андрей Писарев Mar 24 '19 at 18:40
  • Before that I ran command "service postgresql restart", "service postgresql stop", "service postgresql start" – Андрей Писарев Mar 24 '19 at 18:42
  • I found an answer by grant rigths "chmod u+w /var/lib/postgresql/11/main/" – Андрей Писарев Mar 24 '19 at 19:16
  • Follow this: https://stackoverflow.com/a/74163070/14591547 – abdulalim Oct 22 '22 at 11:20

2 Answers2

3

Got a similar error while settings up PostgreSQL to work with a Python/Django project.

  1. Try to stop and restart the server again.
  2. If indeed the challenge is port 5432 as you suspect, you can try to start postgres on another port say 5433 and see what happens.
  3. For those, whose database server is not running at all, this solution worked for me, after reading the official PostgreSQL-9.6.16 documentation.
MwamiTovi
  • 2,425
  • 17
  • 25
-2

In your pg_hba.conf, you have :

host all all 127.0.0.1/32 trust

But this should probably contain the actual IP address of the machine from which the requests are coming (even if it is localhost, it should still contain the remote IP address as a connection from another machine would refer to it.

Mark Hickey
  • 1