印刻数据库

外观

PostgreSQL 物理架构 ​

PostgreSQL的物理架构是指数据库在磁盘上的存储结构和文件组织方式。理解PostgreSQL的物理架构对于DBA进行存储管理、性能优化和故障恢复至关重要。

物理架构概述 ​

PostgreSQL的物理存储结构由多个层次组成，从底层到上层依次为：

  1. 操作系统层：文件系统、磁盘I/O、内存管理
  2. PostgreSQL存储层：数据文件、WAL文件、配置文件等
  3. 数据库对象层：数据库、表、索引、视图等

PostgreSQL的物理存储采用目录树结构，所有数据和配置文件都存储在一个集中的目录中，称为数据目录（Data Directory）。

数据目录结构 ​

数据目录是PostgreSQL存储所有数据和配置文件的根目录，由initdb命令创建。数据目录的结构如下：

data_directory/
├── PG_VERSION          # PostgreSQL版本号
├── base/               # 数据库数据文件目录
├── global/             # 全局系统表目录
├── pg_commit_ts/       # 事务提交时间戳目录
├── pg_dynshmem/        # 动态共享内存目录
├── pg_hba.conf         # 主机认证配置文件
├── pg_ident.conf       # 身份映射配置文件
├── pg_logical/         # 逻辑复制目录
├── pg_multixact/       # 多事务ID目录
├── pg_notify/          # LISTEN/NOTIFY消息目录
├── pg_replslot/        # 复制槽目录
├── pg_serial/          # 序列生成器状态目录
├── pg_snapshots/       # 快照目录
├── pg_stat/            # 统计信息临时目录
├── pg_stat_tmp/        # 临时统计信息目录
├── pg_subtrans/        # 子事务状态目录
├── pg_tblspc/          # 表空间链接目录
├── pg_twophase/        # 两阶段事务目录
├── PG_VERSION          # PostgreSQL版本号文件
├── pg_wal/             # WAL文件目录（旧版本为pg_xlog）
├── pg_xact/            # 事务ID目录
├── postgresql.auto.conf # 自动生成的配置文件
├── postgresql.conf     # 主配置文件
└── postmaster.pid      # 主进程PID文件

主要目录和文件说明 ​

PG_VERSION ​

  • 包含PostgreSQL的主版本号（如"14"、"15"）
  • 用于验证数据目录与PostgreSQL二进制文件的兼容性

base/ 目录 ​

  • 存储各个数据库的数据文件
  • 每个数据库对应一个子目录，目录名是数据库的OID
  • 系统数据库postgres、template0、template1也存储在此目录

global/ 目录 ​

  • 存储全局系统表，如pg_database、pg_authid等
  • 这些表不属于任何特定数据库，对所有数据库可见

pg_wal/ 目录 ​

  • 存储Write-Ahead Log（WAL）文件
  • 用于数据恢复、复制和PITR（时间点恢复）
  • WAL文件默认大小为16MB（可配置）
  • WAL文件按顺序编号，形成WAL序列

pg_tblspc/ 目录 ​

  • 存储表空间的符号链接
  • 每个符号链接指向实际的表空间目录
  • 用于支持将表存储在不同的磁盘或文件系统

postgresql.conf ​

  • PostgreSQL的主配置文件
  • 包含数据库服务器的各种配置参数
  • 支持动态加载部分参数（无需重启）

pg_hba.conf ​

  • 主机基础认证配置文件
  • 控制哪些主机可以连接到数据库
  • 定义认证方法和访问权限

数据库与表的物理存储 ​

数据库的物理存储 ​

  • 每个数据库在base/目录下有一个对应的子目录，目录名是数据库的OID
  • 数据库的OID可以通过查询pg_database系统表获取：
    sql
    SELECT oid, datname FROM pg_database;

表的物理存储 ​

  • 每个表的数据存储在对应数据库目录下的一个或多个文件中
  • 文件名格式为relfilenode，其中relfilenode是表的物理文件标识符
  • 可以通过查询pg_class系统表获取表的relfilenode：
    sql
    SELECT relname, relfilenode FROM pg_class WHERE relname = 'table_name';

大表的存储 ​

  • 当表的大小超过1GB时，PostgreSQL会创建多个文件
  • 文件名格式为relfilenode.1、relfilenode.2等
  • 每个文件的最大大小由编译时参数BLCKSZ和RELSEG_SIZE决定
  • 默认情况下，每个文件包含131072个数据页（8KB/页 × 131072 = 1GB）

索引的物理存储 ​

  • 索引的物理存储方式与表类似
  • 每个索引对应一个或多个文件，存储在对应数据库目录下
  • 索引的relfilenode也存储在pg_class系统表中

数据页结构 ​

数据页是PostgreSQL存储数据的基本单位，默认大小为8KB（可在编译时调整）。数据页的结构如下：

+----------------+---------------------------------+----------------+------------+
| Page Header    | Item Pointer Array               | Free Space     | Item Data  |
| (24 bytes)     | (variable size)                  | (variable)     | (variable) |
+----------------+---------------------------------+----------------+------------+
|        Page Footer (8 bytes)                                        |
+---------------------------------------------------------------------+

数据页各部分说明 ​

Page Header（页头） ​

  • 固定大小24字节
  • 包含页的元数据：
    • pd_lsn：最后修改该页的WAL记录位置
    • pd_checksum：页校验和（PostgreSQL 9.3+）
    • pd_flags：页标志（如脏页标志）
    • pd_lower：指向空闲空间开始的偏移量
    • pd_upper：指向空闲空间结束的偏移量
    • pd_special：指向特殊空间开始的偏移量（用于索引）
    • pd_pagesize_version：页大小和版本信息

Item Pointer Array（行指针数组） ​

  • 可变大小，每个行指针占用4字节
  • 存储指向行数据的偏移量
  • 行指针按逻辑顺序排列，但对应的行数据可能无序
  • 行指针数组从页头之后开始，向上增长

Free Space（空闲空间） ​

  • 页中未使用的空间
  • 用于存储新插入的行数据
  • 空闲空间位于行指针数组和行数据之间

Item Data（行数据） ​

  • 存储实际的行记录
  • 行数据从页的末尾开始，向下增长
  • 每行数据包含元数据和用户数据

Page Footer（页尾） ​

  • 固定大小8字节
  • 包含：
    • 页校验和（与页头的校验和相同）
    • 页的LSN（与页头的LSN相同）

行数据结构 ​

行数据存储在数据页的Item Data区域，每个行数据包含元数据和用户数据两部分：

行元数据 ​

  • xmin：创建该行的事务ID
  • xmax：删除或更新该行的事务ID
  • cid：命令ID（同一事务中多条命令的顺序）
  • ctid：行的物理位置（页号:行号）
  • infomask：行标志位（如是否有NULL值、是否有变长字段等）
  • infomask2：更多行标志位（如是否有TOAST数据等）
  • hoff：行头长度

用户数据 ​

  • 存储表中各列的实际数据
  • 列数据按创建表时的顺序存储
  • 支持NULL值压缩存储
  • 支持变长字段（如text、varchar）

TOAST机制 ​

TOAST（The Oversized-Attribute Storage Technique）是PostgreSQL用于存储大字段的机制：

  • 当行数据大小超过页大小的25%时，大字段会被压缩或存储到TOAST表中
  • TOAST表是一种特殊的辅助表，用于存储大字段数据
  • TOAST机制支持四种存储策略：
    • PLAIN：不使用TOAST，大字段直接存储
    • EXTENDED：先压缩，再存储到TOAST表（默认）
    • EXTERNAL：不压缩，直接存储到TOAST表
    • MAIN：只压缩，不存储到TOAST表

WAL（Write-Ahead Logging）机制 ​

WAL是PostgreSQL实现数据持久性和一致性的核心机制，确保在系统崩溃时能够恢复数据。

WAL的工作原理 ​

  1. 所有数据修改操作先写入WAL日志
  2. WAL日志写入磁盘并刷新后，事务才能提交
  3. 后台进程（bgwriter、checkpointer）将脏页从共享缓冲区写入数据文件
  4. 系统崩溃时，通过重放WAL日志恢复未写入数据文件的修改

WAL文件结构 ​

  • WAL文件存储在pg_wal/目录中
  • 文件名格式：000000010000000000000001（时间线+逻辑日志序列号）
  • 默认大小为16MB（可通过--wal-segsize参数在initdb时配置）
  • WAL文件按顺序生成，形成一个无限的WAL序列

WAL级联 ​

  • 当WAL文件填满时，PostgreSQL会创建新的WAL文件
  • 旧的WAL文件会被归档（如果启用了归档）或回收（用于复制）
  • WAL文件的回收和归档由WAL保留策略控制

WAL配置参数 ​

  • wal_level：控制WAL的详细程度（minimal、replica、logical）
  • synchronous_commit：控制事务提交时WAL的刷新策略
  • wal_buffers：WAL缓冲区大小
  • checkpoint_timeout：检查点间隔时间
  • max_wal_size：WAL文件的最大保留数量
  • min_wal_size：WAL文件的最小保留数量

表空间 ​

表空间是PostgreSQL用于将数据库对象存储在不同磁盘或文件系统的机制，提供了灵活的存储管理能力。

表空间的物理存储 ​

  • 表空间在物理上对应一个目录
  • 表空间目录可以位于任何文件系统
  • pg_tblspc/目录中包含指向实际表空间目录的符号链接
  • 符号链接的名称是表空间的OID

表空间的创建和管理 ​

  • 使用CREATE TABLESPACE命令创建表空间
  • 使用ALTER TABLESPACE命令修改表空间
  • 使用DROP TABLESPACE命令删除表空间
  • 使用TABLESPACE子句指定表或索引的表空间

表空间的应用场景 ​

  • 将热点数据存储在高性能存储（如SSD）
  • 将冷数据存储在低成本存储（如HDD）
  • 将索引和数据分开存储，提高I/O并行度
  • 管理不同磁盘的空间使用
  • 实现存储的分级管理

存储管理 ​

空间分配与回收 ​

PostgreSQL使用多种机制管理存储空间：

  1. 自动空间分配：当表需要更多空间时，自动创建新的数据文件
  2. VACUUM操作：回收死元组占用的空间
  3. autovacuum进程：自动执行VACUUM和ANALYZE操作
  4. TOAST机制：高效存储大字段数据
  5. 表分区：将大表分割为多个小表，便于管理

存储监控 ​

DBA需要定期监控PostgreSQL的存储使用情况：

  • 表大小监控：

    sql
    SELECT pg_size_pretty(pg_total_relation_size('table_name'));

  • 数据库大小监控：

    sql
    SELECT pg_size_pretty(pg_database_size('database_name'));

  • WAL使用监控：

    sql
    SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_replay_lsn()) AS replication_lag;

  • 表空间使用监控：

    sql
    SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) AS size
FROM pg_tablespace;

存储优化 ​

  1. 选择合适的文件系统：

    • 推荐使用ext4、XFS或ZFS
    • 避免使用NTFS（Windows环境）
    • 考虑使用SSD提高性能

  2. 优化磁盘I/O：

    • 将WAL文件和数据文件存储在不同的磁盘
    • 使用RAID技术提高可靠性和性能
    • 调整文件系统参数（如noatime、nodiratime）

  3. 调整PostgreSQL参数：

    • shared_buffers：调整共享缓冲区大小
    • work_mem：调整排序和哈希操作的内存
    • maintenance_work_mem：调整维护操作的内存
    • effective_io_concurrency：调整异步I/O的并发度

  4. 合理规划表空间：

    • 将热点表和索引放在高性能存储
    • 将历史数据和冷数据放在低成本存储
    • 定期清理无用的数据

故障恢复机制 ​

PostgreSQL的物理架构设计支持多种故障恢复机制：

崩溃恢复 ​

  • 当PostgreSQL异常关闭时，会自动执行崩溃恢复
  • 恢复过程包括：
    1. 重新启动数据库服务器
    2. 读取并验证WAL文件
    3. 重放未提交的WAL记录
    4. 回滚未完成的事务
    5. 恢复数据库到一致状态

时间点恢复（PITR） ​

  • 基于基础备份和WAL归档实现
  • 允许将数据库恢复到任意时间点
  • 恢复过程包括：
    1. 还原基础备份
    2. 重放WAL归档文件
    3. 停止在指定的时间点或事务ID

基于WAL的复制 ​

  • 主库将WAL记录发送到从库
  • 从库重放WAL记录，保持与主库数据一致
  • 支持同步复制和异步复制
  • 用于实现高可用性和负载均衡

备份与恢复的物理层面 ​

物理备份 ​

  • 直接复制数据目录或表空间
  • 优点：备份和恢复速度快，适用于大型数据库
  • 缺点：不支持跨版本恢复，不能选择性恢复
  • 工具：pg_basebackup、rsync等

逻辑备份 ​

  • 导出数据库对象的逻辑结构和数据
  • 优点：支持跨版本恢复，可选择性恢复
  • 缺点：备份和恢复速度慢，适用于小型数据库
  • 工具：pg_dump、pg_dumpall、pg_restore等

增量备份 ​

  • 基于WAL归档实现
  • 只备份自上次备份以来的变化
  • 优点：备份速度快，占用空间小
  • 缺点：恢复过程复杂，需要基础备份

案例分析：存储性能优化 ​

背景：某电商平台在促销期间，数据库I/O负载过高，导致系统响应缓慢。

分析：

  1. 监控显示磁盘I/O使用率接近100%
  2. 主要瓶颈是WAL写入和数据文件更新
  3. 数据库和WAL文件存储在同一磁盘上
  4. 使用的是普通HDD磁盘，IOPS不足

优化措施：

  1. 存储分离：

    • 将WAL文件迁移到专用SSD磁盘
    • 将热点表和索引迁移到SSD表空间
    • 将历史数据迁移到HDD表空间

  2. 参数调整：

    • 调整shared_buffers从1GB增加到4GB
    • 调整wal_buffers从16MB增加到64MB
    • 调整checkpoint_timeout从5min增加到15min
    • 调整max_wal_size从1GB增加到4GB

  3. 文件系统优化：

    • 将文件系统挂载选项改为noatime,nodiratime
    • 调整XFS文件系统的日志级别

结果：

  • 磁盘I/O使用率从95%降低到30%
  • 事务提交延迟从200ms降低到50ms
  • 系统能够处理的并发请求数增加3倍
  • 促销期间系统稳定运行，未出现性能问题