Amazon Aurora PostgreSQLでTDSのForeign Data Wrapperがサポートされたので設定してみた

いわさです。

Amazon Aurora PostgreSQLで外部データソースと接続するための拡張である、tds_fdwがサポートされました。

ちなみに、先日はmysql_fdwを使ってRDS間のデータ連携を試しています。

本日はtds_fdwを使ってRDS for SQL Server との接続を行ってみたいと思います。

RDS for SQL Serverを用意

こちらはデータベースサーバーとしては特別な考慮は不要ですが、AuroraからのTDS接続を許可する必要があるのでセキュリティグループだけ変更する必要があります。
ここでは、後ほど作成するAurora PostgreSQLのセキュリティグループからのアクセスを許可しています。

あとは通常どおり適用なデータベースを作成しておきますが、PostgreSQL側とのデータのマッピングが必要になるのでデータ型だけ意識しておきましょう。

$ sqlcmd -S hogemssql.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com -U admin -P hogehoge
1> create database fuga
2> go

1> use fuga;
2> go
Changed database context to 'fuga'.

1> create table members(id int identity(1,1) primary key, name nvarchar(32), birthday datetime);
2> go

1> insert into members (name) values ('a1');
2> insert into members (name) values ('a2');
3> insert into members (name) values ('a3');
4> go

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> select * from members;
2> go
id          name                             birthday               
----------- -------------------------------- -----------------------
          1 a1                                                  NULL
          2 a2                                                  NULL
          3 a3                                                  NULL

(3 rows affected)

Aurora PostgreSQLを用意

Aurora PostgreSQLを構築していきますが、tds_fdwがサポートされているのは本日時点で最新のv13.6のみです。
バージョンだけ気をつけて作成します。

まず、fdw用に一般ユーザーとサンプルデータベースを作成しておきます。

$ psql -h hogeaurorapostgres-instance-1.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com -U postgres
Password for user postgres: 
psql (14.1, server 13.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> create database hogedb;
CREATE DATABASE
postgres=> \c hogedb
psql (14.1, server 13.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "hogedb" as user "postgres".
hogedb=> create table fuga (id int, name2 text);
CREATE TABLE
hogedb=> insert into fuga values (1, 'aaa2');
INSERT 0 1
hogedb=> insert into fuga values (2, 'bbb2');
INSERT 0 1
hogedb=> insert into fuga values (3, 'ccc2');
INSERT 0 1
hogedb=> CREATE ROLE user1 WITH PASSWORD 'hogehoge' LOGIN;
CREATE ROLE
hogedb=> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE fuga TO user1;
GRANT

ここからtds_fdwを有効化し、連携の設定を行います。

hogedb=> CREATE EXTENSION tds_fdw;
CREATE EXTENSION

hogedb=> CREATE SERVER hogemssql FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'hogemssql.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com', port '1433', database 'fuga', tds_version '7.4');
CREATE SERVER

hogedb=> GRANT USAGE ON FOREIGN SERVER hogemssql to user1;
GRANT

hogedb=> CREATE USER MAPPING FOR user1 SERVER hogemssql OPTIONS (username 'admin', password 'hogehoge');
CREATE USER MAPPING

hogedb=> CREATE FOREIGN TABLE mssqltbl (id int, name text, birthday timestamp) SERVER hogemssql OPTIONS (table_name 'members');
CREATE FOREIGN TABLE

hogedb=> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mssqltbl TO user1;
GRANT

tds_version指定

tds_versionを指定するのですが、以下のようにautoを使ったところ失敗しました。

hogedb=> CREATE SERVER hogemssql FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'hogemssql.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com', port '1433', database 'fuga', tds_version 'auto');
ERROR:  Unknown tds version: auto.

ここでは以下を参考にSQL Server 2019でサポートされている7.4を指定しました。

が、公式ドキュメントの手順ではtds_versionを指定していませんでした。
以下によると省略時はプロトコルを自動検出する機能が備わっておりデフォルト動作させるのが推奨されているようです。

tds_fdw/ForeignServerCreation.md at master · tds-fdw/tds_fdw

接続エラー

接続エラーの場合は連携データ取得のタイミングで以下のエラーが発生しました。
ここではセキュリティグループの許可設定を見直しました。

hogedb=> select * from postgresqltbl;
ERROR:  DB-Library error: DB #: 20009, DB Msg: Unable to connect: Adaptive Server is unavailable or does not exist (hogemssql.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com), OS #: 110, OS Msg: Connection timed out, Level: 9

マッピングエラー

CREATE FOREIGN TABLEの際にテーブルマッピングを定義するのですが、フィールド名が誤っている際は以下のエラーとなりました。
内部エラーはSQL Server側のログを確認する必要があるのですがRDSのエラーログには出力されずでした。

hogedb=> select * from postgresqltbl;
ERROR:  DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

確認

またいつものように、それぞれのサーバーからデータの取得と、サーバー間でのJOINを試してみました。
設定がうまく出来ていることが確認出来ました。

hogedb=> select * from mssqltbl;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
 id | name | birthday 
----+------+----------
  1 | a1   | 
  2 | a2   | 
  3 | a3   | 
(3 rows)

hogedb=> select * from fuga;
 id | name2 
----+-------
  1 | aaa2
  2 | bbb2
  3 | ccc2
(3 rows)

hogedb=> select * from fuga t1 inner join mssqltbl t2 on t1.id = t2.id;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
 id | name2 | id | name | birthday 
----+-------+----+------+----------
  1 | aaa2  |  1 | a1   | 
  2 | bbb2  |  2 | a2   | 
  3 | ccc2  |  3 | a3   | 
(3 rows)

さいごに

本日はAurora PostgreSQLからRDS for SQL Serverへのデータ連携を行ってみました。

実際に複数のシステム間の連携のため、別サーバー間のデータ連携の話はご相談いただくことがあります。
先日はRDS for PostgreSQL → RDS for MySQLでのデータ連携でしたが、今回のtds_fdwも含めAurora PostgreSQLでも拡張機能がサポートされているので是非使ってみてください。