Определение структуры данных
Для создания базы данных используется команда CREATE DATABASE , после которой указывается название базы данных.
Для выполнения запросов будем использовать графический клиент pgAdmin, хотя также можно использовать консольный клиент psql.
Чтобы создать новую базу, данных откроем pgAdmin . В левой части программы выберем какую-нибудь базу данных, например, стандартную бд postgres, и нажмем на нее правой кнопкой мыши.
В появившемся меню выберем пункт Query Tool. , и в центральной части программы откроется поле для ввода кода SQL. В это поле введем следующий код:
Для выполнения кода нажмем на значок молнии, и после этого будет создана база данных usersdb.
Чтобы увидеть нашу базу данных, нажмем в левой части на узел Databases правой кнопкой мыши и в контекстном меню выберем Refresh. :
Произойдет обновление, и мы увидем созданную базу данных.
По умолчанию база является неактивной, поэтому ее значок имеет серый цвет. Но чтобы к ней подключиться, достаточно нажать на нее и раскрыть ее узел.
Удаление базы данных
Для удаления базы данных применяется команда DROP DATABASE , после которой указывается название базы данных.
Удаляемая база данных должна быть неактивной, то есть подключение к ней должно быть закрыта.
alt=»Блог Михаила Григорьева» />
20 Фев 2018 21:02:09 | 
1 comment
Начало работы с PostgreSQL 10
В данной статье я расскажу о базовых операциях PostgreSQL 10: основные команды в оболочке psql, создание и удаление базы данных, работа с табличными пространствами, создание и удаление ролей, создание простых таблиц, работа со схемами и т.д.
Исходные данные: Debian 9.3 (Stretch), PostgreSQL 10.2
Задача: Научиться базовым навыкам работы с PostgreSQL
Давайте зайдем под пользователем postgres, запустим оболочку psql и попробуем поработать:
Перед нами открылась оболочка с приглашением ввести команды:
Вывод postgres=# указывает нам, что мы подключились к базе postgres.
Первым делом давайте посмотрим информацию о версии PostgreSQL:
Давайте пройдемся по списку наиболее нужных команд:
1. Список баз данных.
Список БД можно посмотреть 3-мя способами:
Ключ -l командной строки приложения psql, метакоманда \l или select запрос к системному каталогу pg_database, например
2. Создание баз данных.
Базу данных можно создать 2-мя способами: Через утилиту createdb или с помощью команды CREATE DATABASE имя;
Утилита createdb не делает ничего волшебного, она просто подключается к базе данных postgres и выполняет SQL-команду CREATE DATABASE.
Пример этих команд и вывод списка БД:
Так же следует упомянуть про шаблоны баз данных, при инициализации основной БД создаются так же 2 базы-шаблоны — это template0 и template1.
По факту команда CREATE DATABASE выполняет копирование существующей базы данных. По умолчанию копируется стандартная системная база template1. Таким образом, template1 это шаблон, на основе которого создаются новые базы. Если добавить объекты в template1, то впоследствии они будут копироваться в новые базы данных.
В системную базу template0 не желательно вносить какие либо изменения.
В таблице pg_database есть два полезных флага для каждой базы данных: datistemplate и datallowconn.
datistemplate — Если true, базу данных сможет клонировать любой пользователь с правами CREATEDB, то есть использовать в качестве шаблона, в противном случае, клонировать эту базу смогут только суперпользователи и её владелец.
datallowconn — Если false, никто не сможет подключаться к этой базе данных. Это позволяет защитить базу данных template0 от модификаций.
Для создания базы данных на основе template0 нужно выполнить из среды SQL:
или из командной оболочки под пользователем postgres:
Просмотр флагов datistemplate и datallowconn:
3. Удаление баз данных.
Тут так же как и при создании, есть 2 подхода: Через утилиту dropdb или с помощью команды DROP DATABASE имя;
Пример:
Пример этих команд и вывод списка БД:
4. Работа с табличными пространствами.
Табличные пространства в PostgreSQL позволяют администраторам организовать логику размещения файлов объектов базы данных в файловой системе. К однажды созданному табличному пространству можно обращаться по имени на этапе создания объектов.
Табличные пространства позволяют администратору управлять дисковым пространством для инсталляции PostgreSQL. Это полезно минимум по двум причинам. Во-первых, это нехватка места в разделе, на котором был инициализирован экземпляр Pg или кластера Pg и невозможность его расширения. Табличное пространство можно создать в другом разделе и использовать его до тех пор, пока не появится возможность переконфигурирования системы. Во-вторых, табличные пространства позволяют администраторам оптимизировать производительность согласно бизнес-процессам, связанным с объектами базы данных. Например, часто используемый индекс можно разместить на очень быстром и надёжном, но дорогом SSD-диске. В то же время таблица с архивными данными, которые редко используются и скорость к доступа к ним не важна, может быть размещена в более дешёвом и медленном хранилище.
Для создания табличного пространства используется команда CREATE TABLESPACE.
Каталог нового табличного пространства должен существовать, быть пустым и принадлежать пользователю ОС, под которым запущен PostgreSQL.
Создаем каталог и выставляем права:
Подключаемся к Pg и создаем новое табличное пространство:
Посмотреть список табличных пространств можно 2-мя способами: метакоманда \db или \db+ или select запрос к системному каталогу pg_tablespace;
Создавать табличное пространство должен суперпользователь базы данных, но после этого можно разрешить обычным пользователям его использовать. Для этого необходимо предоставить привилегию CREATE на табличное пространство.
Таблицы, индексы и целые базы данных могут храниться в отдельных табличных пространствах. Для этого пользователь с правом CREATE на табличное пространство должен указать его имя в качестве параметра соответствующей команды.
Например, далее создаётся база данных в табличном пространстве fast:
В колонке Tablespace мы видим что база test2 находится в новом табличном пространстве fast.
Табличное пространство, связанное с базой данных, также используется для хранения её системных каталогов. Более того, это табличное пространство используется по умолчанию для таблиц, индексов и временных файлов, создаваемых в базе данных, если не указано иное в выражении
TABLESPACE, или переменной default_tablespace, или temp_tablespaces (соответственно). Если база данных создана без указания конкретного табличного пространства, то используется пространство, к которому принадлежит копируемый шаблон.
При инициализации экземпляра Pg или кластера Pg автоматически создаются два табличных пространства. Табличное пространство pg_global используется для общих системных каталогов. Табличное пространство pg_default используется по умолчанию для баз данных template1 и template0 (в свою очередь, также является пространством по умолчанию для других баз данных, пока не будет явно указано иное в выражении TABLESPACE команды CREATE DATABASE).
5. Работа с ролями.
В PostgreSQL используется концепция ролей (roles) для управления разрешениями на доступ к базе данных. Роль можно рассматривать как пользователя базы данных или как группу пользователей, в зависимости от того, как роль настроена. Роли могут владеть объектами базы данных (например, таблицами и функциями) и выдавать другим ролям разрешения на доступ к этим объектам, управляя тем, кто имеет доступ и к каким объектам. Кроме того, можно предоставить одной роли членство в другой роли, таким образом одна роль может использовать привилегии других ролей.
Концепция ролей включает в себя концепцию пользователей (user) и групп (groups). До версии 8.1 в PostgreSQL пользователи и группы были отдельными сущностями, но теперь есть только роли. Любая роль может использоваться в качестве пользователя, группы, и того и другого.
Роли базы данных концептуально полностью отличаются от пользователей операционной системы.На практике поддержание соответствия между ними может быть удобным, но не является обязательным. Роли базы данных являются глобальными для всего инстана Pg или кластера Pg базы данных (не для отдельной базы данных). Для создания роли используется SQL-команда CREATE ROLE, а для удаления DROP ROLE.
5.1 Создание ролей.
Роль можно создать/удалить 2-мя путями: через консольную утилиту createuser или dropuser и через psql с помощью CREATE ROLE или DROP ROLE.
Давайте попробуем обя способа создания роли:
Для просмотра списка ролей можно использовать метакоманду \du или сделать select запрос к системному каталогу pg_roles.
Но между этими 2-мя способами создания роли есть отличия. Вы наверно заметили, что у роли utest2 созданной SQL-командой «CREATE ROLE» в колонке «Attributes» стоит «Cannot login», для более детального понимания сделаем такой запрос к каталогу pg_roles:
Для роли utest2 в столбце rolcanlogin стоит false — это значит, что роль не может подключаться к серверу. То есть эта роль не может быть указана в качестве начального идентификатора авторизации сеанса.
У роли utest созданной с помощью утилиты createuser в колонке rolcanlogin стоит true — значит роль может подключаться к серверу и может быть указана в качестве начального идентификатора авторизации сеанса.
По сути использование консольной утилиты createuser эквивалентно SQL-команде «CREATE USER» т.к. ставит атрибут LOGIN. В этом и есть отличия 2-х методов создания ролей.
Для каждой роли можно назначит определенный атрибуты, определяющие её полномочия и взаимодействие с системой аутентификации клиентов.
Доступны следующие атрибуты (в скобках указаны столбцы из каталогу pg_roles):
1) Право подключения (rolcanlogin):
Только роли с атрибутом LOGIN могут использоваться для начального подключения к базе данных. Роль с атрибутом LOGIN можно рассматривать как пользователя базы данных. Для создания роли такой роли можно использовать любой из вариантов:
CREATE ROLE имя LOGIN;
или
CREATE USER имя;
или использование консольной утилиты createuser.
Как мы уже сказали выше, команда CREATE USER эквивалентна CREATE ROLE за исключением того, что CREATE USER по умолчанию предполагает атрибут LOGIN, в то время как CREATE ROLE нет.)
2) Статус суперпользователя (rolsuper):
Суперпользователь базы данных обходит все проверки прав доступа, за исключением права на вход в систему. Это опасная привилегия и она не должна использоваться небрежно. Лучше всего выполнять большую часть работы не как суперпользователь. Для создания нового супер пользователя используется CREATE ROLE имя SUPERUSER. Это нужно выполнить из под роли, которая также является суперпользователем.
3) Создание базы данных (rolcreatedb):
Роль должна явно иметь разрешение на создание базы данных (за исключением суперпользователей, которые пропускают все проверки). Для создания такой роли используется CREATE ROLE имя CREATEDB.
4) Создание роли (rolcreaterole):
Роль должна явно иметь разрешение на создание других ролей (за исключением суперпользователей, которые пропускают все проверки). Для создания такой роли используется CREATE ROLE имя CREATEROLE. Роль с привилегией CREATEROLE может также изменять и удалять другие
роли, а также выдавать и отзывать членство в ролях. Однако, для создания, изменения, удаления суперпользовательских ролей, а также изменения в них членства, требуется иметь статус суперпользователя, привилегии CREATEROLE в таких случаях недостаточно.
5) Запуск репликации (rolreplication):
Роль должна иметь явное разрешение на запуск потоковой репликации (за исключением суперпользователей, которые пропускают все проверки). Роль, используемая для потоковой репликации, также должна иметь атрибут LOGIN. Для создания такой роли используется CREATE ROLE имя REPLICATION LOGIN.
6) Пароль (rolpassword):
Пароль имеет значение, если метод аутентификации клиентов требует, чтобы пользователи предоставляли пароль при подключении к базе данных. Методы аутентификации password и md5 используют пароли. База данных и операционная система используют раздельные пароли. Пароль указывается при создании роли: CREATE ROLE имя PASSWORD ‘строка’.
7) Игнорировать систему защиты строк (rolbypassrls):
В дополнение к стандартной системе прав SQL, управляемой командой GRANT, на уровне таблиц можно определить политики защиты строк, ограничивающие для пользователей наборы строк, которые могут быть возвращены обычными запросами или добавлены, изменены и удалены командами, изменяющими данные. Это называется также защитой на уровне строк (RLS, Row-Level Security). Суперпользователи и роли с атрибутом BYPASSRLS всегда обращаются к таблице, минуя систему защиты строк. Более детально о RLS читайте в официальной документации или в переводе на русский.
Атрибуты ролей могут быть изменены после создания командой ALTER ROLE. Для получения более детальной информации Вам следует обратиться к справке по командам CREATE ROLE и ALTER ROLE.
Давайте добавим право LOGIN и CREATEDB для нашей роли utest2:
А теперь отберем у роли utest2 право CREATEDB и добавим CREATEROLE и REPLICATION:
Отдельно нужно упомянуть про такую вещь как членство в роли.
Часто бывает удобным сгруппировать пользователей для упрощения администрирования привилегий: привилегии выдаются или отзываются на всю группу. В PostgreSQL для этого создаётся роль, которая представляет группу, а затем членство (membership) в этой группе выдаётся ролям
индивидуальных пользователей.
Сделаем групповую роль, добавим членов и создадим новую БД в табличном пространстве fast и укажем эту групповую роль владельцем БД:
В столбце «Member of» мы видим, что utest2 является членом роли group_role1. Более подробно об этом функционале читайте в русском переводе документации.
Так же стоит отметить, что PostgreSQL имеется набор предопределённых ролей, которые дают доступ к некоторым часто востребованным, но не общедоступным функциям и данным. Администраторы могут назначать (GRANT) эти роли пользователям и/или ролям в своей среде, таким образом открывая этим пользователям доступ к указанной функциональности и информации. Более подробно об этом функционале читайте в postgres=# ALTER USER utest2 WITH ENCRYPTED PASSWORD ‘mysecretpwd’; ALTER ROLE
Теперь мы можем попробовать подключиться к базе group_db под пользователем utest2 из консоли:
После ввода пароля mysecretpwd нас пустят в базу group_db. База пока у нас пустая, чуть ниже мы попробуем создать там простую таблицу.
Так же хочу обратить внимание на такую вещь как владелец табличного пространства, чуть выше когда мы создавали табличное пространство fast, мы его создали под пользователем postgres и его владельцем стал он, см. столбец Owner при выводе информации метакомандой \db
Поменять владельца табличного пространства можно командой ALTER TABLESPACE OWNER TO ;
Например:
5.2 Удаление ролей.
Так как роли могут владеть объектами баз данных и иметь права доступа к объектам других, удаление роли не сводится к немедленному действию DROP ROLE. Сначала должны быть удалены и переданы другим владельцами все объекты, принадлежащие роли, также должны быть отозваны все права, данные роли.
Если сейчас мы попытаемся удалить нашу групповую роль group_role1, то получим такую ошибку:
ERROR: role «group_role1» cannot be dropped because some objects depend on it
DETAIL: owner of database group_db
То есть наша роль является владельцем базы group_db. Чтобы решить эти проблемы нужно поменять владельца БД и только после этого удалить роль:
Как мы видим и вывода \l+ у нашей БД group_db поменялся владелец и роль group_role1 была удалена (вывод \du).
6. Работа с таблицами.
Посмотрим список таблиц в БД с помощью метакоманды \dt
Теперь удалим табличку my_first_table с помощью DROP TABLE
Попытка удаления несуществующей таблицы считается ошибкой, поэтому рекомендуется использовать конструкцию DROP TABLE IF EXISTS
После удаления вывод \dt скажет нам, что в БД нет таблиц.
7. Работа со схемами.
Схема — это способ объединения таблиц, функций, операторов, типов данных и других объектов в БД.
Одно и то же имя объекта можно свободно использовать в разных схемах, например и schema1, и myschema могут содержать таблицы с именем mytable. В отличие от баз данных, схемы не ограничивают доступ к данным: пользователи могут обращаться к объектам в любой схеме текущей базы данных, если им назначены соответствующие права.
Для создания схемы используется команда CREATE SCHEMA ;
Чтобы создать объекты в схеме или обратиться к ним, указывайте полное имя, состоящее из имён схемы и объекта, разделённых точкой:
Этот синтаксис работает везде, где ожидается имя таблицы, включая команды модификации таблицы и команды обработки данных, обсуждаемые в следующих главах. (Для краткости мы будем говорить только о таблицах, но всё это распространяется и на другие типы именованных объектов, например, типы и функции.)
Есть ещё более общий синтаксис
При создании схемы так же как и при создании таблиц её владельцем становится текущий пользователь, об этом не стоит забывать!
Создадим схему и таблицу в ней:
Посмотрим список схем (метакоманда \dn):
Чтобы найти нашу таблицу мы должны будем обращаться к ней по полному пути, но вывод списка таблиц через метакоманду \dt не выведет нашу новую таблицу из схемы sc_test, чтобы он вывел таблицу мы должны добавить путь поиска схемы, это делается командой:
Чтобы не вводить SET search_path TO каждый раз мы можем задать search_path для конкретного пользователя на постоянной основе командой:
После этого мы сможем вывести список наших таблиц из схемы public и новой sc_test:
А что же за схема public? А дело вот в чем, до этого мы создавали таблицы, не указывая никакие имена схем, думаю Вы помните. По умолчанию такие таблицы (и другие объекты) автоматически помещаются в схему ‘public’. Она содержится во всех создаваемых базах данных. Таким образом, до того как мы указали путь поиска нашей новой схемы, команда:
Указав путь поиска схемы мы можем выполнить DROP TABLE my_first_table; без указания схемы, но тут есть нюанс:
ВНИМАНИЕ! Т.к. мы можем создавать одинаковые по именам объекты в разных схемах, то порядок поиска схем в путь будет иметь значение при разных операциях (создание, модификацию, удаления и др.) над объектами, вывести список путей поиска схем можно командой:
Таким образом DROP TABLE my_first_table; первым делом удалит таблицу из схемы sc_test если она там есть, а повторный вызов DROP TABLE my_first_table; уже удалит таблицу из схемы public, при её наличии там.
Вот поэтому если Вы используете схемы, то и все операции над объектами должны выполнятся с указанием полного имени объекта.
Для удаления схемы нужно воспользоваться командой DROP SCHEMA ;
Попытка удаления несуществующей схемы считается ошибкой, поэтому рекомендуется использовать конструкцию DROP SCHEMA IF EXISTS ;
Если в схеме есть объекты, то при попытке её удаление будет выведена ошибка, пример:
В таком случае нужно воспользоваться доп. опцией CASCADE, например:
Как мы видим, схема sc_test и таблица my_first_table были удалены.
На этом все, до скорых встреч.
Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.
PostgreSQL – DROP база данных
Будьте осторожны, прежде чем использовать эту операцию, потому что удаление существующей базы данных приведет к потере полной информации, хранящейся в базе данных.
Использование DROP DATABASE
Эта команда удаляет базу данных. Он удаляет записи каталога для базы данных и удаляет каталог, содержащий данные. Он может быть выполнен только владельцем базы данных. Эта команда не может быть выполнена, пока вы или кто-либо еще подключен к целевой базе данных (подключитесь к postgres или любой другой базе данных, чтобы выполнить эту команду).
Синтаксис
Синтаксис для DROP DATABASE приведен ниже –
параметры
В таблице приведены параметры с их описаниями.
ЕСЛИ СУЩЕСТВУЕТ
Не выдавайте ошибку, если база данных не существует. В этом случае выдается уведомление.
Имя базы данных для удаления.
ЕСЛИ СУЩЕСТВУЕТ
Не выдавайте ошибку, если база данных не существует. В этом случае выдается уведомление.
Имя базы данных для удаления.
Мы не можем удалить базу данных, которая имеет какие-либо открытые соединения, включая наше собственное соединение с psql или pgAdmin III . Мы должны переключиться на другую базу данных или шаблон1, если мы хотим удалить базу данных, к которой мы сейчас подключены. Таким образом, может быть удобнее использовать программу dropdb , которая является оберткой для этой команды.
пример
Ниже приведен простой пример, который удалит testdb из вашей схемы PostgreSQL:
Использование команды dropdb
Исполняемый файл командной строки PostgresSQL dropdb – это оболочка командной строки для команды SQL DROP DATABASE . Нет эффективной разницы между удалением баз данных с помощью этой утилиты и других методов доступа к серверу. dropdb уничтожает существующую базу данных PostgreSQL. Пользователь, который выполняет эту команду, должен быть суперпользователем базы данных или владельцем базы данных.
Синтаксис
Синтаксис для dropdb , как показано ниже –
параметры
В следующей таблице перечислены параметры с их описаниями
Имя базы данных, которая будет удалена.
аргументы командной строки, которые принимает dropdb.
Имя базы данных, которая будет удалена.
аргументы командной строки, которые принимает dropdb.
Опции
В следующей таблице перечислены аргументы командной строки, которые принимает dropdb –
Показывает команды, отправляемые на сервер.
Выдает запрос подтверждения, прежде чем делать что-либо разрушительное.
Распечатайте версию dropdb и выйдите.
–Если-существует
Не выдавайте ошибку, если база данных не существует. В этом случае выдается уведомление.
Показать справку об аргументах командной строки dropdb и завершиться.
Указывает имя хоста компьютера, на котором работает сервер.
Указывает порт TCP или расширение файла локального сокета домена UNIX, на котором сервер прослушивает соединения.
-U имя пользователя
Имя пользователя для подключения как.
Никогда не выдавайте запрос пароля.
Заставьте dropdb запрашивать пароль перед подключением к базе данных.
–maintenance-DB = имя_бд
Задает имя базы данных, к которой нужно подключиться, чтобы удалить целевую базу данных.
Показывает команды, отправляемые на сервер.
Выдает запрос подтверждения, прежде чем делать что-либо разрушительное.
Распечатайте версию dropdb и выйдите.
–Если-существует
Не выдавайте ошибку, если база данных не существует. В этом случае выдается уведомление.
Показать справку об аргументах командной строки dropdb и завершиться.
Указывает имя хоста компьютера, на котором работает сервер.
Указывает порт TCP или расширение файла локального сокета домена UNIX, на котором сервер прослушивает соединения.
-U имя пользователя
Имя пользователя для подключения как.
Никогда не выдавайте запрос пароля.
Заставьте dropdb запрашивать пароль перед подключением к базе данных.
–maintenance-DB = имя_бд
Задает имя базы данных, к которой нужно подключиться, чтобы удалить целевую базу данных.
пример
В следующем примере демонстрируется удаление базы данных из командной строки ОС –
Приведенная выше команда удаляет базу данных testdb . Здесь я использовал имя пользователя postgres (находится в pg_roles шаблона template1) для удаления базы данных.