PostgreSQL Logical Replication

PostgreSQL逻辑复制

一.定义
逻辑复制是PostgreSQL 10版本的新功能.
官方定义为：逻辑复制是一种根据数据对象的复制标识复制数据对象及其数据变化的方法.

这里需要注意以下几点：
1.需要逻辑复制的主表的复制标识通常为主键，也可以是唯一索引
2.从表的表结构需要与主表一致，包括Schema，表名，字段名，主键等
3.目前逻辑复制仅支持DML语句(例如Insert Update Delete),不支持DDL语句的同步(例如truncate功能)
4.目前逻辑复制不支持序列数据的同步
5.目前逻辑复制仅支持基表到基表之间的数据同步

二.优点
1.可以实现不同数据库之间的数据单向实时同步
2. 可以跨实例，但必须处于同一局域网内
3.可以跨平台，例如Linux到Windows上的PostgreSQL实例之间复制
4.可以跨版本，在PostgreSQL的不同主要版本之间复制
5.物理复制目前只能做到整个集群的复制,逻辑复制可以做到针对某个对象的复制
6.物理复制的从库只读，不能写入。逻辑复制的从库读写都可以

三.缺点
1.逻辑复制需要等待事务提交后，发布端才会使用wal_sender进程将decode后的数据发送给订阅端，订阅端流式接收与流式apply,因此逻辑复制对主库性能影响较大
2.数据库日志记录增加，会增加系统磁盘的空间(可以通过相应的参数配置来优化)
3.在数据抽取的应用方面，相对于ETL工具，在出现问题时排错较为复杂

四.操作步骤

1.修改数据库配置参数

将参数wal_level的值改为logical
文件名：postgresql.conf
文件路径：\PostgreSQL\11\data

说明：该参数需要重启数据库服务才能生效

2.创建publication
在主库创建发布，命令如下：

create publication my_pub1 for table public.my_table1；

说明：my_pub1为该发布的名称;
public.my_table1为需要逻辑复制的表名，多张表的情况下可以使用逗号分隔;
查询当前数据库的发布语句：

select * from pg_publication;

3.创建replication slots
创建复制槽的命令如下：

select * from pg_create_logical_replication_slot(‘my_slot1’, ‘pgoutput’);

说明：‘my_slot1’ 为复制槽的名称；
如果主库和从库是同一实例下的两个不同数据库，则必须要在主库创建复制槽;
如果主库和从库是不同实例，则无需在主库创建复制槽，直接跳到下一步创建订阅即可;

查询当前数据库的复制槽语句：

select * from pg_replication_slots;

4.创建subscription
创建订阅的命令如下：

create subscription my_sub1 connection ‘host=IP port=5432
dbname=my_db1 user=my_user1 password=my_pwd1’ publication my_pub1
with(create_slot=false)–如果上一步已经创建了复制槽，则需要加此参数.

说明：‘my_sub1’ 为订阅的名称；
‘my_pub1’ 为发布的名称；

查询当前数据库的订阅语句：

select * from pg_subscription;

参考资料：https://www.postgresql.org/docs/10/logical-replication.html