ORCA（システム）作業用メモ DB 点数・セット・入力CD移行 - git0331-lgtm/Knowledge GitHub Wiki

対象テーブル一覧 : 
tbl_inputcd      | 入力コードマスタ（診療行為の入力コード情報）
tbl_inputset     | 入力セット情報（セット診療情報）
tbl_tensuplus    | 追加点数情報（ORCAカスタマイズ用）
tbl_tensuplus2   | 追加点数情報2（詳細情報）
tbl_tensu_price  | 点数価格情報（診療点数の料金情報）
tbl_tensu_user   | ユーザー設定点数情報（カスタム点数）
tbl_userbyomei   | ユーザー病名設定（カスタム病名）
master.tbl_tensu_master | 点数マスタ（診療報酬の公式点数情報）

？ tbl_tenmst_kanren       | 関連点数情報（診療行為の関連点数）
？ tbl_userkasan           | ユーザー加算情報（カスタム加算点数）

１．
環境A・B : バックアップ取得

２．
環境A・B : パッチ適応状況・マスタ更新状況を同じにする

３.
環境A : データ数保存
psql -d orca -c "SELECT 'tbl_inputcd' AS table_name, COUNT(*) FROM tbl_inputcd;" > data_count_envA.txt
psql -d orca -c "SELECT 'tbl_inputset' AS table_name, COUNT(*) FROM tbl_inputset;" >> data_count_envA.txt
psql -d orca -c "SELECT 'tbl_tensuplus' AS table_name, COUNT(*) FROM tbl_tensuplus;" >> data_count_envA.txt
psql -d orca -c "SELECT 'tbl_tensuplus2' AS table_name, COUNT(*) FROM tbl_tensuplus2;" >> data_count_envA.txt
psql -d orca -c "SELECT 'tbl_tensu_price' AS table_name, COUNT(*) FROM tbl_tensu_price;" >> data_count_envA.txt
psql -d orca -c "SELECT 'tbl_tensu_user' AS table_name, COUNT(*) FROM tbl_tensu_user;" >> data_count_envA.txt
psql -d orca -c "SELECT 'tbl_userbyomei' AS table_name, COUNT(*) FROM tbl_userbyomei;" >> data_count_envA.txt
psql -d orca -c "SELECT 'master.tbl_tensu_master' AS table_name, COUNT(*) FROM master.tbl_tensu_master;" >> data_count_envA.txt

？ psql -d orca -c "SELECT 'tbl_tenmst_kanren' AS table_name, COUNT(*) FROM tbl_tenmst_kanren;" >> data_count_envA.txt
？ psql -d orca -c "SELECT 'tbl_userkasan' AS table_name, COUNT(*) FROM tbl_userkasan;" >> data_count_envA.txt

４．
環境A : テーブル単位バックアップ
pg_dump -O -t tbl_inputcd orca > tbl_inputcd.dump
pg_dump -O -t tbl_inputset orca > tbl_inputset.dump
pg_dump -O -t tbl_tensuplus orca > tbl_tensuplus.dump
pg_dump -O -t tbl_tensuplus2 orca > tbl_tensuplus2.dump
pg_dump -O -t tbl_tensu_price orca > tbl_tensu_price.dump
pg_dump -O -t tbl_tensu_user orca > tbl_tensu_user.dump
pg_dump -O -t tbl_userbyomei orca > tbl_userbyomei.dump
pg_dump -O -t master.tbl_tensu_master orca > master.tbl_tensu_master.dump

？ pg_dump -O -t tbl_tenmst_kanren orca > tbl_tenmst_kanren.dump
？ pg_dump -O -t tbl_userkasan orca > tbl_userkasan.dump

５．
環境B : テーブル単位データ削除
$ psql orca  

orca=> delete from tbl_inputcd ;
orca=> delete from tbl_inputset ;
orca=> delete from tbl_tensuplus ;
orca=> detete from tbl_tensuplus2 ;
orca=> delete from tbl_tensu_price ;
orca=> delete from tbl_tensu_user ;
orca=> delete from tbl_userbyomei ;
orca=> delete from master.tbl_tensu_master ;

？ orca=> delete from tbl_tenmst_kanren ;
？ orca=> delete from tbl_userkasan ;

orca=> \q

６．
環境B : テーブル単位データリストア
psql -e orca < tbl_inputcd.dump
psql -e orca < tbl_inputset.dump
psql -e orca < tbl_tensuplus.dump
psql -e orca < tbl_tensuplus2.dump
psql -e orca < tbl_tensu_price.dump
psql -e orca < tbl_tensu_user.dump
psql -e orca < tbl_userbyomei.dump
psql -e orca < master.tbl_tensu_master.dump

？psql -e orca < tbl_tenmst_kanren.dump
？psql -e orca < tbl_userkasan.dump

７．
環境B : データ数保存
psql -d orca -c "SELECT 'tbl_inputcd' AS table_name, COUNT(*) FROM tbl_inputcd;" > data_count_envB.txt
psql -d orca -c "SELECT 'tbl_inputset' AS table_name, COUNT(*) FROM tbl_inputset;" >> data_count_envB.txt
psql -d orca -c "SELECT 'tbl_tensuplus' AS table_name, COUNT(*) FROM tbl_tensuplus;" >> data_count_envB.txt
psql -d orca -c "SELECT 'tbl_tensuplus2' AS table_name, COUNT(*) FROM tbl_tensuplus2;" >> data_count_envB.txt
psql -d orca -c "SELECT 'tbl_tensu_price' AS table_name, COUNT(*) FROM tbl_tensu_price;" >> data_count_envB.txt
psql -d orca -c "SELECT 'tbl_tensu_user' AS table_name, COUNT(*) FROM tbl_tensu_user;" >> data_count_envB.txt
psql -d orca -c "SELECT 'tbl_userbyomei' AS table_name, COUNT(*) FROM tbl_userbyomei;" >> data_count_envB.txt
psql -d orca -c "SELECT 'master.tbl_tensu_master' AS table_name, COUNT(*) FROM master.tbl_tensu_master;" >> data_count_envB.txt

？ psql -d orca -c "SELECT 'tbl_tenmst_kanren' AS table_name, COUNT(*) FROM tbl_tenmst_kanren;" >> data_count_envB.txt
？ psql -d orca -c "SELECT 'tbl_userkasan' AS table_name, COUNT(*) FROM tbl_userkasan;" >> data_count_envB.txt

８．
環境Aと環境Bのデータ数比較
diff data_count_envA.txt data_count_envB.txt

差分行のみ出力
grep -v -f data_count_envA.txt data_count_envB.txt

環境Bにデータが足りないテーブル
diff data_count_envA.txt data_count_envB.txt | grep "<" > missing_in_envB.txt

環境Bに余計なデータがあるテーブル
diff data_count_envA.txt data_count_envB.txt | grep ">" > extra_in_envB.txt