postgresql的体系结构

逻辑结构

物理结构

 

tips:

数据库的OID存储在pg_database系统表中；

数据库中，表，索引，序列等对象的OID存储在pg_class中

创建表空间

[postgres@localhost data]$ mkdir -p /pgdata/10/mytblspc
postgres=# create tablespace myspc LOCATION '/pgdata/10/mytblspc'
postgres-# ;
CREATE TABLESPACE
postgres=# \db
                  List of tablespaces
    Name    |  Owner   |            Location
------------+----------+--------------------------------
 myspc      | postgres | /pgdata/10/mytblspc
 pg_default | postgres |
 pg_global  | postgres |
 tbs_mydb   | pguser   | /database/pg10/pg_tbs/tbs_mydb

创建新的库或者表时，就可以指定表空间

postgres=# create table t(id serial primary key,ival int ) Tablespace myspc;
CREATE TABLE
postgres=# \d+ t
                                                Table "public.t"
 Column |  Type   | Collation | Nullable |            Default            | Storage | Stats target | Description
--------+---------+-----------+----------+-------------------------------+---------+--------------+-------------
 id     | integer |           | not null | nextval('t_id_seq'::regclass) | plain   |              |
 ival   | integer |           |          |                               | plain   |              |
Indexes:
    "t_pkey" PRIMARY KEY, btree (id)
Tablespace: "myspc"

查看mydb，oid

postgres=# select oid,datname from pg_database where datname='mydb';
  oid  | datname
-------+---------
 16388 | mydb

找到mydb的文件

 

内存结构