Базы данных и схемы – это логическое распределение данных в кластере. А табличные пространства в PostgreSQL относится к физическому расположению данных – то есть, в каких каталогах хранятся файлы базы данных. Объекты базы данных могут хранится в разных табличных пространствах, другими словами в разных каталогах.

При инициализации кластера создаются 2 табличных пространства:

  • pg_default – табличное пространство по умолчанию, находится в каталоге $PGDATA/base;
  • pg_global – пространство для общих объектов системного каталога, $PGDATA/global.

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

Каталог $PGDATA/base разбит на подкаталоги по идентификаторам баз данных.

Когда мы создаём своё табличное пространство, мы сами указываем нужный каталог. При этом PostgreSQL создает символическую ссылку $PGDATA/pg_tblspc/<TS_OID> на указанный каталог (/путь/ver/dboid).

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

Табличное пространство по умолчанию для базы определяется шаблоном из которого клонируется новая база. Таким образом если мы поменяли табличное пространство по умолчанию в шаблоне template1, то и новые базы созданные из этого шаблона получат новое табличное пространство по умолчанию.

Практика

Существует общая табличка pg_tablespace, в которой хранится список табличных пространств:

postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.

postgres@postgres=# SELECT * FROM pg_tablespace;
 oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |
(2 rows)

Time: 0,601 ms

Перед созданием своего пространства, создадим для него каталог. При этом пользователю postgres нужны будут права на этот каталог:

postgres@postgres=# \! mkdir /home/postgres/ts_dir

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

postgres@postgres=# CREATE TABLESPACE ts LOCATION '/home/postgres/ts_dir';
CREATE TABLESPACE
Time: 1,249 ms

Список табличных пространств можно получить командой \db:

postgres@postgres=# \db
              List of tablespaces
    Name    |  Owner   |       Location
------------+----------+-----------------------
 pg_default | postgres |
 pg_global  | postgres |
 ts         | postgres | /home/postgres/ts_dir
(3 rows)

Теперь при создании базы данных можно указать ей табличное пространство по умолчанию с помощью параметра TABLESPACE:

postgres@postgres=# CREATE DATABASE appdb TABLESPACE ts;
CREATE DATABASE
Time: 64,808 ms

Подключимся к этой базе и создадим табличку. А затем создадим ещё одну табличку, но в другом табличном пространстве:

postgres@postgres=# \c appdb
You are now connected to database "appdb" as user "postgres".

postgres@appdb=# CREATE TABLE t1(id serial, name text);
CREATE TABLE
Time: 4,240 ms

postgres@appdb=# CREATE TABLE t2(n numeric) TABLESPACE pg_default;
CREATE TABLE
Time: 1,526 ms

Используя табличку pg_tables, посмотрим на таблицы в этой базе, нас будут интересовать столбцы tablename и tablespace. Чтобы исключить схему

postgres@appdb=# SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
 tablename | tablespace
-----------+------------
 t1        |
 t2        | pg_default
(2 rows)
Time: 0,602 ms

Видим 2 таблички, вторая в пространстве pg_default, а у первой пространство не указано. Если табличное пространство не указано, значит она находится в пространстве по умолчанию для данной базы данных.

Можем создать другую базу в табличном пространстве по умолчанию и сделать там табличку в табличном пространстве ts:

postgres@appdb=# CREATE DATABASE configdb;
CREATE DATABASE
Time: 69,267 ms

postgres@appdb=# \c configdb
You are now connected to database "configdb" as user "postgres".

postgres@configdb=# CREATE TABLE t(n integer) TABLESPACE ts;
CREATE TABLE
Time: 2,701 ms

PostgreSQL позволяет перемещать объекты между табличными пространствами с помощью ALTER TABLE ... SET TABLESPACE ...:

postgres@configdb=# \c appdb
You are now connected to database "appdb" as user "postgres".

postgres@appdb=# ALTER TABLE t1 SET TABLESPACE pg_default;
ALTER TABLE
Time: 2,944 ms

postgres@appdb=# SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
 tablename | tablespace
-----------+------------
 t2        | pg_default
 t1        | pg_default
(2 rows)
Time: 1,271 ms

А также можно переместить все объекты из одного пространства в другое (ALTER TABLE ALL IN TABLESPACE ... SET TABLESPACE ...):

postgres@appdb=# ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE ts;
ALTER TABLE
Time: 3,021 ms

postgres@appdb=# SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
 tablename | tablespace
-----------+------------
 t2        |
 t1        |
(2 rows)
Time: 0,640 ms

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

Занимаемый объём табличного пространства можно посмотреть с помощью функции pg_tablespace_size():

postgres@appdb=# SELECT pg_size_pretty( pg_tablespace_size('ts') );
 pg_size_pretty
----------------
 7677 kB
(1 row)

Time: 2,648 ms

Табличное пространство ts является пространством по умолчанию для базы appdb, в нем сейчас 2 пустые таблицы и объекты системного каталога для базы appdb.

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

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

Выясним OID табличного пространства из pg_tablespace:

postgres@appdb=# SELECT OID FROM pg_tablespace WHERE spcname = 'ts';
  oid
-------
 16464
(1 row)
Time: 0,241 ms

Этот OID запишем в переменную tsoid с помощью \gset:

postgres@appdb=# SELECT OID AS tsoid FROM pg_tablespace WHERE spcname = 'ts' \gset
Time: 0,235 ms

С помощью функции pg_tablespace_databases(<oid пространства>) можем узнать oid баз данных в табличном пространстве:

postgres@appdb=# SELECT pg_tablespace_databases(:tsoid);
 pg_tablespace_databases
-------------------------
                   16465
                   16481
(2 rows)
Time: 0,658 ms

Следующим запросом полученные OID баз превратим в имена баз:

postgres@appdb=# SELECT datname FROM pg_database WHERE OID IN (SELECT pg_tablespace_databases(:tsoid));
 datname
----------
 appdb
 configdb
(2 rows)
Time: 0,790 ms

Теперь нужно подключиться к каждой базе и получить список объектов из pg_class, где табличное пространство равно ts:

postgres@appdb=# \c configdb
You are now connected to database "configdb" as user "postgres".
postgres@configdb=# SELECT relname FROM pg_class WHERE reltablespace = :tsoid;
 relname
---------
 t
(1 row)
Time: 2,320 ms

Можем удалить табличку t:

postgres@configdb=# DROP TABLE t;
DROP TABLE
Time: 1,641 ms

Со второй базой посложнее, там табличное пространство ts используется по умолчанию. Это означает что все объекты из системного каталога находятся в этом пространстве и удалить их не получится. Зато получится сменить табличное пространство по умолчанию для этой базы:

postgres@appdb=# \c postgres
You are now connected to database "postgres" as user "postgres".

postgres@postgres=# ALTER DATABASE appdb SET TABLESPACE pg_default;
ALTER DATABASE
Time: 78,812 ms

При смене табличного пространства по умолчанию все объекты системного каталога переезжают в новое табличное пространство.

Вот теперь мы можем удалить табличное пространство ts:

postgres@postgres=# DROP TABLESPACE ts;
DROP TABLESPACE
Time: 1,125 ms

Сводка
Табличные пространства в PostgreSQL
Имя статьи
Табличные пространства в PostgreSQL
Описание
Табличные пространства в PostgreSQL относится к физическому расположению данных - то есть, в каких каталогах хранятся файлы базы данных

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *