PostgreSQL流复制、逻辑复制

PostgreSQL流复制、逻辑复制

目录

• PostgreSQL流复制、逻辑复制
  • 一、物理流复制 VS 逻辑订阅
    • 1.1 物理复制
    • 1.2 逻辑订阅
    • 解决方案：
  • 二、PostgreSQL 物理流复制
    • 2.1 synchronous_commit 同步复制选项
    • 2.2 物理复制-使用场景
  • 三、PostgreSQL 逻辑复制
    • 3.1 逻辑复制-使用场景
    • 3.2 逻辑复制限制

一、物理流复制 VS 逻辑订阅

1.1 物理复制

物理复制目前只能做到整个集群的复制。
物理流复制是基于REDO的块级别复制，复制出来的数据与主库数据库一模一样，每个块都是一样，好似从主库克隆出来一样。
物理流复制的备库为只读，不能写入。
物理流复制不需要等待事务提交，即可将REDO日志发往备库，备库可以直接应用；
物理流复制需要复制所有的REDO日志。
物理流复制不需要记录REDO信息（主键或者整行）。
物理流复制备库上的查询语句可能会与清理REDO冲突，查询需要的读取的数据版本，主库可能已经清理，同步到备库时，和正在查询的SQL冲突。

1.2 逻辑订阅

逻辑订阅可以做到表级别复制。
逻辑订阅支持读写。
逻辑订阅需要等待事务提交后，发布端才会使用wal_sender进程将解码后的数据发送给订阅端，订阅端接收应用。【因此逻辑订阅的延迟笔物理流复制要高】
逻辑订阅不需要复制所有的REDO日志，仅复制“订阅表产生的REDO解码后的数据”。
逻辑订阅需要产生额外的REDO信息（主键或者整行）；订阅对主库的性能影响较大。
逻辑定于模式不存在SQL冲突。

解决方案：

• a. 主库延迟清理（导致主库膨胀）
• b. 备库延迟应用REDO

二、PostgreSQL 物理流复制

• 当用户执行DML、DDL操作后，产生的WAL日志，会实时有wal sender进程发送到备库，备库wal receiver进程。
• 接收后写入本地wal日志，有startup进程读取wal日志，进行应用。备库进程体现是一个恢复进程。
• PostgreSQL9.0之前提供的方法是主库写完一个WAL日志文件后，才把WAL日志文件传送到备库，这样的方式导致主备延迟特别大。
• PostgreSQL在9.0之后引入了主备流复制机制，通过流复制，备库不断的从主库同步相应的数据，并在备库apply每个WAL record，每次传输单位是WAL日志的record。
• PostgreSql 支持同步复制以及基于优先级的同步复制。

2.1 synchronous_commit 同步复制选项

异步复制下的设置

• 设置为off，表示提交事务部等待本地WAL BUFFER写入WAL日志后才向客户端返回成功。设置为OFF不会对数据库带来风险，当数据库宕机时最新提交的事务可能丢失，数据库重启后会 认为这些事务异常终止，设置为OFF能够提高数据性能。
• 设置为local，代表事务日志写入到主库磁盘中，主库即可返回成功。

注意：// 在同步复制中不能设置为off和local。

• 设置为on，代表需要等待备库应用完事务日志并且数据刷到磁盘中，主库才可以返回成功。
• 设置为remote_apply ，代表需要等待备库应用完事务日志，主库即可返回成功。
• 设置为remote_write，代表需要等待备库将事务日志写入到磁盘中，主库即可返回成功。
  synchronous_standby_names 如果使用同步复制，这里需要设置standby的名字 FISRT 2 (s1,s2,s3),ANY 2(s1,s2,s3),*

primary_conninfo ‘application_name=standby1 host=primary-host port=5432 dbname=postgres user=repl password=repl’

2.2 物理复制-使用场景

• 适合用于单向复制；
• 适合任意事务，任意密度写的同步；
• 适合HA、容灾、读写分离；
• 适合备库只读的场景。

三、PostgreSQL 逻辑复制

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

3.1 逻辑复制-使用场景

• 适合发布端和订阅端都有读写的情况；
• 不同库名之间的表同步；
• PostgreSQL 跨版本数据同步；
• 将多个数据库实例的数据汇聚到同一个目标库或将一个库的数据分发到多个不同的库；
• PostgreSQL大版本升级；
• 满足业务上需求，实现某些指定表数据同步；
• 多个业务之间少量数据同步；
• 数据汇总；
• 数据拆分。

3.2 逻辑复制限制

• 目前不支持DDL解析，只能解析DML（INSERT、UPDATE、DELETE，TRUNCATE）；
• TEMPORARY表和UNLOGGED表不会被复制；
• 表必须有主键或唯一约束，否则像update或delete操作无法被复制；
• 序列不被复制；
• 大对象不被复制；
• 新增加的表，不会自动加入订阅，需要在订阅端进行刷新；
• 分区表逻辑复制，只能基于子表（V13版本已经实现）