Problems connecting to Windows-based Postgresql

Question

Other computers are having trouble even finding the running Postgresql 9.1 on my Windows 7 machine. Local connections work just fine of course.

In my pg_hba.conf I have:

#office subnets
host all all 192.168.##.0/24 md5
host all all 192.168.##.0/24 md5

In postgresql.conf I have (Yes, port 5433 is intentional):

listen_addresses = '*' # what IP address(es) to listen on;
 # comma-separated list of addresses;
 # defaults to 'localhost', '*' = all
 # (change requires restart)
port = 5433 # (change requires restart)

Upon running netstat -a -b in the cmd I find:

 Can not obtain ownership information
 TCP 0.0.0.0:5433 XXXXX-PC:0 LISTENING

Heck, I even made a firewall exception:

Protocols and Ports:
 Local port: Specific Ports, 5433
 Remote port: All Ports
Scope: Any IP Address
Advanced: Profiles: Private, Public
Programs and Services: Apply to this service: postgresql-x64-9.1

The typical response from pgAdmin is always the dreaded:

Server doesn't listen
could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "...." and accepting TCP/IP connections on port 5433?

Any ideas?

• Craig Ringer: That all looks reasonable. pg_hba.conf isn't your issue, since you're not even establishing a successful connection. Consider using wireshark to verify that the packets are actually reaching the server.
• DJStroky: I am able to ping the machine from the other machine and observe that in Wireshark. However, I don't see any tcp.dstport == 5433 or tcp.srcport == 5433 in the records. This absence is also observed when I make a request from pgAdmin to both my local windows machine, but also to another linux server with pg. However, whenever there is a request from the Tomcat on this windows machine it doesn't show for the local pg (even though the query works), but it does show in Wireshark with the linux pg. Very confused.
• Craig Ringer: Router redirect rules or iptables rules remapping things from the client end or midway along? Something not honouring the non-default port you've set?
• Houari: I imagine that you have multiple PostgreSQL servers on this machine (because you have changed the port). Are you sure about the service name configured in the firewall exception?
• user1822: Sounds like a Firewall issue to me.

Answer 1

Here are the steps of troubleshooting I would recommend:

1. From the local machine try the following. If they work you know the issue is not PostgreSQL. If they don't work then you know your issue is local to the machine.

   • psql -h localhost -p 5433
   • psql -h [external ip] -p 5433
   • If the latter fails, try temporarily disabling all firewalls and trying again

2. Now if that works the next step is to troubleshoot the network. Traceroute may be helpful here. Also try connecting to any other open ports using other software and make sure that works. From there, you may be able to figure out exactly where the packets are being dropped.

• FindOutIslamNow: In cmd it says 'psql' in not recognized as in internal or external command.
• Bera: @FindOutIslamNow: stackoverflow.com/questions/30401460/…

Answer 2

Make sure PostgreSQL Server is allowed through the firewall:

On the server machine, go to

Control Panel> System and Security> Windows Firewall> Allow an app through Windows Firewall

Scroll through Allowed apps and features, make sure 'PostgreSQL Server' is checked.

Answer 3

I faced the same problem, it was resolved by turning off my Win Firewall and Antivirus
They were blocking the DB server connection

• András Váczi: Can't you just disable filtering the DB connections?

Answer 4

Add rule in firewall for postgres

Go to "Control Pannel" =>> "Windows Defender Firewall"

Pannel

Firewall

Then Choose "Advanced Settings"(Mostly in Left Hand Side Vertical Options) This will open "Windows Defender Firewall with Advanced Security On Local Computer"

Advance

Then Select "Inbound Rule".

Inbound

Then Click On "New Rule". This will open "New Inbound Rule Wizard".

New Rule

Select "Program" and Click On Next.

Program

Add Program Path of psql.exe. In my case psql.exe is at localtion "C:\Program Files\PostgreSQL9円.6\bin\psql.exe".

Pgm Path

Select "Allow the location".

location

Check all checkbox for whom you want to apply the rule.

Allow For

Give proper name and description for the rule. Then click on finish.

Name

This will add new rule to access psql with localhost. Then try to run pgAdmin or access db with cmd.

Answer 5

For postgresql version 11.8 and postgresql version 12.3 on a Windows 10; the only solution for me was to reinstall.

A broad, elaborated explanation of my experience and solution and all the other topic-solutions I tried:

The general thought/assumptions:

It looks like it is pgAdmin (probably version 4) or your admin rights or one of the files mentioned above (postgresql.conf and/or pg_hba.conf).

I had the same error and even a password error (for the correct password) after being a step further by some todo's and fixes that helped other people in previous versions;

But none of them worked for postgresql version 12.3 and/or postgresql version 11.8

Solution versions:

The only thing that helped after two hours was: As explained in this post: 'PostgreSQL Install fail on Windows 10 (icalcs error/acces denied)' on the superuser forum of StackExchange:

• Uninstall en reinstall postgresql. The official version provided by EDB through this link: postgresql.org/download; Or try a different version if you are sure this one doesn't work for you. Therefor head to the 'File Browser'of Postgresql: here
• After reinstall, start pgAdmin from the Start Menu (scroll for the folder and the correct related pgAdmin; an older version could still hang around in the shortcuts and won't start).

Note: You can keep your data map so nothing is lost!

In my case e.g.: I removed the /data folder from the (Windows 10) [Drive]/Program Files/PostgreSQL/11 and as soon as I uninstalled version 11.2 and reinstalled (earlier removed 12) version 12: when I started pgAdmin4 my former servers ([nameA] and [PostgreSQL 11] and databases ánd the new server [PostgreSQL 12] were there! And the connection was reestablished

Tried:

Earlier tried solutions that did not work in my case:

• Question 37307346 'Is the server running on host localhost 1 and accepting tcp ip connections on port 5432?'
• Question 40532399 'Unable to connect to server for postgres'
• Database Administrators StackExchange: Problems connecting to Windows-based PostgreSQL
• Question 16904997 'Connection refused pgerror postgresql and rails'
• Question 60532791 'Timeout expired pgadmin unable to connect to server' note: This was after semi solution of netstat running postgres service (port was not used before that solution; here listed as 4th item, item above).