×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Monitoring number of postgres connections

Monitoring number of postgres connections

Monitoring number of postgres connections

(OP)
Does anybody know of a way to see or monitor the number of concurrent connections to the postgres database.

I know the default is 32 but I started getting 'too many clients' errors so I have restarted postgres with 200 connections allowed.  I need to know how many actually are connecting so I can increase further or lower a bit.

------------------------------------------
Thanx | your welcome !!

roycrom :)

RE: 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)
https://www.smvfp.com
Nothing Runs Like the Fox

RE: Monitoring number of postgres connections

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=www.smvfp.com; 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 "There are "+str(count)+" connections open."+chr(13)+;
            "Press any key to Return"

RETURN

********  End of App   ****************

Leland F. Jackson, CPA
Software - Master (TM)
https://www.smvfp.com
Nothing Runs Like the Fox

RE: Monitoring number of postgres connections

(OP)
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 :)

RE: Monitoring number of postgres connections

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)
https://www.smvfp.com
Nothing Runs Like the Fox

RE: Monitoring number of postgres connections

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)
https://www.smvfp.com
Nothing Runs Like the Fox

RE: Monitoring number of postgres connections

(OP)
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 :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close