В этой статье поговорим про схемы в базах данных PostgreSQL и шаблоны. Для понимания, иерархия такая: СУБД > Базы данных > Схемы > Таблицы (и другие объекты).
Базы данных и шаблоны
Когда мы создаём новые кластер командой initdb
у нас создается 3 одинаковые базы данных:
- postgres
- template0
- template1
postgres@s-pg13:~$ psql Timing is on. psql (13.3) Type "help" for help. postgres@postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | template0 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows)
База postgres используется, чтобы по умолчанию к ней подключаться. Принципиально она не нужна, но есть приложения которым она может понадобится, поэтому лучше её не удалять.
Две дополнительные базы template0 и template1 — это шаблоны. Новая база всегда создается путём копирования из другой шаблонной базы. По умолчанию для шаблона используется база template1. Поэтому, если у вас есть расширения, которыми вы пользуетесь, можете их заранее создать в template1.
Основная задача базы template0 заключается в том, что бы она никогда не менялась. Она используется, например при загрузке базы из дампа. Вначале вы создаёте базу из template0, а затем туда заливаете сохранённый дамп. Также база template0 позволяет создавать базы с использованием категорий локалей не по умолчанию (LC_COLLATE, LC_CTYPE).

Схемы
Схема — это пространство имён для объектов внутри базы данных.
Суть работы схемы можно представить так: мы все складываем не все в одну большую кучу, а по небольшим отдельным кучкам. Например, как в файловой системе, всё кладем не в один каталог, а раскладываем по подкаталогам.
Вот пример работы со схемами! В одну схему поместим объекты для модуля «логистика», а в другую для модуля «финансы» и так далее.

В базе данных может быть несколько схем. По умолчанию существует две глобальные схемы. Глобальные они потому-что не принадлежат какой-то определённой базе данных:
- pg_catalog — служебная схема (её ещё называют системный каталог), присутствует во всех базах данных, например там находится представление pg_tables;
- public — общая схема, присутствует во всех базах данных и по умолчанию все объекты создаются в ней.
Также вы можете создать свои дополнительные схемы.
Путь поиска
Так называемое «Квалифицированное имя» состоит из явно указанной схемы и имени объекта (как абсолютный путь в файловой системе). Например: <схема.имя>.
Если мы не указываем схему, то нужно понять, в какой схеме искать или создавать объект. Определяют схему с помощью пути поиска, который задается параметром search_path.
В параметре search_path можно через запятую перечислить схемы, в которых нужно искать объект, если мы не указываем схему явно. search_path это что-то вроде переменной окружения PATH в Linux, для поиска команд.
Из search_path исключаются:
- несуществующие схемы;
- схемы к которым нет доступа.
А некоторые схемы всегда добавляются в search_path, даже если мы их туда не запишем. Например pg_catalog.
Реальное значение search_path показывает функция current_schemas().
postgres@postgres=# SELECT current_schemas(true); current_schemas --------------------- {pg_catalog,public} (1 row) Time: 1,945 ms
При создании нового объекта, он будет помещаться в первую указанную в search_path схему. Если посмотреть пример выше, то так как у нас нет права писать в схему pg_catalog, объекты будут создаваться в public.
Специальные схемы, временные объекты
К специальным схемам относят:
- public — по умолчанию входит в путь поиска, если ничего не менять, все объекты будут в этой схеме.
- Схема, одноимённая с пользователем — по умолчанию входит в search_path, но не существует. Если сделать, например схему postgres, то пользователь postgres будет по умолчанию работать с этой схемой.
- pg_catalog — схема для объектов системного каталога. Если pg_catalog не прописан, то это схема будет там подразумеваться первой.
Временные таблицы — существуют на время сеанса или транзакции. Они не журналируются и не попадают в общую память. Чтобы реализовать временную таблицу в postgres применяет временные схемы.
Схема pg_temp_N — автоматически создается для временных таблиц. Такая схема тоже по умолчанию находится в search_path. По окончанию все объекты временной схемы удаляются, а сама схема остается. Оставшаяся временная схема может использоваться для новых временных таблиц, новой транзакции или сеанса.
Практика
Список баз
Как мы уже видели с помощью команды \l
, у нас действительно 3 базы. Сейчас мы подключены к базе postgres. Тут мы можем обратиться к представлению pg_database и посмотреть на список баз из этого представления:
postgres@postgres=# SELECT datname, datistemplate, datallowconn, datconnlimit FROM pg_database; datname | datistemplate | datallowconn | datconnlimit -----------+---------------+--------------+-------------- postgres | f | t | -1 template1 | t | t | -1 template0 | t | f | -1 (3 rows) Time: 0,875 ms
Здесь мы видим:
- datname — имя базы данных;
- datistemplate — является ли база данных шаблоном;
- datallowconn — разрешены ли соединения с базой данных;
- datconnlimit — максимальное количество соединений (-1 = без ограничений).
Настройка шаблона template1
Проверим, доступна ли нам функция шифрования в этой базе, если не доступна, то создадим необходимое расширение и повторим проверку:
postgres@postgres=# \c template1 You are now connected to database "template1" as user "postgres". postgres@template1=# SELECT digest('Hello, world!', 'md5'); ERROR: function digest(unknown, unknown) does not exist LINE 1: SELECT digest('Hello, world!', 'md5'); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Time: 2,081 ms postgres@template1=# CREATE EXTENSION pgcrypto; CREATE EXTENSION Time: 24,470 ms postgres@template1=# SELECT digest('Hello, world!', 'md5'); digest ------------------------------------ \x6cd3556deb0da54bca060b4c39479839 (1 row) Time: 0,419 ms
В случае если у вас не было скомпилировано это расширение, то в первом уроке мы разбирали как компилировать postgres и его расширения. Примерно это делается так:
$ cd postgresql-13.3/contrib/pgcrypto/ $ make $ sudo make install
Теперь создадим новую базу данных и так как она была создана из шаблона template1, то и расширение pgcrypto здесь уже установлено:
postgres@postgres=# \c postgres You are now connected to database "postgres" as user "postgres". postgres@postgres=# CREATE DATABASE db; CREATE DATABASE Time: 103,788 ms postgres@postgres=# \c db You are now connected to database "db" as user "postgres". postgres@db=# SELECT digest('Hello, world!', 'md5'); digest ------------------------------------ \x6cd3556deb0da54bca060b4c39479839 (1 row) Time: 0,868 ms
Выше мы вначале отключились от базы template1, так как использовать шаблон можно только, если к нему никто не подключен!
Редактирование базы
Теперь переименуем созданную базу данных (ALTER DATABASE … RENAME TO … ), предварительно отключившись от неё:
postgres@db=# \c postgres You are now connected to database "postgres" as user "postgres". postgres@postgres=# ALTER DATABASE db RENAME TO appdb; ALTER DATABASE Time: 1,164 ms postgres@postgres=# SELECT datname, datistemplate, datallowconn, datconnlimit FROM pg_database; datname | datistemplate | datallowconn | datconnlimit -----------+---------------+--------------+-------------- postgres | f | t | -1 template1 | t | t | -1 template0 | t | f | -1 appdb | f | t | -1 (4 rows) Time: 0,434 ms
С помощью ALTER DATABASE можно менять и другие параметры, например число доступных подключений:
postgres@postgres=# ALTER DATABASE appdb CONNECTION LIMIT 10; ALTER DATABASE Time: 0,456 ms postgres@postgres=# SELECT datname, datistemplate, datallowconn, datconnlimit FROM pg_database; datname | datistemplate | datallowconn | datconnlimit -----------+---------------+--------------+-------------- postgres | f | t | -1 template1 | t | t | -1 template0 | t | f | -1 appdb | f | t | 10 (4 rows) Time: 0,202 ms
Смотрим размер базы данных
Размер базы данных можно считать с помощью функции pg_database_size(). Для перевода из байтов в более удобочитаемые единицы, можно использовать функцию pg_size_pretty():
postgres@postgres=# SELECT pg_database_size('appdb'); pg_database_size ------------------ 7787055 (1 row) Time: 1,750 ms postgres@postgres=# SELECT pg_size_pretty(pg_database_size('appdb')); pg_size_pretty ---------------- 7605 kB (1 row) Time: 1,247 ms
Вот мы и узнали размер пустой базы!
Работа со схемами
Список схем можно узнать с помощью команды \dn:
postgres@postgres=# \dn List of schemas Name | Owner --------+---------- public | postgres (1 row)
Это не все схемы, здесь исключены служебные схемы!
Создадим новую схему, предварительно подключившись к нашей базе:
postgres@postgres=# \c appdb You are now connected to database "appdb" as user "postgres". postgres@appdb=# CREATE SCHEMA app; CREATE SCHEMA Time: 0,927 ms postgres@appdb=# \dn List of schemas Name | Owner --------+---------- app | postgres public | postgres (2 rows)
На путь поиска схем можно посмотреть с помощью search_path:
postgres@appdb=# SHOW search_path; search_path ----------------- "$user", public (1 row) Time: 0,248 ms
Это означает, что при создании таблицы, она попытается попасть в схему «$user» (postgres), но такой схемы нет. А затем попадет в схему public! И наоборот, при обращении к таблице она будет искаться в начале в «$user», а затем в public!
Дополнительно можем посмотреть текущие схемы, в этой базе данных с помощью функции current_schemas():
postgres@appdb=# SELECT current_schemas(true); current_schemas --------------------- {pg_catalog,public} (1 row) Time: 0,343 ms
Здесь мы видим служебную схему pg_catalog, но к ней нет доступа. Поэтому судя по пути поиска и по текущим схемам, можем сказать что по умолчанию таблицы будут создаваться в схеме public.
Теперь создадим таблицу «t«, в ней создадим строку и с помощью команды \dt посмотрим в какой схеме оказалась эта таблица:
postgres@appdb=# CREATE TABLE t(s text); CREATE TABLE Time: 4,398 ms postgres@appdb=# INSERT INTO t VALUES ('Я - таблица t'); INSERT 0 1 Time: 1,172 ms postgres@appdb=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t | table | postgres (1 row)
Таблицы можно перемещать между схемами с помощью ALTER TABLE ... SET SCHEMA ...
. Если схемы нет в пути поиска, то к таблицам в этой схеме нужно обращаться по полному пути:
postgres@appdb=# ALTER TABLE t SET SCHEMA app; ALTER TABLE Time: 0,916 ms postgres@appdb=# SELECT * FROM app.t; s --------------- Я - таблица t (1 row) Time: 0,372 ms postgres@appdb=# SELECT * FROM t; ERROR: relation "t" does not exist LINE 1: SELECT * FROM t; ^ Time: 0,226 ms
Выше мы видим, что не указав полный путь мы получили ошибку!
Установить путь поиска можно так:
postgres@appdb=# SET search_path = public, app; SET Time: 0,203 ms postgres@appdb=# SELECT * FROM t; s --------------- Я - таблица t (1 row) Time: 0,205 ms
Но это установит путь только для текущего сеанса!
Установить этот параметр для базы, а не для сеанса можно с помощью ALTER DATABASE ... SET search_path = ...
:
appdb=# ALTER DATABASE appdb SET search_path = public, app; ALTER DATABASE
Выше команда означает, что при подключении к базе appdb будет выполняться команда SET search_path = public, app.
Теперь создадим временную таблицу с таким-же именем «t» и посмотрим что из этого выйдет:
postgres@appdb=# CREATE TEMP TABLE t(s text); CREATE TABLE Time: 1,908 ms postgres@appdb=# \dt List of relations Schema | Name | Type | Owner -----------+------+-------+---------- pg_temp_3 | t | table | postgres (1 row)
Мы видим только временную таблицу, а первую созданную таблицу уже не видим в списке баз!
Посмотрим на текущий путь поиска с помощью функции current_schemas (). А затем вставим строку во временную таблицу и прочитаем её. И далее прочитаем строки из обычной таблицы используя полный путь:
postgres@appdb=# SELECT current_schemas(true); current_schemas ----------------------------------- {pg_temp_3,pg_catalog,public,app} (1 row) Time: 0,202 ms postgres@appdb=# INSERT INTO t VALUES ('Я - временная таблица'); INSERT 0 1 Time: 0,608 ms postgres@appdb=# SELECT * FROM app.t; s --------------- Я - таблица t (1 row) Time: 0,191 ms postgres@appdb=# SELECT * FROM pg_temp.t; s ----------------------- Я - временная таблица (1 row) Time: 0,203 ms
При выходе из сеанса все объекты во временной схеме уничтожаются:
postgres@appdb=# \c appdb You are now connected to database "appdb" as user "postgres". postgres@appdb=# SELECT current_schemas(true); current_schemas ------------------------- {pg_catalog,public,app} (1 row) Time: 0,373 ms postgres@appdb=# SELECT * FROM t; s --------------- Я - таблица t (1 row) Time: 0,359 ms
Удаление схемы и базы
Схему нельзя удалить, если в ней есть какие-нибудь объекты. А для удаления схемы вместе с объектами нужно использовать опцию CASCADE:
postgres@appdb=# DROP SCHEMA app; ERROR: cannot drop schema app because other objects depend on it DETAIL: table t depends on schema app HINT: Use DROP ... CASCADE to drop the dependent objects too. Time: 0,612 ms postgres@appdb=# DROP SCHEMA app CASCADE; NOTICE: drop cascades to table t DROP SCHEMA Time: 1,175 ms
Базу данных можно удалить, если к ней нет активных подключений:
postgres@appdb=# \c postgres You are now connected to database "postgres" as user "postgres". postgres@postgres=# DROP DATABASE appdb; DROP DATABASE Time: 14,367 ms
Такое утверждение в описанном контексте:
…
public — общая схема, присутствует во всех базах данных и по умолчанию все объекты создаются в ней.
…
Это не верно. Верно другое: схема public является схемой по умолчанию для каждой созданной базы данных, поэтому её содержание различно в каждой базе данных.
Скорее всего вы правы, сейчас загружен другой темой. Как будет время вникнуть опять в postgres, то поменяю в статье это определение. Спасибо за комментарий!