聊一聊 Failover Slot
xiongcc约 15 分钟1前言
今天这个话题或许有点 excited，众所周知，在逻辑解码的天空中飘着的几朵小乌云还一直乌泱泱地压着，诸如
schema的复制 序列的复制 standby logical decoding（16可能会支持，已经有相关commit） failover slot 等等。
今天官方网站上 Latest News 里面的 PG Failover Slots (pg_failover_slots) 十分惹眼，因为截止15，逻辑解码一直有个疑难杂症未得到根除：Failover Slot。逻辑解码依赖复制槽，因为复制槽持有着消费者的"消费状态"，因此数据库不会将消费者还没处理的消息（WAL）清理掉。
Provide guarantees that WAL segments are not removed until consumed
Provide protection against relevant rows being removed by (auto)vacuum
Logical replication slots are tightly coupled with particular database.
但是比较遗憾的是，目前复制槽不会被同步到备机上，因此一旦发生切换，原来的复制槽将不能继续使用。另外备库也不支持逻辑解码，因此客户端/订阅者也无法在备库创建逻辑复制插槽。
所以看到这个插件我一下就来了精神，尝下鲜。
2使用
功能
先瞅瞅这个插件支持哪些功能：
copy any missing slots from primary to standby，将任何缺失的复制槽从主库复制到备用库 remove any slots from standby that are not found on primary，在备库中删除在主库中找不到的复制槽 periodically synchronize position of slots on standby based on primary，基于主节点定期同步备库复制槽的位点 ensure that selected standbys receive data before any of the logical slot walsenders can send data to consumers，确保选定的备库在任何逻辑复制槽 walsenders 可以向消费者发送数据之前接收到数据
如何同步
The slots are not synchronized to the standby immediately, because of consistency reasons. The standby can be too behind logical slots, or too ahead of logical slots on primary when the pg_failover_slots extension is activated, so the extension does verification and only synchronizes slots when it's actually safe.
由于一致性原因，插槽不会立即同步到备用数据库。当 pg_failover_slots 扩展被激活时，备库可能远落后于逻辑复制槽，或者远超前于主数据库上的逻辑复制槽，因此该扩展会进行验证并且仅在实际安全时才同步插槽。
This, however brings a need to verify that the slots are synchronized and that the standby is actually ready to be a failover target with consistent logical decoding for all slots. This only needs to be done initially, once the slots are synchronized the first time, they will always be consistent as long as the extension is active in the cluster.
然而，这就需要验证复制槽是否同步，以及备库是否对所有复制槽逻辑解码一致以真正准备好成为故障转移的目标。这只需要在最开始阶段进行，一旦复制槽首次同步，只要扩展在集群中处于活动状态，它们就会一直保持一致。
验证的方式很简单，在备库上查询 pg_replication_slots 即可，状态为 false 的即为准备就绪，为 active 代表正在初始化。
# SELECT slot_name, active FROM pg_replication_slots WHERE slot_type = 'logical';
slot_name | active
-----------------+--------
regression_slot1 | f
regression_slot2 | f
regression_slot3 | t
这意味着 regression_slot1 和 regression_slot2 已从主库同步到备库，而 regression_slot3 仍在同步中。如果在此阶段发生故障转移，则 regression_slot3 将丢失。全部成为 false 之后，就可以放心执行 Failover 了。
配置项
shared_preload_libraries：用于 failover 和 switchover 的主备都要配置
synchronize_slot_names：支持通配符，默认所有的逻辑复制槽都会复制
drop_extra_slots：是否删除备库上存在而主库上不存在的复制槽，根据 synchronize_slot_names 参数过滤，默认是 true，比如备库上多了一个 myslot，那么会将其删掉
primary_dsn：指定备库获取复制槽信息时用于连接到主库的连接方式，默认同 primary_conninfo，如果连接字符串中有密码字段，则不能使用 primary_conninfo，因为会被混淆并且 pg_failover_slots 实际上看不到密码，这种情况下就得配置该参数了
standby_slot_names：确保 failover 候选流复制备库在所有更改对任何订阅者可见之前已接收并刷新所有的更改，这样可以确保提交不会在 failover 到逻辑复制槽消费者的备库时消失。位于此列表中的 slot 会被 walsender 特殊处理（具体如何处理暂不清楚）这会确保在 walsender 发送逻辑复制槽的这些更改之前，所有本地更改都被发送并刷新到 pg_failover_slots.standby_slot_names 中的复制槽，通常是物理复制槽
Effectively, it provides a synchronous replication barrier between the named list of slots and all the consumers of logically decoded streams from walsender.
实际上，它在指定的插槽列表和来自 walsender 的逻辑解码流的所有消费者之间提供了一个同步复制屏障。
standby_slots_min_confirmed：类似于基于优选提交，必须等待多少个 standby slot 确认
standby_slot_names 这个参数稍微复杂点，让我们再捋捋。主要看下 README 里面提到的两个异常：
Without this safeguard, two anomalies are possible where a commit can be received by a subscriber and then vanish from the provider on failover because the failover candidate hadn't received it yet: 如果没有这种保护措施，可能会出现两种异常情况，订阅者可以收到提交，然后在故障转移时从提供者那里消失，因为故障转移候选者尚未收到它 For 1+ subscribers, the subscriber may have applied the change but the new provider may execute new transactions that conflict with the received change, as it never happened as far as the provider is concerned; 对于 1+ 订阅者，订阅者可能已经应用了更改，但新的提供者可能会执行与收到的更改冲突的新事务，因为就提供者而言，这从未发生过； For 2+ subscribers, at the time of failover, not all subscribers have applied the change. The subscribers now have inconsistent and irreconcilable states because the subscribers that didn't receive the commit have no way to get it now. 对于 2 个以上的订阅者，在故障转移时，并非所有订阅者都应用了更改。订阅者现在有不一致和不可调和的状态，因为没有收到提交的订阅者现在没有办法得到它。 Setting pg_failover_slots.standby_slot_nameswill (by design) cause subscribers to lag behind the provider if the provider's failover-candidate replica(s) are not keeping up. Monitoring is thus essential. 如果提供者的故障转移候选副本没有跟上，设置 pg_failover_slots.standby_slot_names 将（按设计）导致订阅者落后于提供者。因此，监测必不可少。
因此根据叙述，还依赖额外的物理复制槽。
3实操
让我们搭建一下，启动之后，主备都会新增一个额外的 pg_failover_slots worker 进程
1 21826 21826 21826 ? -1 Ss 1000 0:00 /usr/pgsql-15/bin/postgres -D 15data ---主库，发布端
21826 21827 21827 21827 ? -1 Ss 1000 0:00 \_ postgres: logger
21826 21828 21828 21828 ? -1 Ss 1000 0:00 \_ postgres: checkpointer
21826 21829 21829 21829 ? -1 Ss 1000 0:00 \_ postgres: background writer
21826 21831 21831 21831 ? -1 Ss 1000 0:00 \_ postgres: walwriter
21826 21832 21832 21832 ? -1 Ss 1000 0:00 \_ postgres: autovacuum launcher
21826 21833 21833 21833 ? -1 Ss 1000 0:00 \_ postgres: pg_failover_slots worker
21826 21834 21834 21834 ? -1 Ss 1000 0:00 \_ postgres: logical replication launcher
21826 21840 21840 21840 ? -1 Ss 1000 0:00 \_ postgres: postgres postgres [local] idle
21826 32528 32528 32528 ? -1 Ss 1000 0:00 \_ postgres: walsender postgres [local] streaming 23/16000148
1 32432 32432 32432 ? -1 Ss 1000 0:00 /usr/pgsql-15/bin/postgres -D 15data_logical ---订阅端
32432 32433 32433 32433 ? -1 Ss 1000 0:00 \_ postgres: logger
32432 32434 32434 32434 ? -1 Ss 1000 0:00 \_ postgres: checkpointer
32432 32435 32435 32435 ? -1 Ss 1000 0:00 \_ postgres: background writer
32432 32437 32437 32437 ? -1 Ss 1000 0:00 \_ postgres: walwriter
32432 32438 32438 32438 ? -1 Ss 1000 0:00 \_ postgres: autovacuum launcher
32432 32440 32440 32440 ? -1 Ss 1000 0:00 \_ postgres: logical replication launcher
1 32521 32521 32521 ? -1 Ss 1000 0:00 /usr/pgsql-15/bin/postgres -D 15data_bak ---备库
32521 32522 32522 32522 ? -1 Ss 1000 0:00 \_ postgres: logger
32521 32523 32523 32523 ? -1 Ss 1000 0:00 \_ postgres: checkpointer
32521 32524 32524 32524 ? -1 Ss 1000 0:00 \_ postgres: background writer
32521 32525 32525 32525 ? -1 Ss 1000 0:00 \_ postgres: startup recovering 000000010000002300000016
32521 32527 32527 32527 ? -1 Ss 1000 0:00 \_ postgres: walreceiver streaming 23/16000148
备库需要打开 hot_standby_feedback
2023-04-18 16:33:29.214 CST [32459] LOG: starting pg_failover_slots replica worker
2023-04-18 16:33:29.215 CST [32459] ERROR: cannot synchronize replication slot positions because hot_standby_feedback is off
2023-04-18 16:33:29.215 CST [32454] LOG: background worker "pg_failover_slots worker" (PID 32459) exited with exit code 1
2023-04-18 16:33:29.217 CST [32460] LOG: started streaming WAL from primary at 23/16000000 on timeline 1
而且还需要配置 primary_slot_name（此时我没有配置 standby_slot_names，看样子也需要一个额外的复制槽），当然必须得是物理的，不然会提示 cannot use a logical replication slot for physical replication
2023-04-18 16:37:22.603 CST [32526] LOG: starting pg_failover_slots replica worker
2023-04-18 16:37:22.603 CST [32526] ERROR: cannot synchronize replication slot positions because primary_slot_name is not set
2023-04-18 16:37:22.604 CST [32521] LOG: background worker "pg_failover_slots worker" (PID 32526) exited with exit code 1
然后主库上创建一个发布，现在就有两个复制槽了，一个物理一个逻辑
postgres=# select slot_name,plugin,slot_type,temporary,active,active_pid,xmin,catalog_xmin,restart_lsn,confirmed_flush_lsn from pg_replication_slots ;
slot_name | plugin | slot_type | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+----------+-----------+-----------+--------+------------+---------+--------------+-------------+---------------------
myslot | | physical | f | t | 32576 | 1997738 | 1997738 | 23/16007F88 |
sub1 | pgoutput | logical | f | t | 32617 | | 1997738 | 23/16007F50 | 23/16007F88
(2 rows)
备库上的复制槽也被顺利复制
[postgres@xiongcc ~]$ psql -p 5433
psql (15.1)
Type "help" for help.postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# select slot_name,plugin,slot_type,temporary,active,active_pid,xmin,catalog_xmin,restart_lsn,confirmed_flush_lsn from pg_replication_slots ;
slot_name | plugin | slot_type | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+----------+-----------+-----------+--------+------------+------+--------------+-------------+---------------------
sub1 | pgoutput | logical | f | f | | | 1997738 | 23/16007F50 | 23/16007F88
(1 row)
此时的状态
1 21826 21826 21826 ? -1 Ss 1000 0:00 /usr/pgsql-15/bin/postgres -D 15data
21826 21827 21827 21827 ? -1 Ss 1000 0:00 \_ postgres: logger
21826 21828 21828 21828 ? -1 Ss 1000 0:00 \_ postgres: checkpointer
21826 21829 21829 21829 ? -1 Ss 1000 0:00 \_ postgres: background writer
21826 21831 21831 21831 ? -1 Ss 1000 0:00 \_ postgres: walwriter
21826 21832 21832 21832 ? -1 Ss 1000 0:00 \_ postgres: autovacuum launcher
21826 21833 21833 21833 ? -1 Ss 1000 0:00 \_ postgres: pg_failover_slots worker
21826 21834 21834 21834 ? -1 Ss 1000 0:00 \_ postgres: logical replication launcher
21826 21840 21840 21840 ? -1 Ss 1000 0:00 \_ postgres: postgres postgres [local] idle
21826 32576 32576 32576 ? -1 Ss 1000 0:00 \_ postgres: walsender postgres [local] streaming 23/16007F88
21826 32617 32617 32617 ? -1 Ss 1000 0:00 \_ postgres: walsender postgres ::1(49656) START_REPLICATION
1 32432 32432 32432 ? -1 Ss 1000 0:00 /usr/pgsql-15/bin/postgres -D 15data_logical
32432 32433 32433 32433 ? -1 Ss 1000 0:00 \_ postgres: logger
32432 32434 32434 32434 ? -1 Ss 1000 0:00 \_ postgres: checkpointer
32432 32435 32435 32435 ? -1 Ss 1000 0:00 \_ postgres: background writer
32432 32437 32437 32437 ? -1 Ss 1000 0:00 \_ postgres: walwriter
32432 32438 32438 32438 ? -1 Ss 1000 0:00 \_ postgres: autovacuum launcher
32432 32440 32440 32440 ? -1 Ss 1000 0:00 \_ postgres: logical replication launcher
32432 32613 32613 32613 ? -1 Ss 1000 0:00 \_ postgres: postgres postgres [local] idle
32432 32616 32616 32616 ? -1 Ss 1000 0:00 \_ postgres: logical replication worker for subscription 36323
1 32568 32568 32568 ? -1 Ss 1000 0:00 /usr/pgsql-15/bin/postgres -D 15data_bak
32568 32569 32569 32569 ? -1 Ss 1000 0:00 \_ postgres: logger
32568 32570 32570 32570 ? -1 Ss 1000 0:00 \_ postgres: checkpointer
32568 32571 32571 32571 ? -1 Ss 1000 0:00 \_ postgres: background writer
32568 32572 32572 32572 ? -1 Ss 1000 0:00 \_ postgres: startup recovering 000000010000002300000016
32568 32573 32573 32573 ? -1 Ss 1000 0:00 \_ postgres: pg_failover_slots worker
32568 32574 32574 32574 ? -1 Ss 1000 0:00 \_ postgres: walreceiver streaming 23/16007F88
4分析
根据代码显式，将日志设置为 DEBUG2 就可以看到细节了
2023-04-18 16:59:12.489 CST [327] DEBUG: sendtime 2023-04-18 16:59:12.489366+08 receipttime 2023-04-18 16:59:12.489495+08 replication apply delay (N/A) transfer latency 1 ms
2023-04-18 16:59:12.489 CST [327] DEBUG: sending write 23/160080C0 flush 23/160080C0 apply 23/160080C0
2023-04-18 16:59:12.493 CST [326] DEBUG: got new restart lsn 23/16008088 at 23/16000000
2023-04-18 16:59:12.497 CST [326] DEBUG: synchronized existing slot sub1 to lsn (23/16008088) and catalog xmin (1997738)
2023-04-18 16:59:12.497 CST [326] DEBUG: starting replication slot synchronization from primary
2023-04-18 16:59:12.501 CST [326] DEBUG: established connection to remote backend with pid 330
2023-04-18 16:59:12.510 CST [326] DEBUG: updated xmin: 0 restart: 1
2023-04-18 16:59:12.510 CST [326] DEBUG: got new restart lsn 23/16008088 at 23/16000000
2023-04-18 16:59:12.514 CST [326] DEBUG: synchronized existing slot sub1 to lsn (23/16008088) and catalog xmin (1997738)
2023-04-18 16:59:12.589 CST [327] DEBUG: sending hot standby feedback xmin 1997738 epoch 0 catalog_xmin 1997738 catalog_xmin_epoch 0
2023-04-18 16:59:22.508 CST [327] DEBUG: sending write 23/160080C0 flush 23/160080C0 apply 23/160080C0
2023-04-18 16:59:22.608 CST [327] DEBUG: sending hot standby feedback xmin 1997738 epoch 0 catalog_xmin 1997738 catalog_xmin_epoch 0
2023-04-18 16:59:32.528 CST [327] DEBUG: sending write 23/160080C0 flush 23/160080C0 apply 23/160080C0
2023-04-18 16:59:32.628 CST [327] DEBUG: sending hot standby feedback xmin 1997738 epoch 0 catalog_xmin 1997738 catalog_xmin_epoch 0
2023-04-18 16:59:42.548 CST [327] DEBUG: sending write 23/160080C0 flush 23/160080C0 apply 23/160080C0 (reply requested)
2023-04-18 16:59:42.548 CST [327] DEBUG: sendtime 2023-04-18 16:59:42.548305+08 receipttime 2023-04-18 16:59:42.548446+08 replication apply delay (N/A) transfer latency 1 ms
2023-04-18 16:59:42.648 CST [327] DEBUG: sending hot standby feedback xmin 1997738 epoch 0 catalog_xmin 1997738 catalog_xmin_epoch 0
2023-04-18 16:59:52.568 CST [327] DEBUG: sending write 23/160080C0 flush 23/160080C0 apply 23/160080C0
2023-04-18 16:59:52.668 CST [327] DEBUG: sending hot standby feedback xmin 1997738 epoch 0 catalog_xmin 1997738 catalog_xmin_epoch 0
2023-04-18 17:00:02.587 CST [327] DEBUG: sending write 23/160080C0 flush 23/160080C0 apply 23/160080C0
2023-04-18 17:00:02.688 CST [327] DEBUG: sending hot standby feedback xmin 1997738 epoch 0 catalog_xmin 1997738 catalog_xmin_epoch 0
可以看到会每隔 10 秒向主库发送回 xmin 和 catalog_xmin
关于 hot_standby_feedback 这一段注释有所说明，
We could technically synchronize slot positions even on older versions of PostgreSQL but since logical decoding can't go over the timeline switch before PG10, it's pointless to have slots synchronized. Also, older versions can't keep catalog_xmin separate from xmin in hot standby feedback, so sending the feedback we need to preserve our catalog_xmin could cause severe table bloat on the master.
即使在旧版本的 PostgreSQL 上，我们在技术上也可以同步插槽位置，但由于逻辑解码无法在 PG10 之前妥善处理时间线（翻译为妥善处理是否正确？），因此同步复制槽毫无意义。此外，旧版本无法在 hot_standby_feedback 中将 catalog_xmin 与 xmin 分开，因此发送我们需要保留的 catalog_xmin 的 feedback 可能会导致 master 上出现严重的表膨胀。
因为我们知道逻辑解码解析出来的是一串"SQL"，数据库得知道当时的表结构，因为可能发生了表结构变更，比如加字段删字段，因此通过 catalog_xmin 来实现，并且由于备库通常是滞后于主库的，因此需要借助一个额外的物理复制槽来同步这个 catalog_xmin，让 vacuum/autovacuum 手下留人。
5Patroni的实现
我们知道，patroni在2.1.0也实现 failover slot，大致原理是：
通过 libpq 拷贝复制槽，同时使用 pg_read_binary_file() 函数读取复制槽信息，其实就是拷贝的主库的 pg_repslot 目录下的信息 在备节点创建复制槽后，使用 pg_replication_slot_advance() 更新复制槽的信息 复制槽的信息会添加到 DCS 中，由 Patroni 的主实例持续维护 类似 pg_failover_slots 插件，逻辑复制槽的所有备节点上都要打开 hot_standby_feedback 和 primary_slot_name Patroni 得打开 postgresql.use_slots，以确保每个备节点使用主节点上的复制槽
是不是很类似？或许 pg_failover_slots 就是 inspired from Patroni。
6小结
这么看来，pg_failover_slots 和 Patroni 的实现大有异曲同工之妙，只不过 patroni 还会借助 DCS 来进行管理，健壮性更强，另外在 16 版本里可能会支持 standby logical decoding，这个对于那些未使用 Patroni 作为 HA 组件的实例就要方便多了， standby logical decoding + pg_failover_slots，让 DTS/CDC 等场景更加健壮完美，飘着的小乌云又少了一朵。让我们拭目以待。另外今天看到②群一位老铁问了个问题
其实 mv_stats 插件就可以，目前已经成立了三个群（1群500，2群300，3群40），感兴趣入群唠嗑的老铁都可以后台回复加群。