Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For a monitoring script I need to count the number of open connections to a postgres database. This is done easily enough by opening up a client connection and running a count on the pg_stat_activity table.

However, if the maximum number of connections has already been reached, the connection, and check script, will hang waiting for the connection. I'd prefer to not have this as it complicates the check script and could hang for an indeterminate amount of time. Also, although it is only one connection, opening up a client connection adds to the connection pool which is not ideal.

Looking at the pg_stat_activity table there is a column listing the process id for the connection. This gave me the idea of using the linux command ps to list the current running process and grep out those that are related to protgres. Specifically, the command I am using is:

ps -ef | grep 'postgres' | grep 'root' | grep -v 'grep' | grep -v 'su - postgres'| wc -l

This seems to match the number of entries in pg_stat_activity, but is sometimes, but not always, off by 1 (can be above and below).

Is there anything that might be in the process list that I should include or grep out? Or is there another way to do this without using a db connection?

share|improve this question
    
netstat -anltp | grep -c postgres and subtract 1 –  foibs Jan 30 at 15:28
    
@foibs thanks for the reply. That seems to be off by 5 and at time by 6. –  scottod Jan 30 at 15:39
    
this usually works for me.. how about netstat -anltp | grep ESTABLISHED | grep -c postgres? –  foibs Jan 30 at 16:09
    
that again seems to be 5 or 6 under. However, when querying the pg_stat_activity table, if I ignore the connections with client port -1 then your netstat command will give the correct answer. I assume these connections are counted when postgres checks against the max number of connections. –  scottod Jan 30 at 17:30
1  
The usual solution here is to set superuser_reserved_connections and have the monitoring script connect as superuser. –  Craig Ringer Jan 31 at 0:49
add comment

3 Answers

up vote 0 down vote accepted

The answer above won't necessarily give you the number of open connections since there is no reason you might not start more worker processes than you need at startup.

A safer and more reliable way is to use netstat.

I would start with netstat --numeric-ports | grep 5432 as this will show you active connections. The problem of course is that there may be some false positives. The following should work for most cases though:

netstat --numeric-ports | awk '/(localhost:5432|PGSQL.5432)/ {print $0}' | wc -l
share|improve this answer
    
Although this wasn't used in the end (using netstat has caused issues on our systems before for reasons that are not clear) I am going to set this as the answer due to this being the closest to answering the question and that there have been no other suggestions on how to elimintate false positives. –  scottod Feb 20 at 11:27
add comment

Look at what puts your count out. With ps aux | grep postgres | grep -v grep I see 7 server processes plus one for every open client connection.

USER         PID  %CPU %MEM     VSZ    RSS TT  STAT STARTED         TIME COMMAND
pgsql      11755   0.0  0.1   73420  11568 ??  Is    8:16pm      0:00.00 postgres: logger process    (postgres)
pgsql      11759   0.0  0.2  111404  15808 ??  Is    8:16pm      0:00.03 postgres: checkpointer process    (postgres)
pgsql      11760   0.0  0.4  111404  35888 ??  Ss    8:16pm      0:00.31 postgres: writer process    (postgres)
pgsql      11761   0.0  0.1  111404  11964 ??  Ss    8:16pm      0:00.24 postgres: wal writer process    (postgres)
pgsql      11762   0.0  0.2  115500  14612 ??  Ss    8:16pm      1:36.72 postgres: autovacuum launcher process    (postgres)
pgsql      11763   0.0  0.2   77516  13300 ??  Ss    8:16pm      3:44.84 postgres: stats collector process    (postgres)
pgsql      30021   0.0  0.2  115500  14244 ??  Is    3:50pm      0:00.00 postgres: shane postgres ::1(11365)  (postgres)
pgsql      30455   0.0  0.2  115500  14236 ??  Is    3:56pm      0:00.00 postgres: shane postgres ::1(60588)  (postgres)
pgsql      30884   0.0  0.3  115500  22640 ??  Is    4:04pm      0:00.07 postgres: tinder tinderbox ::1(23149)  (postgres)
pgsql      11754   0.0  0.2  111404  15356 37- S     8:16pm      0:24.92 /usr/local/bin/postgres -D /usr/local/pgsql/data

I'm on FreeBSD and see the processes after the initial server command all start with postgres:, end with (postgres) and the server helpers all have process in their command.

So ps aux | grep postgres: | grep -v grep | grep -v process | wc -l seems to give me the exact figure you are after.

share|improve this answer
    
Unless you have a user called "process" :-) –  Colin 't Hart Jan 31 at 10:21
add comment

In the interests of closing out the question we ended up using the below command (quotation marks escaped as this was used within a perl script). We weren't sure on hos to be exact on this but this gives numbers that were close enough.

'ps -ef | grep \'postgres\' | grep \'root\' | grep -v \'grep\' | grep -cv \'su - postgres\''

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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