使用pgl_ddl_deploy插件

简介

有很多的数据库处于各种目的需要将数据复制到其他数据库，其中移动数据最有用的数据库技术之一被称为“逻辑复制”，但是数据库中有两类SQL语句，DML和DDL，出于很多原因，必须单独处理DDL，在迁移过程中需要让DBA以正确的顺序为所有涉及的数据库集群手动部署SQL，管理锁的争抢，并在必要时将新表添加到复制中。pgl_ddl_deploy建立在pglogical之上，能够使得任何DDL SQL语句都可以直接传播给订阅者，解决了pglogical不能够同步DDL语句的问题。

更多信息，请参见pgl_ddl_deploy官方文档。

支持的版本说明

PostgreSQL 12及以上版本的最新小版本支持该插件。可通过以下SQL语句查询当前实例是否支持该插件：

SELECT * FROM pg_available_extension_versions WHERE name = 'pgl_ddl_deploy';

如果不支持，可通过升级内核小版本或者使用转储与还原升级大版本使用该插件。

RDS PostgreSQL实例支持的插件，具体请参见支持的插件列表。

插件介绍

RDS PostgreSQL支持pgl_ddl_deploy插件，用于自动同步DDL语句，在很多环境中，能够涵盖大部分在应用环境中执行的DDL语句。

• 任何DDL语句都可以同步给订阅者。
• 表可以在创建时自动添加到复制中。
• 支持按正则表达式、按一组特定的表进行过滤。
• 可以选择以锁定安全的方式部署在订阅者上。
• 可以选择使订阅者上的某些事件失败，以便稍后重试。
• 可以在某些边缘情况下，围绕为DBA提供的日志记录构建警报，然后处理可能的手动部署。
• ALTER TABLE语句可以通过子命令标签进行过滤。
• 可选支持自动终止订阅者系统上组织DDL执行的阻塞进程。

插件安装/卸载

• 安装插件

  SELECT control_extension ('create', 'pgl_ddl_deploy');

• 删除插件

  SELECT control_extension ('drop', 'pgl_ddl_deploy');

基本使用

插件涉及到发布订阅和依赖于pglogical，需要添加和配置参数。

wal_level = 'logical'
shared_preload_libraries = 'pglogical'

shared_preload_libraries参数的修改可以参考修改shared_preload_libraries参数。

-- 发布端配置
SELECT control_extension ('create', 'pglogical');
SELECT control_extension ('create', 'pgl_ddl_deploy');
CREATE TABLE foo (id INT PRIMARY KEY);
-- 创建发布
CREATE PUBLICATION testpub FOR TABLE foo;
-- 配置复制集
INSERT INTO pgl_ddl_deploy.set_configs (set_name, include_schema_regex, driver) VALUES ('testpub', '.*', 'native'::pgl_ddl_deploy.driver);
-- 部署这个发布
SELECT pgl_ddl_deploy.deploy('testpub');
-- 添加用户权限
SELECT pgl_ddl_deploy.add_role(oid) FROM pg_roles WHERE rolname='root';
-- 订阅端配置
SELECT control_extension ('create', 'pglogical');
SELECT control_extension ('create', 'pgl_ddl_deploy');
CREATE TABLE foo (id INT PRIMARY KEY);
-- 创建订阅
CREATE SUBSCRIPTION testsub CONNECTION conninfo PUBLICATION testpub;
ALTER SUBSCRIPTION testsub REFRESH PUBLICATION;

配置完之后在发布端执行DDL语句：

ALTET TABLE foo ADD COLUMN bla INT;
CREATE TABLE bra (id INT PRIMARY KEY);

可以在订阅端验证：

\d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 bla    | integer |           |          |
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)
\dt
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | bar     | table | root
 public | foo     | table | root
(2 rows)

局限性

该插件具有一定的局限性，在很多情况下，该插件可能涵盖大部分在应用环境中执行的DDL语句，这并没有涵盖100%的边缘情况。

涉及多个表的DDL

不支持同时更改复制表和非复制表的单个DDL SQL语句，例如：将参数include_schema_regex配置成'^replicated.*'

DROP TABLE replicated.foo, notreplicated.bar;

此时发布端会出现提示：

WARNING: Unhandled deployment logged in pgl_ddl_deploy.unhandled
DROP TABLE

订阅端查询存在表replicated.foo：

\d replicated.foo
              Table "replicated.foo"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

同样，如果使用过滤复制，以下内容可能会出现问题：

ALTER TABLE replicated.foo ADD COLUMN bar_id INT REFERENCES notreplicated.bar (id);

订阅端查看是不同步的：

\d replicated.foo
              Table "replicated.foo"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id     | integer |           | not null |
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

不支持的命令

CREATE TABLE AS和SELECT INTO由于事务的一致性，不支持复制DDL，如果表是根据提供者的上一组数据创建的，那么在订阅者上运行相同的SQL将无法保证数据的一致性。例如：

CREATE TABLE foo AS
SELECT field_1, field_2, now() AS refreshed_at
FROM table_1;

SELECT INTO和CREATE TABLE AS类似，会出现提示：

WARNING:  Unhandled deployment logged in pgl_ddl_deploy.unhandled

多语句客户端SQL限制

当客户端将所有SQL语句作为一个字符串发送到PostgreSQL时，就会出现复杂性和局限性，例如如下语句：

CREATE TABLE foo (id serial primary key, bla text);
INSERT INTO foo (bla) VALUES ('hello world');

如果是通过psql调用的文件中，它将作为两个单独的SQL命令字符串运行。但是如果在Python或Ruby的ActiveRecord中创建了一个如上的字符串并执行了它，那么它将作为1个SQL命令字符串发送到Postgres。这种情况下根据allow_multi_statements有所不同：

• 如果是false，将只自动复制包含1个与事件触发命令标签匹配的命令标签的客户端SQL语句。这是安全的，但是可能会有未处理的部署。
• 如果是true，将只自动复制包含要传播的安全命令标签的DDL。比如，DDL和DML混合是被禁止的；如果在一个命令中有两个以上的DDL语句，作用在复制和不复制的表上，会出现涉及多个表的DDL问题。

基于以上情景的不能够自动同步到订阅者的SQL会记录为WARNING，并记录到unhandled表中，需要进行手动处理。 更多细节信息和解决复制中出现的问题请参阅pgl_ddl_deploy官方文档。