[RDS]本番環境の同一テーブル間の時間経過によるデータの差分をテスト環境で確認する

はじめに

実際に起きた事情
本番RDSのとあるテーブルにおいて、理論上何も更新していないはずだから時間経過によって
差分が発生しないはずなのになぜか差分が発生しているくさい！調査したい！

でも、本番DBに対して調査のために色々と操作していると思わぬ事故につながる可能性があるし、
そもそも本番環境に対してもろもろ調査をすることで負荷をかけたくない。
なお、ググった結果テーブルの比較は同一DB内でしかできないらしく(異なるホスト間、異なるDB間の比較方法がヒットしなかった。いい方法知ってる人教えてください!)、
したがって一時的に調査用のテーブルを新しく作る必要があるので、
この点からも本番環境で作業するわけにはいかない。

上記の事情から同一テーブル間の時系列な差分の調査はテスト環境でごりごりやるしかない・・・

なお、Popular Beetle(←Windowsのみ対応かつ8で更新とまってるみたい。)みたいな異なるDB間でテーブルを比較できるツールが使えれば楽だけど・・・
いまは持ってないし調査は一刻を争う。

---

というわけで、以下の方法でゴリゴリ実際にやりましたという記事。

ざっくりとした全体的な手順とイメージ

手順

1. 本番RDSインスタンスのスナップショットからテスト環境RDSでインスタンスを復元する
2. テーブル比較対象のRDSインスタンスでDBのdumpを作成する
3. テーブル比較対象のRDSインスタンスのウチ、いずれかで各dumpからリストアする
4. テーブルを比較する

イメージ

※リストアの矢印がABCそれぞれに伸びてますがホントはCのみが正解のイメージです！

詳細手順

1. 本番RDSインスタンスのスナップショットからテスト環境RDSでインスタンスを復元する

以下の手順で比較対象のスナップショットを本番環境RDSからテスト環境RDSへ移動させ、復元する。
[AWS]RDSスナップショットの本番→テスト環境移行(別アカウント間)

比較したい時系列の分だけテスト環境RDSでインスタンスを復元。
結果、ここでは以下の3つのRDSインスタンスを作ったとする。

• テスト環境RDSインスタンスA(本番環境スナップショットAより)
• テスト環境RDSインスタンスB(本番環境スナップショットBより)
• テスト環境RDSインスタンスC(本番環境スナップショットCより)

2. テーブル比較対象のRDSインスタンスについてDBのdumpを作成する

テスト環境RDSインスタンスA,B,Cについて、pg_dumpでデータ比較対象のテーブル(群)のdumpファイルを作成する。

テスト環境RDSに接続するマシンのターミナルで以下のコマンドを実行。

pg_dump -h <テスト環境RDSインスタンスAのエンドポイント> -d <比較対象のテーブルが属するDB名> -U <接続するDBのマスターユーザー名> -Fc -t <比較対象テーブル1> -t <比較対象テーブル2>... -W > <比較対象テーブル1>_<比較対象テーブル2>_..._<RDSインスタンスAの元となったスナップショットの作成日時(yyyymmdd)>.dump
※パスワード入力するとdumpが実行される

→これをインスタンスB,Cについても同様に繰り返す。

※pg_dumpのバージョンが、アクセスするDBサーバで使っているPostgreSQLエンジンと異なる場合はエラーが発生するので、以下の方法でうまいこと対処

pg_dumpコマンドを複数バージョン入れる方法 - Qiita

結果、以下の3つのdumpファイルができたとする。

• table1_table2_20200229.dump
• table1_table2_20200301.dump
• table1_table2_20200302.dump

3. テーブル比較対象のRDSインスタンスのウチ、いずれかで各dumpからリストアする

テスト環境RDSインスタンスCでdumpから復元するとする。

まず、テスト環境RDSインスタンスのDBで、比較対象となるテーブル名をリネームして退避させておく(リストア時に同名のテーブルができるため)。

作業には2つのターミナルを使うものとする

• ターミナル1
• ターミナル2

ターミナル1で以下を実行

# psqlでDBへ接続
psql -h <テスト環境RDSインスタンスCのエンドポイント> -U <接続するDBのマスターユーザー名> -d <比較対象のテーブルが属するDB名> -W
※パスワード入力するとDBへ接続される

# psqlでそれぞれ実行
## オリジナルの比較対象のテーブルをリネームして退避
alter table <比較対象テーブル1> rename to <比較対象テーブル1>_origin;
alter table <比較対象テーブル2> rename to <比較対象テーブル2>_origin;
...

ターミナル2で以下を実行(★１)

# インスタンスAのdumpで比較対象テーブルをリストア
pg_restore -h <テスト環境RDSインスタンスCのエンドポイント> -c -d <比較対象のテーブルが属するDB名> -U <接続するDBのマスターユーザー名> -W < table1_table2_20200229.dump

※補足
以下のような外部制約とかのエラーがめちゃくちゃ途中で出るかもしれないが、データ差分の調査には影響しないので無視して待つ。

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2986; 2606 26633 FK CONSTRAINT <外部キー制約名> <DB名>
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.<テーブル名>" does not exist
    Command was: ALTER TABLE ONLY public.<テーブル名> DROP CONSTRAINT <外部キー制約名>;
→<テーブル名> の <外部キー制約名>を削除しようとしたけど、そもそも<テーブル名>が存在しない(リネームしてるから。)これらは先にリストア対象のテーブルをリネームしているゆえのエラーであり、pg_restoreのときに-cオプションをつけているため。


pg_restore: [archiver (db)] Error from TOC entry 231; 1259 26624 TABLE <テーブル名> <DB名>
pg_restore: [archiver (db)] could not execute query: ERROR:  table "<テーブル名>" does not exist
    Command was: DROP TABLE public.<テーブル名>;
→上と同じような感じ。

pg_restore: [archiver (db)] Error from TOC entry 2984; 2606 26629 CONSTRAINT <テーブル名>_pkey <DB名>
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "<テーブル名>_pkey" already exists
    Command was: ALTER TABLE ONLY <テーブル名>
    ADD CONSTRAINT <テーブル名>_pkey PRIMARY KEY (id);
→うーんちょっとよくわかんない・・・

ターミナル1で以下を実行(★2)

# psqlで引き続き実行
## リストアしたインスタンスAの比較対象テーブルをスナップショットの日付付きでリネーム
alter table <比較対象テーブル1> rename to <比較対象テーブル1>_<RDSインスタンスAの元となったスナップショットの作成日時(yyyymmdd)>
alter table <比較対象テーブル2> rename to <比較対象テーブル2>_<RDSインスタンスAの元となったスナップショットの作成日時(yyyymmdd)>
...

以降、インスタンスB、インスタンスCの比較対象テーブルのリストアも★1、★2を同様に繰り返して実行する。

結果、インスタンスCのRDS上に以下の比較調査用テーブルが出来上がったはず。

• table1_yyyymmdd(インスタンスAスナップショットの作成日時)
• table1_yyyymmdd(インスタンスBスナップショットの作成日時)
• table1_yyyymmdd(インスタンスCスナップショットの作成日時)
• table2_yyyymmdd(インスタンスAスナップショットの作成日時)
• table2_yyyymmdd(インスタンスBスナップショットの作成日時)
• table2_yyyymmdd(インスタンスCスナップショットの作成日時)
  ....

4. テーブルを比較する

以下のようなコマンドで得られる結果をベースに適宜select文などで抽出して詳細な調査を進めていく。

レコードが存在する/しないに差分があったらそのIDを出力

select id from <比較対象テーブル1>_<RDSインスタンスAの元となったスナップショットの作成日時(yyyymmdd)> 
except all
select id from <比較対象テーブル1>_<RDSインスタンスBの元となったスナップショットの作成日時(yyyymmdd)> ;

いずれかのカラムに差分があるレコードの情報をすべて出力

(
  SELECT '<テーブルA>' AS _table_name, * FROM <テーブルA>
  EXCEPT ALL SELECT '<テーブルA>' AS table_name, * FROM <テーブルB>
) UNION ALL (
  SELECT '<テーブルB>' AS _table_name, * FROM <テーブルB>
  EXCEPT ALL SELECT '<テーブルB>' AS table_name, * FROM <テーブルA>
) ORDER BY id, _table_name;

※テーブルAとテーブルB
テーブルA: <比較対象テーブル1>_<RDSインスタンスAの元となったスナップショットの作成日時(yyyymmdd)>
テーブルB: <比較対象テーブル1>_<RDSインスタンスBの元となったスナップショットの作成日時(yyyymmdd)>

参考

• https://www.techlive.tokyo/archives/4011
• http://ajisaba.net/db/postgresql/table_record_diff.html