PostgreSQLで作業前後のスキーマとデータをソートして差分を取る方法

PostgreSQLを利用したシステム運用に際して、作業前後のスキーマ構造やデータの差分を確認したいことがあります。
いつかの方法とその注意点、自作したツールを紹介します。

\copyでダンプ

psqlインタプリタ内で\copyメタコマンドを使って対象テーブルを指定ファイルに出力できます。
\copyコマンドは単一テーブルを指定するか、任意のSELECTクエリを指定することができますが、
単一テーブル指定の場合は出力はソートされません。
差分確認が目的であれば出力をソートしておくと便利ですので、SELECTクエリを利用しORDER BY句で順序を指定しておきます。

psql内でのコマンド例

-- 指定テーブルをダンプ (ソートされない)
\copy テーブル名 to '出力ファイルパス'

-- SELECTクエリをダンプ (ソート順を指定可能)
\copy (SELECT ... FROM テーブル名 ORDER BY ...) to '出力ファイルパス'

対象テーブルがたくさんある場合は、Stack Overflowの記事で紹介されている方法 を応用すると一括で\copyコマンドを生成できます。

\copyコマンド一括生成SQL

select
    '\copy (select * from '||r.relname||' order by '||
    array_to_string(array_agg(a.attname), ',')||
    ') to '''||r.relname||'.dmp'';'
from
    pg_class r,
    pg_constraint c,
    pg_attribute a
where
    r.oid = c.conrelid
    and r.oid = a.attrelid
    and a.attnum = ANY(conkey)
    and contype = 'p'
    and relkind = 'r'
group by
    r.relname
order by
    r.relname
;

なお、\copyコマンドはデフォルトでタブ区切りテキストを出力します。
formatオプションでcsv(カンマ区切り)にすることもできますが、データ中の改行がエスケープされないため差分比較に不適なことが多いでしょう。
(詳細は psql ドキュメント参照)

COPYでダンプ

対象テーブルが巨大な場合などは、PostgreSQLの拡張SQLコマンド COPY の利用を検討しても良いでしょう。
サーバ側でファイル生成して加工・圧縮してクライアントに持ってくる、などの応用ができます。

SQL例

-- サーバ側に出力する
COPY (SELECT ... FROM テーブル名 ORDER BY ...) TO 'サーバ環境での出力ファイルパス';

注意として、ファイルを書き出すのはサーバ側のpostgresデーモンです。
そのためファイルはサーバ側に生成され、その際にpostgresデーモン実行ユーザ(通常はpostgres)がアクセスできるファイルパスである必要があります。
またファイル出力する場合では、クエリを実行するDBユーザが(通常は)スーパーユーザ(postgres)である必要があります。
(詳細は COPY ドキュメント参照)

pg_dump でダンプ ＋ ソートフィルタ pg_dump_sort

PostgreSQLに付属のpg_dump, pg_dumpallクライアントプログラムを利用すると、手軽にスキーマ構造とデータの両方の情報をダンプできます。
ただし、ダンプされたテーブルの行データはソートされません。
そこで、pg_dump (pg_dumpall) のダンプ出力を解析し、全テーブルのデータ部分をそのテーブルの主キーでソートするフィルタスクリプト pg_dump_sort を書きました。
主キーが設定されていない場合は、代わりにユニークキーで、それも無ければ行を文字列的に評価してソートします。

htaketani/pg_dump_sort: Sort table rows data by primary key in pg_dump output sql.

pg_dumpをソートする例

# ダンプする (`--inserts`オプションを使わない)
pg_dump somedb > dump.sql

# ダンプを解析し、各テーブルデータを主キーでソートする
pg_dump_sort dump.sql > dump-sorted.sql

標準ライブラリのみを利用したperlスクリプトなので、どの環境でも動くと思います。
以下はインストール例です。環境に合わせて適宜chownしたりmove先を変更してください。

pg_dump_sortインストール例

curl -LO https://github.com/htaketani/pg_dump_sort/raw/main/pg_dump_sort && \
chmod +x pg_dump_sort && \
sudo mv pg_dump_sort /usr/local/bin/

タブ区切りテキストの差分を見る

ソート済みのダンプデータを作成できたら、あとはお好みのdiffコマンドなどで差分確認します。
git diffはレポジトリ外のファイルの差分も取れ、--color-wordsオプションを使うと見やすくなるので重宝しています。

差分確認例

# アルファベットの塊、数字の塊をワードとし、それ以外(日本語など）は1文字単位で色付きdiff
git diff --no-index --color-words=$'[A-Za-z]+|[0-9]+|[^\x80-\xbf][\x80-\xbf]*' before-sorted.dmp after-sorted.dmp

よく使うのでエイリアスにしてます。

~/.gitconfigエイリアス設定

[alias]
    d = diff --relative --no-prefix --color-words='[A-Za-z]+|[0-9]+|[^€-¿][€-¿]*'

参考

• 日本PostgreSQLユーザ会: PostgreSQL 12.4 付属ドキュメント
• php - Sorting postgresql database dump (pg_dump) - Stack Overflow
• tigra564/pgdump-sort: Sort entries of pg_dump output for the purpose of diffing database structure and contents
• Git Diff で日本語の文章も綺麗に差分を出す - Neo's World
• 厳密ではないものの、ほぼUTF-8の1文字にマッチするお手軽正規表現 [^\x80-\xbf][\x80-\xbf]* なるほどー - tmatsuu のブックマーク / はてなブックマーク