PostgreSQL 11逻辑副本：检测发布者与订阅者表结构差异

一、核心思路 #

通过跨库对比发布方与订阅方的表元数据（表名、列定义、可空性等关键属性），捕获结构不一致项并触发错误，Jenkins通过捕获命令执行状态判断任务是否失败。需确保订阅方数据库能通过dblink访问发布方数据库。

二、前置准备：安装dblink扩展（订阅方执行） #

CREATE EXTENSION IF NOT EXISTS dblink;

三、创建结构一致性检查函数 #

编写PL/pgSQL函数，连接发布方数据库，对比指定发布集下的表结构：

CREATE OR REPLACE FUNCTION check_subscription_schema_consistency(
    p_publisher_dsn text, -- 发布方数据库连接串，示例：'dbname=pubdb host=pubhost user=pubuser password=pubpass'
    p_publication_name text -- 目标发布名称
) RETURNS void AS $$
DECLARE
    v_pub_tables record;
    v_sub_columns record;
    v_pub_columns record;
    v_inconsistencies text[] := '{}'::text[];
BEGIN
    -- 遍历发布方指定发布下的所有表
    FOR v_pub_tables IN
        SELECT schemaname, tablename
        FROM dblink(p_publisher_dsn,
            'SELECT schemaname, tablename FROM pg_publication_tables WHERE pubname = ' || quote_literal(p_publication_name))
        AS t(schemaname text, tablename text)
    LOOP
        -- 检查订阅方是否存在对应表
        IF NOT EXISTS (
            SELECT 1 FROM pg_tables WHERE schemaname = v_pub_tables.schemaname AND tablename = v_pub_tables.tablename
        ) THEN
            v_inconsistencies := array_append(v_inconsistencies, 
                '表缺失：' || v_pub_tables.schemaname || '.' || v_pub_tables.tablename);
            CONTINUE;
        END IF;

        -- 逐列对比定义（列名、数据类型、可空性）
        FOR v_pub_columns IN
            SELECT column_name, data_type, is_nullable
            FROM dblink(p_publisher_dsn,
                'SELECT column_name, data_type, is_nullable FROM information_schema.columns ' ||
                'WHERE table_schema = ' || quote_literal(v_pub_tables.schemaname) || 
                ' AND table_name = ' || quote_literal(v_pub_tables.tablename))
            AS t(column_name text, data_type text, is_nullable text)
        LOOP
            SELECT column_name, data_type, is_nullable INTO v_sub_columns
            FROM information_schema.columns
            WHERE table_schema = v_pub_tables.schemaname
              AND table_name = v_pub_tables.tablename
              AND column_name = v_pub_columns.column_name;

            -- 列不存在
            IF NOT FOUND THEN
                v_inconsistencies := array_append(v_inconsistencies, 
                    '列缺失：' || v_pub_tables.schemaname || '.' || v_pub_tables.tablename || '.' || v_pub_columns.column_name);
            ELSE
                -- 数据类型不匹配
                IF v_sub_columns.data_type <> v_pub_columns.data_type THEN
                    v_inconsistencies := array_append(v_inconsistencies, 
                        '列类型不匹配：' || v_pub_tables.schemaname || '.' || v_pub_tables.tablename || '.' || v_pub_columns.column_name || 
                        ' 发布方:' || v_pub_columns.data_type || ' 订阅方:' || v_sub_columns.data_type);
                END IF;
                -- 可空性不匹配
                IF v_sub_columns.is_nullable <> v_pub_columns.is_nullable THEN
                    v_inconsistencies := array_append(v_inconsistencies, 
                        '列可空性不匹配：' || v_pub_tables.schemaname || '.' || v_pub_tables.tablename || '.' || v_pub_columns.column_name || 
                        ' 发布方:' || v_pub_columns.is_nullable || ' 订阅方:' || v_sub_columns.is_nullable);
                END IF;
            END IF;
        END LOOP;
    END LOOP;

    -- 存在不一致则抛出错误
    IF array_length(v_inconsistencies, 1) > 0 THEN
        RAISE NOTICE '结构不一致项：%', array_to_string(v_inconsistencies, E'\n');
        RAISE EXCEPTION '发布方与订阅方表结构不一致';
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

四、集成到Jenkins任务 #

在Jenkins中添加「执行shell」步骤，执行以下命令：

#!/bin/bash
# 连接订阅方数据库执行检查函数
psql -U sub_user -d sub_db -c "SELECT check_subscription_schema_consistency('$PUBLISHER_DSN', 'my_publication');"

# 捕获psql退出码，非0则标记任务失败
if [ $? -ne 0 ]; then
    echo "结构一致性检查失败"
    exit 1
fi

注：建议将发布方连接串PUBLISHER_DSN通过Jenkins环境变量传递，避免硬编码敏感信息。

五、扩展说明 #

• 可根据需求增加检查项，比如主键、唯一约束等，这些变更也可能导致逻辑复制故障
• 若发布方存在表重命名的规范流程，可在函数中维护表名映射表，实现重命名后的一致性检查

内容的提问来源于stack exchange，提问作者Gerzzog