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.

• scottod: 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.

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.

• Colin 't Hart: Unless you have a user called "process" :-)

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