109

I am trying to connect postgresql but I am getting this error.

org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

My pg_hba.conf file is like this.

 TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

I would be much obliged if anyone please be so kind enough to explain whats going on here and how I should correct it.

Raghavendra Singh
  • 103
  • 5
Rose18
  • 2,892
  • 8
  • 47
  • 98

1 Answers1

169

The error you quote has nothing to do with pg_hba.conf; it's failing to connect, not failing to authorize the connection.

Do what the error message says:

Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections

You haven't shown the command that produces the error. Assuming you're connecting on localhost port 5432 (the defaults for a standard PostgreSQL install), then either:

  • PostgreSQL isn't running

  • PostgreSQL isn't listening for TCP/IP connections (listen_addresses in postgresql.conf)

  • PostgreSQL is only listening on IPv4 (0.0.0.0 or 127.0.0.1) and you're connecting on IPv6 (::1) or vice versa. This seems to be an issue on some older Mac OS X versions that have weird IPv6 socket behaviour, and on some older Windows versions.

  • PostgreSQL is listening on a different port to the one you're connecting on

  • (unlikely) there's an iptables rule blocking loopback connections

(If you are not connecting on localhost, it may also be a network firewall that's blocking TCP/IP connections, but I'm guessing you're using the defaults since you didn't say).

So ... check those:

  • ps -f -u postgres should list postgres processes

  • sudo lsof -n -u postgres |grep LISTEN or sudo netstat -ltnp | grep postgres should show the TCP/IP addresses and ports PostgreSQL is listening on

BTW, I think you must be on an old version. On my 9.3 install, the error is rather more detailed:

$ psql -h localhost -p 12345
psql: could not connect to server: Connection refused
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 12345?
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • I am connecting on localhost port 5432. postgresql.conf ->> listen_addresses = '*', I am connecting on IPv4 and my os is Windows 7. – Rose18 Dec 30 '13 at 15:46
  • I am using Postgrsql 9.0 – Rose18 Dec 30 '13 at 15:49
  • 2
    I tried these commands but nothing happend – Rose18 Dec 30 '13 at 16:01
  • How is this not marked as the best answer. It's the only answer I've found that helped me fix the problem. – MrSilent Oct 27 '14 at 13:51
  • $ ps -f -u postgres UID PID PPID C STIME TTY TIME CMD jalal@klein:~/computer_vision/XNAT/xnat/deployments/xnat$ ps auxwww | grep postgres jalal 17998 0.0 0.0 13692 2240 pts/12 S+ 18:16 0:00 grep --color=auto postgres – Mona Jalal Sep 07 '16 at 23:17
  • 11
    I just realized I was connecting to a wrong port *5432* instead of *5433*. Check here `cat /etc/postgresql/9.6/main/postgresql.conf | grep port`. Apartamenty default port changed from version to version. – Janusz Skonieczny Jul 04 '17 at 14:55
  • 2
    @JanuszSkonieczny That's only on Debian/Ubuntu with `pg_wrapper` managed installations. See `pg_lscluster`. – Craig Ringer Jul 05 '17 at 01:36
  • 10
    I think the answer should talk about difference of 127.0.0.1 vs 0.0.0.0 vs localhost and if docker is involved or not between prisma (usually run insider docker container) vs postgres in docker or on native OS. – bjm88 Jul 07 '18 at 04:31
  • @bjm88 Why? Nothing can cover every topic, every virtualisation tool, etc. WTF is "prisma" ? I agree that it's worth addressing container networking to a limited extent, so please add another answer that elaborates accordingly. – Craig Ringer Jul 11 '18 at 05:26
  • 4
    uncommenting and adding `listen_addresses` helped me. Thanks – ilya_direct Aug 29 '19 at 08:32
  • Sometimes the user owner of the log folder changes, this command fixes this issue: sudo chown postgres:postgres /var/log/postgresql/ -R – NELSON RODRIGUEZ Mar 22 '21 at 22:15
  • I did a couple of things go to postgresql.conf, first one is check it out for port number , and then after that check pg_hba.conf file and all of the databases method must be trust instead of md5. When you modify all of things restart your db and try again . Hope it helps =) – Cemalettin Altınsoy Jun 21 '21 at 20:34
  • @ilya_direct Thank you very much for writing that verbosely! I just installed postgres, nuked the server due to the default OS being outdated, and installed postgres on the new OS. Went over everything so quickly I totally forgot to remove the # in the beginning of the line. – UTF-8 Dec 26 '22 at 14:03