Zenn
💨

CentOS7にtds_fdwを導入してpostgresからSQL Serverを参照する

2023/08/27に公開
SQL Server
PostgreSQL
データ移行
tdsfdw
freetds
tech

目的

DBサーバー・DBMS変更を伴う移行案件で、データ移行のため、新DBであるCentOS7のpostgresから、旧DBである外部SQL Serverのデータを直接参照したい。

方法

tds_fdwという拡張を導入すると、参照出来る（更新は不可）ようなので試す。

導入の参考にさせていただいたページ

tds_fdwの導入〜有効化迄

CREATE EXTENSION tds_fdw（失敗）

事前準備を何もせずにpostgresにログオンして以下実行しても、もちろんエラーになる

CREATE EXTENSION tds_fdw
# 機能拡張の制御ファイル"/usr/pgsql-14/share/extension/tds_fdw.control"をオープンできませんでした
# そのようなファイルやディレクトリはありません

tds_fdwが依存するfreetdsをまずはインストールする必要がある。freetdsはepelから取得出来る。
あと、makeに必要なツールもいくつか導入する。

freetds導入

インストールの準備として、epelの設定。

cd /etc/yum.repos.d/
vi epel-yum-ol7.repo

以下を最下部に挿入。

"ol7_epel"
name=Oracle Linux $releasever EPEL ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/developer_EPEL/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=0
enabled=1

インストールする。

yum search freetds
yum install -y freetds freetds-devel

centos-release-scl（LLVM7）導入

yum install centos-release-scl

postgresql14-devel導入

yum install postgresql14-devel

tds_fdw（本丸）導入

cd /tmp
wget https://github.com/GeoffMontee/tds_fdw/archive/master.zip
unzip master.zip
PATH=/usr/pgsql-14/bin:$PATH
which psql
whereis psql
whereis pg_config
cd tds_fdw-master
make USE_PGXS=1
make USE_PGXS=1 install

CREATE EXTENSION tds_fdw（成功）

tds_fdw拡張が導入済状態でpostgresにログオンして以下実行すると、拡張の有効化が成功する

CREATE EXTENSION tds_fdw
# 0 件のレコードを操作しました。（成功）

tds_fdwの活用（データ移行実施）

1. 接続先となるSQL Serverのユーザーアクセスの確認

tsql -H '{OLD-IP-ADDRESS}' -p 1433 -U {OLD-USER-NAME} -P {OLD-PASSWORD}
1> use "{OLD-DB-NAME}"
2> select * from testa
3> go
{結果}
1> quit

2. 接続元となるPostgreSQL14のユーザーアクセスの確認

psql -h{NEW-IP-ADDRESS} -U{NEW-USER-NAME} -W {NEW-DB-NAME}
パスワード:
{NEW-DB-NAME}=# select * from testb;
{結果}
{NEW-DB-NAME}=# exit;

3. 外部サーバー、ユーザマッピング、外部サーバー利用権限の定義

psql -h{NEW-IP-ADDRESS} -U{NEW-USER-NAME} -W {NEW-DB-NAME}
{NEW-PASSWORD}
CREATE SERVER old_mssqlsrv FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '{OLD-IP-ADDRESS}', port '1433', database '{OLD-DB-NAME}');
CREATE USER MAPPING FOR postgres SERVER old_mssqlsrv OPTIONS (username '{OLD-USER-NAME}', password '{OLD-PASSWORD}');
grant usage on foreign server old_mssqlsrv to postgres;

4. 外部表を設置するスキーマの作成

CREATE SCHEMA oldmssql;

5. 外部表の定義

CREATE FOREIGN TABLE oldmssql."testc" (
	"testcolumn1" float8
	, "testcolumn2" int
)
SERVER old_mssqlsrv
OPTIONS (schema_name 'dbo', table_name 'testc');

6. 移行先とする内部表の定義

create table public."testc" (
	"testcolumn1" float8
	, "testcolumn2" int
	, created_at timestamp(6) with time zone default CURRENT_TIMESTAMP
	, updated_at timestamp(6) with time zone default CURRENT_TIMESTAMP
)

7. 移行先とする内部表で利用するトリガーの定義

CREATE TRIGGER refresh_attestc_updated_at_step1
	BEFORE UPDATE ON public."testc" FOR EACH ROW
	EXECUTE PROCEDURE refresh_updated_at_step1();

CREATE TRIGGER refresh_attestc_updated_at_step2
	BEFORE UPDATE OF updated_at ON public."testc" FOR EACH ROW
	EXECUTE PROCEDURE refresh_updated_at_step2();

CREATE TRIGGER refresh_attestc_updated_at_step3
	BEFORE UPDATE ON public."testc" FOR EACH ROW
	EXECUTE PROCEDURE refresh_updated_at_step3();

※CREATE FUNCTION refresh_updated_at_step1()などもしておく。

参考させていただいたページ

8. セレクトインサートを用いたデータ移行

insert into public."testc" (
"testcolumn1"
,"testcolumn2"
) 
"testcolumn1"
,"testcolumn2"
from
oldmssql."testc"

データ移行が出来た。

Discussion