How can you get the active users connected to a postgreSQL database via SQL?

Question

How can you get the active users connected to a postgreSQL database via SQL? This could be the userid's or number of users.

balexandre · Accepted Answer · 2016-06-22 14:23:11Z

142

(question) Don't you get that info in

select * from pg_user;

or using the view pg_stat_activity:

select * from pg_stat_activity;

Added:

the view says:

One row per server process, showing database OID, database name, process ID, user OID, user name, current query, query's waiting status, time at which the current query began execution, time at which the process was started, and client's address and port number. The columns that report data on the current query are available unless the parameter stats_command_string has been turned off. Furthermore, these columns are only visible if the user examining the view is a superuser or the same as the user owning the process being reported on.

can't you filter and get that information? that will be the current users on the Database, you can use began execution time to get all queries from last 5 minutes for example...

something like that.

edited Jun 22, 2016 at 14:23

user330315

answered Jan 21, 2009 at 9:50

balexandre

75.4k47 gold badges240 silver badges352 bronze badges

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

4 Comments

Keltia Over a year ago

No, that is the list of known users, not the number currently connected.

balexandre Over a year ago

how about the Stats collector? (follow the link on the view name)

Hao Over a year ago

@mm2010: pg_stat_activity: active users only. pg_user: list all users

Brad Koch Over a year ago

Downvoted, answer wanders a bit and provides some information that might mislead future readers, for example, pg_user is not useful for finding the active users connected to a PostgreSQL database.

Sven Lilienthal · Accepted Answer · 2009-01-21 09:58:02Z

42

Using balexandre's info:

SELECT usesysid, usename FROM pg_stat_activity;

answered Jan 21, 2009 at 9:58

Sven Lilienthal

6,5144 gold badges31 silver badges25 bronze badges

1 Comment

fagiani Over a year ago

You can add client_addr to the above query in order to get client's IP.

Tom Hundt · Accepted Answer · 2019-07-18 05:54:21Z

14

OP asked for users connected to a particular database:

-- Who's currently connected to my_great_database?
SELECT * FROM pg_stat_activity 
  WHERE datname = 'my_great_database';

This gets you all sorts of juicy info (as others have mentioned) such as

userid (column usesysid)
username (usename)
client application name (appname), if it bothers to set that variable -- psql does :-)
IP address (client_addr)
what state it's in (a couple columns related to state and wait status)
and everybody's favorite, the current SQL command being run (query)

answered Jul 18, 2019 at 5:54

Tom Hundt

1,92421 silver badges18 bronze badges

Collectives™ on Stack Overflow

How can you get the active users connected to a postgreSQL database via SQL?

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related