Что такое schema в postgresql
Что такое schema в postgresql
Кластер баз данных PostgreSQL содержит один или несколько именованных экземпляров баз. На уровне кластера создаются роли и некоторые другие объекты. При этом в рамках одного подключения к серверу можно обращаться к данным только одной базы — той, что была выбрана при установлении соединения.
Примечание
Пользователи кластера не обязательно будут иметь доступ ко всем базам данных этого кластера. Тот факт, что роли создаются на уровне кластера, означает только то, что в кластере не может быть двух ролей joe в разных базах данных, хотя система позволяет ограничить доступ joe только некоторыми базами данных.
Есть несколько возможных объяснений, для чего стоит применять схемы:
Чтобы одну базу данных могли использовать несколько пользователей, независимо друг от друга.
Чтобы объединить объекты базы данных в логические группы для облегчения управления ими.
Чтобы в одной базе сосуществовали разные приложения, и при этом не возникало конфликтов имён.
Схемы в некоторым смысле подобны каталогам в операционной системе, но они не могут быть вложенными.
5.8.1. Создание схемы
Чтобы создать объекты в схеме или обратиться к ним, указывайте полное имя, состоящее из имён схемы и объекта, разделённых точкой:
Этот синтаксис работает везде, где ожидается имя таблицы, включая команды модификации таблицы и команды обработки данных, обсуждаемые в следующих главах. (Для краткости мы будем говорить только о таблицах, но всё это распространяется и на другие типы именованных объектов, например, типы и функции.)
Есть ещё более общий синтаксис
но в настоящее время он поддерживается только для формального соответствия стандарту SQL. Если вы указываете базу данных, это может быть только база данных, к которой вы подключены.
Таким образом, создать таблицу в новой схеме можно так:
Чтобы удалить пустую схему (не содержащую объектов), выполните:
Удалить схему со всеми содержащимися в ней объектами можно так:
Стоящий за этим общий механизм описан в Разделе 5.13.
Часто бывает нужно создать схему, владельцем которой будет другой пользователь (это один из способов ограничения пользователей пространствами имён). Сделать это можно так:
Вы даже можете опустить имя схемы, в этом случае именем схемы станет имя пользователя. Как это можно применять, описано в Подразделе 5.8.6.
5.8.2. Схема public
5.8.3. Путь поиска схемы
Везде писать полные имена утомительно, и часто всё равно лучше не привязывать приложения к конкретной схеме. Поэтому к таблицам обычно обращаются по неполному имени, состоящему просто из имени таблицы. Система определяет, какая именно таблица подразумевается, используя путь поиска, который представляет собой список просматриваемых схем. Подразумеваемой таблицей считается первая подходящая таблица, найденная в схемах пути. Если подходящая таблица не найдена, возникает ошибка, даже если таблица с таким именем есть в других схемах базы данных.
Первая схема в пути поиска называется текущей. Эта схема будет использоваться не только при поиске, но и при создании объектов — она будет включать таблицы, созданные командой CREATE TABLE без указания схемы.
Чтобы узнать текущий тип поиска, выполните следующую команду:
В конфигурации по умолчанию она возвращает:
Первый элемент ссылается на схему с именем текущего пользователя. Если такой схемы не существует, ссылка на неё игнорируется. Второй элемент ссылается на схему public, которую мы уже видели.
Первая существующая схема в пути поиска также считается схемой по умолчанию для новых объектов. Именно поэтому по умолчанию объекты создаются в схеме public. При указании неполной ссылки на объект в любом контексте (при модификации таблиц, изменении данных или в запросах) система просматривает путь поиска, пока не найдёт соответствующий объект. Таким образом, в конфигурации по умолчанию неполные имена могут относиться только к объектам в схеме public.
Чтобы добавить в путь нашу новую схему, мы выполняем:
И так как myschema — первый элемент в пути, новые объекты будут по умолчанию создаваться в этой схеме.
Мы можем также написать:
Тогда мы больше не сможем обращаться к схеме public, не написав полное имя объекта. Единственное, что отличает схему public от других, это то, что она существует по умолчанию, хотя её так же можно удалить.
В Разделе 9.25 вы узнаете, как ещё можно манипулировать путём поиска схем.
Как и для имён таблиц, путь поиска аналогично работает для имён типов данных, имён функций и имён операторов. Имена типов данных и функций можно записать в полном виде так же, как и имена таблиц. Если же вам нужно использовать в выражении полное имя оператора, для этого есть специальный способ — вы должны написать:
Такая запись необходима для избежания синтаксической неоднозначности. Пример такого выражения:
На практике пользователи часто полагаются на путь поиска, чтобы не приходилось писать такие замысловатые конструкции.
5.8.4. Схемы и права
По умолчанию пользователь не может обращаться к объектам в чужих схемах. Чтобы изменить это, владелец схемы должен дать пользователю право USAGE для данной схемы. Чтобы пользователи могли использовать объекты схемы, может понадобиться назначить дополнительные права на уровне объектов.
5.8.5. Схема системного каталога
5.8.6. Шаблоны использования
Схемам можно найти множество применений. Для защиты от влияния недоверенных пользователей на поведение запросов других пользователей предлагается шаблон безопасного использования схем, но если этот шаблон не применяется в базе данных, пользователи, желающие безопасно выполнять в ней запросы, должны будут принимать защитные меры в начале каждого сеанса. В частности, они должны начинать каждый сеанс с присвоения пустого значения переменной search_path или каким-либо другим образом удалять из search_path схемы, доступные для записи обычным пользователям. С конфигурацией по умолчанию легко реализуются следующие шаблоны использования:
Сохранить поведение по умолчанию. Все пользователи неявно обращаются к схеме public. Тем самым имитируется ситуация с полным отсутствием схем, что позволяет осуществить плавный переход из среды без схем. Однако данный шаблон ни в коем случае нельзя считать безопасным. Он подходит, только если в базе данных имеется всего один либо несколько доверяющих друг другу пользователей.
При любом подходе, устанавливая совместно используемые приложения (таблицы, которые нужны всем, дополнительные функции сторонних разработчиков и т. д.), помещайте их в отдельные схемы. Не забудьте дать другим пользователям права для доступа к этим схемам. Тогда пользователи смогут обращаться к этим дополнительным объектам по полному имени или при желании добавят эти схемы в свои пути поиска.
5.8.7. Переносимость
Конечно, есть СУБД, в которых вообще не реализованы схемы или пространства имён поддерживают (возможно, с ограничениями) обращения к другим базам данных. Если вам потребуется работать с этими системами, максимальной переносимости вы достигнете, вообще не используя схемы.
Что такое schema в postgresql
CREATE SCHEMA — создать схему
Синтаксис
Описание
CREATE SCHEMA создаёт новую схему в текущей базе данных. Имя схемы должно отличаться от имён других существующих схем в текущей базе данных.
Команда CREATE SCHEMA может дополнительно включать подкоманды, создающие объекты в новой схеме. Эти подкоманды по сути воспринимаются как отдельные команды, выполняемые после создания схемы, за исключением того, что с предложением AUTHORIZATION все создаваемые объекты будут принадлежать указанному в нём пользователю.
Параметры
Имя пользователя (роли), назначаемого владельцем новой схемы. Если опущено, по умолчанию владельцем будет пользователь, выполняющий команды. Чтобы назначить владельцем создаваемой схемы другую роль, необходимо быть непосредственным или опосредованным членом этой роли, либо суперпользователем. элемент_схемы
Замечания
Чтобы создать схему, пользователь должен иметь право CREATE в текущей базе данных. (Разумеется, на суперпользователей это условие не распространяется.)
Примеры
Создание схемы для пользователя joe ; схема так же получит имя joe :
Создание схемы, в которой сразу создаются таблица и представление:
Заметьте, что отдельные подкоманды не завершаются точкой с запятой.
Следующие команды приводят к тому же результату другим способом:
Совместимость
Стандарт SQL также допускает в команде CREATE SCHEMA предложение DEFAULT CHARACTER SET и дополнительные типы подкоманд, которые PostgreSQL в настоящее время не принимает.
В стандарте SQL говорится, что подкоманды в CREATE SCHEMA могут следовать в любом порядке. Однако текущая реализация в PostgreSQL не воспринимает все возможные варианты ссылок вперёд в подкомандах, поэтому иногда возникает необходимость переупорядочить подкоманды, чтобы исключить такие ссылки.
Согласно стандарту SQL, владелец схемы всегда владеет всеми объектами в ней, но PostgreSQL допускает размещение в схемах объектов, принадлежащих не владельцу схемы. Такая ситуация возможна, только если владелец схемы даст право CREATE в этой схеме кому-либо другому, либо объекты в ней будет создавать суперпользователь.
Что такое schema в postgresql
CREATE SCHEMA — создать схему
Синтаксис
Описание
CREATE SCHEMA создаёт новую схему в текущей базе данных. Имя схемы должно отличаться от имён других существующих схем в текущей базе данных.
Команда CREATE SCHEMA может дополнительно включать подкоманды, создающие объекты в новой схеме. Эти подкоманды по сути воспринимаются как отдельные команды, выполняемые после создания схемы, за исключением того, что с предложением AUTHORIZATION все создаваемые объекты будут принадлежать указанному в нём пользователю.
Параметры
Имя пользователя (роли), назначаемого владельцем новой схемы. Если опущено, по умолчанию владельцем будет пользователь, выполняющий команды. Чтобы назначить владельцем создаваемой схемы другую роль, необходимо быть непосредственным или опосредованным членом этой роли, либо суперпользователем. элемент_схемы
Замечания
Чтобы создать схему, пользователь должен иметь право CREATE в текущей базе данных. (Разумеется, на суперпользователей это условие не распространяется.)
Примеры
Создание схемы для пользователя joe ; схема так же получит имя joe :
Создание схемы, в которой сразу создаются таблица и представление:
Заметьте, что отдельные подкоманды не завершаются точкой с запятой.
Следующие команды приводят к тому же результату другим способом:
Совместимость
Стандарт SQL также допускает в команде CREATE SCHEMA предложение DEFAULT CHARACTER SET и дополнительные типы подкоманд, которые PostgreSQL в настоящее время не принимает.
В стандарте SQL говорится, что подкоманды в CREATE SCHEMA могут следовать в любом порядке. Однако текущая реализация в PostgreSQL не воспринимает все возможные варианты ссылок вперёд в подкомандах, поэтому иногда возникает необходимость переупорядочить подкоманды, чтобы исключить такие ссылки.
Согласно стандарту SQL, владелец схемы всегда владеет всеми объектами в ней, но PostgreSQL допускает размещение в схемах объектов, принадлежащих не владельцу схемы. Такая ситуация возможна, только если владелец схемы даст право CREATE в этой схеме кому-либо другому, либо объекты в ней будет создавать суперпользователь.
Что такое schema в postgresql
Кластер баз данных Postgres Pro содержит один или несколько именованных экземпляров баз. На уровне кластера создаются пользователи и группы, но данные могут относиться только к базам данных. При этом в рамках одного подключения к серверу можно обращаться к данным только одной базы данных, указанной при установлении соединения.
Примечание
Пользователи кластера не обязательно будут иметь доступ ко всем базам данных этого кластера. То, что пользователи создаются на уровне кластера, означает только, что в нём не может быть двух пользователей joe в разных базах данных, хотя система позволяет ограничить доступ joe только некоторыми базами данных.
Есть несколько возможных объяснений, для чего стоит применять схемы:
Чтобы одну базу данных могли использовать несколько пользователей, независимо друг от друга.
Чтобы объединить объекты базы данных в логические группы для облегчения управления ими.
Чтобы в одной базе сосуществовали разные приложения, и при этом не возникало конфликтов имён.
Схемы в некоторым смысле подобны каталогам в операционной системе, но они не могут быть вложенными.
5.8.1. Создание схемы
Чтобы создать объекты в схеме или обратиться к ним, указывайте полное имя, состоящее из имён схемы и объекта, разделённых точкой:
Этот синтаксис работает везде, где ожидается имя таблицы, включая команды модификации таблицы и команды обработки данных, обсуждаемые в следующих главах. (Для краткости мы будем говорить только о таблицах, но всё это распространяется и на другие типы именованных объектов, например, типы и функции.)
Есть ещё более общий синтаксис
но в настоящее время он поддерживается только для формального соответствия стандарту SQL. Если вы указываете базу данных, это может быть только база данных, к которой вы подключены.
Таким образом, создать таблицу в новой схеме можно так:
Чтобы удалить пустую схему (не содержащую объектов), выполните:
Удалить схему со всеми содержащимися в ней объектами можно так:
Стоящий за этим общий механизм описан в Разделе 5.13.
Часто бывает нужно создать схему, владельцем которой будет другой пользователь (это один из способов ограничения пользователей пространствами имён). Сделать это можно так:
Вы даже можете опустить имя схемы, в этом случае именем схемы станет имя пользователя. Как это можно применять, описано в Подразделе 5.8.6.
5.8.2. Схема public
5.8.3. Путь поиска схемы
Везде писать полные имена утомительно, и часто всё равно лучше не привязывать приложения к конкретной схеме. Поэтому к таблицам обычно обращаются по неполному имени, состоящему просто из имени таблицы. Система определяет, какая именно таблица подразумевается, используя путь поиска, который представляет собой список просматриваемых схем. Подразумеваемой таблицей считается первая подходящая таблица, найденная в схемах пути. Если подходящая таблица не найдена, возникает ошибка, даже если таблица с таким именем есть в других схемах базы данных.
Первая схема в пути поиска называется текущей. Эта схема будет использоваться не только при поиске, но и при создании объектов — она будет включать таблицы, созданные командой CREATE TABLE без указания схемы.
Чтобы узнать текущий тип поиска, выполните следующую команду:
В конфигурации по умолчанию она возвращает:
Первый элемент ссылается на схему с именем текущего пользователя. Если такой схемы не существует, ссылка на неё игнорируется. Второй элемент ссылается на схему public, которую мы уже видели.
Первая существующая схема в пути поиска также считается схемой по умолчанию для новых объектов. Именно поэтому по умолчанию объекты создаются в схеме public. При указании неполной ссылки на объект в любом контексте (при модификации таблиц, изменении данных или в запросах) система просматривает путь поиска, пока не найдёт соответствующий объект. Таким образом, в конфигурации по умолчанию неполные имена могут относиться только к объектам в схеме public.
Чтобы добавить в путь нашу новую схему, мы выполняем:
И так как myschema — первый элемент в пути, новые объекты будут по умолчанию создаваться в этой схеме.
Мы можем также написать:
Тогда мы больше не сможем обращаться к схеме public, не написав полное имя объекта. Единственное, что отличает схему public от других, это то, что она существует по умолчанию, хотя её так же можно удалить.
В Разделе 9.25 вы узнаете, как ещё можно манипулировать путём поиска схем.
Как и для имён таблиц, путь поиска аналогично работает для имён типов данных, имён функций и имён операторов. Имена типов данных и функций можно записать в полном виде так же, как и имена таблиц. Если же вам нужно использовать в выражении полное имя оператора, для этого есть специальный способ — вы должны написать:
Такая запись необходима для избежания синтаксической неоднозначности. Пример такого выражения:
На практике пользователи часто полагаются на путь поиска, чтобы не приходилось писать такие замысловатые конструкции.
5.8.4. Схемы и права
По умолчанию пользователь не может обращаться к объектам в чужих схемах. Чтобы изменить это, владелец схемы должен дать пользователю право USAGE для данной схемы. Чтобы пользователи могли использовать объекты схемы, может понадобиться назначить дополнительные права на уровне объектов.
5.8.5. Схема системного каталога
5.8.6. Шаблоны использования
Схемам можно найти множество применений. Хотя есть несколько шаблонов использования, легко поддерживаемых стандартной конфигурацией, только один из них достаточно безопасен, когда одни пользователи базы данных не доверяют другим:
Сохранить поведение по умолчанию. Все пользователи неявно обращаются к схеме public. Тем самым имитируется ситуация с полным отсутствием схем, что позволяет осуществить плавный переход из среды без схем. Однако при этом любой пользователь может выполнять произвольные запросы от имени любого пользователя, который не позаботится о своей защите специально. Этот шаблон подходит, только если в базе данных имеется всего один или несколько взаимно доверяющих пользователей.
При любом подходе, устанавливая совместно используемые приложения (таблицы, которые нужны всем, дополнительные функции сторонних разработчиков и т. д.), помещайте их в отдельные схемы. Не забудьте дать другим пользователям права для доступа к этим схемам. Тогда пользователи смогут обращаться к этим дополнительным объектам по полному имени или при желании добавят эти схемы в свои пути поиска.
5.8.7. Переносимость
Конечно, есть СУБД, в которых вообще не реализованы схемы или пространства имён поддерживают (возможно, с ограничениями) обращения к другим базам данных. Если вам потребуется работать с этими системами, максимальной переносимости вы достигнете, вообще не используя схемы.
Sysadminium
База знаний системного администратора
Схемы и шаблоны в СУБД PostgreSQL
В этой статье поговорим про схемы в базах данных PostgreSQL и шаблоны. Для понимания, иерархия такая: СУБД > Базы данных > Схемы > Таблицы (и другие объекты).
Базы данных и шаблоны
Когда мы создаём новые кластер командой initdb у нас создается 3 одинаковые базы данных:
База postgres используется, чтобы по умолчанию к ней подключаться. Принципиально она не нужна, но есть приложения которым она может понадобится, поэтому лучше её не удалять.
Две дополнительные базы template0 и template1 – это шаблоны. Новая база всегда создается путём копирования из другой шаблонной базы. По умолчанию для шаблона используется база template1. Поэтому, если у вас есть расширения, которыми вы пользуетесь, можете их заранее создать в template1.
Основная задача базы template0 заключается в том, что бы она никогда не менялась. Она используется, например при загрузке базы из дампа. Вначале вы создаёте базу из template0, а затем туда заливаете сохранённый дамп. Также база template0 позволяет создавать базы с использованием категорий локалей не по умолчанию (LC_COLLATE, LC_CTYPE).
Схемы
Схема – это пространство имён для объектов внутри базы данных.
Суть работы схемы можно представить так: мы все складываем не все в одну большую кучу, а по небольшим отдельным кучкам. Например, как в файловой системе, всё кладем не в один каталог, а раскладываем по подкаталогам.
Вот пример работы со схемами! В одну схему поместим объекты для модуля “логистика”, а в другую для модуля “финансы” и так далее.
В базе данных может быть несколько схем. По умолчанию существует две глобальные схемы. Глобальные они потому-что не принадлежат какой-то определённой базе данных:
Также вы можете создать свои дополнительные схемы.
Путь поиска
Если мы не указываем схему, то нужно понять, в какой схеме искать или создавать объект. Определяют схему с помощью пути поиска, который задается параметром search_path.
В параметре search_path можно через запятую перечислить схемы, в которых нужно искать объект, если мы не указываем схему явно. search_path это что-то вроде переменной окружения PATH в Linux, для поиска команд.
Из search_path исключаются:
А некоторые схемы всегда добавляются в search_path, даже если мы их туда не запишем. Например pg_catalog.
Реальное значение search_path показывает функция current_schemas().
При создании нового объекта, он будет помещаться в первую указанную в search_path схему. Если посмотреть пример выше, то так как у нас нет права писать в схему pg_catalog, объекты будут создаваться в public.
Специальные схемы, временные объекты
К специальным схемам относят:
Временные таблицы – существуют на время сеанса или транзакции. Они не журналируются и не попадают в общую память. Чтобы реализовать временную таблицу в postgres применяет временные схемы.
Схема pg_temp_N – автоматически создается для временных таблиц. Такая схема тоже по умолчанию находится в search_path. По окончанию все объекты временной схемы удаляются, а сама схема остается. Оставшаяся временная схема может использоваться для новых временных таблиц, новой транзакции или сеанса.
Практика
Список баз
Настройка шаблона template1
Проверим, доступна ли нам функция шифрования в этой базе, если не доступна, то создадим необходимое расширение и повторим проверку:
В случае если у вас не было скомпилировано это расширение, то в первом уроке мы разбирали как компилировать postgres и его расширения. Примерно это делается так:
Теперь создадим новую базу данных и так как она была создана из шаблона template1, то и расширение pgcrypto здесь уже установлено:
Выше мы вначале отключились от базы template1, так как использовать шаблон можно только, если к нему никто не подключен!
Редактирование базы
Теперь переименуем созданную базу данных (ALTER DATABASE … RENAME TO … ), предварительно отключившись от неё:
С помощью ALTER DATABASE можно менять и другие параметры, например число доступных подключений:
Смотрим размер базы данных
Размер базы данных можно считать с помощью функции pg_database_size(). Для перевода из байтов в более удобочитаемые единицы, можно использовать функцию pg_size_pretty():
Вот мы и узнали размер пустой базы!
Работа со схемами
Список схем можно узнать с помощью команды \dn:
Это не все схемы, здесь исключены служебные схемы!
Создадим новую схему, предварительно подключившись к нашей базе:
На путь поиска схем можно посмотреть с помощью search_path:
Это означает, что при создании таблицы, она попытается попасть в схему “$user” (postgres), но такой схемы нет. А затем попадет в схему public! И наоборот, при обращении к таблице она будет искаться в начале в “$user”, а затем в public!
Дополнительно можем посмотреть текущие схемы, в этой базе данных с помощью функции current_schemas():
Здесь мы видим служебную схему pg_catalog, но к ней нет доступа. Поэтому судя по пути поиска и по текущим схемам, можем сказать что по умолчанию таблицы будут создаваться в схеме public.
Теперь создадим таблицу “t“, в ней создадим строку и с помощью команды \dt посмотрим в какой схеме оказалась эта таблица:
Выше мы видим, что не указав полный путь мы получили ошибку!
Установить путь поиска можно так:
Но это установит путь только для текущего сеанса!
Выше команда означает, что при подключении к базе appdb будет выполняться команда SET search_path = public, app.
Теперь создадим временную таблицу с таким-же именем “t” и посмотрим что из этого выйдет:
Мы видим только временную таблицу, а первую созданную таблицу уже не видим в списке баз!
Посмотрим на текущий путь поиска с помощью функции current_schemas (). А затем вставим строку во временную таблицу и прочитаем её. И далее прочитаем строки из обычной таблицы используя полный путь:
При выходе из сеанса все объекты во временной схеме уничтожаются:
Удаление схемы и базы
Схему нельзя удалить, если в ней есть какие-нибудь объекты. А для удаления схемы вместе с объектами нужно использовать опцию CASCADE:
Базу данных можно удалить, если к ней нет активных подключений: