PG逻辑复制简介及实操

IT那活儿发布于2023-01-11 13:19 / 1525人阅读

PG逻辑复制简介及实操

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

逻辑复制介绍

PostgreSQL 10版本中新增了一个逻辑复制的特性，支持内置的逻辑复制。

它使用类似消息队列的发布者、订阅者的模型，可基于表级别复制，是一种粒度可细的复制。利用复制槽的技术，发布者将wal日志解析成一定格式的数据流，订阅者对解析后的wal日志数据流进行回放应用，从而实现数据的同步。

逻辑复制原理

使用发布者/订阅者模型，使用订阅复制槽技术，可并行的传输WAL日志，通过在订阅端回放WAL日志中的逻辑条目，保持复制表的数据同步。

这里并不是“SQL”复制，而是复制SQL操作的结果。

逻辑复制的缺陷

目前仅支持数据库表逻辑复制，其他对象如函数、视图不支持。仅仅支持复制DML操作，不支持Truncate和DDL操作。

实际具体操作步骤

1. 发布

在源端执行下列命令，定义发布和要复制的表：

CREATE PUBLICATION pub1 FOR TABLE text.yjxt_tf_fmc_payflow_fee_sync;

执行下列命令，检查是否创建成功。

SELECT * FROM pg_publication;

2. 订阅

在目标端执行下面的命令，创建一个到远程服务器的订阅，复制发布pub1中的表，并在提交时立即开始复制：

CREATE SUBSCRIPTION sub1 CONNECTION host=133.0.xxx.xxx port=xxxx user=test dbname=test password=xxxxx PUBLICATION pub1;

执行下列命令，检查是否创建成功。

SELECT * FROM pg_subscription;

3. 查看源端复制槽是否活跃

SELECT slot_name,plugin,slot_type,database,active,restart_lsn FROM pg_replication_slots;

4. 在源端执行下面的命令

可以在已经创建的发布里新增需要逻辑复制的表。

ALTER PUBLICATION pub1 ADD TABLE text.commission_project_yj_sync;

5. 执行下面的命令，在目标端刷新订阅

alter subscription sub1 refresh publication;

6. 通过查询语句查看两边数据，验证逻辑复制是否成功。

逻辑复制注意事项

1. 逻辑复制的源数据库用户必须具有 replication 或 superuser 角色，并且保证订阅段可通过流复制协议连接到源数据库上。

2. 当发布包含delete、update操作时，表必须设置replica identity用来标识旧行（pk、uk、full），对于无主键/唯一键的表逻辑同步，只允许发布端进行insert操作，无法执行delete、upadte操作。

可以执行下面的命令修改表的复制标识来解决非主键表无法update的问题。

ALTER TABLE table_name REPLICA IDENTITY FULL;

注：REPLICA IDENTITY，复制标识，共有4种配置模式，分别为：

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

表改复制标识可以通过ALTER TABLE进行修改。

早前发布过我们团队另外一位作者写的一篇《PGsql逻辑复制》，大家可以结合本篇内容阅读查看之前发布的文章：《PGsql逻辑复制》

本文作者：吴航舟（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

私有云云服务器数据库管理实操 datav设计与实操 MySQL主从同步实操私藏项目实操分享使用 JavaScri

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129365.html

新书推荐 |《PostgreSQL实战》出版(提供样章下载)

摘要：作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写，国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。很高兴《PostgreSQL实战》一书终于出版，本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结，本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreS...

Martin91 2019-07-25 12:36 评论0 收藏0
私有云搭建-私有云搭建之存储虚拟化

摘要：平台采用分布式存储系统作为虚拟化存储，用于对接虚拟化计算及通用数据存储服务，消除集中式网关，使客户端直接与存储系统进行交互，并以多副本纠删码多级故障域数据重均衡故障数据重建等数据保护机制，确保数据安全性和可用性。云计算平台通过硬件辅助的虚拟化计算技术最大程度上提高资源利用率和业务运维管理的效率，整体降低 IT 基础设施的总拥有成本，并有效提高业务服务的可用性、可靠性及稳定性。在解决计算资源的...

ernest.wang 2022-06-28 15:50 评论0 收藏0
PG的两种集群技术：Pgpool-II与Postgres-XL

摘要：最近研究了的两种集群方案，分别是和，在这里总结一下二者的机制结构优劣测试结果等。其中的前身的，的前身是。为了避免单点故障，可以为所有节点配置对应的节点。测试测试结果测试结果显示，两种集群与单机的性能指标几乎一致，无法分辨高下。最近研究了PG的两种集群方案，分别是Pgpool-II和Postgres-XL，在这里总结一下二者的机制、结构、优劣、测试结果等。 1、 Pgpool-I...

gitmilk 2019-07-25 12:32 评论0 收藏0
一个从基础到实战的学习机会：Go & Rust、分布式数据库系统 | PingCAP Tal

摘要：每年设有三期线下课程，分别在月份，月份以及月份，所有线下课程将在北京总部进行。当大家完成了线下课程和全部课程考核，我们会举办一个充满仪式感的结业答辩，并为顺利结业的小伙伴授予专属的结业证书。 TiDB 每一次微小进步都离不开广大社区小伙伴们的支持，但也有很多同学反映 TiDB 是一个非常复杂的分布式数据库系统，如果没有相关知识和经验积累，在参与之初难免会遇到各种问题。因此我们决定全面升...

TNFE 2019-06-25 19:11 评论0 收藏0