なんで繋ぎたいんだってばよ
- 複数のDBがある
- プログラム上でDB結合するより、結合してるDBに対してSQL発行できたほうが楽じゃない？
- 短期開発アサイン者には明かしたくないDBの接続情報がある…
手順
基本はpostgres特殊ユーザ（スーパーユーザ）で実施すること。
ポスグレは9.4以上じゃないと駄目。
機能拡張
postgres_fdwを入れる
CREATE EXTENSION postgres_fdw;
-- 確認
SELECT * FROM pg_extension;
外部接続ラッパーを作成
指定するもの
- 接続元サーバ内で使用する接続先名称
- 例：acces_target_server
- 接続先のhost
- 例：00.00.00.00
- 接続先のデータベース名
- 例：access_target_db
- 接続先へのポート番号
- 例：5432
CREATE SERVER acces_target_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '00.00.00.00', dbname 'access_target_db', port '5432');
-- 確認
SELECT * FROM pg_foreign_server;
外部接続ラッパー使うユーザを指定、マッピング
指定するもの
- 接続元サーバ内に存在・使用するユーザ名
- 例：here_user
- 接続先のユーザ
- 例：postgres
- 接続先のユーザのパスワード
- 例：password
勿論接続先の情報が正しくて権限がないと駄目です。
CREATE USER MAPPING FOR here_user SERVER common OPTIONS (user 'postgres', password 'password');
-- 確認
SELECT * FROM pg_user_mapping;
休憩 ここまでの登録情報を確認
ふむふむ。
select
mapping.umuser,
auth.rolname,
mapping.umserver,
server.srvname,
mapping.umoptions
from pg_user_mapping as mapping
inner join pg_authid as auth
on mapping.umuser = auth.oid
inner join pg_foreign_server as server
on mapping.umserver = server.oid
接続したいTableを繋ぐ
指定するもの
- 接続先にあるテーブル名
- 例：target_table
- 接続先にあるテーブルから取得したいカラムの定義
- 接続元サーバ内で使用する接続先名称
- 例：acces_target_server
- さっき作ったね
- 例：acces_target_server
接続元DBに既存の名称のテーブルと”同名称”の接続先テーブルと繋ぎたい時ってどうしたらいいんでしょうか…？？？
最初から命名に気をつけろって話？？？
CREATE FOREIGN TABLE target_table (
target_id integer not null
, target_name character varying(100) not null
)
SERVER acces_target_server;
ユーザに権限をふる
USER MAPPINGしたユーザがスーパーユーザだったら問題はないけれど、そうでなければ権限を振らないとエラーになります。
GRANT SELECT ON target_table TO here_user;
dblinkじゃ駄目なんですか
前提に書いたこと無視して、いろいろ丸見えのままで良ければ、多分以下のやり方が楽チン。
いちいちカラム定義を指定してあげないといけないので、View化したいところ。
-- ①「dblink」拡張
CREATE EXTENSION dblink SCHEMA public;
-- ②コネクションを繋ぐ
select dblink_connect('conn1', 'hostaddr=00.00.00.00 port=5432 dbname=access_target_db user=postgres password=password');
-- ③select
SELECT * FROM dblink('conn1', 'SELECT target_id, target_name FROM public.target_table')
t1(target_id integer, target_name character(100));
-- ④使ったコネクションは閉じる
select dblink_disconnect('conn1');