PostgreSQLのFDWを触ってみた - おふとんの中から

本記事は PostgreSQL Advent Calendar 2019 の2日目の記事です。

ゆいなです。

なんやかんやであまりpostgreSQLのことをあまり触ったことがないと気づいたので、
今回はpostgreSQLのFDWを触ってみようと思います。

FDWって何？

あるデータベースからその外部のデータへのアクセスを行うための仕組み

今回は色々と挫折してlocalhostでお試ししてます。
どうすれば良いのか分かる方は是非教えてほしい・・・

試してみた

環境：postgreSQL 11.2(もともと入れてたpostgreSQL)

前準備

データを作るのがめんどくさかったので、railstutorialをごにょごにょしてlocalhostにDBを作成。

ごにょごにょと言いつつ、やったことはdatabase.ymlを書き換えてlocalhostのpostgreSQLに繋ぎ、migrationとseed流しただけです。

railstutorial=# \dt
                     List of relations
 Schema |         Name         | Type  |       Owner
--------+----------------------+-------+--------------------
 public | ar_internal_metadata | table | railstutorial_user
 public | microposts           | table | railstutorial_user
 public | relationships        | table | railstutorial_user
 public | schema_migrations    | table | railstutorial_user
 public | users                | table | railstutorial_user
(5 rows)

とりあえずこんな感じに。

FDW設定してみる

読み出す側のDBの作成

create database adventcalendar;

adventcalendarに、extensionからpostgre_fdwを入れる。

create extension postgre_fdw;

adventcalendarに、サーバーを作成。

CREATE SERVER railstutorial 
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (dbname 'railstutorial', port '5432', host 'localhost');

作成したサーバーの接続方法を設定。

CREATE USER MAPPING for yuina
SERVER railstutorial 
OPTIONS (user 'railstutorial_user');

これのためにダッシュで作ったので、railstutorial_userのパスワードとか何も設定してないですごめんなさい。

サーバーの設定をしたら外部DBからスキーマをインポートしてくる。

IMPORT FOREIGN SCHEMA public
FROM SERVER railstutorial INTO public;

外部DBのpublicスキーマの内容を読み出すDBのpublicスキーマにインポートする形。

これが成功すると...

adventcalendar=# \d
                     List of relations
 Schema |         Name         |     Type      |   Owner
--------+----------------------+---------------+-----------
 public | ar_internal_metadata | foreign table | yuina_mac
 public | microposts           | foreign table | yuina_mac
 public | relationships        | foreign table | yuina_mac
 public | schema_migrations    | foreign table | yuina_mac
 public | users                | foreign table | yuina_mac
(5 rows)

外部テーブルとしてrailstutorialのテーブルが認識される。

adventcalendar=# select count(*) from users;
 count
-------
   100
(1 row)

外部テーブルと意識せずに読める！！

ということでlocalhost上でFDWできました。

試そうとしてうまくいかなかったやつ

最初は、railstutorialを上げているheroku postgresのpostgreSQLを外部DBとして使おうとしてました。

以下の通り設定。

CREATE SERVER railstutorial
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'hogehoge.compute-1.amazonaws.com', port '5432', dbname 'database');

CREATE USER MAPPING FOR yuina
SERVER railstutorial
OPTIONS (user 'hoge', password 'fuga');

これでインポートすればいけるやろーと思い、

IMPORT FOREIGN SCHEMA public
FROM SERVER railstutorial INTO public;

すると、

ERROR:  could not connect to server "railstutorial"
DETAIL:  could not send data to server: Software caused connection abort
could not send SSL negotiation packet: Software caused connection abort

heroku相手だし、sslmodeの設定いるのかなぁと思い、サーバーの作成し直し。

CREATE SERVER test_fdw
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'ec2-23-21-244-254.compute-1.amazonaws.com', port '5432', sslmode 'require', dbname 'd7dulj5k3bs1ks');

しかし変わらず…

ERROR:  could not connect to server "railstutorial"
DETAIL:  could not send data to server: Software caused connection abort
could not send SSL negotiation packet: Software caused connection abort

~~ダムの横でパソコン広げて試していて、次の場所行こうとしてたのもあって~~ heroku postgresでやることを断念しました・・・。

sslmodeのデフォルトってpreferなはずなので特に何もせずにいけるはずなんですが・・・