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

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

Обзор

База данных — это именованная коллекция объектов SQL («объекты базы данных»). Как правило, каждый объект базы данных (таблицы, функции и т. д.) принадлежит одной и только одной базе данных. (Однако есть несколько системных каталогов, например, pg_database, которые принадлежат всему экземпляру и доступны из каждой базы данных внутри экземпляра). Точнее, база данных представляет собой набор схем, а схемы содержат таблицы, функции и т. д. Таким образом, полная иерархия: сервер, база данных, схема, таблица (или какой-либо другой вид объекта, например функция).

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

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

SELECT datname FROM pg_database;

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

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

Чтобы создать базу данных, сервер QHB должен быть запущен (см. раздел Запуск сервера базы данных).

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

CREATE DATABASE name;

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

Создание баз данных - ограниченная операция. См. раздел Атрибуты ролей для получения разрешения.

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

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

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

createdb dbname

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

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

CREATE DATABASE dbname OWNER rolename;

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

createdb -O rolename dbname

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

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

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 dbname TEMPLATE template0;

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

createdb -T template0 dbname

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

Можно создать дополнительные базы данных шаблонов, и в действительности можно скопировать любую базу данных в экземпляре, указав ее имя в качестве шаблона для CREATE DATABASE. Тем не менее, важно понимать, что это не предназначено для универсального средства «COPY 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 dbname RESET varname.

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

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

DROP DATABASE name;

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

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

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

dropdb dbname

(В отличие от 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 содержит символические ссылки, которые указывают на каждое из встроенных табличных пространств, определенных в экземпляре. Хотя это и не рекомендуется, но можно вручную настроить макет табличного пространства, переопределив эти ссылки. Ни при каких обстоятельствах не выполняйте эту операцию во время работы сервера.