PHP实战中知识总结 / PgSQL - pgsql数据目录文件结构解析
一、安装时指定数据目录
// 在安装时指定的数据存储目录
[root@izwz91quxhnlkan8kjak5hz postgres]# pwd
/www/server/data/postgres
[root@izwz91quxhnlkan8kjak5hz postgres]# ls
base pg_commit_ts pg_hba.conf pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase pg_wal postgresql.auto.conf postmaster.opts
global pg_dynshmem pg_ident.conf pg_multixact pg_replslot pg_snapshots pg_stat_tmp pg_tblspc PG_VERSION pg_xact postgresql.conf postmaster.pid
二、数据目录结构说明
| 文件/目录 | 类型 | 含义 |
|---|---|---|
| base | 目录 | 存储整体POSTGRESQL 数据的目录，而base 里面就是以每个数据库的OID为名字的目录，目录里面全是这个数据库里面的表及相关文件 |
| pg_commit_ts | 目录 | 事务提交时间戳数据的目录 |
| pg_hba.conf | 文件 | 客户端认证控制文件 |
| pg_logical | 目录 | 用于逻辑复制的状态数据的目录 |
| pg_notify | 目录 | 包含LISTEN/NOTIFY状态数据的目录 |
| pg_serial | 目录 | 已提交的可序列化事务信息的目录 |
| pg_stat | 目录 | 统计信息的存储目录 |
| pg_stat_tmp | 目录 | 临时文件目录 |
| pg_subtrans | 目录 | 存储子事务状态数据的目录 |
| pg_twophase | 目录 | 用于存储预备事务状态文件的目录 |
| pg_wal | 目录 | 保存预写日志的目录 |
| postgresql.auto.conf | 文件 | 自动配置文件，参数文件，只保存alter system命令修改的参数 |
| postgresql.conf | 文件 | 参数文件 |
| postmaster.opts | 文件 | 记录服务器最后一次启动时使用的命令行参数 |
| global | 目录 | 包含集簇范围的表的文件和全局控制信息等 |
| pg_dynshmem | 目录 | 存储被动态共享内存子系统所使用文件的目录 |
| pg_ident.conf | 文件 | 用来配置哪些操作系统用户可以映射为哪个数据库用户 |
| pg_multixact | 目录 | 存储多事务状态数据的子目录（用户共享的行锁） |
| pg_replslot | 目录 | 该目录包含了复制槽的数据 |
| pg_snapshots | 目录 | 储存导出的快照 |
| pg_tblspc | 目录 | 存储表空间的符号链接 |
| PG_VERSION | 文件 | 记录当前数据库的版本号 |
| pg_xact | 目录 | 存储事务提交状态数据的目录 |
| postgresql.conf | 文件 | 参数配置文件 |
| postmaster.pid | 文件 | Postgres主进程文件，记录进程PID、端口号等信息 |
三、PgSQL.auto.conf和postgresql.conf的区别
（1）postgresql.auto.conf的优先级高于postgresql.conf，如果配置同时存在，系统会有限读postgresql.auto.conf的参数配置
（2）使用alter system set命令修改的是postgresql.auto.conf文件的内容，postgresql.conf则是通过文本编辑方式修改
（3）将参数设回 default 时，auto.conf文件的这项配置会被删除，重新用回 conf 文件的设置
四、postmaster.pid内容解析
PostgresSQL的主进程是Postmaster，当我们启动PostgresSQL后，会在PostgreSQL中的数据文件夹下生产一个postmaster.pid的文件
[root@izwz91quxhnlkan8kjak5hz postgres]# cat postmaster.pid
15757 // 主进程的PID
/www/server/data/postgres // 数据存储目录
1615723512 // 创建时间
5432 // 数据库监听端口，在postgresql.conf中对应着 port = 5432
/tmp // unix socket的监听目录，在postgresql.conf中对应unix_socket_directory = '/tmp'
localhost // 数据库监听地址，对应postgresql.conf的listen_addresses = 'localhost'
2377784 229377 // 共享内存的地址(shared memory segments中的key和shmid)，可以通过 ipcs 命令查看
read // 主进程状态
五、base目录解析
每个数据库，在$PGDATA/base里都有一个子目录对应，子目录的名字为该数据库在pg_database里的 OID。
每一张表的数据（大部分）又是放在$PGDATA/base/{oid}/{relfilenode}这个文件里面，relfilenode一般情况下和oid一致，但有些情况下也会变化，如TRUNCATE、REINDEX、CLUSTER以及某些形式的ALTER TABLE。
// 查看各个数据库的oid
postgres=# select datname,oid from pg_database;
datname | oid
-----------+-------
postgres | 13580
test | 16411
template1 | 1
template0 | 13579
(4 rows)
// 查询relowner
// 系统表pg_authid包含有关数据库认证标识符(角色)的信息：http://www.postgres.cn/docs/13/catalog-pg-authid.html
postgres=# select oid,rolname from pg_authid where rolname='postgres';
oid | rolname
-----+----------
10 | postgres
(1 row)
// 查询pg_class里的relfilenode
postgres=# select relname,relowner,relfilenode from pg_class;
relname | relowner | relfilenode
-----------------------------------------------+----------+-------------
test_pkey | 10 | 16387
test | 10 | 16384
pg_statistic | 10 | 2619
pg_type | 10 | 0
alerts_id_seq | 10 | 16389
pg_toast_16391 | 10 | 16397
pg_toast_16391_index | 10 | 16399
alerts | 10 | 16391
index_attack_alarm_attack_result | 10 | 16400
index_attack_alarm_attack_type | 10 | 16401
...
在$PGDATA/base/{oid}目录中通常会包含以下文件：
（1）纯数字类型的文件：如16391，这是数据库对应表的数据文件或索引文件，这些文件是需要到pg_class里根据OID查到对应的relfilenode来与文件名匹配的
// 在表或者索引超过1GB之后，它就被划分成1GB大小的段。 第一个段的文件名和文件节点相同，随后的段被命名为`filenode.1`、`filenode.2`等等。
// 这样就避免了在某些有文件大小限制的平台上的问题
// 查看表文件存储位置
postgres=# select pg_relation_filepath('alerts');
pg_relation_filepath
----------------------
base/13580/16391
(1 row)
[root@izwz91quxhnlkan8kjak5hz base]# find -name '*16391*'
./13580/16391.1
./13580/16391
./13580/16391.2
./13580/16391_fsm
./13580/16391_vm
./13580/16391.3
（2）_fsm后缀的文件：如16385_fsm，其对应的空闲空间映射文件
（3）_vm后缀的文件：如16385_vm，其对应的可见性映射文件
（4）pg_filenode.map：pg_class里relfilenode为0的系统表，OID与文件的硬编码映射
（5）pg_internal.init：系统表的cache文件，用于加快读取。默认不存在，查询系统表后自动产生
（6）PG_VERSION：当前数据库数据格式对应的版本号
六、FSM文件解析
以_fsm后缀的文件就是Free Space Mapping文件，即空闲空间映射文件
在PostgreSQL的mvcc机制中，当数据块中进行insert、update、delete这些操作时，都会产生一个旧版本的数据。当pg执行VACUUM操作时，便会清理掉这些旧版本数据，那这样就会出现数据块中存在很多空闲的空间，在新插入数据时会继续分配一个新的数据块，如果不对空闲的空间进行有效利用的话，那数据文件的膨胀速度就会非常快，因此PostgreSQL便通过FSM来快速找到能插入数据的空闲数据块
FSM不存储数据块的精确空间，而是将一个数据块的大小分成256份，例如默认的一个数据块是8k，那么序号1表示0～31k大小，序号2表示32～63k大小，依此类推。。。为什么这样设置呢，因为256刚好等于2^8，这样我们在每个数据块中使用一个字节就能表示这个数据块剩余的空间了。
FSM文件不是在创建表的时候就会生成，而是在插入数据或者进行vacuum操作的时候才会生成。
pgsql提供了一个pg_freespacemap扩展，它提供了一个称为pg_freespace的函数，用于查询FSM。
postgres=# create extension pg_freespacemap;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
-----------------+---------+------------+-------------------------------------------------------------------
first_last_agg | 0.1.4 | public | first() and last() aggregate functions
pg_freespacemap | 1.2 | public | examine the free space map (FSM)
pg_trgm | 1.5 | public | text similarity measurement and index searching based on trigrams
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(4 rows)
// 查看pg_freespacemap扩展的可用函数
postgres=# \dxS+ pg_freespacemap;
Objects in extension "pg_freespacemap"
Object description
----------------------------------------
function pg_freespace(regclass)
function pg_freespace(regclass,bigint)
(2 rows)
// 查看表的FSM
postgres=# SELECT * FROM pg_freespace('alerts')
postgres-# \g
blkno | avail
-------+-------
0 | 384
1 | 64
2 | 64
3 | 64
...
七、VM文件解析
以_vm为后缀的文件为visual map文件，可见性映射表
在进行update, delete 操作行后，这一行tuple 并不会马上被清理掉，而是要通过vacuum ，autovacuum 等操作将这些 tuple 来清理， PG8.4.1版本之后，为每个数据文件加了一个后缀为“_vm”的文件，主要作用是显示占用的tuple，扫描的时候会跳过这些tuple，这是为了能加快VACUUM清理的速度和降低对系统IO性能的影响。
VM文件中为每个数据块设置了一个标志位，用来标记数据块中是否存在需要清理的行。有了这个文件后，通过VACUUM命令扫描这个文件时，如果存在vm文件，则表示该数据块没有需要清理的行， 那么会跳过对这个数据块的扫描，从而加快VACUUM清理的速度。
VACUUM清理数据有两种方式。一种称为Lazy VACUUM,另一种称为Full VACUUM。VM文件健在Lazy VACUUM中使用到，Full VACUUM操作则需要对整个数据文件进行扫描。
pgsql提供了一个插件pg_visibility可供我们查询VM，该插件提供了一种方式来检查一个表的可见性映射（VM）以及页级别的可见性信息。它还提供了函数来检查可见性映射的完整性以及强制重建可见性映射
postgres=# create extension pg_visibility;
CREATE EXTENSION
// 查看已安装的扩展
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
-----------------+---------+------------+-------------------------------------------------------------------
first_last_agg | 0.1.4 | public | first() and last() aggregate functions
pg_freespacemap | 1.2 | public | examine the free space map (FSM)
pg_trgm | 1.5 | public | text similarity measurement and index searching based on trigrams
pg_visibility | 1.2 | public | examine the visibility map (VM) and page-level visibility info
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(5 rows)
// 查看扩展可用函数
postgres=# \dxS+ pg_visibility
Objects in extension "pg_visibility"
Object description
-----------------------------------------------
function pg_check_frozen(regclass)
function pg_check_visible(regclass)
function pg_truncate_visibility_map(regclass)
function pg_visibility_map(regclass)
function pg_visibility_map(regclass,bigint)
function pg_visibility_map_summary(regclass)
function pg_visibility(regclass)
function pg_visibility(regclass,bigint)
(8 rows)
// 查看表可见的block
postgres=# SELECT * FROM pg_visibility('test');
blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
0 | f | f | f
1 | f | f | f
....
八、vacuum命令解析
vacuum命令用于收回由死亡元组占用的存储空间，即无效的空间。在通常的PostgreSQL操作中，被删除或者被更 新废弃的元组并没有在物理上从它们的表中移除，它们将一直存在直到一次vacuum被执 行。因此有必要周期性地做VACUUM，特别是在频繁被更新的表上
vacuum命令不能在事务中执行，vacuum 不加参数是对所有表进行操作
// 命令格式：
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]
其中option可以是下列之一：
FULL [ boolean ]
FREEZE [ boolean ]
VERBOSE [ boolean ]
ANALYZE [ boolean ]
DISABLE_PAGE_SKIPPING [ boolean ]
SKIP_LOCKED [ boolean ]
INDEX_CLEANUP [ boolean ]
TRUNCATE [ boolean ]
而table_and_columns是：
table_name [ ( column_name [, ...] ) ]
九、vacuum和vacuum full命令的区别
（1）vacuum不会锁表，只是单纯的回收空间以被重用，被回收的空间一般情况不会被返还给操作系统，仅仅被保留在同一个表中以备重用
（2）vacuum full会锁表，会将表的整个内容重写到一个新的磁盘文件中，并且不包含额外的空间，这使得没有被使用的空间被还给操作系统
（3）vacuum full甚至会改变relfilenode（relfilenode 是用来命名数据文件的，会随数据物理位置变化而变化），但是vacuum full不会生成fsm和vm文件
// 查看test表的数据文件节点filenode，
postgres=# select relname,relowner,relfilenode from pg_class where relname='test';
relname | relowner | relfilenode
---------+----------+-------------
test | 10 | 16384
(1 row)
// 查看表信息
postgres=# \dt+ test;
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------+-------+----------+-------------+---------+-------------
public | test | table | postgres | permanent | 5184 kB |
(1 row)
// 执行完vaccum 命令后，发现表空间并无变化
postgres=# vacuum test;
VACUUM
postgres=# \dt+ test;
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------+-------+----------+-------------+---------+-------------
public | test | table | postgres | permanent | 5184 kB |
(1 row)
// 执行vaccum full命令后，发现表空间被回收了，而且表的filenode也发生了变化
postgres=# vacuum full test;
VACUUM
postgres=# \dt+ test;
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------+-------+----------+-------------+---------+-------------
public | test | table | postgres | permanent | 5096 kB |
(1 row)
postgres=# select relname,relowner,relfilenode from pg_class where relname='test';
relname | relowner | relfilenode
---------+----------+-------------
test | 10 | 16550
(1 row)