postgresql查询登录情况

SELECT
    a.pid AS "进程ID",
    COALESCE(a.client_addr::text, 'local') AS "客户端IP",
    a.usename AS "用户名",
    a.datname AS "数据库名",
    a.application_name AS "应用名称",
    TO_CHAR(a.backend_start, 'YYYY-MM-DD HH24:MI:SS') AS "登录时间",
    TO_CHAR(a.query_start, 'YYYY-MM-DD HH24:MI:SS') AS "查询开始时间",
    CASE 
        WHEN a.state = 'active' 
        THEN EXTRACT(EPOCH FROM (NOW() - a.query_start))::numeric(10,3) || ' 秒'
        ELSE NULL 
    END AS "已执行时长",
    a.wait_event_type || ': ' || a.wait_event AS "等待事件",
    a.state AS "状态",
    LEFT(a.query, 100) AS "SQL语句(前100字符)",
    s.calls AS "执行次数",
    ROUND(s.total_exec_time::numeric, 3) AS "总执行时间(ms)",
    s.rows AS "影响行数",
    pg_size_pretty((s.shared_blks_read + s.local_blks_read) * 8192) AS "读取数据量",
    pg_size_pretty((s.shared_blks_written + s.local_blks_written) * 8192) AS "写入数据量"
FROM pg_stat_activity a
LEFT JOIN pg_stat_statements s 
    ON a.query = s.query 
    AND a.usename::regrole::oid = s.userid
WHERE a.backend_type = 'client backend'
  AND a.pid <> pg_backend_pid()
ORDER BY a.query_start DESC NULLS LAST;

安装和使用 pg_stat_statements 扩展

pg_stat_statements 是 PostgreSQL 的一个核心扩展，用于跟踪服务器执行的所有 SQL 语句的统计信息。

安装步骤

1. 修改 PostgreSQL 配置文件
   编辑 postgresql.conf 文件（通常位于 /etc/postgresql/[版本]/main/ 或 /var/lib/postgresql/data/）：

shared_preload_libraries = ‘pg_stat_statements’ # 添加或修改这一行
pg_stat_statements.track = all # 跟踪所有语句
pg_stat_statements.max = 10000 # 保留的语句数量
track_activity_query_size = 2048 # 增加以捕获更长的查询
2. 重启 PostgreSQL 服务
#根据你的系统选择适当的命令
sudo systemctl restart postgresql
#或
sudo service postgresql restart
3. 在数据库中创建扩展
连接到你的数据库并执行：
CREATE EXTENSION pg_stat_statements;
验证安装
SELECT * FROM pg_stat_statements LIMIT 5;

常用查询
查看最耗时的查询:
SELECT query, total_exec_time, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

查看最频繁执行的查询:
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

重置统计信息:
SELECT pg_stat_statements_reset();

注意事项
该扩展会增加少量性能开销
统计信息不会持久化，重启后会被重置
在高负载生产环境中使用时需谨慎配置参数
如需更详细的统计信息，可以考虑结合其他工具如 pgBadger 或 PostgreSQL 的 EXPLAIN ANALYZE 功能。