PG 管理

• PostgreSQL 逻辑复制详解
• PostgreSQL 宏观查询优化之 pg_stat_statements
• 如何用 pg_filedump 抢救数据？
• PG中的本地化排序规则
• PG复制标识详解（Replica Identity）
• PG慢查询诊断方法论
• 故障档案:时间回溯导致的Patroni故障
• 在线修改主键列类型
• 黄金监控指标：错误延迟吞吐饱和
• 数据库集群管理概念与实体命名规范
• PostgreSQL的KPI
• 在线修改PG字段类型
• 故障档案：PG安装Extension导致无法连接
• PostgreSQL 常见复制拓扑方案
• 温备：使用pg_receivewal
• 故障档案：pg_dump导致的连接池污染
• PostgreSQL数据页面损坏修复
• 关系膨胀的监控与治理
• PipelineDB快速上手
• TimescaleDB 快速上手
• 故障档案：PostgreSQL事务号回卷
• 故障档案：序列号消耗过快导致整型溢出
• 监控PG中的表大小
• PgAdmin安装配置
• 故障档案：快慢不匀雪崩
• Bash与psql小技巧
• PostgreSQL例行维护
• 备份恢复手段概览
• PgBackRest2中文文档
• Pgbouncer快速上手
• PG服务器日志常规配置
• 空中换引擎 —— PostgreSQL不停机迁移数据
• 使用FIO测试磁盘性能
• 使用sysbench测试PostgreSQL性能
• 找出没用过的索引
• 批量配置SSH免密登录
• Wireshark抓包分析协议
• file_fdw妙用无穷——从数据库读取系统信息
• Linux 常用统计 CLI 工具
• 源码编译安装 PostGIS
• PostgreSQL MongoFDW安装部署

PostgreSQL 逻辑复制详解

逻辑复制

逻辑复制（Logical Replication），是一种根据数据对象的 复制标识（Replica Identity）（通常是主键）复制数据对象及其变化的方法。

逻辑复制 这个术语与 物理复制相对应，物理复制使用精确的块地址与逐字节复制，而逻辑复制则允许对复制过程进行精细的控制。

逻辑复制基于 发布（Publication） 与 订阅（Subscription）模型：

• 一个 发布者（Publisher） 上可以有多个发布，一个 订阅者（Subscriber） 上可以有多个 订阅 。
• 一个发布可被多个订阅者订阅，一个订阅只能订阅一个发布者，但可订阅同发布者上的多个不同发布。

针对一张表的逻辑复制通常是这样的：订阅者获取发布者数据库上的一个快照，并拷贝表中的存量数据。一旦完成数据拷贝，发布者上的变更（增删改清）就会实时发送到订阅者上。订阅者会按照相同的顺序应用这些变更，因此可以保证逻辑复制的事务一致性。这种方式有时候又称为 事务性复制（transactional replication）。

逻辑复制的典型用途是：

• 迁移，跨PostgreSQL大版本，跨操作系统平台进行复制。
• CDC，收集数据库（或数据库的一个子集）中的增量变更，在订阅者上为增量变更触发触发器执行定制逻辑。
• 分拆，将多个数据库集成为一个，或者将一个数据库拆分为多个，进行精细的分拆集成与访问控制。

逻辑订阅者的行为就是一个普通的PostgreSQL实例（主库），逻辑订阅者也可以创建自己的发布，拥有自己的订阅者。

如果逻辑订阅者只读，那么不会有冲突。如果会写入逻辑订阅者的订阅集，那么就可能会出现冲突。

发布

一个 发布（Publication） 可以在物理复制主库 上定义。创建发布的节点被称为 发布者（Publisher） 。

一个 发布 是 由一组表构成的变更集合。也可以被视作一个 变更集（change set） 或 复制集（Replication Set） 。每个发布都只能在一个 数据库（Database） 中存在。

发布不同于模式（Schema），不会影响表的访问方式。（表纳不纳入发布，自身访问不受影响）

发布目前只能包含表（即：索引，序列号，物化视图这些不会被发布），每个表可以添加到多个发布中。

除非针对ALL TABLES创建发布，否则发布中的对象（表）只能（通过ALTER PUBLICATION ADD TABLE）被显式添加。

发布可以筛选所需的变更类型：包括INSERT、UPDATE、DELETE 和TRUNCATE的任意组合，类似触发器事件，默认所有变更都会被发布。

复制标识

复制标识

一个被纳入发布中的表，必须带有 复制标识（Replica Identity），只有这样才可以在订阅者一侧定位到需要更新的行，完成UPDATE与DELETE操作的复制。

默认情况下，主键 （Primary Key）是表的复制标识，非空列上的唯一索引 （UNIQUE NOT NULL）也可以用作复制标识。

如果没有任何复制标识，可以将复制标识设置为FULL，也就是把整个行当作复制标识。（一种有趣的情况，表中存在多条完全相同的记录，也可以被正确处理，见后续案例）使用FULL模式的复制标识效率很低（因为每一行修改都需要在订阅者上执行全表扫描，很容易把订阅者拖垮），所以这种配置只能是保底方案。使用FULL模式的复制标识还有一个限制，订阅端的表上的复制身份所包含的列，要么与发布者一致，要么比发布者更少。

INSERT操作总是可以无视 复制标识 直接进行（因为插入一条新记录，在订阅者上并不需要定位任何现有记录；而删除和更新则需要通过复制标识 定位到需要操作的记录）。如果一个没有 复制标识 的表被加入到带有UPDATE和DELETE的发布中，后续的UPDATE和DELETE会导致发布者上报错。

表的复制标识模式可以查阅pg_class.relreplident获取，可以通过ALTER TABLE进行修改。

ALTER TABLE tbl REPLICA IDENTITY 
{ DEFAULT | USING INDEX index_name | FULL | NOTHING };

尽管各种排列组合都是可能的，然而在实际使用中，只有三种可行的情况。

• 表上有主键，使用默认的 default 复制标识
• 表上没有主键，但是有非空唯一索引，显式配置 index 复制标识
• 表上既没有主键，也没有非空唯一索引，显式配置full复制标识（运行效率非常低，仅能作为兜底方案）
• 其他所有情况，都无法正常完成逻辑复制功能。输出的信息不足，可能会报错，也可能不会。
• 特别需要注意：如果nothing复制标识的表纳入到逻辑复制中，对其进行删改会导致发布端报错！

管理发布

CREATE PUBLICATION用于创建发布，DROP PUBLICATION用于移除发布，ALTER PUBLICATION用于修改发布。

发布创建之后，可以通过ALTER PUBLICATION动态地向发布中添加或移除表，这些操作都是事务性的。

CREATE PUBLICATION name
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
      | FOR ALL TABLES ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ]

ALTER PUBLICATION name ADD TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name SET TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name DROP TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name RENAME TO new_name

DROP PUBLICATION [ IF EXISTS ] name [, ...];

publication_parameter 主要包括两个选项：

• publish：定义要发布的变更操作类型，逗号分隔的字符串，默认为insert, update, delete, truncate。
• publish_via_partition_root：13后的新选项，如果为真，分区表将使用根分区的复制标识进行逻辑复制。

查询发布

发布可以使用psql元命令\dRp查询。

# \dRp
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | t          | t       | t       | t       | t         | f

pg_publication 发布定义表

``pg_publication` 包含了发布的原始定义，每一条记录对应一个发布。

# table pg_publication;
oid          | 20453
pubname      | pg_meta_pub
pubowner     | 10
puballtables | t
pubinsert    | t
pubupdate    | t
pubdelete    | t
pubtruncate  | t
pubviaroot   | f

• puballtables：是否包含所有的表
• pubinsert|update|delete|truncate 是否发布这些操作
• pubviaroot：如果设置了该选项，任何分区表（叶表）都会使用最顶层的（被）分区表的复制身份。所以可以把整个分区表当成一个表，而不是一系列表进行发布。

pg_publication_tables 发布内容表

pg_publication_tables是由pg_publication，pg_class和pg_namespace拼合而成的视图，记录了发布中包含的表信息。

postgres@meta:5432/meta=# table pg_publication_tables;
   pubname   | schemaname |    tablename
-------------+------------+-----------------
 pg_meta_pub | public     | spatial_ref_sys
 pg_meta_pub | public     | t_normal
 pg_meta_pub | public     | t_unique
 pg_meta_pub | public     | t_tricky

使用pg_get_publication_tables可以根据订阅的名字获取订阅表的OID

SELECT * FROM pg_get_publication_tables('pg_meta_pub');
SELECT p.pubname,
       n.nspname AS schemaname,
       c.relname AS tablename
FROM pg_publication p,
     LATERAL pg_get_publication_tables(p.pubname::text) gpt(relid),
     pg_class c
         JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.oid = gpt.relid;

同时，pg_publication_rel 也提供类似的信息，但采用的是多对多的OID对应视角，包含的是原始数据。

  oid  | prpubid | prrelid
-------+---------+---------
 20414 |   20413 |   20397
 20415 |   20413 |   20400
 20416 |   20413 |   20391
 20417 |   20413 |   20394

这两者的区别特别需要注意：当针对ALL TABLES发布时，pg_publication_rel中不会有具体表的OID，但是在pg_publication_tables中可以查询到实际纳入逻辑复制的表列表。所以通常应当以pg_publication_tables为准。

创建订阅时，数据库会先修改pg_publication目录，然后将发布表的信息填入pg_publication_rel。

订阅

订阅（Subscription） 是逻辑复制的下游。定义订阅的节点被称为 订阅者（Subscriber） 。

订阅定义了：如何连接到另一个数据库，以及需要订阅目标发布者上的哪些发布。

逻辑订阅者的行为与一个普通的PostgreSQL实例（主库）无异，逻辑订阅者也可以创建自己的发布，拥有自己的订阅者。

每个订阅者，都会通过一个 复制槽（Replication） 来接收变更，在初始数据复制阶段，可能会需要更多的临时复制槽。

逻辑复制订阅可以作为同步复制的备库，备库的名字默认就是订阅的名字，也可以通过在连接信息中设置application_name来使用别的名字。

只有超级用户才可以用pg_dump转储订阅的定义，因为只有超级用户才可以访问pg_subscription视图，普通用户尝试转储时会跳过并打印警告信息。

逻辑复制不会复制DDL变更，因此发布集中的表必须已经存在于订阅端上。只有普通表上的变更会被复制，视图、物化视图、序列号，索引这些都不会被复制。

发布与订阅端的表是通过完整限定名（如public.table）进行匹配的，不支持把变更复制到一个名称不同的表上。

发布与订阅端的表的列也是通过名称匹配的。列的顺序无关紧要，数据类型也不一定非得一致，只要两个列的文本表示兼容即可，即数据的文本表示可以转换为目标列的类型。订阅端的表可以包含有发布端没有的列，这些新列都会使用默认值填充。

管理订阅

CREATE SUBSCRIPTION用于创建订阅，DROP SUBSCRIPTION用于移除订阅，ALTER SUBSCRIPTION用于修改订阅。

订阅创建之后，可以通过ALTER SUBSCRIPTION 随时暂停与恢复订阅。

移除并重建订阅会导致同步信息丢失，这意味着相关数据需要重新进行同步。

CREATE SUBSCRIPTION subscription_name
    CONNECTION 'conninfo'
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

ALTER SUBSCRIPTION name CONNECTION 'conninfo'
ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( set_publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ENABLE
ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )
ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER SUBSCRIPTION name RENAME TO new_name

DROP SUBSCRIPTION [ IF EXISTS ] name;

subscription_parameter定义了订阅的一些选项，包括：

• copy_data(bool)：复制开始后，是否拷贝数据，默认为真
• create_slot(bool)：是否在发布者上创建复制槽，默认为真
• enabled(bool)：是否启用该订阅，默认为真
• connect(bool)：是否尝试连接到发布者，默认为真，置为假会把上面几个选项强制设置为假。
• synchronous_commit(bool)：是否启用同步提交，向主库上报自己的进度信息。
• slot_name：订阅所关联的复制槽名称，设置为空会取消订阅与复制槽的关联。

管理复制槽

每个活跃的订阅都会通过复制槽 从远程发布者接受变更。

通常这个远端的复制槽是自动管理的，在CREATE SUBSCRIPTION时自动创建，在DROP SUBSCRIPTION时自动删除。

在特定场景下，可能需要分别操作订阅与底层的复制槽：

• 创建订阅时，所需的复制槽已经存在。则可以通过create_slot = false关联已有复制槽。

• 创建订阅时，远端不可达或状态不明朗，则可以通过connect = false不访问远程主机，pg_dump就是这么做的。这种情况下，您必须在远端手工创建复制槽后，才能在本地启用该订阅。

• 移除订阅时，需要保留复制槽。这种情况通常是订阅者要搬到另一台机器上去，希望在那里重新开始订阅。这种情况下需要先通过ALTER SUBSCRIPTION解除订阅与复制槽点关联

• 移除订阅时，远端不可达。这种情况下，需要在删除订阅之前使用ALTER SUBSCRIPTION解除复制槽与订阅的关联。

  如果远端实例不再使用那么没事，然而如果远端实例只是暂时不可达，那就应该手动删除其上的复制槽；否则它将继续保留WAL，并可能导致磁盘撑爆。

订阅查询

订阅可以使用psql元命令\dRs查询。

# \dRs
     Name     |  Owner   | Enabled |  Publication
--------------+----------+---------+----------------
 pg_bench_sub | postgres | t       | {pg_bench_pub}

pg_subscription 订阅定义表

每一个逻辑订阅都会有一条记录，注意这个视图是跨数据库集簇范畴的，每个数据库中都可以看到整个集簇中的订阅信息。

只有超级用户才可以访问此视图，因为里面包含有明文密码（连接信息）。

oid             | 20421
subdbid         | 19356
subname         | pg_test_sub
subowner        | 10
subenabled      | t
subconninfo     | host=10.10.10.10 user=replicator password=DBUser.Replicator dbname=meta
subslotname     | pg_test_sub
subsynccommit   | off
subpublications | {pg_meta_pub}

• subenabled：订阅是否启用
• subconninfo ：因为包含敏感信息，会针对普通用户进行隐藏。
• subslotname：订阅使用的复制槽名称，也会被用作逻辑复制的源名称（Origin Name），用于除重。
• subpublications：订阅的发布名称列表。
• 其他状态信息：是否启用同步提交等等。

pg_subscription_rel 订阅内容表

pg_subscription_rel 记录了每张处于订阅中的表的相关信息，包括状态与进度。

• srrelid 订阅中关系的OID
• srsubstate，订阅中关系的状态：i 初始化中，d 拷贝数据中，s 同步已完成，r 正常复制中。
• srsublsn，当处于i|d状态时为空，当处于s|r状态时，远端的LSN位置。

创建订阅时

当一个新的订阅创建时，会依次执行以下操作：

• 将发布的信息存入 pg_subscription 目录中，包括连接信息，复制槽，发布名称，一些配置选项等。
• 连接至发布者，检查复制权限，（注意这里不会检查对应发布是否存在），
• 创建逻辑复制槽：pg_create_logical_replication_slot(name, 'pgoutput')
• 将复制集中的表注册到订阅端的 pg_subscription_rel 目录中。
• 执行初始快照同步，注意订阅测表中的原有数据不会被删除。

复制冲突

逻辑复制的行为类似于正常的DML操作，即使数据在用户节点上的本地发生了变化，数据也会被更新。如果复制来的数据违反了任何约束，复制就会停止，这种现象被称为 冲突（Conflict） 。

当复制UPDATE或DELETE操作时，缺失数据（即要更新/删除的数据已经不存在）不会产生冲突，此类操作直接跳过。

冲突会导致错误，并中止逻辑复制，逻辑复制管理进程会以5秒为间隔不断重试。冲突不会阻塞订阅端对复制集中表上的SQL。关于冲突的细节可以在用户的服务器日志中找到，冲突必须由用户手动解决。

日志中可能出现的冲突

解决冲突的方法，可以是改变订阅侧的数据，使其不与进入的变更相冲突，或者跳过与现有数据冲突的事务。

使用订阅对应的node_name与LSN位置调用函数pg_replication_origin_advance()可以跳过事务，pg_replication_origin_status系统视图中可以看到当前ORIGIN的位置。

局限性

逻辑复制目前有以下限制，或者说功能缺失。这些问题可能会在未来的版本中解决。

数据库模式和DDL命令不会被复制。存量模式可以通过pg_dump --schema-only手动复制，增量模式变更需要手动保持同步（发布订阅两边的模式不需要绝对相同不需要两边的模式绝对相同)。逻辑复制对于对在线DDL变更仍然可靠：在发布数据库中执行DDL变更后，复制的数据到达订阅者但因为表模式不匹配而导致复制出错停止，订阅者的模式更新后复制会继续。在许多情况下，先在订阅者上执行变更可以避免中间的错误。

序列号数据不会被复制。序列号所服务的标识列与SERIAL类型里面的数据作为表的一部分当然会被复制，但序列号本身仍会在订阅者上保持为初始值。如果订阅者被当成只读库使用，那么通常没事。然而如果打算进行某种形式的切换或Failover到订阅者数据库，那么需要将序列号更新为最新的值，要么通过从发布者复制当前数据（也许可以使用pg_dump -t *seq*），要么从表本身的数据内容确定一个足够高的值（例如max(id)+1000000）。否则如果在新库执行获取序列号作为身份的操作时，很可能会产生冲突。

逻辑复制支持复制TRUNCATE命令，但是在TRUNCATE由外键关联的一组表时需要特别小心。当执行TRUNCATE操作时，发布者上与之关联的一组表（通过显式列举或级连关联）都会被TRUNCATE，但是在订阅者上，不在订阅集中的表不会被TRUNCATE。这样的操作在逻辑上是合理的，因为逻辑复制不应该影响到复制集之外的表。但如果有一些不在订阅集中的表通过外键引用订阅集中被TRUNCATE的表，那么TRUNCATE操作就会失败。

大对象不会被复制

只有表能被复制（包括分区表），尝试复制其他类型的表会导致错误（视图，物化视图，外部表，Unlogged表）。具体来说，只有在pg_class.relkind = 'r'的表才可以参与逻辑复制。

复制分区表时默认按子表进行复制。默认情况下，变更是按照分区表的叶子分区触发的，这意味着发布上的每一个分区子表都需要在订阅上存在（当然，订阅者上的这个分区子表不一定是一个分区子表，也可能本身就是一个分区母表，或者一个普通表）。发布可以声明要不要使用分区根表上的复制标识取代分区叶表上的复制标识，这是PG13提供的新功能，可以在创建发布时通过publish_via_partition_root 选项指定。

触发器的行为表现有所不同。行级触发器会触发，但UPDATE OF cols类型的触发器不触发。而语句级触发器只会在初始数据拷贝时触发。

日志行为不同。即使设置log_statement = 'all'，日志中也不会记录由复制产生的SQL语句。

双向复制需要极其小心：互为发布与订阅是可行的，只要两遍的表集合不相交即可。但一旦出现表的交集，就会出现WAL无限循环。

同一实例内的复制：同一个实例内的逻辑复制需要特别小心，必须手工创建逻辑复制槽，并在创建订阅时使用已有的逻辑复制槽，否则会卡死。

只能在主库上进行：目前不支持从物理复制的从库上进行逻辑解码，也无法在从库上创建复制槽，所以从库无法作为发布者。但这个问题可能会在未来解决。

架构

逻辑复制始于获取发布者数据库上的快照，基于此快照拷贝表上的存量数据。一旦拷贝完成，发布者上的变更（增删改等）就会实时发送到订阅者上。

逻辑复制采用与物理复制类似的架构，是通过一个walsender和apply进程实现的。发布端端walsender进程会加载逻辑解码插件（pgoutput），并开始逻辑解码WAL日志。逻辑解码插件（Logical Decoding Plugin） 会读取WAL中的变更，按照发布的定义筛选变更，将变更转变为特定的形式，以逻辑复制协议传输出去。数据会按照流复制协议传输至订阅者一侧的apply进程，该进程会在接收到变更时，将变更映射至本地表上，然后按照事务顺序重新应用这些变更。

初始快照

订阅侧的表在初始化与拷贝数据期间，会由一种特殊的apply进程负责。这个进程会创建它自己的临时复制槽，并拷贝表中的存量数据。

一旦数据拷贝完成，这张表会进入到同步模式（pg_subscription_rel.srsubstate = 's'），同步模式确保了 主apply进程 可以使用标准的逻辑复制方式应用拷贝数据期间发生的变更。一旦完成同步，表复制的控制权会转交回 主apply进程，恢复正常的复制模式。

进程结构

逻辑复制的发布端会针对来自订阅端端每一条连接，创建一个对应的 walsender 进程，发送解码的WAL日志。在订阅测，则会

复制槽

当创建订阅时，

一条逻辑复制

逻辑解码

同步提交

逻辑复制的同步提交是通过Backend与Walsender之间的SIGUSR1通信完成的。

临时数据

逻辑解码的临时数据会落盘为本地日志快照。当walsender接收到walwriter发送的SIGUSR1信号时，就会读取WAL日志并生成相应的逻辑解码快照。当传输结束时会删除这些快照。

文件地址为：$PGDATA/pg_logical/snapshots/{LSN Upper}-{LSN Lower}.snap

监控

逻辑复制采用与物理流复制类似的架构，所以监控一个逻辑复制的发布者节点与监控一个物理复制主库差别不大。

订阅者的监控信息可以通过pg_stat_subscription视图获取。

pg_stat_subscription 订阅统计表

每个活跃订阅都会在这个视图中有至少一条 记录，即Main Worker（负责应用逻辑日志）。

Main Worker的relid = NULL，如果有负责初始数据拷贝的进程，也会在这里有一行记录，relid为负责拷贝数据的表。

subid                 | 20421
subname               | pg_test_sub
pid                   | 5261
relid                 | NULL
received_lsn          | 0/2A4F6B8
last_msg_send_time    | 2021-02-22 17:05:06.578574+08
last_msg_receipt_time | 2021-02-22 17:05:06.583326+08
latest_end_lsn        | 0/2A4F6B8
latest_end_time       | 2021-02-22 17:05:06.578574+08

• received_lsn ：最近收到的日志位置。
• lastest_end_lsn：最后向walsender回报的LSN位置，即主库上的confirmed_flush_lsn。不过这个值更新不太勤快，

通常情况下一个活跃的订阅会有一个apply进程在运行，被禁用的订阅或崩溃的订阅则在此视图中没有记录。在初始同步期间，被同步的表会有额外的工作进程记录。

pg_replication_slot 复制槽

postgres@meta:5432/meta=# table pg_replication_slots ;
-[ RECORD 1 ]-------+------------
slot_name           | pg_test_sub
plugin              | pgoutput
slot_type           | logical
datoid              | 19355
database            | meta
temporary           | f
active              | t
active_pid          | 89367
xmin                | NULL
catalog_xmin        | 1524
restart_lsn         | 0/2A08D40
confirmed_flush_lsn | 0/2A097F8
wal_status          | reserved
safe_wal_size       | NULL

复制槽视图中同时包含了逻辑复制槽与物理复制槽。逻辑复制槽点主要特点是：

• plugin字段不为空，标识了使用的逻辑解码插件，逻辑复制默认使用pgoutput插件。
• slot_type = logical，物理复制的槽类型为physical。
• datoid与database字段不为空，因为物理复制与集簇关联，而逻辑复制与数据库关联。

逻辑订阅者也会作为一个标准的 复制从库 ，出现于 pg_stat_replication 视图中。

pg_replication_origin 复制源

复制源

table pg_replication_origin_status;
-[ RECORD 1 ]-----------
local_id    | 1
external_id | pg_19378
remote_lsn  | 0/0
local_lsn   | 0/6BB53640

• local_id：复制源在本地的ID，2字节高效表示。
• external_id：复制源的ID，可以跨节点引用。
• remote_lsn：源端最近的提交位点。
• local_lsn：本地已经持久化提交记录的LSN

检测复制冲突

最稳妥的检测方法总是从发布与订阅两侧的日志中检测。当出现复制冲突时，发布测上可以看见复制连接中断

LOG:  terminating walsender process due to replication timeout
LOG:  starting logical decoding for slot "pg_test_sub"
DETAIL:  streaming transactions committing after 0/xxxxx, reading WAL from 0/xxxx

而订阅端则可以看到复制冲突的具体原因，例如：

logical replication worker PID 4585 exited with exit code 1
ERROR: duplicate key value violates unique constraint "pgbench_tellers_pkey","Key (tid)=(9) already exists.",,,,"COPY pgbench_tellers, line 31",,,,"","logical replication worker"

此外，一些监控指标也可以反映逻辑复制的状态：

例如：pg_replication_slots.confirmed_flush_lsn 长期落后于pg_cureent_wal_lsn。或者pg_stat_replication.flush_ag/write_lag 有显著增长。

安全

参与订阅的表，其Ownership与Trigger权限必须控制在超级用户所信任的角色手中（否则修改这些表可能导致逻辑复制中断）。

在发布节点上，如果不受信任的用户具有建表权限，那么创建发布时应当显式指定表名而非通配ALL TABLES。也就是说，只有当超级用户信任所有 可以在发布或订阅侧具有建表（非临时表）权限的用户时，才可以使用FOR ALL TABLES。

用于复制连接的用户必须具有REPLICATION权限（或者为SUPERUSER）。如果该角色缺少SUPERUSER与BYPASSRLS，发布者上的行安全策略可能会被执行。如果表的属主在复制启动之后设置了行级安全策略，这个配置可能会导致复制直接中断，而不是策略生效。该用户必须拥有LOGIN权限，而且HBA规则允许其访问。

为了能够复制初始表数据，用于复制连接的角色必须在已发布的表上拥有SELECT权限（或者属于超级用户）。

创建发布，需要在数据库中的CREATE权限，创建一个FOR ALL TABLES的发布，需要超级用户权限。

将表加入到发布中，用户需要具有表的属主权限。

创建订阅需要超级用户权限，因为订阅的apply进程在本地数据库中以超级用户的权限运行。

权限只会在建立复制连接时检查，不会在发布端读取每条变更记录时重复检查，也不会在订阅端应用每条记录时检查。

配置选项

逻辑复制需要一些配置选项才能正常工作。

在发布者一侧，wal_level 必须设置为logical，max_replication_slots最少需要设为 订阅的数量+用于表数据同步的数量。max_wal_senders最少需要设置为max_replication_slots + 为物理复制保留的数量，

在订阅者一侧，也需要设置max_replication_slots，max_replication_slots，最少需要设为订阅数。

max_logical_replication_workers最少需要配置为订阅的数量，再加上一些用于数据同步的工作进程数。

此外，max_worker_processes需要相应调整，至少应当为max_logical_replication_worker + 1。注意一些扩展插件和并行查询也会从工作进程的池子中获取连接使用。

配置参数样例

64核机器，1～2个发布与订阅，最多6个同步工作进程，最多8个物理从库的场景，一种样例配置如下所示：

首先决定Slot数量，2个订阅，6个同步工作进程，8个物理从库，所以配置为16。Sender = Slot + Physical Replica = 24。

同步工作进程限制为6，2个订阅，所以逻辑复制的总工作进程设置为8。

wal_level: logical                      # logical	
max_worker_processes: 64                # default 8 -> 64, set to CPU CORE 64
max_parallel_workers: 32                # default 8 -> 32, limit by max_worker_processes
max_parallel_maintenance_workers: 16    # default 2 -> 16, limit by parallel worker
max_parallel_workers_per_gather: 0      # default 2 -> 0,  disable parallel query on OLTP instance
# max_parallel_workers_per_gather: 16   # default 2 -> 16, enable parallel query on OLAP instance

max_wal_senders: 24                     # 10 -> 24
max_replication_slots: 16               # 10 -> 16 
max_logical_replication_workers: 8      # 4 -> 8, 6 sync worker + 1~2 apply worker
max_sync_workers_per_subscription: 6    # 2 -> 6, 6 sync worker

快速配置

首先设置发布侧的配置选项 wal_level = logical，该参数需要重启方可生效，其他参数的默认值都不影响使用。

然后创建复制用户，添加pg_hba.conf配置项，允许外部访问，一种典型配置是：

CREATE USER replicator REPLICATION BYPASSRLS PASSWORD 'DBUser.Replicator';

注意，逻辑复制的用户需要具有SELECT权限，在Pigsty中replicator已经被授予了dbrole_readonly角色。

host     all          replicator     0.0.0.0/0     md5
host     replicator   replicator     0.0.0.0/0     md5

然后在发布侧的数据库中执行：

CREATE PUBLICATION mypub FOR TABLE <tablename>;

然后在订阅测数据库中执行：

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=<pub_db> host=<pub_host> user=replicator' PUBLICATION mypub;

以上配置即会开始复制，首先复制表的初始数据，然后开始同步增量变更。

沙箱样例

以Pigsty标准4节点两集群沙箱为例，有两套数据库集群pg-meta与pg-test。现在将pg-meta-1作为发布者，pg-test-1作为订阅者。

PGSRC='postgres://dbuser_admin@meta-1/meta'           # 发布者
PGDST='postgres://dbuser_admin@node-1/test'           # 订阅者
pgbench -is100 ${PGSRC}                               # 在发布端初始化Pgbench
pg_dump -Oscx -t pgbench* -s ${PGSRC} | psql ${PGDST} # 在订阅端同步表结构

# 在发布者上创建**发布**，将默认的`pgbench`相关表加入到发布集中。
psql ${PGSRC} -AXwt <<-'EOF'
CREATE PUBLICATION "pg_meta_pub" FOR TABLE
  pgbench_accounts,pgbench_branches,pgbench_history,pgbench_tellers;
EOF

# 在订阅者上创建**订阅**，订阅发布者上的发布。
psql ${PGDST} <<-'EOF'
CREATE SUBSCRIPTION pg_test_sub
  CONNECTION 'host=10.10.10.10 dbname=meta user=replicator' 
  PUBLICATION pg_meta_pub;
EOF

复制流程

逻辑复制的订阅创建后，如果一切正常，逻辑复制会自动开始，针对每张订阅中的表执行复制状态机逻辑。

如下图所示。

当所有的表都完成复制，进入r（ready）状态时，逻辑复制的存量同步阶段便完成了，发布端与订阅端整体进入同步状态。

因此从逻辑上讲，存在两种状态机：表级复制小状态机与全局复制大状态机。每一个Sync Worker负责一张表上的小状态机，而一个Apply Worker负责一条逻辑复制的大状态机。

逻辑复制状态机

逻辑复制有两种Worker：Sync与Apply。Sync

因此，逻辑复制在逻辑上分为两个部分：每张表独自进行复制，当复制进度追赶至最新位置时，由

当创建或刷新订阅时，表会被加入到 订阅集 中，每一张订阅集中的表都会在pg_subscription_rel视图中有一条对应纪录，展示这张表当前的复制状态。刚加入订阅集的表初始状态为i，即initialize，初始状态。

如果订阅的copy_data选项为真（默认情况），且工作进程池中有空闲的Worker，PostgreSQL会为这张表分配一个同步工作进程，同步这张表上的存量数据，此时表的状态进入d，即拷贝数据中。对表做数据同步类似于对数据库集群进行basebackup，Sync Worker会在发布端创建临时的复制槽，获取表上的快照并通过COPY完成基础数据同步。

当表上的基础数据拷贝完成后，表会进入sync模式，即数据同步，同步进程会追赶同步过程中发生的增量变更。当追赶完成时，同步进程会将这张表标记为r（ready）状态，转交逻辑复制主Apply进程管理变更，表示这张表已经处于正常复制中。

2.4 等待逻辑复制同步

创建订阅后，首先必须监控 发布端与订阅端两侧的数据库日志，确保没有错误产生。

2.4.1 逻辑复制状态机

2.4.2 同步进度跟踪

数据同步（d）阶段可能需要花费一些时间，取决于网卡，网络，磁盘，表的大小与分布，逻辑复制的同步worker数量等因素。

作为参考，1TB的数据库，20张表，包含有250GB的大表，双万兆网卡，在6个数据同步worker的负责下大约需要6~8小时完成复制。

在数据同步过程中，每个表同步任务都会源端库上创建临时的复制槽。请确保逻辑复制初始同步期间不要给源端主库施加过大的不必要写入压力，以免WAL撑爆磁盘。

发布侧的 pg_stat_replication，pg_replication_slots，订阅端的pg_stat_subscription，pg_subscription_rel提供了逻辑复制状态的相关信息，需要关注。

psql ${PGDST} -Xxw <<-'EOF'
    SELECT subname, json_object_agg(srsubstate, cnt) FROM
    pg_subscription s JOIN
      (SELECT srsubid, srsubstate, count(*) AS cnt FROM pg_subscription_rel 
       GROUP BY srsubid, srsubstate) sr
    ON s.oid = sr.srsubid GROUP BY subname;
EOF

可以使用以下SQL确认订阅中表的状态，如果所有表的状态都显示为r，则表示逻辑复制已经成功建立，订阅端可以用于切换。

   subname   | json_object_agg
-------------+-----------------
 pg_test_sub | { "r" : 5 }

当然，最好的方式始终是通过监控系统来跟踪复制状态。

沙箱样例

以Pigsty标准4节点两集群沙箱为例，有两套数据库集群pg-meta与pg-test。现在将pg-meta-1作为发布者，pg-test-1作为订阅者。

通常逻辑复制的前提是，发布者上设置有wal_level = logical，并且有一个可以正常访问，具有正确权限的复制用户。

Pigsty的默认配置已经符合要求，且带有满足条件的复制用户replicator，以下命令均从元节点以postgres用户发起，数据库用户dbuser_admin，带有SUPERUSER权限。

PGSRC='postgres://dbuser_admin@meta-1/meta'        # 发布者
PGDST='postgres://dbuser_admin@node-1/test'        # 订阅者

准备逻辑复制

使用pgbench工具，在pg-meta集群的meta数据库中初始化表结构。

pgbench -is100 ${PGSRC}

使用pg_dump与psql 同步 pgbench* 相关表的定义。

pg_dump -Oscx -t pgbench* -s ${PGSRC} | psql ${PGDST}

创建发布订阅

在发布者上创建发布，将默认的pgbench相关表加入到发布集中。

psql ${PGSRC} -AXwt <<-'EOF'
CREATE PUBLICATION "pg_meta_pub" FOR TABLE
  pgbench_accounts,pgbench_branches,pgbench_history,pgbench_tellers;
EOF

在订阅者上创建订阅，订阅发布者上的发布。

psql ${PGDST} <<-'EOF'
CREATE SUBSCRIPTION pg_test_sub
  CONNECTION 'host=10.10.10.10 dbname=meta user=replicator' 
  PUBLICATION pg_meta_pub;
EOF

观察复制状态

当pg_subscription_rel.srsubstate全部变为r （准备就绪）状态后，逻辑复制就建立起来了。

$ psql ${PGDST} -c 'TABLE pg_subscription_rel;'
 srsubid | srrelid | srsubstate |  srsublsn
---------+---------+------------+------------
   20451 |   20433 | d          | NULL
   20451 |   20442 | r          | 0/4ECCDB78
   20451 |   20436 | r          | 0/4ECCDB78
   20451 |   20439 | r          | 0/4ECCDBB0

校验复制数据

可以简单地比较发布与订阅端两侧的表记录条数，与复制标识列的最大最小值来校验数据是否完整地复制。

function compare_relation(){
	local relname=$1
	local identity=${2-'id'}
	psql ${3-${PGPUB}} -AXtwc "SELECT count(*) AS cnt, max($identity) AS max, min($identity) AS min FROM ${relname};"
	psql ${4-${PGSUB}} -AXtwc "SELECT count(*) AS cnt, max($identity) AS max, min($identity) AS min FROM ${relname};"
}
compare_relation pgbench_accounts aid
compare_relation pgbench_branches bid
compare_relation pgbench_history  tid
compare_relation pgbench_tellers  tid

更近一步的验证可以通过在发布者上手工创建一条记录，再从订阅者上读取出来。

$ psql ${PGPUB} -AXtwc 'INSERT INTO pgbench_accounts(aid,bid,abalance) VALUES (99999999,1,0);'
INSERT 0 1
$ psql ${PGSUB} -AXtwc 'SELECT * FROM pgbench_accounts WHERE aid = 99999999;'
99999999|1|0|

现在已经拥有一个正常工作的逻辑复制了。下面让我们来通过一系列实验来掌握逻辑复制的使用与管理，探索可能遇到的各种离奇问题。

逻辑复制实验

将表加入已有发布

CREATE TABLE t_normal(id BIGSERIAL PRIMARY KEY,v  TIMESTAMP); -- 常规表，带有主键
ALTER PUBLICATION pg_meta_pub ADD TABLE t_normal; -- 将新创建的表加入到发布中

如果这张表在订阅端已经存在，那么即可进入正常的逻辑复制流程：i -> d -> s -> r。

如果向发布加入一张订阅端不存在的表？那么新订阅将会无法创建。已有订阅无法刷新，但可以保持原有复制继续进行。

如果订阅还不存在，那么创建的时候会报错无法进行：在订阅端找不到这张表。如果订阅已经存在，无法执行刷新命令：

ALTER SUBSCRIPTION pg_test_sub REFRESH PUBLICATION;

如果新加入的表没有任何写入，已有的复制关系不会发生变化，一旦新加入的表发生变更，会立即产生复制冲突。

将表从发布中移除

ALTER PUBLICATION pg_meta_pub ADD TABLE t_normal;

从发布移除后，订阅端不会有影响。效果上就是这张表的变更似乎消失了。执行订阅刷新后，这张表会从订阅集中被移除。

另一种情况是重命名发布/订阅中的表，在发布端执行表重命名时，发布端的发布集会立刻随之更新。尽管订阅集中的表名不会立刻更新，但只要重命名后的表发生任何变更，而订阅端没有对应的表，那么会立刻出现复制冲突。

同理，在订阅端重命名表时，订阅的关系集也会刷新，但因为发布端的表没有对应物了。如果这张表没有变更，那么一切照旧，一旦发生变更，立刻出现复制冲突。

直接在发布端DROP此表，会顺带将该表从发布中移除，不会有报错或影响。但直接在订阅端DROP表则可能出现问题，DROP TABLE时该表也会从订阅集中被移除。如果发布端此时这张表上仍有变更产生，则会导致复制冲突。

所以，删表应当先在发布端进行，再在订阅端进行。

两端列定义不一致

发布与订阅端的表的列通过名称匹配，列的顺序无关紧要。

订阅端表的列更多，通常不会有什么影响。多出来的列会被填充为默认值（通常是NULL）。

特别需要注意的是，如果要为多出来的列添加NOT NULL约束，那么一定要配置一个默认值，否则变更发生时违反约束会导致复制冲突。

订阅端如果列要比发布端更少，会产生复制冲突。在发布端添加一个新列并不会立刻导致复制冲突，随后的第一条变更将导致复制冲突。

所以在执行加列DDL变更时，可以先在订阅者上先执行，然后在发布端进行。

列的数据类型不需要完全一致，只要两个列的文本表示兼容即可，即数据的文本表示可以转换为目标列的类型。

这意味着任何类型都能转换成TEXT类型，BIGINT 只要不出错，也可以转换成INT，不过一旦溢出，还是会出现复制冲突。

复制身份与索引的正确配置

表上的复制标识配置，与表上有没有索引是两件独立的事。尽管各种排列组合都是可能的，然而在实际使用中只有三种可行的情况，其他情况都无法正常完成逻辑复制的功能（如果不报错，通常也是侥幸）

• 表上有主键，使用默认的 default 复制标识，不需要额外配置。
• 表上没有主键，但是有非空唯一索引，显式配置 index 复制标识。
• 表上既没有主键也没有非空唯一索引，显式配置full复制标识（运行效率低，仅作为兜底方案）

在所有情况下，INSERT都可以被正常复制。x代表DELETE|UPDATE所需关键信息缺失无法正常完成。

最好的方式当然是事前修复，为所有的表指定主键，以下查询可以用于找出缺失主键或非空唯一索引的表：

SELECT quote_ident(nspname) || '.' || quote_ident(relname) AS name, con.ri AS keys,
       CASE relreplident WHEN 'd' THEN 'default' WHEN 'n' THEN 'nothing' WHEN 'f' THEN 'full' WHEN 'i' THEN 'index' END AS replica_identity
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid, LATERAL (SELECT array_agg(contype) AS ri FROM pg_constraint WHERE conrelid = c.oid) con
WHERE relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema', 'monitor', 'repack', 'pg_toast')
ORDER BY 2,3;

注意，复制身份为nothing的表可以加入到发布中，但在发布者上对其执行UPDATE|DELETE会直接导致报错。

其他问题

Q：逻辑复制准备工作

Q：什么样的表可以逻辑复制？

Q：监控逻辑复制状态

Q：将新表加入发布

Q：没有主键的表加入发布？

Q：没有复制身份的表如何处理？

Q：ALTER PUB的生效方式

Q：在同一对 发布者-订阅者 上如果存在多对订阅，且发布包含的表重叠？

Q：订阅者和发布者的表定义有什么限制？

Q：pg_dump是如何处理订阅的

Q：什么情况下需要手工管理订阅复制槽？

PostgreSQL 宏观查询优化之 pg_stat_statements

在线业务数据库中，慢查询不仅影响终端用户体验，还会浪费系统资源、拉高资源饱和度、导致死锁和事务冲突，增加数据库连接压力，导致主从复制延迟等问题。因此，查询优化是 DBA 的核心工作内容之一。

在查询优化这条路上，有两种不同的方法：

宏观优化：整体分析工作负载，对其进行剖分下钻，自上而下地识别并改进其中表现最糟糕的部分。

微观优化：分析并改进一条特定的查询，这便需要记录慢查询日志，掌握 EXPLAIN 的玄机，领悟执行计划的奥妙。

今天我们先来说说前者，宏观优化有三个主要目标与动机：

减少资源消耗：降低资源饱和的风险，优化CPU/内存/IO，通常以查询总耗时/总IO作为优化目标。

改善用户体验：最常见的优化目标，在OLTP系统中通常以降低查询平均响应时间作为优化目标。

平衡工作负载：确保不同查询组之间的资源使用/性能表现的比例关系得当。

实现这些目标的关键在于数据支撑，但是数据从哪里来？

—— pg_stat_statements！

扩展插件：PGSS

pg_stat_statements，以下简称 PGSS ，是践行观宏之道的核心工具。

PGSS 出自 PostgreSQL 全球开发组官方之手，以第一方扩展插件的形式，随数据库内核本体一并发行，提供了跟踪 SQL 查询语句级别指标的方法。

PostgreSQL 生态中有许许多多的扩展，但如果说有哪一个是“必选”的，我必定会毫不犹豫的回答：PGSS。这也是在 Pigsty 中，我们宁愿“自作主张”，也要默认启用并主动加载的两个扩展之一。（另一个是用于微观优化的 auto_explain）

PGSS 需要在 shared_preload_library 中显式指定加载，并在数据库中通过 CREATE EXTENSION 显式创建。创建扩展后即可通过视图 pg_stat_statements 访问查询的统计信息。

在 PGSS 中，系统中的每一类查询（即抽取变量后，执行计划相同的查询）都会被分配一个查询ID，紧接着是调用次数，执行总耗时，以及各种其他指标，其完整模式定义如下（PG15+）：

CREATE TABLE pg_stat_statements
(
    userid                 OID,     -- （标签值）执行此语句的用户 OID（标签值）
    dbid                   OID,     -- （标签值）此语句所在的数据库 OID（标签值）
    toplevel               BOOL,    -- （标签值）此语句是否是顶层 SQL 语句（标签值）
    queryid                BIGINT,  -- （标签值）查询ID：标准化查询的哈希值（标签值）
    query                  TEXT,    -- （标签值）标准化查询语句的文本内容
    plans                  BIGINT,  -- （累积量）此语句被 PLAN 的次数
    total_plan_time        FLOAT,   -- （累积量）此语句花费在 PLAN 上的总时长
    min_plan_time          FLOAT,   -- （测量值）PLAN 的最小时长
    max_plan_time          FLOAT,   -- （测量值）PLAN 的最大时长
    mean_plan_time         FLOAT,   -- （测量值）PLAN 的平均时长
    stddev_plan_time       FLOAT,   -- （测量值）PLAN 时间的标准差
    calls                  BIGINT,  -- （累积量）此语句被调用执行的次数
    total_exec_time        FLOAT,   -- （累积量）此语句花费在执行上的总时长
    min_exec_time          FLOAT,   -- （测量值）执行的最小时长
    max_exec_time          FLOAT,   -- （测量值）执行的最大时长
    mean_exec_time         FLOAT,   -- （测量值）执行的平均时长
    stddev_exec_time       FLOAT,   -- （测量值）执行时间的标准差
    rows                   BIGINT,  -- （累积量）执行此语句返回的总行数
    shared_blks_hit        BIGINT,  -- （累积量）命中的共享缓冲区总块数
    shared_blks_read       BIGINT,  -- （累积量）读取的共享缓冲区总块数
    shared_blks_dirtied    BIGINT,  -- （累积量）写脏的共享缓冲区总块数
    shared_blks_written    BIGINT,  -- （累积量）写入磁盘的共享缓冲区总块数
    local_blks_hit         BIGINT,  -- （累积量）命中的本地缓冲区总块数
    local_blks_read        BIGINT,  -- （累积量）读取的本地缓冲区总块数
    local_blks_dirtied     BIGINT,  -- （累积量）写脏的本地缓冲区总块数
    local_blks_written     BIGINT,  -- （累积量）写入磁盘的本地缓冲区总块数
    temp_blks_read         BIGINT,  -- （累积量）读取的临时缓冲区总块数
    temp_blks_written      BIGINT,  -- （累积量）写入磁盘的临时缓冲区总块数
    blk_read_time          FLOAT,   -- （累积量）读取块花费的总时长
    blk_write_time         FLOAT,   -- （累积量）写入块花费的总时长
    wal_records            BIGINT,  -- （累积量）生成 WAL 的记录总数
    wal_fpi                BIGINT,  -- （累积量）生成的 WAL全页镜像总数
    wal_bytes              NUMERIC, -- （累积量）生成的 WAL 字节总数
    jit_functions          BIGINT,  -- （累积量）JIT 编译的函数数量
    jit_generation_time    FLOAT,   -- （累积量）生成 JIT 字节码的总时长
    jit_inlining_count     BIGINT,  -- （累积量）函数被内联的次数
    jit_inlining_time      FLOAT,   -- （累积量）花费在内联函数上的总时长
    jit_optimization_count BIGINT,  -- （累积量）查询被 JIT优化的次数
    jit_optimization_time  FLOAT,   -- （累积量）花费在JIT优化上的总时长
    jit_emission_count     BIGINT,  -- （累积量）代码被 JIT Emit的次数
    jit_emission_time      FLOAT,   -- （累积量）花费在 JIT Emit上的总时长
    PRIMARY KEY (userid, dbid, queryid, toplevel)
);

PGSS 视图的 SQL 定义（PG 15+版本）

PGSS 也有一些局限性：首先，正在执行中的查询语句并不会纳入这里的统计，而需要从 pg_stat_activity 中查看获取。其次，执行失败的查询（例如，因为 statement_timeout 超时被取消的语句）也不会被计入这里的统计 —— 这是错误分析要解决的问题，而不是查询优化所关心的目标。

最后，查询标识符 queryid 的稳定性需要特别注意：当数据库二进制版本和系统数据目录完全相同时，同一类查询会具有相同的 queryid （即在物理复制的主从上，同类查询的 queryid 默认是相同的），然而对于逻辑复制则不然。但用户不应当对这一性质抱有过度的依赖与假设。

原始数据

PGSS 视图中的列可以分为三类：

描述性的标签列（Label）：查询ID（queryid）、数据库 ID（dbid）、用户（userid），一个顶层查询标记，和标准化的查询文本（query）。

测量性的指标（Gauge）：与最小、最大、均值标准差有关的八列统计量，以 min，max，mean，stddev 作为前缀，以 plan_time 与 exec_time 作为后缀。

累积性的指标（Counter）：除了上面八列与标签列的其他指标，例如 calls、rows 等，最重要、最有用的指标都在这一类里。

首先解释一下 queryid：queryid 是查询语句被解析后，剥离常量后生成规范化查询的哈希值，因此可以用来标识同一类查询。不同的查询语句可能有着同样的 queryid （规范化后结构一样），同样的查询语句也可能有着不同的 queryid （例如因为 search_path 不同，导致实际查询的表不懂）。

同样的查询可能会在不同的数据库中被不同的用户所执行。因此在 PGSS 视图中，queryid，dbid，userid，toplevel 四个标签列，共同组成了唯一标识一条记录的“主键”。

对于指标列而言，测量性质的指标（GAUGE） 主要是执行时间与计划时间相关的八个统计量，然而用户没有办法很好地控制这些统计量的统计范围，所以实用价值并不大。

真正重要的指标是累积性的指标（Counter），例如：

calls ：此查询组发生了多少次调用。

total_exec_time + total_plan_time：查询组累计耗费时间。

rows：查询组累计返回了多少行。

shared_blks_hit + shared_blks_read：缓冲池累计命中和读取操作次数。

wal_bytes：此组中的查询累计生成的 WAL 字节数。

blk_read_time 和 blk_write_time：累计花费在块读写IO上的时间

这里，最有意义的指标是 calls 与 total_exec_time，可以用于计算查询组的核心指标 QPS （吞吐量）与 RT（延迟/响应时间），但其他的指标也很有参考价值。

可视化展现 PGSS 视图的某个查询组快照

要解读累积性指标数据，只有某一个时刻的数据是不够的。我们需要对比至少两个时刻的快照，才能得到有意义的结论。

作为特例，如果您感兴趣的范围正好是从统计周期伊始（通常是启用此扩展时）至今，那么确实不需要对比“两个快照”。但用户感兴趣的时间粒度通常并不会这么粗放，而往往是以分钟、小时、天为单位。

根据多个 PGSS 查询组快照计算历史时序指标

好在类似 Pigsty 监控系统这样的工具会定期（默认每隔10s）截取头部查询（耗时Top256）的快照。有了许多不同类型的累积指标 M（etrics）在不同时刻的快照之后，我们就能计算出某个累积性指标的三种重要派生指标：

dM/dt ：指标 M 基于时间的微分，即每秒的增量。

dM/dc：指标 M 基于调用次数的微分，即每次调用的平均增量。

%M：指标 M 在整个工作负载中所占的百分比。

这三类指标正好与宏观优化的三类目标相对应，对时间的微分 dM/dt 揭示了每秒资源使用量，通常用于减少资源消耗的优化目标。对调用次数的微分 dM/dc 揭示了每次调用的资源使用量，通常用于改善用户体验的优化目标。而百分比指标 %M 展示了查询组在整个工作负载中所占的百分比，通常用于平衡工作负载的优化目标。

对时间微分

让我们首先来看第一类指标：对时间的微分。在这里，我们可以使用的指标 M 包括：calls，total_exec_time，rows，wal_bytes，shared_blks_hit + shared_blks_read，以及 blk_read_time + blk_write_time。其他的指标也有参考意义，但让我们从最重要的开始。

可视化展现对时间的微分指标 dM/dt

计算这些指标的方式其实很简单，我们只需要：

• 首先计算两个快照之间的指标值 M 的差值：M2 - M1
• 然后计算两个快照之间的时间差值：t2 - t1
• 最终计算 (M2 - M1) / (t2 - t1) 即可

生产环境通常会使用 5s，10s，15s，30s，60s 这样的数据采样间隔。对于负载分析通常会使用 1m， 5m，15m 作为常用的分析窗口大小。

例如，当我们计算 QPS 时，就会分别计算最近 1分钟，5分钟，15分钟的 QPS。窗口越长曲线就越平稳，更能反映长期变化趋势；但是会隐藏短期波动细节，不利于发现瞬时异常波动，所以不同粒度的指标需要结合来看。

展示特定查询组 1/5/15 分钟窗口下的 QPS

如果您使用 Pigsty / Prometheus 来采集监控数据，那么可以使用 PromQL 简单地完成这些计算工作。例如，计算所有查询最近1分钟的 QPS 指标，使用以下语句就可以了： rate(pg_query_calls{}[1m])

QPS

当 M 是 calls 时，对时间求导的结果是 QPS，它的单位是每秒查询数（req/s），这是一个非常基础的指标。查询 QPS 属于吞吐量指标，直接反应了业务施加的负载状况，如果一个查询的吞吐量过高（例如，10000+）或者过低（例如，1-），有可能是值得关注的。

QPS：1/5/15 分钟 µ/CV， ±1/3σ分布

如果我们把所有查询组的 QPS 指标累加起来（且没超过PGSS的收集范围），就会得到所谓的 “全局QPS”。另一种获得全局 QPS 的方式是在客户端打点，在类似 Pgbouncer 的连接池中间件上采集，或者使用 ebpf 探测。但都不如 PGSS 方便。

请注意，QPS 指标并不具备负载意义上的横向可比性。不同查询组可能有着同样的 QPS，而单个查询的耗时却天差地别。甚至同一个查询组在不同时间点上产生的负载水平，也可能因为执行计划不同而发生巨大变化。每秒执行时长是一个更好的衡量负载的指标。

每秒执行时长

当 M 是 total_exec_time （+ total_plan_time，可选 ）时，我们就会得到宏观优化中最重要的指标之一：在查询组上耗费的的执行时间，有意思的是，这个导数的单位是 秒/每秒，所以分子分母相互约掉了，使得它实际上是一个无量纲的指标。

这个指标的涵义是：服务器每秒钟花费多少秒来处理这个查询组中的查询，例如 2 s/s 意味着服务器每秒花费两秒执行时间在这组查询上；对于多核CPU，这当然是有可能的：把两个CPU核的全部时间都拿来就行了。

每秒执行时长：1/5/15 分钟均值

因此这里的值也可以理解为一个百分比：可以超过 100%，在这种视角下，它是一个类似于主机 load1, load5, load15 的指标，揭示了该查询组产生的负载水平。如果除以 CPU 核数，甚至可以得到归一化的查询负载贡献度指标。

但是我们需要注意的是，执行时间中包括了等待锁，等待I/O的时间。所以确实可能出现这样的情况：查询执行时间很长，但却没有对 CPU 负载产生影响。所以如果要精细分析慢查询，我们还要参考等待事件来进一步分析才行。

每秒行数

当 M 是 rows 时，我们会得到每秒该查询组返回的行数，单位是行/每秒（rows/s）。例如 10000 rows/s 意味着该类查询每秒向客户端吐出1万行数据。返回的行需要耗费客户端的处理资源，当我们需要检视应用客户端的数据处理压力时，这是一个非常有参考意义的指标。

每秒返回的行数：1/5/15 分钟均值

共享缓冲区访问带宽

当 M 是 shared_blks_hit + shared_blks_read 时，我们会得到每秒命中/读取的共享缓冲区块数，如果将其乘以默认块大小 8KiB（极少情况下有可能会是其他的大小，例如32KiB），我们就会得到一类查询“访问”内存磁盘的带宽：单位是字节/秒。

举个例子，如果某一类查询每秒访问50万次共享缓冲区，折合 3.8 GiB/s 的内部访问数据流：那么这就是一个显著负载，也许会是一个很好的优化候选项。也许你应该检查一下这个查询，看看它是否配得上这些“资源消耗”。

共享缓冲区访问带宽与缓冲区命中率

另一个值得参考的衍生指标是缓冲区命中率：即 hit / (hit + read) ，它可以用于分析性能变化的可能原因 —— 缓存未命中。当然，重复访问同一个共享缓冲池里的块，并不会真的重新读取，即使真的去读取，也不一定是读取磁盘，有可能是读内存中的FS Cache。所以这里只是一个参考值，但它确实是一个非常重要的宏观查询优化参考指标。

WAL日志量

当 M 是 wal_bytes 时，我们得到了该查询生成 WAL 的速率，单位是字节/每秒（B/s）。这个指标是在 PostgreSQL 13 新引入的，可以用来定量揭示查询产生的 WAL 大小：写入的 WAL 越多越快，刷写磁盘、物理复制/逻辑复制、日志归档的压力就会越大。

一个典型的例子是：BEGIN; DELETE FROM xxx; ROLLBACK; 。这样的事务删了很多数据，产生了大量 WAL 却没有执行任何有用的工作，通过这个指标可以将其揪出来。

WAL字节率：1/5/15 分钟均值

这里有两个注意事项：上面我们说过，PGSS 无法跟踪执行失败的语句，但这里事务虽然 ROLLBACK 失败了，但是语句却是成功执行了的，所以会被 PGSS 跟踪记录。

第二件事是：在 PostgreSQL 中并非仅仅是 INSERT/UPDATE/DELETE 会产生 WAL 日志，SELECT 操作也有可能产生 WAL 日志，因为 SELECT 可能会修改元组上的标记（Hint Bit）让页面校验和出现变化，触发 WAL 日志写入。

甚至存在这种可能，如果读取负载非常大，它会有较大概率导致 FPI 镜像生成，产生可观的 WAL 日志量。你可以通过进一步检查 wal_fpi 指标。

共享缓冲区写脏/写回带宽

对于 13 以下的版本，共享缓冲区写脏/写回带宽指标可以作为一个近似下位替代，用于分析查询组的写入负载特征。

I/O耗时

当 M 是 blks_read_time + blks_write_time ，我们会得到查询组花费在块 I/O 上的耗时比例，单位是 “秒/每秒”，与每秒执行时长指标一样，它也反映出一样操作占用的时间比例。

I/O 耗时对于分析查询毛刺原因很有帮助

因为 PostgreSQL 会使用操作系统提供的 FS Cache，所以即使这里执行了块读取/写入，可能在文件系统层面上仍然是发生在内存中的缓冲操作。所以它只能作为一个参考指标，使用时需要谨慎，需要与主机节点上的磁盘 I/O 监控相互对照。

对时间微分的指标 dM/dt，可以展现出一个数据库实例/集群内部工作负载的全貌，对于优化资源使用的场景来说尤其有用。但是如果您的优化目标是改善用户体验，那么可能另一组指标 —— 对调用次数的微分 dM/dc，会更有参考意义。

对调用次数微分

上面我们已经计算了六类重要指标对于时间的微分，另一类衍生指标计算方式是对 “调用次数” 进行微分，也就是分母从时间差变成了 QPS。

这类指标重要性相比前者甚至更高，因为它提供了直接关乎用户体验的几个核心指标，比如最重要的 —— 查询响应时间 （RT，Response Time），或曰 延迟（Latency）。

计算这些指标的方式也很简单，我们只需要：

• 计算两个快照之间的指标值 M 的差值：M2 - M1
• 然后计算两个快照之间的 calls 差值：c2 - c1
• 然后计算 (M2 - M1) / (c2 - c1) 即可

对于 PromQL 实现来说，对于调用次数的微分指标 dM/dc，可以用“对时间的微分指标 dM/dt” 计算得到。例如要计算 RT，就可以使用 每秒执行时长 / 每秒查询数 ，两指标相除即可：

rate(pg_query_exec_time{}[1m]) / rate(pg_query_calls{}[1m])

dM/dt 可以用于计算 dM/dc

调用次数

当 M 是 calls 时，对自己微分没有任何意义（结果会恒为 1）。

平均延迟/响应时间/RT

当 M 是 total_exec_time 时，对调用次数求导的结果是 RT，或响应时间/延迟。它的单位是秒（s）。RT 直接反映了用户体验，是宏观性能分析中最重要的指标。这个指标的含义是：此查询组在服务器上的平均查询响应时间。如果条件允许启用 pg_stat_statements.track_planning，还可以加上 total_plan_time 一起计算，结果会更精确更具有代表性。

RT：1/5/15 分钟 µ/CV， ±1/3σ分布

这里要特别强调两种特殊情况：第一：PGSS不跟踪失败/执行中的语句；第二：PGSS的统计数据受（pg_stat_statements.max）参数限制，可能出现部分采样偏差。尽管有这些局限性，但想要获取至关重要的查询语句组延迟数据，PGSS 毫无疑问是最为稳妥可靠的来源。正如上面所述，在其他观测点位也有办法采集查询 RT 数据，但会麻烦得多。

你可以在客户端侧打点，采集语句执行时间，通过指标或者日志上报；你也可以尝试使用 ebpf 来探测语句 RT，这对基础设施和工程师要求会比较高。Pgbouncer 和 PostgreSQL （14+） 倒是也提供了 RT 指标，只可惜粒度都是数据库级别，没有一个能做到 PGSS 查询语句组级别的指标收集。

RT：语句级/连接池级/数据库级

不同于 QPS 这样的吞吐量指标，RT 是具有横向可比性的：例如某个查询组平时的 RT 都在1毫秒内，那么超过 10ms 的事件应当被视作严重的偏差进行分析。

当出现故障时， RT 视图对于定位原因也很有帮助：如果所有查询整体 RT 变慢，那么最有可能与资源不足有关。如果只是特定查询组的 RT 发生变化，那就更有可能是某些慢查询导致了问题，应当进一步调查分析。如果 RT 变化的时间点与应用发布部署吻合，则应当考虑是否要回滚这些部署。

此外，在性能分析，压力测试，基准测试时，RT 也是最重要的指标。你可以通过对比典型查询在不同环境（例如不同PG大版本、不同硬件、不同配置参数）下的延迟表现来评估系统的性能，并以此为依据不断对系统性能进行调整与改进。

RT 是如此重要，以至于 RT 本身又会衍生出许多下游指标来：1分钟/5分钟/15分钟的均值µ与标准差σ自然必不可少；过去15分钟的 ±σ，±3σ 可以用来衡量 RT 的波动范围，过去1小时的 95，99 分位点也很有参考价值。

RT 是评估 OLTP工作负载的核心指标，怎么强调它的重要性都不为过。

平均返回行数

当 M 是 rows 时，我们会得到每次查询平均返回的行数，单位是行/每查询。对于 OLTP 工作负载来说，典型查询模式为点查，即每次查询返回几条数据。

按照主键查询单条记录，平均返回行数稳定为1

如果一个查询组每次查询向客户端吐出几百甚至成千上万行记录，那么应当对其进行审视。如果这是有意而为之的设计，比如批量加载任务/数据转储，那么不需要做什么。如果这是由应用/客户端发起的请求，那么可能存在错误，比如语句缺少 LIMIT 限制，查询缺少分页设计，这样的查询应该进行调整修复。

平均共享缓冲区读取/命中

当 M 是 shared_blks_hit + shared_blks_read 时，我们会得到每条查询“命中”与“读取”共享缓冲区的平均次数，如果将其乘以默认块大小 8KiB，我们就会得到这类查询每次执行的“带宽”，单位是 B/s：每次查询平均会访问/读取多少 MB 数据 ？

按照主键查询单条记录，平均返回行数稳定为1

查询平均访问的数据量通常与平均返回的行数相匹配，如果你的查询平均只返回了几行，却访问了成M上G的数据块，那你就需要特别注意了：这样的查询对于数据冷热状态非常敏感，如果所有的块都在缓冲区中，它的性能可能还说的过去，但如果从磁盘冷启动，执行时间可能会出现戏剧性的变化。

当然，不要忘记 PostgreSQL 双缓存问题，所谓“读取”的数据可能已经在操作系统文件系统层面被缓存过一次了。所以你需要与操作系统监控指标，或者 pg_stat_kcache ，pg_stat_io 这些系统视图相互参照进行分析。

另一种值得关注的模式是此指标的突变，这通常意味着该查询组的执行计划可能出现了翻转/劣化，非常值得关注与进一步研究。

平均WAL日志量

当 M 是 wal_bytes 时，我们得到了每条查询平均生成 WAL 的大小，这是 PostgreSQL 13 新引入的字段。这个指标可以衡量查询的变更足迹大小，并计算读写比例等重要评估参数。

稳定的QPS却有着周期性WAL波动，可推断是 FPI 的影响

另一个用途是优化检查点/Checkpoint：如果你观察到此指标周期性的起伏（周期约等于 checkpoint_timeout），那么可以通过调整检查点间距，来优化查询产生 WAL 的数量。

对调用次数进行微分的指标 dM/dc，可以展现出一类查询的工作负载特性，对于优化用户体验来说非常有用。特别是 RT 乃是性能优化的黄金指标，怎样强调其重要性都不为过。

dM/dc 这样的指标为我们提供类似重要的绝对值指标，但如果想要找出哪些查询的优化潜在收益最大，还需要用到 %M 百分比指标。

百分比指标

现在我们来研究第三类指标，百分比指标。即某个查询组相对于整体工作负载所占的比例。

百分比指标 M% 为我们提供了某个查询组相对于整体工作负载的比例，帮助我们在频次、时间、I/O时间/次数上时识别出“主要参与者”，找出潜在优化收益最大的候选查询组，作为优先级评定的重要依据。

常用百分比指标 %M 一览

举个例子，如果某个查询组有 1000 QPS 的绝对值，看上去不少；但如果它只占整个工作负载的 3%，那么优化此查询的收益与优先级就没那么高了；反之，如果它占据了整个工作负载的 50% 还要多 —— 如果你有办法把它优化掉就可以砍掉整个实例吞吐量的半壁江山，优化它的优先级就会非常之高。

常见的优化策略是这样的：首先把所有查询组分别按照上面提到的重要指标：calls，total_exec_time，rows，wal_bytes，shared_blks_hit + shared_blks_read，以及 blk_read_time + blk_write_time 在一段时间内的 dM/dt 值进行排序取 TopN （比如 N=10 或者更多），加入优化候选列表中。

按照特定标准，选取待优化的 TopSQL

然后，对于优化候选列表中的每个查询组，依次分析其 dM/dc 指标，结合具体的查询语句与慢查询日志/等待事件进行分析，决定这是不是一个值得优化的查询。对于决定（Plan）进行优化的查询，就可以使用后续篇 “微观优化” 将要介绍的技巧进行调优（Do），并使用监控系统评估优化的效果（Check），总结分析后进入下一个 PDCA 戴明循环，持续进行管理优化。

除了对指标取 TopN 之外，还可以使用可视化的方式。可视化非常有助于从工作负载中识别 “主要贡献者”，复杂的判断算法可能还远比不上人类DBA对监控图形模式的直觉。想要形成比例感，我们可以借助饼图，树图或者堆叠的时序图。

将所有查询组的 QPS 进行堆叠

例如，我们可以使用饼图来标识过去1小时内耗时/IO使用最大的查询，使用二维树图（大小代表总耗时，颜色代表平均RT）来展示一个额外的维度。并用堆叠时序图来展示比例随时间的变化关系。

我们也可以直接分析当下的 PGSS 快照，按照不同的关注点进行排序，按照您自己的标准选择有待优化的查询即可。

I/O 耗时对于分析查询毛刺原因很有帮助

总结

最后，让我们对上面的内容做一个总结。

PGSS提供了丰富的指标，其中最重要的累积指标可以使用三种方式进行加工处理：

dM/dt ：指标 M 基于时间的微分，揭示了每秒资源使用量，通常用于减少资源消耗的优化目标。

dM/dc：指标 M 基于调用次数的微分，揭示了每次调用的资源使用量，通常用于改善用户体验的优化目标。

%M ：百分比指标展示了查询组在整个工作负载中所占的百分比，通常用于平衡工作负载的优化目标。

通常，我们会根据 %M ：百分比指标 Top 查询选择高价值的备选优化查询，并使用 dM/dt *dM/dc* 指标进行进一步的评估，确认是否有优化空间和可行性，并评估优化后的效果。如此往复，不断循环。

理解了宏观优化的方法论后，我们就可以用这样的方法去定位优化慢查询了。这里给出了一个具体的 《 利用监控系统诊断PG慢查询》的例子。在下一篇中，我们将介绍关于 PostgreSQL查询 微观优化 的经验技巧。

参考

[1] PostgreSQL HowTO: pg_stat_statements by Nikolay Samokhvalov

[2] pg_stat_statements

[3] 利用监控系统诊断PG慢查询

[4] 如何用Pigsty监控现有PostgreSQL (RDS/PolarDB/自建)？

[5] Pigsty v2.5 发布：Ubuntu/Debian支持与监控改版/新扩展

[6] PostgreSQL监控系统Pigsty概述

如何用 pg_filedump 抢救数据？

备份是DBA的生命线 —— 但如果你的 PostgreSQL 数据库已经爆炸了又没有备份，那么该怎么办呢？也许 pg_filedump 可以帮到你！

最近遇到了一个比较离谱的活儿，情况是这样的：有个用户的 PostgreSQL 数据库损坏了，是 Gitlab 自己拉起的 PostgreSQL。没有从库，没有备份，也没有 dump。跑在拿 SSD 当透明缓存的BCACHE上，断电后起不来了。

但这还没完，接连经受了几轮摧残之后，它彻底歇菜了：首先是因为忘了挂BCACHE盘，导致 Gitlab重新初始化了一遍新的数据库集群；然后是因为各种原因隔离失效，在同一个集簇目录上运行两个数据库进程烤糊了数据目录；接着是运行 pg_resetwal 不带参数把数据库推回起源点，最后是让空数据库跑了一阵子，然后把烤糊前的临时备份移除了。

看到这个 Case 我确实有点无语：这都成一团浆糊了还恢复个什么，目测只能从底层二进制文件直接抽取数据来恢复了。我建议他去找个数据恢复公司碰碰运气吧，也帮忙问了一圈儿，但是一大堆数据恢复公司里，几乎没有几个有 PostgreSQL 数据恢复服务的，有的也是比较基础的那种问题处理，碰上这种情况都说只能随缘试试。

数据恢复报价通常是按文件数量来收费的，一个文件从 ¥1000 ～ ¥5000 不等。Gitlab库里几千个文件，按表算的话大概有 1000张表，全恢复完几十万可能不至于，但十几万肯定是没跑了。可一天过去了也没人接，这着实让我感觉蛋疼：要是没人能接这活，岂不是显得 PG 社区没人了？

我想了一下，这活看着挺蛋疼，但也挺有挑战趣味的，咱死马当活马医，修不好不收钱就是 —— 不试试咋知道行不行呢？所以就接了自己上了。

工具

工欲善其事，必先利其器。数据恢复首先当然是要找有没有趁手的工具：pg_filedump 就是一把不错的武器，它可以用来从 PostgreSQL 数据页面中抽取原始二进制数据，许多低层次的工作可以交给它。

这个工具可以用 make 三板斧编译安装，当然需要先安装对应大版本的 PostgreSQL 才行。Gitlab 默认使用的是 PG 13，所以确保对应版本的 pg_config 在路径中后直接编译即可。

git clone https://github.com/df7cb/pg_filedump
cd pg_filedump && make && sudo make install

pg_filedump 的使用方式并不复杂，你把数据文件喂给他，告诉它这张表每一列的类型，它就能帮你解读出来。比如第一步，我们就得知道这个数据库集簇中有哪几个数据库。这个信息记录在系统视图 pg_database 中。这是一张系统层面的表，位于 global 目录中，在集群初始化时会分配固定的 OID 1262，所以对应的物理文件通常是： global/1262。

vonng=# select 'pg_database'::RegClass::OID;
 oid
------
 1262

这张系统视图里有不少字段，但我们主要关心的是前两个： oid 和 datname ，datname 是数据库的名称，oid 则可以用于定位数据库目录位置。以用 pg_filedump 把这张表解出来看一看， -D 参数可以告诉 pg_filedump 如何解释这张表里每一行的二进制数据。你可以指定每个字段的类型，用逗号分隔，~ 表示后面的部分都忽略不要。

可以看到，每一行数据都以 COPY 开始，这里我们发现了目标数据库 gitlabhq_production，其 OID 为 16386 。所以这个数据库内的所有文件都应当位于 base/16386 子目录中。

恢复数据字典

知道了要恢复的数据文件目录，下一步就是解出数据字典来，这里面有四张重要的表需要关注：

•pg_class：包含了所有表的重要元数据•pg_namespace：包含了模式的元数据•pg_attribute：包含了所有的列定义•pg_type：包含了类型的名称

其中 pg_class 是最为重要，不可或缺的一张表。其他几张系统视图属于 Nice to have：能让我们的工作更加简单一些。所以，我们首先尝试恢复这张表。

pg_class 是数据库级别的系统视图，默认有着 OID = 1259 ，所以 pg_class 对应的文件应当是： base/16386/1259，在 gitlabhq_production 对应数据库目录下。

这里说句题外话：熟悉 PostgreSQL 原理的朋友知道：实际底层存储数据的文件名（RelFileNode）虽然默认与表的 OID 保持一致，但是一些操作可能会改变这一点，在这种情况下，你可以用 pg_filedump -m pg_filenode.map 解析数据库目录下的映射文件，找到 OID 1259 对应的 Filenode。当然这里两者是一致的，就表过不提了。

我们根据 pg_class 的表结构定义（注意要使用对应PG大版本的表结构），解析其二进制文件： pg_filedump -D ‘oid,name,oid,oid,oid,oid,oid,oid,oid,int,real,int,oid,bool,bool,char,char,smallint,smallint,bool,bool,bool,bool,bool,bool,char,bool,oid,xid,xid,text,text,text’ -i base/16386/1259

然后就可以看到解析出来的数据了。这里的数据是 \t 分隔的单行记录，与 PostgreSQL COPY 命令默认使用的格式相同。所以你可以用脚本 grep 收集过滤，掐掉每行开头的 COPY ，并重新灌入一张真正的数据库表来细看。

在数据恢复时需要注意许多细节，其中第一条就是：你需要处理被删除的行。怎么识别呢？使用 -i 参数打印每一行的元数据，元数据里有一个 XMAX 字段。如果某一行元组被某个事务删除了，那么这条记录的 XMAX 就会被设置为该事务的 XID 事务号。所以如果某一行的 XMAX 不是零，就意味着这是一条被删除的记录，不应当输出到最终的结果中。

这里的 XMAX 代表这是条被删除的记录

有了 pg_class 数据字典之后，你就可以清楚地找到其他表，包括系统视图的 OID 对应关系了。用同样的办法可以恢复 pg_namespace ，pg_attribute ，pg_type 这三张表。有了这四张表就可以干什么呢？

你可以用 SQL 生成每张表的输入路径，自动拼出每一列的类型作为 -D 参数，生成临时结果表的 Schema。总而言之，可以用编程自动化的方式，自动生成所有需要完成的任务。

SELECT  id, name, nspname, relname, nspid, attrs, fields, has_tough_type,
        CASE WHEN toast_page > 0 THEN toast_name ELSE NULL END AS toast_name, relpages, reltuples, path
FROM
    (
        SELECT n.nspname || '.' || c.relname AS "name", n.nspname, c.relname, c.relnamespace AS nspid, c.oid AS id, c.reltoastrelid AS tid,
               toast.relname AS toast_name, toast.relpages AS toast_page,
               c.relpages, c.reltuples, 'data/base/16386/' || c.relfilenode::TEXT AS path
        FROM meta.pg_class c
                 LEFT JOIN meta.pg_namespace n ON c.relnamespace = n.oid
           , LATERAL (SELECT * FROM meta.pg_class t WHERE t.oid = c.reltoastrelid) toast
        WHERE c.relkind = 'r' AND c.relpages > 0
          AND c.relnamespace IN (2200, 35507, 35508)
        ORDER BY c.relnamespace, c.relpages DESC
    ) z,
    LATERAL ( SELECT string_agg(name,',') AS attrs,
                     string_agg(std_type,',') AS fields,
                     max(has_tough_type::INTEGER)::BOOLEAN AS has_tough_type
              FROM meta.pg_columns WHERE relid = z.id ) AS columns;

这里需要注意，pg_filedump -D 参数支持的数据类型名称是有严格限定的标准名称的，所以你必须把 boolean 转为 bool，INTEGER 转为 int。如果你想解析的数据类型不在下面这个列表中，可以首先尝试使用 TEXT 类型，例如表示IP地址的 INET 类型就可以用 TEXT 的方式解析。

bigint bigserial bool char charN date float float4 float8 int json macaddr name numeric oid real serial smallint smallserial text time timestamp timestamptz timetz uuid varchar varcharN xid xml

但确实会有其他的一些特殊情况需要额外的处理，比如 PostgreSQL 中的 ARRAY 数组类型，后面会详细介绍。

恢复一张普通表

恢复普通数据表和恢复一张系统目录表并没有本质区别：只不过 Catalog 的模式和信息都是公开的标准化的，而待恢复的数据库模式则不一定。

Gitlab 也属于一个开源的很有知名度的软件，所以找到它的数据库模式定义并不是一件难事。如果是一个普通的业务系统，那么多费点功夫也可以从 pg_catalog 中还原出原始 DDL 。

知道了 DDL 定义，我们就可以使用 DDL 中每一列的数据类型，来解释二进制文件中的数据了。下面，我们用 public.approval_merge_request_rules 这张 Gitlab 中的普通表为例，演示如何恢复这样一张普通数据表。

create table approval_project_rules
(
    id                        bigint,
    created_at                timestamp with time zone,
    updated_at                timestamp with time zone,
    project_id                integer,
    approvals_required        smallint,
    name                      varchar,
    rule_type                 smallint,
    scanners                  text[],
    vulnerabilities_allowed   smallint,
    severity_levels           text[],
    report_type               smallint,
    vulnerability_states      text[],
    orchestration_policy_idx  smallint,
    applies_to_all_protected_branches              boolean,
    security_orchestration_policy_configuration_id bigint,
    scan_result_policy_id                          bigint
);

首先，我们要将这里的类型转换成 pg_filedump 可以识别的类型，这里涉及到类型映射的问题：如果你有不确定的类型，比如上面的 text[] 字符串数组字段，就可以先用 text 类型占位替代，也可以直接用 ~ 忽略：

bigint,timestamptz,timestamptz,int,smallint,varchar,smallint,text,smallint,text,smallint,text,smallint,bool,bigint,bigint

当然这里有第一个知识点就是 PostgreSQL 的元组列布局是有顺序的，这个顺序保存在系统视图 pg_attribute 里面的 attrnum 中，而表中每一列的类型ID则保存在 atttypid 字段中，而为了获取类型的英文名称，你又需要通过类型ID引用 pg_type 系统视图（当然系统默认类型都有固定ID，也可以直接用ID映射）。综上，为了获取表中物理记录的解释方法，你至少需要用到上面提到的那四张系统字典表。

有了这张表上列的顺序与类型之后，并且知道这张表的二进制文件位置之后，你就可以利用这个信息翻译二进制数据了。

pg_filedump -i -f -D 'bigint,...,bigint' 38304

输出时结果建议添加 -i 与 -f 选项，前者会打印每一行的元数据（需要根据 XMAX 判断这一行有没有被删除）；后者会打印原始二进制数据上下文（这一点对于处理 pg_filedump 解决不了的复杂数据是必要的）。

正常情况下，每一条记录都会以 COPY: 或 Error: 开头，前者代表提取成功，后者代表部分成功，或者失败。如果是失败，会有各种各样的原因，需要分别处理。对于成功的数据，你可以直接把它拿出来，每一行就是一条数据，用 \t 分隔，把 \N 替换为 NULL，处理好写入到临时表中保存待用即可。

当然魔鬼其实都在细节里，要是数据恢复真这么容易就好了。

魔鬼在细节中

在处理数据数据恢复时，有许多小细节需要关注，这里我提几个重要的点。

首先是 TOAST 字段的处理。TOAST 是“ The Oversized-Attribute Storage Technique ”的缩写，即超标属性存储技术。如果你发现解析出来的字段内容是 (TOASTED)，那就说明这个字段因为太长，被切片转移到另外一张专用的表 —— TOAST 表中了。

如果某张表里有可能 TOAST 的字段，它就会有一张对应的 TOAST 表，在 pg_class 中用 reltoastrelid 标识其 OID。TOAST 其实也可以看做一张普通的表来处理，所以你可以用一样的方法把 TOAST 数据解析出来，拼接回去，再填入到原表中，这里就不展开了。

第二个问题是复杂类型，正如上一节所说， pg_filedump README里列出了支持的类型，但类似数组这样的类型就需要进行额外的二进制解析处理了。

举个例子，当你转储数组二进制时，看到的结果可能是一串儿 \0\0 。这是因为 pg_filedump 直接把处理不了的复杂类型给吐出来了。当然这里就会带来一些额外的问题 —— 字符串里的零值会让你的插入报错，所以你的解析脚本需要处理好这种问题，当遇到一个解析错误的复杂列时，应该先做个标记占个坑，把二进制值现场给保留下来，留给后面的步骤去具体处理。

这里我们来看个具体的例子：还是以上面 public.approval_merge_request_rules 表为例。我们可以从吐出来的数据，二进制视图，以及 ASCII 视图里面看到一些零星的字符串：critical，unknown 之类的东西，掺杂在一串 \0 与二进制控制字符中。没错，这就是一个字符串数组的二进制表示。PostgreSQL 中的数组允许任意类型任意深度的嵌套，所以这里的数据结构会有一点点复杂。

例如，图片中标色的地方对应的数据是一个包含三个字符串的数组：{unknown,high,critical}::TEXT[] 。01 代表这是一个一位数组，紧跟着空值位图，以及代表数组元素的类型OID 的 0x00000019 ，0x19 十进制值为 25 对应 pg_type 中的 text类型，说明这里是一个字符串数组（如果是 0x17 则说明是整型数组）。紧接着是这个数组第一维的维度 0x03，因为这个数组只有一维，三个元素；接下来的 1 告诉我们数组第一维度的起始偏移量在哪儿。再后面才是挨着的三个字符串结构了：由4字节的长度打头（要右移两位处理标记未），接着才是字符串内容，还要考虑布局对齐与填充的问题。

总的来说，你需要对照着源代码实现去挖掘，而这里有无穷无尽的细节：可变长度，空值位图，字段压缩，线外存储，以及大小端序，稍有不慎，你解出来的东西就是一团没用的浆糊。

你可以选择直接用 Python 脚本去记录的上下文中解析原始二进制回补数据，或者在 pg_filedump 源代码中注册新的类型与回调处理函数，复用 PG 提供的 C 解析函数，无论哪一种都称不上是轻松。

好在 PostgreSQL 本身已经提供了一些C语言的辅助函数 & 宏可以帮助你完成大部分工作，而且幸运的是 Gitlab 中的数组都是一维数组，类型也仅限于整型数组与字符串数组，其他带复杂类型的数据页也可以从其他表中重建，所以总体工作量还是可以接受的 。

后记

这个活儿折腾了我两天，掏粪细节就不展开了，我估计读者也不会感兴趣。总之经过了一系列处理，校正，补对之后，数据恢复的工作终于完成了！除了有几张表里有几条损坏的数据之外，其他的数据都成功解出来了。好家伙，整整一千张表啊！

我以前也弄过一些数据恢复的活儿，大多数情况都还比较简单，数据坏块儿，控制文件/CLOG损坏，或者是被挖矿病毒种了勒索木马（往Tablespace里写了几个垃圾文件），但炸的这么彻底的Case我还是第一次弄。之所以敢接这个活，也是因为我对PG内核还是有些了解的，知道这些繁琐的实现细节。只要你知道这是一个工程上可解的问题，那么即使过程再脏再累也不会担心完不成。

尽管有些缺陷，但 pg_filedump 还是一个不错的工具，后面我可能会考虑完善一下它，让它对各种数据类型都有完整的支持，这样就不用再自己写一堆 Python 小脚本来处理各种繁琐的细节了。在弄完这个案例后，我已经把 pg_filedump 打好了 PG 12 - 16 x EL 7 - 9 上的 RPM 包放在 Pigsty 的 Yum源中，默认收录在 Pigsty 离线软件包里，目前已经在 Pigsty v2.4.1 中实装交付了。我衷心希望您永远也用不上这个扩展，但如果你真的碰上需要它的场景时，我也希望它就在你的手边可以开箱即用。

最后我还是想说一句，许多软件都需要数据库，但数据库的安装部署维护是一件很有门槛的活儿。Gitlab 拉起的 PostgreSQL 质量已经算是相当不错的了，但面对这种情况依然束手无策，更不用提那些土法手造 docker 镜像的简陋单机实例了。一场大故障，就能让一个企业积累的代码数据、CI/CD流程、Issue/PR/MR 记录灰飞烟灭。我真的建议您好好检视一下自己的数据库系统，至少请定期做个备份吧！

Gitlab 的企业版和社区版的核心区别就在于它底下的 PG 有没有高可用和监控。而开箱即用的 PostgreSQL 发行版 —— Pigsty 也可以为您更好地解决这些问题，却完全开源免费，分文不取：无论是高可用，PITR，还是监控系统一应俱全：下次再遇到这种问题时，就可以自动切换/一键回滚，游刃有余得多。之前我们自己的 Gitlab, Jira, Confluence 等软件都跑在上面，如果您有类似需求，倒是不妨试一下哦。

PG中的本地化排序规则

为什么Pigsty在初始化Postgres数据库时默认指定了locale=C与encoding=UTF8

答案其实很简单，除非真的明确知道自己会用到LOCALE相关功能，否则就根本不应该配置C.UTF8之外的任何字符编码与本地化排序规则选项。特别是`

关于字符编码的部分，之前写过一篇文章专门介绍，这里表过不提。今天专门说一下LOCALE（本地化）的配置问题。

如果说服务端字符编码配置因为某些原因配置为UTF8之外的值也许还情有可原，那么LOCALE配置为C之外的任何选就是无可救药了。因为对于PostgreSQL来说，LOCALE不仅仅是控制日期和钱怎么显示这一类无伤大雅的东西，而是会影响到某些关键功能的使用。

错误的LOCALE配置可能导致几倍到十几倍的性能损失，还会导致LIKE查询无法在普通索引上使用。而设置LOCALE=C一点也不会影响真正需要本地化规则的使用场景。所以官方文档给出的指导是：“如果你真正需要LOCALE，才去使用它”。

不幸的是，在PostgreSQLlocale与encoding的默认配置取决于操作系统的配置，因此C.UTF8可能并不是默认的配置，这就导致了很多人误用LOCALE而不自知，白白折损了大量性能，也导致了某些数据库特性无法正常使用。

太长；不看

• 强制使用UTF8字符编码，强制数据库使用C的本地化规则。
• 使用非C本地化规则，可能导致涉及字符串比较的操作开销增大几倍到几十倍，对性能产生显著负面影响
• 使用非C本地化规则，会导致LIKE查询无法使用普通索引，容易踩坑雪崩。
• 使用非C本地化规则的实例，可以通过text_ops COLLATE "C"或text_pattern_ops建立索引，支持LIKE查询。

LOCALE是什么

我们经常能在操作系统和各种软件中看到 LOCALE（区域） 的相关配置，但LOCALE到底是什么呢？

LOCALE支持指的是应用遵守文化偏好的问题，包括字母表、排序、数字格式等。LOCALE由很多规则与定义组成，包括：

一个LOCALE就是一组规则，LOCALE通常会用语言代码 + 国家代码的方式来命名。例如中国大陆使用的LOCALE zh_CN就分为两个部分：zh是 语言代码，CN 是国家代码。现实世界中，一种语言可能有多个国家在用，一个国家内也可能存在多种语言。还是以中文和中国为例：

中国（COUNTRY=CN）相关的语言LOCALE有：

• zh：汉语：zh_CN
• bo：藏语：bo_CN
• ug：维语：ug_CN

讲中文（LANG=zh）的国家或地区相关的LOCAL有：

• CN 中国：zh_CN
• HK 香港：zh_HK
• MO 澳门：zh_MO
• TW 台湾：zh_TW
• SG 新加坡：zh_SG

LOCALE的例子

我们可以参考一个典型的Locale定义文件：Glibc提供的 zh_CN

这里截取一小部分展示，看上去好像都是些鸡零狗碎的格式定义，月份星期怎么叫啊，钱和小数点怎么显示啊之类的东西。

但这里有一个非常关键的东西，叫做LC_COLLATE，即排序方式（Collation），会对数据库行为有显著影响。

LC_CTYPE
copy "i18n"
translit_start
include  "translit_combining";""
translit_end
class	"hanzi"; /
<U4E00>..<U9FA5>;/
<UF92C>;<UF979>;<UF995>;<UF9E7>;<UF9F1>;<UFA0C>;<UFA0D>;<UFA0E>;/
<UFA0F>;<UFA11>;<UFA13>;<UFA14>;<UFA18>;<UFA1F>;<UFA20>;<UFA21>;/
<UFA23>;<UFA24>;<UFA27>;<UFA28>;<UFA29>
END LC_CTYPE

LC_COLLATE
copy "iso14651_t1_pinyin"
END LC_COLLATE

LC_TIME
% 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月
mon           "<U4E00><U6708>";/
     "<U4E8C><U6708>";/
     "<U4E09><U6708>";/
     "<U56DB><U6708>";/
...
% 星期日, 星期一, 星期二, 星期三, 星期四, 星期五, 星期六
day           "<U661F><U671F><U65E5>";/
     "<U661F><U671F><U4E00>";/
     "<U661F><U671F><U4E8C>";/
...
week          7;19971130;1
first_weekday 2
% %Y年%m月%d日 %A %H时%M分%S秒
d_t_fmt       "%Y<U5E74>%m<U6708>%d<U65E5> %A %H<U65F6>%M<U5206>%S<U79D2>"
% %Y年%m月%d日
d_fmt         "%Y<U5E74>%m<U6708>%d<U65E5>"
% %H时%M分%S秒
t_fmt         "%H<U65F6>%M<U5206>%S<U79D2>"
% 上午, 下午
am_pm         "<U4E0A><U5348>";"<U4E0B><U5348>"
% %p %I时%M分%S秒
t_fmt_ampm    "%p %I<U65F6>%M<U5206>%S<U79D2>"
% %Y年 %m月 %d日 %A %H:%M:%S %Z
date_fmt      "%Y<U5E74> %m<U6708> %d<U65E5> %A %H:%M:%S %Z"
END LC_TIME

LC_NUMERIC
decimal_point "."
thousands_sep ","
grouping      3
END LC_NUMERIC

LC_MONETARY
% ￥
currency_symbol    "<UFFE5>"
int_curr_symbol    "CNY "

比如zh_CN提供的LC_COLLATE使用了iso14651_t1_pinyin排序规则，这是一个基于拼音的排序规则。

下面通过一个例子来介绍LOCALE中的COLLATION如何影响Postgres的行为。

排序规则一例

创建一张包含7个汉字的表，然后执行排序操作。

CREATE TABLE some_chinese(
    name TEXT PRIMARY KEY
);
INSERT INTO some_chinese VALUES 
('阿'),('波'),('磁'),('得'),('饿'),('佛'),('割');

SELECT * FROM some_chinese ORDER BY name;

执行以下SQL，按照默认的C排序规则对表中的记录排序。可以看到，这里实际上是按照字符的ascii|unicode 码位 进行排序的。

vonng=# SELECT name, ascii(name) FROM some_chinese ORDER BY name COLLATE "C";
 name | ascii
------+-------
 佛   | 20315
 割   | 21106
 得   | 24471
 波   | 27874
 磁   | 30913
 阿   | 38463
 饿   | 39295

但这样基于码位的排序对于中国人来说可能没有任何意义。例如新华字典在收录汉字时，就不会使用这种排序方式。而是采用zh_CN 所使用的 拼音排序 规则，按照拼音比大小。如下所示：

 SELECT * FROM some_chinese ORDER BY name COLLATE "zh_CN";
 name
------
 阿
 波
 磁
 得
 饿
 佛
 割

可以看到，按照zh_CN排序规则排序得到的结果，就是拼音顺序abcdefg，而不再是不知所云的Unicode码位排序。

当然这个查询结果取决于zh_CN 排序规则的具体定义，像这样的排序规则并不是数据库本身定义的，数据库本身提供的排序规则就是C（或者其别名POSIX）。COLLATION的来源，通常要么是操作系统，要么是glibc，要么是第三方的本地化库（例如icu），所以可能因为不同的实质定义出现不同的效果。

但代价是什么？

PostgreSQL中使用非C或非POSIX LOCALE的最大负面影响是：

特定排序规则对涉及字符串大小比较的操作有巨大的性能影响，同时它还会导致无法在LIKE查询子句中使用普通索引。

另外，C LOCALE是由数据库本身确保在任何操作系统与平台上使用的，而其他的LOCALE则不然，所以使用非C Locale的可移植性更差。

性能损失

接下来让我们考虑一个使用LOCALE排序规则的例子， 我们有Apple Store 150万款应用的名称，现在希望按照不同的区域规则进行排序。

-- 创建一张应用名称表，里面有中文也有英文。
CREATE TABLE app(
    name TEXT PRIMARY KEY
);
COPY app FROM '/tmp/app.csv';

-- 查看表上的统计信息
SELECT
    correlation , -- 相关系数 0.03542578 基本随机分布
    avg_width ,   -- 平均长度25字节
    n_distinct    -- -1，意味着1508076个记录没有重复
FROM pg_stats WHERE tablename = 'app';

-- 使用不同的排序规则进行一系列的实验
SELECT * FROM app;
SELECT * FROM app order by name; 
SELECT * FROM app order by name COLLATE "C";
SELECT * FROM app order by name COLLATE "en_US";
SELECT * FROM app order by name COLLATE "zh_CN"; 

相当令人震惊的结果，使用C和zh_CN的结果能相差十倍之多：

下面是实验5对应的详细执行计划，即使配置了足够大的内存，依然会溢出到磁盘执行外部排序。尽管如此，显式指定LOCALE的实验都出现了此情况，因此可以横向对比出C与zh_CN的性能差距来。

另一个更有对比性的例子是比大小。

这里，表中的所有的字符串都会和World比一下大小，相当于在表上进行150万次特定规则比大小，而且也不涉及到磁盘IO。

SELECT count(*) FROM app WHERE name > 'World';
SELECT count(*) FROM app WHERE name > 'World' COLLATE "C";
SELECT count(*) FROM app WHERE name > 'World' COLLATE "en_US";
SELECT count(*) FROM app WHERE name > 'World' COLLATE "zh_CN";

尽管如此，比起C LOCALE来，zh_CN 还是费了接近3倍的时长。

如果说排序可能是O(n2)次比较操作有10倍损耗 ，那么这里的O(n)次比较3倍开销也基本能对应上。我们可以得出一个初步的粗略结论：

比起C Locale来，使用zh_CN或其他Locale可能导致几倍的额外性能开销。

除此之外，错误的Locale不仅仅会带来性能损失，还会导致功能损失。

功能缺失

除了性能表现糟糕外，另一个令人难以接受的问题是，使用非C的LOCALE，LIKE查询走不了普通索引。

还是以刚才的实验为例，我们分别在使用C和en_US作为默认LOCALE创建的数据库实例上执行以下查询：

SELECT * FROM app WHERE name LIKE '中国%';

找出所有以“中国”两字开头的应用。

在使用C的库上

该查询能正常使用app_pkey索引，利用主键B树的有序性加速查询，约2毫秒内执行完毕。

postgres@meta:5432/meta=# show lc_collate;
 C

postgres@meta:5432/meta=# EXPLAIN SELECT * FROM app WHERE name LIKE '中国%';
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Only Scan using app_pkey on app  (cost=0.43..2.65 rows=1510 width=25)
   Index Cond: ((name >= '中国'::text) AND (name < '中图'::text))
   Filter: (name ~~ '中国%'::text)
(3 rows)

在使用en_US的库上

我们发现，这个查询无法利用索引，走了全表扫描。查询劣化至70毫秒，性能恶化了三四十倍。

vonng=# show lc_collate;
 en_US.UTF-8

vonng=# EXPLAIN SELECT * FROM app WHERE name LIKE '中国%';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on app  (cost=0.00..29454.95 rows=151 width=25)
   Filter: (name ~~ '中国%'::text)

为什么？

因为索引（B树索引）的构建，也是建立在序的基础上，也就是等值和比大小这两个操作。

然而，LOCALE关于字符串的等价规则有一套自己的定义，例如在Unicode标准中就定义了很多匪夷所思的等价规则（毕竟是万国语言，比如多个字符复合而成的字符串等价于另一个单体字符，详情参考 现代字符编码 一文）。

因此，只有最朴素的C LOCALE，才能够正常地进行模式匹配。C LOCALE的比较规则非常简单，就是挨个比较 字符码位，不玩那一套花里胡哨虚头巴脑的东西。所以，如果您的数据库不幸使用了非C的LOCALE，那么在执行LIKE查询时就没有办法使用默认的索引了。

解决办法

对于非C LOCALE的实例，只有建立特殊类型的索引，才能支持此类查询：

CREATE INDEX ON app(name COLLATE "C");
CREATE INDEX ON app(name text_pattern_ops);

这里使用 text_pattern_ops运算符族来创建索引也可以用来支持LIKE查询，这是专门用于支持模式匹配的运算符族，从原理上讲它会无视 LOCALE，直接基于 逐个字符 比较的方式执行模式匹配，也就是使用C LOCALE的方式。

因此在这种情况下，只有基于text_pattern_ops操作符族建立的索引，或者基于默认的text_ops但使用COLLATE "C"' 的索引，才可以用于支持LIKE查询。

vonng=# EXPLAIN ANALYZE SELECT * FROM app WHERE name LIKE '中国%';

Index Only Scan using app_name_idx on app  (cost=0.43..1.45 rows=151 width=25) (actual time=0.053..0.731 rows=2360 loops=1)
   Index Cond: ((name ~>=~ '中国'::text) AND (name ~<~ '中图'::text))
   Filter: (name ~~ '中国%'::text COLLATE "en_US.UTF-8")

建立完索引后，我们可以看到原来的LIKE查询可以走索引了。

LIKE无法使用普通索引这个问题，看上去似乎可以通过额外创建一个text_pattern_ops索引来曲线解决。但这也意味着原本可以直接利用现成的PRIMARY KEY或UNIQUE约束自带索引解决的问题，现在需要额外的维护成本与存储空间。

对于不熟悉这一问题的开发者来说，很有可能因为错误的LOCALE配置，导致本地没问题的模式结果在线上因为没有走索引而雪崩。（例如本地使用C，但生产环境用了非C LOCALE）。

兼容性

假设您在接手时数据库已经使用了非C的LOCALE（这种事相当常见），现在您在知道了使用非C LOCALE的危害后，决定找个机会改回来。

那么有哪些地方需要注意呢？具体来讲，Locale的配置影响PostgreSQL以下功能：

1. 使用LIKE子句的查询。

2. 任何依赖特定LOCALE排序规则的查询，例如依赖拼音排序作为结果排序依据。

3. 使用大小写转换相关功能的查询，函数upper、lower和initcap

4. to_char函数家族，涉及到格式化为本地时间时。

5. 正则表达式中的大小写不敏感匹配模式（SIMILAR TO ,~）。

如果不放心，可以通过pg_stat_statements列出所有涉及到以下关键词的查询语句进行手工排查：

LIKE|ILIKE                   -- 是否使用了模式匹配
SIMILAR TO | ~ | regexp_xxx  -- 是否使用了 i 选项
upper, lower, initcap        -- 是否针对其他带有大小写模式的语言使用（西欧字符之类）
ORDER BY col                 -- 按文本类型列排序时，是否依赖特定排序规则？（例如按照拼音）

兼容性修改

通常来说，C LOCALE在功能上是其他LOCALE配置的超集，总是可以从其他LOCALE切换为C。如果您的业务没有使用这些功能，通常什么都不需要做。如果使用本地化规则特性，则总是可以通过**显式指定COLLATE**的方式，在C LOCALE下实现相同的效果。

SELECT upper('a' COLLATE "zh_CN");  -- 基于zh_CN规则执行大小写转换
SELECT  '阿' < '波';                 -- false, 在默认排序规则下  阿(38463) > 波(27874)
SELECT  '阿' < '波' COLLATE "zh_CN"; -- true, 显式使用中文拼音排序规则： 阿(a) < 波(bo)

目前唯一已知的问题出现在扩展pg_trgm上。

PG复制标识详解（Replica Identity）

引子：土法逻辑复制

复制身份的概念，服务于 逻辑复制。

逻辑复制的基本工作原理是，将逻辑发布相关表上对行的增删改事件解码，复制到逻辑订阅者上执行。

逻辑复制的工作方式有点类似于行级触发器，在事务执行后对变更的元组逐行触发。

假设您需要自己通过触发器实现逻辑复制，将一章表A上的变更复制到另一张表B中。通常情况下，这个触发器的函数逻辑通常会长这样：

-- 通知触发器
CREATE OR REPLACE FUNCTION replicate_change() RETURNS TRIGGER AS $$
BEGIN
  IF    (TG_OP = 'INSERT') THEN 
  -- INSERT INTO tbl_b VALUES (NEW.col);
  ELSIF (TG_OP = 'DELETE') THEN 
	-- DELETE tbl_b WHERE id = OLD.id;
  ELSIF (TG_OP = 'UPDATE') THEN 
	-- UPDATE tbl_b SET col = NEW.col,... WHERE id = OLD.id;
  END IF;
END; $$ LANGUAGE plpgsql;

触发器中会有两个变量OLD与NEW，分别包含了变更记录的旧值与新值。

• INSERT操作只有NEW变量，因为它是新插入的，我们直接将其插入到另一张表即可。
• DELETE操作只有OLD变量，因为它只是删除已有记录，我们 根据ID 在目标表B上。
• UPDATE操作同时存在OLD变量与NEW变量，我们需要通过 OLD.id 定位目标表B中的记录，将其更新为新值NEW。

这样的基于触发器的“逻辑复制”可以完美达到我们的目的，在逻辑复制中与之类似，表A上带有主键字段id。那么当我们删除表A上的记录时，例如：删除id = 1的记录时，我们只需要告诉订阅方id = 1，而不是把整个被删除的元组传递给订阅方。那么这里主键列id就是逻辑复制的复制标识。

但上面的例子中隐含着一个工作假设：表A和表B模式相同，上面有一个名为 id 的主键。

对于生产级的逻辑复制方案，即PostgreSQL 10.0后提供的逻辑复制，这样的工作假设是不合理的。因为系统无法要求用户建表时一定会带有主键，也无法要求主键的名字一定叫id。

于是，就有了 复制标识（Replica Identity） 的概念。复制标识是对OLD.id这样工作假设的进一步泛化与抽象，它用来告诉逻辑复制系统，哪些信息可以被用于唯一定位表中的一条记录。

复制标识

对于逻辑复制而言，INSERT 事件不需要特殊处理，但要想将DELETE|UPDATE复制到订阅者上时，必须提供一种标识行的方式，即复制标识（Replica Identity）。复制标识是一组列的集合，这些列可以唯一标识一条记录。其实这样的定义在概念上来说就是构成主键的列集，当然非空唯一索引中的列集（候选键）也可以起到同样的效果。

一个被纳入逻辑复制 发布中的表，必须配置有 复制标识（Replica Identity），只有这样才可以在订阅者一侧定位到需要更新的行，完成UPDATE与DELETE操作的复制。默认情况下，主键 （Primary Key）和 非空列上的唯一索引 （UNIQUE NOT NULL）可以用作复制标识。

注意，复制标识 和表上的主键、非空唯一索引并不是一回事。复制标识是表上的一个属性，它指明了在逻辑复制时，哪些信息会被用作身份定位标识符写入到逻辑复制的记录中，供订阅端定位并执行变更。

如PostgreSQL 13官方文档所述，表上的复制标识 共有4种配置模式，分别为：

• 默认模式（default）：非系统表采用的默认模式，如果有主键，则用主键列作为身份标识，否则用完整模式。
• 索引模式（index）：将某一个符合条件的索引中的列，用作身份标识
• 完整模式（full）：将整行记录中的所有列作为复制标识（类似于整个表上每一列共同组成主键）
• 无身份模式（nothing）：不记录任何复制标识，这意味着UPDATE|DELETE操作无法复制到订阅者上。

复制标识查询

表上的复制标识可以通过查阅pg_class.relreplident获取。

这是一个字符类型的“枚举”，标识用于组装 “复制标识” 的列：d = default ，f = 所有的列，i 使用特定的索引，n 没有复制标识。

表上是否具有可用作复制标识的索引约束，可以通过以下查询获取：

SELECT quote_ident(nspname) || '.' || quote_ident(relname) AS name, con.ri AS keys,
       CASE relreplident WHEN 'd' THEN 'default' WHEN 'n' THEN 'nothing' WHEN 'f' THEN 'full' WHEN 'i' THEN 'index' END AS replica_identity
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid, LATERAL (SELECT array_agg(contype) AS ri FROM pg_constraint WHERE conrelid = c.oid) con
WHERE relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema', 'monitor', 'repack', 'pg_toast')
ORDER BY 2,3;

复制标识配置

表到复制标识可以通过ALTER TABLE进行修改。

ALTER TABLE tbl REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING };
-- 具体有四种形式
ALTER TABLE t_normal REPLICA IDENTITY DEFAULT;                    -- 使用主键，如果没有主键则为FULL
ALTER TABLE t_normal REPLICA IDENTITY FULL;                       -- 使用整行作为标识
ALTER TABLE t_normal REPLICA IDENTITY USING INDEX t_normal_v_key; -- 使用唯一索引
ALTER TABLE t_normal REPLICA IDENTITY NOTHING;                    -- 不设置复制标识

复制标识实例

下面用一个具体的例子来说明复制标识的效果：

CREATE TABLE test(k text primary key, v int not null unique);

现在有一个表test，上面有两列k和v。

INSERT INTO test VALUES('Alice', '1'), ('Bob', '2');
UPDATE test SET v = '3' WHERE k = 'Alice';    -- update Alice value to 3
UPDATE test SET k = 'Oscar' WHERE k = 'Bob';  -- rename Bob to Oscaar
DELETE FROM test WHERE k = 'Alice';           -- delete Alice

在这个例子中，我们对表test执行了增删改操作，与之对应的逻辑解码结果为：

table public.test: INSERT: k[text]:'Alice' v[integer]:1
table public.test: INSERT: k[text]:'Bob' v[integer]:2
table public.test: UPDATE: k[text]:'Alice' v[integer]:3
table public.test: UPDATE: old-key: k[text]:'Bob' new-tuple: k[text]:'Oscar' v[integer]:2
table public.test: DELETE: k[text]:'Alice'

默认情况下，PostgreSQL会使用表的主键作为复制标识，因此在UPDATE|DELETE操作中，都通过k列来定位需要修改的记录。

如果我们手动修改表的复制标识，使用非空且唯一的列v作为复制标识，也是可以的：

ALTER TABLE test REPLICA IDENTITY USING INDEX test_v_key; -- 基于UNIQUE索引的复制身份

同样的变更现在产生如下的逻辑解码结果，这里v作为身份标识，出现在所有的UPDATE|DELETE事件中。

table public.test: INSERT: k[text]:'Alice' v[integer]:1
table public.test: INSERT: k[text]:'Bob' v[integer]:2
table public.test: UPDATE: old-key: v[integer]:1 new-tuple: k[text]:'Alice' v[integer]:3
table public.test: UPDATE: k[text]:'Oscar' v[integer]:2
table public.test: DELETE: v[integer]:3

如果使用完整身份模式（full）

ALTER TABLE test REPLICA IDENTITY FULL; -- 表test现在使用所有列作为表的复制身份

这里，k和v同时作为身份标识，记录到UPDATE|DELETE的日志中。对于没有主键的表，这是一种保底方案。

table public.test: INSERT: k[text]:'Alice' v[integer]:1
table public.test: INSERT: k[text]:'Bob' v[integer]:2
table public.test: UPDATE: old-key: k[text]:'Alice' v[integer]:1 new-tuple: k[text]:'Alice' v[integer]:3
table public.test: UPDATE: old-key: k[text]:'Bob' v[integer]:2 new-tuple: k[text]:'Oscar' v[integer]:2
table public.test: DELETE: k[text]:'Alice' v[integer]:3

如果使用无身份模式（nothing）

ALTER TABLE test REPLICA IDENTITY NOTHING; -- 表test现在没有复制标识

那么逻辑解码的记录中，UPDATE操作中只有新记录，没有包含旧记录中的唯一身份标识，而DELETE操作中则完全没有信息。

table public.test: INSERT: k[text]:'Alice' v[integer]:1
table public.test: INSERT: k[text]:'Bob' v[integer]:2
table public.test: UPDATE: k[text]:'Alice' v[integer]:3
table public.test: UPDATE: k[text]:'Oscar' v[integer]:2
table public.test: DELETE: (no-tuple-data)

这样的逻辑变更日志对于订阅端来说完全没用，在实际使用中，对逻辑复制中的无复制标识的表执行DELETE|UPDATE会直接报错。

复制标识详解

表上的复制标识配置，与表上有没有索引，是相对正交的两个因素。

尽管各种排列组合都是可能的，然而在实际使用中，只有三种可行的情况。

• 表上有主键，使用默认的 default 复制标识
• 表上没有主键，但是有非空唯一索引，显式配置 index 复制标识
• 表上既没有主键，也没有非空唯一索引，显式配置full复制标识（运行效率非常低，仅能作为兜底方案）
• 其他所有情况，都无法正常完成逻辑复制功能

下面，我们来考虑几个边界条件。

重建主键

假设因为索引膨胀，我们希望重建表上的主键索引回收空间。

CREATE TABLE test(k text primary key, v int);
CREATE UNIQUE INDEX test_pkey2 ON test(k);
BEGIN;
ALTER TABLE test DROP CONSTRAINT test_pkey;
ALTER TABLE test ADD PRIMARY KEY USING INDEX test_pkey2;
COMMIT;

在default模式下，重建并替换主键约束与索引并不会影响复制标识。

重建唯一索引

假设因为索引膨胀，我们希望重建表上的非空唯一索引回收空间。

CREATE TABLE test(k text, v int not null unique);
ALTER TABLE test REPLICA IDENTITY USING INDEX test_v_key;
CREATE UNIQUE INDEX test_v_key2 ON test(v);
-- 使用新的test_v_key2索引替换老的Unique索引
BEGIN;
ALTER TABLE test ADD UNIQUE USING INDEX test_v_key2;
ALTER TABLE test DROP CONSTRAINT test_v_key;
COMMIT;

与default模式不同，index模式下，复制标识是与具体的索引绑定的：

                                    Table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 k      | text    |           |          |         | extended |              |
 v      | integer |           | not null |         | plain    |              |
Indexes:
    "test_v_key" UNIQUE CONSTRAINT, btree (v) REPLICA IDENTITY
    "test_v_key2" UNIQUE CONSTRAINT, btree (v)

这意味着如果采用偷天换日的方式替换UNIQUE索引会导致复制身份的丢失。

解决方案有两种：

1. 使用REINDEX INDEX (CONCURRENTLY)的方式重建该索引，不会丢失复制标识信息。
2. 在替换索引时，一并刷新表的默认复制身份：

BEGIN;
ALTER TABLE test ADD UNIQUE USING INDEX test_v_key2;
ALTER TABLE test REPLICA IDENTITY USING INDEX test_v_key2;
ALTER TABLE test DROP CONSTRAINT test_v_key;
COMMIT;

顺带一提，移除作为身份标识的索引。尽管在表的配置信息中仍然为index模式，但效果与nothing相同。所以不要随意折腾作为身份的索引。

使用不合格的索引作为复制标识

复制标识需要一个 唯一，不可延迟，整表范围的，建立在非空列集上的索引。

最经典的例子就是主键索引，以及通过col type NOT NULL UNIQUE声明的单列非空索引。

之所以要求 NOT NULL，是因为NULL值无法进行等值判断，所以表中允许UNIQE的列上存在多条取值为NULL的记录，允许列为空说明这个列无法起到唯一标识记录的效果。如果尝试使用一个普通的UNIQUE索引（列上没有非空约束）作为复制标识，则会报错。

[42809] ERROR: index "t_normal_v_key" cannot be used as replica identity because column "v" is nullable

使用FULL复制标识

如果没有任何复制标识，可以将复制标识设置为FULL，也就是把整个行当作复制标识。

使用FULL模式的复制标识效率很低，所以这种配置只能是保底方案，或者用于很小的表。因为每一行修改都需要在订阅者上执行全表扫描，很容易把订阅者拖垮。

FULL模式限制

使用FULL模式的复制标识还有一个限制，订阅端的表上的复制身份所包含的列，要么与发布者一致，要么比发布者更少，否则也无法保证的正确性，下面具体来看一个例子。

假如发布订阅两侧的表都采用FULL复制标识，但是订阅侧的表要比发布侧多了一列（是的，逻辑复制允许订阅端的表带有发布端表不具有的列）。这样的话，订阅端的表上的复制身份所包含的列要比发布端多了。假设在发布端上删除(f1=a, f2=a)的记录，却会导致在订阅端删除两条满足身份标识等值条件的记录。

     (Publication)       ------>           (Subscription)
|--- f1 ---|--- f2 ---|          |--- f1 ---|--- f2 ---|--- f3 ---|
|    a     |     a    |          |    a     |     a    |     b    |
                                 |    a     |     a    |     c    |

FULL模式如何应对重复行问题

PostgreSQL的逻辑复制可以“正确”处理FULL模式下完全相同行的场景。假设有这样一张设计糟糕的表，表中存在多条一模一样的记录。

CREATE TABLE shitty_table(
	 f1  TEXT,
	 f2  TEXT,
	 f3  TEXT
);
INSERT INTO shitty_table VALUES ('a', 'a', 'a'), ('a', 'a', 'a'), ('a', 'a', 'a');

在FULL模式下，整行将作为复制标识使用。假设我们在shitty_table上通过ctid扫描作弊，删除了3条一模一样记录中的其中一条。

# SELECT ctid,* FROM shitty_table;
 ctid  | a | b | c
-------+---+---+---
 (0,1) | a | a | a
 (0,2) | a | a | a
 (0,3) | a | a | a

# DELETE FROM shitty_table WHERE ctid = '(0,1)';
DELETE 1

# SELECT ctid,* FROM shitty_table;
 ctid  | a | b | c
-------+---+---+---
 (0,2) | a | a | a
 (0,3) | a | a | a

从逻辑上讲，使用整行作为身份标识，那么订阅端执行以下逻辑，会导致全部3条记录被删除。

DELETE FROM shitty_table WHERE f1 = 'a' AND f2 = 'a' AND f3 = 'a'

但实际情况是，因为PostgreSQL的变更记录以行为单位，这条变更仅会对第一条匹配的记录生效，所以在订阅侧的行为也是删除3行中的1行。在逻辑上与发布端等效。

PG慢查询诊断方法论

You can’t optimize what you can’t measure

慢查询是在线业务数据库的大敌，如何诊断定位慢查询是DBA的必修课题。

本文介绍了使用监控系统 —— Pigsty诊断慢查询的一般方法论。

慢查询：危害

对于实际服务于在线业务事务处理的PostgreSQL数据库而言，慢查询的危害包括：

• 慢查询挤占数据库连接，导致普通查询无连接可用，堆积并导致数据库雪崩。
• 慢查询长时间锁住了主库已经清理掉的旧版本元组，导致流复制重放进程锁死，导致主从复制延迟。
• 查询越慢，查询间相互踩踏的几率越高，越容易产生死锁、锁等待，事务冲突等问题。
• 慢查询浪费系统资源，拉高系统水位。

因此，一个合格的DBA必须知道如何及时定位并处理慢查询。

图：一个慢查询优化前后，系统的整体饱和度从40%降到了4%

慢查询诊断 —— 传统方法

传统上来说，在PostgreSQL有两种方式可以获得慢查询的相关信息，一个是通过官方的扩展插件pg_stat_statements，另一种是慢查询日志。

慢查询日志顾名思义，所有执行时间长于log_min_duration_statement参数的查询都会被记录到PG的日志中，对于定位慢查询，特别是对于分析特例、单次慢查询不可或缺。不过慢查询日志也有自己的局限性。在生产环境中出于性能考虑，通常只会记录时长超出某一阈值的查询，那么许多信息就无法从慢查询日志中获取了。当然值得一提的是，尽管开销很大，但全量查询日志仍然是慢查询分析的终极杀手锏。

更常用的慢查询诊断工具可能还是pg_stat_statements。这事是一个非常实用的扩展，它会收集数据库内运行查询的统计信息，在任何场景下都强烈建议启用该扩展。

pg_stat_statements 提供的原始指标数据以系统视图表的形式呈现。系统中的每一类查询（即抽取变量后执行计划相同的查询）都分配有一个查询ID，紧接着是调用次数，总耗时，最大、最小、平均单次耗时，响应时间都标准差，每次调用平均返回的行数，用于块IO的时间这些指标类数据。

一种简单的方式当然是观察 mean_time/max_time这类指标，从系统的Catalog中，您的确可以知道某类查询有史以来平均的响应时间。对于定位慢查询来说，也许这样也算得上基本够用了。但是像这样的指标，只是系统在当前时刻的一个静态快照，所以能够回答的问题是有限的。譬如说，您想看一看某个查询在加上新索引之后的性能表现是不是有所改善，用这种方式可能就会非常繁琐。

pg_stat_statements需要在shared_preload_library中指定，并在数据库中通过CREATE EXTENSION pg_stat_statements显式创建。创建扩展后即可通过视图pg_stat_statements访问查询统计信息

慢查询的定义

多慢的查询算慢查询？

应该说这个问题取决于业务、以及实际的查询类型，并没有通用的标准。

作为一种经验阈值，频繁的CRUD点查，如果超过1ms，可列为慢查询。

对于偶发的单次特例查询而言，通常超过100ms或1s可以列为慢查询。

慢查询诊断 —— Pigsty

监控系统就可以更全面地回答关于慢查询的问题。监控系统中的数据是由无数历史快照组成的（如5秒一次快照采样）。因此用户可以回溯至任意时间点，考察不同时间段内查询平均响应时间的变化。

上图是Pigsty中 PG Query Detail提供的界面，这里展现出了单个查询的详细信息。

这是一个典型的慢查询，平均响应时间几秒钟。为它添加了一个索引后。从右中Query RT仪表盘的上可以看到，查询的平均响应世界从几秒降到了几毫秒。

用户可以利用监控系统提供的洞察迅速定位数据库中的慢查询，定位问题，提出猜想。更重要的是，用户可以即时地在不同层次审视表与查询的详细指标，应用解决方案并获取实时反馈，这对于紧急故障处理是非常有帮助的。

有时监控系统的用途不仅仅在于提供数据与反馈，它还可以作为一种安抚情绪的良药：设想一个慢查询把生产数据库打雪崩了，如果老板或客户没有一个地方可以透明地知道当前的处理状态，难免会焦急地催问，进一步影响问题解决的速度。监控系统也可以做作为精确管理的依据。您可以有理有据地用监控指标的变化和老板与客户吹牛逼。

一个模拟的慢查询案例

Talk is cheap, show me the code

假设用户已经拥有一个 Pigsty沙箱演示环境，下面将使用Pigsty沙箱，演示模拟的慢查询定位与处理流程。

慢查询：模拟

因为没有实际的业务系统，这里我们以一种简单快捷的方式模拟系统中的慢查询。即pgbench自带的类tpc-b场景。

通过make ri / make ro / make rw，在pg-test集群上初始化 pgbench 用例，并对集群施加读写负载

# 50TPS 写入负载
while true; do pgbench -nv -P1 -c20 --rate=50 -T10 postgres://test:test@pg-test:5433/test; done

# 1000TPS 只读负载
while true; do pgbench -nv -P1 -c40 --select-only --rate=1000 -T10 postgres://test:test@pg-test:5434/test; done

现在我们已经有了一个模拟运行中的业务系统，让我们通过简单粗暴的方式来模拟一个慢查询场景。在pg-test集群的主库上执行以下命令，删除表pgbench_accounts的主键：

ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey ;

该命令会移除 pgbench_accounts 表上的主键，导致相关查询从索引扫描变为顺序全表扫描，全部变为慢查询，访问PG Instance ➡️ Query ➡️ QPS，结果如下图所示：

图1：平均查询响应时间从1ms飙升为300ms，单个从库实例的QPS从500下降至7。

与此同时，实例因为慢查询堆积，系统会在瞬间雪崩过载，访问PG Cluster首页，可以看到集群负载出现飙升。

图2：系统负载达到200%，触发机器负载过大，与查询响应时间过长的报警规则。

慢查询：定位

首先，使用PG Cluster面板定位慢查询所在的具体实例，这里以 pg-test-2 为例。

然后，使用PG Query面板定位具体的慢查询：编号为 -6041100154778468427

图3：从查询总览中发现异常慢查询

该查询表现出：

• 响应时间显著上升： 17us 升至 280ms
• QPS 显著下降： 从500下降到 7
• 花费在该查询上的时间占比显著增加

可以确定，就是这个查询变慢了！

接下来，利用PG Stat Statements面板或PG Query Detail，根据查询ID定位慢查询的具体语句。

图4：定位查询语句为SELECT abalance FROM pgbench_accounts WHERE aid = $1

慢查询：猜想

获知慢查询语句后，接下来需要推断慢查询产生的原因。

SELECT abalance FROM pgbench_accounts WHERE aid = $1

该查询以 aid 作为过滤条件查询 pgbench_accounts 表，如此简单的查询变慢，大概率是这张表上的索引出了问题。 用屁股想都知道是索引少了，因为就是我们自己删掉的嘛！

分析查询后， 可以提出猜想： 该查询变慢是pgbench_accounts表上aid列缺少索引。

下一步，我们就要验证猜想。

第一步，使用PG Table Catalog，我们可以检视表的详情，例如表上建立的索引。

第二步，查阅 PG Table Detail 面板，检查 pgbench_accounts 表上的访问，来验证我们的猜想

图5： pgbench_accounts 表上的访问情况

通过观察，我们发现表上的索引扫描归零，与此同时顺序扫描却有相应增长。这印证了我们的猜想！

慢查询：方案

假设一旦成立，就可以着手提出方案，解决问题了。

解决慢查询通常有三种方式：修改表结构、修改查询、修改索引。

修改表结构与查询通常涉及到具体的业务知识和领域知识，需要具体问题具体分析。但修改索引通常来说不需要太多的具体业务知识。

这里的问题可以通过添加索引解决，pgbench_accounts 表上 aid 列缺少索引，那么我们尝试在 pgbench_accounts 表上为 aid 列添加索引，看看能否解决这个问题。

CREATE UNIQUE INDEX ON pgbench_accounts (aid);

加上索引后，神奇的事情发生了。

图6：可以看到，查询的响应时间与QPS已经恢复正常。

图7：系统的负载也恢复正常

慢查询：评估

作为慢查询处理的最后一步，我们通常需要对操作的过程进行记录，对效果进行评估。

有时候一个简单的优化可以产生戏剧性的效果。也许本来需要砸几十万加机器的问题，创建一个索引就解决了。

这种故事，就可以通过监控系统，用很生动直观的形式表达出来，赚取KPI与Credit。

图：一个慢查询优化前后，系统的整体饱和度从40%降到了4%

（相当于节省了X台机器，XX万元，老板看了心花怒放，下一任CTO就是你了！）

慢查询：小结

通过这篇教程，您已经掌握了慢查询优化的一般方法论。即：

• 定位问题

• 提出猜想

• 验证假设

• 制定方案

• 评估效果

监控系统在慢查询处理的整个生命周期中都能起到重要的效果。更能将运维与DBA的“经验”与“成果”，以可视化，可量化，可复制的方式表达出来。

故障档案:时间回溯导致的Patroni故障

摘要：机器因为故障重启，NTP服务在PG启动后修复了PG的时间，导致 Patroni 无法启动。

Patroni中的故障信息如下所示：

Process %s is not postmaster, too much difference between PID file start time %s and process start time %s

patroni 进程启动时间和pid时间不一致。就会认为：postgres is not running。

两个时间相差超过30秒。patroni 就尿了，启动不了了。

打印错误信息的代码为：

start_time = int(self._postmaster_pid.get('start_time', 0))
if start_time and abs(self.create_time() - start_time) > 3:
    logger.info('Process %s is not postmaster, too much difference between PID file start time %s and process start time %s', self.pid, self.create_time(), start_time)

同时，发现了Patroni里的一个BUG：https://github.com/zalando/patroni/issues/811 错误信息里两个时间戳打反了。

经验与教训： NTP 时间同步是非常重要的

在线修改主键列类型

如何在线修改主键列类型，比如将 INT 至 BIGINT，同时又不影响业务？

假设在PG中有一个表，在设计的时候拍脑袋使用了 INT 整型主键，现在业务蓬勃发展发现序列号不够用了，想升级到BIGINT类型。这时候该怎么做呢？

拍脑袋的方法当然是直接使用DDL修改类型：

ALTER TABLE pgbench_accounts ALTER COLUMN aid SET DATA TYPE BIGINT;

但这种方式对于访问频繁的生产大表是不可行的

太长；不看

让我们以 pgbench 自带的场景为例

-- 操作目标：升级 pgbench_accounts 表普通列 abalance 类型：INT -> BIGINT

-- 添加新列：abalance_tmp BIGINT
ALTER TABLE pgbench_accounts ADD COLUMN abalance_tmp BIGINT;

-- 创建触发器函数：保持新列数据与旧列同步
CREATE OR REPLACE FUNCTION public.sync_pgbench_accounts_abalance() RETURNS TRIGGER AS $$
BEGIN NEW.abalance_tmp = NEW.abalance; RETURN NEW;END;
$$ LANGUAGE 'plpgsql';

-- 完成整表更新，分批更新的方式见下
UPDATE pgbench_accounts SET abalance_tmp = abalance; -- 不要在大表上运行这个

-- 创建触发器
CREATE TRIGGER tg_sync_pgbench_accounts_abalance BEFORE INSERT OR UPDATE ON pgbench_accounts
    FOR EACH ROW EXECUTE FUNCTION sync_pgbench_accounts_abalance();

-- 完成列的新旧切换，这时候数据同步方向变化 旧列数据与新列保持同步
BEGIN;
LOCK TABLE pgbench_accounts IN EXCLUSIVE MODE;
ALTER TABLE pgbench_accounts DISABLE TRIGGER tg_sync_pgbench_accounts_abalance;
ALTER TABLE pgbench_accounts RENAME COLUMN abalance TO abalance_old;
ALTER TABLE pgbench_accounts RENAME COLUMN abalance_tmp TO abalance;
ALTER TABLE pgbench_accounts RENAME COLUMN abalance_old TO abalance_tmp;
ALTER TABLE pgbench_accounts ENABLE TRIGGER tg_sync_pgbench_accounts_abalance;
COMMIT;

-- 确认数据完整性
SELECT count(*) FROM pgbench_accounts WHERE abalance_new != abalance;

-- 清理触发器与函数
DROP FUNCTION IF EXISTS sync_pgbench_accounts_abalance();
DROP TRIGGER tg_sync_pgbench_accounts_abalance ON pgbench_accounts;