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,529
  • 13
  • 27
  • 32

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
  • 32,512
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,529
  • 13
  • 27
  • 32