В предыдущих статьях мы научились устанавливать PostgreSQL 10 на Debian 9, а так же полностью удалять PostgreSQL 10.
В данной статье я расскажу о базовых операциях PostgreSQL 10: основные команды в оболочке psql, создание и удаление базы данных, работа с табличными пространствами, создание и удаление ролей, создание простых таблиц, работа со схемами и т.д.
Исходные данные: Debian 9.3 (Stretch), PostgreSQL 10.2
Задача: Научиться базовым навыкам работы с PostgreSQL
Давайте зайдем под пользователем postgres, запустим оболочку psql и попробуем поработать:
$ su - postgres $ psql
Перед нами открылась оболочка с приглашением ввести команды:
psql (10.2 (Debian 10.2-1.pgdg90+1)) Type "help" for help. postgres=#
Вывод postgres=# указывает нам, что мы подключились к базе postgres.
Первым делом давайте посмотрим информацию о версии PostgreSQL:
postgres=# select version();
version
--------------------------------------------------------------------------------------------------
PostgreSQL 10.2 (Debian 10.2-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
(1 row)
Давайте пройдемся по списку наиболее нужных команд:
1. Список баз данных.
Список БД можно посмотреть 3-мя способами:
Ключ -l командной строки приложения psql, метакоманда \l или select запрос к системному каталогу pg_database, например
$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
$ psql
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# SELECT datname FROM pg_database;
datname
-----------
postgres
template1
template0
(3 rows)
2. Создание баз данных.
Базу данных можно создать 2-мя способами: Через утилиту createdb или с помощью команды CREATE DATABASE имя;
Утилита createdb не делает ничего волшебного, она просто подключается к базе данных postgres и выполняет SQL-команду CREATE DATABASE.
Пример этих команд и вывод списка БД:
$ createdb test
$ psql
psql (10.2 (Debian 10.2-1.pgdg90+1))
Type "help" for help.
postgres=# CREATE DATABASE test1;
CREATE DATABASE
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
test1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
Так же следует упомянуть про шаблоны баз данных, при инициализации основной БД создаются так же 2 базы-шаблоны — это template0 и template1.
По факту команда CREATE DATABASE выполняет копирование существующей базы данных. По умолчанию копируется стандартная системная база template1. Таким образом, template1 это шаблон, на основе которого создаются новые базы. Если добавить объекты в template1, то впоследствии они будут копироваться в новые базы данных.
В системную базу template0 не желательно вносить какие либо изменения.
В таблице pg_database есть два полезных флага для каждой базы данных: datistemplate и datallowconn.
datistemplate — Если true, базу данных сможет клонировать любой пользователь с правами CREATEDB, то есть использовать в качестве шаблона, в противном случае, клонировать эту базу смогут только суперпользователи и её владелец.
datallowconn — Если false, никто не сможет подключаться к этой базе данных. Это позволяет защитить базу данных template0 от модификаций.
Для создания базы данных на основе template0 нужно выполнить из среды SQL:
postgres=# CREATE DATABASE dbname TEMPLATE template0;
или из командной оболочки под пользователем postgres:
$ createdb -T template0 dbname
Просмотр флагов datistemplate и datallowconn:
postgres=# SELECT datname,datistemplate,datallowconn FROM pg_database; datname | datistemplate | datallowconn -----------+---------------+-------------- postgres | f | t template1 | t | t template0 | t | f test1 | f | t test | f | t (5 rows)
3. Удаление баз данных.
Тут так же как и при создании, есть 2 подхода: Через утилиту dropdb или с помощью команды DROP DATABASE имя;
Пример:
Пример этих команд и вывод списка БД:
$ dropdb test1
$
$ psql
psql (10.2 (Debian 10.2-1.pgdg90+1))
Type "help" for help.
postgres=# DROP DATABASE test;
DROP DATABASE
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
4. Работа с табличными пространствами.
Табличные пространства в PostgreSQL позволяют администраторам организовать логику размещения файлов объектов базы данных в файловой системе. К однажды созданному табличному пространству можно обращаться по имени на этапе создания объектов.
Табличные пространства позволяют администратору управлять дисковым пространством для инсталляции PostgreSQL. Это полезно минимум по двум причинам. Во-первых, это нехватка места в разделе, на котором был инициализирован экземпляр Pg или кластера Pg и невозможность его расширения. Табличное пространство можно создать в другом разделе и использовать его до тех пор, пока не появится возможность переконфигурирования системы. Во-вторых, табличные пространства позволяют администраторам оптимизировать производительность согласно бизнес-процессам, связанным с объектами базы данных. Например, часто используемый индекс можно разместить на очень быстром и надёжном, но дорогом SSD-диске. В то же время таблица с архивными данными, которые редко используются и скорость к доступа к ним не важна, может быть размещена в более дешёвом и медленном хранилище.
Для создания табличного пространства используется команда CREATE TABLESPACE.
Каталог нового табличного пространства должен существовать, быть пустым и принадлежать пользователю ОС, под которым запущен PostgreSQL.
Создаем каталог и выставляем права:
$ mkdir /ssd2/postgresql/data $ chown -R postgres:postgres /ssd2/postgresql/data $ chmod 700 /ssd2/postgresql/data
Подключаемся к Pg и создаем новое табличное пространство:
$ su - postgres $ psql postgres=# CREATE TABLESPACE fast LOCATION '/ssd2/postgresql/data';
Посмотреть список табличных пространств можно 2-мя способами: метакоманда \db или \db+ или select запрос к системному каталогу pg_tablespace;
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-----------------------------+-------------------+---------+---------+-------------
fast | postgres | /ssd2/postgresql/data | | | 0 bytes |
pg_default | postgres | | | | 37 MB |
pg_global | postgres | | | | 573 kB |
(3 rows)
postgres=# SELECT spcname FROM pg_tablespace;
spcname
------------
pg_default
pg_global
fast
(3 rows)
Создавать табличное пространство должен суперпользователь базы данных, но после этого можно разрешить обычным пользователям его использовать. Для этого необходимо предоставить привилегию CREATE на табличное пространство.
Таблицы, индексы и целые базы данных могут храниться в отдельных табличных пространствах. Для этого пользователь с правом CREATE на табличное пространство должен указать его имя в качестве параметра соответствующей команды.
Например, далее создаётся база данных в табличном пространстве fast:
postgres=# CREATE DATABASE test2 TABLESPACE fast;
CREATE DATABASE
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7605 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7473 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7473 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7473 kB | pg_default |
test1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7473 kB | pg_default |
test2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7473 kB | fast |
(6 rows)
В колонке 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.
Давайте попробуем обя способа создания роли:
$ createuser utest $ psql psql (10.2 (Debian 10.2-1.pgdg90+1)) Type "help" for help. postgres=# CREATE ROLE utest2; CREATE ROLE
Для просмотра списка ролей можно использовать метакоманду \du или сделать select запрос к системному каталогу pg_roles.
Пример:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
utest | | {}
utest2 | Cannot login | {}
postgres=# SELECT rolname FROM pg_roles;
rolname
----------------------
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_signal_backend
postgres
utest
utest2
(8 rows)
Но между этими 2-мя способами создания роли есть отличия. Вы наверно заметили, что у роли utest2 созданной SQL-командой «CREATE ROLE» в колонке «Attributes» стоит «Cannot login», для более детального понимания сделаем такой запрос к каталогу pg_roles:
postgres=# SELECT rolname,rolcanlogin FROM pg_roles;
rolname | rolcanlogin
----------------------+-------------
pg_monitor | f
pg_read_all_settings | f
pg_read_all_stats | f
pg_stat_scan_tables | f
pg_signal_backend | f
postgres | t
utest | t
utest2 | f
(8 rows)
Для роли 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:
postgres=# ALTER ROLE utest2 LOGIN CREATEDB;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
utest | | {}
utest2 | Create DB | {}
А теперь отберем у роли utest2 право CREATEDB и добавим CREATEROLE и REPLICATION:
postgres=# ALTER ROLE utest2 NOCREATEDB CREATEROLE REPLICATION;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
utest | | {}
utest2 | Create role, Replication | {}
Отдельно нужно упомянуть про такую вещь как членство в роли.
Часто бывает удобным сгруппировать пользователей для упрощения администрирования привилегий: привилегии выдаются или отзываются на всю группу. В PostgreSQL для этого создаётся роль, которая представляет группу, а затем членство (membership) в этой группе выдаётся ролям
индивидуальных пользователей.
Сделаем групповую роль, добавим членов и создадим новую БД в табличном пространстве fast и укажем эту групповую роль владельцем БД:
postgres=# CREATE ROLE group_role1;
CREATE ROLE
postgres=# GRANT group_role1 TO utest2;
GRANT ROLE
postgres=#
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+---------------
group_role1 | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
utest | | {}
utest2 | Create role | {group_role1}
postgres=# CREATE DATABASE group_db TABLESPACE fast OWNER group_role1;
CREATE DATABASE
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+-------------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
group_db | group_role1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7473 kB | fast |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7605 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7473 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7473 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
В столбце «Member of» мы видим, что utest2 является членом роли group_role1. Более подробно об этом функционале читайте в русском переводе документации.
Так же стоит отметить, что PostgreSQL имеется набор предопределённых ролей, которые дают доступ к некоторым часто востребованным, но не общедоступным функциям и данным. Администраторы могут назначать (GRANT) эти роли пользователям и/или ролям в своей среде, таким образом открывая этим пользователям доступ к указанной функциональности и информации. Более подробно об этом функционале читайте в русском переводе документации.
Теперь давайте установим для нашей роли (пользователя) пароль, для этого воспользуемся командой ALTER USER:
postgres=# ALTER USER utest2 WITH ENCRYPTED PASSWORD 'mysecretpwd'; ALTER ROLE
Теперь мы можем попробовать подключиться к базе group_db под пользователем utest2 из консоли:
psql -h 127.0.0.1 -U utest2 -d group_db -W
После ввода пароля mysecretpwd нас пустят в базу group_db. База пока у нас пустая, чуть ниже мы попробуем создать там простую таблицу.
Так же хочу обратить внимание на такую вещь как владелец табличного пространства, чуть выше когда мы создавали табличное пространство fast, мы его создали под пользователем postgres и его владельцем стал он, см. столбец Owner при выводе информации метакомандой \db
Поменять владельца табличного пространства можно командой ALTER TABLESPACE <имя_табличного_пространства> OWNER TO <новый_владелец>;
Например:
postgres=# ALTER TABLESPACE fast OWNER TO utest2;
ALTER TABLESPACE
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+-----------------------------
fast | utest2 | /ssd2/postgresql/data
pg_default | postgres |
pg_global | postgres |
(3 rows)
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. Чтобы решить эти проблемы нужно поменять владельца БД и только после этого удалить роль:
postgres=# ALTER DATABASE group_db OWNER TO utest2;
ALTER DATABASE
postgres=# DROP ROLE group_role1;
DROP ROLE
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
group_db | utest2 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7473 kB | fast |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7605 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7473 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7473 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
utest | | {}
utest2 | Create role | {}
Как мы видим и вывода \l+ у нашей БД group_db поменялся владелец и роль group_role1 была удалена (вывод \du).
6. Работа с таблицами.
PostgreSQL — это реляционная система управления базами данных (РСУБД). Это означает, что это система управления данными, представленными в виде отношений (relation). Отношение — это математически точное обозначение таблицы.
Далее я не буду цитировать официальную документацию, что такое таблица, вы и сами можете это прочитать здесь.
Для создания таблицы в PostgreSQL как и в MySQL используется команда CREATE TABLE. В этой команде Вы должны указать как минимум имя новой таблицы и имена и типы данных каждого столбца.
Для начала мы должны подключиться в нужной нам базе, сделать это можно 2-мя способами:
1. Из консоли с помощью команды: psql -d
2. Если вы уже запустили psql и находитесь в системной БД postgres, то с помощью метакоманды ‘\c
Важно понимать, что подключаясь к другой БД мы подключимся к ней с правами текущего пользователя, то есть пользователя postgres и в результате создания объектов их владельцем так же будет пользователь postgres.
Для того чтобы подключиться к БД под другим пользователем нужно воспользоваться одним из способов:
1. Из консоли с помощью команды: psql -h 127.0.0.1 -U
2. Если Вы уже запустили psql и находитесь в системной БД postgres, то с помощью метакоманды ‘\c
Для чего мы написали подключение к 127.0.0.1 через TCP? А для того, что для всех пользователей кроме системного postgres разрешено подключаться только по TCP и только с указанием пароля. Эти ограничения устанавливаются в файле /etc/postgresql/10/main/pg_hba.conf, но о них мы поговорим в следующий раз.
Далее мы подключимся к базу group_db вначале под пользователем postgres, а потом под utest и создадим простую табличку my_first_table:
postgres=# \c group_db
You are now connected to database "group_db" as user "postgres".
group_db=#
group_db=#
group_db=# \c group_db utest2 127.0.0.1
Password for user utest: ******
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "group_db" as user "utest2" on host "127.0.0.1" at port "5432".
group_db=>
group_db=>
CREATE TABLE my_first_table (
first_column text,
second_column integer
);
Посмотрим список таблиц в БД с помощью метакоманды \dt
group_db=> \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+----------
public | my_first_table | table | utest2
(1 row)
Теперь удалим табличку my_first_table с помощью DROP TABLE <имя_таблицы>
Попытка удаления несуществующей таблицы считается ошибкой, поэтому рекомендуется использовать конструкцию DROP TABLE IF EXISTS <имя_таблицы>
group_db=> DROP TABLE IF EXISTS my_first_table; DROP TABLE group_db=> \dt Did not find any relations.
После удаления вывод \dt скажет нам, что в БД нет таблиц.
7. Работа со схемами.
Схема — это способ объединения таблиц, функций, операторов, типов данных и других объектов в БД.
Одно и то же имя объекта можно свободно использовать в разных схемах, например и schema1, и myschema могут содержать таблицы с именем mytable. В отличие от баз данных, схемы не ограничивают доступ к данным: пользователи могут обращаться к объектам в любой схеме текущей базы данных, если им назначены соответствующие права.
Для создания схемы используется команда CREATE SCHEMA <имя_схемы>;
Чтобы создать объекты в схеме или обратиться к ним, указывайте полное имя, состоящее из имён схемы и объекта, разделённых точкой:
схема.таблица
Этот синтаксис работает везде, где ожидается имя таблицы, включая команды модификации таблицы и команды обработки данных, обсуждаемые в следующих главах. (Для краткости мы будем говорить только о таблицах, но всё это распространяется и на другие типы именованных объектов, например, типы и функции.)
Есть ещё более общий синтаксис
база_данных.схема.таблица
При создании схемы так же как и при создании таблиц её владельцем становится текущий пользователь, об этом не стоит забывать!
Создадим схему и таблицу в ней:
postgres=# \c group_db utest2 127.0.0.1
Password for user utest:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "group_db" as user "utest2" on host "127.0.0.1" at port "5432".
group_db=>
group_db=> CREATE SCHEMA sc_test;
CREATE SCHEMA
group_db=>
CREATE TABLE sc_test.my_first_table (
first_column text,
second_column integer
);
Посмотрим список схем (метакоманда \dn):
group_db=> \dn List of schemas Name | Owner ---------+---------- public | postgres sc_test | utest2 (2 rows)
Чтобы найти нашу таблицу мы должны будем обращаться к ней по полному пути, но вывод списка таблиц через метакоманду \dt не выведет нашу новую таблицу из схемы sc_test, чтобы он вывел таблицу мы должны добавить путь поиска схемы, это делается командой:
SET search_path TO sc_test,public;
Чтобы не вводить SET search_path TO каждый раз мы можем задать search_path для конкретного пользователя на постоянной основе командой:
ALTER USER utest2 SET search_path=sc_test,public;
После этого мы сможем вывести список наших таблиц из схемы public и новой sc_test:
group_db=> \dt
List of relations
Schema | Name | Type | Owner
---------+----------------+-------+-------
sc_test | my_first_table | table | utest2
(1 row)
А что же за схема public? А дело вот в чем, до этого мы создавали таблицы, не указывая никакие имена схем, думаю Вы помните. По умолчанию такие таблицы (и другие объекты) автоматически помещаются в схему ‘public’. Она содержится во всех создаваемых базах данных. Таким образом, до того как мы указали путь поиска нашей новой схемы, команда:
CREATE TABLE my_first_table ( ... ); эквивалентна: CREATE TABLE public.my_first_table ( ... );
Указав путь поиска схемы мы можем выполнить DROP TABLE my_first_table; без указания схемы, но тут есть нюанс:
ВНИМАНИЕ! Т.к. мы можем создавать одинаковые по именам объекты в разных схемах, то порядок поиска схем в путь будет иметь значение при разных операциях (создание, модификацию, удаления и др.) над объектами, вывести список путей поиска схем можно командой:
group_db=# SHOW search_path; search_path ----------------- sc_test, public (1 row)
Таким образом DROP TABLE my_first_table; первым делом удалит таблицу из схемы sc_test если она там есть, а повторный вызов DROP TABLE my_first_table; уже удалит таблицу из схемы public, при её наличии там.
Вот поэтому если Вы используете схемы, то и все операции над объектами должны выполнятся с указанием полного имени объекта.
Для удаления схемы нужно воспользоваться командой DROP SCHEMA <имя_схемы>;
Попытка удаления несуществующей схемы считается ошибкой, поэтому рекомендуется использовать конструкцию DROP SCHEMA IF EXISTS <имя_схемы>;
Если в схеме есть объекты, то при попытке её удаление будет выведена ошибка, пример:
group_db=> DROP SCHEMA IF EXISTS sc_test; ERROR: cannot drop schema sc_test because other objects depend on it DETAIL: table my_first_table depends on schema sc_test HINT: Use DROP ... CASCADE to drop the dependent objects too.
В таком случае нужно воспользоваться доп. опцией CASCADE, например:
group_db=> DROP SCHEMA IF EXISTS sc_test CASCADE; NOTICE: drop cascades to table my_first_table DROP SCHEMA group_db=> \dt Did not find any relations. group_db=> \dn List of schemas Name | Owner --------+---------- public | postgres (1 row)
Как мы видим, схема sc_test и таблица my_first_table были удалены.
На этом все, до скорых встреч.
Дополнительные статьи:
Установка и первоначальная настройка PostgreSQL 10.0 на Debian 9
Полное удаление PostgreSQL 10 на Debian 9
Как удалить все таблицы из БД PostgreSQL и MySQL?
Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.
Профессионально занимаюсь системным администрированием Linux -серверов и баз данных (MySQL, PostgreSQL) на протяжении последних 24 лет.
Большое спасибо за очень структурированное изложение материала!