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?