İçindekiler

2003 yılında, SQL standardına SQL Management of External Data (SQL/MED) adı verilen uzak verilere erişim özelliği eklendi. Bu özellik, 9.1 sürümünden beri SQL / MED’in bir bölümünü gerçekleştirmek için PostgreSQL tarafından geliştirilmektedir. SQL / MED’de, uzak sunucudaki bir tabloya foreign tablo denir. PostgreSQL Foreign Data Wrapper (FDW), local tablolara benzer foreign tabloları yönetmek için SQL / MED kullanırlar.

PostgreSQL Foreign Data Wrapper

Yani kısaca FDW, Postgres içerisinden harici/uzak/farklı veri sistemlerine doğrudan bağlanmanızı, sorgulamanızı sağlayan tercümanlardır diyebiliriz. Bir eklenti olarak kurulan FDW ile sadece uzak farklı veritabanı sistemlerine bağlanmak ve veri alabilmenin, sorgu gönderebilmenin yanı sıra aynı zamanda yazma işlemi de yapabilmekteyiz. FWD’yi, farklı veri kaynaklarından verileri normalize ederek merkezi bir veritabanına join sorgular oluşturarak çekmek için ETL aracı olarak da kullanmak da mümkün olmakla birlikte, belki en performanslı araç olmayabilir ama şurası kesin ki en hızlı yöntemdir.

FWD eklentilerinin bir kısmı Postgres üzerinde hazır olarak gelirken, bazıları açık kaynaklı paketin içine dahil edilemezler ve ayrıca indirilmelidirler. Buna örnek MySQL FWD’yi aktif hale getirmeden önce indirip kurmanız gerekir ki bu çok basit bir işlemdir. Bazı FWD Eklentilerine kısaca göz atalım:

  • Postgres FDW : Şüphesiz en kolay ve hızlı giriş yapabileceğimiz FWD, Postgres’in kaynak olarak farklı bir host üzerinde olan yine PostgreSQL veritabanına bağlanmak için kullandığımız Potgres FWD olacak.
  • MySQL FDW : Bu PostgreSQL extensionı MySQL için Foreign Data Wrapper (FDW) uygular.
  • Oracle FDW : oracle_fdw, WHERE koşullarının ve gerekli sütunların pushdown ve kapsamlı EXPLAIN desteği dahil olmak üzere Oracle veritabanlarına kolay ve verimli erişim için Foreign Data Wrapper sağlayan bir PostgreSQL extensionıdır.
  • CSV : File_fdw modülü, sunucunun dosya sistemindeki veri dosyalarına erişmek veya sunucudaki programları çalıştırmak ve çıktılarını okumak için kullanılabilen FDW file_fdw sağlar.
  • Sybase / MS SQL Server
  • BigTable or HBase
  • Cassandra
  • InfluxDB
  • MongoDB :
  • Redis
  • JSON
  • XML
  • pgsql-http
  • RSS
  • www
  • S3
  • Twitter
  • Elasticsearch
  • Hadoop
  • Docker
  • Log Files


FDW özelliğini kullanmak için, uygun extensionı yüklemeniz ve CREATE FOREIGN TABLE, CREATE SERVER ve CREATE USER MAPPING gibi kurulum komutlarını çalıştırmanız gerekir. Uygun ayarı sağladıktan sonra, extensiondan tanımlanan fonksiyonlar, foreign tablolara erişmek için sorgu işleme sırasında çağrılır. Örneklere geçmeden önce FDW’nin gelişimi ile ilgili kısaca bilgi vermek istiyorum. postgres_fdw eklentisi, PostgreSQL Global Development Group tarafından resmi olarak bakımı yapılan özel bir modüldür ve kaynak kodu PostgreSQL kaynak kodu ağacına dahil edilmiştir.

Versiyon Açıklama
9.3 postgres_fdw modülü yayınlandı (Sadece read only olarak çalışırdı ve çekilen veride filtreleme olup olmamasına bakmayarak bütün veriyi çekip filtrelemeyi kendi üzerinde yapar)
9.6 Uzak sunucuda sort işlemini desteklendi

Uzak sunucuda join işlemini desteklendi

uzak sunucuda Read/write desteklendi

Bir sorgu çalıştırdığımızda sorguları bölerek getirmeyi destekledi. (Örn 10.000 veri getirecekse varsayılan olarak 100’er veri olarak getirmeye başladı – fetch size)

10 Aggregate fonksiyonlarını uzak sunucuda yapıp getirmeyi destekledi, öncesinde bu fonksiyonlar veriler çekildikten sonra yerel sunucuda yapılırdı
11 Partition olan tablolarda where şartını(push down) uzak sunucuda kullanmayı desteklemeye başladı

Join kullanılarak delete/update desteklenmeye başlandı

12 ORDER BY ile birlikte LIMIT sorgularınıda uzak sunucuda çalıştırmayı destekledi

NOT : postgres_fdw ve FDW özelliği, distributed lock manager ve distributed deadlock algılama özelliğini desteklemez. Bu nedenle, deadlock kolayca oluşturulabilir. Örneğin, Client_A yerel bir tabloyu ‘tbl_local’ ve forein tablo ‘tbl_remote’ ve Client_B ‘tbl_remote’ ve ‘tbl_local’ güncellerse, bu iki transaction deadlock’dır ancak PostgreSQL tarafından algılanamaz. Bu nedenle bu transactionlar commitlenmez.

localdb=# — Client A
localdb=# BEGIN;
BEGIN
localdb=# UPDATE tbl_local SET data = 0 WHERE id = 1;
UPDATE 1
localdb=# UPDATE tbl_remote SET data = 0 WHERE id = 1;
UPDATE 1
localdb=# — Client B
localdb=# BEGIN;
BEGIN
localdb=# UPDATE tbl_remote SET data = 0 WHERE id = 1;
UPDATE 1
localdb=# UPDATE tbl_local SET data = 0 WHERE id = 1;
UPDATE 1

postgres_fdw

Postgres_fdw modülü, harici PostgreSQL sunucularında depolanan verilere erişmek için kullanılır.  Bu modül tarafından sağlanan işlevsellik, eski dblink modülünün işlevselliği ile büyük ölçüde örtüşmektedir. Ancak postgres_fdw, uzak tablolara erişim için daha şeffaf ve standartlara uygun sözdizimi sağlar ve birçok durumda daha iyi performans sağlar. Postgres_fdw kullanarak uzaktan erişime hazırlanmak için:

NOT : Red hat tabanlı sistemlerde bu eklentinin gelmesi için işletim sisteminde postgresql12-contrib.x86_64 paketinin yüklü olması gerekmetedir. deb tabanlı sistemlerde paket ana dağıtımda geldiği için ayrıca kurmamıza gerek yok.

yum install -y postgresql-contrib.x86_64

1. Postgres_fdw uzantısını CREATE EXTENSION kullanarak yükleriz.

CREATE EXTENSION postgres_fdw;

Aşağıdaki sorgu ile yüklü eklentileri görebiliriz;

postgres=# select * from pg_available_extensions where name='postgres_fdw';
     name     | default_version | installed_version |                      comment                       
--------------+-----------------+-------------------+----------------------------------------------------
 postgres_fdw | 1.0             | 1.0               | foreign-data wrapper for remote PostgreSQL servers
(1 row)

2. Bağlanmak istediğiniz her uzak veritabanını temsil etmek için CREATE SERVER kullanarak bir foreign server nesnesi oluşturun. Sunucu nesnesinin seçenekleri olarak kullanıcı ve parola dışında bağlantı bilgilerini belirtin.

postgres=# CREATE SERVER foreign_server
			FOREIGN DATA WRAPPER postgres_fdw
			OPTIONS (host '192.168.10.106', port '5432', dbname 'remotedb');

Aşağıdaki sorgu ile FDW leri görebiliriz;

postgres=# select * from pg_foreign_server;
-[ RECORD 1 ]-----------------------------------------------
oid        | 16388
srvname    | foreign_server
srvowner   | 10
srvfdw     | 16387
srvtype    | 
srvversion | 
srvacl     | 
srvoptions | {host=192.168.10.106,port=5432,dbname=remotedb}

3. Her bir foreign server a erişmesine izin vermek istediğiniz her veritabanı kullanıcısı için CREATE USER MAPPING kullanarak bir kullanıcı eşlemesi oluşturun. Kullanıcı eşlemesinin kullanıcı ve parola seçenekleri olarak kullanmak için uzak kullanıcı adını ve parolayı belirtin.

CREATE USER MAPPING FOR postgres
        SERVER foreign_server
        OPTIONS (user 'postgres');

Aşağıdaki sorgu ile User mapping leri görebiliriz;

postgres=# select  * from pg_user_mapping;

4. Erişmek istediğiniz her remote tablo için CREATE FOREIGN TABLE veya IMPORT FOREIGN SCHEMA kullanarak foreign bir tablo oluşturun. Foreign tablonun sütunları başvurulan uzak tabloyla eşleşmelidir. Bununla birlikte, foreign tablo nesnesinin seçenekleri olarak doğru uzak adları belirtirseniz, uzak tablodan farklı tablo ve / veya sütun adlarını kullanabilirsiniz.

CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'public', table_name 'remote_table');

Aşağıdaki sorgu ile foreign tabloları görebiliriz;

select * from pg_foreign_table;

Veya IMPORT cümleciğinide kullanabiliriz.

IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server INTO test_schema;
postgres=# select * from test_schema.remote_table;

NOT : postgres_fdw, IMPORT FOREIGN SCHEMA’yı kullanarak foreign tablo tanımlarını içe aktarabilir. Bu komut, yerel sunucuda uzak sunucuda bulunan tablolar veya viewlerle eşleşen foreign tablo tanımları oluşturur. NOT NULL dışındaki constraintlerin uzak tablolardan içe aktarılmayacağını unutmayın. PostgreSQL, foreign tablolarda CHECK kısıtlamalarını desteklese de, yerel ve uzak sunucularda bir kısıtlama ifadesinin farklı şekilde değerlendirilme riski nedeniyle, bunları otomatik olarak içe aktarmak için herhangi bir hüküm yoktur. Başka bir tablonun partitionı olan tablolar veya foreign tablolar otomatik olarak hariç tutulur.

IMPORT FOREIGN SCHEMA’yı çalıştırdığımızda, remote veritabanının mevcut yapısına dayalı olarak foreign tablolar oluşturduk. Remote veritabanı değişirse – bir tablo eklersek veya bir sütun silersek – foreign  tablolarımızın da güncellenmesi gerekecektir.

file_fdw

File_fdw modülü, sunucunun dosya sistemindeki veri dosyalarına erişmek veya sunucudaki programları çalıştırmak ve çıktılarını okumak için kullanılabilen fdw’dir. Veri dosyası veya program çıktısı, COPY FROM tarafından okunabilecek bir formatta olmalıdır.
Eklentiyi yükleriz;

CREATE EXTENSION file_fdw;

Server oluştururuz;

CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

Artık foreign veri tablosunu oluşturmaya hazırsınız. CREATE FOREIGN TABLE komutunu kullanarak tablo için sütunları, CSV dosya adını ve formatını tanımlamanız gerekecektir;

CREATE FOREIGN TABLE pglog (
  log_time timestamp(3) with time zone,
  user_name text,
  process_id integer,
  session_start_time timestamp with time zone,
  transaction_id bigint
) SERVER pglog
OPTIONS ( filename '/home/josh/data/log/pglog.csv', format 'csv' );

oracle_fdw

oracle_fdw, Oracle veritabanlarına kolay ve verimli erişim için FDW sağlayan bir PostgreSQL eklentisidir. Özellikleri;

  • Oracle veri türlerinin benzer PostgreSQL veri türlerine çevrilmesini destekler,
  • Mümkün olduğunda, koşullar ve ORDER BY ifadeleri Oracle’a iletilir,
  • Yalnızca gerekli Oracle tablo sütunları getirilir
  • Normal Oracle istemcisi ve Oracle Instant Client ile çalışır
  • EXPLAIN VERBOSE ile oracle’dali execution planı görebiliriz
  • Oracle bağlantıları, PostgreSQL session süresince önbelleğe alınır
  • PostgreSQL 9.3’den itibaren INSERT, UPDATE ve DELETE’i destekler

oracle_fdw’yi kurmak için kaynak kodunu indirin. İndirme linki için https://pgxn.org/dist/oracle_fdw sitesinden yararlanabiliriz.

wget https://github.com/laurenz/oracle_fdw/archive/ORACLE_FDW_2_2_0.zip

Yerel ortam PostgreSQL kurulumuna sahip olmalı ve kullanıcılar en azından Oracle instantclient-basic, instantclient-devel kurmalıdır. Belirtilen paketleri Oracle’ın websitesinden indirebilirsiniz(https://www.oracle.com/tr/database/technologies/instant-client/linux-x86-64-downloads.html)

Not 1 : Oracle sunucusu ve PostgreSQL sunucusu aynı makinede çalışıyorsa, client kurmanız gerekmez, başka bir deyişle bu adımı atlayabilirsiniz.

NOT 2 : Bu arada oracle clientlerin çalışması içinde mib glibc 2.14 olması gerekiyor.

yum install -y glibc.x86_64 -y
yum localinstall oracle-instantclient19.8-basic-19.8.0.0.0-1.x86_64.rpm  oracle-instantclient19.8-devel-19.8.0.0.0-1.x86_64.rpm

pg_config’in path’de olmasına ve LD_LIBRARY_PATH ile ORACLE_HOME değişkenlerini set ettiğimize emin olalım.

bash-4.2$ which pg_config
/usr/pgsql-12/bin/pg_config

export PATH="/usr/pgsql-12/bin:$PATH"
export LD_LIBRARY_PATH=/usr/lib/oracle/19.8/client64/lib
export ORACLE_HOME=/usr/lib/oracle/19.8/client64

root kullanıcıs ile İndirdiğimiz zip dosyasını açıp, açılan klasöre gittikten sonra aşağıdaki komutları çalıştırırız.

yum makecache
yum install centos-release-scl
yum install llvm-toolset-7
yum install llvm5.0
scl enable llvm-toolset-7 bash
yum install postgresql12-devel
make
make install

Psql ile postgres’e bağlandıktan sonra aşağıdaki komutu çalıştırarak eklentimizi oluşturuyoruz.

CREATE EXTENSION IF NOT EXISTS oracle_fdw ;

Eklenti kurulduktan sonra diğer eklenti kurulumlarında olduğu gibi server oluşturuyoruz.

CREATE SERVER oradb_fdw FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//xx.xx.xx.xx/orcl' );

User mapping oluşturuyoruz.

create user mapping for oradb_fdw server oracle options (user 'system', password '112233ee');

PostgreSQL’den erişmek için Oracle veritabanından bir tablo seçin ve son olarak foreign table oluşturuyoruz.

create foreign table datawrap_test (id int, name varchar(50)) server oracle options (schema 'SYSTEM', table 'DATAWRAP',nls_lang 'TURKISH_TURKEY.UTF8');

Artık Postgresql veri tabanınızdan oracle’a erişim sağlayabilirsiniz. Burda benim tavsiyem postgresql veri tabanında bir şema oluşturun ve istediğniz orale tablolarını bu şemaya import edebilirsiniz.

CREATE SCHEMA fdw;

Burada “ORACLE_SCHEMA_ISMI” olarak verdiğiniz şemadaki tüm tabloları fdw şemasına import eder, fakat orada LIMIT TO kullanımı ile sadece şu tabloları da at diye bilirsiniz. Yada limit to yerine except kullanırsanız o tablo dişindaki diğer tüm tabloları import et diye bilirsiniz.

IMPORT FOREIGN SCHEMA "ORACLE_SCHEMA_ISMI" --LIMIT TO (tablo1,tablo2) FROM SERVER server_ismi INTO fdw OPTIONS (case 'lower', collation 'C', readonly 'true');

Burada çok kritik bir bilgi olarak verebileceğim null terminate string hatasıdı oracle veri tabanı kabul ederken postgresql veri tabanı kabul etmiyor, bu yüzden böyle bir tablo ile karşılaşırsanız aşağıdaki import örneğinde olduğu gibi tablo daki kolon bazlı REPLACE fonksiyonu ile sorunu çözebilirsiniz.

CREATE FOREIGN TABLE fdw.deneme (
  name VARCHAR(12),
  surname VARCHAR(1)
) 
SERVER server_ismi
OPTIONS (
  table '(
  SELECT 
      name,
      (REPLACE(surname, chr(0))) AS TESKILAT_TURU
    FROM OWNER.DENEME
  )',
  readonly 'true');

Mustafa Bektaş Tepe
İyi Çalışmalar

Referanslar;
http://www.interdb.jp/pg/pgsql04.html#_4.1.
https://www.profelis.com.tr/postgresqlin-yabanci-veri-paketleyicileri-foreign-data-wrappers/
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
https://thoughtbot.com/blog/postgres-foreign-data-wrapper
https://www.enginyilmaz.gen.tr/makale.php?m=75

Loading