PostgreSQLでFDWを利用する方法

投稿者: nakamuraryo未分類

CentOS７にPostgreSQLをインストールし、FDW(SQLServer・Mysql用)を構成しましたので、備忘録を残したいと思います。

１、FDWとは

FDWとは、SELECT文やUPDATE文などのSQL文を使用して、外部にあるデータにアクセスできるようにするための、PostgreSQLの拡張機能です。詳しくは以下のリンクを参照してください。

外部データとの連携 ～FDWで様々なデータソースとつなぐ～

その為、初めにPostgreSQLをサーバにインストールする必要があります。RDSでも一部のFDWに対応していますが、自身で必要なFDWをインストールすることはできません。(マネージドサービスだからしょうがない)

Amazon RDS for PostgreSQL のバージョンと拡張機能

各FDWの詳細は、以下のリンクを参照してください。

・MySQL参照用FDW

 Git mysql_fdw

・SQLServer参照用FDW

Git tds_fdw

なお、現時点で「tds_fdw」はSQLServerの参照のみできます。

２、PostgreSQLのインストール

ここではCentOS7にPostgreSQLをインストールする手順を記述します。既にPostgreSQLをインストールしている場合はスキップします。インストール先はEC2上のCentOS7にインストールすることを想定しています。

１、管理者権限昇格
sudo -i

２、アップデート
yum update
reboot

３、リポジトリ追加　※ログイン後、管理者権限に昇格
rpm -ivh --nodeps https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

４、インストール
yum -y install postgresql12-server postgresql12-contrib

５、インストール確認
ls -l /usr/pgsql-12/
※「bin  doc  lib  share」
があること

６、自動起動有効化
systemctl enable postgresql-12.service

７、DBデータ保存先変更※必要な場合
vi /usr/lib/systemd/system/postgresql-12.service
==================================================
変更前）
Environment=PGDATA=/var/lib/pgsql/12/data/
変更後）
Environment=PGDATA=/data/
==================================================

８、変更適用
systemctl daemon-reload

９、初期DB作成
PGSETUP_INITDB_OPTIONS="-E UTF8 --no-locale" /usr/pgsql-12/bin/postgresql-12-setup initdb

１０、作成確認
cat /data/PG_VERSION 
※「/data」は「７、」で変更したパスを入力
※「12」が表示されること

１１、「postgres」ユーザーへ切り替え
su - postgres

１２、パスの追加
※/var/lib/pgsql/.pgsql_profileにパスを追加します。
vi /var/lib/pgsql/.pgsql_profile
==================================================
PATH=/usr/pgsql-12/bin:$PATH
export PATH
==================================================

※PGDATAを修正します。
vi /var/lib/pgsql/.bash_profile
==================================================
#PGDATA=/var/lib/pgsql/12/data
PGDATA=/data
==================================================
※「/data」は「７、」で変更したパスを入力

１３、設定反映
source ~/.bash_profile

１４、DB起動
pg_ctl start

１５、バージョン確認
psql -V

１６、データベース確認
psql -l

１７、外部接続許可
vi /data/postgresql.conf
==================================================
変更前）
#listen_addresses = 'localhost'
変更後）
listen_addresses = '*'
==================================================
※セキュリティグループでインバウンドルールが許可されていることも確認

１８、postgresユーザーを抜ける
exit

３、FDWのインストール

３－１、Mysql用FDWの設定

１、管理者権限昇格
sudo -i

２、mysql用リポジトリ追加
rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

３、「mysql-community-devel」インストール
yum -y install mysql-community-devel

４、FDWインストールに必要なパッケージインストール
yum -y install epel-release centos-release-scl
yum -y install git gcc wget postgresql12-devel

５、mysql用FDWのソースコードをダウンロード
cd /tmp
git clone https://github.com/EnterpriseDB/mysql_fdw.git
cd /tmp/mysql_fdw/

６、パス設定
export PATH=/usr/pgsql-12/bin/:$PATH
export PATH=/usr/bin/:$PATH

７、FDWインストール
make USE_PGXS=1
make USE_PGXS=1 install

３－２、SQLServer用FDWの設定

１、管理者権限昇格
sudo -i

２、FreeTDSダウンロード
cd /tmp
wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-X.X.X.tar.gz
※最新バージョンは以下を確認する。
https://www.freetds.org/

３、圧縮ファイル解凍
tar xvzf freetds-X.X.X.tar.gz

４、FreeTDSインストール
cd freetds-X.X.X/
./configure --with-openssl=/bin/openssl --with-tdsver=7.4

make 
make install

５、パス設定
vi /root/.bash_profile
最終行に以下を追加する
export PATH=${PATH}:/usr/local/bin

６、パス有効化
source ~/.bash_profile

７、FreeTDSインストール確認
tsql -C
※以下の様な出力があること
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Compile-time settings (established with the "configure" script)
                            Version: freetds vX.X.X
             freetds.conf directory: /usr/local/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.4
                              iODBC: no
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: yes
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

８、SqlServer用FDWのソースコードをダウンロード
cd /tmp
git clone https://github.com/tds-fdw/tds_fdw.git
cd tds_fdw

９、FDWインストール
make USE_PGXS=1 PG_CONFIG=/usr/pgsql-12/bin/pg_config
make USE_PGXS=1 PG_CONFIG=/usr/pgsql-12/bin/pg_config install

１０、パス設定
cd /usr/lib64
ln /usr/local/lib/libsybdb.so.5.1.0 libsybdb.so.5

１１、確認
ls -l  libsybdb.so.5

４、DB設定など

１、ユーザー切り替え
su - postgres

２、DBユーザー作成
createuser --login --pwprompt username
Enter password for new role: ＜任意のパスワード＞
Enter it again: ＜任意のパスワード＞

３、DB作成
createdb --owner=username dbname 

４、接続設定変更
vi /data/pg_hba.conf
==================================================
# "local" is for Unix domain socket connections only
local   dbname          username                                md5
→ローカルからdbnameへusernameでmd5接続できるように1行追加。※最上位行に記載

host    all             all             0.0.0.0/0               trust
→全ホストから接続できる様にする

local   all             all                                     peer
# IPv4 local connections:
host    all             all             192.168.10.0/24         md5
→192.168.10.0/24から接続できるように1行追加。
host    all             all             127.0.0.1/32            ident
==================================================

５、設定読み込み
pg_ctl reload

６、DBへログイン
psql -U postgres

７、権限変更
ALTER ROLE username SUPERUSER;
quit

８、FDW設定先DBへ切り替え
psql dbname username

９、FDW有効化
CREATE EXTENSION mysql_fdw;
CREATE EXTENSION tds_fdw;

１０、FDW　MySql設定
※詳細な利用方法は以下のGitを参照する。
「https://github.com/EnterpriseDB/mysql_fdw」
CREATE SERVER ＜サーバ名＞
     FOREIGN DATA WRAPPER mysql_fdw
     OPTIONS (host '＜エンドポイント＞', port '3306');

CREATE USER MAPPING FOR ＜ユーザー名＞
    SERVER ＜サーバ名＞
    OPTIONS (username '＜参照先DBのユーザー名＞', password '＜参照先DBのパスワード＞');

CREATE FOREIGN TABLE ＜テーブル名＞(
    ＜カラム設定＞
    ※カラム設定は参照先テーブルと同様の状態にする。
    )
    SERVER ＜サーバ名＞
    OPTIONS (dbname '＜参照先DBのDB名＞', table_name '＜参照先DBのテーブル名＞');

１１、FDW　SQLServer設定
※詳細な利用方法は以下のGitを参照する。
「https://github.com/tds-fdw/tds_fdw」
※AWS
CREATE SERVER ＜サーバ名＞
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername '＜エンドポイント＞', port '1433', database '＜参照先DBのDB名＞', tds_version '7.4');

CREATE USER MAPPING FOR ＜ユーザー名＞
    SERVER ＜サーバ名＞
    OPTIONS (username '＜参照先DBのユーザー名＞', password '＜参照先DBのパスワード＞');

CREATE FOREIGN TABLE ＜テーブル名＞ (
    ＜カラム設定＞
    ※カラム設定は参照先テーブルと同様の状態にする。
    )
    SERVER ＜サーバ名＞
    OPTIONS (table_name '＜参照先DBのテーブル名＞');

※Azure
CREATE SERVER azure_svr
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername 'IPアドレス', port '1433', database '＜参照先DBのDB名＞', tds_version '7.4');

CREATE USER MAPPING FOR ＜ユーザー名＞
    SERVER ＜サーバ名＞
    OPTIONS (username '＜参照先DBのユーザー名＞@＜参照先DBのサーバ名＞', password '＜参照先DBのパスワード＞');

CREATE FOREIGN TABLE ＜テーブル名＞ (
    ＜カラム設定＞
    ※カラム設定は参照先テーブルと同様の状態にする。
    )
    SERVER ＜サーバ名＞
    OPTIONS (table_name '＜参照先DBのテーブル名＞');

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。（スパム対策）