PG主从系列-逻辑复制

前言

　　Postgresql支持“逻辑”和“物理”两种复制方式，逻辑复制是一种基于数据对象的复制标识（通常是主键）复制数据对象及其修改的方法，而后者使用准确的块地址以及逐字节的复制方式（WAL日志），逻辑复制允许数据复制和安全性上更细粒度的控制（可以对特定表进行复制）。逻辑复制采用发布者->订阅者模型，具体流程结构图如下：

逻辑复制的能力：

　　1、一个数据库或者一个数据库子集发生更改时，把增量的改变发送给订阅者。

　　2、在更改到达订阅者时引发触发器。

　　3、把多个数据库联合到单一数据库。

　　4、在Postgresql不同主版本之间进行复制。

　　5、在不同的平台（例如Linux到Windows）的Postgresql实例之间复制。

　　6、将复制数据的访问给予不同的用户组。

　　7、在多个数据库之间共享数据库的子集。

　　8、Pubilcation可以选择把它们产生的更改限制为INSERT、UPDATE、DELETE及UPDATE的任意组合。

　　9、表的列通过名称匹配。订阅表中的列顺序不需要与发布表顺序一致；列的数据类型也不需要一样，只要可以将数据的文本表示转换为目标类型即可，如：int列复制到bigint列，此外目标表还可以具备发布表中不存在的额外列，额外列将以定义的默认值填充。

逻辑复制限制：

　　1、一个订阅被删除并且重建时，同步信息会丢失，数据必须重新同步。

　　2、表在发布者和订阅者之间使用完全限定的表名进行匹配，不支持复制到订阅者上命名不同的表，同时列名也必须匹配不能映射。

　　3、采用复制槽的方式，当存在冲突时，复制会暂停，需要人工处理，可能导致发布节点WAL日志积压严重，严重情况下导致发布节点不可用。

　　4、数据库模式和DDL命令不会被复制

　　5、序列数据不被复制

　　6、大对象不会被复制

　　7、复制只能是普通表，不能是视图、物化视图、分区根表或者外部表；如果是分区，可以一一对应地复制分区层次，但当前不能复制成一种不同的分区设置。

　　8、支持TRUNCATE但是当使用cascade清空，而表存在外键类链接时，如果受影响的表属于同一订阅，这会正确工作，但是不属于同一订阅的表时，会报错。

　　9、要求PG9.4及以上版本

　　10、复制表需要有主键、或者唯一性约束，否则只能设置replica identity补全日志

　　11、复制用户权限需要replication或superuser权限

快速部署

1、修改发布者postgres.conf文件

wal_level = logical
max_wal_senders = 100

2、发布者节点创建用户

#以public模式为例
create user repl replication with password 'repl';
alter user repl set default_transaction_read_only=on;
grant USAGE on SCHEMA public to repl;
grant SELECT on ALL tables in schema public to repl;
alter default privileges in schema public grant select on tables to repl;

3、修改发布者节点pg_hba.conf文件

host    all    repl    订阅者IP/32    md5

4、重启PG

su - postgres -c "pg_ctl restart"

5、发布者节点创建发布

create publication pub1 for table tab1;
--alter table tab1 replica identity full; --无主键或者唯一性约束时开启补全日志，此时会导致复制效率很低

6、订阅者节点

#max_replication_slots需要设置
#max_logical_replication_workers需要设置
#max_worker_processes需要设置
#创建表结构
create subscription pub1 connection 'host=发布者IP post=5432 dbname=postgres user=repl password=repl' publication pub1;
alter subscription  pub1  refresh publication;

运维监控

alter publication pub1 add table test.tab1;#从节点需要刷新才能看到新添加表
alter subscription pub1 refresh publication; #刷新订阅
drop pubilcation pub1; #删除发布
select * from pg_pubilcation;#发布
select * from pg_pubilcation_tables;#发布哪些表
select * from pg_stat_subscription;#复制进度
select * from pg_subscription; #订阅
select *,srrelid::regclass from pg_subscription_rel; #订阅哪些表
drop subscription pub1;#删除订阅

冲突处理

update或delete操作时可能会导致冲突，这时可以查看日志刚刚订阅者上的数据，或者使用pg_replication_origin_advance()函数跳过该事务。