快速、简便地比较 PostgreSQL 数据

检查归档或处理 PostgreSQL 复制时，可能需要核对数据是否一致。比较行数是一种常见办法，却无法发现字段内容不一致。将整张表通过网络传输后逐行逐字段比较，又可能耗费大量资源。

本文介绍一种可以加入 PostgreSQL 工具箱的简单方案：通过外部数据包装器（Foreign Data Wrapper，FDW）连接两份数据，再配合 SQL 快速比较。

创建环境

为了让资源有限的读者也能实践，原作者在单个 PostgreSQL 集群内创建两个数据库：生产库 hrprod 和报表库 hrreport，通过 PostgreSQL FDW 连接。实际使用中，源数据库与目标数据库不必位于同一集群。

原作者借助 Crunchy Postgres for Kubernetes 和 Postgres Operator Examples 快速部署了一个简单集群。下面只展示在数据库容器中通过 psql 执行的步骤。

配置生产库 hrprod

创建数据库，安装 postgres_fdw 扩展，创建 employee 表并插入三行数据：

postgres=> create database hrprod;
CREATE DATABASE

postgres=> \c hrprod
You are now connected to database "hrprod" as user "postgres".

hrprod=> create extension postgres_fdw;
CREATE EXTENSION
hrprod=> create table employee (id int, first_name varchar(50), last_name varchar(50), department varchar(20));
CREATE TABLE

hrprod=> insert into employee (id, first_name, last_name, department) values (1,'John','Smith','explorer'),(2,'George','Washington','government'),(3,'Thomas','Edison','inventor');
INSERT 0 3

配置报表库 hrreport

重复同样步骤：

postgres=> create database hrreport;
CREATE DATABASE

postgres=> \c hrreport
You are now connected to database "hrreport" as user "postgres".

hrreport=> create extension postgres_fdw;
CREATE EXTENSION

hrreport=> create table employee (id int, first_name varchar(50), last_name varchar(50), department varchar(20));
CREATE TABLE
hrreport=> insert into employee (id, first_name, last_name, department) values (1,'John','Smith','explorer'),(2,'George','Washington','government'),(3,'Thomas','Edison','inventor');
INSERT 0 3

至此，两边的 employee 表拥有相同数据。

比较数据

比较操作从报表库 hrreport 一侧进行。先创建名为 data_compare 的比较用表，保存三类信息：

source_name：标识数据来源，本例是 hrprod 或 hrreport。
id：保存被比较表的主键值。
hash_value：保存非主键字段的哈希值。

如果表使用复合主键，可以将各主键字段拼成一个字符串填入 id。哈希计算在数据源一侧完成，跨网络比较时只传输哈希值，因此能大幅减少传输量和时间。

准备比较表

在生产库和目标库中都创建 data_compare：

hrreport=> \c hrprod
You are now connected to database "hrprod" as user "postgres".

hrprod=> CREATE TABLE data_compare
        (source_name VARCHAR(140),
        id VARCHAR(1000),
        hash_value varchar(100)
        );
CREATE TABLE

hrprod=> \c hrreport
You are now connected to database "hrreport" as user "postgres".
hrreport=> CREATE TABLE data_compare
        (source_name VARCHAR(140),
        id VARCHAR(1000),
        hash_value varchar(100)
        );
CREATE TABLE

随后在两边分别执行 INSERT，填充比较表，再对比其内容。多轮比较时，可以通过外部表、pg_dump 等方式传输比较表内容，减少重复比较的时间和传输量。

原作者用下面的步骤创建外部表：

hrreport=> CREATE SERVER hrprod FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'hrprod', port '5432');
CREATE SERVER

hrreport=> CREATE USER MAPPING FOR current_user SERVER hrprod options (user 'postgres', password 'welcome1');
CREATE USER MAPPING
CREATE FOREIGN TABLE hrprod_data_compare (source_name varchar(140), id varchar(1000), hash_value varchar(100)) SERVER hrprod OPTIONS (table_name 'data_compare');

执行第一次比较

在源库和目标库中填充 data_compare：

hrprod=> INSERT INTO data_compare (source_name, id, hash_value)
  (SELECT 'hrprod' source_name, id::text, md5(concat_ws('|',first_name, last_name, department)) hash_value FROM employee e);
INSERT 0 3


hrreport=> INSERT INTO data_compare (source_name, id, hash_value)
  (SELECT 'hrreport' source_name, id::text, md5(concat_ws('|',first_name, last_name, department)) hash_value FROM employee e);
INSERT 0 3

此时已知两边数据完全相同。实际比较使用以下 SQL，原文输出也一并保留：

hrreport=> SELECT COALESCE(s.id,t.id) id,
              s.hash_value source_hash_value, t.hash_value target_hash_value,
              CASE WHEN s.hash_value = t.hash_value THEN 'equal'
                    WHEN s.id IS NULL THEN 'row not on source'
                    WHEN t.id IS NULL THEN 'row not on target'
                    ELSE 'difference'
              END compare_result
        FROM hrprod_data_compare s
            FULL JOIN data_compare t ON s.id=t.id;


 id |        source_hash_value         |        target_hash_value         |  compare_result
----+----------------------------------+----------------------------------+-------------------
 1  | 681c37a127083d90164a9f04b5f92759 | 681c37a127083d90164a9f04b5f92759 | equal
 2  | 6e181f686815319daa07c5e0e1ddcd27 | 6e181f686815319daa07c5e0e1ddcd27 | equal
 3  | 4d4eba0d792cb227d247a3b0f9f66979 | 4d4eba0d792cb227d247a3b0f9f66979 | equal
(3 rows)

compare_result 显示三行数据均一致。文末还提供另一种比较 SQL，用于将两份比较数据合并后进行不同方式的比较。

制造不一致并再次比较

当前表有三行相同数据：

hrprod=> SELECT * FROM employee;
 id | first_name | last_name  | department
----+------------+------------+------------
  1 | John       | Smith      | explorer
  2 | George     | Washington | government
  3 | Thomas     | Edison     | inventor
(3 rows)

接下来制造差异：

在 hrprod 中加入 id 为 4 的 CS Lewis、id 为 5 的 Charles Babbage，以及 id 为 6 的 Blaise Pascal。
在 hrreport 中加入 id 为 4 的 Charles Babbage、id 为 5 的 CS Lewis，以及 id 为 7 的 Kenny Rogers。

CS Lewis 和 Charles Babbage 的 id 被对调；两边还分别增加一条独有记录。因此预期会有三行一致、两行字段内容不同，以及两行只存在于其中一个库。

先修改源库：

hrprod=> INSERT INTO employee (id, first_name, last_name, department)
        VALUES (4,'CS','Lewis','author'),(5,'Charles','Babbage','math'),(6,'Blaise','Pascal','math');

hrprod=> SELECT * FROM employee ORDER BY id;
 id | first_name | last_name  | department
----+------------+------------+------------
  1 | John       | Smith      | explorer
  2 | George     | Washington | government
  3 | Thomas     | Edison     | inventor
  4 | CS         | Lewis      | author
  5 | Charles    | Babbage    | math
  6 | Blaise     | Pascal     | math
(6 rows)

再修改目标库：

hrreport=> INSERT INTO employee (id, first_name, last_name, department)
        VALUES (5,'CS','Lewis','author'),(4,'Charles','Babbage','math'),(7,'Kenny','Rogers','music');

hrreport=> SELECT * FROM employee ORDER BY id;
 id | first_name | last_name  | department
----+------------+------------+------------
  1 | John       | Smith      | explorer
  2 | George     | Washington | government
  3 | Thomas     | Edison     | inventor
  4 | Charles    | Babbage    | math
  5 | CS         | Lewis      | author
  7 | Kenny      | Rogers     | music
(6 rows)

现在的状态为：

id 1、2、3：两边一致。
id 4、5：两边都存在，但内容不同。
id 6、7：各自只存在于其中一边。

清空比较表并重新填充：

postgres=> \c hrprod
You are now connected to database "hrprod" as user "postgres".

hrprod=> DELETE FROM data_compare;
DELETE 3

hrprod=> INSERT INTO data_compare (source_name, id, hash_value)
  (SELECT 'hrprod' source_name, id::text id, md5(textin(record_out(e))) FROM employee e);
INSERT 0 6

hrprod=> \c hrreport
You are now connected to database "hrreport" as user "postgres".

hrreport=> DELETE FROM data_compare;
DELETE 3

hrreport=> INSERT INTO data_compare (source_name, id, hash_value)
  (SELECT 'hrreport' source_name, id::text id, md5(textin(record_out(e))) FROM employee e);
INSERT 0 6

然后再次比较：

hrreport=> SELECT COALESCE(s.id,t.id) id,
              s.hash_value source_hash_value, t.hash_value target_hash_value,
              CASE WHEN s.hash_value = t.hash_value THEN 'equal'
                    WHEN s.id IS NULL THEN 'row not on source'
                    WHEN t.id IS NULL THEN 'row not on target'
                    ELSE 'difference'
              END compare_result
        FROM hrprod_data_compare s
            FULL JOIN data_compare t ON s.id=t.id;


 id |        source_hash_value         |        target_hash_value         |  compare_result
----+----------------------------------+----------------------------------+-------------------
 1  | 681c37a127083d90164a9f04b5f92759 | 681c37a127083d90164a9f04b5f92759 | equal
 2  | 6e181f686815319daa07c5e0e1ddcd27 | 6e181f686815319daa07c5e0e1ddcd27 | equal
 3  | 4d4eba0d792cb227d247a3b0f9f66979 | 4d4eba0d792cb227d247a3b0f9f66979 | equal
 4  | bbee9d6cccbeac4e9125ec78507c4eb7 | 57acef6ed228a52b8c42f0a6c155e62b | difference
 5  | 57acef6ed228a52b8c42f0a6c155e62b | bbee9d6cccbeac4e9125ec78507c4eb7 | difference
 6  | 047742fb256df0b78cebc3fbbc3ca4ad |                                  | row not on target
 7  |                                  | 66e5e35673780bd392d2f81d589fbb52 | row not on source
 (7 rows)

结果表明 id 1 至 3 在两边均存在，且内容一致。id 4 和 5 的内容不同；进一步观察可发现，两个人物对应的哈希值相同，但分别关联到了对方的 id。

id 6 只存在于源库 hrprod，id 7 只存在于目标库 hrreport，合计四行不同步。

识别这些记录后，就可以采取合适的步骤进行同步。原作者还提出一种场景：如果两库使用逻辑复制，而目标库因复制延迟尚未追上源库，可以在延迟消除后，只对标记为不同步的行重新执行 INSERT 和比较，以复核这些记录。

结语

大规模数据比较可能十分困难。这种技巧在无法使用昂贵的数据比较软件时，曾多次帮助原作者完成工作。

比较 SQL 仍有调整空间，可以按实际需求改写。例如，只输出某一边缺失的行：

SELECT id, hash_value,
       count(src1) src1,
       count(src2) src2
 FROM
     ( SELECT a.*,
              1 src1,
              null src2
        FROM data_compare a
        WHERE source_name='hrprod'
        UNION ALL
        SELECT b.*,
               null src1,
               2 src2
        FROM data_compare b
        WHERE source_name='hrreport'
    ) c
 GROUP BY id, hash_value
 HAVING count(src1) <> count(src2);

通过 postgres_fdw 连接数据，计算字段哈希，再写 SQL 查找差异，就能完成一次快速、简单的 PostgreSQL 数据比较。原作者也邀请读者向 @crunchydata 分享其他喜欢的数据比较方案。

原文：Quick and Easy Postgres Data Compare，作者 Brian Pace，发表于 2022-06-15。中文翻译。© Crunchy Data Solutions, Inc.；原始 SQL、示例数据和输出归原作者所有。

文章版权归作者所有，未经允许请勿转载。

THE END