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

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

## \copyでダンプ

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

```sql:psql内でのコマンド例
-- 指定テーブルをダンプ (ソートされない)
\copy テーブル名 to '出力ファイルパス'

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

対象テーブルがたくさんある場合は、[Stack Overflowの記事で紹介されている方法](https://stackoverflow.com/questions/2204640/sorting-postgresql-database-dump-pg-dump) を応用すると一括で\copyコマンドを生成できます。

```sql:\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](https://www.postgresql.jp/document/12/html/app-psql.html) ドキュメント参照)


## COPYでダンプ

対象テーブルが巨大な場合などは、PostgreSQLの拡張SQLコマンド [COPY](https://www.postgresql.jp/document/12/html/sql-copy.html) の利用を検討しても良いでしょう。
サーバ側でファイル生成して加工・圧縮してクライアントに持ってくる、などの応用ができます。

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

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

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

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

[htaketani/pg\_dump\_sort: Sort table rows data by primary key in pg\_dump output sql\.](https://github.com/htaketani/pg_dump_sort)

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

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

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

```sh: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`オプションを使うと見やすくなるので重宝しています。

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

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

```conf:~/.gitconfigエイリアス設定
[alias]
    d = diff --relative --no-prefix --color-words='[A-Za-z]+|[0-9]+|[^-¿][-¿]*'
```

## 参考

* [日本PostgreSQLユーザ会: PostgreSQL 12\.4 付属ドキュメント](https://www.postgresql.jp/document/current/index.html)
* [php \- Sorting postgresql database dump \(pg\_dump\) \- Stack Overflow](https://stackoverflow.com/questions/2204640/sorting-postgresql-database-dump-pg-dump)
* [tigra564/pgdump\-sort: Sort entries of pg\_dump output for the purpose of diffing database structure and contents](https://github.com/tigra564/pgdump-sort)
* [Git Diff で日本語の文章も綺麗に差分を出す \- Neo's World](https://neos21.net/blog/2018/05/28-01.html)
* [厳密ではないものの、ほぼUTF\-8の1文字にマッチするお手軽正規表現 \[^\\x80\-\\xbf\]\[\\x80\-\\xbf\]\* なるほどー \- tmatsuu のブックマーク / はてなブックマーク](https://b.hatena.ne.jp/entry/363690522/comment/tmatsuu)