0

Our production server is running PostgreSQL v8.2.3 on Red Hat Enterprise Linux Server release 5 (Tikanga).

For some reason, statistics collector in PostgreSQL is not working.

Even posted this question in PostgreSQL mailing list here: Statistics Collector not collecting server activities

Reasons they say that would cause not to start the statistics collector are:

  1. OS firewall
  2. anti-virus program running on this machine
  3. any other unusual security features, like SE-Linux
  4. problem in creating UDP sockets

We don't have any anti-virus program running or SE-Linux enabled in the server.

By now, I'm also sure that in my case it is because of the 4th reason, that is, problem in creating UDP sockets.

I'm not an expert into Linux iptables. Is there anything that I need to allow/remove in the iptables to allow to create UDP sockets.

NOTE: Database runs as "postgres" user.

Here is my iptables entry:

[root@myserver ~]# more /etc/sysconfig/iptables
# Generated by iptables-save v1.3.5 on Mon Sep 14 20:04:30 2009
*nat
:PREROUTING ACCEPT [10934:1556118]
:POSTROUTING ACCEPT [0:0]
:OUTPUT ACCEPT [111392:6686084]
-A PREROUTING -p tcp -m tcp --dport 80 -j REDIRECT --to-ports 5050
-A POSTROUTING -j MASQUERADE
-A OUTPUT -d 192.168.0.200 -p tcp -m tcp --dport 80 -j DNAT --to-destination 192.168.0.200:5050
-A OUTPUT -d 127.0.0.1 -p tcp -m tcp --dport 80 -j DNAT --to-destination 127.0.0.1:5050
COMMIT
# Completed on Mon Sep 14 20:04:30 2009
# Generated by iptables-save v1.3.5 on Mon Sep 14 20:04:30 2009
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -j ACCEPT
-A FORWARD -j ACCEPT
-A OUTPUT -j ACCEPT
COMMIT
# Completed on Mon Sep 14 20:04:30 2009

Experts advice to resolve this statistics collector starting problem are highly appreciated.

UPDATE: From the day one we had this problem, we had a slight suspicion on iptables firewall. So we finally decided to disable iptables firewall in the server and give it a try. So, if I totally disable/stop iptables firewall by executing service iptables stop and then if I restart PostgreSQL service, Voila!! PostgreSQL statistics collector is working fine with iptables firewall disabled/stopped.

Gnanam
  • 1,439
  • 13
  • 26
  • 32
  • PostgreSQL v8.2.3? You're 14 patches behind, over 3 years without any maintenance... The most recent version of the 8.2 branch, is version 8.2.17. You have to do some maintenance, many bugs have been fixed the last couple of years. It might solve your current problem as well. – Frank Heikens Aug 03 '10 at 11:04
  • @Frank We're running the same kind of server setup in another 5 to 10 customer places, but we don't have this sort of problem in any other customer server setups. Though, I'll also consider your point on upgrading to the latest version in PostgreSQL 8.2 series. – Gnanam Aug 03 '10 at 11:58

2 Answers2

0

I wonder how this could be iptables related, since by default everything for you seems to be in ACCEPT state. Does the PostgreSQL server happen to have that 192.168.0.200 IP address? Although anything related to statistics collector should not be running on port 80 ...

Are you absolutely, positively sure there's not something wrong with your PostgreSQL config? As a next thing I would check that out.

Janne Pikkarainen
  • 31,454
  • 4
  • 56
  • 78
  • @Janne Actually, as the first thing, I've completely checked with PostgreSQL config parameters and I don't find any issues over there. Then I realized that there is something restricting at the OS level. Yes, you're right my PostgreSQL server is running on `192.168.0.200` IP address. In this case, how do I make sure that 'postgres' user is able to create UDP sockets? Is there any command that I can execute to verify/confirm this? – Gnanam Aug 03 '10 at 10:14
  • 1
    Change yourself to postgres user with su - postgres and then try nc -u somehostyoutrytoconnect portnumber – Janne Pikkarainen Aug 03 '10 at 10:18
  • @Janne Even I tried out the command here which was asked by me but in vain: http://serverfault.com/questions/131518/how-to-relax-firewall-for-udp-connections-ports-for-a-specific-ip-address – Gnanam Aug 03 '10 at 10:19
  • @Janne Since this `nc` command is very new for me, what is the output expected from this command? I tried to execute `nc -u 192.168.0.200 35000` as 'postgres' user, it neither throws any error nor returns back to the command prompt. Let me know how do you want me to check this out further? – Gnanam Aug 03 '10 at 10:29
  • So... the nc command I presented to you tells what? – Janne Pikkarainen Aug 03 '10 at 10:30
  • I'm also seeing the following CAVEAT at the bottom of `man nc` page: `UDP port scans will always succeed (i.e. report the port as open), rendering the -uz combination of flags relatively useless.` – Gnanam Aug 03 '10 at 10:36
  • @Janne. FYI. We're running both database and web server in the same server. Port 80 request is configured in iptables to redirect to 5050 port, because our web server is running on port 5050 as non-root user. – Gnanam Aug 03 '10 at 10:45
  • @Janne Check my current update at the bottom of this question. PostgreSQL statistics collector is working fine with iptables firewall disabled/stopped. So, it is something to do with the entries in `/etc/sysconfig/iptables` shown above. – Gnanam Aug 04 '10 at 04:15
0

Found and resolved the problem in creating UDP sockets. It was because of the OS firewall (iptables) restricting in creating UDP sockets.

I completely flushed/cleared all the existing iptables rules and then restarted iptables service. It's working now and the PostgreSQL statistics collector has also started working as expected.

See a Q&A posted by me related to this: iptables command to clear all existing rules

Community
  • 1
Gnanam
  • 1,439
  • 13
  • 26
  • 32