逻辑解码Logical decoding

备注

Azure Database for PostgreSQL - 单一服务器上的逻辑解码目前为公共预览版。Logical decoding is in public preview on Azure Database for PostgreSQL - Single Server.

使用 PostgreSQL 中的逻辑解码可将数据更改流式传输到外部使用者。Logical decoding in PostgreSQL allows you to stream data changes to external consumers. 逻辑解码广泛用于事件流和变更数据捕获方案。Logical decoding is popularly used for event streaming and change data capture scenarios.

逻辑解码使用一个输出插件将 Postgres 的预写日志 (WAL) 转换为可读格式。Logical decoding uses an output plugin to convert Postgres's write ahead log (WAL) into a readable format. Azure Database for PostgreSQL 提供以下输出插件:wal2jsontest_decoding 和 pgoutput。Azure Database for PostgreSQL provides the output plugins wal2json, test_decoding and pgoutput. pgoutput 由 PostgreSQL 10 及更高版本中的 PostgreSQL 提供。pgoutput is made available by PostgreSQL from PostgreSQL version 10 and up.

有关 Postgres 逻辑解码工作原理的概述,请访问我们的博客For an overview of how Postgres logical decoding works, visit our blog.

备注

Azure Database for PostgreSQL 单一服务器不支持使用 PostgreSQL 发布/订阅的逻辑复制。Logical replication using PostgreSQL publication/subscription is not supported with Azure Database for PostgreSQL - Single Server.

设置服务器Set up your server

逻辑解码和只读副本都依赖于 Postgres 预写日志 (WAL) 来获取信息。Logical decoding and read replicas both depend on the Postgres write ahead log (WAL) for information. 这两个功能需要使用来自 Postgres 的不同级别的日志记录。These two features need different levels of logging from Postgres. 逻辑解码需要的日志记录的级别比只读副本需要的更高。Logical decoding needs a higher level of logging than read replicas.

若要配置正确的日志记录级别,请使用 Azure 复制支持参数。To configure the right level of logging, use the Azure replication support parameter. Azure 复制支持有三个设置选项:Azure replication support has three setting options:

  • 关闭 - 在 WAL 中包含最少的信息。Off - Puts the least information in the WAL. 大多数 Azure Database for PostgreSQL 服务器上都不提供此设置。This setting is not available on most Azure Database for PostgreSQL servers.
  • 副本 - 比“关闭”详细。Replica - More verbose than Off. 这是运行只读副本所需的最低日志记录级别。This is the minimum level of logging needed for read replicas to work. 此设置是大多数服务器上的默认设置。This setting is the default on most servers.
  • 逻辑 - 比“副本”详细。Logical - More verbose than Replica. 这是运行逻辑解码所需的最低日志记录级别。This is the minimum level of logging for logical decoding to work. 使用此设置时,只读副本也可以运行。Read replicas also work at this setting.

使用 Azure CLIUsing Azure CLI

  1. 将 azure.replication_support 设置为 logicalSet azure.replication_support to logical.

    az postgres server configuration set --resource-group mygroup --server-name myserver --name azure.replication_support --value logical
    
  2. 重启服务器以应用更改。Restart the server to apply the change.

    az postgres server restart --resource-group mygroup --name myserver
    
  3. 如果运行的是 Postgres 9.5 或 9.6,并使用公共网络访问,请添加防火墙规则以包括你将从中运行逻辑复制的客户端的公共 IP 地址。If you are running Postgres 9.5 or 9.6, and use public network access, add the firewall rule to include the public IP address of the client from where you will run the logical replication. 防火墙规则名称必须包括“_replrule”。The firewall rule name must include _replrule. 例如,“test_replrule”。For example, test_replrule. 若要在服务器上创建新的防火墙规则,请运行 az postgres server firewall-rule create 命令。To create a new firewall rule on the server, run the az postgres server firewall-rule create command.

使用 Azure 门户Using Azure portal

  1. 将 Azure 复制支持设置为“逻辑”。Set Azure replication support to logical. 选择“保存” 。Select Save.

    Azure Database for PostgreSQL - 复制 - Azure 复制支持

  2. 通过选择“是”,重启服务器以应用更改。Restart the server to apply the change by selecting Yes.

    Azure Database for PostgreSQL - 复制 - 确认重启

  3. 如果运行的是 Postgres 9.5 或 9.6,并使用公共网络访问,请添加防火墙规则以包括你将从中运行逻辑复制的客户端的公共 IP 地址。If you are running Postgres 9.5 or 9.6, and use public network access, add the firewall rule to include the public IP address of the client from where you will run the logical replication. 防火墙规则名称必须包括“_replrule”。The firewall rule name must include _replrule. 例如,“test_replrule”。For example, test_replrule. 然后单击“保存” 。Then click Save.

    Azure Database for PostgreSQL - 复制 - 添加防火墙规则

开始逻辑解码Start logical decoding

可以通过流式处理协议或 SQL 接口使用逻辑解码。Logical decoding can be consumed via streaming protocol or SQL interface. 这两种方法都使用复制槽Both methods use replication slots. 槽表示来自单一数据库的更改流。A slot represents a stream of changes from a single database.

使用复制槽需要 Postgres 的复制特权。Using a replication slot requires Postgres's replication privileges. 目前,复制特权仅适用于服务器的管理员用户。At this time, the replication privilege is only available for the server's admin user.

流式处理协议Streaming protocol

我们通常倾向于通过流式处理协议使用更改。Consuming changes using the streaming protocol is often preferable. 你可以创建自己的使用者/连接器,或者使用 Debezium 之类的工具。You can create your own consumer / connector, or use a tool like Debezium.

请访问 wal2json 文档,查看将流式处理协议与 pg_recvlogical 配合使用的示例Visit the wal2json documentation for an example using the streaming protocol with pg_recvlogical.

SQL 接口SQL interface

以下示例将 SQL 接口与 wal2json 插件配合使用。In the example below, we use the SQL interface with the wal2json plugin.

  1. 创建槽。Create a slot.

    SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');
    
  2. 发出 SQL 命令。Issue SQL commands. 例如:For example:

    CREATE TABLE a_table (
       id varchar(40) NOT NULL,
       item varchar(40),
       PRIMARY KEY (id)
    );
    
    INSERT INTO a_table (id, item) VALUES ('id1', 'item1');
    DELETE FROM a_table WHERE id='id1';
    
  3. 使用更改。Consume the changes.

    SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');
    

    输出如下所示:The output will look like:

    {
          "change": [
          ]
    }
    {
          "change": [
                   {
                            "kind": "insert",
                            "schema": "public",
                            "table": "a_table",
                            "columnnames": ["id", "item"],
                            "columntypes": ["character varying(40)", "character varying(40)"],
                            "columnvalues": ["id1", "item1"]
                   }
          ]
    }
    {
          "change": [
                   {
                            "kind": "delete",
                            "schema": "public",
                            "table": "a_table",
                            "oldkeys": {
                                  "keynames": ["id"],
                                  "keytypes": ["character varying(40)"],
                                  "keyvalues": ["id1"]
                            }
                   }
          ]
    }
    
  4. 用完槽后,请将其删除。Drop the slot once you are done using it.

    SELECT pg_drop_replication_slot('test_slot'); 
    

监视槽Monitoring slots

必须监视逻辑解码。You must monitor logical decoding. 必须删除任何未使用的复制槽。Any unused replication slot must be dropped. 在使用者读取更改之前,槽会一直保存到 Postgres WAL 日志和相关的系统目录。Slots hold on to Postgres WAL logs and relevant system catalogs until changes have been read by a consumer. 如果使用者失败或者未经正确配置,则未使用的日志将不断堆积,直至填满存储。If your consumer fails or has not been properly configured, the unconsumed logs will pile up and fill your storage. 此外,未使用的日志会增大事务 ID 换行的风险。Also, unconsumed logs increase the risk of transaction ID wraparound. 这两种情况可能会导致服务器不可用。Both situations can cause the server to become unavailable. 因此,逻辑复制槽被持续使用是至关重要的。Therefore, it is critical that logical replication slots are consumed continuously. 如果不再使用某个逻辑复制槽,请立即将其删除。If a logical replication slot is no longer used, drop it immediately.

pg_replication_slots 视图中的“active”列指示是否有使用者连接到某个槽。The 'active' column in the pg_replication_slots view will indicate whether there is a consumer connected to a slot.

SELECT * FROM pg_replication_slots;

请针对“已用存储”和“副本的最大滞后时间”指标设置警报,以便在值超过正常阈值时收到通知。 Set alerts on Storage used and Max lag across replicas metrics to notify you when the values increase past normal thresholds.

重要

必须删除未使用的复制槽。You must drop unused replication slots. 否则可能会导致服务器不可用。Failing to do so can lead to server unavailability.

如何删除槽How to drop a slot

如果你未积极使用某个复制槽,应将其删除。If you are not actively consuming a replication slot you should drop it.

若要使用 SQL 删除名为 test_slot 的复制槽:To drop a replication slot called test_slot using SQL:

SELECT pg_drop_replication_slot('test_slot');

重要

如果停止使用逻辑解码,请将 azure.replication_support 改回到 replicaoffIf you stop using logical decoding, change azure.replication_support back to replica or off. 通过 logical 保留的 WAL 详细信息更详尽,应在逻辑解码未在使用时被禁用。The WAL details retained by logical are more verbose, and should be disabled when logical decoding is not in use.

后续步骤Next steps