PostgreSQL 逻辑复制一网打尽

l

PostgreSQL 的逻辑复制的场景还是蛮多的，尤其在一些需要进行关键数据表数据同步的情况下，将数据操作进行同步是十分有必要的业务场景。在提到POSTGRESQL的逻辑复制之前，还是的先说说逻辑复制的应用场景，以及与物理复制的不同和操作中的注意事项。

1  场景：

逻辑复制的场景主要包含

1   数据的跟踪与捕捉，如数据抽取与数据的汇聚

2   数据大表的迁移，通过逻辑复制可以量数据表从一个PG的服务器迁移到另一个物理的服务器

3  PG 物理服务器升级中大表的数据转移

4  数据的拆分和特定场景的数据处理，如复制仅仅进行insert操作，记录一个表中数据的原始记录等等

5   对原表的数据进行更多函数计算并直接落入复制表中

2 与物理复制的不同

1  仅仅提取数据库中指定数据表的DML数据输出

2  可以进行CDC的操作，获取数据进行异构数据库表的数据同步

3  数据复制中性能相对物理复制会比较差

4  对于复制的表时有要求的，（对比物理复制）

5  需要逻辑复制槽的支持

下面我们就先通过简单的操作来看看建立一个简单的逻辑复制的过场

下面会在两台服务器上建立逻辑复制

1  建立测试数据， 我们在一台机器的postgres 上建立test_data 数据表，并插入数据

首先我们先搞一批数据进去，其中的函数等等，回来有另外一篇说说测试数据的问题。

2  创建publication

create publication test_data_p for table test_data;

3  在另外一台机器的postgres库创建于原库一致的数据表，包括表名

4  create subscription test_sub connection 'host=192.168.198.100 dbname=postgres user=admin password=admin port=5432' publication test_data_p;

5 一个简单的逻辑复制就建立完成了，在原表中插入一条数据

insert into test_data (id,e_name,age,score,date) values (1010001,'CXCP',12,12.9,'2020-10-10');

6 在从库上查询相关数据已经插入到目的库表

以上是简单的数据逻辑复制操作，实际上逻辑复制中有很多的搭配和选择，同时逻辑复制也会有诸多的问题，下面通过事例来进行解释

例 1  

对一张表中的DML 操作有挑选的进行工作，如在操作中只进行insert 和 update 的操作的提取，不接受delete 操纵的

操作：此操作的重点在于建立publication 的时候对DML操作的设置

create publication test_data for table test_data WITH (publish = 'update','insert');

在publish 中设置对于 publish 的设置，通过对publish 的设置来限定发布的DML 操作的范围

注意：如果在设置中限定了某些DML操作不在逻辑复制的范围内，如以上逻辑中并没有delete的操作，那么主表在操作删除操作后，可能会再次插入，此时就会触发主键冲突的问题，最终导致复制停止。如下面的一些错误

例 2

在逻辑复制中添加表或者删除表

逻辑复制中最灵活的方案就是对于需要复制的表进行添加和删除，通过alter publication 的方式来添加表

1  我们在publication端添加一个表，插入数据

create table idt (id int primary key, name varchar(20));

insert into idt (id,name) values (1,'s');

2 在publication 端添加刚才加入的新表

ALTER PUBLICATION test_data_p ADD TABLE idt ;

ALTER PUBLICATION test_data_p ADD TABLE idt ;

3  在订阅端设置刷新的策略

alter subscription test_sub refresh publication;

数据就已经逻辑复制过来。

例3  

逻辑复制中对于主键的需求的问题，下面我们来看看如果一个表没有主键是否能进行逻辑复制

1  在publication 添加一个没有主键的表，并插入数据

create table withoutpk (id int, name varchar(20));

insert into withoutpk (id,name) values (1,'chk');

2  将表加入到publication当中

alter publication test_data_p add table withoutpk;

3 在目的端，进行复制刷新

create table withoutpk (id int, name varchar(20));

alter subscription test_sub refresh publication;

4 在目的端进行update操作

update withoutpk set id = 2 where name = 'chk';

首先为什么会报错，与MYSQL 原理同理，数据表复制中需要有一个字段作为表中的标识，通过标识来对表的复制信息到目的端进行准确的，一般都是通过表的主键作为默认的标识，而上面的表并没有主键，导致在数据UPDATE 的情况下，报错，需要通过set relica identity 来指定复制中的标识。

解决这个问题通常有两种方式

1 对表建立主键

2  建立唯一索引，通过命令告知复制中使用此索引作为标识

下面我们来通过建立唯一索引的方式来进行

create unique index uqe_id on withoutpk (id);
alter table withoutpk replica identity using index uqe_id;
alter table withoutpk alter id set not null;

alter table withoutpk replica identity using index uqe_id;

从上图可以看到，单独添加唯一索引后，如果字段为可 NULL ，相关的字段还是不能作为复制标识，必须将其设置为非空后，才可以进行数据复制

但此时目的端在之前就开始报错，所以数据已经无法进行复制

所以只能将表删除，重新建表，并建立索引和非空，然后刷新publication

同时如果实在不想使用索引作为标识的情况下，还可以使用FULL的模式将一整行作为复制的标识，这样操作需要有两个前提，1 表比较小  2 可以接受性能低的问题。

另外逻辑复制中也有一些问题是需要注意和知晓的

1  在高可用的环境下，如果主机切换，逻辑复制是无法进行切换的

2  如果在设置复制为同步模式，则可能在部分情况下引起主库commit的性能问题