Postgresql物理目录结构（postgresql读法）-技术教程-四时宝库

1.1 数据目录布局

文件	描述
PG_VERSION	记录pg版本信息
pg_hba.conf	控制客户端认证
pg_ident.conf	控制用户名映射
postgresql.conf	配置参数文件
postgresql.auto.conf	存储ALTER SYSTEM命令设置的参数(versions 9.4+)
postmaster.opts	记录最后启动时的命令行参数

子目录	描述
base/	包含预定义和自建的数据库
global/	存放集群范围内的表，例如pg_database和pg_control
pg_commit_ts/	包含事务提交时间戳数据(versions 9.5+)
pg_clog/ (versions 9.6+)	包含事务提交状态的数据。version 10重命名为pg_xact
pg_dynshmem/	包含动态共享内存子系统使用的文件(versions 9.4+)
pg_logical/	包含逻辑解码的状态数据(versions 9.4+)
pg_multixact/	包含多事务状态数据(用于共享行锁)
pg_notify/	包含监听和通知状态数据
pg_repslot/	包含复制槽数据(versions 9.4+)
pg_serial/	串行提交的事务信息(versions 9.1+)
pg_snapshots/	存放export的快照，函数pg_export_snapshot创建的快照存放于此目录(versions 9.2+)
pg_stat/	持久化的统计信息数据
pg_stat_tmp/	临时的统计信息数据
pg_subtrans/	子事务状态
pg_tblspc/	包含自定义表空间的软链接
pg_twophase/	包含prepared事务的状态
pg_wal/ (versions 10+)	存放wal日志，10之前为pg_xlog
pg_xact/ (versions 10+)	事务提交状态信息
pg_xlog/ (versions 9.6+)	存放wal日志

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

四时宝库

程序员的知识宝库

Postgresql物理目录结构（postgresql读法）