Count unique active users per database Postgresql

Question

This is a multi tenant question. I want to check how many users connected per database.

This query is the closest I have come:

SELECT datname, count(usesysid) as users
FROM pg_stat_activity
GROUP BY 1

But it seems not to be correct as the postgres db has 2 users and only 1 is logged in.

Is there any other way to figure out this?

It seems that every query in PGAdmin uses another process and acts like another user. So I can count processes but not "active users":

The usesysid are the same for all postgres users. Last time I checked it was 4 postgres with usesysid 10 — sibert
– sibert, Commented Mar 16, 2016 at 11:39
10;"postgres" 10;"postgres" (the number of "postgres" differ from session to session) — sibert
– sibert, Commented Mar 16, 2016 at 13:00

Trung Huynh · Accepted Answer · 2016-03-18 03:42:11Z

3

If I understand your question correctly which is "Count unique active users per database Postgresql", I would query

SELECT datname, usename, COUNT(*)
  FROM pg_stat_activity
  GROUP BY datname, usename;

edited Mar 18, 2016 at 3:42

answered Mar 17, 2016 at 10:27

Trung Huynh

791 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sibert Over a year ago

All users from same company share the same role (usename), so it do not tells me how many users on each company that are logged in.

Trung Huynh Over a year ago

So, if all users from a same company share the same usename so you'll need to add the dimension usename to the aggregation. I updated the query to reflex this. Let me know if this answer your question.

sibert Over a year ago

This gives the exact same result as my original query. I cannot figure out why there is 2 postgres users, because I am the only admin logged in as postgres.

Trung Huynh Over a year ago

Could you please edit your question and add the result of your query, my query and your expected result. Also, the result of SELECT * FROM pg_stat_activity WHERE usename = 'postgres' will be useful. The question why you see 2 users postgres even if you're the only one who connect have many possible answers. By taking a look to the columns application_name, client_addr, state,.. in the result of the above query, you might find the answer.

sibert Over a year ago

Done. It seems that I cannot count logged in users. Only open processes.

Collectives™ on Stack Overflow

Count unique active users per database Postgresql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related