为Airbyte配置Postgres源

目录

Postgres复制方法

Xmin

创建专用只读 Postgres 用户

在 Airbyte UI 中创建新的 Postgres 源

CDC

为只读用户添加REPLICATION权限

在 Postgres 数据库上启用逻辑复制

在 Postgres 数据库上创建复制槽

为每个 Postgres 表创建发布和复制标识

创建复制标识

 创建发布

在 Airbyte UI 中启用 CDC 复制

游标

为表中添加update_at字段和触发器

在Airbyte UI中配置Postgres源

---

Postgres复制方法

Postgres 源目前提供 3 种将更新复制到目标的方法：CDC、xmin 和标准（使用用户定义的游标）。推荐使用CDC 和 xmin 这两个方法。

Xmin

Xmin 复制是 Postgres 的新无游标复制方法。无游标同步允许同步新行或更新的行，而无需显式选择游标字段。xmin 系统列（在所有 Postgres 数据库中都可用）用于跟踪源数据的插入和更新。不能跟踪数据的删除！要支持删除数据建议使用CDC的方式。

在以下情况下，这是一个很好的解决方案：

• 没有明确定义的游标候选项可用于标准增量模式。
• 您想要替换以前配置的完全刷新同步。
• 您正在复制小于 500GB 的 Postgres 表。
• 您不是在复制非实例化视图。xmin 复制不支持非具体化视图。

创建专用只读 Postgres 用户

创建一个专用的只读用户来复制数据。也可以使用数据库中的现有 Postgres 用户。

以下命令将创建一个新用户：

CREATE USER <user_name> PASSWORD 'your_password_here';

现在，为该用户提供对相关架构和表的只读访问权限。对每一个你希望去复制数据的schema（postgres数据库默认schema为public）运行以下命令：

GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <user_name>;

在 Airbyte UI 中创建新的 Postgres 源

在Airbyte UI中，从左侧导航栏中选择Source，搜索Postgres ，然后创建新的 Postgres 源。进入到配置界面，填写好pg数据库主机名、端口、数据库名等信息，勾选更新方法为使用xmin方法。

​

配置好后点击set up source查看是否可以连接到postgres数据库。

CDC

Airbyte 使用 Postgres 预写日志 write-ahead log （WAL） 的逻辑复制，通过复制插件以增量方式捕获删除。

在以下情况下，建议使用 CDC 配置 Postgres 源：

• 您需要删除记录。
• 您有一个非常大的数据库（500 GB 或更多）。
• 您的表有一个主键，但没有用于增量同步的合理游标字段 （updated_at）。

如果您的目的是在目标中维护表的快照，但限制阻止您使用 CDC，请考虑使用 xmin 复制方法。

创建只读用户并添加REPLICATION权限

若要为 Postgres 源连接器配置 CDC，需创建一个只读用户并授予REPLICATION权限：

CREATE USER <user_name> PASSWORD 'your_password_here';
GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <user_name>;


ALTER USER <user_name> REPLICATION;

在 Postgres 数据库上启用逻辑复制

要在裸机、虚拟机 （EC2/GCE/etc） 或 Docker 上启用逻辑复制，请在 postgresql.conf文件中为postgres数据库配置以下参数：

参数	描述	将值设置为
wal_level	Postgres 预写日志中使用的编码类型	logical
max_wal_senders	用于处理 WAL 更改的最大进程数	1
max_replication_slots	允许流式传输 WAL 更改的最大复制槽数	1（如果 Airbyte 是唯一读取WAL 更改的服务，则为1。如果其他服务也从 WAL 读取，则大于 1）

注意：以上配置需要postgres服务重新加载配置后才能生效。

在 Postgres 数据库上创建复制槽

Airbyte 需要配置一个仅被其使用的复制槽。

对于此步骤，Airbyte 需要使用 pgoutput 插件。要使用pgoutput创建名为airbyte_slot的复制槽，请以上面新授予REPLICATION角色的用户身份运行：

SELECT pg_create_logical_replication_slot('airbyte_slot', 'pgoutput');

为每个 Postgres 表创建发布和复制标识

创建复制标识

对于要使用 CDC 复制的每个表，请执行以下步骤：

为要复制的每个表添加复制标识：

ALTER TABLE tbl1 REPLICA IDENTITY DEFAULT;

注意：如果表使用支持 TOAST 的数据类型或具有非常大的字段值，请考虑改用副本标识类型FULL: 

ALTER TABLE tbl1 REPLICA IDENTITY FULL;.

 创建发布

创建 Postgres 名为airbyte_publication的发布 。应将要复制的所有表作为发布的一部分包括在内：

CREATE PUBLICATION airbyte_publication FOR TABLE <tbl1, tbl2, tbl3>;

 发布名称是可自定义的。如果将来需要在发布中添加或删除表，请参阅 Postgres 文档。

注意：Airbyte UI 目前允许为 CDC 选择任何表。如果选择的表不属于发布，则即使选择了该表，也不会复制该表。如果表是发布的一部分，但没有复制标识，则如果 Airbyte 用户具有必要的权限，则该复制标识将在首次运行时自动创建。

如果想要为整个库的表创建复制标识和发布可以使用下面SQL：

CREATE PUBLICATION airbyte_publication FOR ALL TABLES;

在 Airbyte UI 中启用 CDC 复制

在Airbyte UI 中将 Postgres 的复制模式更改为CDC，然后输入刚刚创建的复制槽和发布（airbyte_slot，airbyte_publication）。

配置好后点击set up source查看是否可以连接到postgres数据库。

游标

使用游标的方式进行数据同步，需要您的表包含一个updated_at字段（数据类型为timestamp）。该字段将作为一个游标字段。增量同步需要游标才能跟踪以前已同步的记录，以便它们不会重新发送到目标。

为了使游标正常工作，每次写入或修改记录时都必须更新此表中的updated_at字段。使用 Postgres 时，这可以通过Postgres触发器自动完成，每次更行数据时，触发器都会将updated_at字段的值更新。

为表中添加update_at字段和触发器

例：

现拥有一个名为table_test的表

CREATE TABLE table_test(

id integer PRIMARY KEY,

name VARCHAR(200),

updated_at timestamp DEFAULT NOW() NOT NULL

);

给该表创建一个名为trigger_set_timestamp的触发器

CREATE OR REPLACE FUNCTION trigger_set_timestamp()

RETURNS TRIGGER AS '

BEGIN

NEW.updated_at = NOW();

RETURN NEW;

END;

'

LANGUAGE plpgsql;

触发器应在每次更新table_one表时执行，这是通过执行以下代码完成的:

CREATE TRIGGER set_timestamp_on_table_one

BEFORE UPDATE ON table_test
FOR EACH ROW

EXECUTE PROCEDURE trigger_set_timestamp();

在Airbyte UI中配置Postgres源

在Airbyte UI左侧导航栏中，点击Sources，搜索postgres，进入到配置界面，填写好pg数据库主机名、端口、数据库名等信息，勾选更新方法为使用游标的方法。

配置好后点击set up source查看是否可以连接到postgres数据库。

---------------------------------（创作不易，点个免费的赞再走）--------------------------------