Counting postgres open connections without SQL query or connecting to postgres itself

Question

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?

Answer 1

A safe and 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

Inspecting the process table (with ps) won't necessarily give you the number of open connections: there is no reason you might not start more worker processes than you need at startup.

Answer 2

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.

Answer 3

In the interests of closing out the question we ended up using the output of the below command. We weren't sure on how 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'

Answer 4

The usual solution here is to set superuser_reserved_connections and have the monitoring script connect as superuser. - https://dba.stackexchange.com/users/7788