PostgreSQL表空间以及表空间与数据库的文件存储
PostgreSQL表空间VS数据库
表空间：PostgreSQL中，表空间是一个逻辑概念，安装好PostgreSQL后有自带的默认的表空间，也可以创建用户表空间，每个表空间，又对应着一个物理上的路径。
数据库：PostgreSQL数据库也是一个逻辑概念，数据库强绑定到一个表空间，但是数据库中的对象（表，索引等）可以指定到不同的表空间中，这个有点灵活，还是凌乱？
表空间与数据库：一个表空间可以包含多个数据库，数据库中的不同的表，也可以分布于不同的表空间中，两者没有包含和被包含的关系。是不是已经凌乱了。。。。。。
PostgreSQL默认pg_default和pg_global表空间
默认情况下，PostgreSQL有两个表空间，pg_default和pg_global,如下查看表空间信息
-- PostgreSQL表空间查询
SELECT
spcname,
pg_get_userbyid(spcowner) AS spc_owner,
pg_tablespace_location(oid) AS location
FROM pg_tablespace
ORDER BY spcname;
spcname |spc_owner|location |
----------+---------+--------------------------------+
pg_default|postgres | |
pg_global |postgres | |pg_default：数据库级系统表，临时表对象，对应的路径为pg_instance/data/base。如果存在用户自定义数据库，每个自定义数据库位于pg_instance/data/base路径下，每个数据库一个文件夹
pg_global：集群级共享系统表，对应的路径为pg_instance/data/global
在默认表空间上创建自定义数据库
当创建数据库的时候，如果没有指定表空间，那么数据库会创建在default表空间。对于数据库的物理文件位置：如果存在用户自定义数据库，每个自定义数据库位于pg_instance/data/base路径下，每个数据库一个文件夹
--默认情况下数据会创建在默认（pg_default）表空间
create database db01;
--查看数据库与对应的表空间以及物理位置
SELECT
d.datname AS database,
t.spcname AS table_space,
(select modification from pg_stat_file('base/'||d.oid||'/PG_VERSION') ) as created,
CASE
WHEN t.spcname = 'pg_default' THEN
current_setting('data_directory') || '/base/' || d.oid
WHEN t.spcname = 'pg_global' THEN
current_setting('data_directory') || '/global'
ELSE
pg_tablespace_location(t.oid)
END AS pyhsical_location
FROM pg_database d
JOIN pg_tablespace t ON d.dattablespace = t.oid
ORDER BY d.datname;
database |table_space|pyhsical_location |
---------+-----------+-----------------------------------------+
db01 |pg_default |/usr/local/pgsql16/pg9000/data/base/16400|
postgres |pg_default |/usr/local/pgsql16/pg9000/data/base/5 |
template0|pg_default |/usr/local/pgsql16/pg9000/data/base/4 |
template1|pg_default |/usr/local/pgsql16/pg9000/data/base/1 |PostgreSQL默认数据文件目录的物理存储结构如下
目录的解释如下
PGDATA/
├── base/ # 核心数据库文件存储目录
├── global/ # 集群范围（跨数据库）的系统表
├── pg_tblspc/ # 自定义表空间的符号链接
│ └── 12345 -> /mnt/ssd # 指向自定义表空间物理位置的链接
├── pg_wal/ # WAL（预写日志）文件
├── pg_xact/ # 事务提交状态数据（旧版本为pg_clog）
├── pg_subtrans/ # 子事务状态数据
├── pg_multixact/ # 多事务状态信息
├── pg_commit_ts/ # 事务提交时间戳
├── pg_replslot/ # 复制槽数据
├── pg_snapshots/ # 导出的事务快照
├── pg_stat/ # 统计信息的永久存储
├── pg_stat_tmp/ # 统计信息的临时存储
├── pg_logical/ # 逻辑解码数据
│ ├── snapshots/ # 逻辑解码快照
│ └── mappings/ # 复制标识映射
├── postgresql.conf # 主配置文件
├── pg_hba.conf # 客户端认证配置文件
├── pg_ident.conf # 用户名映射文件
├── postmaster.opts # 上次启动服务器的命令行选项
└── postmaster.pid # 当前运行的postmaster进程ID
PostgreSQL用户自定义表空间
首选需要在Linux上创建一个表空间的路径，并授权给postgres用户
linux:
root@iZ7xv55xixens4mlf4jusqZ:# mkdir /usr/local/pgsql16/pg9000/db_tbs
root@iZ7xv55xixens4mlf4jusqZ:# chown postgres:postgres /usr/local/pgsql16/pg9000/db_tbs然后再数据库中创建表空间，目的是做一个物理文件和逻辑文件的映射
psql:
CREATE TABLESPACE db_tbs LOCATION '/usr/local/pgsql16/pg9000/db_tbs';创建表空间之后，会在PostgreSQL实例的data目录下创建一个软连接，指向上述表空间的物理地址
root@iZ7xv55xixens4mlf4jusqZ:/usr/local/pgsql16/pg9000/data# cd pg_tblspc
root@iZ7xv55xixens4mlf4jusqZ:/usr/local/pgsql16/pg9000/data/pg_tblspc# ll
total 8
drwx------ 2 postgres postgres 4096 Apr 24 10:37 ./
drwx------ 19 postgres postgres 4096 Apr 24 00:00 ../
lrwxrwxrwx 1 postgres postgres 32 Apr 24 10:37 24603 -> /usr/local/pgsql16/pg9000/db_tbs/再次查看表空间，发现多了一个上面的表空间
--查看表空间以及其路径信息
SELECT
spcname,
pg_get_userbyid(spcowner) AS spc_owner,
pg_tablespace_location(oid) AS location
FROM pg_tablespace
ORDER BY spcname;
spcname |spcowner|usr |
----------+--------+--------------------------------+
db_tbs |postgres|/usr/local/pgsql16/pg9000/db_tbs|
pg_default|postgres| |
pg_global |postgres| |
PostgreSQL中利用自定义表空间创建数据库
创建用户自定义数据库，指定到自定义的表空间中
--创建数据库指定表空间
create database test_db01 tablespace db_tbs;
-- 查看数据库与对应的表空间
SELECT
d.datname,
t.spcname ,
pg_size_pretty(pg_database_size(d.datname)) AS "DBSize"
FROM pg_database d
JOIN pg_tablespace t ON d.dattablespace = t.oid
ORDER BY d.datname;
datname |spcname |DBSize |
---------+----------+-------+
db01 |pg_default|8604 kB|
postgres |pg_default|7324 kB|
template0|pg_default|7252 kB|
template1|pg_default|7161 kB|
test_db01|db_tbs |7372 kB| ----这个是自定义数据库的与对应的表空间
PostgreSQL中：库，表，索引，对应表空间之间的关系
--当前数据库下：创建表时不指定表空间，默认位于数据库的表空间下
CREATE TABLE table01 (
c1 integer NOT NULL,
c2 varchar(20),
c3 timestamp
);
--当前数据库下：创建表时显式指定表空间，表空间为当前数据库的表空间
CREATE TABLE table02 (
c1 integer NOT NULL,
c2 varchar(20),
c3 timestamp
) TABLESPACE db_tbs;
--当前数据库下：创建表时显式指定表空间，表空间为非数据库的表空间
CREATE TABLE table03 (
c1 integer NOT NULL,
c2 varchar(20),
c3 timestamp
) TABLESPACE pg_default;
-- 创建索引，如果不指定表空间，默认为数据库的表空间
CREATE INDEX idx_tb1_1 ON table01 (c1);
-- 创建索引，可以指定为数据库的表空间
CREATE INDEX idx_tb1_2 ON table01 (c2) tablespace db_tbs;
-- 创建索引，可以指定为数据库的其他的表空间
CREATE INDEX idx_tb1_3 ON table01 (c3) tablespace pg_default;数据库，表，索引与表空间之间的关系，可以参考如下这个图，能表明他们之间的关系：
1，表空间对应一个物理路径
2，一个数据库中的表，可以完全归属于一个表空间
3，一个数据库中的表，可以归属于不同的表空间
查看上述自定义对象，也即数据库的表对应的数据库，表空间，schema，物理文件路径，以及大小
这里遇到一个非常扯淡的问题，pg_class的reltablespace字段，
如果是系统表空间，该字段对应系统表空间的oid，如果是用户自定义表空间，该字段是0，也就是默认的归属的数据库的表空间，此时去查库的表空间
--查看数据库的表对应的数据库，表空间，schema，物理文件路径，以及大小
SELECT
db.datname as databae_name,
case
when ts.spcname is not null then ts.spcname
--这里有一个非常扯淡的问题，pg_class的reltablespace字段，
--如果是系统表空间，该字段对应系统表空间的oid，如果是用户自定义表空间，该字段是0，也就是默认的归属的数据库的表空间，此时去查库的表空间
when ts.spcname is null then
(
SELECT
t.spcname
FROM pg_database d
JOIN pg_tablespace t ON d.dattablespace = t.oid
where d.datname = current_database()
) end as table_space,
n.nspname as schema_name,
c.relname AS object_name,
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'i' THEN 'index'
WHEN 't' THEN 'toast_table'
ELSE c.relkind::text
END AS object_type,
pg_relation_filepath(c.oid) AS physical_location,
pg_size_pretty(pg_relation_size(c.oid)) AS object_size
FROM pg_class c
inner join pg_database db on db.datname = current_database()
LEFT JOIN pg_tablespace ts ON c.reltablespace = ts.oid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE
n.nspname NOT in ('pg_catalog','pg_toast','information_schema') --排除系统表
AND (c.relkind = 'r' OR c.relkind = 'i' OR c.relkind = 't')
ORDER BY
n.nspname,
CASE WHEN c.relkind = 'r' THEN c.relname
ELSE
(
SELECT relname FROM pg_class WHERE oid = (SELECT indrelid FROM pg_index WHERE indexrelid = c.oid)
) END,
CASE WHEN c.relkind = 'r' THEN 0 ELSE 1 END,
c.relname;
databae_name|table_space|schema_name|object_name|object_type|physical_location |object_size|
------------+-----------+-----------+-----------+-----------+-------------------------------------------+-----------+
test_db01 |db_tbs |public |table01 |table |pg_tblspc/24603/PG_16_202307071/24604/24637|0 bytes |
test_db01 |db_tbs |public |idx_tb1_1 |index |pg_tblspc/24603/PG_16_202307071/24604/24646|8192 bytes |
test_db01 |db_tbs |public |idx_tb1_2 |index |pg_tblspc/24603/PG_16_202307071/24604/24647|8192 bytes |
test_db01 |pg_default |public |idx_tb1_3 |index |base/24604/24648 |8192 bytes |
test_db01 |db_tbs |public |table02 |table |pg_tblspc/24603/PG_16_202307071/24604/24640|0 bytes |
test_db01 |pg_default |public |table03 |table |base/24604/24643 |0 bytes |
浙公网安备 33010602011771号