Получение списка активных пользователей в PostgreSQL: SQL-запрос

Быстрый ответ

Если ваша задача – выявление активных пользователей в базе данных PostgreSQL, то следующий запрос поможет вам в этом:

SELECT usename FROM pg_stat_activity WHERE state = 'active';

Для того чтобы узнать не только имена, но также и адреса и порты активных пользователей, используйте запрос:

SELECT usename, client_addr, client_port FROM pg_stat_activity WHERE state = 'active';

Мониторинг активных сессий важен для оценки производительности, контроля безопасности и общего понимания ситуации в вашей базе данных.

Разбор активных сессий

Что делают пользователи?

Анализ текущей активности в базе данных не ограничивается только установлением факта подключения пользователей. Важно также понимать, что именно эти пользователи совершают. Чтобы получить информацию о состоянии пользователя и его текущем запросе, выполните следующий SQL-запрос:

SELECT usename, state, query FROM pg_stat_activity WHERE state IN ('active', 'idle in transaction');

Примечание: Статус idle in transaction указывает на активную, но не завершённую транзакцию. Это похоже на автомобиль, оставленный включенным без надзора.

Подробнее об этом расскажет наш спикер на видео

Активность по времени

Когда требуется проанализировать действия пользователей за конкретный период, используйте поле query_start:

SELECT usename, query_start, query FROM pg_stat_activity
WHERE query_start >= NOW() – INTERVAL '5 minutes' AND state = 'active';

Доступ к мониторингу

Запомните: не каждый пользователь имеет доступ к pg_stat_activity. Возможно, вам потребуются права суперпользователя для просмотра этих данных.

Визуализация

Для наглядной иллюстрации активных пользователей PostgreSQL рассмотрим аналогию с вечеринкой:

Вечеринка в базе данных 🏢: [🧑‍💻, 🧑‍💼, 👩‍💻, 👩‍💼]

Определите участников вечеринки:

SELECT usename FROM pg_stat_activity;

Кто они и чем занимаются?

🧑‍💻 (Пользователь А) – Занят операциями с данными
🧑‍💼 (Пользователь Б) – Ушел в транзакции у бара
👩‍💻 (Пользователь В) – Получает данные с помощью быстрых запросов
👩‍💼 (Пользователь Г) – Исследует сущности БД в уединении

И вот как выглядит ваша «вечеринка», или скорее, бурная активность в базе данных!

Расширенный анализ сессий

Уровни активности

Состояния active, idle, idle in transaction дают реальное представление о происходящем в базе данных.

Анализ данных о клиентах

Для мониторинга в реальном времени добавьте в SQL-запрос информацию о клиентском приложении, IP-адресе и статусе подключения:

SELECT usename, application_name, client_addr, state FROM pg_stat_activity;

Влияние различных версий

Создавая запросы, не забывайте, что PostgreSQL может иметь разные версии, которые могут требовать специфического подхода.

Более сложные методы

Роли и привилегии пользователей

Важно не только знать имя пользователя, но и понимать его права для корректного управления доступом. Объедините pg_stat_activity и pg_user:

SELECT s.usename, u.usesysid, u.usecreatedb FROM pg_stat_activity s
JOIN pg_user u ON s.usename = u.usename;

Обширные статистические данные

Для детального анализа свяжите pg_stat_activity с pg_locks или pg_stat_statements.

Различные настройки – различные сценарии

Каждая база данных уникальна и отвечает на запросы по-своему в зависимости от настроек и конфигурации. Подгоняйте свой подход под особенности окружения.

Полезные материалы

1. PostgreSQL: документация по pg_stat_activity — интересующий раздел документации для мониторинга.
2. Управление активными подключениями в PostgreSQL на Stack Overflow.
3. Мониторинг PostgreSQL с помощью инструментов Datadog — примечательный обзор для определения активных пользователей.
4. Отмена запросов и завершение сессий на WikiLeaks по PostgreSQL.
5. Список сессий / активные соединения в PostgreSQL — написание SQL-запросов для просмотра сессий.