《PostgreSQL指南》记录

Why choose PostgreSQL, or not MySQL ?

  • SQL 标准支持更好：PostgreSQL 对 SQL 标准的支持更加全面，而 MySQL 在某些情况下依赖非标准实现（如分组函数的行为）。
  • 高级功能更强：
    • JOSNB 的性能更优：PostgreSQL 的 JSONB 数据类型支持更高效的索引和操作，而 MySQL 的 JSON 功能较为有限。
    • 复杂查询：支持递归查询、窗口函数等复杂功能，而 MySQL 在这些方面功能较弱或支持有限。
    • 并行查询：PostgreSQL 提供原生的并行查询能力，能够充分利用多核 CPU，而 MySQL 直到 8.0 版本才有一定程度的改进。
  • 扩展性更好：
    • PostgreSQL 支持自定义数据类型、函数和插件，更适合需要扩展或复杂业务逻辑的项目。
    • PostGIS 插件使其在地理信息领域表现出色，而 MySQL 的 GIS 功能相对简单。
  • 更高的性能和灵活性：
    • 支持多种索引类型（如 GIN、GiST、BRIN 等），适合高性能全文搜索、地理查询等场景。
    • 支持更复杂的查询优化器策略，适合复杂查询和数据分析。
  • 更可靠的数据一致性：
    • PostgreSQL 的 MVCC 机制更成熟，避免了常见的锁争用问题。
    • MySQL 的 MVCC 在某些情况下（如高并发）性能欠佳，容易导致死锁。

PostgreSQL 适用于有复杂数据分析需求、地理信息系统、高并发和大数据量等应用场景，相比于 MySQL 的应用场景更复杂。

数据库集簇、数据库和数据表

数据库集簇的逻辑结构

数据库集簇是一组数据库的集合，由一个 PostgreSQL 服务器管理。而数据库是数据对象的集合，数据库对象用于存储或引用数据的数据结构。

在 PosgreSQL 中，所有的数据库对象都通过相应的对象标识符（oid）进行管理，这些标识都是无符号 4 字节整型。

数据库集簇的物理结构

数据库集簇就是一个文件目录。对于 PostgreSQL，base 子目录中的每个子目录都是一个数据库，数据库中的每个表和索引都至少在相应子目录下存储为一个文件，且该子目录的名称与相应数据库的 oid 相同。

每个小于 1GB 的表或索引都在相应的数据库目录中存储为单个文件，这些文件通过 oid 进行管理。大小超过 1GB 时，会创建并使用一个名为 relfilenode.1 的新文件，如果也填满了就会创建 relfilenode.2 ，以此类推。文件的最大大小是可以通过配置文件更改的。

PostgreSQL 的表空间是基础目录之外的附加数据区域，表空间就像一个仓库，用来存放数据库文件。每个仓库可以放在不同的物理位置（比如不同的磁盘或路径）。默认情况下，PostgreSQL 有一个主仓库，叫 pg_default，所有的数据库和表默认都存放在这里。

堆表文件的内部布局

数据文件内部被划分成固定长度的区块，大小默认为 8KB，从 0 开始按顺序编号。如果文件已满，就在最后加一个新的区块。

区块包含三种类型的数据：

  • 堆元组：数据记录本身，从区块地步开始依序堆叠；
  • 行指针：指向堆元组的指针；
  • 首部数据：包含关于区块的元数据，大小为 24B。

为了识别表中的元组，数据库使用元组标识符（TID），它由区块号、指向元组的行指针的偏移号组成。

读写元组的方式

这里只讲述最基本的读写过程，其余细节留待后续章节描述。

写堆元组：

  • PostgreSQL 在堆表的存储文件中找到有空闲空间的区块；
  • 数据作为一条元组插入到这个区块的空闲空间中。

读堆元组：

  • PostgreSQL 首先通过表的存储文件和区块号找到目标区块；
  • 使用共享缓冲区，把目标区块加载到内存；
  • PostgreSQL 遍历页面中的所有堆元组，检查每个元组是否满足查询条件。

进程和内存架构

进程架构

postgres 服务器进程是所有进程的父进程，它会在内存中分配共享内存区域，启动各种后台进程，并等待来自客户端的连接请求。每当接收到来自客户端的连接请求时，它都会启动一个后端进程，然后由启动的后端进程处理该客户端发出的所有查询。

每个后端进程由 postgres 服务器进程启动，并处理连接另一侧的客户端发出的查询。它通过单条 TCP 连接与客户端通信。PostgreSQL 没有原生的连接池功能，客户端与服务器频繁地建立断开连接会导致开销变大，可以通过池化中间件解决。

后台进程有很多种类，它们依赖于 PostgreSQL 的内部机制和特定的独立特性。

除了这三类进程外，还有复制相关进程、后台工作进程。

内存架构

PostgreSQL 的内存架构可以分为：

  • 本地内存区域：由每个后端进程分配，自己使用。
  • 共享内存区域：由所有进程使用。

​

查询处理

概览

每个后端进程由以下 5 个子系统构成：

  • 解析器：根据 SQL 语句生成一棵语法解析树；
  • 分析器：对语法解析树进行语义分析，生成一棵查询树；
  • 重写器：按照规则系统中的规则对查询树进行改写；
  • 计划器：基于查询树生成一棵执行效率最高的计划树；
  • 执行器：按照计划树中的顺序访问表和索引，执行相应查询。

解析器只会检查 SQL 语法，并不会检查语义是否正确。解析器不检查语义，如查询中有一个不存在的表名，解析器不会报错，该部分由分析器负责。

分析器中，解析和分析过程会吧所有语句转换为一颗查询树，查询树包含对应查询的元数据、特定子句相应的数据，供之后的步骤进行进一步的处理。

PostgreSQL 的规则系统基于重写器实现，重写器会根据存储在 pg_rules 中的规则对查询树进行转换。PostgreSQL 的视图是基于规则系统实现的，当使用 CREATE VIEW 命令定义一个视图时，PostgreSQL就会创建相应的规则，并存储到系统目录中。当执行一个包含该视图的查询，解析器会创建一棵语法解析树，重写器会根据 pg_rules 中存储的视图规则将部分节点重写为一棵查询子树，与子查询相对应。

计划器从重写器获取一棵查询树，基于查询树生成一棵能够被执行器高效执行的计划树。计划器是完全基于代价估计的，而不是基于规则的优化和提示。

计划器非常复杂，也是极其重要的内容，在后面章节会详细说明。

计划树由计划节点组成，计划节点包含执行器进行处理所必需的信息，在单表查询的场景中，执行器会按照从终端节点往根节点的顺序依次处理这些节点。

单表查询的代价估计

在 PostgreSQL 中有三种代价：

  • 启动代价：在读取到第一条元组前花费的代价，比如索引扫描节点的启动代价就是读取目标表的索引页，取到第一个元组的代价；
  • 运行代价：获取全部元组的代价；
  • 总代价：前两者之和。

下面分为三种方式去计算代价估计：顺序扫描、索引扫描、排序。

顺序扫描

顺序扫描会逐页扫描整个表，因此其代价与表的大小有关。计算公式如下：

$$ {Cost}_{seqscan} = {Cost}_{startup} + {Pages} \times SeqPageCost + {Tuples} \times CpuTupleCost $$

解释：

  • cost_startup：初始化代价，通常为零或很小。
  • pages：表占用的磁盘页数（关系到表的大小）。
  • seq_page_cost：每个磁盘页的顺序访问代价，默认值是 1.0。
  • tuples：表中的行数。
  • cpu_tuple_cost：处理每行数据的 CPU 代价，默认值是 0.01。

优化点：当表很大时，顺序扫描的代价较高，适合全表扫描场景。

索引扫描

索引扫描会首先通过索引定位到目标记录，再访问对应的表行，因此代价包括两部分：索引扫描代价和表扫描代价。公式如下：

$$ {Cost}_{indexscan} = {Cost}_{startup} + {IndexPages} \times {RandomPageCost} + {Tuples} \times ({CpuTupleCost} + {CpuIndexTupleCost}) $$

解释：

  • cost_startup：索引初始化代价。
  • index_pages：访问的索引页数量。
  • random_page_cost：随机访问磁盘页的代价，默认值是 4.0。
  • tuples：满足条件的行数。
  • cpu_tuple_cost：处理每行数据的 CPU 代价。
  • cpu_index_tuple_cost：处理索引元组的 CPU 代价，默认值是 0.005。

特点：

  • 当条件过滤比例较低（即匹配的行数很少）时，索引扫描的代价较低。

  • 如果表较大且符合条件的记录较多，索引扫描代价可能会超过顺序扫描。

排序

排序的代价主要受需要排序的行数、排序的字段数量以及排序的内存/磁盘使用影响。内存足够用快排，不足用归并。公式如下：

$$ {Cost}_{sort} = {Cost}_{startup} + {Tuples} \times \log_2({Tuples}) \times {CpuOperatorCost} + {DiskCost} $$

解释：

  • cost_startup：排序初始化代价。
  • tuples：需要排序的行数。
  • cpu_operator_cost：每次比较操作的代价，默认值是 0.0025。
  • disk_cost：当排序超出内存时，需要进行磁盘操作，其代价由随机和顺序磁盘访问的混合代价计算。

特点：

  • 如果排序数据量较小，代价较低且可能完全在内存中完成。
  • 如果排序数据量较大，可能需要多次磁盘读写，导致代价显著增加。

创建单表查询的计划树

计划树的创建会经过计划器的三个步骤：

  1. 执行预处理；
  2. 找到代价最小的访问路径；
  3. 按照代价最小的访问路径，创建计划树。

访问路径是估算代价时的处理单元；比如，顺序扫描，索引扫描，排序以及各种连接操作都有其对应的路径。访问路径只在计划器创建查询计划树的时候使用。

预处理

在生成计划树前，优化器需要对查询进行预处理。预处理的主要目的是简化和规范查询，为后续的优化和执行计划生成做好准备。

预处理的具体内容：

  • 解析查询：将 SQL 查询解析为语法解析树。

  • 语义检查：

    • 验证表和列是否存在。

    • 检查数据类型是否匹配。

    • 验证用户权限是否足够执行该查询。

  • 规则重写：

    • 如果查询涉及视图，展开视图为对应的基表查询。

    • 如果触发规则存在（如 INSTEAD OF 触发器），根据规则替换查询。

  • 生成查询树：

    • 根据语法解析树和重写规则生成逻辑查询计划。
    • 查询树是一种规范化的结构，描述了查询的逻辑步骤，但尚未包含执行方式。

找到代价最小的访问路径

优化器为查询中的每个表选择访问路径，并评估每种路径的代价。目标是找到总代价最低的访问路径。

评估访问路径的方式：

  • 顺序扫描：逐页扫描整个表，代价与表的大小（页数）和行数成正比。
  • 索引扫描：利用索引定位到满足条件的记录，代价与索引大小、符合条件的记录数成正比。
  • 索引仅扫描：如果查询所需的列全部包含在索引中，无需访问表。
  • 位图索引扫描：当多个索引条件（布尔表达式）结合时，使用位图来合并索引结果，适合中等数量的结果集。

代价与其计算方式见《单表查询的代价估计》这一小节。

按照代价最小的访问路径，创建计划树

根据评估出的代价最小路径，生成具体的计划树。计划树描述了查询的实际执行步骤和顺序，供执行引擎使用。

创建流程：

  • 创建计划节点：
    • 为每个操作创建一个计划节点。
    • 每个节点包含该操作的代价、输入来源和操作类型。
  • 构建计划树结构：
    • 将各个计划节点组织成一棵树，根节点表示查询的最终输出，子节点是该输出的输入来源。
    • 子节点之间的关系体现了操作的顺序。
  • 优化计划树：
    • 管道化操作：避免中间结果的浪费，如将过滤和排序合并为流水线操作。
    • 并行化：如果系统支持并行查询，评估是否可以并行执行操作。

执行器的工作流程

  1. 接收执行计划：从优化器获取执行计划树，明确执行顺序和操作方式；
  2. 初始化执行环境：准备所需资源，加载表、索引等元数据；
  3. 遍历执行计划树：按计划树的结构自顶向下逐节点执行操作，每个节点处理完成后，将结果传递给父节点；
  4. 流式处理返回结果：顶层节点汇总所有数据，并逐步返回给客户端。采用按需获取机制，减少中间结果的物化。

连接操作 Join

在 PostgreSQL 中有三种连接操作：

  • 嵌套循环连接：对每个外表中的记录，逐一扫描内表，寻找匹配的记录。这种连接操作适用于外表数据较小，而内表有索引，能够快速定位匹配记录的场景。时间复杂度为 $O(m \times n)$ 。
  • 归并连接：要求参与连接的表已经按连接条件的列排序（通常是索引列），同时遍历两表的排序结果，逐步匹配记录。适用场景为两个表都按连接列排序，或可以通过索引快速排序，两表数据量大且有序时效果最佳。时间复杂度为 $O(m + n)$ 。
  • 散列连接：将较小的表（称为内表）加载到内存中并构建哈希表，使用连接列作为哈希键。遍历较大的表（称为外表），通过哈希表快速定位匹配记录。适用场景为外表或内表数据分布均匀，且内表较小时性能较优。时间复杂度为 $O(m + n)$ 。

并发控制

在 PostgreSQL 中，选择的并发控制机制是多版本并发控制（MVCC）的变体快照隔离（SI）。

PostgreSQL 中的事务隔离级别：

隔离等级脏读不可重复读幻读串行化异常
读已提交NoYesYesYes
可重复读NoNoPG 中 No，ANSI SQL 中 YesYes
串行化NoNoNoNo

  • 脏读：一个事务可以读取到其他未提交事务的修改。

  • 不可重复读：在同一个事务中，两次读取同一行数据，第二次读取的数据值可能不同。

  • 幻读：在同一事务中，两次查询同一范围数据，第二次查询返回的结果集不同。

事务标识

每当事务开始时，事务管理器就会为其分配一个称为事务标识（transaction id, txid）的唯一标识符。 PostgreSQL 的 txid 是一个32位无符号整数，总取值约42亿。

提交日志 clog

PostgreSQL 在提交日志（Commit Log, clog）中保存事务的状态。clog 分配于共享内存中，并用于事务处理过程的全过程。

PostgreSQL 定义了四种事务状态，即：IN_PROGRESS，COMMITTED，ABORTED 和 SUB_COMMITTED。前三种很明显，SUB_COMMITTED 用于子事务。

clog 在逻辑上是一个数组，由共享内存中一系列页面组成。数组的序号索引对应相应事务的标识，内容就是事务的状态。txid 不断前进，当 clog 空间耗尽无法存储新的事务状态时，就会追加分配一个新的页面。当需要获取事务的状态时，PostgreSQL 将调用相应内部函数读取clog，并返回所请求事务的状态。

当 PostgreSQL 关机或执行存档过程时，clog 数据会写入至 pg_clog 子目录下的文件中（注意在10版本中，pg_clog 被重命名 pg_xact）。当PostgreSQL启动时会加载存储在 pg_clog（pg_xact）中的文件，用其数据初始化 clog。clog 的大小会不断增长，因为只要 clog 一填满就会追加新的页面。但并非所有数据都是必需的，PostgreSQL 会定期进行清除。

事务快照

事务快照是一个数据集，存储着某个特定事务在某个特定时间点所看到的事务状态信息。

事务快照是由事务管理器提供的。在 READ COMMITTED 隔离级别，事务在执行每条 SQL 时都会获取快照；其他情况下（REPEATABLE READ 或 SERIALIZABLE 隔离级别），事务只会在执行第一条 SQL 命令时获取一次快照。获取的事务快照用于元组的可见性检查。

使用获取的快照进行可见性检查时，所有活跃的事务都必须被当成 IN PROGRESS 的事务等同对待，无论它们实际上是否已经提交或中止。这条规则非常重要，因为它正是 READ COMMITTED 和 REPEATABLE READ/SERIALIZABLE 隔离级别中表现差异的根本来源。

可见性检查

可见性检查指如何为给定事务挑选堆元组的恰当版本。

可见性检查的流程如下：

  1. 获取快照：事务启动时，获取当前数据库的快照

    • 快照记录了以下信息：

      • 当前事务 ID（Transaction ID, XID）：标识当前事务。

        活跃事务列表：记录其他未提交事务的 XID。

        最小活动事务 ID 和最大活动事务 ID：用于界定事务的范围。

  2. 判断数据版本：

    每条记录都存储了信息，用于可见性检查：

    • xmin：插入该行的事务 ID。

      xmax：删除该行的事务 ID（如果行未被删除，则为空）。

      行状态判断：

      • 如果 xmin 对当前事务可见，且 xmax 为空或不可见，则该行对当前事务可见。
      • 如果 xmax 可见，表示该行已被删除，对当前事务不可见。

  3. 基于隔离级别调整规则：在可见性检查中，不同隔离级别有不同的规则。

    • READ COMMITTED：
      • 动态检查事务状态。
      • 如果 xmin 所属事务已提交，行可见。
      • 如果 xmax 所属事务已提交，行不可见。
      • 快照实时变化，能看到最新提交的数据。
    • REPEATABLE READ：
      • 快照固定。
      • 可见性检查完全基于事务开始时的快照，忽略后续事务提交或中止的变化。
      • 保证整个事务期间读取一致。
    • SERIALIZABLE：
      • 同 REPEATABLE READ，但引入额外冲突检测，确保事务之间不存在逻辑冲突。

由此可以得到并发问题的解决方法：

  • 脏读：READ COMMITTED 和更高级隔离级别中，通过可见性检查规则避免脏读。

    • 检查 xmin 或 xmax 的事务是否已提交，未提交的数据不可见。

  • 不可重复读：REPEATABLE READ 和 SERIALIZABLE：

    • 快照固定，第二次读取仍基于事务开始时的快照，因此避免不可重复读。

  • 幻读：

    • REPEATABLE READ：

      • 基于快照查询，不考虑事务 B 的插入，因此避免幻读。

    • SERIALIZABLE：

      • 除了固定快照外，还通过冲突检测防止逻辑上可能导致幻读的操作，如对范围加锁。

防止丢失更新

丢失更新，即写冲突，是事务并发更新同一行时发生的异常。

当使用 UPDATE 进行更新操作时，内部调用了 ExecUpdate 函数，该函数包含了防止丢失更新的核心逻辑。

ExecUpdate 同时采用以下几种方式来防止丢失更新：

  1. MVCC：PostgreSQL 通过 MVCC 机制对数据行的版本进行管理，ExecUpdate 会检查每个行的可见性。

  2. 条件更新（乐观并发控制）：

    • 思想：假定冲突很少发生，因此允许事务自由执行，提交时检查是否冲突，冲突则回滚。无需锁定、性能较高。

    • 更新行时，验证行的 ctid（元组 ID） 和其他条件是否匹配当前快照。如果行已被其他事务修改，条件不匹配，则抛出异常或返回冲突状态。

  3. 行锁（悲观并发控制）：

    • 思想： 假定冲突可能发生，因此通过锁机制防止事务并发修改。
    • 更新前会对目标行加锁，防止其他事务并发修改。

  4. 检查并更新（CAS）：

    • CAS（Compare And Swap，比较并交换）是原子操作。无需加锁，性能高。
    • CAS的简单步骤：
      1. 读取目标值。
      2. 比较目标值和预期值是否相等，相等则更新为新值，不相等则放弃操作或重试。
    • 在 ExecUpdate 中会调用辅助函数检查当前行的状态是否与快照一致：
      • 如果行已被修改，则回滚或重试。

  5. 触发器与约束

    • 在 ExecUpdate 中，触发器和约束检查可以防止逻辑层面的丢失更新：
      • 触发器可以验证是否存在更新冲突。
      • 约束（如唯一性约束）会防止插入或更新导致的数据不一致。

  6. 隔离级别的可见性检查

清理过程（VACUUM）

清理概述

清理的两个主要任务是删除死元组和冻结事务标识。

为了移除死元组，有并发清理和完整清理两种模式。并发清理会删除表文件每个页面中的死元组，而其他事务可以在其运行时继续读取该表。相反，完整清理不仅会移除整个文件中所有的死元组，还会对整个文件中所有的活元组进行碎片整理，而其他事务在完整清理运行时无法访问该表。

以下是具体的清理任务：

  • 移除死元组：
    • 移除每一页中的死元组，并对每一页内的活元组进行碎片整理。
    • 移除指向死元组的索引元组。
  • 冻结旧的事务标识：
    • 如有必要，冻结旧元组的事务标识。
    • 更新与冻结事务标识相关的系统视图。
    • 如果可能，移除非必需的 clog。
  • 其他：
    • 更新已处理表的空闲空间映射（FSM）和可见性映射（VM）。
    • 更新一些统计信息。

可见性映射

清理过程的代价高昂，因此PostgreSQL在8.4版中引入了VM，用于减小清理的开销。

VM的基本概念很简单。 每个表都拥有各自的可见性映射，用于保存表文件中每个页面的可见性。 页面的可见性确定了每个页面是否包含死元组。清理过程可以跳过没有死元组的页面。

冻结过程

冻结过程有两种模式，分别称为惰性模式和迫切模式。冻结过程通常以惰性模式运行，但当满足特定条件时，也会以迫切模式进行。在惰性模式下，冻结处理仅使用目标表对应的 VM 扫描包括死元组的页面。迫切模式则相反，它会扫描所有的页面，无论其是否包含死元组，它还会更新与冻结处理相关的系统视图，并在可能的情况下删除不必要的 clog。

9.5或更早版本中的迫切模式效率不高，因为它始终会扫描所有页面。 为了解决这一问题，9.6版本改进了可见性映射 VM 与冻结过程。

自动清理

自动清理守护进程已经将清理过程自动化，自动清理守护程序周期性地唤起几个 autovacuum_worker 进程。

自动清理守护进程唤起的 autovacuum 工作进程会依次对各个表执行并发清理，从而将对数据库活动的影响降至最低。

预写式日志 WAL

事务日志是数据库的关键组件，因为当出现系统故障时，任何数据库管理系统都不允许丢失数据。事务日志是数据库系统中所有变更与行为的历史记录，当诸如电源故障，或其他服务器错误导致服务器崩溃时，它被用于确保数据不会丢失。由于日志包含每个已执行事务的相关充分信息，因此当服务器崩溃时，数据库服务器应能通过重放事务日志中的变更与行为来恢复数据库集群。

WAL 是 Write Ahead Logging 的缩写，它指的是将变更与行为写入事务日志的协议或规则；而在 PostgreSQL 中，WAL 是 Write Ahead Log 的缩写。在这里它被当成事务日志的同义词，而且也用来指代一种将行为写入事务日志（WAL）的实现机制。

概述

为了应对系统失效，PostgreSQL 将所有修改作为历史数据写入持久化存储中。这份历史数据称为 XLOG 记录或 WAL 数据。

当插入、删除、提交等变更动作发生时，PostgreSQL 会将 XLOG 记录写入内存中的 WAL 缓冲区。当事务提交或中止时，它们会被立即写入持久存储上的 WAL 段文件中。XLOG 记录的日志序列号（Log Sequence Number, LSN）标识了该记录在事务日志中的位置，记录的 LSN 被用作 XLOG 记录的唯一标识符。

数据库系统恢复时，从重做点开始恢复，即最新一个检查点开始时 XLOG 记录写入的位置。检查点进程是一个后台进程，周期性地执行过程。当检查点进程开始执行检查点时，它会向当前 WAL 段文件写入一条 XLOG 记录，称为检查点，这条记录包含了最新的重做点位置。不需要任何特殊的操作，重启 PostgreSQL 时会自动进入恢复模式，PostgreSQL 会从重做点开始，依序读取正确的 WAL 段文件并重放 XLOG 记录。

事务日志与WAL段文件

PostgreSQL 中的事务日志实际上默认被划分为 16M 大小（可以改变）的一系列文件，这些文件被称作 WAL 段。

WAL 文件有特定的规则命名，命名格式为24位十六进制日志段编号：

  • 前 8 位：时间线标识符（Timeline ID）。

    时间线标识符用于表示数据库的历史版本。主要用于数据库的恢复和分叉历史跟踪，尤其是在主从切换或恢复过程中。

  • 中间 8 位：逻辑日志文件号。

  • 后 8 位：日志段号。

WAL记录的写入

PostgreSQL 的 WAL 记录数据库事务的每一步操作，在将数据写入磁盘之前，先将变更写入 WAL 文件。这种机制保证了以下特性：

  • 数据一致性：即使系统崩溃，所有已提交的事务都可以通过 WAL 恢复。
  • 高效性：避免频繁直接写入数据文件，减少 I/O 操作。

WAL 写入的核心逻辑：

  1. 事务开始：分配事务 ID 并初始化 WAL 条目。
  2. 操作记录：在事务中对表或索引进行的每一项更改都会生成 WAL 条目。
  3. 提交事务：记录事务提交信息到 WAL 中。

WAL写入进程

WAL 写入者是一个后台进程，用于定期检查 WAL 缓冲区，并将所有未写入的 XLOG 记录写入 WAL 段文件。 这个进程的目的是避免 XLOG 记录的突发写入。 如果没有启用该进程，则在一次提交大量数据时，XLOG 记录的写入可能会成为瓶颈。

PostgreSQL中的检查点过程

在 PostgreSQL 中，检查点是将内存中已修改但未写入磁盘的数据（脏页）刷写到磁盘的过程，并记录数据库的状态以加速崩溃恢复。

检查点过程：

  1. 标记检查点起点：记录当前 WAL 的位置。
  2. 刷写脏页：将共享缓冲区中的修改数据写入磁盘文件。
  3. 记录检查点日志：在 WAL 中插入检查点记录。
  4. 更新控制文件：保存检查点的元信息，如时间线和 WAL 位置。

PostgreSQL中的数据库恢复

  • 崩溃恢复：

    1. 从控制文件（pg_control）获取最近的检查点位置。

    2. 从检查点位置开始读取并应用 WAL 文件。

    3. 回放未完成的事务，并撤销部分完成的事务。

    4. 恢复完成后，数据库进入正常运行状态。

归档日志与持续归档

归档日志是指 PostgreSQL 的 WAL 文件被保存到一个安全的位置（如磁盘、云存储等），以便在恢复过程中重放这些日志。持续归档（Continuous Archiving）是指 PostgreSQL 在运行期间持续将 WAL 文件归档到外部存储，用于长期保存和恢复。

基础备份与时间点恢复

基础备份

在 PostgreSQL 中，自8.0版本开始提供在线的全量物理备份，整个数据库集簇（即物理备份数据）的运行时快照称为基础备份（base backup）。

基础备份分为以下两种：

  • 热备份：在数据库运行时进行备份。
  • 冷备份：数据库完全停止运行后，直接复制数据目录。

基础备份中热备份的工作流程如下：

  1. 启动备份：调用 pg_start_backup() 命令，创建备份标记并生成 WAL 文件。
  2. 拷贝数据文件：使用工具如 pg_basebackup 或手动复制数据目录。
  3. 结束备份：调用 pg_stop_backup()，生成备份结束标记，并记录需要的 WAL 位置。

时间点恢复的工作原理

PostgreSQL 在8.0版中引入了时间点恢复（Point-In-Time Recovery, PITR）。这一功能可以将数据库恢复至任意时间点，这是通过使用一个基础备份和由持续归档生成的归档日志（WAL）来实现的。

工作原理：

  1. 应用 WAL 文件：从基础备份的 WAL 起点开始逐步回放 clog ，根据相关参数决定回放的终止位置。
  2. 完成恢复：当达到目标时间点后，数据库停止恢复并进入正常运行模式。

时间线与时间线历史文件

每次数据库恢复到新状态（如时间点恢复）时，都会生成一个新的时间线，时间线用于区分不同的数据库历史路径。每个时间线用一个唯一的 ID 表示（如 00000002），时间线的变更记录在 WAL 文件中，并由 pg_control 跟踪。

时间线历史文件记录了时间线的分叉点和相关信息。第二次以及后续的时间点恢复需要依靠时间线历史文件进行，包括切换不同的时间线等操作。