管理

• Postgres逻辑复制详解
• PG中的本地化排序规则
• PG复制标识详解（Replica Identity）
• PG慢查询诊断方法论
• PgSQL在线修改列类型
• 黄金监控指标
• 数据库集群管理概念与实体命名规范
• PostgreSQL的KPI
• 在线修改PG字段类型
• PostgreSQL标准复制方案
• PgSQL备份方案
• PgSQL报警方案
• 关系膨胀：监控与处理
• PipelineDB快速上手
• TimescaleDB快速上手
• 监控PG中的表大小
• PgAdmin安装配置
• Bash与psql小技巧
• PostgreSQL例行维护
• PgSQL备份恢复概览
• PgBackRest2中文文档
• Pgbouncer快速上手
• 空中换引擎 —— PostgreSQL不停机迁移数据
• PgSQL日志方案
• 使用FIO测试磁盘性能
• 使用sysbench测试PostgreSQL性能
• 找出没用过的索引
• 批量配置SSH免密登录
• PgSQL协议分析:网络抓包
• file_fdw妙用无穷——从数据库读取系统信息
• Unix命令 iostat
• Unix命令: free
• Unix命令: top
• Unix命令: vmstat
• 编译安装PostGIS
• PostgreSQL MongoFDW安装部署
• PgSQL: WAL与检查点

Postgres逻辑复制详解

逻辑复制

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

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

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

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

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

逻辑复制的典型用途是：

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

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

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

发布（Publication）

一个 发布（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：什么情况下需要手工管理订阅复制槽？

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的“经验”与“成果”，以可视化，可量化，可复制的方式表达出来。

PgSQL在线修改列类型

如何在线升级INT至Bigint？

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

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

ALTER TABLE pgbench_accounts

太长；不看

以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;

外键

alter table my_table add column new_id bigint;

begin; update my_table set new_id = id where id between 0 and 100000; commit;
begin; update my_table set new_id = id where id between 100001 and 200000; commit;
begin; update my_table set new_id = id where id between 200001 and 300000; commit;
begin; update my_table set new_id = id where id between 300001 and 400000; commit;
...

create unique index my_table_pk_idx on my_table(new_id);

begin;
alter table my_table drop constraint my_table_pk;
alter table my_table alter column new_id set default nextval('my_table_id_seq'::regclass);
update my_table set new_id = id where new_id is null;
alter table my_table add constraint my_table_pk primary key using index my_table_pk_idx;
alter table my_table drop column id;
alter table my_table rename column new_id to id;
commit;

以pgbench为例

vonng=# \d pgbench_accounts
              Table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
 aid      | integer       |           | not null |
 bid      | integer       |           |          |
 abalance | integer       |           |          |
 filler   | character(84) |           |          |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

升级abalance列为BIGINT

会锁表，在表大小非常小，访问量非常小的的情况下可用。

ALTER TABLE pgbench_accounts ALTER COLUMN abalance SET DATA TYPE bigint;

在线升级流程

1. 添加新列
2. 更新数据
3. 在新列上创建相关索引（如果没有也可以单列创建，加快第四步的速度）
4. 执行切换事务
   1. 排他锁表
   2. UPDATE更新空列（也可以使用触发器）
   3. 删旧列
   4. 重命名新列

-- Step 1 : 创建新列
ALTER TABLE pgbench_accounts ADD COLUMN abalance_new BIGINT;

-- Step 2 : 更新数据，可以分批更新，分批更新方法详见下面
UPDATE pgbench_accounts SET abalance_new = abalance;

-- Step 3 : 可选（在新列上创建索引）
CREATE INDEX CONCURRENTLY ON public.pgbench_accounts (abalance_new);
UPDATE pgbench_accounts SET abalance_new = abalance WHERE ;

-- Step 3 :

-- Step 4 :

-- 同步更新对应列
CREATE OR REPLACE FUNCTION public.sync_abalance() RETURNS TRIGGER AS $$
BEGIN NEW.abalance_new = OLD.abalance; RETURN NEW;END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER pgbench_accounts_sync_abalance BEFORE INSERT OR UPDATE ON pgbench_accounts EXECUTE FUNCTION sync_abalance();

alter table my_table add column new_id bigint;

begin; update my_table set new_id = id where id between 0 and 100000; commit;
begin; update my_table set new_id = id where id between 100001 and 200000; commit;
begin; update my_table set new_id = id where id between 200001 and 300000; commit;
begin; update my_table set new_id = id where id between 300001 and 400000; commit;
...

create unique index my_table_pk_idx on my_table(new_id);

begin;
alter table my_table drop constraint my_table_pk;
alter table my_table alter column new_id set default nextval('my_table_id_seq'::regclass);
update my_table set new_id = id where new_id is null;
alter table my_table add constraint my_table_pk primary key using index my_table_pk_idx;
alter table my_table drop column id;
alter table my_table rename column new_id to id;
commit;

批量更新逻辑

有时候需要为大表添加一个非空的，带有默认值的列。因此需要对整表进行一次更新，可以使用下面的办法，将一次巨大的更新拆分为100次或者更多的小更新。

从统计信息中获取主键的分桶信息：

SELECT unnest(histogram_bounds::TEXT::BIGINT[]) FROM pg_stats WHERE tablename = 'signup_users' and attname = 'id';

直接从统计分桶信息中生成需要执行的SQL，在这里把SQL改成需要更新的语

SELECT 'UPDATE signup_users SET app_type = '''' WHERE id BETWEEN ' || lo::TEXT || ' AND ' || hi::TEXT || ';'
FROM (
         SELECT lo, lead(lo) OVER (ORDER BY lo) as hi
         FROM (
                  SELECT unnest(histogram_bounds::TEXT::BIGINT[]) lo
                  FROM pg_stats
                  WHERE tablename = 'signup_users'
                    and attname = 'id'
                  ORDER BY 1
              ) t1
     ) t2;

直接使用SHELL脚本打印出更新语句

DATNAME=""
RELNAME="pgbench_accounts"
IDENTITY="aid"
UPDATE_CLAUSE="abalance_new = abalance"

SQL=$(cat <<-EOF
SELECT 'UPDATE ${RELNAME} SET ${UPDATE_CLAUSE} WHERE ${IDENTITY} BETWEEN ' || lo::TEXT || ' AND ' || hi::TEXT || ';'
FROM (
		SELECT lo, lead(lo) OVER (ORDER BY lo) as hi
		FROM (
				SELECT unnest(histogram_bounds::TEXT::BIGINT[]) lo
				FROM pg_stats
				WHERE tablename = '${RELNAME}'
					and attname = '${IDENTITY}'
				ORDER BY 1
			) t1
	) t2;
EOF
)

# echo $SQL

psql ${DATNAME} -qAXwtc "ANALYZE ${RELNAME};"
psql ${DATNAME} -qAXwtc "${SQL}"

处理边界情况。

 UPDATE signup_users SET app_type = '' WHERE app_type != '';

也可以加工一下，添加事务语句和休眠间隔

DATNAME="test"
RELNAME="pgbench_accounts"
COLNAME="aid"
UPDATE_CLAUSE="abalance_tmp = abalance"
SLEEP_INTERVAL=0.1

SQL=$(cat <<-EOF
SELECT 'BEGIN;UPDATE ${RELNAME} SET ${UPDATE_CLAUSE} WHERE ${COLNAME} BETWEEN ' || lo::TEXT || ' AND ' || hi::TEXT || ';COMMIT;SELECT pg_sleep(${SLEEP_INTERVAL});VACUUM ${RELNAME};'
FROM (
		SELECT lo, lead(lo) OVER (ORDER BY lo) as hi
		FROM (
				SELECT unnest(histogram_bounds::TEXT::BIGINT[]) lo
				FROM pg_stats
				WHERE tablename = '${RELNAME}'
					and attname = '${COLNAME}'
				ORDER BY 1
			) t1
	) t2;
EOF
)
# echo $SQL
psql ${DATNAME} -qAXwtc "ANALYZE ${RELNAME};"
psql ${DATNAME} -qAXwtc "${SQL}"

BEGIN;UPDATE pgbench_accounts SET abalance_new = abalance WHERE aid BETWEEN 397 AND 103196;COMMIT;SELECT pg_sleep(0.5);VACUUM pgbench_accounts;
BEGIN;UPDATE pgbench_accounts SET abalance_new = abalance WHERE aid BETWEEN 103196 AND 213490;COMMIT;SELECT pg_sleep(0.5);VACUUM pgbench_accounts;
BEGIN;UPDATE pgbench_accounts SET abalance_new = abalance WHERE aid BETWEEN 213490 AND 301811;COMMIT;SELECT pg_sleep(0.5);VACUUM pgbench_accounts;
BEGIN;UPDATE pgbench_accounts SET abalance_new = abalance WHERE aid BETWEEN 301811 AND 400003;COMMIT;SELECT pg_sleep(0.5);VACUUM pgbench_accounts;
BEGIN;UPDATE pgbench_accounts SET abalance_new = abalance WHERE aid BETWEEN 400003 AND 511931;COMMIT;SELECT pg_sleep(0.5);VACUUM pgbench_accounts;
BEGIN;UPDATE pgbench_accounts SET abalance_new = abalance WHERE aid BETWEEN 511931 AND 613890;COMMIT;SELECT pg_sleep(0.5);VACUUM pgbench_accounts;

黄金监控指标

前言

玩数据库和玩车有一个共通之处，就是都需要经常看仪表盘。

盯着仪表盘干什么，看指标。为什么看指标，掌握当前运行状态才能有效施加控制。

车有很多指标：车速，胎压，扭矩，刹车片磨损，各种温度，等等等等，各式各样。

但人的注意力空间有限，仪表盘也就那么大，

所以，指标可以分两类：

• 你会去看的：黄金指标 / 关键指标 / 核心指标
• 你不会看的：黑匣子指标 / 冷指标。

黄金指标就是那几个关键性的核心数据，需要时刻保持关注（或者让自动驾驶系统/报警系统替你时刻保持关注），而冷指标通常只有故障排查时才会去看，故障排查与验尸要求尽可能还原现场，黑匣子指标多多益善。需要时没有就很让人抓狂

今天我们来说说PostgreSQL的核心指标，数据库的核心指标是什么？

数据库的指标

在讲数据库的核心指标之前，我们先来瞄一眼有哪些指标。

avg(count by (ins) ({__name__=~"pg.*"}))
avg(count by (ins) ({__name__=~"node.*"}))

1000多个pg的指标，2000多个机器的指标。

这些指标都是数据宝藏，挖掘与可视化可以提取出其中的价值。

但对于日常管理，只需要少数几个核心指标就可以了。

可用指标千千万，哪些才是核心指标？

核心指标

根据经验和使用频度，不断地做减法，可以筛选出一些核心指标：

紧急情况下：错误是始终是第一优先级的黄金指标。

常规情况下：应用视角的黄金指标：QPS与RT

常规情况下：DBA视角的黄金指标：DB饱和度（水位）

为什么是它们？

错误指标

第一优先级的指标永远是错误，错误往往是直接面向终端用户的。

如果只能选一个指标进行监控，那么选错误指标，比如应用，系统，DB层的每秒错误日志条数可能最合适。

一辆车，只能选一个仪表盘上的功能，你会选什么？

选错误指标，小车不停只管推。

错误类指标非常重要，直接反映出系统的异常，譬如连接池排队。但错误类指标最大的问题就是，它只在告警时有意义，难以用于日常的水位评估与性能分析，此外，错误类指标也往往难以精确量化，往往只能给出定性的结果：有问题 vs 没问题。

此外，错误类指标难以精确量化。我们只能说：当连接池出现排队时，数据库负载比较大；队列越长，负载越大；没有排队时，数据库负载不怎么大，仅此而已。对于日常使用管理来说，这样的能力肯定也是不够的。

定指标，做监控报警系统的一个重要原因就是用于预防系统过载，如果系统已经过载大量报错，那么使用错误现象反过来定义饱和度是没有意义的。

指标的目的，是为了衡量系统的运行状态。，我们还会关注系统其他方面的能力：吞吐量/流量，响应时间/延迟，饱和度/利用率/水位线。这三者分别代表系统的能力，服务质量，负载水平。

关注点不同，后端（数据库用户）关注系统能力与服务质量，DBA（数据库管理者）更关注系统的负载水平。

流量指标

流量类的指标很有潜力，特别是QPS，TPS这样的指标相当具有代表性。

流量指标可以直接衡量系统的能力，譬如每秒处理多少笔订单，每秒处理的多少个请求。

与车速计有异曲同工之妙，高速限速，城市限速。环境，负载。

但像TPS QPS这样流量也存在问题。一个数据库实例上的查询往往是五花八门各式各样的，一个耗时10微秒的查询和一个10秒的查询在统计时都被算为一个Q，类似于QPS这样的指标无法进行横向比较，只有比较粗略的参考意义，甚至当查询类型发生变化时，都无法和自己的历史数据进行纵向比较。此外也很难针对QPS、TPS这样的指标设置利用率目标，同一个数据库执行SELECT 1可以打到几十万的QPS，但执行复杂SQL时可能就只能打到几千的QPS。不同负载类型和机器硬件会对数据库的QPS上限产生显著影响，只有当一个数据库上的查询都是高度单一同质且没有复杂变化的条件下，QPS才有参考意义，在这种苛刻条件下倒是可以通过压力测试设定一个QPS的水位目标。

延迟指标

与档位类似，查询慢，档位低，车速慢。查询档次低，TPS水位低。查询档次高，TPS水位高

延迟适合衡量系统的服务质量。

比起QPS/TPS，RT（响应时间 Response Time）这样的指标反而更具有参考价值。因为响应时间增加往往是系统饱和的前兆。根据经验法则，数据库的负载越大，查询与事务的平均响应时间也会越高。RT相比QPS的一个优势是**，RT是可以设置一个利用率目标的**，比如可以为RT设定一个绝对阈值：不允许生产OLTP库上出现RT超过1ms的慢查询。但QPS这样的指标就很难画出红线来。不过，RT也有自己的问题。第一个问题是它依然是定性而非定量的，延迟增加只是系统饱和的预警，但没法用来精确衡量系统的饱和度。第二个问题通常能从数据库与中间件获取到的RT统计指标都是平均值，但真正起到预警效果的有可能是诸如P99，P999这样的统计量。

饱和度指标

饱和度指标类似汽车的发动机转速表，油量表，水温表。

饱和度指标适合衡量系统的负载

即用户期待的负载指标是一个饱和度（Saturation）指标，所谓饱和度，即服务容量有多”满“，通常是系统中目前最为受限的某种资源的某个具体指标的度量。通常来说，0%的饱和度意味着系统完全空闲，100%的饱和度意味着满载，系统在达到100%利用率前就会出现性能的严重下降，因此设定指标时还需要包括一个利用率目标，或者说水位红线、黄线，当系统瞬时负载超过红线时应当触发告警，长期负载超过黄线时应当进行扩容。

数据库集群管理概念与实体命名规范

数据库集群管理概念与实体命名规范

名之则可言也，言之则可行也。

概念及其命名是非常重要的东西，命名风格体现了工程师对系统架构的认知。定义不清的概念将导致沟通困惑，随意设定的名称将产生意想不到的额外负担。因此需要审慎地设计。

TL;DR

• **集群（Cluster）**是基本自治单元，由用户指定唯一标识，表达业务含义，作为顶层命名空间。
• 集群在硬件层面上包含一系列的节点（Node），即物理机，虚机（或Pod），可以通过IP唯一标识。
• 集群在软件层面上包含一系列的实例（Instance），即软件服务器，可以通过IP:Port唯一标识。
• 集群在服务层面上包含一系列的服务（Service），即可访问的域名与端点，可以通过域名唯一标识。
• Cluster命名可以使用任意满足DNS域名规范的名称，但不能带点（[a-zA-Z0-9-]+）。
• Node/Pod命名采用Cluster名称前缀，后接-连接一个从0开始分配的序号，（与k8s保持一致）
• 实例命名通常与Node保持一致，即${cluster}-${seq}的方式，这种方式隐含着节点与实例1:1部署的假设。如果这个假设不成立，则可以采用独立于节点的序号，但保持同样的命名规则。
• Service命名采用Cluster名称前缀，后接-连接服务具体内容，如primary, standby

以上图为例，用于测试的数据库集群名为“pg-test”，该集群由一主两从三个数据库服务器实例组成，部署在集群所属的三个节点上。pg-test集群集群对外提供两种服务，读写服务pg-test-primary与只读副本服务pg-test-standby。

基本概念

在Postgres集群管理中，有如下概念：

集群（Cluster）

集群是基本的自治业务单元，这意味着集群能够作为一个整体组织对外提供服务。类似于k8s中Deployment的概念。注意这里的集群是软件层面的概念，不要与PG Cluster（数据库集簇，即包含多个PG Database实例的单个PG Server Instance）或Node Cluster（机器集群）混淆。

集群是管理的基本单位之一，是用于统合各类资源的组织单位。例如一个PG集群可能包括：

• 三个物理机器节点
• 一个主库实例，对外提供数据库读写服务。
• 两个从库实例，对外提供数据库只读副本服务。
• 两个对外暴露的服务：读写服务，只读副本服务。

每个集群都有用户根据业务需求定义的唯一标识符，本例中定义了一个名为pg-test的数据库集群。

节点（Node）

节点是对硬件资源的一种抽象，通常指代一台工作机器，无论是物理机（bare metal）还是虚拟机（vm），或者是k8s中的Pod。这里注意k8s中Node是硬件资源的抽象，但在实际管理使用上，是k8s中的Pod而不是Node更类似于这里Node概念。总之，节点的关键要素是：

• 节点是硬件资源的抽象，可以运行一系列的软件服务
• 节点可以使用IP地址作为唯一标识符

尽管可以使用lan_ip地址作为节点唯一标识符，但为了便于管理，节点应当拥有一个人类可读的充满意义的名称作为节点的Hostname，作为另一个常用的节点唯一标识。

服务（Service）

服务是对软件服务（例如Postgres，Redis）的一种命名抽象（named abastraction）。服务可以有各种各样的实现，但其的关键要素在于：

• 可以寻址访问的服务名称，用于对外提供接入，例如：
  • 一个DNS域名（pg-test-primary）
  • 一个Nginx/Haproxy Endpoint
• 服务流量路由解析与负载均衡机制，用于决定哪个实例负责处理请求，例如：
  • DNS L7：DNS解析记录
  • HTTP Proxy：Nginx/Ingress L7：Nginx Upstream配置
  • TCP Proxy：Haproxy L4：Haproxy Backend配置
  • Kubernetes：Ingress：Pod Selector 选择器。

同一个数据集簇中通常包括主库与从库，两者分别提供读写服务（primary）和只读副本服务(standby)。

实例（Instance）

实例指带一个具体的数据库服务器，它可以是单个进程，也可能是共享命运的一组进程，也可以是一个Pod中几个紧密关联的容器。实例的关键要素在于：

• 可以通过IP:Port唯一标识
• 具有处理请求的能力

例如，我们可以把一个Postgres进程，为之服务的独占Pgbouncer连接池，PgExporter监控组件，高可用组件，管理Agent看作一个提供服务的整体，视为一个数据库实例。

实例隶属于集群，每个实例在集群范围内都有着自己的唯一标识用于区分。

实例由服务负责解析，实例提供被寻址的能力，而Service将请求流量解析到具体的实例组上。

命名规则

一个对象可以有很多组 标签（Tag） 与 元数据（Metadata/Annotation） ，但通常只能有一个名字。

管理数据库和软件，其实与管理子女或者宠物类似，都是需要花心思去照顾的。而起名字就是其中非常重要的一项工作。肆意的名字（例如 XÆA-12，NULL，史珍香）很可能会引入不必要的麻烦（额外复杂度），而设计得当的名字则可能会有意想不到的效果。

总的来说，对象起名应当遵循一些原则：

• 简洁直白，人类可读：名字是给人看的，因此要好记，便于使用。

• 体现功能，反映特征：名字需要反映对象的关键特征

• 独一无二，唯一标识：名字在命名空间内，自己的类目下应当是独一无二，可以惟一标识寻址的。

• 不要把太多无关的东西塞到名字里去：在名字中嵌入很多重要元数据是一个很有吸引力的想法，但维护起来会非常痛苦，例如反例：pg:user:profile:10.11.12.13:5432:replica:13。

集群命名

集群名称，其实类似于命名空间的作用。所有隶属本集群的资源，都会使用该命名空间。

集群命名的形式，建议采用符合DNS标准 RFC1034 的命名规则，以免给后续改造埋坑。例如哪一天想要搬到云上去，发现以前用的名字不支持，那就要再改一遍名，成本巨大。

我认为更好的方式是采用更为严格的限制：集群的名称不应该包括点（dot）。应当仅使用小写字母，数字，以及减号连字符（hyphen）-。这样，集群中的所有对象都可以使用这个名称作为前缀，用于各种各样的地方，而不用担心打破某些约束。即集群命名规则为：

cluster_name := [a-z][a-z0-9-]*

之所以强调不要在集群名称中用点，是因为以前很流行一种命名方式，例如com.foo.bar。即由点分割的层次结构命名法。这种命名方式虽然简洁名快，但有一个问题，就是用户给出的名字里可能有任意多的层次，数量不可控。如果集群需要与外部系统交互，而外部系统对于命名有一些约束，那么这样的名字就会带来麻烦。一个最直观的例子是K8s中的Pod，Pod的命名规则中不允许出现.。

集群命名的内涵，建议采用-分隔的两段式，三段式名称，例如：

<集群类型>-<业务>-<业务线>

比如：pg-test-tt就表示tt 业务线下的test集群，类型为pg。pg-user-fin表示fin业务线下的user服务。当然，采集多段命名最好还是保持段数固定。

节点命名

节点命名建议采用与k8s Pod一致的命名规则，即

<cluster_name>-<seq>

Node的名称会在集群资源分配阶段确定下来，每个节点都会分配到一个序号${seq}，从0开始的自增整型。这个与k8s中StatefulSet的命名规则保持一致，因此能够做到云上云下一致管理。

例如，集群pg-test有三个节点，那么这三个节点就可以命名为：

pg-test-0, pg-test-1和pg-test2。

节点的命名，在整个集群的生命周期中保持不变，便于监控与管理。

实例命名

对于数据库来说，通常都会采用独占式部署方式，一个实例占用整个机器节点。PG实例与Node是一一对应的关系，因此可以简单地采用Node的标识符作为Instance的标识符。例如，节点pg-test-1上的PG实例名即为：pg-test-1，以此类推。

采用独占部署的方式有很大优势，一个节点即一个实例，这样能最小化管理复杂度。混部的需求通常来自资源利用率的压力，但虚拟机或者云平台可以有效解决这种问题。通过vm或pod的抽象，即使是每个redis（1核1G）实例也可以有一个独占的节点环境。

作为一种约定，每个集群中的0号节点（Pod），会作为默认主库。因为它是初始化时第一个分配的节点。

服务命名

通常来说，数据库对外提供两种基础服务：primary 读写服务，与standby只读副本服务。

那么服务就可以采用一种简单的命名规则：

<cluster_name>-<service_name>

例如这里pg-test集群就包含两个服务：读写服务pg-test-primary与只读副本服务pg-test-standby。

还有一种流行的实例/节点命名规则：<cluster_name>-<service_role>-<sequence>，即把数据库的主从身份嵌入到实例名称中。这种命名方式有好处也有坏处。好处是管理的时候一眼就能看出来哪一个实例/节点是主库，哪些是从库。缺点是一但发生Failover，实例与节点的名称必须进行调整才能维持一执性，这就带来的额外的维护工作。此外，服务与节点实例是相对独立的概念，这种Embedding命名方式扭曲了这一关系，将实例唯一隶属至服务。但复杂的场景下这一假设可能并不满足。例如，集群可能有几种不同的服务划分方式，而不同的划分方式之间很可能会出现重叠。

• 可读从库（解析至包含主库在内的所有实例）
• 同步从库（解析至采用同步提交的备库）
• 延迟从库，备份实例（解析至特定具体实例）

因此，不要把服务角色嵌入实例名称，而是在服务中维护目标实例列表。

小结

命名属于相当经验性的知识，很少有地方会专门会讲这件事。这种“细节”其实往往能体现出命名者的一些经验水平来。

标识对象不仅仅可以通过ID和名称，还可以通过标签（Label）和选择器（Selector）。实际上这一种做法会更具有通用性和灵活性，本系列下一篇文章（也许）将会介绍数据库对象的标签设计与管理。

微信公众号原文

PostgreSQL的KPI

管数据库和管人差不多，都需要定KPI（关键性能指标）。那么数据库的KPI是什么？本文介绍了一种衡量PostgreSQL负载的方式：使用一种单一横向可比，与负载类型和机器类型基本无关的指标，名曰PG Load（PG负载）。

0x01 Introduction

在现实生产中，经常会有衡量数据库性能与负载，评估数据库水位的需求。一种最朴素的形式就是，能不能有一个类似于KPI的单一指标，能直接了当地告诉用户他心爱的数据库负载有没有超过警戒线？工作量到底饱和不饱和？

当然这里其实隐含着一个重要信息，即用户期待的负载指标是一个饱和度（Saturation）指标，所谓饱和度，即服务容量有多”满“，通常是系统中目前最为受限的某种资源的某个具体指标的度量。通常来说，0%的饱和度意味着系统完全空闲，100%的饱和度意味着满载，系统在达到100%利用率前就会出现性能的严重下降，因此设定指标时还需要包括一个利用率目标，或者说水位红线、黄线，当系统瞬时负载超过红线时应当触发告警，长期负载超过黄线时应当进行扩容。

不幸的是，定义系统有多”饱和“并不是一件容易的事情，往往需要借助某些间接指标。评估一个数据库的负载程度，传统上通常会基于这样几类指标进行综合评估：

• 流量：每秒查询数量QPS，或每秒事务数量TPS。

• 延迟：查询平均响应时间 Query RT，或事务平均响应时间Xact RT

• 饱和度：机器负载（Load），CPU使用率，磁盘读写带宽饱和度，网卡IO带宽饱和度

• 错误：数据库客户端连接排队

这些指标对于数据库性能评估都很有参考意义，但它们也都存在各式各样的问题。

0x02 常用评估指标的问题

让我们来看一看，这些现有的常用指标都有哪些问题。

第一个Pass的当然是错误类指标，譬如连接池排队。错误类指标最大的问题就是，当错误出现时，饱和度可能已经没有意义了。评估饱和度的一个重要原因就是用于预防系统过载，如果系统已经过载大量报错，那么使用错误现象反过来定义饱和度是没有意义的。此外，错误类指标难以精确量化。我们只能说：当连接池出现排队时，数据库负载比较大；队列越长，负载越大；没有排队时，数据库负载不怎么大，仅此而已。这样的定义当然也无法让人满意。

第二个Pass的则是系统层（机器级别）指标，数据库运行在机器上，CPU使用率，IO使用率这样的指标与数据库负载程度密切相关，如果CPU和IO是瓶颈，理论上当然是可以直接使用瓶颈资源的饱和度指标作为数据库的饱和指标，但这一点并非总是成立的，有可能系统瓶颈在于数据库本身。而且严格来说它们是机器的KPI而不是DB的KPI，评估数据库负载时当然可以参照系统层的指标，但DB层也应该有本层的评估指标。要先有数据库本身的饱和度指标，才可以去比较底层资源和数据库本身到底谁先饱和谁是瓶颈。这条原则同样适用于应用层观察到的指标。

流量类的指标很有潜力，特别是QPS，TPS这样的指标相当具有代表性。但这些指标也存在问题。一个数据库实例上的查询往往是五花八门各式各样的，一个耗时10微秒的查询和一个10秒的查询在统计时都被算为一个Q，类似于QPS这样的指标无法进行横向比较，只有比较粗略的参考意义，甚至当查询类型发生变化时，都无法和自己的历史数据进行纵向比较。此外也很难针对QPS、TPS这样的指标设置利用率目标，同一个数据库执行SELECT 1可以打到几十万的QPS，但执行复杂SQL时可能就只能打到几千的QPS。不同负载类型和机器硬件会对数据库的QPS上限产生显著影响，只有当一个数据库上的查询都是高度单一同质且没有复杂变化的条件下，QPS才有参考意义，在这种苛刻条件下倒是可以通过压力测试设定一个QPS的水位目标。

比起QPS/TPS，RT（响应时间 Response Time）这样的指标反而更具有参考价值。因为响应时间增加往往是系统饱和的前兆。根据经验法则，数据库的负载越大，查询与事务的平均响应时间也会越高。RT相比QPS的一个优势是**，RT是可以设置一个利用率目标的**，比如可以为RT设定一个绝对阈值：不允许生产OLTP库上出现RT超过1ms的慢查询。但QPS这样的指标就很难画出红线来。不过，RT也有自己的问题。第一个问题是它依然是定性而非定量的，延迟增加只是系统饱和的预警，但没法用来精确衡量系统的饱和度。第二个问题通常能从数据库与中间件获取到的RT统计指标都是平均值，但真正起到预警效果的有可能是诸如P99，P999这样的统计量。

这里把常用指标都批判了一番，到底什么样的指标适合作为数据库本身的饱和度呢？

0x03 衡量PG的负载

我们不妨参考一下**机器负载（Node Load）和CPU利用率（CPU Utilization）**的评估指标是如何设计的。

机器负载（Node Load）

想要看到机器的负载水平，可以在Linux系统中使用top命令。top命令的第一行输出就醒目地打印出当前机器1分钟，5分钟，15分钟的平均负载水平。

$ top -b1
top - 19:27:38 up 18:49,  1 user,  load average: 1.15, 0.72, 0.71

这里load average后面的三个数字分别表示最近1分钟，5分钟，15分钟系统的平均负载水平。

那么这个数字到底是什么意思呢？简单的解释是，这个数字越大机器越忙。

在单核CPU的场景下，Node Load（以下简称负载）是一个非常标准的饱和度指标。对于单核CPU，负载为0时CPU处于完全空闲的状态，负载为1（100%）时，CPU正好处于满载工作的状态。负载大于100%时，超出100%部分比例的任务正在排队。

Node Load也有自己的利用率目标，通常的经验是在单核情况下：0.7（70%）是黄线，意味着系统有问题，需要尽快检查；1.0（100%）是红线，负载大于1意味着进程开始堆积，需要立即着手处理。5.0（500%）是死线，意味着系统基本上已经堵死了。

对于多核CPU，事情稍微有点不一样。假设有n个核，那么当系统负载为n时，所有CPU都处于满载工作的状态；而当系统负载为n/2时，姑且可以认为一半CPU核正在满载运行。因而48核CPU的机器满载时的负载为48。总的来说，如果我们把机器负载除以机器的CPU核数，得到的指标就与单核场景下保持一致了（0%空载，100%满载）。

CPU利用率（CPU Utilization）

另一个很有借鉴意义的指标是CPU利用率（CPU Utilization）。CPU利用率其实是通过一个简单的公式计算出来的，对于单核CPU：

1 - irate(node_cpu_seconds_total{mode="idle"}[1m]

这里node_cpu_seconds_total{mode="idle"}是一个计数器指标，表示CPU处于空闲状态的总时长。irate函数会用该指标对时间进行求导，得出的结果是，每秒CPU处于空闲状态的时长，换句话说也就是CPU空闲率。用1减去该值就得到了CPU的利用率。

对于多核CPU来说，只需要把每个CPU核的利用率加起来，除以CPU的核数，就可以得到CPU的整体利用率。

那么这两个指标对于PG的负载又有什么借鉴意义呢？

数据库负载（PG Load）

PG的负载是不是也可以采用类似于CPU利用率和机器负载的方式来定义？当然可以，而且这是一个极棒的主意。

让我们先来考虑单进程情况下的PG负载，假设我们需要这样一个指标，当该PG进程完全空闲时负载因子为0，当该进程处于满载状态时负载为1（100%）。类比CPU利用率的定义，我们可以使用“单个PG进程处于活跃状态的时长占比”来表示“单个PG后端进程的利用率”。

如图1所示，在一秒的统计周期内，PG处于活跃（执行查询或者执行事务）状态的时长为0.6秒，那么这一秒内的PG负载就是60%。如果这个唯一的PG进程在整个统计周期中都处于忙碌状态，而且还有0.4秒的任务在排队，如那么就可以认为PG的负载为140%。

对于并行场景，计算方法与多核CPU的利用率类似，首先把所有PG进程在统计周期（1s）内处于活跃状态的时长累加，然后除以“可用的PG进程/连接数”，或者说“可用并行数”，即可得到PG本身的利用率指标，如图3所示。两个PG后端进程分别有200ms+400ms与800ms的活跃时长，那么整体的负载水平为：(0.2s + 0.4s + 0.8s) / 1s / 2 = 70%

总结一下，某一段时间内PG的负载可以定义为：

pg_load = pg_active_seconds / time_peroid / parallel

• pg_active_seconds是该时间段内所有PG进程处于活跃状态的时长之和。

• time_peroid是负载计算的统计周期，通常为1分钟，5分钟，15分钟，以及实时（小于10秒）。

• parallel 是PostgreSQL的可用并行数，后面会详细解释。

因为前两项之商实际上就是一段时间内的每秒活跃时长总数，因此这个公式进一步可以简化为活跃时长对时间的导数除以可用并行数，即：

rate(pg_active_seconds[time_peroid]) / parallel

time_peroid通常是固定的常量（1，5，15分钟），所以问题就是如何获取PG进程活跃总时长pg_active_seconds这个指标，以及如何评估计算数据库可用并行数max_parallel 了。

0x04 计算PG的负载饱和度

事务还是查询？

当我们说数据库进程 活跃/空闲 时，究竟在说什么？ PG处于活跃状态，到底是什么意思？如果PG后端进程正在执行查询，那么当然可以认为PG正处于忙碌状态。但如果如上图4所示，PG进程正在执行一个交互式事务，但没有实际执行查询，即所谓的“Idle in Transaction”状态，又应该怎么计算“活跃时长”呢？图4中两个查询中空闲的那200ms时间。那么这段时间应该算作“活跃”，还是算作“空闲”呢？

这里的核心问题是怎么定义活跃状态：数据库进程位于事务中算活跃，还是只有当实际执行查询时才算活跃。对于没有交互式事务的场景，一个查询就是一个事务，用哪种方式都一样，但对于多语句，特别是交互式的多语句事务，这两者就有比较明显的区别了。从资源使用的角度看，没有执行查询也就意味着没有消耗数据库本身的资源。但空闲着的事务本身会占用连接导致连接无法复用，Idle In Transaction本身也应当是一种极力避免的情况。总的来说，这两种定义方式都可以，使用事务的方式会略微高估应用负载，但从负载评估的角度可能会更为合适。

如何获取活跃时长

决定了数据库后端进程的活跃定义后，第二个问题就是，如何获取一段时间的数据库活跃时长？不幸的是在PG中，用户很难通过数据库本身获取这一性能指标。PG提供了一个系统视图：pg_stat_activity，可以看到当前运行着的Postgres进程里列表，但这是一个时间点快照，只能大致告诉在当前时刻，数据库的后端进程中有多少个处于活跃状态，有多少个处于空闲状态。统计一段时间内数据库处于活跃状态的时长，就成了一个难题。一种解决方案是使用类似于Load的计算方式，通过周期性地采样PG中活跃进程的数量，计算出一个负载指标来。不过，这里有更好的办法，但是需要中间件的协助参与。

数据库中间件对于性能监控非常重要，因为很多指标数据库本身并没有提供，只有通过中间件才能暴露出来。以Pgbouncer为例，Pgbouncer在内部维护了一系列统计计数器，使用SHOW STATS可以打印出这些指标，诸如：

• total_xact_count：总共执行了多少个事务
• total_query_count：总共执行了多少个查询
• total_xact_time：总共花费在事务执行的时长
• total_query_time：总共花费在查询执行上的时长

这里total_xact_time就是我们需要的数据，它记录了Pgbouncer中间件中花费在某个数据库上的事务总耗时。我们只需要用这个指标对时间求导，就可以得到想要的数据：每秒活跃时长占比。

这里使用Prometheus的PromQL表达计算逻辑，首先对事务耗时计数器求导，分别算出其1分钟，5分钟，15分钟，以及实时粒度（最近两次采样点之间）上的每秒活跃时长。再上卷求和，将数据库层次的指标上卷为实例级别的指标。（连接池SHOW STATS这里的统计指标是以数据库为单位的，因此在计算实例级别的总活跃时长时，应当上卷求和，消除数据库维度的标签：sum without(datname)）

- record: pg:ins:xact_time_realtime
expr: sum without (datname) (irate(pgbouncer_stat_total_xact_time{}[1m]))
- record: pg:ins:xact_time_rate1m
expr: sum without (datname) (rate(pgbouncer_stat_total_xact_time{}[1m]))
- record: pg:ins:xact_time_rate5m
expr: sum without (datname) (rate(pgbouncer_stat_total_xact_time{}[5m]))
- record: pg:ins:xact_time_rate15m
expr: sum without (datname) (rate(pgbouncer_stat_total_xact_time{}[15m]))

这样计算得到的结果指标已经可以相对本身进行纵向比较，并在同样规格的实例间进行横向比较了。而且无论数据库的负载类型怎样，都可以使用这个指标。

不过不同规格的实例，仍然没法使用这个指标进行对比。比如对于单核单连接PG，满载时每秒活跃时长可能是1秒，也就是100%利用率。而对于64核64连接的PG，满载时每秒活跃时长是64秒，那么就是6400%的利用率。因此，还需要一个归一化的处理，那么问题又来了。

可用并行数如何定义？

不同于CPU利用率，PG的可用并行数并没有一个清晰的定义，而且跟负载类型有一些微妙的关系。但能够确定的是，在一定范围内，最大可用并行与CPU的核数呈粗略的线性关系。当然这个结论的前提是数据库最大连接数显著超过CPU核数，如果在64核的CPU上只允许数据库建立30条连接，那么可以肯定最大可用并行就是30而不是CPU核数64。软件的并行最终还是要由硬件的并行度来支撑，因此我们可以简单的使用实例的CPU核数作为可用并行数。

在64核的CPU上运行64个活跃PG进程，则其负载为（6400% / 64 = 100%）。同理运行128个活跃PG进程，负载就是（12800% / 64 = 200%）。

那么利用上面计算得到的每秒活跃时长指标，就可以计算出实例级别的PG负载指数了。

- record: pg:ins:load0
expr:  pg:ins:xact_time_realtime / on (ip) group_left()  node:ins:cpu_count
- record: pg:ins:load1
expr: pg:ins:xact_time_rate1m  / on (ip) group_left()  node:ins:cpu_count
- record: pg:ins:load5
expr: pg:ins:xact_time_rate5m  / on (ip) group_left()  node:ins:cpu_count
- record: pg:ins:load15
expr: pg:ins:xact_time_rate15m  / on (ip) group_left()  node:ins:cpu_count

PG LOAD的另一种解释

如果我们仔细审视PG Load的定义，其实可以发现每秒活跃时长这个指标，其实可以粗略等价于：TPS x XactRT，或者QPS x Query RT。这个也很好理解，假设我QPS为1000，每个查询RT为1ms，则每秒花费在查询上的时间为 1000 * 1ms = 1s。

因此，PG Load可以视为一个由三个核心指标复合而成的衍生指标：tps * xact_rt / cpu_count

TPS，RT用于负载评估都有各自的问题，但它们通过简单的乘法结合成一个新的复合指标，一下子就显示出了神奇的力量。（尽管实际上是通过其他更准确的方式计算出来的）

0x05 PG Load的实际效果

接下来，我们来看一下PG Load用于实际生产环境的表现。

PG Load最直接的作用有两个，告警以及容量评估。

Case 1: 用于报警：慢查询堆积导致的服务不可用

下图是一次生产事故的现场，由于某业务上线了一个慢查询，瞬间导致连接池被慢查询占据，发生堆积。可以看出PG Load和RT都很及时地反映出了故障的情况，而TPS看上去则是掉了一个坑，并不是特别显眼。

从效果上看，PG Load1与PG Load0（实时负载）是一个相当灵敏的指标，对于大多数与压力负载有关的故障都能及时准确作出反应。所以被我们采纳为核心报警指标。

PG Load的利用率目标有一些经验值：黄线通常为50%，即需要引起关注的阈值；红线通常为70%，即报警线，需要立刻采取行动的阈值；500%或更高通常意味着这个实例已经被打崩了。

Case 2：用于水位评估与容量规划

比起报警，水位评估与容量规划更像是PG Load的核心用途。毕竟报警之类的的需求还是可以通过延迟，排队连接等指标来满足的。

这里，PG集群的15分钟负载是一个很好的参考值。通过这个指标的历史均值，峰值，以及其他一些统计量，我们可以很轻松地看出哪些集群处于高负载状态需要扩容，哪些集群处于低资源利用率状态需要缩容。

CPU利用率是另一个很重要的容量评估指标。我们可以看出，PG Load与CPU Usage有着很密切的关系。不过相比CPU使用率，PG Load更为纯粹地反映了数据库本身的负载水平，滤除了机器上的无关负载，也可以滤除掉数据库维护工作（备份，清理，垃圾回收）产生的杂音，更为丝滑平顺。因此非常适合用于容量评估。

当系统负载长期位于30%~50%时，就应该考虑进行扩容了。

0x06 结论

本文介绍了一种定量衡量PG负载的方式，即PG Load指标

该指标可以简单直观地反映数据库实例的负载水平

该指标非常适合作容量评估之用，也可以作为核心报警指标。

该指标可以基本无视负载类型与机器类型，进行纵向历史比较与横向水位比较。

该指标可以通过简单的方式计算得出，即每秒后端进程活跃总时长除以可用并发数。

该指标所需数据需要从数据库中间件获取

PG Load的0代表空载，100%代表满载。黄线经验值为50%，红线经验值为70%，

PG Load是一个好指标👍

微信公众号原文

在线修改PG字段类型

场景

在数据库的生命周期中，有一类需求是很常见的，修改字段类型。例如：

• 使用INT作为主键，结果发现业务红红火火，INT32的21亿序号不够用了，想要升级为BIGINT
• 使用BIGINT存身份证号，结果发现里面有个X需要改为TEXT类型。
• 使用FLOAT存放货币，发现精度丢失，想要修改为Decimal
• 使用TEXT存储JSON字段，想用到PostgreSQL的JSON特性，修改为JSONB类型。

那么，如何应对这种需求呢？

常规操作

通常来说，ALTER TABLE可以用来修改字段类型。

ALTER TABLE tbl_name ALTER col_name TYPE new_type USING expression;

修改字段类型通常会重写整个表。作为一个特例，如果修改后的类型与之前是二进制兼容的，则可以跳过表重写的过程，但是如果列上有索引，索引还是需要重建的。二进制兼容的转换可以使用以下查询列出。

SELECT t1.typname AS from, t2.typname AS To
FROM pg_cast c
         join pg_type t1 on c.castsource = t1.oid
         join pg_type t2 on c.casttarget = t2.oid
where c.castmethod = 'b';

刨除PostgreSQL内部的类型，二进制兼容的类型转换如下所示

text     → varchar 
xml      → varchar 
xml      → text    
cidr     → inet    
varchar  → text    
bit      → varbit  
varbit   → bit     

常见的二进制兼容类型转换基本就是这两种：

• varchar(n1) → varchar(n2) （n2 ≥ n1）（比较常用，扩大长度约束不会重写，缩小会重写）

• varchar ↔ text （同义转换，基本没啥用）

也就是说，其他的类型转换，都会涉及到表的重写。大表的重写是很慢的，从几分钟到十几小时都有可能。一旦发生重写，表上就会有AccessExclusiveLock，阻止一切并发访问。

如果是一个玩具数据库，或者业务还没上线，或者业务根本不在乎停机多久，那么整表重写的方式当然是没有问题的。但绝大多数时候，业务根本不可能接受这样的停机时间。所以，我们需要一种在线升级的办法。在不停机的情况完成字段类型的改造。

基本思路

在线改列的基本原理如下：

• 创建一个新的临时列，使用新的类型

• 旧列的数据同步至新的临时列

  • 存量同步：分批更新
  • 增量同步：更新触发器

• 处理列依赖：索引

• 执行切换

  • 处理列以来：约束，默认值，分区，继承，触发器

  • 通过列重命名的方式完成新旧列切换

在线改造的问题在于锁粒度拆分，将原来一次长期重锁操作，等效替代为多个瞬时轻锁操作。

原来ALTER TYPE重写过程中，会加上AccessExclusiveLock，阻止一切并发访问，持续时间几分钟到几天。

• 添加新列：瞬间完成：AccessExclusiveLock
• 同步新列-增量：创建触发器，瞬间完成，锁级别低。
• 同步新列-存量：分批次UPDATE，少量多次，每次都能快速完成，锁级别低。
• 新旧切换：锁表，瞬间完成。

让我们用pgbench的默认用例来说明在线改列的基本原理。假设我们希望在pgbench_accounts有访问的情况下修改abalance字段类型，从INT修改为BIGINT，那么应该如何处理呢？

1. 首先，为pgbench_accounts创建一个名为abalance_tmp，类型为BIGINT的新列。
2. 编写并创建列同步触发器，触发器会在每一行被插入或更新前，使用旧列abalance同步到

详情如下所示：

-- 操作目标：升级 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;