Организация данных
Логическая структура
12
Авторские права
© Postgres Professional, 2017–2021
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Базы данных и шаблоны
Схемы и путь поиска
Специальные схемы
Системный каталог
3
новая БДtemplate1template0
postgres
Кластер баз данных
Инициализация кластера создает три базы данных
Новая база всегда клонируется из существующей
таблица
таблица
объект
таблица
таблица
объект
таблица
таблица
объект
таблица
таблица
объект
не меняется
подключение
по умолчанию
общие
изменения
CREATE DATABASE
Экземпляр PostgreSQL управляет несколькими базами данных —
кластером. При инициализации кластера (автоматически при установке
PostgreSQL либо вручную командой initdb) создаются три одинаковые
базы данных. Все остальные БД, создаваемые пользователем,
клонируются из какой-либо существующей.
Шаблонная БД template1 используется по умолчанию для создания
новых баз данных. В нее можно добавить объекты и расширения,
которые будут копироваться в каждую новую базу данных.
Шаблон template0 не должен изменяться. Он нужен как минимум в двух
ситуациях. Во-первых, для восстановления БД из резервной копии,
выполненной утилитой pg_dump (как рассматривается в теме
«Резервное копирование. Логическое резервирование»). Во-вторых,
при создании новой БД с кодировкой, отличной от указанной при
инициализации кластера (подробнее обсуждается в курсе DBA2).
База данных postgres используется при подключении по умолчанию
пользователем postgres. Она не является обязательной, но некоторые
утилиты предполагают ее наличие, поэтому ее не рекомендуется
удалять, даже если она не нужна.
5
Схемы
Пространство имен для объектов
разделение объектов на логические группы
предотвращение конфликта имен между приложениями
Схема и пользователь — разные сущности
Специальные схемы
public — по умолчанию в ней создаются все объекты
pg_catalog — системный каталог
information_schema — вариант системного каталога
pg_temp — для временных таблиц
…
Схемы представляют собой пространства имен для объектов БД. Они
позволяют разделить объекты на логические группы для управления
ими, предотвратить конфликты имен при работе нескольких
пользователей или приложений.
В PostgreSQL схема и пользователь — разные сущности (хотя
настройки по умолчанию упрощают работу с одноименными схемами).
Существует несколько специальных схем, обычно присутствующих
в каждой базе данных.
Схема public используется по умолчанию для хранения объектов, если
не выполнены иные настройки.
Схема pg_catalog хранит объекты системного каталога. Системный
каталог — это метаинформация об объектах, принадлежащих кластеру,
которая хранится в самом кластере в виде таблиц. Альтернативное
представление системного каталога (определенное в стандарте SQL)
дает схема information_schema.
Схема pg_temp служит для хранения временных таблиц. (На самом
деле таблицы создаются в схемах pg_temp_1, pg_temp_2 и т. п. —
у каждого пользователя своя схема. Но обращаются все пользователи
к ней как к pg_temp.)
Есть и другие схемы, но они носят технический характер.
6
template1postgres
Базы и схемы кластера
pg_catalog
таблица
таблица
объект
public
таблица
таблица
объект
pg_catalog public
таблица
таблица
объект
таблица
таблица
объект
таблица
таблица
объект
таблица
таблица
объект
таблица
таблица
объект
таблица
таблица
объект
таблица
таблица
объект
системный
каталог
общие
объекты
кластера
схема
таблица
таблица
объект
таблица
таблица
объект
Схемы принадлежат базам данных, все объекты БД распределены по
каким-либо схемам.
Однако несколько таблиц системного каталога хранят информацию,
общую для всего кластера. Это список баз данных, список
пользователей и некоторые другие сведения. Эти таблицы хранятся вне
какой-либо базы данных, но при этом одинаково видны из каждой БД.
Таким образом, клиент, подключенный к какой-либо базе данных, видит
в системном каталоге описание объектов не только данной базы, но и
общих объектов кластера. Описание объектов других баз данных
можно получить, только подключившись к ним.
8
Путь поиска
Определение схемы объекта
квалифицированное имя (схем а. имя) явно определяет схему
имя без квалификатора проверяется в схемах, указанных в пути поиска
Путь поиска
определяется параметром search_path,
реальное значение — функция current_schemas
не включаются несуществующие схемы и схемы, к которым нет доступа
схемы pg_temp и pg_catalog неявно включены первыми,
если не указаны в search_path
первая явно указанная в пути схема используется для создания объектов
При указании объекта надо определить, о какой схеме идет речь,
поскольку в разных схемах могут храниться объекты с одинаковыми
именами.
Если имя объекта квалифицировано именем схемы, то все просто —
используется явно указанная схема. Если имя использовано без
квалификатора, PostgreSQL пытается найти имя в одной из схем,
перечисленных в пути поиска, который определяется
конфигурационным параметром search_path.
Путь поиска может отличаться от значения параметра search_path.
В путь поиска не включаются несуществующие схемы из search_path,
а также схемы, к которым у пользователя нет доступа (разграничению
доступа посвящена одна из последующих тем курса). Кроме того,
в начало пути поиска неявно добавляются:
●
схема pg_catalog, чтобы всегда иметь доступ к системному каталогу
●
схема pg_temp, если пользователь создавал временные объекты
Реальный путь поиска, включая неявные схемы, возвращает вызов
функции: current_schemas(true). Схемы перебираются в указанном
в пути поиска порядке, слева направо. Если в схеме нет объекта
с нужным именем, поиск продолжается в следующей схеме. Можно
провести аналогию между путем поиска search_path и путем PATH в
операционных системах.
При создании нового объекта с именем без квалификатора он попадает
в первую явно указанную в пути схему (не считая несуществующих и
тех, на которые недостаточно прав).
10
Системный каталог
Описание всех объектов кластера
набор таблиц в каждой базе данных (схема pg_catalog)
и несколько глобальных объектов кластера
набор представлений для удобства
Доступ
запросы SQL, специальные команды psql
Правила организации
названия таблиц начинаются с «pg_»
имена столбцов содержат трехбуквенный префикс
в качестве ключа используется столбец oid типа oid
названия объектов хранятся в нижнем регистре
Системный каталог хранит метаинформацию об объектах кластера.
В каждой базе данных имеется собственный набор таблиц,
описывающих объекты этой конкретной базы. Также есть несколько
таблиц, общих для всего кластера и не принадлежащих никакой
отдельной БД. Для удобства над таблицами также определены
несколько представлений.
К системному каталогу можно обращаться с помощью обычных
запросов SQL, а psql имеет целый ряд команд для удобства просмотра.
Таблицы системного каталога не обновляют напрямую; они изменяются
автоматически при выполнении команд DDL.
Все имена таблиц системного каталога начинаются с pg_, например,
pg_database. Столбцы таблиц начинаются с префикса, обычно
соответствующего имени таблицы, например, datname. Имена объектов
хранятся в нижнем регистре, например, 'postgres'.
В таблицах системного каталога первичные ключи не определены
явным образом, но в большинстве случаев в качестве первичного
ключа выступает столбец oid, имеющий специальный тип oid — object
identifier (целое 32-битное число). До версии PostgreSQL 12 этот
столбец был скрытым (его можно было увидеть, только явно указав имя
в списке SELECT).
12
Итоги
Логически
кластер содержит базы данных,
базы данных — схемы,
схемы — конкретные объекты (таблицы, индексы и т. п.)
Базы данных создаются клонированием существующих
Схема объекта определяется по пути поиска
Полное описание содержимого кластера баз данных
хранится в системном каталоге
13
Практика
1. В новой базе данных создайте схему, названную так же,
как и пользователь. Создайте схему app.
Создайте несколько таблиц в обеих схемах.
2. Получите в psql описание созданных схем и список
всех таблиц в них.
3. Установите путь поиска так, чтобы при подключении к базе
данных таблицы из обеих схем были доступны по
неквалифицированному имени; приоритет должна иметь
«пользовательская» схема.
Проверьте правильность настройки.
