Thank you for reading this post, don't forget to subscribe!
Получение информации о базе данных
Размер базы данных
Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:
1 |
SELECT <span class="token function">pg_database_size</span><span class="token punctuation">(</span><span class="token function">current_database</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
Результат будет представлен как число вида 41809016
.
current_database()
— функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:
1 |
SELECT <span class="token function">pg_database_size</span><span class="token punctuation">(</span><span class="token string">'my_database'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty
:
1 |
SELECT <span class="token function">pg_size_pretty</span><span class="token punctuation">(</span><span class="token function">pg_database_size</span><span class="token punctuation">(</span><span class="token function">current_database</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
В результате получим информацию вида 40 Mb
.
Перечень таблиц
Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:
1 2 |
SELECT table_name FROM information_schema<span class="token punctuation">.</span>tables WHERE table_schema NOT IN <span class="token punctuation">(</span><span class="token string">'information_schema'</span><span class="token punctuation">,</span><span class="token string">'pg_catalog'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
information_schema
— стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.
Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:
1 2 3 |
SELECT table_name FROM information_schema<span class="token punctuation">.</span>tables WHERE table_schema NOT IN <span class="token punctuation">(</span><span class="token string">'information_schema'</span><span class="token punctuation">,</span> <span class="token string">'pg_catalog'</span><span class="token punctuation">)</span> AND table_schema <span class="token function">IN</span><span class="token punctuation">(</span><span class="token string">'public'</span><span class="token punctuation">,</span> <span class="token string">'myschema'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
В последнем условии IN
можно указать имя определенной схемы.
Размер таблицы
По аналогии с получением размера базы данных размер данных таблицы можно вычислить с помощью соответствующей функции:
1 |
SELECT <span class="token function">pg_relation_size</span><span class="token punctuation">(</span><span class="token string">'accounts'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
Функция pg_relation_size
возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.
Имя самой большой таблицы
Для того, чтобы вывести список таблиц текущей базы данных, отсортированный по размеру таблицы, выполним следующий запрос:
1 |
SELECT relname<span class="token punctuation">,</span> relpages FROM pg_class ORDER BY relpages DESC<span class="token punctuation">;</span> |
Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT
:
1 |
SELECT relname<span class="token punctuation">,</span> relpages FROM pg_class ORDER BY relpages DESC LIMIT <span class="token number">1</span><span class="token punctuation">;</span> |
relname
— имя таблицы, индекса, представления и т.п.
relpages
— размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class
— системная таблица, которая содержит информацию о связях таблиц базы данных.
Перечень подключенных пользователей
Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:
1 |
SELECT datname<span class="token punctuation">,</span>usename<span class="token punctuation">,</span>client_addr<span class="token punctuation">,</span>client_port FROM pg_stat_activity<span class="token punctuation">;</span> |
Активность пользователя
Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:
1 |
SELECT datname FROM pg_stat_activity WHERE usename <span class="token operator">=</span> <span class="token string">'devuser'</span><span class="token punctuation">;</span> |
Работа с данными и полями таблиц
Удаление одинаковых строк
Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:
- дублирующиеся строки,
- ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).
Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).
Удалить все дубликаты поможет следующий запрос:
1 2 |
DELETE FROM customers WHERE ctid NOT IN <span class="token punctuation">(</span>SELECT <span class="token function">max</span><span class="token punctuation">(</span>ctid<span class="token punctuation">)</span> FROM customers GROUP BY customers<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
Уникальное для каждой записи поле ctid
по умолчанию скрыто, но оно есть в каждой таблице.
Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.
Теперь рассмотрим случай, когда повторяются значения полей.
Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:
1 2 |
DELETE FROM customers WHERE ctid NOT IN <span class="token punctuation">(</span>SELECT <span class="token function">max</span><span class="token punctuation">(</span>ctid<span class="token punctuation">)</span> FROM customers GROUP BY customer_id<span class="token punctuation">)</span><span class="token punctuation">;</span> |
Если данные важны, то сначала нужно найти записи с дубликатами:
1 2 |
SELECT <span class="token operator">*</span> FROM customers WHERE ctid NOT IN <span class="token punctuation">(</span>SELECT <span class="token function">max</span><span class="token punctuation">(</span>ctid<span class="token punctuation">)</span> FROM customers GROUP BY customer_id<span class="token punctuation">)</span><span class="token punctuation">;</span> |
Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id
на другое.
Общая форма запроса на удаление описанных выше записей выглядит следующим образом:
1 |
DELETE FROM table_name WHERE ctid NOT IN <span class="token punctuation">(</span>SELECT <span class="token function">max</span><span class="token punctuation">(</span>ctid<span class="token punctuation">)</span> FROM table_name GROUP BY column1<span class="token punctuation">,</span> <span class="token punctuation">[</span>column <span class="token number">2</span><span class="token punctuation">,</span><span class="token punctuation">]</span> <span class="token punctuation">)</span><span class="token punctuation">;</span> |
Безопасное изменение типа поля
Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER
. Давайте для примера снова рассмотрим таблицу с покупателями.
Для поля customer_id
используется строковый тип данных varchar
. Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат integer
. Использование varchar
неоправданно. Попробуем исправить это недоразумение с помощью команды ALTER
:
1 |
ALTER TABLE customers ALTER COLUMN customer_id TYPE integer<span class="token punctuation">;</span> |
Но в результате выполнения получим ошибку:
ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.
Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar
, СУБД не может определить принадлежность значения к integer
. Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING
, чтобы корректно преобразовать наши данные в integer
:
1 |
ALTER TABLE customers ALTER COLUMN customer_id TYPE integer USING <span class="token punctuation">(</span>customer_id<span class="token punctuation">:</span><span class="token punctuation">:</span>integer<span class="token punctuation">)</span><span class="token punctuation">;</span> |
В результате всё прошло без ошибок:
Обратите внимание, что при использовании USING
кроме конкретного выражения возможно использование функций, других полей и операторов.
Например, преобразуем поле customer_id
обратно в varchar
, но с преобразованием формата данных:
1 |
ALTER TABLE customers ALTER COLUMN customer_id TYPE varchar USING <span class="token punctuation">(</span>customer_id <span class="token operator">||</span> <span class="token string">'-'</span> <span class="token operator">||</span> first_name<span class="token punctuation">)</span><span class="token punctuation">;</span> |
В результате таблица примет следующий вид:
Поиск «потерянных» значений
Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.
Рассмотрим два варианта поиска.
Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:
1 2 3 4 5 6 7 8 9 |
SELECT customer_id <span class="token operator">+</span> <span class="token number">1</span> FROM customers mo WHERE NOT EXISTS <span class="token punctuation">(</span> SELECT NULL FROM customers mi WHERE mi<span class="token punctuation">.</span>customer_id <span class="token operator">=</span> mo<span class="token punctuation">.</span>customer_id <span class="token operator">+</span> <span class="token number">1</span> <span class="token punctuation">)</span> ORDER BY customer_id<span class="token punctuation">;</span> |
В результате получим значения: 5
, 9
и 11
.
Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:
1 2 3 4 5 6 7 8 9 |
WITH seq_max AS <span class="token punctuation">(</span> SELECT <span class="token function">max</span><span class="token punctuation">(</span>customer_id<span class="token punctuation">)</span> FROM customers <span class="token punctuation">)</span><span class="token punctuation">,</span> seq_min AS <span class="token punctuation">(</span> SELECT <span class="token function">min</span><span class="token punctuation">(</span>customer_id<span class="token punctuation">)</span> FROM customers <span class="token punctuation">)</span> SELECT <span class="token operator">*</span> FROM <span class="token function">generate_series</span><span class="token punctuation">(</span><span class="token punctuation">(</span>SELECT min FROM seq_min<span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span>SELECT max FROM seq_max<span class="token punctuation">)</span><span class="token punctuation">)</span> EXCEPT SELECT customer_id FROM customers<span class="token punctuation">;</span> |
В результате видим следующий результат: 5
, 9
и 6
.
Второй способ
Получаем имя последовательности, связанной с customer_id
:
1 |
SELECT <span class="token function">pg_get_serial_sequence</span><span class="token punctuation">(</span><span class="token string">'customers'</span><span class="token punctuation">,</span> <span class="token string">'customer_id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
И находим все пропущенные идентификаторы:
1 2 3 4 5 6 7 |
WITH sequence_info AS <span class="token punctuation">(</span> SELECT start_value<span class="token punctuation">,</span> last_value FROM <span class="token string">"SchemaName"</span><span class="token punctuation">.</span><span class="token string">"SequenceName"</span> <span class="token punctuation">)</span> SELECT generate_series <span class="token punctuation">(</span><span class="token punctuation">(</span>sequence_info<span class="token punctuation">.</span>start_value<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>sequence_info<span class="token punctuation">.</span>last_value<span class="token punctuation">)</span><span class="token punctuation">)</span> FROM sequence_info EXCEPT SELECT customer_id FROM customers<span class="token punctuation">;</span> |
Подсчёт количества строк в таблице
Количество строк вычисляется стандартной функцией count
, но её можно использовать с дополнительными условиями.
Общее количество строк в таблице:
1 |
SELECT <span class="token function">count</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> FROM table<span class="token punctuation">;</span> |
Количество строк при условии, что указанное поле не содержит NULL
:
1 |
SELECT <span class="token function">count</span><span class="token punctuation">(</span>col_name<span class="token punctuation">)</span> FROM table<span class="token punctuation">;</span> |
Количество уникальных строк по указанному полю:
1 |
SELECT <span class="token function">count</span><span class="token punctuation">(</span>distinct col_name<span class="token punctuation">)</span> FROM table<span class="token punctuation">;</span> |
Использование транзакций
Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.
Начнём транзакцию с помощью команды BEGIN
.
Для того, чтобы откатить все операции, расположенные после BEGIN
, используем команду ROLLBACK
.
А чтобы применить — команду COMMIT
.
Просмотр и завершение исполняемых запросов
Для того, чтобы получить информацию о запросах, выполним следующую команду:
1 2 3 4 |
SELECT pid<span class="token punctuation">,</span> <span class="token function">age</span><span class="token punctuation">(</span>query_start<span class="token punctuation">,</span> <span class="token function">clock_timestamp</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span> usename<span class="token punctuation">,</span> query FROM pg_stat_activity WHERE query <span class="token operator">!=</span> <span class="token string">'<IDLE>'</span> AND query NOT ILIKE <span class="token string">'%pg_stat_activity%'</span> ORDER BY query_start desc<span class="token punctuation">;</span> |
Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):
1 |
SELECT <span class="token function">pg_cancel_backend</span><span class="token punctuation">(</span>procpid<span class="token punctuation">)</span><span class="token punctuation">;</span> |
Для того, чтобы прекратить работу запроса, выполним:
1 |
SELECT <span class="token function">pg_terminate_backend</span><span class="token punctuation">(</span>procpid<span class="token punctuation">)</span><span class="token punctuation">;</span> |
Работа с конфигурацией
Поиск и изменение расположения экземпляра кластера
Возможна ситуация, когда на одной операционной системе настроено несколько экземпляров PostgreSQL, которые «сидят» на различных портах. В этом случае поиск пути к физическому размещению каждого экземпляра — достаточно нервная задача. Для того, чтобы получить эту информацию, выполним следующий запрос для любой базы данных интересующего кластера:
1 |
SHOW data_directory<span class="token punctuation">;</span> |
Изменим расположение на другое с помощью команды:
1 |
SET data_directory to new_directory_path<span class="token punctuation">;</span> |
Но для того, чтобы изменения вступили в силу, требуется перезагрузка.
Получение перечня доступных типов данных
Получим перечень доступных типов данных с помощью команды:
1 |
SELECT typname<span class="token punctuation">,</span> typlen from pg_type where typtype<span class="token operator">=</span><span class="token string">'b'</span><span class="token punctuation">;</span> |
typname
— имя типа данных.
typlen
— размер типа данных.
Изменение настроек СУБД без перезагрузки
Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf
и pg_hba.conf
. После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:
1 |
SELECT <span class="token function">pg_reload_conf</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
Но, к сожалению, она применима не ко всем параметрам. В некоторых случаях для применения настроек перезагрузка обязательна.