PostgreSQL计算全表 MD5 值

在很多时候我们需要计算两张表是否完全一致，尤其是类似发布订阅复制表的时候。这时候可以使用如下函数做全表的 hash 计算。

单表 HASH：

-- 创建函数
CREATE OR REPLACE FUNCTION table_to_hash(table_name text)
RETURNS text AS $$
DECLARE
    output_hash_result text;
    row_data text[];
BEGIN
    -- 查询表的数据到变量
    EXECUTE format('SELECT array_agg(row_to_json(t)::text) FROM %I AS t', table_name) INTO row_data;

    -- 将数组中的每一行连接为一个字符串
    output_hash_result := md5(array_to_string(row_data, E'\n'));

    RETURN output_hash_result;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
select table_to_hash('test');

全库所有表 HASH:

-- 创建函数
CREATE OR REPLACE FUNCTION get_tables_hash()
    RETURNS VOID
    AS $$
DECLARE
    table_schema text;
    table_name text;
    table_data text[];
    table_count bigint;
    table_hash text;
BEGIN
    FOR table_schema,
    table_name IN
    SELECT
        t.table_schema,
        t.table_name
    FROM
        information_schema.tables t
    WHERE
        t.table_schema NOT IN ('pg_catalog', 'information_schema')
    ORDER BY
        1,
        2 LOOP
            EXECUTE format('SELECT COUNT(*) FROM %I.%I', table_schema, table_name) INTO table_count;
            EXECUTE format('SELECT array_agg(row_to_json(t)::text) FROM %I.%I AS t', table_schema, table_name) INTO table_data;
            table_hash := md5(array_to_string(table_data, E'\n'));
            RAISE INFO '% COUNT: % MD5: %', rpad(substr(table_schema || '.' || table_name, 1, 50), 50), rpad(table_count::text, 10), table_hash;
        END LOOP;
END;
$$
LANGUAGE plpgsql;

-- 调用函数
SELECT get_tables_hash();