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

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

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

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

Windows:
./smpo-server/bin/server-control stop
linux:
./smpo-server/bin/server-control stop

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

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

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

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

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

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

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

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

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