Базы данных и схемы — это логическое распределение данных в кластере. А табличные пространства в 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