CODEWITHSUNDEEP
sqlpostgresqlactive-users
Johan Bresler
Johan Bresler

Reputation: 6508

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

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

Upvotes: 112

Views: 142132

Answers (3)

Tom Hundt
Tom Hundt

Reputation: 1850

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)

Upvotes: 14

balexandre
balexandre

Reputation: 75103

(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.

Upvotes: 140

Sven Lilienthal
Sven Lilienthal

Reputation: 6504

Using balexandre's info:

SELECT usesysid, usename FROM pg_stat_activity;

Upvotes: 42

Related Questions