如何让活动用户通过 SQL 连接到 postgreSQL 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/464623/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can you get the active users connected to a postgreSQL database via SQL?
提问by Johan Bresler
How can you get the active users connected to a postgreSQL database via SQL? This could be the userid's or number of users.
如何让活动用户通过 SQL 连接到 postgreSQL 数据库？这可能是用户 ID 或用户数量。
回答by balexandre
(question) Don't you get that info in
（问题）你没有得到这些信息吗
select * from pg_user;
select * from pg_user;
or using the view pg_stat_activity:
或使用视图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.
每个服务器进程一行，显示数据库 OID、数据库名称、进程 ID、用户 OID、用户名、当前查询、查询的等待状态、当前查询开始执行的时间、进程启动的时间以及客户端的地址和端口号。除非参数 stats_command_string 已关闭，否则报告当前查询数据的列是可用的。此外，仅当检查视图的用户是超级用户或与拥有所报告进程的用户相同时，这些列才可见。
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...
你不能过滤并获取这些信息吗？这将是数据库上的当前用户，您可以使用开始执行时间来获取最近 5 分钟的所有查询，例如...
something like that.
类似的东西。
回答by Sven Lilienthal
Using balexandre's info:
使用 balexandre 的信息：
SELECT usesysid, usename FROM pg_stat_activity;
回答by Tom Hundt
OP asked for users connected to a particular database:
OP 询问连接到特定数据库的用户：
-- 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 --psqldoes :-) - 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)
- 用户 ID（列
usesysid） - 用户名 (
usename) - 客户端应用程序名称 (
appname)，如果它麻烦设置该变量 -psql做 :-) - IP 地址 (
client_addr) - 它处于什么状态（与状态和等待状态相关的几列）
- 以及每个人的最爱，当前正在运行的 SQL 命令 (
query)
