Табличные пространства (ТП)

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

Табличные пространства в файловой системе

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

На схеме продемонстрирована организация данных в базах данных с использованием схем (pg_catalog, public и plugh) и табличных пространств (pg_global, pg_default и user_tablespace — пользовательское пространство).

Вывести список табличных пространств можно с помощью метакоманды \db.

postgres@postgres=# \db

            List of tablespaces
    Name    |  Owner   |  Location
------------+----------+--------------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

На данный момент существует всего два пространства: pg_default и pg_global. Они относятся к системным каталогам и создаются при инициализации кластера вместе с каталогами для хранения данных, таблицами общего каталога и базами данных postgres, template1 и template0. Посмотрим на них чуть подробнее.

Табличное пространство по умолчанию pg_default установлено табличным пространством по умолчанию для баз данных template1 и template0. А поскольку при создании базы данных за основу берется template1, если не указан другой шаблон, табличное пространство по умолчанию наследуется новой базой данных. Располагается pg_default в каталоге $PGDATA/base.

Глобальное табличное пространство pg_global предназначено для хранения общих системных каталогов. В них содержатся метаданные, которые одинаковы для всех баз данных в кластере, например информация о пользователях, языковых объектах и функциях. Располагается pg_global в каталоге $PGDATA/global.

Проверить расположение пространств можно следующим образом.

postgres@postgres=# \! ls -ld $PGDATA/{global,base}

Вместо того, чтобы писать две отдельные команды для global и base, можно использовать фигурные скобки как в примере выше. Оболочка самостоятельно развернет их при выполнении и выполнит по отдельности, но вывод будет общий.

drwx------ 6 postgres postgres 4096 Oct 12 18:54 /pgdata/06/data/base
drwx------ 2 postgres postgres 4096 Oct 12 18:54 /pgdata/06/data/global

Начало путей каталогов в выводе совпадают с $PGDATA — /pgdata/06/data/, а дальше идет разветвление: pg_default лежит в подкаталоге base, а pg_global — в global.

Еще один способ для получения списка табличных пространств — запрос к pg_tablespace. В данном каталоге хранится информация обо всех табличных пространствах кластера.

postgres@postgres=# SELECT spcname FROM pg_tablespace;

  spcname
------------
 pg_default
 pg_global
(2 rows)

Пользовательское табличное пространство

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

Создание табличного пространства

Перед созданием табличного пространства нужно убедиться, что в файловой системе имеется пустой каталог, его владельцем является пользователь ОС postgres и у него есть права на запись в этот каталог. Каталог может располагаться в любом месте файловой системы.

к сведению

Владельцем каталога должен быть пользователь ОС postgres, поскольку от его имени работают процессы кластера.

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

[postgres@ServerName ~]$ mkdir -p /pgdata/06/newdisk

Теперь можно создать новое табличное пространство.

postgres@postgres=# CREATE TABLESPACE newtabspace LOCATION '/pgdata/06/newdisk';

CREATED TABLESPACE

Табличное пространство создается с помощью команды CREATE с ключевым словом TABLESPACE, после которого нужно указать имя табличного пространства, в нашем случае newtabspace. Далее следует ключевое слово LOCATION, которое указывает на путь к созданному ранее каталогу.

Проверим список табличных пространств.

postgres@postgres=# \db

            List of tablespaces
    Name    |  Owner   |     Location
------------+----------+--------------------
newtabspace | postgres | /pgdata/06/newdisk
 pg_default | postgres |
  pg_global | postgres |
(3 rows)

Как видно, созданное табличное пространство newtabspace появилось в списке. Поскольку это пользовательское пространство, для него еще указывается путь.

В СУБД Pangolin можно создавать засекреченные табличные пространства. Подробнее о них в курсе DBP1.

Каталог pg_tblspc

PostgreSQL ведет учет всех пользовательских табличных пространств с помощью каталога pg_tblspc. В нем хранятся символические ссылки пользовательских пространств, которые ведут на физические каталоги. Имя ссылки соответствует oid табличного пространства.

Выведем содержимое pg_tblspc.

postgres@postgres=# \! ls -l $PGDATA/pg_tblspc/

total 0
lrwxrwxrwx 1 postgres postgres 18 Oct 13 21:47 16440 -> /pgdata/06/newdisk

На данный момент создано лишь одно пользовательское пространство newtabspace, поэтому и ссылка в каталоге pg_tblspc всего одна. Имя ссылки — 16440, сама ссылка — /pgdata/06/newdisk, то есть расположение каталога, которое было указано при создании пространства.

Если необходимо получить oid любого табличного пространства, можно обратиться к каталогу pg_tablespace.

postgres@postgres=#SELECT oid FROM pg_tablespace WHERE spcname = 'newtabspace';

  oid
-------
 16440
(1 row)

В запросе к pg_tablespace нужно указать имя пространства, oid которой интересует. В нашем случае запрос был по пространству newtabspace. Полученный oid совпал с тем, что был указан в имени ссылки в pg_tblspc.

Базы данных в табличных пространствах

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

Каталоги баз данных в ТП

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

postgres@postgres=# \! oid2name

All databases:
  Oid Database Name    Tablespace
----------------------------------
   5    postgres        pg_default
16409   sch_db          pg_default
   4    template0       pg_default
   1    template1       pg_default

При использовании oid2name без аргументов в выводе программы будет представлен список баз данных с их oid и табличными пространствами по умолчанию. В нашем случае для всех баз данных табличным пространством назначено pg_default.

Зная, что pg_default расположено в каталоге $PGDATA/base, выведем его содержимое и проверим, есть ли в нем такие же подкаталоги.

postgres@postgres=# \! ls -l $PGDATA/base

total 52
drwx------ 2 postgres postgres 12288 Oct 27 15:40 1
drwx------ 2 postgres postgres 12288 Oct  2 15:42 16409
drwx------ 2 postgres postgres 12288 Oct 27 14:38 4
drwx------ 2 postgres postgres 12288 Oct 27 15:40 5

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

Почему все подкаталоги имеют одинаковый размер?

Метакоманда ls -l выводит список файлов и каталогов в подробном виде (каждый элемент на отдельной строке). Число 12288 в выводе отображает размер метаданных подкаталогов — 12 КБ, а не фактический размер всех файлов в подкаталогах. На этот случай есть метакоманда du. Применим ее для базы данных sch_db.

postgres@sch_db=# \! du -sh $PGDATA/base/16409

14M     /pgdata/06/data/base/16409

Опция -s позволяет указать нужный каталог, а опция -h представляет размер в удобном виде: вместо 13916 — 14 МБ.

Теперь хорошо видно, что фактический размер базы данных sch_db составляет 14 МБ.

Создание баз данных с указанием ТП

Все базы данных, имеющиеся в кластере, расположены в табличном пространстве по умолчанию. Создадим новую базу данных в пространстве newtabspace. Для этого после названия базы данных указывается ключевое слово TABLESPACE и название табличного пространства.

postgres@sch_db=# CREATE DATABASE nts_db TABLESPACE newtabspace;

CREATE DATABASE

Теперь выведем подробную информацию о пользовательских базах данных.

postgres@sch_db=# \x \l+ ???_db \x

Разбор запроса

• \x — включение расширенного режима вывода. Информация по каждой базе данных будет оформлена в вертикальную запись вместо общей таблицы;
• \l+ — команда для вывода всех баз данных с подробной информацией о них;
• ???_db — шаблон для поиска баз данных с названием *_db (??? аналогичны *);
• \x — повторное использование данной команды выключит расширенный вывод в конце.

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

-[ RECORD 1 ]------+------------
Name               | nts_db
Owner              | postgres
Encoding           | UTF8
Collate            | en_US.UTF8
Ctype              | en_US.UTF8
ICU Locale         |
Locale Provider    | libc
Access privileges  |
Size               | 9607 kB
Tablespace         | newtablespace
Description        |
-[ RECORD 2 ]------+------------
Name               | sch_db
Owner              | postgres
Encoding           | UTF8
Collate            | en_US.UTF8
Ctype              | en_US.UTF8
ICU Locale         |
Locale Provider    | libc
Access privileges  |
Size               | 9687 kB
Tablespace         | pg_default
Description        |

В записях о nts_db и sch_db есть одинаковые поля, такие как Owner, Encoding и другие, а также отличающиеся: Size и Tablespace. Наиболее важное поле Tablespace, поскольку это не просто каталог, в котором лежит база данных. Именно в этом пространстве по умолчанию будут создаваться все объекты базы данных.

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

postgres@sch_db=# SELECT oid, datname, dattablespace FROM pg_database;

В таблице pg_database достаточно много полей, поэтому для вывода ограничимся oid, datname и dattablespace.

  oid  |  datname  | dattablespace
-------+-----------+---------------
     5 | postgres  |          1663
     1 | template1 |          1663
     4 | template0 |          1663
 16409 | sch_db    |          1663
 16413 | nts_db    |         16388
(5 rows)

У базы данных sch_db, как и для postgres, template1 и template0, пространство по умолчанию pg_default с oid равным 1663. А вот у nts_db табличное пространство отличается, поэтому и oid другой — 16388.

Убедиться, что oid 16388 соответствует именно newtablespace можно с помощью таблицы pg_tablespace. В запросе укажем oid и spcname.

postgres@sch_db=# SELECT oid, spcname FROM pg_tablespace;

  oid  |   spcname
-------+-------------
  1663 | pg_default
  1664 | pg_global
 16388 | newtabspace
(3 rows)

Как видим, oid и имя табличного пространства совпало.

Работа с табличным пространством

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

Объекты баз данных в ТП

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

Выведем список таких баз данных для табличного пространства newtabspace.

postgres@sch_db=# SELECT datname FROM pg_database WHERE oid
                 IN (SELECT pg_tablespace_databases(SELECT oid FROM pg_tablespace WHERE spcname = 'newtabspace'));

Разбор запроса

Для удобства вывода запрос был усложнен, разберем его изнутри:

• SELECT oid FROM pg_tablespace WHERE spcname = 'newtabspace' — получаем oid табличного пространства newtabspace из таблицы пространств pg_tablespace;
• SELECT pg_tablespace_databases('{oid}') — вместо {oid} передаем функции pg_tablespace_databases() oid пространства newtabspace и получаем последовательность из oid баз данных;
• SELECT datname FROM pg_database WHERE oid IN ('{oid}') — выбираем из списка всех баз данных в таблице pg_database тольке те, oid которых равен oid из списка с предыдущего этапа.

 datname
---------
 nts_db
(1 row)

Если бы внешний уровень запроса, то есть запрос к pg_database, был пропущен, в выводе был бы получен лишь oid базы данных.

 pg_tablespace_databases
-------------------------
                   16413
(1 row)

Так или иначе, функция pg_tablespace_databases() вернула лишь одну базу данных — nts_db.

Почему функция pg_tablespace_databases() вернула nts_db, ведь эта база данных пустая?

Все верно, в базе данных nts_db еще не было создано ни одного объекта. Однако при создании nts_db табличное пространство newtabspace было указано как пространство по умолчанию, поэтому в нем лежат системные каталоги базы данных. Несмотря на то, что это не совсем объекты, PostgreSQL считает их в том числе.

Удаление ТП

Когда необходимость в табличном пространстве пропадает, его нужно удалить с помощью команды DROP TABLESPACE. Но в отличие от других объектов, перед удалением табличного пространства нужно убедиться, что оно не содержит в себе каких-либо объектов. То есть либо перенести все объекты из него в другие табличные пространства, либо удалить их.

Если же не очистить пространство, при попытки удаления появится ошибка.

postgres@sch_db=# DROP TABLESPACE newtabspace;

ERROR:  tablespace "newtabspace" is not empty

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

Удалить базу данных командой DROP DATABASE или схему командой DROP SCHEMA ... CASCADE можно со всеми данными, которые в них находились.

postgres@sch_db=# DROP DATABASE nts_db;

DROP DATABASE

После удаления единственного содержимого пространства newtabspace, приступим и к его удалению.

postgres@sch_db=# DROP TABLESPACE newtabspace;

DROP TABLESPACE

Проверим, что newtabspace исчезло из списка табличных пространств.

postgres@sch_db=# \db

             List of tablespaces
    Name     |  Owner   |      Location
-------------+----------+--------------------
 pg_default  | postgres |
 pg_global   | postgres |
(2 rows)

Пространства в списке нет, удаление прошло корректно.

Итоги

• Табличное пространство (ТП) — именованный каталог в файловой системе для физического размещения файлов данных;
• pg_default — ТП по умолчанию, расположено в $PGDATA/base; наследуется новыми базами данных из template1;
• pg_global — глобальное ТП для общих системных каталогов кластера, расположено в $PGDATA/global;
• Пользовательское ТП создается командой CREATE TABLESPACE ... LOCATION в пустой каталог, владельцем которого является пользователь ОС postgres;
• В каталоге $PGDATA/pg_tblspc создается символическая ссылка с именем oid пространства на физический каталог;
• Перед удалением ТП (DROP TABLESPACE) необходимо очистить его — перенести или удалить все объекты;
• Функция pg_tablespace_databases(oid) возвращает oid баз данных, хранящих объекты в заданном ТП;
• Каждая база данных имеет подкаталог с именем oid внутри каталога табличного пространства.

Самопроверка

Вопрос 1

В каком подкаталоге $PGDATA располагается табличное пространство pg_default?

$PGDATA/global$PGDATA/base$PGDATA/pg_tblspc$PGDATA/main$PGDATA/data

Вопрос 2

Создан новый пользователь командой CREATE USER. В каком табличном пространстве произойдут изменения?

Ни в какомpg_globalpg_defaultpg_userpg_role

Вопрос 3

Какой каталог в PGDATA хранит символические ссылки на пользовательские табличные пространства?

pg_defaultpg_tablespacepg_tblspcpg_linkspg_space

Вопрос 4

Что произойдет при попытке удалить табличное пространство, содержащее объекты?

Пространство будет удалено вместе с объектамиВозникнет ошибка — пространство должно быть пустымОбъекты будут перемещены в pg_defaultБудет запрошено подтверждение