はじめに
先日、ある処理のリハーサルを「本番環境のデータを使って検証環境で実施したい」といった気持になることがありました。そんな時、pg_dumpとpg_restoreを使ってDBダンプの取得やDBのコピーをしたところ便利だったので本記事として残しておこうと思います。この記事では、pg_dump / pg_restore を使って本番データを新しい別名DBに安全に複製し、確認後に不要になったら破棄するという一連の流れまでを解説します。
想定環境:
- PostgreSQL（Aurora PostgreSQL 含む）。クライアントに
psql/pg_dump/pg_restoreが導入済み。 - DBへの接続経路は別途確保済みとします（例: SSM ポートフォワーディングで
localhost:15432）。 -
一時DB は "リストア先" インスタンス内に作成します。dump 元は別サーバ・別アカウントの Aurora環境等 でも構いません（クロスアカウント可）。その場合は接続先を dump 元／restore 先で張り替え、
--no-owner --no-aclでユーザ差を吸収します。 -
DB を新規作成できる権限を持つユーザ（
CREATEDB権限、またはpostgresスーパーユーザ）が必要です。通常のアプリ用ユーザだけではCREATE DATABASEに失敗します。
基本方針: 既存DBには一切触れない
事故の多くは「既存のDBに対して --clean 付きでリストアして中身を吹き飛ばす」といったようなことで起きます。
これを避けるため、本記事では次の方針を取ります。
- リストア先は 作業日付付きの新規DB（例:
sampledb_temp_20260929） - 既存DBに対しては DROP / DELETE / TRUNCATE を一切行わない
- 確認が終わったら一時DBごと DROP して後片付けする
| 操作 | 対象 | 既存DBへの影響 |
|---|---|---|
| CREATE DATABASE | 新規 xxx_temp_日付
|
なし |
| pg_restore | 新規 xxx_temp_日付
|
なし |
| DROP DATABASE | 新規 xxx_temp_日付
|
なし |
手順
1. pg_dump でカスタム形式のダンプを取得する
※本記事のコマンドは「SSMトンネルで localhost:15432 に接続する」前提です。VPC内から直接繋ぐ場合は -h を Aurora のエンドポイント、-p を 5432 に読み替えてください。
pg_dump \
-h 127.0.0.1 -p 15432 -U <DB_USER> -d <SRC_DB> \
-F c --verbose \
-f ./dump_$(date +%Y%m%d).dump
| オプション | 説明 |
|---|---|
-F c |
Custom Format（圧縮・オブジェクト単位で選択復元できる） |
--verbose |
進捗を詳細出力 |
-f |
出力先ファイル |
-F c（カスタム形式）にしておくと、後述の pg_restore -l でオブジェクト単位に復元対象を取捨選択できます。プレーンSQL（-F p）ではこれができません。
2. リストア対象を一覧化して取捨選択する
ダンプに含まれるオブジェクト一覧を書き出します。
pg_restore -l ./dump_$(date +%Y%m%d).dump > restore.list
restore.list の各行の先頭に ; を付けると、その行は復元対象から除外されます。個人情報や機微データを含むテーブルを検証環境に持ち込みたくない場合に有効です。
;1234; 0 17542 TABLE DATA public sensitive_table db_user
機微データを含むテーブルを他環境へ復元する際は、必ず事前に承認・マスキング方針を確認しましょう。場合によってはネットワークレベルでの制限を検討しておくことも大事ですね。
3. 新規の一時DBを作成する
app_user のような通常ユーザは CREATEDB 権限を持たないことが多いため、その場合はスーパーユーザ（postgres）で作成します。
psql -h 127.0.0.1 -p 15432 -U postgres -d postgres \
-c "CREATE DATABASE <TEMP_DB> TEMPLATE template0;"
-
TEMPLATE template0を指定すると、template1に加えられたローカルな変更の影響を受けず、クリーンな初期状態のDBを作れます。
Owner がスーパーユーザになってしまう場合は、アプリ用ユーザに付け替えます。
psql -h 127.0.0.1 -p 15432 -U postgres -d postgres \
-c "ALTER DATABASE <TEMP_DB> OWNER TO <DB_USER>;"
4. 一時DBへリストアする（--clean は使わない）
pg_restore \
-h 127.0.0.1 -p 15432 -U <DB_USER> -d <TEMP_DB> \
--no-owner --no-acl --verbose \
--use-list=restore.list \
./dump_$(date +%Y%m%d).dump
| オプション | 説明 |
|---|---|
-d <TEMP_DB> |
復元先は新規の一時DB。既存DBには触れない |
--no-owner |
所有者情報を復元しない（環境間でユーザーが異なるときに必要） |
--no-acl |
権限（GRANT/REVOKE）を復元しない |
--use-list |
restore.list に従って対象を制御 |
--clean |
使わない。既存オブジェクトを削除するため、新規DBには不要かつ危険 |
新規DBへのリストアでは --clean / --if-exists は不要です。うっかり付けて事故らないよう、意識的に外します。
5. 復元結果を確認する
psql -h 127.0.0.1 -p 15432 -U <DB_USER> -d <TEMP_DB> \
-c "SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10;"
主要テーブルの行数が想定どおりかを確認します。
6. 後片付け（一時DBを丸ごと削除）
確認が終わったら一時DBを削除します。接続が残っていると削除に失敗するため、必要に応じて接続を切断します。
# 自分以外の接続を切断
psql -h 127.0.0.1 -p 15432 -U <DB_USER> -d <SRC_DB> \
-c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = '<TEMP_DB>' AND pid <> pg_backend_pid();"
# 一時DBを削除
psql -h 127.0.0.1 -p 15432 -U <DB_USER> -d <SRC_DB> \
-c "DROP DATABASE <TEMP_DB>;"
DROP DATABASE の対象が <TEMP_DB> であることを実行前に必ず目視確認してください。既存DB名を指定する事故を防ぐため、DROP は独立したコマンドとして慎重に扱います。
残骸チェックの小技
一時DBを日付付きで命名しておくと、消し忘れを一覧で検知できます。
psql -h 127.0.0.1 -p 15432 -U <DB_USER> -d <SRC_DB> \
-c "SELECT datname FROM pg_database WHERE datname LIKE '%_temp_%' ORDER BY datname;"
まとめ
- 復元先は新規の別名DBにして、既存DBには DROP/DELETE/TRUNCATE を一切行わない
-
-F c+pg_restore -l+--use-listで復元対象を安全に取捨選択できる - 新規DBへは
--cleanを付けない（事故防止） -
TEMPLATE template0でクリーンなDBを作り、--no-owner --no-aclで環境差を吸収する - 後片付けは
pg_terminate_backendで切断 →DROP DATABASE
「既存を壊さない」を仕組みで担保する、ヒューマンエラー防止を意識した作業フローになっています。
もしかするともっとよい方法があるかもしれないですが、もっと良い方法を知っている方はそっとコメントで教えてください。
本記事が何かのお役に立てば幸いです。
参考
- PostgreSQL 公式: pg_dump: https://www.postgresql.org/docs/current/app-pgdump.html
- PostgreSQL 公式: pg_restore: https://www.postgresql.org/docs/current/app-pgrestore.html