Получение информации о базе данных
Размер базы данных
Чтобы узнать физический размер хранилища базы данных в PostgreSQL (иногда эту СУБД сокращенно называют Postgres), можно воспользоваться следующей командой:
SELECT pg_database_size(current_database());Но чтобы получить размер базы данных в удобном формате (KB, MB, GB), нужно использовать другую команду:
SELECT pg_size_pretty(pg_database_size(current_database()));Вместо current_database() можно просто ввести название базы данных:
SELECT pg_size_pretty(pg_database_size('database_name'));Перечень таблиц
Если необходимо просмотреть все таблицы в текущей БД в консоли psql, можно ввести такую команду:
/dt
Так можно получить дополнительные сведения о таблицах, например, об их размерах:
/dt+
Существует и альтернативный способ (здесь уже используется SQL-запрос):
SELECT table_name
FROM information_schema.tables WHERE table_schema='public';Размер таблицы
Чтобы узнать размер таблицы в Postgres, можно воспользоваться следующей командой (размер сразу будет получен в читаемом формате):
SELECT pg_size_pretty(pg_total_relation_size('table_name'));Можно заметить, что эта команда похожа на ту, с помощью которой можно найти размер базы данных.
Имя самой большой таблицы
Если нужно узнать, какая таблица занимает больше места в памяти, то можно воспользоваться такой командой:
SELECT relname, relpages
FROM pg_class ORDER BY relpages DESC LIMIT 1;Эта команда будет выполнена относительно быстро, но результат выполнения не будет точным, так как для оценки размера таблиц используется relpages.
Если нужно получить точный результат (при этом выполнение операции займет больше времени), то лучше использовать такую команду:
SELECT relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 1;Перечень подключенных пользователей
Чтобы получить список пользователей, которые подключены к базе данных, в PostgreSQL можно использовать такую команду (будут показаны имя пользователя, IP-адрес и порт):
SELECT usename, client_addr, client_port
FROM pg_stat_activity;Если нужно получить список активных пользователей, то можно воспользоваться следующей командой:
SELECT usename, client_addr, client_port
FROM pg_stat_activity WHERE state = 'active';Также не обязательно запрашивать всю информацию — если она не нужна, то можно ограничиться, например, только именами подключенных пользователей:
SELECT usename
FROM pg_stat_activity WHERE state = 'active';Активность пользователя
Если нужно получить информацию об активности конкретного пользователя, то можно воспользоваться следующей командой:
SELECT query, state, query_start
FROM pg_stat_activity WHERE usename = 'username';В результате будет получена информация о том, какие запросы выполняет пользователь, каков их статус и когда они начали выполняться.
Также в PostgreSQL можно вывести информацию обо всех пользователях, чье подключение активно или ожидает выполнения транзакции (то есть она уже начата, но не завершена):
SELECT usename, state, query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction');Работа с данными и полями таблиц
Удаление одинаковых строк
Если необходимо удалить дубликаты, которые представляют из себя повторение целых строк, то можно использовать такую команду (после GROUP BY нужно указать все столбцы таблицы):
DELETE FROM table_name
WHERE ctid NOT IN
(SELECT MAX(ctid) FROM table_name GROUP BY column1, [column 2,]);Также случается такое, что повторяются значения строк, тогда их можно удалить сразу:
DELETE FROM table_name
WHERE ctid NOT IN
(SELECT MIN(ctid) FROM table_name GROUP BY column1, [column 2,]);Или предварительно получить строки с дублирующимися значениями и сохранить их каким-либо способом. Получить дубликаты можно так:
SELECT * FROM table_name WHERE ctid NOT IN
(SELECT MAX(ctid) FROM table_name GROUP BY column1, [column 2,]);При использовании MAX(ctid) будет выбрана строка с максимальным значением ctid (то есть последняя строка из дублирующихся), а остальные строки будут удалены. С MIN(ctid) все наоборот: удалены будут все записи кроме первой из повторяющихся.
Безопасное изменение типа поля
При изменении типа поля в PostgreSQL нужно учитывать совместимость старого и нового типов.
Если они совместимы (например, INTEGER меняется на BIGINT), то для изменения типа поля можно использовать такую команду:
ALTER TABLE table_name ALTER COLUMN column_name TYPE BIGINT;- Если они несовместимы (например, VARCHAR меняется на INTEGER), то для явного приведения типов потребуется использование USING:
ALTER TABLE table_name ALTER COLUMN column_name TYPE INTEGER USING column_name::INTEGER;Поиск «потерянных» значений
Для того чтобы найти «потерянные» значения, в PostgreSQL можно воспользоваться следующим способом:
SELECT pg_get_serial_sequence(table_name, column_name);Так будет получено имя последовательности.
Затем нужно выполнить такую команду:
WITH sequence_info AS (
SELECT start_value, last_value FROM "SchemaName"."SequenceName"
)
SELECT generate_series(
(sequence_info.start_value),
(sequence_info.last_value)
)
FROM sequence_info
EXCEPT
SELECT column_name FROM table_name;Подсчет количества строк в таблице
Если нужно узнать, сколько строк содержит таблица в PostgreSQL, то можно воспользоваться следующей командой:
SELECT COUNT(*) FROM table_name;Выполнение такой команды — это ресурсозатратная операция, поэтому использование COUNT(*) достаточно эффективно для маленьких таблиц, но в случае с большими таблицами выполнение команды может быть долгим. Поэтому существует другая команда, которая считает примерное количество строк:
SELECT reltuples AS estimated_count FROM pg_class WHERE oid = 'table_name'::regclass;Эта команда «посчитает» количество строк, используя внутреннюю статистику PostgreSQL, поэтому к ней не стоит прибегать, когда нужно узнать точное количество строк.
Использование транзакций
Транзакции начинаются со слова BEGIN и заканчиваются словом COMMIT. В PostgreSQL транзакции нужны, чтобы объединять несколько команд в одну операцию. При этом их используют, если необходимо, чтобы выполнились либо все эти команды вместе, либо ни одна из них, так как если возникнет ошибка при выполнении какой-то команды, то не будет сохранен результат всей транзакции.
Транзакция может выглядеть следующим образом:
BEGIN;
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column1 = new_value WHERE condition;
DELETE FROM table_name WHERE condition;
COMMIT;Просмотр и завершение исполняемых запросов
Чтобы найти информацию о текущих запросах, можно использовать такую команду:
SELECT pid, age(query_start, clock_timestamp()), usename, query
FROM pg_stat_activity
WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start DESC;Команда для остановки запроса (это не всегда означает его завершение):
SELECT pg_cancel_backend(pid);Чтобы завершить работу запроса, в Postgres используется такая команда:
SELECT pg_terminate_backend(pid);Работа с конфигурацией
Поиск и изменение расположения экземпляра кластера
Иногда возникает необходимость узнать текущее расположение экземпляра кластера PostgreSQL, но если делать это вручную, то процесс отнимет много сил и времени. В таком случае можно использовать следующую команду:
SHOW data_directory;Чтобы изменить расположение экземпляра кластера, можно использовать эту команду:
SET data_directory to new_directory_path;После смены расположения понадобится перезагрузка сервера.
Получение перечня доступных типов данных
Чтобы получить список доступных типов данных, в PostgreSQL можно воспользоваться этой командой:
SELECT typname, typlen FROM pg_type;В результате будут получены имена типов данных и их длина.
Изменение настроек СУБД без перезагрузки
После изменения параметров в PostgreSQL обычно необходима перезагрузка сервера, но часто прерывать его работу не стоит, особенно если есть требования к доступности системы. Поэтому существует команда, позволяющая изменить настройки без перезагрузки.
Сначала нужно изменить параметры, например, с помощью ALTER SYSTEM SET:
ALTER SYSTEM SET setting_name = value;После выполнения этой команды нужно применить другую:
SELECT pg_reload_conf();Так работа сервера не будет прервана. При этом не все параметры можно изменять без перезагрузки.