Initial configuration

MySQL Foreign Data Wrapperを使用する前に：

1. 拡張機能を作成する コマンドを使用して、PostgresホストにMySQL Foreign Data

Wrapper拡張機能を作成します。 2.

サーバーを作成する コマンドを使用して、MySQLサーバーへの接続を定義します。

3. ユーザーマッピングを作成する コマンドを使用して、Postgresロールをサーバーに関連付けるマッピングを定義します。

4. 外国のテーブルを作成する コマンドを使用して、MySQLサーバーにあるテーブルに対応する単一のテーブルを定義するか、 外部スキーマのインポート コマンドを使用してローカルスキーマに複数のリモートテーブルをインポートします。

拡張機能を作成する

CREATE EXTENSION コマンドを使用して、 mysql_fdw 拡張機能を作成します。コマンドを呼び出すには、選択したクライアント（たとえば、 psql）を使用して、MySQLサーバーを照会するPostgresデータベースに接続し、コマンドを呼び出します。

CREATE EXTENSION [IF NOT EXISTS] mysql_fdw [WITH] [SCHEMA schema_name];

パラメーター

IF NOT EXISTS

IF NOT EXISTS 句を含めて、同じ名前の拡張機能が既に存在する場合にエラーを返す代わりに通知を発行するようにサーバーに指示します。

schema_name

オプションで、拡張機能のオブジェクトをインストールするスキーマの名前を指定します。

例

次のコマンドは、MySQL外部データラッパーをインストールします。

CREATE EXTENSION mysql_fdw;

外部データラッパー CREATE EXTENSION コマンドの使用の詳細については、

PostgreSQL documentation を参照してください。

サーバーを作成する

CREATE SERVER コマンドを使用して、外部サーバーへの接続を定義します。構文は次のとおりです。

CREATE SERVER server_name FOREIGN DATA WRAPPER mysql_fdw
    [OPTIONS (option value [, ...])]

サーバーを定義するロールは、サーバーの所有者です。 ALTER SERVER コマンドを使用して、外部サーバーの所有権を再割り当てします。外部サーバーを作成するには、 CREATE SERVER コマンドで指定された外部データラッパーに対するUSAGE 権限が必要です。

パラメーター

server_name

server_name を使用して、外部サーバーの名前を指定します。サーバー名はデータベース内で一意である必要があります。

FOREIGN_DATA_WRAPPER

FOREIGN_DATA_WRAPPER 句を含めて、サーバーがクラスターに接続するときにmysql_fdw 外部データラッパーを使用するように指定します。

OPTIONS

CREATE SERVER コマンドのOPTIONS 句を使用して、外部サーバーの接続情報を指定します。これらのオプションを含めることができます。

Option	Description
character_set	MySQL接続に使用する文字セット。デフォルト値は`auto`で、オペレーティングシステム設定に基づいた自動検出を意味します。 character_set`オプションが導入される前は、文字セットはPostgreSQLデータベースのエンコーディングと同様に設定されていました。この古い動作を取得するには、 `character_set を特別な値`PGDatabaseEncoding`に設定します。
fetch_size	このオプションは、 mysql_fdw が各フェッチ操作で取得する行数を指定します。外部テーブルまたは外部サーバーに指定できます。テーブルに指定されたオプションは、サーバーに指定されたオプションをオーバーライドします。デフォルト値は`100`です。
host	MySQLサーバーのアドレスまたはホスト名。デフォルト値は`127.0.0.1`です。
init_command	MySQLサーバーへの接続時に実行するSQLステートメント。
port	MySQLサーバーのポート番号。デフォルトは`3306`です。
reconnect	既存の接続が失われた場合の MySQL サーバーへの自動再接続を有効または無効にします。デフォルト値は`false`です。
secure_auth	安全な認証を有効または無効にするために使用します。デフォルトは`true`です。
sql_mode	確立された接続にMySQL `sql_mode`を設定します。デフォルトは`ANSI_QUOTES`です。
ssl_key	クライアントの秘密鍵ファイルのパス名。
ssl_cert	クライアント公開鍵証明書ファイルのパス名。
ssl_ca	認証局（CA）証明書ファイルのパス名。このオプションを使用する場合、サーバーが使用するのと同じ証明書を指定する必要があります。
ssl_capath	信頼できるSSL CA証明書ファイルを含むディレクトリのパス名。
ssl_cipher	SSL暗号化で許可される暗号のリスト。
use_remote_estimate	`use_remote_estimate`を含めて、処理コストを見積もるときにリモートサーバーで`EXPLAIN`コマンドを使用するようにサーバーに指示します。デフォルトでは、`use_remote_estimate`は`false`です。

例

次のコマンドは、 mysql_fdw 外部データラッパーを使用して127.0.0.1 のIPアドレスを持つホストに接続するmysql_server という名前の外部サーバーを作成します。

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 127.0.0.1, port 3306);

外部サーバーは、MySQLクラスター上のクライアントへの接続にデフォルトのポート（3306 ）を使用します。

CREATE SERVER コマンドの使用の詳細については、 PostgreSQL documentation を参照してください。

ユーザーマッピングを作成する

CREATE USER MAPPING コマンドを使用して、Postgresロールを外部サーバーに関連付けるマッピングを定義します。

CREATE USER MAPPING FOR role_name SERVER server_name
       [OPTIONS (option value [, ...])];

そのサーバーのユーザーマッピングを作成するには、そのサーバーの所有者である必要があります。

パラメーター

role_name

role_name を使用して、外部サーバーに関連付けるロールを指定します。

server_name

server_name を使用して、MySQLクラスターへの接続を定義するサーバーの名前を指定します。

OPTIONS

OPTIONS 句を使用して、外部サーバーの接続情報を指定します。

username は、MySQLサーバー上のユーザーの名前です。

password は、ユーザー名に関連付けられたパスワードです。

例

次のコマンドは、enterprisedb という名前のロールのユーザーマッピングを作成します。マッピングは、mysql_server という名前のサーバーに関連付けられています。

CREATE USER MAPPING FOR enterprisedb SERVER mysql_server;

データベースホストが安全な認証を使用する場合、ユーザーマッピングを作成するときに接続資格情報を提供します。

CREATE USER MAPPING FOR public SERVER mysql_server OPTIONS (username foo, password bar);

このコマンドは、mysql_server という名前のサーバーに関連付けられているpublic という名前のロールのユーザーマッピングを作成します。 MySQLサーバーに接続すると、サーバーはfoo として認証され、bar のパスワードを提供します。

CREATE USER MAPPING コマンドの詳細については、 PostgreSQL documentation を参照してください。

外国のテーブルを作成する

外部テーブルは、MySQLホストにあるテーブルへのポインタです。 Postgresサーバーで外部テーブル定義を作成する前に、MySQLサーバーに接続してテーブルを作成します。テーブルの列は、Postgresサーバー上のテーブルの列にマップされます。次に、 CREATE FOREIGN TABLE コマンドを使用して、MySQLホストにあるテーブルに対応する列を持つテーブルをPostgresサーバーに定義します。構文は次のとおりです。

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ OPTIONS ( option value [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name [ OPTIONS ( option value [, ... ] ) ]

column_constraint は次のとおりです。

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expr) [ NO INHERIT ] | DEFAULT default_expr }

table_constraint は次のとおりです。

[ CONSTRAINT constraint_name ] CHECK (expr) [ NO INHERIT ]

パラメーター

table_name

外部テーブルの名前を指定します。スキーマ名を含めて、外部テーブルが存在するスキーマを指定します。

IF NOT EXISTS

IF NOT EXISTS 句を含めて、同じ名前のテーブルが既に存在する場合にエラーを返さないようにサーバーに指示します。同名のテーブルが存在する場合、サーバーは通知を発行します。

column_name

新しいテーブルの列の名前を指定します。各列は、MySQLサーバーで説明されている列に対応している必要があります。

data_type

列のデータ型を指定します。可能な場合、PostgresサーバーとMySQLサーバーの各列に同じデータ型を指定します。同じ名前のデータ型が利用できない場合、Postgresサーバーはデータ型をMySQLサーバーと互換性のある型にキャストしようとします。サーバーが互換性のあるデータ型を識別できない場合、エラーを返します。

COLLATE collation

COLLATE 句を含めて、列に照合を割り当てます。指定しない場合、列のデータ型のデフォルトの照合が使用されます。

INHERITS (parent_table [, ... ])

INHERITS 句を含めて、新しい外部テーブルがすべての列を継承するテーブルのリストを指定します。親テーブルは、プレーンテーブルまたは外部テーブルです。

CONSTRAINT constraint_name

列またはテーブルの制約のオプションの名前を指定します。指定しない場合、サーバーは制約名を生成します。

NOT NULL

NOT NULL キーワードを含めて、列にnull値を含めることができないことを示します。

NULL

NULL キーワードを含めて、列にNULL値を含めることが許可されていることを示します。これがデフォルトです。

CHECK (expr) [NO INHERIT]

CHECK 句を使用して、テーブルの各行が満たす必要があるブール結果を生成する式を指定します。列制約として指定されたチェック制約はその列の値のみを参照しますが、テーブル制約に表示される式は複数の列を参照できます。

CHECK 式には、サブクエリを含めたり、現在の行の列以外の変数を参照したりできません。

NO INHERIT キーワードを含めて、制約が子テーブルに伝搬しないように指定します。

DEFAULT default_expr

DEFAULT 句を含めて、列定義が表示される列のデフォルトのデータ値を指定します。デフォルトの式のデータ型は、列のデータ型と一致する必要があります。

SERVER server_name [OPTIONS (option 'value' [, ... ] ) ]

MySQLファイルシステムにあるテーブルを照会できる外部テーブルを作成するには、 SERVER 句を含め、MySQLデータアダプターを使用する外部サーバーに server_name を指定します。

OPTIONS 句を使用して、次のオプションとそれに対応する値を指定します。

Option	Value
dbname	The name of the database on the MySQL server. The database name is required.
fetch_size	This option specifies the number of rows mysql_fdw should get in each fetch operation. It can be specified for a foreign table or a foreign server. The option specified on a table overrides an option specified for the server. The default value is 100.
table_name	The name of the table on the MySQL server. The default is the name of the foreign table.
max_blob_size	The maximum BLOB size to read without truncation.

例

MySQLサーバーに保存されているデータを使用するには、MySQLテーブルの列をPostgresテーブルの列にマップするテーブルを作成する必要があります。たとえば、次の定義を持つMySQLテーブルの場合。

CREATE TABLE warehouse (
 warehouse_id      INT PRIMARY KEY,
 warehouse_name    TEXT,
 warehouse_created TIMESTAMP);

Postgresサーバーで同等のテーブルを作成するコマンドを実行します。

CREATE FOREIGN TABLE warehouse
(
 warehouse_id      INT,
 warehouse_name    TEXT,
 warehouse_created TIMESTAMP
)
SERVER mysql_server
         OPTIONS (dbname db, table_name warehouse);

SERVER 句を含めて、MySQLサーバーに保存されているデータベースの名前とPostgresサーバー上のテーブルに対応するテーブル（warehouse ）の名前を指定します。

CREATE FOREIGN TABLE コマンドの使用の詳細については、 PostgreSQL documentation を参照してください。

注釈

MySQL外部データラッパーは書き込み機能機能をサポートしています。

データ型のマッピング

外部データラッパーを使用する場合、MySQLサーバーにあるテーブルをミラーリングするテーブルをPostgresサーバーに作成する必要があります。 MySQLデータラッパーは、次のMySQLデータ型をターゲットのPostgres型に変換します。

MySQL	Postgres
BIGINT	BIGINT/INT8
BOOLEAN	SMALLINT
BLOB	BYTEA
CHAR	CHAR
DATE	DATE
DOUBLE	DOUBLE PRECISION/FLOAT8
FLOAT	FLOAT/FLOAT4
INT/INTEGER	INT/INTEGER/INT4
LONGTEXT	TEXT
SMALLINT	SMALLINT/INT2
TIMESTAMP	TIMESTAMP
VARCHAR()	VARCHAR()/CHARCTER VARYING()

注釈

ENUM データ型の場合：

MySQLは、文字列形式のenum 値を受け入れます。 MySQLサーバーに存在するのとまったく同じenum リストをEDB Postgres Advanced Serverに作成する必要があります。ローカルサーバーで不明な値を持つ行をフェッチしているときに、何らかの不整合が発生するとエラーが発生します。

また、指定されたenum 値がMySQL側に存在しないが、 EDB Postgres Advanced Server側に存在する場合、空の文字列（'' ）がenum 列のMySQL側で値として挿入されます。 enum 値を'' として持つテーブルから選択するには、Postgres側ですべての有効な値と'' を使用してenum タイプを作成します。

外国人スキーマのインポート

IMPORT FOREIGN SCHEMA コマンドを使用して、MySQLサーバーからPostgresサーバーにテーブル定義をインポートします。新しい外部テーブルは、既存のローカルスキーマのリモートテーブルと同じ列定義で作成されます。構文は次のとおりです。

IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option value [, ... ] ) ]

パラメーター

remote_schema

インポートするリモートスキーマ（MySQLデータベース）を指定します。

LIMIT TO ( table_name [, ...] )

デフォルトでは、MySQLホスト上の特定のデータベースに存在するすべてのビューとテーブルがインポートされます。このオプションを使用すると、テーブルのリストを指定したサブセットに制限できます。

EXCEPT ( table_name [, ...] )

デフォルトでは、MySQLホスト上の特定のデータベースに存在するすべてのビューとテーブルがインポートされます。このオプションを使用すると、指定した外部テーブルをインポートから除外できます。

SERVER server_name

外部テーブルをインポートするサーバーの名前を指定します。

local_schema

インポートされた外部テーブルを作成するローカルスキーマの名前を指定します。

OPTIONS

OPTIONS 句を使用して、次のオプションとそれに対応する値を指定します。

Option	Description
import_default	外部サーバーからインポートされた外部テーブルの定義に列`DEFAULT`式を含めるかどうかを制御します。デフォルトは`false`です。
import_not_null	外部サーバーからインポートされた外部テーブルの定義に列`NOT NULL`制約を含めるかどうかを制御します。デフォルトは`true`です。
import_enum_as_text	外部テーブルの定義でMySQL `ENUM`タイプを`TEXT`タイプにマップするために使用できます。それ以外の場合、タイプが作成されるという警告を出力します。デフォルトは`false`です。

例

次の定義でedbデータベースに作成されたMySQLテーブルの場合：

CREATE TABLE color(cid INT PRIMARY KEY, cname TEXT);
INSERT INTO color VALUES (1, Red);
INSERT INTO color VALUES (2, Green);
INSERT INTO color VALUES (3, Orange);

CREATE TABLE fruit(fid INT PRIMARY KEY, fname TEXT);
INSERT INTO fruit VALUES (1, Orange);
INSERT INTO fruit VALUES (2, Mango);

Postgresサーバー上の同等のテーブルをインポートするコマンドを実行します。

IMPORT FOREIGN SCHEMA edb FROM SERVER mysql_server INTO public;

SELECT * FROM color;

 cid | cname
 -----+--------
   1 | Red
   2 | Green
   3 | Orange
 (3 rows)

SELECT * FROM fruit;

 fid | fname
 -----+--------
   1 | Orange
   2 | Mango
 (2 rows)

このコマンドは、サーバーmysql_server 上のリモートスキーマedb からテーブル定義をインポートし、ローカルスキーマpublic に外部テーブルを作成します。

IMPORT FOREIGN SCHEMA コマンドの使用の詳細については、

PostgreSQL documentation を参照してください。

ドロップエクステンション

DROP EXTENSION コマンドを使用して、拡張機能を削除します。コマンドを呼び出すには、選択したクライアント（たとえば、 psql）を使用して、MySQLサーバーを削除するPostgresデータベースに接続し、コマンドを実行します。

DROP EXTENSION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ];

パラメーター

IF EXISTS

IF EXISTS 句を含めて、指定された名前の拡張機能が存在しない場合にエラーを返す代わりに通知を発行するようにサーバーに指示します。

name

必要に応じて、インストールされている拡張機能の名前を指定します。

CASCADE

拡張機能に依存するオブジェクトを削除します。他のすべての依存オブジェクトも削除します。

RESTRICT

同じ DROP コマンドにリストされているメンバーオブジェクトと拡張機能以外のオブジェクトが依存している場合、拡張機能を削除しないでください。

例

次のコマンドは、既存のデータベースから拡張機能を削除します。

DROP EXTENSION mysql_fdw;

外部データラッパー DROP EXTENSION コマンドの使用の詳細については、

PostgreSQL documentation を参照してください。

ドロップサーバー

DROP SERVER コマンドを使用して、外部サーバーへの接続を削除します。構文は次のとおりです。

DROP SERVER [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

サーバーを削除するロールはサーバーの所有者です。 ALTER SERVER コマンドを使用して、外部サーバーの所有権を再割り当てします。外部サーバーを削除するには、 DROP SERVER コマンドで指定された外部データラッパーに対するUSAGE 権限が必要です。

パラメーター

IF EXISTS

IF EXISTS 句を含めて、指定された名前のサーバーが存在しない場合にエラーを返す代わりに通知を発行するようにサーバーに指示します。

name

オプションで、インストールされたサーバーの名前を指定します。

CASCADE

サーバーに依存するオブジェクトを削除します。他のすべての依存オブジェクトも削除します。

RESTRICT

サーバーに依存しているオブジェクトがある場合は、サーバーを削除しないでください。

例

次のコマンドは、mysql_server という名前の外部サーバーを削除します。

DROP SERVER mysql_server;

DROP SERVER コマンドの使用の詳細については、 PostgreSQL documentation を参照してください。

DROP USER MAPPING コマンドを使用して、Postgresロールを外部サーバーに関連付けるマッピングを削除します。そのサーバーのユーザーマッピングを削除するには、そのサーバーの所有者である必要があります。

DROP USER MAPPING [ IF EXISTS ] FOR { user_name | USER | CURRENT_USER | PUBLIC } SERVER server_name;

パラメーター

IF EXISTS

ユーザーマッピングが存在しない場合にエラーをスローする代わりに通知を発行するようにサーバーに指示するには、 IF EXISTS 句を含めます。

user_name

マッピングのユーザー名を指定します。

server_name

MySQL クラスターへの接続を定義するサーバーの名前を指定します。

例

次のコマンドは、enterprisedb という名前のロールのユーザーマッピングを削除します。マッピングは、mysql_server という名前のサーバーに関連付けられています。

DROP USER MAPPING FOR enterprisedb SERVER mysql_server;

DROP USER MAPPING コマンドの詳細については、 PostgreSQL documentation を参照してください。

外国のテーブルをドロップ

外部テーブルは、MySQLホストにあるテーブルへのポインタです。 DROP FOREIGN TABLE コマンドを使用して、外部テーブルを削除します。外部テーブルの所有者のみが削除できます。

DROP FOREIGN TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

パラメーター

IF EXISTS

IF EXISTS 句を含めて、指定された名前の外部テーブルが存在しない場合にエラーを返す代わりに通知を発行するようにサーバーに指示します。

name

外部テーブルの名前を指定します。

CASCADE

外部テーブルに依存するオブジェクトを削除します。他のすべての依存オブジェクトも削除します。

RESTRICT

オブジェクトが依存している場合、外部テーブルを削除しないでください。

例

DROP FOREIGN TABLE warehouse;

DROP FOREIGN TABLE コマンドの使用の詳細については、 PostgreSQL documentation を参照してください。