Find the users pointing to my database postgresql

Question

I logged into the psql console to delete some databases, but it gives me this error:

ERROR: database "production" is being accessed by other users DETAIL: There is 1 other session using the database.

How should I find the users pointing to my database?

Answer 1

You can use the pg_stat_activity view. It will show pretty much everything that is of interest about every open session, such as the user (usename), where s/he is connecting from (client_addr and client_hostname), the state (active, idle, etc) and the last query (being) executed.

Answer 2

You can get it from pg_user table

SELECT * FROM pg_user;

Or from pg_stat_activity view

SELECT usesysid, usename FROM pg_stat_activity;