PG跨库同步与关联-包含CDC和外部表方案实践

原创

哥们要飞 2024-04-27 23:18:01 ©著作权

文章标签 PostgreSQL CDC 数据同步逻辑复制 文章分类 数据仓库大数据

©著作权归作者所有：来自51CTO博客作者哥们要飞的原创作品，请联系作者获取转载授权，否则将追究法律责任

〇、相关资料

一、相关知识

1.1 逻辑复制

1、逻辑复制概念

逻辑复制是 PostgreSQL 的一种复制方式，它允许用户选择性地复制表级的数据变化（如 INSERT、UPDATE 和 DELETE 操作），而不是复制整个数据库的物理变化。这种复制方式是基于发布（Publication）和订阅（Subscription）模型来实现的。

2、复制槽

复制槽（Replication Slot）是 PostgreSQL 用来跟踪 WAL（写前日志）记录的一种机制，以确保在逻辑复制过程中不会丢失任何数据。

当创建一个订阅时，通常也会在源数据库上自动创建一个复制槽。复制槽保证了源数据库不会删除任何对于订阅服务器来说还没有被复制的 WAL 记录。换句话说，复制槽会追踪订阅服务器已确认的最后一个 WAL 记录的位置，以便源服务器知道需要保存哪些WAL信息。

3、发布

发布（Publication）是指在源数据库（发布者）上定义的一组要复制的表。发布可以配置为复制表的所有更改，或者是特定类型的更改。

CREATE PUBLICATION my_pub FOR TABLE my_table;

4、订阅

订阅（Subscription）是指目标数据库（订阅者）上创建的连接，用于连接到源数据库，并订阅一个或多个发布。

CREATE SUBSCRIPTION my_sub

CONNECTION 'host=source_host port=5432 user=replication_user password=replication_pass dbname=source_db'

PUBLICATION my_pub;

5、WAL

WAL 是 PostgreSQL 用来保证数据库事务日志的一致性和持久性的一种机制。当开启逻辑复制时，所有的数据变更（如 INSERT、UPDATE、DELETE）首先被记录到 WAL 中。这些WAL记录可以被用来在数据库故障时恢复数据，或者被用于复制过程中，把数据变更从源数据库传输到目标数据库。

6、发布订阅执行过程

发布创建：

当您在源数据库上创建一个发布时，您指定了哪些表的更改要发布。这个过程不直接涉及WAL或复制槽的设置，它只是标记了数据变化需要被捕获和复制。

订阅创建：

当您在目标数据库上创建订阅时，PostgreSQL 会做以下事情：

在源数据库上自动创建一个与订阅关联的复制槽（如果在创建订阅时没有指定复制槽的话）。
开始监视指定发布中表的更改。
从WAL中提取这些更改，并通过复制协议发送给目标数据库。
目标数据库接收到这些更改，并将它们应用到本地数据库中相应的表上。

WAL的使用：

所有数据变更都首先记录在WAL中。逻辑复制过程使用逻辑解码功能来解释WAL中的数据变更，并生成可以被订阅者理解的消息格式。

复制槽的使用：

复制槽确保了源数据库中相关的WAL记录在目标数据库成功接收和应用这些变更之前不会被清除。这保证了在断开连接或其他故障情况下，一旦恢复连接，所有的更改都能被订阅者接收。

总之，发布和订阅过程中逻辑复制和复制槽的使用是自动进行的。PgSQL负责管理这些复杂的内部机制，而用户只需要关注创建发布和订阅的SQL命令。然而，如果需要对复制过程进行微调或排除故障，了解这些内部机制是很有帮助的。

1.2 扩展

二、命令操作

1.1 复制槽相关

1、新增

SELECT * FROM pg_create_logical_replication_slot('new_slot_name ', 'pgoutput');

new_slot_name 是新复制槽的名称，output_plugin 是输出插件的名称（如 pgoutput）。

2、删除

SELECT pg_drop_replication_slot('slot_name');

3、查询

select * from pg_replication_slots;

4、位置确认

SELECT pg_replication_slot_advance('my_slot_name', '0/14B3F68');

将指定的复制槽向前推进到指定的 WAL 位置（LSN，Log Sequence Number）。这个操作可以用于管理复制时跳过某些 WAL 记录。然而，请注意，手动推进复制槽是高风险操作，因为这可能会导致丢失尚未复制的 WAL 记录，从而导致主从数据库之间的数据不一致。

LSN 值通常是一个十六进制的数字，表示 WAL 日志中的一个确切位置。

5、查看复制槽占用wal的大小

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)

FROM pg_replication_slots

WHERE slot_name = 'slot_name';

1.2 扩展和外部表相关

/*
    官网：http://postgres.cn/docs/13/postgres-fdw.html
    postgres_fdw模块提供了外部数据包装器postgres_fdw，
    它可以被用来访问存储在外部PostgreSQL服务器中的数据。

    这个模块提供的功能大体上覆盖了较老的dblink模块的功能。
    但是postgres_fdw提供了更透明且更兼容标准的语法来访问远程表，并且可以在很多情况下给出更好的性能。
*/
-- 1. 安装postgres_fdw扩展
create extension if not exists postgres_fdw;

-- 2. 创建一个外部服务器对象，它用来表示你想连接的每一个远程数据库，创建一个外部服务器对象，它用来表示你想连接的每一个远程数据库
create server if not exists integration_ods
    foreign data wrapper postgres_fdw
    options (host 'ip', dbname 'mapping_db');

-- 3. 创建一个用户映射，每一个用户映射都代表你想允许一个数据库用户访问一个**外部服务器**。指定远程用户名和口令作为用户映射的user和password选项。
create user mapping if not exists for bigdata
    server integration_ods
    options (user 'name', password 'code@2022');

-- 4. 创建schema
drop schema if exists ods_di cascade ;
create schema ods_di;
-- 5. 为每一个你想访问的远程表使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA创建一个外部表。
/**
  CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text )
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');
 */
import foreign schema ods_di from server integration_ods into ods_di;

三、相关实战

3.1 Debezium Engine同步NoSQL

1、demo-Oracle

package com.xxx.debezium;

import io.debezium.engine.ChangeEvent;
import io.debezium.engine.DebeziumEngine;
import io.debezium.engine.format.Json;

import java.io.IOException;
import java.util.Properties;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class DebeziumOracleCDC {
    public static void main(String[] args) {

        final Properties props = new Properties();
        // Debezium Engine基本配置
        props.setProperty("name", "engine");
        props.setProperty("connector.class", "io.debezium.connector.oracle.OracleConnector");
        props.setProperty("offset.storage", "org.apache.kafka.connect.storage.FileOffsetBackingStore");
        props.setProperty("topic.prefix", "pg-write-neo4j");
        props.setProperty("database.history", "io.debezium.relational.history.FileDatabaseHistory");
        props.setProperty("offset.flush.interval.ms", "60000");


        props.setProperty("offset.storage.file.filename", "/Users/sss/debezium/oracle4.txt");
        props.setProperty("database.history.file.filename", "/Users/sss/debezium/history.dat");
        props.setProperty("schema.history.internal.file.filename", "/Users/yinhaixiang/debezium/oracle4.txt");


        // Oracle连接器配置
        props.setProperty("database.hostname", "ip");
        props.setProperty("database.port", "1521");
        props.setProperty("database.user", "sys as sysdba");
        props.setProperty("database.password", "dwsaw231");
        props.setProperty("database.dbname", "ORCL");
        props.setProperty("database.server.id", "10001");

        props.setProperty("topic.prefix", "my-app-connector");
        props.setProperty("schema.history.internal", "io.debezium.storage.file.history.FileSchemaHistory");
        // props.setProperty("database.schema", "aaa");
        props.setProperty("table.include.list", "aaa.COMPANY");


        // Create the engine with this configuration ...
        try (DebeziumEngine<ChangeEvent<String, String>> engine = DebeziumEngine.create(Json.class)
                .using(props).notifying(record -> {
                    // 在这里处理每个捕获的数据变更事件
                    System.out.println(record);
                }).build()) {
            // Run the engine asynchronously ...
            ExecutorService executor = Executors.newSingleThreadExecutor();
            executor.execute(engine);

            // Do something else or wait for a signal or an event
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        // Engine is stopped when the main code is finished

    }
}