PostgreSQLでDB間でデータをコピーする

PostgreSQLでDB間またはスキーマー間でpd_dumpを使ってコピーを試みたものの、うまくいきませんでした。

調べているとやり方はあるらしいですが、結局手間がかかり\copyの方が早いらしいのでこれで試してみました。

環境

PostgreSQL 9.4
CentOS 6
Mac 11

コピーの流れ

「コピー元DB：A、コピー先DB：B」とします。

1.AでCSVエクスポート
2.Bでデータ削除
3.BでCSVインポート
4.Bでシーケンス初期化

各項番の説明をしていきます。

1.AでCSVエクスポート

ターミナルで次を実行します。

# 通常接続
psql -h Aのホスト -U Aのユーザー名 -d AのDB名 -c "\copy テーブル名 to '/出力先フルパス/テーブル名.csv'  (format csv, delimiter ',', header true);"

# SSL接続
psql "sslmode=require host=Aのホスト user=Aのユーザー名 dbname=AのDB名" -c "\copy テーブル名 to '/出力先フルパス/テーブル名.csv'  (format csv, delimiter ',', header true);"

2.Bでデータ削除

ターミナルでpsqlコマンドでDBに接続し、次のSQLを流してデータを削除します。
（psql -c "コマンド"で接続しなくてもできます）

TRUNCATE TABLE テーブル名;

3.BでCSVインポート

エクスポートの「to」を「from」に変えるだけです。
ターミナルで次を実行します。

# 通常接続
psql -h Bのホスト -U Bのユーザー名 -d BのDB名 -c "\copy テーブル名 from '/出力先フルパス/テーブル名.csv'  (format csv, delimiter ',', header true);"

# SSL接続
psql "sslmode=require host=Bのホスト user=Bのユーザー名 dbname=BのDB名" -c "\copy テーブル名 from '/出力先フルパス/テーブル名.csv'  (format csv, delimiter ',', header true);"

4.Bでシーケンス初期化

ターミナルでpsqlコマンドでDBに接続し、次のSQLを流してシーケンスを設定します。
「\copy」でデータをインポートしても、シーケンスは設定されません。
「テーブルの件数+1」とすることで、次のシーケンス値になります。

SELECT SETVAL('シーケンス名',(SELECT COUNT(*)+1 FROM テーブル名),FALSE);

補足

パスワードの省略

psqlを流すとパスワードを聞かれるので毎回入力するのは面倒です。
それを省くため、ターミナル毎に次の環境変数を設定しておく便利です。

export PGPASSWORD=接続先DBのパスワード

各一覧の取得

テーブル一覧やシーケンス一覧を取得するには、ターミナルでDB接続後次のコマンドを入力します。

-- テーブル一覧
\dt

-- シーケンス一覧
\ds

ターミナルだと出力された結果で、Altキーを押しながらマウスで選択すると矩形選択になるります。
テキストエディタ等に貼り付け、置換を駆使してコマンド・SQLを作成すれば作業が捗ります。

超絶おすすめするPostgreSQL書籍！！

[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

PostgreSQL全機能バイブル