Сопровождение базы данных Тема сопровождения баз данных весьма обширна. В этом разделе рассматриваются такие аспекты, как физическое сопровождение системы (использование дискового пространства), аналитическое сопровождение (повышение быстродействия) и документирование объектов баз данных (упрощение сопровождения, повышение наглядности). Основным средством физического и аналитического сопровождения баз данных в PostgreSQL является команда SQL VACUUM и ее аналог — сценарий vacuumdb. Оба средства выполняют две общие функции: удаление всех данных, оставшихся в результате отмены транзакций и других операций, оставляющих временные данные; анализ операций с базами данных, по результатам которого PostgreSQL конструирует более эффективные запросы. Для баз данных, работающих в условиях реальной эксплуатации, команду VACUUM рекомендуется выполнять каждую ночь. Хотя команда может выполняться параллельно с обращениями к данным, это замедляет работу сервера. По этой причине сопровождение рекомендуется запланировать на время с минимальным количеством операций с базой данных. Каждый раз, когда в базе данных создается или удаляется большое количество записей, базу данных следует анализировать командой VACUUM, которая автоматически информирует оптимизатор запросов PostgreSQL о серьезных изменениях в таблице. В результате PostgreSQL получает более достоверный профиль данных, хранящихся в базе, и располагает более качественной информацией для оптимального планирования запросов. Все это способствует быстрому и эффективному получению ответа от базы данных. ВНИМАНИЕ Команда VACUUM блокирует таблицы в монопольном режиме. Это означает, что все запросы, использующие обрабатываемую таблицу, приостанавливаются и ожидают снятия блокировки. Команда VACUUM Команда SQL VACUUM имеет следующий синтаксис: VACUUM [ VERBOSE ] [ ANALYZE ] [ таблица ] VACUUM [ VERBOSE ] ANALYZE [ таблица [ ( поле [. ...] ) ] ] При вызове без необязательных ключевых слов и идентификаторов команда VACUUM последовательно чистит все таблицы базы данных, подключенной в настоящий момент, удаляет временные данные и освобождает место на диске. Чаще всего команда VACUUM выполняется именно для получения максимального объема свободного дискового пространства. Необязательный идентификатор таблица задается в том случае, если команда VACUUM должна очистить только одну таблицу в подключенной базе данных. Команда также обновляет в системных каталогах статистику по количеству записей и объемам данных. В листинге 9.14 приведен пример использования команды VACUUM для таблицы books в базе данных booktown. Листинг 9.14. Применение команды VACUUM к отдельной таблице booktown=# VACUUM books; VACUUM Сообщение VACUUM означает, что процесс завершился успешно. Если заданную таблицу не удается найти, выводится следующее сообщение об ошибке: NOTICE: Vacuum: table not found При наличии необязательного ключевого слова ANALYZE PostgreSQL анализирует структуру данных во всех полях всех таблиц (или только заданной таблицы, если она указана), после чего эта информация используется оптимизатором запросов для более эффективного планирования. Ключевое слово ANALYZE также позволяет ограничить анализ отдельными полями. В листинге 9.15 приведен пример использования команды VACUUM ANALYZE для всей базы данных booktown. Листинг 9.15. Применение команды VACUUM ANALYZE ко всей базе данных booktown=# VACUUM ANALYZE; VACUUM Наконец, необязательное ключевое слово VERBOSE применяется в том случае, если вы хотите получить подробный внутренний отчет по результатам выполнения команды VACUUM. Скорее всего, эта информация представляет интерес только для программистов, занимающихся разработкой PostgreSQL или взаимосвязанных программ. Приложение vacuumdb Для команды VACUUM, как и для многих других команд управления базами данных, существует вспомогательное приложение командной строки vacuumdb. По сравнению с командой SQL VACUUM сценарий vacuumdb обладает одной важной дополнительной функцией: с его помощью можно выполнить команду VACUUM для всех баз данных PostgreSQL в системе. Кроме того, сценарию передаются параметры подключения к PostgreSQL, что позволяет использовать его в удаленном режиме (то есть без предварительного подключения к компьютеру при помощи терминального клиента и последующего выполнения программ vacuumdb или psql с удаленного компьютера). Впрочем, для этого режим аутентификации в файле pg_hba.conf должен быть настроен для внешнего доступа (за дополнительной информацией обращайтесь к главе 8). Синтаксис запуска приложения vacuumdb: vacuumdb [ параметры ] [ бдза_данных ] Ключи командной строки сценария vacuumdb, как и сценариев createdb и dropdb, могут задаваться как с одним дефисом, так и с двумя дефисами в стиле GNU. Обязательным является только параметр база_дапных (который может быть заменен ключом --all); этот параметр определяет базу данных, с которой выполняются операции очистки и анализа. Строка параметры определяет режим выполнения команды VACUUM. Ниже приведен полный список ключей приложения vacuumdb. - h хост, -- host=*oc//z. Хост, с которым устанавливается связь для очистки базы данных. Ключ используется для очистки удаленных баз данных. -р порт, --port=nopm. Порт, по которому должно производиться подключение к серверу, вместо порта по умолчанию (обычно 5432, хотя при компиляции PostgreSQL можно задать другой порт при помощи флага --with-pgport). U пользователь, -- изегтте=полъзова7пелъ. Имя пользователя, с которым производится подключение к PostgreSQL для очистки базы данных (вместо имени системного пользователя, запустившего программу vacuumdb). -W, - - password. Ключ передается без параметров и обеспечивает запрос пароля у пользователя. Это происходит автоматически, если в файле pg_hba.conf хост, от которого поступил запрос, не объявлен доверенным. -d 6аза_даппых, —6Ьг\ате=база_данньис. Имя базы данных, для которой выполняется команда VACUUM. Ключ -d является взаимоисключающим по отношению к ключу -а. - а, - - а! 1. Команда VACUUM с заданными параметрами применяется ко всем базам данных, присутствующим в системном каталоге. -z, - -analyze. Аналог ключевого слова ANALYZE в команде SQL VACUUM. Обновляет статистику распределения данных в полях, используемую оптимизатором запросов в процессе внутреннего планирования. -t 'таблица [ (поле\_, ...])]', --tab~\e='таблица [ (поле[, ...])]'. Ключ определяет таблицу (или конкретные поля в таблице), обрабатываемые командой VACUUM. Для обработки отдельных полей должен быть установлен ключ : - analyze. -v, - -verbose. Аналог ключевого слова VERBOSE в команде SQL VACUUM. Обеспечивает вывод подробного внутреннего отчета по результатам выполнения команды VACUUM. -е, - -echo. Ключ передается без параметров. При включении вывода эха запрос, передаваемый PostgreSQL, выводится на экран в момент его выполнения программой vacuumdb. -q, - -quiet. Ключ передается без параметров и запрещает вывод данных в стандартный поток stdout (хотя ошибки по-прежнему направляются в стандартный поток stderr). В листинге 9.16 приведен пример использования сценария vacuumdb для сервера базы данных booktown. Ключ -U указывает, что для подключения должно использоваться имя пользователя manager, а флаг - -all обеспечивает последовательную обработку всех баз данных в системном каталоге. Листинг 9.16. Применение сценария vacuumdb ко всем базам данных [jworsley@booktown -]$ vacuumdb -U manager --all Vacuuming postgres VACUUM Vacuuming booktown VACUUM Vacuuming tempiatel VACUUM Как упоминалось выше, параметры подключения позволяют легко выполнять сценарий vacuumdb с удаленного сервера. В листинге 9.17 происходит практически то же самое, что и в листинге 9.16, но на этот раз ключ - h определяет удаленный сервер с именем booktown.commandprompt.com. Кроме того, в листинге 9.17 обрабатывается конкретная база данных booktown (вместо чистки всех баз данных). Листинг 9.17. Применение сценария vacuumdb к удаленной базе данных [jworsley@cmd ~]$ vacuumdb -h booktown.commandprompt.com -U manager booktown VACUUM Документирование базы данных Команда COMMENT В PostgreSQL поддерживается нестандартная команда SQL COMMENT, при помощи которой можно документировать любой объект базы данных. Используя команду COMMENT с таблицей, функцией, оператором или другим объектом базы данных, вы можете ввести описание, которое будет храниться в системной таблице pg_descri pti on. Выборка описаний легко производится при помощи управляющих команд psql. У многих стандартных объектов баз данных имеются стандартные описания, которые выводятся (вместе с описаниями, добавленными пользователем) командой \dd клиента psql. Синтаксис команды COMMENT: COMMENT ON [ [ DATABASE | INDEX | RULE SEQUENCE TABLE | TYPE j VIEW ] { объект | COLUMN таблица.поле \ AGGREGATE агрегат тип_агрегата \ FUNCTION функция ( тип_аргумента [. ...] ) | OPERATOR оператор ( тип_левого_операнда. тип_правого_операнда ) | TRIGGER триггер ON таблица } ] IS 'описание' Параметр объект определяет имя объекта базы данных, для которого создается комментарий. Ключевые слова для основных объектов баз данных не обязательны, но если комментарий создается для поля, функции, агрегатной функции, оператора или триггера, необходимо указать соответствующее ключевое слово — по нему PostgreSQL проверяет синтаксис дальнейшего описания. ПРИМЕЧАНИЕ Все комментарии связываются как с базой данных, так и с конкретным пользователем. Каждый пользователь может просматривать только свои комментарии. Строка описание, следующая за ключевым словом IS, содержит текст комментария, сохраняемого в базе данных. В листинге 9.18 создается простое описание для поля id базы данных booktown. Листинг 9.18. Создание комментария к таблице books booktown=# COMMENT ON COLUMN books.id booktown-# IS 'An Internal Book Town Identifier'; COMMENT Сообщение COMMENT означает, что комментарии к полю успешно создан. Чтение комментариев Комментарии к объектам базы данных легко читаются при помощи управляющих команд psql. Список этих команд приведен ниже. \d+. Выводит ту же информацию, что и стандартная команда \d (данные обо всех таблицах, представлениях, последовательностях и индексах текущей базы данных), но добавляет к ней столбец комментариев. \1+. Выводит комментарии ко всем базам данных. \df+ [ шаблон ]. Выводит описания всех функций текущей базы данных (с информацией о языке и реализации каждой функции). Чтобы просмотреть результаты этой команды в расширенном режиме вывода, следует предварительно выполнить команду \х (за дополнительной информацией обращайтесь к главе 6). Команде можно передать регулярное выражение шаблон, с которым должны сравниваться имена функций. Это сужает круг поиска и уменьшает количество выводимых функций. \dt+. Выводит комментарии ко всем таблицам текущей базы данных. \di+. Выводит комментарии ко всем индексам текущей базы данных. \ds+. Выводит комментарии ко всем последовательностям текущей базы данных. \dv+. Выводит комментарии ко всем представлениям текущей базы данных. \dS+. Выводит комментарии к системным таблицам. Следует помнить, что комментарии к системным таблицам тоже привязываются к конкретной базе данных и не выводятся, если команда \dS+ выполняется при подключении к другой базе данных. \dd. Выводит все описания всех объектов базы данных. В листинге 9.19 приведен пример вывода комментариев к таблице books (листинг 9.18) командой \d+ клиента psql. Листинг 9.19. Вывод комментариев booktown=# \d+ books Table "books" Attribute j Type Modifier | Description id | integer not null | An Internal Book Town Identifier title text not null j authorjd j integer subject_id | integer Index: books_id_pkey Команда SQL COMMENT обеспечивает очень простые средства внутреннего документирования различных объектов, от таблиц до функций. Комментарии оказывают неоценимую помощь при работе с большими или очень сложными структурами баз данных. Даже специальные схемы выбора имен не всегда делают очевидным смысл объектов базы данных, особенно если над проектом работает несколько разработчиков.