Linked Serverを使ってSQLServerからPostgresに接続する

概要

Postgresを主DBとして使用している環境で、
都合によってSQLServer経由でPostgresに接続したくなった。

以下のような接続である
Client -> SQL Server -> Postgres

これを SQLServerの Linked Server を使って実現した

直接関連しないものもあるが、構築した環境のその他情報

• PostgresはRDS Aurora を利用
• 外部からPostgresに接続するには踏み台(Linux)ServerへのSSHトンネルが必要
• SQLServerはexpress edition
• SQLServerは任意の場所にあって、クライアントから接続できる

設定手順

事前準備

事前に SQLServerを配置するインスタンスに SQLServer と SSMS をインストールして使えるようにしておく。

Postgres ODBC Driverのインストール

posrgres12ならココからdownload

流れにそってSQLServerを配置するインスタンスにインストールしておく

SQLServerにLinked Server設定

SSMS でクエリエディタを開き、以下の例のように記述し、実行
※中でpostgresへの接続設定があるので書き換えてください
※今回はsshトンネル経由の例なので、postgres serverがlocalhostやらport12345やらになってます

DECLARE @name NVARCHAR(4000);
DECLARE @provider NVARCHAR(4000);
DECLARE @provstr NVARCHAR(4000);
DECLARE @db_name NVARCHAR(4000);

-- destination postgres database
SET @name = N'test'; -- リンクServerの名前。任意につける。後にselectとかする時に必要
SET @provider = N'MSDASQL';
SET @provstr = 'Driver={PostgreSQL UNICODE};Server=localhost;Port=12345;Database=test_db;UID=test_user;Password=test_user_password;Network=dbmssocn;'
SET @db_name = N'test_db';  -- postgres db name

-- create linked server
EXEC MASTER.dbo.sp_addlinkedserver @server = @name
,@srvproduct = N'PostgreSQL'
,@provider = @provider
,@provstr = @provstr
,@catalog = @db_name

-- set up Extended properties of the Linked Server
EXEC MASTER.dbo.sp_serveroption @server = @name
,@optname = 'data access'
,@optvalue = 'true'
EXEC MASTER.dbo.sp_serveroption @server = @name
,@optname = 'use remote collation'
,@optvalue = 'true'
EXEC MASTER.dbo.sp_serveroption @server = @name
,@optname = 'rpc'
,@optvalue = 'true'
EXEC MASTER.dbo.sp_serveroption @server = @name
,@optname = 'rpc out'
,@optvalue = 'true'
GO

うまく設定できれば以下のように SSMS からテーブル情報等を見ることができる

SQLServerからPostgres Tableへクエリの発行

SQLServerから普通にselectを発行すると、SQLServerだけに参照ロックがかかる。
これは嫌。
T-SQL の OPENQUERY を使ってSQLを発行すると参照ロックがかからないらしい。

select * from OPENQUERY(
  hoge, -- link server name
  'select * from xxxxxxxxx'
)