12

After many days I am trying to connect to my PostgreSQL instance, I decided time has come to ask for help.

I am trying to connect to my PostgreSQL db from a Windows machine.

I am trying pgAdmin 4 and dBeaver but both fail to connect. Below is the screenshot of the error I receive when connecting using dBeaver.

enter image description here

The connection I am creating is like so:

enter image description here

My users are (\du):

                                 List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 umberto   | Superuser, Create role, Create DB                          | {}

My databases (\l):

                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 umberto   | umberto  | UTF8     | C.UTF-8 | C.UTF-8 |
 wondermap | postgres | UTF8     | C.UTF-8 | C.UTF-8 |

Don't know exactly where to search for logs to dig into this problems on the server machine. The only thing I could find was the folder /var/log/postgresql where I see only two non gzipped files but the messages are referring to days previous to my attempts to connect.

Finally, my pg_hba.conf:

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
host    all             all             ::0/0                   md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    all             all             ::/0                    md5

What could be the problem?

Improve this question
5
  • 1
    I don't think you've explicitly said so, but I take it postgres is running on a different machine than the one you're connecting from? If that's the case then it's most likely a firewall or similar connection issue rather than postgres. If it was rejected by pg_hba.conf settings, you wouldn't get a timeout error. Maybe postgres isn't running or listening on the expected port, but that's less likely than something rejecting connections in between or at the server end. Commented Oct 28, 2019 at 9:45
  • Yes, it's on a different machine. I made sure server-side port 5432 is open. Could it be the client firewall? Commented Oct 28, 2019 at 9:47
  • 1
    If the client = you then no, unless you're blocking outbound connections which would be a bit unusual. From your client you could download portqry from Microsoft then try portqry -n [pg host name] -e 5432 and see what the result is. "Listening" = able to access that port on the server and something (hopefully pg) is listening on that port; "Not listening" = you're able to access that port but nothing is listening on it (could cause timeouts); "Filtered" = access blocked, almost certainly due to a firewall. Commented Oct 28, 2019 at 9:50
  • yep, I got FILTERED. Thanks for your comments I now would be able to resolve my issue. Really appreciate your help, if you want you can post your comments as answer and I'll be happy to accept it. Cheers! Commented Oct 28, 2019 at 9:56
  • Great hopefully it helps you find the exact problem. I've posted it as an answer now. Commented Oct 28, 2019 at 10:14

6 Answers 6

Reset to default
11

I wouldn't generally look at postgres logs for troubleshooting a connection timeout error, because if postgres is rejecting the connections, they'll get rejected right away rather than giving you a timeout, so if you're getting a timeout it typically means it never reached postgres, so there will be nothing relevant in the logs.

In my experience, a connection timeout error is typically due to a windows/networking issue, for example a firewall on (or in front of) the server doesn't allow access on port 5432, or nothing is actually listening on port 5432 (could be that postgres isn't actually running, or it's configured to listen on a different port, etc).

My favourite tool for troubleshooting these sorts of connectivity issues on Windows is portqry. Usage is portqry -n [hostname] -e [port number]. It will try to connect to [hostname] on port [port number] and give you the results:

  • Listening: portqry was able to connect to the host on the specified port, and an application was listening on that port. This is what you want.
  • Not listening: portqry was able to reach the host on the specified port, but nothing was listening on that port. In the case of postgres, this may be because the service isn't running, or is listening on a different port.
  • Filtered: portqry was unable to reach the host on the specified port. This means it was actually blocked from connecting, and this is generally caused by a firewall on the host or in between the client and host, which is preventing access to the host on that port.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

5

In my case, I was trying to load a database from AWS RDS. After whitelisting my IP address, the error was resolved.

Improve this answer

1 Comment

Yes. That resolved it.
1

If you are hosting your pgadmin on the same server as your postgres database, try inserting "localhost" into the server / host connection field during the setup of your pgadmin connection to that database, also try "127.0.0.1". After ruling out all network issues this was what worked for me oddly enough.

Improve this answer

Comments

0

Just to add another solution here. Before this I want to clarify that I'm using Rocky Linux with Xfce for the host and Windows 10 for the client. So I was having this same issue and what I did was:

  • On the host, go to Network (taskbar) > right click > Edit connections
  • Double click on the connection your machine is in.
  • On the "General" tab, check the Firewall zone, and then change it to "trusted" then Save.

Now that solved my problem, hope it helps someone.

Improve this answer

Comments

0

I used TCPView and couldn't find what application is holding the port 5432 yet i had PostgreSQL13 running on 5433 so i disconnected it, then changed the pg17 server port to be 5433 rather than 5432, it connected right away.

Only connect to one server at a time? maybe

Improve this answer

Comments

-1

I got this exact error. The issue was resolved after public accessibility was granted to the AWS database. It can be checked in the AWS console.

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.