logical_ddl逻辑复制插件

插件地址：
https://github.com/samedyildirim/logical_ddl

logical_ddl是PostgreSQL的扩展，它获取表上的DDL操作，并帮助它们通过逻辑复制向订阅者复制。该扩展是为了减少ddl所需的手动操作数量，以及在使用逻辑复制时由于表定义不匹配而停止复制的风险。

支持PostgreSQL 11及以上版本

怎么运行的？

logical_ddl利用PostgreSQL的事件触发特性捕获DDL操作。DDL命令执行后，调用用PL/pgSQL和C语言编写的触发器函数，将执行的DDL命令保存到表中，并通过逻辑复制进行复制。当订阅者接收到DDL操作的详细信息时，它生成一个SQL (DDL)查询，以便对订阅者端的表应用相同或类似的更改并执行。

支持哪些DDL操作

- ALTER TABLE ... RENAME TO ...
- ALTER TABLE ... RENAME COLUMN ... TO ...
- ALTER TABLE ... ADD COLUMN ...
- ALTER TABLE ... ALTER COLUMN ... TYPE ...
- ALTER TABLE ... DROP COLUMN ...

哪些数据类型兼容

- 支持所有内置数据类型
- 支持阵列
- 支持复合数据类型、域和枚举类型
    - 不支持复制复合数据类型、域和枚举类型本身的定义。数据类型或域应该已经在订阅者端可用。

注意事项

• 为了能对表进行ALTER TABLE命令，role必须是对象的所有者或者是SUPERUSER权限。Logical_ddl在superuser权限下工作
• PostGIS 尚未经过测试
• 数据类型更改的USING_表达式尚未实现
• 获取和复制列的默认值表达式尚未实现
• 获取和复制表和列约束尚未实现
• 获取和复制索引尚未实现
• 该扩展仍在开发中，不久的将来可能会有与当前版本不兼容的更改

安装

tar -zxvf logical_ddl-0.1.0.tar.gz
cd logical_ddl-0.1.0
make 
make install

编译成功后，在数据库中创建扩展

testdb=# create extension logical_ddl ;
CREATE EXTENSION
testdb=# \dx
                          List of installed extensions
    Name     | Version |   Schema    |               Description                
-------------+---------+-------------+------------------------------------------
 logical_ddl | 0.1.0   | logical_ddl | DDL replication over logical replication
 plpgsql     | 1.0     | pg_catalog  | PL/pgSQL procedural language
(2 rows)

创建扩展后，会自动创建一个名为logical_ddl的schema，logical_ddl下自动创建了几张配置表

testdb=# set search_path to logical_ddl ;
SET
testdb=# \d
                       List of relations
   Schema    |            Name            |   Type   |  Owner   
-------------+----------------------------+----------+----------
 logical_ddl | applied_commands           | table    | postgres
 logical_ddl | applied_commands_id_seq    | sequence | postgres
 logical_ddl | publish_tablelist          | table    | postgres
 logical_ddl | publish_tablelist_id_seq   | sequence | postgres
 logical_ddl | settings                   | table    | postgres
 logical_ddl | shadow_table               | table    | postgres
 logical_ddl | shadow_table_id_seq        | sequence | postgres
 logical_ddl | subscribe_tablelist        | table    | postgres
 logical_ddl | subscribe_tablelist_id_seq | sequence | postgres
(9 rows)

使用

在使用logical_ddl来完成DDL的发布之前，需要先搭建好PostgreSQL的逻辑复制，发布和订阅需要创建好，需要发布的表要先在订阅端创建好，

1.发布端

在需要发布的业务库中create extension

testdb=# create extension logical_ddl ;
CREATE EXTENSION

给你的发布起一个名字

testdb=# insert into logical_ddl.settings values (true,'source1');
INSERT 0 1

添加表到发布里

testdb=# insert into logical_ddl.publish_tablelist (relid) VALUES ('table1'::regclass);
INSERT 0 1

如果要在逻辑复制中添加所有表，可以使用下面的查询。

testdb=# insert into logical_ddl.publish_tablelist (relid) SELECT prrelid FROM pg_catalog.pg_publication_rel;

需要注意的是，如果你需要发布的表存在于多个publication中，使用该语句会触发publish_tablelist表字段relid上的唯一索引。需要在pg_publication中找到你创建的发布的名称所对应的oid值，然后在pg_publication_rel中prpubid字段上找到对应的值

testdb=# insert into logical_ddl.publish_tablelist (relid) SELECT prrelid FROM pg_catalog.pg_publication_rel where prpubid = '234923946';

将shadow_table加到你的publication中

testdb=# alter publication pub_name  add table logical_ddl.shadow_table;

shadow_table是存储捕获的更改的表

2.订阅端

在订阅的业务库中create extension

testdb=# create extension logical_ddl ;
CREATE EXTENSION

添加DDL发布者源名称

testdb=# insert into logical_ddl.settings VALUES (false, 'source1');
INSERT 0 1

为DDL操作添加目标表

testdb=# insert into logical_ddl.subscribe_tablelist (source,relid) VALUES ('source1','table1'::regclass);

如果要在逻辑复制中添加所有表，可以使用下面的查询。

testdb=# INSERT INTO logical_ddl.subscribe_tablelist (source,relid) SELECT 'source1',srrelid FROM pg_catalog.pg_subscription_rel;

刷新publication

testdb=# ALTER SUBSCRIPTION sub_name REFRESH PUBLICATION;

3.测试：

1.发布端：

testdb=# create table test.t1(id int);
CREATE TABLE
testdb=# insert into logical_ddl.settings values (true,'test');
INSERT 0 1
testdb=# insert into logical_ddl.publish_tablelist (relid) values ('test.t1'::regclass);
INSERT 0 1
testdb=# alter publication pub1  add table logical_ddl.shadow_table;

给逻辑发布用的role赋权，让其有权限读取logical_ddl里面的表

testdb=# grant USAGE on SCHEMA logical_ddl to logical_rel;
GRANT 
testdb=# grant SELECT on ALL tables in schema logical_ddl to logical_rel ;
GRANT
testdb=# alter default privileges in schema logical_ddl grant select on tables to logical_rel;
ALTER DEFAULT PRIVILEGES

2.订阅端

创建对应的t1表并添加配置信息

testdb=# create table test.t1(id int);
CREATE TABLE
testdb=# insert into logical_ddl.settings VALUES (false, 'test');、
INSERT 0 1
testdb=# insert into logical_ddl.subscribe_tablelist (source,relid) VALUES ('test','test.t1'::regclass);
INSERT 0 1
testdb=# alter subscription sub1 refresh publication;
ALTER SUBSCRIPTION
testdb=# 

3.t1表添加字段

##发布端

testdb=# select * from test.t1;
 id 
----
(0 rows)
testdb=#
testdb=# alter table test.t1 add column id2 int;
ALTER TABLE

###订阅端查看t1表
testdb=# select * from test.t1;
 id | id2 
----+-----
(0 rows)

4.t1表删除字段

##发布端
testdb=# alter table test.t1  drop  column id2 ;
ALTER TABLE
testdb=# select * from test.t1;
 id 
----
(0 rows)

###订阅端查看t1表
testdb=# select * from test.t1;
 id 
----
(0 rows)```