PostgreSQL表空间以及表空间与数据库的文件存储

PostgreSQL表空间VS数据库

表空间：PostgreSQL中，表空间是一个逻辑概念，安装好PostgreSQL后有自带的默认的表空间，也可以创建用户表空间，每个表空间，又对应着一个物理上的路径。
数据库：PostgreSQL数据库也是一个逻辑概念，数据库强绑定到一个表空间，但是数据库中的对象（表，索引等）可以指定到不同的表空间中，这个有点灵活，照旧凌乱？
表空间与数据库：一个表空间可以包含多个数据库，数据库中的不同的表，也可以分布于不同的表空间中，两者没有包含和被包含的关系。是不是已经凌乱了。。。。。。

 
PostgreSQL默认pg_default和pg_global表空间

默认情况下，PostgreSQL有两个表空间，pg_default和pg_global,如下查看表空间信息

1. -- PostgreSQL表空间查询
2. SELECT
3. spcname,
4. pg_get_userbyid(spcowner) AS spc_owner,
5. pg_tablespace_location(oid) AS location
6. FROM pg_tablespace
7. ORDER BY spcname;
10. spcname   |spc_owner|location                        |
11. ----------+---------+--------------------------------+
12. pg_default|postgres |                                |
13. pg_global |postgres |                                |

复制代码

pg_default：数据库级系统表，临时表对象，对应的路径为pg_instance/data/base。如果存在用户自界说数据库，每个自界说数据库位于pg_instance/data/base路径下，每个数据库一个文件夹
pg_global：集群级共享系统表，对应的路径为pg_instance/data/global
 
在默认表空间上创建自界说数据库

当创建数据库的时候，如果没有指定表空间，那么数据库会创建在default表空间。对于数据库的物理文件位置：如果存在用户自界说数据库，每个自界说数据库位于pg_instance/data/base路径下，每个数据库一个文件夹

1. --默认情况下数据会创建在默认（pg_default）表空间
2. create database db01;
5. --查看数据库与对应的表空间以及物理位置
6. SELECT
7.     d.datname AS database,
8.     t.spcname AS table_space,
9.     CASE
10.         WHEN t.spcname = 'pg_default' THEN
11.             current_setting('data_directory') || '/base/' || d.oid
12.         WHEN t.spcname = 'pg_global' THEN
13.             current_setting('data_directory') || '/global'
14.         ELSE
15.             pg_tablespace_location(t.oid)
16.     END AS pyhsical_location
17. FROM pg_database d
18. JOIN pg_tablespace t ON d.dattablespace = t.oid
19. ORDER BY d.datname;
22. database |table_space|pyhsical_location                        |
23. ---------+-----------+-----------------------------------------+
24. db01     |pg_default |/usr/local/pgsql16/pg9000/data/base/16400|
25. postgres |pg_default |/usr/local/pgsql16/pg9000/data/base/5    |
26. template0|pg_default |/usr/local/pgsql16/pg9000/data/base/4    |
27. template1|pg_default |/usr/local/pgsql16/pg9000/data/base/1    |

复制代码

 PostgreSQL默认数据文件目录的物理存储结构如下

目录的表明如下

1. PGDATA/
2. ├── base/                 # 核心数据库文件存储目录
3. ├── global/               # 集群范围（跨数据库）的系统表
4. ├── pg_tblspc/            # 自定义表空间的符号链接
5. │   └── 12345 -> /mnt/ssd # 指向自定义表空间物理位置的链接
6. ├── pg_wal/               # WAL（预写日志）文件
7. ├── pg_xact/              # 事务提交状态数据（旧版本为pg_clog）
8. ├── pg_subtrans/          # 子事务状态数据
9. ├── pg_multixact/         # 多事务状态信息
10. ├── pg_commit_ts/         # 事务提交时间戳
11. ├── pg_replslot/          # 复制槽数据
12. ├── pg_snapshots/         # 导出的事务快照
13. ├── pg_stat/              # 统计信息的永久存储
14. ├── pg_stat_tmp/          # 统计信息的临时存储
15. ├── pg_logical/           # 逻辑解码数据
16. │   ├── snapshots/        # 逻辑解码快照
17. │   └── mappings/         # 复制标识映射
18. ├── postgresql.conf       # 主配置文件
19. ├── pg_hba.conf           # 客户端认证配置文件
20. ├── pg_ident.conf         # 用户名映射文件
21. ├── postmaster.opts       # 上次启动服务器的命令行选项
22. └── postmaster.pid        # 当前运行的postmaster进程ID

复制代码

 
PostgreSQL用户自界说表空间

首选必要在Linux上创建一个表空间的路径，并授权给postgres用户

1. linux:
2. root@iZ7xv55xixens4mlf4jusqZ:# mkdir /usr/local/pgsql16/pg9000/db_tbs
3. root@iZ7xv55xixens4mlf4jusqZ:# chown postgres:postgres  /usr/local/pgsql16/pg9000/db_tbs

复制代码

然后再数据库中创建表空间，目的是做一个物理文件和逻辑文件的映射

1. psql:
2. CREATE TABLESPACE db_tbs LOCATION '/usr/local/pgsql16/pg9000/db_tbs';

复制代码

创建表空间之后，会在PostgreSQL实例的data目录下创建一个软毗连，指向上述表空间的物理地址

1. root@iZ7xv55xixens4mlf4jusqZ:/usr/local/pgsql16/pg9000/data# cd pg_tblspc
2. root@iZ7xv55xixens4mlf4jusqZ:/usr/local/pgsql16/pg9000/data/pg_tblspc# ll
3. total 8
4. drwx------  2 postgres postgres 4096 Apr 24 10:37 ./
5. drwx------ 19 postgres postgres 4096 Apr 24 00:00 ../
6. lrwxrwxrwx  1 postgres postgres   32 Apr 24 10:37 24603 -> /usr/local/pgsql16/pg9000/db_tbs/

复制代码

再次查看表空间，发现多了一个上面的表空间

1. --查看表空间以及其路径信息
2. SELECT
3.     spcname,
4.     pg_get_userbyid(spcowner) AS spc_owner,
5.     pg_tablespace_location(oid) AS location
6. FROM pg_tablespace
7. ORDER BY spcname;
9. spcname   |spcowner|usr                             |
10. ----------+--------+--------------------------------+
11. db_tbs    |postgres|/usr/local/pgsql16/pg9000/db_tbs|
12. pg_default|postgres|                                |
13. pg_global |postgres|                                |

复制代码

 
PostgreSQL中使用自界说表空间创建数据库

创建用户自界说数据库，指定到自界说的表空间中

1. --创建数据库指定表空间
2. create database test_db01 tablespace db_tbs;
4. -- 查看数据库与对应的表空间
5. SELECT
6.     d.datname,
7.     t.spcname ,
8.     pg_size_pretty(pg_database_size(d.datname)) AS "DBSize"
9. FROM pg_database d
10. JOIN pg_tablespace t ON d.dattablespace = t.oid
11. ORDER BY d.datname;
13. datname  |spcname   |DBSize |
14. ---------+----------+-------+
15. db01     |pg_default|8604 kB|
16. postgres |pg_default|7324 kB|
17. template0|pg_default|7252 kB|
18. template1|pg_default|7161 kB|
19. test_db01|db_tbs    |7372 kB|    ----这个是自定义数据库的与对应的表空间

复制代码

 
PostgreSQL中：库，表，索引，对应表空间之间的关系

1. --当前数据库下：创建表时不指定表空间，默认位于数据库的表空间下
2. CREATE TABLE table01 (
3.    c1 integer NOT NULL,
4.    c2 varchar(20),
5.    c3 timestamp
6. );
8. --当前数据库下：创建表时显式指定表空间，表空间为当前数据库的表空间
9. CREATE TABLE table02 (
10.    c1 integer NOT NULL,
11.    c2 varchar(20),
12.    c3 timestamp
13. ) TABLESPACE db_tbs;
16. --当前数据库下：创建表时显式指定表空间，表空间为非数据库的表空间
17. CREATE TABLE table03 (
18.    c1 integer NOT NULL,
19.    c2 varchar(20),
20.    c3 timestamp
21. ) TABLESPACE pg_default;
25. -- 创建索引，如果不指定表空间，默认为数据库的表空间
26. CREATE INDEX idx_tb1_1 ON table01 (c1);
27. -- 创建索引，可以指定为数据库的表空间
28. CREATE INDEX idx_tb1_2 ON table01 (c2) tablespace db_tbs;
29. -- 创建索引，可以指定为数据库的其他的表空间
30. CREATE INDEX idx_tb1_3 ON table01 (c3) tablespace pg_default;

复制代码

数据库，表，索引与表空间之间的关系，可以参考如下这个图，能表明他们之间的关系：
1，表空间对应一个物理路径
2，一个数据库可以完全归属于一个表空间
3，一个数据库也可以跨多个表空间

查看上述自界说对象，也即数据库的表对应的数据库，表空间，schema，物理文件路径，以及大小
这里遇到一个非常扯淡的问题，pg_class的reltablespace字段，
如果是系统表空间，该字段对应系统表空间的oid，如果是用户自界说表空间，该字段是0，也就是默认的归属的数据库的表空间，此时去查库的表空间

1. --查看数据库的表对应的数据库，表空间，schema，物理文件路径，以及大小
2. SELECT
3.     db.datname as databae_name,  
4.     case
5.             when ts.spcname is not null then ts.spcname
6.             --这里有一个非常扯淡的问题，pg_class的reltablespace字段，
7.             --如果是系统表空间，该字段对应系统表空间的oid，如果是用户自定义表空间，该字段是0，也就是默认的归属的数据库的表空间，此时去查库的表空间
8.             when ts.spcname is  null then
9.             (
10.                     SELECT
11.                             t.spcname
12.                         FROM pg_database d
13.                         JOIN pg_tablespace t ON d.dattablespace = t.oid
14.                         where d.datname = current_database()
15.                        
16.             ) end as table_space,
17.         n.nspname as schema_name,
18.     c.relname AS object_name,
19.     CASE c.relkind
20.         WHEN 'r' THEN 'table'
21.         WHEN 'i' THEN 'index'
22.         WHEN 't' THEN 'toast_table'
23.         ELSE c.relkind::text
24.     END AS object_type,
25.     pg_relation_filepath(c.oid) AS physical_location,
26.     pg_size_pretty(pg_relation_size(c.oid)) AS object_size
27. FROM pg_class c
28. inner join pg_database db on db.datname = current_database()
29. LEFT JOIN pg_tablespace ts ON c.reltablespace = ts.oid
30. LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
31. WHERE
32. n.nspname NOT in ('pg_catalog','pg_toast','information_schema') --排除系统表
33. AND (c.relkind = 'r' OR c.relkind = 'i' OR c.relkind = 't')
34. ORDER BY
35.     n.nspname,
36.     CASE WHEN c.relkind = 'r' THEN c.relname
37.     ELSE
38.     (
39.         SELECT relname FROM pg_class WHERE oid = (SELECT indrelid FROM pg_index WHERE indexrelid = c.oid)
40.     ) END,
41.     CASE WHEN c.relkind = 'r' THEN 0 ELSE 1 END,
42.     c.relname;
43.    
44.    
45.    
46. databae_name|table_space|schema_name|object_name|object_type|physical_location                          |object_size|
47. ------------+-----------+-----------+-----------+-----------+-------------------------------------------+-----------+
48. test_db01   |db_tbs     |public     |table01    |table      |pg_tblspc/24603/PG_16_202307071/24604/24637|0 bytes    |
49. test_db01   |db_tbs     |public     |idx_tb1_1  |index      |pg_tblspc/24603/PG_16_202307071/24604/24646|8192 bytes |
50. test_db01   |db_tbs     |public     |idx_tb1_2  |index      |pg_tblspc/24603/PG_16_202307071/24604/24647|8192 bytes |
51. test_db01   |pg_default |public     |idx_tb1_3  |index      |base/24604/24648                           |8192 bytes |
52. test_db01   |db_tbs     |public     |table02    |table      |pg_tblspc/24603/PG_16_202307071/24604/24640|0 bytes    |
53. test_db01   |pg_default |public     |table03    |table      |base/24604/24643                           |0 bytes    |
54.    

复制代码

 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。