PostgreSQL の md5 でデータの同一性の確認

やりたいこと

PostgreSQL でテーブルに登録されているデータが、ある時点と別の時点とで同じかどうかを簡易に確認できるようにする。

実現方法

• pg_dump で全テーブルの全レコードをダンプした結果の md5 で比較
• テーブル毎に全レコードの md5 で比較

pg_dump の出力結果の md5 で比較

以下では、データベースに test_md5 、テーブルに test_item1、test_item2 を指定する例を示す。

pg_dump によるダンプ

特定のテーブルを指定

$ pg_dump -d test_md5 -t test_item1

--
-- PostgreSQL database dump
--
(中略)
CREATE TABLE public.test_item1 (
    id integer NOT NULL,
    name character varying(64)
);
(中略)
COPY public.test_item1 (id, name) FROM stdin;
1       item1
2       item2
3       item3
\.

複数のテーブルを指定

$ pg_dump -d test_md5 -t test_item1 -t test_item2

*を使用してテーブルを指定

$ pg_dump -d test_md5 -t 'test_*'

pg_dump の出力結果の md5 を作成

$ pg_dump -d test_md5 | md5sum

3b7e72d7c89545d0d4b82f857020cf50  -

テーブル毎に全レコードの md5 で比較

string_agg() によるデータの取得

string_agg() でテーブルの全レコードを取得することができる。

string_agg()

> select string_agg(t::text, '' order by id asc) as agg from test_item1 as t;

             agg
-----------------------------
 (1,item1)(2,item2)(3,item3)
(1 row)

string_agg() の結果から md5 を作成

md5()

> select md5(string_agg(t::text, '' order by id)) as hash from test_item1 as t;
               hash
----------------------------------
 820cbf49a348d75cf264b7bf0448b6ec
(1 row)

各テーブルの md5 を作成するスクリプト

psql_md5.sh

#!/bin/bash
TABLES=("test_item1" "test_item2")

DB_HOST='{host}'
DB_PORT='{port(5432)}'
DB_DATABASE='test_md5'
DB_USER='{user}'
DB_PASSWORD='{password}'

for TABLE in "${TABLES[@]}"; do
    SQL_QUERY="
select
    '${TABLE}' as name,
    md5(string_agg(t::text, '' order by id)) as hash
from
    ${TABLE} as t
;"

    PGPASSWORD=${DB_PASSWORD} \
              psql \
              -h ${DB_HOST} \
              -U ${DB_USER} \
              -d ${DB_DATABASE} \
              -q \
              -A \
              -F $'\t' \
              -t \
              -c '\pset tuples_only on' \
              -c "${SQL_QUERY}"
done

実行するとテーブル毎に md5 が出力される。

実行結果

$ ./psql_md5.sh

test_item1      820cbf49a348d75cf264b7bf0448b6ec
test_item2      7017fc8b8a80453f3434b234adea83e0

上記の結果から md5sum コマンドで md5 を作成

$ ./psql_md5.sh | md5sum

6458369b73b98580ee0782e68d02b0cb  -