LightDB/Postgres逻辑复制的搭建

原创姚崇 2023-02-14

1190

PostgreSQL/LightDB物理流复制的部署,其有如下主要优点

物理层面完全一致，是主要的复制方式，其类似于Oracle的DG
延迟低，事务执行过程中产生REDO record，实时的在备库apply，事务结束时，备库立马能见到数据
物理复制的一致性、可靠性高，不必担心数据逻辑层面不一致

但是其又在实际使用的场景中存在一些无法满足的需求，例如：

无法满足指定库或部分表的复制需求
将多个数据库实例的数据汇聚到同一个目标库或将一个库的数据分发到多个不同的库
不同的版本之间的复制
不同库名之间的表同步
对于以上场景,物理复制时无法满足的，因此逻辑复制应运而生了。
逻辑复制的复制架构图如下：

逻辑复制是基于逻辑解析，其核心原理是逻辑主库将Publication中表的WAL日志解析成一定格式并发送给逻辑备库，逻辑备库Subscription接收到解析后的WAL日志后进行重做，从而实现表数据同步。

2. 逻辑复制的部署

PS：以下的逻辑从库可以在新的机器上部署，如在原先的从库上修改，需停止原实例，并将recovery.conf文件删除或重命名（如修改为recovery.conf.done）

2.1 修改主库的postgresql.conf

可以基于原先的物理复制的配置文件进行修改,配置逻辑复制主要需调整如下参数

wal_level = logical
max_wal_senders = 10
max_replication_slots = 8

参数简要说明如下
wal_level：设置成logical才支持逻辑复制
max_wal_senders：由于每个订阅节点和流复制备库在主库上都会占用主库上一个WAL发送进程，因此此参数设置值需大于max_replication_slots参数值加上物理备库数量
max_replication_slots：设置值需大于订阅节点的数量

2.2 修改逻辑从库的postgresql.conf

逻辑从库的postgresql.conf也可以在物理复制的基础上修改，与主库不同的是主要修改如下参数

wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

参数简要说明
wal_level：设置成logical才支持逻辑复制,逻辑从库可以视情况设置
max_replication_slots：设置数据库复制槽数量，应大于订阅节点的数量
max_logical_replication_workers：设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量，此参数默认值为4

2.3 创建逻辑复制账号

lightdb@postgres=# alter USER test REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'test'; 
CREATE ROLE

逻辑复制用户需要REPLICATION权限即可，可以不需要SUPERUSER权限，之后需要在发布节点上将需要同步的表赋权给logical_repl用户，使logical_repl账号具有对这些表的读权限。

2.4 在逻辑主库上创建库及表

/** 创建用于逻辑复制的主库 */ 
lightdb@postgres=# \c test test
Password for user test: 
You are now connected to database "test" as user "test".
test@test=> create schema test;
CREATE SCHEMA
test@test=> create table test (id int,name varchar(100)) ;
CREATE TABLE

2.5 逻辑从库上创建库及表

/** 在逻辑从库上创建不同的库 */
lightdb@lt_test=# \c  test_subscription test_subscription
Password for user test_subscription: 
You are now connected to database "test_subscription" as user "test_subscription".
test_subscription@test_subscription=> create table test (id int,name varchar(100));
CREATE TABLE

注：逻辑复制的表结构需要手动在从库创建

2.6 在逻辑主库上创建发布

/** 在发布主库上创建发布pub1,注意实在test库下执行 */
test@test=> CREATE PUBLICATION pub1 FOR TABLE test;
test@test=# 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 
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------
(0 rows)
test@test=# select * from pg_publication;
  oid   | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot 
--------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 117449 | pub1    |   117440 | f            | t         | t         | t         | t           | f
(1 row)
-- 多个表格式如下，逗号分隔符
CREATE PUBLICATION pub1 FOR TABLE bmsql_config
,bmsql_customer
,bmsql_district
,bmsql_history
,bmsql_item
,bmsql_new_order
,bmsql_oorder
,bmsql_order_line
,bmsql_stock
,bmsql_warehouse;

如果需发布多张表则表名间用逗号（,）分割，如果需发布所有库，则将FOR TABLE 调整为FOR ALL TABLES。
此时可以查看到如下信息

test@test=> SELECT * FROM pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 24523 | pub1    |    24512 | f            | t         | t         | t         | t           | f
(1 row)

字段说明如下：

pubname：指发布的名称
pubowner：指发布的属主，可以和pg_user视图的usesysid字段关联查询得到属主具体信息
puballtables：是否发布数据库中的所有表，t表示发布数据库中所有已存在的表和以后新建的表
pubinsert： t表示仅发布表上的INSERT操作
pubupdate： t表示仅发布表上的UPDATE操作
pubdelete： t表示仅发布表上的DELETE操作

2.7 在逻辑从库上创建订阅

lightdb@lt_test=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=syncyser dbname=test password=syncyser' PUBLICATION pub1;
ERROR:  could not connect to the publisher: FATAL:  must be superuser or replication role to start walsender

-- 在主库上更改test用户的权限
lightdb@postgres=# alter USER test REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'test'; 
ALTER ROLE

lightdb@lt_test=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=syncyser dbname=test password=syncyser' PUBLICATION pub1;
ERROR:  schema "test" does not exist
报错 schema test不存在，说明发布端和订阅段用户名和库可以不相同，但是表所在的schema名字必须相同，重建订阅端表
test_subscription@test_subscription=> create table test.test_subscription(id int,name varchar(100));
CREATE TABLE

lightdb@test_subscription=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=syncyser dbname=syncyser password=test' PUBLICATION pub1;
ERROR:  relation "test.test" does not exist
lightdb@test_subscription=# create table test.test(id int,name varchar(100));
CREATE TABLE

lightdb@test_subscription=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=syncyser dbname=test password=syncyser' PUBLICATION pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
-- 会自动创建slot

创建成功后，可以在逻辑主库上查询到如下信息：

test@test=> SELECT slot_name,plugin,slot_type,database,active,restart_lsn    FROM pg_replication_slots;
       slot_name       |  plugin  | slot_type | database | active | restart_lsn  
-----------------------+----------+-----------+----------+--------+--------------
 sub1                  | ltoutput | logical   | test     | t      | 763/6A6D63A8
 sub1_64269_sync_64262 | ltoutput | logical   | test     | t      | 763/6A6C68C8
(2 rows)

在逻辑从库上可以查询到如下信息：

lightdb@test_subscription# SELECT * FROM pg_subscription;
  oid  | subdbid | subname | subowner | subenabled |                            subconninfo                             | subslotname | subsynccommit | subpu
blications 
-------+---------+---------+----------+------------+--------------------------------------------------------------------+-------------+---------------+------
-----------
 24283 |   24225 | sub1    |       10 | t          | hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test | sub1        | off           | {pub1
}
(1 row)

2.8 给复制账号授权

如果不是同用户的，需要将表给到publication用户查询权限
因为步骤2.3中只创建了复制账号并未对需要复制的表进行授权，因此，此时逻辑从库的日志中有如下错误
2023-02-14 15:57:21.847 CST [27443] ERROR: could not start initial contents copy for table “test.test”: ERROR: permission denied for table test
2023-02-14 15:57:21.848 CST [24722] LOG: background worker “logical replication worker” (PID 27443) exited with exit code 1
因此，现在需要对复制用户授权。

sourcedb=# GRANT USAGE ON SCHEMA public TO test;
GRANT 
sourcedb=# GRANT SELECT ON logical_tb1 TO test; 
GRANT

授权后,显示正常
2023-02-14 16:00:25.959 CST [28204] LOG: logical replication table synchronization worker for subscription “sub1”, table “test” has started
2023-02-14 16:00:25.967 CST [28204] LOG: logical replication table synchronization worker for subscription “sub1”, table “test” has finished

2.9 测试数据同步

在逻辑主库插入数据

/** 在主库插入数据 */ 
alter table test add  primary key (id);
ALTER TABLE
test@test=> insert into test values (1,'aaa');
INSERT 0 1

在逻辑从库查看结果

/** 查看数据是否同步完成 */ 
lightdb@test_subscription=# select * from test.test;
 id | name 
----+------
  1 | aaa
(1 row)

由此可见数据已同步完成。

2.10 添加复制所需的表

在逻辑主库和逻辑从库均添加一张新表，并添加到发布列表中

/** 主库上创建表结构 */ 
sourcedb=# create table logical_tb2(id int primary key ,addr varchar(100)); 
CREATE TABLE 
sourcedb=# 
/** 从库上创建表结构 */
desdb=# create table logical_tb2(id int primary key ,addr varchar(100)); 
CREATE TABLE 
/** 在主库上给逻辑复制账号授权 */ 
sourcedb=# GRANT SELECT ON logical_tb2 TO logical_repl; 
GRANT
/** 添加新表至发布列表 */ 
sourcedb=# ALTER PUBLICATION pub1 ADD TABLE logical_tb2; 
ALTER PUBLICATION 
/** 在主库查看发布列表中的表名 */
sourcedb=# SELECT * FROM pg_publication_tables;
 pubname | schemaname |  tablename  
---------+------------+-------------
 pub1    | public     | logical_tb1
 pub1    | public     | logical_tb2
(2 rows)

此时已加入一张表进入发布列表中。
此时在主库写入数据，查看从库情况如下：

/** 主库插入一条记录 */ 
sourcedb=# insert into logical_tb2(id,addr) values(1,'beijing'); 
INSERT 0 1 
/** 此时在逻辑从库查看，结果却没有数据 */
desdb=# select  * from logical_tb2;
 id | addr 
----+------
(0 rows)

因为还需要在从库刷新一下订阅

/** 此时在从库刷新订阅 */ 
test@test=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
/** 刷新完成后再查数据已经有数据了 */ 
ALTER SUBSCRIPTION
desdb=# select  * from logical_tb2;
 id |  addr   
----+---------
  1 | beijing
(1 row)

至此，PostgreSQL的逻辑复制也部署完毕。

3. 物理复制与逻辑复制特点和应用场景

PostgreSQL的逻辑复制与物理复制的差异比较突出，在使用中可以根据其特点选择使用哪种复制方式。

逻辑订阅，适合于发布端与订阅端都有读写的情况。
逻辑订阅，更适合于小事务，或者低密度写（轻度写）的同步。如果有大事务、高密度写，逻辑订阅的延迟相比物理复制更高。
逻辑订阅，适合于双向，多向同步；但是schema名和表名订阅端必须和发布端相同
物理复制，适合于单向同步。
物理复制，适合于任意事务，任意密度写（重度写）的同步。
物理复制，适合于HA、容灾、读写分离。
物理复制，适合于备库没有写，只有读的场景。

4. 常见错误

4.1 has neither REPLICA IDENTITY index nor PRIMARY KEY and published relation does not have REPLICA IDENTITY FULL

2023-03-16 17:01:19.038711T  @  logical replication worker  55000[2023-03-16 17:01:18 CST] 0 [9874] ERROR:  logical replication target relation "edb_jy_splittable.c_indicator_data" has neither REPLICA IDENTITY index nor PRIMARY KEY and published relation does not have REPLICA IDENTITY FULL
2023-03-16 17:01:19.040412T  @  postmaster  00000[2023-03-16 16:06:03 CST] 0 [707] LOG:  background worker "logical replication worker" (PID 9874) exited with exit code 1
2023-03-16 17:01:24.049265T  @  logical replication worker  00000[2023-03-16 17:01:24 CST] 0 [9906] LOG:  logical replication apply worker for subscription "sub_edb" has started

说明表上没有主键，如果复制标识默认走default的话，没有主键便会报上述错误

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

select relname,relreplident from pg_class where
relname in 
('c_indicator_data','c_indicator_data_access','c_indicator_tree','edb_finance_data','edb_fix_data',
'edb_quotation_data','c_indicator_label','c_indicator_label_access','c_indicator_main',
'c_indicator_main_access','c_indicator_usage_tick','diff_code','edb_custom_data','edb_sync_config',
'indicator_code_hxtmp','indicator_code_tmp','indicator_sequence','t_data_monitor_detail');

如果实在无法添加主键，可以使用下列语句更改复制标识模式

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

4.1 cannot update table a because it does not have a replica identity and publishes updates Hint: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE

LightDB/PostgreSQL的a表逻辑复制表报错分析和原因


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

原因是当前trd_serial_counter这个表无主键（即便有唯一索引也不行）
可以通过下面SQL确认当前是哪种模式
select relname,relreplident from pg_class where
relname in ('trd_serial_counter');

如果实在无法添加主键，可以使用下列语句更改复制标识模式

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

我们当前就是要设置为第二种 REPLICA IDENTITY FULL;

建议：
1、斗转同步时注意表结构，如果没有主键要执行ALTER TABLE t REPLICA IDENTITY FULL; 使用整行作为标识，但是该模式效率低
2、业务表要加主键
参考：
https://www.modb.pro/db/611334

4.2 slot has been invalidated because it exceeded the maximum reserved size

18:31:24 subp syncuser@dw_irp 172.2.8.4(5594) walsender idle 5500[18:31:24 CST] 0 [xx] ERROR: cannot read from logical replication slot "sub_dwirp"
18:31:24 subp syncuser@dw_irp 172.2.8.4(5594) walsender idle 5500[18:31:24 CST] 0 [xx] DETAIL: This slot has been invalidated because it exceeded the maximum reserved size.

逻辑复制的槽位 “sub_dwirp” 已经超过了预留的最大大小限制，因此无法继续读取。原因为订阅者（subscriber）无法及时处理来自发布者（publisher）的复制数据，导致数据积压并超出槽位的限制所致。
在 PostgreSQL 中，逻辑复制槽位的最大大小限制由参数 “max_slot_wal_keep_size” 控制。该参数指定了逻辑复制槽位在 WAL 日志中最多可以保留的数据量，超过该限制后，槽位将被标记为无效并无法继续读取。

4.3 could not create replication slot

2023-02-09 13:08:50.112789T  @  logical replication worker  53400[2023-02-09 12:36:33 CST] 0 [20340] WARNING:  out of logical replication worker slots
2023-02-09 13:08:50.112789T  @  logical replication worker  53400[2023-02-09 12:36:33 CST] 0 [20340] HINT:  You might need to increase max_logical_replication_workers.
2023-02-09 13:08:50.987126T  @  logical replication worker  XX000[2023-02-09 13:08:35 CST] 0 [23239] ERROR:  could not create replication slot "subname1_55740_sync_48957": ERROR:  canceling statement due to lock timeout
2023-02-09 13:08:50.988663T  @  postmaster  00000[2023-02-09 12:19:09 CST] 0 [18777] LOG:  background worker "logical replication worker" (PID 23239) exited with exit code 1

每个逻辑复制进程都需要一个单独的工作进程来处理。如果数据库中的逻辑复制工作进程已经全部使用，则将无法创建新的逻辑复制进程，并且会出现上述lock timeout错误。

4.4 调整逻辑参数值

修改LightDB发布端参数(slots reload生效，workers重启生效)，默认值均为4：

max_logical_replication_workers 参数设置为10：
max_logical_replication_workers = 10
max_replication_slots=10

max_logical_replication_workers：参数指定了逻辑复制工作者的最大数量。逻辑复制是指将一个数据库的更改操作应用于另一个数据库的过程，其中更改操作被记录为一系列逻辑操作。这些逻辑操作可以是 INSERT、UPDATE 或 DELETE，这些操作在逻辑上描述了如何修改数据库中的数据。max_logical_replication_workers 参数控制了可以同时运行的逻辑复制工作者数量，如果设置过低可能会导致复制延迟。

max_replication_slots：参数指定了在实例中允许的最大复制槽数量。复制槽是用于复制的一种机制，它允许在主服务器上保留 WAL 日志，以便从备份服务器上进行恢复。如果备份服务器上的复制槽数量超过了该参数设置的最大值，那么 PostgreSQL 将不会创建更多的复制槽。这个参数的设置需要根据系统的需求和硬件资源进行调整。

1.已确认，明确发布端与订阅端数据条数差异大（超20万），提出的同步异常，未提及数据丢失。
2.主从机房网络为 100m专线，机房运营商未知，重建发布订阅关系是，订阅端接收速率约为12M，主从机房见网络 Ping 1024字节延时3.8~4.2ms,偶发9ms,1 ~ 10%loss
3.同步延迟造成槽位占用的问题，已经通过调整参数槽和工作进程处理，后续观察
4.复制标识默认default ,部分同步表没有主键需要加上

由于表无主键，走默认复制标识，复制标识便会降级为full模式，full模式全表更新便会出现缓慢情况；复制缓慢时候，每个逻辑复制进程都需要一个单独的工作进程来处理。如果数据库中的逻辑复制工作进程已经全部使用，则将无法创建新的逻辑复制进程，所以日志中出现could not create replication slot同时replication进程lock timeout错误，最终导致slot has been invalidated because it exceeded the maximum reserved size复制中断

墨力计划

最后修改时间：2024-06-18 10:53:27

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者