Как в pgadmin сделать схему бд

Как в pgadmin сделать схему бд

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

После этого нам откроется следующая программа pgAdmin. При открытии также отображится окно для ввода пароля для подключения к серверу Postgres:

Здесь необходимо ввести пароль для суперпользователя postgres, который был задан при установке PostgreSQL.

После успешного логина нам откроется содержимое сервера:

В частности, в узле Databases мы можем увидеть все имеющиеся базы данных. По умолчанию здесь есть только одна база данных — postgres.

Также в правой части мы можем увидеть узел Login/Group Roles , который предназначен для управления пользователями и их ролями.

И третий узел — Tablespaces позволяет управлять местом хранения файлов баз данных.

Теперь создадим свою базу данных. Для этого нажмем правой кнопкой мыши на узел Databases . И далее в контекстном меню выберем Create -> Database.

После этого нам отобразится окно для создания базы данных. Введем название для БД, например, test1 и нажмем на кнопку «Save»:

После этого в древовидном меню слева отобразится содержимое созданной базы данных test1:

Как в pgadmin сделать схему бд

CREATE SCHEMA — создать схему

Синтаксис

CREATE SCHEMA имя_схемы [ AUTHORIZATION указание_роли ] [ элемент_схемы [ . ] ] CREATE SCHEMA AUTHORIZATION указание_роли [ элемент_схемы [ . ] ] CREATE SCHEMA IF NOT EXISTS имя_схемы [ AUTHORIZATION указание_роли ] CREATE SCHEMA IF NOT EXISTS AUTHORIZATION указание_роли Здесь указание_роли: имя_пользователя | CURRENT_USER | SESSION_USER

Описание

CREATE SCHEMA создаёт новую схему в текущей базе данных. Имя схемы должно отличаться от имён других существующих схем в текущей базе данных.

Схема по сути представляет собой пространство имён: она содержит именованные объекты (таблицы, типы данных, функции и операторы), имена которых могут совпадать с именами других объектов, существующих в других схемах. Для обращения к объекту нужно либо « дополнить » его имя именем схемы в виде префикса, либо установить путь поиска, включающий требуемую схему. Команда CREATE , в которой указывается неполное имя объекта, создаёт объект в текущей схеме (схеме, стоящей первой в пути поиска; узнать её позволяет функция current_schema ).

Команда CREATE SCHEMA может дополнительно включать подкоманды, создающие объекты в новой схеме. Эти подкоманды по сути воспринимаются как отдельные команды, выполняемые после создания схемы, за исключением того, что с предложением AUTHORIZATION все создаваемые объекты будут принадлежать указанному в нём пользователю.

Параметры

Имя создаваемой схемы. Если оно опущено, именем схемы будет имя_пользователя . Это имя не может начинаться с pg_ , так как такие имена зарезервированы для системных схем. имя_пользователя

Имя пользователя (роли), назначаемого владельцем новой схемы. Если опущено, по умолчанию владельцем будет пользователь, выполняющий команды. Чтобы назначить владельцем создаваемой схемы другую роль, необходимо быть непосредственным или опосредованным членом этой роли, либо суперпользователем. элемент_схемы

Оператор SQL, определяющий объект, создаваемый в новой схеме. В настоящее время CREATE SCHEMA может содержать только подкоманды CREATE TABLE , CREATE VIEW , CREATE INDEX , CREATE SEQUENCE , CREATE TRIGGER и GRANT . Создать объекты других типов можно отдельными командами после создания схемы. IF NOT EXISTS

Не делать ничего (только выдать замечание), если схема с таким именем уже существует. Когда используется это указание, эта команда не может содержать подкоманды элемент_схемы .

Замечания

Чтобы создать схему, пользователь должен иметь право CREATE в текущей базе данных. (Разумеется, на суперпользователей это условие не распространяется.)

Примеры

CREATE SCHEMA myschema;

Создание схемы для пользователя joe ; схема так же получит имя joe :

CREATE SCHEMA AUTHORIZATION joe;

Создание схемы с именем test , владельцем которой будет пользователь joe , если только схема test ещё не существует. (Является ли владельцем существующей схемы пользователь joe , значения не имеет.)

CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;

Создание схемы, в которой сразу создаются таблица и представление:

CREATE SCHEMA hollywood CREATE TABLE films (title text, release date, awards text[]) CREATE VIEW winners AS SELECT title, release FROM films WHERE awards IS NOT NULL;

Заметьте, что отдельные подкоманды не завершаются точкой с запятой.

Следующие команды приводят к тому же результату другим способом:

CREATE SCHEMA hollywood; CREATE TABLE hollywood.films (title text, release date, awards text[]); CREATE VIEW hollywood.winners AS SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;

Совместимость

Стандарт SQL также допускает в команде CREATE SCHEMA предложение DEFAULT CHARACTER SET и дополнительные типы подкоманд, которые PostgreSQL в настоящее время не принимает.

В стандарте SQL говорится, что подкоманды в CREATE SCHEMA могут следовать в любом порядке. Однако текущая реализация в PostgreSQL не воспринимает все возможные варианты ссылок вперёд в подкомандах, поэтому иногда возникает необходимость переупорядочить подкоманды, чтобы исключить такие ссылки.

Согласно стандарту SQL, владелец схемы всегда владеет всеми объектами в ней, но PostgreSQL допускает размещение в схемах объектов, принадлежащих не владельцу схемы. Такая ситуация возможна, только если владелец схемы даст право CREATE в этой схеме кому-либо другому, либо объекты в ней будет создавать суперпользователь.

Указание IF NOT EXISTS является расширением PostgreSQL .

См. также

Пред.	Наверх	След.
CREATE RULE	Начало	CREATE SEQUENCE

Как в pgadmin сделать схему бд

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

Примечание

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

База данных содержит одну или несколько именованных схем, которые в свою очередь содержат таблицы. Схемы также содержат именованные объекты других видов, включая типы данных, функции и операторы. Одно и то же имя объекта можно свободно использовать в разных схемах, например и schema1 , и myschema могут содержать таблицы с именем mytable . В отличие от баз данных, схемы не ограничивают доступ к данным: пользователь может обращаться к объектам в любой схеме текущей базы данных, если ему назначены соответствующие права.

Есть несколько возможных объяснений, для чего стоит применять схемы:

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

Чтобы объединить объекты базы данных в логические группы для облегчения управления ими.

Схемы в некоторым смысле подобны каталогам в операционной системе, но они не могут быть вложенными.

5.8.1. Создание схемы

Для создания схемы используется команда CREATE SCHEMA . При этом вы определяете имя схемы по своему выбору, например так:

CREATE SCHEMA myschema;

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

схема.таблица 

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

Есть ещё более общий синтаксис

база_данных.схема.таблица 

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

Таким образом, создать таблицу в новой схеме можно так:

CREATE TABLE myschema.mytable ( . );

Чтобы удалить пустую схему (не содержащую объектов), выполните:

DROP SCHEMA myschema;

Удалить схему со всеми содержащимися в ней объектами можно так:

DROP SCHEMA myschema CASCADE;

Стоящий за этим общий механизм описан в Разделе 5.13.

Часто бывает нужно создать схему, владельцем которой будет другой пользователь (это один из способов ограничения пользователей пространствами имён). Сделать это можно так:

CREATE SCHEMA имя_схемы AUTHORIZATION имя_пользователя;

Вы даже можете опустить имя схемы, в этом случае именем схемы станет имя пользователя. Как это можно применять, описано в Подразделе 5.8.6.

Схемы с именами, начинающимися с pg_ , являются системными; пользователям не разрешено использовать такие имена.

5.8.2. Схема public

До этого мы создавали таблицы, не указывая никакие имена схем. По умолчанию такие таблицы (и другие объекты) автоматически помещаются в схему « public » . Она содержится во всех создаваемых базах данных. Таким образом, команда:

CREATE TABLE products ( . );

CREATE TABLE public.products ( . );

5.8.3. Путь поиска схемы

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

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

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

Чтобы узнать текущий тип поиска, выполните следующую команду:

SHOW search_path;

В конфигурации по умолчанию она возвращает:

search_path -------------- "$user", public

Первый элемент ссылается на схему с именем текущего пользователя. Если такой схемы не существует, ссылка на неё игнорируется. Второй элемент ссылается на схему public, которую мы уже видели.

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

Чтобы добавить в путь нашу новую схему, мы выполняем:

SET search_path TO myschema,public;

(Мы опускаем компонент $user , так как здесь в нём нет необходимости.) Теперь мы можем обращаться к таблице без указания схемы:

DROP TABLE mytable;

И так как myschema — первый элемент в пути, новые объекты будут по умолчанию создаваться в этой схеме.

Мы можем также написать:

SET search_path TO myschema;

Тогда мы больше не сможем обращаться к схеме public, не написав полное имя объекта. Единственное, что отличает схему public от других, это то, что она существует по умолчанию, хотя её так же можно удалить.

В Разделе 9.25 вы узнаете, как ещё можно манипулировать путём поиска схем.

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

OPERATOR(схема.оператор) 

Такая запись необходима для избежания синтаксической неоднозначности. Пример такого выражения:

SELECT 3 OPERATOR(pg_catalog.+) 4;

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

5.8.4. Схемы и права

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

Пользователю также можно разрешить создавать объекты в схеме, не принадлежащей ему. Для этого ему нужно дать право CREATE в требуемой схеме. Заметьте, что по умолчанию все имеют права CREATE и USAGE в схеме public . Благодаря этому все пользователи могут подключаться к заданной базе данных и создавать объекты в её схеме public . Некоторые шаблоны использования требуют запретить это:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(Первое слово « public » обозначает схему, а второе означает « каждый пользователь » . В первом случае это идентификатор, а во втором — ключевое слово, поэтому они написаны в разном регистре; вспомните указания из Подраздела 4.1.1.)

5.8.5. Схема системного каталога

В дополнение к схеме public и схемам, создаваемым пользователями, любая база данных содержит схему pg_catalog , в которой находятся системные таблицы и все встроенные типы данных, функции и операторы. pg_catalog фактически всегда является частью пути поиска. Если даже эта схема не добавлена в путь явно, она неявно просматривается до всех схем, указанных в пути. Так обеспечивается доступность встроенных имён при любых условиях. Однако вы можете явным образом поместить pg_catalog в конец пути поиска, если вам нужно, чтобы пользовательские имена переопределяли встроенные.

Так как имена системных таблиц начинаются с pg_ , такие имена лучше не использовать во избежание конфликта имён, возможного при появлении в будущем системной таблицы с тем же именем, что и ваша. (С путём поиска по умолчанию неполная ссылка будет воспринята как обращение к системной таблице.) Системные таблицы будут и дальше содержать в имени приставку pg_ , так что они не будут конфликтовать с неполными именами пользовательских таблиц, если пользователи со своей стороны не будут использовать приставку pg_ .

5.8.6. Шаблоны использования

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

Ограничить обычных пользователей личными схемами. Для реализации этого подхода выполните REVOKE CREATE ON SCHEMA public FROM PUBLIC и создайте для каждого пользователя схему с его именем. Как вы знаете, путь поиска по умолчанию начинается с имени $user , вместо которого подставляется имя пользователя. Таким образом, если у всех пользователей будет отдельная схема, они по умолчанию будут обращаться к собственным схемам. Применяя этот шаблон в базе, к которой уже могли подключаться недоверенные пользователи, проверьте, нет ли в схеме public объектов с такими же именами, как у объектов в схеме pg_catalog . Этот шаблон является шаблоном безопасного использования схем, только если никакой недоверенный пользователь не является владельцем базы данных и не имеет права CREATEROLE . В противном случае безопасное использование схем невозможно.

Удалить схему public из пути поиска по умолчанию, изменив postgresql.conf или выполнив команду ALTER ROLE ALL SET search_path = «$user» . При этом все по-прежнему смогут создавать объекты в общей схеме, но выбираться эти объекты будут только по полному имени, со схемой. Тогда как обращаться к таблицам по полному имени вполне допустимо, обращения к функциям в общей схеме всё же будут небезопасными или ненадёжными. Поэтому если вы создаёте функции или расширения в схеме public, применяйте первый шаблон. Если же нет, этот шаблон, как и первый, безопасен при условии, что никакой недоверенный пользователь не является владельцем базы данных и не имеет права CREATEROLE .

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

5.8.7. Переносимость

Стандарт SQL не поддерживает обращение в одной схеме к разным объектам, принадлежащим разным пользователям. Более того, в ряде реализаций СУБД нельзя создавать схемы с именем, отличным от имени владельца. На практике, в СУБД, реализующих только базовую поддержку схем согласно стандарту, концепции пользователя и схемы очень близки. Таким образом, многие пользователи полагают, что полное имя на самом деле образуется как имя_пользователя . имя_таблицы . И именно так будет вести себя PostgreSQL , если вы создадите схемы для каждого пользователя.

В стандарте SQL нет и понятия схемы public . Для максимального соответствия стандарту использовать схему public не следует.

Конечно, есть СУБД, в которых вообще не реализованы схемы или пространства имён поддерживают (возможно, с ограничениями) обращения к другим базам данных. Если вам потребуется работать с этими системами, максимальной переносимости вы достигнете, вообще не используя схемы.

Пред.	Наверх	След.
5.7. Политики защиты строк	Начало	5.9. Наследование

Визуализируем разработку БД PostgreSQL

Ни для кого не секрет, что проектирование структуры БД является одной из основных и порой очень трудозатратных задач при разработке любого ПО, работающего с данными. Все мы так или иначе проектируем БД, пытаясь представить себе схему взаимосвязей таблиц, а зачастую рисуем, визуализируем структуру БД, прежде чем перенести ее в СУБД. Для моделирования баз данных MySQL есть MySQL Workbench, поставляемый разработчиком, для MS SQL есть Database Diagrams; я до недавнего времени пользовался Dia, а кто-то, может быть, использует для этих целей MS Visio. Но для PostgreSQL я не встречал ни одного адекватного решения, которое позволяло бы максимально просто и точно перенести наброски структуры БД в код ее создания в самой СУБД.

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

Итак… (текст, много картинок)

Хочу представить вам opensource продукт, распространяемый по лицензии GNU General Public License 3, под названием pgModeler.

Автор официально заявляет, что его продукт (приближенный перевод):

1. Прост в использовании
   Легко создавать и редактировать модели БД с помощью простого и интуитивно понятного интерфейса;
2. Поддерживает различные версии PostgreSQL
   Смоделируйте БД один раз, а затем просто экспортируйте в свою версию СУБД. В состав pgModeler входят методы генерации кода, которые дают возможность Вашим моделям быть выгруженными для различных версий PostgreSQL;
   
3. Кроссплатформенный
   Написанный с помощью Qt, pgModeler может быть скомпилирован для Windows, Linux и MacOSX. Скрипты сборки легко конфигурабельны, что помогает разрешить специфические зависимости в каждой системе;
   
4. Может быть функционально расширен с помощью плагинов
   Если Вам понадобится какой-то дополнительный функционал, то Вы легко сможете реализовать его в виде плагина к pgModeler. Шаблон плагина и документация любезно включена в сборку;
5. Является открытым ПО
   Ссылка на исходный код, расположенный на гитхабе, видна на официальном сайте невооруженным глазом.

Вы все (кроме самых любопытных, которые уже сходили по ссылке на официальную страницу), наверное, спросите, чем же уникален этот продукт?

Давайте посмотрим (картинки кликабельны).

Что ж, на вид все очень даже вкусно, но так ли это на самом деле? Предлагаю создать тестовую базу.

Для начала необходимо зарегистрировать новое подключение к СУБД в настройках (Меню->Edit->Configurations->Connections):

Добавим новую роль для нашей тестовой БД. Для этого в древовидном представлении справа необходимо вызвать контекстное меню от самой БД:

Базу автоматически создавать не будем:

да, ID для роли придется добавить вручную.

Далее добавляем новую схему:

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

Создаем новую таблицу. Это можно сделать, например, из контекстного меню, вызванного от рабочей области (клетчатое поле):

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

После принятия всех параметров таблицы она отобразится в рабочей области:

Создаем таблицу товаров:

Не стоит создавать колонку для связи двух таблиц. Конструктор это сделает за нас.

Для связи двух таблиц предусмотрено несколько типов отношений:

Создаем отношение 1-ко-Многим:

На схеме оно будет выглядеть вот так:

Теперь мы можем выгрузить нашу схему в файл-скрипт создания, в файл-картинку или напрямую в СУБД:

а также посмотреть (без возможности изменить) скрипт создания БД для любой из поддерживаемых версий СУБД в нативном формате и в формате XML (в контекстном меню от базы данных в древовидной структуре справа):

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

В качестве заключения

Что ж, заявленные автором возможности данного ПО вполне себя оправдывают. Все очень интуитивно и просто.

Данный проект сейчас находится в версии v0.4.0-rc1 и активно развивается. Функционал достаточно богат, но возможны некоторые баги и недоработки, кои, впрочем, я пока не встретил.

Думаю, pgModeler придется по вкусу всем тем, кто выбирает для своих проектов PostgreSQL в качестве основной СУБД, и он поможет вам в моделировании и визуализации ваших баз данных.

PS: я не являюсь автором pgModeler.
PPS: c автором вы можете связаться сами.

• database design
• postgresql
• pgmodeler