查看表空间位置
postgres=# \d pg_tablespace
Table "pg_catalog.pg_tablespace"
Column | Type | Collation | Nullable | Default
------------+-----------+-----------+----------+---------
oid | oid | | not null |
spcname | name | | not null |
spcowner | oid | | not null |
spcacl | aclitem[] | | |
spcoptions | text[] | C | |
Indexes:
"pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
"pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
postgres=# select oid,spcname from pg_tablespace;
oid | spcname
-------+------------
1663 | pg_default
1664 | pg_global
24577 | tblspc1
(3 rows)
postgres=# select pg_size_pretty(pg_tablespace_size('pg_default'));
pg_size_pretty
----------------
69 MB
(1 row)
在Linux操作系统上面查看表对应文件
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
postgres=# select pg_relation_filepath('t1');
pg_relation_filepath
---------------------------------------------
pg_tblspc/24577/PG_12_201909212/13593/40960
(1 row)
当文件超过1G就会遵从新的规则,40960.1 40960.2的方式产生。
[postgres@pg1 data]$ cd $PG_HOME/data/pg_tblspc/24577/PG_12_201909212/13593
[postgres@pg1 13593]$ ll -lah | grep 40960
-rw-------. 1 postgres postgres 24K Jun 6 22:00 40960
-rw-------. 1 postgres postgres 24K Jun 6 21:59 40960_fsm
fsm空间空间地图,记录的有多少的可用空间,文件大小也是数据页的整数倍。
vm可见性地图,在vacuum时候参考vm进行冻结。
表空间
pg8.1以后pg开始支持表空间,MySQL的表空间与Oracle的表空间类似,然而PG的表空间本质上就是一个目录。
仅仅是数据文件之外附加的一个区域。
[postgres@pg1 ~]$ pwd
/home/postgres
[postgres@pg1 ~]$ mkdir jantblspc
[postgres@pg1 ~]$ ll -lah |grep jantblspc
drwxrwxr-x. 2 postgres postgres 6 Sep 9 11:15 jantblspc
[postgres@pg1 ~]$ psql
psql (12.2)
Type "help" for help.
postgres=# select spcname,oid from pg_tablespace;
spcname | oid
------------+-------
pg_default | 1663
pg_global | 1664
tblspc1 | 24577
(3 rows)
postgres=# create tablespace janspc location '/home/postgres/jantblspc';
CREATE TABLESPACE
postgres=# select spcname,oid from pg_tablespace;
spcname | oid
------------+-------
pg_default | 1663
pg_global | 1664
tblspc1 | 24577
janspc | 49167
(4 rows)
postgres=# exit
[postgres@pg1 ~]$ cd $PG_HOME/data/pg_tblspc
[postgres@pg1 pg_tblspc]$ ll |grep 49167
lrwxrwxrwx. 1 postgres postgres 24 Sep 9 11:16 49167 -> /home/postgres/jantblspc
[postgres@pg1 pg_tblspc]$ cd /home/postgres/jantblspc/
[postgres@pg1 jantblspc]$ ll
total 0
drwx------. 2 postgres postgres 6 Sep 9 11:16 PG_12_201909212
[postgres@pg1 jantblspc]$ ll PG_12_201909212/
total 0
PG_12_201909212是PG以默认格式自动创建的目录
PG_VersionNumber_CreateDate
jantestdb=# \c
You are now connected to database "jantestdb" as user "postgres".
jantestdb=# select spcname,oid from pg_tablespace;
spcname | oid
------------+-------
pg_default | 1663
pg_global | 1664
tblspc1 | 24577
janspc | 49167
(4 rows)
jantestdb=# create table jant1 (id int,name varchar(20)) tablespace janspc;
CREATE TABLE
jantestdb=# select pg_relation_filepath('jant1');
pg_relation_filepath
---------------------------------------------
pg_tblspc/49167/PG_12_201909212/49162/49171
(1 row)
jantestdb=# exit
[postgres@pg1 jantblspc]$ pwd
/home/postgres/jantblspc
[postgres@pg1 jantblspc]$ ll
total 0
drwx------. 4 postgres postgres 32 Sep 9 11:32 PG_12_201909212
[postgres@pg1 jantblspc]$ cd /home/postgres/jantblspc/PG_12_201909212/49162/
[postgres@pg1 49162]$ ll
total 0
-rw-------. 1 postgres postgres 0 Sep 9 11:32 49171
实验证明,PG数据库通过表空间的实际存储位置控制数据的存放位置,这样就可以解决物理磁盘空间不够的问题。
在PG_HOME/data/pg_tblspc以软链接的形式控制数据文件及物文件的对应关系。
jantestdb=# select * from jant1;
id | name
----+------
(0 rows)
jantestdb=# insert into jant1 values (1,'JanNest1');
INSERT 0 1
jantestdb=# insert into jant1 values (2,'JanNest2');
INSERT 0 1
jantestdb=# insert into jant1 values (3,'JanNest3');
INSERT 0 1
......
......
jantestdb=# insert into jant1 values (29,'JanNest13');
INSERT 0 1
jantestdb=# insert into jant1 values (30,'JanNest13');
INSERT 0 1
jantestdb=# insert into jant1 select * from jant1;
INSERT 0 60
jantestdb=# insert into jant1 select * from jant1;
INSERT 0 120
jantestdb=# insert into jant1 select * from jant1;
INSERT 0 240
jantestdb=# exit
[postgres@pg1 49162]$ ll
total 48
-rw-------. 1 postgres postgres 24576 Sep 9 12:00 49171
-rw-------. 1 postgres postgres 24576 Sep 9 12:00 49171_fsm
fsm文件会随数据插入的增多而产生。
vm文件会随表update操作的增加而产生。
PG会将数据文件格式化为若干数据页,并标记数据页从0开始依次增加。
pd_lsn表示当前日志序列号;
pd_lower表示当前数据页块头指针最新的位置;
pd_upper表示当前数据页最新行的开头的位置;
块头的数据是从上往下增长,随数据增多块头只想数据行的指针(point)会逐渐增多。
Oracle中的sequential read在PG中的实现就是依据数据页页头行指针的顺序,顺序的读取对应行的数据。
[postgres@pg1 49162]$ psql -d jantestdb
psql (12.2)
Type "help" for help.
jantestdb=# select ctid,id,name from jant1 where ctid='(0,1)';
ctid | id | name
-------+----+----------
(0,1) | 1 | JanNest1
(1 row)
jantestdb=# select ctid,id,name from jant1 where ctid='(1,1)';
ctid | id | name
-------+----+----------
(1,1) | 6 | JanNest6
ctid类似于Oracle中的rowid的概念;
查询第0块的第一行的数据是1和JanNest1;
查询第1块的第一行的数据是6和JanNest6。
OID
PG数据库中,所有的数据库对象都有一个OID;
OID称为对象标识符,是无符号4字节整数;
数据库的OID和堆表的OID分别存储在pg_database和pg_class中。
PG将数据字典表叫做系统目录,pg_catalog是一个模式(schema);
jantestdb=# \dS+
List of relations
Schema | Name | Type | Owner | Size | Description
------------+---------------------------------+-------+----------+------------+-------------
pg_catalog | pg_aggregate | table | postgres | 56 kB |
pg_catalog | pg_am | table | postgres | 40 kB |
pg_catalog | pg_amop | table | postgres | 80 kB |
jantestdb=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
jantestdb | januser | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(9 rows)
postgres数据库,类似Oracle的system表空间,存储pg_catalog等数据字典表。
template1可以自定义,为自定义情况下与template0一致。
pg_class表示当前数据库下,所有对象的信息。
逻辑结构
base目录是所有物理文件的父目录,直接查看base目录下是数据库的对象标识符OID。
jantestdb=# select oid,datname from pg_database;
oid | datname
-------+-----------
13593 | postgres
16384 | oracle
1 | template1
13592 | template0
32782 | d1
32786 | u2_db
32787 | u1_db
49157 | test
49162 | jantestdb
(9 rows)
jantestdb=# exit
[postgres@pg1 ~]$ cd /usr/local/pg12.2/data/base/
[postgres@pg1 base]$ ll
total 108
drwx------. 2 postgres postgres 8192 Apr 26 13:40 1
drwx------. 2 postgres postgres 8192 Apr 26 13:40 13592
drwx------. 2 postgres postgres 8192 Sep 9 10:06 13593
drwx------. 2 postgres postgres 8192 Sep 9 10:06 16384
drwx------. 2 postgres postgres 8192 Sep 9 10:06 32782
drwx------. 2 postgres postgres 8192 May 19 09:51 32786
drwx------. 2 postgres postgres 8192 May 18 14:39 32787
drwx------. 2 postgres postgres 8192 Sep 9 10:06 49157
drwx------. 2 postgres postgres 8192 Sep 9 10:06 49162
truncate会改变表的relfilenode,原来的数据文件也被删掉了。
[postgres@pg1 base]$ psql -d jantestdb
psql (12.2)
Type "help" for help.
jantestdb=# select relname,oid,relfilenode from pg_class;
relname | oid | relfilenode
-----------------------------------------------+-------+-------------
test | 49163 | 49163
jant1 | 49171 | 49171
pg_statistic | 2619 | 2619
......
......
jantestdb=# truncate table jant1;
TRUNCATE TABLE
jantestdb=# select relname,oid,relfilenode from pg_class;
relname | oid | relfilenode
-----------------------------------------------+-------+-------------
test | 49163 | 49163
jant1 | 49171 | 49174
pg_statistic | 2619 | 2619
......
......
[postgres@pg1 49162]$ ll
total 0
-rw-------. 1 postgres postgres 0 Sep 9 12:44 49174
[postgres@pg1 49162]$ pwd
/home/postgres/jantblspc/PG_12_201909212/49162