使用pglogical插件

简介

pglogical 扩展使用发布/订阅模型为 PostgreSQL 提供逻辑流复制。

pglogical 是一个完全作为PostgreSQL 扩展实现的逻辑复制系统，完全集成，不需要触发器或外部程序。使用发布/订阅模型复制数据以进行选择性复制的一种高效方法。

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

支持的版本说明

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

SELECT * FROM pg_available_extension_versions WHERE name = 'pglogical';

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

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

插件介绍

pglogical支持的使用场景：

• 主版本数据库之间的升级（存在上述的版本限制）。
• 完整的数据库复制。
• 利用复制集，选择性的筛选的关系表。
• 可从多个上游服务器，做数据的聚集和合并。

pglogical要求：

• pglogical 扩展必须同时安装在发布端和订阅端上。
• 发布端和订阅端上的表必须具有相同的名称并且位于相同的模式中。
• 发布端和订阅端的表必须具有相同的列，每列中的数据类型必须相同。
• 表必须具有相同的主键。不建议添加除 PRIMARY KEY 之外的其他 UNIQUE 约束。
• 要复制多个数据库，您必须为每个数据库设置单独的发布端/订阅端关系。无法同时为 PostgreSQL 安装中的所有数据库配置复制。

插件安装/卸载

• 安装插件

  select control_extension('create', 'pglogical');

• 删除插件

  select control_extension('drop', 'pglogical');

更多信息，请参见通过界面安装和卸载插件和通过SQL命令安装和卸载插件。

基本使用

使用pglogical插件的功能，需要修改配置参数。

wal_level = 'logical'

shared_preload_libraries = 'pglogical'

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

1. 配置逻辑流复制。在发布端创建发布节点：

   SELECT pglogical.create_node(
       node_name := 'provider',
       dsn := 'host=127.0.0.1 port=5432 dbname=test user=provider_user'
   );

2. 配置复制集。将public中的所有表添加到default复制集：

   SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

   

   复制集用来控制将发布节点数据库中的哪些表以及这些表上的哪些操作发布给订阅者。

   default复制集，表示发布所有表及这些表的所有操作。

   更多复制集定义请参见pglogical官方文档。

3. 在订阅端创建订阅节点。 一旦设置了发布端节点，订阅者就可以订阅它。首先必须创建订阅者节点：

   SELECT pglogical.create_node(
       node_name := 'subscriber',
       dsn := 'host=127.0.0.1 port=5432 dbname=test user=subscriber_user'
   );

4. 在订阅端创建订阅。在订阅者节点创建订阅后，将在后台启动同步和复制过程：

   SELECT pglogical.create_subscription(
       subscription_name := 'subscription',
       provider_dsn := 'host=providerhost port=5432 dbname=test user=provider_user'
   );
   SELECT pglogical.wait_for_subscription_sync_complete('subscription');

进阶使用

1. 在发布端和订阅端分别创建要复制的表。

   create table test(id int primary key, name text, reg_time timestamp);

   

   发布端和订阅端的表必须名字、结构一致。

2. 在发布端给表插入数据。

   insert into test select generate_series(1,10000),'test',now();

3. 在发布端将表加入复制集。

   -- 将所有的表加入到复制集
   SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
   -- 将指定的表加入到复制集
   SELECT pglogical.replication_set_add_table( set_name := 'default', relation := 'test',synchronize_data := true);

   

   如果执行sql将所有的表加入到复制集，那么在订阅端还需要执行以下步骤将数据同步，否则数据无法到达订阅端，订阅状态是unknown。

   select pglogical.alter_subscription_synchronize('subscription1');

   如果执行sql将指定的表加入到复制集，则默认会自动同步。

4. 验证该表已经被加入到复制集。

   select * from pglogical.replication_set_table ;

5. 订阅端查询订阅状态。

   select * from pglogical.show_subscription_table('subscription1','test');

6. 订阅端查看表数据是否同步。

   select count(*) from test;