pgsql數據目錄文件結構解析 · PHP知識總結

[TOC] ``` // 在安裝時指定的數據存儲目錄 [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、端口號等信息| # **postgresql.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命令的區別：** * vacuum不會鎖表，只是單純的回收空間以被重用，被回收的空間一般情況不會被返還給操作系統，僅僅被保留在同一個表中以備重用 * vacuum full會鎖表，會將表的整個內容重寫到一個新的磁盤文件中，并且不包含額外的空間，這使得沒有被使用的空間被還給操作系統 * 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) ```