PostgreSQL实时查看数据库实例正在执行的SQL语句实例详解

在PostgreSQL数据库中，我们可以实时查看当前正在执行的SQL语句，以帮助我们定位和解决一些性能问题或死锁问题，本文将提供一种详细的攻略来完成这个任务。

步骤1：连接到PostgreSQL数据库

我们首先需要连接到我们的PostgreSQL数据库，使用以下命令登录到PostgreSQL控制台：

psql -U your_username -d your_database_name

在这里，your_username表示我们要连接的数据库用户，your_database_name表示我们要连接的数据库名。

步骤2：使用pg_stat_activity视图查看当前执行的SQL语句

接下来，我们可以使用PostgreSQL内置的pg_stat_activity视图来查询当前正在执行的SQL语句，使用以下命令：

SELECT pid, datname, usename, query_start, query FROM pg_stat_activity WHERE state='active';

这个查询将会返回所有当前正在执行的SQL语句列表，包括进程ID、数据库名、用户名、查询开始时间和查询语句。

步骤3：分析查询结果

查询结果将返回当前活跃进程的查询语句，我们可以分析这些查询来确定哪些查询可能对服务器造成性能问题，或者哪些正在处理复杂任务。

例如，我们可以看到以下执行中的查询：

SELECT COUNT(*) FROM orders WHERE status = 'processing';

这个查询将会导致表orders上锁并扫描整个表，这可能会拖慢其他查询的执行。我们需要进一步分析，优化这个查询以避免过多的锁。

示例1：查找当前执行时间最长的SQL语句

我们可以添加一个ORDER BY子句，按执行时间排序，并找到执行时间最长的SQL语句，使用以下命令：

SELECT pid, datname, usename, query_start, state_change, now() - query_start as duration, query FROM pg_stat_activity WHERE state='active' ORDER BY duration DESC LIMIT 1;

这个查询将会返回最长的查询，包括进程ID、数据库名、用户名、查询开始时间、最后状态更改时间、查询执行时间以及查询语句。我们可以分析这个查询，找出性能问题的根源。

示例2：查找当前正在阻塞其他查询的SQL语句

我们可以查找那些正在阻塞其他查询的SQL语句，使用以下命令：

SELECT
    COALESCE(blocking_pids.pid::text, '<none>') AS blocking_pid,
    blocked_pids.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
FROM
    pg_stat_activity blocked_activity
    JOIN pg_locks blocked_locks ON (
        blocked_activity.pid = blocked_locks.pid
    )
    JOIN pg_stat_activity blocking_activity ON (
        blocking_locks.locktype = blocked_locks.locktype AND
        blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND
        blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND
        blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND
        blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND
        blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND
        blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND
        blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND
        blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND
        blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND
        blocking_locks.pid != blocked_locks.pid
    )
    JOIN pg_locks blocking_locks ON (
        blocking_activity.pid = blocking_locks.pid
    )
    LEFT JOIN LATERAL (
        SELECT pid, query FROM pg_stat_activity
        WHERE pid = blocking_locks.pid
    ) blocking_pids ON true
WHERE
    blocked_locks.granted AND
    NOT blocking_locks.granted;

这个查询将返回被当前阻塞的查询和阻塞查询的详细信息，包括进程ID、查询字符串等。我们可以使用这个查询来找出那些导致死锁或阻塞的查询，并进行应急处理。

至此，我们已经详细讲解了如何实时查看PostgreSQL数据库实例上正在执行的SQL语句，并且提供了两个示例说明。