Postgresqlから外部のデータベースを操作する

あまプロでは、データベースを扱う案件を良く請けます。
先日、PostgreSQLからMySQLを操作する案件に携わりましたので、あメログ。

PostgreSQLからMySQL等の外部のデータベースへ接続するにはForeignDataWrapperという拡張機能を用います。
DBリンクという似た機能も有るのですが、これは外部のPostgreSQLとしか接続出来ません。
FDWであればMySQLだけに限らず、色々なDBへ接続できる様です。詳しくはこちらをご覧下さい。

今回はPostgreSQLからMySQLのlist_db.listsというテーブルに接続してみます。
どちらも同じローカルにインストールしている前提です。
リモートのデータベースに接続する場合は、適宜接続設定を行って下さい。
環境は以下の通り

Ubuntu 17.10
PostgreSQL 9.6.6
MySQL 5.7.20

先ず、MySQL用のFDWをインストールします。
Ubuntu（Debian系）では

sudo apt install postgresql-9.6-mysql-fdw

でインストール可能です。
恐らく他のディストリビューションでもインストール出来る筈です。

以降の操作にはデータベースでの管理者権限が必要です。
使用するユーザに管理者権限が付いてない場合は

GRANT ALL PRIVILEGES ON DATABASE データベース名 TO ユーザ名;

で、権限を付与して下さい。


psqlでPostgreSQL（の権限の付けられたデータベース）へログインします。
拡張機能を作成します。

CREATE EXTENSION mysql_fdw;

サーバを作成します。

CREATE SERVER サーバ名 FOREIGN DATA WRAPPER mysql_fdw OPTIONS ( host 'localhost', port '3306');

サーバ名は（以降の設定でも使うので）解り易い名前にして、ホストやポートは環境に合わせて適宜変更して下さい。

マッピングを作成します。

CREATE USER MAPPING FOR public SERVER サーバ名 OPTIONS ( password '******', username 'ユーザ名');

パスワードとユーザ名はMySQL側のパスワードとユーザ名を指定します。

外部テーブルを作成します。

CREATE FOREIGN TABLE lists ( id int, name text)
 SERVER サーバ名 OPTIONS ( dbname 'list_db', table_name 'lists');

テーブルの列名やデータ型はMySQLのテーブル定義に合わせます。
MySQLの場合は

desc テーブル名;

でテーブル定義が表示されます。
違う列名やデータ型に出来るんかな？
そこは未確認です。ひょっとしたら必要な列だけ取得出来るかも。

以上で設定は終わりです。

SELECT * FROM lists;

UPDATE lists SET name = 'ほげほげ';

こんな感じで、PostgreSQLからMySQLへ対してSQL( のDML)を発行する事が出来ます。

外部のデータベース故に色々と制約は有るのですが、結構便利です。