「本番データベースを開発環境にコピーしたい」 「PostgreSQLのバージョンアップでデータを移行したい」 「定期的なバックアップを自動化したい」 「CSVでデータをやり取りしたい」
こんな場面、よくありますよね？
PostgreSQLには強力なエクスポート・インポートツールが用意されています。この記事を読めば、データの引っ越しマスターになれます！
小規模なテーブルから、数百GBの巨大データベースまで、あらゆる場面に対応できる方法を、実例付きで解説します。
基本を押さえる：4つのエクスポート方法
エクスポート方法の比較表
| 方法 | 用途 | 形式 | サイズ | 速度 |
|---|---|---|---|---|
| pg_dump（SQL形式） | 小〜中規模、可読性重視 | テキスト | 大きい | 遅い |
| pg_dump（カスタム形式） | 中〜大規模、圧縮重視 | バイナリ | 小さい | 速い |
| COPY TO | 特定テーブルのCSV出力 | CSV/TSV | 中程度 | 最速 |
| pg_dumpall | 全データベース、全ユーザー | テキスト | 最大 | 遅い |
それぞれ詳しく見ていきましょう！
方法1：pg_dumpでデータベースをエクスポート
基本的な使い方（SQL形式）
# データベース全体をエクスポート
pg_dump -U postgres -d mydb > mydb_backup.sql
# より詳細なオプション付き
pg_dump -U postgres -h localhost -p 5432 -d mydb -v -f mydb_backup.sql
オプションの意味：
-U: ユーザー名-h: ホスト名-p: ポート番号-d: データベース名-v: 詳細表示（verbose）-f: 出力ファイル名
スキーマのみエクスポート（構造だけ）
# テーブル構造、インデックス、制約のみ
pg_dump -U postgres -d mydb --schema-only > schema.sql
# 実行例の出力内容
-- CREATE TABLE employees (
-- id SERIAL PRIMARY KEY,
-- name VARCHAR(100),
-- salary INTEGER
-- );
-- CREATE INDEX idx_employees_name ON employees(name);
データのみエクスポート（構造なし）
# INSERTステートメントとして出力
pg_dump -U postgres -d mydb --data-only > data.sql
# COPYコマンド形式で出力（高速）
pg_dump -U postgres -d mydb --data-only --column-inserts > data_inserts.sql
特定テーブルのみエクスポート
# 単一テーブル
pg_dump -U postgres -d mydb -t employees > employees.sql
# 複数テーブル
pg_dump -U postgres -d mydb -t employees -t departments > tables.sql
# ワイルドカード使用
pg_dump -U postgres -d mydb -t 'public.emp*' > emp_tables.sql
# 特定テーブルを除外
pg_dump -U postgres -d mydb -T logs -T temp_* > backup_without_logs.sql
方法2：カスタム形式で圧縮エクスポート（推奨！）
なぜカスタム形式が推奨なのか？
メリット：
- ✅ ファイルサイズが1/5〜1/10に圧縮
- ✅ 並列リストアが可能
- ✅ 部分的なリストアが可能
- ✅ エラー時の再開が可能
# カスタム形式でエクスポート（-Fc）
pg_dump -U postgres -d mydb -Fc -f mydb.dump
# 圧縮レベル指定（0-9、デフォルト6）
pg_dump -U postgres -d mydb -Fc -Z 9 -f mydb_max_compress.dump
# 並列処理でエクスポート（大規模DB向け）
pg_dump -U postgres -d mydb -Fc -j 4 -f mydb_parallel.dump
ディレクトリ形式（超大規模向け）
# ディレクトリ形式でエクスポート
pg_dump -U postgres -d mydb -Fd -f mydb_dir/
# 並列処理付き（各テーブルが別ファイルになる）
pg_dump -U postgres -d mydb -Fd -j 8 -f mydb_dir/
方法3：COPYコマンドでCSVエクスポート
テーブルをCSVファイルへ
-- PostgreSQL内から実行（サーバー側のファイル）
COPY employees TO '/tmp/employees.csv' WITH (FORMAT CSV, HEADER);
-- より詳細な設定
COPY employees TO '/tmp/employees.csv'
WITH (
FORMAT CSV,
HEADER true,
DELIMITER ',',
QUOTE '"',
ESCAPE '"',
NULL 'NULL'
);
psqlから実行（クライアント側）
# \copyコマンドを使用（クライアント側のファイルに出力）
psql -U postgres -d mydb -c "\copy employees TO 'employees.csv' WITH (FORMAT CSV, HEADER)"
# 条件付きエクスポート
psql -U postgres -d mydb -c "\copy (SELECT * FROM employees WHERE department = '営業部') TO 'sales_employees.csv' CSV HEADER"
プログラムから実行（スクリプト例）
#!/bin/bash
# export_tables.sh - 全テーブルをCSVエクスポート
DB="mydb"
USER="postgres"
OUTPUT_DIR="./csv_export"
mkdir -p $OUTPUT_DIR
# テーブル一覧を取得
TABLES=$(psql -U $USER -d $DB -t -c "SELECT tablename FROM pg_tables WHERE schemaname='public'")
# 各テーブルをCSVエクスポート
for TABLE in $TABLES; do
echo "Exporting $TABLE..."
psql -U $USER -d $DB -c "\copy $TABLE TO '$OUTPUT_DIR/$TABLE.csv' CSV HEADER"
done
インポート：データを取り込む
SQL形式のインポート
# 基本的なインポート
psql -U postgres -d mydb < mydb_backup.sql
# エラーで停止しない
psql -U postgres -d mydb -f mydb_backup.sql --single-transaction
# 進捗を表示
psql -U postgres -d mydb -f mydb_backup.sql -v ON_ERROR_STOP=1
カスタム形式のインポート（pg_restore）
# 基本的なリストア
pg_restore -U postgres -d mydb mydb.dump
# データベースを作成してからリストア
pg_restore -U postgres -C -d postgres mydb.dump
# 並列リストア（高速）
pg_restore -U postgres -d mydb -j 4 mydb.dump
# データのみリストア（構造は既存）
pg_restore -U postgres -d mydb --data-only mydb.dump
# 特定テーブルのみリストア
pg_restore -U postgres -d mydb -t employees mydb.dump
CSVファイルのインポート
-- COPY FROM（サーバー側のファイル）
COPY employees FROM '/tmp/employees.csv' WITH (FORMAT CSV, HEADER);
-- データを追記ではなく置換したい場合
TRUNCATE employees;
COPY employees FROM '/tmp/employees.csv' WITH (FORMAT CSV, HEADER);
# psqlから（クライアント側のファイル）
psql -U postgres -d mydb -c "\copy employees FROM 'employees.csv' CSV HEADER"
実践例：よくあるシナリオ
シナリオ1：本番DBを開発環境にコピー
#!/bin/bash
# production_to_dev.sh
# 1. 本番DBをエクスポート
echo "本番DBをエクスポート中..."
pg_dump -h prod-server -U postgres -d production_db -Fc -f prod_backup.dump
# 2. 開発DBを削除・再作成
echo "開発DBをリセット中..."
dropdb -h dev-server -U postgres dev_db
createdb -h dev-server -U postgres dev_db
# 3. データをインポート
echo "開発環境にインポート中..."
pg_restore -h dev-server -U postgres -d dev_db -j 4 prod_backup.dump
# 4. 個人情報をマスキング（オプション）
echo "個人情報をマスキング中..."
psql -h dev-server -U postgres -d dev_db << EOF
UPDATE users SET
email = 'user' || id || '@example.com',
phone = '090-0000-' || LPAD(id::text, 4, '0');
EOF
echo "完了！"
シナリオ2：定期バックアップスクリプト
#!/bin/bash
# daily_backup.sh
# 設定
DB_NAME="mydb"
DB_USER="postgres"
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
KEEP_DAYS=7
# バックアップ実行
echo "バックアップ開始: $DATE"
# フルバックアップ（カスタム形式）
pg_dump -U $DB_USER -d $DB_NAME -Fc -f "$BACKUP_DIR/full_${DB_NAME}_${DATE}.dump"
# スキーマのみ（テキスト形式で保存）
pg_dump -U $DB_USER -d $DB_NAME --schema-only -f "$BACKUP_DIR/schema_${DB_NAME}_${DATE}.sql"
# 古いバックアップを削除
find $BACKUP_DIR -name "*.dump" -mtime +$KEEP_DAYS -delete
find $BACKUP_DIR -name "*.sql" -mtime +$KEEP_DAYS -delete
echo "バックアップ完了: $DATE"
# crontabに登録して毎日2時に実行
# 0 2 * * * /path/to/daily_backup.sh
シナリオ3：大規模DBの移行（100GB以上）
#!/bin/bash
# large_db_migration.sh
# 並列処理とディレクトリ形式を使用
SOURCE_DB="large_production_db"
TARGET_DB="new_production_db"
JOBS=8 # CPU数に応じて調整
# 1. エクスポート（並列・ディレクトリ形式）
echo "大規模DBをエクスポート中..."
time pg_dump -U postgres -d $SOURCE_DB -Fd -j $JOBS -f db_export_dir/
# 2. 転送（必要に応じて）
echo "ファイルを転送中..."
rsync -avz --progress db_export_dir/ remote-server:/tmp/db_export_dir/
# 3. インポート（並列）
echo "新環境にインポート中..."
time pg_restore -U postgres -d $TARGET_DB -j $JOBS db_export_dir/
# 4. 統計情報を更新
echo "統計情報を更新中..."
psql -U postgres -d $TARGET_DB -c "ANALYZE;"
トラブルシューティング
エラー1：権限不足
# エラー: permission denied for schema public
# 解決策1: スーパーユーザーでエクスポート/インポート
pg_dump -U postgres -d mydb > backup.sql
# 解決策2: --no-owner オプションを使用
pg_dump -U user -d mydb --no-owner --no-acl > backup.sql
エラー2：エンコーディングの不一致
# エラー: ERROR: invalid byte sequence for encoding "UTF8"
# 解決策: エンコーディングを指定
pg_dump -U postgres -d mydb -E UTF8 > backup.sql
# インポート時に変換
iconv -f LATIN1 -t UTF8 backup.sql | psql -U postgres -d mydb
エラー3：メモリ不足
# 大規模テーブルでメモリ不足
# 解決策1: COPYを使用
psql -c "\copy large_table TO 'large_table.csv' CSV"
# 解決策2: WHERE句で分割
pg_dump -U postgres -d mydb -t large_table --where="id < 1000000" > part1.sql
pg_dump -U postgres -d mydb -t large_table --where="id >= 1000000" > part2.sql
パフォーマンスのヒント
エクスポートの高速化
# 1. 並列処理を使用
pg_dump -Fd -j 8 -f backup_dir/
# 2. 圧縮を無効化（ネットワークが速い場合）
pg_dump -Fc -Z 0 -f backup.dump
# 3. --exclude-table-dataで不要なデータを除外
pg_dump --exclude-table-data='logs*' -f backup.sql
インポートの高速化
-- インポート前の設定変更
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET wal_buffers = '16MB';
-- 自動VACUUM無効化
ALTER SYSTEM SET autovacuum = off;
-- インポート後に戻す
ALTER SYSTEM RESET ALL;
SELECT pg_reload_conf();
セキュリティの考慮事項
パスワードの扱い
# 方法1: .pgpassファイルを使用
echo "localhost:5432:mydb:postgres:mypassword" > ~/.pgpass
chmod 600 ~/.pgpass
# 方法2: 環境変数を使用
export PGPASSWORD='mypassword'
pg_dump -U postgres -d mydb > backup.sql
# 方法3: 接続文字列を使用（URLエンコード注意）
pg_dump "postgresql://user:pass@localhost/mydb" > backup.sql
バックアップファイルの暗号化
# エクスポート時に暗号化
pg_dump -U postgres -d mydb | gzip | openssl enc -aes-256-cbc -out backup.sql.gz.enc
# 復号化してインポート
openssl enc -d -aes-256-cbc -in backup.sql.gz.enc | gunzip | psql -U postgres -d mydb
まとめ：もうデータ移行で悩まない！
今日マスターした重要ポイント：
✅ pg_dump = 最も基本的で万能なツール ✅ カスタム形式（-Fc） = 圧縮＋高速＋柔軟 ✅ 並列処理（-j） = 大規模DBの救世主 ✅ COPY = CSVでのやり取りに最適 ✅ pg_restore = カスタム形式の相棒 ✅ –schema-only/–data-only = 構造とデータを分離 ✅ 定期バックアップ = スクリプト化で自動化
最初は「コマンドが多くて覚えられない…」と感じるかもしれません。
でも、基本はpg_dump -Fcでエクスポート、pg_restoreでインポート。これだけ覚えれば、ほとんどの場面で対応できます。
今日から、データの引っ越しは怖くありません！
Happy Migration!
次のステップにおすすめ：
- PostgreSQLレプリケーションでリアルタイム同期
- pg_basebackupで物理バックアップ
- WALアーカイブでポイントインタイムリカバリ
コメント