PostgreSQL v16beta1允许在备库上进行逻辑解码

PostgreSQL v16beta1允许在备库上进行逻辑解码

概念描述

最近发布的pg16beta版本的发布说明中提到，关于逻辑复制的功能增强，其中一个是允许在备库上进行逻辑解码。

在之前的版本中，我们使用逻辑复制时，只可以在主节点使用逻辑解码和创建逻辑复制槽，上一篇中提到的问题，”使用逻辑复制时当数据库发生主备切换后，备库没有复制槽信息，导致故障期间的数据无法通过逻辑复制同步给订阅端“，其中关于备库创建复制槽的方法，我们是把主库$PGDATA/pg_replslot目录中的文件拷贝到备库，并重启备库，才可以在备库中看到该复制槽，如果后期v16正式版本中保留可以在备库创建复制槽的功能，就可以直接使用函数pg_create_logical_replication_slot在备库创建复制槽了。

但是备库不活跃复制槽的lsn依旧不会主动向前推进。

测试验证

测试备库可以进行逻辑解码，可以创建复制槽

测试使用到三个节点，分别是

​ server1:192.168.8.16（主库）测试主库可以作为逻辑复制的发布端

​ server2:192.168.8.17（server1的备库）测试备库可以作为逻辑复制的发布端，可以手动创建复制槽

​ server3:192.168.8.18（单点主库）作为逻辑复制的订阅端

可以看到192.168.8.16为主库，192.168.8.17为备库，复制槽sub1为逻辑复制槽且状态为active，订阅端为192.168.8.18，test_slots表通过定时任务每分钟插入一条数据

[postgres@server1 ~]$ hostname -i
192.168.8.16
[postgres@server1 ~]$ psql -p 5433
psql (16beta1)
Type "help" for help.

postgres=# select * from pg_stat_replication;
  pid   | usesysid | usename | application_name | client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sen
t_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
--------+----------+---------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+----
-------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 491208 |    16388 | repl    | server2:5433     | 192.168.8.17 |                 |       57822 | 2023-06-19 14:26:17.903827+08 |         1023 | streaming | 0/5
05D260 | 0/505D260 | 0/505D260 | 0/505D260  |           |           |            |             0 | async      | 2023-06-19 16:46:39.552095+08
 475418 |    16388 | repl    | sub1             | 192.168.8.18 |                 |       51082 | 2023-06-19 11:30:57.804679+08 |              | streaming | 0/5
05D260 | 0/505D260 | 0/505D260 | 0/505D260  |           |           |            |             0 | async      | 2023-06-19 03:32:16.625387+08
(2 rows)

postgres=# select * from pg_replication_slots ;
 slot_name |  plugin  | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status
| safe_wal_size | two_phase | conflicting
-----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------
+---------------+-----------+-------------
 sub1      | pgoutput | logical   |      5 | postgres | f         | t      |     475418 |      |         1095 | 0/505D228   | 0/505D260           | reserved
|               | f         | f
(1 row)

postgres=# select * from test_slots order by 2 desc ;
 id |            time
----+----------------------------
  1 | 2023-06-19 16:57:01.668928
  1 | 2023-06-19 16:56:01.523773
  1 | 2023-06-19 16:55:01.374319
  1 | 2023-06-19 16:54:01.227169
  1 | 2023-06-19 16:53:02.083697
  1 | 2023-06-19 16:52:01.921939

通过函数pg_is_in_recovery（）的返回结果可以看到192.168.8.17为备库，复制槽sub2为逻辑复制槽且状态为active，订阅端为192.168.8.18,复制槽sub1是使用函数pg_create_logical_replication_slot（）手动创建的复制槽

[postgres@server2 pg_wal]$ hostname -i
192.168.8.17
[postgres@server2 pg_wal]$ psql -p 5433
psql (16beta1)
Type "help" for help.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

postgres=# select * from pg_replication_slots ;
 slot_name |  plugin  | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status
| safe_wal_size | two_phase | conflicting
-----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------
+---------------+-----------+-------------
 sub2      | pgoutput | logical   |      5 | postgres | f         | t      |     532706 |      |         1076 | 0/5055AF8   | 0/5055B30           | reserved
|               | f         | f
 sub1      | pgoutput | logical   |      5 | postgres | f         | f      |            |      |         1023 | 0/50443D0   | 0/5044408           | reserved
|               | f         | f
(2 rows)

postgres=# select * from pg_stat_replication;
  pid   | usesysid | usename | application_name | client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sen
t_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
--------+----------+---------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+----
-------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 532706 |    16388 | repl    | sub2             | 192.168.8.18 |                 |       40928 | 2023-06-19 14:26:20.064179+08 |              | streaming | 0/5
055B30 | 0/5055B30 | 0/5055B30 | 0/5055B30  |           |           |            |             0 | async      | 2023-06-19 03:13:23.147091+08
(1 row)

postgres=#

192.168.8.18为订阅端，分别订阅了server1（主库）和server2（备库），可以看到test_slots表同一时间的数据都是两条，说明server1和server2的数据都通过逻辑复制同步过来

[postgres@server3 data]$ hostname -i
192.168.8.18
[postgres@server3 data]$ psql -p 5433
psql (16beta1)
Type "help" for help.

postgres=# select subname,subconninfo from pg_subscription;
 subname |                   subconninfo
---------+--------------------------------------------------
 sub1    | dbname=postgres host=server1 user=repl port=5433
 sub2    | dbname=postgres host=server2 user=repl port=5433
(2 rows)

postgres=# select * from pg_stat_subscription;
 subid | subname |  pid   | leader_pid | relid | received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_
end_time
-------+---------+--------+------------+-------+--------------+-------------------------------+-------------------------------+----------------+---------------
----------------
 24577 | sub1    | 364827 |            |       | 0/50612E8    | 2023-06-19 16:55:10.693336+08 | 2023-06-19 03:40:47.704071+08 | 0/50612E8      | 2023-06-19 16:
55:10.693336+08
 24579 | sub2    | 423902 |            |       | 0/50612E8    | 2023-06-19 16:55:10.70513+08  | 2023-06-19 03:40:47.704329+08 | 0/50612E8      | 2023-06-19 16:
55:10.70513+08
(2 rows)

postgres=# select * from test_slots order by 2 desc limit 10;
 id |            time
----+----------------------------
  1 | 2023-06-19 16:55:01.374319
  1 | 2023-06-19 16:55:01.374319
  1 | 2023-06-19 16:54:01.227169
  1 | 2023-06-19 16:54:01.227169
  1 | 2023-06-19 16:53:02.083697
  1 | 2023-06-19 16:53:02.083697
  1 | 2023-06-19 16:52:01.921939
  1 | 2023-06-19 16:52:01.921939
  1 | 2023-06-19 16:51:01.774134
  1 | 2023-06-19 16:51:01.774134
(10 rows)

postgres=#

知识总结

1）pg16beta版本中备库可以进行逻辑解码，可以创建复制槽

2）备库不活跃复制槽的lsn依旧不会主动向前推进，为了及时清理wal日志，还需要手动推进备库复制槽的lsn

参考文档

PostgreSQL: PostgreSQL 16 Beta 1 Released!