Обслуживание базы данных PostgreSQL

Введение

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

Вы начнете с подключения к базе данных PostgreSQL и запроса размеров таблиц и индексов. Затем вы узнаете, как запускать ANALYZE для обновления статистики базы данных и VACUUM для освобождения дискового пространства. Наконец, вы научитесь выводить список активных клиентских соединений и проверять журналы сервера на наличие ошибок для диагностики и решения проблем с базой данных.

Запрос размеров объектов базы данных

На этом этапе вы подключитесь к базе данных PostgreSQL и запросите размеры таблицы и ее индекса. Понимание размера объектов вашей базы данных имеет решающее значение для настройки производительности и планирования мощностей.

Сначала откройте терминал. Чтобы подключиться к серверу PostgreSQL от имени пользователя postgres и получить доступ к базе данных mydatabase, выполните следующую команду:

sudo -u postgres psql mydatabase

Вы должны увидеть приглашение psql (mydatabase=#), указывающее на то, что вы подключены к базе данных mydatabase. Все последующие SQL-запросы в этой лабораторной работе должны выполняться в этой оболочке psql, если не указано иное.

Теперь определим размер mytable. Мы будем использовать функции pg_size_pretty и pg_relation_size. pg_relation_size возвращает размер таблицы в байтах, а pg_size_pretty форматирует его в удобочитаемый формат (например, KB, MB).

Выполните следующий SQL-запрос, чтобы получить размер mytable:

SELECT pg_size_pretty(pg_relation_size('mytable'));

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

 pg_size_pretty
----------------
 56 kB
(1 row)

Далее проверьте размер индекса idx_mytable_name, который был создан для столбца name:

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

Вывод покажет пространство, занимаемое индексом:

 pg_size_pretty
----------------
 48 kB
(1 row)

Наконец, чтобы получить общий размер таблицы, включая все ее индексы, используйте функцию pg_total_relation_size:

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

Этот вывод показывает комбинированный размер таблицы и ее индекса:

 pg_size_pretty
----------------
 176 kB
(1 row)

Оптимизация с помощью ANALYZE

На этом этапе вы узнаете о команде ANALYZE, которая необходима для поддержания хорошей производительности запросов.

Понимание ANALYZE

Команда ANALYZE собирает статистику о содержимом таблиц в базе данных. Планировщик запросов PostgreSQL использует эту статистику для выбора наиболее эффективных планов выполнения запросов. Без точной статистики планировщик может сделать неправильный выбор, что приведет к медленной работе запросов. Рекомендуется периодически запускать ANALYZE, особенно после значительных изменений в данных таблицы.

Находясь в оболочке psql, выполните ANALYZE для таблицы mytable:

ANALYZE mytable;

Эта команда анализирует mytable и обновляет ее статистику. Вы увидите следующий вывод, подтверждающий успешное выполнение команды:

ANALYZE

Хотя команда просто возвращает ANALYZE, она обновила внутреннюю статистику для mytable в фоновом режиме.

Освобождение места с помощью VACUUM

На этом этапе вы будете использовать команду VACUUM для выполнения еще одной важной задачи обслуживания: освобождения места.

Понимание VACUUM

В PostgreSQL, когда строка обновляется или удаляется, старая версия строки ("мертвый кортеж" - dead tuple) не удаляется немедленно с диска. VACUUM освобождает пространство, занимаемое этими мертвыми кортежами, делая его доступным для повторного использования. Он также обновляет информацию о видимости данных, что помогает улучшить производительность запросов.

Давайте выполним VACUUM для таблицы mytable. В оболочке psql выполните:

VACUUM mytable;

Эта команда обработает таблицу и вернет подтверждение:

VACUUM

Вы также можете объединить VACUUM и ANALYZE в одну эффективную команду. Это распространенная практика обслуживания.

VACUUM ANALYZE mytable;

Эта команда сначала освобождает место, а затем обновляет статистику для таблицы, возвращая VACUUM по завершении.

Примечание: Существует также команда VACUUM FULL, которая более агрессивно освобождает место и уменьшает размер файла на диске. Однако она блокирует всю таблицу, предотвращая любые чтения или записи во время своей работы, поэтому ее следует использовать с осторожностью и только при необходимости.

Мониторинг соединений и журналов

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

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

Выполните следующий SQL-запрос:

SELECT datname, usename, client_addr, state FROM pg_stat_activity;

Этот запрос показывает имя базы данных (datname), имя пользователя (usename), IP-адрес клиента (client_addr) и текущее состояние соединения (state). Вы увидите свое собственное соединение в выводе, похожее на это:

  datname  | usename  | client_addr | state
-----------+----------+-------------+--------
 mydatabase| postgres |             | active
 postgres  | postgres |             |
           | labex    |             |
(3 rows)

Далее вы проверите журналы сервера. Для этого вам сначала нужно выйти из оболочки psql. Введите \q и нажмите Enter:

\q

Теперь вы вернулись в стандартную командную строку Linux. Журналы PostgreSQL находятся в каталоге /var/log/postgresql/. В этой системе файл журнала называется postgresql-14-main.log.

Используйте команду grep для поиска строк, содержащих "ERROR", в файле журнала:

grep ERROR /var/log/postgresql/postgresql-14-main.log

Если ошибок нет, эта команда не выдаст никакого вывода. Это ожидаемый результат для данной лабораторной работы, поскольку мы не выполняли никаких действий, которые могли бы вызвать ошибку. Изучение журналов сервера является фундаментальным шагом в диагностике проблем с базой данных.

Резюме

В этой лабораторной работе вы научились выполнять основные задачи обслуживания базы данных PostgreSQL. Теперь вы понимаете, как отслеживать размер таблиц и индексов, запускать ANALYZE для оптимизации производительности запросов и использовать VACUUM для освобождения дискового пространства. Вы также практиковались в перечислении активных клиентских соединений и проверке журналов сервера на наличие ошибок. Эти навыки имеют решающее значение для обеспечения работоспособности, производительности и надежности вашей базы данных PostgreSQL.