Управление базами данных

Каждый экземпляр работающего сервера QHB управляет одной или несколькими базами данных. Таким образом, базы данных являются высшим иерархическим уровнем для организации объектов SQL («объектов базы данных»). В этой главе описываются свойства баз данных, а также способы их создания, управления и уничтожения.

Обзор

Небольшое число объектов, таких как роли, базы данных и табличные пространства, определяются на уровне кластера и сохраняются в табличном пространстве pg_global. Внутри кластера имеется несколько баз данных, которые изолированы друг от друга, но могут обращаться к объектам уровня кластера. Внутри каждой базы данных имеется несколько схем, содержащих такие объекты, как таблицы и функции. Таким образом полная иерархия выглядит так: кластер, база данных, схема, таблица (или какой-то другой тип объекта, например функция).

При подключении к серверу баз данных клиент должен указать в своем запросе на подключение имя базы данных, к которой он хочет подключиться. Через одно соединение невозможно получить доступ более чем к одной базе данных. Однако клиенты могут открывать несколько подключений к одной базе данных или к разным. Защита на уровне базы данных состоит из двух компонентов: управление доступом (см. раздел Файл qhb_hba.conf), реализуемое на уровне подключения, и управление авторизацией (см. раздел Права), реализуемое через систему прав. Обертки сторонних данных (см. postgres_fdw) позволяют объектам в одной базе данных выступать в качестве представителей объектов в других базах данных или кластерах. Похожую возможность предоставляет более старый модуль dblink (см. dblink). По умолчанию все пользователи могут подключаться ко всем базам данных, пользуясь любыми методами подключения.

Если один кластер сервера QHB предназначен для размещения несвязанных проектов или пользователей, которые, по большей части, не должны замечать друг друга, рекомендуется поместить их в отдельные базы данных и соответствующим образом настроить управление авторизацией и доступом. Если же проекты или пользователи взаимосвязаны и поэтому должны иметь возможность использовать ресурсы друг друга, они должны быть помещены в одну базу данных, но, возможно, в разные схемы; это создаст модульную структуру с изоляцией пространств имен и управлением авторизацией. Более подробная информация об управлении схемами находится в разделе Схемы.

Хотя в одном кластере можно создать несколько баз данных, сначала рекомендуется тщательно оценить, перевешивают ли получаемые преимущества сопряженные с этим риски и ограничения. В частности, влияние, которое наличие общего WAL (см. главу Прочность и журнал упреждающей записи) имеет на параметры резервного копирования и восстановления. Хотя с позиции пользователя отдельные базы данных в кластере и изолированы друг от друга, но с точки зрения администратора баз данных они тесно связаны.

Базы данных создаются с помощью команды CREATE DATABASE (см. раздел Создание базы данных) и уничтожаются с помощью команды DROP DATABASE (см. раздел Удаление базы данных). Чтобы просмотреть набор существующих баз данных, изучите системный каталог pg_database, например

SELECT datname FROM pg_database;

Метакоманда \l и параметр командной строки -l программы psql также позволяют вывести список существующих баз данных.

Примечание
В стандарте SQL базы данных называются «каталогами», но на практике между ними нет никакой разницы.

Создание базы данных

Для создания базы данных сервер QHB должен быть запущен и функционален (см. раздел Запуск сервера баз данных).

Базы данных создаются с помощью команды SQL CREATE DATABASE:

CREATE DATABASE имя;

где имя следует обычным правилам для идентификаторов SQL. Владельцем новой базы данных автоматически становится текущая роль. Владелец базы данных имеет право потом ее удалить (что также удаляет все объекты в ней, даже если у них другой владелец).

Создание баз данных — операция ограниченного использования. Как предоставить на нее право, описано в разделе Атрибуты ролей.

Поскольку для выполнения команды CREATE DATABASE необходимо подключиться к серверу баз данных, остается вопрос, как можно создать первую базу данных в любом заданном месте. Первая база данных всегда создается командой qhb_bootstrap при инициализации области хранения данных. (См. раздел Создание кластера баз данных). Эта база данных называется qhb. Таким образом, чтобы создать первую «обычную» базу данных, можно подключиться к qhb.

Вторая база данных, template1, также создается во время инициализации кластера баз данных. Всякий раз, когда в кластере создается новая база данных, template1, по сути, клонируется. Это означает, что все изменения, которые вы делаете в template1, распространяются на все впоследствии созданные базы данных. Поэтому в template1 лучше не создавать объекты, если вы не хотите, чтобы они распространялись на каждую вновь созданную базу данных. Более подробную информацию см. в разделе Шаблоны баз данных.

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

createdb имя_бд

Утилита createdb подключается к базе данных qhb и выполняет команду CREATE DATABASE, в точности как описано выше. На справочной странице createdb описаны подробности ее вызова. Обратите внимание, что createdb без аргументов создаст базу данных с именем текущего пользователя.

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

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

CREATE DATABASE имя_бд OWNER имя_роли;

из среды SQL, или:

createdb -O имя_роли имя_бд

из оболочки ОС. Только суперпользователь может создавать базу данных для кого-то другого (то есть для роли, членом которой он не является).

Шаблоны баз данных

Команда CREATE DATABASE фактически работает путем копирования существующей базы данных. По умолчанию она копирует стандартную системную базу данных с именем template1. Таким образом, эта база данных является «шаблоном», из которого создаются новые базы данных. Если в template1 добавить объекты, они будут скопированы во вновь создаваемые пользовательские базы данных. Такое поведение допускает локальные модификации стандартного набора объектов в базах данных. Например, если вы установите процедурный язык PL/Perl в template1, он автоматически будет доступен в пользовательских базах данных без каких-либо дополнительных действий при создании этих баз.

Существует вторая стандартная системная база данных с именем template0. Эта база данных содержит те же данные, что и исходное содержимое template1, то есть только стандартные объекты, предопределенные вашей версией QHB. Шаблон template0 нельзя изменять после инициализации кластера баз данных. Указав CREATE DATABASE скопировать template0 вместо template1, можно создать «нетронутую» пользовательскую базу данных (в которой отсутствуют пользовательские объекты и не были изменены системные объекты), не содержащую никаких локальных дополнений из template1. Это особенно удобно при восстановлении дампа qhb_dump: скрипт дампа следует восстанавливать в нетронутой базе данных, чтобы он точно воссоздавал правильное содержимое скопированной базы данных, без конфликтов с объектами, которые могли быть добавлены в template1 позже.

Другая распространенная причина копирования template0 вместо template1 заключается в том, что при копировании template0 можно указать новые значения кодировки и локали, тогда как для копии template1 должны использоваться значения шаблона. Это связано с тем, что template1 может содержать данные в специфичных кодировках или локалях, а template0 заведомо не может.

Чтобы создать базу данных путем копирования template0, выполните:

CREATE DATABASE имя_бд TEMPLATE template0;

из среды SQL, или:

createdb -T template0 имя_бд

из оболочки ОС

Можно создать дополнительные шаблоны баз данных, и, разумеется, скопировать любую базу данных в кластере, указав ее имя в качестве шаблона для CREATE DATABASE. Однако важно понимать, что это (пока) не назначено универсальным средством для реализации возможности «COPY DATABASE». Основным ограничением является то, что при копировании исходной базе данных к ней не могут быть подключены никакие другие сеансы. CREATE DATABASE завершится ошибкой, если при ее запуске существует какое-либо другое подключение; во время операции копирования новые подключения к исходной базе данных запрещены.

В pg_database есть два полезных флага для каждой базы данных: столбцы datistemplate и datallowconn. Столбец datistemplate можно установить, чтобы показать, что база данных может выступать в качестве шаблона для CREATE DATABASE. Если этот флаг установлен, базу данных может клонировать любой пользователь с правами CREATEDB; если он не установлен, клонировать могут только суперпользователи и владелец базы данных. Если datallowconn имеет значение false, то к этой базе данных не будут разрешены новые подключения (но существующие сеансы одной установкой флага false не закрываются). База данных template0 обычно помечается как datallowconn = false, чтобы предотвратить ее модификацию. И template0, и template1 всегда должны быть помечены флагом datistemplate = true.

Примечание
template1 и template0 не имеют особого статуса, кроме того факта, что имя template1 является именем исходной базы данных по умолчанию для CREATE DATABASE. Например, можно удалить template1 и воссоздать его из template0 без каких-либо нежелательных последствий. Этот способ может быть целесообразным, если кто-то небрежно замусорил template1. (Чтобы удалить template1, нужно установить pg_database.datistemplate = false).

База данных qhb также создается при инициализации кластера баз данных. Эта база является базой данных по умолчанию при подключении пользователей и приложений. Это просто копия template1, которую при необходимости можно удалить и воссоздать.

Конфигурация базы данных

Как говорилось в главе Конфигурация сервера, сервер QHB предоставляет большое количество переменных конфигурации времени выполнения. Для многих из этих параметров можно установить значения по умолчанию, специфичные для базы данных.

Например, если по какой-то причине вы хотите выключить для какой-то базы данных оптимизатор GEQO, обычно приходится либо выключить его для всех баз данных, либо убедиться, что каждый подключающийся клиент выполняет SET geqo TO off. Чтобы установить этот параметр по умолчанию в конкретной базе данных, можно выполнить команду:

ALTER DATABASE mydb SET geqo TO off;

Это сохранит данный параметр (но не установит его сразу). При последующих подключениях к этой базе данных все будет выглядеть так, как будто команда SET geqo TO off; была выполнена незадолго до начала сеанса. Обратите внимание, что пользователи по-прежнему могут изменять этот параметр во время своих сеансов; это будет просто значение по умолчанию. Чтобы отменить любой подобный параметр, используйте ALTER DATABASE имя_бд RESET имя_переменной.

Удаление базы данных

Базы данных удаляются командой DROP DATABASE:

DROP DATABASE имя;

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

Нельзя выполнить команду DROP DATABASE, будучи подключенным к целевой базе данных. Однако можно подключиться к любой другой базе данных, включая template1. При удалении последней пользовательской базы данных указанного кластера template1 может стать единственным вариантом.

Для удобства существует также программа-оболочка для удаления баз данных dropdb:

dropdb имя_бд

(В отличие от createdb, удаление базы данных с именем текущего пользователя не является действием по умолчанию).

Табличные пространства

Табличные пространства в QHB позволяют администраторам баз данных определять места в файловой системе, где могут храниться файлы, представляющие объекты базы данных. После создания на табличное пространство можно ссылаться по имени при создании объектов базы данных.

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

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

ПРЕДУПРЕЖДЕНИЕ!
Несмотря на то, что табличные пространства расположены вне основного каталога данных QHB, они являются неотъемлемой частью кластера баз данных и не могут рассматриваться как автономная коллекция файлов данных. Они зависят от метаданных, содержащихся в главном каталоге данных и поэтому не могут быть присоединены к другому кластеру баз данных или копироваться по отдельности. Аналогичным образом при потере табличного пространства (удаление файла, сбой диска и т. д.) кластер баз данных может стать нечитаемым или перестанет запускаться. Размещение табличного пространства во временной файловой системе, например, в RAM-диске, ставит под угрозу надежность всего кластера.

Чтобы определить табличное пространство, используйте команду CREATE TABLESPACE, например:

CREATE TABLESPACE fastspace LOCATION '/ssd1/qhb/data';

Расположение должно быть существующим пустым каталогом, который принадлежит пользователю операционной системы QHB. Все объекты, впоследствии создаваемые в этом табличном пространстве, будут храниться в файлах в этом каталоге. Каталог не должен размещаться в съемном или временном хранилище, поскольку кластер может перестать функционировать, если табличное пространство отсутствует или потеряно.

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

Само создание табличного пространства должно выполняться от имени суперпользователя базы данных, но после этого можно разрешить обычным пользователям базы данных его использовать. Для этого предоставьте им право CREATE.

Таблицы, индексы и целые базы данных могут быть назначены конкретным табличным пространствам. Для этого пользователь с привилегией CREATE в данном табличном пространстве должен передать имя этого табличного пространства в качестве параметра соответствующей команде. Например, следующая команда создает таблицу в табличном пространстве space1:

CREATE TABLE foo(i int) TABLESPACE space1;

Как вариант, можно воспользоваться параметром default_tablespace:

SET default_tablespace = space1;
CREATE TABLE foo(i int);

Если для default_tablespace задано значение, отличное от пустой строки, оно предоставляет неявное предложение TABLESPACE для команд CREATE TABLE и CREATE INDEX, в которых это предложение не задано явно.

Существует также параметр temp_tablespaces, который определяет размещение временных таблиц и индексов, а также временных файлов, которые используются для таких целей, как сортировка больших наборов данных. Это может быть не одно имя, а список имен табличных пространств, поэтому нагрузку, связанную с временными объектами, можно распределить по нескольким табличным пространствам. Каждый раз, когда создается временный объект, выбирается случайное имя из списка.

Табличное пространство, связанное с базой данных, используется для хранения системных каталогов этой базы данных. Более того, это табличное пространство по умолчанию, используемое для таблиц, индексов и временных файлов, созданных в базе данных, если не задано предложение TABLESPACE и в default_tablespace или temp_tablespaces (в зависимости от ситуации) не определены никакие другие параметры. Если база данных создается без указания для нее табличного пространства, она использует то же табличное пространство, что и база-шаблон, из которой она копируется.

При инициализации кластера баз данных автоматически создаются два табличных пространства. Пространство pg_global используется для общих системных каталогов. Пространство pg_default является табличным пространством по умолчанию для баз данных template1 и template0 (и, следовательно, будет табличным пространством по умолчанию и для других баз данных, если это не переопределено предложением TABLESPACE в команде CREATE DATABASE).

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

Чтобы удалить пустое табличное пространство, используйте команду DROP TABLESPACE.

Чтобы просмотреть набор существующих табличных пространств, изучите системный каталог pg_tablespace, например:

SELECT spcname FROM pg_tablespace;

Метакоманда \db программы psql также позволяет вывести список существующих табличных пространств.

Для упрощения реализации табличных пространств QHB использует символические ссылки. Это означает, что табличные пространства могут использоваться только в системах, поддерживающих символические ссылки.

Каталог $PGDATA/pg_tblspc содержит символические ссылки, указывающие на каждое из встроенных табличных пространств, определенных в кластере. Хотя это и не рекомендуется, но можно настроить структуру табличных пространств вручную, переопределив эти ссылки. Ни при каких обстоятельствах не выполняйте эту операцию во время работы сервера.