使用 pglogical 在 OCI Database for PostgreSQL 中进行双向复制

简介

OCI PostgreSQL 中的逻辑复制支持基于表的复制身份（通常是主键）复制数据对象及其更改。它使用发布 - 订阅模式，其中一个或多个订户订阅在发布者节点上定义的出版物。订户从这些发布中提取数据，并可以选择重新发布更改以启用级联复制或更复杂的拓扑。

逻辑复制通过使用逻辑解码对 WAL（写前日志）进行解码来流式传输行级更改（INSERT、UPDATE 和 DELETE 操作）。这些解码更改独立于 OCI PostgreSQL 的物理存储格式，仅代表逻辑数据操作，为下游系统提供了更大的灵活性。

在本教程中，我们将讨论如何使用 pglogical 扩展进行逻辑复制，以及它如何在 OCI Database for PostgreSQL 中的两个数据库系统之间实现双向复制。

双向拷贝

双向复制 (BDR) 和 pglogical 意味着两个或多个 OCI PostgreSQL Database Systems 都发布和订阅彼此的更改。换言之，每个 OCI PostgreSQL 数据库系统都可以读取和写入，所有更改都按两个方向复制。因此，将在两个 OCI PostgreSQL 数据库系统上镜像和实施任何修改。

当您需要为位于多个位置的用户提供低延迟读写访问权限的区域或全局数据库架构时，通常建议使用双向复制。通过在每个区域的用户附近共同定位读/写实例，可以在本地进行更改，然后复制到其他区域，从而确保数据在所有节点之间同步。

在实践中，大多数用例只要求从多个区域写入一小部分表，而大多数数据库可以通过单向复制保持只读或集中管理。

但是，双向复制会带来复杂性。随着系统发展，保持数据一致性和管理复制拓扑可能会变得越来越困难。

如果您的应用的主要目标是实现区域中断的可读性和可恢复性，则一种更简单、更强大的方法是在同一区域或跨区域部署具有多个读取副本的适用于 PostgreSQL 实例的高可用性 OCI 数据库。

双向 pglogical 工作原理

在 pglogical 中，可以按如下方式配置多个订阅和出版物：

• Database System 1 发布更改→ Database System 2 订阅

• Database System 2 发布更改→ Database System 1 订阅

每个节点：

• 将更改发送至另一个

• 应用其他更改

• 使用 pglogical 的冲突解决设置处理潜在的冲突

要了解有关 OCI PostgreSQL 中跨区域的单向 pglogical 复制的更多信息，请参阅此博客：跨区域的单向 pglogical 。

管理冲突和配置

发布者（源数据库）定义一个或多个出版物，并将更改数据（DML 操作）发送到订户（目标数据库）。订户可以连接到多个发布者并将其更改应用到其本地数据库。

pglogical 扩展通过 pglogical.conflict_resolution 参数提供高级冲突解决方法，该参数支持以下模式：

• apply_remote（在 OCI Database for PostgreSQL 中为默认值）

• error

• keep_local

• last_update_wins

• first_update_wins

有关 pglogical 中的 conflict_resolution 的更多详细信息，请参阅有关 github 的官方文档：第二个象限配置选项。

使用 keep_local、last_update_wins 或 first_update_wins 时，必须在发布者和订户上启用 track_commit_timestamps。请记住，启用此设置可能会产生可衡量的性能开销，具体取决于您的工作负载。强烈建议在部署之前评估生产环境中的性能影响。

在 OCI PostgreSQL 中设置双向复制

要在 OCI Database for PostgreSQL 中启用 pglogical 扩展，请参阅以下教程：在 OCI PostgreSQL 中启用 pglogical 扩展。

任务 1：先决条件

在 OCI Database for PostgreSQL 中创建 pglogical 扩展后，向数据库系统 #1 和数据库系统 #2 授予以下权限。

ALTER ROLE psql WITH REPLICATION;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_reset() TO psql; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql; 
GRANT ALL ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql;

注：psql 是本教程中专门为演示目的而创建的示例复制用户。

任务 2：配置单向逻辑复制

在数据库系统 1 上：

完成前面概述的先决条件后，创建一个测试表并插入一些记录以验证 pglogical 工作流。

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 
INSERT INTO test_table
    (id, data)
VALUES
    (generate_series(1, 1000), 'Test');

创建提供方节点：

下一步是使用 pglogical 函数 create_node 创建节点。

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxx
            dbname=postgres'
);

下面是 create_node 函数的参数定义：

• node_name：指定要在数据库系统 #1 上创建的发布者的名称。
• host：输入数据库系统 #1 的全限定域名 (FQDN)。
• port_number：提供运行数据库系统 #1 的端口。
• database_name：指定将在其中配置 pglogical 复制的数据库的名称。

下一步，确定需要包含在复制中的表。pglogical 使用复制集来管理复制的表和操作（INSERT、UPDATE、DELETE）。您可以使用 create_replication_set 函数创建定制复制集或使用预定义的默认集，该集将复制其分配表上的所有操作。要包括表，请对单个表使用 replication_set_add_table，或使用 replication_set_add_all_tables 添加特定方案中的所有表。

以下命令将公共方案中的所有表添加到默认复制集：

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

要了解有关复制集的更多信息，请参见 Replication sets 。

在数据库系统 2 上：

创建与数据库系统 #1 中相同的表：

在数据库系统 #2 中创建测试表，以匹配数据库系统 #1 中的表结构。

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);

创建订阅者节点：

接下来，使用 pglogical 函数 create_node 在数据库系统 #2 上创建一个节点，就像在数据库系统 #1 上那样：

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=primary.xxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxxx
            dbname=postgres'
);

下面是 create_node 函数的参数定义：

• node_name：定义数据库系统 #2 上的订户的名称。
• host：输入数据库系统 #2 的全限定域名 (FQDN)。
• port_number：输入运行数据库系统 #2 的端口。
• database_name：提供要在其中创建订阅的数据库的名称。

创建 pglogic 订阅：

下一步是创建订阅，以使用函数 create_subscription 开始从数据库系统 #1 复制数据。

subscription_name 参数可以是您为订阅选择的任何名称。provider_dsn 是数据库系统 #1 的连接字符串，replication_sets 参数指定要使用的复制集。在本示例中，我们使用的是默认复制集。

synchronize_data 参数告知 pglogical 是否从源复制现有数据。在测试中，它设置为 true，因为我们希望复制所有行以及将来的任何更改。

forward_origins 参数对于启用双向复制至关重要。设置 forward_origins := '{}' 可确保仅转发源自节点本身的更改，而不转发已从其他节点复制的更改。这会阻止复制进程进入无限循环，其中更改会不断地来回复制。

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=primary.xxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}'
);

检查订阅状态：

使用以下命令检查数据库系统 #2 的订阅状态和基本信息：

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription1
status            | replicating
provider_node     | provider1
provider_dsn      | host=primary.xxxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxxx
slot_name         | pgl_postgres_provider1_subscription1
replication_sets  | {default}
forward_origins   |

任务 3：测试从数据库系统 #1 到数据库系统 #2 的复制

现在，在数据库系统 #1 （源）和数据库系统 #2 （目标）之间设置了复制，让我们测试在数据库系统 #1 中所做的更改是否自动复制到数据库系统 #2。

1. 验证初始数据

   在数据库系统 #2 上，检查表 test_table：

   postgres=> SELECT COUNT(*) FROM test_table;
    count 
   -------
     1000
   (1 row)
   

   这证实该表已经有 1000 行。

2. 测试 INSERT

   在数据库系统 #1 中添加新行：

   postgres=> INSERT INTO test_table VALUES (10000, 'initial load');
   

3. 在数据库系统 #1 中检查：

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

4. 现在检查数据库系统 #2。此时将显示相同的行，其中显示了复制工作：

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

5. 测试 DELETE

   删除数据库系统 #1 中的同一行：

   postgres=> DELETE FROM test_table WHERE id = 10000;
   

6. 在两个系统中验证 - 该行已从两个系统中消失。

   (0 rows)
   

7. 测试 UPDATE

   更新数据库系统 #1 中的现有行：

   postgres=> UPDATE test_table SET data = 'Initial load' WHERE id = 1;
   

   检查数据库系统 #1：

     id   |     data     
   -------+--------------
     1 | Initial load
   

   检查数据库系统 #2 - 更新后的值也在那里。

通过这些测试，我们确认 INSERT、UPDATE 和 DELETE 操作已从 Database System #1 → Database System #2 中正确复制。

任务 4：配置双向逻辑复制

以前的任务设置从 Database System #1 到 Database System #2 的单向复制。接下来，我们将运行其他命令以启用反向复制。

在数据库系统 2 上：

与在数据库系统 #1 上一样设置复制集：

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

在数据库系统 #1 上：

在数据库系统 #1 上创建订阅，以便它开始从数据库系统 #2 提取更改：

SELECT pglogical.create_subscription(
    subscription_name := 'subscription2',
    provider_dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := false,
    forward_origins := '{}'
);

在前面的命令中，synchronize_data 参数已设置为 false，这与以前不同。这是因为我们不需要将现有数据从数据库系统 #2 复制到数据库系统 #1，因为两个表已经包含相同的数据。

使用此命令，双向复制设置现在已完成，在一个服务器上所做的任何更改都将复制到另一个服务器上。接下来，检查数据库系统 #1 上的订阅状态。

检查数据库系统 #1 的订阅状态：

使用以下命令检查数据库系统 #1 的订阅状态和基本信息：

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription2
status            | replicating
provider_node     | subscriber1
provider_dsn      | host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxx
slot_name         | pgl_postgres_subscriber1_subscription2
replication_sets  | {default}
forward_origins   |

任务 5：测试双向复制

首先，向两个数据库系统添加一行，以验证双向复制是否正常运行。

1. 在数据库系统 #1 中插入行：

   在数据库系统 #1 上运行以下命令：

   postgres=# INSERT INTO test_table VALUES (1001, 'Initial_load');
   INSERT 0 1
   

2. 验证在数据库系统 #1 中插入的行：

   postgres=# SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

3. 验证数据库系统 #2 上的复制：

   现在，检查数据库系统 #2 以确认该行已复制：

   postgres=> SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

   这表明在数据库系统 #1 上的插入已复制到数据库系统 #2。

4. 在数据库系统 #2 中插入行：

   在数据库系统 #2 上运行以下命令：

   postgres=> INSERT INTO test_table VALUES (1002, 'Initial_load');
   INSERT 0 1
   

5. 验证在数据库系统 #2 中插入的行：

   postgres=> SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

6. 验证数据库系统 #1 上的复制：

   现在，检查数据库系统 #1 以确认该行已复制回：

   postgres=# SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

   此测试确认数据更改在 Database System #1 和 Database System #2 之间的两个方向均成功复制。

监视逻辑复制滞后

pglogical 设置完成后，应监视复制日志以跟踪复制插槽、延迟和其他相关度量。

select * from pg_stat_replication;

此命令返回 OCI PostgreSQL 中复制的当前状态。

小结

本教程将引导您完成使用 pglogical 的 PostgreSQL 双向复制的配置。双向复制增加了数据库基础设施的复杂性，并需要额外的努力，因此确认它符合您的用例非常重要。如果您的应用需要跨不同区域的多个写入器且数据库分片不可行，则双向复制是理想的解决方案。

确认

• 作者 - Kaviya Selvaraj（高级技术人员）

更多学习资源

通过 docs.oracle.com/learn 浏览其他实验室，或者通过 Oracle Learning YouTube 频道访问更多免费学习内容。此外，请访问 education.oracle.com/learning-explorer 以成为 Oracle Learning Explorer。

有关产品文档，请访问 Oracle 帮助中心。

---

标题和版权信息

Bi-directional replication in OCI Database for PostgreSQL using pglogical

G43216-01

Copyright ©2025,

Oracle 和/或其关联公司。