ODBC_FDWでPostgreSQLからSQLServerに接続する

概要

PostgreSQLの拡張機能odbc_fdwを使用し、
PostgreSQLからSQL Server上のテーブルを直接参照する方法について記述する。

経緯

メインのDBとしてPostgreSQLを使用していたが、別用途で使用しているSQL ServerのデータをPostgreSQLに連携する必要が出てきた。
このためにpythonやjavascriptを書くのがめんどくさかったので最近知った本機能を試した。

検証環境

OS: Windows11
PostgreSQL: 10.6
SQL Server: SQL Server2022 16.0.1000.6 NT

手順

1. PostgreSQLのバージョンを確認し、対応するodbc_fdwのパッケージを取得する。
   バージョンを確認 psql -V
   下記URLから対応パッケージを取得
   https://www.postgresonline.com/winextensions.php

2. odbc_fdwをインストールする。
   パッケージに含まれる libやbinフォルダをPostgreSQLの同フォルダにコピーする。

3. SQL ServerのODBCを作成する。
   Windowsシステム検索からODBC データソースを起動する。
   システムDSNタブで追加を押下する。
   データソースドライバー: SQL Server を選択する。
   任意の名前、説明を入力、サーバーにはSQL Serverのホストを入力する。
   SQL Serverのログイン方法をユーザーが入力するSQL Server用のログインIDとパスワードを使うに設定する。
   疎通テスト用にログインID、パスワードを入力し 次へ。
   その後の項目は変更せずデータソースのテストの画面まで進む。
   テストは無事に完了しました。のダイアログが出れば成功。

4. odbc_fdwを有効化する。
   PostgreSQLコンソールに入る。
   psql -U xxx
   有効化
   create extension odbc_fdw;
   拡張機能確認
   \dx

5. 外部サーバを作成する。
   create server {作成したODBC名} foreign data wrapper odbc_fdw options(dsn '{作成したODBC名}');

   参照先に日本語を含むレコードが存在する場合、オプションにエンコードを追加する。
   options(dsn '{作成したODBC名}', encode 'sjis')

   確認
   \des

6. ユーザーマッピングを作成する。
   create user mapping for postgres server {外部サーバ名} options (odbc_UID '{ODBC作成で入力したログインID}', odbc_PWD '{ODBC作成で入力したパスワード}');

7. 外部テーブルを作成する。
   create foreign table {テーブル名}({カラム名} {型}, ...) server {外部サーバ名} options (schema '{参照先schema名}', table '参照先テーブル名');

   型はPostgreSQLで使用可能な型にすること。

参考

PostgreSQLでODBC_FDWを使ってSQL Serverを参照する方法(pgAdmin4)
PostgreSQL 10.5文書 付録F 追加で提供されるモジュール