postgres 逻辑复制

一、发布节点创建逻辑复制用户

也可以不创建，直接使用已有的用户但需要注意必须有流复制权限

create user logicalrep replication login encrypted password ‘logicalrep’; 必须有流复制权限

二、发布节点创建发

Command:     CREATE PUBLICATION  
Description: define a new publication  
Syntax:  
CREATE PUBLICATION name
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
      | FOR ALL TABLES ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ] 
where option can be:  
      PUBLISH INSERT | NOPUBLISH INSERT  
    | PUBLISH UPDATE | NOPUBLISH UPDATE  
    | PUBLISH DELETE | NOPUBLISH DELETE  
默认发布insert,update,delete。  

例子

--创建一个发布，发布两个表中所有更改:
create publication mypub1 for table users,departments;

--创建一个发布，发布所有表中的所有更改:
create publication mypub2 for all tables;

--创建一个发布，只发布一个表中的insert操作:
create publication mypub_insert_only for table mydata with (publish = 'insert');

--将发布修改为只发布删除和更新:
alter publication noinsert set (publish = 'update,delete');

--给发布添加一些表:
alter publication mypub1 add table emp,dept;

--查看发布:
select * from pg_publication;

三、订阅端创建订阅表

创建表结构同发布端表 结构相同 的空表 注意主键约束，没有的话发布端的update delete等操作会报错

create table t1 (id int primary key,name text)

四、订阅端创建订阅

Command:     CREATE SUBSCRIPTION  
Description: define a new subscription  
Syntax:  
CREATE SUBSCRIPTION subscription_name CONNECTION 'conninfo' PUBLICATION { publication_name [, ...] } [ WITH ( subscription_parameter [= value] [, ... ] ) ]  

例子

--创建一个到远程服务器订阅，复制发布mypub1和insert_only中的表，并在提交时立即开始复制:
create subscription mysub1
connection 'host=192.168.66.91 port=5432 user=replhc dbname=lhcdb'
publication mypub1,insert_only;

--创建一个到远程服务器订阅，复制insert_only发布中的表，并且不开始复制直到稍后启用复制:
create subscription mysub2
connection 'host=192.168.66.91 port=5432 user=replhc dbname=lhcdb'
publication insert_only
with (enabled = false);

--将订阅的发布更改为insert_only
alter subscription mypub1 set publication insert_only;

--禁用(停止)订阅:
alter subscription mypub1 disable;

五.管理复制槽

每个活跃的订阅都会通过复制槽 从远程发布者接受变更。

通常这个远端的复制槽是自动管理的，在CREATE SUBSCRIPTION时自动创建，在DROP SUBSCRIPTION时自动删除。

在特定场景下，可能需要分别操作订阅与底层的复制槽：

• 创建订阅时，所需的复制槽已经存在。则可以通过create_slot = false关联已有复制槽。

• 创建订阅时，远端不可达或状态不明朗，则可以通过connect =
  false不访问远程主机，pg_dump就是这么做的。这种情况下，您必须在远端手工创建复制槽后，才能在本地启用该订阅。

• 移除订阅时，需要保留复制槽。这种情况通常是订阅者要搬到另一台机器上去，希望在那里重新开始订阅。这种情况下需要先通过ALTER SUBSCRIPTION解除订阅与复制槽点关联

• 移除订阅时，远端不可达。这种情况下，需要在删除订阅之前使用ALTER SUBSCRIPTION解除复制槽与订阅的关联。

--postgresql.conf关联配置
wal_level = logical
max_replication_slots = 10
注意:参数设置后，重启数据后生效

--创建复制槽
select * from pg_create_logical_replication_slot('log_slot1','test_decoding');

--查看复制槽信息
select * from pg_replication_slots where slot_name='log_slot1';

--查看复制槽解析记录，记录只能查询一次，之后查询为空，如果想重复查询到日志，
--需使用pg_logical_slot_peek_changes()函数
select * from pg_logical_slot_get_changes('log_slot1',NULL,NULL);

--使用pg_recvlogical接收数据变化
pg_recvlogical -h 127.0.0.1 -d postgres --slot log_slot1 --start -f -

PS：如果远端实例不再使用那么没事，然而如果远端实例只是暂时不可达，那就应该手动删除其上的复制槽；否则它将继续保留WAL，并可能导致磁盘撑爆。

查看当前的复制槽

SELECT * FROM pg_replication_slots;

删除指定的复制槽

SELECT pg_drop_replication_slot('slot_name');

检测是否删除

SELECT * FROM pg_replication_slots;

参考资料:
13.PG14逻辑复制总结（一）
详谈pg逻辑复制系列之逻辑复制适用场景及原理介绍（一）
PostgreSQL 逻辑复制详解