Databases for PostgreSQL 作为逻辑复制目的地

IBM Cloud® Databases for PostgreSQL 支持逻辑复制，您可以创建订阅者或发布者。您还可以将外部 PostgreSQL 设置为发布者，将 Databases for PostgreSQL 部署设置为订阅者，并将数据从外部数据库复制到您的部署中。

逻辑复制仅适用于运行 PostgreSQL 10及以上版本的部署环境。 PostgreSQL 文档的链接将引导您访问 PostgreSQL 的当前版本。如果您需要特定版本的文档，可以在 PostgreSQL 文档页面上找到不同版本的 PostgreSQL 的链接。

配置发布者

外部 PostgreSQL 实例是发布器，需要进行配置，以便 Databases for PostgreSQL 部署能够连接并正确调入数据。

发布功能

create_publisher

在每个数据库中，您可以创建多个出版物，以便将更改发布到不同的订阅者。

Arguments:

    publisher_name               The Unique name of publisher.
    for table                    To publish single/list of tables
    for all table                To publish all tables, along with future tables.
    for all tables in schema     To publish all tables in schema, along wth future tables.

Usage:
    exampledb=> CREATE PUBLICATION my_publication;
    CREATE PUBLICATION

list_publisher

列出每个数据库中运行的出版物数量。

Usage:
   exampledb=# select * from pg_publication;
   oid  |    pubname     | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
   -------+----------------+----------+--------------+-----------+-----------+-----------+-------------
   16401 | my_publication |       10 | t            | t         | t         | t         | t

alter_publisher

修改出版物的定义。

Syntax:
ALTER PUBLICATION name ADD publication_object [, ...]
ALTER PUBLICATION name SET publication_object [, ...]
ALTER PUBLICATION name DROP publication_object [, ...]
ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name RENAME TO new_name


 Usage:
    exampledb=# ALTER PUBLICATION my_publication RENAME TO my_publication_new;
    ALTER PUBLICATION

drop_publisher

删除/移除不再使用的出版物。

Usage:
    exampledb=# drop publication my_publication_new;
    DROP PUBLICATION

请考虑以下前提条件：

配置外部（发布者） PostgreSQL wal_level=logical。
每个被选中用于复制的表都需要包含一个主键，或者 REPLICA IDENTITY 一组。
您的外部（发布商） PostgreSQL 需要有一个拥有 PostgreSQL 权限 REPLICATION 的复制用户，该用户需要拥有数据库的SELECT和USAGE权限以及REPLICATION权限。
您正在从中进行复制的 PostgreSQL 需要启用 TLS / SSL。
如果启用了行级安全，则向外部（发布者） PostgreSQL 复制用户授予 BYPASSRLS 权限。

PostgreSQL 文档中概述了逻辑复制的固有局限性以及一些限制。在决定逻辑复制是否适合您的使用案例之前，请先阅读这些内容。

配置用户

要配置Databases for PostgreSQL部署并确保正确复制数据，请确保以下几点。

您需要在部署中创建一个与您打算复制的数据库同名的数据库。
逻辑复制在表级别上运行，因此在开始逻辑复制流程之前，需要在订阅者中创建您选择发布的每一个表。 (您可以使用 pg_dump 来提供帮助）订阅者的表格不必与发布者的表格完全相同。不过，订阅者的表必须至少包含发布者表中的每一列。订阅者中的其他列不得具有 NOT NULL 或其他限制条件。如果有，复制就会失败。

PostgreSQL 的原生订阅命令需要超级用户权限，而 Databases for PostgreSQL 部署环境中不具备此类权限。相反，您的部署包含一组功能，可用于设置和管理订阅的逻辑复制。

只有由 Databases for PostgreSQL 提供的管理员用户才有权限运行以下复制命令，这些命令可让您订阅并复制来自外部 PostgreSQL 发布者的内容。

用户功能

create_subscription

Arguments:
    subscription_name   Unique name to create the subscription channel with
    host_ip             Publisher hostname or public IP address
    port                Port number publisher is running on
    password            Password of the `admin` user on the publisher
    username            `admin` user created on the publisher
    db_name             The name of the database to be replicated
    publisher_name      The name of publisher channel on the publisher

    These additional configuration options are only supported in PostgreSQL 17 and later.
    copy_data           The copy_data parameter (true or false, default: true)
                        determines whether the initial data from the publication
                        should be copied to the subscriber when the subscription
                        is created.
    origin              The origin parameter (ANY or NONE, default: NONE) allows
                        filtering of changes based on their origin. It helps exclude
                        or include changes coming from specific nodes in multi-node or
                        cascading replication setups.
    failover            The failover parameter (true or false, default: false) enables
                        support for automatic failover of the subscription between replicated
                        nodes, ensuring continuity during primary node transitions.

Usage:
    exampledb=> SELECT create_subscription('subs1','130.215.223.184','5432','password','admin','exampledb','my_publication');

    PostgreSQL 17 and later
    exampledb=> SELECT create_subscription('subs1','130.215.223.184','5432','password','admin','exampledb','my_publication'  'true',  'ANY' , 'true');

delete_subscription

Arguments:
    subscription_name   Name the subscription channel to delete
    db_name             The name of the replicated database

Usage:
exampledb=> SELECT delete_subscription('subs1', 'exampledb');

list_subscriptions

Arguments:
    None

Usage:
    exampledb=> SELECT * FROM list_subscriptions();

disable_subscription

Arguments:
    subscription_name   Name the subscription channel to disable
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT disable_subscription('subs1','exampledb');

enable_subscription

Arguments:
    subscription_name   Name the subscription channel to enable
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT enable_subscription('subs1','exampledb');

subscription_slot_none

此函数用于将订阅的插槽名称设置为“无”。删除订阅时需要这样做，这样远程复制插槽就不会被删除，或者不存在或从未存在过。

Arguments:
    subscription_name   Name the subscription channel to alter
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT subscription_slot_none('subs1','exampledb');

refresh_subscription

该函数用于在发布者进行更改（如添加或删除表）后刷新订阅者的订阅。

Arguments:
    subscription_name   Name the subscription channel to refresh
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT refresh_subscription('subs1','exampledb');

在发布者上设置逻辑复制

要将外部 PostgreSQL 配置为发布者，请执行以下步骤。

编辑本地 pg_hba.conf 并添加以下内容。

hostssl    replication            replicator         0.0.0.0/0      md5
hostssl    all                    replicator         0.0.0.0/0      md5

复制器 "字段是您使用 PostgreSQL 特权 REPLICATION 设置的用户。

使用所需的逻辑复制配置编辑本地 postgresql.conf。将 wal_level 设置为“逻辑”，并将 listen_addresses='*' 设置为接受来自任何主机的连接。
```
listen_addresses='*'
wal_level = logical
```
请重启您的 PostgreSQL 服务器。

现在可以在数据库上定义一个发布者，并添加要复制到订阅者的表。

使用复制用户登录要发布的数据库。
```
psql -U replicator -d exampledb
```

创建发布渠道。

exampledb=> CREATE PUBLICATION my_publication;

为出版商添加表格。
```
exampledb=> ALTER PUBLICATION my_publication ADD TABLE my_table;
```
由 max_logical_replication_workers 配置参数定义的支持同步的 Worker 数量是有限的，不能更改。因此，应尽可能减少出版物的数量，并在一份出版物中添加尽可能多的表格。

在用户上设置逻辑复制

要将 Databases for PostgreSQL 部署配置为订阅者，请执行以下步骤。

使用 admin 用户登录为复制创建的数据库。
```
psql -U admin -d exampledb
```

运行以下查询，调用 create_subscription 函数并创建订阅者频道。

exampledb=> SELECT create_subscription('subs1','130.215.223.184','5432','admin','password','exampledb','my_publication');

监视复制

您可以通过在发布者和订阅者中的任意一个上运行以下查询来监控逻辑复制的状态。

exampledb=> SELECT * FROM pg_stat_replication;