Как посмотреть выполняемые запросы в PostgreSQL?

Для мониторинга активных запросов в PostgreSQL используйте системное представление pg_stat_activity.

1. Просмотр всех активных запросов

SELECT 
    pid,               -- ID процесса
    usename,           -- Имя пользователя
    application_name,  -- Имя приложения (например, из JDBC-строки)
    client_addr,       -- IP-адрес клиента
    state,             -- Состояние: active, idle, idle in transaction
    query,             -- Текст выполняемого SQL-запроса
    query_start,       -- Время начала выполнения
    now() - query_start AS duration -- Длительность выполнения
FROM pg_stat_activity 
WHERE state = 'active' -- Показывать только выполняющиеся запросы
  AND query !~* '^IDLE' -- Исключить служебные соединения
ORDER BY duration DESC;

2. Поиск долгих запросов (например, выполняющихся > 5 минут)

SELECT pid, query, query_start, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active' 
  AND now() - query_start > interval '5 minutes';

3. Получение информации о блокировках (если запрос "висит")

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks 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_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Графические инструменты:

• pgAdmin: Вкладка "Dashboard" -> "Server Status".
• Расширения: pg_top, pg_activity (устанавливаются отдельно).