Pglogical 复制概述

pglogical 扩展使用发布/订阅模块为 PostgreSQL 提供逻辑流复制。它基于作为 BDR 项目的一部分开发的技术。

我们使用以下术语来描述节点之间的数据流，有意重复使用早期的 Slony 技术：

节点 - PostgreSQL 数据库实例
提供者和订阅者 - 节点承担的角色
复制集——表的集合

pglogical 是利用最新核心功能的新技术，因此我们有以下版本限制：

提供者和订阅者节点必须运行 PostgreSQL 9.4+
复制源过滤和冲突检测需要 PostgreSQL 9.5+
此外，订阅者可以是 Postgres-XL 9.5+

支持的用例包括：

主要版本之间的升级（考虑到上述限制）
完整数据库复制
使用复制集选择性地复制表集
在发布者或订阅者端选择性复制表行（row_filter）
发布者端表列的选择性复制
从多个上游服务器收集/合并数据

架构细节：

pglogical 在每个数据库级别上工作，而不是像物理流复制那样在整个服务器级别工作
一个提供程序可以为多个订阅程序提供服务，而不会产生额外的磁盘写入开销
一个订阅者可以合并来自多个来源的更改，并使用自动和可配置的冲突解决方案检测更改之间的冲突（多主所需的一些但不是所有方面）。
级联复制以变更集转发的形式实现。

1.要求

要使用 pglogical，提供商和订阅者必须运行 PostgreSQL 9.4 或更新版本。

提供商和订阅者都必须安装此 pglogical 扩展程序。您必须 CREATE EXTENSION pglogical在两者上都安装。

提供方和订阅方的表必须具有相同的名称并采用相同的架构。未来的修订版本可能会添加映射功能。

提供者和订阅者上的表必须具有相同的列，并且每列的数据类型相同。订阅者上的CHECK约束、 NOT NULL 限制等必须与提供者上的相同或更弱（更宽松）。

2. 使用方法

本节介绍 pglogical 复制扩展的基本用法。

2.1 快速设置

首先，必须正确配置 PostgreSQL 服务器以支持逻辑解码：

wal_level = 'logical'
max_worker_processes = 10   # one per database needed on provider node
                            # one per node needed on subscriber node
max_replication_slots = 10  # one per node needed on provider node
max_wal_senders = 10        # one per node needed on provider node
shared_preload_libraries = 'pglogical'

如果您使用的是 PostgreSQL 9.5+（这在 9.4 上不起作用）并且想要通过最后/第一个更新获胜来处理冲突解决（请参阅下面的冲突），您可以将这个附加选项添加到 postgresql.conf：

track_commit_timestamp = on # needed for last/first update wins conflict resolution
                            # property available in PostgreSQL 9.5+

pg_hba.conf 必须允许来自本地主机的复制连接。

接下来 pglogical 必须在所有节点上安装扩展：

CREATE EXTENSION pglogical;

如果使用 PostgreSQL 9.4，则 pglogical_origin 还必须在该节点上安装扩展：

CREATE EXTENSION pglogical_origin;

现在创建提供者节点：

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=providerhost port=5432 dbname=db'
);

将公共架构中的所有表添加到默认复制集。

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

您还可以选择创建额外的复制集并向其中添加表（请参阅下面的“复制集”）。

通常，最好在订阅之前创建复制集，以便在单个初始事务中初始复制设置期间同步所有表。但是，大型数据库的用户可能希望逐步创建它们，以便更好地控制。

一旦设置了提供者节点，订阅者就可以订阅它。首先必须创建订阅者节点：#

SELECT pglogical.create_node(
    node_name := 'subscriber1',#订阅者
    dsn := 'host=thishost port=5432 dbname=db'
);

最后，您可以在订阅者节点上创建订阅，它将在后台启动同步和复制过程：

SELECT pglogical.create_subscription(  #创建订阅
    subscription_name := 'subscription1',
    provider_dsn := 'host=providerhost port=5432 dbname=db'  #提供者名称
);

2.2 节点管理

可以使用 SQL 接口动态添加和删除节点。

pglogical.create_node(node_name name, dsn text) 创建一个节点。

参数：
- node_name - 新节点的名称，每个数据库只允许一个节点
- dsn - 节点的连接字符串，对于应该是提供者的节点，这应该是可以从外部访问的
pglogical.drop_node(node_name name, ifexists bool) 删除 pglogical 节点。

参数：
- node_name - 现有节点的名称
- ifexists - 如果为 true，则当订阅不存在时不会抛出错误，默认为 false
pglogical.alter_node_add_interface(node_name name, interface_name name, dsn text) 向节点添加附加接口。

创建节点时，还会创建其接口，其名称与节点相同，并dsn 在中指定。此接口允许向现有节点添加具有不同连接字符串的替代接口。create_node

参数：
- node_name - 现有节点的名称
- interface_name - 要添加的新接口的名称
- dsn - 用于新接口的节点的连接字符串
pglogical.alter_node_drop_interface(node_name name, interface_name name) 从节点中删除现有接口。

参数：

node_name - 现有节点的名称
interface_name - 现有接口的名称

2.3 订阅管理

pglogical.create_subscription(subscription_name name, provider_dsn text, replication_sets text[], synchronize_structure boolean, synchronize_data boolean, forward_origins text[], apply_delay interval) 创建从当前节点到提供者节点的订阅。命令不会阻塞，只会启动操作。

参数：
- subscription_name - 订阅的名称，必须是唯一的
- provider_dsn - 提供程序的连接字符串
- replication_sets - 要订阅的复制集数组，这些必须已经存在，默认值为“{default,default_insert_only,ddl_sql}”
- synchronize_structure - 指定是否将结构从提供者同步到订阅者，默认为 false
- synchronize_data - 指定是否将数据从提供者同步到订阅者，默认 true
- forward_origins - 要转发的来源名称数组，当前仅支持的值是空数组，表示不转发任何未源自提供者节点的更改，或“{all}”，表示复制所有更改，无论其来源是什么，默认值为“{all}”
- apply_delay - 延迟复制多长时间，默认为 0 秒
pglogical.drop_subscription(subscription_name name, ifexists bool) 断开订阅并将其从目录中删除。

参数：
- subscription_name - 现有订阅的名称
- ifexists - 如果为 true，则当订阅不存在时不会抛出错误，默认为 false
pglogical.alter_subscription_disable(subscription_name name, immediate bool) 禁用订阅并断开其与提供商的连接。

参数：
- subscription_name - 现有订阅的名称
- immediate - 如果为 true，则订阅立即停止，否则仅在当前事务结束时停止，默认为 false
pglogical.alter_subscription_enable(subscription_name name, immediate bool) 启用已禁用的订阅。

参数：
- subscription_name - 现有订阅的名称
- immediate - 如果为 true，则订阅将立即启动，否则它将仅在当前事务结束时启动，默认值为 false
pglogical.alter_subscription_interface(subscription_name name, interface_name name) 切换订阅以使用不同的接口连接到提供商节点。

参数：
- subscription_name - 现有订阅的名称
- interface_name - 当前提供商节点的现有接口的名称
pglogical.alter_subscription_synchronize(subscription_name name, truncate bool) 所有集合中所有未同步的表均在单个操作中同步。表将逐个复制和同步。命令不会阻塞，只会启动操作。

参数：
- subscription_name - 现有订阅的名称
- truncate - 如果为 true，则表将在复制之前被截断，默认为 false
pglogical.alter_subscription_resynchronize_table(subscription_name name, relation regclass) 重新同步一个现有表。警告：此函数将首先截断表。

参数：
- subscription_name - 现有订阅的名称
- relation - 现有表的名称，可选限定
pglogical.show_subscription_status(subscription_name name) 显示订阅的状态和基本信息。

参数：
- subscription_name - 现有订阅的可选名称，当未提供名称时，该函数将显示本地节点上所有订阅的状态
pglogical.show_subscription_table(subscription_name name, relation regclass) 显示表的同步状态。

参数：
- subscription_name - 现有订阅的名称
- relation - 现有表的名称，可选限定
pglogical.alter_subscription_add_replication_set(subscription_name name, replication_set name) 将一个复制集添加到订阅者。不同步，仅激活事件的消费。

参数：
- subscription_name - 现有订阅的名称
- replication_set - 要添加的复制集的名称
pglogical.alter_subscription_remove_replication_set(subscription_name name, replication_set name) 从订阅者中删除一个复制集。

参数：

subscription_name - 现有订阅的名称
replication_set - 要删除的复制集的名称

2.4 复制集

复制集提供了一种机制来控制数据库中的哪些表将被复制以及对这些表执行的哪些操作将被复制。

每个复制集可以单独指定集合中的、和是否 INSERTs被 UPDATEs复制 DELETEs 。 TRUNCATEs 每个表都可以位于多个复制集中，每个订阅者也可以订阅多个复制集。复制的表和操作的结果集是表所在集的并集。表只有在被添加到复制集中后才会被复制。

有三个预先存在的复制集，分别名为“default”、“default_insert_only”和“ddl_sql”。 “default”复制集定义为复制对表的所有更改。 “default_insert_only”仅复制 INSERT，适用于没有主键的表（有关详细信息，请参阅限制部分）。 “ddl_sql”复制集定义为复制由 pglogical.replicate_ddl_command

提供以下函数来管理复制集：

pglogical.create_replication_set(set_name name, replicate_insert bool, replicate_update bool, replicate_delete bool, replicate_truncate bool) 该函数创建一个新的复制集。

参数：
- set_name - 集合的名称必须是唯一的
- replicate_insert - 指定是否 INSERT 复制，默认为 true
- replicate_update - 指定是否 UPDATE 复制，默认为 true
- replicate_delete - 指定是否 DELETE 复制，默认为 true
- replicate_truncate - 指定是否 TRUNCATE 复制，默认为 true
pglogical.alter_replication_set(set_name name, replicate_inserts bool, replicate_updates bool, replicate_deletes bool, replicate_truncate bool) 该函数改变现有复制集的参数。

参数：
- set_name – 现有复制集的名称
- replicate_insert - 指定是否 INSERT 复制，默认为 true
- replicate_update - 指定是否 UPDATE 复制，默认为 true
- replicate_delete - 指定是否 DELETE 复制，默认为 true
- replicate_truncate - 指定是否 TRUNCATE 复制，默认为 true
pglogical.drop_replication_set(set_name text) 删除复制集。

参数：
- set_name – 现有复制集的名称
pglogical.replication_set_add_table(set_name name, relation regclass, synchronize_data boolean, 列文本[]，行过滤器文本) 将表添加到复制集。

参数：
- set_name – 现有复制集的名称
- relation - 要添加到集合中的表的名称或 OID
- synchronize_data - 如果为 true，则表数据将在订阅给定复制集的所有订阅者上同步，默认为 false
- columns - 要复制的列的列表。通常，当所有列都应复制时，该值将设置为默认值 NULL
- row_filter - 行过滤表达式，默认为 NULL（无过滤），有关详细信息，请参阅（行过滤）。警告：使用有效的行过滤器同步数据时请谨慎。使用 synchronize_data=true 有效的过滤row_filter 器就像对表进行一次性操作。使用 modified 再次执行它 row_filter不会将数据同步到订阅者。订阅者可能需要致电 pglogical.alter_subscription_resynchronize_table() 来修复它。
pglogical.replication_set_add_all_tables(set_name name, schema_names text[], synchronize_data boolean) 添加给定架构中的所有表。仅添加现有表，将来创建的表不会自动添加。有关如何确保将来创建的表添加到正确的复制集，请参阅“新表的复制集自动分配”部分。

参数：
- set_name – 现有复制集的名称
- schema_names - 应从中添加表的现有架构的名称数组
- synchronize_data - 如果为 true，则表数据将在订阅给定复制集的所有订阅者上同步，默认为 false
pglogical.replication_set_remove_table(set_name name, relation regclass) 从复制集中删除一个表。

参数：
- set_name – 现有复制集的名称
- relation - 要从集合中删除的表的名称或 OID
pglogical.replication_set_add_sequence(set_name name, relation regclass, synchronize_data boolean) 将序列添加到复制集。

参数：
- set_name – 现有复制集的名称
- relation - 要添加到集合中的序列的名称或 OID
- synchronize_data - 如果为 true，序列值将立即同步，默认为 false
pglogical.replication_set_add_all_sequences(set_name name, schema_names text[], synchronize_data boolean) 添加给定模式中的所有序列。仅添加现有序列，将来创建的任何序列都不会自动添加。

参数：
- set_name – 现有复制集的名称
- schema_names - 应从中添加序列的现有模式名称数组
- synchronize_data - 如果为 true，序列值将立即同步，默认为 false
pglogical.replication_set_remove_sequence(set_name name, relation regclass) 从复制集中删除一个表。

参数：
- set_name – 现有复制集的名称
- relation - 要从集合中删除的序列的名称或 OID

您可以通过查询视图来查看哪个表属于哪个集合的信息 pglogical.tables 。

2.4.1 新表的复制集自动分配

事件触发器功能可用于描述定义新建表的复制集的规则。

例子：

CREATE OR REPLACE FUNCTION pglogical_assign_repset()
RETURNS event_trigger AS $$
DECLARE obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF obj.object_type = 'table' THEN
            IF obj.schema_name = 'config' THEN
                PERFORM pglogical.replication_set_add_table('configuration', obj.objid);
            ELSIF NOT obj.in_extension THEN
                PERFORM pglogical.replication_set_add_table('default', obj.objid);
            END IF;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER pglogical_assign_repset_trg
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
    EXECUTE PROCEDURE pglogical_assign_repset();

上面的例子将把模式中创建的所有新表 config 放入复制集中 configuration ，并且所有其他不是由扩展创建的新表都将进入 default 复制集。

2.5 附加功能

pglogical.replicate_ddl_command(command text, replication_sets text[]) 在本地执行，然后将指定的命令发送到复制队列，以便在订阅了指定命令之一的订阅者上执行 replication_sets。

参数：
- command - 要执行的 DDL 查询
- replication_sets - 与该命令关联的复制集数组，默认为“{ddl_sql}”

pglogical.synchronize_sequence(relation regclass) 将序列状态推送给所有订阅者。与订阅和表同步功能不同，此功能应在提供程序上运行。它会强制更新跟踪的序列状态，一旦订阅者复制了执行此功能的事务，所有订阅者（复制集过滤仍然适用）都将使用该状态。

参数：
- relation - 现有序列的名称，可选限定

2.6 行过滤

PGLogical 允许在提供者端和订阅者端进行基于行的过滤。

根据提供商进行行过滤

row_filter 在提供程序上，可以通过为函数指定参数来完成行过滤 pglogical.replication_set_add_table 。row_filter 是普通的 PostgreSQL 表达式，其对允许的内容具有与约束相同的限制 CHECK 。

简单 row_filter 来说就是 row_filter := 'id > 0' 可以确保只有列值 id 大于零的行才会被复制。

允许在内部使用易失性函数 row_filter ，但在写入时必须小心，因为任何执行写入的表达式都会引发错误并停止复制。

还值得注意的是， row_filter 在复制会话内部运行，因此会话特定的表达式（例如） CURRENT_USER 将具有复制会话的值，而不是执行写入的会话的值。

订阅服务器上的行过滤

在订阅者上，可以使用标准 BEFORE TRIGGER 机制实现基于行的过滤。

需要将任何此类触发器标记为 ENABLE REPLICA 否则 ENABLE ALWAYS 它们将不会被复制过程执行。

3. 冲突

如果节点订阅了多个提供商，或者在订阅者上发生本地写入，则传入更改可能会发生冲突。这些冲突会被自动检测，并可根据配置采取行动。

冲突解决程序的配置通过 pglogical.conflict_resolution 设置完成。支持的值包括 pglogical.conflict_resolution ：

error - 如果检测到冲突并且需要手动操作来解决，则复制将因错误而停止
apply_remote - 始终应用与本地数据冲突的更改，这是默认设置
keep_local - 保留本地版本的数据并忽略来自远程节点的冲突更改
last_update_wins - 具有最新提交时间戳的数据版本将被保留（可以是本地版本或远程版本）
first_update_wins - 将保留具有最旧时间戳的数据版本（可以是本地版本或远程版本）

可用的设置和默认值取决于 PostgreSQL 的版本和其他设置。

keep_local、last_update_wins和first_update_wins设置需要启用track_commit_timestamp PostgreSQL设置。由于track_commit_timestamp在PostgreSQL 9.4中不可用，pglogical.conflict_resolution只能是apply_remote或error。

在 Postgres-XL 中，唯一支持的值和默认值是 error。

pglogical.conflict_log_levelpglogical.conflict_resolution当设置为除之外的任何值时，设置用于报告检测到的冲突的日志级别 error。
此设置的主要用途是抑制冲突的记录。

可能的值与 log_min_messages PostgreSQL 设置相同。

默认值为 LOG。
pglogical.batch_inserts 告知 PGLogical 在可能的情况下使用批量插入机制。批量机制使用 PostgreSQL 内部批量插入模式，该模式也由 COPY 命令使用。

批量插入将提高对一个表进行多次插入的事务的复制性能。当事务执行超过 5 次插入时，PGLogical 将切换到批处理模式。

仅当表上没有 INSTEAD OF INSERT 和 BEFORE INSERT 触发器并且表的列没有带有易失性表达式的默认值时，才可以切换到批处理模式。此外，仅当 pglogical.conflict_resolution 设置为时，批处理模式才会起作用error。

默认值为 true。
pglogical.use_spi 告诉 PGLogical 使用 SPI 接口来形成实际的 SQL（INSERT、、）语句来应用传入的更改UPDATE， DELETE而不是使用内部低级接口。

这主要用于 Postgres-XL 和调试目的。

PostgreSQL 中的默认值是 false。

true 仅当 pglogical.conflict_resolution 设置为时才可以设置为 error。在此状态下，不会检测到冲突。

在 Postgres-XL 中，默认且唯一允许的设置是 true。
pglogical.temp_directory 定义存放架构同步所需临时文件的系统路径。此路径必须存在，并且运行 Postgres 的用户可写入。

默认值为空，告诉 PGLogical 根据环境和操作系统设置使用默认临时目录。

4. 限制和约束

4.1 需要超级用户

目前，pglogic 复制和管理需要超级用户权限。以后可能会扩展到更细粒度的权限。

`4.2 UNLOGGED` 并且 `TEMPORARY` 没有被复制

UNLOGGED 并且 TEMPORARY 表不会也不能被复制，就像物理流复制一样。

4.3 一次一个数据库

要复制多个数据库，您必须为每个数据库设置单独的提供者/订阅者关系。无法一次性为 PostgreSQL 安装中的所有数据库配置复制。

4.4 需要 PRIMARY KEY 或 REPLICA IDENTITY

对于缺少PRIMARY KEY或其他有效副本标识（如UNIQUE约束）的表，无法复制更新和删除。复制无法找到应该更新/删除的元组，因为没有唯一的标识符。

4.5 只有一个唯一索引/约束/PK

如果配置了多个上游或下游接受本地写入，则 UNIQUE 下游复制表上应只有一个索引。冲突解决一次只能使用一个索引，因此ERROR 如果某行满足 PRIMARY KEY 但违反了 UNIQUE 下游的约束，则可能会发生冲突。这将停止复制，直到修改下游表以消除违规。

如果下游仅从上游获取写入，而不从其他地方获取写入，则对上游设置额外的唯一约束是可以的。规则是下游约束不得比上游的约束更严格。

4.6 DDL

不支持自动 DDL 复制。管理 DDL 以使提供者和订阅者数据库保持兼容是用户的责任。

pglogical 提供 pglogical.replicate_ddl_command 允许 DDL 在提供程序和订阅者上的一致点上运行的功能。

4.7 无复制队列刷新

不支持在主服务器上冻结事务并等待所有待处理的排队 xact 从插槽中重放。未来版本将添加对此使上游变为只读的支持。

这意味着在应用表结构更改时必须小心谨慎。如果存在尚未复制的已提交事务，并且提供者和订阅者的表结构同时发生更改，导致订阅者表与排队的事务不兼容，则复制将停止。

管理员应该确保在进行架构更改之前停止对主服务器的写入，或者使用该pglogical.replicate_ddl_command 功能对架构更改进行排队，以便它们在副本上的一致点重播。

一旦添加了多主复制支持，使用 pglogical.replicate_ddl_command 将不够，因为在发布者上提交架构更改后，订阅者可能会使用旧结构生成新的 xact。用户必须确保在进行架构更改之前，所有节点上的写入都已停止，并且所有插槽都已赶上。

4.8 外键

复制过程中不强制执行外键约束 - 即使 FOREIGN KEY 违反了规定，提供者端的成功也会应用到订阅者端。

4.9 截断

使用 TRUNCATE ... CASCADE 只会 CASCADE 在提供商端应用该选项。

（正确处理这个问题可能需要 ON TRUNCATE CASCADE 在 PostgreSQL 中增加对外键的支持）。

TRUNCATE ... RESTART IDENTITY 不受支持。身份重启步骤不会复制到副本。

4.10 序列

添加到复制集的序列的状态是定期复制的，而不是实时复制的。动态缓冲区用于复制的值，以便订阅者实际上收到序列的未来状态。这最大限度地减少了订阅者对序列的 last_value 概念落后的可能性，但并没有完全消除这种可能性。

synchronize_sequence 在数据库中发生“重大事件”（例如数据加载或在线升级期间）后，可能需要调用以确保所有订阅者都拥有有关给定序列的最新信息。

对于多节点系统上的序列，通常建议使用bigserial和bigint类型，因为较小的序列可能会很快到达序列空间的末尾。

希望在提供者和订阅者上拥有独立序列的用户可以避免将序列添加到复制集中，并创建步长间隔等于或大于节点数的序列。然后在每个节点上设置不同的偏移量。使用CREATE SEQUENCE或ALTER SEQUENCEs的INCREMENT BY选项，并使用setval（…）设置起点。

posted @ 2024-07-27 00:29 wongchaofan 阅读(65) 评论(0) 编辑收藏举报

刷新页面返回顶部

wonchaofan

Pglogical 复制概述

1.要求

2. 使用方法

2.1 快速设置

2.2 节点管理

2.3 订阅管理

2.4 复制集

2.4.1 新表的复制集自动分配

2.5 附加功能

2.6 行过滤

根据提供商进行行过滤

订阅服务器上的行过滤

3. 冲突

4. 限制和约束

4.1 需要超级用户

`4.2 UNLOGGED` 并且 `TEMPORARY` 没有被复制

4.3 一次一个数据库

4.4 需要 PRIMARY KEY 或 REPLICA IDENTITY

4.5 只有一个唯一索引/约束/PK

4.6 DDL

4.7 无复制队列刷新

4.8 外键

4.9 截断

4.10 序列

公告

wonchaofan

Pglogical 复制概述

1.要求

2. 使用方法

2.1 快速设置

2.2 节点管理

2.3 订阅管理

2.4 复制集

2.4.1 新表的复制集自动分配

2.5 附加功能

2.6 行过滤

根据提供商进行行过滤

订阅服务器上的行过滤

3. 冲突

4. 限制和约束

4.1 需要超级用户

4.2 UNLOGGED 并且 TEMPORARY 没有被复制

4.3 一次一个数据库

4.4 需要 PRIMARY KEY 或 REPLICA IDENTITY

4.5 只有一个唯一索引/约束/PK

4.6 DDL

4.7 无复制队列刷新

4.8 外键

4.9 截断

4.10 序列

公告

`4.2 UNLOGGED` 并且 `TEMPORARY` 没有被复制