PostgreSQL物理流复制及逻辑复制全面对比

PostgreSQL 物理流复制及逻辑复制全面对比一、物理流复制概述PostgreSQL 物理流复制是一种基于 wal 日志的物理复制，是将 wal 日志从 主节点到备节点实时传输并复用的过程PostgreSQL数据库在不同版本间物理复 制实现的方式有很大差异，其中变化最大的是wal日志复制的方式，PostgreSQL 9.0之前提供的方法是主库写完一个WAL日志文件后，才把WAL日志文件传送 到备库，这样的方式导致主备延迟非常大，RPO （恢复点目标，决定了丢失多少数 据）得不到保障。在9.0之后引入了主备流复制机制，通过物理流复制，备库不 断的从主库同步WAL日志的record，并在备库应用每个WAL record。对于物理流复制，传递日志两种方式：同步流复制和异步流复制。同步流 复制就是用户“写”操作在写主库前已经将日志写到备库，主备库同时写库，数 据0丢失。异步流复制就是用户“写”操作在写主库成功后已经将日志写到备库， 数据同步至少有一个 record 延迟。同步的好处是数据 0 丢失，坏处是对主备间 网络质量要求高，如果网络质量不高（存在丢包或者时延大），主库的“写”操 作会受到巨大影响。异步的好处就是同步动作对主库没有影响，坏处就是数据有 丢失可能。示意图二、逻辑复制概述我们在这里讲的逻辑复制是 PostgreSQL 在 10 版本后引入的新特性。增加 的 pglogical 模块为 PostgreSQL 数据库提供了逻辑流复制功能。 pglogical 重用 了双向复制（BDR）中的一部分相关技术。是一个完全内部集成的逻辑复制系统， 不需要单独安装。这种物理复制的完全可以替代早期的 Slony 逻辑复制，比 Slony 逻辑复制更加高效和稳定。Logical逻辑复制使用订阅复制槽技术，可并行的传输WAL日志，通过在订 阅端回放WAL日志中的逻辑条目，保持复制表的数据同步。该逻辑不是“SQL”复制，而是复制SQL操作的结果示意图subscriberis winI 徑呼 Iu Mb Inil.lf al呂 sq Logical replicaticKH worker for suiiscriplion N三、对比说明1、概述物理流复制逻辑复制复制粒度整库表主备库平台要求同构平台同构/异构平台主备库版本要求大版本一致可以跨大版本DDL/DML复制限制无DDL及特定对象无法复制主库数量11或n备库状态只读读写主备间一致性强一般主要应用场景灾备/读写分离多（双）活/数据聚集分发配置难易差异一般容易2、详解 复制粒度：物理流复制是对整个数据库级别的复制，逻辑复制是表级别的 复制，可以选择一个表或者一组表。 主备库平台要求：物理流复制要求主备库部署的操作系统相同，比如都是 windows或者都是centos，且操作系统版本要求一致；逻辑复制的主备库 可以跨操作系统平台。 主备库版本要求：物理流复制要求主备库的大版本相同；逻辑复制的主备 库可以跨大版本，但需求注意完全支持从旧版本复制到新版本，但只有有 限的向前兼容性。 DDL/DML复制限制：物理流复制可以复制所有的DDL和DML操作；逻辑 复制不可以复制包括truncate在内的DDL操作，也不支持对序列、大对 象、临时表、MQT （物化视图）、外部表、视图的复制。 主库数量：物理流复制的主库只能有一个；逻辑复制的备库可以一个也可 以多个，可以多个主库的表往一个备库中进行数据聚集。A 备库状态：物理流复制的备库是打开且只读状态；逻辑复制的备库是打开 且可读写状态，因为备库可读写且处于同步状态，所以备库中数据的一致 性需要人为保证。 主备间一致性：物理流复制是数据库级的全同步，可以保证主备库的强一 致性，不丢失操作和数据。逻辑复制是表级复制，可定义复制的操作（比 如只同步插入操作）并且部分数据库对象无法复制，因此主备库之间不存 在强一致性。主要应用场景：物理流复制由于存在主备间强一致性因此是常规灾备方 案的首选，并且由于备库是打开且只读状态因此又可作为读写分离方案； 逻辑复制由于主备库是打开且可读写状态，所以可以作为多（双）活方案， 但由于主备库之间的数据一致性是人为控制的，所以要进行良好的设计， 比如同表上的主键唯一性问题，避免使用特殊数据库对象问题等；由于逻 辑复制的多对一，多对多关系，所以也可以使用在数据聚集和分发场景。 配置难易差异：物理流复制配置过程比较简单，主要步骤有：1、主库创 建 replication 专有用户 2、配置主库参数 3、配置主库 pg_hba.conf 文件（访问控制）4、在备库上使用 pg_basebackup 从主库同步数据 5、在备 库上配置recovery.conf文件6、配置备库pg_hba.conf文件（访问控制）。 逻辑复制非常简单，主要步骤有：1、配置主库参数 2、在主库上定义“发 布”3、配置主库pg_hba.conf文件（访问控制）4、在备库上定义“订阅”