PostgreSQL实现DDL逻辑复制

PostgreSQL 社区官方版本不支持DDL的逻辑复制，仅支持DML【INSERT、UPDATE、DELETE、TRUNCATE】，且要求表有主键，否则UPDATE和DELETE无法复制（注意：逻辑复制要求wal_level为logical）。

• BDR【收费】

• pglogical【开源，比较复杂】

• pgl_ddl_deploy

本文主要介绍pgl_ddl_deploy插件。

pgl_ddl_deploy该插件可以实现PG逻辑复制中的DDL复制。该插件具有以下特点：

• 任何DDL SQL语句都可以直接传播给订阅者
• 表可以在创建时自动添加到复制中
• 支持过滤。可以选择性仅复制某些schema
• 可以选择以锁定安全的方式在订阅服务器上进行部署
• ALTER TABLE语句可以由子命令标签过滤

当前最新版本是2.10版，从2.0版本开始，它支持使用本机逻辑复制进行DDL复制(以前依赖于pglogical插件)。

详细信息请查阅：https://github.com/enova/pgl_ddl_deploy

1.1 创建发布

▼▼▼
CREATE PUBLICATION testpub FOR ALL TABLES IN SCHEAM public;CREATE EXTENSION pgl_ddl_deploy;

1.2 在发布端进行配置

▼▼▼
INSERT INTO pgl_ddl_deploy.set_configs (set_name,include_schema_regex,driver) VALUES('testpub','.*','native'::pgl_ddl_deploy.driver);

set_name:发布端的名字，一定要和创建的发布的名称一致

include_schema_regex:提供正则表达式，用来匹配需要同步的schema，它可以将新表自动添加到复制中

driver:选择原生（native）或者pglogical插件

1.3 开启DDL复制

▼▼▼
SELECT pgl_ddl_deploy.deploy('testpub') from pgl_ddl_deploy.set_configs;

返回结果为t，代表启动成功

1.4 相关权限配置

▼▼▼
SELECT pgl_ddl_deploy.add_role('testpub') from pg_roles where rolname='replic';

▼▼▼
CREATE EXTENSION pgl_ddl_deploy;CREATE SUBSCRIPTION testsub connection 'host=PG-1 port=5432 user=replic password=replic dbname=test' PUBLICATION testpub;

源端配置：

目标端配置：

源端执行ALTER：

目标查看ALTER直接结果：

源端添加和删除表：

目标端查看结果：

源端插入数据：

目标端查看：【新添加的表的数据不会自动同步到目标端，需要在目标端进行手工刷新】

END

更多精彩干货分享

点击下方名片关注

IT那活儿