PostgreSQL逻辑复制常见错误以及处理方式

  在PostgreSQL和SQLServer中，有一个共同的特点，相比如物理复制，逻辑复制可能会存在较多的一些细节和可能错误的发生，作为SQLServer Replication（逻辑复制）的深入用户，有必要深入学习试验一下PostgreSQL的逻辑复制的一些细节。  

订阅端的主要订阅选项信息

create subscription slave_db01_subscription 
connection 'host=192.168.152.100 port=1234 dbname=db01 user=replica_user  password=A-Strong-Password' 
publication master_db01_pulication 
with (    
        enabled = true,                                　　  --创建完订阅之后是否启动订阅，默认即为启动
        create_slot = false,                                --(发布节点上)是否自动创建复制槽，如果发布节点上已经有了复制槽，就不用创建，如果没有，则这里必须设置为创建。
        slot_name = db01_logic_replication_slot01,          --复制槽名称
        binary = true                          　　　　　　　 --是否以二进制格式传输数据，二进制格式比文本格式传输效率更高
        
        copy_data = true,                        　　　　　　 --如果是true，会初始化发布节点已有数据；
                                                            --如果是false，不会初始化发布节点已有数据，比如重建订阅的时候，如果相关的表已有数据，可以不用重新初始化数据
        
        synchronous_commit  = remote_apply,                 --提交模式，可选为off, local, remote_write, remote_apply, or on，含义同物理复制类似
        
        streaming = parallel,                      　　　　　 --以流格式传输数据,off|on|parallel，off会等待主节点上所有数据写入完成之后统一发送，on会一直传输主节点日志，
                                                            --发布端事务未提交之前就开始传递日志，在订阅节点上，非完整日志先换存在本地，等主节点提交之后，日志完成之后当前节点再apply日志
                                                            --parallel模式，会基于max_parallel_apply_workers_per_subscription做并行apply
    );

订阅的删除

-- 删除之前先取消订阅与复制槽之间的关联，取消关联之前先disable订阅，所以一下是一个完整的删除订阅的步骤
alter subscription slave_db01_subscription disable;
alter subscription slave_db01_subscription SET (slot_name = NONE);
drop subscription slave_db01_subscription;

常见逻辑复制错误以及处理

1,订阅端连接到发布端的账号权限不足

2024-10-24 22:14:26.922 CST [823145] ERROR: could not start initial contents copy for table "public.t2": ERROR: permission denied for table t2

订阅节点连接串中的账号没有发布数据库的读的权限，需要再发布数据库上赋读权限， grant select on XXX|all tables in schema public to replica_user;

2，订阅端表中已存在主键冲突的数据

2024-10-24 22:19:41.567 CST [824794] ERROR: duplicate key value violates unique constraint "t1_pkey"

2024-10-24 22:19:41.567 CST [824794] DETAIL: Key (c1)=(1) already exists.

2024-10-24 22:19:41.567  CST [2148903] CONTEXT:  processing remote data for replication origin "pg_41023" during message type "INSERT" for replication target relation "public.t1" in transaction 796, finished at 0/9A036598

--需要设置log_min_messages = info        

这种错误可能发生在第一次做数据初始化的时候，也可能发生在逻辑复制的运行过程中，从节点中已经存在了与主节点主键冲突的数据，有两种方式来处理这种错误

1，忽略订阅端的数据：需在再订阅节点删除订阅表中对应key的整条数据即可。

2，保留订阅端的数据：订阅端跳过事务 ALTER SUBSCRIPTION mysub SKIP (lsn = '0/9A036598');

3，订阅端表缺少字段

2024-10-24 22:26:33.107 CST [826947] ERROR: logical replication target relation "public.t1" is missing replicated column: "c5"

订阅端表缺少部分字段，需要增加相关字段