“PostgreSQL逻辑复制不支持DDL复制？” “No，你能”

励志成为postgresql大神 2021-03-27

2079

逻辑复制DDL

PG官方文档上显示逻辑复制不支持DDL，但是客户有这样的需求。今天给大家带来一款插件pgl_ddl_deploy，它能帮助我们在逻辑复制上实现ddl复制。

pgl_ddl_deploy安装

当前最新版本是2.10版，从2.0版本开始，它支持使用本机逻辑复制进行DDL复制(以前依赖于pglogical插件)，最新版支持PostgreSQL 13。接下来我们来实际测试一下。

首先需要在发布端和订阅端都安装好pgl_ddl_deploy
插件。

--在发布端和订阅端都安装好pgl_ddl_deploy插件。export PGHOME=/data/postgresql/pgsqlexport PATH=$PGHOME/bin:$PATHgit clone https://github.com/enova/pgl_ddl_deploy.gitUSE_PGXS=1 makeUSE_PGXS=1 make installpostgres=# \c hrYou are now connected to database "hr" as user "postgres".hr=# create extension pgl_ddl_deploy;CREATE EXTENSION
复制

然后配置好逻辑复制。（事实证明这是个错误的想法，后面会说明）

--发布端CREATE PUBLICATION testpub FOR TABLE jobs;--订阅端create subscription testsub connection 'host=192.168.56.119 port=5432 dbname=hr user=replication' publication testpub;
复制

逻辑复制正常之后，我们就可以配置ddl同步了。

1.在发布端配置

发布端我们需要进行配置，配置方法就是往pgl_ddl_deploy表中插入记录。

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)或者是pgologic插件实现。

因为是demo配置，配置较为简单，详细复杂的配置选项参考:https://github.com/enova/pgl_ddl_deploy

2.开启DDL复制

配置完成后使用pgl_ddl_deploy.deploy方法启动ddl复制。

hr=# SELECT pgl_ddl_deploy.deploy('testpub') from pgl_ddl_deploy.set_configs;NOTICE:  table "tmp_objs" does not exist, skippingNOTICE:  event trigger "auto_rep_ddl_create_1_testpub" does not exist, skippingNOTICE:  event trigger "auto_rep_ddl_drop_1_testpub" does not exist, skippingNOTICE:  event trigger "auto_rep_ddl_unsupp_1_testpub" does not exist, skippingNOTICE:  function pgl_ddl_deploy.auto_rep_ddl_create_1_testpub() does not exist, skippingNOTICE:  function pgl_ddl_deploy.auto_rep_ddl_drop_1_testpub() does not exist, skippingNOTICE:  function pgl_ddl_deploy.auto_rep_ddl_unsupp_1_testpub() does not exist, skipping deploy -------- t(1 row)
复制

这里显示为t，代表启动成功。

3.测试DDL复制

启动成功之后，我们来做个小测试，jobs表来增加一列。

--发布侧hr=# alter table jobs add column a1 int; ALTER TABLE--订阅侧hr=# \d jobs                         Table "public.jobs"   Column   |         Type          | Collation | Nullable | Default ------------+-----------------------+-----------+----------+--------- job_id     | character varying(10) |           | not null |  job_title  | character varying(35) |           | not null |  min_salary | integer               |           |          |  max_salary | integer               |           |          | 
复制

可以看到并没有复制过来，这是什么情况？

一个可能的原因是因为没有权限。在发布侧和订阅侧都需要增加权限。

--发布侧和订阅侧SELECT pgl_ddl_deploy.add_role(oid) FROM pg_roles WHERE rolname IN('hr','replication');
复制

增加完权限之后，还是没有反映。查看发布侧相关视图。

可以看到命令执行被记录到了相关视图，但是并没有被复制过去，也没有异常。于是我把订阅删除重建了一下，这次就顺利同步过来了。

--订阅端SELECT pgl_ddl_deploy.add_role(oid) FROM pg_roles WHERE rolname IN('hr','replication');drop subscription testsub;create subscription testsub connection 'host=192.168.56.119 port=5432 dbname=hr user=replication' publication testpub;hr=# \d jobs                          Table "public.jobs"   Column   |         Type          | Collation | Nullable | Default ------------+-----------------------+-----------+----------+--------- job_id     | character varying(10) |           | not null |  job_title  | character varying(35) |           | not null |  min_salary | integer               |           |          |  max_salary | integer               |           |          |  a1         | integer               |           |          | 
复制

从后台日志可以看到。

进程启动后，会把queue
表也给复制过去。所以刚刚无法复制成功的原因就是订阅预先创建，没有对queue
表进行订阅。

hr=# \dRp+                            Publication testpub  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- postgres | f          | t       | t       | t       | t         | fTables:    "pgl_ddl_deploy.queue"    "public.jobs"
复制

现在查看订阅这边queue
表的信息，可以看到传到了订阅端。