Home / server / Questions / 315911
Accepted
alphadogg
Asked: 2011-09-28 03:47:38 +0800 CST

Cannot connect to PostgreSQL DB Via pgAdmin

  • 772

So, I installed a minimal install of Fedora 15 to create a server for a web application. I also installed PostgreSQL 9.1 from pgrpms.org. PostgreSQL install went well. Locally, I was able to initdb, start and psql in to change the postgres password.

Now, I have installed pgAdmin on a Windows system on the same subnet. However, I cannot connect.

I have edited /var/lib/pgsql/9.1/data/postgresql.conf to set listen_addresses = '*'. I have edited /var/lib/pgsql/9.1/data/pg_hba.conf to allow host all all 192.168.1.0/24 trust. I have also restarted after the changes (service postgresql-9.1 restart)

The error in pgAdmin is: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "192.168.1.110" and accepting TCP/IP connections on port 5432?

The answer is yes. I didn't install a firewall, and disabled the one on my Windows-based workstation. I am able to ping and SSH into the server. tcpdump shows the connection attempt on port 5432 from pgAdmin does happen:

[root@cobalion yum.repos.d]# tcpdump port 5432
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
07:28:44.014920 IP totodile.mcs.local.54067 > 192.168.1.110.postgres: Flags [S], seq 3554805012, w in 8192, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0
07:28:47.023859 IP totodile.mcs.local.54067 > 192.168.1.110.postgres: Flags [S], seq 3554805012, w in 8192, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0
07:28:53.019464 IP totodile.mcs.local.54067 > 192.168.1.110.postgres: Flags [S], seq 3554805012, w in 8192, options [mss 1460,nop,nop,sackOK], length 0

I am at a loss to know where to look next? On the surface, seems like I should be able to connect. Any ideas? Can I somehow check from "inside" a running postgresql server what settings were loaded?

postgresql

1 Answers

  1. Best Answer

    Your tcpdump pretty much confirms that iptables is dropping the packets on the server side. tcpdump inspects the packets before they're touched by the firewall. If the firewall was not dropping the packets and postgres wasn't working, the kernel would RST the connection attempt:

    07:39:14.878008 IP localhost.12343 > localhost.35259: Flags [R.], seq 0, ack 1980582372, win 0, length 0

    and you'd immediately get a connection refused rather than a timeout.

    The only other thing I can think of is if you set an absurdly low connection limit on the server and it's already full. I've never tried that to see what would happen, though I have a feeling it would reject connections rather than timing them out based on the behavior of various websites with db errors I run across all the time. You could check postgresql's log to see if it mentions the connection attempt.

    • 0