[PostgreSQL]別DBのテーブルとJOINしたい

DBを分けたテーブルとJOINしていい感じに扱えないか 尋ねられたので調べてみました。

ETL かなにかでレコードを同じDBのテーブルに格納すれば JOIN は可能ですが、 同期する手間やラグを考えるのは嫌なことです。

結論から言うと DBLINK や Foreign Data Wrapper の仕組みを使うことで透過的に参照できますが、 ハマりどころがあったので誰かの役に立つことを信じて備忘録として残しておくことにします。

準備

今回は docker-compose で環境を用意します。

version: "3.7"
services:
  pg1:
    container_name: pg1
    image: postgres:11.4
    environment:
      POSTGRES_USER: user1
      POSTGRES_PASSWORD: password
      POSTGRES_DB: db1
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=C"
    networks:
      - dblink-test-network
  pg2:
    container_name: pg2
    image: postgres:11.4
    environment:
      POSTGRES_USER: user2
      POSTGRES_PASSWORD: password
      POSTGRES_DB: db2
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=C"
    networks:
      - dblink-test-network
  my1:
    container_name: my1
    image: mysql:8.0.3
    environment:
      MYSQL_USER: user3
      MYSQL_PASSWORD: password
      MYSQL_DATABASE: db4
      MYSQL_ROOT_PASSWORD: root
    networks:
      - dblink-test-network

networks:
  dblink-test-network:

以降は docker-compose up によってコンテナが起動している前提で話を進めます。

• pg1
• pg2

• $ docker exec -it pg1 /bin/bash # psql -U user1 --dbname db1 db1=# CREATE EXTENSION dblink; CREATE EXTENSION db1=# CREATE TABLE a (id INTEGER, field VARCHAR(10)); CREATE TABLE db1=# INSERT INTO a VALUES (1, 'aa'); INSERT 0 1 db1=# CREATE DATABASE db3; CREATE DATABASE db1=# \c db3 You are now connected to database "db3" as user "user1". db3=# INSERT INTO c VALUES (1, 'cccc'); INSERT 0 1 db3=# CREATE TABLE c (id INTEGER, field VARCHAR(10)); CREATE TABLE db3=# \c db1 You are now connected to database "db1" as user "user1".
• $ docker exec -it pg2 /bin/bash # psql -U user2 --dbname db2 db2=# CREATE TABLE b (id INTEGER, field VARCHAR(10)); CREATE TABLE db2=# INSERT INTO b VALUES (1, 'bbb'); INSERT 0 1

準備したDBを俯瞰するとはこんな感じ。

pg1 (ゲスト環境1)

• a in db1

  • id	field
    1	aa

• c in db1

  • id	field
    1	cccc

pg2 (ゲスト環境2)

• b in db2

  • id	field
    1	bbb

info

• CREATE EXTENSION dblink をやらないと HINT: No function matches the given name and argument types. You might need to add explicit type casts. といわれます
  • これは初回に一度実行すればOKです
• PostgreSQL のバージョンによっては postgresql-contrib のインストールが必要かもしれません

作ったテーブルを db1 にいる状態で覗いてみましょう。

a は見れましたが、 b, c は 見つからないと言われてしまいました。 まぁこれは期待通りです。

DBLINK

今度は DBLINK 関数を使って 接続します。

レコードが取れました！ ここまでくればもうできたようなもんですね。

warning

• DBLINK で取れた表に対しては AS でフィールドと型を紐づけてあげないとエラーになります
  • ERROR: a column definition list is required for functions returning "record"
• 接続条件に user を指定しないと postgres で接続しに行くので DETAIL: FATAL: role \"postgres\" does not exist と言われることがあります。
  • あらかじめ postgres を作っておくか存在するユーザを指定しましょう。

まぁでもこのままでは少し冗長なので VIEW にしてみます。

作ったビューと結合してみましょう。 結合条件は適当です。

b や c は VIEW なので変更は即座に反映されます。

実験のために b のレコードを削除します

db1から見ても b は空になってますね

dblink_connect

DBLINK は使用するたびにセッションが新たに作られるようで、これはあまり好ましくありません。

また、認証情報も毎回指定するのだるいです。こんなときに使うのが dblink_connect です。

dblink_connect は 接続を別名としてセッション内で永続化します。

ここでは pg2 の db2 データベースへのコネクションを db2 という名前で保存します。

試しにこのタイミングで (db2 データベースの) user2 パスワードを password2 に変更し、 dblink_connect で作ったコネクションを使った接続と使わない接続を試してみましょう。

• 使う
• 使わない

• db1=# SELECT * FROM DBLINK('db2', 'SELECT * FROM b') db1-# AS f(id INTEGER, field VARCHAR(10));
• db1=# SELECT * FROM DBLINK('host=pg2 user=user2 password=password dbname=db2', 'SELECT * FROM b') db1-# AS f(id INTEGER, field VARCHAR(10)); ERROR: could not establish connection DETAIL: FATAL: password authentication failed for user "user2"

dblink_connect で作った接続を使っている クエリした方は同じ接続を使い回すのでエラーになりませんでしたね。

warning

• セッション内なので、一度セッションを抜けると接続は切れます。

Foreign Data Wrapper

Foreign Data Wrapper (頭文字をとって fdw) は外部のデータアクセスを提供します。

DB_LINK も外部DBへの接続ができましたが、 fdw は PostgreSQL 以外のDBも透過的に扱えます。 対象がリモートであれば積極的にこちらを採用したいところですね。

今回は PostgreSQL から MySQL(8.0.3) の外部サーバに接続してみましょう。

info

• MySQL 8.0.4 以上を使うと デフォルトの認証方法が caching_sha2_password に変わり、 いろいろとめんどくさいので今回は MySQL の 8.0.3 を使っています。
• ERROR: failed to connect to MySQL: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/x86_64-linux-gnu/mariadb18/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory]

PostgreSQL から MySQL に接続するためには mysql_fdw という エクステンションが必要ですが、 通常は入っていません。

今回は MySQL イメージということで OS は Ubuntu なので、 postgresql-11-mysql-fdw をインストールするだけで準備完了です。

おわりました。

あとは psql で PostgreSQLに繋いで以下のことをします。

• CREATE EXTENSION 文で mysql_fdw エクステンションを有効にする
• CREATE SERVER 文で接続対象のサーバを登録する
• CREATE USER MAPPING FOR 文でリモートユーザとユーザを紐付ける
• CREATE FOREIGN TABLE 文で外部テーブルとスキーマを紐付ける

ちなみにMySQLには予めテーブル作成＆データ登録をしてます

できましたね。

もっといいやり方あるよ～って方は教えてください。では。

参考

https://lets.postgresql.jp/documents/technical/contrib/dblink https://www.postgresql.jp/document/11/html/contrib-dblink-connect.html https://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret https://qiita.com/msrx9/items/734d644b7b2aad796b09 https://thinkit.co.jp/article/9933