При создании кластера создаются два табличных пространства:
=> SELECT * FROM pg_tablespace;
spcname | spcowner | spcacl | spcoptions ------------+----------+--------+------------ pg_default | 10 | | pg_global | 10 | | (2 rows)
Для нового табличного пространства нужен пустой каталог, владельцем которого является пользователь postgres.
postgres$ cd /home/postgres
postgres$ mkdir ts_dir
Теперь можно создать табличное пространство:
=> CREATE TABLESPACE ts LOCATION '/home/postgres/ts_dir';
CREATE TABLESPACE
Список табличных пространств можно получить и командой psql:
=> \db
List of tablespaces
Name | Owner | Location
------------+----------+-----------------------
pg_default | postgres |
pg_global | postgres |
ts | postgres | /home/postgres/ts_dir
(3 rows)
У каждой базы данных есть табличное пространство "по умолчанию".
Создадим БД и назначим ей ts в качестве такого пространства:
=> CREATE DATABASE appdb TABLESPACE ts;
CREATE DATABASE
Теперь все создаваемые таблицы и индексы будут попадать в ts, если явно не указать другое.
Подключимся к базе:
=> \c appdb
You are now connected to database "appdb" as user "postgres".
Создадим таблицу:
=> CREATE TABLE t1(id serial, name text);
CREATE TABLE
При создании объекта табличное пространство можно указать явно:
=> CREATE TABLE t2(n numeric) TABLESPACE pg_default;
CREATE TABLE
=> SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
tablename | tablespace -----------+------------ t1 | t2 | pg_default (2 rows)
Пустое поле tablespace указывает на табличное пространство по умолчанию, а у второй таблицы поле заполнено.
Одно табличное пространство может использоваться для объектов нескольких баз данных.
=> CREATE DATABASE configdb;
CREATE DATABASE
У этой БД табличным пространством по умолчанию будет pg_default.
=> \c configdb
You are now connected to database "configdb" as user "postgres".
=> CREATE TABLE t(n integer) TABLESPACE ts;
CREATE TABLE
Таблицы (и другие объекты, например, индексы), можно перемещать между табличными пространствами.
=> \c appdb
You are now connected to database "appdb" as user "postgres".
=> ALTER TABLE t1 SET TABLESPACE pg_default;
ALTER TABLE
=> SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
tablename | tablespace -----------+------------ t2 | pg_default t1 | pg_default (2 rows)
Можно переместить и все объекты из одного табличного пространства в другое:
=> ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE ts;
ALTER TABLE
=> SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
tablename | tablespace -----------+------------ t2 | t1 | (2 rows)
Важно понимать, что перенос в другое табличное пространство (в отличие от переноса в другую схему) - физическая операция, связанная с копированием файлов данных из каталога в каталог. На время ее выполнения перемещаемый объект полностью блокируется.
Мы уже рассматривали, как узнать объем, занимаемый базой данных. Объем можно посмотреть и в разрезе табличных пространств:
=> SELECT pg_size_pretty( pg_tablespace_size('ts') );
pg_size_pretty ---------------- 7134 kB (1 row)
Почему такой размер, если в табличном пространстве всего несколько пустых таблиц?
Поскольку ts является табличным пространством по умолчанию для базы appdb, в нем хранятся объекты системного каталога. Они и занимают место.
Табличное пространство можно удалить, но только в том случае, если оно пусто.
=> DROP TABLESPACE ts;
ERROR: tablespace "ts" is not empty
В отличие от удаления схемы, в команде DROP TABLESPACE нельзя использовать фразу CASCADE: объекты табличного пространства могут принадлежать разным базам данных, а подключены мы только к одной.
Но можно выяснить, в каких базах есть зависимые объекты. В этом нам поможет системный каталог.
Сначала узнаем и запомним OID табличного пространства:
=> SELECT OID FROM pg_tablespace WHERE spcname = 'ts';
oid ------- 16469 (1 row)
=> SELECT OID AS tsoid FROM pg_tablespace WHERE spcname = 'ts' \gset
Затем получим список баз данных, в которых есть объекты из удаляемого пространства:
=> SELECT datname FROM pg_database WHERE OID IN (SELECT pg_tablespace_databases(:tsoid));
datname ---------- configdb appdb (2 rows)
Дальше подключаемся к каждой базе данных и получаем список объектов из pg_class:
=> \c configdb
You are now connected to database "configdb" as user "postgres".
=> SELECT relnamespace::regnamespace, relname, relkind FROM pg_class WHERE reltablespace = :tsoid;
relnamespace | relname | relkind --------------+---------+--------- public | t | r (1 row)
Таблица больше не нужна, удалим ее.
=> DROP TABLE t;
DROP TABLE
И вторая база данных. Но, поскольку ts является табличным пространством по умолчанию, у объектов в pg_class поле содержит ноль:
=> \c appdb
You are now connected to database "appdb" as user "postgres".
=> SELECT count(*) FROM pg_class WHERE reltablespace = 0;
count ------- 313 (1 row)
Это, как нам уже известно, объекты системного каталога.
Табличное пространство по умолчанию можно сменить; при этом все таблицы из старого пространства физически переносятся в новое. Предварительно надо отключиться от базы.
=> \c postgres
You are now connected to database "postgres" as user "postgres".
=> ALTER DATABASE appdb SET TABLESPACE pg_default;
ALTER DATABASE
Вот теперь табличное пространство может быть удалено.
=> DROP TABLESPACE ts;
DROP TABLESPACE
Конец демонстрации.