Monitoring number of postgres connections

hi roycrom,

If you set the maximun number of connections in postgreSQL.conf to 200, the vaild connection handles in this scenario would be any number from one to two-hundred.

Set up a loop and make connection to postgres until postgres returns -1 indicating there are no more connections. At each successful connection, save the connection handle to a file, memory array, database, etc.

Then have the script count the number of connections, and this would be the number of open connection prior to the start of your program.

Be sure and run another loop to issue a sqldisconnect(i), where i is the number of the connection handle postgres issued you. This will free the connections again. The script/app should run very quickly, so postgres should not be out of connections more than a few seconds at most.

LelandJ Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox

Hi roycrom,

I've been thinking about withing a little script to do this for some time, so I when ahead with it tonight.

This is a Visual FoxPro Script. I run a loop making connections until postgres run out of connections. This give me the number of open connections just prior to running the program. Then I immediately disconnect from all connections opened during the session. It take about three seconds to run. My postgres database is set to a maximum of 32 connections.

LelandJ

****** Beginning of Visual FoxPro App ************8

mystring="DATABASE=vfp2pg;SERVER= port=5432;UID=VfP2Pg;pwd=HelloWorld;provider=MSDASQLR;DRIVER=postgreSQL;"

Public Array used_conn(32)

count=0

FOR i = 1 TO 32

oConn=SQLSTRINGCONNECT(mystring)

used_conn(i)=oConn

if type( [used_conn(i)] ) = 'N' .and. !used_conn(i) < 1
count=count+1
endif

endfor


for i = 1 to (count)

SQLDISCONNECT( used_conn(i) ) && release connection assigned this session

endfor


wait window &quot;There are &quot;+str(count)+&quot; connections open.&quot;+chr(13)+;
&quot;Press any key to Return&quot;

RETURN

******** End of App **************** Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox

Thanks Leland,

Unfortunately I don't use FoxPro but you've set me on the right track and I'm going to see if I can come up with a php script that does the same thing, you've given me the mechanics of the script so shouldn't be too difficult. If I'm successful I'll post it here. Thanks again. ------------------------------------------
Thanx | your welcome !!

roycrom

Hi roycrom,

I am a member of many of the postgres mailing lists. This morning I came across what would be a better solution for us in monitoring connections. You can query one of the system tables like:

select * from pg_stat_activity;

The rows provided are

datid datname procpid userID username current query
----- ------- ------- ------ -------- -------------

The info held in the rows are:

datid: database's id number
datname: database's name
procpid: Process id
userID: user's id
username: user's name


I suspected there was a better way, but didn't discover it until this morning.

Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox

Hi,

I was playing around with RedHat Linux 8.0. I decided to
take a look at pgaccess which is the linux postgres DB Admin tool. It has been greatly improved. Under the RedHat linux 8.0, pgaccess now has a pgmonitor menu option. When clicking on the pgmonitor menu option, a window is opened that show connections to postgres databases/tables. Each connection is represented by a row in the monitor window. Some of the information provided in each row is the user name used to make the connection, to which database the user connected, whether the connection is local or remote, whether a queries are idle, percentage of cpu used, percentage of memory used, process ID number, and the time at which the connection was initialed.

Also, user connections can be terminated using this tool. There is a button that when click allow the rows in the monitor to be queried. This make for a very interesting tool.

Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox

Thanks for all the suggestions leland,

I've been trying to get pgaccess going on 7.3 but I'm having trouble compiling tk. Oh well, its one thing after another. I'm going to get RH8 on anyway, as its about time I upgraded.

Once again thanks for the help. ------------------------------------------
Thanx | your welcome !!

roycrom