Here is my setup: two CentOS 5.2 boxes on VMWare ESXi 4.0. The first box ip is 192.168.22.52 on eth0 and 192.168.99.1 on eth1. The second box runs PostgreSQL 8.3 with ip 192.168.99.2 on eth0. Here are iptables for box1, for box2 see comment below.
I have set up port 5432 forwarding on box1 and am able to connect to PostgreSQL on box2 via pgAdminIII or psql from Vista notebook (192.168.22.1, there is no other boxes in this subnet, it has its own switch and is physically isolated). The database I am connecting to has two schemas, one is 'smaller' (basically just one table), another one is bigger (some 30 tables, 100 functions, etc.) So I am able to work with the smaller schema (browse the table and so on) but I when I try to expand the bigger schema - pgAdminIII freezes for 20 min or so.
PostgreSQL log shows there is a query which takes way too long:
2009-06-04 21:04:46 EEST LOG: 00000: duration: 493578.874 ms statement: SELECT pr.oid, pr.xmin, pr.*, format_type(TYP.oid, NULL) AS typname, typns.nspname AS typnsp, lanname, proargnames, proconfig, pg_get_userbyid(proowner) as funcowner, description FROM pg_proc pr JOIN pg_type typ ON typ.oid=prorettype JOIN pg_namespace typns ON typns.oid=typ.typnamespace JOIN pg_language lng ON lng.oid=prolang LEFT OUTER JOIN pg_description des ON des.objoid=pr.oid WHERE proisagg = FALSE AND pronamespace = 2200::oid AND typname <> 'trigger' ORDER BY proname
Both box1 and box2 are clones of the development boxes, and the original network structure was different - box2 was directly accessible without port forwarding and there was no problem accessing the databases whatsoever.
Now, if I run the above query via psql on box2 or 'original' machine, or from box1 connecting to box2, it executes immediately.
During the query is run, tcpdump on the box2 periodically says:
12:45:39.770609 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 8760:10220(1460) ack 1 win 54 12:45:39.968496 IP 192.168.22.1.49484 > 192.168.99.2.postgres: . ack 10220 win 16425 12:45:39.968541 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 10220:11680(1460) ack 1 win 54 12:45:39.968574 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 11680:13140(1460) ack 1 win 54 12:45:39.969250 IP 192.168.22.1.49484 > 192.168.99.2.postgres: . ack 13140 win 16425 12:45:39.969275 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 13140:17520(4380) ack 1 win 54 12:45:39.969408 IP 192.168.22.52 > 192.168.99.2: ICMP 192.168.22.1 unreachable - need to frag (mtu 1500), length 556
Other than that, I do not see much traffic. MTU on all ethN interfaces is 1500. ping -l 1472 -f 192.168.99.1 from the notebook goes through without problems.
I suspect that I am missing something about iptables or network setup and would appreciate your advise.