postgresql|数据库|pg数据库的文件系统详解---最全面的解析

2023-05-16

前言：

postgresql是一个非常成熟的开源的功能强大的关系型数据库，总体来说，该数据库安装简单，使用复杂，复杂度在多个维度都会有所体现，比如，SQL语法的复杂（语法和MySQL，oracle等基本是一致的，这就比较友好了，虽然什么左联查，右联查，函数，触发器，约束等等内容非常多），文件系统的复杂（配置文件，标识文件，数据库文件等等），维护的复杂（备份和恢复工具比较多，需要根据自己的需求来仔细甄别，挑选，使用），优化的复杂（通常，数据库能跑起来是一件事，能高效率的，稳定的运行是另一回事）

OK，那么，postgresql数据库内的文件系统到底是一个什么系统？数据库是如何运行的？wal日志文件存放在哪里？wal日志文件有什么用？postgresql的主配置文件到底能干什么？标识文件如何使用？数据库文件是存放在哪里的？这些文件存在的意义是什么？

一，

postgresql的文件系统概述

[postgres@k8s-node2 pgsql]$ ll
total 1240
-rw-r--r--  1 postgres postgres    522 Nov 10  2020 aclocal.m4
drwx------  2 postgres postgres   4096 Mar  9 00:54 bin
drwxrwxrwx  2 postgres postgres   4096 Nov 10  2020 config
-rw-------  1 postgres postgres 434222 Mar  9 00:42 config.log
-rwx------  1 postgres postgres  40061 Mar  9 00:42 config.status
-rwxr-xr-x  1 postgres postgres 575329 Nov 10  2020 configure
-rw-r--r--  1 postgres postgres  84108 Nov 10  2020 configure.in
drwxrwxrwx 56 postgres postgres   4096 Nov 10  2020 contrib
-rw-r--r--  1 postgres postgres   1192 Nov 10  2020 COPYRIGHT
drwx------ 19 postgres postgres   4096 Mar 25 18:17 data
drwxrwxrwx  3 postgres postgres     87 Nov 10  2020 doc
-rw-------  1 postgres postgres   3998 Mar  9 00:42 GNUmakefile
-rw-r--r--  1 postgres postgres   3998 Nov 10  2020 GNUmakefile.in
-rw-r--r--  1 postgres postgres    284 Nov 10  2020 HISTORY
drwx------  6 postgres postgres   4096 Mar  9 00:54 include
-rw-r--r--  1 postgres postgres  61479 Nov 10  2020 INSTALL
drwx------  4 postgres postgres   4096 Mar  9 00:54 lib
-rw-r--r--  1 postgres postgres   1665 Nov 10  2020 Makefile
-rw-r--r--  1 postgres postgres   1212 Nov 10  2020 README
drwx------  6 postgres postgres   4096 Mar  9 00:54 share
drwxrwxrwx 16 postgres postgres   4096 Mar  9 00:42 src

OK，以上是二进制编译安装的一个postgresql数据库，该数据库已经初始化完成了。

猛一看，文件并不多，大概也就1w多个文件（夹）而已，深入研究的话，可能会大吃一惊的。

1，

contrib文件夹

此文件夹是插件目录，里面有很多插件，比如passwordcheck 密码检查工具（安全增强插件），不过基本都是需要编译的，由开源组织提供的小工具集，也可以认为是一个扩展功能集合，可以这么理解的哦

[root@EULER1 contrib]# ls
adminpack     bloom       contrib-global.mk  dict_xsyn      hstore           intarray        lo              oid2name        pgcrypto         pg_standby          pg_visibility  sepgsql        tablefunc        tsm_system_time  xml2
amcheck       btree_gin   cube               earthdistance  hstore_plperl    isn             ltree           pageinspect     pg_freespacemap  pg_stat_statements  postgres_fdw   spi            tcn              unaccent
auth_delay    btree_gist  dblink             file_fdw       hstore_plpython  jsonb_plperl    ltree_plpython  passwordcheck   pg_prewarm       pgstattuple         README         sslinfo        test_decoding    uuid-ossp
auto_explain  citext      dict_int           fuzzystrmatch  intagg           jsonb_plpython  Makefile        pg_buffercache  pgrowlocks       pg_trgm             seg            start-scripts  tsm_system_rows  vacuumlo

2，

lib文件夹

数据库运行时的类库，也可以认为是开发库

[root@EULER1 lib]# ls
ascii_and_mic.so     euc_jp_and_sjis.so     libecpg.a               libecpg.so           libpgfeutils.a     libpgtypes.so.3     libpq.so.5.12        pkgconfig             utf8_and_euc2004.so  utf8_and_gb18030.so    utf8_and_sjis2004.so
cyrillic_and_mic.so  euc_kr_and_mic.so      libecpg_compat.a        libecpg.so.6         libpgport.a        libpgtypes.so.3.12  libpqwalreceiver.so  plpgsql.so            utf8_and_euc_cn.so   utf8_and_gbk.so        utf8_and_sjis.so
dict_snowball.so     euc_tw_and_big5.so     libecpg_compat.so       libecpg.so.6.12      libpgport_shlib.a  libpq.a             passwordcheck.so     utf8_and_ascii.so     utf8_and_euc_jp.so   utf8_and_iso8859_1.so  utf8_and_uhc.so
euc2004_sjis2004.so  latin2_and_win1250.so  libecpg_compat.so.3     libpgcommon.a        libpgtypes.a       libpq.so            pgoutput.so          utf8_and_big5.so      utf8_and_euc_kr.so   utf8_and_iso8859.so    utf8_and_win.so
euc_cn_and_mic.so    latin_and_mic.so       libecpg_compat.so.3.12  libpgcommon_shlib.a  libpgtypes.so      libpq.so.5          pgxs                 utf8_and_cyrillic.so  utf8_and_euc_tw.so   utf8_and_johab.so

3，

share文件夹

直译是共享文件夹，该文件夹内存放的是初始化数据库的文件，里面包括一些初始化时执行的SQL语句，PostgreSQL的文档、 man、示例文件以及一些扩展，此文件夹在初始化时非常重要

[root@EULER1 share]# ll
total 1108
-rw-r--r--.  1 pg1 pg1  33114 Mar  9 00:54 errcodes.txt
drwx------.  2 pg1 pg1   4096 Mar 25 19:22 extension
-rw-r--r--.  1 pg1 pg1 112339 Mar  9 00:54 information_schema.sql
-rw-r--r--.  1 pg1 pg1   4674 Mar  9 00:54 pg_hba.conf.sample
-rw-r--r--.  1 pg1 pg1   1636 Mar  9 00:54 pg_ident.conf.sample
-rw-r--r--.  1 pg1 pg1    604 Mar  9 00:54 pg_service.conf.sample
-rw-r--r--.  1 pg1 pg1 649166 Mar  9 00:54 postgres.bki
-rw-r--r--.  1 pg1 pg1 134668 Mar  9 00:54 postgres.description
-rw-r--r--.  1 pg1 pg1  26578 Mar  9 00:54 postgresql.conf.sample
-rw-r--r--.  1 pg1 pg1     49 Mar  9 00:54 postgres.shdescription
-rw-r--r--.  1 pg1 pg1    278 Mar  9 00:54 psqlrc.sample
-rw-r--r--.  1 pg1 pg1  33590 Mar  9 00:54 snowball_create.sql
-rw-r--r--.  1 pg1 pg1  35441 Mar  9 00:54 sql_features.txt
-rw-r--r--.  1 pg1 pg1  50232 Mar  9 00:54 system_views.sql
drwx------. 18 pg1 pg1   4096 Mar  9 00:54 timezone
drwx------.  2 pg1 pg1   4096 Mar  9 00:54 timezonesets
drwx------.  2 pg1 pg1   4096 Mar  9 00:54 tsearch_data

比较有价值的文件是：

errcodes.txt （错误代码表，如果开发中有错误，得看这个文件）
postgres.bki 后端接口文件---模板数据库template1是通过运行在bootstrap模式的postgres程序读取postgres.bki文件创建的。BKI文件是一些用特殊语言写的脚本，这些脚本使PostgreSQL后端能够理解，且以特殊的bootstrap模式来执行之，这种模式允许在不存在系统表的零初始条件下执行数据库函数，而普通的SQL命令要求系统表必须存在。因此BKI文件仅用于初始化数据集簇。
system_views.sql 系统视图SQL脚本
以及配置文件模板（包括主配置文件的模板和连接权限hba模板）

4，

data目录

此目录通常我们也可以称之为数据文件目录。由于Linux的设计理念是万物皆为文件，因此，逻辑上的所有概念都可以物化为文件

此目录只有初始化数据库后才会生成出现，该目录下存放的是几个主要配置文件和数据库的物理文件

[pg1@EULER1 data]$ ll
total 140
-rw------- 1 pg1 pg1   224 Mar 20 02:31 backup_label.old
drwx------ 5 pg1 pg1  4096 Mar 19 20:16 base
-rw------- 1 pg1 pg1    44 Mar 25 18:07 current_logfiles
drwx------ 2 pg1 pg1  4096 Mar 25 19:28 global
drwx------ 2 pg1 pg1  4096 Mar 25 18:07 log
drwx------ 2 pg1 pg1  4096 Mar 18 07:05 pg_commit_ts
drwx------ 2 pg1 pg1  4096 Mar 18 07:05 pg_dynshmem
-rw------- 1 pg1 pg1  4902 Mar 18 07:11 pg_hba.conf
-rw------- 1 pg1 pg1  1636 Mar 18 07:05 pg_ident.conf
drwx------ 4 pg1 pg1  4096 Mar 25 19:32 pg_logical
drwx------ 4 pg1 pg1  4096 Mar 18 07:05 pg_multixact
drwx------ 2 pg1 pg1  4096 Mar 25 18:07 pg_notify
drwx------ 2 pg1 pg1  4096 Mar 20 02:31 pg_replslot
drwx------ 2 pg1 pg1  4096 Mar 18 07:05 pg_serial
drwx------ 2 pg1 pg1  4096 Mar 18 07:05 pg_snapshots
drwx------ 2 pg1 pg1  4096 Mar 20 03:00 pg_stat
drwx------ 2 pg1 pg1  4096 Mar 25 20:15 pg_stat_tmp
drwx------ 2 pg1 pg1  4096 Mar 20 02:40 pg_subtrans
drwx------ 2 pg1 pg1  4096 Mar 20 02:40 pg_tblspc
drwx------ 2 pg1 pg1  4096 Mar 18 07:05 pg_twophase
-rw------- 1 pg1 pg1     3 Mar 18 07:05 PG_VERSION
drwx------ 3 pg1 pg1  4096 Mar 20 02:40 pg_wal
drwx------ 2 pg1 pg1  4096 Mar 18 07:05 pg_xact
-rw------- 1 pg1 pg1   110 Mar 19 02:52 postgresql.auto.conf
-rw------- 1 pg1 pg1 26695 Mar 20 03:00 postgresql.conf
-rw------- 1 pg1 pg1    59 Mar 25 18:07 postmaster.opts
-rw------- 1 pg1 pg1    80 Mar 25 18:07 postmaster.pid
-rw------- 1 pg1 pg1    30 Mar 20 02:31 tablespace_map.old

A，

base目录

此目录是基本表空间和在其表空间下的数据库文件

一级目录名是用户数据库对象的OID（例如，下面的13590），一级目录内的二级子文件都是隶属于该数据库对象的关系，包括表、索引、视图等。

二级子文件分为三大类，第一类是以关系OID命名的主数据文件，第二类是文件名以_fsm结尾的空闲空间映射文件，第三类是文件名以_vm结尾的可见性映射文件。

一般情况下，postgresql数据库只需要两个基础表空间，如果data目录所在分区磁盘空间不足的时候，我们可能会自定义表空间在其它分区，从而扩展数据库。

postgres-# \db+
                                          List of tablespaces
    Name    |  Owner   |        Location         | Access privileges | Options |  Size   | Description 
------------+----------+-------------------------+-------------------+---------+---------+-------------
 mytbs      | postgres | /opt/custome-tablespace |                   |         | 7833 kB | 
 pg_default | pg1      |                         |                   |         | 23 MB   | 
 pg_global  | pg1      |                         |                   |         | 623 kB  |

例如，上面的表空间详情表示，pg_default 这个表空间对应的就是base这个目录了，那么，如何证明呢？

postgres-# \db+ pg_default  
                                List of tablespaces
    Name    | Owner | Location | Access privileges | Options | Size  | Description 
------------+-------+----------+-------------------+---------+-------+-------------
 pg_default | pg1   |          |                   |         | 23 MB | 
(1 row)

[root@EULER1 base]# du -sh ./
24M	./

OK，看看base目录下有一些什么：

[root@EULER1 base]# pwd
/usr/local/pgsql/data/base
[root@EULER1 base]# ll
total 20
drwx------ 2 pg1 pg1  4096 Mar 18 07:05 1
drwx------ 2 pg1 pg1  4096 Mar 18 07:05 13590
drwx------ 2 pg1 pg1 12288 Mar 25 19:28 13591

OK，13590和13591是什么呢？

这些数字叫oid，如何查询它们对应的是哪个数据库呢？

postgres=# select oid, datname from pg_database;
  oid  |  datname  
-------+-----------
 13591 | postgres
     1 | template1
 13590 | template0
 32775 | test
(4 rows)

查询某个表在哪个具体的目录下（例如，查询ssss这个表在哪个目录下）：

postgres=# select pg_relation_filepath('ssss');
 pg_relation_filepath 
----------------------
 base/13591/40976
(1 row)

如果是空表，那么40976这个文件的状态是empty，如果此表有数据，那么将是data（一开始是空的，后面我添加了数据，添加数据没有演示）：

[root@EULER1 13591]# pwd
/usr/local/pgsql/data/base/13591
[root@EULER1 13591]# file 40976 
40976: empty
[root@EULER1 13591]# file 40976 
40976: data

数据库和表空间的对应关系查询：

postgres=# \l+
                                                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-------------------+---------+------------+--------------------------------------------
 postgres  | pg1      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 7993 kB | pg_default | default administrative connection database
 template0 | pg1      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pg1           +| 7809 kB | pg_default | unmodifiable empty database
           |          |          |             |             | pg1=CTc/pg1       |         |            | 
 template1 | pg1      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pg1           +| 7809 kB | pg_default | default template for new databases
           |          |          |             |             | pg1=CTc/pg1       |         |            | 
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 7961 kB | mytbs      | 
(4 rows)

OK，13591和 1 这两个数据库是模板数据库，比较特殊的数据库，上面有介绍，新建数据库是使用的template1这个模板，完全的空，且不可修改的数据库是template0模板（因为不可连接），可以这样使用模板数据库建立新数据库：

root@EULER1 data]# createdb -T template1 -U postgres test2
[root@EULER1 data]# createdb -T template0 -U postgres test3

template1和template0的联系和区别：

PostgreSQL默认初始化数据库后，模板数据库template0和template1包含的内容是一致的。在PG中创建数据库时，默认是从template1模板数据库中克隆出来的，创建出来的数据库是继承template1的，而template0不会继承，因为template是不可连接的。

简单来说，template1是会被污染的，比如，连接到template1数据库，然后在里面建立了一张名字为test的表，并插入一些垃圾数据，那么，在创建新的数据库的时候，新数据库里也将会有同样的test表，表内有同样的垃圾数据
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \c template0
FATAL:  database "template0" is not currently accepting connections
Previous connection kept
使用 template1 模板库建库时不可指定新的 encoding 和 locale，而 template0 可以。这里的“新的”指的是非UTF8

PostgreSQL为了满足能够给用户一个干净（也就是最原始）的数据库需求，当需要的时候，可以指定从template0模板数据库中去新建数据库。

在$PGDATA/base/{dboid}中通常会包含三种文件（也就是前面说的二级子目录）：例如16385、16385_fsm、16385_vm，分别是该数据库对应表的数据或索引文件、其对应的空闲空间映射文件、其对应的可见性映射文件。

B，global文件夹

global目录存储pg_control及数据库集群维度的数据库及其关系，非客户维度的数据，例如pg_database、pg_class等。目录内的文件结构和base是一致的。

此文件夹里的内容是全局数据，其中有一个非常重要的文件pg_control，该文件的格式是data

该文件的内容主要分为是三部分,初始化静态信息、WAL及检查点的动态信息、一些配置信息（不能通过文本方式查看，需要pg_controldata这个工具查看该文件内容）

[root@EULER1 global]# pwd
/usr/local/pgsql/data/global
[root@EULER1 global]# file pg_control 
pg_control: data

读取此文件由pg_controldata这个程序实现，此命令基本没有参数，直接运行即可：

[root@EULER1 global]# pg_controldata 
pg_control version number:            1201
Catalog version number:               201909212
Database system identifier:           7211655189372047015
Database cluster state:               in production
pg_control last modified:             Sat 25 Mar 2023 11:01:51 PM CST
Latest checkpoint location:           0/C08CD68
Latest checkpoint's REDO location:    0/C08CD30
Latest checkpoint's REDO WAL file:    00000004000000000000000C
Latest checkpoint's TimeLineID:       4
Latest checkpoint's PrevTimeLineID:   4
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:663
Latest checkpoint's NextOID:          57347
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        479
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  663
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Sat 25 Mar 2023 11:01:50 PM CST
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              1000
max_worker_processes setting:         8
max_wal_senders setting:              10
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            be64e48d6831a39e8504bc367bfd40768edba136fbdcaa058b8c6acac954b81a

输出的内容非常多，但十分重要，比如：

Catalog version number:               201909212  #版本发行时间
Database cluster state:               in production #数据库状态，此时是单实例或者主从里的主
pg_control last modified:             Sat 25 Mar 2023 11:01:51 PM CST#数据库启动时间，启动的时候会读取pg_control 文件，因此，是修改状态
Latest checkpoint location:           0/C08CD68  #wal文件名称
Time of latest checkpoint:            Sat 25 Mar 2023 11:01:50 PM CST#数据库启动时间
wal_level setting:                    replica #wal日志文件的等级，
Latest checkpoint's REDO location:    0/C08CD30 #检查点现在正在使用的wal文件名称
Latest checkpoint's REDO WAL file:    00000004000000000000000C #检查点wal日志文件名称，此文件是正在写入wal日志状态

C，

pg_xact

pg_xact是事务提交日志（Commit Log）的存储目录，事务提交日志默认256KB，文件名形如NNNN，系统初始化后从0000开始递增至FFFF。PG 10及之后的高版本改目录名为pg_xact，10之前目录名称是pg_clog。

[root@EULER1 pg_xact]# pwd
/usr/local/pgsql/data/pg_xact
[root@EULER1 pg_xact]# file 0000 
0000: International EBCDIC text, with NEL line terminators
[root@EULER1 pg_xact]# ls -al
total 16
drwx------   2 pg1 pg1 4096 Mar 18 07:05 .
drwx------. 20 pg1 pg1 4096 Mar 26 00:00 ..
-rw-------   1 pg1 pg1 8192 Mar 25 23:01 0000

D，

pg_wal文件夹

该文件夹内存放的是wal归档日志文件，一般情况下是不可以删除的，备份操作主要依赖此文件夹内的wal日志文件

[root@EULER1 pg_wal]# ll
total 32780
-rw------- 1 pg1 pg1       74 Mar 20 02:40 00000003.history
-rw------- 1 pg1 pg1 16777216 Mar 26 01:33 00000004000000000000000C
-rw------- 1 pg1 pg1 16777216 Mar 20 02:40 00000004000000000000000D
-rw------- 1 pg1 pg1      118 Mar 20 02:40 00000004.history
drwx------ 2 pg1 pg1     4096 Mar 20 02:40 archive_status

以上基本都是重要的文件夹，下面介绍一下postgresql数据库里的重要文件：

A，

postgresql 主配置文件

该配置文件内容涉及的方面很多，主要是服务状态，日志设置，备份设置，恢复设置，主从设置，查询设置等等方方面面，绝大多数配置参数都被注释掉了，它们默认被内置到了数据库中，仅剩下几个参数没有被注释掉，被系统重写了（数据库版本不同，重写参数可能不同），如 pg 12.4 中被重写的了如下几个参数。测试环境使用可以采用默认参数，但在生产中使用就需要对默认参数进行一些优化配置了。

OK，具体的如何设置这些参数，如何优化内容就太多了，在此就不多做介绍了。

B，

pg_hba.conf 连接配置文件

该文件是安全方面的配置文件，主要定义哪些用户以何种方式连接数据库，例如，

host    all             all             0.0.0.0/0                 md5

表示任意客户端均可远程连接此数据库，只是远程连接的时候需要密码校验，也就是需要输入相关用户的密码，如果该用户设置了密码的话。

只是需要注意，此配置文件的更改生效有两种方式，第一是重启数据库服务，第二是利用postgresql的内置函数：

例如，pg_reload_conf这个pg的内置函数可以热更新配置

test=# select  pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

C，

postgresql.auto.conf

该文件是存放数据库系统级别的配置，此文件非常的吊，级别是最高的，也就是说相同的配置在postgresql.conf和postgresql.auto.conf 两个文件里都写的有，那么，最终是auto这个配置文件里的配置项生效

例如：

配置数据库listen也就是监听范围

[pg1@EULER1 ~]$ psql -d postgres -c "ALTER SYSTEM SET listen_addresses TO '*';"
ALTER SYSTEM

[root@EULER1 data]# cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_wal_size = '2500'
listen_addresses = '*'

D，

recovery.conf文件

该文件在低版本出现，大概是pg10版本左右，是需要自己编写的，在pg12版本结合到postgresql.conf 文件内了。

该文件主要是用于数据库恢复阶段。

recovery.conf中的参数可以通过alter system 的方式进行配置了

由于是过期的文件，因此，本文不做过多介绍（12已经是主流版本了）

E，

standby.singal

此文件是标识文件，表示服务器状态的无内容文件，通常是表示此时的数据库进入了紧急状态，一般是在主服务器恢复后出现此文件。

未完待续

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)