32

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?

1 Answer 1

62

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.

2
  • what is res_for_super and res_for_normal means ?
    – DBA100
    Dec 2, 2021 at 8:44
  • @DBA100, based on my tests, res_for_super means how many connections remain for superuser (postgres and others) and res_for_normal same but for regular users. Dec 15, 2021 at 17:56

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.