Postgresql物理目录结构（postgresql和mysql区别）

1.1 数据目录布局

1.2 数据库、表目录定位

可以查询pg_database表获取数据库的oid，数据库对应的目录为$PGDATA/base/oid

mydb=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | myq      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

mydb=# select oid,datname from pg_database where datname='mydb';
  oid  | datname 
-------+---------
 16385 | mydb
(1 row)

mydb=# \! ls $PGDATA/base/
1  13891  13892  16385	pgsql_tmp

通过pg_class视图查询表的oid，然后通过函数pg_relation_filepath()获取表的数据文件路径。

mydb=# \dt
         List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 myq    | employees | table | myq
 myq    | emps      | table | myq
 myq    | t         | table | myq
(3 rows)

mydb=# select oid,relname from pg_class where relname='emps';
  oid  | relname 
-------+---------
 16444 | emps
(1 row)

mydb=# select pg_relation_filepath(16444);
 pg_relation_filepath 
----------------------
 base/16385/16444
(1 row)

mydb=# \! ls $PGDATA/base/16385/16444*
/var/postgres/data/base/16385/16444  /var/postgres/data/base/16385/16444_fsm  /var/postgres/data/base/16385/16444_vm

当执行某些操作时，如truncate、reindex等时，文件的relfilenode会改变，oid不变。

mydb=# select oid,relname,relfilenode from pg_class where relname='t';
  oid  | relname | relfilenode 
-------+---------+-------------
 16448 | t       |       16462
(1 row)

mydb=# truncate table t;
TRUNCATE TABLE
mydb=# select oid,relname,relfilenode from pg_class where relname='t';
  oid  | relname | relfilenode 
-------+---------+-------------
 16448 | t       |       16466
(1 row)

1.3. 表和数据文件

每个表都对应着操作系统上的一个或多个数据文件。当表的数据文件增长到1GB时，会自动分割，创建一个新的数据文件，类似于12345，12345.1，12345.2等等。

可以在编译pg时通过参数./configure --with-segsize指定数据文件分割的阈值。

每个表除了数据文件外，还有两个相关的文件fsm和vm。

• fsm文件 用于跟踪可用的数据页，快速查找可用于插入数据的页。只有vacuum操作后才会有。
• vm文件 可以快速判断一个页是否需要vacuum或frozen。不适用于索引页。被vm跟踪的页在vacuum时会被跳过。
• 索引页只有fsm文件，没有vm文件。

1.4. 自定义表空间

位于自定义表空间的对象存储于自定义表空间目录，会在$PGDATA/pg_tblspc目录创建一个名为表空间oid的软连接，软连接指向了表空间所在目录。 自定义表空间的目录结构为PG_版本_创建时间/数据库oid/数据文件

#自定义表空间myqtbs位于目录/var/postgres/myqtbs
postgres=# \db+
                                        List of tablespaces
    Name    |  Owner   |       Location       | Access privileges | Options |  Size   | Description 
------------+----------+----------------------+-------------------+---------+---------+-------------
 myqtbs     | myq      | /var/postgres/myqtbs |                   |         | 0 bytes | 
 pg_default | postgres |                      |                   |         | 41 MB   | 
 pg_global  | postgres |                      |                   |         | 560 kB  | 
(3 rows)

#数据库db2位于表空间myqtbs
db2=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 db2       | myq      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8553 kB | myqtbs     | 
 mydb      | myq      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8761 kB | pg_default | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8561 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8385 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8385 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            | 
(5 rows)

#数据库db2的oid为24623
db2=# select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
 13892 | postgres
 16385 | mydb
     1 | template1
 13891 | template0
 24623 | db2
(5 rows)


#$PGDATA/pg_tblspc下以表空间oid命名
[postgres@pg14-7 myqtbs]$ tree $PGDATA/pg_tblspc
/var/postgres/data/pg_tblspc
└── 24625 -> /var/postgres/myqtbs

#表空间目录以数据库oid为目录组织文件
[postgres@pg14-7 myqtbs]$ tree -L 2 /var/postgres/myqtbs/
/var/postgres/myqtbs/
└── PG_14_202107181
    └── 24623