PostgreSQL を利用したデータ移送
はじめに
PostgreSQLを利用したデータ移送の要件がありました。
別々のDBからDBへデータ移送をするということだが、同一なのはテーブル名とカラムのみで、ユーザーやスキーマが異なる要件があり、作ってみました。
テーブル情報を含んだ移送
・アーカイブ経由
- スキーマレベル、データベース全体を移送したい場合は、アーカイブ形式が最も簡単
- pg_restore ではテーブル単位の挿入も可能で、pg_dump で大きくバックアップを取り、pg_restoreで個別にデータ挿入が可能
移送元と移送先のユーザ、スキーマの構成が同じ場合はこれが最も簡単
pg_dump -Fc
pg_restore
参考：PostgresSQLのbackup, restore方法まとめ
ただし、移送元と移送先のDDL構造が異なる場合、これを利用できないため、
本記事を作成。
・スクリプト経由
- pg_dump でSQLファイル（DDL/DML）が生成され、これを経由して実行
- sed コマンドで、ddlのユーザ名（1か所のみ）、スキーマ名（結構多い）の置換を実施
- sed コマンドでsearch_path を追加（DDLに記載していれば問題ないはずだが、念のため追加）
# db -> sql
pg_dump --username=user_test --table trgt_tbl--schema test -h localhost -p 5432 db_nm> trgt_tbl.sql
# dump_sql include
# if you change the owner
# --- in script
# --- 'ALTER TABLE before_test.trgt_tbl OWNER TO before_user;'
# sed -i-e "s/OWNER TO before_user/OWNER TO after_user/g"
# if you change the schema
# sed -i-e "s/before_test/after_test/g"
# if you want to specify schema because psql does not have the property of schema
sed -i 'set search_path to test;' trgt_tbl.sql
# sql -> db
psql -h localhost -p 5432 -U test -d db_nm < trgt_tbl.sql
※ ただし、本件の致命的なパターンとして、移送元のユーザー名とスキーマ名が同じで、移送先のユーザー名とスキーマ名が別々の場合、置換できず、上記のツールは利用できない。
※ そのため、下記のパターンを作成
データのみの移送
シェルで一度に実施したい場合があったので作成してみました。
〇ポイント
・コピーコマンドでCSVを経由しています。
・スキーマを考慮するために、set search_path to を指定できるようにしています。
・一時ファイルの削除処理（ローカルにファイルを配置するため、ループして利用するとディスクフルになる可能性があるため。）
set -x;
# input db info
input_db_user="test"
input_db_url="localhost"
input_db_port=5432
input_db_name="db_name"
input_db_schema="test"
# output db info
output_db_user="test"
output_db_url="localhost"
output_db_port=5432
output_db_name="db_name"
output_db_schema="test"
# target table
trgt_tbl=$1; shift
# convert data from jt-db to temp-csv
export PGPASSWORD=${input_db_user}
psql -h ${input_db_url} -p ${input_db_port} -U ${input_db_user} -d ${input_db_name} << EOF
set search_path to ${input_db_schema};
\COPY ${trgt_tbl} to ${trgt_tbl}.csv CSV HEADER;
EOF
# convert data from temp-csv to dev-db
export PGPASSWORD=${output_db_user};
psql -h ${output_db_url} -p ${output_db_port} -U ${output_db_user} -d ${output_db_name} << EOF
set search_path to ${output_db_schema};
truncate table ${trgt_tbl};
\COPY ${trgt_tbl} from ${trgt_tbl}.csv CSV HEADER;
EOF
# delete temp-csv for disk full
rm ${trgt_tbl}.csv