本記事は 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なはずなので特に何もせずにいけるはずなんですが・・・