Полезные команды в PostgreSQL

Мы уже писали о командах для безопасного изменения типа поля и работы с конфигурацией в PostgreSQL. В этот раз поговорим о командах, позволяющих получать информацию о базе данных: размере БД и таблиц, перечне таблиц и пользователей, активности пользователей.

Размер БД

Для получения физического размера файлов применяем следующий запрос:

SELECT pg_database_size(current_database());

Результат будет представлен в формате числа вида 41809016. В нашем случае current_database() является функцией, возвращающей имя текущей БД. Вместо неё мы можем ввести имя текстом:

SELECT pg_database_size('my_database');

Если хотим получить информацию в более удобочитаемом виде, нам пригодится функция pg_size_pretty:

SELECT pg_size_pretty(pg_database_size(current_database()));

В итоге получим информацию вида 40 Mb.

Перечень таблиц

Порой, нужно получить перечень таблиц БД. Для этого нам пригодится следующий запрос:

SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','pg_catalog');

Здесь information_schema — это стандартная схема БД, содержащая коллекции представлений (views): таблицы, поля и т. п. Представления таблиц включают информацию о всех таблицах БД.

Следующий запрос выберет все таблицы из указанной схемы текущей БД:

SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
AND table_schema IN('public', 'myschema');

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

Размер таблицы

Как и в случае с получением размера БД, размер данных таблицы мы можем вычислить посредством соответствующей функции:

SELECT pg_relation_size('accounts');

В нашем случае функция pg_relation_size возвращает объём, занимаемый на диске указанным слоем заданной таблицы либо индекса.

Имя наибольшей таблицы

Чтобы вывести список таблиц текущей БД, отсортированный по размеру таблицы, подойдёт следующий запрос:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Чтобы получить информацию о наибольшей таблице, мы можем ограничить запрос посредством LIMIT:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;

Что здесь что: — relname — имя таблицы, представления, индекса и т. д.; — relpages — размер представления этой таблицы на диске в количестве страниц (по умолчанию одна страницы — 8 Кб); — pg_class — системная таблица, содержащая информацию о связях таблиц БД.

Перечень подключенных пользователей, активность пользователя

Если хотим узнать имя, IP и используемый порт подключенных юзеров, выполняем:

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;

Если хотим узнать активность соединения конкретного пользователя, выполняем:

SELECT datname FROM pg_stat_activity WHERE usename = 'devuser';

Статья написана по материалам «15 Advanced PostgreSQL Commands with Examples» и «10 Most Useful PostgreSQL Commands with Examples».