異るPostgreSQLサーバ間のデータ移行では、pg_dump等のダンプツールを使うことが多い。しかし、同一インスタンス内の異なるDB間でデータを移行する時は、pg_dumpとかを使うと一度ファイルに吐かないといけなかったりして面倒なのでFDWを使ってデータを移行してみる。
今回の例では、同一PostgreSQLインスタンス内の異るDB間でのデータ移行だが、異るPostgreSQLサーバ間のデータ移行にも応用することも可能なはず。FDWを使って移行すれば、
- 移行元には何もしなくて良い
- ファイルに吐く必要もない
- 全部SQLで可能
- データのクレンジング、変換等も楽
- 異るスキーマ、テーブル定義への移行も楽
とかのメリットがある。
環境準備
移行先、移行元のDBを作成する。
CREATE DATABASE src_db;
CREATE DATABASE dst_db;
移行元となるテーブルを作成する。
移行元DB
CREATE TABLE hoge (c int);
INSERT INTO hoge SELECT generate_series(1,10000);
移行手順
データ移行は、出来るだけPostgreSQLの機能を使って楽に移行してみる。まずは、postgres_fdwの登録と外部サーバの準備。
移行先DB
-- postgres_fdwモジュールの登録
CREATE EXTENSION postgres_fdw;
-- 外部サーバの作成
-- 同一PostgreSQLインスタンスなのでhostはlocalhost、portは同じ。接続先DBには移行元DBを指定する
DO $$
BEGIN
EXECUTE 'CREATE SERVER src_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS(' ||
'port' || '''' || current_setting('port') || ''', ' ||
'dbname ''src_db'', ' ||
'host ''localhost'')';
END
$$;
-- ユーザマッピングの作成
CREATE USER MAPPING FOR public SERVER src_server;
次に、一時的に外部テーブルを作成するスキーマを移行先DBに作成する。そして、その一時スキーマに移行元のスキーマ情報をインポートしてから、実際のテーブルにデータをロードする。
移行先
-- 一時スキーマの作成
CREATE SCHEMA tmp;
-- スキーマ情報のインポート
IMPORT FOREIGN SCHEMA public FROM SERVER src_server INTO tmp;
-- 移行先のテーブル作成
CREATE TABLE hoge (like tmp.hoge);
-- データ移行
INSERT INTO hoge SELECT * FROM tmp.hoge;
-- 一時スキーマの削除
DROP SCHEMA temp;
さいごに
SQLだけでデータを移行することができました。FDW＋IMPORT FOREIGN SCHEMA便利。
ちなみに、最後に実行したINSERT・・・SELECTはFDWを介して、SELECTが移行元（自分自身のsrc_db）へ透過的に実行されています。
EXPLAIN ANALYZE VERBOSE INSERT INTO hoge SELECT * FROM tmp.hoge;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Insert on public.hoge (cost=100.00..197.75 rows=2925 width=4) (actual time=96.531..96.531 rows=0 loops=1)
-> Foreign Scan on tmp.hoge hoge_1 (cost=100.00..197.75 rows=2925 width=4) (actual time=1.898..58.106 rows=10000 loops=1)
Output: hoge_1.i
Remote SQL: SELECT i FROM public.hoge
Planning time: 0.234 ms
Execution time: 97.508 ms
(6 rows)
Time: 104.242 ms