24

I would like to get statistics about the peak number of connections over a period of time.

I know the pg_stat_activity view, like select count(*) from pg_stat_activity, but I think this method is not very smart.

Are there other views or tables that can provide the information I need?

44

This SQL will help you

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal 
from 
  (select count(*) used from pg_stat_activity) t1,
  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
  (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3

Result:

max_conn | used | res_for_super | res_for_normal 
---------+------+---------------+----------------
  100    |    2 |             3 |             95
(1 row)

You can put this in shell:

#!/bin/bash
for (( c=1; c<=3600; c++ ))
do
     gsql -U pgdba -W pgdba -p 6432 -c "sql" >> /home/pgdba/res_data.log
     sleep 1  # once per second
done

or you can record the results into a table, then execute

postgres=# copy restbl to '/home/pgdba/res.csv' csv header;

to get result csv file.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.