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
:PREROUTING ACCEPT [10934:1556118]
:OUTPUT ACCEPT [111392:6686084]
-A PREROUTING -p tcp -m tcp --dport 80 -j REDIRECT --to-ports 5050
-A OUTPUT -d -p tcp -m tcp --dport 80 -j DNAT --to-destination
-A OUTPUT -d -p tcp -m tcp --dport 80 -j DNAT --to-destination
# Completed on Mon Sep 14 20:04:30 2009
# Generated by iptables-save v1.3.5 on Mon Sep 14 20:04:30 2009
# 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.

  • 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 3 '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 3 '10 at 11:58

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 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.

| improve this answer | |
  • @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 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 3 '10 at 10:14
  • 1
    Change yourself to postgres user with su - postgres and then try nc -u somehostyoutrytoconnect portnumber – Janne Pikkarainen Aug 3 '10 at 10:18
  • @Janne Even I tried out the command here which was asked by me but in vain: serverfault.com/questions/131518/… – Gnanam Aug 3 '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 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 3 '10 at 10:29
  • So... the nc command I presented to you tells what? – Janne Pikkarainen Aug 3 '10 at 10:30

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.