Техническое обслуживание БД PostgreSQL

Техническое обслуживание БД PostgreSQL

avtor Разное

Как и в любой СУБД, в PostgreSQL для достижения оптимальной производительности нужно регулярно выполнять определённые процедуры.
Эти задачи являются обязательными, но они по природе своей повторяющиеся и легко поддаются автоматизации с использованием стандартных средств, таких как задания cron или Планировщика задач в Windows.
Создание соответствующих заданий и контроль над их успешным выполнением входят в обязанности администратора базы данных.

Порядок проведения технического обслуживания БД PostgreSQL

Внимание! Перед проведением технического обслуживания обязательно создайте резервную копию базы данных.
1.Остановите сервер.

Windows:
./Server_KROS/smpo-server/bin/server-control stop
linux:
./Server_KROS/smpo-server/bin/server-control stop
Pg1.JPG
Pg2.JPG
Pg3.JPG

2. Запустите программу pgAdmin
3. Раскройте дерево объектов PostgreSQL двойным щелчком по соответствующей строке левой кнопкой мыши, затем введите пароль пользователя postgres. (Если установить флажок Запомнить пароль, то при последующих входах пароль не будет запрашиваться).

4. Щелкните правой кнопкой мыши по строке базы jupiter и в появившемся меню выберите обслуживание postgresql.

5. В результате откроется окно выбора операций Обслуживания, в котором можно выбрать описанные ниже операции.

Основные принципы очистки:
Операция VACUUM
Команды VACUUM в PostgreSQL должны обрабатывать каждую таблицу по следующим причинам:

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

Разные причины диктуют выполнение действий VACUUM с разной частотой и в разном объёме.

Существует два варианта VACUUM:
Обычный VACUUM и VACUUM FULL.

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

При использовании операции в форме VACUUM [FULL] ANALYZE после сборки мусора будет обновлена статистика по данной таблице, используемая оптимизатором. В абсолютном большинстве случаев целесообразно использовать именно эту форму.
Рекомендуется достаточно частое (например, раз в несколько минут) выполнение операции VACUUM ANALYZE для часто обновляемых баз (или отдельных таблиц). В стандартных случаях достаточно ежедневного выполнения этой операции.
Операцию VACUUM FULL стоит запускать достаточно редко, не чаще раза в неделю. Ее также целесообразно запускать вручную для конкретной таблицы после удаления или обновления большой части записей в ней.

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

Операция REINDEX
Операция REINDEX используется для перестройки существующих индексов.
Ее использование целесообразно в следующих случаях:

  • порча индекса;
  • постоянное увеличение размера индекса.

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

Если вы заметили подобное поведение какого-либо индекса, то целесообразно настроить для него периодическое выполнение команды REINDEX. Учтите, что операция REINDEX, как и VACUUM FULL, полностью блокирует доступ к таблице, поэтому ее следует выполнять в периоды времени, когда загрузка сервера минимальна.

Для проведения технического обслуживания рекомендуется выполнить операции REINDEX (FORCE) и VACUUM (FULL, ANALYZE).

Внимание! Чем больше информации содержится в базе данных, тем больше времени могут выполняться операции REINDEX и VACUUM.