PG vs MySQL 主从复制的异同点

MySQL主从复制原理

如下图是一个完整的主从复制的一个流程。

根据图解流程如下：

（1）执行change master to IP，host，port，user，password 命令时，会将信息写入到mysql库的slave_master_info表中。执行start slave 命令，会启动SQL线程和IO线程。
（2）从库IO线程连接主库。
（3）主库分配dump线程与从库的IO线程通信。
（4）IO线程与dump线程建立连接。
（5）IO线程读取slave_master_info表，获取上次收到的binlog信息位置。
（6）并从此位置开始请求新的binlog日志。
（7）dump线程接收请求，截取binlog日志，返回给IO线程。
（8）IO线程接收binlog，日志放在TCP/IP缓存中，此时网络层层面返回ACK给主库，主库工作完成。
（9）IO线程将binlog最终写入到relay log 中，并更新slave_master_info表，此时IO线程结束工作。
（10）SQL线程读取slave_relay_log_info表信息，获取上次执行到的位置点。
（11）SQL线程向后执行新的relay log。
（12）再次更新slave_relay_log_info表。

PG主从复制原理

PG的复制分为流复制和逻辑复制

PG流复制原理

流复制是物理复制，核心原理是主库将预写日志WAL日志流发送给备库，备库接收到WAL日志流后进行重做。
PG主备流复制的核心部分由walsender，walreceiver和startup三个进程组成，如下：

（1）主库 backend 进程，它负责执行用户的 SQL，在修改数据前会先记录 WAL日志。
（2）主库 WALsender 进程，负责把 WAL 日志发送给备库的 WALreceiver 进程。
（3）备库 WALreceiver 进程，负责接收 WALsender 发送的 WAL日志，并持久化到存储。
（4）备库 startup 进程，负责恢复 WALsender 写到磁盘上的 WAL日志，把数据 apply 到数据页面上。

根据图解流程如下：

当用户连接进行数据操作，产生对应的WAL日志记录后，walwriter会周期性地把产生的WAL page刷新到磁盘中，如果配置了备库，则walsender会不断将WAL page发给备库的walreceiver进程，walreceiver进程会把对应WAL page直接写到本地磁盘，同时slave上的startup辅助进程会不断地应用xlog日志，改变本地数据，实现与主库之间的数据同步。

WAL流复制支持同步、异步方式：

异步流复制模式中，主库提交的事务不会等待备库接收WAL日志流并返回确认信息，因此异步流复制模式下主库与备库的数据版本上会存在一定的处理延迟，延迟的时间主要受主库压力、备库主机性能、网络带宽等影响，当正常情况下，主备的延迟通常在毫秒级的范围内，当主库宕机，这个延迟就主要受到故障发现与切换时间的影响而拉长，不过虽然如此，这些数据延迟的问题，可以从架构或相关自动化运维手段不断优化设置。
同步流复制模式中，要求主库把WAL日志写入磁盘，同时等待WAL日志记录复制到备库、并且WAL日志记录在任何一个备库写入磁盘后，才能向应用返回Commit结果。一旦所有备库故障，在主库的应用操作则会被挂起，所以此方式建议起码是1主2备。

可以看到PG这点，同步复制类似于MySQL的半同步复制。

PG逻辑复制原理

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

(1)Walsender：使用OutputPlugins进行逻辑解码，然后将解码后的数据发送给订阅端
(2)logical replication launcher：类似于autovacuum launcher，守护进程，进行fork logical replication worker，发布端和订阅端都有该进程
(3)logical replication worker：工作进程，接收Walsender发送过来的数据
(4)将数据重新执行一次insert、update或delete

根据图解流程如下：
在wal日志产生的数据库上，由逻辑解析模块对wal日志进行初步的解析,它的解析结果为ReorderBufferChange（可以简单理解为HeapTupleData），再由pgoutput plugin对中间结果进行过滤和消息化拼接后，然后将其发送到订阅端，订阅端根据接收到的HeapTupleData重新对其执行insert、delete、update的操作。这里要注意，流复制是将数据从walrecord拷贝到数据页，逻辑复制是将数据重新执行一次insert、update或delete。

PG VS MySQL

PG的流复制具有如下特点：

（1）物理层面、逻辑层面完全一致：主备数据库的物理块完全一样，逻辑层面也完全一样，达到金融级需求。
（2）延迟极低，不怕大事务，主库完成提交，备库能立即提交。
（3）支持断点续传。
（4）支持多副本。
（5）备库与主库物理完全一致，并支持只读。

流复制的缺陷：
（1）流复制主库可读写，但从库只允许查询不允许写入，而逻辑复制的从库可读写。
（2）流复制要求PG大版本必须一致，逻辑复制支持跨PG大版本。

PG的逻辑复制具有如下特点：

（1）满足业务上需求，实现某些指定表数据同步。
（2）报表系统，采集报表数据。
（3）可从多个上游服务器,做数据的聚集和合并。
（4）PostgreSQL 跨版本数据同步。
（5）PostgreSQL 大版本升级。

基于pgoutput插件的逻辑复制页存在一些局限性如下：
（1）不支持DDL复制(ALTER TABLE/CREATE TABLE)
（2）不支持TEMPRORARY表和UNLOGGED表复制
（3）不支持Sequences复制（ serial/bigserial/identity）
（4）不支持TRUNCATE操作复制
（5）不支持大对象复制（Bytea）
（6）不支持视图、物化视图、外部表复制

MySQL的主从复制特点：

（1）基于binlog的逻辑复制，相比PG的流复制，存在大事务延迟的情况。
（2）相比PG的强一致性，MySQL只能做到最终一致性。
（3）相比PG只支持单源复制，MySQL可以多源复制。
（4）相比PG的逻辑复制，MySQL的逻辑复制支持DDL，能实现整个实例的复制。
（5）相比PG的逻辑复制 MySQL在并行复制、基于行的复制在性能上更有优势，而逻辑复制的逻辑解码可能带来性能开销。