PostgreSQL'de TDS_FDW Kullanımı

Merhaba,

Veritabanlarında oluşturduğunuz sorguları sadece kendi veritabanınızdaki tabloları kullanarak oluşturmak istemeyebilirsiniz. DWH'de kulanılan sorgular, finans sorguları vs. için bazen tek bir veritabanı yerine farklı cluster'lar üzerindeki veritabanları arasında da sorgu yazma ihtiyacı doğabilir ve bu ihtiyaç farklı sadece aynı veritabanı arasında değil, farklı sunucular üzerinde ki veritabanlarıyla da çalışma ihtiyacını doğurabilir.

Bu ihtiyaç için PostgreSQL'de Foreign Data Wrappers(FDW) kullanılmaktadır. Bir kaç farklı çeşit FDW bulunmaktadır. En çok bilinenleri postgres_fdw ve tds_fdw dir. postgres_fdw, PostgreSQL-PostgreSQL veritabanları arasında erişimi sağlar. tds_fdw, PostgreSQL ile SQL Server veya SysBase veritabanları arasında erişimi sağlar.

tds_fdw, Tabular Data Stream protokolünü kullanır. Bu protokol farklı sunucu ve istemci arasında data transferini sağlar.

tds_fdw kurulumu için izlenmesi gereken sıra aşağıdaki gibidir.
 
CREATE EXTENSION tds_fdw;
CREATE SERVER ...
CREATE USER MAPPING ...
CREATE FOREIGN TABLE/IMPORT FDW SCHEMA

Yukarıdaki dört adımı izleyerek tds_fdw kurulumunu yapabiliriz.

tds_fdw kurulumu için öncelikle tds_fdw için kullanılan paketi kaynak koddan derlemelisiniz.

PostgreSQL10 üzerine güncel tds_fdw sürümü 2.0.0 kurulumunun CentOS/RHEL üzerinde yapılması için izlenmesi gereken adımlar şöyledir:

sudo yum install epel-release
sudo yum install freetds freetds-devel

export TDS_FDW_VERSION="2.0.0" 
wget https://github.com/tds-fdw/tds_fdw/archive/v2.0.0-alpha.2.tar.gz -O tds_fdw-${TDS_FDW_VERSION}.tar.gz 
tar -xvzf tds_fdw-${TDS_FDW_VERSION}.tar.gz 
cd tds_fdw-${TDS_FDW_VERSION} 
PATH=/usr/<pgsql-10>/bin:$PATH 
make USE_PGXS=1 
sudo PATH=/usr/pgsql-10/bin:$PATH 
make USE_PGXS=1 install

PostgreSQL10 üzerinde güncel tds_fdw 2.0.0 sürümünü Ubuntu üzerinde kurmak için izlenmasi gereken adımlar;

sudo apt-get install libsybdb5 freetds-dev freetds-common
wget https://github.com/tds-fdw/tds_fdw/archive/v2.0.0-alpha.2.tar.gz 
cd tds_fdw-2.0.0 
make USE_PGXS=1 
sudo make USE_PGXS=1 install  

Tds_fdw konusuna başlamadan önce dikkat edilmesi gereken konulardan birisi eğer makinanın slave'i varsa CREATE EXTENSION komutunu master üzerinde kullanmadan önce slave üzerinde fdw paketinin kurulması gerekliliğidir. Aksi taktirde slave üzerinde CREATE EXTENSION ile fdw kurulmak istendiğinde slave makinasında eklenti kurmak için gereken paket kurulmamış olduğundan hata verecektir.

Extension

Paket kurulumundan sonra PostgreSQL veritabanınız üzerinde tds_fdw için kullanacağınız eklentiyi aşağıdaki komutla birlikte kurabilirsiniz.

CREATE EXTENSION tds_fdw;

Hedef veritabanına erişecek PostgreSQL veritabanı üzerinde eklenti kurulmalıdır. Yani PostgreSQL veritabanında kurduğunuz tds_fdw eklentinizi diğer veritabanında kullanamazsınız. Dolayısıyla ilgili veritabanı üzerinde eklenti kurulumu ve diğer işlemlerin yapılması gerekir.

Kurduğunuz paketi görüntülemek için aşağıdaki scripti kullanabilirsiniz.

select name from pg_available_extensions where name='tds_fdw';

Server

Karşı taraftaki sunucu ve üzerinde koşulan veritabanının ne olduğunu ve nasıl ulaşılacağını PostgreSQL tarafında bildirmek için SERVER oluşturmalıyız. Server oluşturmak için gereken komut şöyledir:

CREATE SERVER <server_ismi> FOREIGN DATA WRAPPER tds_fdw OPTIONS(....);

Options alanında kullanılan parametreler; servername, port ve database. Diğer parametreler; language, character_set, , dbuse, tds_version, msg_handler, row_estimate_method, use_remote_estimate, fdw_startup_cost, fdw_tuple_cost'dir.

En sık kullanılan üç parametre;

• Servername: hedef veritabanının bulunduğu makinaya ait hostname'ini veya IP değerini girmelisiniz.
• Port: hedef veritabanının kullandığı port bilgisi olmalı.
• Database: hedef veritabanı adı olmalı.
  

Örnek bir CREATE SERVER scripti şöyle olabilir.CREATE SERVER test_fdw_server FOREIGN DATA WRAPPER tds_fdw OPTIONS(servername '192.168.8.8', port '1433', database 'hedef_db');

Server listesini görüntülemek için sistem kataloğu olan pg_foreign_Server ile aşağıdaki scriptteki gibi ya da psql komutu olan \des+ komutunu kullanabilirsiniz. 

select name, wrapper from pg_foreign_Server;

Oluşturduğunuz Server'i silmek için aşağıdaki scripti kullanabilirsiniz. Eğer server'a bağlı User Mapping varsa önce onu silmelisiniz.

DROP SERVER <server_ismi>; 

User Mapping

Hedef veritabanının ne olduğunu PostgreSQL tarafında belirledikten sonra bu veritabanına PostgreSQL veritabanı üzerinden erişim sağlayacak kullanıcıların belirlenmesi gerekir. Bu kullanıcıları USER MAPPING oluşturarak belirleyeceğiz. PostgreSQL ve SQL Server arasında bağlantıyı sağlamak için SQL server/Sysbase veritabanları üzerinde mevcut olan ve kullanmak istediğimiz tablolara erişim yetkisi olan kullanıcıya ait kullanıcı adı ve parolasına ihtiyacımız var bu noktada. 

CREATE USER MAPPING syntax'ı şöyledir:
 
CREATE USER MAPPING FOR <PostgreSQL veritabanındaki bir kullanıcı> server <server_ismi> options(....); 

User mapping oluşturmak için her iki taraftaki kullanıcılara da ihtiyacımız var. PostgreSQL de karşı taraftaki tablolara erişmesini istediğimiz kullanıcıyı belirlemeliyiz. Sonrasında da hedef veritabanındaki bir kullanıcı üzerinden fdw tablolarına erişim sağlayacağız. 

Örnek bir CREATE USER MAPPING scripti şöyle oluşturulabilir.  

CREATE USER MAPPING FOR postgres SERVER test_fdw_server OPTIONS(username 'sqlserver_kullanicisi', password 'password');
Hangi server üzerinden hangi kullanıcıyla karşı tarafa erişeceği bilgisini belirlemiş olduk.

User mapping, postgres kullanıcısı dışında PostgreSQL üzerinde bulunan diğer kullanıcılar için de oluşturulabilir.

Bir PostgreSQL veritabanı üzerinde birden fazla server ve birden fazla user mapping oluşturulabilir. Bir user birden fazla server ile farklı sunucular üzerindeki veritabanlarına ulaşabilir.

User mapping listesine psql komutu olan \deu+ ile yada pg_user_mapping sistem kataloğu ile erişebilirsiniz. 

select * from pg_user_mappings;

User Mapping'i silmek için DROP scriptini kullanabilirsiniz.

DROP USER MAPPING FOR <user_mapping_kullanıcı_ismi> SERVER <server_ismi>;

DROP USER MAPPING FOR postgres SERVER test_fdw_server;

FDW Tabloları

Son olarak FDW tablolarını oluşturmalısınız. Her bir tabloyu tek başına oluşturabilirsiniz ya da istediğiniz şema için şemada bulunna tüm tabloları import edebilirsiniz.

FDW, hedef tablolarda tablo üzerinde tablo yapısındaki değişiklikleri PostgreSQL tarafına otomatik şekilde aktaramaz. Hedef veritabanı üzerindeki tablolarda yapılan değişiklikleri yansıtmak için bu işlemi manuel yönetmeliyiz.

FDW tablosu yaratmak için CREATE FOREIGN TABLE scripti şöyledir:

CREATE FOREIGN TABLE <table_ismi> (sütun1 data_tipi, sütun2 data_tipi ...) SERVER <server_ismi>;

FDW tablosunu oluştururken sütunların data type'larının hedef tablodaki sütunların data tipleriyle aynı olmasına dikkat etmelisiniz. FDW tablosuna ait gerçek bir örnek şöyle olabilir:

CREATE FOREIGN TABLE interns (id integer, f_name character varying, l_name character varying, created_date timestamp without time zone) SERVER test_fdw_server;

Hedef veritbanındaki bir şema altındaki tabloların tamamını taşımak isterseniz IMPORT FOREIGN SCHEMA scriptini kullanmalısınız.

IMPORT FOREIGN SCHEMA <hedef_şema> from server <server_ismi> into <postgresql_şema_ismi>;

PostgreSQL tarafında şema aktarımı yapmadan önce, fdw tabloları için ayrı bir şema oluşturabilirsiniz. Şema aktarımını yaptıktan sonra da PostgreSQL tarafında USER MAPPING ile eklediğiniz kullanıcıların o şemayı kullanım(USAGE) ve erişim(en azından SELECT) yetkisinin olup olmadığı kontrol edilmelidir. Import schema syntax'ı aşağıdaki gibidir:

IMPORT FOREIGN SCHEMA dbo from server test_fdw_server into dbo_fdw;
 
Dediğinizde hedef şemadaki tüm tablolar PostgreSQL'de oluşturduğumuz dbo_fdw şeması içine aktarılacaktır. 

FDW tablo listesini \dE ile yada aşağıdaki script ile görüntüleyebilirsiniz.

SELECT table_name,table_type FROM information_schema.tables WHERE table_type='FOREIGN TABLE'
 
Son olarak PostgreSQL veritabanı üzerinden FDW tablosuna select attığımızda;

dummy_=# select * from test_fdw_dbo."interns" limit 1;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results 
         Id          | f_name        | l_name      |created_date
---------------------+---------------+-------------+------------------
 12342123222         | f_test        | l_test      | 2018-08-05 01:32:02.692413+03
(1 row)

Sevgiler,